-- Migration 003: product discovery tables + page classification

ALTER TABLE pages
  ADD COLUMN IF NOT EXISTS page_class VARCHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS product BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS has_product_catalogue BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS product_page_role VARCHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS product_entry_url TEXT NULL,
  ADD COLUMN IF NOT EXISTS context_type VARCHAR(64) NULL,
  ADD COLUMN IF NOT EXISTS context_label TEXT NULL,
  ADD COLUMN IF NOT EXISTS first_seen_run_id CHAR(36) NULL,
  ADD COLUMN IF NOT EXISTS last_seen_run_id CHAR(36) NULL,
  ADD COLUMN IF NOT EXISTS created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE INDEX IF NOT EXISTS idx_pages_page_class ON pages (page_class);
CREATE INDEX IF NOT EXISTS idx_pages_product_role ON pages (product_page_role);

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_key CHAR(64) NOT NULL UNIQUE,
  domain VARCHAR(255) NOT NULL,
  canonical_product_url TEXT NULL,
  product_name TEXT NULL,
  product_code TEXT NULL,
  product_family TEXT NULL,
  product_type VARCHAR(64) NULL,
  product_page_role VARCHAR(64) NULL,
  identity_confidence DECIMAL(5,2) NULL,
  identity_source VARCHAR(64) NULL,
  first_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  last_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_products_domain (domain),
  INDEX idx_products_name (product_name(255)),
  INDEX idx_products_code (product_code(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS page_product_references (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reference_key CHAR(64) NOT NULL UNIQUE,
  page_key CHAR(64) NOT NULL,
  product_key CHAR(64) NOT NULL,
  reference_type VARCHAR(64) NOT NULL,
  context_type VARCHAR(64) NULL,
  context_label TEXT NULL,
  visible_product_name TEXT NULL,
  visible_product_code TEXT NULL,
  product_url TEXT NULL,
  canonical_product_url TEXT NULL,
  position_index INT NULL,
  first_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  last_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  UNIQUE KEY uq_page_product_ctx (page_key, product_key, context_type, context_label(255)),
  INDEX idx_ppr_page (page_key),
  INDEX idx_ppr_product (product_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_variants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  variant_key CHAR(64) NOT NULL UNIQUE,
  product_key CHAR(64) NOT NULL,
  article_number TEXT NULL,
  article_number_raw TEXT NULL,
  variant_name TEXT NULL,
  type_description TEXT NULL,
  dimming TEXT NULL,
  status VARCHAR(64) NULL,
  luminous_flux TEXT NULL,
  power TEXT NULL,
  efficacy TEXT NULL,
  color_temperature TEXT NULL,
  cri TEXT NULL,
  ugr TEXT NULL,
  ip_rate TEXT NULL,
  source_url TEXT NULL,
  source_section TEXT NULL,
  first_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  last_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  INDEX idx_variants_product (product_key),
  INDEX idx_variants_article (article_number(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_option_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  group_key CHAR(64) NOT NULL UNIQUE,
  product_key CHAR(64) NOT NULL,
  step_name TEXT NULL,
  tab_name TEXT NULL,
  group_name TEXT NOT NULL,
  group_type VARCHAR(64) NULL,
  required_option BOOLEAN NULL,
  multi_select BOOLEAN NULL,
  source_section TEXT NULL,
  position_index INT NULL,
  INDEX idx_option_groups_product (product_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_option_values (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value_key CHAR(64) NOT NULL UNIQUE,
  product_key CHAR(64) NOT NULL,
  group_key CHAR(64) NOT NULL,
  value_text TEXT NOT NULL,
  value_code TEXT NULL,
  value_unit TEXT NULL,
  value_image_url TEXT NULL,
  selected_by_default BOOLEAN NULL,
  disabled BOOLEAN NULL,
  raw_label TEXT NULL,
  position_index INT NULL,
  INDEX idx_option_values_product (product_key),
  INDEX idx_option_values_group (group_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_downloads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  download_key CHAR(64) NOT NULL UNIQUE,
  product_key CHAR(64) NOT NULL,
  variant_key CHAR(64) NULL,
  download_label TEXT NULL,
  download_type VARCHAR(64) NULL,
  url TEXT NOT NULL,
  canonical_url TEXT NULL,
  source_section TEXT NULL,
  first_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  last_seen_run_id CHAR(36) NOT NULL DEFAULT '',
  INDEX idx_downloads_product (product_key),
  INDEX idx_downloads_variant (variant_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_page_analysis (
  id INT AUTO_INCREMENT PRIMARY KEY,
  analysis_key CHAR(64) NOT NULL UNIQUE,
  page_key CHAR(64) NOT NULL,
  model VARCHAR(128) NOT NULL,
  input_digest CHAR(64) NOT NULL,
  output_json JSON NOT NULL,
  confidence DECIMAL(5,2) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ai_page (page_key),
  INDEX idx_ai_input_digest (input_digest)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS page_catalogue_filters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  page_key CHAR(64) NOT NULL,
  filter_key CHAR(64) NOT NULL UNIQUE,
  filter_name TEXT NOT NULL,
  filter_type VARCHAR(64) NULL,
  visible_value_count INT NULL,
  raw_text TEXT NULL,
  position_index INT NULL,
  INDEX idx_pcf_page (page_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS page_catalogue_filter_values (
  id INT AUTO_INCREMENT PRIMARY KEY,
  filter_key CHAR(64) NOT NULL,
  value_key CHAR(64) NOT NULL UNIQUE,
  page_key CHAR(64) NOT NULL,
  filter_name TEXT NOT NULL,
  value_text TEXT NOT NULL,
  value_code TEXT NULL,
  result_count INT NULL,
  selected BOOLEAN NULL,
  disabled BOOLEAN NULL,
  position_index INT NULL,
  INDEX idx_pcfv_filter (filter_key),
  INDEX idx_pcfv_page (page_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
