-- Hopepress Chapel Attendance Management System Database Schema
-- Version: 2.0 - Improved with Delegate Support and Better Role Management


-- ============================================
-- USER ROLES TABLE (New)
-- Defines system roles with hierarchical permissions
-- ============================================
CREATE TABLE IF NOT EXISTS `user_roles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `role_name` VARCHAR(50) NOT NULL UNIQUE,
    `role_description` TEXT,
    `role_level` INT NOT NULL, -- 1=delegate, 2=worker, 3=hod, 4=admin, 5=superadmin
    `can_clock_in` BOOLEAN DEFAULT TRUE,
    `can_view_own_attendance` BOOLEAN DEFAULT TRUE,
    `can_view_department_attendance` BOOLEAN DEFAULT FALSE,
    `can_issue_tickets` BOOLEAN DEFAULT FALSE,
    `can_resolve_tickets` BOOLEAN DEFAULT FALSE,
    `can_manage_users` BOOLEAN DEFAULT FALSE,
    `can_manage_services` BOOLEAN DEFAULT FALSE,
    `can_manage_rewards` BOOLEAN DEFAULT FALSE,
    `can_generate_reports` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_user_roles_role_name` (`role_name`),
    INDEX `idx_user_roles_role_level` (`role_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default roles
INSERT INTO `user_roles` 
(`role_name`, `role_description`, `role_level`, `can_clock_in`, `can_view_own_attendance`, `can_view_department_attendance`, `can_issue_tickets`, `can_resolve_tickets`, `can_manage_users`, `can_manage_services`, `can_manage_rewards`, `can_generate_reports`) 
VALUES
('delegate', 'Conference/Training attendee with limited access', 1, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
('worker', 'Regular church worker', 2, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
('hod', 'Head of Department with supervisory privileges', 3, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE),
('admin', 'Administrator with full management access', 4, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE),
('superadmin', 'Super Administrator with system-wide access', 5, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE);

-- ============================================
-- LOOKUP TABLES
-- ============================================

-- Service Types table (to distinguish regular services from special events)
CREATE TABLE IF NOT EXISTS `service_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `description` TEXT,
    `allows_delegates` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_service_types_name` (`name`)
);

INSERT INTO `service_types` (`name`, `description`, `allows_delegates`) VALUES
('Regular Service', 'Standard weekly church services', FALSE),
('Conference', 'Special conference events', TRUE),
('Training', 'Training and development programs', TRUE),
('Special Event', 'Other special church events', TRUE),
('Midweek Service', 'Midweek services and prayer meetings', FALSE);

-- Reward Types table (to categorize different types of rewards)
CREATE TABLE IF NOT EXISTS `reward_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `description` TEXT,
    `requires_certificate` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_reward_types_name` (`name`)
);

INSERT INTO `reward_types` (`name`, `description`, `requires_certificate`) VALUES
('Physical Item', 'Tangible rewards like books, gifts, etc.', FALSE),
('Meeting', 'One-on-one or group meeting with Senior Pastor', FALSE),
('Travel', 'Travel opportunities or trips', FALSE),
('Physical Certificate', 'Printed certificate of achievement', TRUE),
('Digital Certificate', 'Downloadable digital certificate', TRUE),
('Recognition', 'Public recognition or appreciation', FALSE),
('Cash Prize', 'Monetary rewards or gift cards', FALSE);

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

-- Insert default departments
INSERT INTO `departments` (`name`, `description`) VALUES
('Media', 'Handles all media-related activities including audio, video, and live streaming.'),
('Ushers', 'Responsible for welcoming and seating attendees during services.'),
('Welfare', 'Provides support and assistance to members in need.'),
('Protocol', 'Ensures proper conduct and procedures during church events.'),
('Music', 'Leads worship and music during services.'),
('Finance', 'Manages the church financial resources and budgeting.'),
('Administration', 'Oversees the administrative functions of the church.'),
('Hospitality', 'Coordinates hospitality services including refreshments and meals.'),
('Outreach', 'Organizes community outreach and evangelism programs.'),
('Children', 'Focuses on programs and activities for children.'),
('Youth', 'Engages and supports the youth members of the church.'),
('Tech', 'Manages technical equipment and IT infrastructure.'),
('Security', 'Ensures the safety and security of church members and property.'),
('Cleaning', 'Maintains cleanliness and hygiene within church premises.'),
('Maintenance', 'Handles repairs and upkeep of church facilities.'),
('Parking', 'Manages parking arrangements during church events.'),
('Counseling', 'Provides counseling services to members in need.'),
('Prayer', 'Leads prayer sessions and spiritual support.'),
('Evangelism', 'Focuses on spreading the gospel and church growth.');

-- ============================================
-- SERVICES TABLE (Improved)
-- ============================================

CREATE TABLE IF NOT EXISTS `service_templates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `service_type_id` INT,
    `location` VARCHAR(255),
    `latitude` DECIMAL(10,8),
    `longitude` DECIMAL(11,8),
    `geofence_radius` INT DEFAULT 100,
    `is_recurring` BOOLEAN DEFAULT FALSE,
    `recurrence_pattern` ENUM('daily','weekly','monthly','none') DEFAULT 'none',
    `recurrence_interval` INT DEFAULT 1, -- every X weeks, etc.
    `recurrence_days` SET('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'),
    `recurrence_start_date` DATE,
    `recurrence_end_date` DATE,
    `default_start_time` TIME,
    `default_end_time` TIME,
    `is_active` BOOLEAN DEFAULT TRUE,
    CONSTRAINT `fk_service_templates_service_types` FOREIGN KEY (`service_type_id`) REFERENCES `service_types`(`id`) ON UPDATE CASCADE,
    INDEX `idx_service_templates_name` (`name`),
    INDEX `idx_service_templates_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `service_occurrences` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_template_id` INT NOT NULL,
    `occurrence_date` DATE NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `was_cancelled` BOOLEAN DEFAULT FALSE,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_service_occurrences_service_templates` FOREIGN KEY (`service_template_id`) REFERENCES `service_templates`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY `unique_service_occurrence` (`service_template_id`, `occurrence_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- ATTENDANCE TABLE (Improved)
-- ============================================
CREATE TABLE IF NOT EXISTS `attendance` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `service_occurrence_id` INT NOT NULL,
    `clock_in_time` DATETIME NOT NULL,
    `clock_out_time` DATETIME,
    `clock_in_latitude` DECIMAL(10, 8),
    `clock_in_longitude` DECIMAL(11, 8),
    `clock_out_latitude` DECIMAL(10, 8),
    `clock_out_longitude` DECIMAL(11, 8),
    `time_spent` INT, -- in minutes
    `is_valid` BOOLEAN DEFAULT TRUE, -- For marking invalid attendance (e.g., left too early)
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_attendance_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_attendance_service_occurrences` FOREIGN KEY (`service_occurrence_id`) REFERENCES `service_occurrences`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY `unique_user_service_attendance` (`user_id`, `service_occurrence_id`, `clock_in_time`),
    INDEX `idx_attendance_user_id` (`user_id`),
    INDEX `idx_attendance_service_occurrence_id` (`service_occurrence_id`),
    INDEX `idx_attendance_clock_in_time` (`clock_in_time`),
    INDEX `idx_attendance_is_valid` (`is_valid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- REWARDS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS `rewards` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `reward_type_id` INT NOT NULL,
    `certificate_template` TEXT, -- Template for certificate generation (HTML or reference)
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_rewards_reward_types` FOREIGN KEY (`reward_type_id`) REFERENCES `reward_types`(`id`) ON UPDATE CASCADE,
    INDEX `idx_rewards_name` (`name`),
    INDEX `idx_rewards_type` (`reward_type_id`),
    INDEX `idx_rewards_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- SERVICE REWARDS TABLE (Junction)
-- ============================================
CREATE TABLE IF NOT EXISTS `service_rewards` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_template_id` INT NOT NULL,
    `reward_id` INT NOT NULL,
    `start_date` DATETIME NOT NULL,
    `end_date` DATETIME,
    `qr_code` VARCHAR(255),
    `attendance_required` INT DEFAULT 1,
    `description` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_service_rewards_service_templates` FOREIGN KEY (`service_template_id`) REFERENCES `service_templates`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_service_rewards_rewards` FOREIGN KEY (`reward_id`) REFERENCES `rewards`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_service_rewards_service_template_id` (`service_template_id`),
    INDEX `idx_service_rewards_reward_id` (`reward_id`),
    INDEX `idx_service_rewards_start_date` (`start_date`),
    INDEX `idx_service_rewards_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- USERS SERVICE REWARDS TABLE (Progress Tracking)
-- ============================================
CREATE TABLE IF NOT EXISTS `users_service_rewards` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `service_reward_id` INT NOT NULL,
    `attendance_count` INT DEFAULT 0,
    `rewards_earned` INT DEFAULT 0,
    `last_attendance_date` DATETIME,
    `is_redeemed` BOOLEAN DEFAULT FALSE,
    `redeemed_at` DATETIME,
    `redeemed_by` INT, -- Admin user who processed redemption
    `redemption_notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_users_service_rewards_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_users_service_rewards_service_rewards` FOREIGN KEY (`service_reward_id`) REFERENCES `service_rewards`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_users_service_rewards_admin_users` FOREIGN KEY (`redeemed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    UNIQUE KEY `unique_user_service_reward` (`user_id`, `service_reward_id`),
    INDEX `idx_users_service_rewards_user_id` (`user_id`),
    INDEX `idx_users_service_rewards_attendance_count` (`attendance_count`),
    INDEX `idx_users_service_rewards_is_redeemed` (`is_redeemed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- CERTIFICATES TABLE (New)
-- Tracks certificates earned by delegates
-- ============================================
CREATE TABLE IF NOT EXISTS `certificates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `service_template_id` INT NOT NULL,
    `certificate_number` VARCHAR(50) UNIQUE NOT NULL,
    `attendances_completed` INT NOT NULL,
    `attendances_required` INT NOT NULL,
    `issue_date` DATE NOT NULL,
    `certificate_url` VARCHAR(255), -- Path to generated PDF
    `is_downloaded` BOOLEAN DEFAULT FALSE,
    `first_downloaded_at` DATETIME,
    `download_count` INT DEFAULT 0,
    `issued_by` INT, -- Admin who issued/approved
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_certificates_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_certificates_service_templates` FOREIGN KEY (`service_template_id`) REFERENCES `service_templates`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_certificates_admin_users` FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    UNIQUE KEY `unique_user_service_certificate` (`user_id`, `service_template_id`),
    INDEX `idx_certificates_user_id` (`user_id`),
    INDEX `idx_certificates_service_template_id` (`service_template_id`),
    INDEX `idx_certificates_certificate_number` (`certificate_number`),
    INDEX `idx_certificates_issue_date` (`issue_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TICKETS TABLE (Penalty System)
-- ============================================
CREATE TABLE IF NOT EXISTS `tickets` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `issued_by` INT NOT NULL, -- HoD or Admin who issued the ticket
    `ticket_type` ENUM('warning', 'violation', 'misconduct', 'absence') DEFAULT 'warning',
    `reason` TEXT NOT NULL,
    `severity` ENUM('low', 'medium', 'high') DEFAULT 'medium',
    `issued_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `is_resolved` BOOLEAN DEFAULT FALSE,
    `resolved_at` DATETIME,
    `resolved_by` INT, -- Admin who resolved
    `resolution_notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_tickets_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_tickets_users_issued` FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_tickets_users_resolved` FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    INDEX `idx_tickets_user_id` (`user_id`),
    INDEX `idx_tickets_issued_by` (`issued_by`),
    INDEX `idx_tickets_is_resolved` (`is_resolved`),
    INDEX `idx_tickets_severity` (`severity`),
    INDEX `idx_tickets_issued_at` (`issued_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- ADMIN LOGS TABLE (Enhanced Audit Trail)
-- ============================================
CREATE TABLE IF NOT EXISTS `admin_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT NOT NULL,
    `action` VARCHAR(255) NOT NULL,
    `action_type` ENUM('create', 'update', 'delete', 'view', 'export', 'other') DEFAULT 'other',
    `target_type` VARCHAR(50), -- user, service, ticket, reward, certificate, etc.
    `target_id` INT,
    `ip_address` VARCHAR(45), -- IPv6 compatible
    `user_agent` TEXT,
    `details` JSON, -- Store detailed information as JSON
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_admin_logs_users` FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX `idx_admin_logs_admin_id` (`admin_id`),
    INDEX `idx_admin_logs_action_type` (`action_type`),
    INDEX `idx_admin_logs_target_type` (`target_type`),
    INDEX `idx_admin_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
