CREATE TABLE IF NOT EXISTS product_master (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  barcode VARCHAR(80) NOT NULL,
  name VARCHAR(220) NOT NULL,
  description TEXT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'PZA',
  purchase_unit VARCHAR(20) NOT NULL DEFAULT 'PZA',
  category VARCHAR(120) NULL,
  department VARCHAR(120) NULL,
  sat_key VARCHAR(20) NULL,
  barcode_type VARCHAR(40) NOT NULL DEFAULT 'EAN-13',
  active TINYINT(1) NOT NULL DEFAULT 1,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_product_master_barcode (barcode),
  KEY idx_product_master_name (name),
  KEY idx_product_master_category (category),
  KEY idx_product_master_department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_assets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  asset_type VARCHAR(20) NOT NULL DEFAULT 'image',
  url TEXT NOT NULL,
  source_type VARCHAR(40) NOT NULL DEFAULT 'own',
  license_status VARCHAR(40) NOT NULL DEFAULT 'authorized',
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_product_assets_product (product_id),
  CONSTRAINT fk_product_assets_product FOREIGN KEY (product_id) REFERENCES product_master(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_hash VARCHAR(255) NOT NULL,
  tenant_name VARCHAR(160) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_api_tokens_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_contributions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  barcode VARCHAR(80) NOT NULL,
  sku VARCHAR(120) NULL,
  name VARCHAR(220) NOT NULL,
  description TEXT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'PZA',
  purchase_unit VARCHAR(20) NOT NULL DEFAULT 'PZA',
  category VARCHAR(120) NULL,
  department VARCHAR(120) NULL,
  sat_key VARCHAR(20) NULL,
  barcode_type VARCHAR(40) NOT NULL DEFAULT 'EAN-13',
  image_url TEXT NULL,
  thumb_url TEXT NULL,
  original_size INT UNSIGNED NULL,
  processed_size INT UNSIGNED NULL,
  source_tenant VARCHAR(160) NULL,
  source_branch VARCHAR(160) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending_review',
  review_notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_product_contributions_barcode (barcode),
  KEY idx_product_contributions_status (status),
  KEY idx_product_contributions_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;