-- Supplemental Schema for Singapore HRM System
-- Adds tables/columns that app/controllers and app/models reference but that
-- were missing from database.sql and update_schema.sql.
-- Safe to re-run: uses IF NOT EXISTS / conditional column adds.

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

-- MySQL 8 has no `ADD COLUMN IF NOT EXISTS`, so use a helper procedure to
-- keep this script idempotent (safe to re-run against an already-migrated DB).
DROP PROCEDURE IF EXISTS `_add_col_if_missing`;
DELIMITER $$
CREATE PROCEDURE `_add_col_if_missing`(IN tbl VARCHAR(64), IN col VARCHAR(64), IN colDef TEXT)
BEGIN
  IF NOT EXISTS (
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND COLUMN_NAME = col
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', tbl, '` ADD COLUMN `', col, '` ', colDef);
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

-- ---------------------------------------------------------------------
-- 1. Employees: extra columns used by Employee model / EmployeesController
-- ---------------------------------------------------------------------
CALL _add_col_if_missing('employees', 'basic_salary', "decimal(10,2) NOT NULL DEFAULT 0.00 AFTER `designation`");
CALL _add_col_if_missing('employees', 'worksite_id', "int(11) DEFAULT NULL AFTER `work_location`");
CALL _add_col_if_missing('employees', 'is_pr_citizen', "tinyint(1) DEFAULT 1 AFTER `cpf_eligible`");
CALL _add_col_if_missing('employees', 'nric_doc', "varchar(255) DEFAULT NULL AFTER `profile_pic`");
CALL _add_col_if_missing('employees', 'contract_doc', "varchar(255) DEFAULT NULL AFTER `nric_doc`");

-- ---------------------------------------------------------------------
-- 2. Worksites (geofencing) - referenced by Worksite model / AttendanceController
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `worksites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `latitude` decimal(10,7) NOT NULL,
  `longitude` decimal(10,7) NOT NULL,
  `radius_meters` int(11) DEFAULT 200,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- FK from employees.worksite_id -> worksites.id (added after table exists)
SET @fk_exists = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND CONSTRAINT_NAME = 'fk_employees_worksite');
SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE `employees` ADD CONSTRAINT `fk_employees_worksite` FOREIGN KEY (`worksite_id`) REFERENCES `worksites`(`id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------
-- 3. Attendance: extra columns used by Attendance model / AttendanceController
-- ---------------------------------------------------------------------
CALL _add_col_if_missing('attendance', 'device_info', "varchar(255) DEFAULT NULL AFTER `clock_out_location`");
CALL _add_col_if_missing('attendance', 'expected_start_time', "time DEFAULT NULL AFTER `device_info`");
CALL _add_col_if_missing('attendance', 'expected_end_time', "time DEFAULT NULL AFTER `expected_start_time`");

-- ---------------------------------------------------------------------
-- 4. Employee Schedules (rotational / recurring shift assignment)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `employee_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `shift_id` int(11) NOT NULL,
  `schedule_date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `emp_date` (`employee_id`, `schedule_date`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `shifts`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 5. Leave Requests: multi-level approval columns used by Leave model
-- ---------------------------------------------------------------------
CALL _add_col_if_missing('leave_requests', 'duration', "varchar(30) DEFAULT 'Full Day' AFTER `days_count`");
CALL _add_col_if_missing('leave_requests', 'manager_status', "enum('Pending','Approved','Rejected') DEFAULT 'Pending' AFTER `status`");
CALL _add_col_if_missing('leave_requests', 'manager_id', "int(11) DEFAULT NULL AFTER `manager_status`");
CALL _add_col_if_missing('leave_requests', 'manager_remarks', "text AFTER `manager_id`");
CALL _add_col_if_missing('leave_requests', 'hr_status', "enum('Pending','Approved','Rejected') DEFAULT 'Pending' AFTER `manager_remarks`");
CALL _add_col_if_missing('leave_requests', 'hr_id', "int(11) DEFAULT NULL AFTER `hr_status`");
CALL _add_col_if_missing('leave_requests', 'hr_remarks', "text AFTER `hr_id`");

-- ---------------------------------------------------------------------
-- 6. Leave Policy (entitlement per employment type) - PoliciesController / Leave model
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `leave_policy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employment_type` varchar(20) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `days_per_year` decimal(4,1) NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type_leave` (`employment_type`, `leave_type_id`),
  FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Starter defaults so leave balances can initialize out of the box (mirrors
-- leave_types.days_entitled for every employment type). Review and adjust
-- via Settings -> Leave Policies to match the client's actual policy.
INSERT IGNORE INTO `leave_policy` (`employment_type`, `leave_type_id`, `days_per_year`)
SELECT et.employment_type, lt.id, lt.days_entitled
FROM leave_types lt
CROSS JOIN (SELECT 'Full-time' AS employment_type UNION SELECT 'Part-time' UNION SELECT 'Contract') et;

-- ---------------------------------------------------------------------
-- 7. Leave Balances (per employee, per year)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `leave_balances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `year` int(4) NOT NULL,
  `entitled` decimal(4,1) NOT NULL DEFAULT 0.0,
  `pending` decimal(4,1) NOT NULL DEFAULT 0.0,
  `utilized` decimal(4,1) NOT NULL DEFAULT 0.0,
  `remaining` decimal(4,1) NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `emp_type_year` (`employee_id`, `leave_type_id`, `year`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 8. Leave Encashments
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `leave_encashments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `days` decimal(4,1) NOT NULL,
  `year` int(4) NOT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `approved_by` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 9. System Settings (key/value store) - grace period, CPF OW ceiling
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO `system_settings` (`setting_key`, `setting_value`) VALUES
('late_grace_period', '5'),
('cpf_ow_ceiling', '6800'),
('company_name', 'Your Company Name'),
('company_address', 'Singapore');

-- ---------------------------------------------------------------------
-- 10. CPF Rates (age-banded, per PoliciesController::cpf / Payroll::calculateCPF)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cpf_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age_min` int(3) NOT NULL,
  `age_max` int(3) NOT NULL,
  `total_rate` decimal(5,4) NOT NULL,
  `employee_rate` decimal(5,4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO `cpf_rates` (`id`, `age_min`, `age_max`, `total_rate`, `employee_rate`) VALUES
(1, 0, 55, 0.37, 0.20),
(2, 56, 60, 0.325, 0.16),
(3, 61, 65, 0.235, 0.105),
(4, 66, 70, 0.165, 0.075),
(5, 71, 120, 0.125, 0.05);

-- ---------------------------------------------------------------------
-- 11. OT Rates and Public Holidays - Overtime model
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ot_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) NOT NULL,
  `rate_multiplier` decimal(3,2) NOT NULL DEFAULT 1.50,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO `ot_rates` (`type`, `rate_multiplier`) VALUES
('Normal', 1.5),
('Rest Day', 2.0),
('Public Holiday', 3.0);

CREATE TABLE IF NOT EXISTS `public_holidays` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `holiday_name` varchar(150) NOT NULL,
  `holiday_date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `holiday_date` (`holiday_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `ot_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `attendance_id` int(11) DEFAULT NULL,
  `date` date NOT NULL,
  `ot_type` varchar(30) NOT NULL,
  `ot_hours` decimal(5,2) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `approved_by` int(11) DEFAULT NULL,
  `remarks` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`attendance_id`) REFERENCES `attendance`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 12. Salary Components, Employee assignments, and Loans
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `salary_components` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('Earning','Deduction') NOT NULL,
  `is_fixed` tinyint(1) DEFAULT 0,
  `category` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `employee_salary_components` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `component_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `month_year` varchar(7) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`component_id`) REFERENCES `salary_components`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `loans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `loan_amount` decimal(10,2) NOT NULL,
  `installment_amount` decimal(10,2) NOT NULL,
  `remaining_balance` decimal(10,2) NOT NULL,
  `status` enum('Active','Paid') DEFAULT 'Active',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 13. Sales Commission tracking tables (HRM-side only)
-- NOTE: Salescommission model also reads from `thiru_services`,
-- `thiru_bill_outdoor_services`, and `thiru_bill_other_services`, which are
-- tables belonging to an external temple billing system, not created here.
-- See chat for follow-up on which existing database to point at for that.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_commissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_id` int(11) DEFAULT NULL,
  `tray_id` int(11) DEFAULT NULL,
  `employee_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `month_year` varchar(7) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `bill_id` (`bill_id`),
  KEY `tray_id` (`tray_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `sales_commission_processed` (
  `bill_id` int(11) NOT NULL,
  `processed_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `thachanai_tray_processed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `month_year` varchar(7) NOT NULL,
  `processed_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP PROCEDURE IF EXISTS `_add_col_if_missing`;

COMMIT;