Motmaina ACP - RBAC Menus, Website Features & Permissions Seed (stg-v2 to master)

sidebar.sql

-- =============================================================================
-- Migration: Add RBAC Menus, Website Features, Permissions, and Localization
-- Diff: stg-v2 compared to master (PR #1161)
--
-- This script contains all RBAC menus, website_features, rbac_menu_actions,
-- rbac_roles_permissions, and associated sidebar localizations introduced
-- in stg-v2. Idempotent (safe to run multiple times).
-- =============================================================================

SET NAMES utf8mb4;

-- =============================================================================
-- 1. TF-721: Customer Wallet (Top-level Sidebar Menu)
-- =============================================================================

-- Add Customer Wallet to rbac_menus (direct link, no child)
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'customer_wallet', 'acp/customer_wallet/listall', '<svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="currentColor" class="bi bi-wallet2" viewBox="0 0 16 16"><path d="M12.136.326A1.5 1.5 0 0 1 14 1.78V3h.5A1.5 1.5 0 0 1 16 4.5v9A1.5 1.5 0 0 1 14.5 15h-13A1.5 1.5 0 0 1 0 13.5v-9A1.5 1.5 0 0 1 1.5 3H5V1.78A1.5 1.5 0 0 1 7.864.326L12.136.326zM10 1.78l-4 .5V3h4V1.78zM1.5 4a.5.5 0 0 0-.5.5v1.128l4.5 1.5 4.5-1.5V4.5a.5.5 0 0 0-.5-.5h-8zM14 5.772l-4.5 1.5L14 8.772V5.772zm-13 .5V13.5a.5.5 0 0 0 .5.5h13a.5.5 0 0 0 .5-.5V8.772l-5.11 1.704a1 1 0 0 1-.78 0L1 6.272z"/></svg>', '', '1', 'customer_wallet', '0', '0'
WHERE NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'customer_wallet');

-- Insert website_features for customer_wallet (required for top-level menu display)
INSERT INTO `website_features` (`PID`, `Name_ar`, `Name_en`, `Menu_en`, `Menu_ar`, `Website_Link`, `Is_Header`, `Is_Footer`, `Status`, `Order_In_List`, `Created_Date`)
SELECT m.Id, 'المحفظة', 'Customer Wallet', 'Customer Wallet', 'المحفظة', '', '0', '0', '1', '0', NOW()
FROM `rbac_menus` m WHERE m.Menu_Key = 'customer_wallet'
AND NOT EXISTS (SELECT 1 FROM `website_features` wf WHERE wf.PID = m.Id);


-- =============================================================================
-- 2. TF-600: Notifications Submenu (notif_listall, notification_hooks)
-- =============================================================================

-- Turn existing "Notifications" top-level into dropdown parent
UPDATE `rbac_menus`
SET `Link` = '#', `DefaultSelected` = 1
WHERE `Menu_Key` = 'notifications' AND `Link` != '#';

-- Submenu 1: legacy notifications list
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'notif_listall', 'acp/notifications/listall', '', '', '0', 'notif_listall', parent.Id, '0'
FROM `rbac_menus` parent
WHERE parent.Menu_Key = 'notifications'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'notif_listall');

-- Submenu 2: notification hooks dashboard
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'notification_hooks', 'acp/notification_hooks/listall', '', '', '0', 'notification_hooks', parent.Id, '0'
FROM `rbac_menus` parent
WHERE parent.Menu_Key = 'notifications'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'notification_hooks');

-- Reparent if notification_hooks previously added as top-level
UPDATE `rbac_menus` m
JOIN `rbac_menus` parent ON parent.Menu_Key = 'notifications'
SET m.Parent_ID = parent.Id, m.Is_SideBar_Menu = 0
WHERE m.Menu_Key = 'notification_hooks' AND m.Parent_ID = 0;

-- Drop website_features for notification_hooks if previously created (child menus do not use it)
DELETE wf FROM `website_features` wf
JOIN `rbac_menus` m ON m.Id = wf.PID
WHERE m.Menu_Key = 'notification_hooks';

-- Localization for notifications children
INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT 'notification_hooks', 'Notification Hooks', 'إشعارات الخطافات'
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = 'notification_hooks');

INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT 'notif_listall', 'Notifications', 'التنبيهات'
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = 'notif_listall');

-- Permissions for notification children (roles 1 & 3)
INSERT INTO `rbac_menu_actions` (`Menu_ID`, `Action_ID`)
SELECT m.Id, a.Action_ID
FROM `rbac_menus` m
JOIN `rbac_actions` a ON a.Action_Key = 'view'
WHERE m.Menu_Key IN ('notification_hooks', 'notif_listall')
AND NOT EXISTS (
    SELECT 1 FROM `rbac_menu_actions` ma
    WHERE ma.Menu_ID = m.Id AND ma.Action_ID = a.Action_ID
);

INSERT INTO `rbac_roles_permissions` (`Role_ID`, `Menu_ID`, `MenuAction_ID`)
SELECT roles.Role_ID, ma.Menu_ID, ma.MA_ID
FROM (SELECT 1 AS Role_ID UNION SELECT 3 AS Role_ID) AS roles
JOIN `rbac_menus` m ON m.Menu_Key IN ('notification_hooks', 'notif_listall')
JOIN `rbac_menu_actions` ma ON ma.Menu_ID = m.Id
WHERE NOT EXISTS (
    SELECT 1 FROM `rbac_roles_permissions` rp
    WHERE rp.Role_ID = roles.Role_ID AND rp.MenuAction_ID = ma.MA_ID
);


-- =============================================================================
-- 3. TF-761: Consultants Menu Split (manage_consultants, orders, ratings)
-- =============================================================================

-- Turn existing top-level "Consultants" into dropdown parent
UPDATE `rbac_menus` SET `Link` = '#' WHERE `Menu_Key` = 'consultants' AND `Link` != '#';

-- Submenu 1: Consultants
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'consultants', 'acp/consultations/manageConsultants', '', '', '1', 'manage_consultants', p.Id, '0'
FROM `rbac_menus` p
WHERE p.Menu_Key = 'consultants'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'manage_consultants');

-- Submenu 2: Orders
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'orders', 'acp/consultations/consultantOrders', '', '', '1', 'consultant_orders_menu', p.Id, '0'
FROM `rbac_menus` p
WHERE p.Menu_Key = 'consultants'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'consultant_orders_menu');

-- Submenu 3: Ratings
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'consultant_ratings', 'acp/consultations/consultantRatings', '', '', '1', 'consultant_ratings_menu', p.Id, '0'
FROM `rbac_menus` p
WHERE p.Menu_Key = 'consultants'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'consultant_ratings_menu');

-- Permissions for consultants children (roles 1 & 3: view, export)
INSERT INTO `rbac_menu_actions` (`Menu_ID`, `Action_ID`)
SELECT m.Id, a.Action_ID
FROM `rbac_menus` m
JOIN `rbac_actions` a ON a.Action_Key IN ('view', 'export')
WHERE m.Menu_Key IN ('manage_consultants', 'consultant_orders_menu', 'consultant_ratings_menu')
AND NOT EXISTS (
    SELECT 1 FROM `rbac_menu_actions` ma
    WHERE ma.Menu_ID = m.Id AND ma.Action_ID = a.Action_ID
);

INSERT INTO `rbac_roles_permissions` (`Role_ID`, `Menu_ID`, `MenuAction_ID`)
SELECT roles.Role_ID, ma.Menu_ID, ma.MA_ID
FROM (SELECT 1 AS Role_ID UNION SELECT 3 AS Role_ID) AS roles
JOIN `rbac_menus` m ON m.Menu_Key IN ('manage_consultants', 'consultant_orders_menu', 'consultant_ratings_menu')
JOIN `rbac_menu_actions` ma ON ma.Menu_ID = m.Id
WHERE NOT EXISTS (
    SELECT 1 FROM `rbac_roles_permissions` rp
    WHERE rp.Role_ID = roles.Role_ID AND rp.MenuAction_ID = ma.MA_ID
);


-- =============================================================================
-- 4. TF-802: Prescriptions (Top-level Dropdown Parent + 7 Reference Tables)
-- =============================================================================

-- Top-level dropdown parent
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'prescriptions', '#', '<svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="currentColor" viewBox="0 0 16 16"><path d="M3 2a1 1 0 0 1 1-1h8a1 1 0 0 1 1 1v12a1 1 0 0 1-1 1H4a1 1 0 0 1-1-1V2z"/><path fill="#fff" d="M6.5 4h3v2.5H12v3H9.5V12h-3V9.5H4v-3h2.5z"/></svg>', '', '1', 'prescriptions', '0', '1'
WHERE NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescriptions');

-- website_features row for prescriptions parent
INSERT INTO `website_features` (`PID`, `Name_ar`, `Name_en`, `Menu_en`, `Menu_ar`, `Website_Link`, `Is_Header`, `Is_Footer`, `Status`, `Order_In_List`, `Created_Date`)
SELECT m.Id, 'الوصفات الطبية', 'Prescriptions', 'Prescriptions', 'الوصفات الطبية', '', '0', '0', '1', '0', NOW()
FROM `rbac_menus` m WHERE m.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `website_features` wf WHERE wf.PID = m.Id);

-- Submenu 1: Duration Units
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_duration_units', 'acp/prescriptions/duration_units/list', '', '', '0', 'prescription_duration_units', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescription_duration_units');

-- Submenu 2: Frequencies
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_frequencies', 'acp/prescriptions/frequencies/list', '', '', '0', 'prescription_frequencies', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescription_frequencies');

-- Submenu 3: Strength Units
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_strength_units', 'acp/prescriptions/strength_units/list', '', '', '0', 'prescription_strength_units', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescription_strength_units');

-- Submenu 4: Dosage Forms
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_dosage_forms', 'acp/prescriptions/dosage_forms/list', '', '', '0', 'prescription_dosage_forms', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescription_dosage_forms');

-- Submenu 5: Routes
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_routes', 'acp/prescriptions/routes/list', '', '', '0', 'prescription_routes', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescription_routes');

-- Submenu 6: PRN Reasons
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_prn_reasons', 'acp/prescriptions/prn_reasons/list', '', '', '0', 'prescription_prn_reasons', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'prescription_prn_reasons');

-- Submenu 7: Dosage Units
INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'presc_dosage_units', 'acp/prescriptions/dosage_units/list', '', '', '0', 'dosage_units', parent.Id, '0'
FROM `rbac_menus` parent WHERE parent.Menu_Key = 'prescriptions'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'dosage_units');

-- Reparent existing "medicin" under Prescriptions
UPDATE `rbac_menus` m
JOIN `rbac_menus` parent ON parent.Menu_Key = 'prescriptions'
SET m.Link = 'acp/prescriptions/medicine/list', m.Parent_ID = parent.Id
WHERE m.Menu_Key = 'medicin' AND m.Link != 'acp/prescriptions/medicine/list';

-- Ensure links are updated for reference tables
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/duration_units/list' WHERE `Menu_Key` = 'prescription_duration_units' AND `Link` != 'acp/prescriptions/duration_units/list';
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/frequencies/list' WHERE `Menu_Key` = 'prescription_frequencies' AND `Link` != 'acp/prescriptions/frequencies/list';
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/strength_units/list' WHERE `Menu_Key` = 'prescription_strength_units' AND `Link` != 'acp/prescriptions/strength_units/list';
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/dosage_forms/list' WHERE `Menu_Key` = 'prescription_dosage_forms' AND `Link` != 'acp/prescriptions/dosage_forms/list';
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/routes/list' WHERE `Menu_Key` = 'prescription_routes' AND `Link` != 'acp/prescriptions/routes/list';
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/prn_reasons/list' WHERE `Menu_Key` = 'prescription_prn_reasons' AND `Link` != 'acp/prescriptions/prn_reasons/list';
UPDATE `rbac_menus` SET `Link` = 'acp/prescriptions/dosage_units/list' WHERE `Menu_Key` = 'dosage_units' AND `Link` != 'acp/prescriptions/dosage_units/list';

-- Permissions for prescription submenus (roles 1 & 3: view, export)
INSERT INTO `rbac_menu_actions` (`Menu_ID`, `Action_ID`)
SELECT m.Id, a.Action_ID
FROM `rbac_menus` m
JOIN `rbac_actions` a ON a.Action_Key IN ('view', 'export')
WHERE m.Menu_Key IN (
    'prescription_duration_units', 'prescription_frequencies', 'prescription_strength_units',
    'prescription_dosage_forms', 'prescription_routes', 'prescription_prn_reasons', 'dosage_units'
)
AND NOT EXISTS (
    SELECT 1 FROM `rbac_menu_actions` ma
    WHERE ma.Menu_ID = m.Id AND ma.Action_ID = a.Action_ID
);

INSERT INTO `rbac_roles_permissions` (`Role_ID`, `Menu_ID`, `MenuAction_ID`)
SELECT roles.Role_ID, ma.Menu_ID, ma.MA_ID
FROM (SELECT 1 AS Role_ID UNION SELECT 3 AS Role_ID) AS roles
JOIN `rbac_menus` m ON m.Menu_Key IN (
    'prescription_duration_units', 'prescription_frequencies', 'prescription_strength_units',
    'prescription_dosage_forms', 'prescription_routes', 'prescription_prn_reasons', 'dosage_units'
)
JOIN `rbac_menu_actions` ma ON ma.Menu_ID = m.Id
WHERE NOT EXISTS (
    SELECT 1 FROM `rbac_roles_permissions` rp
    WHERE rp.Role_ID = roles.Role_ID AND rp.MenuAction_ID = ma.MA_ID
);

-- Localization for prescription child menus
INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT vals.k, vals.en, vals.ar FROM (
    SELECT 'presc_duration_units' AS k, 'Duration Units' AS en, 'وحدات المدة' AS ar
    UNION ALL SELECT 'presc_frequencies', 'Frequencies', 'الجرعات (التكرار)'
    UNION ALL SELECT 'presc_strength_units', 'Strength Units', 'وحدات التركيز'
    UNION ALL SELECT 'presc_dosage_forms', 'Dosage Forms', 'الأشكال الدوائية'
    UNION ALL SELECT 'presc_routes', 'Routes', 'طرق الاستخدام'
    UNION ALL SELECT 'presc_prn_reasons', 'PRN Reasons', 'أسباب الاستخدام عند الحاجة'
    UNION ALL SELECT 'presc_dosage_units', 'Dosage Units', 'وحدات الجرعات'
) AS vals
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = vals.k);

UPDATE `localization` loc
JOIN (
    SELECT 'presc_duration_units' AS k, 'Duration Units' AS en, 'وحدات المدة' AS ar
    UNION ALL SELECT 'presc_frequencies', 'Frequencies', 'الجرعات (التكرار)'
    UNION ALL SELECT 'presc_strength_units', 'Strength Units', 'وحدات التركيز'
    UNION ALL SELECT 'presc_dosage_forms', 'Dosage Forms', 'الأشكال الدوائية'
    UNION ALL SELECT 'presc_routes', 'Routes', 'طرق الاستخدام'
    UNION ALL SELECT 'presc_prn_reasons', 'PRN Reasons', 'أسباب الاستخدام عند الحاجة'
    UNION ALL SELECT 'presc_dosage_units', 'Dosage Units', 'وحدات الجرعات'
) AS vals ON loc.`Key` = vals.k
SET loc.`String_en` = vals.en, loc.`String_ar` = vals.ar
WHERE loc.`String_en` = CONCAT(vals.k, '_en') OR loc.`String_ar` = CONCAT(vals.k, '_ar');


-- =============================================================================
-- 5. TF-868: Learning Center (Submenu under Consultants)
-- =============================================================================

INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'learning_center', 'acp/learningCenter/manage', '', '', '0', 'learning_center', p.Id, '0'
FROM `rbac_menus` p
WHERE p.Menu_Key = 'consultants'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'learning_center');

INSERT INTO `rbac_menu_actions` (`Menu_ID`, `Action_ID`)
SELECT m.Id, a.Action_ID
FROM `rbac_menus` m
JOIN `rbac_actions` a ON a.Action_Key IN ('view', 'export')
WHERE m.Menu_Key = 'learning_center'
AND NOT EXISTS (
    SELECT 1 FROM `rbac_menu_actions` ma
    WHERE ma.Menu_ID = m.Id AND ma.Action_ID = a.Action_ID
);

INSERT INTO `rbac_roles_permissions` (`Role_ID`, `Menu_ID`, `MenuAction_ID`)
SELECT roles.Role_ID, ma.Menu_ID, ma.MA_ID
FROM (SELECT 1 AS Role_ID UNION SELECT 3 AS Role_ID) AS roles
JOIN `rbac_menus` m ON m.Menu_Key = 'learning_center'
JOIN `rbac_menu_actions` ma ON ma.Menu_ID = m.Id
WHERE NOT EXISTS (
    SELECT 1 FROM `rbac_roles_permissions` rp
    WHERE rp.Role_ID = roles.Role_ID AND rp.MenuAction_ID = ma.MA_ID
);

INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT 'learning_center', 'Learning Center', 'مركز التعلم'
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = 'learning_center');
UPDATE `localization` SET String_en = 'Learning Center', String_ar = 'مركز التعلم'
WHERE `Key` = 'learning_center' AND (String_en = 'learning_center_en' OR String_ar = 'learning_center_ar');


-- =============================================================================
-- 6. TF-883: Medical Files (Submenu under Consultations)
-- =============================================================================

INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'medical_files', 'acp/consultations/medicalFiles', '', '', '0', 'medical_files', p.Id, '1'
FROM `rbac_menus` p
WHERE p.Menu_String_Key = 'Consultations'
AND p.Parent_ID = 0
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'medical_files');

INSERT INTO `rbac_menu_actions` (`Menu_ID`, `Action_ID`)
SELECT m.Id, a.Action_ID
FROM `rbac_menus` m
JOIN `rbac_actions` a ON a.Action_Key = 'view'
WHERE m.Menu_Key = 'medical_files'
AND NOT EXISTS (
    SELECT 1 FROM `rbac_menu_actions` ma
    WHERE ma.Menu_ID = m.Id AND ma.Action_ID = a.Action_ID
);

INSERT INTO `rbac_roles_permissions` (`Role_ID`, `Menu_ID`, `MenuAction_ID`)
SELECT roles.Role_ID, ma.Menu_ID, ma.MA_ID
FROM (SELECT 1 AS Role_ID UNION SELECT 3 AS Role_ID) AS roles
JOIN `rbac_menus` m ON m.Menu_Key = 'medical_files'
JOIN `rbac_menu_actions` ma ON ma.Menu_ID = m.Id
WHERE NOT EXISTS (
    SELECT 1 FROM `rbac_roles_permissions` rp
    WHERE rp.Role_ID = roles.Role_ID AND rp.MenuAction_ID = ma.MA_ID
);

UPDATE `rbac_roles_permissions` rp
JOIN `rbac_menu_actions` ma ON ma.MA_ID = rp.MenuAction_ID
JOIN `rbac_menus` m ON m.Id = ma.Menu_ID
SET rp.Menu_ID = m.Id
WHERE m.Menu_Key = 'medical_files' AND rp.Menu_ID IS NULL;

INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT 'medical_files', 'Medical Files', 'ملفات طبية'
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = 'medical_files');
UPDATE `localization` SET `String_en` = 'Medical Files', `String_ar` = 'ملفات طبية'
WHERE `Key` = 'medical_files' AND (`String_en` = 'medical_files_en' OR `String_ar` = 'medical_files_ar');


-- =============================================================================
-- 7. TF-885: Immediate Readiness (Submenu under Consultants)
-- =============================================================================

INSERT INTO `rbac_menus` (`Menu_String_Key`, `Link`, `Icon`, `JS_Function`, `Is_SideBar_Menu`, `Menu_Key`, `Parent_ID`, `DefaultSelected`)
SELECT 'immediate_readiness', 'acp/consultations/immediateReadiness', '', '', '1', 'immediate_readiness', p.Id, '0'
FROM `rbac_menus` p
WHERE p.Menu_Key = 'consultants'
AND NOT EXISTS (SELECT 1 FROM `rbac_menus` WHERE `Menu_Key` = 'immediate_readiness');

INSERT INTO `rbac_menu_actions` (`Menu_ID`, `Action_ID`)
SELECT m.Id, a.Action_ID
FROM `rbac_menus` m
JOIN `rbac_actions` a ON a.Action_Key = 'view'
WHERE m.Menu_Key = 'immediate_readiness'
AND NOT EXISTS (
    SELECT 1 FROM `rbac_menu_actions` ma
    WHERE ma.Menu_ID = m.Id AND ma.Action_ID = a.Action_ID
);

INSERT INTO `rbac_roles_permissions` (`Role_ID`, `Menu_ID`, `MenuAction_ID`)
SELECT roles.Role_ID, ma.Menu_ID, ma.MA_ID
FROM (SELECT 1 AS Role_ID UNION SELECT 3 AS Role_ID) AS roles
JOIN `rbac_menus` m ON m.Menu_Key = 'immediate_readiness'
JOIN `rbac_menu_actions` ma ON ma.Menu_ID = m.Id
WHERE NOT EXISTS (
    SELECT 1 FROM `rbac_roles_permissions` rp
    WHERE rp.Role_ID = roles.Role_ID AND rp.MenuAction_ID = ma.MA_ID
);

INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT 'immediate_readiness', 'Immediate Readiness', 'الجاهزية الفورية'
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = 'immediate_readiness');
UPDATE `localization` SET `String_en` = 'Immediate Readiness', `String_ar` = 'الجاهزية الفورية'
WHERE `Key` = 'immediate_readiness' AND (`String_en` = 'immediate_readiness_en' OR `String_ar` = 'immediate_readiness_ar');


-- =============================================================================
-- 8. TF-887: Remove Legacy "Immediate Consultations" Menu
-- =============================================================================

DELETE FROM `rbac_roles_permissions`
WHERE `Menu_ID` = (SELECT `Id` FROM `rbac_menus` WHERE `Link` = 'acp/consultations/immediateConsultations' LIMIT 1);

DELETE FROM `rbac_menu_actions`
WHERE `Menu_ID` = (SELECT `Id` FROM `rbac_menus` WHERE `Link` = 'acp/consultations/immediateConsultations' LIMIT 1);

DELETE FROM `rbac_menus`
WHERE `Link` = 'acp/consultations/immediateConsultations';


-- =============================================================================
-- 9. TF-1071: Update Reports V2 Menu String Key
-- =============================================================================

INSERT INTO `localization` (`Key`, `String_en`, `String_ar`)
SELECT 'perf_report', 'Performance Report', 'تقرير الاداء'
WHERE NOT EXISTS (SELECT 1 FROM `localization` WHERE `Key` = 'perf_report');

UPDATE `rbac_menus`
SET `Menu_String_Key` = 'perf_report'
WHERE `Link` = 'acp/reports_V2' AND `Menu_String_Key` = 'reports';