-- This script adds a new top-level menu "Platform Management"
-- with two sub-menus "User Management" and "Platform Parameters Management".
-- These menus will be assigned to the 'admin' role.

-- Start Transaction for atomicity
BEGIN;

-- 1. Find the ID of the 'admin' role
--    Assuming 'admin' role name exists and is unique.
DO $$
DECLARE
    admin_role_id INTEGER;
    platform_management_menu_id INTEGER;
    user_management_menu_id INTEGER;
    platform_parameters_menu_id INTEGER;
BEGIN
    SELECT id INTO admin_role_id FROM roles WHERE name = 'admin';

    IF admin_role_id IS NULL THEN
        RAISE EXCEPTION 'Admin role not found. Please ensure the admin role exists.';
    END IF;

    -- 2. Insert the top-level menu: "Platform Management"
    --    Check if it already exists to prevent duplicates on re-run
    SELECT id INTO platform_management_menu_id FROM menus WHERE name = 'platform_management' AND parent_id IS NULL;

    IF platform_management_menu_id IS NULL THEN
        INSERT INTO menus (name, title, icon, path, component, sort_order, is_active, description, created_at, updated_at)
        VALUES (
            'platform_management',
            '平台管理',
            'settings', -- Using a generic settings icon for platform management
            NULL,       -- It's a parent menu, no direct path
            NULL,
            3,          -- Assuming sort_order 1 & 2 are for Dashboard & Data Management
            TRUE,
            '管理用户和系统参数',
            NOW(),
            NOW()
        ) RETURNING id INTO platform_management_menu_id;
        RAISE NOTICE 'Inserted Platform Management menu with ID: %', platform_management_menu_id;

        -- Assign to admin role
        INSERT INTO role_menus (role_id, menu_id, created_at)
        VALUES (admin_role_id, platform_management_menu_id, NOW());
        RAISE NOTICE 'Assigned Platform Management to admin role.';
    ELSE
        RAISE NOTICE 'Platform Management menu already exists with ID: %', platform_management_menu_id;
    END IF;


    -- 3. Insert sub-menu: "User Management"
    --    Check if it already exists
    SELECT id INTO user_management_menu_id FROM menus WHERE name = 'user_management' AND parent_id = platform_management_menu_id;

    IF user_management_menu_id IS NULL THEN
        INSERT INTO menus (parent_id, name, title, icon, path, component, sort_order, is_active, description, created_at, updated_at)
        VALUES (
            platform_management_menu_id,
            'user_management',
            '用户管理',
            'users', -- Icon for user management
            '/admin/users', -- Admin users page path
            'admin/Users',  -- React component path
            1,
            TRUE,
            '管理系统用户账号',
            NOW(),
            NOW()
        ) RETURNING id INTO user_management_menu_id;
        RAISE NOTICE 'Inserted User Management menu with ID: %', user_management_menu_id;

        -- Assign to admin role
        INSERT INTO role_menus (role_id, menu_id, created_at)
        VALUES (admin_role_id, user_management_menu_id, NOW());
        RAISE NOTICE 'Assigned User Management to admin role.';
    ELSE
        RAISE NOTICE 'User Management menu already exists with ID: %', user_management_menu_id;
    END IF;


    -- 4. Insert sub-menu: "Platform Parameters Management"
    --    Check if it already exists
    SELECT id INTO platform_parameters_menu_id FROM menus WHERE name = 'platform_parameters_management' AND parent_id = platform_management_menu_id;

    IF platform_parameters_menu_id IS NULL THEN
        INSERT INTO menus (parent_id, name, title, icon, path, component, sort_order, is_active, description, created_at, updated_at)
        VALUES (
            platform_management_menu_id,
            'platform_parameters_management',
            '平台参数管理',
            'sliders', -- Icon for parameters/settings
            '/admin/settings', -- Admin settings page path
            'admin/Settings',  -- React component path
            2,
            TRUE,
            '管理系统通用配置参数',
            NOW(),
            NOW()
        ) RETURNING id INTO platform_parameters_menu_id;
        RAISE NOTICE 'Inserted Platform Parameters Management menu with ID: %', platform_parameters_menu_id;

        -- Assign to admin role
        INSERT INTO role_menus (role_id, menu_id, created_at)
        VALUES (admin_role_id, platform_parameters_menu_id, NOW());
        RAISE NOTICE 'Assigned Platform Parameters Management to admin role.';
    ELSE
        RAISE NOTICE 'Platform Parameters Management menu already exists with ID: %', platform_parameters_menu_id;
    END IF;

END $$;

-- Commit the transaction
COMMIT;