-- SPRINT 3 (Produk): Master Produk + Pricing basic

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(191) NOT NULL,
  sku VARCHAR(64) NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  product_type ENUM('goods','service') NOT NULL DEFAULT 'goods',
  manage_stock TINYINT(1) NOT NULL DEFAULT 0,
  stock_alert_level DECIMAL(18,4) NULL,
  purchase_price DECIMAL(18,2) NULL,
  price_public DECIMAL(18,2) NOT NULL DEFAULT 0,
  price_agent DECIMAL(18,2) NOT NULL DEFAULT 0,
  image_path VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_tenant_sku (tenant_id, sku),
  KEY idx_tenant (tenant_id),
  KEY idx_cat (tenant_id, category_id),
  KEY idx_unit (tenant_id, unit_id),
  KEY idx_active (tenant_id, is_active),
  KEY idx_name (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS product_locations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_tenant_product_location (tenant_id, product_id, location_id),
  KEY idx_product (tenant_id, product_id),
  KEY idx_location (tenant_id, location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS product_flags (
  product_id BIGINT UNSIGNED NOT NULL,
  show_pos TINYINT(1) NOT NULL DEFAULT 1,
  warehouse_only TINYINT(1) NOT NULL DEFAULT 0,
  need_spk TINYINT(1) NOT NULL DEFAULT 0,
  lendable TINYINT(1) NOT NULL DEFAULT 0,
  vendor_enabled TINYINT(1) NOT NULL DEFAULT 0,
  channel_marketplace TINYINT(1) NOT NULL DEFAULT 0,
  channel_landing TINYINT(1) NOT NULL DEFAULT 0,
  show_agent TINYINT(1) NOT NULL DEFAULT 1,
  price_editable TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS product_related (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  related_product_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_tenant_rel (tenant_id, product_id, related_product_id),
  KEY idx_product (tenant_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS product_variants (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(191) NOT NULL,
  price_public DECIMAL(18,2) NOT NULL DEFAULT 0,
  price_agent DECIMAL(18,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_product (tenant_id, product_id),
  KEY idx_active (tenant_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS product_price_tiers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  segment ENUM('public','agent') NOT NULL DEFAULT 'public',
  min_qty INT UNSIGNED NOT NULL,
  max_qty INT UNSIGNED NULL,
  price DECIMAL(18,2) NOT NULL,
  label VARCHAR(191) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_product (tenant_id, product_id),
  KEY idx_segment_qty (tenant_id, segment, min_qty)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
