-- =============================================================================
-- DXP — Digital Experience Platform (Party/UPI identity + Vendor Portal Phase 1)
-- Migration: 20260710 — Identity Core (Phase 1.1)
-- Database:   DXPDb (SQL Server variant — see companion _Postgres.sql for the
--             PostgreSQL variant; both define the identical logical schema so
--             either engine can host the DXP system database per deployment)
-- Plan:       /Users/venkatv/.claude/plans/in-downloads-dxp-folder-requirements-optimized-karp.md
--
-- Run order: 20260710 (initial — no prior DXP migrations required)
--
-- Phase 1.1 tables (this file):
--   MDXPPARTY            — Global Party Graph, one row per real-world entity across ALL tenants
--   TDXPPARTYLINK        — links one MDXPPARTY row to N per-tenant MPARTY rows
--   TDXPPARTYBRANCHLINK  — links one MDXPPARTY row to N per-tenant MPARTYBRANCH rows
--   MDXPUSER             — global login identity (a real person, distinct from any tenant MUSER)
--   MDXPUSERPARTYROLE    — which MDXPUSER can act as which MDXPPARTY, in what role
--   MDXPREFRESHTOKEN     — revocable JWT refresh tokens (rotation chain)
--
-- Phase 1.2 tables (separate migration): MDXPPARTYKYCPROFILE
--
-- IMPORTANT — these are GLOBAL tables, not tenant-scoped:
--   MDXPPARTY / MDXPUSER / MDXPUSERPARTYROLE carry NO TenantId/ClientId column and must live in
--   a dedicated DXP system database (same tier as GB5's existing system DB), never inside any
--   client's tenant DB. This is the one deliberate exception to GB5's "always filter by
--   TenantId/ClientId" rule (see CLAUDE.md Security > Multi-Tenancy) — do NOT "fix" this later
--   by adding a tenant filter, it would break cross-tenant identity, which is the entire point.
--   TDXPPARTYLINK/TDXPPARTYBRANCHLINK DO carry TENANTID — but as a plain business column
--   identifying which tenant a given link points to, not as a current-session tenant filter.
--
-- Column casing convention: ALL CAPS (matches GB5 SQL Server standard)
-- Constraint naming:  PK_{TABLE}, DF_{TABLE}_{COLUMN}, CK_{TABLE}_{COLUMN},
--                     IX_{TABLE}_{COLUMNS}, UX_{TABLE}_{COLUMNS}
-- Audit columns: global M tables here use VERSION/STATUS/CREATEDBYID/CREATEDON/
--                MODIFIEDBYID/MODIFIEDON only (no SORTORDER/SOURCETYPE/TENANTID — those don't
--                apply to a cross-tenant identity table). Tenant-scoped link tables add TENANTID.
--                MDXPREFRESHTOKEN is a high-volume token log — AutoNumber PK (like every other
--                DXP table, not IDENTITY — see its own table comment for why) + minimal audit,
--                same L-style audit treatment IDMS gives its L-prefixed log tables despite the
--                M-style name (name was fixed in the approved plan).
-- =============================================================================

-- =============================================================================
-- TABLE: MDXPPARTY
-- M table — application-generated INT PK (AutoNumber, no IDENTITY)
-- PARTYTYPECODE: 1=Vendor 2=Customer 3=Dealer 4=ServiceFranchisee 5=Consumer
-- STATUS: 1=Active 2=Suspended 3=Merged 4=Deleted
-- =============================================================================
CREATE TABLE MDXPPARTY (
    -- Primary Key (application-generated AutoNumber)
    DXPPARTYID              INT            NOT NULL,

    -- Business columns
    LEGALNAME                NVARCHAR(300)  NOT NULL,
    PARTYTYPECODE            TINYINT        NOT NULL,
        -- 1=Vendor 2=Customer 3=Dealer 4=ServiceFranchisee 5=Consumer
    PRIMARYGSTINHASH         NVARCHAR(128)  NULL,
        -- SHA-256 hex of normalized GSTIN — dedup lookup only, never the source of truth
    PRIMARYPANHASH           NVARCHAR(128)  NULL,
    PRIMARYMOBILEHASH        NVARCHAR(128)  NULL,
    PRIMARYEMAILHASH         NVARCHAR(128)  NULL,
    MERGEDINTOPARTYID        INT            NULL,
        -- set when this party was deduped/merged into another MDXPPARTY row

    -- Audit columns (no SORTORDER/SOURCETYPE/TENANTID — global table, see header)
    VERSION                  SMALLINT       NOT NULL
        CONSTRAINT DF_MDXPPARTY_VERSION                 DEFAULT 0,
    STATUS                   TINYINT        NOT NULL
        CONSTRAINT DF_MDXPPARTY_STATUS                  DEFAULT 1,
        -- 1=Active 2=Suspended 3=Merged 4=Deleted
    CREATEDBYID              INT            NOT NULL
        CONSTRAINT DF_MDXPPARTY_CREATEDBYID             DEFAULT -1,
    CREATEDON                DATETIME       NOT NULL
        CONSTRAINT DF_MDXPPARTY_CREATEDON               DEFAULT GETDATE(),
    MODIFIEDBYID             INT            NOT NULL
        CONSTRAINT DF_MDXPPARTY_MODIFIEDBYID            DEFAULT -1,
    MODIFIEDON                DATETIME       NOT NULL
        CONSTRAINT DF_MDXPPARTY_MODIFIEDON              DEFAULT GETDATE(),

    -- Constraints
    CONSTRAINT PK_MDXPPARTY
        PRIMARY KEY (DXPPARTYID),
    CONSTRAINT CK_MDXPPARTY_PARTYTYPECODE
        CHECK (PARTYTYPECODE IN (1, 2, 3, 4, 5)),
    CONSTRAINT CK_MDXPPARTY_STATUS
        CHECK (STATUS IN (1, 2, 3, 4))
);
GO

-- =============================================================================
-- TABLE: TDXPPARTYLINK
-- T table — application-generated INT PK (AutoNumber, no IDENTITY)
-- Maps one MDXPPARTY row to one or more per-tenant MPARTY rows.
-- RELATIONSHIPTYPE: 1=Vendor 2=Customer 3=Dealer 4=ServiceFranchisee 5=Consumer
--   (scoped to THIS tenant relationship — a party can be Vendor to one tenant, Customer to
--    another; this can differ from MDXPPARTY.PARTYTYPECODE, which is just the party's primary
--    classification for display purposes)
-- STATUS: 1=Active 2=Suspended 3=Revoked
-- DATABASETYPE: 0=SQL 1=Oracle 2=PostGre 3=MySQL (mirrors GB5Shared.GB5Constant.Constant.DBTYPE)
-- =============================================================================
CREATE TABLE TDXPPARTYLINK (
    -- Primary Key (application-generated AutoNumber)
    DXPPARTYLINKID           INT            NOT NULL,

    -- Business columns
    DXPPARTYID                INT            NOT NULL,
        -- FK -> MDXPPARTY.DXPPARTYID
    TENANTID                  INT            NOT NULL,
        -- which GB5 client tenant this link points to (business column, not a session filter)
    DATABASENAME               NVARCHAR(100)  NOT NULL,
        -- physical DB the tenant's MPARTY row lives in
    DATABASETYPE                TINYINT        NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_DATABASETYPE        DEFAULT 0,
        -- 0=SQL 1=Oracle 2=PostGre 3=MySQL
    LOCALPARTYID                INT            NOT NULL,
        -- MPARTY.PARTYID in that tenant's DB
    RELATIONSHIPTYPE            TINYINT        NOT NULL,
        -- 1=Vendor 2=Customer 3=Dealer 4=ServiceFranchisee 5=Consumer
    LINKEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_LINKEDON            DEFAULT GETDATE(),
    LINKEDBYID                   INT            NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_LINKEDBYID          DEFAULT -1,
        -- MDXPUSERID who established the link (e.g. admin approving onboarding)

    -- Audit columns
    STATUS                       TINYINT        NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_STATUS              DEFAULT 1,
        -- 1=Active 2=Suspended 3=Revoked
    CREATEDBYID                  INT            NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_CREATEDBYID         DEFAULT -1,
    CREATEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_CREATEDON           DEFAULT GETDATE(),
    MODIFIEDBYID                 INT            NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_MODIFIEDBYID        DEFAULT -1,
    MODIFIEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_TDXPPARTYLINK_MODIFIEDON          DEFAULT GETDATE(),

    -- Constraints
    CONSTRAINT PK_TDXPPARTYLINK
        PRIMARY KEY (DXPPARTYLINKID),
    CONSTRAINT CK_TDXPPARTYLINK_DATABASETYPE
        CHECK (DATABASETYPE IN (0, 1, 2, 3)),
    CONSTRAINT CK_TDXPPARTYLINK_RELATIONSHIPTYPE
        CHECK (RELATIONSHIPTYPE IN (1, 2, 3, 4, 5)),
    CONSTRAINT CK_TDXPPARTYLINK_STATUS
        CHECK (STATUS IN (1, 2, 3)),
    CONSTRAINT UX_TDXPPARTYLINK_PARTY_TENANT_LOCALPARTY
        UNIQUE (DXPPARTYID, TENANTID, LOCALPARTYID)
);
GO

-- =============================================================================
-- TABLE: TDXPPARTYBRANCHLINK
-- T table — application-generated INT PK (AutoNumber, no IDENTITY)
-- Resolves to the branch/plant level (MPARTYBRANCH), since GSTIN, delivery location, and
-- invoicing are branch-scoped in the existing GB5 model, not party-scoped.
-- STATUS: 1=Active 2=Suspended 3=Revoked
-- =============================================================================
CREATE TABLE TDXPPARTYBRANCHLINK (
    -- Primary Key (application-generated AutoNumber)
    DXPPARTYBRANCHLINKID      INT            NOT NULL,

    -- Business columns
    DXPPARTYID                 INT            NOT NULL,
        -- FK -> MDXPPARTY.DXPPARTYID
    TENANTID                    INT            NOT NULL,
    LOCALPARTYBRANCHID           INT            NOT NULL,
        -- MPARTYBRANCH.PARTYBRANCHID in that tenant's DB
    ISDEFAULT                    BIT            NOT NULL
        CONSTRAINT DF_TDXPPARTYBRANCHLINK_ISDEFAULT     DEFAULT 0,

    -- Audit columns
    STATUS                       TINYINT        NOT NULL
        CONSTRAINT DF_TDXPPARTYBRANCHLINK_STATUS        DEFAULT 1,
        -- 1=Active 2=Suspended 3=Revoked
    CREATEDBYID                  INT            NOT NULL
        CONSTRAINT DF_TDXPPARTYBRANCHLINK_CREATEDBYID   DEFAULT -1,
    CREATEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_TDXPPARTYBRANCHLINK_CREATEDON     DEFAULT GETDATE(),
    MODIFIEDBYID                 INT            NOT NULL
        CONSTRAINT DF_TDXPPARTYBRANCHLINK_MODIFIEDBYID  DEFAULT -1,
    MODIFIEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_TDXPPARTYBRANCHLINK_MODIFIEDON    DEFAULT GETDATE(),

    -- Constraints
    CONSTRAINT PK_TDXPPARTYBRANCHLINK
        PRIMARY KEY (DXPPARTYBRANCHLINKID),
    CONSTRAINT CK_TDXPPARTYBRANCHLINK_STATUS
        CHECK (STATUS IN (1, 2, 3)),
    CONSTRAINT UX_TDXPPARTYBRANCHLINK_PARTY_TENANT_BRANCH
        UNIQUE (DXPPARTYID, TENANTID, LOCALPARTYBRANCHID)
);
GO

-- =============================================================================
-- TABLE: MDXPUSER
-- M table — application-generated INT PK (AutoNumber, no IDENTITY)
-- Global login identity — a real person, distinct from any tenant's MUSER.
-- STATUS: 1=Active 2=Locked 3=Deleted
-- =============================================================================
CREATE TABLE MDXPUSER (
    -- Primary Key (application-generated AutoNumber)
    DXPUSERID                 INT            NOT NULL,

    -- Business columns
    FULLNAME                   NVARCHAR(200)  NOT NULL,
    EMAIL                       NVARCHAR(200)  NOT NULL,
    MOBILE                      NVARCHAR(20)   NULL,
    PASSWORDHASH                NVARCHAR(256)  NOT NULL,
        -- Argon2/bcrypt hash — never plaintext, never reversible
    MFAENABLED                  BIT            NOT NULL
        CONSTRAINT DF_MDXPUSER_MFAENABLED               DEFAULT 0,
    MFASECRETENCRYPTED          NVARCHAR(500)  NULL,
        -- TOTP secret, encrypted (Vault-managed key) — never plaintext
    LASTLOGINON                 DATETIME       NULL,
    FAILEDLOGINCOUNT             SMALLINT       NOT NULL
        CONSTRAINT DF_MDXPUSER_FAILEDLOGINCOUNT         DEFAULT 0,

    -- Audit columns
    VERSION                      SMALLINT       NOT NULL
        CONSTRAINT DF_MDXPUSER_VERSION                  DEFAULT 0,
    STATUS                       TINYINT        NOT NULL
        CONSTRAINT DF_MDXPUSER_STATUS                   DEFAULT 1,
        -- 1=Active 2=Locked 3=Deleted
    CREATEDBYID                  INT            NOT NULL
        CONSTRAINT DF_MDXPUSER_CREATEDBYID              DEFAULT -1,
    CREATEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_MDXPUSER_CREATEDON                DEFAULT GETDATE(),
    MODIFIEDBYID                 INT            NOT NULL
        CONSTRAINT DF_MDXPUSER_MODIFIEDBYID             DEFAULT -1,
    MODIFIEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_MDXPUSER_MODIFIEDON               DEFAULT GETDATE(),

    -- Constraints
    CONSTRAINT PK_MDXPUSER
        PRIMARY KEY (DXPUSERID),
    CONSTRAINT CK_MDXPUSER_STATUS
        CHECK (STATUS IN (1, 2, 3)),
    CONSTRAINT UX_MDXPUSER_EMAIL
        UNIQUE (EMAIL)
);
GO

-- =============================================================================
-- TABLE: MDXPUSERPARTYROLE
-- M table — application-generated INT PK (AutoNumber, no IDENTITY)
-- Which MDXPUSER can act as which MDXPPARTY, in what role — the context-switch list.
-- ROLECODE (Vendor Portal Phase 1): VendorGM|FinanceContact|OperationsContact|
--   QualityContact|ReadOnly
-- STATUS: 1=Active 2=Suspended 3=Revoked
-- =============================================================================
CREATE TABLE MDXPUSERPARTYROLE (
    -- Primary Key (application-generated AutoNumber)
    DXPUSERPARTYROLEID        INT            NOT NULL,

    -- Business columns
    DXPUSERID                  INT            NOT NULL,
        -- FK -> MDXPUSER.DXPUSERID
    DXPPARTYID                  INT            NOT NULL,
        -- FK -> MDXPPARTY.DXPPARTYID
    ROLECODE                     NVARCHAR(50)   NOT NULL,
        -- VendorGM|FinanceContact|OperationsContact|QualityContact|ReadOnly
    GRANTEDON                    DATETIME       NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_GRANTEDON       DEFAULT GETDATE(),
    GRANTEDBYID                   INT            NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_GRANTEDBYID     DEFAULT -1,

    -- Audit columns
    STATUS                        TINYINT        NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_STATUS          DEFAULT 1,
        -- 1=Active 2=Suspended 3=Revoked
    CREATEDBYID                   INT            NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_CREATEDBYID     DEFAULT -1,
    CREATEDON                     DATETIME       NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_CREATEDON       DEFAULT GETDATE(),
    MODIFIEDBYID                  INT            NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_MODIFIEDBYID    DEFAULT -1,
    MODIFIEDON                     DATETIME       NOT NULL
        CONSTRAINT DF_MDXPUSERPARTYROLE_MODIFIEDON      DEFAULT GETDATE(),

    -- Constraints
    CONSTRAINT PK_MDXPUSERPARTYROLE
        PRIMARY KEY (DXPUSERPARTYROLEID),
    CONSTRAINT CK_MDXPUSERPARTYROLE_STATUS
        CHECK (STATUS IN (1, 2, 3)),
    CONSTRAINT UX_MDXPUSERPARTYROLE_USER_PARTY_ROLE
        UNIQUE (DXPUSERID, DXPPARTYID, ROLECODE)
);
GO

-- =============================================================================
-- TABLE: MDXPREFRESHTOKEN
-- Application-generated INT PK (AutoNumber, no IDENTITY) — same as every other DXP table.
-- Deliberately NOT IDENTITY/SERIAL: ExecuteIdentityAsync requires an engine-specific trailing
-- clause (SCOPE_IDENTITY() vs RETURNING), which would break this table's DBMS-neutrality —
-- AutoNumber sidesteps that entirely since it's an app-level generator, not an engine feature.
-- High-volume token log — L-table treatment on audit columns despite the M-prefixed name.
-- Insert-only except for REVOKEDON/REPLACEDBYTOKENID on rotation/logout.
-- =============================================================================
CREATE TABLE MDXPREFRESHTOKEN (
    -- Primary Key (application-generated AutoNumber)
    DXPREFRESHTOKENID          INT            NOT NULL,

    -- Business columns
    DXPUSERID                   INT            NOT NULL,
        -- FK -> MDXPUSER.DXPUSERID
    DXPUSERPARTYROLEID           INT            NOT NULL,
        -- FK -> MDXPUSERPARTYROLE.DXPUSERPARTYROLEID — the role this token is scoped to
    DXPPARTYLINKID                INT            NOT NULL,
        -- FK -> TDXPPARTYLINK.DXPPARTYLINKID — the tenant relationship this token is scoped to;
        -- both are required so RefreshAsync can reissue an access token with the same claims
        -- without the caller re-selecting a context on every refresh
    TOKENHASH                    NVARCHAR(256)  NOT NULL,
        -- SHA-256 hash of the actual refresh token — the raw token is never stored
    DEVICEINFO                    NVARCHAR(300)  NULL,
    ISSUEDON                      DATETIME       NOT NULL
        CONSTRAINT DF_MDXPREFRESHTOKEN_ISSUEDON         DEFAULT GETDATE(),
    EXPIRESON                     DATETIME       NOT NULL,
    REVOKEDON                     DATETIME       NULL,
    REPLACEDBYTOKENID             INT            NULL,
        -- rotation chain — set when this token was exchanged for a new one

    -- Audit columns (minimal — token log, not a business master)
    CREATEDBYID                   INT            NOT NULL
        CONSTRAINT DF_MDXPREFRESHTOKEN_CREATEDBYID      DEFAULT -1,
    CREATEDON                     DATETIME       NOT NULL
        CONSTRAINT DF_MDXPREFRESHTOKEN_CREATEDON        DEFAULT GETDATE(),

    -- Constraints
    CONSTRAINT PK_MDXPREFRESHTOKEN
        PRIMARY KEY (DXPREFRESHTOKENID),
    CONSTRAINT UX_MDXPREFRESHTOKEN_TOKENHASH
        UNIQUE (TOKENHASH)
);
GO

-- =============================================================================
-- INDEXES
-- =============================================================================

CREATE INDEX IX_MDXPPARTY_GSTINHASH
    ON MDXPPARTY (PRIMARYGSTINHASH)
    WHERE PRIMARYGSTINHASH IS NOT NULL;
GO

CREATE INDEX IX_MDXPPARTY_PANHASH
    ON MDXPPARTY (PRIMARYPANHASH)
    WHERE PRIMARYPANHASH IS NOT NULL;
GO

CREATE INDEX IX_TDXPPARTYLINK_DXPPARTYID
    ON TDXPPARTYLINK (DXPPARTYID);
GO

CREATE INDEX IX_TDXPPARTYLINK_TENANT_LOCALPARTY
    ON TDXPPARTYLINK (TENANTID, LOCALPARTYID);
GO

CREATE INDEX IX_TDXPPARTYBRANCHLINK_DXPPARTYID
    ON TDXPPARTYBRANCHLINK (DXPPARTYID);
GO

CREATE INDEX IX_MDXPUSERPARTYROLE_DXPUSERID
    ON MDXPUSERPARTYROLE (DXPUSERID, STATUS);
GO

CREATE INDEX IX_MDXPUSERPARTYROLE_DXPPARTYID
    ON MDXPUSERPARTYROLE (DXPPARTYID);
GO

CREATE INDEX IX_MDXPREFRESHTOKEN_DXPUSERID
    ON MDXPREFRESHTOKEN (DXPUSERID, EXPIRESON)
    WHERE REVOKEDON IS NULL;
GO

-- =============================================================================
-- END OF MIGRATION 20260710_DXP_Phase1_Schema (SQL Server variant)
-- =============================================================================