-- =============================================================================
-- Recruitment Phase 3 — Post-offer tracking (PostgreSQL)
-- Migration: 20260903 — see companion _SqlServer.sql for full rationale/header; both define
-- the identical logical schema. Summary:
--   - MJOBOFFER gets OFFERRESPONSESTATUS (0=Pending 1=Accepted 2=Declined 3=Expired) and
--     OFFERRESPONSEDATE — explicit offer accept/decline signal, separate from the generic
--     repo-wide STATUS lifecycle column (0-5 Pending/Active/Deleted/Amended/Inactive/Archived),
--     which has never carried "did the candidate accept" and shouldn't be overloaded to.
--   - TAPPLICATIONSTAGEHISTORY.OUTCOME is widened from 1=Pending 2=Passed 3=Failed 4=Skipped
--     (Phase 1) to also allow 5=Delayed (in progress, past expectation — e.g. a BG check
--     running long) and 6=DroppedOff (candidate/org withdrew, distinct from a Failed
--     evaluation). A new DROPOFFREASON column (only meaningful when OUTCOME=6) records why:
--     0=N/A 1=CounterOffer 2=DelayedJoining 3=BGVerificationFailed 4=MedicalClearanceFailed
--     5=PersonalReasons 6=Other.
--   - Named PreBoarding sub-stages (e.g. "BG Verification", "Medical Clearance",
--     "Documentation") already work today with zero schema change —
--     MSELECTIONPROCESSSTAGE.STAGECATEGORY=6 (PreBoarding) was reserved for exactly this in
--     the Phase 1 migration; a template just needs stage rows using it. This migration only
--     adds what genuinely doesn't exist yet: the offer-response signal and the richer
--     stage-history outcomes + reason code.
--   - TINYINT -> SMALLINT, DATETIME -> TIMESTAMP, per this repo's usual SqlServer->Postgres
--     mapping (see 20260901_Recruitment_Phase1_CoreATS_Schema_Postgres.sql).
--   - The OUTCOME CHECK-constraint widen uses the dynamic-lookup-then-drop DO $$ pattern
--     already established in this repo for "rename doesn't rename constraints" situations —
--     see 20260902_BIFieldMapping_ComputedField_Postgres.sql: a DO $$ block queries
--     pg_constraint/pg_class (contype = 'c', pg_get_constraintdef ILIKE the column name) to
--     find the existing anonymous/auto-named CHECK constraint, then EXECUTE format(...) to
--     drop it by its discovered name before adding the replacement constraint with an explicit
--     name. Postgres has no ADD CONSTRAINT IF NOT EXISTS, hence the DO $$ guard everywhere else
--     in this file too.
--
-- Column casing convention: ALL CAPS, unquoted (Postgres folds to lowercase; matches how this
-- repo's other dual-DB tables are written). Constraint naming: DF_{TABLE}_{COLUMN} implemented
-- as inline DEFAULT (Postgres has no named-default-constraint syntax), CK_{TABLE}_{COLUMN}.
-- Idempotent via ADD COLUMN IF NOT EXISTS + DO $$ pg_constraint guards.
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. MJOBOFFER — explicit offer response, separate from the generic STATUS column
--    OFFERRESPONSESTATUS: 0=Pending 1=Accepted 2=Declined 3=Expired
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE MJOBOFFER ADD COLUMN IF NOT EXISTS OFFERRESPONSESTATUS SMALLINT NOT NULL DEFAULT 0;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ck_mjoboffer_offerresponsestatus') THEN
        ALTER TABLE MJOBOFFER ADD CONSTRAINT CK_MJOBOFFER_OFFERRESPONSESTATUS CHECK (OFFERRESPONSESTATUS IN (0,1,2,3));
    END IF;
END $$;

ALTER TABLE MJOBOFFER ADD COLUMN IF NOT EXISTS OFFERRESPONSEDATE TIMESTAMP NULL;


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. TAPPLICATIONSTAGEHISTORY — richer outcomes + a structured drop-off reason.
--    OUTCOME was 1=Pending 2=Passed 3=Failed 4=Skipped (Phase 1) — extending to add
--    5=Delayed (in progress, past expectation — e.g. a BG check running long) and
--    6=DroppedOff (candidate/org withdrew, distinct from a Failed evaluation).
--    DROPOFFREASON (only meaningful when OUTCOME=6): 1=CounterOffer 2=DelayedJoining
--    3=BGVerificationFailed 4=MedicalClearanceFailed 5=PersonalReasons 6=Other
--
--    Constraint name discovered dynamically (unquoted Postgres identifiers fold to lowercase) --
--    same rename-doesn't-rename-constraints situation as
--    20260902_BIFieldMapping_ComputedField_Postgres.sql.
-- ─────────────────────────────────────────────────────────────────────────────
DO $$
DECLARE
    ck_name text;
BEGIN
    SELECT con.conname INTO ck_name
    FROM pg_constraint con
    JOIN pg_class rel ON rel.oid = con.conrelid
    WHERE rel.relname = 'tapplicationstagehistory'
      AND con.contype = 'c'
      AND pg_get_constraintdef(con.oid) ILIKE '%outcome%';

    IF ck_name IS NOT NULL THEN
        EXECUTE format('ALTER TABLE tapplicationstagehistory DROP CONSTRAINT %I', ck_name);
    END IF;

    ALTER TABLE tapplicationstagehistory ADD CONSTRAINT ck_tapplicationstagehistory_outcome
        CHECK (outcome IN (1,2,3,4,5,6));
END $$;

ALTER TABLE TAPPLICATIONSTAGEHISTORY ADD COLUMN IF NOT EXISTS DROPOFFREASON SMALLINT NOT NULL DEFAULT 0;
    -- 0 = N/A (not a drop-off row); 1-6 per header comment above

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ck_tapplicationstagehistory_dropoffreason') THEN
        ALTER TABLE TAPPLICATIONSTAGEHISTORY ADD CONSTRAINT CK_TAPPLICATIONSTAGEHISTORY_DROPOFFREASON CHECK (DROPOFFREASON IN (0,1,2,3,4,5,6));
    END IF;
END $$;

-- =============================================================================
-- END OF MIGRATION 20260903_Recruitment_Phase3_PostOfferTracking_Schema (PostgreSQL)
-- =============================================================================