-- ====================================================================
-- COMPLETE SUPERADMIN PERMISSIONS SETUP (CORRECT ORDER)
-- ====================================================================

SET @employee_id = (SELECT id FROM employees WHERE employee_id = 'EMP-001' LIMIT 1);
SET @superadmin_role_id = UUID();
SET @current_timestamp = NOW();

-- ====================================================================
-- 1. FIRST CREATE SUPERADMIN ROLE
-- ====================================================================
INSERT INTO roles (id, name, code, description, is_system_role, is_active, display_order, created_at, updated_at)
VALUES (
    @superadmin_role_id,
    'Super Administrator',
    'SUPER_ADMIN',
    'Full system access with all permissions',
    TRUE,
    TRUE,
    1,
    @current_timestamp,
    @current_timestamp
);

-- ====================================================================
-- 2. THEN ASSIGN SUPERADMIN ROLE TO EMP-001
-- ====================================================================
INSERT INTO employee_roles (employee_id, role_id, assigned_by, assigned_at)
VALUES (
    @employee_id,
    @superadmin_role_id,
    @employee_id,
    @current_timestamp
)
ON DUPLICATE KEY UPDATE 
    assigned_by = VALUES(assigned_by),
    assigned_at = VALUES(assigned_at);

-- ====================================================================
-- 3. DELETE ANY OLD PERMISSIONS FOR THIS ROLE (clean slate)
-- ====================================================================
DELETE FROM role_permissions WHERE role_id = @superadmin_role_id;

-- ====================================================================
-- 4. ADD TABLE-LEVEL PERMISSIONS (ALL TABLES)
-- ====================================================================
INSERT INTO role_permissions (id, role_id, table_name, column_name, can_read, can_create, can_edit, can_delete, can_approve, can_export, granted_by, created_at, updated_at)
SELECT 
    UUID(),
    @superadmin_role_id,
    table_name,
    NULL,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    @employee_id,
    @current_timestamp,
    @current_timestamp
FROM (
    SELECT 'company_config' AS table_name UNION ALL
    SELECT 'locations' UNION ALL
    SELECT 'module_meta' UNION ALL
    SELECT 'field_meta' UNION ALL
    SELECT 'departments' UNION ALL
    SELECT 'designations' UNION ALL
    SELECT 'roles' UNION ALL
    SELECT 'role_permissions' UNION ALL
    SELECT 'employee_permission_overrides' UNION ALL
    SELECT 'employees' UNION ALL
    SELECT 'user_sessions' UNION ALL
    SELECT 'password_reset_tokens' UNION ALL
    SELECT 'employee_roles' UNION ALL
    SELECT 'work_schedules' UNION ALL
    SELECT 'employee_schedules' UNION ALL
    SELECT 'leave_policies' UNION ALL
    SELECT 'leave_balances' UNION ALL
    SELECT 'leave_requests' UNION ALL
    SELECT 'salary_components' UNION ALL
    SELECT 'employee_salaries' UNION ALL
    SELECT 'payroll_runs' UNION ALL
    SELECT 'payslips' UNION ALL
    SELECT 'loan_applications' UNION ALL
    SELECT 'loan_installments' UNION ALL
    SELECT 'expenses' UNION ALL
    SELECT 'projects' UNION ALL
    SELECT 'tasks' UNION ALL
    SELECT 'task_comments' UNION ALL
    SELECT 'notifications' UNION ALL
    SELECT 'audit_logs'
) AS all_tables;

-- ====================================================================
-- 5. ADD COLUMN-LEVEL PERMISSIONS FOR EMPLOYEES TABLE
-- ====================================================================
INSERT INTO role_permissions (id, role_id, table_name, column_name, can_read, can_create, can_edit, can_delete, can_approve, can_export, granted_by, created_at, updated_at)
SELECT 
    UUID(),
    @superadmin_role_id,
    'employees',
    column_name,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    @employee_id,
    @current_timestamp,
    @current_timestamp
FROM (
    SELECT 'id' AS column_name UNION ALL
    SELECT 'employee_id' UNION ALL
    SELECT 'first_name' UNION ALL
    SELECT 'last_name' UNION ALL
    SELECT 'date_of_birth' UNION ALL
    SELECT 'gender' UNION ALL
    SELECT 'nationality' UNION ALL
    SELECT 'national_id' UNION ALL
    SELECT 'passport_no' UNION ALL
    SELECT 'personal_email' UNION ALL
    SELECT 'work_email' UNION ALL
    SELECT 'personal_phone' UNION ALL
    SELECT 'work_phone' UNION ALL
    SELECT 'emergency_name' UNION ALL
    SELECT 'emergency_phone' UNION ALL
    SELECT 'emergency_relation' UNION ALL
    SELECT 'address_line1' UNION ALL
    SELECT 'address_line2' UNION ALL
    SELECT 'city' UNION ALL
    SELECT 'state' UNION ALL
    SELECT 'country' UNION ALL
    SELECT 'postal_code' UNION ALL
    SELECT 'avatar_url' UNION ALL
    SELECT 'bio' UNION ALL
    SELECT 'department_id' UNION ALL
    SELECT 'designation_id' UNION ALL
    SELECT 'manager_id' UNION ALL
    SELECT 'works_at_location_id' UNION ALL
    SELECT 'allowed_locations' UNION ALL
    SELECT 'employment_type' UNION ALL
    SELECT 'joining_date' UNION ALL
    SELECT 'confirmation_date' UNION ALL
    SELECT 'resignation_date' UNION ALL
    SELECT 'last_working_day' UNION ALL
    SELECT 'status' UNION ALL
    SELECT 'password_hash' UNION ALL
    SELECT 'password_changed_at' UNION ALL
    SELECT 'failed_login_count' UNION ALL
    SELECT 'locked_until' UNION ALL
    SELECT 'last_login_at' UNION ALL
    SELECT 'bank_name' UNION ALL
    SELECT 'bank_account_no' UNION ALL
    SELECT 'bank_iban' UNION ALL
    SELECT 'bank_branch' UNION ALL
    SELECT 'notes' UNION ALL
    SELECT 'created_at' UNION ALL
    SELECT 'updated_at' UNION ALL
    SELECT 'deleted_at'
) AS emp_columns;

-- ====================================================================
-- 6. ADD COLUMN-LEVEL PERMISSIONS FOR ROLE_PERMISSIONS TABLE
-- ====================================================================
INSERT INTO role_permissions (id, role_id, table_name, column_name, can_read, can_create, can_edit, can_delete, can_approve, can_export, granted_by, created_at, updated_at)
SELECT 
    UUID(),
    @superadmin_role_id,
    'role_permissions',
    column_name,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    TRUE,
    @employee_id,
    @current_timestamp,
    @current_timestamp
FROM (
    SELECT 'id' AS column_name UNION ALL
    SELECT 'role_id' UNION ALL
    SELECT 'table_name' UNION ALL
    SELECT 'column_name' UNION ALL
    SELECT 'can_read' UNION ALL
    SELECT 'can_create' UNION ALL
    SELECT 'can_edit' UNION ALL
    SELECT 'can_delete' UNION ALL
    SELECT 'can_approve' UNION ALL
    SELECT 'can_export' UNION ALL
    SELECT 'extra_permissions' UNION ALL
    SELECT 'granted_by' UNION ALL
    SELECT 'created_at' UNION ALL
    SELECT 'updated_at'
) AS rp_columns;

-- ====================================================================
-- 7. ADD MODULE META ENTRIES (if missing)
-- ====================================================================
INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'employees', 'Employees', 'Users', 'HR', 'Manage employee information', TRUE, 1, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'employees');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'roles', 'Roles', 'Shield', 'Security', 'Manage user roles', TRUE, 2, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'roles');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'role_permissions', 'Permissions', 'Key', 'Security', 'Manage role permissions', TRUE, 3, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'role_permissions');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'departments', 'Departments', 'Building', 'HR', 'Manage departments', TRUE, 4, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'departments');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'designations', 'Designations', 'Briefcase', 'HR', 'Manage designations', TRUE, 5, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'designations');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'locations', 'Locations', 'MapPin', 'Settings', 'Manage locations', TRUE, 6, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'locations');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at)
SELECT UUID(), 'leave_requests', 'Leave Requests', 'Calendar', 'HR', 'Manage leave requests', TRUE, 8, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'leave_requests');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'payroll_runs', 'Payroll', 'DollarSign', 'Finance', 'Manage payroll', TRUE, 9, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'payroll_runs');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'expenses', 'Expenses', 'Receipt', 'Finance', 'Manage expenses', TRUE, 10, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'expenses');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'projects', 'Projects', 'Folder', 'Projects', 'Manage projects', TRUE, 11, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'projects');

INSERT INTO module_meta (id, table_name, display_name, icon, category, description, is_active, display_order, created_at, updated_at) 
SELECT UUID(), 'tasks', 'Tasks', 'CheckSquare', 'Projects', 'Manage tasks', TRUE, 12, @current_timestamp, @current_timestamp
WHERE NOT EXISTS (SELECT 1 FROM module_meta WHERE table_name = 'tasks');

-- ====================================================================
-- 8. VERIFICATION QUERY - Check if everything is set
-- ====================================================================
SELECT '=== SUPERADMIN SETUP VERIFICATION ===' AS '';

SELECT 
    'Role Created' AS check_type,
    COUNT(*) AS count,
    CASE WHEN COUNT(*) > 0 THEN '✅ PASS' ELSE '❌ FAIL' END AS status
FROM roles WHERE id = @superadmin_role_id;

SELECT 
    'Role Assigned to EMP-001' AS check_type,
    COUNT(*) AS count,
    CASE WHEN COUNT(*) > 0 THEN '✅ PASS' ELSE '❌ FAIL' END AS status
FROM employee_roles WHERE role_id = @superadmin_role_id AND employee_id = @employee_id;

SELECT 
    'Table Permissions' AS check_type,
    COUNT(*) AS count,
    CASE WHEN COUNT(*) > 0 THEN '✅ PASS' ELSE '❌ FAIL' END AS status
FROM role_permissions WHERE role_id = @superadmin_role_id AND column_name IS NULL;

SELECT 
    'Column Permissions' AS check_type,
    COUNT(*) AS count,
    CASE WHEN COUNT(*) > 0 THEN '✅ PASS' ELSE '❌ FAIL' END AS status
FROM role_permissions WHERE role_id = @superadmin_role_id AND column_name IS NOT NULL;

SELECT 
    'Module Meta' AS check_type,
    COUNT(*) AS count,
    CASE WHEN COUNT(*) > 0 THEN '✅ PASS' ELSE '❌ FAIL' END AS status
FROM module_meta WHERE is_active = TRUE;

-- Show final summary
SELECT 
    @employee_id AS employee_uuid,
    (SELECT employee_id FROM employees WHERE id = @employee_id) AS employee_code,
    (SELECT CONCAT(first_name, ' ', last_name) FROM employees WHERE id = @employee_id) AS employee_name,
    @superadmin_role_id AS role_id,
    'Super Administrator' AS role_name,
    '✅ Superadmin permissions have been granted successfully' AS message;