-- =====================================================
-- LIMS User Management & RBAC System
-- Seed Data Script
-- =====================================================
-- This script populates default roles, permissions,
-- and creates a default super admin user
-- =====================================================

-- =====================================================
-- STEP 1: Insert Default Roles
-- =====================================================
INSERT INTO `roles` (`role_name`, `role_code`, `description`, `is_active`) VALUES
('Sample Receiving', 'sample_receiving', 'Can access Sample Line Listing and Sample Registration pages', TRUE),
('SRU Incharge', 'sru_incharge', 'Can access SRU Incharge page for sample management', TRUE),
('Sample Accessioning', 'sample_accessioning', 'Can access Sample Accessioning page', TRUE),
('Lab Analyst', 'lab_analyst', 'Can access Lab Analyst page for test analysis', TRUE),
('Reviewer', 'reviewer', 'Can access Reviewer page for result review', TRUE),
('Approver', 'approver', 'Can access Approver page for final approval', TRUE),
('Super Admin', 'super_admin', 'Full system access including configuration and user management', TRUE);

-- =====================================================
-- STEP 2: Insert Permissions for All Resources
-- =====================================================

-- Sample Line Listing Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Sample Line Listing', 'sample_line_listing_read', 'sample_line_listing', 'read', 'View sample line listing page'),
('Create Sample Line Listing', 'sample_line_listing_create', 'sample_line_listing', 'create', 'Create new entries in sample line listing'),
('Update Sample Line Listing', 'sample_line_listing_update', 'sample_line_listing', 'update', 'Update sample line listing entries'),
('Delete Sample Line Listing', 'sample_line_listing_delete', 'sample_line_listing', 'delete', 'Delete sample line listing entries');

-- Sample Registration Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Sample Registration', 'sample_registration_read', 'sample_registration', 'read', 'View sample registration page'),
('Create Sample Registration', 'sample_registration_create', 'sample_registration', 'create', 'Register new samples'),
('Update Sample Registration', 'sample_registration_update', 'sample_registration', 'update', 'Update sample registration'),
('Delete Sample Registration', 'sample_registration_delete', 'sample_registration', 'delete', 'Delete sample registration');

-- SRU Incharge Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View SRU Incharge', 'sru_incharge_read', 'sru_incharge', 'read', 'View SRU Incharge page'),
('Create SRU Incharge', 'sru_incharge_create', 'sru_incharge', 'create', 'Create SRU entries'),
('Update SRU Incharge', 'sru_incharge_update', 'sru_incharge', 'update', 'Update SRU entries'),
('Delete SRU Incharge', 'sru_incharge_delete', 'sru_incharge', 'delete', 'Delete SRU entries'),
('Execute SRU Actions', 'sru_incharge_execute', 'sru_incharge', 'execute', 'Execute SRU specific actions');

-- Sample Accessioning Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Sample Accessioning', 'sample_accessioning_read', 'sample_accessioning', 'read', 'View sample accessioning page'),
('Create Sample Accessioning', 'sample_accessioning_create', 'sample_accessioning', 'create', 'Create accessioning entries'),
('Update Sample Accessioning', 'sample_accessioning_update', 'sample_accessioning', 'update', 'Update accessioning entries'),
('Delete Sample Accessioning', 'sample_accessioning_delete', 'sample_accessioning', 'delete', 'Delete accessioning entries'),
('Execute Accessioning Actions', 'sample_accessioning_execute', 'sample_accessioning', 'execute', 'Assign tests to analysts');

-- Lab Analyst Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Lab Analyst', 'lab_analyst_read', 'lab_analyst', 'read', 'View lab analyst page'),
('Create Lab Analyst', 'lab_analyst_create', 'lab_analyst', 'create', 'Create test results'),
('Update Lab Analyst', 'lab_analyst_update', 'lab_analyst', 'update', 'Update test results'),
('Delete Lab Analyst', 'lab_analyst_delete', 'lab_analyst', 'delete', 'Delete test results'),
('Execute Lab Analyst Actions', 'lab_analyst_execute', 'lab_analyst', 'execute', 'Submit results, send back to accessioning');

-- Reviewer Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Reviewer', 'reviewer_read', 'reviewer', 'read', 'View reviewer page'),
('Create Reviewer', 'reviewer_create', 'reviewer', 'create', 'Create review entries'),
('Update Reviewer', 'reviewer_update', 'reviewer', 'update', 'Update review entries'),
('Delete Reviewer', 'reviewer_delete', 'reviewer', 'delete', 'Delete review entries'),
('Execute Reviewer Actions', 'reviewer_execute', 'reviewer', 'execute', 'Approve or reject results');

-- Approver Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Approver', 'approver_read', 'approver', 'read', 'View approver page'),
('Create Approver', 'approver_create', 'approver', 'create', 'Create approval entries'),
('Update Approver', 'approver_update', 'approver', 'update', 'Update approval entries'),
('Delete Approver', 'approver_delete', 'approver', 'delete', 'Delete approval entries'),
('Execute Approver Actions', 'approver_execute', 'approver', 'execute', 'Final approval of results');

-- Configuration Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Configuration', 'configuration_read', 'configuration', 'read', 'View configuration page'),
('Create Configuration', 'configuration_create', 'configuration', 'create', 'Create configuration entries'),
('Update Configuration', 'configuration_update', 'configuration', 'update', 'Update configuration'),
('Delete Configuration', 'configuration_delete', 'configuration', 'delete', 'Delete configuration entries');

-- User Management Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Users', 'user_management_read', 'user_management', 'read', 'View user management page'),
('Create Users', 'user_management_create', 'user_management', 'create', 'Create new users'),
('Update Users', 'user_management_update', 'user_management', 'update', 'Update user information'),
('Delete Users', 'user_management_delete', 'user_management', 'delete', 'Delete users');

-- Role Management Permissions
INSERT INTO `permissions` (`permission_name`, `permission_code`, `resource`, `action`, `description`) VALUES
('View Roles', 'role_management_read', 'role_management', 'read', 'View role management page'),
('Create Roles', 'role_management_create', 'role_management', 'create', 'Create new roles'),
('Update Roles', 'role_management_update', 'role_management', 'update', 'Update role information'),
('Delete Roles', 'role_management_delete', 'role_management', 'delete', 'Delete roles');

-- =====================================================
-- STEP 3: Assign Permissions to Roles
-- =====================================================

-- Sample Receiving Role: Line Listing + Sample Registration (Read + Create + Update)
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'sample_receiving' 
AND p.permission_code IN (
  'sample_line_listing_read', 'sample_line_listing_create', 'sample_line_listing_update',
  'sample_registration_read', 'sample_registration_create', 'sample_registration_update'
);

-- SRU Incharge Role: Full access to SRU Incharge page
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'sru_incharge' 
AND p.resource = 'sru_incharge';

-- Sample Accessioning Role: Full access to Sample Accessioning page
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'sample_accessioning' 
AND p.resource = 'sample_accessioning';

-- Lab Analyst Role: Full access to Lab Analyst page
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'lab_analyst' 
AND p.resource = 'lab_analyst';

-- Reviewer Role: Full access to Reviewer page
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'reviewer' 
AND p.resource = 'reviewer';

-- Approver Role: Full access to Approver page
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'approver' 
AND p.resource = 'approver';

-- Super Admin Role: ALL permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.role_code = 'super_admin';

-- =====================================================
-- STEP 4: Create Default Super Admin User
-- =====================================================
-- Default credentials:
-- Username: admin
-- Password: Admin@123
-- Password hash generated using bcrypt with 10 salt rounds
INSERT INTO `users` (`username`, `email`, `password_hash`, `full_name`, `is_active`) VALUES
('admin', 'admin@lims.local', '$2a$10$XqJz8ZqE5KvJ5K5K5K5K5uO5K5K5K5K5K5K5K5K5K5K5K5K5K5K5K', 'System Administrator', TRUE);

-- Assign Super Admin role to the default admin user
INSERT INTO `user_roles` (`user_id`, `role_id`)
SELECT u.id, r.id FROM users u, roles r
WHERE u.username = 'admin' AND r.role_code = 'super_admin';

-- =====================================================
-- STEP 5: Verification Queries
-- =====================================================
-- Run these to verify data was inserted successfully:

-- SELECT * FROM roles;
-- SELECT * FROM permissions ORDER BY resource, action;
-- SELECT COUNT(*) as total_permissions FROM permissions;
-- SELECT r.role_name, COUNT(rp.permission_id) as permission_count
-- FROM roles r
-- LEFT JOIN role_permissions rp ON r.id = rp.role_id
-- GROUP BY r.id, r.role_name;
-- SELECT * FROM users;
-- SELECT u.username, r.role_name 
-- FROM users u
-- JOIN user_roles ur ON u.id = ur.user_id
-- JOIN roles r ON ur.role_id = r.id;

-- =====================================================
-- IMPORTANT NOTES
-- =====================================================
-- 1. The default admin password hash is a placeholder
--    It will be replaced by the actual hash when running
--    the Node.js seed script
-- 2. After running this script, use the Node.js seed
--    script to properly hash the password
-- =====================================================
