-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 27, 2026 at 08:06 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dualbiz_db`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`dualglob`@`localhost` PROCEDURE `AddColumnIfNotExists` (IN `tableName` VARCHAR(128), IN `columnName` VARCHAR(128), IN `columnDefinition` VARCHAR(255))   BEGIN
    DECLARE columnExists INT;
    
    SELECT COUNT(*) INTO columnExists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = tableName
      AND COLUMN_NAME = columnName;
    
    IF columnExists = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', tableName, '` ADD COLUMN `', columnName, '` ', columnDefinition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('? Added column: ', columnName) AS message;
    ELSE
        SELECT CONCAT('? Column already exists: ', columnName) AS message;
    END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `activity_log`
--

CREATE TABLE `activity_log` (
  `log_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `attendance_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `employee_id` int(11) NOT NULL,
  `attendance_date` date NOT NULL,
  `time_in` time DEFAULT NULL,
  `time_out` time DEFAULT NULL,
  `checkin_lat` decimal(10,8) DEFAULT NULL,
  `checkin_lng` decimal(11,8) DEFAULT NULL,
  `checkout_lat` decimal(10,8) DEFAULT NULL,
  `checkout_lng` decimal(11,8) DEFAULT NULL,
  `checkin_method` enum('gps','manual','hr') DEFAULT 'hr',
  `attendance_type` enum('present','absent','half_day','leave') DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `status` enum('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `approval_status` enum('draft','approved') DEFAULT 'draft' COMMENT 'Attendance approval status',
  `approved_by` int(11) DEFAULT NULL COMMENT 'User ID who approved',
  `approved_at` datetime DEFAULT NULL COMMENT 'When approved',
  `submitted_by` int(11) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `rejection_reason` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `attendance_locations`
--

CREATE TABLE `attendance_locations` (
  `location_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `location_name` varchar(100) NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `radius_metres` int(5) NOT NULL DEFAULT 200,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `company_id` int(11) NOT NULL,
  `company_master_id` int(11) DEFAULT 1,
  `company_name` varchar(255) NOT NULL,
  `registration_no` varchar(100) DEFAULT NULL,
  `gst_reg_no` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `fax` varchar(50) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `company_type` enum('vendor','client','both') DEFAULT 'vendor',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `companies_master`
--

CREATE TABLE `companies_master` (
  `company_master_id` int(11) NOT NULL,
  `company_name` varchar(200) NOT NULL,
  `company_reg_no` varchar(100) DEFAULT NULL,
  `gst_reg_no` varchar(100) DEFAULT NULL,
  `gst_registered` tinyint(1) NOT NULL DEFAULT 1,
  `gst_registration_date` date DEFAULT NULL,
  `gst_deregistration_date` date DEFAULT NULL,
  `address` text DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `website` varchar(200) DEFAULT NULL,
  `logo_filename` varchar(200) DEFAULT NULL,
  `letterhead_color` varchar(20) DEFAULT '#3498db',
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account_no` varchar(100) DEFAULT NULL,
  `bank_account_name` varchar(200) DEFAULT NULL,
  `bank_branch` varchar(100) DEFAULT NULL,
  `bank_swift_code` varchar(50) DEFAULT NULL,
  `paynow_uen` varchar(20) DEFAULT NULL,
  `paynow_name` varchar(100) DEFAULT NULL,
  `default_currency` varchar(10) DEFAULT 'SGD',
  `default_gst_rate` decimal(5,2) DEFAULT 9.00,
  `default_payment_terms` varchar(255) DEFAULT NULL,
  `quotation_prefix` varchar(10) DEFAULT 'Q',
  `invoice_prefix` varchar(10) DEFAULT 'INV',
  `proforma_prefix` varchar(10) DEFAULT 'PI',
  `progress_claim_prefix` varchar(10) DEFAULT 'PC',
  `delivery_note_prefix` varchar(10) DEFAULT 'DN',
  `is_active` tinyint(4) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `delivery_order_prefix` varchar(10) DEFAULT 'DO',
  `cpf_csn_uen` varchar(10) DEFAULT NULL COMMENT 'UEN/NRIC/FIN for CPF CSN (9 or 10 chars, left-justified)',
  `cpf_payment_type` varchar(3) DEFAULT 'PTE' COMMENT 'CPF Payment Type e.g. PTE, AMS, VCT',
  `cpf_sno` varchar(2) DEFAULT '01' COMMENT 'CPF Submission Serial No e.g. 01, 02, 03'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `companies_master`
--

INSERT INTO `companies_master` (`company_master_id`, `company_name`, `company_reg_no`, `gst_reg_no`, `gst_registered`, `gst_registration_date`, `gst_deregistration_date`, `address`, `email`, `phone`, `mobile`, `website`, `logo_filename`, `letterhead_color`, `bank_name`, `bank_account_no`, `bank_account_name`, `bank_branch`, `bank_swift_code`, `paynow_uen`, `paynow_name`, `default_currency`, `default_gst_rate`, `default_payment_terms`, `quotation_prefix`, `invoice_prefix`, `proforma_prefix`, `progress_claim_prefix`, `delivery_note_prefix`, `is_active`, `created_at`, `updated_at`, `delivery_order_prefix`, `cpf_csn_uen`, `cpf_payment_type`, `cpf_sno`) VALUES
(1, 'My Company Pte Ltd', NULL, '', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '#3498db', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'SGD', 9.00, NULL, 'Q', 'INV', 'PI', 'PC', 'DN', 1, '2026-09-27 05:47:39', '2026-09-27 06:05:08', 'DO', NULL, 'PTE', '01');

-- --------------------------------------------------------

--
-- Table structure for table `company_cpf`
--

CREATE TABLE `company_cpf` (
  `cpf_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `cpf_csn_uen` varchar(20) NOT NULL,
  `cpf_payment_type` varchar(3) NOT NULL DEFAULT 'PTE',
  `cpf_sno` varchar(2) NOT NULL DEFAULT '01',
  `sector_label` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `cpf_rates`
--

CREATE TABLE `cpf_rates` (
  `cpf_rate_id` int(11) NOT NULL,
  `residential_status` enum('Singapore Citizen','Singapore PR','NRIC') NOT NULL,
  `age_range` varchar(50) NOT NULL,
  `effective_from` date NOT NULL DEFAULT '2026-01-01',
  `effective_to` date DEFAULT NULL,
  `wage_floor` decimal(10,2) NOT NULL DEFAULT 750.00,
  `wage_ceiling` decimal(10,2) NOT NULL DEFAULT 8000.00,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `notes` text DEFAULT NULL,
  `age_min` int(11) NOT NULL,
  `age_max` int(11) DEFAULT NULL,
  `employer_rate` decimal(5,2) NOT NULL,
  `employee_rate` decimal(5,2) NOT NULL,
  `total_rate` decimal(5,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cpf_rates`
--

INSERT INTO `cpf_rates` (`cpf_rate_id`, `residential_status`, `age_range`, `effective_from`, `effective_to`, `wage_floor`, `wage_ceiling`, `is_active`, `notes`, `age_min`, `age_max`, `employer_rate`, `employee_rate`, `total_rate`, `created_at`, `updated_at`) VALUES
(1, 'Singapore Citizen', '55 and below', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 0, 55, 17.00, 20.00, 37.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(2, 'Singapore Citizen', 'above 55 to 60', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 56, 60, 16.00, 18.00, 34.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(3, 'Singapore Citizen', 'above 60 to 65', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 61, 65, 12.50, 12.50, 25.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(4, 'Singapore Citizen', 'above 65 to 70', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 66, 70, 9.00, 7.50, 16.50, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(5, 'Singapore Citizen', 'above 70', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 71, NULL, 7.50, 5.00, 12.50, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(6, 'Singapore PR', '55 and below', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 0, 55, 17.00, 20.00, 37.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(7, 'Singapore PR', 'above 55 to 60', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 56, 60, 16.00, 18.00, 34.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(8, 'Singapore PR', 'above 60 to 65', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 61, 65, 12.50, 12.50, 25.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(9, 'Singapore PR', 'above 65 to 70', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 66, 70, 9.00, 7.50, 16.50, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(10, 'Singapore PR', 'above 70', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 71, NULL, 7.50, 5.00, 12.50, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(11, 'NRIC', '55 and below', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 0, 55, 17.00, 20.00, 37.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(12, 'NRIC', 'above 55 to 60', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 56, 60, 16.00, 18.00, 34.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(13, 'NRIC', 'above 60 to 65', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 61, 65, 12.50, 12.50, 25.00, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(14, 'NRIC', 'above 65 to 70', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 66, 70, 9.00, 7.50, 16.50, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(15, 'NRIC', 'above 70', '2026-01-01', NULL, 750.00, 8000.00, 1, 'CPF rates effective from 01 Jan 2026. Wage floor: >$750, Ceiling: $8,000', 71, NULL, 7.50, 5.00, 12.50, '2026-02-18 12:40:46', '2026-02-26 06:52:48'),
(16, 'Singapore Citizen', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 0, 55, 17.00, 20.00, 37.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(17, 'Singapore Citizen', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 56, 60, 15.50, 17.00, 32.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(18, 'Singapore Citizen', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 61, 65, 12.00, 11.50, 23.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(19, 'Singapore Citizen', 'above 65 to 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 66, 70, 9.00, 7.50, 16.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(20, 'Singapore Citizen', 'above 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 71, NULL, 7.50, 5.00, 12.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(21, 'Singapore PR', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SPR 3rd yr+. 1 Jan 2025', 0, 55, 17.00, 20.00, 37.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(22, 'Singapore PR', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SPR 3rd yr+. 1 Jan 2025', 56, 60, 15.50, 17.00, 32.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(23, 'Singapore PR', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SPR 3rd yr+. 1 Jan 2025', 61, 65, 12.00, 11.50, 23.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(24, 'Singapore PR', 'above 65 to 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SPR 3rd yr+. 1 Jan 2025', 66, 70, 9.00, 7.50, 16.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(25, 'Singapore PR', 'above 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SPR 3rd yr+. 1 Jan 2025', 71, NULL, 7.50, 5.00, 12.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(26, 'NRIC', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 0, 55, 17.00, 20.00, 37.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(27, 'NRIC', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 56, 60, 15.50, 17.00, 32.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(28, 'NRIC', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 61, 65, 12.00, 11.50, 23.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(29, 'NRIC', 'above 65 to 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 66, 70, 9.00, 7.50, 16.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(30, 'NRIC', 'above 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 1 - SC/SPR 3rd yr+. 1 Jan 2025', 71, NULL, 7.50, 5.00, 12.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(31, '', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 2 - SPR 1st yr G/G. 1 Jan 2025', 0, 55, 4.00, 5.00, 9.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(32, '', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 2 - SPR 1st yr G/G. 1 Jan 2025', 56, 60, 4.00, 5.00, 9.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(33, '', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 2 - SPR 1st yr G/G. 1 Jan 2025', 61, 65, 3.50, 5.00, 8.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(34, '', 'above 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 2 - SPR 1st yr G/G. 1 Jan 2025', 66, NULL, 3.50, 5.00, 8.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(35, '', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 3 - SPR 2nd yr G/G. 1 Jan 2025', 0, 55, 9.00, 15.00, 24.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(36, '', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 3 - SPR 2nd yr G/G. 1 Jan 2025', 56, 60, 6.00, 12.50, 18.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(37, '', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 3 - SPR 2nd yr G/G. 1 Jan 2025', 61, 65, 3.50, 7.50, 11.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(38, '', 'above 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 3 - SPR 2nd yr G/G. 1 Jan 2025', 66, NULL, 3.50, 5.00, 8.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(39, '', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 4 - SPR 1st yr F/G. 1 Jan 2025', 0, 55, 17.00, 5.00, 22.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(40, '', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 4 - SPR 1st yr F/G. 1 Jan 2025', 56, 60, 15.50, 5.00, 20.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(41, '', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 4 - SPR 1st yr F/G. 1 Jan 2025', 61, 65, 12.00, 5.00, 17.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(42, '', 'above 65 to 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 4 - SPR 1st yr F/G. 1 Jan 2025', 66, 70, 9.00, 5.00, 14.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(43, '', 'above 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 4 - SPR 1st yr F/G. 1 Jan 2025', 71, NULL, 7.50, 5.00, 12.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(44, '', '55 and below', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 5 - SPR 2nd yr F/G. 1 Jan 2025', 0, 55, 17.00, 15.00, 32.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(45, '', 'above 55 to 60', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 5 - SPR 2nd yr F/G. 1 Jan 2025', 56, 60, 15.50, 12.50, 28.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(46, '', 'above 60 to 65', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 5 - SPR 2nd yr F/G. 1 Jan 2025', 61, 65, 12.00, 7.50, 19.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(47, '', 'above 65 to 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 5 - SPR 2nd yr F/G. 1 Jan 2025', 66, 70, 9.00, 5.00, 14.00, '2026-07-23 02:03:27', '2026-07-22 22:03:27'),
(48, '', 'above 70', '2025-01-01', '2025-12-31', 750.00, 7400.00, 0, 'Table 5 - SPR 2nd yr F/G. 1 Jan 2025', 71, NULL, 7.50, 5.00, 12.50, '2026-07-23 02:03:27', '2026-07-22 22:03:27');

-- --------------------------------------------------------

--
-- Table structure for table `cpf_rate_details`
--

CREATE TABLE `cpf_rate_details` (
  `rate_id` int(11) NOT NULL,
  `version_id` varchar(10) NOT NULL COMMENT 'Links to cpf_rate_versions',
  `table_code` varchar(5) NOT NULL COMMENT 'T1, T2, T3, T4, or T5',
  `age_min` int(11) NOT NULL COMMENT 'Minimum age for this rate',
  `age_max` int(11) DEFAULT NULL COMMENT 'Maximum age (NULL = no limit)',
  `wage_min` decimal(10,2) NOT NULL COMMENT 'Minimum wage for this rate',
  `wage_max` decimal(10,2) DEFAULT NULL COMMENT 'Maximum wage (NULL = no limit)',
  `total_rate` decimal(5,4) NOT NULL COMMENT 'Total CPF rate (employer + employee)',
  `employee_rate` decimal(5,4) NOT NULL COMMENT 'Employee contribution rate',
  `total_ow_max` decimal(10,2) DEFAULT NULL COMMENT 'Maximum total OW contribution cap',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='CPF rate details for all versions';

--
-- Dumping data for table `cpf_rate_details`
--

INSERT INTO `cpf_rate_details` (`rate_id`, `version_id`, `table_code`, `age_min`, `age_max`, `wage_min`, `wage_max`, `total_rate`, `employee_rate`, `total_ow_max`, `created_at`) VALUES
(93, 'V2026', 'T1', 0, 55, 750.01, NULL, 0.3700, 0.2000, 2960.00, '2026-06-13 14:39:00'),
(94, 'V2026', 'T1', 56, 60, 750.01, NULL, 0.3400, 0.1800, 2720.00, '2026-06-13 14:39:00'),
(95, 'V2026', 'T1', 61, 65, 750.01, NULL, 0.2500, 0.1250, 2000.00, '2026-06-13 14:39:00'),
(96, 'V2026', 'T1', 66, 70, 750.01, NULL, 0.1650, 0.0750, 1320.00, '2026-06-13 14:39:00'),
(97, 'V2026', 'T1', 71, 150, 750.01, NULL, 0.1250, 0.0500, 1000.00, '2026-06-13 14:39:00'),
(98, 'V2026', 'T2', 0, 55, 750.01, NULL, 0.0900, 0.0500, 720.00, '2026-06-13 14:39:00'),
(99, 'V2026', 'T2', 56, 60, 750.01, NULL, 0.0900, 0.0500, 720.00, '2026-06-13 14:39:00'),
(100, 'V2026', 'T2', 61, 65, 750.01, NULL, 0.0850, 0.0500, 680.00, '2026-06-13 14:39:00'),
(101, 'V2026', 'T2', 66, 150, 750.01, NULL, 0.0850, 0.0500, 680.00, '2026-06-13 14:39:00'),
(102, 'V2026', 'T3', 0, 55, 750.01, NULL, 0.2400, 0.1500, 1920.00, '2026-06-13 14:39:00'),
(103, 'V2026', 'T3', 56, 60, 750.01, NULL, 0.1850, 0.1250, 1480.00, '2026-06-13 14:39:00'),
(104, 'V2026', 'T3', 61, 65, 750.01, NULL, 0.1100, 0.0750, 880.00, '2026-06-13 14:39:00'),
(105, 'V2026', 'T3', 66, 150, 750.01, NULL, 0.0850, 0.0500, 680.00, '2026-06-13 14:39:00'),
(106, 'V2026', 'T4', 0, 55, 750.01, NULL, 0.2200, 0.0500, 1760.00, '2026-06-13 14:39:00'),
(107, 'V2026', 'T4', 56, 60, 750.01, NULL, 0.2100, 0.0500, 1680.00, '2026-06-13 14:39:00'),
(108, 'V2026', 'T4', 61, 65, 750.01, NULL, 0.1750, 0.0500, 1400.00, '2026-06-13 14:39:00'),
(109, 'V2026', 'T4', 66, 70, 750.01, NULL, 0.1400, 0.0500, 1120.00, '2026-06-13 14:39:00'),
(110, 'V2026', 'T4', 71, 150, 750.01, NULL, 0.1250, 0.0500, 1000.00, '2026-06-13 14:39:00'),
(111, 'V2026', 'T5', 0, 55, 750.01, NULL, 0.3200, 0.1500, 2560.00, '2026-06-13 14:39:00'),
(112, 'V2026', 'T5', 56, 60, 750.01, NULL, 0.2850, 0.1250, 2280.00, '2026-06-13 14:39:00'),
(113, 'V2026', 'T5', 61, 65, 750.01, NULL, 0.2000, 0.0750, 1600.00, '2026-06-13 14:39:00'),
(114, 'V2026', 'T5', 66, 70, 750.01, NULL, 0.1400, 0.0500, 1120.00, '2026-06-13 14:39:00'),
(115, 'V2026', 'T5', 71, 150, 750.01, NULL, 0.1250, 0.0500, 1000.00, '2026-06-13 14:39:00'),
(116, 'V2025', 'T1', 0, 55, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(117, 'V2025', 'T1', 0, 55, 50.01, 500.00, 0.1700, 0.0000, NULL, '2026-07-23 02:54:37'),
(118, 'V2025', 'T1', 0, 55, 500.01, 750.00, 0.1700, 0.0000, NULL, '2026-07-23 02:54:37'),
(119, 'V2025', 'T1', 0, 55, 750.01, NULL, 0.3700, 0.2000, 2738.00, '2026-07-23 02:54:37'),
(120, 'V2025', 'T1', 56, 60, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(121, 'V2025', 'T1', 56, 60, 50.01, 500.00, 0.1550, 0.0000, NULL, '2026-07-23 02:54:37'),
(122, 'V2025', 'T1', 56, 60, 500.01, 750.00, 0.1550, 0.0000, NULL, '2026-07-23 02:54:37'),
(123, 'V2025', 'T1', 56, 60, 750.01, NULL, 0.3250, 0.1700, 2405.00, '2026-07-23 02:54:37'),
(124, 'V2025', 'T1', 61, 65, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(125, 'V2025', 'T1', 61, 65, 50.01, 500.00, 0.1200, 0.0000, NULL, '2026-07-23 02:54:37'),
(126, 'V2025', 'T1', 61, 65, 500.01, 750.00, 0.1200, 0.0000, NULL, '2026-07-23 02:54:37'),
(127, 'V2025', 'T1', 61, 65, 750.01, NULL, 0.2350, 0.1150, 1739.00, '2026-07-23 02:54:37'),
(128, 'V2025', 'T1', 66, 70, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(129, 'V2025', 'T1', 66, 70, 50.01, 500.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(130, 'V2025', 'T1', 66, 70, 500.01, 750.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(131, 'V2025', 'T1', 66, 70, 750.01, NULL, 0.1650, 0.0750, 1221.00, '2026-07-23 02:54:37'),
(132, 'V2025', 'T1', 71, NULL, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(133, 'V2025', 'T1', 71, NULL, 50.01, 500.00, 0.0750, 0.0000, NULL, '2026-07-23 02:54:37'),
(134, 'V2025', 'T1', 71, NULL, 500.01, 750.00, 0.0750, 0.0000, NULL, '2026-07-23 02:54:37'),
(135, 'V2025', 'T1', 71, NULL, 750.01, NULL, 0.1250, 0.0500, 925.00, '2026-07-23 02:54:37'),
(136, 'V2025', 'T2', 0, 55, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(137, 'V2025', 'T2', 0, 55, 50.01, 500.00, 0.0400, 0.0000, NULL, '2026-07-23 02:54:37'),
(138, 'V2025', 'T2', 0, 55, 500.01, 750.00, 0.0400, 0.0000, NULL, '2026-07-23 02:54:37'),
(139, 'V2025', 'T2', 0, 55, 750.01, NULL, 0.0900, 0.0500, 666.00, '2026-07-23 02:54:37'),
(140, 'V2025', 'T2', 56, 60, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(141, 'V2025', 'T2', 56, 60, 50.01, 500.00, 0.0400, 0.0000, NULL, '2026-07-23 02:54:37'),
(142, 'V2025', 'T2', 56, 60, 500.01, 750.00, 0.0400, 0.0000, NULL, '2026-07-23 02:54:37'),
(143, 'V2025', 'T2', 56, 60, 750.01, NULL, 0.0900, 0.0500, 666.00, '2026-07-23 02:54:37'),
(144, 'V2025', 'T2', 61, 65, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(145, 'V2025', 'T2', 61, 65, 50.01, 500.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(146, 'V2025', 'T2', 61, 65, 500.01, 750.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(147, 'V2025', 'T2', 61, 65, 750.01, NULL, 0.0850, 0.0500, 629.00, '2026-07-23 02:54:37'),
(148, 'V2025', 'T2', 66, NULL, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(149, 'V2025', 'T2', 66, NULL, 50.01, 500.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(150, 'V2025', 'T2', 66, NULL, 500.01, 750.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(151, 'V2025', 'T2', 66, NULL, 750.01, NULL, 0.0850, 0.0500, 629.00, '2026-07-23 02:54:37'),
(152, 'V2025', 'T3', 0, 55, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(153, 'V2025', 'T3', 0, 55, 50.01, 500.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(154, 'V2025', 'T3', 0, 55, 500.01, 750.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(155, 'V2025', 'T3', 0, 55, 750.01, NULL, 0.2400, 0.1500, 1776.00, '2026-07-23 02:54:37'),
(156, 'V2025', 'T3', 56, 60, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(157, 'V2025', 'T3', 56, 60, 50.01, 500.00, 0.0600, 0.0000, NULL, '2026-07-23 02:54:37'),
(158, 'V2025', 'T3', 56, 60, 500.01, 750.00, 0.0600, 0.0000, NULL, '2026-07-23 02:54:37'),
(159, 'V2025', 'T3', 56, 60, 750.01, NULL, 0.1850, 0.1250, 1369.00, '2026-07-23 02:54:37'),
(160, 'V2025', 'T3', 61, 65, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(161, 'V2025', 'T3', 61, 65, 50.01, 500.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(162, 'V2025', 'T3', 61, 65, 500.01, 750.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(163, 'V2025', 'T3', 61, 65, 750.01, NULL, 0.1100, 0.0750, 814.00, '2026-07-23 02:54:37'),
(164, 'V2025', 'T3', 66, NULL, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(165, 'V2025', 'T3', 66, NULL, 50.01, 500.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(166, 'V2025', 'T3', 66, NULL, 500.01, 750.00, 0.0350, 0.0000, NULL, '2026-07-23 02:54:37'),
(167, 'V2025', 'T3', 66, NULL, 750.01, NULL, 0.0850, 0.0500, 629.00, '2026-07-23 02:54:37'),
(168, 'V2025', 'T4', 0, 55, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(169, 'V2025', 'T4', 0, 55, 50.01, 500.00, 0.1700, 0.0000, NULL, '2026-07-23 02:54:37'),
(170, 'V2025', 'T4', 0, 55, 500.01, 750.00, 0.1700, 0.0000, NULL, '2026-07-23 02:54:37'),
(171, 'V2025', 'T4', 0, 55, 750.01, NULL, 0.2200, 0.0500, 1628.00, '2026-07-23 02:54:37'),
(172, 'V2025', 'T4', 56, 60, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(173, 'V2025', 'T4', 56, 60, 50.01, 500.00, 0.1550, 0.0000, NULL, '2026-07-23 02:54:37'),
(174, 'V2025', 'T4', 56, 60, 500.01, 750.00, 0.1550, 0.0000, NULL, '2026-07-23 02:54:37'),
(175, 'V2025', 'T4', 56, 60, 750.01, NULL, 0.2050, 0.0500, 1517.00, '2026-07-23 02:54:37'),
(176, 'V2025', 'T4', 61, 65, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(177, 'V2025', 'T4', 61, 65, 50.01, 500.00, 0.1200, 0.0000, NULL, '2026-07-23 02:54:37'),
(178, 'V2025', 'T4', 61, 65, 500.01, 750.00, 0.1200, 0.0000, NULL, '2026-07-23 02:54:37'),
(179, 'V2025', 'T4', 61, 65, 750.01, NULL, 0.1700, 0.0500, 1258.00, '2026-07-23 02:54:37'),
(180, 'V2025', 'T4', 66, 70, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(181, 'V2025', 'T4', 66, 70, 50.01, 500.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(182, 'V2025', 'T4', 66, 70, 500.01, 750.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(183, 'V2025', 'T4', 66, 70, 750.01, NULL, 0.1400, 0.0500, 1036.00, '2026-07-23 02:54:37'),
(184, 'V2025', 'T4', 71, NULL, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(185, 'V2025', 'T4', 71, NULL, 50.01, 500.00, 0.0750, 0.0000, NULL, '2026-07-23 02:54:37'),
(186, 'V2025', 'T4', 71, NULL, 500.01, 750.00, 0.0750, 0.0000, NULL, '2026-07-23 02:54:37'),
(187, 'V2025', 'T4', 71, NULL, 750.01, NULL, 0.1250, 0.0500, 925.00, '2026-07-23 02:54:37'),
(188, 'V2025', 'T5', 0, 55, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(189, 'V2025', 'T5', 0, 55, 50.01, 500.00, 0.1700, 0.0000, NULL, '2026-07-23 02:54:37'),
(190, 'V2025', 'T5', 0, 55, 500.01, 750.00, 0.1700, 0.0000, NULL, '2026-07-23 02:54:37'),
(191, 'V2025', 'T5', 0, 55, 750.01, NULL, 0.3200, 0.1500, 2368.00, '2026-07-23 02:54:37'),
(192, 'V2025', 'T5', 56, 60, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(193, 'V2025', 'T5', 56, 60, 50.01, 500.00, 0.1550, 0.0000, NULL, '2026-07-23 02:54:37'),
(194, 'V2025', 'T5', 56, 60, 500.01, 750.00, 0.1550, 0.0000, NULL, '2026-07-23 02:54:37'),
(195, 'V2025', 'T5', 56, 60, 750.01, NULL, 0.2800, 0.1250, 2072.00, '2026-07-23 02:54:37'),
(196, 'V2025', 'T5', 61, 65, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(197, 'V2025', 'T5', 61, 65, 50.01, 500.00, 0.1200, 0.0000, NULL, '2026-07-23 02:54:37'),
(198, 'V2025', 'T5', 61, 65, 500.01, 750.00, 0.1200, 0.0000, NULL, '2026-07-23 02:54:37'),
(199, 'V2025', 'T5', 61, 65, 750.01, NULL, 0.1950, 0.0750, 1443.00, '2026-07-23 02:54:37'),
(200, 'V2025', 'T5', 66, 70, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(201, 'V2025', 'T5', 66, 70, 50.01, 500.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(202, 'V2025', 'T5', 66, 70, 500.01, 750.00, 0.0900, 0.0000, NULL, '2026-07-23 02:54:37'),
(203, 'V2025', 'T5', 66, 70, 750.01, NULL, 0.1400, 0.0500, 1036.00, '2026-07-23 02:54:37'),
(204, 'V2025', 'T5', 71, NULL, 0.01, 50.00, 0.0000, 0.0000, NULL, '2026-07-23 02:54:37'),
(205, 'V2025', 'T5', 71, NULL, 50.01, 500.00, 0.0750, 0.0000, NULL, '2026-07-23 02:54:37'),
(206, 'V2025', 'T5', 71, NULL, 500.01, 750.00, 0.0750, 0.0000, NULL, '2026-07-23 02:54:37'),
(207, 'V2025', 'T5', 71, NULL, 750.01, NULL, 0.1250, 0.0500, 925.00, '2026-07-23 02:54:37');

-- --------------------------------------------------------

--
-- Table structure for table `cpf_rate_versions`
--

CREATE TABLE `cpf_rate_versions` (
  `version_id` varchar(10) NOT NULL COMMENT 'Version identifier (e.g., V001, V002)',
  `table_code` varchar(5) NOT NULL COMMENT 'T1, T2, T3, T4, or T5',
  `rate_set_name` varchar(100) NOT NULL COMMENT 'Descriptive name (e.g., "Rates Jan 2026")',
  `effective_from` date NOT NULL COMMENT 'Date when this version becomes active',
  `effective_to` date DEFAULT NULL COMMENT 'Date when this version expires (NULL = open-ended)',
  `status` enum('HISTORICAL','CURRENT','FUTURE') NOT NULL DEFAULT 'FUTURE' COMMENT 'Auto-calculated status',
  `applies_to` varchar(100) DEFAULT NULL COMMENT 'Who this applies to (e.g., "SC & PR (3rd yr+)")',
  `scheme_type` varchar(50) DEFAULT NULL COMMENT 'Scheme type (e.g., "Full/Full", "G/G", "F/G")',
  `added_by` varchar(100) DEFAULT NULL COMMENT 'Who added this version',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='CPF rate version manager';

--
-- Dumping data for table `cpf_rate_versions`
--

INSERT INTO `cpf_rate_versions` (`version_id`, `table_code`, `rate_set_name`, `effective_from`, `effective_to`, `status`, `applies_to`, `scheme_type`, `added_by`, `created_at`, `updated_at`) VALUES
('V2025', 'T1', 'CPF Rates 2025 - Table 1', '2025-01-01', '2025-12-31', 'HISTORICAL', 'SC / SPR 3rd yr onwards', 'Full/Full', 'CPF Board', '2026-07-23 02:54:37', NULL),
('V2025', 'T2', 'CPF Rates 2025 - Table 2', '2025-01-01', '2025-12-31', 'HISTORICAL', 'SPR 1st year (G/G)', 'Graduated/Graduated', 'CPF Board', '2026-07-23 02:54:37', NULL),
('V2025', 'T3', 'CPF Rates 2025 - Table 3', '2025-01-01', '2025-12-31', 'HISTORICAL', 'SPR 2nd year (G/G)', 'Graduated/Graduated', 'CPF Board', '2026-07-23 02:54:37', NULL),
('V2025', 'T4', 'CPF Rates 2025 - Table 4', '2025-01-01', '2025-12-31', 'HISTORICAL', 'SPR 1st year (F/G)', 'Full/Graduated', 'CPF Board', '2026-07-23 02:54:37', NULL),
('V2025', 'T5', 'CPF Rates 2025 - Table 5', '2025-01-01', '2025-12-31', 'HISTORICAL', 'SPR 2nd year (F/G)', 'Full/Graduated', 'CPF Board', '2026-07-23 02:54:37', NULL),
('V2026', 'T1', 'CPF Rates 2026 - Table 1', '2026-01-01', NULL, 'CURRENT', 'SC / SPR 3rd yr onwards', 'Full/Full', 'CPF Board', '2026-06-13 14:39:00', NULL),
('V2026', 'T2', 'CPF Rates 2026 - Table 2', '2026-01-01', NULL, 'CURRENT', 'SPR 1st year (G/G)', 'Graduated/Graduated', 'CPF Board', '2026-06-13 14:39:00', NULL),
('V2026', 'T3', 'CPF Rates 2026 - Table 3', '2026-01-01', NULL, 'CURRENT', 'SPR 2nd year (G/G)', 'Graduated/Graduated', 'CPF Board', '2026-06-13 14:39:00', NULL),
('V2026', 'T4', 'CPF Rates 2026 - Table 4', '2026-01-01', NULL, 'CURRENT', 'SPR 1st year (F/G)', 'Full/Graduated', 'CPF Board', '2026-06-13 14:39:00', NULL),
('V2026', 'T5', 'CPF Rates 2026 - Table 5', '2026-01-01', NULL, 'CURRENT', 'SPR 2nd year (F/G)', 'Full/Graduated', 'CPF Board', '2026-06-13 14:39:00', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `document_approvals`
--

CREATE TABLE `document_approvals` (
  `approval_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `document_type` enum('quotation','progress_claim') NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `client_email` varchar(255) NOT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `token` varchar(64) NOT NULL,
  `status` enum('pending','approved','rejected','expired') DEFAULT 'pending',
  `message` text DEFAULT NULL,
  `client_comments` text DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `responded_at` datetime DEFAULT NULL,
  `responded_ip` varchar(45) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `document_type_config`
--

CREATE TABLE `document_type_config` (
  `config_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `doc_type` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `label_plural` varchar(100) NOT NULL,
  `theme_color` varchar(20) DEFAULT '#2c3e50',
  `theme_color_light` varchar(20) DEFAULT '#ecf0f1',
  `pdf_layout` varchar(20) NOT NULL DEFAULT 'classic',
  `logo_position` enum('left','center','right') NOT NULL DEFAULT 'center',
  `icon` varchar(50) DEFAULT 'fa-file',
  `show_pricing` tinyint(1) DEFAULT 1,
  `show_gst` tinyint(1) DEFAULT 1,
  `show_discount` tinyint(1) DEFAULT 1,
  `show_qty` tinyint(1) DEFAULT 1,
  `show_unit` tinyint(1) DEFAULT 0,
  `show_signatures` tinyint(1) DEFAULT 0,
  `show_delivery` tinyint(1) DEFAULT 0,
  `show_payment` tinyint(1) DEFAULT 0,
  `available_statuses` varchar(255) DEFAULT 'draft,sent,approved,cancelled',
  `default_status` varchar(50) DEFAULT 'draft',
  `list_page` varchar(100) DEFAULT NULL,
  `edit_page` varchar(100) DEFAULT NULL,
  `pdf_file` varchar(100) DEFAULT NULL,
  `action_buttons` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `document_type_config`
--

INSERT INTO `document_type_config` (`config_id`, `company_master_id`, `doc_type`, `label`, `label_plural`, `theme_color`, `theme_color_light`, `pdf_layout`, `logo_position`, `icon`, `show_pricing`, `show_gst`, `show_discount`, `show_qty`, `show_unit`, `show_signatures`, `show_delivery`, `show_payment`, `available_statuses`, `default_status`, `list_page`, `edit_page`, `pdf_file`, `action_buttons`, `sort_order`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 0, 'quotation', 'Quotation', 'Quotations', '#1B5E6E', '#E8F4F6', 'original', 'center', 'fa-file-invoice', 1, 1, 1, 1, 0, 1, 0, 0, 'draft,sent,approved,rejected,cancelled,converted', 'draft', 'list_quotations.php', 'quotation_form.php', 'download_quotation_pdf.php', NULL, 1, 1, '2026-07-06 04:13:37', '2026-07-06 14:46:17'),
(2, 1, 'invoice', 'Tax Invoice', 'Invoices', '#27ae60', '#e8f8f0', 'compact', 'center', 'fa-file-invoice-dollar', 1, 1, 1, 1, 0, 0, 0, 1, 'pending,paid,cancelled,void', 'pending', 'list_invoices.php', 'create_standalone_invoice.php', 'download_invoice_pdf.php', NULL, 2, 1, '2026-07-06 04:13:37', '2026-09-27 03:16:31'),
(3, 1, 'proforma', 'Proforma Invoice', 'Proforma Invoices', '#8e44ad', '#f3e8ff', 'compact', 'center', 'fa-file-alt', 1, 1, 1, 1, 0, 0, 0, 0, 'draft,sent,approved,cancelled,converted', 'draft', 'list_proforma.php', 'create_proforma.php', 'download_proforma_pdf.php', NULL, 3, 1, '2026-07-06 04:13:37', '2026-08-31 11:40:19'),
(4, 0, 'delivery_order', 'Delivery Order', 'Delivery Orders', '#1B5E3C', '#E8F6EC', 'compact', 'right', 'fa-truck', 0, 0, 0, 1, 1, 0, 1, 0, 'pending,delivered,cancelled', 'pending', 'list_delivery_orders.php', 'create_delivery_order.php', 'download_do_pdf.php', NULL, 4, 1, '2026-07-06 04:13:37', '2026-08-30 03:35:40'),
(5, 1, 'credit_note', 'Credit Note', 'Credit Notes', '#4c2882', '#f0eaff', 'compact', 'center', 'fa-file-minus', 1, 1, 1, 1, 0, 0, 0, 0, 'draft,approved,cancelled', 'draft', 'list_credit_notes.php', 'create_credit_note.php', 'download_credit_note_pdf.php', NULL, 5, 1, '2026-07-06 04:13:37', '2026-08-30 05:29:56'),
(6, 1, 'debit_note', 'Debit Note', 'Debit Notes', '#8c460a', '#fff3e0', 'compact', 'center', 'fa-file-plus', 1, 1, 1, 1, 0, 0, 0, 0, 'draft,approved,cancelled', 'draft', 'list_debit_notes.php', 'create_debit_note.php', 'download_debit_note_pdf.php', NULL, 6, 1, '2026-07-06 04:13:37', '2026-08-31 11:40:41'),
(7, 1, 'progress_claim', 'Progress Claim', 'Progress Claims', '#2471a3', '#eaf4fb', 'compact', 'center', 'fa-tasks', 1, 1, 0, 1, 0, 0, 0, 0, 'draft,submitted,approved,rejected,invoiced', 'draft', 'list_progress_claims.php', 'edit_progress_claim.php', 'download_claim_pdf.php', NULL, 7, 1, '2026-07-06 04:13:37', '2026-08-31 15:25:16'),
(8, 1, 'quotation', 'Quotation', 'Quotations', '#0566e6', '#e8f4f6', 'compact', 'center', 'fa-file-invoice', 1, 1, 1, 1, 0, 1, 0, 0, 'draft,sent,approved,rejected,cancelled,converted', 'draft', 'list_quotations.php', 'quotation_form.php', 'download_quotation_pdf.php', NULL, 1, 1, '2026-07-07 03:52:15', '2026-08-31 15:11:17'),
(9, 1, 'delivery_order', 'Delivery Order', 'Delivery Orders', '#1b5e3c', '#e8f6ec', 'compact', 'center', 'fa-truck', 0, 0, 0, 1, 1, 1, 1, 0, 'pending,delivered,cancelled', 'pending', 'list_delivery_orders.php', 'create_delivery_order.php', 'download_do_pdf.php', NULL, 4, 1, '2026-07-07 03:53:36', '2026-08-30 05:29:45'),
(10, 0, 'payslip', 'Payslip', 'Payslips', '#1e3c72', '#eef2fa', 'mom_official', 'center', 'fa-file-invoice-dollar', 1, 1, 1, 1, 0, 0, 0, 0, 'draft,sent,approved,cancelled', 'draft', NULL, NULL, NULL, NULL, 100, 1, '2026-07-07 04:36:38', '2026-07-23 08:15:52'),
(13, 3, 'payslip', 'Payslip', 'Payslips', '#1e3c72', '#eef2fa', 'mom_official', 'center', 'fa-file-invoice-dollar', 0, 0, 0, 0, 0, 0, 0, 0, 'draft,approved,paid', 'draft', 'list_payroll.php', 'generate_payroll.php', 'download_payslip_pdf.php', NULL, 100, 1, '2026-07-07 06:56:19', '2026-07-23 08:15:57'),
(14, 3, 'quotation', 'Quotation', 'Quotations', '#6f341b', '#e8f4f6', 'modern', 'center', 'fa-file-invoice', 1, 1, 1, 1, 0, 1, 0, 0, 'draft,sent,approved,rejected,cancelled,converted', 'draft', 'list_quotations.php', 'quotation_form.php', 'download_quotation_pdf.php', NULL, 1, 1, '2026-07-09 11:33:30', '2026-08-28 07:32:25'),
(15, 1, 'payslip', 'Payslip', 'Payslips', '#1e3c72', '#eef2fa', 'modern', 'center', 'fa-file-invoice-dollar', 0, 0, 0, 0, 0, 0, 0, 0, 'draft,approved,paid', 'draft', 'list_payroll.php', 'generate_payroll.php', 'download_payslip_pdf.php', NULL, 0, 1, '2026-08-31 15:14:46', '2026-09-24 08:29:36'),
(16, 2, 'quotation', 'Quotation', 'Quotations', '#1b5e6e', '#e8f4f6', 'original', 'center', 'fa-file-invoice', 1, 1, 1, 1, 0, 1, 0, 0, 'draft,sent,approved,rejected,cancelled,converted', 'draft', 'list_quotations.php', 'quotation_form.php', 'download_quotation_pdf.php', NULL, 1, 1, '2026-09-25 02:37:15', '2026-09-25 02:37:15');

-- --------------------------------------------------------

--
-- Table structure for table `email_log`
--

CREATE TABLE `email_log` (
  `log_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `document_id` int(11) DEFAULT NULL,
  `document_type` varchar(50) DEFAULT NULL,
  `to_email` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `email_type` enum('approval_request','reminder','approved','rejected') NOT NULL,
  `status` enum('sent','failed') DEFAULT 'sent',
  `error_message` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT current_timestamp(),
  `sent_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `full_name` varchar(100) NOT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `date_of_commencement` date DEFAULT NULL,
  `residential_status` enum('Singapore Citizen','Singapore PR','Employment Pass','S Pass','Work Permit') DEFAULT 'Singapore Citizen',
  `nationality` varchar(50) DEFAULT NULL,
  `race` varchar(30) DEFAULT NULL,
  `marital_status` varchar(20) DEFAULT NULL,
  `ot_entitled` enum('Yes','No') DEFAULT 'Yes',
  `nric_no` varchar(20) DEFAULT NULL,
  `fin_no` varchar(20) DEFAULT NULL,
  `wp_no` varchar(20) DEFAULT NULL,
  `passport_no` varchar(50) DEFAULT NULL,
  `passport_expiry` date DEFAULT NULL,
  `date_of_application` date DEFAULT NULL,
  `date_of_issue` date DEFAULT NULL,
  `work_pass_expiry` date DEFAULT NULL,
  `safety_course_expiry` date DEFAULT NULL,
  `driving_licence_expiry` date DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `address` text DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `emergency_contact` varchar(100) DEFAULT NULL,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_relationship` varchar(50) DEFAULT NULL,
  `emergency_contact_phone` varchar(30) DEFAULT NULL,
  `basic_salary` decimal(10,2) DEFAULT 0.00,
  `salary_type` enum('monthly','daily') DEFAULT 'monthly' COMMENT 'Monthly salary or Daily rate',
  `daily_rate` decimal(10,2) DEFAULT 0.00 COMMENT 'Daily rate for work permit holders',
  `daily_hours` decimal(4,1) DEFAULT 8.0 COMMENT 'Working hours per day for daily rate calculation',
  `allowance_housing` decimal(10,2) DEFAULT 0.00,
  `allowance_transport` decimal(10,2) DEFAULT 0.00,
  `allowance_others` decimal(10,2) DEFAULT 0.00,
  `deduction_housing` decimal(10,2) DEFAULT 0.00,
  `deduction_others` decimal(10,2) DEFAULT 0.00,
  `net_salary` decimal(10,2) DEFAULT 0.00,
  `working_hours_per_week` decimal(4,1) DEFAULT 44.0 COMMENT 'Working hours per week (e.g., 40, 44, 48)',
  `working_days_per_week` decimal(2,1) DEFAULT 6.0 COMMENT 'Number of working days per week: 6.0 (default), 5.5, or 5.0',
  `rest_day` varchar(10) DEFAULT 'Sunday',
  `weekday_ot_rate` enum('1.0','1.5','2.0','N/A') DEFAULT '1.5',
  `sunday_ot_rate` decimal(3,1) DEFAULT 2.0 COMMENT 'Sunday OT multiplier rate',
  `public_holiday_ot_rate` decimal(3,1) DEFAULT 2.0 COMMENT 'Public Holiday OT multiplier rate',
  `ot_rate_type` enum('variable','flat') DEFAULT 'variable',
  `weekday_ot_flat_rate` decimal(10,2) DEFAULT NULL,
  `sunday_ot_flat_rate` decimal(10,2) DEFAULT 0.00 COMMENT 'Sunday OT flat hourly rate',
  `public_holiday_ot_flat_rate` decimal(10,2) DEFAULT 0.00 COMMENT 'Public Holiday OT flat hourly rate',
  `cpf_applicable` enum('Yes','No') DEFAULT 'No',
  `cpf_entry_id` int(11) DEFAULT NULL,
  `bank_account` varchar(100) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account_no` varchar(50) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `cpf_table_code` varchar(5) DEFAULT 'T1' COMMENT 'T1-T5 CPF rate table',
  `cpf_status` varchar(20) DEFAULT 'SC' COMMENT 'SC/PR/SPR1/SPR2',
  `levy_tier` varchar(20) DEFAULT NULL,
  `quota_sector` varchar(30) DEFAULT NULL,
  `pr_anniversary_date` date DEFAULT NULL COMMENT 'For SPR year calculation',
  `shg_type` enum('NONE','CDAC','ECF','MBMF','SINDA') NOT NULL DEFAULT 'NONE',
  `shg_opt_out` tinyint(1) NOT NULL DEFAULT 0,
  `photo_path` varchar(255) DEFAULT NULL,
  `contract_signed_date` date DEFAULT NULL,
  `contract_file_path` varchar(255) DEFAULT NULL,
  `medical_checkup_date` date DEFAULT NULL,
  `medical_checkup_result` varchar(20) DEFAULT NULL,
  `bank_verification_status` varchar(20) DEFAULT 'Pending',
  `induction_completed_date` date DEFAULT NULL,
  `resignation_date` date DEFAULT NULL,
  `notice_period_days` int(11) DEFAULT NULL,
  `last_working_day` date DEFAULT NULL,
  `exit_reason` varchar(30) DEFAULT NULL,
  `rehire_eligible` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `employee_history`
--

CREATE TABLE `employee_history` (
  `history_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `basic_salary` decimal(10,2) DEFAULT NULL,
  `salary_type` varchar(20) DEFAULT NULL,
  `daily_rate` decimal(10,2) DEFAULT NULL,
  `working_days_per_week` decimal(3,1) DEFAULT NULL,
  `working_hours_per_week` decimal(4,1) DEFAULT NULL,
  `allowance_housing` decimal(10,2) DEFAULT NULL,
  `allowance_transport` decimal(10,2) DEFAULT NULL,
  `allowance_others` decimal(10,2) DEFAULT NULL,
  `deduction_housing` decimal(10,2) DEFAULT NULL,
  `deduction_others` decimal(10,2) DEFAULT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `expense_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `expense_date` date NOT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `vendor_name` varchar(255) DEFAULT NULL,
  `description` text NOT NULL,
  `subtotal` decimal(15,2) NOT NULL DEFAULT 0.00,
  `gst_rate` decimal(5,2) DEFAULT 9.00,
  `gst_amount` decimal(15,2) DEFAULT 0.00,
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `gst_claimable` tinyint(1) DEFAULT 1,
  `payment_method` enum('cash','cheque','bank_transfer','credit_card','other') DEFAULT 'bank_transfer',
  `payment_status` enum('paid','pending','partial') DEFAULT 'paid',
  `receipt_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `expense_categories`
--

CREATE TABLE `expense_categories` (
  `category_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `category_name` varchar(100) NOT NULL,
  `category_code` varchar(20) DEFAULT NULL,
  `category_type` enum('expense','income') NOT NULL DEFAULT 'expense',
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `expense_categories`
--

INSERT INTO `expense_categories` (`category_id`, `company_master_id`, `category_name`, `category_code`, `category_type`, `description`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
(1, 1, 'Materials & Supplies', 'MAT', 'expense', NULL, 1, 1, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(2, 1, 'Subcontractor', 'SUB', 'expense', NULL, 1, 2, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(3, 1, 'Salaries & Wages', 'SAL', 'expense', NULL, 1, 3, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(4, 1, 'Rent & Utilities', 'RNT', 'expense', NULL, 1, 4, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(5, 1, 'Transport & Logistics', 'TRN', 'expense', NULL, 1, 5, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(6, 1, 'Equipment & Tools', 'EQP', 'expense', NULL, 1, 6, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(7, 1, 'Professional Fees', 'PRO', 'expense', NULL, 1, 7, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(8, 1, 'Insurance', 'INS', 'expense', NULL, 1, 8, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(9, 1, 'Marketing & Advertising', 'MKT', 'expense', NULL, 1, 9, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(10, 1, 'Office Expenses', 'OFF', 'expense', NULL, 1, 10, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(11, 1, 'Miscellaneous', 'MIS', 'expense', NULL, 1, 99, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(12, 1, 'Invoice Revenue', 'INV', 'income', NULL, 1, 1, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(13, 1, 'Progress Claim', 'PC', 'income', NULL, 0, 2, '2026-05-30 15:35:44', '2026-05-31 01:41:01'),
(14, 1, 'Other Income', 'OTH', 'income', NULL, 1, 3, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(15, 2, 'Materials & Supplies', 'MAT', 'expense', NULL, 1, 1, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(16, 2, 'Subcontractor', 'SUB', 'expense', NULL, 1, 2, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(17, 2, 'Salaries & Wages', 'SAL', 'expense', NULL, 1, 3, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(18, 2, 'Rent & Utilities', 'RNT', 'expense', NULL, 1, 4, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(19, 2, 'Transport & Logistics', 'TRN', 'expense', NULL, 1, 5, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(20, 2, 'Equipment & Tools', 'EQP', 'expense', NULL, 1, 6, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(21, 2, 'Professional Fees', 'PRO', 'expense', NULL, 1, 7, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(22, 2, 'Insurance', 'INS', 'expense', NULL, 1, 8, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(23, 2, 'Marketing & Advertising', 'MKT', 'expense', NULL, 1, 9, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(24, 2, 'Office Expenses', 'OFF', 'expense', NULL, 1, 10, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(25, 2, 'Miscellaneous', 'MIS', 'expense', NULL, 1, 99, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(26, 2, 'Invoice Revenue', 'INV', 'income', NULL, 1, 1, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(27, 2, 'Progress Claim', 'PC', 'income', NULL, 1, 2, '2026-05-30 15:35:44', '2026-05-30 15:35:44'),
(28, 2, 'Other Income', 'OTH', 'income', NULL, 1, 3, '2026-05-30 15:35:44', '2026-05-30 15:35:44');

-- --------------------------------------------------------

--
-- Table structure for table `expense_claims`
--

CREATE TABLE `expense_claims` (
  `claim_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `claim_date` date NOT NULL,
  `category` enum('transport','medical','meals','client_entertainment','others') NOT NULL DEFAULT 'others',
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `payroll_id` int(11) DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL COMMENT 'user_id of HR who submitted on behalf',
  `on_behalf_note` varchar(255) DEFAULT NULL COMMENT 'HR submitted on behalf of staff'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `financial_documents`
--

CREATE TABLE `financial_documents` (
  `document_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `document_type` enum('quotation','invoice','proforma','progress_claim','delivery_order','delivery_note','debit_note','credit_note') NOT NULL,
  `document_no` varchar(50) NOT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `client_address` text DEFAULT NULL,
  `client_contact` varchar(100) DEFAULT NULL,
  `client_email` varchar(255) DEFAULT NULL,
  `reference_doc_type` enum('invoice','quotation','proforma','progress_claim') DEFAULT NULL,
  `reference_doc_id` int(11) DEFAULT NULL,
  `reference_doc_no` varchar(50) DEFAULT NULL COMMENT 'Human-readable reference number',
  `company_id` int(11) DEFAULT NULL,
  `attention_to` varchar(255) DEFAULT NULL,
  `subject` text DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `document_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `valid_until` date DEFAULT NULL COMMENT 'For proforma invoices',
  `subtotal` decimal(12,2) DEFAULT 0.00,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  `discount_type` enum('amount','percentage') DEFAULT 'amount',
  `actual_amount` decimal(15,2) DEFAULT 0.00,
  `gst_rate` decimal(5,2) DEFAULT 9.00,
  `discount_amount` decimal(12,2) DEFAULT 0.00,
  `tax_percentage` decimal(5,2) DEFAULT 7.00,
  `gst_mode` varchar(20) DEFAULT 'exclusive',
  `gst_amount` decimal(15,2) DEFAULT 0.00,
  `currency` varchar(10) DEFAULT 'SGD',
  `remarks` text DEFAULT NULL,
  `tax_amount` decimal(12,2) DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL,
  `status` enum('draft','pending','sent','submitted','approved','paid','invoiced','cancelled','void','converted','rejected') DEFAULT 'draft',
  `payment_status` enum('unpaid','partial','paid','refunded','applied') DEFAULT 'unpaid',
  `paid_amount` decimal(12,2) DEFAULT 0.00,
  `payment_date` date DEFAULT NULL,
  `payment_method` varchar(100) DEFAULT NULL,
  `credit_type` enum('refund','adjustment','discount','return','goodwill') DEFAULT NULL COMMENT 'Type of credit',
  `application_method` enum('refund','next_invoice','write_off') DEFAULT NULL COMMENT 'How credit is applied',
  `converted_to_invoice_id` int(11) DEFAULT NULL,
  `converted_at` datetime DEFAULT NULL,
  `description` text DEFAULT NULL,
  `reason` text DEFAULT NULL COMMENT 'Mandatory for debit/credit notes',
  `notes` text DEFAULT NULL COMMENT 'Internal notes',
  `terms_conditions` text DEFAULT NULL,
  `created_by` int(11) DEFAULT 1 COMMENT 'User who created the document',
  `approved_by` int(11) DEFAULT NULL COMMENT 'User who approved',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `payment_terms` varchar(255) DEFAULT NULL,
  `claim_period_from` date DEFAULT NULL,
  `claim_period_to` date DEFAULT NULL,
  `retention_percentage` decimal(5,2) DEFAULT 0.00,
  `retention_amount` decimal(15,2) DEFAULT 0.00,
  `previous_claim_amount` decimal(15,2) DEFAULT 0.00,
  `current_claim_amount` decimal(15,2) DEFAULT 0.00,
  `terms` text DEFAULT NULL,
  `internal_notes` text DEFAULT NULL,
  `parent_document_id` int(11) DEFAULT NULL,
  `delivery_location` varchar(255) DEFAULT NULL,
  `serviced_by` varchar(100) DEFAULT NULL,
  `serviced_date` date DEFAULT NULL,
  `received_by` varchar(100) DEFAULT NULL,
  `received_date` date DEFAULT NULL,
  `our_reference` varchar(100) DEFAULT NULL,
  `your_reference` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Unified table for Proforma Invoices, Debit Notes, Credit Notes';

-- --------------------------------------------------------

--
-- Table structure for table `financial_document_items`
--

CREATE TABLE `financial_document_items` (
  `item_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `sl_no` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_module_header` tinyint(1) DEFAULT 0,
  `item_description` text NOT NULL,
  `item_code` varchar(100) DEFAULT NULL,
  `quantity` decimal(10,2) DEFAULT 1.00,
  `unit_price` decimal(12,2) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `adjustment_reason` varchar(255) DEFAULT NULL COMMENT 'Specific reason for this line item',
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `item_order` int(11) DEFAULT 0,
  `previous_qty` decimal(10,2) DEFAULT 0.00,
  `current_qty` decimal(10,2) DEFAULT 0.00,
  `cumulative_qty` decimal(10,2) DEFAULT 0.00,
  `claim_percentage` decimal(5,2) DEFAULT 0.00,
  `unit` varchar(50) DEFAULT 'Nos',
  `inventory_item_id` int(11) DEFAULT NULL,
  `stock_deducted` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Line items for financial documents';

-- --------------------------------------------------------

--
-- Table structure for table `financial_document_payments`
--

CREATE TABLE `financial_document_payments` (
  `payment_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `payment_date` date NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Partial/full payment history against a financial_documents row';

-- --------------------------------------------------------

--
-- Table structure for table `foreign_worker_levy`
--

CREATE TABLE `foreign_worker_levy` (
  `levy_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `levy_month` tinyint(4) NOT NULL,
  `levy_year` smallint(6) NOT NULL,
  `quota_sector` varchar(30) DEFAULT NULL,
  `levy_tier` varchar(20) DEFAULT NULL,
  `dependency_tier` varchar(20) DEFAULT 'Tier 1',
  `monthly_rate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `days_in_month` tinyint(4) NOT NULL DEFAULT 30,
  `days_applicable` tinyint(4) NOT NULL DEFAULT 30,
  `waiver_days` tinyint(4) NOT NULL DEFAULT 0,
  `waiver_reason` varchar(150) DEFAULT NULL,
  `prorated_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` varchar(20) NOT NULL DEFAULT 'draft',
  `remarks` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fwl_rates`
--

CREATE TABLE `fwl_rates` (
  `rate_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `quota_sector` varchar(30) NOT NULL,
  `levy_tier` varchar(20) NOT NULL,
  `dependency_tier` varchar(20) NOT NULL DEFAULT 'Tier 1',
  `monthly_rate` decimal(10,2) NOT NULL,
  `effective_from` date NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gst_rates`
--

CREATE TABLE `gst_rates` (
  `id` int(11) NOT NULL,
  `rate` decimal(5,2) NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `gst_rates`
--

INSERT INTO `gst_rates` (`id`, `rate`, `effective_from`, `effective_to`, `description`, `created_by`, `created_at`) VALUES
(1, 9.00, '2024-01-01', NULL, 'GST 9% (2024 onwards - update if changed)', NULL, '2026-07-11 01:48:03');

-- --------------------------------------------------------

--
-- Table structure for table `inventory_items`
--

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `sku` varchar(50) NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `item_name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `unit` varchar(30) DEFAULT 'piece',
  `cost_price` decimal(10,2) DEFAULT 0.00,
  `selling_price` decimal(10,2) DEFAULT 0.00,
  `min_stock` int(11) DEFAULT 0,
  `preferred_supplier_id` int(11) DEFAULT NULL,
  `supplier_sku` varchar(100) DEFAULT NULL,
  `current_stock` int(11) DEFAULT 0,
  `reserved_stock` int(11) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `inventory_transactions`
--

CREATE TABLE `inventory_transactions` (
  `transaction_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `transaction_type` enum('stock_in','stock_out','adjustment') NOT NULL,
  `quantity` int(11) NOT NULL,
  `balance_after` int(11) NOT NULL,
  `unit_cost` decimal(10,2) DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `leave_applications`
--

CREATE TABLE `leave_applications` (
  `leave_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `employee_id` int(11) NOT NULL,
  `leave_type` enum('annual','medical','unpaid','emergency','others') NOT NULL DEFAULT 'annual',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `days_applied` decimal(4,1) NOT NULL DEFAULT 1.0,
  `half_day` tinyint(1) NOT NULL DEFAULT 0,
  `half_day_session` enum('morning','afternoon') DEFAULT NULL,
  `medical_cert` tinyint(1) NOT NULL DEFAULT 0,
  `attachment_path` varchar(255) DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `leave_entitlements`
--

CREATE TABLE `leave_entitlements` (
  `entitlement_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `employee_id` int(11) NOT NULL,
  `leave_type` enum('annual','medical','hospitalisation','unpaid','emergency','childcare','others') NOT NULL,
  `year` int(4) NOT NULL,
  `entitled_days` decimal(4,1) NOT NULL DEFAULT 0.0,
  `carried_forward` decimal(4,1) NOT NULL DEFAULT 0.0,
  `used_days` decimal(4,1) NOT NULL DEFAULT 0.0,
  `balance_days` decimal(4,1) NOT NULL DEFAULT 0.0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `leave_policies`
--

CREATE TABLE `leave_policies` (
  `policy_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `leave_type` enum('annual','medical','hospitalisation','unpaid','emergency','childcare','others') NOT NULL,
  `entitled_days` decimal(4,1) NOT NULL DEFAULT 14.0,
  `carry_forward` tinyint(1) NOT NULL DEFAULT 0,
  `max_carry_days` decimal(4,1) NOT NULL DEFAULT 0.0,
  `requires_approval` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `leave_policies`
--

INSERT INTO `leave_policies` (`policy_id`, `company_master_id`, `leave_type`, `entitled_days`, `carry_forward`, `max_carry_days`, `requires_approval`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 1, 'annual', 14.0, 1, 5.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52'),
(2, 1, 'medical', 14.0, 0, 0.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52'),
(3, 1, 'hospitalisation', 60.0, 0, 0.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52'),
(4, 1, 'unpaid', 0.0, 0, 0.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52'),
(5, 1, 'emergency', 3.0, 0, 0.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52'),
(6, 1, 'childcare', 6.0, 0, 0.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52'),
(7, 1, 'others', 0.0, 0, 0.0, 1, 1, '2026-06-01 13:46:27', '2026-06-21 00:53:52');

-- --------------------------------------------------------

--
-- Table structure for table `loan_repayments`
--

CREATE TABLE `loan_repayments` (
  `repayment_id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `payroll_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pay_period_month` int(2) NOT NULL,
  `pay_period_year` int(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `attempt_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `success` tinyint(1) NOT NULL,
  `attempted_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `overtime`
--

CREATE TABLE `overtime` (
  `overtime_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `employee_id` int(11) NOT NULL,
  `overtime_date` date NOT NULL,
  `day_type` enum('weekday_8am_5pm','weekday_after_5pm','sunday_ph','saturday_half') NOT NULL DEFAULT 'weekday_after_5pm',
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `hours_worked` decimal(5,2) NOT NULL,
  `hourly_rate` decimal(10,2) NOT NULL,
  `overtime_rate` decimal(3,1) NOT NULL,
  `overtime_pay` decimal(10,2) NOT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `source` enum('manual','auto') DEFAULT 'manual',
  `remarks` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `rejection_reason` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `payment_terms`
--

CREATE TABLE `payment_terms` (
  `term_id` int(11) NOT NULL,
  `quotation_id` int(11) NOT NULL,
  `term_description` text NOT NULL,
  `percentage` decimal(5,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `payroll`
--

CREATE TABLE `payroll` (
  `payroll_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL DEFAULT 1,
  `employee_id` int(11) NOT NULL,
  `pay_period_month` int(11) NOT NULL,
  `pay_period_year` int(11) NOT NULL,
  `basic_salary` decimal(10,2) DEFAULT 0.00,
  `allowance_housing` decimal(10,2) DEFAULT 0.00,
  `allowance_transport` decimal(10,2) DEFAULT 0.00,
  `allowance_others` decimal(10,2) DEFAULT 0.00,
  `deduction_housing` decimal(10,2) DEFAULT 0.00,
  `deduction_others` decimal(10,2) DEFAULT 0.00,
  `overtime_hours` decimal(6,2) DEFAULT 0.00,
  `overtime_pay` decimal(10,2) DEFAULT 0.00,
  `employee_cpf` decimal(10,2) DEFAULT 0.00,
  `employer_cpf` decimal(10,2) DEFAULT 0.00,
  `total_cpf` decimal(10,2) DEFAULT 0.00,
  `cpf_version_id` varchar(10) DEFAULT NULL COMMENT 'CPF rate version used',
  `cpf_table_code` varchar(5) DEFAULT NULL COMMENT 'T1-T5 table used',
  `employee_age` int(11) DEFAULT NULL,
  `cpf_rate_employer` decimal(5,2) DEFAULT 0.00,
  `cpf_rate_employee` decimal(5,2) DEFAULT 0.00,
  `gross_salary` decimal(10,2) DEFAULT 0.00,
  `weekday_ot_hours` decimal(5,2) DEFAULT 0.00,
  `sunday_ot_hours` decimal(5,2) DEFAULT 0.00,
  `ph_ot_hours` decimal(5,2) DEFAULT 0.00,
  `ot_pay` decimal(10,2) DEFAULT 0.00,
  `total_deductions` decimal(10,2) DEFAULT 0.00,
  `net_salary` decimal(10,2) DEFAULT 0.00,
  `days_present` decimal(5,1) DEFAULT 0.0 COMMENT 'Days present including half-days (0.5)',
  `days_absent` decimal(5,1) DEFAULT 0.0 COMMENT 'Days absent',
  `days_leave` decimal(5,1) DEFAULT 0.0 COMMENT 'Days on leave',
  `status` enum('draft','approved','paid') DEFAULT 'draft',
  `payment_date` date DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `shg_type` enum('NONE','CDAC','ECF','MBMF','SINDA') NOT NULL DEFAULT 'NONE',
  `shg_amount` decimal(6,2) NOT NULL DEFAULT 0.00,
  `shg_version_code` varchar(20) DEFAULT NULL,
  `reimbursement_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `loan_deduction` decimal(10,2) NOT NULL DEFAULT 0.00,
  `sdl_amount` decimal(6,2) NOT NULL DEFAULT 0.00,
  `aw_cpf_applied` decimal(10,2) NOT NULL DEFAULT 0.00,
  `aws_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `bonus_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `commission_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `other_adjustment_amount` decimal(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `payroll_adjustments`
--

CREATE TABLE `payroll_adjustments` (
  `adjustment_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `pay_period_year` int(11) NOT NULL,
  `pay_period_month` int(11) NOT NULL,
  `adjustment_type` enum('AWS','Bonus','Commission','Other') NOT NULL DEFAULT 'Bonus',
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `public_holidays`
--

CREATE TABLE `public_holidays` (
  `holiday_id` int(11) NOT NULL,
  `holiday_date` date NOT NULL,
  `holiday_name` varchar(100) NOT NULL,
  `is_recurring` tinyint(1) DEFAULT 0 COMMENT '1 if holiday recurs yearly (e.g., New Year), 0 if one-time',
  `year` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `public_holidays`
--

INSERT INTO `public_holidays` (`holiday_id`, `holiday_date`, `holiday_name`, `is_recurring`, `year`, `created_at`) VALUES
(1, '2025-01-01', 'New Year\'s Day', 1, 2025, '2026-02-21 02:12:03'),
(2, '2025-01-29', 'Chinese New Year', 0, 2025, '2026-02-21 02:12:03'),
(3, '2025-01-30', 'Chinese New Year', 0, 2025, '2026-02-21 02:12:03'),
(4, '2025-04-18', 'Good Friday', 0, 2025, '2026-02-21 02:12:03'),
(5, '2025-05-01', 'Labour Day', 1, 2025, '2026-02-21 02:12:03'),
(6, '2025-05-12', 'Vesak Day', 0, 2025, '2026-02-21 02:12:03'),
(7, '2025-06-02', 'Hari Raya Puasa', 0, 2025, '2026-02-21 02:12:03'),
(8, '2025-08-09', 'Hari Raya Haji', 0, 2025, '2026-02-21 02:12:03'),
(9, '2025-10-20', 'Deepavali', 0, 2025, '2026-02-21 02:12:03'),
(10, '2025-12-25', 'Christmas Day', 1, 2025, '2026-02-21 02:12:03'),
(11, '2026-01-01', 'New Year\'s Day', 1, 2026, '2026-02-21 02:12:03'),
(12, '2026-02-17', 'Chinese New Year', 0, 2026, '2026-02-21 02:12:03'),
(13, '2026-02-18', 'Chinese New Year', 0, 2026, '2026-02-21 02:12:03'),
(14, '2026-04-03', 'Good Friday', 0, 2026, '2026-02-21 02:12:03'),
(15, '2026-05-01', 'Vesak Day', 0, 2026, '2026-02-21 02:12:03'),
(16, '2026-05-22', 'Hari Raya Puasa', 0, 2026, '2026-02-21 02:12:03'),
(17, '2026-07-29', 'Hari Raya Haji', 0, 2026, '2026-02-21 02:12:03'),
(18, '2026-08-09', 'National Day', 1, 2026, '2026-02-21 02:12:03'),
(19, '2026-08-10', 'National Day (Observed)', 0, 2026, '2026-02-21 02:12:03'),
(20, '2026-11-08', 'Deepavali', 0, 2026, '2026-02-21 02:12:03'),
(21, '2026-12-25', 'Christmas Day', 1, 2026, '2026-02-21 02:12:03'),
(22, '2026-11-09', 'Deepavali (Observed)', 0, 0, '2026-09-23 06:16:58');

-- --------------------------------------------------------

--
-- Table structure for table `purchase_orders`
--

CREATE TABLE `purchase_orders` (
  `po_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `po_no` varchar(50) NOT NULL,
  `po_date` date NOT NULL,
  `vendor_id` int(11) NOT NULL COMMENT 'references companies.company_id',
  `delivery_date` date DEFAULT NULL,
  `status` enum('draft','sent','partial','received','cancelled') DEFAULT 'draft',
  `subtotal` decimal(12,2) DEFAULT 0.00,
  `discount_amount` decimal(12,2) DEFAULT 0.00,
  `gst_rate` decimal(5,2) DEFAULT 9.00,
  `gst_amount` decimal(12,2) DEFAULT 0.00,
  `total_amount` decimal(12,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `purchase_order_items`
--

CREATE TABLE `purchase_order_items` (
  `poi_id` int(11) NOT NULL,
  `po_id` int(11) NOT NULL,
  `inventory_item_id` int(11) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `quantity_ordered` decimal(10,2) DEFAULT 0.00,
  `quantity_received` decimal(10,2) DEFAULT 0.00,
  `unit` varchar(30) DEFAULT NULL,
  `unit_cost` decimal(10,2) DEFAULT 0.00,
  `total_cost` decimal(12,2) DEFAULT 0.00,
  `item_order` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `purchase_receipts`
--

CREATE TABLE `purchase_receipts` (
  `receipt_id` int(11) NOT NULL,
  `po_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `receipt_no` varchar(50) NOT NULL,
  `receipt_date` date NOT NULL,
  `supplier_do_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `purchase_receipt_items`
--

CREATE TABLE `purchase_receipt_items` (
  `ri_id` int(11) NOT NULL,
  `receipt_id` int(11) NOT NULL,
  `poi_id` int(11) NOT NULL,
  `inventory_item_id` int(11) DEFAULT NULL,
  `quantity_received` decimal(10,2) DEFAULT 0.00,
  `unit_cost` decimal(10,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `shg_rates`
--

CREATE TABLE `shg_rates` (
  `id` int(11) NOT NULL,
  `version_id` int(11) NOT NULL,
  `shg_type` enum('CDAC','ECF','MBMF','SINDA') NOT NULL,
  `wage_min` decimal(10,2) NOT NULL,
  `wage_max` decimal(10,2) DEFAULT NULL,
  `contribution` decimal(6,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `shg_rates`
--

INSERT INTO `shg_rates` (`id`, `version_id`, `shg_type`, `wage_min`, `wage_max`, `contribution`) VALUES
(1, 1, 'CDAC', 0.00, 2000.00, 0.50),
(2, 1, 'CDAC', 2000.01, 3500.00, 1.00),
(3, 1, 'CDAC', 3500.01, 5000.00, 1.50),
(4, 1, 'CDAC', 5000.01, 7500.00, 2.00),
(5, 1, 'CDAC', 7500.01, NULL, 3.00),
(6, 1, 'ECF', 0.00, 1000.00, 2.00),
(7, 1, 'ECF', 1000.01, 1500.00, 4.00),
(8, 1, 'ECF', 1500.01, 2500.00, 6.00),
(9, 1, 'ECF', 2500.01, 4000.00, 9.00),
(10, 1, 'ECF', 4000.01, 7000.00, 12.00),
(11, 1, 'ECF', 7000.01, 10000.00, 16.00),
(12, 1, 'ECF', 10000.01, NULL, 20.00),
(13, 1, 'MBMF', 0.00, 1000.00, 3.00),
(14, 1, 'MBMF', 1000.01, 2000.00, 4.50),
(15, 1, 'MBMF', 2000.01, 3000.00, 6.50),
(16, 1, 'MBMF', 3000.01, 4000.00, 15.00),
(17, 1, 'MBMF', 4000.01, 6000.00, 19.50),
(18, 1, 'MBMF', 6000.01, 8000.00, 22.00),
(19, 1, 'MBMF', 8000.01, 10000.00, 24.00),
(20, 1, 'MBMF', 10000.01, NULL, 26.00),
(21, 1, 'SINDA', 0.00, 1000.00, 1.00),
(22, 1, 'SINDA', 1000.01, 1500.00, 3.00),
(23, 1, 'SINDA', 1500.01, 2500.00, 5.00),
(24, 1, 'SINDA', 2500.01, 4500.00, 7.00),
(25, 1, 'SINDA', 4500.01, 7500.00, 9.00),
(26, 1, 'SINDA', 7500.01, 10000.00, 12.00),
(27, 1, 'SINDA', 10000.01, 15000.00, 18.00),
(28, 1, 'SINDA', 15000.01, NULL, 30.00),
(29, 2, 'CDAC', 0.00, 2000.00, 0.50),
(30, 2, 'CDAC', 2000.01, 3500.00, 1.00),
(31, 2, 'CDAC', 3500.01, 5000.00, 1.50),
(32, 2, 'CDAC', 5000.01, 7500.00, 2.00),
(33, 2, 'CDAC', 7500.01, NULL, 3.00),
(34, 2, 'ECF', 0.00, 1000.00, 2.00),
(35, 2, 'ECF', 1000.01, 1500.00, 4.00),
(36, 2, 'ECF', 1500.01, 2500.00, 6.00),
(37, 2, 'ECF', 2500.01, 4000.00, 9.00),
(38, 2, 'ECF', 4000.01, 7000.00, 12.00),
(39, 2, 'ECF', 7000.01, 10000.00, 16.00),
(40, 2, 'ECF', 10000.01, NULL, 20.00),
(41, 2, 'MBMF', 0.00, 1000.00, 3.00),
(42, 2, 'MBMF', 1000.01, 2000.00, 4.50),
(43, 2, 'MBMF', 2000.01, 3000.00, 6.50),
(44, 2, 'MBMF', 3000.01, 4000.00, 15.00),
(45, 2, 'MBMF', 4000.01, 6000.00, 19.50),
(46, 2, 'MBMF', 6000.01, 8000.00, 22.00),
(47, 2, 'MBMF', 8000.01, 10000.00, 24.00),
(48, 2, 'MBMF', 10000.01, NULL, 26.00),
(49, 2, 'SINDA', 0.00, 1000.00, 1.00),
(50, 2, 'SINDA', 1000.01, 1500.00, 3.00),
(51, 2, 'SINDA', 1500.01, 2500.00, 5.00),
(52, 2, 'SINDA', 2500.01, 4500.00, 7.00),
(53, 2, 'SINDA', 4500.01, 7500.00, 9.00),
(54, 2, 'SINDA', 7500.01, 10000.00, 12.00),
(55, 2, 'SINDA', 10000.01, 15000.00, 18.00),
(56, 2, 'SINDA', 15000.01, NULL, 30.00);

-- --------------------------------------------------------

--
-- Table structure for table `shg_rate_versions`
--

CREATE TABLE `shg_rate_versions` (
  `version_id` int(11) NOT NULL,
  `version_code` varchar(20) NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `shg_rate_versions`
--

INSERT INTO `shg_rate_versions` (`version_id`, `version_code`, `effective_from`, `effective_to`, `remarks`, `created_at`) VALUES
(1, 'V001', '2026-01-01', NULL, 'Initial SHG rate table - CPFB reference', '2026-06-13 09:35:33'),
(2, 'V001', '2026-01-01', NULL, 'Initial SHG rate table - CPFB reference', '2026-06-13 09:55:14');

-- --------------------------------------------------------

--
-- Table structure for table `staff_loans`
--

CREATE TABLE `staff_loans` (
  `loan_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `loan_type` enum('advance','loan') NOT NULL DEFAULT 'loan',
  `loan_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `monthly_instalment` decimal(10,2) NOT NULL DEFAULT 0.00,
  `balance_remaining` decimal(10,2) NOT NULL DEFAULT 0.00,
  `start_month` int(2) NOT NULL,
  `start_year` int(4) NOT NULL,
  `status` enum('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `remarks` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supervisor_assignments`
--

CREATE TABLE `supervisor_assignments` (
  `assignment_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `supervisor_id` int(11) NOT NULL,
  `work_location_id` int(11) DEFAULT NULL,
  `assigned_from` date NOT NULL,
  `assigned_to` date DEFAULT NULL,
  `assigned_by` int(11) NOT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_invoices`
--

CREATE TABLE `supplier_invoices` (
  `si_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `si_no` varchar(50) NOT NULL,
  `si_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `vendor_id` int(11) NOT NULL COMMENT 'references companies.company_id',
  `po_id` int(11) DEFAULT NULL COMMENT 'optional link to purchase_orders',
  `vendor_invoice_no` varchar(100) DEFAULT NULL COMMENT 'suppliers own invoice number',
  `subtotal` decimal(12,2) DEFAULT 0.00,
  `gst_rate` decimal(5,2) DEFAULT 9.00,
  `gst_amount` decimal(12,2) DEFAULT 0.00,
  `total_amount` decimal(12,2) DEFAULT 0.00,
  `payment_status` enum('unpaid','partial','paid') DEFAULT 'unpaid',
  `paid_amount` decimal(12,2) DEFAULT 0.00,
  `payment_date` date DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL COMMENT 'e.g. Materials, Services, Equipment',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_invoice_items`
--

CREATE TABLE `supplier_invoice_items` (
  `sii_id` int(11) NOT NULL,
  `si_id` int(11) NOT NULL,
  `inventory_item_id` int(11) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `quantity` decimal(10,2) DEFAULT 1.00,
  `unit` varchar(30) DEFAULT NULL,
  `unit_cost` decimal(10,2) DEFAULT 0.00,
  `total_cost` decimal(12,2) DEFAULT 0.00,
  `item_order` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_invoice_payments`
--

CREATE TABLE `supplier_invoice_payments` (
  `payment_id` int(11) NOT NULL,
  `si_id` int(11) NOT NULL,
  `payment_date` date NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `tenant_directory`
--

CREATE TABLE `tenant_directory` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_code` varchar(30) NOT NULL COMMENT 'Short code the app user types, e.g. ACME01',
  `company_name` varchar(255) NOT NULL COMMENT 'Display name shown to the user while confirming',
  `api_base_url` varchar(255) NOT NULL COMMENT 'e.g. https://acmecorp.com/dgbiz/api',
  `web_base_url` varchar(255) NOT NULL COMMENT 'e.g. https://acmecorp.com/dgbiz',
  `status` enum('active','suspended') NOT NULL DEFAULT 'active',
  `notes` varchar(500) DEFAULT NULL COMMENT 'Internal notes only, never returned by the lookup API',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `full_name` varchar(100) NOT NULL,
  `role` enum('admin','hr','supervisor','employee','manager','accountant') NOT NULL DEFAULT 'employee',
  `is_active` tinyint(1) DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `username`, `email`, `password_hash`, `employee_id`, `full_name`, `role`, `is_active`, `last_login`, `created_at`, `updated_at`, `created_by`) VALUES
(1, 'admin', 'admin@example.com', '$2y$12$NQefrR3YRlc3KR/eOLKoK..ZlJcl7Z5urzq/KcKe7O72xVW/9L/be', NULL, 'Administrator', 'admin', 1, NULL, '2026-09-27 05:47:39', '2026-09-27 05:47:39', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `user_companies`
--

CREATE TABLE `user_companies` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `company_master_id` int(11) NOT NULL,
  `role` enum('owner','admin','manager','hr','accounts','viewer','user') NOT NULL DEFAULT 'user',
  `last_accessed` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_companies`
--

INSERT INTO `user_companies` (`id`, `user_id`, `company_master_id`, `role`, `last_accessed`, `created_at`) VALUES
(1, 1, 1, 'owner', NULL, '2026-09-27 05:47:40');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_log`
--
ALTER TABLE `activity_log`
  ADD PRIMARY KEY (`log_id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_timestamp` (`created_at`);

--
-- Indexes for table `attendance`
--
ALTER TABLE `attendance`
  ADD PRIMARY KEY (`attendance_id`),
  ADD UNIQUE KEY `unique_employee_date` (`employee_id`,`attendance_date`),
  ADD KEY `idx_employee_date` (`employee_id`,`attendance_date`),
  ADD KEY `idx_date` (`attendance_date`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_approval_status` (`approval_status`),
  ADD KEY `idx_att_company` (`company_master_id`);

--
-- Indexes for table `attendance_locations`
--
ALTER TABLE `attendance_locations`
  ADD PRIMARY KEY (`location_id`),
  ADD KEY `idx_company` (`company_master_id`);

--
-- Indexes for table `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`company_id`),
  ADD KEY `idx_company_master` (`company_master_id`);

--
-- Indexes for table `companies_master`
--
ALTER TABLE `companies_master`
  ADD PRIMARY KEY (`company_master_id`),
  ADD KEY `is_active` (`is_active`);

--
-- Indexes for table `company_cpf`
--
ALTER TABLE `company_cpf`
  ADD PRIMARY KEY (`cpf_id`),
  ADD KEY `idx_company_master_id` (`company_master_id`);

--
-- Indexes for table `cpf_rates`
--
ALTER TABLE `cpf_rates`
  ADD PRIMARY KEY (`cpf_rate_id`),
  ADD KEY `idx_status_age` (`residential_status`,`age_min`,`age_max`),
  ADD KEY `idx_effective_dates` (`effective_from`,`effective_to`),
  ADD KEY `idx_active` (`is_active`),
  ADD KEY `idx_residential_status` (`residential_status`,`effective_from`);

--
-- Indexes for table `cpf_rate_details`
--
ALTER TABLE `cpf_rate_details`
  ADD PRIMARY KEY (`rate_id`),
  ADD KEY `idx_version_table` (`version_id`,`table_code`),
  ADD KEY `idx_age_wage` (`age_min`,`age_max`,`wage_min`,`wage_max`);

--
-- Indexes for table `cpf_rate_versions`
--
ALTER TABLE `cpf_rate_versions`
  ADD PRIMARY KEY (`version_id`,`table_code`),
  ADD KEY `idx_effective_dates` (`effective_from`,`effective_to`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_table_code` (`table_code`);

--
-- Indexes for table `document_approvals`
--
ALTER TABLE `document_approvals`
  ADD PRIMARY KEY (`approval_id`),
  ADD UNIQUE KEY `token` (`token`),
  ADD KEY `idx_token` (`token`),
  ADD KEY `idx_document` (`document_id`,`document_type`),
  ADD KEY `idx_company` (`company_master_id`);

--
-- Indexes for table `document_type_config`
--
ALTER TABLE `document_type_config`
  ADD PRIMARY KEY (`config_id`),
  ADD UNIQUE KEY `uk_company_doctype` (`company_master_id`,`doc_type`),
  ADD KEY `idx_company` (`company_master_id`);

--
-- Indexes for table `email_log`
--
ALTER TABLE `email_log`
  ADD PRIMARY KEY (`log_id`),
  ADD KEY `idx_document` (`document_id`,`document_type`);

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`employee_id`),
  ADD KEY `idx_full_name` (`full_name`),
  ADD KEY `idx_department` (`department`),
  ADD KEY `idx_residential_status` (`residential_status`),
  ADD KEY `idx_active` (`is_active`),
  ADD KEY `idx_nric` (`nric_no`),
  ADD KEY `idx_fin` (`fin_no`),
  ADD KEY `idx_salary_type` (`salary_type`),
  ADD KEY `idx_emp_company` (`company_master_id`),
  ADD KEY `idx_wp_expiry` (`work_pass_expiry`),
  ADD KEY `idx_sc_expiry` (`safety_course_expiry`),
  ADD KEY `idx_dl_expiry` (`driving_licence_expiry`);

--
-- Indexes for table `employee_history`
--
ALTER TABLE `employee_history`
  ADD PRIMARY KEY (`history_id`),
  ADD KEY `idx_employee` (`employee_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_effective` (`effective_from`,`effective_to`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`expense_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_date` (`expense_date`),
  ADD KEY `idx_category` (`category_id`);

--
-- Indexes for table `expense_categories`
--
ALTER TABLE `expense_categories`
  ADD PRIMARY KEY (`category_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_type` (`category_type`);

--
-- Indexes for table `expense_claims`
--
ALTER TABLE `expense_claims`
  ADD PRIMARY KEY (`claim_id`),
  ADD KEY `idx_emp` (`employee_id`,`company_master_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `financial_documents`
--
ALTER TABLE `financial_documents`
  ADD PRIMARY KEY (`document_id`),
  ADD UNIQUE KEY `document_no` (`document_no`),
  ADD KEY `idx_document_type` (`document_type`),
  ADD KEY `idx_document_no` (`document_no`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_reference` (`reference_doc_type`,`reference_doc_id`),
  ADD KEY `idx_date` (`document_date`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_company_master_id` (`company_master_id`),
  ADD KEY `fk_financial_documents_company` (`company_id`),
  ADD KEY `idx_parent_document` (`parent_document_id`);

--
-- Indexes for table `financial_document_items`
--
ALTER TABLE `financial_document_items`
  ADD PRIMARY KEY (`item_id`),
  ADD KEY `idx_document` (`document_id`),
  ADD KEY `idx_module_header` (`is_module_header`),
  ADD KEY `idx_item_order` (`item_order`);

--
-- Indexes for table `financial_document_payments`
--
ALTER TABLE `financial_document_payments`
  ADD PRIMARY KEY (`payment_id`),
  ADD KEY `idx_document` (`document_id`),
  ADD KEY `idx_company` (`company_master_id`);

--
-- Indexes for table `foreign_worker_levy`
--
ALTER TABLE `foreign_worker_levy`
  ADD PRIMARY KEY (`levy_id`),
  ADD UNIQUE KEY `uniq_emp_month` (`employee_id`,`levy_month`,`levy_year`),
  ADD KEY `idx_company_month` (`company_master_id`,`levy_month`,`levy_year`);

--
-- Indexes for table `fwl_rates`
--
ALTER TABLE `fwl_rates`
  ADD PRIMARY KEY (`rate_id`),
  ADD KEY `idx_lookup` (`company_master_id`,`quota_sector`,`levy_tier`,`dependency_tier`,`effective_from`);

--
-- Indexes for table `gst_rates`
--
ALTER TABLE `gst_rates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_effective` (`effective_from`,`effective_to`);

--
-- Indexes for table `inventory_items`
--
ALTER TABLE `inventory_items`
  ADD PRIMARY KEY (`item_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_sku` (`sku`),
  ADD KEY `idx_category` (`category`);

--
-- Indexes for table `inventory_transactions`
--
ALTER TABLE `inventory_transactions`
  ADD PRIMARY KEY (`transaction_id`),
  ADD KEY `idx_item` (`item_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_date` (`transaction_date`);

--
-- Indexes for table `leave_applications`
--
ALTER TABLE `leave_applications`
  ADD PRIMARY KEY (`leave_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_employee` (`employee_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_dates` (`start_date`,`end_date`);

--
-- Indexes for table `leave_entitlements`
--
ALTER TABLE `leave_entitlements`
  ADD PRIMARY KEY (`entitlement_id`),
  ADD UNIQUE KEY `unique_entitlement` (`employee_id`,`leave_type`,`year`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_employee` (`employee_id`),
  ADD KEY `idx_year` (`year`);

--
-- Indexes for table `leave_policies`
--
ALTER TABLE `leave_policies`
  ADD PRIMARY KEY (`policy_id`),
  ADD UNIQUE KEY `unique_policy` (`company_master_id`,`leave_type`),
  ADD KEY `idx_company` (`company_master_id`);

--
-- Indexes for table `loan_repayments`
--
ALTER TABLE `loan_repayments`
  ADD PRIMARY KEY (`repayment_id`),
  ADD KEY `idx_loan` (`loan_id`),
  ADD KEY `idx_payroll` (`payroll_id`),
  ADD KEY `idx_employee` (`employee_id`);

--
-- Indexes for table `login_attempts`
--
ALTER TABLE `login_attempts`
  ADD PRIMARY KEY (`attempt_id`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_ip` (`ip_address`),
  ADD KEY `idx_timestamp` (`attempted_at`);

--
-- Indexes for table `overtime`
--
ALTER TABLE `overtime`
  ADD PRIMARY KEY (`overtime_id`),
  ADD UNIQUE KEY `unique_emp_date` (`employee_id`,`overtime_date`),
  ADD KEY `idx_employee_date` (`employee_id`,`overtime_date`),
  ADD KEY `idx_date` (`overtime_date`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_day_type` (`day_type`),
  ADD KEY `idx_ot_company` (`company_master_id`);

--
-- Indexes for table `payment_terms`
--
ALTER TABLE `payment_terms`
  ADD PRIMARY KEY (`term_id`),
  ADD KEY `quotation_id` (`quotation_id`);

--
-- Indexes for table `payroll`
--
ALTER TABLE `payroll`
  ADD PRIMARY KEY (`payroll_id`),
  ADD UNIQUE KEY `unique_employee_period` (`employee_id`,`pay_period_year`,`pay_period_month`),
  ADD KEY `idx_period` (`pay_period_year`,`pay_period_month`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_pay_company` (`company_master_id`);

--
-- Indexes for table `payroll_adjustments`
--
ALTER TABLE `payroll_adjustments`
  ADD PRIMARY KEY (`adjustment_id`),
  ADD UNIQUE KEY `uniq_emp_period` (`employee_id`,`pay_period_year`,`pay_period_month`,`adjustment_type`),
  ADD KEY `idx_period` (`company_master_id`,`pay_period_year`,`pay_period_month`);

--
-- Indexes for table `public_holidays`
--
ALTER TABLE `public_holidays`
  ADD PRIMARY KEY (`holiday_id`),
  ADD UNIQUE KEY `holiday_date` (`holiday_date`),
  ADD KEY `idx_date` (`holiday_date`),
  ADD KEY `idx_year` (`year`);

--
-- Indexes for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD PRIMARY KEY (`po_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_vendor` (`vendor_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD PRIMARY KEY (`poi_id`),
  ADD KEY `idx_po` (`po_id`),
  ADD KEY `idx_item` (`inventory_item_id`);

--
-- Indexes for table `purchase_receipts`
--
ALTER TABLE `purchase_receipts`
  ADD PRIMARY KEY (`receipt_id`),
  ADD KEY `idx_po` (`po_id`),
  ADD KEY `idx_company` (`company_master_id`);

--
-- Indexes for table `purchase_receipt_items`
--
ALTER TABLE `purchase_receipt_items`
  ADD PRIMARY KEY (`ri_id`),
  ADD KEY `idx_receipt` (`receipt_id`);

--
-- Indexes for table `shg_rates`
--
ALTER TABLE `shg_rates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `version_id` (`version_id`);

--
-- Indexes for table `shg_rate_versions`
--
ALTER TABLE `shg_rate_versions`
  ADD PRIMARY KEY (`version_id`);

--
-- Indexes for table `staff_loans`
--
ALTER TABLE `staff_loans`
  ADD PRIMARY KEY (`loan_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_employee` (`employee_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `supervisor_assignments`
--
ALTER TABLE `supervisor_assignments`
  ADD PRIMARY KEY (`assignment_id`),
  ADD KEY `idx_emp` (`employee_id`),
  ADD KEY `idx_sup` (`supervisor_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_date_range` (`supervisor_id`,`assigned_from`,`assigned_to`),
  ADD KEY `idx_emp_date` (`employee_id`,`assigned_from`,`assigned_to`);

--
-- Indexes for table `supplier_invoices`
--
ALTER TABLE `supplier_invoices`
  ADD PRIMARY KEY (`si_id`),
  ADD KEY `idx_company` (`company_master_id`),
  ADD KEY `idx_vendor` (`vendor_id`),
  ADD KEY `idx_po` (`po_id`),
  ADD KEY `idx_status` (`payment_status`),
  ADD KEY `idx_date` (`si_date`);

--
-- Indexes for table `supplier_invoice_items`
--
ALTER TABLE `supplier_invoice_items`
  ADD PRIMARY KEY (`sii_id`),
  ADD KEY `idx_si` (`si_id`),
  ADD KEY `idx_item` (`inventory_item_id`);

--
-- Indexes for table `supplier_invoice_payments`
--
ALTER TABLE `supplier_invoice_payments`
  ADD PRIMARY KEY (`payment_id`),
  ADD KEY `idx_si` (`si_id`);

--
-- Indexes for table `tenant_directory`
--
ALTER TABLE `tenant_directory`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_company_code` (`company_code`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_role` (`role`),
  ADD KEY `idx_active` (`is_active`),
  ADD KEY `idx_employee_id` (`employee_id`);

--
-- Indexes for table `user_companies`
--
ALTER TABLE `user_companies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_company` (`user_id`,`company_master_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `company_master_id` (`company_master_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_log`
--
ALTER TABLE `activity_log`
  MODIFY `log_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `attendance`
--
ALTER TABLE `attendance`
  MODIFY `attendance_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `attendance_locations`
--
ALTER TABLE `attendance_locations`
  MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `companies`
--
ALTER TABLE `companies`
  MODIFY `company_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `companies_master`
--
ALTER TABLE `companies_master`
  MODIFY `company_master_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `company_cpf`
--
ALTER TABLE `company_cpf`
  MODIFY `cpf_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cpf_rates`
--
ALTER TABLE `cpf_rates`
  MODIFY `cpf_rate_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=49;

--
-- AUTO_INCREMENT for table `cpf_rate_details`
--
ALTER TABLE `cpf_rate_details`
  MODIFY `rate_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=208;

--
-- AUTO_INCREMENT for table `document_approvals`
--
ALTER TABLE `document_approvals`
  MODIFY `approval_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `document_type_config`
--
ALTER TABLE `document_type_config`
  MODIFY `config_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `email_log`
--
ALTER TABLE `email_log`
  MODIFY `log_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `employee_history`
--
ALTER TABLE `employee_history`
  MODIFY `history_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
  MODIFY `expense_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expense_categories`
--
ALTER TABLE `expense_categories`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

--
-- AUTO_INCREMENT for table `expense_claims`
--
ALTER TABLE `expense_claims`
  MODIFY `claim_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_documents`
--
ALTER TABLE `financial_documents`
  MODIFY `document_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_document_items`
--
ALTER TABLE `financial_document_items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_document_payments`
--
ALTER TABLE `financial_document_payments`
  MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `foreign_worker_levy`
--
ALTER TABLE `foreign_worker_levy`
  MODIFY `levy_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fwl_rates`
--
ALTER TABLE `fwl_rates`
  MODIFY `rate_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `gst_rates`
--
ALTER TABLE `gst_rates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `inventory_items`
--
ALTER TABLE `inventory_items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inventory_transactions`
--
ALTER TABLE `inventory_transactions`
  MODIFY `transaction_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leave_applications`
--
ALTER TABLE `leave_applications`
  MODIFY `leave_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leave_entitlements`
--
ALTER TABLE `leave_entitlements`
  MODIFY `entitlement_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leave_policies`
--
ALTER TABLE `leave_policies`
  MODIFY `policy_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `loan_repayments`
--
ALTER TABLE `loan_repayments`
  MODIFY `repayment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `login_attempts`
--
ALTER TABLE `login_attempts`
  MODIFY `attempt_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `overtime`
--
ALTER TABLE `overtime`
  MODIFY `overtime_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payment_terms`
--
ALTER TABLE `payment_terms`
  MODIFY `term_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payroll`
--
ALTER TABLE `payroll`
  MODIFY `payroll_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payroll_adjustments`
--
ALTER TABLE `payroll_adjustments`
  MODIFY `adjustment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `public_holidays`
--
ALTER TABLE `public_holidays`
  MODIFY `holiday_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  MODIFY `po_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  MODIFY `poi_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_receipts`
--
ALTER TABLE `purchase_receipts`
  MODIFY `receipt_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_receipt_items`
--
ALTER TABLE `purchase_receipt_items`
  MODIFY `ri_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `shg_rates`
--
ALTER TABLE `shg_rates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57;

--
-- AUTO_INCREMENT for table `shg_rate_versions`
--
ALTER TABLE `shg_rate_versions`
  MODIFY `version_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `staff_loans`
--
ALTER TABLE `staff_loans`
  MODIFY `loan_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supervisor_assignments`
--
ALTER TABLE `supervisor_assignments`
  MODIFY `assignment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_invoices`
--
ALTER TABLE `supplier_invoices`
  MODIFY `si_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_invoice_items`
--
ALTER TABLE `supplier_invoice_items`
  MODIFY `sii_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_invoice_payments`
--
ALTER TABLE `supplier_invoice_payments`
  MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_directory`
--
ALTER TABLE `tenant_directory`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `user_companies`
--
ALTER TABLE `user_companies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_log`
--
ALTER TABLE `activity_log`
  ADD CONSTRAINT `activity_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE;

--
-- Constraints for table `cpf_rate_details`
--
ALTER TABLE `cpf_rate_details`
  ADD CONSTRAINT `cpf_rate_details_ibfk_1` FOREIGN KEY (`version_id`,`table_code`) REFERENCES `cpf_rate_versions` (`version_id`, `table_code`) ON DELETE CASCADE;

--
-- Constraints for table `expenses`
--
ALTER TABLE `expenses`
  ADD CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`category_id`) ON DELETE SET NULL;

--
-- Constraints for table `financial_documents`
--
ALTER TABLE `financial_documents`
  ADD CONSTRAINT `fk_financial_documents_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `financial_document_items`
--
ALTER TABLE `financial_document_items`
  ADD CONSTRAINT `financial_document_items_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `financial_documents` (`document_id`) ON DELETE CASCADE;

--
-- Constraints for table `financial_document_payments`
--
ALTER TABLE `financial_document_payments`
  ADD CONSTRAINT `fk_fdp_document` FOREIGN KEY (`document_id`) REFERENCES `financial_documents` (`document_id`) ON DELETE CASCADE;

--
-- Constraints for table `inventory_transactions`
--
ALTER TABLE `inventory_transactions`
  ADD CONSTRAINT `inventory_transactions_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`item_id`) ON DELETE CASCADE;

--
-- Constraints for table `overtime`
--
ALTER TABLE `overtime`
  ADD CONSTRAINT `overtime_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE;

--
-- Constraints for table `payroll`
--
ALTER TABLE `payroll`
  ADD CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE;

--
-- Constraints for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD CONSTRAINT `purchase_order_items_ibfk_1` FOREIGN KEY (`po_id`) REFERENCES `purchase_orders` (`po_id`) ON DELETE CASCADE;

--
-- Constraints for table `purchase_receipts`
--
ALTER TABLE `purchase_receipts`
  ADD CONSTRAINT `purchase_receipts_ibfk_1` FOREIGN KEY (`po_id`) REFERENCES `purchase_orders` (`po_id`);

--
-- Constraints for table `purchase_receipt_items`
--
ALTER TABLE `purchase_receipt_items`
  ADD CONSTRAINT `purchase_receipt_items_ibfk_1` FOREIGN KEY (`receipt_id`) REFERENCES `purchase_receipts` (`receipt_id`) ON DELETE CASCADE;

--
-- Constraints for table `shg_rates`
--
ALTER TABLE `shg_rates`
  ADD CONSTRAINT `shg_rates_ibfk_1` FOREIGN KEY (`version_id`) REFERENCES `shg_rate_versions` (`version_id`);

--
-- Constraints for table `supplier_invoice_items`
--
ALTER TABLE `supplier_invoice_items`
  ADD CONSTRAINT `supplier_invoice_items_ibfk_1` FOREIGN KEY (`si_id`) REFERENCES `supplier_invoices` (`si_id`) ON DELETE CASCADE;

--
-- Constraints for table `supplier_invoice_payments`
--
ALTER TABLE `supplier_invoice_payments`
  ADD CONSTRAINT `supplier_invoice_payments_ibfk_1` FOREIGN KEY (`si_id`) REFERENCES `supplier_invoices` (`si_id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
