-- =============================================================================
-- Entitlement Platform — gb-ent-admin menu/DB-seed migration (PostgreSQL variant)
-- Migration: 20260716 — Entitlement Phase 1.6
-- Database:  the central/system DB that owns MMENU/MMODULE/MMODULEVSMENU/MROLE/MROLEVSMENU/
--            MWEBFORM — same central-schema scope as MCLIENT/MUSER/TPARTNERPRODUCT, which every
--            other Entitlement migration already FK-references without creating.
--
-- See the SQL Server companion file (20260716_Entitlement_Phase1_6_MenuSeed_SqlServer.sql) for
-- the full "VERIFICATION STATUS" note — identical caveats apply here: MMENU's ancillary legacy
-- FK columns default to -1 per this system's own established "NONE" sentinel convention (a
-- reasoned inference, not independently confirmed against a live DB); @ModuleId and @AdminRoleId
-- MUST be resolved against the target install's real MMODULE/MROLE tables before running.
--
-- 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 real values the SQL Server companion migration's own VERIFICATION
    -- STATUS section documented: the "Admin" MMODULE confirmed at MODULEID -1900000000 across
    -- 11 real tenant DBs, and GBSUPERUSER (-1799999978), the chosen candidate over the other
    -- real role found (SystemAdmin, -1799999969). Neither is independently confirmed as THE
    -- right value for every target install; verify both before running.
    v_module_id     INT := -1900000000;
    v_admin_role_id INT := -1799999978;
    v_count         INT;
    v_base_id       BIGINT;
BEGIN

CREATE TEMP TABLE ent_menu_seed (menu_code VARCHAR(50), menu_name VARCHAR(200), sl_no SMALLINT) ON COMMIT DROP;
INSERT INTO ent_menu_seed (menu_code, menu_name, sl_no) VALUES
    ('entdashboard',    'Entitlement Dashboard',  1),
    ('entfeaturegroups','Feature Groups',         2),
    ('entfeatures',     'Features',               3),
    ('entplans',        'Plans',                  4),
    ('entplanmatrix',   'Plan Feature Matrix',    5),
    ('entsubscriptions','Subscriptions',          6),
    ('ententitlements', 'Entitlements',           7),
    ('entlicenses',     'Licenses',               8),
    ('entfeatureflags', 'Feature Flags',          9),
    ('entflagtargets',  'Flag Targets',          10),
    ('entbetaclients',  'Beta Clients',          11),
    ('entbundles',      'Bundles',               12),
    ('entmenusetmap',   'Menu Set Map',          13),
    ('entaudit',        'Audit',                 14);

    -- ── 1. MWEBFORM ──────────────────────────────────────────────────────────
    SELECT COUNT(*) INTO v_count FROM ent_menu_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 ent_menu_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 ent_menu_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, 'Entitlement', 0, -1, '-1',
            1, 'Seeded by 20260716_Entitlement_Phase1_6_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 ent_menu_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 ent_menu_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 ent_menu_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 ent_menu_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 ent_menu_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 20260716_Entitlement_Phase1_6_MenuSeed (PostgreSQL variant)
-- =============================================================================