-- =============================================================================
-- Entitlement Platform — Change-request approval + client capability grants
-- Migration: 20260719 — Entitlement Phase 3 (Change Request) (PostgreSQL variant)
-- Database:  tenant business DB (all tables are tenant-scoped) — companion
--            _SqlServer.sql defines the identical logical schema for SQL Server hosts.
-- Plan:      /Users/venkatv/.claude/plans/in-dowloads-entitlement-folder-we-woolly-stroustrup.md
--            (Thread 8)
--
-- See the _SqlServer.sql companion's header for full rationale (why this is a
-- small Entitlement-owned interim mechanism rather than the shared
-- WorkFlowEngine, and why MENTITLEMENTCLIENTUSERROLE mirrors DXP's
-- MDXPUSERPARTYROLE pattern rather than reusing MROLE/MROLEVSMENU).
--
-- Dialect notes: TINYINT -> SMALLINT, NVARCHAR(n) -> VARCHAR(n), DATETIME ->
-- TIMESTAMP. App-generated INT PK via AutoNumber on both tables (no
-- SERIAL/IDENTITY). CREATEDON/MODIFIEDON/REQUESTEDON/REVIEWEDON/GRANTEDON
-- populated from C# — never NOW().
--
-- Required indexes:
--   MENTITLEMENTCLIENTUSERROLE  (CLIENTUSERID, ROLECODE) UNIQUE
--   MENTITLEMENTCHANGEREQUEST   (CLIENTID, REQUESTSTATUS)
--   MENTITLEMENTCHANGEREQUEST   (SUBSCRIPTIONID)
-- =============================================================================

-- 1. MENTITLEMENTCLIENTUSERROLE — capability grants layered on top of a
--    ClientAdmin-tier MENTITLEMENTCLIENTUSER row. A user may hold more than
--    one capability grant. ROLECODE: 'TECH_ADMIN' | 'COMMERCIAL_ADMIN'.
CREATE TABLE IF NOT EXISTS MENTITLEMENTCLIENTUSERROLE (
    CLIENTUSERROLEID    INT             PRIMARY KEY,
    CLIENTUSERID        INT             NOT NULL,   -- FK -> MENTITLEMENTCLIENTUSER
    ROLECODE            VARCHAR(50)     NOT NULL,   -- TECH_ADMIN | COMMERCIAL_ADMIN
    GRANTEDON           TIMESTAMP       NOT NULL DEFAULT '1900-01-01',
    GRANTEDBYID         INT             NOT NULL DEFAULT -1,
    VERSION             SMALLINT        NOT NULL DEFAULT 0,
    STATUS              SMALLINT        NOT NULL DEFAULT 1,  -- 1=Active 2=Revoked
    SORTORDER           INT             NOT NULL DEFAULT 0,
    CREATEDBYID         INT             NOT NULL DEFAULT -1,
    CREATEDON           TIMESTAMP       NOT NULL DEFAULT '1900-01-01',
    MODIFIEDBYID        INT             NOT NULL DEFAULT -1,
    MODIFIEDON          TIMESTAMP       NOT NULL DEFAULT '1900-01-01',
    SOURCETYPE          SMALLINT        NOT NULL DEFAULT 0,
    TENANTID            INT             NOT NULL DEFAULT -1,
    CONSTRAINT UX_MENTITLEMENTCLIENTUSERROLE UNIQUE (CLIENTUSERID, ROLECODE),
    CONSTRAINT FK_MENTITLEMENTCLIENTUSERROLE_CLIENTUSER FOREIGN KEY (CLIENTUSERID)
        REFERENCES MENTITLEMENTCLIENTUSER (CLIENTUSERID)
);

-- 2. MENTITLEMENTCHANGEREQUEST — a pending, proposed change; nothing is
--    applied until REQUESTSTATUS transitions to Approved.
--    REQUESTTYPE: 0=ChangePlan 1=Renew 2=BetaEnrol 3=BetaOptOut
--    REQUESTSTATUS: 0=Pending 1=Approved 2=Rejected
CREATE TABLE IF NOT EXISTS MENTITLEMENTCHANGEREQUEST (
    CHANGEREQUESTID         INT             PRIMARY KEY,
    CLIENTID                INT             NOT NULL,   -- FK -> MCLIENT
    REQUESTTYPE             SMALLINT        NOT NULL,   -- 0=ChangePlan 1=Renew 2=BetaEnrol 3=BetaOptOut
    SUBSCRIPTIONID          INT             NULL,       -- FK -> MENTITLEMENTSUBSCRIPTION (ChangePlan/Renew only)
    PROPOSEDPLANID          INT             NULL,       -- ChangePlan only
    PROPOSEDVALIDTILL       TIMESTAMP       NULL,       -- Renew only
    BETAPROGRAMNAME         VARCHAR(200)    NULL,       -- BetaEnrol/BetaOptOut only
    REQUESTSTATUS           SMALLINT        NOT NULL DEFAULT 0,  -- 0=Pending 1=Approved 2=Rejected
    REQUESTEDBYCLIENTUSERID INT             NOT NULL,   -- FK -> MENTITLEMENTCLIENTUSER
    REQUESTEDON              TIMESTAMP      NOT NULL DEFAULT '1900-01-01',
    REVIEWEDBYUSERID         INT             NULL,       -- internal MUSER — set on approve/reject
    REVIEWEDON               TIMESTAMP       NULL,
    REVIEWCOMMENTS           VARCHAR(1000)   NULL,
    VERSION                  SMALLINT       NOT NULL DEFAULT 0,
    STATUS                   SMALLINT       NOT NULL DEFAULT 1,
    SORTORDER                INT            NOT NULL DEFAULT 0,
    CREATEDBYID              INT            NOT NULL DEFAULT -1,
    CREATEDON                TIMESTAMP      NOT NULL DEFAULT '1900-01-01',
    MODIFIEDBYID             INT            NOT NULL DEFAULT -1,
    MODIFIEDON                TIMESTAMP     NOT NULL DEFAULT '1900-01-01',
    SOURCETYPE                SMALLINT      NOT NULL DEFAULT 0,
    TENANTID                  INT           NOT NULL DEFAULT -1,
    CONSTRAINT CK_MENTITLEMENTCHANGEREQUEST_REQUESTTYPE   CHECK (REQUESTTYPE   IN (0, 1, 2, 3)),
    CONSTRAINT CK_MENTITLEMENTCHANGEREQUEST_REQUESTSTATUS CHECK (REQUESTSTATUS IN (0, 1, 2)),
    CONSTRAINT FK_MENTITLEMENTCHANGEREQUEST_CLIENT FOREIGN KEY (CLIENTID)
        REFERENCES MCLIENT (CLIENTID),
    CONSTRAINT FK_MENTITLEMENTCHANGEREQUEST_SUBSCRIPTION FOREIGN KEY (SUBSCRIPTIONID)
        REFERENCES MENTITLEMENTSUBSCRIPTION (SUBSCRIPTIONID),
    CONSTRAINT FK_MENTITLEMENTCHANGEREQUEST_REQUESTEDBY FOREIGN KEY (REQUESTEDBYCLIENTUSERID)
        REFERENCES MENTITLEMENTCLIENTUSER (CLIENTUSERID)
);
CREATE INDEX IF NOT EXISTS IX_MENTITLEMENTCHANGEREQUEST_CLIENT_STATUS
    ON MENTITLEMENTCHANGEREQUEST (CLIENTID, REQUESTSTATUS);
CREATE INDEX IF NOT EXISTS IX_MENTITLEMENTCHANGEREQUEST_SUBSCRIPTION
    ON MENTITLEMENTCHANGEREQUEST (SUBSCRIPTIONID);

-- =============================================================================
-- END OF MIGRATION 20260719_Entitlement_Phase3_ChangeRequest_Schema (PostgreSQL variant)
-- =============================================================================