-- Shopee JP Researcher - Database Schema
-- DB: ctjfryyt_shopee_res

CREATE TABLE IF NOT EXISTS items (
    user_id         INT           NOT NULL,
    region          VARCHAR(10)   NOT NULL,
    itemid          BIGINT        NOT NULL,
    shopid          BIGINT        NOT NULL,
    name            TEXT          NOT NULL,
    price           DECIMAL(15,2),
    currency        VARCHAR(5),
    image_url       TEXT,
    category_name   VARCHAR(255),
    seller_location VARCHAR(50),
    updated_at      DATETIME      NOT NULL,
    PRIMARY KEY (user_id, region, itemid),
    INDEX idx_shop (user_id, region, shopid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS snapshots (
    user_id         INT           NOT NULL,
    region          VARCHAR(10)   NOT NULL,
    itemid          BIGINT        NOT NULL,
    date            DATE          NOT NULL,
    historical_sold INT,
    stock           INT,
    price           DECIMAL(15,2),
    PRIMARY KEY (user_id, region, itemid, date),
    INDEX idx_date (user_id, region, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shops (
    user_id         INT           NOT NULL,
    region          VARCHAR(10)   NOT NULL,
    shopid          BIGINT        NOT NULL,
    shop_name       VARCHAR(255),
    username        VARCHAR(255),
    updated_at      DATETIME,
    PRIMARY KEY (user_id, region, shopid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS keywords (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT           NOT NULL,
    keyword         VARCHAR(255)  NOT NULL,
    region          VARCHAR(10)   NOT NULL,
    source          ENUM('manual', 'ai_deepseek') DEFAULT 'manual',
    priority        INT           DEFAULT 0,
    is_active       BOOLEAN       DEFAULT TRUE,
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,
    last_crawled_at DATETIME,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_reports (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT           NOT NULL,
    region          VARCHAR(10)   NOT NULL,
    report_type     ENUM('trending', 'keyword_suggestion', 'competitor', 'opportunity'),
    content_json    JSON          NOT NULL,
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(100)  NOT NULL UNIQUE,
    password_hash   VARCHAR(255)  NOT NULL,
    api_token       VARCHAR(64)   UNIQUE,
    role            ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;