import type { Kysely } from "kysely";
import { sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  await sql`
    create table if not exists kcgm_groups (
      name text primary key,
      sort_order int not null default 0,
      meta text
    );
    
    create table if not exists kcgm_promo_codes (
      code text primary key,
      display_name text,
      main_group text,
      notes text
    );
    
    create table if not exists kcgm_region_map (
      country text primary key,
      region text not null
    );
    
    create table if not exists kcgm_allowed_domains ( domain text primary key );
    
    create table if not exists kcgm_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_kcgm_leads_promo_code on kcgm_leads(promo_code);
    create index if not exists idx_kcgm_leads_logged_at on kcgm_leads(logged_at);
    create index if not exists idx_kcgm_leads_country on kcgm_leads(country);
    
    create table if not exists kcgm_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_kcgm_access_email on kcgm_access(lower(email));
    
    create table if not exists kcgm_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 kcgm_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 kcgm_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_kcgm_audit_ts on kcgm_audit(ts desc);
    
    create table if not exists kcgm_settings ( key text primary key, value text );

    create or replace function kcgm_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 kcgm_promo_codes pc where pc.code = upper(coalesce(p_code,''));
      if g is not null and exists(select 1 from kcgm_groups gr where gr.name=g) then return g; end if;
      if upper(coalesce(p_prefix,''))='RPM' and exists(select 1 from kcgm_groups where name='Reciprocal Partner Clubs (RPM)')
        then return 'Reciprocal Partner Clubs (RPM)'; end if;
      if exists(select 1 from kcgm_groups where name='No Promo Code') then return 'No Promo Code'; end if;
      return g;
    end; $$;
    
    create or replace function kcgm_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 kcgm_region_map where lower(country)=lower(btrim(p_country)) limit 1),'EU_UK_ROW') end;
    $$;
    
    create or replace view kcgm_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, kcgm_resolve_group(l.promo_code,l.promo_prefix) as "group",
      l.logged_at::date as acq, kcgm_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 kcgm_leads l;
  `.execute(db);
}

export async function down(db: Kysely<any>): Promise<void> {
  await sql`
    drop view if exists kcgm_dashboard_leads;
    drop function if exists kcgm_region_for;
    drop function if exists kcgm_resolve_group;
    drop table if exists kcgm_settings;
    drop table if exists kcgm_audit;
    drop table if exists kcgm_export_requests;
    drop table if exists kcgm_access_requests;
    drop table if exists kcgm_access;
    drop table if exists kcgm_leads;
    drop table if exists kcgm_allowed_domains;
    drop table if exists kcgm_region_map;
    drop table if exists kcgm_promo_codes;
    drop table if exists kcgm_groups;
  `.execute(db);
}