-- Extend projects table with analytics config fields
-- Matches columns from Excel "Data Source" sheet

ALTER TABLE projects
  ADD COLUMN IF NOT EXISTS business_id           VARCHAR(20)    NULL AFTER id,
  ADD COLUMN IF NOT EXISTS business_category     VARCHAR(100)   NULL,
  ADD COLUMN IF NOT EXISTS business_sub_category VARCHAR(150)   NULL,
  ADD COLUMN IF NOT EXISTS revenue_managed       DECIMAL(12,2)  NULL,
  ADD COLUMN IF NOT EXISTS seo_head              VARCHAR(100)   NULL,
  ADD COLUMN IF NOT EXISTS manager_name          VARCHAR(100)   NULL,
  ADD COLUMN IF NOT EXISTS sr_analyst            VARCHAR(100)   NULL,
  ADD COLUMN IF NOT EXISTS onboard_date          DATE           NULL,
  ADD COLUMN IF NOT EXISTS brand_term_regex      VARCHAR(500)   NULL,
  ADD COLUMN IF NOT EXISTS ai_overview_event     VARCHAR(100)   NULL,
  ADD COLUMN IF NOT EXISTS lead_categories       JSON           NULL;

-- lead_categories JSON schema (per project, flexible):
-- [
--   { "key": "calls",    "label": "Calls",    "events": ["Click To Call"],        "cpl_override": null },
--   { "key": "forms",    "label": "Forms",    "events": ["Contact Us Form Submit"],"cpl_override": 1200 },
--   { "key": "whatsapp", "label": "WhatsApp", "events": ["WhatsApp - GA4"],        "cpl_override": null }
-- ]
-- cpl_override = null → use PPC/config.json lead_category_cpls global value
-- cpl_override = number → project-specific override (takes precedence)
