-- Update Employee Master Sheet table to include all 18 columns
-- Run this if you already have the employee_master_sheet table

-- Drop the old table and recreate (backup data first if needed!)
DROP TABLE IF EXISTS `employee_master_sheet`;

-- Create the complete table with all 18 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;

