USE travel_performa;

-- Create attendance_audit_logs table for tracking attendance reissue audit trail
CREATE TABLE IF NOT EXISTS `attendance_audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `attendance_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `admin_id` INT NOT NULL,
  `reason` TEXT NOT NULL,
  `original_data` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`attendance_id`) REFERENCES `attendance`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ensure tracking_settings table exists for dynamic tracking configuration
CREATE TABLE IF NOT EXISTS `tracking_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(50) UNIQUE NOT NULL,
  `setting_value` VARCHAR(255) NOT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add indexes for optimized queries
ALTER TABLE `performas` ADD INDEX `idx_visit_date` (`visit_date`);
ALTER TABLE `performas` ADD INDEX `idx_user_visit` (`user_id`, `visit_date`);
ALTER TABLE `attendance` ADD INDEX `idx_user_date` (`user_id`, `attendance_date`);