db.js

/**
 * db.js — Supabase (PostgreSQL + PostGIS) data layer for PotholeIQ.
 *
 * Uses the Supabase client (REST) so it works with the API keys:
 *   SUPABASE_URL="https://<ref>.supabase.co"
 *   SUPABASE_SECRET_KEY="sb_secret_..."   (service role — bypasses RLS, server-side only)
 *
 * Requires the schema in db/schema.sql to be run once in the Supabase SQL editor
 * (creates the cases table, the geom trigger, the spatial index, and the
 * find_nearby_cases() PostGIS function).
 *
 * If not configured, isEnabled() is false and the backend falls back to cases.json.
 */
'use strict';

let createClient;
try { ({ createClient } = require('@supabase/supabase-js')); } catch (_) { createClient = null; }

let client = null;

function cfg() {
  return {
    url: process.env.SUPABASE_URL || '',
    key: process.env.SUPABASE_SECRET_KEY || process.env.SUPABASE_KEY || '',
  };
}

function isEnabled() {
  const { url, key } = cfg();
  return !!(createClient && url && key);
}

function getClient() {
  if (!isEnabled()) return null;
  if (!client) {
    const { url, key } = cfg();
    client = createClient(url, key, { auth: { persistSession: false } });
  }
  return client;
}

// camelCase case object -> snake_case table row. geom is set by a DB trigger from lat/lng.
function toRow(c = {}) {
  const num = (v) => (v === undefined || v === null || v === '' ? null : Number(v));
  const str = (v) => (v === undefined || v === null ? null : String(v));
  return {
    case_id: str(c.caseId),
    status: str(c.status),
    submitted_at: c.submittedAt || null,
    resolved_at: c.resolvedAt || null,
    address: str(c.address),
    ward: str(c.ward),
    district: str(c.district),
    maintenance_zone: str(c.maintenanceZone),
    latitude: num(c.latitude),
    longitude: num(c.longitude),
    severity_score: num(c.severityScore ?? c.severityScore0100),
    severity_level: str(c.severityLevel),
    pothole_size: str(c.potholeSize),
    duplicate_status: str(c.duplicateStatus),
    duplicate_count: num(c.duplicateCount) || 0,
    linked_case_ids: str(c.linkedCaseIds),
    weather_risk: str(c.weatherRisk),
    ai_review_status: str(c.aiReviewStatus),
    reporter_contact: str(c.reporterContact),
    photo_url: str(c.photoUrl),
    box_folder_id: str(c.boxFolderId || c.caseFolderId),
    assigned_crew: str(c.assignedCrew),
    repair_method: str(c.repairMethod),
    materials_used: str(c.materialsUsed),
    labor_hours: num(c.laborHours),
    crew_notes: str(c.crewNotes || c.notes),
    after_photo_url: str(c.afterPhotoUrl),
    signed_by: str(c.signedBy),
    signed_at: c.signedAt || null,
    raw: c.raw || c,
  };
}

async function upsertCase(c = {}) {
  const sb = getClient();
  if (!sb) throw new Error('Supabase not configured.');
  const row = toRow(c);
  // drop null fields (except case_id) so a partial update doesn't wipe existing values
  Object.keys(row).forEach((k) => { if (row[k] === null && k !== 'case_id') delete row[k]; });
  const { error } = await sb.from('cases').upsert(row, { onConflict: 'case_id' });
  if (error) throw new Error(error.message);
  return c.caseId;
}

// Geo-radius duplicate check (meters) via the PostGIS ST_DWithin RPC — indexed.
async function findNearby(lat, lng, radiusM = 80, excludeCaseId = null) {
  const sb = getClient();
  if (!sb || lat == null || lng == null) return [];
  const { data, error } = await sb.rpc('find_nearby_cases', {
    p_lat: Number(lat), p_lng: Number(lng), p_radius_m: Number(radiusM), p_exclude: excludeCaseId,
  });
  if (error) throw new Error(error.message);
  return (data || []).map((r) => ({
    caseId: r.case_id,
    distanceM: Math.round(r.distance_m),
    severityLevel: r.severity_level,
    status: r.status,
    submittedAt: r.submitted_at,
  }));
}

async function getCase(caseId) {
  const sb = getClient();
  if (!sb) return null;
  const { data } = await sb.from('cases').select('*').eq('case_id', caseId).maybeSingle();
  return data || null;
}

async function listCases(limit = 500) {
  const sb = getClient();
  if (!sb) return [];
  const { data } = await sb.from('cases').select('*').order('submitted_at', { ascending: false }).limit(limit);
  return data || [];
}

async function setStatus(caseId, status, extra = {}) {
  return upsertCase({ caseId, status, ...extra });
}

module.exports = { isEnabled, upsertCase, findNearby, getCase, listCases, setStatus };