-- Create Attendance Monthly Grid Table Only
-- Run this in phpMyAdmin if you only need the attendance table

USE hrm2;

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 RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verify table creation
SELECT 'Attendance table created successfully!' as Status;
