-- KCGM Dashboard - PostgreSQL schema (plain Postgres for the custom backend).
-- The backend (apps/api) is the gatekeeper: it authenticates the user and filters
-- every response. (No Supabase grants / no RLS here; access is enforced in the API.)

create table if not exists groups (
  name text primary key,
  sort_order int not null default 0,
  meta text
);
create table if not exists promo_codes (
  code text primary key,           -- full UPPERCASE code (e.g. 'RPM-SQUARECLUB')
  display_name text,
  main_group text,
  notes text
);
create table if not exists region_map (
  country text primary key,
  region text not null
);
create table if not exists allowed_domains ( domain text primary key );

create table if not exists leads (
  account_id text primary key,
  first_name text, last_name text, country text, email text, phone text,
  nationality text, dob date,
  promo_code text, promo_prefix text, promo_suffix text,
  points integer,
  logged_at timestamptz, source_date date, source_month text,
  ingested_at timestamptz default now(),
  contact text generated always as (
    case
      when coalesce(btrim(phone),'')<>'' and coalesce(btrim(email),'')<>'' then 'Phone + Email'
      when coalesce(btrim(phone),'')<>'' then 'Phone Only'
      when coalesce(btrim(email),'')<>'' then 'Email Only'
      else 'Neither' end) stored
);
create index if not exists idx_leads_promo_code on leads(promo_code);
create index if not exists idx_leads_logged_at on leads(logged_at);
create index if not exists idx_leads_country on leads(country);

create table if not exists access (
  id bigint generated always as identity primary key,
  email text not null,
  role text not null check (role in ('admin','viewer','allviewer','noexport')),
  grp text,
  codes text[],
  created_by text,
  created_at timestamptz default now(),
  constraint access_viewer_needs_group check (role <> 'viewer' or grp is not null)
);
create index if not exists idx_access_email on access(lower(email));

create table if not exists access_requests (
  id bigint generated always as identity primary key,
  email text not null, requested_at timestamptz default now(),
  status text not null default 'pending' check (status in ('pending','granted','denied')),
  grp text, handled_by text, handled_at timestamptz
);
create table if not exists export_requests (
  id text primary key, requested_at timestamptz default now(),
  requested_by text not null, scope text, count int, payload jsonb, filename text,
  status text not null default 'pending' check (status in ('pending','approved','denied')),
  handled_by text, handled_at timestamptz
);
create table if not exists audit (
  id bigint generated always as identity primary key,
  ts timestamptz default now(), actor text, action text, subject text, details text
);
create index if not exists idx_audit_ts on audit(ts desc);

-- group resolution (promo code -> group, with RPM / No Promo Code special-casing)
create or replace function resolve_group(p_code text, p_prefix text)
returns text language plpgsql stable as $$
declare g text;
begin
  select pc.main_group into g from promo_codes pc where pc.code = upper(coalesce(p_code,''));
  if g is not null and exists(select 1 from groups gr where gr.name=g) then return g; end if;
  if upper(coalesce(p_prefix,''))='RPM' and exists(select 1 from groups where name='Reciprocal Partner Clubs (RPM)')
    then return 'Reciprocal Partner Clubs (RPM)'; end if;
  if exists(select 1 from groups where name='No Promo Code') then return 'No Promo Code'; end if;
  return g;
end; $$;

create or replace function region_for(p_country text)
returns text language sql stable as $$
  select case when coalesce(btrim(p_country),'')='' then 'No Country Info'
    else coalesce((select region from region_map where lower(country)=lower(btrim(p_country)) limit 1),'EU_UK_ROW') end;
$$;

-- dashboard shape (the API selects from this and filters by the caller's access)
create or replace view dashboard_leads as
select l.account_id as id, l.first_name as first, l.last_name as last, l.country,
  l.nationality as nat, l.dob, l.phone, l.email, l.promo_suffix as promo, l.points,
  l.promo_prefix as prefix, resolve_group(l.promo_code,l.promo_prefix) as "group",
  l.logged_at::date as acq, region_for(l.country) as region, l.contact,
  l.source_date as upload,
  coalesce(l.source_month, to_char(l.logged_at,'YYYY-MM')) as sm
from leads l;
