-- =============================================================================
-- Recruitment Phase 6 — Agency management (SQL Server)
-- Migration: 20260905
-- Plan: /Users/venkatv/.claude/plans/can-you-check-the-precious-sunbeam.md, Phase 6
--
-- CONTEXT — placement agencies already submit candidates today via ApplicationSource=3/
-- CandidateDTO.Source=3 ("Agency") and CandidateDTO.SourceDetail (a free-text agency name), but
-- there is no real Agency identity/master record and no GOP-based bulk-intake flow anywhere in
-- this repo (grepped "GOP"/"SourceBinding"/"Qualifier"/"Mapper"/"TargetOperation" across
-- GB5Solution/Recruitment before writing this migration — the only "GOP" hits are
-- BgVerificationVendorIntegrationService's own placeholder doc comments, and the only "Agency"
-- hits are the Source=3 enum label and CandidateSourceQualityReport's per-source rollup). This
-- migration adds the identity + attribution layer only:
--   1. MRECRUITMENTAGENCY — the placement-agency profile, modeled as a DXP Party (identity: legal
--      name, PartyTypeCode) plus this tenant-scoped extension table (contact, contract terms/
--      commission %, specialization tags, this tenant's own ACTIVE/INACTIVE relationship status).
--      See RecruitmentDAL.DTO.Agency.AgencyDTO's doc comment for the full DXP-Party-vs-local
--      split rationale and RecruitmentBLL.Integration.IDxpPartyIntegrationService for how the
--      cross-host DXP Party sync actually reaches DXP's SaveParty endpoint.
--   2. MCANDIDATE.AGENCYID / TAPPLICATION.AGENCYID — additive attribution columns so a future
--      GOP agency-intake flow (Source Binding -> Qualifier -> Mapper -> Target Operation calling
--      SaveCandidate/SaveApplication, per the plan's Phase 1 integration-architecture section)
--      has somewhere to stamp "which agency submitted this batch" the moment it is actually
--      built. NOT wired to any GOP flow by this migration/task — that flow was never implemented
--      (see plan's Phase 1 GOP caveat: "no Flow/Mapper/Source-Binding authoring UI... re-verify
--      GOP's current state before building on it"). This is deliberately just the readiness hook
--      CandidateBLL.SaveCandidate/ApplicationBLL.SaveApplication already persist for free once a
--      caller sets AgencyId on the DTO — no new endpoint was needed for that half.
--
-- FK strategy: AGENCYID on MCANDIDATE/TAPPLICATION is FK-by-convention only (not enforced) —
-- same cross-boundary pattern already established by BgVerificationDTO.ReportAttachmentId /
-- OBJECTRECRUITMENTCANDIDATEDOC in this module. A real FK constraint would require a -1 sentinel
-- row to exist in MRECRUITMENTAGENCY for every tenant (matching the DEFAULT -1 "not attributed"
-- value), which this migration does not seed — kept simple and consistent with the existing
-- precedent rather than introducing a new sentinel-row convention for one column.
--
-- DXPPARTYID on MRECRUITMENTAGENCY is likewise FK-by-convention only — DXP's MDXPPARTY lives in
-- a separate, cross-host database this tenant DB cannot FK into.
--
-- PK strategy: plain INT AutoNumber (key "RECRUITMENTAGENCY"), matching every other Phase 1-6
-- Recruitment table.
-- Idempotent via IF OBJECT_ID(...) IS NULL / IF NOT EXISTS guards. NOT executed against any live
-- database as part of this task.
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. MRECRUITMENTAGENCY — one row per placement agency the tenant works with.
--    DXPPARTYID          — FK-by-convention (not enforced, cross-host) into DXP's
--                          MDXPPARTY.DXPPARTYID; -1 sentinel only until the very first
--                          AgencyBLL.SaveAgency call successfully syncs a DXP Party (in practice
--                          this is always populated before the row commits — see AgencyBLL).
--    AGENCYNAME          — denormalized mirror of the DXP Party's LegalName (DXP's DB is cross-
--                          host; every local list/search screen needs the name without a
--                          cross-database join).
--    COMMISSIONPERCENTAGE — nullable DECIMAL(5,2), e.g. 8.50 = 8.5%; NULL = not yet negotiated.
--    STATUS              — this tenant's own ACTIVE(1)/INACTIVE(4)/Deleted(2) relationship
--                          status, independent of the DXP Party's own Status (0=Pending 1=Active
--                          2=Deleted 3=Amended 4=Inactive 5=Archived, same repo-wide convention).
-- ─────────────────────────────────────────────────────────────────────────────
IF OBJECT_ID('MRECRUITMENTAGENCY', 'U') IS NULL
BEGIN
CREATE TABLE MRECRUITMENTAGENCY
(
    RECRUITMENTAGENCYID     INT             NOT NULL,   -- AutoNumber, key "RECRUITMENTAGENCY"
    DXPPARTYID              INT             NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_DXPPARTYID DEFAULT -1,
    AGENCYNAME              NVARCHAR(200)   NOT NULL,
    CONTACTPERSONNAME       NVARCHAR(150)   NULL,
    CONTACTEMAIL            NVARCHAR(150)   NULL,
    CONTACTMOBILE           NVARCHAR(30)    NULL,
    CONTRACTTERMS           NVARCHAR(MAX)   NULL,
    COMMISSIONPERCENTAGE    DECIMAL(5,2)    NULL,
    SPECIALIZATIONTAGS      NVARCHAR(500)   NULL,       -- comma-separated, e.g. "IT,Engineering,Executive Search"

    SORTORDER               SMALLINT        NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_SORTORDER DEFAULT 9999,
    STATUS                  TINYINT         NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_STATUS DEFAULT 1,
    VERSION                 SMALLINT        NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_VERSION DEFAULT 0,
    TENANTID                INT             NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_TENANTID DEFAULT -1,
    CREATEDBYID             INT             NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_CREATEDBYID DEFAULT -1,
    CREATEDON               DATETIME        NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_CREATEDON DEFAULT GETDATE(),
    MODIFIEDBYID            INT             NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_MODIFIEDBYID DEFAULT -1,
    MODIFIEDON              DATETIME        NOT NULL    CONSTRAINT DF_MRECRUITMENTAGENCY_MODIFIEDON DEFAULT GETDATE(),

    CONSTRAINT PK_MRECRUITMENTAGENCY               PRIMARY KEY (RECRUITMENTAGENCYID),
    CONSTRAINT FK_MRECRUITMENTAGENCY_TENANTID      FOREIGN KEY (TENANTID)     REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT FK_MRECRUITMENTAGENCY_CREATEDBYID   FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_MRECRUITMENTAGENCY_MODIFIEDBYID  FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT CK_MRECRUITMENTAGENCY_STATUS        CHECK (STATUS IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MRECRUITMENTAGENCY_COMMISSIONPCT CHECK (COMMISSIONPERCENTAGE IS NULL OR (COMMISSIONPERCENTAGE >= 0 AND COMMISSIONPERCENTAGE <= 100))
);
END
GO

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

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('MRECRUITMENTAGENCY') AND name = 'IX_MRECRUITMENTAGENCY_DXPPARTYID')
    CREATE INDEX IX_MRECRUITMENTAGENCY_DXPPARTYID ON MRECRUITMENTAGENCY (DXPPARTYID);
GO


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. MCANDIDATE.AGENCYID / TAPPLICATION.AGENCYID — additive attribution columns. Default -1
--    ("not agency-attributed") preserves existing behavior for every row/caller that doesn't set
--    it. FK-by-convention only, not enforced — see header note above.
-- ─────────────────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('MCANDIDATE') AND name = 'AGENCYID')
    ALTER TABLE MCANDIDATE ADD AGENCYID INT NOT NULL CONSTRAINT DF_MCANDIDATE_AGENCYID DEFAULT -1;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('MCANDIDATE') AND name = 'IX_MCANDIDATE_AGENCYID')
    CREATE INDEX IX_MCANDIDATE_AGENCYID ON MCANDIDATE (AGENCYID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('TAPPLICATION') AND name = 'AGENCYID')
    ALTER TABLE TAPPLICATION ADD AGENCYID INT NOT NULL CONSTRAINT DF_TAPPLICATION_AGENCYID DEFAULT -1;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('TAPPLICATION') AND name = 'IX_TAPPLICATION_AGENCYID')
    CREATE INDEX IX_TAPPLICATION_AGENCYID ON TAPPLICATION (AGENCYID);
GO


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. MAUTONUMBER seed for MRECRUITMENTAGENCY — the new table uses a plain application-generated
--    (AutoNumber) INT PK, so it needs its own MAUTONUMBER row before AgencyBLL can call
--    AutoNumber.GetNumberAsync(1, "RECRUITMENTAGENCY", ...).
--
--    ENTITYID/AUTOID values continue the same shared placeholder range used by the prior
--    Recruitment Phase 1-6 AutoNumber seeds (highest confirmed as of this migration: ENTITYID
--    -1826904100 / AUTOID -1500026000, both BGVERIFICATION, from
--    20260904_Recruitment_Phase6_BgVerification_SqlServer.sql). Continuing from that high-water
--    mark, spaced by 100/1000 as this module's convention establishes:
--      RECRUITMENTAGENCY -> -1826904200 / -1500027000
--    Idempotent: guarded by ENTITYCODE existence, matching the Phase 1-6 seeds.
-- ─────────────────────────────────────────────────────────────────────────────
INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID)
SELECT -1826904200, 'RECRUITMENTAGENCY', -1500027000
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'RECRUITMENTAGENCY');
GO


-- ─────────────────────────────────────────────────────────────────────────────
-- 4. MEVENTTYPE seed rows for the 3 new EventTypeConstant entries added to
--    GB5Shared/GB5Constant/Constant.cs's Recruitment block (-1392000058..-1392000060),
--    continuing the Phase 0/1/2/3/5/6 blocks (last used value: -1392000057) with
--    Save/Update/Delete for the new Agency entity. Column set / value conventions mirror the
--    Phase 0-6 seeds exactly: EVENTCATEGORY=1 (Business), ISBIZDOMAIN=1, ISAUDIT=1,
--    RETENTIONDAYS=1095 (3yr warm). SORTORDER continues from 156 (Phase 6 BgVerification used
--    154-156) -> 157,158,159.
-- ─────────────────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM MEVENTTYPE WHERE EVENTTYPEID = -1392000058)
    INSERT INTO MEVENTTYPE
        (EVENTTYPEID, EVENTTYPECODE, EVENTTYPENAME, EVENTCATEGORY, ISBIZDOMAIN, ISAUDIT,
         RETENTIONDAYS, CRITICALITY, LINKEDFORMID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
         STATUS, VERSION, SORTORDER, SOURCETYPE)
    VALUES
        (-1392000058, 'REC_AGENCY_CREATED', 'Recruitment Agency Created', 1, 1, 1,
         1095, 1, -1, -1, '2026-09-05', -1, '2026-09-05', 1, 0, 157, 1);
GO

IF NOT EXISTS (SELECT 1 FROM MEVENTTYPE WHERE EVENTTYPEID = -1392000059)
    INSERT INTO MEVENTTYPE
        (EVENTTYPEID, EVENTTYPECODE, EVENTTYPENAME, EVENTCATEGORY, ISBIZDOMAIN, ISAUDIT,
         RETENTIONDAYS, CRITICALITY, LINKEDFORMID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
         STATUS, VERSION, SORTORDER, SOURCETYPE)
    VALUES
        (-1392000059, 'REC_AGENCY_UPDATED', 'Recruitment Agency Updated', 1, 1, 1,
         1095, 1, -1, -1, '2026-09-05', -1, '2026-09-05', 1, 0, 158, 1);
GO

IF NOT EXISTS (SELECT 1 FROM MEVENTTYPE WHERE EVENTTYPEID = -1392000060)
    INSERT INTO MEVENTTYPE
        (EVENTTYPEID, EVENTTYPECODE, EVENTTYPENAME, EVENTCATEGORY, ISBIZDOMAIN, ISAUDIT,
         RETENTIONDAYS, CRITICALITY, LINKEDFORMID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
         STATUS, VERSION, SORTORDER, SOURCETYPE)
    VALUES
        (-1392000060, 'REC_AGENCY_DELETED', 'Recruitment Agency Deleted', 1, 1, 1,
         1095, 1, -1, -1, '2026-09-05', -1, '2026-09-05', 1, 0, 159, 1);
GO