-- ============================================
-- ENTERPRISE HRM ERP - EXTENDED SCHEMA
-- Additional Modules: Overtime, Petty Cash, DFC, MIS, Transfer, Resigned
-- ============================================

-- ============================================
-- OVERTIME MODULE
-- ============================================

-- Overtime Records Table
CREATE TABLE IF NOT EXISTS `overtime_records` (
  `overtime_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `overtime_date` date NOT NULL,
  `overtime_hours` decimal(5,2) NOT NULL DEFAULT 0.00,
  `overtime_rate` decimal(10,2) DEFAULT 0.00,
  `overtime_amount` decimal(15,2) DEFAULT 0.00,
  `remarks` text,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`overtime_id`),
  UNIQUE KEY `unique_emp_date_ot` (`employee_id`, `overtime_date`),
  KEY `fk_ot_site` (`site_id`),
  KEY `fk_ot_emp` (`employee_id`),
  KEY `fk_ot_created_by` (`created_by`),
  KEY `fk_ot_updated_by` (`updated_by`),
  KEY `fk_ot_locked_by` (`locked_by`),
  KEY `idx_month_year` (`month`, `year`),
  CONSTRAINT `fk_ot_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ot_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ot_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ot_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ot_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Overtime Rate Configuration (Site-wise, Dynamic)
CREATE TABLE IF NOT EXISTS `overtime_rates` (
  `rate_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `rate_name` varchar(255) NOT NULL,
  `rate_multiplier` decimal(5,2) DEFAULT 1.50,
  `effective_from` date NOT NULL,
  `effective_to` date,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rate_id`),
  KEY `fk_ot_rate_site` (`site_id`),
  CONSTRAINT `fk_ot_rate_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- PETTY CASH MODULE
-- ============================================

-- Petty Cash Transactions Table
CREATE TABLE IF NOT EXISTS `petty_cash_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `transaction_date` date NOT NULL,
  `transaction_type` enum('cash_in','cash_out') NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `expense_head_id` int(11),
  `description` text,
  `voucher_number` varchar(100),
  `denomination_500` int(11) DEFAULT 0,
  `denomination_200` int(11) DEFAULT 0,
  `denomination_100` int(11) DEFAULT 0,
  `denomination_50` int(11) DEFAULT 0,
  `denomination_20` int(11) DEFAULT 0,
  `denomination_10` int(11) DEFAULT 0,
  `denomination_5` int(11) DEFAULT 0,
  `denomination_2` int(11) DEFAULT 0,
  `denomination_1` int(11) DEFAULT 0,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `fk_pc_site` (`site_id`),
  KEY `fk_pc_expense_head` (`expense_head_id`),
  KEY `fk_pc_created_by` (`created_by`),
  KEY `fk_pc_updated_by` (`updated_by`),
  KEY `fk_pc_locked_by` (`locked_by`),
  KEY `idx_pc_date` (`transaction_date`),
  KEY `idx_pc_month_year` (`month`, `year`),
  CONSTRAINT `fk_pc_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_pc_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pc_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pc_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expense Heads (Dynamic)
CREATE TABLE IF NOT EXISTS `expense_heads` (
  `expense_head_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `expense_head_code` varchar(50) NOT NULL,
  `expense_head_name` varchar(255) NOT NULL,
  `expense_category` varchar(100),
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`expense_head_id`),
  UNIQUE KEY `unique_site_expense_code` (`site_id`, `expense_head_code`),
  KEY `fk_expense_site` (`site_id`),
  CONSTRAINT `fk_expense_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Petty Cash Month Closing
CREATE TABLE IF NOT EXISTS `petty_cash_closing` (
  `closing_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `opening_balance` decimal(15,2) DEFAULT 0.00,
  `total_cash_in` decimal(15,2) DEFAULT 0.00,
  `total_cash_out` decimal(15,2) DEFAULT 0.00,
  `closing_balance` decimal(15,2) DEFAULT 0.00,
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`closing_id`),
  UNIQUE KEY `unique_site_month_year_pc` (`site_id`, `month`, `year`),
  KEY `fk_pc_closing_site` (`site_id`),
  KEY `fk_pc_closing_locked_by` (`locked_by`),
  CONSTRAINT `fk_pc_closing_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_pc_closing_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================
-- DFC (DAILY FOOD COST) MODULE
-- ============================================

-- DFC Records
CREATE TABLE IF NOT EXISTS `dfc_records` (
  `dfc_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `dfc_date` date NOT NULL,
  `catering_btc_sale` decimal(15,2) DEFAULT 0.00,
  `total_sale` decimal(15,2) DEFAULT 0.00,
  `food_cost` decimal(15,2) DEFAULT 0.00,
  `food_cost_percent` decimal(5,2) DEFAULT 0.00,
  `manpower_cost` decimal(15,2) DEFAULT 0.00,
  `manpower_cost_percent` decimal(5,2) DEFAULT 0.00,
  `cylinder_cost` decimal(15,2) DEFAULT 0.00,
  `cylinder_cost_percent` decimal(5,2) DEFAULT 0.00,
  `benchmark_percent` decimal(5,2) DEFAULT 0.00,
  `variation_amount` decimal(15,2) DEFAULT 0.00,
  `variation_type` enum('positive','negative') DEFAULT 'positive',
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`dfc_id`),
  UNIQUE KEY `unique_site_date_dfc` (`site_id`, `dfc_date`),
  KEY `fk_dfc_site` (`site_id`),
  KEY `fk_dfc_created_by` (`created_by`),
  KEY `fk_dfc_updated_by` (`updated_by`),
  KEY `fk_dfc_locked_by` (`locked_by`),
  KEY `idx_dfc_month_year` (`month`, `year`),
  CONSTRAINT `fk_dfc_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_dfc_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_dfc_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_dfc_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- MIS REPORTS MODULE
-- ============================================

-- MIS Report Templates (Dynamic)
CREATE TABLE IF NOT EXISTS `mis_report_templates` (
  `template_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `template_name` varchar(255) NOT NULL,
  `template_type` varchar(100) NOT NULL,
  `report_config` text,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`template_id`),
  KEY `fk_mis_template_site` (`site_id`),
  CONSTRAINT `fk_mis_template_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Report Data
CREATE TABLE IF NOT EXISTS `mis_report_data` (
  `report_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `template_id` int(11),
  `report_month` int(11) NOT NULL,
  `report_year` int(11) NOT NULL,
  `sales` decimal(15,2) DEFAULT 0.00,
  `purchase` decimal(15,2) DEFAULT 0.00,
  `labour_cost` decimal(15,2) DEFAULT 0.00,
  `overheads` decimal(15,2) DEFAULT 0.00,
  `gop` decimal(15,2) DEFAULT 0.00,
  `gop_percent` decimal(5,2) DEFAULT 0.00,
  `budget_amount` decimal(15,2) DEFAULT 0.00,
  `actual_amount` decimal(15,2) DEFAULT 0.00,
  `variance` decimal(15,2) DEFAULT 0.00,
  `report_data` text,
  `generated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `generated_by` int(11),
  PRIMARY KEY (`report_id`),
  UNIQUE KEY `unique_site_month_year_mis` (`site_id`, `report_month`, `report_year`),
  KEY `fk_mis_site` (`site_id`),
  KEY `fk_mis_template` (`template_id`),
  KEY `fk_mis_generated_by` (`generated_by`),
  CONSTRAINT `fk_mis_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_mis_template` FOREIGN KEY (`template_id`) REFERENCES `mis_report_templates` (`template_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mis_generated_by` FOREIGN KEY (`generated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TRANSFER MODULE
-- ============================================

-- Employee Transfers
CREATE TABLE IF NOT EXISTS `employee_transfers` (
  `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `from_site_id` int(11) NOT NULL,
  `to_site_id` int(11) NOT NULL,
  `transfer_date` date NOT NULL,
  `transfer_reason` text,
  `old_department_id` int(11),
  `new_department_id` int(11),
  `old_designation_id` int(11),
  `new_designation_id` int(11),
  `transfer_status` enum('pending','approved','completed','cancelled') DEFAULT 'pending',
  `approved_by` int(11),
  `approved_at` datetime,
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transfer_id`),
  KEY `fk_transfer_emp` (`employee_id`),
  KEY `fk_transfer_from_site` (`from_site_id`),
  KEY `fk_transfer_to_site` (`to_site_id`),
  KEY `fk_transfer_old_dept` (`old_department_id`),
  KEY `fk_transfer_new_dept` (`new_department_id`),
  KEY `fk_transfer_old_desig` (`old_designation_id`),
  KEY `fk_transfer_new_desig` (`new_designation_id`),
  KEY `fk_transfer_approved_by` (`approved_by`),
  KEY `fk_transfer_created_by` (`created_by`),
  KEY `fk_transfer_updated_by` (`updated_by`),
  CONSTRAINT `fk_transfer_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_transfer_from_site` FOREIGN KEY (`from_site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_transfer_to_site` FOREIGN KEY (`to_site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_transfer_old_dept` FOREIGN KEY (`old_department_id`) REFERENCES `departments` (`department_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transfer_new_dept` FOREIGN KEY (`new_department_id`) REFERENCES `departments` (`department_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transfer_old_desig` FOREIGN KEY (`old_designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transfer_new_desig` FOREIGN KEY (`new_designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transfer_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transfer_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transfer_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- RESIGNED & ABSCONDED MODULE
-- ============================================

-- Employee Exit Records
CREATE TABLE IF NOT EXISTS `employee_exits` (
  `exit_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `exit_type` enum('resigned','absconded','terminated','retired') NOT NULL,
  `last_working_date` date NOT NULL,
  `notice_served_date` date,
  `notice_period_days` int(11) DEFAULT 0,
  `exit_reason` text,
  `final_settlement_amount` decimal(15,2) DEFAULT 0.00,
  `final_settlement_date` date,
  `exit_status` enum('pending','processing','completed','cancelled') DEFAULT 'pending',
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`exit_id`),
  UNIQUE KEY `unique_emp_exit` (`employee_id`),
  KEY `fk_exit_emp` (`employee_id`),
  KEY `fk_exit_site` (`site_id`),
  KEY `fk_exit_locked_by` (`locked_by`),
  KEY `fk_exit_created_by` (`created_by`),
  KEY `fk_exit_updated_by` (`updated_by`),
  CONSTRAINT `fk_exit_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_exit_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_exit_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exit_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exit_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DATA LOCKING SYSTEM
-- ============================================

-- Data Locking Records
CREATE TABLE IF NOT EXISTS `data_locks` (
  `lock_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `lock_type` enum('attendance','payroll','petty_cash','dfc','exit') NOT NULL,
  `lock_reference_id` int(11),
  `month` int(11),
  `year` int(11),
  `lock_date` date,
  `is_locked` tinyint(1) DEFAULT 1,
  `locked_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `locked_by` int(11) NOT NULL,
  `lock_reason` text,
  PRIMARY KEY (`lock_id`),
  UNIQUE KEY `unique_lock` (`site_id`, `lock_type`, `month`, `year`, `lock_reference_id`),
  KEY `fk_lock_site` (`site_id`),
  KEY `fk_lock_user` (`locked_by`),
  KEY `idx_lock_type_month_year` (`lock_type`, `month`, `year`),
  CONSTRAINT `fk_lock_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_lock_user` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ADDITIONAL EMPLOYEE FIELDS
-- ============================================

-- Add missing columns to employees table if not exist
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `employee_category` varchar(100) AFTER `employment_status`,
ADD COLUMN IF NOT EXISTS `pf_number` varchar(50) AFTER `bank_ifsc`,
ADD COLUMN IF NOT EXISTS `esic_number` varchar(50) AFTER `pf_number`,
ADD COLUMN IF NOT EXISTS `uan_number` varchar(50) AFTER `esic_number`;

-- ============================================
-- INITIAL DATA FOR NEW MODULES
-- ============================================

-- Default Expense Heads
INSERT INTO `expense_heads` (`site_id`, `expense_head_code`, `expense_head_name`, `expense_category`) VALUES
(1, 'TRAVEL', 'Travel Expenses', 'operational'),
(1, 'STATIONERY', 'Stationery & Supplies', 'operational'),
(1, 'MAINTENANCE', 'Maintenance & Repairs', 'operational'),
(1, 'MISCELLANEOUS', 'Miscellaneous Expenses', 'operational');

-- Default Overtime Rate
INSERT INTO `overtime_rates` (`site_id`, `rate_name`, `rate_multiplier`, `effective_from`, `is_active`) VALUES
(1, 'Standard OT Rate', 1.50, CURDATE(), 1);

COMMIT;

