Subversion Repositories SmartDukaan

-- Migration: Add "Bulk Order Approval Report" menu item

-- Visible to the same categories/roles as "Transaction Approval" (action_class = 'transaction-approval')

-- Step 1: Find the parent_menu_id and sequence of existing "Transaction Approval" menu

-- SELECT id, display_text, parent_menu_id, sequence, action_class FROM auth.menu WHERE action_class = 'transaction-approval';

-- Step 2: Insert the new menu item under the same parent, next sequence

INSERT INTO auth.menu (display_text, description, parent_menu_id, sequence, action_class, icon_class)

SELECT 'Approval Report', 'Bulk Order Approval Report', m.parent_menu_id, m.sequence + 1, 'bulk-order-approval-report', m.icon_class

FROM (SELECT * FROM auth.menu WHERE action_class = 'transaction-approval') m

WHERE NOT EXISTS (SELECT 1 FROM auth.menu WHERE action_class = 'bulk-order-approval-report');

-- Step 3: Copy menu_category mappings so it's visible to the same roles

INSERT INTO auth.menu_category (menu_id, category_id, escalation_type)

SELECT new_menu.id, mc.category_id, mc.escalation_type

FROM auth.menu_category mc

JOIN auth.menu old_menu ON old_menu.id = mc.menu_id AND old_menu.action_class = 'transaction-approval'

JOIN auth.menu new_menu ON new_menu.action_class = 'bulk-order-approval-report'

WHERE NOT EXISTS (

    SELECT 1 FROM auth.menu_category mc2

    WHERE mc2.menu_id = new_menu.id AND mc2.category_id = mc.category_id AND mc2.escalation_type = mc.escalation_type

);

-- Step 4: Add API access for non-admin roles (same as pendingApprovals)

INSERT INTO api (uri, description)

SELECT '/transaction/bulkOrderApprovalReport', 'Bulk Order Approval Report'

WHERE NOT EXISTS (SELECT 1 FROM api WHERE uri = '/transaction/bulkOrderApprovalReport');

INSERT INTO role_api (role_id, api_id)

SELECT ra.role_id, new_api.id

FROM role_api ra

JOIN api old_api ON old_api.id = ra.api_id AND old_api.uri = '/transaction/pendingApprovals'

JOIN api new_api ON new_api.uri = '/transaction/bulkOrderApprovalReport'

WHERE NOT EXISTS (

    SELECT 1 FROM role_api ra2 WHERE ra2.role_id = ra.role_id AND ra2.api_id = new_api.id

);