-- Hopepress Chapel Attendance Management System
-- Migration 001: System Improvements
-- Run this migration to add new features

-- ============================================
-- ATTENDANCE STATUS ENUM UPDATE
-- Add late and early_departure tracking
-- ============================================

ALTER TABLE `attendance`
ADD COLUMN IF NOT EXISTS `attendance_status` ENUM('on_time', 'late', 'very_late', 'early_departure') DEFAULT 'on_time' AFTER `is_valid`,
ADD COLUMN IF NOT EXISTS `late_minutes` INT DEFAULT 0 AFTER `attendance_status`,
ADD COLUMN IF NOT EXISTS `early_departure_minutes` INT DEFAULT 0 AFTER `late_minutes`,
ADD COLUMN IF NOT EXISTS `admin_adjusted` BOOLEAN DEFAULT FALSE AFTER `early_departure_minutes`,
ADD COLUMN IF NOT EXISTS `adjusted_by` INT NULL AFTER `admin_adjusted`,
ADD COLUMN IF NOT EXISTS `adjustment_reason` TEXT NULL AFTER `adjusted_by`;

-- Add foreign key for adjusted_by if not exists
-- Note: This may fail if the column doesn't exist yet, which is fine
-- ALTER TABLE `attendance` ADD CONSTRAINT `fk_attendance_adjusted_by` FOREIGN KEY (`adjusted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Add index for attendance status
CREATE INDEX IF NOT EXISTS `idx_attendance_status` ON `attendance` (`attendance_status`);

-- ============================================
-- LEAVE MANAGEMENT TABLES
-- ============================================

CREATE TABLE IF NOT EXISTS `leave_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT,
    `requires_approval` BOOLEAN DEFAULT TRUE,
    `max_days_per_year` INT DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_leave_types_name` (`name`),
    INDEX `idx_leave_types_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default leave types
INSERT IGNORE INTO `leave_types` (`name`, `description`, `requires_approval`, `max_days_per_year`) VALUES
('Personal Leave', 'Personal time off for non-emergency situations', TRUE, 12),
('Sick Leave', 'Time off due to illness or medical appointments', TRUE, NULL),
('Emergency Leave', 'Urgent leave due to unforeseen circumstances', TRUE, NULL),
('Annual Leave', 'Planned vacation or rest period', TRUE, 14),
('Bereavement', 'Leave due to death of a family member', TRUE, 5),
('Travel', 'Out of town for work or personal reasons', TRUE, NULL),
('Training/Conference', 'Attending external training or conference', FALSE, NULL);

CREATE TABLE IF NOT EXISTS `leave_requests` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `leave_type_id` INT NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `reason` TEXT NOT NULL,
    `status` ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
    `approved_by` INT NULL,
    `approved_at` DATETIME NULL,
    `rejection_reason` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_leave_requests_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_leave_requests_leave_types` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_leave_requests_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX `idx_leave_requests_user_id` (`user_id`),
    INDEX `idx_leave_requests_status` (`status`),
    INDEX `idx_leave_requests_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- ANNOUNCEMENTS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS `announcements` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    `type` ENUM('general', 'urgent', 'reminder', 'event') DEFAULT 'general',
    `target_audience` ENUM('all', 'workers', 'hods', 'admins', 'department') DEFAULT 'all',
    `target_department_id` INT NULL,
    `priority` ENUM('low', 'normal', 'high') DEFAULT 'normal',
    `publish_at` DATETIME NOT NULL,
    `expires_at` DATETIME NULL,
    `is_published` BOOLEAN DEFAULT FALSE,
    `is_pinned` BOOLEAN DEFAULT FALSE,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_announcements_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_announcements_department` FOREIGN KEY (`target_department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX `idx_announcements_publish_at` (`publish_at`),
    INDEX `idx_announcements_is_published` (`is_published`),
    INDEX `idx_announcements_target_audience` (`target_audience`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Track which users have read announcements
CREATE TABLE IF NOT EXISTS `announcement_reads` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `announcement_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `read_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_announcement_reads_announcements` FOREIGN KEY (`announcement_id`) REFERENCES `announcements`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_announcement_reads_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY `unique_announcement_user` (`announcement_id`, `user_id`),
    INDEX `idx_announcement_reads_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- SERVICE REMINDERS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS `service_reminders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_occurrence_id` INT NOT NULL,
    `reminder_time` DATETIME NOT NULL,
    `reminder_type` ENUM('email', 'push', 'sms', 'all') DEFAULT 'push',
    `message` TEXT,
    `is_sent` BOOLEAN DEFAULT FALSE,
    `sent_at` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_service_reminders_occurrence` FOREIGN KEY (`service_occurrence_id`) REFERENCES `service_occurrences`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_service_reminders_time` (`reminder_time`),
    INDEX `idx_service_reminders_is_sent` (`is_sent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- PUSH NOTIFICATION SUBSCRIPTIONS
-- ============================================

CREATE TABLE IF NOT EXISTS `push_subscriptions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `endpoint` TEXT NOT NULL,
    `p256dh_key` TEXT NOT NULL,
    `auth_key` TEXT NOT NULL,
    `device_type` ENUM('web', 'android', 'ios') DEFAULT 'web',
    `device_name` VARCHAR(255) NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_push_subscriptions_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_push_subscriptions_user` (`user_id`),
    INDEX `idx_push_subscriptions_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- GAMIFICATION TABLES
-- ============================================

-- Achievement/Badge types
CREATE TABLE IF NOT EXISTS `badge_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT,
    `icon` VARCHAR(50) DEFAULT 'award',
    `color` VARCHAR(20) DEFAULT '#fdb913',
    `category` ENUM('attendance', 'streak', 'milestone', 'special') DEFAULT 'attendance',
    `requirement_type` ENUM('attendance_count', 'streak_days', 'perfect_month', 'early_bird', 'custom') NOT NULL,
    `requirement_value` INT NOT NULL,
    `points` INT DEFAULT 10,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_badge_types_category` (`category`),
    INDEX `idx_badge_types_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default badges
INSERT IGNORE INTO `badge_types` (`name`, `description`, `icon`, `category`, `requirement_type`, `requirement_value`, `points`) VALUES
('First Service', 'Attended your first service', 'star', 'milestone', 'attendance_count', 1, 10),
('Regular Attendee', 'Attended 10 services', 'check-circle', 'attendance', 'attendance_count', 10, 25),
('Faithful Worker', 'Attended 25 services', 'award', 'attendance', 'attendance_count', 25, 50),
('Dedicated Servant', 'Attended 50 services', 'trophy', 'attendance', 'attendance_count', 50, 100),
('Veteran', 'Attended 100 services', 'crown', 'milestone', 'attendance_count', 100, 200),
('Week Streak', 'Attended 7 consecutive services', 'flame', 'streak', 'streak_days', 7, 30),
('Month Streak', 'Attended 30 consecutive services', 'zap', 'streak', 'streak_days', 30, 75),
('Quarter Streak', 'Attended 90 consecutive services', 'target', 'streak', 'streak_days', 90, 150),
('Perfect Month', 'Never missed a service in a month', 'calendar-check', 'special', 'perfect_month', 1, 50),
('Early Bird', 'Clocked in early 10 times', 'sunrise', 'special', 'early_bird', 10, 40);

-- User badges (earned)
CREATE TABLE IF NOT EXISTS `user_badges` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `badge_type_id` INT NOT NULL,
    `earned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `is_displayed` BOOLEAN DEFAULT TRUE,
    CONSTRAINT `fk_user_badges_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_user_badges_badge_types` FOREIGN KEY (`badge_type_id`) REFERENCES `badge_types`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY `unique_user_badge` (`user_id`, `badge_type_id`),
    INDEX `idx_user_badges_user` (`user_id`),
    INDEX `idx_user_badges_earned_at` (`earned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- User points and streaks
CREATE TABLE IF NOT EXISTS `user_stats` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL UNIQUE,
    `total_points` INT DEFAULT 0,
    `current_streak` INT DEFAULT 0,
    `longest_streak` INT DEFAULT 0,
    `total_attendance` INT DEFAULT 0,
    `on_time_count` INT DEFAULT 0,
    `late_count` INT DEFAULT 0,
    `early_bird_count` INT DEFAULT 0,
    `perfect_months` INT DEFAULT 0,
    `last_attendance_date` DATE NULL,
    `rank_position` INT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_user_stats_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_user_stats_total_points` (`total_points`),
    INDEX `idx_user_stats_current_streak` (`current_streak`),
    INDEX `idx_user_stats_rank` (`rank_position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- QR CODE CLOCK-IN SUPPORT
-- ============================================

-- Add QR code columns to service_occurrences
ALTER TABLE `service_occurrences`
ADD COLUMN IF NOT EXISTS `qr_code` VARCHAR(100) NULL AFTER `notes`,
ADD COLUMN IF NOT EXISTS `qr_code_expires_at` DATETIME NULL AFTER `qr_code`,
ADD COLUMN IF NOT EXISTS `allow_qr_clockin` BOOLEAN DEFAULT TRUE AFTER `qr_code_expires_at`;

CREATE INDEX IF NOT EXISTS `idx_service_occurrences_qr_code` ON `service_occurrences` (`qr_code`);

-- Track QR code scans (for security)
CREATE TABLE IF NOT EXISTS `qr_clockin_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `service_occurrence_id` INT NOT NULL,
    `qr_code_used` VARCHAR(100) NOT NULL,
    `scanned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `was_successful` BOOLEAN DEFAULT TRUE,
    `failure_reason` VARCHAR(255) NULL,
    CONSTRAINT `fk_qr_logs_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_qr_logs_occurrences` FOREIGN KEY (`service_occurrence_id`) REFERENCES `service_occurrences`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_qr_logs_user` (`user_id`),
    INDEX `idx_qr_logs_service` (`service_occurrence_id`),
    INDEX `idx_qr_logs_scanned_at` (`scanned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- SOFT DELETES
-- Add deleted_at columns to key tables
-- ============================================

ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `deleted_at` DATETIME NULL;
ALTER TABLE `departments` ADD COLUMN IF NOT EXISTS `deleted_at` DATETIME NULL;
ALTER TABLE `service_templates` ADD COLUMN IF NOT EXISTS `deleted_at` DATETIME NULL;
ALTER TABLE `rewards` ADD COLUMN IF NOT EXISTS `deleted_at` DATETIME NULL;

CREATE INDEX IF NOT EXISTS `idx_users_deleted_at` ON `users` (`deleted_at`);
CREATE INDEX IF NOT EXISTS `idx_departments_deleted_at` ON `departments` (`deleted_at`);
CREATE INDEX IF NOT EXISTS `idx_service_templates_deleted_at` ON `service_templates` (`deleted_at`);
CREATE INDEX IF NOT EXISTS `idx_rewards_deleted_at` ON `rewards` (`deleted_at`);

-- ============================================
-- ENHANCED ADMIN LOGS
-- Add more detail tracking
-- ============================================

ALTER TABLE `admin_logs`
ADD COLUMN IF NOT EXISTS `before_data` JSON NULL AFTER `details`,
ADD COLUMN IF NOT EXISTS `after_data` JSON NULL AFTER `before_data`,
ADD COLUMN IF NOT EXISTS `request_id` VARCHAR(50) NULL AFTER `after_data`;

CREATE INDEX IF NOT EXISTS `idx_admin_logs_request_id` ON `admin_logs` (`request_id`);

-- ============================================
-- SETTINGS TABLE
-- For system-wide configuration
-- ============================================

CREATE TABLE IF NOT EXISTS `system_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT,
    `setting_type` ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    `description` TEXT,
    `is_public` BOOLEAN DEFAULT FALSE,
    `updated_by` INT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_system_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX `idx_system_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default settings
INSERT IGNORE INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`) VALUES
('attendance_grace_period', '15', 'integer', 'Grace period in minutes for late arrivals', FALSE),
('min_valid_attendance', '30', 'integer', 'Minimum minutes for valid attendance', FALSE),
('clock_in_early_minutes', '60', 'integer', 'How early before service users can clock in', FALSE),
('clock_out_late_minutes', '60', 'integer', 'How late after service users can clock out', FALSE),
('geofence_default_radius', '100', 'integer', 'Default geofence radius in meters', FALSE),
('enable_qr_clockin', 'true', 'boolean', 'Allow QR code clock-in as alternative', TRUE),
('enable_push_notifications', 'true', 'boolean', 'Enable push notifications', TRUE),
('enable_gamification', 'true', 'boolean', 'Enable badges and points system', TRUE),
('points_per_attendance', '5', 'integer', 'Points awarded per valid attendance', FALSE),
('points_bonus_on_time', '2', 'integer', 'Bonus points for on-time arrival', FALSE),
('church_name', 'Hopepress Chapel', 'string', 'Church name for display', TRUE),
('church_timezone', 'Africa/Accra', 'string', 'Default timezone', TRUE);

-- ============================================
-- UPDATE USER_ROLES PERMISSIONS
-- Add new permission columns
-- ============================================

ALTER TABLE `user_roles`
ADD COLUMN IF NOT EXISTS `can_manage_announcements` BOOLEAN DEFAULT FALSE AFTER `can_generate_reports`,
ADD COLUMN IF NOT EXISTS `can_approve_leave` BOOLEAN DEFAULT FALSE AFTER `can_manage_announcements`,
ADD COLUMN IF NOT EXISTS `can_view_analytics` BOOLEAN DEFAULT FALSE AFTER `can_approve_leave`;

-- Update existing roles with new permissions
UPDATE `user_roles` SET
    `can_manage_announcements` = TRUE,
    `can_approve_leave` = TRUE,
    `can_view_analytics` = TRUE
WHERE `role_name` IN ('admin', 'superadmin');

UPDATE `user_roles` SET
    `can_approve_leave` = TRUE,
    `can_view_analytics` = TRUE
WHERE `role_name` = 'hod';
