-- Migration: Create LeaveRequests table (Sequelize model: LeaveRequest, tableName: LeaveRequests)
-- Run on existing databases. New installs: see DATABASE_FULL_SETUP.sql section 14b.

CREATE TABLE IF NOT EXISTS `LeaveRequests` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `UserId` INT NOT NULL,
  `CompanyId` INT NOT NULL,
  `ReviewedById` INT NULL,
  `type` ENUM(
    'Annual Leave',
    'Sick Leave',
    'Emergency Leave',
    'Maternity/Paternity Leave',
    'Unpaid Leave',
    'Other'
  ) NOT NULL,
  `startDate` DATE NOT NULL,
  `endDate` DATE NOT NULL,
  `comment` TEXT NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewNote` TEXT NULL,
  `reviewedAt` DATETIME NULL,
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_leave_user` (`UserId`),
  KEY `idx_leave_company` (`CompanyId`),
  KEY `idx_leave_status` (`status`),
  KEY `idx_leave_reviewer` (`ReviewedById`),
  CONSTRAINT `LeaveRequests_ibfk_user` FOREIGN KEY (`UserId`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `LeaveRequests_ibfk_company` FOREIGN KEY (`CompanyId`) REFERENCES `Companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `LeaveRequests_ibfk_reviewer` FOREIGN KEY (`ReviewedById`) REFERENCES `Users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Employee leave requests';
