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
);