-- =============================================================================
-- GB5 Framework — SysJob "My Jobs" screen menu/DB-seed migration (PostgreSQL variant)
-- Migration: 20260724 — Framework SysJob My Jobs
-- Database:  the central/system DB that owns MMENU/MMODULE/MMODULEVSMENU/MROLE/MROLEVSMENU/MWEBFORM
--
-- See the SQL Server companion file (20260724_Framework_SysJob_MenuSeed_SqlServer.sql) for the
-- full context. Identical caveats apply: @ModuleId/@AdminRoleId MUST be resolved against the
-- target install's real MMODULE/MROLE tables before running.
--
-- VERIFY BEFORE RUNNING:
--   SELECT moduleid, modulecode FROM mmodule WHERE modulecode = 'Admin'    -- expect -1900000000
--   SELECT roleid, rolename FROM mrole WHERE rolename = 'GBSUPERUSER'       -- expect -1799999978
-- =============================================================================

DO $$
DECLARE
    v_module_id     INT := -1900000000;
    v_admin_role_id INT := -1799999978;
    v_count         INT;
    v_base_id       BIGINT;
BEGIN

CREATE TEMP TABLE sysjob_menu_seed (menu_code VARCHAR(50), menu_name VARCHAR(200), sl_no SMALLINT) ON COMMIT DROP;
INSERT INTO sysjob_menu_seed (menu_code, menu_name, sl_no) VALUES
    ('adminmyjobs', 'My Jobs', 1);

    -- ── 1. MWEBFORM ──────────────────────────────────────────────────────────
    SELECT COUNT(*) INTO v_count FROM sysjob_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 sysjob_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 sysjob_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, 'Administration', 0, -1, '-1',
            1, 'Seeded by 20260724_Framework_SysJob_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 sysjob_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 sysjob_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 sysjob_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 sysjob_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 sysjob_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 20260724_Framework_SysJob_MenuSeed (PostgreSQL variant)
-- =============================================================================