-- Hotel POS Employee Attendance feature
CREATE TABLE IF NOT EXISTS `attendance` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `attendance_date` date NOT NULL,
  `clock_in` timestamp NULL DEFAULT NULL,
  `clock_out` timestamp NULL DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'present',
  `worked_minutes` int unsigned NOT NULL DEFAULT 0,
  `notes` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `attendance_user_date_unique` (`user_id`,`attendance_date`),
  KEY `attendance_date_status_index` (`attendance_date`,`status`),
  KEY `attendance_user_id_index` (`user_id`),
  CONSTRAINT `attendance_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add Attendance permissions to existing non-administrator roles without removing existing permissions.
UPDATE `roles`
SET `permissions` = JSON_ARRAY_APPEND(COALESCE(`permissions`, JSON_ARRAY()), '$', 'attendance.view')
WHERE `slug` <> 'administrator'
  AND JSON_SEARCH(COALESCE(`permissions`, JSON_ARRAY()), 'one', 'attendance.view') IS NULL;

UPDATE `roles`
SET `permissions` = JSON_ARRAY_APPEND(COALESCE(`permissions`, JSON_ARRAY()), '$', 'attendance.manage')
WHERE `slug` IN ('manager')
  AND JSON_SEARCH(COALESCE(`permissions`, JSON_ARRAY()), 'one', 'attendance.manage') IS NULL;

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_10_06_000001_create_attendance_table', COALESCE(MAX(`batch`),0)+1 FROM `migrations`
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_10_06_000001_create_attendance_table');