-- =============================================================================
-- DXP — Digital Experience Platform (Party/UPI identity + Vendor Portal Phase 1)
-- Migration: 20260711 — KYC Profile (Phase 1.2)
-- Database:   DXPDb (PostgreSQL variant — see companion _SqlServer.sql)
-- Plan:       /Users/venkatv/.claude/plans/in-downloads-dxp-folder-requirements-optimized-karp.md
--
-- Run order: 20260711 (after 20260710_DXP_Phase1_Schema_Postgres.sql — depends on
--            TDXPPARTYBRANCHLINK)
--
-- See the SQL Server variant's header for the full encryption/UAS-linkage rationale
-- (identical here) — GSTIN/PAN are AES-GCM encrypted with a Vault-resolved key, hash columns
-- are one-way SHA-256 for lookup only, and the actual documents live in UAS's TATTACHMENT.
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. MDXPPARTYKYCPROFILE
--    Structured KYC fields, scoped per TDXPPARTYBRANCHLINK row (not one-per-party).
--    VERIFICATIONSTATUS: 1=Pending 2=Verified 3=Rejected
--    STATUS: 1=Active 2=Superseded 3=Deleted
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS MDXPPARTYKYCPROFILE (
    DXPPARTYKYCPROFILEID   INTEGER         PRIMARY KEY,
        -- application-generated (AutoNumber) — see header, not SERIAL
    DXPPARTYBRANCHLINKID    INTEGER         NOT NULL REFERENCES TDXPPARTYBRANCHLINK (DXPPARTYBRANCHLINKID),
    GSTINENCRYPTED          VARCHAR(500),   -- AES-GCM ciphertext, base64
    GSTINHASH               VARCHAR(128),   -- SHA-256 hex — lookup/audit only, never reversible
    PANENCRYPTED            VARCHAR(500),
    PANHASH                 VARCHAR(128),
    VERIFICATIONSTATUS      SMALLINT        NOT NULL DEFAULT 1,  -- 1=Pending 2=Verified 3=Rejected
    VERIFIEDON              TIMESTAMP,
    VERIFIEDBYID            INTEGER,
    REJECTIONREASON         VARCHAR(500),
    ROWGUID                 UUID            NOT NULL,
        -- client-generated — UAS two-phase attachment pattern
    VERSION                 SMALLINT        NOT NULL DEFAULT 0,
    STATUS                  SMALLINT        NOT NULL DEFAULT 1,  -- 1=Active 2=Superseded 3=Deleted
    CREATEDBYID             INTEGER         NOT NULL DEFAULT -1,
    CREATEDON               TIMESTAMP       NOT NULL DEFAULT NOW(),
    MODIFIEDBYID            INTEGER         NOT NULL DEFAULT -1,
    MODIFIEDON              TIMESTAMP       NOT NULL DEFAULT NOW(),
    CONSTRAINT CHK_MDXPPARTYKYCPROFILE_VERIFICATIONSTATUS CHECK (VERIFICATIONSTATUS IN (1, 2, 3)),
    CONSTRAINT CHK_MDXPPARTYKYCPROFILE_STATUS CHECK (STATUS IN (1, 2, 3)),
    CONSTRAINT UX_MDXPPARTYKYCPROFILE_ROWGUID UNIQUE (ROWGUID)
);

CREATE INDEX IF NOT EXISTS IX_MDXPPARTYKYCPROFILE_BRANCHLINK
    ON MDXPPARTYKYCPROFILE (DXPPARTYBRANCHLINKID, STATUS);

CREATE INDEX IF NOT EXISTS IX_MDXPPARTYKYCPROFILE_GSTINHASH
    ON MDXPPARTYKYCPROFILE (GSTINHASH) WHERE GSTINHASH IS NOT NULL;

-- =============================================================================
-- END OF MIGRATION 20260711_DXP_Phase1_2_KycProfile_Schema (PostgreSQL variant)
-- =============================================================================