-- 0011_ingest_state.sql
--
-- Makes photo ingestion a managed, Cockpit-governed service instead of a one-off
-- batch. One singleton row per Person holds both the CONTROL plane (the Cockpit
-- toggles enabled / requests a run / pauses) and the STATUS plane (the
-- content-ingestor worker reports progress here after each batch). The worker
-- talks only to platform.api (single API plane) — it never reaches iOS directly;
-- iOS reads/writes this state through platform.api's /ingestion endpoints.
--
-- Layers on users/orgs (0001). Reuses 0001 helpers: current_user_uuid() (RLS) +
-- touch_updated_at(). platform.db is Postgres ≥ 14 (INFRA.md §1).

BEGIN;

CREATE TYPE ingest_run_state AS ENUM ('idle', 'running', 'paused');

CREATE TABLE ingest_state (
  user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  org_id         UUID NULL     REFERENCES orgs(id)  ON DELETE CASCADE,

  -- Control plane (set by the Cockpit via POST /ingestion/control)
  enabled        BOOLEAN NOT NULL DEFAULT FALSE,  -- continuous polling on/off
  run_requested  BOOLEAN NOT NULL DEFAULT FALSE,  -- one-shot "run now" trigger; worker clears it

  -- Status plane (reported by the worker via PUT /ingestion/progress)
  state          ingest_run_state NOT NULL DEFAULT 'idle',
  cursor         TIMESTAMPTZ NULL,                -- newest-first paging cursor (photos.taken_at)
  total_photos   INTEGER NOT NULL DEFAULT 0,      -- library size with uploaded bytes
  processed      INTEGER NOT NULL DEFAULT 0,      -- assets classified+landed so far
  hot_count      INTEGER NOT NULL DEFAULT 0,
  stocked_count  INTEGER NOT NULL DEFAULT 0,
  explicit_count INTEGER NOT NULL DEFAULT 0,
  failed_count   INTEGER NOT NULL DEFAULT 0,
  last_run_at    TIMESTAMPTZ NULL,
  last_error     TEXT NULL,

  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (user_id),
  CONSTRAINT ingest_state_counts_chk CHECK (
    total_photos >= 0 AND processed >= 0 AND hot_count >= 0
    AND stocked_count >= 0 AND explicit_count >= 0 AND failed_count >= 0
  )
);
CREATE INDEX idx_ingest_state_org ON ingest_state(org_id) WHERE org_id IS NOT NULL;

CREATE TRIGGER trg_touch_ingest_state BEFORE UPDATE ON ingest_state
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- Row-level security (per 0001 convention). current_user_uuid() reads the
-- app.current_user_id GUC set by platform.api per request.
ALTER TABLE ingest_state ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON ingest_state
  USING (
    user_id = current_user_uuid()
    OR (org_id IS NOT NULL
        AND org_id IN (SELECT org_id FROM org_members
                       WHERE user_id = current_user_uuid()))
  )
  WITH CHECK (
    user_id = current_user_uuid()
    OR (org_id IS NOT NULL
        AND org_id IN (SELECT org_id FROM org_members
                       WHERE user_id = current_user_uuid()))
  );

COMMENT ON TABLE ingest_state IS
  'Singleton-per-Person control+status for photo ingestion. Cockpit sets enabled/run_requested; the content-ingestor worker reports progress. Makes ingestion a managed service surfaced in the iOS Cockpit.';

COMMIT;