-- =========================================================
-- 100_pricelist_foundation.sql
-- Pricelist / price catalog presentation layer
-- Source of truth harga tetap di products / variants / tiers
-- =========================================================

CREATE TABLE IF NOT EXISTS pricelist_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(180) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_pricelist_groups_scope_active (tenant_id, location_id, is_active, sort_order),
  UNIQUE KEY uq_pricelist_groups_location_slug (location_id, slug),
  CONSTRAINT fk_pricelist_groups_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_pricelist_groups_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pricelist_item_configs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  pricelist_group_id BIGINT UNSIGNED NOT NULL,
  is_visible TINYINT(1) NOT NULL DEFAULT 1,
  display_name_override VARCHAR(180) NULL,
  display_order INT NOT NULL DEFAULT 0,
  display_mode ENUM('basic','variant','tier','contact_only') NOT NULL DEFAULT 'basic',
  short_note VARCHAR(255) NULL,
  available_for_retail TINYINT(1) NOT NULL DEFAULT 1,
  available_for_agent TINYINT(1) NOT NULL DEFAULT 0,
  is_public_visible TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pricelist_item_configs_group_product (pricelist_group_id, product_id),
  KEY idx_pricelist_item_configs_scope (tenant_id, location_id, pricelist_group_id, is_visible, display_order),
  KEY idx_pricelist_item_configs_product (product_id),
  CONSTRAINT fk_pricelist_item_configs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_pricelist_item_configs_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_pricelist_item_configs_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_pricelist_item_configs_group FOREIGN KEY (pricelist_group_id) REFERENCES pricelist_groups(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pricelist_landings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  token VARCHAR(80) NOT NULL,
  price_mode ENUM('retail','agent') NOT NULL DEFAULT 'retail',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  visibility ENUM('private','token_only','public') NOT NULL DEFAULT 'token_only',
  selected_groups JSON NULL,
  selected_items JSON NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pricelist_landings_token (token),
  KEY idx_pricelist_landings_scope_active (tenant_id, location_id, is_active, sort_order),
  CONSTRAINT fk_pricelist_landings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_pricelist_landings_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;