-- Migration: Create resigned_abscondee table
-- Date: 2025-01-XX
-- Description: Creates table for Resigned & Abscondee data entry with month/year wise submission

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) DEFAULT NULL,
  `sl_no` int(11) DEFAULT NULL,
  `emp_id` varchar(50) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `resigned_abscondee` enum('Resigned','Abscondee') DEFAULT 'Resigned',
  `site_name` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `last_date_of_working` date DEFAULT NULL,
  `resignation_submitted` date DEFAULT NULL,
  `notice_period_served` varchar(50) DEFAULT NULL,
  `remark` text DEFAULT NULL,
  `month` varchar(20) DEFAULT NULL,
  `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`),
  KEY `idx_month` (`month`),
  KEY `idx_site_month` (`site_id`, `month`),
  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;

