-- PSAU GAD Information System Database Schema
-- Created: 2025-11-03
-- Database: gad_system

-- Drop existing database if exists
DROP DATABASE IF EXISTS gad_system;
CREATE DATABASE gad_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gad_system;

-- ============================================
-- USERS & AUTHENTICATION
-- ============================================

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'employee', 'secretariat') NOT NULL,
    is_psau_account BOOLEAN DEFAULT FALSE,
    is_approved BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_approved (is_approved)
) ENGINE=InnoDB;

CREATE TABLE employee_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,
    employee_number VARCHAR(50) UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    suffix VARCHAR(10),
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    date_of_birth DATE,
    age INT,
    position VARCHAR(150),
    category ENUM('Faculty', 'Non-Teaching', 'Service Personnel', 'Other') NOT NULL,
    department VARCHAR(150),
    contact_number VARCHAR(20),
    address TEXT,
    profile_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_category (category),
    INDEX idx_gender (gender)
) ENGINE=InnoDB;

-- ============================================
-- ACTIVITIES & EVENTS
-- ============================================

CREATE TABLE activities (
    activity_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    venue VARCHAR(255),
    activity_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    registration_deadline DATETIME,
    attendance_cutoff_time TIME COMMENT 'Time after which attendance is marked as late',
    zoom_link VARCHAR(500),
    training_hours DECIMAL(4,2),
    category ENUM('Gender Sensitivity', 'Women Empowerment', 'LGBTQ+ Rights', 'Anti-VAWC', 'Other') NOT NULL,
    level ENUM('Basic', 'Intermediate', 'Advanced') DEFAULT 'Basic',
    target_participants INT,
    budget DECIMAL(12,2),
    quarter ENUM('Q1', 'Q2', 'Q3', 'Q4'),
    year INT,
    is_active BOOLEAN DEFAULT TRUE,
    requires_pre_test BOOLEAN DEFAULT TRUE,
    requires_post_test BOOLEAN DEFAULT TRUE,
    requires_evaluation BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(user_id),
    INDEX idx_date (activity_date),
    INDEX idx_category (category),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE activity_target_groups (
    target_group_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    category ENUM('Faculty', 'Non-Teaching', 'Service Personnel', 'All') NOT NULL,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE activity_pre_assignments (
    assignment_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    file_url VARCHAR(500),
    due_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- REGISTRATION & ATTENDANCE
-- ============================================

CREATE TABLE activity_registrations (
    registration_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    user_id INT NOT NULL,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_approved BOOLEAN DEFAULT FALSE,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    qr_code VARCHAR(255) UNIQUE,
    qr_code_generated_at TIMESTAMP NULL,
    status ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
    rejection_reason TEXT,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(user_id),
    UNIQUE KEY unique_registration (activity_id, user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE activity_attendance (
    attendance_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    user_id INT NOT NULL,
    registration_id INT,
    attendance_type ENUM('qr_scan', 'walk_in') NOT NULL,
    check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('present', 'late', 'absent') DEFAULT 'present',
    scanned_by INT COMMENT 'User who scanned the QR or registered walk-in',
    notes TEXT,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (registration_id) REFERENCES activity_registrations(registration_id),
    FOREIGN KEY (scanned_by) REFERENCES users(user_id),
    UNIQUE KEY unique_attendance (activity_id, user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- ============================================
-- ASSESSMENTS (Pre-test, Post-test, Evaluation)
-- ============================================

CREATE TABLE assessment_templates (
    template_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    assessment_type ENUM('pre_test', 'post_test', 'evaluation') NOT NULL,
    title VARCHAR(255) NOT NULL,
    instructions TEXT,
    passing_score INT DEFAULT 70,
    time_limit INT COMMENT 'Time limit in minutes',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE assessment_questions (
    question_id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM('multiple_choice', 'true_false', 'rating', 'text') NOT NULL,
    options JSON COMMENT 'For multiple choice questions',
    correct_answer VARCHAR(255) COMMENT 'For pre/post tests',
    order_number INT,
    points INT DEFAULT 1,
    FOREIGN KEY (template_id) REFERENCES assessment_templates(template_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE assessment_responses (
    response_id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    question_id INT NOT NULL,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    answer TEXT,
    score INT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES assessment_templates(template_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES assessment_questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    INDEX idx_user_activity (user_id, activity_id)
) ENGINE=InnoDB;

CREATE TABLE assessment_results (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    total_score DECIMAL(5,2),
    percentage DECIMAL(5,2),
    passed BOOLEAN,
    time_taken INT COMMENT 'Time taken in minutes',
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES assessment_templates(template_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    UNIQUE KEY unique_result (template_id, user_id, activity_id)
) ENGINE=InnoDB;

-- ============================================
-- CERTIFICATES
-- ============================================

CREATE TABLE certificates (
    certificate_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    certificate_number VARCHAR(100) UNIQUE NOT NULL,
    issued_date DATE NOT NULL,
    certificate_file VARCHAR(255),
    is_downloaded BOOLEAN DEFAULT FALSE,
    download_count INT DEFAULT 0,
    first_download_at TIMESTAMP NULL,
    last_download_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    UNIQUE KEY unique_certificate (user_id, activity_id)
) ENGINE=InnoDB;

-- ============================================
-- REPORTS & PLANNING
-- ============================================

CREATE TABLE gpb_forms (
    gpb_id INT PRIMARY KEY AUTO_INCREMENT,
    year INT NOT NULL,
    activity_title VARCHAR(255) NOT NULL,
    description TEXT,
    target_date DATE,
    estimated_budget DECIMAL(12,2),
    target_participants INT,
    category VARCHAR(100),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(user_id)
) ENGINE=InnoDB;

CREATE TABLE monitoring_forms (
    monitoring_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_id INT NOT NULL,
    gpb_item VARCHAR(255),
    actual_date DATE,
    actual_budget DECIMAL(12,2),
    actual_participants INT,
    accomplishments TEXT,
    challenges TEXT,
    recommendations TEXT,
    submitted_by INT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id) ON DELETE CASCADE,
    FOREIGN KEY (submitted_by) REFERENCES users(user_id)
) ENGINE=InnoDB;

-- ============================================
-- RESOURCES & LIBRARY
-- ============================================

CREATE TABLE resource_library (
    resource_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    resource_type ENUM('manual', 'poster', 'video') NOT NULL,
    file_url VARCHAR(500),
    thumbnail_url VARCHAR(500),
    file_size BIGINT,
    uploaded_by INT,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (uploaded_by) REFERENCES users(user_id),
    INDEX idx_type (resource_type)
) ENGINE=InnoDB;

-- ============================================
-- CONTACT & NOTIFICATIONS
-- ============================================

CREATE TABLE contact_messages (
    message_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(150),
    email VARCHAR(255),
    subject VARCHAR(255),
    message TEXT NOT NULL,
    status ENUM('unread', 'read', 'replied') DEFAULT 'unread',
    replied_at TIMESTAMP NULL,
    replied_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (replied_by) REFERENCES users(user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE email_notifications (
    notification_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    email_type ENUM('registration_confirmation', 'approval', 'pre_test', 'qr_code', 'post_test', 'certificate', 'reminder') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_sent BOOLEAN DEFAULT FALSE,
    error_message TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- SYSTEM SETTINGS
-- ============================================

CREATE TABLE system_settings (
    setting_id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(user_id)
) ENGINE=InnoDB;

-- Insert default settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'PSAU GAD Information System', 'System name'),
('site_email', 'gad@psau.edu.ph', 'System email address'),
('attendance_grace_period', '20', 'Grace period for attendance in minutes'),
('auto_approve_psau', '1', 'Auto-approve PSAU accounts'),
('certificate_template', 'default', 'Certificate template to use'),
('smtp_host', '', 'SMTP host for email'),
('smtp_port', '587', 'SMTP port'),
('smtp_username', '', 'SMTP username'),
('smtp_password', '', 'SMTP password');

