-- Kowa Supabase schema.
-- Run this in your Supabase project's SQL editor (Database -> SQL Editor)
-- before setting SUPABASE_URL / SUPABASE_SERVICE_KEY in config.php.
--
-- These tables have no Row Level Security policies enabled, because the
-- PHP backend talks to Supabase using the service_role key, which
-- bypasses RLS. Never expose the service_role key to a browser or app
-- client — it stays in config.php on your server only. If you later
-- build a customer-facing dashboard directly against Supabase using the
-- anon key, add RLS policies at that point.

create extension if not exists pgcrypto;

create table if not exists traders (
    phone text primary key,
    name text,
    language text,
    wares text,
    state text default 'NEW',
    pending_sale_draft jsonb,
    created_at timestamptz default now()
);

create table if not exists sales (
    id uuid primary key default gen_random_uuid(),
    trader_id text references traders(phone),
    item text,
    amount numeric,
    customer_name text,
    customer_phone text,
    status text default 'owing',
    promised_date date,
    reminded_at timestamptz,
    created_at timestamptz default now()
);

create table if not exists open_reminders (
    customer_phone text primary key,
    sale_id uuid references sales(id),
    created_at timestamptz default now()
);

create table if not exists ajo_groups (
    id uuid primary key default gen_random_uuid(),
    name text,
    weekly_amount numeric,
    cycle_length_weeks int,
    current_week int default 1,
    created_at timestamptz default now()
);

create table if not exists ajo_members (
    id bigserial primary key,
    group_id uuid references ajo_groups(id),
    phone text,
    payout_week int
);

create table if not exists ajo_contributions (
    id bigserial primary key,
    group_id uuid references ajo_groups(id),
    week int,
    trader_phone text,
    amount numeric,
    paid_at timestamptz default now(),
    unique (group_id, week, trader_phone)
);

create table if not exists ajo_payouts (
    id bigserial primary key,
    group_id uuid references ajo_groups(id),
    week int,
    trader_phone text,
    gross numeric,
    fee numeric,
    net numeric,
    paystack_ref text,
    paid_at timestamptz default now()
);

create index if not exists idx_sales_trader on sales(trader_id);
create index if not exists idx_sales_status_promised on sales(status, promised_date);
create index if not exists idx_ajo_members_group on ajo_members(group_id);
create index if not exists idx_ajo_contrib_group_week on ajo_contributions(group_id, week);