-- WA Conversion Tracker: skema Fase 1 (PostgreSQL / Supabase)
-- Multi-tenant: satu organisasi = satu perusahaan. Isolasi data ada di 02_rls.sql.
-- Jalankan 01_schema.sql dulu, lalu 02_rls.sql.

create extension if not exists pgcrypto;

-- ENUM ----------------------------------------------------------------------
create type app_role as enum ('owner', 'marketing', 'cs_supervisor', 'cs', 'viewer');
create type lead_stage as enum ('new', 'contacted', 'product_selected', 'checkout', 'payment_info', 'purchased', 'lost');
create type lead_temperature as enum ('cold', 'warm', 'hot');
create type meta_event_name as enum ('ViewContent', 'Lead', 'Contact', 'AddToCart', 'InitiateCheckout', 'AddPaymentInfo', 'Purchase');
create type event_status as enum ('pending', 'sent', 'failed', 'skipped');
create type stage_source as enum ('cs', 'ai_suggested', 'ai_auto', 'system');

-- TENANT, USER, ROLE ---------------------------------------------------------
create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  plan text not null default 'trial',
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  full_name text,
  is_super_admin boolean not null default false,   -- khusus kamu (level platform)
  created_at timestamptz not null default now()
);

create table memberships (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  user_id uuid not null references profiles (id) on delete cascade,
  role app_role not null,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  unique (org_id, user_id)
);
create index on memberships (user_id);

-- KONFIGURASI META, PRODUK, ROTATOR -------------------------------------------
create table meta_connections (
  org_id uuid primary key references organizations (id) on delete cascade,
  dataset_id text not null,                  -- Pixel / Dataset ID
  capi_token_secret_id uuid,                 -- referensi ke Supabase Vault, jangan simpan token mentah
  test_event_code text,
  currency text not null default 'IDR',
  backfill_missed_stages boolean not null default false,
  updated_at timestamptz not null default now()
);

create table products (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  sku text,
  name text not null,
  price numeric(14, 2),
  currency text not null default 'IDR',
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);
create index on products (org_id);

create table rotators (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  slug text not null unique,                 -- dipakai di URL publik rotator
  name text not null,
  product_id uuid references products (id) on delete set null,
  prefilled_template text not null default 'Halo, saya mau tanya produknya. Ref: {ref}',
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table cs_numbers (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  rotator_id uuid not null references rotators (id) on delete cascade,
  user_id uuid references profiles (id) on delete set null,   -- CS pemegang nomor
  label text not null,
  wa_number text not null,                   -- format 62812xxxx (tanpa +)
  weight int not null default 1,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);
create index on cs_numbers (rotator_id);

-- ATRIBUSI: jembatan klik iklan -> chat -----------------------------------------
create table clicks (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  click_id text not null unique,             -- dibuat di LP
  ref_code text not null unique,             -- kode pendek di prefilled message, mis. A7K2
  rotator_id uuid references rotators (id) on delete set null,
  cs_number_id uuid references cs_numbers (id) on delete set null,
  fbclid text,
  fbc text,
  fbp text,
  client_ip inet,
  user_agent text,
  event_source_url text,
  utm_source text,
  utm_medium text,
  utm_campaign text,
  utm_content text,
  utm_term text,
  lead_event_id text,                        -- event_id Lead (dedup Pixel + CAPI)
  clicked_at timestamptz not null default now()
);
create index on clicks (org_id, clicked_at desc);

-- LEAD, TAHAPAN, CHAT, SKOR, ORDER ----------------------------------------------
create table leads (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  source_click_id uuid references clicks (id) on delete set null,
  assigned_to uuid references profiles (id) on delete set null,
  customer_name text,
  phone text,                                -- 62812xxxx
  phone_sha256 text,                         -- hash untuk CAPI (dinormalisasi lalu SHA-256)
  stage lead_stage not null default 'new',
  temperature lead_temperature,
  score smallint check (score between 0 and 100),
  scored_at timestamptz,
  first_message_at timestamptz,
  last_message_at timestamptz,
  closed_at timestamptz,
  lost_reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on leads (org_id, stage);
create index on leads (org_id, assigned_to);
create index on leads (org_id, phone_sha256);

create table lead_stage_history (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  lead_id uuid not null references leads (id) on delete cascade,
  stage lead_stage not null,
  occurred_at timestamptz not null,          -- waktu sinyal terjadi di chat (jadi event_time Meta)
  source stage_source not null,
  confidence numeric(3, 2),
  changed_by uuid references profiles (id) on delete set null,
  note text,
  created_at timestamptz not null default now()
);
create index on lead_stage_history (lead_id, occurred_at);

create table chat_imports (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  lead_id uuid not null references leads (id) on delete cascade,
  uploaded_by uuid references profiles (id) on delete set null,
  raw_text text not null,
  message_count int,
  first_message_at timestamptz,
  last_message_at timestamptz,
  expires_at timestamptz,                    -- kebijakan retensi chat (UU PDP)
  created_at timestamptz not null default now()
);
create index on chat_imports (lead_id);

create table lead_scores (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  lead_id uuid not null references leads (id) on delete cascade,
  chat_import_id uuid references chat_imports (id) on delete set null,
  score smallint not null check (score between 0 and 100),
  temperature lead_temperature not null,
  suggested_stage lead_stage,
  stage_confidence numeric(3, 2),
  signals jsonb not null default '[]',       -- [{type, label, evidence, weight}]
  reasoning text,
  next_action text,
  model text,
  created_at timestamptz not null default now()
);
create index on lead_scores (lead_id, created_at desc);

create table orders (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  lead_id uuid not null references leads (id) on delete cascade,
  product_id uuid references products (id) on delete set null,
  amount numeric(14, 2) not null check (amount > 0),
  currency text not null default 'IDR',
  quantity int not null default 1,
  payment_method text,                       -- transfer / COD / VA / e-wallet
  proof_url text,
  paid_at timestamptz not null,
  confirmed_by uuid references profiles (id) on delete set null,
  created_at timestamptz not null default now()
);
create index on orders (org_id, paid_at desc);

-- ANTREAN EVENT KE META (CAPI) ---------------------------------------------------
create table meta_events (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  source_click_id uuid references clicks (id) on delete set null,
  lead_id uuid references leads (id) on delete set null,
  order_id uuid references orders (id) on delete set null,
  event_name meta_event_name not null,
  event_id text not null,
  event_time timestamptz not null,
  status event_status not null default 'pending',
  attempts int not null default 0,
  payload jsonb,
  response jsonb,
  error text,
  sent_at timestamptz,
  created_at timestamptz not null default now(),
  unique (org_id, event_id)
);
-- satu event per lead (kecuali Purchase, karena bisa repeat order)
create unique index meta_events_once_per_lead on meta_events (lead_id, event_name)
  where lead_id is not null and event_name <> 'Purchase';
-- event level klik (mis. Lead) cukup sekali per klik
create unique index meta_events_once_per_click on meta_events (source_click_id, event_name)
  where source_click_id is not null and lead_id is null;
create index on meta_events (org_id, status, created_at);

create table audit_logs (
  id bigint generated always as identity primary key,
  org_id uuid references organizations (id) on delete cascade,
  actor_id uuid references profiles (id) on delete set null,
  action text not null,
  entity text,
  entity_id uuid,
  meta jsonb,
  created_at timestamptz not null default now()
);

create or replace function set_updated_at() returns trigger
language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end $$;

create trigger leads_updated_at before update on leads
  for each row execute function set_updated_at();