-- =============================================================================
-- WIP (Form-Based Approval) Database Migration
-- Run this script on all tenant databases BEFORE deploying the new code.
-- =============================================================================

-- 1. Create TWORKFLOWWIP table
-- ----------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TWORKFLOWWIP')
BEGIN
    CREATE TABLE DBO.TWORKFLOWWIP
    (
        WIPID               INT            NOT NULL IDENTITY(1,1),
        ENTITYID            INT            NOT NULL,
        OBJECTID            INT            NOT NULL CONSTRAINT DF_TWORKFLOWWIP_OBJECTID DEFAULT -1,
        TENANTID            INT            NOT NULL,
        WORKFLOWINSTANCEID  INT            NOT NULL CONSTRAINT DF_TWORKFLOWWIP_WORKFLOWINSTANCEID DEFAULT -1,
        DATAJSON            NVARCHAR(MAX)  NOT NULL,
        DATAHASH            VARBINARY(32)  NULL,
        RESUBMISSIONCOUNT   SMALLINT       NOT NULL CONSTRAINT DF_TWORKFLOWWIP_RESUBMISSIONCOUNT DEFAULT 0,
        LASTACTION          TINYINT        NULL,
        SORTORDER           SMALLINT       NOT NULL CONSTRAINT DF_TWORKFLOWWIP_SORTORDER DEFAULT 0,
        STATUS              TINYINT        NOT NULL CONSTRAINT DF_TWORKFLOWWIP_STATUS DEFAULT 1,
        VERSION             SMALLINT       NOT NULL CONSTRAINT DF_TWORKFLOWWIP_VERSION DEFAULT 1,
        SOURCETYPE          TINYINT        NOT NULL CONSTRAINT DF_TWORKFLOWWIP_SOURCETYPE DEFAULT 1,
        APIENDPOINT         NVARCHAR(500)  NULL,
        CREATEDBYID         INT            NOT NULL,
        CREATEDON           DATETIME       NOT NULL CONSTRAINT DF_TWORKFLOWWIP_CREATEDON DEFAULT GETDATE(),
        MODIFIEDBYID        INT            NOT NULL,
        MODIFIEDON          DATETIME       NOT NULL CONSTRAINT DF_TWORKFLOWWIP_MODIFIEDON DEFAULT GETDATE(),

        CONSTRAINT PK_TWORKFLOWWIP            PRIMARY KEY (WIPID),
        CONSTRAINT FK_TWORKFLOWWIP_ENTITYID   FOREIGN KEY (ENTITYID)           REFERENCES MENTITY(ENTITYID),
        CONSTRAINT FK_TWORKFLOWWIP_TENANTID   FOREIGN KEY (TENANTID)           REFERENCES MCLIENT(CLIENTID),
        CONSTRAINT FK_TWORKFLOWWIP_CREATEDBY  FOREIGN KEY (CREATEDBYID)        REFERENCES MUSER(USERID),
        CONSTRAINT FK_TWORKFLOWWIP_MODIFIEDBY FOREIGN KEY (MODIFIEDBYID)       REFERENCES MUSER(USERID),
        CONSTRAINT CK_TWORKFLOWWIP_STATUS     CHECK (STATUS     IN (0,1,2,3,4,5)),
        CONSTRAINT CK_TWORKFLOWWIP_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)),
        CONSTRAINT CK_TWORKFLOWWIP_LASTACTION CHECK (LASTACTION IN (0,1,2,3))
    );
    PRINT 'TWORKFLOWWIP created.'
END
ELSE
    PRINT 'TWORKFLOWWIP already exists — skipped.'
GO

-- Note: FK to TWORKFLOWINSTANCE not added here because WORKFLOWINSTANCEID starts as -1
-- (set after instance creation). Add the FK separately if needed after verifying all rows.

-- 2. Add CALLBACKENDPOINT column to MWORKFLOWCONFIG
-- ----------------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MWORKFLOWCONFIG' AND COLUMN_NAME = 'CALLBACKENDPOINT'
)
BEGIN
    ALTER TABLE DBO.MWORKFLOWCONFIG
        ADD CALLBACKENDPOINT NVARCHAR(500) NULL;
    PRINT 'CALLBACKENDPOINT added to MWORKFLOWCONFIG.'
END
ELSE
    PRINT 'CALLBACKENDPOINT already exists — skipped.'
GO

-- 3. Add PARTICULARSTEMPLATE column to MWORKFLOWCONFIG
-- ----------------------------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MWORKFLOWCONFIG' AND COLUMN_NAME = 'PARTICULARSTEMPLATE'
)
BEGIN
    ALTER TABLE DBO.MWORKFLOWCONFIG
        ADD PARTICULARSTEMPLATE NVARCHAR(500) NULL;
    PRINT 'PARTICULARSTEMPLATE added to MWORKFLOWCONFIG.'
END
ELSE
    PRINT 'PARTICULARSTEMPLATE already exists — skipped.'
GO

-- 4. Add index on TWORKFLOWWIP for fast lookup by WorkflowInstanceId
-- ----------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME = 'IX_TWORKFLOWWIP_INSTANCEID')
BEGIN
    CREATE NONCLUSTERED INDEX IX_TWORKFLOWWIP_INSTANCEID
        ON DBO.TWORKFLOWWIP (WORKFLOWINSTANCEID);
    PRINT 'Index IX_TWORKFLOWWIP_INSTANCEID created.'
END
GO

-- =============================================================================
-- Sample MWORKFLOWCONFIG entry for WIP (Form-Based Approval)
-- Duplicate the existing row for your entity and set ISFORMBASEDAPPROVAL=1.
-- Replace the CALLBACKENDPOINT with the FULL URL of the entity save endpoint.
-- =============================================================================
-- INSERT INTO MWORKFLOWCONFIG
-- (CLIENTID, ENTITYID, OUID, BIZTRANSACTIONCLASSID, ISBIZTRANSACTIONWISE,
--  BIZTRANSACTIONID, ISFORMBASEDAPPROVAL, WORKFLOWID, EVALCONDITION, REMARKS,
--  CALLBACKENDPOINT, CREATEDDATE)
-- VALUES
-- (-1399999958, -1899999523, -1500000000, -1499999978, 0,
--  -1499999996, 1, 4, 'TaskDetailType == 0 || TaskDetailType == 1 || TaskDetailType == 2 || TaskDetailType == 3 || TaskDetailType == 4',
--  'Task WIP CONFIG',
--  'http://task-service:5001/Task/SaveTask',    -- FULL URL of the save endpoint
--  GETDATE());
-- =============================================================================