-- ============================================================================
-- ActionProcessor DB Migration
-- Creates: TEVENTACTIONRUN, TACTIONOUTBOX, LPROCESSEDACTION
-- Follows CLAUDE-DB-SCHEMA.md conventions:
--   T = Transaction (app-generated INT PK, full standard fields)
--   L = Log/Append-only (IDENTITY INT PK, no standard fields)
-- ============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. TEVENTACTIONRUN
--    Tracks execution lifecycle of each triggered MACTION.
--    RUNSTATUS: 0=Pending, 1=InProgress, 2=Completed, 3=Failed
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE [DBO].TEVENTACTIONRUN
(
    -- PK
    ACTIONRUNID         INT             NOT NULL,

    -- BUSINESS COLUMNS
    ACTIONID            INT             NOT NULL,
    JOBEXECUTIONID      INT             NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_JOBEXECUTIONID  DEFAULT -1,  -- -1 = not scheduler-triggered
    EVENTTYPEID         INT             NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_EVENTTYPEID     DEFAULT -1,  -- -1 = not event-triggered
    RUNSTATUS           TINYINT         NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_RUNSTATUS       DEFAULT 0,   -- 0 Pending, 1 InProgress, 2 Completed, 3 Failed
    ATTEMPTS            TINYINT         NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_ATTEMPTS        DEFAULT 0,
    ERRORMESSAGE        NVARCHAR(MAX),
    STARTEDON           DATETIME,
    COMPLETEDON         DATETIME,
    PAYLOAD             NVARCHAR(MAX),
    RESULT              NVARCHAR(MAX),

    -- STANDARD FIELDS
    VERSION             SMALLINT        NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_VERSION         DEFAULT 0,
    STATUS              TINYINT         NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_STATUS          DEFAULT 1,
    SORTORDER           SMALLINT        NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_SORTORDER       DEFAULT 9999,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_CREATEDON       DEFAULT GETDATE(),
    MODIFIEDBYID        INT             NOT NULL,
    MODIFIEDON          DATETIME        NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_MODIFIEDON      DEFAULT GETDATE(),
    SOURCETYPE          TINYINT         NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_SOURCETYPE      DEFAULT 5,
    TENANTID            INT             NOT NULL CONSTRAINT DF_TEVENTACTIONRUN_TENANTID        DEFAULT -1,

    CONSTRAINT PK_TEVENTACTIONRUN_ACTIONRUNID       PRIMARY KEY (ACTIONRUNID),
    CONSTRAINT FK_TEVENTACTIONRUN_ACTIONID          FOREIGN KEY (ACTIONID)      REFERENCES MACTION(ACTIONID),
    CONSTRAINT FK_TEVENTACTIONRUN_CREATEDBYID       FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_TEVENTACTIONRUN_MODIFIEDBYID      FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TEVENTACTIONRUN_TENANTID          FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_TEVENTACTIONRUN_RUNSTATUS         CHECK (RUNSTATUS   IN (0,1,2,3)),
    CONSTRAINT CK_TEVENTACTIONRUN_STATUS            CHECK (STATUS      IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TEVENTACTIONRUN_SOURCETYPE        CHECK (SOURCETYPE  IN (1,2,3,4,5))
)
GO

CREATE INDEX IX_TEVENTACTIONRUN_TENANTID_RUNSTATUS_CREATEDON
    ON TEVENTACTIONRUN (TENANTID, RUNSTATUS, CREATEDON)
GO

-- ─────────────────────────────────────────────────────────────────────────────
-- 2. TACTIONOUTBOX
--    Transactional outbox for reliable RabbitMQ publishing.
--    SENDSTATUS: 0=Pending, 1=Sent, 2=Failed
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE [DBO].TACTIONOUTBOX
(
    -- PK
    OUTBOXID            INT             NOT NULL,

    -- BUSINESS COLUMNS
    ACTIONRUNID         INT             NOT NULL,
    DESTINATIONTOPIC    NVARCHAR(200)   NOT NULL,
    PAYLOAD             NVARCHAR(MAX)   NOT NULL,
    SENDSTATUS          TINYINT         NOT NULL CONSTRAINT DF_TACTIONOUTBOX_SENDSTATUS DEFAULT 0,  -- 0 Pending, 1 Sent, 2 Failed
    ATTEMPTS            TINYINT         NOT NULL CONSTRAINT DF_TACTIONOUTBOX_ATTEMPTS   DEFAULT 0,

    -- STANDARD FIELDS
    VERSION             SMALLINT        NOT NULL CONSTRAINT DF_TACTIONOUTBOX_VERSION    DEFAULT 0,
    STATUS              TINYINT         NOT NULL CONSTRAINT DF_TACTIONOUTBOX_STATUS     DEFAULT 1,
    SORTORDER           SMALLINT        NOT NULL CONSTRAINT DF_TACTIONOUTBOX_SORTORDER  DEFAULT 9999,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_TACTIONOUTBOX_CREATEDON  DEFAULT GETDATE(),
    MODIFIEDBYID        INT             NOT NULL,
    MODIFIEDON          DATETIME        NOT NULL CONSTRAINT DF_TACTIONOUTBOX_MODIFIEDON DEFAULT GETDATE(),
    SOURCETYPE          TINYINT         NOT NULL CONSTRAINT DF_TACTIONOUTBOX_SOURCETYPE DEFAULT 5,
    TENANTID            INT             NOT NULL CONSTRAINT DF_TACTIONOUTBOX_TENANTID   DEFAULT -1,

    CONSTRAINT PK_TACTIONOUTBOX_OUTBOXID            PRIMARY KEY (OUTBOXID),
    CONSTRAINT FK_TACTIONOUTBOX_ACTIONRUNID         FOREIGN KEY (ACTIONRUNID)   REFERENCES TEVENTACTIONRUN(ACTIONRUNID),
    CONSTRAINT FK_TACTIONOUTBOX_CREATEDBYID         FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_TACTIONOUTBOX_MODIFIEDBYID        FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TACTIONOUTBOX_TENANTID            FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_TACTIONOUTBOX_SENDSTATUS          CHECK (SENDSTATUS  IN (0,1,2)),
    CONSTRAINT CK_TACTIONOUTBOX_STATUS              CHECK (STATUS      IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TACTIONOUTBOX_SOURCETYPE          CHECK (SOURCETYPE  IN (1,2,3,4,5))
)
GO

CREATE INDEX IX_TACTIONOUTBOX_SENDSTATUS_CREATEDON_TENANTID
    ON TACTIONOUTBOX (SENDSTATUS, CREATEDON, TENANTID)
GO

-- ─────────────────────────────────────────────────────────────────────────────
-- 3. LPROCESSEDACTION
--    Idempotency log — append-only, IDENTITY PK, no standard fields.
--    UK on ACTIONRUNID ensures each action run is processed at most once.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE [DBO].LPROCESSEDACTION
(
    LPROCESSEDACTIONID  INT             IDENTITY(1,1) NOT NULL,
    ACTIONRUNID         INT             NOT NULL,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_LPROCESSEDACTION_CREATEDON DEFAULT GETDATE(),

    CONSTRAINT PK_LPROCESSEDACTION_LPROCESSEDACTIONID   PRIMARY KEY (LPROCESSEDACTIONID),
    CONSTRAINT UK_LPROCESSEDACTION_ACTIONRUNID          UNIQUE (ACTIONRUNID),
    CONSTRAINT FK_LPROCESSEDACTION_ACTIONRUNID          FOREIGN KEY (ACTIONRUNID)   REFERENCES TEVENTACTIONRUN(ACTIONRUNID),
    CONSTRAINT FK_LPROCESSEDACTION_CREATEDBYID          FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID)
)
GO

-- ─────────────────────────────────────────────────────────────────────────────
-- Notes
-- ─────────────────────────────────────────────────────────────────────────────
-- 1. ACTIONRUNID and OUTBOXID are app-generated. The DAL uses
--    SELECT ISNULL(MAX(ACTIONRUNID),0)+1 FROM TEVENTACTIONRUN WHERE TENANTID=@TenantId
--    before each INSERT. For high-concurrency environments, consider a SEQUENCE object.
--
-- 2. JOBEXECUTIONID FK to TJOBEXECUTION and EVENTTYPEID FK to MEVENTTYPE are intentionally
--    omitted above. They use the -1 sentinel convention. Add FK constraints once
--    the respective tables have a -1 sentinel row confirming:
--    INSERT INTO TJOBEXECUTION (JOBEXECUTIONID, ...) VALUES (-1, ...)  -- dummy row
--    INSERT INTO MEVENTTYPE    (EVENTTYPEID,    ...) VALUES (-1, ...)  -- dummy row
--
-- 3. MACTION FK requires MACTION table to have ACTIONID column as PK — verify before running.
-- ─────────────────────────────────────────────────────────────────────────────