-- Enterprise HRM ERP System Database Schema
-- Multi-site, Multi-HR, Role-based System
-- Compatible with XAMPP (Windows) and Linux cPanel

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- ============================================
-- CORE CONFIGURATION TABLES
-- ============================================

-- Sites/Companies Table
CREATE TABLE IF NOT EXISTS `sites` (
  `site_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_code` varchar(50) NOT NULL,
  `site_name` varchar(255) NOT NULL,
  `site_address` text,
  `site_phone` varchar(50),
  `site_email` varchar(255),
  `site_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`site_id`),
  UNIQUE KEY `site_code` (`site_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Roles Table
CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_code` varchar(50) NOT NULL,
  `role_name` varchar(100) NOT NULL,
  `role_description` text,
  `is_system_role` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions Table
CREATE TABLE IF NOT EXISTS `permissions` (
  `permission_id` int(11) NOT NULL AUTO_INCREMENT,
  `permission_code` varchar(100) NOT NULL,
  `permission_name` varchar(255) NOT NULL,
  `permission_module` varchar(100) NOT NULL,
  `permission_description` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`permission_id`),
  UNIQUE KEY `permission_code` (`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Role Permissions Mapping
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_permission_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_permission_id`),
  UNIQUE KEY `unique_role_permission` (`role_id`,`permission_id`),
  KEY `fk_role_permission_role` (`role_id`),
  KEY `fk_role_permission_permission` (`permission_id`),
  CONSTRAINT `fk_role_permission_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permission_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`permission_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER MANAGEMENT
-- ============================================

-- Users Table (HR Staff)
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `phone` varchar(50),
  `role_id` int(11) NOT NULL,
  `user_status` enum('active','inactive','locked') DEFAULT 'active',
  `last_login` datetime,
  `failed_login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `fk_user_site` (`site_id`),
  KEY `fk_user_role` (`role_id`),
  CONSTRAINT `fk_user_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User Sessions Table
CREATE TABLE IF NOT EXISTS `user_sessions` (
  `session_id` varchar(128) NOT NULL,
  `user_id` int(11) NOT NULL,
  `ip_address` varchar(45),
  `user_agent` text,
  `last_activity` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`session_id`),
  KEY `fk_session_user` (`user_id`),
  CONSTRAINT `fk_session_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- EMPLOYEE MANAGEMENT
-- ============================================

-- Departments Table
CREATE TABLE IF NOT EXISTS `departments` (
  `department_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `department_code` varchar(50) NOT NULL,
  `department_name` varchar(255) NOT NULL,
  `department_head_id` int(11),
  `department_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `unique_site_dept_code` (`site_id`,`department_code`),
  KEY `fk_dept_site` (`site_id`),
  CONSTRAINT `fk_dept_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Designations Table
CREATE TABLE IF NOT EXISTS `designations` (
  `designation_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `designation_code` varchar(50) NOT NULL,
  `designation_name` varchar(255) NOT NULL,
  `designation_level` int(11) DEFAULT 0,
  `designation_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`designation_id`),
  UNIQUE KEY `unique_site_desig_code` (`site_id`,`designation_code`),
  KEY `fk_desig_site` (`site_id`),
  CONSTRAINT `fk_desig_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employees Table
CREATE TABLE IF NOT EXISTS `employees` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_code` varchar(50) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `middle_name` varchar(100),
  `email` varchar(255),
  `phone` varchar(50),
  `date_of_birth` date,
  `gender` enum('male','female','other'),
  `marital_status` enum('single','married','divorced','widowed'),
  `national_id` varchar(50),
  `address_line1` varchar(255),
  `address_line2` varchar(255),
  `city` varchar(100),
  `state` varchar(100),
  `postal_code` varchar(20),
  `country` varchar(100),
  `department_id` int(11),
  `designation_id` int(11),
  `date_of_joining` date,
  `employment_type` enum('full_time','part_time','contract','temporary') DEFAULT 'full_time',
  `employment_status` enum('active','inactive','terminated','resigned') DEFAULT 'active',
  `salary` decimal(15,2) DEFAULT 0.00,
  `bank_name` varchar(255),
  `bank_account_number` varchar(100),
  `bank_ifsc` varchar(50),
  `emergency_contact_name` varchar(255),
  `emergency_contact_phone` varchar(50),
  `emergency_contact_relation` varchar(100),
  `profile_picture` varchar(255),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `unique_site_emp_code` (`site_id`,`employee_code`),
  KEY `fk_emp_site` (`site_id`),
  KEY `fk_emp_dept` (`department_id`),
  KEY `fk_emp_desig` (`designation_id`),
  KEY `fk_emp_created_by` (`created_by`),
  KEY `fk_emp_updated_by` (`updated_by`),
  CONSTRAINT `fk_emp_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emp_desig` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emp_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emp_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ATTENDANCE MANAGEMENT
-- ============================================

-- Attendance Records Table
CREATE TABLE IF NOT EXISTS `attendance` (
  `attendance_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `attendance_date` date NOT NULL,
  `check_in_time` datetime,
  `check_out_time` datetime,
  `total_hours` decimal(5,2) DEFAULT 0.00,
  `overtime_hours` decimal(5,2) DEFAULT 0.00,
  `attendance_status` enum('present','absent','half_day','late','on_leave') DEFAULT 'absent',
  `remarks` text,
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`attendance_id`),
  UNIQUE KEY `unique_emp_date` (`employee_id`,`attendance_date`),
  KEY `fk_att_site` (`site_id`),
  KEY `fk_att_emp` (`employee_id`),
  KEY `fk_att_created_by` (`created_by`),
  KEY `fk_att_updated_by` (`updated_by`),
  CONSTRAINT `fk_att_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_att_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_att_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_att_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- LEAVE MANAGEMENT
-- ============================================

-- Leave Types Table
CREATE TABLE IF NOT EXISTS `leave_types` (
  `leave_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `leave_type_code` varchar(50) NOT NULL,
  `leave_type_name` varchar(255) NOT NULL,
  `max_days_per_year` int(11) DEFAULT 0,
  `carry_forward` tinyint(1) DEFAULT 0,
  `max_carry_forward_days` int(11) DEFAULT 0,
  `requires_approval` tinyint(1) DEFAULT 1,
  `leave_type_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`leave_type_id`),
  UNIQUE KEY `unique_site_leave_code` (`site_id`,`leave_type_code`),
  KEY `fk_leave_type_site` (`site_id`),
  CONSTRAINT `fk_leave_type_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Leave Applications Table
CREATE TABLE IF NOT EXISTS `leave_applications` (
  `leave_application_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total_days` decimal(5,2) NOT NULL,
  `reason` text,
  `application_status` enum('pending','approved','rejected','cancelled') DEFAULT 'pending',
  `approved_by` int(11),
  `approved_at` datetime,
  `rejection_reason` text,
  `applied_by` int(11),
  `applied_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`leave_application_id`),
  KEY `fk_leave_app_site` (`site_id`),
  KEY `fk_leave_app_emp` (`employee_id`),
  KEY `fk_leave_app_type` (`leave_type_id`),
  KEY `fk_leave_app_approved_by` (`approved_by`),
  KEY `fk_leave_app_applied_by` (`applied_by`),
  CONSTRAINT `fk_leave_app_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_leave_app_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_leave_app_type` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`leave_type_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_leave_app_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leave_app_applied_by` FOREIGN KEY (`applied_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee Leave Balances Table
CREATE TABLE IF NOT EXISTS `employee_leave_balances` (
  `balance_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `allocated_days` decimal(5,2) DEFAULT 0.00,
  `used_days` decimal(5,2) DEFAULT 0.00,
  `carry_forward_days` decimal(5,2) DEFAULT 0.00,
  `balance_days` decimal(5,2) DEFAULT 0.00,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`balance_id`),
  UNIQUE KEY `unique_emp_leave_year` (`employee_id`,`leave_type_id`,`year`),
  KEY `fk_balance_site` (`site_id`),
  KEY `fk_balance_emp` (`employee_id`),
  KEY `fk_balance_type` (`leave_type_id`),
  CONSTRAINT `fk_balance_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_balance_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_balance_type` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`leave_type_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- PAYROLL MANAGEMENT
-- ============================================

-- Salary Components Table
CREATE TABLE IF NOT EXISTS `salary_components` (
  `component_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `component_code` varchar(50) NOT NULL,
  `component_name` varchar(255) NOT NULL,
  `component_type` enum('earning','deduction') NOT NULL,
  `is_taxable` tinyint(1) DEFAULT 0,
  `calculation_type` enum('fixed','percentage','formula') DEFAULT 'fixed',
  `calculation_value` decimal(10,2) DEFAULT 0.00,
  `component_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`component_id`),
  UNIQUE KEY `unique_site_comp_code` (`site_id`,`component_code`),
  KEY `fk_comp_site` (`site_id`),
  CONSTRAINT `fk_comp_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee Salary Structure Table
CREATE TABLE IF NOT EXISTS `employee_salary_structure` (
  `structure_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `component_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date,
  `structure_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`structure_id`),
  KEY `fk_struct_site` (`site_id`),
  KEY `fk_struct_emp` (`employee_id`),
  KEY `fk_struct_comp` (`component_id`),
  CONSTRAINT `fk_struct_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_struct_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_struct_comp` FOREIGN KEY (`component_id`) REFERENCES `salary_components` (`component_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payroll Periods Table
CREATE TABLE IF NOT EXISTS `payroll_periods` (
  `period_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `period_code` varchar(50) NOT NULL,
  `period_name` varchar(255) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `pay_date` date NOT NULL,
  `period_status` enum('draft','processing','locked','paid') DEFAULT 'draft',
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`period_id`),
  UNIQUE KEY `unique_site_period_code` (`site_id`,`period_code`),
  KEY `fk_period_site` (`site_id`),
  KEY `fk_period_created_by` (`created_by`),
  KEY `fk_period_updated_by` (`updated_by`),
  CONSTRAINT `fk_period_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_period_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_period_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payroll Records Table
CREATE TABLE IF NOT EXISTS `payroll_records` (
  `payroll_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `period_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `working_days` decimal(5,2) DEFAULT 0.00,
  `present_days` decimal(5,2) DEFAULT 0.00,
  `absent_days` decimal(5,2) DEFAULT 0.00,
  `overtime_hours` decimal(5,2) DEFAULT 0.00,
  `gross_salary` decimal(15,2) DEFAULT 0.00,
  `total_earnings` decimal(15,2) DEFAULT 0.00,
  `total_deductions` decimal(15,2) DEFAULT 0.00,
  `taxable_income` decimal(15,2) DEFAULT 0.00,
  `tax_amount` decimal(15,2) DEFAULT 0.00,
  `net_salary` decimal(15,2) DEFAULT 0.00,
  `payroll_status` enum('draft','calculated','approved','paid','locked') DEFAULT 'draft',
  `calculated_at` datetime,
  `approved_by` int(11),
  `approved_at` datetime,
  `paid_at` datetime,
  `remarks` text,
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payroll_id`),
  UNIQUE KEY `unique_period_emp` (`period_id`,`employee_id`),
  KEY `fk_payroll_site` (`site_id`),
  KEY `fk_payroll_period` (`period_id`),
  KEY `fk_payroll_emp` (`employee_id`),
  KEY `fk_payroll_approved_by` (`approved_by`),
  KEY `fk_payroll_created_by` (`created_by`),
  KEY `fk_payroll_updated_by` (`updated_by`),
  CONSTRAINT `fk_payroll_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_payroll_period` FOREIGN KEY (`period_id`) REFERENCES `payroll_periods` (`period_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_payroll_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payroll_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payroll_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payroll_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payroll Details Table (Breakdown of earnings and deductions)
CREATE TABLE IF NOT EXISTS `payroll_details` (
  `detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `payroll_id` int(11) NOT NULL,
  `component_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`detail_id`),
  KEY `fk_detail_payroll` (`payroll_id`),
  KEY `fk_detail_comp` (`component_id`),
  CONSTRAINT `fk_detail_payroll` FOREIGN KEY (`payroll_id`) REFERENCES `payroll_records` (`payroll_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_detail_comp` FOREIGN KEY (`component_id`) REFERENCES `salary_components` (`component_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SYSTEM CONFIGURATION
-- ============================================

-- System Settings Table (No hard-coded values)
CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  `setting_type` enum('string','number','boolean','json') DEFAULT 'string',
  `setting_category` varchar(50),
  `setting_description` text,
  `is_editable` tinyint(1) DEFAULT 1,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `setting_key` (`setting_key`),
  KEY `fk_setting_updated_by` (`updated_by`),
  CONSTRAINT `fk_setting_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Log Table
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11),
  `user_id` int(11),
  `action` varchar(100) NOT NULL,
  `table_name` varchar(100),
  `record_id` int(11),
  `old_values` text,
  `new_values` text,
  `ip_address` varchar(45),
  `user_agent` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `fk_audit_site` (`site_id`),
  KEY `fk_audit_user` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_table_record` (`table_name`,`record_id`),
  CONSTRAINT `fk_audit_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INITIAL DATA
-- ============================================

-- Insert default site
INSERT INTO `sites` (`site_id`, `site_code`, `site_name`, `site_status`) VALUES
(1, 'MAIN', 'Main Office', 'active');

-- Insert default roles
INSERT INTO `roles` (`role_id`, `role_code`, `role_name`, `role_description`, `is_system_role`) VALUES
(1, 'SUPER_ADMIN', 'Super Administrator', 'Full system access', 1),
(2, 'HR_ADMIN', 'HR Administrator', 'HR management access', 1),
(3, 'HR_MANAGER', 'HR Manager', 'HR operations management', 1),
(4, 'HR_OFFICER', 'HR Officer', 'HR daily operations', 1),
(5, 'MANAGER', 'Manager', 'Department manager', 0),
(6, 'EMPLOYEE', 'Employee', 'Regular employee', 0);

-- Insert default permissions
INSERT INTO `permissions` (`permission_id`, `permission_code`, `permission_name`, `permission_module`) VALUES
(1, 'EMPLOYEE_VIEW', 'View Employees', 'employee'),
(2, 'EMPLOYEE_CREATE', 'Create Employee', 'employee'),
(3, 'EMPLOYEE_EDIT', 'Edit Employee', 'employee'),
(4, 'EMPLOYEE_DELETE', 'Delete Employee', 'employee'),
(5, 'ATTENDANCE_VIEW', 'View Attendance', 'attendance'),
(6, 'ATTENDANCE_CREATE', 'Create Attendance', 'attendance'),
(7, 'ATTENDANCE_EDIT', 'Edit Attendance', 'attendance'),
(8, 'ATTENDANCE_DELETE', 'Delete Attendance', 'attendance'),
(9, 'LEAVE_VIEW', 'View Leaves', 'leave'),
(10, 'LEAVE_CREATE', 'Create Leave', 'leave'),
(11, 'LEAVE_APPROVE', 'Approve Leave', 'leave'),
(12, 'LEAVE_REJECT', 'Reject Leave', 'leave'),
(13, 'PAYROLL_VIEW', 'View Payroll', 'payroll'),
(14, 'PAYROLL_CREATE', 'Create Payroll', 'payroll'),
(15, 'PAYROLL_CALCULATE', 'Calculate Payroll', 'payroll'),
(16, 'PAYROLL_APPROVE', 'Approve Payroll', 'payroll'),
(17, 'PAYROLL_LOCK', 'Lock Payroll', 'payroll'),
(18, 'REPORTS_VIEW', 'View Reports', 'reports'),
(19, 'SETTINGS_VIEW', 'View Settings', 'settings'),
(20, 'SETTINGS_EDIT', 'Edit Settings', 'settings'),
(21, 'USER_MANAGE', 'Manage Users', 'user'),
(22, 'SITE_MANAGE', 'Manage Sites', 'site');

-- Assign permissions to Super Admin (all permissions)
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, `permission_id` FROM `permissions`;

-- Assign permissions to HR Admin
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, `permission_id` FROM `permissions` WHERE `permission_code` NOT IN ('SITE_MANAGE');

-- Assign permissions to HR Manager
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 3, `permission_id` FROM `permissions` WHERE `permission_code` IN 
('EMPLOYEE_VIEW', 'EMPLOYEE_CREATE', 'EMPLOYEE_EDIT', 
 'ATTENDANCE_VIEW', 'ATTENDANCE_CREATE', 'ATTENDANCE_EDIT',
 'LEAVE_VIEW', 'LEAVE_CREATE', 'LEAVE_APPROVE', 'LEAVE_REJECT',
 'PAYROLL_VIEW', 'PAYROLL_CREATE', 'PAYROLL_CALCULATE', 'PAYROLL_APPROVE',
 'REPORTS_VIEW');

-- Insert default system settings
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `setting_category`, `setting_description`) VALUES
('SYSTEM_NAME', 'Enterprise HRM ERP', 'string', 'general', 'System Name'),
('SYSTEM_TIMEZONE', 'UTC', 'string', 'general', 'System Timezone'),
('SESSION_TIMEOUT', '3600', 'number', 'security', 'Session timeout in seconds'),
('MAX_LOGIN_ATTEMPTS', '5', 'number', 'security', 'Maximum failed login attempts'),
('LOCKOUT_DURATION', '1800', 'number', 'security', 'Account lockout duration in seconds'),
('PAYROLL_TAX_RATE', '0.00', 'number', 'payroll', 'Default tax rate percentage'),
('WORKING_HOURS_PER_DAY', '8', 'number', 'attendance', 'Standard working hours per day'),
('OVERTIME_RATE', '1.5', 'number', 'payroll', 'Overtime multiplier rate'),
('CURRENCY_SYMBOL', '$', 'string', 'payroll', 'Currency symbol'),
('DATE_FORMAT', 'Y-m-d', 'string', 'general', 'Date format'),
('DATETIME_FORMAT', 'Y-m-d H:i:s', 'string', 'general', 'DateTime format');

COMMIT;

