CREATE TABLE IF NOT EXISTS pricing_tiers (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pricing_tier_name (name)
);

CREATE TABLE IF NOT EXISTS pricing_tier_prices (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  pricing_tier_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_size_id BIGINT UNSIGNED NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tier_price_tier FOREIGN KEY (pricing_tier_id) REFERENCES pricing_tiers(id) ON DELETE CASCADE,
  CONSTRAINT fk_tier_price_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_tier_price_size FOREIGN KEY (product_size_id) REFERENCES product_size_options(id) ON DELETE CASCADE,
  UNIQUE KEY uq_tier_product_size_price (pricing_tier_id, product_size_id),
  INDEX idx_tier_prices_product (product_id, product_size_id)
);

SET @customers_pricing_tier_id = (
  SELECT IF(
    EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'customers'
        AND column_name = 'pricing_tier_id'
    ),
    'SELECT 1',
    'ALTER TABLE customers ADD COLUMN pricing_tier_id BIGINT UNSIGNED NULL AFTER category_id'
  )
);
PREPARE customers_pricing_tier_id_stmt FROM @customers_pricing_tier_id;
EXECUTE customers_pricing_tier_id_stmt;
DEALLOCATE PREPARE customers_pricing_tier_id_stmt;

SET @customers_pricing_tier_index = (
  SELECT IF(
    EXISTS (
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'customers'
        AND index_name = 'idx_customers_pricing_tier'
    ),
    'SELECT 1',
    'ALTER TABLE customers ADD INDEX idx_customers_pricing_tier (pricing_tier_id, active)'
  )
);
PREPARE customers_pricing_tier_index_stmt FROM @customers_pricing_tier_index;
EXECUTE customers_pricing_tier_index_stmt;
DEALLOCATE PREPARE customers_pricing_tier_index_stmt;

SET @customers_pricing_tier_fk = (
  SELECT IF(
    EXISTS (
      SELECT 1 FROM information_schema.referential_constraints
      WHERE constraint_schema = DATABASE()
        AND table_name = 'customers'
        AND constraint_name = 'fk_customers_pricing_tier'
    ),
    'SELECT 1',
    'ALTER TABLE customers ADD CONSTRAINT fk_customers_pricing_tier FOREIGN KEY (pricing_tier_id) REFERENCES pricing_tiers(id) ON DELETE SET NULL'
  )
);
PREPARE customers_pricing_tier_fk_stmt FROM @customers_pricing_tier_fk;
EXECUTE customers_pricing_tier_fk_stmt;
DEALLOCATE PREPARE customers_pricing_tier_fk_stmt;