-- =====================================================
-- Salary Components Migration
-- =====================================================
-- This migration creates salary_components and employee_salary_structure tables
-- Run this file to enable salary component management
-- =====================================================

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

-- Step 2: Create employee_salary_structure table if it doesn't exist
CREATE TABLE IF NOT EXISTS `employee_salary_structure` (
  `structure_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `component_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `structure_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`structure_id`),
  KEY `fk_struct_site` (`site_id`),
  KEY `fk_struct_emp` (`employee_id`),
  KEY `fk_struct_comp` (`component_id`),
  KEY `idx_employee_status` (`employee_id`, `structure_status`),
  KEY `idx_employee_component` (`employee_id`, `component_id`, `structure_status`),
  CONSTRAINT `fk_struct_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_struct_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_struct_comp` FOREIGN KEY (`component_id`) REFERENCES `salary_components` (`component_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Insert default salary components for all existing sites
-- Earnings Components
INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'BASIC',
    'Basic Salary',
    'earning',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'BASIC'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'HRA',
    'House Rent Allowance',
    'earning',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'HRA'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'DA',
    'Dearness Allowance',
    'earning',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'DA'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'SPECIAL_ALLOWANCE',
    'Special Allowance',
    'earning',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'SPECIAL_ALLOWANCE'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'TA',
    'Travel Allowance',
    'earning',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'TA'
);

-- Deduction Components
INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'PF',
    'Provident Fund',
    'deduction',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'PF'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'ESIC',
    'Employee State Insurance Corporation',
    'deduction',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'ESIC'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'PROFESSIONAL_TAX',
    'Professional Tax',
    'deduction',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'PROFESSIONAL_TAX'
);

INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`)
SELECT 
    s.site_id,
    'TDS',
    'Tax Deducted at Source',
    'deduction',
    'fixed',
    'active'
FROM `sites` s
WHERE NOT EXISTS (
    SELECT 1 FROM `salary_components` sc 
    WHERE sc.site_id = s.site_id AND sc.component_code = 'TDS'
);

-- Step 4: Migrate existing salary data from employees table to employee_salary_structure
-- This will create BASIC salary component entries for employees who have salary > 0
INSERT IGNORE INTO `employee_salary_structure` (`site_id`, `employee_id`, `component_id`, `amount`, `effective_from`, `structure_status`)
SELECT 
    e.site_id,
    e.employee_id,
    sc.component_id,
    COALESCE(e.salary, 0),
    COALESCE(e.date_of_joining, CURDATE()),
    'active'
FROM `employees` e
INNER JOIN `salary_components` sc ON sc.site_id = e.site_id AND sc.component_code = 'BASIC'
WHERE e.salary > 0
AND NOT EXISTS (
    SELECT 1 FROM `employee_salary_structure` ess 
    WHERE ess.employee_id = e.employee_id 
    AND ess.component_id = sc.component_id 
    AND ess.structure_status = 'active'
);

-- Migration Complete
SELECT 'Salary components migration completed successfully!' AS status;
SELECT COUNT(*) AS total_components FROM `salary_components`;
SELECT COUNT(*) AS total_structures FROM `employee_salary_structure`;

