-- Migration: Add Payroll Excel Structure Tables
-- Date: 2025-12-15
-- Description: Creates all tables needed to match Payroll.xlsx Excel file structure

-- Employee Master Sheet (Excel Structure - Complete with all columns)
CREATE TABLE IF NOT EXISTS `employee_master_sheet` (
  `master_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11),
  `sl_no` int(11),
  `emp_id` varchar(50),
  `employee_name` varchar(255),
  `category` varchar(100),
  `bank_ac_no` varchar(100),
  `bank_name` varchar(255),
  `department` varchar(100),
  `designation` varchar(255),
  `site` varchar(255),
  `contact_number` varchar(50),
  `fathers_name` varchar(255),
  `date_of_birth` date,
  `date_of_joining` date,
  `date_of_leaving` date,
  `reason_for_leaving` text,
  `pf_number` varchar(50),
  `uan_number` varchar(50),
  `insurance_number` varchar(50),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`master_id`),
  KEY `fk_ems_site` (`site_id`),
  KEY `fk_ems_emp` (`employee_id`),
  KEY `idx_emp_id` (`emp_id`),
  KEY `idx_employee_name` (`employee_name`),
  CONSTRAINT `fk_ems_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ems_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New Joinee Statutory (Excel Structure)
CREATE TABLE IF NOT EXISTS `new_joinee_statutory` (
  `joinee_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11),
  `sl_no` int(11),
  `emp_id` varchar(50),
  `name` varchar(255),
  `designation` varchar(255),
  `category` varchar(100),
  `department` varchar(100),
  `employee_type` varchar(50),
  `gender` enum('Male','Female','Other'),
  `dob` date,
  `doj` date,
  `esic_no` varchar(50),
  `uan_no` varchar(50),
  `pan_no` varchar(50),
  `aadhar_num` varchar(50),
  `basic_salary` decimal(15,2) DEFAULT 0.00,
  `hra` decimal(15,2) DEFAULT 0.00,
  `da` decimal(15,2) DEFAULT 0.00,
  `special_allowances` decimal(15,2) DEFAULT 0.00,
  `ta` decimal(15,2) DEFAULT 0.00,
  `earn_total` decimal(15,2) DEFAULT 0.00,
  `contribution_to_pf` decimal(15,2) DEFAULT 0.00,
  `esic` decimal(15,2) DEFAULT 0.00,
  `profession_tax` decimal(15,2) DEFAULT 0.00,
  `tds` decimal(15,2) DEFAULT 0.00,
  `deduction_total` decimal(15,2) DEFAULT 0.00,
  `net_payable` decimal(15,2) DEFAULT 0.00,
  `pay_mode` varchar(50),
  `bank_mode` varchar(50),
  `bank_name` varchar(255),
  `bank_ac_no` varchar(100),
  `ifsc` varchar(50),
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `remarks` text,
  `month` varchar(20),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`joinee_id`),
  KEY `fk_njs_site` (`site_id`),
  KEY `fk_njs_emp` (`employee_id`),
  CONSTRAINT `fk_njs_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_njs_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee Transfer (Excel Structure)
CREATE TABLE IF NOT EXISTS `employee_transfer` (
  `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11),
  `sl_no` int(11),
  `emp_id` varchar(50),
  `name` varchar(255),
  `designation` varchar(255),
  `category` varchar(100),
  `transfer_from_site_name` varchar(255),
  `transfer_from_location` varchar(255),
  `transfer_to_site_name` varchar(255),
  `transfer_to_location` varchar(255),
  `wef` date COMMENT 'With Effect From',
  `effective_branch` varchar(255),
  `effective_attendance` varchar(255),
  `esi_applicability` varchar(50),
  `transfer_request_form_filled` enum('Yes','No') DEFAULT 'No',
  `approved_by_district_head` enum('Yes','No') DEFAULT 'No',
  `remarks` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transfer_id`),
  KEY `fk_et_site` (`site_id`),
  KEY `fk_et_emp` (`employee_id`),
  CONSTRAINT `fk_et_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_et_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Resigned & Abscondee (Excel Structure)
CREATE TABLE IF NOT EXISTS `resigned_abscondee` (
  `exit_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11),
  `sl_no` int(11),
  `emp_id` varchar(50),
  `name` varchar(255),
  `designation` varchar(255),
  `category` varchar(100),
  `resigned_abscondee` enum('Resigned','Abscondee') DEFAULT 'Resigned',
  `site_name` varchar(255),
  `location` varchar(255),
  `last_date_of_working` date,
  `resignation_submitted` date,
  `notice_period_served` varchar(50),
  `remark` text,
  `month` varchar(20),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`exit_id`),
  KEY `fk_ra_site` (`site_id`),
  KEY `fk_ra_emp` (`employee_id`),
  CONSTRAINT `fk_ra_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ra_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance Monthly Grid (Excel Structure - Daily Attendance)
CREATE TABLE IF NOT EXISTS `attendance_monthly_grid` (
  `attendance_grid_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `attendance_month` varchar(20) NOT NULL COMMENT 'Format: Jan-26',
  `day_1` varchar(10) DEFAULT NULL,
  `day_2` varchar(10) DEFAULT NULL,
  `day_3` varchar(10) DEFAULT NULL,
  `day_4` varchar(10) DEFAULT NULL,
  `day_5` varchar(10) DEFAULT NULL,
  `day_6` varchar(10) DEFAULT NULL,
  `day_7` varchar(10) DEFAULT NULL,
  `day_8` varchar(10) DEFAULT NULL,
  `day_9` varchar(10) DEFAULT NULL,
  `day_10` varchar(10) DEFAULT NULL,
  `day_11` varchar(10) DEFAULT NULL,
  `day_12` varchar(10) DEFAULT NULL,
  `day_13` varchar(10) DEFAULT NULL,
  `day_14` varchar(10) DEFAULT NULL,
  `day_15` varchar(10) DEFAULT NULL,
  `day_16` varchar(10) DEFAULT NULL,
  `day_17` varchar(10) DEFAULT NULL,
  `day_18` varchar(10) DEFAULT NULL,
  `day_19` varchar(10) DEFAULT NULL,
  `day_20` varchar(10) DEFAULT NULL,
  `day_21` varchar(10) DEFAULT NULL,
  `day_22` varchar(10) DEFAULT NULL,
  `day_23` varchar(10) DEFAULT NULL,
  `day_24` varchar(10) DEFAULT NULL,
  `day_25` varchar(10) DEFAULT NULL,
  `day_26` varchar(10) DEFAULT NULL,
  `day_27` varchar(10) DEFAULT NULL,
  `day_28` varchar(10) DEFAULT NULL,
  `day_29` varchar(10) DEFAULT NULL,
  `day_30` varchar(10) DEFAULT NULL,
  `day_31` varchar(10) DEFAULT NULL,
  `present_count` int(11) DEFAULT 0,
  `half_day_count` int(11) DEFAULT 0,
  `cl_count` int(11) DEFAULT 0 COMMENT 'Casual Leave',
  `pl_count` int(11) DEFAULT 0 COMMENT 'Paid Leave',
  `sl_count` int(11) DEFAULT 0 COMMENT 'Sick Leave',
  `absent_count` int(11) DEFAULT 0,
  `wo_count` int(11) DEFAULT 0 COMMENT 'Weekly Off',
  `remarks` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`attendance_grid_id`),
  UNIQUE KEY `unique_emp_month` (`employee_id`,`attendance_month`),
  KEY `fk_amg_site` (`site_id`),
  KEY `fk_amg_emp` (`employee_id`),
  CONSTRAINT `fk_amg_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_amg_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Overtime Monthly Grid (Excel Structure)
CREATE TABLE IF NOT EXISTS `overtime_monthly_grid` (
  `overtime_grid_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `attendance_month` varchar(20) NOT NULL COMMENT 'Format: Jan-26',
  `emp_id` varchar(50),
  `designation` varchar(255),
  `day_1` decimal(5,2) DEFAULT 0.00,
  `day_2` decimal(5,2) DEFAULT 0.00,
  `day_3` decimal(5,2) DEFAULT 0.00,
  `day_4` decimal(5,2) DEFAULT 0.00,
  `day_5` decimal(5,2) DEFAULT 0.00,
  `day_6` decimal(5,2) DEFAULT 0.00,
  `day_7` decimal(5,2) DEFAULT 0.00,
  `day_8` decimal(5,2) DEFAULT 0.00,
  `day_9` decimal(5,2) DEFAULT 0.00,
  `day_10` decimal(5,2) DEFAULT 0.00,
  `day_11` decimal(5,2) DEFAULT 0.00,
  `day_12` decimal(5,2) DEFAULT 0.00,
  `day_13` decimal(5,2) DEFAULT 0.00,
  `day_14` decimal(5,2) DEFAULT 0.00,
  `day_15` decimal(5,2) DEFAULT 0.00,
  `day_16` decimal(5,2) DEFAULT 0.00,
  `day_17` decimal(5,2) DEFAULT 0.00,
  `day_18` decimal(5,2) DEFAULT 0.00,
  `day_19` decimal(5,2) DEFAULT 0.00,
  `day_20` decimal(5,2) DEFAULT 0.00,
  `day_21` decimal(5,2) DEFAULT 0.00,
  `day_22` decimal(5,2) DEFAULT 0.00,
  `day_23` decimal(5,2) DEFAULT 0.00,
  `day_24` decimal(5,2) DEFAULT 0.00,
  `day_25` decimal(5,2) DEFAULT 0.00,
  `day_26` decimal(5,2) DEFAULT 0.00,
  `day_27` decimal(5,2) DEFAULT 0.00,
  `day_28` decimal(5,2) DEFAULT 0.00,
  `day_29` decimal(5,2) DEFAULT 0.00,
  `day_30` decimal(5,2) DEFAULT 0.00,
  `day_31` decimal(5,2) DEFAULT 0.00,
  `overtime_rate` decimal(10,2) DEFAULT 0.00,
  `earn_amount` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`overtime_grid_id`),
  UNIQUE KEY `unique_emp_ot_month` (`employee_id`,`attendance_month`),
  KEY `fk_omg_site` (`site_id`),
  KEY `fk_omg_emp` (`employee_id`),
  CONSTRAINT `fk_omg_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_omg_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories Master (for dropdowns)
CREATE TABLE IF NOT EXISTS `categories_master` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `category_code` varchar(50) NOT NULL,
  `category_name` varchar(255) NOT NULL,
  `category_status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique_site_cat_code` (`site_id`,`category_code`),
  KEY `fk_cat_site` (`site_id`),
  CONSTRAINT `fk_cat_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Regions Table (for site regions)
-- Check if column exists before adding
SET @dbname = DATABASE();
SET @tablename = 'sites';
SET @columnname = 'region';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' varchar(100) DEFAULT NULL AFTER site_name')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

