-- =============================================================================
-- GB5 Framework — SysJobShare fixes + SysJob DMS integration seeds (SQL Server)
-- Migration: 20260725 — Framework SysJobShare Fixes
--
-- Fixes applied:
-- 1. Seed MAUTONUMBER entry for SYSJOBSHARE entity (was using WIVERSION by mistake).
-- 2. Add UNIQUE constraint on (SYSJOBID, SHAREDWITHUSERID, TENANTID) to prevent
--    duplicate share rows (SAVE_SYSJOBSHARE SQL now guards this, but the DB
--    constraint is the reliable safety net).
-- 3. Seed MENTITY rows for OBJECTSYSTEMJOB (-1399960001) and OBJECTSYSJOBRUN
--    (-1399960002) so TATTACHMENT can link report output files to TSYSJOB/TSYSJOBRUN.
-- =============================================================================

-- 1. MAUTONUMBER seed for SYSJOBSHARE
--    Insert only if not already present — idempotent.
IF NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SYSJOBSHARE')
BEGIN
    -- Use a negative seed (same pattern as SYSJOBRUN) so generated IDs do not
    -- collide with any positive IDs created by a future IDENTITY column refactor.
    -- ENTITYID is NOT NULL; use -1299990002 (SYSJOBRUN = -1299990001).
    INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID)
    VALUES (-1299990002, 'SYSJOBSHARE', 0);
END
GO

-- 2. Unique constraint on TSYSJOBSHARE — one share record per (job, recipient, tenant)
IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE object_id = OBJECT_ID('TSYSJOBSHARE')
      AND name = 'UK_TSYSJOBSHARE_JOB_USER_TENANT'
)
BEGIN
    ALTER TABLE TSYSJOBSHARE
    ADD CONSTRAINT UK_TSYSJOBSHARE_JOB_USER_TENANT
        UNIQUE (SYSJOBID, SHAREDWITHUSERID, TENANTID);
END
GO

-- 3. MENTITY rows for TSYSJOB and TSYSJOBRUN (needed for TATTACHMENT OBJECTTYPEID)
--    OBJECTSYSTEMJOB = -1399960001, OBJECTSYSJOBRUN = -1399960002
IF NOT EXISTS (SELECT 1 FROM MENTITY WHERE ENTITYID = -1399960001)
BEGIN
    INSERT INTO MENTITY (
        ENTITYID, ENTITYCODE, ENTITYNAME, BASECLASSID, SECURITYLEVEL, ENTITYTYPE, ISPERSIST, SCOPE,
        PROJECTID, MODULEID, DBOBJECTID, ENTITYNATURE, SECTION, IMPLEMENTATIONTYPE, ASSEMBLYID,
        ISCODEAPPLICABLE, MAXCODESIZE, DEFAULTSIZE, ISMULTICATEGORY, ISAUTO, ISMANUAL, ISFORMULA,
        ORGANIZATIONUNITTYPE, ENTITYDEFINEDIN, REMARKS, ISREFERAL, ENTITYCATEGORY, SORTORDER,
        STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
        POCOTODTOLINKID, POCOTOPICKLISTLINKID, CATEGORYMEMBERID,
        ISADDRESS, ISCONTACT, ISSEARCH,
        SAVEDEPLOYMENTID, DELETEDEPLOYMENTID, ADDONDBOBJECTID,
        MENUID, WEBFORMID, DISPLAYWEBFORMID,
        ISICEMAPREQUIRED, ISMULTITENANT, PICKLISTID
    ) VALUES (
        -1399960001, 'SYSTEMJOB', 'System Job',
        -1, 1, 0, 1, 0, -1, -1900000000, -1, 5, 'Framework', 0, -1,
        0, 10, 10, 0, 0, 1, 0,
        0, 'FrameworkDAL.DTO.SystemJob.SystemJobDTO, FrameworkDAL', 'SysJob batch job entity', 0, 0, 9999,
        1, 0, 5, -1, GETDATE(), -1, GETDATE(),
        NULL, NULL, -1,
        0, 0, 0,
        '-1', '-1', -1,
        -1399970000, -1399970001, -1399970001,
        0, 1, -1
    );
END
GO

IF NOT EXISTS (SELECT 1 FROM MENTITY WHERE ENTITYID = -1399960002)
BEGIN
    INSERT INTO MENTITY (
        ENTITYID, ENTITYCODE, ENTITYNAME, BASECLASSID, SECURITYLEVEL, ENTITYTYPE, ISPERSIST, SCOPE,
        PROJECTID, MODULEID, DBOBJECTID, ENTITYNATURE, SECTION, IMPLEMENTATIONTYPE, ASSEMBLYID,
        ISCODEAPPLICABLE, MAXCODESIZE, DEFAULTSIZE, ISMULTICATEGORY, ISAUTO, ISMANUAL, ISFORMULA,
        ORGANIZATIONUNITTYPE, ENTITYDEFINEDIN, REMARKS, ISREFERAL, ENTITYCATEGORY, SORTORDER,
        STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
        POCOTODTOLINKID, POCOTOPICKLISTLINKID, CATEGORYMEMBERID,
        ISADDRESS, ISCONTACT, ISSEARCH,
        SAVEDEPLOYMENTID, DELETEDEPLOYMENTID, ADDONDBOBJECTID,
        MENUID, WEBFORMID, DISPLAYWEBFORMID,
        ISICEMAPREQUIRED, ISMULTITENANT, PICKLISTID
    ) VALUES (
        -1399960002, 'SYSJOBRUN', 'System Job Run',
        -1, 1, 0, 1, 0, -1, -1900000000, -1, 5, 'Framework', 0, -1,
        0, 10, 10, 0, 0, 1, 0,
        0, 'FrameworkDAL.DTO.SysJobRun.SysJobRunDTO, FrameworkDAL', 'SysJob per-attempt run log entity', 0, 0, 9999,
        1, 0, 5, -1, GETDATE(), -1, GETDATE(),
        NULL, NULL, -1,
        0, 0, 0,
        '-1', '-1', -1,
        -1, -1, -1,
        0, 1, -1
    );
END
GO

-- =============================================================================
-- END OF MIGRATION 20260725_Framework_SysJobShare_Fixes (SQL Server variant)
-- =============================================================================