-- Robnur V2 — Product Aggregation Engine schema
-- Run this once against your MySQL database (cPanel: phpMyAdmin or `mysql` CLI).

CREATE TABLE IF NOT EXISTS sources (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug               VARCHAR(40) NOT NULL UNIQUE,   -- 'daraz', 'hostinger', 'namecheap'
  display_name       VARCHAR(100) NOT NULL,
  type               ENUM('api','feed','manual','scrape') NOT NULL DEFAULT 'manual',

  affiliate_id       VARCHAR(191),
  api_key            VARCHAR(500),
  feed_url           VARCHAR(500),
  commission_rate    VARCHAR(50),                   -- free-text reference, e.g. '60%', '$70/sale'

  category_map_json  JSON NULL,
  sync_interval_hrs  INT UNSIGNED NOT NULL DEFAULT 24,
  is_active          TINYINT(1) NOT NULL DEFAULT 1,

  last_synced_at     DATETIME NULL,
  last_sync_status   VARCHAR(255) NULL,

  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  source          VARCHAR(40)  NOT NULL,            -- matches sources.slug
  external_id     VARCHAR(191) NOT NULL,             -- source's own product id/sku

  name            VARCHAR(255) NOT NULL,
  brand           VARCHAR(120),
  category        VARCHAR(120),

  price           DECIMAL(12,2) NOT NULL,
  currency        CHAR(3) NOT NULL,
  country         CHAR(2) NOT NULL,

  description     TEXT,
  specs_json      JSON NULL,
  images_json     JSON NULL,

  rating          DECIMAL(3,2) NULL,
  stock_status    ENUM('in_stock','out_of_stock','unknown') NOT NULL DEFAULT 'unknown',

  affiliate_url   VARCHAR(500) NOT NULL,
  raw_json        JSON NULL,

  fetched_at      DATETIME NOT NULL,
  expires_at      DATETIME NOT NULL,

  UNIQUE KEY uniq_source_item (source, external_id),
  INDEX idx_category (category),
  INDEX idx_brand (brand),
  INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS click_logs (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id   BIGINT UNSIGNED NOT NULL,
  source       VARCHAR(40) NOT NULL,
  clicked_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_hash      VARCHAR(64) NULL,
  INDEX idx_product (product_id),
  INDEX idx_clicked_at (clicked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_users (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(191) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;