waseel/scripts/seed-db.mjs

// Creates and seeds the Waseel database tables (local/self-hosted PostgreSQL).
// Usage: node scripts/seed-db.mjs   (reads DATABASE_URL from .env)

import pg from "pg";
import { readFileSync } from "fs";

const env = readFileSync(new URL("../.env", import.meta.url), "utf8");
const databaseUrl = env
  .split("\n")
  .find((l) => l.startsWith("DATABASE_URL="))
  ?.split("=")
  .slice(1)
  .join("=")
  .trim()
  .replace(/^"|"$/g, "");

if (!databaseUrl) {
  console.error("DATABASE_URL not found in .env");
  process.exit(1);
}

const pool = new pg.Pool({ connectionString: databaseUrl });

const sql = async (strings, ...values) => {
  const text = strings.reduce(
    (acc, chunk, i) => acc + chunk + (i < values.length ? `$${i + 1}` : ""),
    "",
  );
  const result = await pool.query(text, values);
  return result.rows;
};

// Migrate databases created during the Clerk era (clerk_id column, no UNIQUE email).
const clerkCol = await sql`
  SELECT 1 FROM information_schema.columns
  WHERE table_name = 'users' AND column_name = 'clerk_id'
`;
if (clerkCol.length > 0) {
  await sql`ALTER TABLE users DROP COLUMN clerk_id`;
  console.log("Dropped legacy clerk_id column from users.");
}

// Rebuild legacy tables where users.id / rides.user_id are not UUID.
const idType = await sql`
  SELECT data_type FROM information_schema.columns
  WHERE table_name = 'users' AND column_name = 'id'
`;
if (idType.length > 0 && idType[0]?.data_type !== "uuid") {
  console.log(
    "Detected legacy non-UUID users schema, rebuilding users/rides...",
  );
  await sql`DROP TABLE IF EXISTS rides`;
  await sql`DROP TABLE IF EXISTS users CASCADE`;
  console.log("Legacy users/rides tables dropped (test data only).");
}

await sql`CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(20),
  password_hash TEXT,
  google_sub TEXT UNIQUE,
  email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  role VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

// For databases created before self-hosted auth existed.
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20)`;
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20)`;
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS password_hash TEXT`;
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS google_sub TEXT`;
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN NOT NULL DEFAULT FALSE`;

// email must be UNIQUE for ON CONFLICT (email) upserts in register+api.ts.
const emailUnique = await sql`
  SELECT 1 FROM pg_constraint
  WHERE conrelid = 'users'::regclass AND contype = 'u'
    AND conkey @> ARRAY[
      (SELECT attnum::smallint FROM pg_attribute
       WHERE attrelid = 'users'::regclass AND attname = 'email')
    ]
`;
if (emailUnique.length === 0) {
  await sql`ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email)`;
  console.log("Added unique constraint on users.email.");
}

await sql`CREATE TABLE IF NOT EXISTS email_verification_codes (
  email VARCHAR(255) PRIMARY KEY,
  code_hash TEXT NOT NULL,
  attempts INTEGER NOT NULL DEFAULT 0,
  expires_at TIMESTAMP NOT NULL
)`;

await sql`CREATE TABLE IF NOT EXISTS password_reset_codes (
  email VARCHAR(255) PRIMARY KEY,
  code_hash TEXT NOT NULL,
  attempts INTEGER NOT NULL DEFAULT 0,
  expires_at TIMESTAMP NOT NULL
)`;

await sql`CREATE TABLE IF NOT EXISTS drivers (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  profile_image_url TEXT,
  car_image_url TEXT,
  car_seats INTEGER NOT NULL,
  rating NUMERIC(2,1) NOT NULL
)`;

// Driver profiles are linked to a user account (in-app driver onboarding) and
// carry the live state the dispatch engine needs.
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES users(id) ON DELETE SET NULL`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS service VARCHAR(20) NOT NULL DEFAULT 'car'`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS latitude DOUBLE PRECISION`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS longitude DOUBLE PRECISION`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS online BOOLEAN NOT NULL DEFAULT FALSE`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS last_seen TIMESTAMPTZ`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS car_model VARCHAR(100)`;

// Direction and speed of travel, from the same GPS fix as the position.
//
// A map that only knows where cars are can only redraw them somewhere else a
// few seconds later, which reads as teleporting. Knowing which way a car
// points — and whether it is moving at all — is what turns a scatter of dots
// into visible traffic, and lets a parked car be drawn as parked rather than
// as one pointing an arbitrary direction.
//
// heading is degrees clockwise from true north (0-359), NULL when the device
// can't determine it (common when stationary). speed is km/h, rounded.
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS heading SMALLINT`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS speed_kph SMALLINT`;
// One driver profile per user account (legacy seed rows have NULL user_id).
await sql`CREATE UNIQUE INDEX IF NOT EXISTS drivers_user_id_key ON drivers(user_id) WHERE user_id IS NOT NULL`;
await sql`CREATE INDEX IF NOT EXISTS drivers_service_online_idx ON drivers(service, online)`;

// Driver vetting. A profile is not a licence to drive: onboarding records the
// driver's credentials and leaves the row 'pending', and only an owner review
// moves it to 'approved'. Dispatch and the rider map both filter on this, so
// an unapproved profile is invisible to riders and never offered a ride —
// which is what stops any account from self-declaring role='driver' and
// receiving real riders' names, phone numbers and home addresses.
const hadApproval = await sql`
  SELECT 1 FROM information_schema.columns
  WHERE table_name = 'drivers' AND column_name = 'approval_status'
`;

await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS approval_status VARCHAR(20) NOT NULL DEFAULT 'pending'`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS license_number VARCHAR(60)`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS license_expiry DATE`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS national_id VARCHAR(60)`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS plate_number VARCHAR(20)`;
// Document scans. No storage backend is wired up yet, so onboarding collects
// the numbers above and leaves these NULL; they exist so adding uploads later
// is a client change rather than another migration.
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS license_image_url TEXT`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS vehicle_reg_image_url TEXT`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS id_image_url TEXT`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS submitted_at TIMESTAMPTZ`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS reviewed_at TIMESTAMPTZ`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS rejection_reason TEXT`;

const approvalCheck = await sql`
  SELECT 1 FROM pg_constraint
  WHERE conrelid = 'drivers'::regclass AND conname = 'drivers_approval_status_check'
`;
if (approvalCheck.length === 0) {
  await sql`
    ALTER TABLE drivers ADD CONSTRAINT drivers_approval_status_check CHECK (
      approval_status IN ('pending', 'approved', 'rejected', 'suspended')
    )
  `;
}
await sql`CREATE INDEX IF NOT EXISTS drivers_approval_idx ON drivers(approval_status)`;

// /(api)/driver/photo serves a profile photo only when some driver row points
// at that name, and it is hit once per uncached avatar across the rider app.
// This turns that check into an index lookup instead of a scan of the fleet.
await sql`CREATE INDEX IF NOT EXISTS drivers_profile_image_idx ON drivers(profile_image_url)`;

// Grandfather profiles that predate vetting. Retroactively suspending drivers
// who were already working would lock every existing account out of the app;
// the gate applies to everyone who onboards from here on.
if (hadApproval.length === 0) {
  const grandfathered = await sql`
    UPDATE drivers SET approval_status = 'approved', reviewed_at = CURRENT_TIMESTAMP
    WHERE user_id IS NOT NULL
    RETURNING id
  `;
  if (grandfathered.length > 0) {
    console.log(
      `Driver vetting added. Grandfathered ${grandfathered.length} existing driver profile(s) as approved.`,
    );
  }
}

await sql`CREATE TABLE IF NOT EXISTS rides (
  ride_id SERIAL PRIMARY KEY,
  origin_address TEXT NOT NULL,
  destination_address TEXT NOT NULL,
  origin_latitude DOUBLE PRECISION NOT NULL,
  origin_longitude DOUBLE PRECISION NOT NULL,
  destination_latitude DOUBLE PRECISION NOT NULL,
  destination_longitude DOUBLE PRECISION NOT NULL,
  ride_time INTEGER NOT NULL,
  fare_price INTEGER NOT NULL,
  payment_status VARCHAR(50) NOT NULL,
  driver_id INTEGER NOT NULL REFERENCES drivers(id),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  payment_order_id TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

// Link a ride to the server-authoritative payment order that paid for it.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS payment_order_id TEXT`;

// Ride lifecycle state machine:
//   requested -> accepted -> arrived -> en_route -> completed
//   requested -> expired                  (nobody offered in time)
//   requested|accepted|arrived -> cancelled
// A requested ride has no driver yet: it is broadcast to every eligible driver
// nearby, they volunteer for it in ride_offers, and it gets a driver_id at the
// moment the rider picks one — so driver_id must be nullable.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'requested'`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS service VARCHAR(20) NOT NULL DEFAULT 'car'`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS completed_at TIMESTAMPTZ`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS cancelled_at TIMESTAMPTZ`;

// Per-transition timestamps. These are what makes a ride auditable after the
// fact: how long the rider waited for a match, how long the driver took to
// reach the pickup, and how long the trip itself ran.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS accepted_at TIMESTAMPTZ`;

// When the request was announced to nearby drivers. Purely a latch: the
// broadcast is triggered from the rider's status poll as well as from
// creation, and without somewhere to record "already announced" every poll
// would buzz every phone in the neighbourhood again.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS broadcast_at TIMESTAMPTZ`;

// Superseded by the broadcast model, in which a ride is assigned exactly once
// (when the rider picks an offer) and there is no driver-claims-then-rider-
// confirms window to time. Dropped rather than left in place so no query can
// go on reading a column nothing writes.
await sql`ALTER TABLE rides DROP COLUMN IF EXISTS matched_at`;
await sql`ALTER TABLE rides DROP COLUMN IF EXISTS rematch_at`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS arrived_at TIMESTAMPTZ`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS started_at TIMESTAMPTZ`;

// Pickup verification. Generated when a driver accepts, shown to the rider,
// spoken to the driver at the car; the driver must enter it to start the trip.
// This is the handshake that proves the right rider got into the right car.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS pickup_code VARCHAR(6)`;

// Who ended the ride and why — 'rider', 'driver' or 'system' (no driver found).
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS cancelled_by VARCHAR(10)`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS cancellation_reason TEXT`;

// Cash settlement: a cash ride is 'cash' (owed) until the driver confirms
// collection at drop-off, which flips payment_status to 'cash_collected'.
//
// A request starts at 'pending': the rider now chooses cash or card when they
// pick a driver, not before they are shown any, so a ride exists for a while
// with no payment decision attached to it. Nothing settles on a pending ride —
// it can only leave that state by being picked (cash / paid) or by dying.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS cash_collected_at TIMESTAMPTZ`;

// The fare split, stamped when a ride completes.
//
// fare_price is what the rider pays; these two are how it divides. They are
// stored rather than derived because the commission rate will change, and a
// ride has to keep the terms it was completed under — recomputing history at
// today's rate would silently rewrite what a driver was owed last month. The
// rate itself is stored alongside for exactly that audit.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS platform_fee_cents INTEGER`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS driver_payout_cents INTEGER`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS commission_rate NUMERIC(5,4)`;

// Settlement: has each side of the split actually changed hands?
//
// Which side is outstanding depends on who physically held the fare. On a card
// ride the company already has its fee and owes the driver; on a cash ride the
// driver already has their payout and owes the company its fee. NULL means
// "still owed", a timestamp means "handed over" — see lib/settlement.ts.
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS platform_fee_settled_at TIMESTAMPTZ`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS driver_payout_settled_at TIMESTAMPTZ`;
await sql`ALTER TABLE rides ADD COLUMN IF NOT EXISTS settlement_note TEXT`;

// Drives the "what is still owed?" queries the admin ledger runs on every
// load. Partial, so it stays small as settled history accumulates.
await sql`
  CREATE INDEX IF NOT EXISTS rides_unsettled_idx ON rides(driver_id)
  WHERE status = 'completed'
    AND (platform_fee_settled_at IS NULL OR driver_payout_settled_at IS NULL)
`;

// Backfill rides that completed before the split existed. Only completed rides
// get a split: a cancelled ride earns nobody anything, and leaving those NULL
// keeps "was there ever a payout here?" answerable.
const backfilled = await sql`
  UPDATE rides
  SET platform_fee_cents = ROUND(fare_price * 0.20),
      driver_payout_cents = fare_price - ROUND(fare_price * 0.20),
      commission_rate = 0.20
  WHERE status = 'completed' AND driver_payout_cents IS NULL
  RETURNING ride_id
`;
if (backfilled.length > 0) {
  console.log(
    `Backfilled the fare split on ${backfilled.length} completed ride(s) at 20%.`,
  );
}

// Seed settlement state for rides that completed before it was tracked, using
// the same rule new rides get: whoever held the fare is already settled, the
// other side is outstanding. Scoped to rows where BOTH are NULL so it can
// never overwrite a settlement someone has since recorded.
const settlementSeeded = await sql`
  UPDATE rides
  SET platform_fee_settled_at =
        CASE WHEN payment_status = 'paid' THEN COALESCE(completed_at, CURRENT_TIMESTAMP) END,
      driver_payout_settled_at =
        CASE WHEN payment_status = 'cash_collected' THEN COALESCE(completed_at, CURRENT_TIMESTAMP) END
  WHERE status = 'completed'
    AND payment_status IN ('paid', 'cash_collected')
    AND platform_fee_settled_at IS NULL
    AND driver_payout_settled_at IS NULL
  RETURNING ride_id
`;
if (settlementSeeded.length > 0) {
  console.log(
    `Seeded settlement state on ${settlementSeeded.length} completed ride(s).`,
  );
}

await sql`
  ALTER TABLE rides ALTER COLUMN driver_id DROP NOT NULL
`;

// Constrain status to the state machine above, so a bad write fails loudly
// instead of stranding a ride in a state no screen renders.
//
// Rebuilt whenever the stored definition disagrees with this list, rather than
// only created when missing: the set of states has changed once already, and a
// database still carrying the previous constraint rejects every write in the
// new state — a failure that shows up as "ride won't dispatch" long after the
// deploy that caused it.
const RIDE_STATUSES = [
  "requested",
  "accepted",
  "arrived",
  "en_route",
  "completed",
  "cancelled",
  "expired",
];

const statusList = RIDE_STATUSES.map((state) => `'${state}'`).join(",");
const [existingCheck] = await sql`
  SELECT pg_get_constraintdef(oid) AS def FROM pg_constraint
  WHERE conrelid = 'rides'::regclass AND conname = 'rides_status_check'
`;
const constraintStates = (existingCheck?.def ?? "").match(/'([a-z_]+)'/g) ?? [];
const constraintMatches =
  constraintStates.length === RIDE_STATUSES.length &&
  RIDE_STATUSES.every((state) => constraintStates.includes(`'${state}'`));

if (!constraintMatches) {
  // Any row in a state the new list doesn't know about is normalised first,
  // otherwise ADD CONSTRAINT fails on existing data.
  await sql`
    UPDATE rides SET status = 'completed'
    WHERE status <> ALL(${`{${RIDE_STATUSES.join(",")}}`}::text[])
  `;
  await sql`ALTER TABLE rides DROP CONSTRAINT IF EXISTS rides_status_check`;
  // The state list is built in JS, so this one statement goes through the pool
  // directly rather than the tagged helper (which only interpolates values,
  // and a CHECK body is not a value).
  await pool.query(
    `ALTER TABLE rides ADD CONSTRAINT rides_status_check CHECK (status IN (${statusList}))`,
  );
  console.log("Rebuilt rides.status check constraint.");
}

await sql`CREATE INDEX IF NOT EXISTS rides_user_id_idx ON rides(user_id)`;
await sql`CREATE INDEX IF NOT EXISTS rides_driver_id_idx ON rides(driver_id)`;
await sql`CREATE INDEX IF NOT EXISTS rides_status_idx ON rides(status)`;
// Drives the "does this rider/driver have something in flight?" lookups that
// every poll makes; a partial index keeps it tiny as history grows.
// Rebuilt unconditionally for the same reason as the constraint: a partial
// index whose predicate lists states that no longer exist silently stops
// covering the rows these lookups care about.
await sql`DROP INDEX IF EXISTS rides_active_user_idx`;
await sql`DROP INDEX IF EXISTS rides_active_driver_idx`;
await sql`DROP INDEX IF EXISTS rides_open_requests_idx`;
await sql`
  CREATE INDEX IF NOT EXISTS rides_active_user_idx ON rides(user_id)
  WHERE status IN ('requested','accepted','arrived','en_route')
`;
await sql`
  CREATE INDEX IF NOT EXISTS rides_active_driver_idx ON rides(driver_id)
  WHERE status IN ('accepted','arrived','en_route')
`;
// Every online driver polls "what is open near me?" every few seconds, and
// that scan is the one query whose cost scales with ride history rather than
// with how busy the city is. Partial, so it only ever holds live requests.
await sql`
  CREATE INDEX IF NOT EXISTS rides_open_requests_idx ON rides(service, created_at)
  WHERE status = 'requested'
`;

// Server-authoritative record of each card payment intent. The client never
// supplies payment_status or the successIndicator; both live here and are
// verified against the gateway before an order can be consumed for a ride.
await sql`CREATE TABLE IF NOT EXISTS payment_orders (
  order_id TEXT PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  amount_cents INTEGER NOT NULL,
  currency VARCHAR(8) NOT NULL DEFAULT 'USD',
  driver_id INTEGER REFERENCES drivers(id),
  origin_address TEXT,
  destination_address TEXT,
  origin_latitude DOUBLE PRECISION,
  origin_longitude DOUBLE PRECISION,
  destination_latitude DOUBLE PRECISION,
  destination_longitude DOUBLE PRECISION,
  ride_time INTEGER,
  success_indicator TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  paid_at TIMESTAMPTZ
)`;

await sql`CREATE INDEX IF NOT EXISTS payment_orders_user_id_idx ON payment_orders(user_id)`;

// Dispatch: a row here is a driver volunteering for an open request. The
// direction matters — the server broadcasts the request to everyone nearby,
// and these are the answers coming back, which is why a ride can have several
// at once and why the rider is the one who resolves them.
//
//   offered   — the driver wants this ride and is waiting on the rider
//   accepted  — the rider picked this driver; the ride is theirs
//   passed    — the rider picked somebody else
//   withdrawn — the driver pulled their own offer before being picked
//   expired   — the request ran out of time before anyone was picked
//
// One offer per (ride, driver): a driver either wants a job or doesn't, and a
// unique index is what makes a double-tap on Offer harmless instead of putting
// the same driver in the rider's list twice.
await sql`CREATE TABLE IF NOT EXISTS ride_offers (
  id SERIAL PRIMARY KEY,
  ride_id INTEGER NOT NULL REFERENCES rides(ride_id) ON DELETE CASCADE,
  driver_id INTEGER NOT NULL REFERENCES drivers(id),
  status VARCHAR(20) NOT NULL DEFAULT 'offered',
  offered_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  responded_at TIMESTAMPTZ
)`;

// How far the driver was from the pickup when they offered, in metres.
// Recorded at offer time rather than read live so the rider is comparing the
// same snapshot for every offer in their list, and so an offer doesn't
// silently improve while they read it.
await sql`ALTER TABLE ride_offers ADD COLUMN IF NOT EXISTS pickup_distance_m INTEGER`;

await sql`CREATE INDEX IF NOT EXISTS ride_offers_driver_status_idx ON ride_offers(driver_id, status)`;
await sql`CREATE INDEX IF NOT EXISTS ride_offers_ride_idx ON ride_offers(ride_id)`;

// Deduplicate any legacy double-offers before the unique index lands.
await sql`
  DELETE FROM ride_offers a USING ride_offers b
  WHERE a.ride_id = b.ride_id AND a.driver_id = b.driver_id AND a.id > b.id
`;
await sql`
  CREATE UNIQUE INDEX IF NOT EXISTS ride_offers_ride_driver_idx
  ON ride_offers(ride_id, driver_id)
`;

// In-app chat between a rider and their assigned driver, scoped to a ride.
// Sender identity is polymorphic: a rider is a users.id (UUID) and a driver is
// a drivers.id (INT) — they live in different tables — so two nullable FK
// columns + a CHECK enforce exactly one sender per row and keep referential
// integrity. The monotonic `id` doubles as the polling cursor.
await sql`CREATE TABLE IF NOT EXISTS messages (
  id BIGSERIAL PRIMARY KEY,
  ride_id INTEGER NOT NULL REFERENCES rides(ride_id) ON DELETE CASCADE,
  sender_type VARCHAR(10) NOT NULL CHECK (sender_type IN ('rider','driver')),
  sender_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  sender_driver_id INTEGER REFERENCES drivers(id) ON DELETE SET NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT messages_sender_xor CHECK (
    (sender_user_id IS NULL) <> (sender_driver_id IS NULL)
  )
)`;
await sql`CREATE INDEX IF NOT EXISTS messages_ride_since_idx ON messages(ride_id, id)`;

// In-app WebRTC audio-call signaling + lifecycle. Both parties are
// denormalized onto the row (user_id + driver_id) so authorization is one
// equality check and the call survives a driver reassignment. Non-trickle ICE:
// the full SDP (offer/answer with gathered candidates embedded) is stored as
// text so signaling can be a few DB-row round-trips polled by both sides — no
// WebSocket. The JSONB ice columns are a debugging fallback, not required.
await sql`CREATE TABLE IF NOT EXISTS calls (
  id BIGSERIAL PRIMARY KEY,
  ride_id INTEGER NOT NULL REFERENCES rides(ride_id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  driver_id INTEGER NOT NULL REFERENCES drivers(id),
  caller_type VARCHAR(10) NOT NULL CHECK (caller_type IN ('rider','driver')),
  status VARCHAR(12) NOT NULL DEFAULT 'ringing',
  sdp_offer TEXT,
  sdp_answer TEXT,
  offer_ice JSONB,
  answer_ice JSONB,
  started_at TIMESTAMPTZ,
  ended_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT calls_status_check CHECK (
    status IN ('ringing','answered','ended','declined','missed')
  )
)`;
await sql`CREATE INDEX IF NOT EXISTS calls_ride_idx ON calls(ride_id, created_at)`;
await sql`CREATE INDEX IF NOT EXISTS calls_driver_status_idx ON calls(driver_id, status)`;
await sql`CREATE INDEX IF NOT EXISTS calls_user_status_idx ON calls(user_id, status)`;

// Two-way ratings, one row per (ride, rater). A rider rates the driver and a
// driver rates the rider, both only once the ride is completed. The UNIQUE
// constraint makes the submit endpoint an idempotent upsert, and the averages
// are denormalised back onto drivers.rating / users.rating so listing screens
// don't aggregate on every read.
await sql`CREATE TABLE IF NOT EXISTS ride_ratings (
  id BIGSERIAL PRIMARY KEY,
  ride_id INTEGER NOT NULL REFERENCES rides(ride_id) ON DELETE CASCADE,
  rater_type VARCHAR(10) NOT NULL CHECK (rater_type IN ('rider','driver')),
  rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT ride_ratings_once UNIQUE (ride_id, rater_type)
)`;
await sql`CREATE INDEX IF NOT EXISTS ride_ratings_ride_idx ON ride_ratings(ride_id)`;

// Riders carry a rating too, so a driver can see who they're picking up.
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS rating NUMERIC(2,1)`;
await sql`ALTER TABLE users ADD COLUMN IF NOT EXISTS rating_count INTEGER NOT NULL DEFAULT 0`;
await sql`ALTER TABLE drivers ADD COLUMN IF NOT EXISTS rating_count INTEGER NOT NULL DEFAULT 0`;

// Expo push tokens, one row per device. The token is the primary key rather
// than (user_id, token): a token identifies a physical device install, and
// when a second account signs in on the same phone the row must MOVE to that
// user, not duplicate — otherwise the previous account keeps receiving
// notifications meant for whoever is signed in now. Tokens are deleted on
// sign-out, and pruned automatically when Expo reports DeviceNotRegistered.
await sql`CREATE TABLE IF NOT EXISTS push_tokens (
  token TEXT PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform VARCHAR(10),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
)`;
await sql`CREATE INDEX IF NOT EXISTS push_tokens_user_idx ON push_tokens(user_id)`;

// No seed drivers: the drivers table starts empty. Real drivers are created
// in-app via onboarding (driver/profile POST), which links a drivers row to a
// real user account and gives it a live GPS position for matching.
console.log("Database ready.");

await pool.end();