CREATE TABLE IF NOT EXISTS isk_products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_slug VARCHAR(190) NOT NULL,
  category_name VARCHAR(255) NOT NULL,
  source_url VARCHAR(700) NOT NULL,
  source_product_id BIGINT NULL,
  title TEXT,
  description MEDIUMTEXT,
  description_hash CHAR(64) NULL,
  title_hash CHAR(64) NULL,
  price_min_toman INT NULL,
  price_max_toman INT NULL,
  regular_price_min_toman INT NULL,
  regular_price_max_toman INT NULL,
  availability ENUM('available','unavailable','unknown') NOT NULL DEFAULT 'unknown',
  product_type ENUM('simple','variable') NOT NULL DEFAULT 'simple',
  variants_hash CHAR(64) NULL,
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  last_changed DATETIME NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_source_url (source_url),
  KEY idx_category_slug (category_slug),
  KEY idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS isk_product_variants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id BIGINT UNSIGNED NOT NULL,
  variant_key VARCHAR(255) NOT NULL,
  variant_label VARCHAR(255) NOT NULL,
  option_name VARCHAR(191) DEFAULT 'رنگ',
  option_value VARCHAR(255) NULL,
  source_variation_id BIGINT NULL,
  price_toman INT NULL,
  regular_price_toman INT NULL,
  availability ENUM('available','unavailable','unknown') NOT NULL DEFAULT 'unknown',
  max_qty DECIMAL(12,2) NULL,
  attributes_json MEDIUMTEXT NULL,
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  last_changed DATETIME NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_product_variant (product_id, variant_key),
  KEY idx_product_id (product_id),
  KEY idx_variant_label (variant_label),
  CONSTRAINT fk_isk_variant_product FOREIGN KEY (product_id) REFERENCES isk_products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS isk_changes_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_url VARCHAR(700) NOT NULL,
  variant_key VARCHAR(255) NULL,
  change_type VARCHAR(60) NOT NULL,
  old_value MEDIUMTEXT NULL,
  new_value MEDIUMTEXT NULL,
  detected_at DATETIME NOT NULL,
  KEY idx_source_url (source_url),
  KEY idx_detected_at (detected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS isk_site_sync_map (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_ref VARCHAR(900) NOT NULL,
  source_hash CHAR(64) NOT NULL,
  mixin_product_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_source_hash (source_hash),
  KEY idx_mixin_product_id (mixin_product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;