-- =====================================================
-- Salary Components Migration - Direct SQL
-- =====================================================
-- Run this file directly in phpMyAdmin or MySQL client
-- This will create tables and insert default components
-- =====================================================

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

-- Create employee_salary_structure table
CREATE TABLE IF NOT EXISTS `employee_salary_structure` (
  `structure_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `component_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default components for site_id = 1 (Main Office)
-- You can change site_id as needed
SET @site_id = 1;

-- Earnings
INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`) VALUES
(@site_id, 'BASIC', 'Basic Salary', 'earning', 'fixed', 'active'),
(@site_id, 'HRA', 'House Rent Allowance', 'earning', 'fixed', 'active'),
(@site_id, 'DA', 'Dearness Allowance', 'earning', 'fixed', 'active'),
(@site_id, 'SPECIAL_ALLOWANCE', 'Special Allowance', 'earning', 'fixed', 'active'),
(@site_id, 'TA', 'Travel Allowance', 'earning', 'fixed', 'active');

-- Deductions
INSERT IGNORE INTO `salary_components` (`site_id`, `component_code`, `component_name`, `component_type`, `calculation_type`, `component_status`) VALUES
(@site_id, 'PF', 'Provident Fund', 'deduction', 'fixed', 'active'),
(@site_id, 'ESIC', 'Employee State Insurance Corporation', 'deduction', 'fixed', 'active'),
(@site_id, 'PROFESSIONAL_TAX', 'Professional Tax', 'deduction', 'fixed', 'active'),
(@site_id, 'TDS', 'Tax Deducted at Source', 'deduction', 'fixed', 'active');

-- Show results
SELECT 'Migration completed!' AS status;
SELECT COUNT(*) AS total_components FROM `salary_components` WHERE site_id = @site_id;
SELECT component_code, component_name, component_type FROM `salary_components` WHERE site_id = @site_id ORDER BY component_type, component_code;

