-- =============================================================================
-- Recruitment <-> ECP cross-host integration: MENTITY seed for Interview/Application
-- Migration: 20260903 — Recruitment ECP MEntity Seed (SQL Server)
-- Database:  tenant business DB — companion _Postgres.sql defines the identical
--            logical rows for Npgsql hosts.
--
-- CONTEXT (Phase 2 of the ECP video-interview / interview-panel-comments integration plan):
-- RecruitmentBLL.Integration.EcpIntegrationService calls ECP's cross-host endpoints
-- (POST /Meeting/SaveMeeting, POST /MeetingRecording/AttachRecording,
-- POST /CommentThread/SaveCommentThread, ...) passing an ObjectTypeId/ObjectId pair for the
-- Recruitment Interview/Application being linked. TMEETING.OBJECTTYPEID,
-- TRECORDINGTRANSCRIPT.OBJECTTYPEID and TCOMMENTTHREAD.OBJECTTYPEID are all documented as
-- resolving against MENTITY.ENTITYID (see ECPDAL.Meeting.DTOs.MeetingDTO /
-- ECPDAL.Communication.DTOs.CommentThreadDTO doc comments) — no MENTITY row existed for either
-- Recruitment entity, so this migration adds both.
--
-- REUSE, NOT NEW CONSTANTS: EntityConstant.OBJECTRECRUITMENTINTERVIEW (-1392100005) and
-- EntityConstant.OBJECTRECRUITMENTAPPLICATION (-1392100011) already exist in
-- GB5Shared/GB5Constant/Constant.cs — InterviewBLL.SaveInterview and
-- ApplicationBLL.SaveApplication/SaveApplicationStageHistory already pass these exact values as
-- the ExecuteSaveAsync entityId (see InterviewBLL.cs line ~106-114, ApplicationBLL.cs
-- CacheKeyGeneration calls). ExecuteSaveAsync writes that entityId into TOUTBOX.OBJECTTYPEID,
-- which the GOP Qualifier migration (20260725_GOP_Qualifier_MEntity_Seed_SqlServer.sql) already
-- proved has a real FK to MENTITY.ENTITYID on this schema — so both constants were already
-- silently one Interview/Application save away from an
-- "INSERT statement conflicted with the FOREIGN KEY constraint FK_TOUTBOX_OBJECTTYPEID" failure,
-- exactly like the GOP Qualifier gap. This migration fixes that AND makes the same two ids
-- resolvable as the polymorphic ObjectTypeId ECP's Meeting/RecordingTranscript/CommentThread
-- tables expect — one seed, two problems solved. No new EntityConstant values were added.
--
-- PATTERN: cloning a real, working MENTITY row (ROUTINGOPERATION, ENTITYID=-1399999773) via
-- dynamic SQL column-by-column, overriding only ENTITYID/ENTITYCODE/ENTITYNAME/CREATEDON/
-- MODIFIEDON — exact proven pattern from 20260725_GOP_Qualifier_MEntity_Seed_SqlServer.sql —
-- avoids hand-guessing MENTITY's many other FK columns (BASECLASSID, PROJECTID, MODULEID,
-- DBOBJECTID, ASSEMBLYID, MENUID, WEBFORMID, etc.).
--
-- KNOWN LIMITATION (same trade-off GOP's migration accepted, not fixed here): DBOBJECTID is
-- cloned from ROUTINGOPERATION's row, so it does NOT point at a real DBOBJECT row for
-- TINTERVIEW/TAPPLICATION. This is harmless for the FK-satisfaction and ECP-polymorphic-lookup
-- purposes this migration exists for (neither TOUTBOX's FK nor ECP's ObjectTypeId resolution
-- dereferences DBOBJECTID). It WOULD matter if Recruitment's Interview/Application saves are ever
-- routed through the workflow engine's SetEntityStatusAsync (which resolves
-- MENTITY.DBOBJECTID -> DBOBJECT.DBOBJECTNAME to run a dynamic UPDATE) — see
-- 20260903_Recruitment_JobRequisition_Workflow_Seed_SqlServer.sql's activation checklist for the
-- full correction procedure (SELECT/insert a real DBOBJECT row for the table first) if that day
-- comes. Not independently confirmed against a live MENTITY row this session (no gb5-schema MCP
-- access) — verify via gb5-schema MCP before relying on this in production.
-- =============================================================================

IF NOT EXISTS (SELECT 1 FROM MENTITY WHERE ENTITYID = -1392100005)
BEGIN
    DECLARE @cols1 nvarchar(max) = STUFF((
        SELECT ',' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MENTITY'
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '');

    DECLARE @selcols1 nvarchar(max) = STUFF((
        SELECT ',' + CASE COLUMN_NAME
            WHEN 'ENTITYID'   THEN '-1392100005'
            WHEN 'ENTITYCODE' THEN N'''RECRUITMENTINTERVIEW'''
            WHEN 'ENTITYNAME' THEN N'''Recruitment Interview'''
            WHEN 'CREATEDON'  THEN 'GETUTCDATE()'
            WHEN 'MODIFIEDON' THEN 'GETUTCDATE()'
            ELSE QUOTENAME(COLUMN_NAME)
        END
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MENTITY'
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '');

    DECLARE @sql1 nvarchar(max) =
        N'INSERT INTO MENTITY (' + @cols1 + N') SELECT ' + @selcols1 +
        N' FROM MENTITY WHERE ENTITYID = -1399999773';

    EXEC sp_executesql @sql1;
END

IF NOT EXISTS (SELECT 1 FROM MENTITY WHERE ENTITYID = -1392100011)
BEGIN
    DECLARE @cols2 nvarchar(max) = STUFF((
        SELECT ',' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MENTITY'
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '');

    DECLARE @selcols2 nvarchar(max) = STUFF((
        SELECT ',' + CASE COLUMN_NAME
            WHEN 'ENTITYID'   THEN '-1392100011'
            WHEN 'ENTITYCODE' THEN N'''RECRUITMENTAPPLICATION'''
            WHEN 'ENTITYNAME' THEN N'''Recruitment Application'''
            WHEN 'CREATEDON'  THEN 'GETUTCDATE()'
            WHEN 'MODIFIEDON' THEN 'GETUTCDATE()'
            ELSE QUOTENAME(COLUMN_NAME)
        END
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MENTITY'
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '');

    DECLARE @sql2 nvarchar(max) =
        N'INSERT INTO MENTITY (' + @cols2 + N') SELECT ' + @selcols2 +
        N' FROM MENTITY WHERE ENTITYID = -1399999773';

    EXEC sp_executesql @sql2;
END