-- +goose Up
-- The enterprise connector incremental sync cursor. One row per (organization, connector), holding the
-- watermark (the max updated timestamp ingested so far) so each sync pulls only records changed since the last
-- run, plus lightweight run bookkeeping. It is the state behind the iCIMS ATS and Workday HRIS connectors that
-- flow a consenting pilot tenant's live workforce data into the candidates table through the proven sourcing
-- ingest path. Tenant scoped and RLS forced like every tenant table: a connector cursor can never leak or be
-- written across orgs. The app role reads and writes its own org's cursor (the sync command runs as the app
-- role under a tenant tx); it never deletes one.
CREATE TABLE connector_sync_state (
    organization_id uuid        NOT NULL REFERENCES organizations(id),
    connector_name  text        NOT NULL,          -- the stable connector id, e.g. icims or workday
    watermark       timestamptz NOT NULL DEFAULT '1970-01-01T00:00:00Z',  -- max updated ts ingested; the incremental window floor
    last_run_at     timestamptz,                   -- when the connector last ran for this org
    records_synced  bigint      NOT NULL DEFAULT 0, -- cumulative records ingested, for operator visibility
    PRIMARY KEY (organization_id, connector_name)
);

ALTER TABLE connector_sync_state ENABLE ROW LEVEL SECURITY;
ALTER TABLE connector_sync_state FORCE  ROW LEVEL SECURITY;
CREATE POLICY connector_sync_state_isolation ON connector_sync_state
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- The app role reads its cursor, inserts it on the first run, and advances it each run. It never deletes a
-- cursor (a sync watermark is operational state, not a record to purge in app code).
GRANT SELECT, INSERT, UPDATE ON connector_sync_state TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS connector_sync_state;