-- =============================================================================
-- Recruitment Phase 5 — Analytics foundations (SQL Server)
-- Migration: 20260904
-- Plan: /Users/venkatv/.claude/plans/can-you-check-the-precious-sunbeam.md, Phase 5
--
-- Two additions Phase 5's reporting genuinely needs and don't exist yet:
--   1. TJOBREQUISITION.ASSIGNEDRECRUITERID — the recruiter dashboard needs to attribute
--      "open requisitions / pending applications / todo vs done" to a specific recruiter.
--      REQUESTEDBYID is the hiring manager who asked for the position, APPROVEDBYID is
--      whoever approved it — neither is "the recruiter actively working this requisition."
--   2. MCANDIDATEDEMOGRAPHIC — voluntary, self-disclosed demographic data for DEI/diversity
--      analytics, per explicit user decision to build this now (not deferred). Deliberately
--      isolated in its OWN table, never joined into any candidate/application read used during
--      active screening or evaluation — this is the structural enforcement of "blind screening":
--      nobody evaluating a candidate has a code path that can accidentally pull this data in,
--      because it simply isn't present on any query that touches MCANDIDATE/TAPPLICATION.
--      Only a dedicated, aggregation-only DEI reporting BLL should ever query this table
--      directly, and that BLL is responsible for cell-suppression (never returning a breakdown
--      group below a minimum size) — enforced in application code, not by this schema, but the
--      isolation here is what makes that enforcement point exist in exactly one place.
--
--      Every demographic field is nullable with an explicit "PreferNotToSay" value distinct from
--      "never asked" (NULL) — a candidate can decline without the system inferring anything from
--      a blank field. CONSENTTOCOLLECT must be true for the row to be considered valid for
--      reporting; the collecting BLL should refuse to write a populated row without it.
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. TJOBREQUISITION — assigned recruiter
-- ─────────────────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('TJOBREQUISITION') AND name = 'ASSIGNEDRECRUITERID')
    ALTER TABLE TJOBREQUISITION ADD ASSIGNEDRECRUITERID INT NOT NULL CONSTRAINT DF_TJOBREQUISITION_ASSIGNEDRECRUITERID DEFAULT -1;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_TJOBREQUISITION_ASSIGNEDRECRUITERID')
    ALTER TABLE TJOBREQUISITION ADD CONSTRAINT FK_TJOBREQUISITION_ASSIGNEDRECRUITERID FOREIGN KEY (ASSIGNEDRECRUITERID) REFERENCES MUSER(USERID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('TJOBREQUISITION') AND name = 'IX_TJOBREQUISITION_ASSIGNEDRECRUITERID')
    CREATE INDEX IX_TJOBREQUISITION_ASSIGNEDRECRUITERID ON TJOBREQUISITION (ASSIGNEDRECRUITERID);
GO


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. MCANDIDATEDEMOGRAPHIC — voluntary self-disclosed demographic data.
--    GENDERIDENTITY/ETHNICITYRACE: free-text, nullable (candidate's own words, not a closed
--    enum — avoids forcing a candidate into categories that don't fit).
--    DISABILITYSTATUS/VETERANSTATUS/AGEBAND: TINYINT, nullable.
--      0=PreferNotToSay 1=Yes/applicable-range... (exact value sets are reporting-label
--      concerns, deliberately left to application-layer resource strings rather than encoded
--      in this schema's comments, so labels can be localized/adjusted without a migration).
--    One row per candidate — CANDIDATEID is UNIQUE, not just indexed.
-- ─────────────────────────────────────────────────────────────────────────────
IF OBJECT_ID('MCANDIDATEDEMOGRAPHIC', 'U') IS NULL
BEGIN
CREATE TABLE MCANDIDATEDEMOGRAPHIC
(
    CANDIDATEDEMOGRAPHICID   INT             NOT NULL,   -- AutoNumber, key "CANDIDATEDEMOGRAPHIC"
    CANDIDATEID              INT             NOT NULL,
    GENDERIDENTITY           NVARCHAR(100)   NULL,
    ETHNICITYRACE            NVARCHAR(100)   NULL,
    DISABILITYSTATUS         TINYINT         NULL,
    VETERANSTATUS            TINYINT         NULL,
    AGEBAND                  TINYINT         NULL,
    CONSENTTOCOLLECT         BIT             NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_CONSENT DEFAULT 0,
    COLLECTEDON              DATETIME        NULL,
    STATUS                   TINYINT         NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_STATUS DEFAULT 1,
    VERSION                  SMALLINT        NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_VERSION DEFAULT 0,
    TENANTID                 INT             NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_TENANTID DEFAULT -1,
    CREATEDBYID              INT             NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_CREATEDBYID DEFAULT -1,
    CREATEDON                DATETIME        NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_CREATEDON DEFAULT GETDATE(),
    MODIFIEDBYID             INT             NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_MODIFIEDBYID DEFAULT -1,
    MODIFIEDON               DATETIME        NOT NULL    CONSTRAINT DF_MCANDIDATEDEMOGRAPHIC_MODIFIEDON DEFAULT GETDATE(),

    CONSTRAINT PK_MCANDIDATEDEMOGRAPHIC                PRIMARY KEY (CANDIDATEDEMOGRAPHICID),
    CONSTRAINT UQ_MCANDIDATEDEMOGRAPHIC_CANDIDATEID    UNIQUE (CANDIDATEID),
    CONSTRAINT FK_MCANDIDATEDEMOGRAPHIC_CANDIDATEID    FOREIGN KEY (CANDIDATEID) REFERENCES MCANDIDATE(CANDIDATEID),
    CONSTRAINT FK_MCANDIDATEDEMOGRAPHIC_TENANTID       FOREIGN KEY (TENANTID)    REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT FK_MCANDIDATEDEMOGRAPHIC_CREATEDBYID    FOREIGN KEY (CREATEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT FK_MCANDIDATEDEMOGRAPHIC_MODIFIEDBYID   FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT CK_MCANDIDATEDEMOGRAPHIC_STATUS         CHECK (STATUS IN (0,1,2,3,4,5))
);
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('MCANDIDATEDEMOGRAPHIC') AND name = 'IX_MCANDIDATEDEMOGRAPHIC_TENANTID')
    CREATE INDEX IX_MCANDIDATEDEMOGRAPHIC_TENANTID ON MCANDIDATEDEMOGRAPHIC (TENANTID);
GO