-- =============================================================================
-- 11-attendance-requests.sql
-- Migrations: AttendanceRequests1781000000000,
--              AttendanceRequestsForeignKeys1781010000000
-- Requires: 00b-cpanel-helpers.sql
-- cPanel safe. Safe to run multiple times.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `attendance_requests` (
  `id` varchar(36) NOT NULL,
  `owner_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `member_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `request_type` enum('off_day_work','correction') NOT NULL,
  `request_date` datetime NOT NULL,
  `reason` varchar(191) NOT NULL DEFAULT 'NT/APL',
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `requested_payload` json NULL,
  `attendance_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `approver_type` varchar(191) NULL,
  `approver_owner_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `approver_member_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `approved_at` datetime NULL,
  `admin_note` varchar(191) NULL,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `IDX_attendance_requests_member_date_status` (`member_id`, `request_date`, `status`),
  INDEX `IDX_attendance_requests_owner_id` (`owner_id`),
  INDEX `IDX_attendance_requests_attendance_id` (`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CALL cp_safe_exec('ALTER TABLE `attendance_requests` ADD CONSTRAINT `FK_attendance_requests_owner` FOREIGN KEY (`owner_id`) REFERENCES `owner`(`id`) ON DELETE RESTRICT');

CALL cp_safe_exec('ALTER TABLE `attendance_requests` ADD CONSTRAINT `FK_attendance_requests_member` FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE RESTRICT');

CALL cp_safe_exec('ALTER TABLE `attendance_requests` ADD CONSTRAINT `FK_attendance_requests_attendance` FOREIGN KEY (`attendance_id`) REFERENCES `attendances`(`id`) ON DELETE SET NULL');

INSERT INTO migrations (timestamp, name)
SELECT 1781000000000, 'AttendanceRequests1781000000000'
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE name = 'AttendanceRequests1781000000000'
);

INSERT INTO migrations (timestamp, name)
SELECT 1781010000000, 'AttendanceRequestsForeignKeys1781010000000'
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE name = 'AttendanceRequestsForeignKeys1781010000000'
);

SHOW TABLES LIKE 'attendance_requests';
