-- =============================================================================
-- GB5 Framework — TSYSJOBSHARE IDENTITY/AutoNumber fix (SQL Server)
-- Migration: 20260808 — Framework SysJobShare IdentityFix
--
-- Live-discovered bug (2026-08-08): TSYSJOBSHARE.SYSJOBSHAREID was created as an
-- IDENTITY column on this install, even though the original DDL (SysJob_Enhancements.sql)
-- declares it as a plain INT NOT NULL PK and SAVE_SYSJOBSHARE (SysJobShareQB.cs) always
-- supplies an explicit AutoNumber-generated value for it. Every SaveSysJobShare call
-- failed with SQL error 544 ("Cannot insert explicit value for identity column ...").
-- Same bug class as the earlier TSYSJOBRUN IDENTITY-vs-AutoNumber fix.
--
-- This mirrors that fix: drop + recreate TSYSJOBSHARE without IDENTITY. Guarded to only
-- run when the table is both IDENTITY-afflicted AND empty — if it ever has real share
-- rows on some install, this migration intentionally does nothing and must be
-- hand-migrated instead of blindly dropping data.
--
-- Also see 20260725_Framework_SysJobShare_Fixes_SqlServer.sql (already-applied MAUTONUMBER
-- seed + UK_TSYSJOBSHARE_JOB_USER_TENANT constraint, both recreated identically here).
-- =============================================================================

IF EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID('TSYSJOBSHARE') AND name = 'SYSJOBSHAREID' AND is_identity = 1
)
BEGIN
    IF (SELECT COUNT(*) FROM TSYSJOBSHARE) = 0
    BEGIN
        DROP TABLE TSYSJOBSHARE;

        CREATE TABLE TSYSJOBSHARE (
            SYSJOBSHAREID    INT      NOT NULL,
            SYSJOBID         INT      NOT NULL,
            SHAREDWITHUSERID INT      NOT NULL,
            CANEXECUTE       BIT      NOT NULL CONSTRAINT DF_TSYSJOBSHARE_CANEXECUTE    DEFAULT 0,
            CANVIEWRESULT    BIT      NOT NULL CONSTRAINT DF_TSYSJOBSHARE_CANVIEWRESULT DEFAULT 0,
            SHAREDBYID       INT      NOT NULL,
            SHAREDON         DATETIME NOT NULL CONSTRAINT DF_TSYSJOBSHARE_SHAREDON      DEFAULT GETUTCDATE(),
            TENANTID         INT      NOT NULL,
            CONSTRAINT PK_TSYSJOBSHARE PRIMARY KEY (SYSJOBSHAREID),
            CONSTRAINT FK_TSYSJOBSHARE_SYSJOB FOREIGN KEY (SYSJOBID) REFERENCES TSYSJOB(SYSJOBID),
            CONSTRAINT UK_TSYSJOBSHARE_JOB_USER_TENANT UNIQUE (SYSJOBID, SHAREDWITHUSERID, TENANTID)
        );

        CREATE INDEX IX_TSYSJOBSHARE_SysJobId ON TSYSJOBSHARE (SYSJOBID, TENANTID);
        CREATE INDEX IX_TSYSJOBSHARE_SharedWith ON TSYSJOBSHARE (SHAREDWITHUSERID, TENANTID);
    END
    -- else: table has data on this install — do not auto-drop; migrate manually.
END
GO

-- =============================================================================
-- END OF MIGRATION 20260808_Framework_SysJobShare_IdentityFix (SQL Server variant)
-- =============================================================================