-- =============================================================================
-- Recruitment Phase 1 — Core ATS entities (PostgreSQL)
-- Migration: 20260901 — see companion _SqlServer.sql for full rationale/header; both define
-- the identical logical schema. Summary:
--   - All 7 new tables use a plain application-generated (AutoNumber) INTEGER PK — matches the
--     SqlServer sibling's PK strategy (no SERIAL/IDENTITY anywhere in this file).
--   - TINYINT -> SMALLINT, NVARCHAR(n) -> VARCHAR(n), NVARCHAR(MAX) -> TEXT, DATETIME ->
--     TIMESTAMP, BIT -> BOOLEAN, GETDATE() -> CURRENT_TIMESTAMP, per this repo's usual
--     SqlServer->Postgres mapping (see 20260901_Recruitment_JobOfferInterviewProfileGenerator_
--     Schema_Postgres.sql).
--   - MCANDIDATE.EMPLOYEEID stays a present, non-FK-constrained placeholder (DEFAULT -1) for the
--     future Candidate->Employee conversion-on-hire transition (Phase 3) — same convention as
--     MJOBOFFER/TINTERVIEW.APPLICANTID in the Phase 0 migration.
--   - The two ALTER TABLE blocks at the end (MJOBOFFER/TINTERVIEW.APPLICATIONID) use the
--     information_schema/pg_constraint idempotency pattern already established in this repo's
--     other Postgres ALTER-table migrations (e.g. 20260806_BIView_Schema_Postgres.sql,
--     20260728_Framework_MenuGroup_AlterMenuSet_Postgres.sql): ADD COLUMN IF NOT EXISTS for the
--     column, a DO $$ ... END $$ block guarded by pg_constraint.conname for the FK, and
--     CREATE INDEX IF NOT EXISTS for the index.
--
-- Column casing convention: ALL CAPS, unquoted (Postgres folds to lowercase; matches how this
-- repo's other dual-DB tables are written). Constraint naming: PK_{TABLE} (inline, via
-- PRIMARY KEY column clause), DF_{TABLE}_{COLUMN} implemented as inline DEFAULT (Postgres has no
-- named-default-constraint syntax), CK_{TABLE}_{COLUMN}, FK_{TABLE}_{COLUMN}, IX_{TABLE}_{COLUMNS}.
-- Idempotent via CREATE TABLE/INDEX IF NOT EXISTS.
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. MCANDIDATE
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS MCANDIDATE
(
    CANDIDATEID              INTEGER         PRIMARY KEY,
        -- application-generated (AutoNumber, key "CANDIDATE")
    FIRSTNAME                VARCHAR(100)    NOT NULL,
    LASTNAME                 VARCHAR(100)    NULL,
    EMAIL                    VARCHAR(150)    NOT NULL,
    PHONE                    VARCHAR(30)     NULL,
    RESUMEURL                VARCHAR(500)    NULL,
    SOURCE                   SMALLINT        NOT NULL DEFAULT 1,   -- 1=Website 2=JobPortal 3=Agency 4=Referral 5=Internal 6=Direct/Manual
    SOURCEDETAIL             VARCHAR(200)    NULL,
    SKILLTAGS                TEXT            NULL,
    EMPLOYEEID               INTEGER         NOT NULL DEFAULT -1,
        -- placeholder for Candidate->Employee conversion on hire (Phase 3) — no FK yet, see header
    CONSENTGIVEN             BOOLEAN         NOT NULL DEFAULT FALSE,
    CONSENTDATE              TIMESTAMP       NULL,
    SORTORDER                SMALLINT        NOT NULL DEFAULT 9999,
    STATUS                   SMALLINT        NOT NULL DEFAULT 1,   -- 0 Pending 1 Active 2 Deleted 3 Amended 4 Inactive 5 Archived
    VERSION                  SMALLINT        NOT NULL DEFAULT 0,
    TENANTID                 INTEGER         NOT NULL DEFAULT -1,
    CREATEDBYID              INTEGER         NOT NULL DEFAULT -1,
    CREATEDON                TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID             INTEGER         NOT NULL DEFAULT -1,
    MODIFIEDON               TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_MCANDIDATE_TENANTID           FOREIGN KEY (TENANTID)      REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_MCANDIDATE_CREATEDBYID        FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER (USERID),
    CONSTRAINT FK_MCANDIDATE_MODIFIEDBYID       FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER (USERID),
    CONSTRAINT CK_MCANDIDATE_SOURCE             CHECK (SOURCE IN (1,2,3,4,5,6)),
    CONSTRAINT CK_MCANDIDATE_STATUS             CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_MCANDIDATE_TENANTID ON MCANDIDATE (TENANTID);
CREATE INDEX IF NOT EXISTS IX_MCANDIDATE_EMAIL ON MCANDIDATE (EMAIL);
CREATE INDEX IF NOT EXISTS IX_MCANDIDATE_STATUS ON MCANDIDATE (STATUS);


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. MSELECTIONPROCESSTEMPLATE
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS MSELECTIONPROCESSTEMPLATE
(
    SELECTIONPROCESSTEMPLATEID  INTEGER        PRIMARY KEY,
        -- application-generated (AutoNumber, key "SELECTIONPROCESSTEMPLATE")
    TEMPLATENAME                VARCHAR(150)   NOT NULL,
    TEMPLATECODE                VARCHAR(50)    NOT NULL,
    RECRUITMENTTYPE             SMALLINT       NOT NULL DEFAULT 1,   -- 1=General 2=Campus 3=Lateral 4=Executive 5=Hourly/BlueCollar
    DESCRIPTION                 VARCHAR(500)   NULL,
    SORTORDER                   SMALLINT       NOT NULL DEFAULT 9999,
    STATUS                      SMALLINT       NOT NULL DEFAULT 1,
    VERSION                     SMALLINT       NOT NULL DEFAULT 0,
    TENANTID                    INTEGER        NOT NULL DEFAULT -1,
    CREATEDBYID                 INTEGER        NOT NULL DEFAULT -1,
    CREATEDON                   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID                INTEGER        NOT NULL DEFAULT -1,
    MODIFIEDON                  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_MSELECTIONPROCESSTEMPLATE_TENANTID   FOREIGN KEY (TENANTID)     REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_MSELECTIONPROCESSTEMPLATE_CREATEDBY  FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER (USERID),
    CONSTRAINT FK_MSELECTIONPROCESSTEMPLATE_MODIFIEDBY FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER (USERID),
    CONSTRAINT CK_MSELECTIONPROCESSTEMPLATE_RECTYPE    CHECK (RECRUITMENTTYPE IN (1,2,3,4,5)),
    CONSTRAINT CK_MSELECTIONPROCESSTEMPLATE_STATUS     CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_MSELECTIONPROCESSTEMPLATE_TENANTID ON MSELECTIONPROCESSTEMPLATE (TENANTID);


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. MSELECTIONPROCESSSTAGE
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS MSELECTIONPROCESSSTAGE
(
    SELECTIONPROCESSSTAGEID     INTEGER        PRIMARY KEY,
        -- application-generated (AutoNumber, key "SELECTIONPROCESSSTAGE")
    SELECTIONPROCESSTEMPLATEID  INTEGER        NOT NULL DEFAULT -1,
    STAGENAME                   VARCHAR(150)   NOT NULL,
    STAGEORDER                  SMALLINT       NOT NULL DEFAULT 0,
    STAGECATEGORY               SMALLINT       NOT NULL DEFAULT 1,   -- 1=Applied 2=Screening 3=Interview 4=Assessment 5=Offer 6=PreBoarding 7=Terminal
    ISTERMINAL                  BOOLEAN        NOT NULL DEFAULT FALSE,
    SORTORDER                   SMALLINT       NOT NULL DEFAULT 9999,
    STATUS                      SMALLINT       NOT NULL DEFAULT 1,
    VERSION                     SMALLINT       NOT NULL DEFAULT 0,
    TENANTID                    INTEGER        NOT NULL DEFAULT -1,
    CREATEDBYID                 INTEGER        NOT NULL DEFAULT -1,
    CREATEDON                   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID                INTEGER        NOT NULL DEFAULT -1,
    MODIFIEDON                  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_MSELECTIONPROCESSSTAGE_TEMPLATEID   FOREIGN KEY (SELECTIONPROCESSTEMPLATEID) REFERENCES MSELECTIONPROCESSTEMPLATE (SELECTIONPROCESSTEMPLATEID),
    CONSTRAINT FK_MSELECTIONPROCESSSTAGE_TENANTID     FOREIGN KEY (TENANTID)     REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_MSELECTIONPROCESSSTAGE_CREATEDBY    FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER (USERID),
    CONSTRAINT FK_MSELECTIONPROCESSSTAGE_MODIFIEDBY   FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER (USERID),
    CONSTRAINT CK_MSELECTIONPROCESSSTAGE_CATEGORY     CHECK (STAGECATEGORY IN (1,2,3,4,5,6,7)),
    CONSTRAINT CK_MSELECTIONPROCESSSTAGE_STATUS       CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_MSELECTIONPROCESSSTAGE_TEMPLATEID_ORDER ON MSELECTIONPROCESSSTAGE (SELECTIONPROCESSTEMPLATEID, STAGEORDER);
CREATE INDEX IF NOT EXISTS IX_MSELECTIONPROCESSSTAGE_TENANTID ON MSELECTIONPROCESSSTAGE (TENANTID);


-- ─────────────────────────────────────────────────────────────────────────────
-- 4. TJOBREQUISITION
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS TJOBREQUISITION
(
    JOBREQUISITIONID          INTEGER         PRIMARY KEY,
        -- application-generated (AutoNumber, key "JOBREQUISITION")
    REQUISITIONCODE           VARCHAR(50)     NULL,
    JOBPOSITIONID             INTEGER         NOT NULL DEFAULT -1,
    JOBPROFILEID              INTEGER         NOT NULL DEFAULT -1,
    SELECTIONPROCESSTEMPLATEID INTEGER        NOT NULL DEFAULT -1,
    REQUISITIONTYPE           SMALLINT        NOT NULL DEFAULT 2,   -- 1=Replacement 2=Additional 3=AdHoc
    REPLACEMENTFOREMPLOYEEID  INTEGER         NOT NULL DEFAULT -1,
    DEPARTMENTID              INTEGER         NOT NULL DEFAULT -1,
    NOOFOPENINGS              SMALLINT        NOT NULL DEFAULT 1,
    BUDGETREFERENCEID         INTEGER         NOT NULL DEFAULT -1,
        -- placeholder for the future budget/headcount-approval entity (Phase 6) — see header
    REQUISITIONSTATUS         SMALLINT        NOT NULL DEFAULT 1,   -- 1=Draft 2=PendingApproval 3=Approved 4=Rejected 5=Published 6=OnHold 7=Closed
    REQUESTEDBYID             INTEGER         NOT NULL DEFAULT -1,
    APPROVEDBYID              INTEGER         NOT NULL DEFAULT -1,
    APPROVALDATE              TIMESTAMP       NULL,
    TARGETJOININGDATE         TIMESTAMP       NULL,
    SORTORDER                 SMALLINT        NOT NULL DEFAULT 9999,
    STATUS                    SMALLINT        NOT NULL DEFAULT 1,
    VERSION                   SMALLINT        NOT NULL DEFAULT 0,
    TENANTID                  INTEGER         NOT NULL DEFAULT -1,
    CREATEDBYID               INTEGER         NOT NULL DEFAULT -1,
    CREATEDON                 TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID              INTEGER         NOT NULL DEFAULT -1,
    MODIFIEDON                TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_TJOBREQUISITION_JOBPOSITIONID      FOREIGN KEY (JOBPOSITIONID) REFERENCES MJOBPOSITION (JOBPOSITIONID),
    CONSTRAINT FK_TJOBREQUISITION_JOBPROFILEID       FOREIGN KEY (JOBPROFILEID)  REFERENCES MJOBPROFILE (JOBPROFILEID),
    CONSTRAINT FK_TJOBREQUISITION_SPTEMPLATEID       FOREIGN KEY (SELECTIONPROCESSTEMPLATEID) REFERENCES MSELECTIONPROCESSTEMPLATE (SELECTIONPROCESSTEMPLATEID),
    CONSTRAINT FK_TJOBREQUISITION_DEPARTMENTID       FOREIGN KEY (DEPARTMENTID)  REFERENCES MDEPARTMENT (DEPARTMENTID),
    CONSTRAINT FK_TJOBREQUISITION_TENANTID           FOREIGN KEY (TENANTID)      REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_TJOBREQUISITION_CREATEDBYID        FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER (USERID),
    CONSTRAINT FK_TJOBREQUISITION_MODIFIEDBYID       FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER (USERID),
    CONSTRAINT CK_TJOBREQUISITION_REQTYPE            CHECK (REQUISITIONTYPE IN (1,2,3)),
    CONSTRAINT CK_TJOBREQUISITION_REQSTATUS          CHECK (REQUISITIONSTATUS IN (1,2,3,4,5,6,7)),
    CONSTRAINT CK_TJOBREQUISITION_STATUS             CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_TJOBREQUISITION_TENANTID ON TJOBREQUISITION (TENANTID);
CREATE INDEX IF NOT EXISTS IX_TJOBREQUISITION_STATUS_REQSTATUS ON TJOBREQUISITION (STATUS, REQUISITIONSTATUS);
CREATE INDEX IF NOT EXISTS IX_TJOBREQUISITION_JOBPOSITIONID ON TJOBREQUISITION (JOBPOSITIONID);


-- ─────────────────────────────────────────────────────────────────────────────
-- 5. TAPPLICATION
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS TAPPLICATION
(
    APPLICATIONID             INTEGER         PRIMARY KEY,
        -- application-generated (AutoNumber, key "APPLICATION")
    CANDIDATEID               INTEGER         NOT NULL DEFAULT -1,
    JOBREQUISITIONID          INTEGER         NOT NULL DEFAULT -1,
    CURRENTSTAGEID            INTEGER         NOT NULL DEFAULT -1,
    APPLICATIONSOURCE         SMALLINT        NOT NULL DEFAULT 1,   -- 1=Website 2=JobPortal 3=Agency 4=Referral 5=Internal 6=Direct/Manual
    APPLIEDON                 TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SORTORDER                 SMALLINT        NOT NULL DEFAULT 9999,
    STATUS                    SMALLINT        NOT NULL DEFAULT 1,
    VERSION                   SMALLINT        NOT NULL DEFAULT 0,
    TENANTID                  INTEGER         NOT NULL DEFAULT -1,
    CREATEDBYID               INTEGER         NOT NULL DEFAULT -1,
    CREATEDON                 TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID              INTEGER         NOT NULL DEFAULT -1,
    MODIFIEDON                TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_TAPPLICATION_CANDIDATEID        FOREIGN KEY (CANDIDATEID)      REFERENCES MCANDIDATE (CANDIDATEID),
    CONSTRAINT FK_TAPPLICATION_JOBREQUISITIONID   FOREIGN KEY (JOBREQUISITIONID) REFERENCES TJOBREQUISITION (JOBREQUISITIONID),
    CONSTRAINT FK_TAPPLICATION_CURRENTSTAGEID     FOREIGN KEY (CURRENTSTAGEID)   REFERENCES MSELECTIONPROCESSSTAGE (SELECTIONPROCESSSTAGEID),
    CONSTRAINT FK_TAPPLICATION_TENANTID           FOREIGN KEY (TENANTID)         REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_TAPPLICATION_CREATEDBYID        FOREIGN KEY (CREATEDBYID)      REFERENCES MUSER (USERID),
    CONSTRAINT FK_TAPPLICATION_MODIFIEDBYID       FOREIGN KEY (MODIFIEDBYID)     REFERENCES MUSER (USERID),
    CONSTRAINT CK_TAPPLICATION_SOURCE             CHECK (APPLICATIONSOURCE IN (1,2,3,4,5,6)),
    CONSTRAINT CK_TAPPLICATION_STATUS             CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_TAPPLICATION_TENANTID ON TAPPLICATION (TENANTID);
CREATE INDEX IF NOT EXISTS IX_TAPPLICATION_CANDIDATEID ON TAPPLICATION (CANDIDATEID);
CREATE INDEX IF NOT EXISTS IX_TAPPLICATION_JOBREQUISITIONID_STAGE ON TAPPLICATION (JOBREQUISITIONID, CURRENTSTAGEID);


-- ─────────────────────────────────────────────────────────────────────────────
-- 6. TAPPLICATIONSTAGEHISTORY
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS TAPPLICATIONSTAGEHISTORY
(
    APPLICATIONSTAGEHISTORYID INTEGER         PRIMARY KEY,
        -- application-generated (AutoNumber, key "APPLICATIONSTAGEHISTORY")
    APPLICATIONID             INTEGER         NOT NULL DEFAULT -1,
    SELECTIONPROCESSSTAGEID   INTEGER         NOT NULL DEFAULT -1,
    STAGEENTEREDON             TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    STAGEEXITEDON              TIMESTAMP      NULL,
    OUTCOME                    SMALLINT       NOT NULL DEFAULT 1,   -- 1=Pending 2=Passed 3=Failed 4=Skipped
    EVALUATORID                INTEGER        NOT NULL DEFAULT -1,
    RECOMMENDATION             TEXT           NULL,
    REMARKS                    TEXT           NULL,
    STATUS                     SMALLINT       NOT NULL DEFAULT 1,
    VERSION                    SMALLINT       NOT NULL DEFAULT 0,
    TENANTID                   INTEGER        NOT NULL DEFAULT -1,
    CREATEDBYID                INTEGER        NOT NULL DEFAULT -1,
    CREATEDON                  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID               INTEGER        NOT NULL DEFAULT -1,
    MODIFIEDON                 TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_TAPPLICATIONSTAGEHISTORY_APPID        FOREIGN KEY (APPLICATIONID) REFERENCES TAPPLICATION (APPLICATIONID),
    CONSTRAINT FK_TAPPLICATIONSTAGEHISTORY_STAGEID      FOREIGN KEY (SELECTIONPROCESSSTAGEID) REFERENCES MSELECTIONPROCESSSTAGE (SELECTIONPROCESSSTAGEID),
    CONSTRAINT FK_TAPPLICATIONSTAGEHISTORY_TENANTID     FOREIGN KEY (TENANTID)      REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_TAPPLICATIONSTAGEHISTORY_CREATEDBYID  FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER (USERID),
    CONSTRAINT FK_TAPPLICATIONSTAGEHISTORY_MODIFIEDBYID FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER (USERID),
    CONSTRAINT CK_TAPPLICATIONSTAGEHISTORY_OUTCOME      CHECK (OUTCOME IN (1,2,3,4)),
    CONSTRAINT CK_TAPPLICATIONSTAGEHISTORY_STATUS       CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_TAPPLICATIONSTAGEHISTORY_APPID_ENTEREDON ON TAPPLICATIONSTAGEHISTORY (APPLICATIONID, STAGEENTEREDON);
CREATE INDEX IF NOT EXISTS IX_TAPPLICATIONSTAGEHISTORY_TENANTID ON TAPPLICATIONSTAGEHISTORY (TENANTID);


-- ─────────────────────────────────────────────────────────────────────────────
-- 7. MJOBPROFILELIBRARY
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS MJOBPROFILELIBRARY
(
    JOBPROFILELIBRARYID       INTEGER         PRIMARY KEY,
        -- application-generated (AutoNumber, key "JOBPROFILELIBRARY")
    ROLETITLE                 VARCHAR(150)    NOT NULL,
    INDUSTRYTAG                VARCHAR(100)   NULL,
    LEVELTAG                   VARCHAR(50)    NULL,
    JOBDESCRIPTION             TEXT           NULL,
    REQUIREDSKILLS             TEXT           NULL,
    RESPONSIBILITIES           TEXT           NULL,
    QUALIFICATIONS             TEXT           NULL,
    SORTORDER                  SMALLINT       NOT NULL DEFAULT 9999,
    STATUS                     SMALLINT       NOT NULL DEFAULT 1,
    VERSION                    SMALLINT       NOT NULL DEFAULT 0,
    TENANTID                   INTEGER        NOT NULL DEFAULT -1,
    CREATEDBYID                INTEGER        NOT NULL DEFAULT -1,
    CREATEDON                  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    MODIFIEDBYID               INTEGER        NOT NULL DEFAULT -1,
    MODIFIEDON                 TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_MJOBPROFILELIBRARY_TENANTID        FOREIGN KEY (TENANTID)      REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_MJOBPROFILELIBRARY_CREATEDBYID     FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER (USERID),
    CONSTRAINT FK_MJOBPROFILELIBRARY_MODIFIEDBYID    FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER (USERID),
    CONSTRAINT CK_MJOBPROFILELIBRARY_STATUS          CHECK (STATUS IN (0,1,2,3,4,5))
);

CREATE INDEX IF NOT EXISTS IX_MJOBPROFILELIBRARY_TENANTID ON MJOBPROFILELIBRARY (TENANTID);


-- ─────────────────────────────────────────────────────────────────────────────
-- 8. Link existing JobOffer/Interview to the new Application entity. APPLICANTID (the
--    Phase 0 placeholder) is kept as-is for backward compatibility with already-fixed
--    Phase 0 code; APPLICATIONID is the real FK new code should use going forward.
--    Idempotency pattern matches this repo's existing Postgres ALTER-table migrations
--    (e.g. 20260806_BIView_Schema_Postgres.sql, 20260728_Framework_MenuGroup_AlterMenuSet_
--    Postgres.sql): ADD COLUMN IF NOT EXISTS + a DO $$ block guarded by pg_constraint.conname
--    for the FK (Postgres has no ADD CONSTRAINT IF NOT EXISTS) + CREATE INDEX IF NOT EXISTS.
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE MJOBOFFER ADD COLUMN IF NOT EXISTS APPLICATIONID INTEGER NOT NULL DEFAULT -1;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_mjoboffer_applicationid') THEN
        ALTER TABLE MJOBOFFER ADD CONSTRAINT FK_MJOBOFFER_APPLICATIONID FOREIGN KEY (APPLICATIONID) REFERENCES TAPPLICATION (APPLICATIONID);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS IX_MJOBOFFER_APPLICATIONID ON MJOBOFFER (APPLICATIONID);

ALTER TABLE TINTERVIEW ADD COLUMN IF NOT EXISTS APPLICATIONID INTEGER NOT NULL DEFAULT -1;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_tinterview_applicationid') THEN
        ALTER TABLE TINTERVIEW ADD CONSTRAINT FK_TINTERVIEW_APPLICATIONID FOREIGN KEY (APPLICATIONID) REFERENCES TAPPLICATION (APPLICATIONID);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS IX_TINTERVIEW_APPLICATIONID ON TINTERVIEW (APPLICATIONID);

-- =============================================================================
-- END OF MIGRATION 20260901_Recruitment_Phase1_CoreATS_Schema (PostgreSQL)
-- =============================================================================