migrations/01_schema.sql

-- objects table (simplified)
CREATE TABLE IF NOT EXISTS objects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  namespace TEXT NOT NULL,
  type TEXT NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  location JSONB,
  severity INT,
  confirmed BOOLEAN DEFAULT FALSE,
  images JSONB,
  provenance JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ledger / payouts
CREATE TABLE IF NOT EXISTS payouts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  fault_id UUID REFERENCES objects(id) ON DELETE CASCADE,
  amount_minor_units BIGINT NOT NULL,
  currency VARCHAR(8) NOT NULL DEFAULT 'ZAR',
  payee_id TEXT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'created', -- created|queued|processing|settled|failed
  tx_ref TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  settled_at TIMESTAMPTZ
);

CREATE INDEX idx_objects_namespace ON objects(namespace);
CREATE INDEX idx_objects_confirmed ON objects(confirmed);
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS objects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  namespace TEXT NOT NULL,
  type TEXT NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  location JSONB,
  severity INT,
  confirmed BOOLEAN DEFAULT FALSE,
  images JSONB,
  provenance JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS payouts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  fault_id UUID REFERENCES objects(id) ON DELETE CASCADE,
  amount_minor_units BIGINT NOT NULL,
  currency VARCHAR(8) NOT NULL DEFAULT 'ZAR',
  payee_id TEXT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'created',
  tx_ref TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  settled_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_objects_namespace ON objects(namespace);
CREATE INDEX IF NOT EXISTS idx_objects_confirmed ON objects(confirmed);