-- Add check-in sequential number to employees table
-- This number is assigned sequentially when an employee checks in at the event

ALTER TABLE `aesa_employees`
ADD COLUMN `checkin_number` INT UNSIGNED NULL AFTER `checked_in_by`,
ADD INDEX `idx_checkin_number` (`checkin_number`);

-- Create a helper table to track the next check-in number atomically
CREATE TABLE IF NOT EXISTS `aesa_checkin_counter` (
    `id` INT UNSIGNED NOT NULL DEFAULT 1 PRIMARY KEY,
    `next_number` INT UNSIGNED NOT NULL DEFAULT 1,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initialize the counter
INSERT INTO `aesa_checkin_counter` (`id`, `next_number`) VALUES (1, 1)
ON DUPLICATE KEY UPDATE `id` = 1;
