-- Thebe schema (rapid scaffold)
-- Run in Supabase SQL editor.

create extension if not exists "uuid-ossp";

-- Profiles (role-based access)
create table if not exists public.profiles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  email text,
  full_name text,
  role text not null default 'borrower_personal',
  created_at timestamptz not null default now()
);

-- Auto-create profile on signup
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (user_id, email, full_name)
  values (new.id, new.email, coalesce(new.raw_user_meta_data->>'full_name',''));
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Loan products (global)
create table if not exists public.loan_products (
  id uuid primary key default uuid_generate_v4(),
  name text not null,
  type_code text not null unique,
  description text,
  active boolean not null default true,
  created_at timestamptz not null default now()
);

-- Issuers
create table if not exists public.issuers (
  id uuid primary key default uuid_generate_v4(),
  name text not null,
  status text not null default 'pending', -- pending/approved/suspended
  tier text not null default 'standard',
  billing_model_json jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists public.issuer_users (
  issuer_id uuid references public.issuers(id) on delete cascade,
  user_id uuid references auth.users(id) on delete cascade,
  role text not null default 'agent',
  primary key (issuer_id, user_id)
);

create table if not exists public.issuer_products (
  id uuid primary key default uuid_generate_v4(),
  issuer_id uuid not null references public.issuers(id) on delete cascade,
  loan_product_id uuid not null references public.loan_products(id) on delete cascade,
  min_amount numeric not null default 0,
  max_amount numeric not null default 999999999,
  min_term int not null default 1,
  max_term int not null default 120,
  rate_min numeric,
  rate_max numeric,
  fees_json jsonb not null default '{}'::jsonb,
  eligibility_rules_json jsonb not null default '{}'::jsonb,
  required_docs_json jsonb not null default '[]'::jsonb,
  active boolean not null default true,
  created_at timestamptz not null default now()
);

-- Borrower KYC
create table if not exists public.kyc_cases (
  id uuid primary key default uuid_generate_v4(),
  user_id uuid not null references auth.users(id) on delete cascade,
  level text not null default 'standard', -- standard/enhanced
  status text not null default 'pending', -- pending/approved/rejected
  risk_score int not null default 0,
  reviewed_by uuid references auth.users(id),
  review_notes text,
  created_at timestamptz not null default now()
);

create table if not exists public.kyc_documents (
  id uuid primary key default uuid_generate_v4(),
  kyc_case_id uuid not null references public.kyc_cases(id) on delete cascade,
  doc_type text not null,
  storage_key text not null,
  status text not null default 'uploaded', -- uploaded/verified/rejected
  meta_json jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

-- Applications
create table if not exists public.applications (
  id uuid primary key default uuid_generate_v4(),
  user_id uuid not null references auth.users(id) on delete cascade,
  loan_product_id uuid not null references public.loan_products(id),
  borrower_type text not null, -- personal/business
  amount_requested numeric not null,
  term_requested int not null,
  purpose_text text,
  status text not null default 'draft', -- draft/submitted/routed/reviewing/offers/accepted/funded/rejected
  submitted_at timestamptz,
  created_at timestamptz not null default now()
);

create table if not exists public.application_documents (
  id uuid primary key default uuid_generate_v4(),
  application_id uuid not null references public.applications(id) on delete cascade,
  doc_type text not null,
  storage_key text not null,
  status text not null default 'uploaded',
  created_at timestamptz not null default now()
);

create table if not exists public.application_routing (
  id uuid primary key default uuid_generate_v4(),
  application_id uuid not null references public.applications(id) on delete cascade,
  issuer_id uuid not null references public.issuers(id) on delete cascade,
  status text not null default 'sent', -- sent/viewed/requested_info/declined/offered
  sent_at timestamptz,
  last_action_at timestamptz,
  unique (application_id, issuer_id)
);

create table if not exists public.offers (
  id uuid primary key default uuid_generate_v4(),
  application_id uuid not null references public.applications(id) on delete cascade,
  issuer_id uuid not null references public.issuers(id) on delete cascade,
  amount_approved numeric not null,
  term int not null,
  interest_rate numeric,
  apr numeric,
  fees_json jsonb not null default '{}'::jsonb,
  repayment_schedule_json jsonb not null default '[]'::jsonb,
  status text not null default 'active', -- active/accepted/expired/withdrawn
  created_at timestamptz not null default now()
);

create table if not exists public.loans (
  id uuid primary key default uuid_generate_v4(),
  offer_id uuid not null references public.offers(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  issuer_id uuid not null references public.issuers(id) on delete cascade,
  principal numeric not null,
  balance numeric not null,
  status text not null default 'pending_funding', -- pending_funding/active/closed/defaulted
  funded_at timestamptz,
  contract_ref text,
  created_at timestamptz not null default now()
);

create table if not exists public.repayment_schedules (
  id uuid primary key default uuid_generate_v4(),
  loan_id uuid not null references public.loans(id) on delete cascade,
  due_date date not null,
  amount_due numeric not null,
  status text not null default 'due', -- due/paid/overdue
  created_at timestamptz not null default now()
);

create table if not exists public.payments (
  id uuid primary key default uuid_generate_v4(),
  loan_id uuid not null references public.loans(id) on delete cascade,
  method text not null, -- card/mobile/bank/cash/manual
  amount numeric not null,
  status text not null default 'initiated', -- initiated/paid/failed/pending_review
  provider_ref text,
  receipt_storage_key text,
  paid_at timestamptz,
  created_at timestamptz not null default now()
);

-- Support
create table if not exists public.tickets (
  id uuid primary key default uuid_generate_v4(),
  user_id uuid not null references auth.users(id) on delete cascade,
  category text not null default 'general',
  status text not null default 'open',
  priority text not null default 'normal',
  created_at timestamptz not null default now()
);

create table if not exists public.ticket_messages (
  id uuid primary key default uuid_generate_v4(),
  ticket_id uuid not null references public.tickets(id) on delete cascade,
  sender_role text not null,
  message text not null,
  attachments_json jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now()
);

-- Audit logs
create table if not exists public.audit_logs (
  id uuid primary key default uuid_generate_v4(),
  actor_user_id uuid references auth.users(id),
  actor_role text,
  action text not null,
  entity_type text,
  entity_id text,
  ip text,
  created_at timestamptz not null default now()
);

-- Convenience view for routing
create or replace view public.issuer_products_view as
select
  ip.id as issuer_product_id,
  ip.issuer_id,
  i.status as issuer_status,
  ip.loan_product_id,
  ip.min_amount, ip.max_amount,
  ip.min_term, ip.max_term,
  ip.active
from public.issuer_products ip
join public.issuers i on i.id = ip.issuer_id;

-- RLS
alter table public.profiles enable row level security;
alter table public.loan_products enable row level security;
alter table public.issuers enable row level security;
alter table public.issuer_users enable row level security;
alter table public.issuer_products enable row level security;
alter table public.kyc_cases enable row level security;
alter table public.kyc_documents enable row level security;
alter table public.applications enable row level security;
alter table public.application_documents enable row level security;
alter table public.application_routing enable row level security;
alter table public.offers enable row level security;
alter table public.loans enable row level security;
alter table public.repayment_schedules enable row level security;
alter table public.payments enable row level security;
alter table public.tickets enable row level security;
alter table public.ticket_messages enable row level security;
alter table public.audit_logs enable row level security;

-- Basic policies
-- Profiles: user can read own profile; admin can read all (via service role in server code)
create policy "profiles_select_own" on public.profiles
  for select using (auth.uid() = user_id);

create policy "profiles_update_own" on public.profiles
  for update using (auth.uid() = user_id);

-- Loan products: public readable
create policy "loan_products_public_read" on public.loan_products
  for select using (true);

-- Applications: borrower owns
create policy "applications_own" on public.applications
  for select using (auth.uid() = user_id);
create policy "applications_insert_own" on public.applications
  for insert with check (auth.uid() = user_id);
create policy "applications_update_own" on public.applications
  for update using (auth.uid() = user_id);

-- Routing: borrower can view routes for their applications
create policy "routing_borrower_view" on public.application_routing
  for select using (
    exists (
      select 1 from public.applications a
      where a.id = application_id and a.user_id = auth.uid()
    )
  );

-- Issuers: issuer users can view their issuer record (simple)
create policy "issuer_users_select" on public.issuer_users
  for select using (auth.uid() = user_id);

create policy "issuers_select_for_issuer_users" on public.issuers
  for select using (
    exists (select 1 from public.issuer_users iu where iu.issuer_id = id and iu.user_id = auth.uid())
    or true -- public can see list later; keep open for now
  );

-- Issuer products: issuer users can view their products; public can read active products for routing
create policy "issuer_products_read" on public.issuer_products
  for select using (true);

-- KYC: borrower can see own cases & docs
create policy "kyc_cases_own" on public.kyc_cases
  for select using (auth.uid() = user_id);
create policy "kyc_cases_insert_own" on public.kyc_cases
  for insert with check (auth.uid() = user_id);

create policy "kyc_docs_own" on public.kyc_documents
  for select using (
    exists (select 1 from public.kyc_cases k where k.id = kyc_case_id and k.user_id = auth.uid())
  );

-- Tickets: borrower owns
create policy "tickets_own" on public.tickets
  for select using (auth.uid() = user_id);
create policy "tickets_insert_own" on public.tickets
  for insert with check (auth.uid() = user_id);

create policy "ticket_messages_own" on public.ticket_messages
  for select using (
    exists (select 1 from public.tickets t where t.id = ticket_id and t.user_id = auth.uid())
  );

-- Offers/loans/payments: borrower can view by ownership (basic)
create policy "offers_borrower_view" on public.offers
  for select using (
    exists (select 1 from public.applications a where a.id = application_id and a.user_id = auth.uid())
  );

create policy "loans_borrower_view" on public.loans
  for select using (auth.uid() = user_id);

create policy "payments_borrower_view" on public.payments
  for select using (
    exists (select 1 from public.loans l where l.id = loan_id and l.user_id = auth.uid())
  );

-- Seed loan products (Botswana-first)
insert into public.loan_products (name, type_code, description) values
  ('Personal Loan','personal','Personal borrowing for everyday needs'),
  ('Auto Loan','auto','Vehicle financing'),
  ('Education Loan','education','Tuition and education-related costs'),
  ('Home Loan','home','Home purchase or improvement'),
  ('Credit Card','credit_card','Credit card style revolving facility'),
  ('Line of Credit','loc','Revolving line of credit'),
  ('Business Loan','business','Business and enterprise borrowing'),
  ('Purchase Order Finance','po_finance','Financing against purchase orders')
on conflict (type_code) do nothing;
