-- =============================================================================
-- DXP Platform — internal "Verify KYC" menu/DB-seed migration (PostgreSQL variant)
-- Migration: 20260723 — DXP KYC Verification
-- Database:  the same internal/tenant DB that owns MMENU/MMODULE/MMODULEVSMENU/MROLE/
--            MROLEVSMENU/MWEBFORM for whichever GoodBooks-staff tenant is designated to run
--            internal-admin actions — same central-schema scope as Entitlement's own menu-seed
--            migrations (see Thread 0 §20's own conclusion: [MenuRights] resolves MROLEVSMENU
--            against whatever tenant DB the caller's Login header points to, not a dedicated
--            DXP-only DB).
--
-- See the SQL Server companion file (20260723_DXP_KycVerification_MenuSeed_SqlServer.sql) for
-- the full purpose/scope note — identical caveats apply: @ModuleId/@AdminRoleId MUST be resolved
-- against the target install's real MMODULE/MROLE tables before running, and no internal-admin
-- FE screen is built as part of this migration (Thread 0 §23.2 scopes that out) — this only
-- registers the menu/rights rows backing
-- [MenuRights("dxpkycverification", RightOperation.Update)] on POST /DXP/Kyc/Verify.
--
-- This is DXP's first internal-admin menu entry (SlNo starts at 1, SECTION='DXP') — not a
-- continuation of Entitlement's own independent menu-code sequence.
--
-- ID GENERATION: new WEBFORMID/MENUID/MODULEVSMENUID/ROLEVSMENUID values are reserved from
-- MAUTONUMBER (entitycode = 'WEBFORM'/'MENU'/'MODULEVSMENU'/'ROLEVSMENU'), not MAX(id)+1 — see
-- docs/AutoNumber-Seed-Pattern.md.
-- =============================================================================

DO $$
DECLARE
    -- RESOLVED using the same real values as 20260716_Entitlement_Phase1_6_MenuSeed_SqlServer.sql
    -- (see that migration's own VERIFICATION STATUS section): the real "Admin" MMODULE confirmed
    -- at MODULEID -1900000000 across 11 real tenant DBs, and GBSUPERUSER (-1799999978), chosen
    -- over the other real role found (SystemAdmin, -1799999969). Verify both before running.
    -- (DXP has no menu tree of its own — this reuses the same central Admin module/role as
    -- Entitlement's menu-seeds, consistent with Thread 0 §23.3's own note that [MenuRights]
    -- resolves against whatever tenant DB the caller's Login header points to.)
    v_module_id     INT := -1900000000;
    v_admin_role_id INT := -1799999978;
    v_count         INT;
    v_base_id       BIGINT;
BEGIN

CREATE TEMP TABLE dxp_kyc_verification_seed (menu_code VARCHAR(50), menu_name VARCHAR(200), sl_no SMALLINT) ON COMMIT DROP;
INSERT INTO dxp_kyc_verification_seed (menu_code, menu_name, sl_no) VALUES
    ('dxpkycverification', 'Verify KYC', 1);

    -- ── 1. MWEBFORM ──────────────────────────────────────────────────────────
    SELECT COUNT(*) INTO v_count FROM dxp_kyc_verification_seed s
    WHERE NOT EXISTS (SELECT 1 FROM MWEBFORM w WHERE w.WEBFORMSECONDURL = s.menu_code);
    IF v_count > 0 THEN
        UPDATE mautonumber SET autoid = autoid + v_count
        WHERE entitycode = 'WEBFORM' RETURNING autoid INTO v_base_id;
        v_base_id := v_base_id - v_count;

        INSERT INTO MWEBFORM
            (WEBFORMID, WEBFORMNAME, WEBFORMURL, TEMPLATE, MODULEID, SORTORDER, STATUS, VERSION, SOURCETYPE,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, WEBFORMSECONDURL, OBJECTMODELID, OBJECTMODELIDS)
        SELECT
            v_base_id + ROW_NUMBER() OVER (ORDER BY s.sl_no),
            s.menu_name, '', 0, v_module_id, s.sl_no, 1, 0, 0,
            -1, '1900-01-01', -1, '1900-01-01', s.menu_code, -1, '-1'
        FROM dxp_kyc_verification_seed s
        WHERE NOT EXISTS (SELECT 1 FROM MWEBFORM w WHERE w.WEBFORMSECONDURL = s.menu_code);
    END IF;

    -- ── 2. MMENU ─────────────────────────────────────────────────────────────
    SELECT COUNT(*) INTO v_count FROM dxp_kyc_verification_seed s
    WHERE NOT EXISTS (SELECT 1 FROM MMENU m WHERE m.MENUCODE = s.menu_code);
    IF v_count > 0 THEN
        UPDATE mautonumber SET autoid = autoid + v_count
        WHERE entitycode = 'MENU' RETURNING autoid INTO v_base_id;
        v_base_id := v_base_id - v_count;

        INSERT INTO MMENU
            (MENUID, MENUCODE, MENUNAME, MODULEID, GCMTYPEID, SECTION, ISPARENTMENU, PARENTID, PARENTIDS,
             LEVELS, REMARKS, REPORTID, WEBFORMID, MENUTYPE, MENUNATURE, IMAGEID, PAGEID, MENUDISPLAYNAME,
             MENUDESCRIPTION, MENUTOOLTIP, MENUCAPTION, SAVEWEBSERVICEID, GETWEBSERVICEID,
             DELETEWEBSERVICEID, BIZTRANSACTIONCLASSID, ENTITYID, HELPURL, SORTORDER, STATUS, VERSION,
             SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DEFAULTREPORTFORMATID,
             URIPARAMETERVALUE, LISTMENUID, ANALYSISQUERYID, DEFAULTMENUID, FROMVERSION, TOVERSION,
             SECTIONID, OBJECTMODELID)
        SELECT
            v_base_id + ROW_NUMBER() OVER (ORDER BY s.sl_no),
            s.menu_code, s.menu_name, v_module_id, -1, 'DXP', 0, -1, '-1',
            1, 'Seeded by 20260723_DXP_KycVerification_MenuSeed', -1, w.WEBFORMID, 0, 2, -1, -1, s.menu_name,
            s.menu_name, s.menu_name, s.menu_name, -1, -1,
            -1, -1, -1, '', s.sl_no, 1, 0,
            0, -1, '1900-01-01', -1, '1900-01-01', -1,
            '', -1, -1, -1, 0, 9999,
            -1, -1
        FROM dxp_kyc_verification_seed s
        INNER JOIN MWEBFORM w ON w.WEBFORMSECONDURL = s.menu_code
        WHERE NOT EXISTS (SELECT 1 FROM MMENU m WHERE m.MENUCODE = s.menu_code);
    END IF;

    -- ── 3. MMODULEVSMENU ─────────────────────────────────────────────────────
    SELECT COUNT(*) INTO v_count FROM dxp_kyc_verification_seed s
    INNER JOIN MMENU m ON m.MENUCODE = s.menu_code
    WHERE NOT EXISTS (SELECT 1 FROM MMODULEVSMENU mv WHERE mv.MENUID = m.MENUID AND mv.MODULEID = v_module_id);
    IF v_count > 0 THEN
        UPDATE mautonumber SET autoid = autoid + v_count
        WHERE entitycode = 'MODULEVSMENU' RETURNING autoid INTO v_base_id;
        v_base_id := v_base_id - v_count;

        INSERT INTO MMODULEVSMENU (MODULEVSMENUID, MODULEID, SLNO, MENUID, PARENTID, PARENTIDS, LEVELS)
        SELECT
            v_base_id + ROW_NUMBER() OVER (ORDER BY s.sl_no),
            v_module_id, s.sl_no, m.MENUID, -1, '-1', 1
        FROM dxp_kyc_verification_seed s
        INNER JOIN MMENU m ON m.MENUCODE = s.menu_code
        WHERE NOT EXISTS (SELECT 1 FROM MMODULEVSMENU mv WHERE mv.MENUID = m.MENUID AND mv.MODULEID = v_module_id);
    END IF;

    -- ── 4. MROLEVSMENU ───────────────────────────────────────────────────────
    SELECT COUNT(*) INTO v_count FROM dxp_kyc_verification_seed s
    INNER JOIN MMENU m ON m.MENUCODE = s.menu_code
    WHERE NOT EXISTS (SELECT 1 FROM MROLEVSMENU rv WHERE rv.MENUID = m.MENUID AND rv.ROLEID = v_admin_role_id);
    IF v_count > 0 THEN
        UPDATE mautonumber SET autoid = autoid + v_count
        WHERE entitycode = 'ROLEVSMENU' RETURNING autoid INTO v_base_id;
        v_base_id := v_base_id - v_count;

        INSERT INTO MROLEVSMENU (ROLEVSMENUID, ROLEID, MENUID, SLNO, ALLOW)
        SELECT
            v_base_id + ROW_NUMBER() OVER (ORDER BY s.sl_no),
            v_admin_role_id, m.MENUID, 1, '000000'
        FROM dxp_kyc_verification_seed s
        INNER JOIN MMENU m ON m.MENUCODE = s.menu_code
        WHERE NOT EXISTS (SELECT 1 FROM MROLEVSMENU rv WHERE rv.MENUID = m.MENUID AND rv.ROLEID = v_admin_role_id);
    END IF;

END $$;

-- =============================================================================
-- END OF MIGRATION 20260723_DXP_KycVerification_MenuSeed (PostgreSQL variant)
-- =============================================================================