-- ============================================
-- HR MODULE EXTENSION MIGRATION SCRIPT
-- Created: 2026-02-16
-- For: Employee Management, Salary Templates, Shifts, Attendance, Payroll
-- ============================================

-- 1. SALARY TEMPLATES TABLE
CREATE TABLE IF NOT EXISTS `tbl_salary_templates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `template_name` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT,
  `basic_salary` DECIMAL(10,2) DEFAULT 0,
  `hra` DECIMAL(10,2) DEFAULT 0,
  `da` DECIMAL(10,2) DEFAULT 0,
  `medical_allowance` DECIMAL(10,2) DEFAULT 0,
  `conveyance` DECIMAL(10,2) DEFAULT 0,
  `other_allowances` DECIMAL(10,2) DEFAULT 0,
  `pf_deduction` DECIMAL(10,2) DEFAULT 0,
  `insurance_deduction` DECIMAL(10,2) DEFAULT 0,
  `tax_deduction` DECIMAL(10,2) DEFAULT 0,
  `other_deductions` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Salary Template Master';

-- 2. EMPLOYEE SALARY TEMPLATE ASSIGNMENT
CREATE TABLE IF NOT EXISTS `tbl_employee_salary_templates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `salary_template_id` INT NOT NULL,
  `assigned_date` DATE NOT NULL,
  `effective_date` DATE,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `assigned_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_employee_template` (`employee_id`, `salary_template_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`salary_template_id`) REFERENCES `tbl_salary_templates`(id),
  FOREIGN KEY (`assigned_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Salary Template Assignment';

-- 3. SHIFTS MASTER TABLE
CREATE TABLE IF NOT EXISTS `tbl_shifts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `shift_name` VARCHAR(100) NOT NULL UNIQUE,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `duration_hours` DECIMAL(3,1),
  `break_minutes` INT DEFAULT 0,
  `shift_allowance` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Shift Timings Master';

-- Sample Shifts
INSERT INTO `tbl_shifts` (`shift_name`, `start_time`, `end_time`, `duration_hours`, `break_minutes`, `shift_allowance`, `status`) VALUES
('Morning Shift', '06:00:00', '14:00:00', 8.0, 30, 0, 'active'),
('Evening Shift', '14:00:00', '22:00:00', 8.0, 30, 100, 'active'),
('Night Shift', '22:00:00', '06:00:00', 8.0, 30, 200, 'active'),
('Flexible Shift', '09:00:00', '17:00:00', 8.0, 60, 0, 'active');

-- 4. EMPLOYEE SHIFT ASSIGNMENT
CREATE TABLE IF NOT EXISTS `tbl_employee_shifts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `shift_id` INT NOT NULL,
  `assigned_date` DATE NOT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `assigned_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp_shift` (`employee_id`, `shift_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `tbl_shifts`(id),
  FOREIGN KEY (`assigned_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Shift Assignment';

-- 5. DAILY ATTENDANCE RECORDS
CREATE TABLE IF NOT EXISTS `tbl_attendance` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `attendance_date` DATE NOT NULL,
  `status` ENUM('present','absent','leave','half_day','weekend','holiday') DEFAULT 'present',
  `shift_id` INT,
  `check_in_time` TIME,
  `check_out_time` TIME,
  `notes` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_attendance` (`employee_id`, `attendance_date`),
  KEY `idx_emp_date` (`employee_id`, `attendance_date`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `tbl_shifts`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Attendance Records';

-- 6. MONTHLY ATTENDANCE SUMMARY
CREATE TABLE IF NOT EXISTS `tbl_attendance_summary` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `month_year` DATE NOT NULL,
  `total_days` INT DEFAULT 0,
  `present_days` INT DEFAULT 0,
  `absent_days` INT DEFAULT 0,
  `leave_days` INT DEFAULT 0,
  `half_days` INT DEFAULT 0,
  `weekend_days` INT DEFAULT 0,
  `holiday_days` INT DEFAULT 0,
  `working_days` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp_month` (`employee_id`, `month_year`),
  KEY `idx_employee` (`employee_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Monthly Attendance Summary';

-- 7. PAYROLL RECORDS
CREATE TABLE IF NOT EXISTS `tbl_payroll` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `salary_month` DATE NOT NULL,
  `salary_template_id` INT,
  `working_days` INT DEFAULT 0,
  `attendance_days` INT DEFAULT 0,
  `leave_days` INT DEFAULT 0,
  `half_days` INT DEFAULT 0,
  `absence_days` INT DEFAULT 0,
  `per_day_salary` DECIMAL(10,2) DEFAULT 0,
  `basic_salary` DECIMAL(10,2) DEFAULT 0,
  `hra` DECIMAL(10,2) DEFAULT 0,
  `da` DECIMAL(10,2) DEFAULT 0,
  `allowances` DECIMAL(10,2) DEFAULT 0,
  `shift_allowance` DECIMAL(10,2) DEFAULT 0,
  `gross_salary` DECIMAL(10,2) DEFAULT 0,
  `pf_deduction` DECIMAL(10,2) DEFAULT 0,
  `insurance_deduction` DECIMAL(10,2) DEFAULT 0,
  `tax_deduction` DECIMAL(10,2) DEFAULT 0,
  `other_deductions` DECIMAL(10,2) DEFAULT 0,
  `total_deductions` DECIMAL(10,2) DEFAULT 0,
  `net_salary` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('draft','approved','paid') DEFAULT 'draft',
  `notes` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `approved_by` INT,
  `approved_at` TIMESTAMP NULL,
  `paid_date` DATE,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_payroll` (`employee_id`, `salary_month`),
  KEY `idx_month` (`salary_month`),
  KEY `idx_status` (`status`),
  KEY `idx_employee` (`employee_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`salary_template_id`) REFERENCES `tbl_salary_templates`(id),
  FOREIGN KEY (`created_by`) REFERENCES `tbl_employees`(id),
  FOREIGN KEY (`approved_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Monthly Payroll Records';

-- 8. PAYROLL ADJUSTMENTS
CREATE TABLE IF NOT EXISTS `tbl_payroll_adjustments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `payroll_id` INT NOT NULL,
  `adjustment_type` ENUM('bonus','incentive','deduction','correction') DEFAULT 'correction',
  `amount` DECIMAL(10,2) NOT NULL,
  `description` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payroll_id`) REFERENCES `tbl_payroll`(id) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payroll Adjustments and Corrections';

-- ============================================
-- SAMPLE DATA FOR TESTING
-- ============================================

-- Sample Salary Templates
INSERT INTO `tbl_salary_templates` (`template_name`, `basic_salary`, `hra`, `da`, `medical_allowance`, `conveyance`, `pf_deduction`, `insurance_deduction`, `tax_deduction`, `status`) VALUES
('Junior Level', 25000, 5000, 2500, 1000, 1500, 2500, 500, 2000, 'active'),
('Senior Level', 50000, 10000, 5000, 2000, 2500, 5000, 1000, 4000, 'active'),
('Manager Level', 75000, 15000, 7500, 3000, 3500, 7500, 1500, 6000, 'active'),
('Executive Level', 100000, 20000, 10000, 5000, 5000, 10000, 2000, 8000, 'active');

-- ============================================
-- END OF MIGRATION SCRIPT
-- ============================================
