-- +goose Up
-- Corrects the hold clock on requisitions and adds the status event trail.
--
-- 0047 stored held_days as an int. That silently discards every hold shorter than a day. A job order
-- put on hold Friday evening and resumed Monday morning three separate times accrued 30 real hours of
-- hold and would have recorded zero, because each period truncates to 0 before it is added. The error is
-- one directional, it always under counts, so adjusted time to fill would always read longer than the
-- truth and no amount of data would reveal it.
--
-- The accumulator is now exact seconds as numeric. Seconds rather than an interval on purpose: summing
-- intervals that carry a day component and then subtracting them from a timestamptz is ambiguous across
-- a daylight saving boundary, because a calendar day is not always 86400 seconds. EXTRACT(EPOCH FROM
-- (a - b)) is an unambiguous count of elapsed seconds at microsecond resolution, and every arithmetic
-- step after that is exact numeric, never floating point.
--
-- held_since is the open bracket of the current hold. The pairing invariant below is what makes the
-- accumulator trustworthy: it is impossible to be on hold without an open bracket, and impossible to
-- carry an open bracket while not on hold. Without it a requisition closed while on hold would keep
-- accruing hold time forever against a dead row.
--
-- Safe to rewrite the column: requisitions is empty in every environment at the time of this migration.

ALTER TABLE requisitions DROP COLUMN held_days;

ALTER TABLE requisitions
    ADD COLUMN held_seconds numeric(20,6) NOT NULL DEFAULT 0 CHECK (held_seconds >= 0),
    ADD COLUMN held_since   timestamptz;

-- The pairing invariant. Together with requisitions_closed_at_chk from 0047 this makes the three clocks
-- on a requisition, opened_at, closed_at and the hold accumulator, mutually consistent for every legal
-- status, enforced by the database rather than by the care of whoever writes the next service method.
ALTER TABLE requisitions ADD CONSTRAINT requisitions_held_since_chk CHECK (
    (status = 'on_hold' AND held_since IS NOT NULL)
    OR (status <> 'on_hold' AND held_since IS NULL));

-- A hold cannot have started before the order was opened, and cannot be settled against a close that
-- happened first. Both would make the adjusted age negative, which is the one arithmetic outcome that
-- is never a real measurement.
ALTER TABLE requisitions ADD CONSTRAINT requisitions_held_since_after_open_chk CHECK (
    held_since IS NULL OR held_since >= opened_at);

-- ---------------------------------------------------------------------------
-- Closes an integrity hole in 0047. hiring_manager_id referenced client_contacts by (organization_id,
-- id), which proves the contact exists somewhere in the tenant but not that it belongs to THIS client.
-- Nothing stopped a job order at one client naming a hiring manager who works at another, and the first
-- symptom would have been a submittal emailed to the wrong company.
--
-- The fix is a composite foreign key that carries client_id, so the database checks the contact belongs
-- to the client on the order. It needs a unique key on the referenced side to point at.
ALTER TABLE client_contacts ADD CONSTRAINT uq_client_contacts_client_id UNIQUE (organization_id, client_id, id);

ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_hiring_manager_id_fkey;

-- A composite foreign key with any NULL column is not checked at all under MATCH SIMPLE, so a manager
-- attached to a clientless order is not verified by the database. The obvious remedy, a CHECK requiring a
-- client whenever a manager is named, was tried and removed, because it cannot coexist with the cascade:
--
--   Deleting a client fires two independent referential actions on this table, one nulling client_id and
--   one nulling hiring_manager_id by way of the cascade through client_contacts. Postgres applies each as
--   its own statement and evaluates row constraints after each, so whichever runs first leaves the row
--   momentarily inconsistent and the CHECK aborts the delete. Postgres does not support DEFERRABLE on a
--   CHECK, and MATCH FULL is not usable here because organization_id is never null, so it would reject
--   every legitimately clientless order. Verified against the live schema: the constraint reintroduced
--   the exact deletion failure that migration 0053 exists to remove.
--
-- The rule is therefore enforced in the service, which rejects a hiring manager without a client before
-- the insert is attempted. The database still guarantees the part that matters and that the application
-- cannot check without a race: when both are present, the manager works at that client.
-- The column list on SET NULL is load bearing, not decoration. A bare ON DELETE SET NULL on a composite
-- key nulls EVERY referencing column, so deleting one contact would blank organization_id, which is NOT
-- NULL and would abort, and would also silently detach the order from its client. Naming the column
-- confines the effect to the manager, which is the only part that stopped being true. Postgres 15 and
-- later support this form and this deployment is on 16.
ALTER TABLE requisitions ADD CONSTRAINT requisitions_hiring_manager_fkey
    FOREIGN KEY (organization_id, client_id, hiring_manager_id)
    REFERENCES client_contacts (organization_id, client_id, id)
    ON DELETE SET NULL (hiring_manager_id);

-- Deleting a client leaves hiring_manager_id dangling, and this is what clears it.
--
-- Verified behaviour: deleting a client fires two referential actions on this table. The one nulling
-- client_id runs first, and once client_id is NULL the composite key contains a NULL, so under MATCH
-- SIMPLE the manager key is no longer enforced and the cascade that deletes the client's contacts never
-- nulls hiring_manager_id. The row is left pointing at a contact that no longer exists.
--
-- The effect is invisible in the interface, because the name is resolved by a join that now finds
-- nothing, which is precisely why it needs handling here rather than being left to be noticed. A trigger
-- is used because no declarative constraint can express it: the rule spans two foreign keys whose actions
-- Postgres applies as separate statements.
--
-- Restricted to UPDATE deliberately. On INSERT the same situation is a mistake by the caller, and the
-- service rejects it with a message naming the field rather than silently discarding what was typed.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_clear_orphaned_hiring_manager() RETURNS trigger AS $$
BEGIN
    IF NEW.client_id IS NULL AND NEW.hiring_manager_id IS NOT NULL THEN
        NEW.hiring_manager_id := NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE TRIGGER requisitions_clear_orphaned_manager
    BEFORE UPDATE ON requisitions
    FOR EACH ROW EXECUTE FUNCTION rm_clear_orphaned_hiring_manager();

-- ---------------------------------------------------------------------------
-- requisition_events. Append only, because every metric Phase 17 reports about how long an order sat in
-- a state is derived from this table, and a derived metric is only auditable if its inputs cannot be
-- rewritten after the fact. Recording it from the first status change means the history exists when the
-- reporting is built, rather than starting from whenever someone remembered to add it.
-- ---------------------------------------------------------------------------
CREATE TABLE requisition_events (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              uuid NOT NULL DEFAULT gen_random_uuid(),
    requisition_id  uuid NOT NULL,
    from_status     text,
    to_status       text NOT NULL,
    -- Seconds spent in from_status, closed out at the moment of this event. Stored rather than derived
    -- so a report does not have to self join the table to the previous row per requisition, and so the
    -- number stays correct if a future migration ever changes what a status means.
    seconds_in_from numeric(20,6) CHECK (seconds_in_from IS NULL OR seconds_in_from >= 0),
    note            text NOT NULL DEFAULT '',
    actor_user_id   uuid,
    -- The actor's name at the time of the event, copied rather than joined.
    --
    -- actor_user_id is nulled when a user is deleted, which is required for offboarding and erasure. If
    -- the name were only reachable through that key, deleting a recruiter would silently rewrite years of
    -- history into "unknown" and destroy the audit value of the table this trigger exists to protect.
    -- Denormalising the name keeps the history legible after the reference is gone, and it is the correct
    -- value regardless: it records who acted THEN, not who happens to hold that user row now.
    actor_name      text NOT NULL DEFAULT '',
    occurred_at     timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    FOREIGN KEY (organization_id, requisition_id) REFERENCES requisitions (organization_id, id) ON DELETE CASCADE,
    -- The column list is required. A bare SET NULL on a composite key nulls organization_id too, which is
    -- NOT NULL, so deleting any user would abort. Migration 0053 repairs twenty three constraints across
    -- this schema that made exactly that mistake; this one is written correctly from the start.
    FOREIGN KEY (organization_id, actor_user_id)  REFERENCES users (organization_id, id)
        ON DELETE SET NULL (actor_user_id),
    -- The first event of a requisition's life has no predecessor. Every later one must name what it left,
    -- so the chain cannot silently lose a link.
    CONSTRAINT requisition_events_from_chk CHECK (
        (from_status IS NULL AND seconds_in_from IS NULL)
        OR (from_status IS NOT NULL AND seconds_in_from IS NOT NULL)),
    CONSTRAINT requisition_events_to_chk CHECK (
        to_status IN ('draft', 'open', 'on_hold', 'filled', 'closed', 'cancelled')),
    CONSTRAINT requisition_events_from_vocab_chk CHECK (
        from_status IS NULL OR from_status IN ('draft', 'open', 'on_hold', 'filled', 'closed', 'cancelled'))
);

CREATE INDEX idx_requisition_events_req ON requisition_events (organization_id, requisition_id, occurred_at);

ALTER TABLE requisition_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE requisition_events FORCE  ROW LEVEL SECURITY;
CREATE POLICY requisition_events_isolation ON requisition_events
    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);

-- Only SELECT and INSERT reach the application role. The append only property has two independent
-- barriers, the missing grant and the trigger, so neither one being wrong on its own is enough to let
-- history be rewritten.
GRANT SELECT, INSERT ON requisition_events TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- Widens the append only guard by exactly one case, and fixes the same latent conflict on
-- application_stage_events, which shares this function.
--
-- A foreign key declared ON DELETE SET NULL performs an UPDATE on the referencing row. The guard refuses
-- every UPDATE from the application role, so deleting a user who ever changed a requisition status or
-- moved a candidate through a stage would be refused by the trigger even after 0053 makes the constraint
-- itself valid. That is the same shape of defect as 0050: a rule written to protect history instead
-- making a legitimate lifecycle operation impossible.
--
-- The exemption is deliberately narrow. It permits only an update that clears actor_user_id from a value
-- to NULL while every other column is byte identical, which is precisely and only what the referential
-- action does. The jsonb comparison is what makes that precise rather than approximate: any other column
-- differing makes the two objects unequal and the update is refused. History still cannot be rewritten,
-- and the actor's name survives in actor_name regardless.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_forbid_event_mutation() RETURNS trigger AS $$
BEGIN
    IF current_user = 'missionmatch_migrator' THEN
        IF TG_OP = 'DELETE' THEN
            RETURN OLD;
        END IF;
        RETURN NEW;
    END IF;
    IF TG_OP = 'UPDATE'
       AND OLD.actor_user_id IS NOT NULL
       AND NEW.actor_user_id IS NULL
       AND (to_jsonb(NEW) - 'actor_user_id') = (to_jsonb(OLD) - 'actor_user_id') THEN
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'append-only table: % not permitted for role %', TG_OP, current_user;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE TRIGGER requisition_events_append_only
    BEFORE UPDATE OR DELETE ON requisition_events
    FOR EACH ROW EXECUTE FUNCTION rm_forbid_event_mutation();
CREATE TRIGGER requisition_events_no_truncate
    BEFORE TRUNCATE ON requisition_events
    FOR EACH STATEMENT EXECUTE FUNCTION rm_forbid_event_mutation();

-- Listing a client's job orders and a recruiter's own desk are the two reads the dashboard makes on
-- every load, so both get an index that already carries the filter.
CREATE INDEX idx_requisitions_org_client ON requisitions (organization_id, client_id, status) WHERE deleted_at IS NULL;
CREATE INDEX idx_requisitions_org_owner  ON requisitions (organization_id, owner_user_id, status) WHERE deleted_at IS NULL;

-- +goose Down
-- Restores the 0050 form of the guard, without the referential exemption.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_forbid_event_mutation() RETURNS trigger AS $$
BEGIN
    IF current_user = 'missionmatch_migrator' THEN
        IF TG_OP = 'DELETE' THEN
            RETURN OLD;
        END IF;
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'append-only table: % not permitted for role %', TG_OP, current_user;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd
ALTER TABLE requisitions DROP CONSTRAINT IF EXISTS requisitions_hiring_manager_fkey;
ALTER TABLE client_contacts DROP CONSTRAINT IF EXISTS uq_client_contacts_client_id;
-- The column list form is kept on the way back down, deliberately, and this is the one place this down
-- does not restore the previous definition byte for byte. The original was a bare ON DELETE SET NULL on a
-- composite key, which nulls EVERY column in the key including organization_id, and organization_id is
-- NOT NULL on requisitions, so deleting a client contact aborts and the contact becomes undeletable.
-- Migration 0053 exists to repair exactly that defect across twenty three constraints. Reinstating it here
-- would hand 0053 back a broken constraint it had already fixed and silently break contact offboarding for
-- anyone who rolled this migration back. A down migration restores the previous SHAPE of the schema, not a
-- known defect that a later migration has already ruled out.
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_hiring_manager_id_fkey
    FOREIGN KEY (organization_id, hiring_manager_id)
    REFERENCES client_contacts (organization_id, id) ON DELETE SET NULL (hiring_manager_id);
DROP TRIGGER IF EXISTS requisitions_clear_orphaned_manager ON requisitions;
DROP FUNCTION IF EXISTS rm_clear_orphaned_hiring_manager();
DROP INDEX IF EXISTS idx_requisitions_org_owner;
DROP INDEX IF EXISTS idx_requisitions_org_client;
DROP TABLE IF EXISTS requisition_events;
ALTER TABLE requisitions DROP CONSTRAINT IF EXISTS requisitions_held_since_after_open_chk;
ALTER TABLE requisitions DROP CONSTRAINT IF EXISTS requisitions_held_since_chk;
ALTER TABLE requisitions DROP COLUMN IF EXISTS held_since;
ALTER TABLE requisitions DROP COLUMN IF EXISTS held_seconds;
ALTER TABLE requisitions ADD COLUMN held_days int NOT NULL DEFAULT 0 CHECK (held_days >= 0);