SET NAMES utf8mb4;

ALTER TABLE ad_products
  ADD COLUMN IF NOT EXISTS vat_rate_percent DECIMAL(5,2) NOT NULL DEFAULT 23.00 AFTER monthly_amount_cents,
  ADD COLUMN IF NOT EXISTS term_options_csv VARCHAR(32) NOT NULL DEFAULT '1,12' AFTER vat_rate_percent;

ALTER TABLE ad_subscriptions
  ADD COLUMN IF NOT EXISTS term_months INT UNSIGNED NOT NULL DEFAULT 1 AFTER notes,
  ADD COLUMN IF NOT EXISTS subtotal_monthly_cents INT UNSIGNED NOT NULL DEFAULT 0 AFTER term_months,
  ADD COLUMN IF NOT EXISTS vat_monthly_cents INT UNSIGNED NOT NULL DEFAULT 0 AFTER subtotal_monthly_cents,
  ADD COLUMN IF NOT EXISTS total_monthly_cents INT UNSIGNED NOT NULL DEFAULT 0 AFTER vat_monthly_cents,
  ADD COLUMN IF NOT EXISTS workflow_status ENUM('awaiting_setup','creative_draft','awaiting_business_approval','approved_pending_charge','live','ended','cancelled') NOT NULL DEFAULT 'awaiting_setup' AFTER total_monthly_cents,
  ADD COLUMN IF NOT EXISTS approval_token VARCHAR(64) DEFAULT NULL UNIQUE AFTER workflow_status,
  ADD COLUMN IF NOT EXISTS approval_approved_at DATETIME DEFAULT NULL AFTER approval_token,
  ADD COLUMN IF NOT EXISTS ad_placement_id INT UNSIGNED DEFAULT NULL AFTER approval_approved_at,
  ADD COLUMN IF NOT EXISTS stripe_setup_intent_id VARCHAR(255) DEFAULT NULL AFTER ad_placement_id,
  ADD COLUMN IF NOT EXISTS stripe_payment_method_id VARCHAR(255) DEFAULT NULL AFTER stripe_setup_intent_id,
  ADD KEY IF NOT EXISTS idx_ad_subs_workflow (workflow_status),
  ADD KEY IF NOT EXISTS idx_ad_subs_placement (ad_placement_id);

ALTER TABLE ad_subscriptions
  ADD CONSTRAINT fk_ad_subs_placement
  FOREIGN KEY (ad_placement_id) REFERENCES ad_placements(id) ON DELETE SET NULL;