-- +goose Up
-- Phase 10, part 2 of 3. The pipeline: applications, their stage history, and the three events that
-- close the funnel.
--
-- The central design decision here is that STAGE HISTORY IS THE SOURCE OF TRUTH and the current stage is
-- a cache maintained by trigger. Every duration and conversion metric in this domain has a definitional
-- fork in it: time to fill can be measured to offer acceptance or to start date, fill rate can be
-- measured in period or by cohort, source can be attributed first touch or last touch. Every one of
-- those is recoverable from a timestamped event stream and unrecoverable from a rollup column. So the
-- events are append only, and applications.stage is derived from them and cannot be written directly.

-- ---------------------------------------------------------------------------
-- rm_stage_ord: the canonical stage ladder as an ordinal, so set based SQL can compare progress without
-- restating the vocabulary. IMMUTABLE and PARALLEL SAFE, pure, reads no table, so it is RLS neutral and
-- usable in an index. Terminal dispositions return negative values: they are outcomes, not rungs, and
-- must never sort as though they were further along than an offer.
-- ---------------------------------------------------------------------------
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_stage_ord(stage text) RETURNS int AS $$
BEGIN
    RETURN CASE stage
        WHEN 'sourced'      THEN 1
        WHEN 'screened'     THEN 2
        WHEN 'submitted'    THEN 3
        WHEN 'interviewing' THEN 4
        WHEN 'offered'      THEN 5
        WHEN 'placed'       THEN 6
        WHEN 'rejected'     THEN -1
        WHEN 'withdrawn'    THEN -2
        ELSE 0
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
-- +goose StatementEnd
GRANT EXECUTE ON FUNCTION rm_stage_ord(text) TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- applications: one candidate considered for one requisition. The junction the product has never had.
-- A candidate may hold many applications and a requisition many candidates, so the pair is the key.
--
-- stage and stage_changed_at are maintained ONLY by the trigger below. The application role is granted
-- UPDATE so it can edit the fields it owns; correctness of stage does not rely on that restraint,
-- because any direct write is overwritten by the next event and every event is recorded.
-- ---------------------------------------------------------------------------
CREATE TABLE applications (
    organization_id  uuid NOT NULL REFERENCES organizations(id),
    id               uuid NOT NULL DEFAULT gen_random_uuid(),
    requisition_id   uuid NOT NULL,
    candidate_id     uuid NOT NULL,
    stage            text NOT NULL DEFAULT 'sourced'
                       CHECK (stage IN ('sourced','screened','submitted','interviewing','offered','placed','rejected','withdrawn')),
    stage_changed_at timestamptz NOT NULL DEFAULT now(),
    -- Why the candidate left the process. Required exactly when the stage is terminal, so a rejection
    -- can never be recorded without a reason and an active application can never carry a stale one.
    disposition_reason text,
    -- Attribution. first_touch is set once at creation and never rewritten; last_touch is the channel
    -- that produced THIS engagement. Storing one mutable column is the flaw behind most unusable source
    -- reporting, because a later touch silently erases how the relationship actually started.
    source_first_touch text NOT NULL DEFAULT '',
    source_last_touch  text NOT NULL DEFAULT '',
    owner_user_id    uuid,
    created_by       uuid,
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, requisition_id, candidate_id),
    FOREIGN KEY (organization_id, requisition_id) REFERENCES requisitions (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, candidate_id)   REFERENCES candidates   (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, owner_user_id)  REFERENCES users        (organization_id, id) ON DELETE SET NULL,
    FOREIGN KEY (organization_id, created_by)     REFERENCES users        (organization_id, id) ON DELETE SET NULL,
    CONSTRAINT applications_disposition_chk CHECK (
        (stage IN ('rejected','withdrawn') AND disposition_reason IS NOT NULL AND length(btrim(disposition_reason)) > 0)
        OR (stage NOT IN ('rejected','withdrawn') AND disposition_reason IS NULL))
);
CREATE INDEX idx_applications_req       ON applications (organization_id, requisition_id, stage);
CREATE INDEX idx_applications_candidate ON applications (organization_id, candidate_id);
CREATE INDEX idx_applications_owner     ON applications (organization_id, owner_user_id);
CREATE INDEX idx_applications_active    ON applications (organization_id, stage_changed_at DESC)
    WHERE stage NOT IN ('placed','rejected','withdrawn');

ALTER TABLE applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE applications FORCE  ROW LEVEL SECURITY;
CREATE POLICY applications_isolation ON applications
    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);
GRANT SELECT, INSERT, UPDATE, DELETE ON applications TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- application_stage_events: append only transition history. This table is the reason every metric in
-- phase 17 is computable and auditable. UPDATE, DELETE and TRUNCATE are refused by trigger and revoked
-- from the application role, exactly as audit_logs is protected.
--
-- occurred_at is separate from created_at so a backdated import records when the transition actually
-- happened while still recording when it was entered.
-- ---------------------------------------------------------------------------
CREATE TABLE application_stage_events (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              bigint GENERATED ALWAYS AS IDENTITY,
    application_id  uuid NOT NULL,
    from_stage      text,
    to_stage        text NOT NULL
                      CHECK (to_stage IN ('sourced','screened','submitted','interviewing','offered','placed','rejected','withdrawn')),
    reason          text NOT NULL DEFAULT '',
    actor_user_id   uuid,
    occurred_at     timestamptz NOT NULL DEFAULT now(),
    created_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, actor_user_id)  REFERENCES users        (organization_id, id) ON DELETE SET NULL,
    CONSTRAINT application_stage_events_no_self_chk CHECK (from_stage IS NULL OR from_stage <> to_stage)
);
CREATE INDEX idx_ase_application ON application_stage_events (organization_id, application_id, occurred_at);
CREATE INDEX idx_ase_to_stage    ON application_stage_events (organization_id, to_stage, occurred_at DESC);

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

CREATE TRIGGER application_stage_events_append_only
    BEFORE UPDATE OR DELETE ON application_stage_events
    FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
CREATE TRIGGER application_stage_events_no_truncate
    BEFORE TRUNCATE ON application_stage_events
    FOR EACH STATEMENT EXECUTE FUNCTION forbid_mutation();

GRANT SELECT, INSERT ON application_stage_events TO missionmatch_app;
REVOKE UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON application_stage_events FROM missionmatch_app;

-- ---------------------------------------------------------------------------
-- The stage cache. An inserted event advances applications.stage, but only when the event is at least as
-- recent as the stage already recorded. Without that guard a backdated import would drag a live
-- application backwards to a stage it left months ago.
-- ---------------------------------------------------------------------------
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_apply_stage_event() RETURNS trigger AS $$
BEGIN
    UPDATE applications
       SET stage              = NEW.to_stage,
           stage_changed_at   = NEW.occurred_at,
           disposition_reason = CASE
               WHEN NEW.to_stage IN ('rejected','withdrawn')
                   THEN NULLIF(btrim(NEW.reason), '')
               ELSE NULL
           END,
           updated_at         = now()
     WHERE organization_id = NEW.organization_id
       AND id              = NEW.application_id
       AND NEW.occurred_at >= stage_changed_at;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd
CREATE TRIGGER application_stage_events_sync
    AFTER INSERT ON application_stage_events
    FOR EACH ROW EXECUTE FUNCTION rm_apply_stage_event();

-- ---------------------------------------------------------------------------
-- interviews, offers and placements all hang off an APPLICATION rather than a candidate, so every one of
-- them is anchored to a specific job. A candidate interviewing for two roles has two interviews, and
-- neither metric can borrow the other's timeline.
-- ---------------------------------------------------------------------------
CREATE TABLE interviews (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              uuid NOT NULL DEFAULT gen_random_uuid(),
    application_id  uuid NOT NULL,
    round           int  NOT NULL DEFAULT 1 CHECK (round BETWEEN 1 AND 20),
    kind            text NOT NULL DEFAULT 'client'
                      CHECK (kind IN ('phone_screen','technical','client','panel','final')),
    scheduled_at    timestamptz,
    completed_at    timestamptz,
    outcome         text NOT NULL DEFAULT 'scheduled'
                      CHECK (outcome IN ('scheduled','passed','failed','no_show','cancelled','rescheduled')),
    interviewer     text NOT NULL DEFAULT '',
    notes           text NOT NULL DEFAULT '',
    created_by      uuid,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, application_id, round),
    FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, created_by)     REFERENCES users        (organization_id, id) ON DELETE SET NULL,
    -- A concluded interview must say when it concluded, and cannot conclude before it was scheduled.
    CONSTRAINT interviews_completed_chk CHECK (
        (outcome IN ('passed','failed','no_show') AND completed_at IS NOT NULL)
        OR outcome NOT IN ('passed','failed','no_show')),
    CONSTRAINT interviews_order_chk CHECK (
        completed_at IS NULL OR scheduled_at IS NULL OR completed_at >= scheduled_at)
);
CREATE INDEX idx_interviews_application ON interviews (organization_id, application_id, round);
CREATE INDEX idx_interviews_upcoming    ON interviews (organization_id, scheduled_at)
    WHERE outcome = 'scheduled';

ALTER TABLE interviews ENABLE ROW LEVEL SECURITY;
ALTER TABLE interviews FORCE  ROW LEVEL SECURITY;
CREATE POLICY interviews_isolation ON interviews
    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);
GRANT SELECT, INSERT, UPDATE, DELETE ON interviews TO missionmatch_app;

-- offers. Contract and permanent carry different money and must never be averaged together: a direct
-- hire fee is the whole gross profit while a contract spread is an hourly delta, so a blended margin
-- percentage moves with mix alone and means nothing.
CREATE TABLE offers (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              uuid NOT NULL DEFAULT gen_random_uuid(),
    application_id  uuid NOT NULL,
    status          text NOT NULL DEFAULT 'extended'
                      CHECK (status IN ('extended','accepted','declined','rescinded','expired')),
    employment_type text NOT NULL DEFAULT 'contract'
                      CHECK (employment_type IN ('contract','contract_to_hire','direct_hire')),
    bill_rate       numeric(12,4) CHECK (bill_rate IS NULL OR bill_rate >= 0),
    pay_rate        numeric(12,4) CHECK (pay_rate  IS NULL OR pay_rate  >= 0),
    salary          numeric(14,2) CHECK (salary    IS NULL OR salary    >= 0),
    fee_pct         numeric(6,4)  CHECK (fee_pct   IS NULL OR (fee_pct >= 0 AND fee_pct <= 1)),
    extended_at     timestamptz NOT NULL DEFAULT now(),
    responded_at    timestamptz,
    expires_at      timestamptz,
    decline_reason  text NOT NULL DEFAULT '',
    created_by      uuid,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, created_by)     REFERENCES users        (organization_id, id) ON DELETE SET NULL,
    CONSTRAINT offers_responded_chk CHECK (
        (status IN ('accepted','declined','rescinded') AND responded_at IS NOT NULL)
        OR status NOT IN ('accepted','declined','rescinded')),
    CONSTRAINT offers_order_chk CHECK (responded_at IS NULL OR responded_at >= extended_at)
);
-- At most one live offer per application. Two simultaneous outstanding offers for the same seat is not a
-- state the business can be in, and allowing it would double count the offer stage.
CREATE UNIQUE INDEX uq_offers_one_live ON offers (organization_id, application_id)
    WHERE status = 'extended';
CREATE INDEX idx_offers_application ON offers (organization_id, application_id, extended_at DESC);
CREATE INDEX idx_offers_open        ON offers (organization_id, expires_at) WHERE status = 'extended';

ALTER TABLE offers ENABLE ROW LEVEL SECURITY;
ALTER TABLE offers FORCE  ROW LEVEL SECURITY;
CREATE POLICY offers_isolation ON offers
    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);
GRANT SELECT, INSERT, UPDATE, DELETE ON offers TO missionmatch_app;

-- placements. start_date is the second time to fill clock and the revenue start. fell_off is separated
-- from terminated because a fall off inside the guarantee period is a commercial event with a clawback,
-- while a later termination is not.
CREATE TABLE placements (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              uuid NOT NULL DEFAULT gen_random_uuid(),
    application_id  uuid NOT NULL,
    offer_id        uuid,
    status          text NOT NULL DEFAULT 'pending_start'
                      CHECK (status IN ('pending_start','active','completed','terminated','fell_off')),
    employment_type text NOT NULL DEFAULT 'contract'
                      CHECK (employment_type IN ('contract','contract_to_hire','direct_hire')),
    start_date      date NOT NULL,
    end_date        date,
    bill_rate       numeric(12,4) CHECK (bill_rate IS NULL OR bill_rate >= 0),
    pay_rate        numeric(12,4) CHECK (pay_rate  IS NULL OR pay_rate  >= 0),
    salary          numeric(14,2) CHECK (salary    IS NULL OR salary    >= 0),
    fee_amount      numeric(14,2) CHECK (fee_amount IS NULL OR fee_amount >= 0),
    guarantee_days  int NOT NULL DEFAULT 90 CHECK (guarantee_days >= 0),
    created_by      uuid,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, offer_id)       REFERENCES offers       (organization_id, id) ON DELETE SET NULL,
    FOREIGN KEY (organization_id, created_by)     REFERENCES users        (organization_id, id) ON DELETE SET NULL,
    CONSTRAINT placements_dates_chk CHECK (end_date IS NULL OR end_date >= start_date),
    CONSTRAINT placements_ended_chk CHECK (
        (status IN ('completed','terminated','fell_off') AND end_date IS NOT NULL)
        OR status NOT IN ('completed','terminated','fell_off')),
    -- A contract placement is priced by rate, a direct hire by salary and fee. Requiring the right shape
    -- stops a margin calculation silently reading a NULL as zero.
    CONSTRAINT placements_pricing_chk CHECK (
        (employment_type = 'direct_hire' AND (salary IS NOT NULL OR fee_amount IS NOT NULL))
        OR (employment_type <> 'direct_hire' AND bill_rate IS NOT NULL AND pay_rate IS NOT NULL)),
    -- Selling below cost is a data entry error, not a business decision worth recording silently.
    CONSTRAINT placements_margin_chk CHECK (
        bill_rate IS NULL OR pay_rate IS NULL OR bill_rate >= pay_rate)
);
-- One live placement per application. A seat cannot be filled twice by the same person on the same order.
CREATE UNIQUE INDEX uq_placements_one_live ON placements (organization_id, application_id)
    WHERE status IN ('pending_start','active');
CREATE INDEX idx_placements_application ON placements (organization_id, application_id);
CREATE INDEX idx_placements_active      ON placements (organization_id, start_date DESC)
    WHERE status IN ('pending_start','active');

ALTER TABLE placements ENABLE ROW LEVEL SECURITY;
ALTER TABLE placements FORCE  ROW LEVEL SECURITY;
CREATE POLICY placements_isolation ON placements
    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);
GRANT SELECT, INSERT, UPDATE, DELETE ON placements TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS placements;
DROP TABLE IF EXISTS offers;
DROP TABLE IF EXISTS interviews;
DROP TRIGGER IF EXISTS application_stage_events_sync ON application_stage_events;
DROP FUNCTION IF EXISTS rm_apply_stage_event();
DROP TABLE IF EXISTS application_stage_events;
DROP TABLE IF EXISTS applications;
DROP FUNCTION IF EXISTS rm_stage_ord(text);