db/schema.sql

-- PotholeIQ — Supabase schema (PostgreSQL + PostGIS)
-- Run this once in the Supabase SQL editor (Project → SQL Editor → New query → paste → Run).

-- 1. Geospatial extension
create extension if not exists postgis;

-- 2. Cases table.  `geom` is a geography point so radius queries are in METERS.
create table if not exists cases (
  case_id           text primary key,
  status            text,
  submitted_at      timestamptz,
  resolved_at       timestamptz,
  address           text,
  ward              text,
  district          text,
  maintenance_zone  text,
  latitude          double precision,
  longitude         double precision,
  geom              geography(Point, 4326),
  severity_score    numeric,
  severity_level    text,
  pothole_size      text,
  duplicate_status  text,
  duplicate_count   integer default 0,
  linked_case_ids   text,
  weather_risk      text,
  ai_review_status  text,
  reporter_contact  text,
  photo_url         text,
  box_folder_id     text,
  -- crew closeout
  assigned_crew     text,
  repair_method     text,
  materials_used    text,
  labor_hours       numeric,
  crew_notes        text,
  after_photo_url   text,
  signed_by         text,
  signed_at         timestamptz,
  raw               jsonb,
  created_at        timestamptz default now(),
  updated_at        timestamptz default now()
);

-- 3. Indexes — spatial index makes "any pothole within 50m?" an instant lookup
create index if not exists cases_geom_gix   on cases using gist (geom);
create index if not exists cases_status_idx on cases (status);
create index if not exists cases_ward_idx   on cases (ward);
create index if not exists cases_submitted_idx on cases (submitted_at desc);

-- 4. Keep geom in sync with lat/lng automatically
create or replace function cases_set_geom() returns trigger language plpgsql as $$
begin
  if new.latitude is not null and new.longitude is not null then
    new.geom := ST_SetSRID(ST_MakePoint(new.longitude, new.latitude), 4326)::geography;
  end if;
  new.updated_at := now();
  return new;
end $$;

drop trigger if exists trg_cases_set_geom on cases;
create trigger trg_cases_set_geom before insert or update on cases
  for each row execute function cases_set_geom();

-- 5. Duplicate finder: every case within `radius_m` meters of a point (excluding optional case_id)
create or replace function find_nearby_cases(
  p_lat double precision, p_lng double precision,
  p_radius_m double precision, p_exclude text default null
) returns table(case_id text, distance_m double precision,
                severity_level text, status text, submitted_at timestamptz)
language sql stable as $$
  select case_id,
         ST_Distance(geom, ST_SetSRID(ST_MakePoint(p_lng, p_lat),4326)::geography) as distance_m,
         severity_level, status, submitted_at
  from cases
  where geom is not null
    and (p_exclude is null or case_id <> p_exclude)
    and ST_DWithin(geom, ST_SetSRID(ST_MakePoint(p_lng, p_lat),4326)::geography, p_radius_m)
  order by distance_m asc;
$$;