-- OMLA · Migration 001 · Initial schema
-- Idempotent. Safe to re-run.

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ---------- models ----------
CREATE TABLE IF NOT EXISTS models (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            TEXT NOT NULL,
  ed25519_pubkey  BYTEA NOT NULL UNIQUE,
  description     TEXT,
  model_hash      TEXT,
  registration_signature BYTEA,
  creator_email   TEXT,
  license_version TEXT NOT NULL DEFAULT 'v8-10-2025',
  is_active       BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_models_creator_email ON models(creator_email);
CREATE INDEX IF NOT EXISTS idx_models_is_active    ON models(is_active);
CREATE INDEX IF NOT EXISTS idx_models_model_hash   ON models(model_hash);

-- ---------- model_metadata (mutable key/value overlay) ----------
CREATE TABLE IF NOT EXISTS model_metadata (
  model_id   UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  key        TEXT NOT NULL,
  value      JSONB,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (model_id, key)
);

-- ---------- lineage_edges (DAG) ----------
CREATE TABLE IF NOT EXISTS lineage_edges (
  parent_id           UUID NOT NULL REFERENCES models(id),
  child_id            UUID NOT NULL REFERENCES models(id),
  relationship        TEXT NOT NULL
    CHECK (relationship IN ('fine-tune','merge','distill','quantize','derivative')),
  contribution_weight NUMERIC(5,4) NOT NULL DEFAULT 1.0
    CHECK (contribution_weight > 0 AND contribution_weight <= 1),
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (parent_id, child_id),
  CHECK (parent_id <> child_id)
);
CREATE INDEX IF NOT EXISTS idx_lineage_parent ON lineage_edges(parent_id);
CREATE INDEX IF NOT EXISTS idx_lineage_child  ON lineage_edges(child_id);

-- ---------- wallets (Bech32m addresses + payment rails) ----------
CREATE TABLE IF NOT EXISTS wallets (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id        UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  address         TEXT NOT NULL UNIQUE
    CHECK (address ~ '^omla1[a-z0-9]{38,58}$'),
  rail            TEXT NOT NULL
    CHECK (rail IN ('stripe','paypal','lightning','ach','sepa','wise','other')),
  rail_identifier TEXT,
  is_primary      BOOLEAN NOT NULL DEFAULT false,
  is_active       BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_wallets_model ON wallets(model_id);

-- Only one primary wallet per model.
CREATE UNIQUE INDEX IF NOT EXISTS uq_wallets_primary_per_model
  ON wallets(model_id) WHERE is_primary = true;

-- ---------- contribution_splits ----------
CREATE TABLE IF NOT EXISTS contribution_splits (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id            UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  recipient_wallet_id UUID NOT NULL REFERENCES wallets(id),
  percentage          NUMERIC(6,3) NOT NULL CHECK (percentage > 0 AND percentage <= 100),
  effective_from      TIMESTAMPTZ NOT NULL DEFAULT now(),
  effective_until     TIMESTAMPTZ,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_splits_model  ON contribution_splits(model_id);
CREATE INDEX IF NOT EXISTS idx_splits_wallet ON contribution_splits(recipient_wallet_id);

-- ---------- commercial_users ----------
CREATE TABLE IF NOT EXISTS commercial_users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_name  TEXT NOT NULL,
  contact_email TEXT NOT NULL UNIQUE,
  api_key_hash  TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- usage_reports ----------
CREATE TABLE IF NOT EXISTS usage_reports (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id           UUID NOT NULL REFERENCES models(id),
  reporter_id        UUID NOT NULL REFERENCES commercial_users(id),
  period_start       DATE NOT NULL,
  period_end         DATE NOT NULL,
  quarter            TEXT,
  revenue_usd        NUMERIC(14,2) NOT NULL DEFAULT 0,
  run_cost_usd       NUMERIC(14,2) NOT NULL DEFAULT 0,
  royalty_basis      TEXT CHECK (royalty_basis IN ('revenue','cost')),
  royalty_amount_usd NUMERIC(14,2),
  reporter_signature BYTEA,
  status             TEXT NOT NULL DEFAULT 'submitted'
    CHECK (status IN ('submitted','verified','disputed','paid')),
  submitted_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (period_end >= period_start)
);
CREATE INDEX IF NOT EXISTS idx_reports_model    ON usage_reports(model_id);
CREATE INDEX IF NOT EXISTS idx_reports_reporter ON usage_reports(reporter_id);
CREATE INDEX IF NOT EXISTS idx_reports_quarter  ON usage_reports(quarter);
CREATE INDEX IF NOT EXISTS idx_reports_status   ON usage_reports(status);

-- ---------- payments ----------
CREATE TABLE IF NOT EXISTS payments (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id        UUID REFERENCES models(id),
  wallet_id       UUID REFERENCES wallets(id),
  report_id       UUID REFERENCES usage_reports(id),
  quarter         TEXT,
  amount_usd      NUMERIC(14,2) NOT NULL,
  fee_usd         NUMERIC(14,2) NOT NULL DEFAULT 0,
  rail            TEXT,
  external_txn_id TEXT,
  status          TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending','processing','completed','failed','retry')),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  paid_at         TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_payments_wallet  ON payments(wallet_id);
CREATE INDEX IF NOT EXISTS idx_payments_report  ON payments(report_id);
CREATE INDEX IF NOT EXISTS idx_payments_quarter ON payments(quarter);
CREATE INDEX IF NOT EXISTS idx_payments_status  ON payments(status);
-- Idempotency: one payment per (wallet, report, quarter).
CREATE UNIQUE INDEX IF NOT EXISTS uq_payments_idempotency
  ON payments(wallet_id, report_id, quarter)
  WHERE report_id IS NOT NULL;

-- ---------- compliance_state ----------
CREATE TABLE IF NOT EXISTS compliance_state (
  model_id        UUID PRIMARY KEY REFERENCES models(id) ON DELETE CASCADE,
  state           TEXT NOT NULL DEFAULT 'REGISTERED'
    CHECK (state IN ('REGISTERED','REPORTING','COMPLIANT','DELINQUENT','UNDER_REVIEW','BLACKLISTED')),
  reason          TEXT,
  last_report_at  TIMESTAMPTZ,
  blacklisted_at  TIMESTAMPTZ,
  last_updated    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- complaints ----------
CREATE TABLE IF NOT EXISTS complaints (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  complaint_type    TEXT NOT NULL
    CHECK (complaint_type IN ('invalid_wallet','ownership_dispute','lineage_misattribution','unpaid_royalty','fraud_impersonation','other')),
  target            TEXT NOT NULL, -- model UUID or wallet address
  target_model_id   UUID REFERENCES models(id),
  target_wallet_id  UUID REFERENCES wallets(id),
  details           TEXT NOT NULL,
  complainant_name  TEXT,
  complainant_email TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'open'
    CHECK (status IN ('open','acknowledged','resolved','dismissed','under_appeal')),
  notified_at       TIMESTAMPTZ,
  resolved_at       TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_complaints_status ON complaints(status);
CREATE INDEX IF NOT EXISTS idx_complaints_target_model ON complaints(target_model_id);

-- ---------- audit_log (append-only) ----------
CREATE TABLE IF NOT EXISTS audit_log (
  id           BIGSERIAL PRIMARY KEY,
  entity       TEXT NOT NULL,
  entity_id    UUID,
  action       TEXT NOT NULL,
  actor        TEXT,
  before_state JSONB,
  after_state  JSONB,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_entity_id ON audit_log(entity_id);
CREATE INDEX IF NOT EXISTS idx_audit_created   ON audit_log(created_at);

-- ---------- summary ----------
DO $$
DECLARE t_count INT;
BEGIN
  SELECT count(*) INTO t_count
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name IN ('models','model_metadata','lineage_edges','wallets',
                         'contribution_splits','commercial_users','usage_reports',
                         'payments','compliance_state','complaints','audit_log');
  RAISE NOTICE 'OMLA · 001_initial_schema applied. Tables present: %', t_count;
END $$;
-- OMLA · Migration 003 · Row-Level Security policies
-- Safe to re-run: DROPs policies before re-creating.

ALTER TABLE models              ENABLE ROW LEVEL SECURITY;
ALTER TABLE model_metadata      ENABLE ROW LEVEL SECURITY;
ALTER TABLE lineage_edges       ENABLE ROW LEVEL SECURITY;
ALTER TABLE wallets             ENABLE ROW LEVEL SECURITY;
ALTER TABLE contribution_splits ENABLE ROW LEVEL SECURITY;
ALTER TABLE commercial_users    ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage_reports       ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments            ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance_state    ENABLE ROW LEVEL SECURITY;
ALTER TABLE complaints          ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log           ENABLE ROW LEVEL SECURITY;

-- ===== Public read (anyone can browse the registry) =====
DROP POLICY IF EXISTS "models_public_read"           ON models;
DROP POLICY IF EXISTS "metadata_public_read"         ON model_metadata;
DROP POLICY IF EXISTS "lineage_public_read"          ON lineage_edges;
DROP POLICY IF EXISTS "wallets_public_read"          ON wallets;
DROP POLICY IF EXISTS "splits_public_read"           ON contribution_splits;
DROP POLICY IF EXISTS "compliance_public_read"       ON compliance_state;

CREATE POLICY "models_public_read"      ON models              FOR SELECT USING (is_active = true);
CREATE POLICY "metadata_public_read"    ON model_metadata      FOR SELECT USING (true);
CREATE POLICY "lineage_public_read"     ON lineage_edges       FOR SELECT USING (true);
CREATE POLICY "wallets_public_read"     ON wallets             FOR SELECT USING (is_active = true);
CREATE POLICY "splits_public_read"      ON contribution_splits FOR SELECT USING (true);
CREATE POLICY "compliance_public_read"  ON compliance_state    FOR SELECT USING (true);

-- ===== Authenticated INSERT (register a model) =====
DROP POLICY IF EXISTS "models_authed_insert"    ON models;
DROP POLICY IF EXISTS "metadata_authed_insert"  ON model_metadata;
DROP POLICY IF EXISTS "lineage_authed_insert"   ON lineage_edges;
DROP POLICY IF EXISTS "wallets_authed_insert"   ON wallets;
DROP POLICY IF EXISTS "splits_authed_insert"    ON contribution_splits;
DROP POLICY IF EXISTS "compliance_authed_insert" ON compliance_state;

CREATE POLICY "models_authed_insert"    ON models              FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND creator_email = auth.jwt()->>'email');

CREATE POLICY "metadata_authed_insert"  ON model_metadata      FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND
              model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

CREATE POLICY "lineage_authed_insert"   ON lineage_edges       FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND
              child_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

CREATE POLICY "wallets_authed_insert"   ON wallets             FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND
              model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

CREATE POLICY "splits_authed_insert"    ON contribution_splits FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND
              model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

CREATE POLICY "compliance_authed_insert" ON compliance_state   FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND
              model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

-- ===== Owners can UPDATE their own =====
DROP POLICY IF EXISTS "models_owner_update"       ON models;
DROP POLICY IF EXISTS "metadata_owner_update"     ON model_metadata;
DROP POLICY IF EXISTS "wallets_owner_update"      ON wallets;
DROP POLICY IF EXISTS "splits_owner_update"       ON contribution_splits;

CREATE POLICY "models_owner_update"   ON models FOR UPDATE
  USING (creator_email = auth.jwt()->>'email')
  WITH CHECK (creator_email = auth.jwt()->>'email');

CREATE POLICY "metadata_owner_update" ON model_metadata FOR UPDATE
  USING (model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

CREATE POLICY "wallets_owner_update"  ON wallets FOR UPDATE
  USING (model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'))
  WITH CHECK (model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

CREATE POLICY "splits_owner_update"   ON contribution_splits FOR UPDATE
  USING (model_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

-- ===== Commercial users =====
DROP POLICY IF EXISTS "commercial_self_read"    ON commercial_users;
DROP POLICY IF EXISTS "commercial_self_insert"  ON commercial_users;
DROP POLICY IF EXISTS "commercial_self_update"  ON commercial_users;

CREATE POLICY "commercial_self_read"   ON commercial_users FOR SELECT USING (contact_email = auth.jwt()->>'email');
CREATE POLICY "commercial_self_insert" ON commercial_users FOR INSERT WITH CHECK (contact_email = auth.jwt()->>'email');
CREATE POLICY "commercial_self_update" ON commercial_users FOR UPDATE USING (contact_email = auth.jwt()->>'email');

-- ===== Usage reports =====
DROP POLICY IF EXISTS "reports_self_read"   ON usage_reports;
DROP POLICY IF EXISTS "reports_self_insert" ON usage_reports;

CREATE POLICY "reports_self_read"   ON usage_reports FOR SELECT
  USING (reporter_id IN (SELECT id FROM commercial_users WHERE contact_email = auth.jwt()->>'email'));

CREATE POLICY "reports_self_insert" ON usage_reports FOR INSERT
  WITH CHECK (reporter_id IN (SELECT id FROM commercial_users WHERE contact_email = auth.jwt()->>'email'));

-- ===== Payments: creators can read payments to their wallets =====
DROP POLICY IF EXISTS "payments_owner_read" ON payments;
CREATE POLICY "payments_owner_read" ON payments FOR SELECT
  USING (wallet_id IN (
    SELECT w.id FROM wallets w
    JOIN models m ON m.id = w.model_id
    WHERE m.creator_email = auth.jwt()->>'email'
  ));

-- Reporters can read payments for their own reports.
DROP POLICY IF EXISTS "payments_reporter_read" ON payments;
CREATE POLICY "payments_reporter_read" ON payments FOR SELECT
  USING (report_id IN (
    SELECT r.id FROM usage_reports r
    JOIN commercial_users c ON c.id = r.reporter_id
    WHERE c.contact_email = auth.jwt()->>'email'
  ));

-- ===== Complaints: anyone can insert; only admins see details =====
DROP POLICY IF EXISTS "complaints_public_insert" ON complaints;
CREATE POLICY "complaints_public_insert" ON complaints FOR INSERT WITH CHECK (true);
-- (Reads of complaints are restricted; Edge Functions use service_role.)

-- ===== Audit log: append-only. =====
DROP POLICY IF EXISTS "audit_insert" ON audit_log;
CREATE POLICY "audit_insert" ON audit_log FOR INSERT WITH CHECK (true);
-- (No SELECT/UPDATE/DELETE policies → access only via service_role.)

DO $$ BEGIN RAISE NOTICE 'OMLA · 003_rls_policies applied.'; END $$;
-- OMLA · Migration 004 · Stored functions
-- The heart of the payout system: resolve_splits() returns a flattened
-- terminal split table for any model, accounting for upstream lineage.

-- Helper: split a single model's royalty pool by its declared contributions.
CREATE OR REPLACE FUNCTION direct_splits(target UUID)
RETURNS TABLE(wallet_id UUID, weight NUMERIC) AS $$
  SELECT cs.recipient_wallet_id, cs.percentage / 100.0
  FROM contribution_splits cs
  WHERE cs.model_id = target
    AND (cs.effective_until IS NULL OR cs.effective_until > now());
$$ LANGUAGE SQL STABLE;

-- Main function: recursive resolve. Returns aggregated percentages per
-- terminal wallet, with wallet rail attached.
CREATE OR REPLACE FUNCTION resolve_splits(target_model_id UUID)
RETURNS TABLE(wallet_address TEXT, wallet_rail TEXT, percentage NUMERIC) AS $$
  WITH RECURSIVE walk(model_id, weight, depth) AS (
    SELECT target_model_id, 1.0::NUMERIC, 0
    UNION ALL
    SELECT le.parent_id,
           w.weight * le.contribution_weight,
           w.depth + 1
    FROM walk w
    JOIN lineage_edges le ON le.child_id = w.model_id
    WHERE w.depth < 20              -- guard against accidental cycles
      AND w.weight > 0.00001        -- prune negligible contributions
  ),
  leaf AS (
    -- For every model in the walk, multiply its direct splits by the path weight.
    SELECT ds.wallet_id, SUM(w.weight * ds.weight) AS share
    FROM walk w
    CROSS JOIN LATERAL direct_splits(w.model_id) ds
    GROUP BY ds.wallet_id
  )
  SELECT wa.address, wa.rail,
         ROUND(SUM(leaf.share) * 100.0, 4) AS percentage
  FROM leaf
  JOIN wallets wa ON wa.id = leaf.wallet_id
  WHERE wa.is_active = true
  GROUP BY wa.address, wa.rail
  ORDER BY percentage DESC;
$$ LANGUAGE SQL STABLE;

-- Convenience: compute the royalty for a (revenue, cost) pair at the OMLA rate.
CREATE OR REPLACE FUNCTION omla_royalty(revenue_usd NUMERIC, run_cost_usd NUMERIC, rate NUMERIC DEFAULT 0.30)
RETURNS TABLE(basis TEXT, amount NUMERIC) AS $$
  SELECT CASE WHEN revenue_usd * rate >= run_cost_usd * rate THEN 'revenue' ELSE 'cost' END,
         GREATEST(revenue_usd * rate, run_cost_usd * rate);
$$ LANGUAGE SQL IMMUTABLE;

-- Cycle check for lineage edges — call before inserting a new edge.
CREATE OR REPLACE FUNCTION would_create_cycle(p_parent UUID, p_child UUID)
RETURNS BOOLEAN AS $$
DECLARE reachable INT;
BEGIN
  WITH RECURSIVE r AS (
    SELECT parent_id FROM lineage_edges WHERE child_id = p_parent
    UNION
    SELECT le.parent_id FROM lineage_edges le JOIN r ON le.child_id = r.parent_id
  )
  SELECT COUNT(*) INTO reachable FROM r WHERE parent_id = p_child;
  RETURN reachable > 0 OR p_parent = p_child;
END; $$ LANGUAGE plpgsql STABLE;

-- Trigger: reject lineage inserts that would create a cycle.
CREATE OR REPLACE FUNCTION reject_lineage_cycle()
RETURNS TRIGGER AS $$
BEGIN
  IF would_create_cycle(NEW.parent_id, NEW.child_id) THEN
    RAISE EXCEPTION 'Lineage edge (% -> %) would create a cycle', NEW.parent_id, NEW.child_id;
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_lineage_no_cycle ON lineage_edges;
CREATE TRIGGER trg_lineage_no_cycle BEFORE INSERT OR UPDATE ON lineage_edges
  FOR EACH ROW EXECUTE FUNCTION reject_lineage_cycle();

-- Enforce that contribution_splits sum to ≤ 100 per model.
CREATE OR REPLACE FUNCTION enforce_split_total()
RETURNS TRIGGER AS $$
DECLARE total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(percentage), 0) INTO total
    FROM contribution_splits
    WHERE model_id = COALESCE(NEW.model_id, OLD.model_id)
      AND (effective_until IS NULL OR effective_until > now());
  IF TG_OP IN ('INSERT','UPDATE') THEN total := total; END IF; -- NEW is included
  IF total > 100.001 THEN
    RAISE EXCEPTION 'contribution_splits for model % total %%% (> 100)', NEW.model_id, total;
  END IF;
  RETURN COALESCE(NEW, OLD);
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_splits_total ON contribution_splits;
CREATE CONSTRAINT TRIGGER trg_splits_total
  AFTER INSERT OR UPDATE ON contribution_splits
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION enforce_split_total();

DO $$ BEGIN RAISE NOTICE 'OMLA · 004_functions applied.'; END $$;
-- OMLA · Migration 005 · Audit-log triggers
-- Every insert/update/delete on critical tables lands in audit_log with
-- before_state and after_state. Append-only; there's no UPDATE or DELETE
-- policy on audit_log (see 003).

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS TRIGGER AS $$
DECLARE actor TEXT;
BEGIN
  actor := COALESCE(auth.jwt()->>'email', current_user);
  INSERT INTO audit_log (entity, entity_id, action, actor, before_state, after_state)
  VALUES (
    TG_TABLE_NAME,
    COALESCE((NEW).id::UUID, (OLD).id::UUID),
    TG_OP,
    actor,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD)::jsonb ELSE NULL END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::jsonb ELSE NULL END
  );
  RETURN COALESCE(NEW, OLD);
END; $$ LANGUAGE plpgsql;

-- Attach to critical tables.
DO $$
DECLARE t TEXT;
BEGIN
  FOREACH t IN ARRAY ARRAY['models','wallets','contribution_splits','payments',
                            'compliance_state','usage_reports','complaints']
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS audit_%1$s ON %1$s;', t);
    EXECUTE format('CREATE TRIGGER audit_%1$s AFTER INSERT OR UPDATE OR DELETE ON %1$s FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();', t);
  END LOOP;
END $$;

DO $$ BEGIN RAISE NOTICE 'OMLA · 005_audit_triggers applied.'; END $$;
-- OMLA · Migration 006 · Compliance state-machine helpers
-- Transitions are deterministic: call tick_compliance() from a weekly cron.

-- When a model is created, seed its compliance row.
CREATE OR REPLACE FUNCTION seed_compliance_on_model_insert()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO compliance_state (model_id, state) VALUES (NEW.id, 'REGISTERED')
  ON CONFLICT (model_id) DO NOTHING;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_seed_compliance ON models;
CREATE TRIGGER trg_seed_compliance AFTER INSERT ON models
  FOR EACH ROW EXECUTE FUNCTION seed_compliance_on_model_insert();

-- Transition: any model with at least one submitted report moves REGISTERED → REPORTING.
-- Transition: models with a completed payment in the latest quarter → COMPLIANT.
-- Transition: models with no report for > 30 days past quarter end → DELINQUENT.
-- Transition: DELINQUENT > 90 days → BLACKLISTED.
CREATE OR REPLACE FUNCTION tick_compliance()
RETURNS TABLE(model_id UUID, old_state TEXT, new_state TEXT) AS $$
DECLARE now_ts TIMESTAMPTZ := now();
BEGIN
  -- REGISTERED → REPORTING
  RETURN QUERY
  UPDATE compliance_state cs
     SET state = 'REPORTING', last_report_at = r.max_sub, last_updated = now_ts
    FROM (SELECT model_id, MAX(submitted_at) AS max_sub FROM usage_reports GROUP BY model_id) r
   WHERE cs.model_id = r.model_id AND cs.state = 'REGISTERED'
  RETURNING cs.model_id, 'REGISTERED'::TEXT, 'REPORTING'::TEXT;

  -- REPORTING → COMPLIANT (has completed payment in last 120 days)
  RETURN QUERY
  UPDATE compliance_state cs
     SET state = 'COMPLIANT', last_updated = now_ts
    FROM (SELECT w.model_id
            FROM payments p JOIN wallets w ON w.id = p.wallet_id
           WHERE p.status = 'completed' AND p.paid_at > now_ts - INTERVAL '120 days'
           GROUP BY w.model_id) p
   WHERE cs.model_id = p.model_id AND cs.state IN ('REPORTING','DELINQUENT')
  RETURNING cs.model_id, cs.state, 'COMPLIANT'::TEXT;

  -- REPORTING → DELINQUENT (no report in > 120 days but have had one before)
  RETURN QUERY
  UPDATE compliance_state cs
     SET state = 'DELINQUENT', reason = 'no report in >120 days', last_updated = now_ts
   WHERE cs.state IN ('REPORTING','COMPLIANT')
     AND (cs.last_report_at IS NOT NULL AND cs.last_report_at < now_ts - INTERVAL '120 days')
  RETURNING cs.model_id, cs.state, 'DELINQUENT'::TEXT;

  -- DELINQUENT → BLACKLISTED (>= 90 days in DELINQUENT)
  RETURN QUERY
  UPDATE compliance_state cs
     SET state = 'BLACKLISTED', blacklisted_at = now_ts, last_updated = now_ts
   WHERE cs.state = 'DELINQUENT'
     AND cs.last_updated < now_ts - INTERVAL '90 days'
  RETURNING cs.model_id, 'DELINQUENT'::TEXT, 'BLACKLISTED'::TEXT;
END; $$ LANGUAGE plpgsql;

DO $$ BEGIN RAISE NOTICE 'OMLA · 006_compliance_fns applied.'; END $$;
-- OMLA · Migration 008 · Close high-impact schema gaps
-- This migration hardens the system against specific failure modes and adds
-- tables referenced in the technical docs and roadmap.
-- Idempotent. Safe to re-run.

-- ---------- 1. Unique weight hash ----------
-- Two registrations cannot claim the same weight file. Disputes go through
-- complaints. NULL hashes are still allowed (legacy rows) but new inserts
-- should always include a hash.
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_indexes WHERE schemaname = 'public' AND indexname = 'uq_models_model_hash'
  ) THEN
    CREATE UNIQUE INDEX uq_models_model_hash ON models(model_hash) WHERE model_hash IS NOT NULL;
  END IF;
END $$;

-- ---------- 2. Effective-window hardening on split resolution ----------
-- We drop the single-arg variants from migration 004 and replace them with
-- two-arg functions whose second parameter defaults to now(). This keeps
-- every existing caller working (PostgREST RPC with `{target_model_id}`
-- resolves to the default-filled overload) while also enabling historical
-- (`as_of`) resolution for quarterly payouts.
--
-- We do this as DROP+CREATE rather than CREATE OR REPLACE because you
-- cannot REPLACE a function with a different parameter list in-place.
DROP FUNCTION IF EXISTS resolve_splits(UUID);
DROP FUNCTION IF EXISTS direct_splits(UUID);

CREATE OR REPLACE FUNCTION direct_splits(target UUID, as_of TIMESTAMPTZ DEFAULT now())
RETURNS TABLE(wallet_id UUID, weight NUMERIC) AS $$
  SELECT cs.recipient_wallet_id, cs.percentage / 100.0
  FROM contribution_splits cs
  WHERE cs.model_id = target
    AND cs.effective_from <= as_of
    AND (cs.effective_until IS NULL OR cs.effective_until > as_of);
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION resolve_splits(target_model_id UUID, as_of TIMESTAMPTZ DEFAULT now())
RETURNS TABLE(wallet_address TEXT, wallet_rail TEXT, percentage NUMERIC) AS $$
  WITH RECURSIVE walk(model_id, weight, depth) AS (
    SELECT target_model_id, 1.0::NUMERIC, 0
    UNION ALL
    SELECT le.parent_id, w.weight * le.contribution_weight, w.depth + 1
    FROM walk w
    JOIN lineage_edges le ON le.child_id = w.model_id
    WHERE w.depth < 20 AND w.weight > 0.00001
  ),
  leaf AS (
    SELECT ds.wallet_id, SUM(w.weight * ds.weight) AS share
    FROM walk w
    CROSS JOIN LATERAL direct_splits(w.model_id, as_of) ds
    GROUP BY ds.wallet_id
  )
  SELECT wa.address, wa.rail, ROUND(SUM(leaf.share) * 100.0, 4) AS percentage
  FROM leaf
  JOIN wallets wa ON wa.id = leaf.wallet_id
  WHERE wa.is_active = true
  GROUP BY wa.address, wa.rail
  ORDER BY percentage DESC;
$$ LANGUAGE SQL STABLE;

GRANT EXECUTE ON FUNCTION resolve_splits(UUID, TIMESTAMPTZ) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION direct_splits(UUID, TIMESTAMPTZ)  TO anon, authenticated;

-- ---------- 3. Lineage weight-sum check ----------
-- The sum of inbound edge weights to any model must be ≤ 1.0.
CREATE OR REPLACE FUNCTION enforce_lineage_weight_sum()
RETURNS TRIGGER AS $$
DECLARE total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(contribution_weight), 0) INTO total
    FROM lineage_edges
    WHERE child_id = COALESCE(NEW.child_id, OLD.child_id);
  IF total > 1.0001 THEN
    RAISE EXCEPTION 'lineage_edges for child % total %%% (> 1.0)', NEW.child_id, total;
  END IF;
  RETURN COALESCE(NEW, OLD);
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_lineage_weight_sum ON lineage_edges;
CREATE CONSTRAINT TRIGGER trg_lineage_weight_sum
  AFTER INSERT OR UPDATE ON lineage_edges
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION enforce_lineage_weight_sum();

-- ---------- 4. External lineage (non-OMLA ancestors) ----------
CREATE TABLE IF NOT EXISTS external_lineage (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  child_id       UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  external_name  TEXT NOT NULL,
  external_url   TEXT,
  license        TEXT,
  note           TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_external_lineage_child ON external_lineage(child_id);

ALTER TABLE external_lineage ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "external_lineage_public_read" ON external_lineage;
CREATE POLICY "external_lineage_public_read" ON external_lineage FOR SELECT USING (true);

DROP POLICY IF EXISTS "external_lineage_authed_insert" ON external_lineage;
CREATE POLICY "external_lineage_authed_insert" ON external_lineage FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND
              child_id IN (SELECT id FROM models WHERE creator_email = auth.jwt()->>'email'));

-- ---------- 5. Dust credits ----------
CREATE TABLE IF NOT EXISTS dust_credits (
  wallet_id   UUID PRIMARY KEY REFERENCES wallets(id) ON DELETE CASCADE,
  balance_usd NUMERIC(12,4) NOT NULL DEFAULT 0,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE dust_credits ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "dust_owner_read" ON dust_credits;
CREATE POLICY "dust_owner_read" ON dust_credits FOR SELECT
  USING (wallet_id IN (
    SELECT w.id FROM wallets w
    JOIN models m ON m.id = w.model_id
    WHERE m.creator_email = auth.jwt()->>'email'
  ));

-- ---------- 6. verify_by_hash() RPC ----------
-- Public lookup: given a SHA-256 weight hash, return the model's
-- canonical identity + compliance state, or NULL if unregistered.
CREATE OR REPLACE FUNCTION verify_by_hash(hash TEXT)
RETURNS TABLE(
  model_id        UUID,
  name            TEXT,
  license_version TEXT,
  wallet          TEXT,
  compliance      TEXT,
  blacklisted     BOOLEAN
) AS $$
  SELECT
    m.id,
    m.name,
    m.license_version,
    (SELECT address FROM wallets w WHERE w.model_id = m.id AND w.is_primary = true LIMIT 1),
    cs.state,
    cs.state = 'BLACKLISTED'
  FROM models m
  LEFT JOIN compliance_state cs ON cs.model_id = m.id
  WHERE m.model_hash = hash
    AND m.is_active = true
  LIMIT 1;
$$ LANGUAGE SQL STABLE;

GRANT EXECUTE ON FUNCTION verify_by_hash(TEXT) TO anon, authenticated;

-- ---------- 7. Commercial-user compliance state ----------
CREATE TABLE IF NOT EXISTS commercial_compliance_state (
  commercial_user_id UUID PRIMARY KEY REFERENCES commercial_users(id) ON DELETE CASCADE,
  state              TEXT NOT NULL DEFAULT 'REGISTERED'
    CHECK (state IN ('REGISTERED','REPORTING','COMPLIANT','DELINQUENT','UNDER_REVIEW','BLACKLISTED')),
  reason             TEXT,
  last_report_at     TIMESTAMPTZ,
  blacklisted_at     TIMESTAMPTZ,
  last_updated       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION seed_commercial_compliance()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO commercial_compliance_state (commercial_user_id, state)
  VALUES (NEW.id, 'REGISTERED')
  ON CONFLICT (commercial_user_id) DO NOTHING;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_seed_commercial_compliance ON commercial_users;
CREATE TRIGGER trg_seed_commercial_compliance AFTER INSERT ON commercial_users
  FOR EACH ROW EXECUTE FUNCTION seed_commercial_compliance();

ALTER TABLE commercial_compliance_state ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "commercial_compliance_public_read" ON commercial_compliance_state;
CREATE POLICY "commercial_compliance_public_read" ON commercial_compliance_state
  FOR SELECT USING (true);

-- ---------- 8. Complaint → UNDER_REVIEW trigger ----------
CREATE OR REPLACE FUNCTION complaint_triggers_review()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'open' AND NEW.target_model_id IS NOT NULL
     AND NEW.complaint_type IN ('invalid_wallet','ownership_dispute','fraud_impersonation') THEN
    UPDATE compliance_state
       SET state = 'UNDER_REVIEW',
           reason = 'open complaint: ' || NEW.complaint_type,
           last_updated = now()
     WHERE model_id = NEW.target_model_id
       AND state NOT IN ('BLACKLISTED','UNDER_REVIEW');
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_complaint_review ON complaints;
CREATE TRIGGER trg_complaint_review AFTER INSERT ON complaints
  FOR EACH ROW EXECUTE FUNCTION complaint_triggers_review();

-- ---------- 9. tick_compliance additions: dust credit, commercial tick ----------
CREATE OR REPLACE FUNCTION tick_commercial_compliance()
RETURNS TABLE(commercial_user_id UUID, old_state TEXT, new_state TEXT) AS $$
DECLARE now_ts TIMESTAMPTZ := now();
BEGIN
  -- REGISTERED → REPORTING when first report lands
  RETURN QUERY
  UPDATE commercial_compliance_state ccs
     SET state = 'REPORTING', last_report_at = r.max_sub, last_updated = now_ts
    FROM (SELECT reporter_id, MAX(submitted_at) AS max_sub
            FROM usage_reports GROUP BY reporter_id) r
   WHERE ccs.commercial_user_id = r.reporter_id AND ccs.state = 'REGISTERED'
  RETURNING ccs.commercial_user_id, 'REGISTERED'::TEXT, 'REPORTING'::TEXT;

  -- REPORTING → COMPLIANT when a paid report exists in last 120 days
  RETURN QUERY
  UPDATE commercial_compliance_state ccs
     SET state = 'COMPLIANT', last_updated = now_ts
   WHERE ccs.state IN ('REPORTING','DELINQUENT')
     AND EXISTS (
       SELECT 1 FROM usage_reports r
        WHERE r.reporter_id = ccs.commercial_user_id
          AND r.status = 'paid'
          AND r.submitted_at > now_ts - INTERVAL '120 days'
     )
  RETURNING ccs.commercial_user_id, ccs.state, 'COMPLIANT'::TEXT;

  -- REPORTING / COMPLIANT → DELINQUENT when no report in > 120 days
  RETURN QUERY
  UPDATE commercial_compliance_state ccs
     SET state = 'DELINQUENT', reason = 'no report in >120 days', last_updated = now_ts
   WHERE ccs.state IN ('REPORTING','COMPLIANT')
     AND ccs.last_report_at IS NOT NULL
     AND ccs.last_report_at < now_ts - INTERVAL '120 days'
  RETURNING ccs.commercial_user_id, ccs.state, 'DELINQUENT'::TEXT;

  -- DELINQUENT → BLACKLISTED after 90 more days
  RETURN QUERY
  UPDATE commercial_compliance_state ccs
     SET state = 'BLACKLISTED', blacklisted_at = now_ts, last_updated = now_ts
   WHERE ccs.state = 'DELINQUENT'
     AND ccs.last_updated < now_ts - INTERVAL '90 days'
  RETURNING ccs.commercial_user_id, 'DELINQUENT'::TEXT, 'BLACKLISTED'::TEXT;
END; $$ LANGUAGE plpgsql;

-- ---------- 10. Summary ----------
DO $$ BEGIN
  RAISE NOTICE 'OMLA · 008_gap_closures applied. New objects: external_lineage, dust_credits, commercial_compliance_state, verify_by_hash(), lineage weight trigger, complaint→review trigger, commercial compliance tick.';
END $$;


-- OMLA · Migration 009 · Model hoster reimbursement
-- Idempotent. Safe to re-run.
--
-- Design (Task 1, no-platform-take variant):
--
--   Commercial user pays 30% royalty to the model's primary wallet.
--   That royalty forks into two pools:
--
--       hoster_pool  = royalty × (models.hoster_share_pct / 100)
--       creator_pool = royalty − hoster_pool
--
--   • creator_pool flows through contribution_splits and lineage_edges
--     exactly as before (Migration 001/004 logic unchanged).
--   • hoster_pool is split among verified, active hoster_claims for the
--     model, weighted by their declared inference volume for the quarter.
--
--   Because OMLA takes 0%, every dollar a hoster earns is a dollar the
--   creator does not. The default hoster_share_pct is 0 — the creator
--   must opt in at registration if they want to reimburse hosters.
--
-- Trust model:
--
--   • Hoster identity is an Ed25519 keypair (independent of the model's
--     creator keypair). A hoster proves control by signing a canonical
--     "host:<model_id>" attestation string, stored on the claim row.
--   • Volume reports are signed by the same Ed25519 key. OMLA cannot
--     verify the inference count itself; it verifies who signed it.
--     Over-reporting is a complaint-workflow problem, not a crypto one.
--   • A claim becomes "verified" when either (a) the model's creator
--     signs a confirmation with their creator keypair, or (b) OMLA
--     compliance review approves it. Unverified claims accrue balance
--     but don't get paid out until verified.
--
-- Forward-compat: payment rails for hosters are deliberately NOT added
-- here. Hoster payout to a rail is Task 1 out-of-scope per the plan.
-- When rails land, we reuse the existing wallets table (a hoster just
-- registers a wallet row tagged to their claim, via a follow-up
-- migration).

BEGIN;

-- --------------------------------------------------------------
-- models: opt-in hoster share
-- --------------------------------------------------------------
ALTER TABLE models
  ADD COLUMN IF NOT EXISTS hoster_share_pct NUMERIC(5,2) NOT NULL DEFAULT 0
    CHECK (hoster_share_pct >= 0 AND hoster_share_pct <= 50);

COMMENT ON COLUMN models.hoster_share_pct IS
  'Fraction of royalty redirected to verified hosters of this model (0-50). Default 0: hosters earn nothing. Set by the creator at registration; mutable only via signed-update RPC (Ed25519).';

-- --------------------------------------------------------------
-- hoster_claims — a hoster attests to hosting a given model
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hoster_claims (
  id                     UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id               UUID        NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  ed25519_pubkey         BYTEA       NOT NULL,
  display_name           TEXT,
  contact_email          TEXT        NOT NULL,
  region                 TEXT,                          -- ISO-2 optional
  endpoint_hint          TEXT,                          -- optional public URL hint
  attestation_signature  BYTEA       NOT NULL,          -- Ed25519 over "host:" || model_id
  verified_by            TEXT
    CHECK (verified_by IN ('creator','omla','self'))
    DEFAULT NULL,
  verified_at            TIMESTAMPTZ,
  revoked_at             TIMESTAMPTZ,
  revoked_reason         TEXT,
  claimed_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (model_id, ed25519_pubkey)
);
CREATE INDEX IF NOT EXISTS idx_hoster_claims_model      ON hoster_claims(model_id);
CREATE INDEX IF NOT EXISTS idx_hoster_claims_pubkey     ON hoster_claims(ed25519_pubkey);
CREATE INDEX IF NOT EXISTS idx_hoster_claims_active
  ON hoster_claims(model_id)
  WHERE revoked_at IS NULL AND verified_at IS NOT NULL;

COMMENT ON TABLE hoster_claims IS
  'A hoster (inference provider, mirror, storage) claims attribution for hosting a specific OMLA model. Identity is an Ed25519 keypair distinct from the model creator''s. Verification is either by the creator counter-signing or by OMLA compliance review.';

-- --------------------------------------------------------------
-- hoster_volume_reports — one row per hoster_claim × quarter
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hoster_volume_reports (
  id                UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  hoster_claim_id   UUID        NOT NULL REFERENCES hoster_claims(id) ON DELETE CASCADE,
  quarter           TEXT        NOT NULL
    CHECK (quarter ~ '^\d{4}-Q[1-4]$'),
  inferences_count  BIGINT      NOT NULL CHECK (inferences_count >= 0),
  tokens_processed  BIGINT               CHECK (tokens_processed IS NULL OR tokens_processed >= 0),
  compute_hours     NUMERIC(12,2)        CHECK (compute_hours IS NULL OR compute_hours >= 0),
  payload_hash      TEXT        NOT NULL,   -- SHA-256 hex of canonical JSON body
  payload_signature BYTEA       NOT NULL,   -- Ed25519 over payload_hash bytes
  submitted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (hoster_claim_id, quarter)
);
CREATE INDEX IF NOT EXISTS idx_hoster_volume_quarter ON hoster_volume_reports(quarter);

-- --------------------------------------------------------------
-- hoster_reimbursements — computed accrual per quarter per claim
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hoster_reimbursements (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  hoster_claim_id UUID        NOT NULL REFERENCES hoster_claims(id) ON DELETE CASCADE,
  model_id        UUID        NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  quarter         TEXT        NOT NULL
    CHECK (quarter ~ '^\d{4}-Q[1-4]$'),
  volume_share    NUMERIC(7,6) NOT NULL
    CHECK (volume_share >= 0 AND volume_share <= 1),   -- this hoster's share of hoster_pool for this model
  amount_usd      NUMERIC(12,2) NOT NULL DEFAULT 0
    CHECK (amount_usd >= 0),
  status          TEXT NOT NULL DEFAULT 'accrued'
    CHECK (status IN ('accrued','pending','paid','failed','forfeited')),
  paid_at         TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (hoster_claim_id, quarter)
);
CREATE INDEX IF NOT EXISTS idx_hoster_reimb_model   ON hoster_reimbursements(model_id);
CREATE INDEX IF NOT EXISTS idx_hoster_reimb_status  ON hoster_reimbursements(status);

-- --------------------------------------------------------------
-- RLS
-- --------------------------------------------------------------
ALTER TABLE hoster_claims           ENABLE ROW LEVEL SECURITY;
ALTER TABLE hoster_volume_reports   ENABLE ROW LEVEL SECURITY;
ALTER TABLE hoster_reimbursements   ENABLE ROW LEVEL SECURITY;

-- hoster_claims: publicly readable (discovery info), writes only via edge
-- function that verifies the Ed25519 attestation signature server-side.
DROP POLICY IF EXISTS hoster_claims_public_read ON hoster_claims;
CREATE POLICY hoster_claims_public_read ON hoster_claims
  FOR SELECT USING (true);

-- Block direct inserts/updates by anon/authenticated roles. Edge function
-- runs as service_role and bypasses RLS.
DROP POLICY IF EXISTS hoster_claims_no_direct_write ON hoster_claims;
CREATE POLICY hoster_claims_no_direct_write ON hoster_claims
  FOR ALL USING (false) WITH CHECK (false);

-- hoster_volume_reports: hoster reads own, nothing else.
-- Matching is by the contact_email on the claim = auth.jwt email claim.
-- (If a hoster wants API-key driven submission, that goes via edge fn.)
DROP POLICY IF EXISTS hoster_volume_own_read ON hoster_volume_reports;
CREATE POLICY hoster_volume_own_read ON hoster_volume_reports
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM hoster_claims hc
      WHERE hc.id = hoster_volume_reports.hoster_claim_id
        AND hc.contact_email = (auth.jwt() ->> 'email')
    )
  );

DROP POLICY IF EXISTS hoster_volume_no_direct_write ON hoster_volume_reports;
CREATE POLICY hoster_volume_no_direct_write ON hoster_volume_reports
  FOR ALL USING (false) WITH CHECK (false);

-- hoster_reimbursements: hoster reads own, service_role writes.
DROP POLICY IF EXISTS hoster_reimb_own_read ON hoster_reimbursements;
CREATE POLICY hoster_reimb_own_read ON hoster_reimbursements
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM hoster_claims hc
      WHERE hc.id = hoster_reimbursements.hoster_claim_id
        AND hc.contact_email = (auth.jwt() ->> 'email')
    )
  );

DROP POLICY IF EXISTS hoster_reimb_no_direct_write ON hoster_reimbursements;
CREATE POLICY hoster_reimb_no_direct_write ON hoster_reimbursements
  FOR ALL USING (false) WITH CHECK (false);

-- --------------------------------------------------------------
-- resolve_hoster_fork(model_id, quarter): returns (creator_pool_pct,
-- hoster_allocations[]) so clients can preview the fork without running
-- the payout math.
-- --------------------------------------------------------------
CREATE OR REPLACE FUNCTION resolve_hoster_fork(
  p_model_id UUID,
  p_quarter  TEXT
) RETURNS TABLE (
  hoster_claim_id UUID,
  display_name    TEXT,
  volume_share    NUMERIC,
  pool_share_pct  NUMERIC     -- this hoster's share expressed as % of the original royalty
) LANGUAGE sql STABLE AS $$
  WITH m AS (
    SELECT hoster_share_pct FROM models WHERE id = p_model_id
  ),
  reports AS (
    SELECT hvr.hoster_claim_id, hvr.inferences_count
    FROM hoster_volume_reports hvr
    JOIN hoster_claims hc ON hc.id = hvr.hoster_claim_id
    WHERE hvr.quarter = p_quarter
      AND hc.model_id = p_model_id
      AND hc.verified_at IS NOT NULL
      AND hc.revoked_at IS NULL
  ),
  total AS (SELECT SUM(inferences_count)::NUMERIC AS t FROM reports)
  SELECT
    r.hoster_claim_id,
    hc.display_name,
    CASE WHEN (SELECT t FROM total) > 0
         THEN (r.inferences_count::NUMERIC / (SELECT t FROM total))
         ELSE 0 END AS volume_share,
    CASE WHEN (SELECT t FROM total) > 0
         THEN (r.inferences_count::NUMERIC / (SELECT t FROM total))
              * (SELECT hoster_share_pct FROM m)
         ELSE 0 END AS pool_share_pct
  FROM reports r
  JOIN hoster_claims hc ON hc.id = r.hoster_claim_id
  ORDER BY pool_share_pct DESC;
$$;

COMMIT;


-- OMLA · Migration 010 · Multi-currency reimbursement + platform fees
-- Idempotent. Safe to re-run.
--
-- Design:
--
--   Creators declare an ordered list of currencies they will accept. At
--   least one entry MUST be a cryptocurrency (the mandatory backup).
--   Platforms / hosters declare which currencies they can remit in, plus
--   optional conversion fees for converting from their native currency
--   into the creator's backup crypto.
--
--   Payout resolution (per quarter, per creator payout):
--
--     1. Walk creator.preferred_currencies in order.
--     2. First currency the remitter can natively pay → use it, 0% fee.
--     3. If none match, fall back to the creator's backup crypto (first
--        crypto in their list) and apply the remitter's declared
--        conversion fee for (native → backup).
--
--   Commercial user / hoster sees the *effective* payout (net of fee)
--   before committing. No silent fees.
--
-- Example:
--
--   Creator preferences: [BUZZ@civitai, USD, BTC*]   (* = backup crypto)
--   Platform = Civitai    → pays in BUZZ, 0% conversion fee.
--   Platform = Fireworks  → supports only USD, so pays USD (match), 0%.
--   Platform = ImaginaryCo (BUZZ-native only) → has no USD, falls back
--                          to BTC, applies ImaginaryCo's declared
--                          conversion fee (say 30% for BUZZ → BTC).
--
-- Tables:
--   - currency_registry      canonical list of known currencies
--   - creator_currency_prefs per-model ordered preference list
--   - remitter_currencies    what each remitter (platform OR hoster) pays
--   - remitter_conversion_fees per (remitter, from_currency, to_currency)
--
-- These tables are purely configuration; no money moves through here.
-- Payout routing in the quarterly payout job reads them to decide per-
-- payee what currency + fee applies.

BEGIN;

-- --------------------------------------------------------------
-- currency_registry — canonical enumeration of accepted currencies
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS currency_registry (
  code              TEXT PRIMARY KEY,               -- "USD", "EUR", "BTC", "ETH", "LN-SATS", "BUZZ", ...
  kind              TEXT NOT NULL
    CHECK (kind IN ('fiat','crypto','platform_native')),
  platform          TEXT,                           -- non-null when kind = platform_native (e.g., 'civitai')
  display_name      TEXT NOT NULL,
  decimals          SMALLINT NOT NULL DEFAULT 2
    CHECK (decimals >= 0 AND decimals <= 18),
  is_backup_capable BOOLEAN NOT NULL DEFAULT false, -- true only for cryptos that the system accepts as mandatory backup
  added_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Seed the initial registry. Add more later without a migration by
-- inserting rows.
INSERT INTO currency_registry (code, kind, platform, display_name, decimals, is_backup_capable)
VALUES
  ('USD',     'fiat',            NULL,       'US Dollar',           2,  false),
  ('EUR',     'fiat',            NULL,       'Euro',                2,  false),
  ('JPY',     'fiat',            NULL,       'Japanese Yen',        0,  false),
  ('GBP',     'fiat',            NULL,       'British Pound',       2,  false),
  ('BTC',     'crypto',          NULL,       'Bitcoin',             8,  true),
  ('LN-SATS', 'crypto',          NULL,       'Lightning (sats)',    0,  true),
  ('ETH',     'crypto',          NULL,       'Ether',               18, true),
  ('USDC',    'crypto',          NULL,       'USD Coin (stable)',   6,  true),
  ('BUZZ',    'platform_native', 'civitai',  'Civitai Buzz',        0,  false),
  ('CREDITS-OR','platform_native','openrouter','OpenRouter Credits',6, false),
  ('REP-CREDITS','platform_native','replicate','Replicate Credits', 4,  false)
ON CONFLICT (code) DO NOTHING;

-- --------------------------------------------------------------
-- creator_currency_prefs — one model, many preferences, ordered
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS creator_currency_prefs (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id      UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  currency_code TEXT NOT NULL REFERENCES currency_registry(code),
  preference    SMALLINT NOT NULL CHECK (preference >= 0),   -- 0 = first choice
  is_backup     BOOLEAN NOT NULL DEFAULT false,
  added_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (model_id, currency_code),
  UNIQUE (model_id, preference)
);
CREATE INDEX IF NOT EXISTS idx_ccp_model ON creator_currency_prefs(model_id);

-- Exactly-one-backup-per-model, and that backup must be a crypto.
CREATE OR REPLACE FUNCTION enforce_model_has_backup_crypto(p_model_id UUID)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
  backup_count INT;
  backup_kind  TEXT;
BEGIN
  SELECT COUNT(*) INTO backup_count
    FROM creator_currency_prefs
    WHERE model_id = p_model_id AND is_backup = true;
  IF backup_count = 0 THEN
    RAISE EXCEPTION 'model % must declare at least one backup currency', p_model_id;
  END IF;
  IF backup_count > 1 THEN
    RAISE EXCEPTION 'model % has more than one backup currency', p_model_id;
  END IF;
  SELECT cr.kind INTO backup_kind
    FROM creator_currency_prefs ccp
    JOIN currency_registry cr ON cr.code = ccp.currency_code
    WHERE ccp.model_id = p_model_id AND ccp.is_backup = true;
  IF backup_kind <> 'crypto' THEN
    RAISE EXCEPTION 'backup currency for model % must be a cryptocurrency (got %)', p_model_id, backup_kind;
  END IF;
END;
$$;

-- Deferred constraint trigger: runs at transaction end so batch inserts
-- setting up a new model's preferences don't trip mid-way.
CREATE OR REPLACE FUNCTION trg_check_backup_on_prefs()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  PERFORM enforce_model_has_backup_crypto(
    COALESCE(NEW.model_id, OLD.model_id)
  );
  RETURN NULL;
END;
$$;

DROP TRIGGER IF EXISTS trg_creator_currency_prefs_check ON creator_currency_prefs;
CREATE CONSTRAINT TRIGGER trg_creator_currency_prefs_check
  AFTER INSERT OR UPDATE OR DELETE ON creator_currency_prefs
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION trg_check_backup_on_prefs();

-- --------------------------------------------------------------
-- remitter_currencies — what currencies a remitter (platform or hoster)
-- can natively pay out in. Keyed by either a commercial_users.id (when
-- the remitter is a platform paying royalties) OR a hoster_claims.id
-- (when a hoster is remitting). Exactly one of the two must be set.
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS remitter_currencies (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  commercial_user_id  UUID REFERENCES commercial_users(id) ON DELETE CASCADE,
  hoster_claim_id     UUID REFERENCES hoster_claims(id)    ON DELETE CASCADE,
  currency_code       TEXT NOT NULL REFERENCES currency_registry(code),
  added_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (
    (commercial_user_id IS NULL) <> (hoster_claim_id IS NULL)
  ),
  UNIQUE (commercial_user_id, hoster_claim_id, currency_code)
);
CREATE INDEX IF NOT EXISTS idx_rc_commercial ON remitter_currencies(commercial_user_id);
CREATE INDEX IF NOT EXISTS idx_rc_hoster     ON remitter_currencies(hoster_claim_id);

-- --------------------------------------------------------------
-- remitter_conversion_fees — fee a remitter charges to convert one of
-- their native currencies into a target currency. Declared up-front so
-- creators and commercial users can see the effective net.
--
-- Example row:
--   commercial_user_id = civitai.id
--   from_currency = 'BUZZ', to_currency = 'BTC', fee_pct = 30.00
--
-- Semantics: when the routing algorithm needs to convert from a
-- remitter's native (from_currency) into (to_currency), this row
-- applies. The fee reduces what the creator actually receives.
-- --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS remitter_conversion_fees (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  commercial_user_id  UUID REFERENCES commercial_users(id) ON DELETE CASCADE,
  hoster_claim_id     UUID REFERENCES hoster_claims(id)    ON DELETE CASCADE,
  from_currency       TEXT NOT NULL REFERENCES currency_registry(code),
  to_currency         TEXT NOT NULL REFERENCES currency_registry(code),
  fee_pct             NUMERIC(5,2) NOT NULL
    CHECK (fee_pct >= 0 AND fee_pct <= 50),   -- 50% hard ceiling
  effective_from      TIMESTAMPTZ NOT NULL DEFAULT now(),
  effective_until     TIMESTAMPTZ,
  CHECK (
    (commercial_user_id IS NULL) <> (hoster_claim_id IS NULL)
  ),
  CHECK (from_currency <> to_currency)
);
CREATE INDEX IF NOT EXISTS idx_rcf_from_to ON remitter_conversion_fees(from_currency, to_currency);

-- --------------------------------------------------------------
-- RLS
-- --------------------------------------------------------------
ALTER TABLE currency_registry          ENABLE ROW LEVEL SECURITY;
ALTER TABLE creator_currency_prefs     ENABLE ROW LEVEL SECURITY;
ALTER TABLE remitter_currencies        ENABLE ROW LEVEL SECURITY;
ALTER TABLE remitter_conversion_fees   ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS curr_registry_public_read ON currency_registry;
CREATE POLICY curr_registry_public_read ON currency_registry
  FOR SELECT USING (true);

DROP POLICY IF EXISTS ccp_public_read ON creator_currency_prefs;
CREATE POLICY ccp_public_read ON creator_currency_prefs
  FOR SELECT USING (true);

-- Writes to creator prefs: creator only, via RPC that checks creator_email.
DROP POLICY IF EXISTS ccp_no_direct_write ON creator_currency_prefs;
CREATE POLICY ccp_no_direct_write ON creator_currency_prefs
  FOR ALL USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS rc_public_read ON remitter_currencies;
CREATE POLICY rc_public_read ON remitter_currencies
  FOR SELECT USING (true);  -- public so creators see what a remitter pays in

DROP POLICY IF EXISTS rc_no_direct_write ON remitter_currencies;
CREATE POLICY rc_no_direct_write ON remitter_currencies
  FOR ALL USING (false) WITH CHECK (false);

DROP POLICY IF EXISTS rcf_public_read ON remitter_conversion_fees;
CREATE POLICY rcf_public_read ON remitter_conversion_fees
  FOR SELECT USING (true);  -- public disclosure of fees is the whole point

DROP POLICY IF EXISTS rcf_no_direct_write ON remitter_conversion_fees;
CREATE POLICY rcf_no_direct_write ON remitter_conversion_fees
  FOR ALL USING (false) WITH CHECK (false);

-- --------------------------------------------------------------
-- resolve_payout_currency(model_id, remitter_kind, remitter_id)
--
-- Returns the chosen currency and fee_pct for a payout from a given
-- remitter to a given model's creator. Conceptually:
--
--   1. Walk creator prefs in order.
--   2. First currency the remitter supports natively → return with 0 fee.
--   3. If none, return the backup crypto with the remitter's declared
--      conversion fee (from remitter's first native currency → backup).
--   4. If remitter declares no native currencies at all, return the
--      backup crypto with 0 fee (remitter pays in backup directly).
--
-- remitter_kind ∈ ('commercial_user', 'hoster').
-- --------------------------------------------------------------
CREATE OR REPLACE FUNCTION resolve_payout_currency(
  p_model_id      UUID,
  p_remitter_kind TEXT,
  p_remitter_id   UUID
) RETURNS TABLE (
  currency_code TEXT,
  fee_pct       NUMERIC,
  route         TEXT                     -- 'native' | 'backup_with_fee' | 'backup_direct'
) LANGUAGE plpgsql STABLE AS $$
DECLARE
  pref_row   RECORD;
  native_ok  BOOLEAN;
  backup_code TEXT;
  remitter_native TEXT;
  conv_fee  NUMERIC;
BEGIN
  -- Creator's backup crypto (always exactly one per migration 010 trigger).
  SELECT currency_code INTO backup_code
    FROM creator_currency_prefs
    WHERE model_id = p_model_id AND is_backup = true
    LIMIT 1;

  -- Walk prefs in order; first intersection wins.
  FOR pref_row IN
    SELECT currency_code
      FROM creator_currency_prefs
      WHERE model_id = p_model_id
      ORDER BY preference ASC
  LOOP
    SELECT true INTO native_ok
      FROM remitter_currencies rc
      WHERE rc.currency_code = pref_row.currency_code
        AND (
          (p_remitter_kind = 'commercial_user' AND rc.commercial_user_id = p_remitter_id)
          OR
          (p_remitter_kind = 'hoster'          AND rc.hoster_claim_id = p_remitter_id)
        )
      LIMIT 1;
    IF native_ok THEN
      currency_code := pref_row.currency_code;
      fee_pct       := 0;
      route         := 'native';
      RETURN NEXT;
      RETURN;
    END IF;
    native_ok := false;
  END LOOP;

  -- No native match. Find the remitter's first declared native currency
  -- for the conversion fee lookup.
  SELECT rc.currency_code INTO remitter_native
    FROM remitter_currencies rc
    WHERE (p_remitter_kind = 'commercial_user' AND rc.commercial_user_id = p_remitter_id)
       OR (p_remitter_kind = 'hoster'          AND rc.hoster_claim_id = p_remitter_id)
    ORDER BY rc.added_at ASC
    LIMIT 1;

  IF remitter_native IS NULL THEN
    -- Remitter declared no native currencies — assume they pay in backup directly.
    currency_code := backup_code;
    fee_pct       := 0;
    route         := 'backup_direct';
    RETURN NEXT;
    RETURN;
  END IF;

  SELECT rcf.fee_pct INTO conv_fee
    FROM remitter_conversion_fees rcf
    WHERE rcf.from_currency = remitter_native
      AND rcf.to_currency   = backup_code
      AND (rcf.effective_until IS NULL OR rcf.effective_until > now())
      AND (
        (p_remitter_kind = 'commercial_user' AND rcf.commercial_user_id = p_remitter_id)
        OR
        (p_remitter_kind = 'hoster'          AND rcf.hoster_claim_id = p_remitter_id)
      )
    ORDER BY rcf.effective_from DESC
    LIMIT 1;

  IF conv_fee IS NULL THEN
    -- Remitter didn't declare a fee; default to 0. Worst-case for remitter,
    -- best-case for creator — if a remitter wants to charge they must declare.
    conv_fee := 0;
  END IF;

  currency_code := backup_code;
  fee_pct       := conv_fee;
  route         := 'backup_with_fee';
  RETURN NEXT;
END;
$$;

COMMIT;
