-- ============================================================
-- CAMPAIGN PLATFORM — DATABASE SCHEMA
-- ============================================================

-- UUID primary keys have no DEFAULT — application code supplies IDs via crypto.randomUUID()

-- ============================================================
-- USERS & AUTH
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
  id            UUID PRIMARY KEY,
  email         TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name          TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('super_admin', 'admin', 'manager', 'viewer')),
  is_active     BOOLEAN NOT NULL DEFAULT true,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- CLIENTS
-- ============================================================

CREATE TABLE IF NOT EXISTS clients (
  id               UUID PRIMARY KEY,
  name             TEXT NOT NULL,
  prefix           CHAR(3) UNIQUE NOT NULL,   -- e.g. ACM — used in campaign codes
  industry         TEXT,                      -- e.g. Financial Services, Retail
  primary_domain   TEXT,                      -- e.g. example.com
  timezone         TEXT NOT NULL DEFAULT 'America/New_York',
  default_currency TEXT NOT NULL DEFAULT 'USD',
  is_active        BOOLEAN NOT NULL DEFAULT true,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add columns to existing tables if upgrading from earlier schema
ALTER TABLE clients ADD COLUMN IF NOT EXISTS industry       TEXT;
ALTER TABLE clients ADD COLUMN IF NOT EXISTS primary_domain TEXT;

-- Users belong to clients (a super_admin has no client_id)
CREATE TABLE IF NOT EXISTS client_users (
  id         UUID PRIMARY KEY,
  client_id  UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role       TEXT NOT NULL DEFAULT 'manager' CHECK (role IN ('admin', 'manager', 'viewer')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(client_id, user_id)
);

-- ============================================================
-- CHANNEL TAXONOMY
-- ============================================================

CREATE TABLE IF NOT EXISTS channels (
  code          TEXT PRIMARY KEY,             -- e.g. PS, SC, EM
  name          TEXT NOT NULL,               -- e.g. Paid Search
  channel_group TEXT NOT NULL,               -- e.g. Paid Media
  aa_mc         TEXT NOT NULL,               -- Adobe Analytics Marketing Channel
  xdm_type      TEXT NOT NULL,               -- XDM channel type
  xdm_id        TEXT NOT NULL,               -- XDM channel URI
  is_active     BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS channel_subtypes (
  id            UUID PRIMARY KEY,
  channel_code  TEXT NOT NULL REFERENCES channels(code) ON DELETE CASCADE,
  subtype_code  TEXT NOT NULL,               -- e.g. PS-BR
  name          TEXT NOT NULL,               -- e.g. Brand Search
  UNIQUE(channel_code, subtype_code)
);

-- Which channels are active for a client
CREATE TABLE IF NOT EXISTS client_channels (
  client_id    UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  channel_code TEXT NOT NULL REFERENCES channels(code) ON DELETE CASCADE,
  is_active    BOOLEAN NOT NULL DEFAULT true,
  PRIMARY KEY (client_id, channel_code)
);

-- ============================================================
-- FIELD PACKS
-- ============================================================

CREATE TABLE IF NOT EXISTS field_packs (
  id          UUID PRIMARY KEY,
  code        TEXT UNIQUE NOT NULL,           -- e.g. content_media, ecommerce
  name        TEXT NOT NULL,
  description TEXT,
  category    TEXT NOT NULL CHECK (category IN ('core', 'industry', 'platform', 'custom')),
  is_default  BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS pack_fields (
  id                   UUID PRIMARY KEY,
  pack_id              UUID NOT NULL REFERENCES field_packs(id) ON DELETE CASCADE,
  field_code           TEXT NOT NULL,
  label                TEXT NOT NULL,
  field_type           TEXT NOT NULL CHECK (field_type IN ('text', 'select', 'multiselect', 'number', 'bool', 'date', 'url')),
  applies_to_channels  TEXT[] NOT NULL DEFAULT '{}',  -- empty = all channels
  applies_to_level     TEXT NOT NULL DEFAULT 'experience' CHECK (applies_to_level IN ('campaign', 'channel_line', 'experience')),
  is_required          BOOLEAN NOT NULL DEFAULT false,
  sort_order           INT NOT NULL DEFAULT 0,
  options              JSONB,                -- for select/multiselect fields
  UNIQUE(pack_id, field_code)
);

-- Which packs a client has activated
CREATE TABLE IF NOT EXISTS client_packs (
  id           UUID PRIMARY KEY,
  client_id    UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  pack_id      UUID NOT NULL REFERENCES field_packs(id) ON DELETE CASCADE,
  activated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  activated_by UUID REFERENCES users(id),
  UNIQUE(client_id, pack_id)
);

-- Client-specific custom fields (beyond standard packs)
CREATE TABLE IF NOT EXISTS client_custom_fields (
  id                  UUID PRIMARY KEY,
  client_id           UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  field_code          TEXT NOT NULL,
  label               TEXT NOT NULL,
  field_type          TEXT NOT NULL CHECK (field_type IN ('text', 'select', 'multiselect', 'number', 'bool', 'date', 'url')),
  applies_to_channels TEXT[] NOT NULL DEFAULT '{}',
  applies_to_level    TEXT NOT NULL DEFAULT 'experience' CHECK (applies_to_level IN ('campaign', 'channel_line', 'experience')),
  is_required         BOOLEAN NOT NULL DEFAULT false,
  sort_order          INT NOT NULL DEFAULT 0,
  options             JSONB,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(client_id, field_code)
);

-- ============================================================
-- CAMPAIGNS
-- ============================================================

CREATE TABLE IF NOT EXISTS campaigns (
  id                  UUID PRIMARY KEY,
  client_id           UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  campaign_number     INT NOT NULL,           -- auto-assigned per client, encoded in codes
  campaign_name       TEXT NOT NULL,
  campaign_objective  TEXT,                  -- freeform (awareness, conversion, etc.)
  program_type        TEXT,                  -- Brand Awareness, Demand Gen, ABM, etc.
  campaign_type       TEXT,                  -- Always-On, Flight, Burst, Evergreen, Test
  target_audience     TEXT,
  region_market       TEXT,
  fiscal_year         TEXT,
  fiscal_quarter      TEXT CHECK (fiscal_quarter IN ('Q1','Q2','Q3','Q4') OR fiscal_quarter IS NULL),
  budget_total        NUMERIC(15,2),
  budget_currency     TEXT DEFAULT 'USD',
  start_date          DATE,
  end_date            DATE,
  brand_sub_brand     TEXT,
  product_service     TEXT,
  agency_partner      TEXT,
  campaign_owner      TEXT,
  experience_counter  INT NOT NULL DEFAULT 0, -- tracks next experience number
  created_by          UUID REFERENCES users(id),
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(client_id, campaign_number)
);

-- Computed status based on dates (used in queries/views)
CREATE OR REPLACE VIEW campaigns_with_status AS
SELECT
  c.*,
  CASE
    WHEN c.start_date IS NULL AND c.end_date IS NULL THEN 'active'
    WHEN c.start_date IS NOT NULL AND c.start_date > CURRENT_DATE THEN 'scheduled'
    WHEN c.end_date IS NOT NULL AND c.end_date < CURRENT_DATE THEN 'inactive'
    ELSE 'active'
  END AS status
FROM campaigns c;

-- ============================================================
-- CHANNEL LINES
-- ============================================================

CREATE TABLE IF NOT EXISTS channel_lines (
  id                UUID PRIMARY KEY,
  campaign_id       UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
  channel_code      TEXT NOT NULL REFERENCES channels(code),
  tactic_type       TEXT,                          -- channel-specific sub-type / tactic
  budget_allocation NUMERIC(15,2),
  field_values      JSONB NOT NULL DEFAULT '{}',   -- channel-level metadata
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(campaign_id, channel_code)               -- one channel line per channel per campaign
);

-- ============================================================
-- EXPERIENCES
-- ============================================================

CREATE TABLE IF NOT EXISTS experiences (
  id               UUID PRIMARY KEY,
  campaign_id      UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
  channel_line_id  UUID NOT NULL REFERENCES channel_lines(id) ON DELETE CASCADE,
  experience_number INT NOT NULL,              -- global per campaign
  generated_code   TEXT NOT NULL UNIQUE,       -- e.g. ACM-PS042:1
  -- Common differentiating fields (also stored in field_values for export flexibility)
  format           TEXT,
  platform         TEXT,
  placement        TEXT,
  size_dimensions  TEXT,
  language         TEXT DEFAULT 'en',
  variant_label    TEXT,
  match_type       TEXT,                         -- Broad / Phrase / Exact (Search channels only)
  field_values     JSONB NOT NULL DEFAULT '{}', -- additional/pack metadata
  created_by       UUID REFERENCES users(id),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(campaign_id, experience_number)
);

ALTER TABLE experiences ADD COLUMN IF NOT EXISTS match_type TEXT;

-- ============================================================
-- EXPORT LOG  (tracks every export for incremental diffing)
-- ============================================================

CREATE TABLE IF NOT EXISTS export_log (
  id            UUID PRIMARY KEY,
  client_id     UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  campaign_id   UUID REFERENCES campaigns(id) ON DELETE CASCADE, -- NULL = full-client export
  format        TEXT NOT NULL,            -- csv | adobe_classification | adobe_cja | ga4
  export_type   TEXT NOT NULL CHECK (export_type IN ('full', 'incremental')),
  row_count     INT NOT NULL DEFAULT 0,
  exported_by   UUID REFERENCES users(id),
  exported_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_export_log_campaign ON export_log(campaign_id, format, exported_at DESC);
CREATE INDEX IF NOT EXISTS idx_export_log_client   ON export_log(client_id, format, exported_at DESC);

-- ============================================================
-- EXPORT TEMPLATES
-- ============================================================

CREATE TABLE IF NOT EXISTS export_templates (
  id             UUID PRIMARY KEY,
  client_id      UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
  name           TEXT NOT NULL,
  export_type    TEXT NOT NULL CHECK (export_type IN (
                   'adobe_classification', 'adobe_classification_sets',
                   'adobe_cja', 'ga4', 'csv', 'bigquery'
                 )),
  field_mappings JSONB NOT NULL DEFAULT '[]',  -- ordered list of {field_code, column_header}
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================

-- Note: updated_at is maintained by application code (SET updated_at = NOW() in PATCH routes)
-- plpgsql triggers are not available on this shared hosting instance.

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_campaigns_client     ON campaigns(client_id);
CREATE INDEX IF NOT EXISTS idx_channel_lines_campaign ON channel_lines(campaign_id);
CREATE INDEX IF NOT EXISTS idx_experiences_campaign  ON experiences(campaign_id);
CREATE INDEX IF NOT EXISTS idx_experiences_channel_line ON experiences(channel_line_id);
CREATE INDEX IF NOT EXISTS idx_experiences_code      ON experiences(generated_code);
CREATE INDEX IF NOT EXISTS idx_client_users_user     ON client_users(user_id);
CREATE INDEX IF NOT EXISTS idx_client_users_client   ON client_users(client_id);
CREATE INDEX IF NOT EXISTS idx_pack_fields_pack      ON pack_fields(pack_id);
CREATE INDEX IF NOT EXISTS idx_client_packs_client   ON client_packs(client_id);