schema.sql

-- GoldenHour database schema (PostgreSQL + PostGIS, via Supabase).
--
-- Run once in the Supabase SQL editor. Stores lat/lng as plain columns (so the
-- seed script can insert them over PostgREST) and derives a PostGIS geography
-- column from them for fast radius queries. Enable Realtime on
-- `confirmation_requests` afterwards so hospital replies push to the frontend.

create extension if not exists postgis;

-- ---------------------------------------------------------------------------
-- hospitals
-- ---------------------------------------------------------------------------
create table if not exists hospitals (
    id                text primary key,            -- e.g. "h1"
    name              text not null,
    lat               double precision not null,
    lng               double precision not null,
    location          geography(Point, 4326)
                          generated always as
                          (ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography) stored,
    departments       text[] not null default '{}',  -- {"trauma","cardiac","general"}
    -- RESERVED, not populated by the app. There is no public real-time bed feed
    -- in India, so the app never writes a fabricated number here. Capacity is
    -- confirmed by a human tapping Accept on the confirmation link. Kept for a
    -- future hospital-HIS integration that could fill these honestly.
    beds_available    integer default null,
    avg_response_rate double precision default null,
    phone             text,
    contact_phone     text
);

-- ---------------------------------------------------------------------------
-- blood_donors
-- ---------------------------------------------------------------------------
create table if not exists blood_donors (
    id            text primary key,                 -- e.g. "d42"
    name          text not null,
    phone         text not null,
    blood_group   text not null,                    -- "O+", "AB-", ...
    lat           double precision not null,
    lng           double precision not null,
    location      geography(Point, 4326)
                      generated always as
                      (ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography) stored,
    last_donated  date,                             -- null = never donated
    sex           text,                             -- 'male' | 'female' | null
    available     boolean not null default true
);

-- ---------------------------------------------------------------------------
-- blood_banks
--
-- Licensed blood banks that replacement donors are routed to. Per-bank unit
-- stock is NOT tracked (no public feed), so the app directs donors to the
-- nearest licensed bank rather than claiming a specific group is in stock.
-- ---------------------------------------------------------------------------
create table if not exists blood_banks (
    id        text primary key,                       -- e.g. "bb1"
    name      text not null,
    lat       double precision not null,
    lng       double precision not null,
    location  geography(Point, 4326)
                  generated always as
                  (ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography) stored,
    city      text
);

-- ---------------------------------------------------------------------------
-- emergency_requests
-- ---------------------------------------------------------------------------
create table if not exists emergency_requests (
    id                  text primary key,           -- request_id, e.g. "req0001"
    emergency_type      text not null,              -- trauma|cardiac|obstetric|general
    blood_group_needed  text not null,
    lat                 double precision not null,
    lng                 double precision not null,
    location            geography(Point, 4326)
                            generated always as
                            (ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography) stored,
    status              text not null default 'pending',  -- pending|confirmed|resolved
    donors_alerted      integer not null default 0,
    donors_responded    integer not null default 0,
    hospitals           jsonb not null default '[]',       -- ranked hospital-card snapshot
    created_at          timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- confirmation_requests
-- ---------------------------------------------------------------------------
create table if not exists confirmation_requests (
    id            text primary key default gen_random_uuid()::text,
    token         text unique not null,             -- the token in the /confirm URL
    emergency_id  text not null references emergency_requests(id) on delete cascade,
    hospital_id   text not null references hospitals(id),
    hospital_name text,                              -- denormalised for the confirm page
    sent_at       timestamptz not null default now(),
    replied_at    timestamptz,                      -- null until the hospital replies
    confirmed     boolean                           -- null=pending, true/false on reply
);

-- ---------------------------------------------------------------------------
-- Spatial indexes — make the radius queries fast.
-- ---------------------------------------------------------------------------
create index if not exists idx_donors_loc on blood_donors using gist (location);
create index if not exists idx_hosp_loc   on hospitals    using gist (location);
create index if not exists idx_banks_loc  on blood_banks  using gist (location);
create index if not exists idx_conf_emergency on confirmation_requests (emergency_id);

-- ---------------------------------------------------------------------------
-- RPC: compatible donors within a radius, off-cooldown, nearest first.
-- Call via supabase.rpc('donors_nearby', {...}). Mirrors donor_service logic.
-- ---------------------------------------------------------------------------
create or replace function donors_nearby(
    p_lat                   double precision,
    p_lng                   double precision,
    p_groups                text[],
    p_radius_m              double precision default 5000,
    p_cooldown_days         integer default 90,
    p_cooldown_days_female  integer default 120
)
returns setof blood_donors
language sql
stable
as $$
    select *
    from blood_donors d
    where d.blood_group = any(p_groups)
      and d.available = true
      and (d.last_donated is null
           or d.last_donated < current_date
               - ((case when d.sex = 'female'
                        then p_cooldown_days_female
                        else p_cooldown_days end) || ' days')::interval)
      and ST_DWithin(
            d.location,
            ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
            p_radius_m)
    order by ST_Distance(
            d.location,
            ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography);
$$;

-- ---------------------------------------------------------------------------
-- RPC: atomic first-accept-wins confirmation.
--
-- A single UPDATE that checks and writes in one statement. Postgres evaluates
-- the NOT EXISTS subquery under the UPDATE's snapshot, so two simultaneous
-- ACCEPT taps cannot both win — only one UPDATE will match rows.
--
-- Returns TRUE if this call won the race (rows updated > 0), FALSE otherwise.
-- Call via supabase.rpc('claim_emergency', {'p_token': ..., 'p_emergency_id': ...}).
-- ---------------------------------------------------------------------------
create or replace function claim_emergency(
    p_token         text,
    p_emergency_id  text
)
returns boolean
language plpgsql
as $$
declare
    v_rows integer;
begin
    update confirmation_requests
    set confirmed  = true,
        replied_at = now()
    where token        = p_token
      and confirmed is null
      and not exists (
          select 1
          from confirmation_requests
          where emergency_id = p_emergency_id
            and confirmed = true
      );
    get diagnostics v_rows = row_count;
    return v_rows > 0;
end;
$$;

-- ---------------------------------------------------------------------------
-- Row-Level Security (RLS)
-- ---------------------------------------------------------------------------
-- Tables created above have RLS DISABLED by default, so the anon key can read
-- and write — convenient for the demo/prototype.
--
-- Recommended production model:
--   * Backend uses the SERVICE-ROLE key (SUPABASE_SERVICE_KEY) — bypasses RLS,
--     so all writes (emergencies, confirmations, donor inserts) go through the
--     trusted server.
--   * Frontend uses the anon key with read-only access to just what it needs to
--     subscribe to via Realtime.
--
-- To lock down, enable RLS and add read-only anon policies, e.g.:
--
--   alter table confirmation_requests enable row level security;
--   create policy "anon reads confirmations"
--     on confirmation_requests for select to anon using (true);
--
--   alter table emergency_requests enable row level security;
--   create policy "anon reads emergencies"
--     on emergency_requests for select to anon using (true);
--
-- (Writes still succeed because the backend connects with the service-role key.)