-- WorthTheBuzz Project Hive — initial MySQL schema draft

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('member','reviewer','editor','admin') NOT NULL DEFAULT 'member',
    hive_is_public BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    forum_url VARCHAR(2048) NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE manufacturers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    description TEXT NULL,
    website_url VARCHAR(2048) NULL,
    forum_url VARCHAR(2048) NULL,
    logo_path VARCHAR(500) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    manufacturer_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    short_description TEXT NULL,
    model_number VARCHAR(120) NULL,
    official_url VARCHAR(2048) NULL,
    forum_url VARCHAR(2048) NULL,
    status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    author_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(220) NOT NULL,
    slug VARCHAR(240) NOT NULL UNIQUE,
    summary TEXT NULL,
    body LONGTEXT NULL,
    buzz_score DECIMAL(3,1) NULL,
    bee_level ENUM('none','bronze','silver','gold','platinum','queens_crown') NOT NULL DEFAULT 'none',
    who_is_it_for TEXT NULL,
    who_should_skip TEXT NULL,
    what_makes_unique TEXT NULL,
    worth_the_money TEXT NULL,
    is_worth_the_buzz TEXT NULL,
    status ENUM('draft','review','scheduled','published','archived') NOT NULL DEFAULT 'draft',
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE review_transparency (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    review_id BIGINT UNSIGNED NOT NULL UNIQUE,
    product_source ENUM('purchased','manufacturer_sample','other') NOT NULL,
    monetary_compensation BOOLEAN NOT NULL DEFAULT FALSE,
    affiliate_links BOOLEAN NOT NULL DEFAULT FALSE,
    manufacturer_reviewed_before_publish BOOLEAN NOT NULL DEFAULT FALSE,
    manufacturer_had_editorial_control BOOLEAN NOT NULL DEFAULT FALSE,
    disclosure_text TEXT NULL,
    policy_version VARCHAR(30) NOT NULL DEFAULT '1.0',
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE
);

CREATE TABLE collections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL DEFAULT 'My Collection',
    visibility ENUM('private','public') NOT NULL DEFAULT 'private',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE collection_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    collection_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    state ENUM('owned','wishlist','previously_owned','favorite','gifted') NOT NULL DEFAULT 'owned',
    notes TEXT NULL,
    item_is_public BOOLEAN NOT NULL DEFAULT FALSE,
    added_at TIMESTAMP NULL,
    UNIQUE KEY unique_collection_product (collection_id, product_id),
    FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE community_trophies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    description TEXT NOT NULL,
    image_path VARCHAR(500) NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE user_trophies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    trophy_id BIGINT UNSIGNED NOT NULL,
    awarded_by BIGINT UNSIGNED NOT NULL,
    reason TEXT NOT NULL,
    public_story TEXT NULL,
    visibility ENUM('private','public') NOT NULL DEFAULT 'public',
    awarded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (trophy_id) REFERENCES community_trophies(id),
    FOREIGN KEY (awarded_by) REFERENCES users(id)
);

CREATE TABLE advertisements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    placement VARCHAR(100) NOT NULL,
    device ENUM('all','desktop','mobile') NOT NULL DEFAULT 'all',
    code LONGTEXT NULL,
    image_path VARCHAR(500) NULL,
    target_url VARCHAR(2048) NULL,
    starts_at TIMESTAMP NULL,
    ends_at TIMESTAMP NULL,
    is_active BOOLEAN NOT NULL DEFAULT FALSE
);