-- Migration: Add employee_master table
-- Date: 2025-12-15
-- Description: Creates employee_master table with all Excel fields including Emp ID, Name, Dept, Designation, Category, Bank details, PF/ESIC/UAN, and Status

-- Employee Master Table (Comprehensive employee data)
CREATE TABLE IF NOT EXISTS `employee_master` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `first_name` varchar(100),
  `middle_name` varchar(100),
  `last_name` varchar(100),
  `department_id` int(11),
  `designation_id` int(11),
  `category_id` int(11),
  `bank_name` varchar(255),
  `bank_account_number` varchar(100),
  `bank_ifsc` varchar(50),
  `bank_branch` varchar(255),
  `pf_number` varchar(50),
  `esic_number` varchar(50),
  `uan_number` varchar(50),
  `status` enum('active','inactive','terminated','resigned') DEFAULT 'active',
  `date_of_joining` date,
  `date_of_birth` date,
  `email` varchar(255),
  `phone` varchar(50),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `unique_employee_code` (`employee_code`),
  KEY `idx_name` (`name`),
  KEY `idx_department` (`department_id`),
  KEY `idx_designation` (`designation_id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_status` (`status`),
  KEY `idx_employee_code` (`employee_code`),
  CONSTRAINT `fk_emp_master_dept` FOREIGN KEY (`department_id`) REFERENCES `departments_master` (`department_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emp_master_desig` FOREIGN KEY (`designation_id`) REFERENCES `designations_master` (`designation_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emp_master_category` FOREIGN KEY (`category_id`) REFERENCES `categories_master` (`category_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

