-- =============================================================================
-- GOP QUEUE/MAPPING SCHEMA FIX
-- File   : 20260723_GOP_QueueSchema_Fix.sql
-- Reason : Live-DB verification (GB5DEMO/BASICDEV/TRANSACTIONTEST) found that
--          GopQueueQB.cs/GopQueueDAL.cs referenced table and column names that
--          never existed under any name — confirmed against DB/Migrations/
--          GOP_DDL.sql (which agrees with the live schema, not the C#). That
--          C# has been rewritten to match the real tables/columns; this
--          migration adds the two things that were genuinely missing rather
--          than misnamed:
--            1. TGOPSOURCEFILE — GopFileUploadBLL (the one GOP endpoint the
--               frontend actually calls live, via gbgateway's
--               "uploadToGopGateway") depends on a source-file tracking table
--               that does not exist anywhere, under any name, in the DDL or
--               any live database checked.
--            2. SNAPSHOTJSON on MMAPPINGVERSION — referenced by MappingDAL's
--               version insert/activate queries; genuinely absent (not a
--               rename) on the live table.
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. TGOPSOURCEFILE (new — no prior table under any name)
--
-- UPLOADSTATUS: 0=Uploaded,1=Queued,2=PartialFailed,3=Failed,4=ParseFailed
-- PK is IDENTITY (not the app-generated INT PK T-tables normally use) to match
-- GopQueueDAL.InsertSourceFile's existing ExecuteIdentityAsync/SCOPE_IDENTITY() call.
-- ─────────────────────────────────────────────────────────────────────────────
IF OBJECT_ID('[DBO].[TGOPSOURCEFILE]', 'U') IS NULL
CREATE TABLE [DBO].[TGOPSOURCEFILE]
(
    GOPSOURCEFILEID     INT            NOT NULL IDENTITY(1,1) CONSTRAINT PK_TGOPSOURCEFILE_ID PRIMARY KEY,
    SOURCECODE          NVARCHAR(20)   NOT NULL,
    ORIGINALFILENAME    NVARCHAR(260)  NOT NULL,
    STOREDFILEPATH      NVARCHAR(500),
    FILESIZEBYTES       BIGINT,
    MIMETYPE            NVARCHAR(100),
    UPLOADEDBY          NVARCHAR(200)  NOT NULL,
    UPLOADEDON          DATETIME       NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_UPLOADEDON DEFAULT GETDATE(),
    UPLOADSTATUS        TINYINT        NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_UPLOADSTATUS DEFAULT 0,
    GOPEXECUTIONQUEUEID INT,
    -- Standard fields
    VERSION             INT            NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_VERSION     DEFAULT 1,
    STATUS              TINYINT        NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_STATUS      DEFAULT 1,
    SORTORDER           INT            NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_SORTORDER   DEFAULT 0,
    SOURCETYPE          TINYINT        NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_SOURCETYPE  DEFAULT 5,
    CREATEDBYID         INT            NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_CREATEDBYID DEFAULT -1,
    CREATEDON           DATETIME       NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT            NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_MODIFIEDBYID DEFAULT -1,
    MODIFIEDON          DATETIME       NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_MODIFIEDON  DEFAULT GETDATE(),
    TENANTID            INT            NOT NULL CONSTRAINT DF_TGOPSOURCEFILE_TENANTID    DEFAULT -1
);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_TGOPSOURCEFILE_TID_SOURCECODE')
    CREATE INDEX IX_TGOPSOURCEFILE_TID_SOURCECODE
    ON [DBO].[TGOPSOURCEFILE] (TENANTID, SOURCECODE);
GO

-- ─────────────────────────────────────────────────────────────────────────────
-- 2. SNAPSHOTJSON on MMAPPINGVERSION
-- ─────────────────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('MMAPPINGVERSION') AND name = 'SNAPSHOTJSON')
    ALTER TABLE MMAPPINGVERSION ADD SNAPSHOTJSON NVARCHAR(MAX) NULL;
GO

-- ─────────────────────────────────────────────────────────────────────────────
-- 3. "-1 NONE" sentinel row on MGOPTARGETOPERATION
--
-- Found via end-to-end smoke test (2026-07-23): MGOPFLOWSTEP.TARGETOPERATIONID
-- defaults to -1 for non-ApiCall steps (Qualifier/Mapper/Approval) and its FK
-- requires that value to exist — MENTITY/MCLIENT/MUSER all carry this -1=NONE
-- sentinel convention, but MGOPTARGETOPERATION never got seeded with one.
-- Without this row, saving any non-ApiCall flow step fails with a FK violation.
-- ─────────────────────────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM MGOPTARGETOPERATION WHERE GOPTARGETOPERATIONID = -1)
INSERT INTO MGOPTARGETOPERATION
    (GOPTARGETOPERATIONID, OPERATIONCODE, OPERATIONNAME, BASEURL, RELATIVEPATH, HTTPMETHOD,
     VERSION, STATUS, SORTORDER, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
VALUES
    (-1, 'NONE', 'NONE', 'NONE', 'NONE', 'POST', 1, 1, 0, 5, -1, GETDATE(), -1, GETDATE(), -1);
GO