CREATE DATABASE IF NOT EXISTS event_management_system
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE event_management_system;

SET NAMES utf8mb4;
SET time_zone = '+08:00';

-- =========================================================
-- 1. ROLES
-- =========================================================
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_roles_role_name (role_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 2. USERS
-- =========================================================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile_photo VARCHAR(255) NULL,
    status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_username (username),
    KEY idx_users_role_id (role_id),
    KEY idx_users_name (last_name, first_name),
    CONSTRAINT fk_users_role
        FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 3. ATTENDEES
-- Optional link to users if attendee can log in
-- =========================================================
CREATE TABLE attendees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    student_no VARCHAR(50) NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    suffix VARCHAR(20) NULL,
    sex ENUM('male','female','other') NULL,
    birth_date DATE NULL,
    email VARCHAR(150) NULL,
    contact_no VARCHAR(30) NULL,
    school VARCHAR(150) NULL,
    department VARCHAR(150) NULL,
    year_level VARCHAR(50) NULL,
    address TEXT NULL,
    photo VARCHAR(255) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_attendees_user_id (user_id),
    UNIQUE KEY uq_attendees_student_no (student_no),
    KEY idx_attendees_name (last_name, first_name),
    KEY idx_attendees_email (email),
    CONSTRAINT fk_attendees_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 4. SPEAKERS
-- =========================================================
CREATE TABLE speakers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    designation VARCHAR(150) NULL,
    organization VARCHAR(150) NULL,
    email VARCHAR(150) NULL,
    contact_no VARCHAR(30) NULL,
    photo VARCHAR(255) NULL,
    signature_image VARCHAR(255) NULL,
    bio TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_speakers_name (full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 5. CERTIFICATE TEMPLATES
-- =========================================================
CREATE TABLE certificate_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(150) NOT NULL,
    page_size VARCHAR(20) NOT NULL DEFAULT 'A4',
    orientation ENUM('portrait','landscape') NOT NULL DEFAULT 'landscape',
    background_image VARCHAR(255) NULL,
    body_html LONGTEXT NULL,
    default_signature_image VARCHAR(255) NULL,
    qr_enabled TINYINT(1) NOT NULL DEFAULT 1,
    serial_prefix VARCHAR(20) NOT NULL DEFAULT 'CERT',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_certificate_templates_status (status),
    CONSTRAINT fk_certificate_templates_created_by
        FOREIGN KEY (created_by) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 6. EVENTS
-- =========================================================
CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_code VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    event_type VARCHAR(100) NULL,
    venue VARCHAR(200) NULL,
    banner_image VARCHAR(255) NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    organizer_id BIGINT UNSIGNED NOT NULL,
    certificate_template_id BIGINT UNSIGNED NULL,
    attendance_mode ENUM('whole_event','per_day','per_session') NOT NULL DEFAULT 'per_session',
    minimum_attendance_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    require_time_out TINYINT(1) NOT NULL DEFAULT 0,
    registration_open_at DATETIME NULL,
    registration_close_at DATETIME NULL,
    public_registration_enabled TINYINT(1) NOT NULL DEFAULT 1,
    badge_enabled TINYINT(1) NOT NULL DEFAULT 1,
    certificate_enabled TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('draft','published','ongoing','completed','cancelled') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_events_event_code (event_code),
    KEY idx_events_organizer_id (organizer_id),
    KEY idx_events_dates (start_date, end_date),
    KEY idx_events_status (status),
    CONSTRAINT fk_events_organizer
        FOREIGN KEY (organizer_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_events_certificate_template
        FOREIGN KEY (certificate_template_id) REFERENCES certificate_templates(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 7. EVENT SPEAKERS
-- Many speakers can belong to one event
-- =========================================================
CREATE TABLE event_speakers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    speaker_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_speakers (event_id, speaker_id),
    KEY idx_event_speakers_speaker_id (speaker_id),
    CONSTRAINT fk_event_speakers_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_event_speakers_speaker
        FOREIGN KEY (speaker_id) REFERENCES speakers(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 8. EVENT SESSIONS
-- Every event should have at least one session
-- =========================================================
CREATE TABLE event_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    session_code VARCHAR(50) NOT NULL,
    session_title VARCHAR(200) NOT NULL,
    session_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    grace_minutes INT NOT NULL DEFAULT 15,
    attendance_required TINYINT(1) NOT NULL DEFAULT 1,
    certificate_countable TINYINT(1) NOT NULL DEFAULT 1,
    session_status ENUM('scheduled','open','closed','cancelled') NOT NULL DEFAULT 'scheduled',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_sessions_code (session_code),
    KEY idx_event_sessions_event_id (event_id),
    KEY idx_event_sessions_date (session_date),
    CONSTRAINT fk_event_sessions_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 9. SESSION SPEAKERS
-- If you want multiple speakers per session
-- =========================================================
CREATE TABLE session_speakers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id BIGINT UNSIGNED NOT NULL,
    speaker_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_session_speakers (session_id, speaker_id),
    KEY idx_session_speakers_speaker_id (speaker_id),
    CONSTRAINT fk_session_speakers_session
        FOREIGN KEY (session_id) REFERENCES event_sessions(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_session_speakers_speaker
        FOREIGN KEY (speaker_id) REFERENCES speakers(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 10. EVENT REGISTRATIONS
-- Junction table for attendees and events
-- =========================================================
CREATE TABLE event_registrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    attendee_id BIGINT UNSIGNED NOT NULL,
    registration_type ENUM('online','manual','imported') NOT NULL DEFAULT 'online',
    reference_no VARCHAR(50) NOT NULL,
    registration_status ENUM('pending','approved','rejected','cancelled','attended') NOT NULL DEFAULT 'pending',
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remarks VARCHAR(255) NULL,
    UNIQUE KEY uq_event_registrations (event_id, attendee_id),
    UNIQUE KEY uq_event_registrations_reference_no (reference_no),
    KEY idx_event_registrations_attendee_id (attendee_id),
    KEY idx_event_registrations_status (registration_status),
    CONSTRAINT fk_event_registrations_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_event_registrations_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_event_registrations_approved_by
        FOREIGN KEY (approved_by) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 11. RFID TAGS
-- History-friendly table for assigned RFID tags
-- =========================================================
CREATE TABLE rfid_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    attendee_id BIGINT UNSIGNED NOT NULL,
    rfid_uid VARCHAR(100) NOT NULL,
    tag_status ENUM('active','inactive','lost','replaced') NOT NULL DEFAULT 'active',
    assigned_by BIGINT UNSIGNED NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unassigned_at DATETIME NULL,
    notes VARCHAR(255) NULL,
    UNIQUE KEY uq_rfid_tags_uid (rfid_uid),
    KEY idx_rfid_tags_attendee_id (attendee_id),
    KEY idx_rfid_tags_status (tag_status),
    CONSTRAINT fk_rfid_tags_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_rfid_tags_assigned_by
        FOREIGN KEY (assigned_by) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 12. RFID SCAN LOGS
-- Stores ALL scan attempts
-- =========================================================
CREATE TABLE rfid_scan_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    session_id BIGINT UNSIGNED NOT NULL,
    attendee_id BIGINT UNSIGNED NULL,
    rfid_uid VARCHAR(100) NOT NULL,
    scan_type ENUM('time_in','time_out') NOT NULL DEFAULT 'time_in',
    scan_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    result_status ENUM(
        'success',
        'duplicate',
        'invalid_tag',
        'not_registered',
        'session_closed',
        'already_checked_out',
        'error'
    ) NOT NULL DEFAULT 'success',
    result_message VARCHAR(255) NULL,
    scanner_user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_rfid_scan_logs_event_id (event_id),
    KEY idx_rfid_scan_logs_session_id (session_id),
    KEY idx_rfid_scan_logs_attendee_id (attendee_id),
    KEY idx_rfid_scan_logs_uid (rfid_uid),
    KEY idx_rfid_scan_logs_time (scan_time),
    CONSTRAINT fk_rfid_scan_logs_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_rfid_scan_logs_session
        FOREIGN KEY (session_id) REFERENCES event_sessions(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_rfid_scan_logs_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_rfid_scan_logs_scanner
        FOREIGN KEY (scanner_user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 13. ATTENDANCE RECORDS
-- Final attendance result per attendee per session
-- =========================================================
CREATE TABLE attendance_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    session_id BIGINT UNSIGNED NOT NULL,
    attendee_id BIGINT UNSIGNED NOT NULL,
    check_in_time DATETIME NULL,
    check_out_time DATETIME NULL,
    attendance_status ENUM('present','late','absent','excused') NOT NULL DEFAULT 'present',
    attendance_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00,
    remarks VARCHAR(255) NULL,
    marked_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_attendance_records (session_id, attendee_id),
    KEY idx_attendance_records_event_id (event_id),
    KEY idx_attendance_records_attendee_id (attendee_id),
    KEY idx_attendance_records_status (attendance_status),
    CONSTRAINT fk_attendance_records_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_attendance_records_session
        FOREIGN KEY (session_id) REFERENCES event_sessions(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_attendance_records_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_attendance_records_marked_by
        FOREIGN KEY (marked_by) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 14. CERTIFICATES
-- =========================================================
CREATE TABLE certificates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    attendee_id BIGINT UNSIGNED NOT NULL,
    certificate_template_id BIGINT UNSIGNED NULL,
    certificate_no VARCHAR(100) NOT NULL,
    verification_code VARCHAR(100) NOT NULL,
    qr_image_path VARCHAR(255) NULL,
    pdf_path VARCHAR(255) NULL,
    eligibility_status ENUM('eligible','not_eligible') NOT NULL DEFAULT 'not_eligible',
    issued_by BIGINT UNSIGNED NULL,
    issued_at DATETIME NULL,
    emailed_at DATETIME NULL,
    revoked_at DATETIME NULL,
    revoke_reason VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_certificates_no (certificate_no),
    UNIQUE KEY uq_certificates_verification_code (verification_code),
    UNIQUE KEY uq_certificates_event_attendee (event_id, attendee_id),
    KEY idx_certificates_attendee_id (attendee_id),
    KEY idx_certificates_eligibility (eligibility_status),
    CONSTRAINT fk_certificates_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_certificates_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_certificates_template
        FOREIGN KEY (certificate_template_id) REFERENCES certificate_templates(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_certificates_issued_by
        FOREIGN KEY (issued_by) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 15. BADGES
-- =========================================================
CREATE TABLE badges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    attendee_id BIGINT UNSIGNED NOT NULL,
    badge_no VARCHAR(100) NOT NULL,
    badge_template_name VARCHAR(150) NULL,
    qr_code_path VARCHAR(255) NULL,
    printed_at DATETIME NULL,
    printed_by BIGINT UNSIGNED NULL,
    status ENUM('generated','printed','reprinted') NOT NULL DEFAULT 'generated',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_badges_badge_no (badge_no),
    UNIQUE KEY uq_badges_event_attendee (event_id, attendee_id),
    KEY idx_badges_attendee_id (attendee_id),
    CONSTRAINT fk_badges_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_badges_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_badges_printed_by
        FOREIGN KEY (printed_by) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 16. EMAIL LOGS
-- Useful for certificate sending and registration notices
-- =========================================================
CREATE TABLE email_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NULL,
    attendee_id BIGINT UNSIGNED NULL,
    certificate_id BIGINT UNSIGNED NULL,
    email_to VARCHAR(150) NOT NULL,
    email_type ENUM('registration','certificate','reminder','notice') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body_excerpt TEXT NULL,
    sent_status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
    error_message TEXT NULL,
    sent_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_email_logs_event_id (event_id),
    KEY idx_email_logs_attendee_id (attendee_id),
    KEY idx_email_logs_certificate_id (certificate_id),
    KEY idx_email_logs_status (sent_status),
    CONSTRAINT fk_email_logs_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_email_logs_attendee
        FOREIGN KEY (attendee_id) REFERENCES attendees(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_email_logs_certificate
        FOREIGN KEY (certificate_id) REFERENCES certificates(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 17. AUDIT LOGS
-- =========================================================
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    module VARCHAR(100) NOT NULL,
    action VARCHAR(100) NOT NULL,
    reference_table VARCHAR(100) NULL,
    reference_id BIGINT UNSIGNED NULL,
    description TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_logs_user_id (user_id),
    KEY idx_audit_logs_module (module),
    KEY idx_audit_logs_reference (reference_table, reference_id),
    CONSTRAINT fk_audit_logs_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 18. SYSTEM SETTINGS
-- Optional but useful
-- =========================================================
CREATE TABLE system_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_system_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- SEED DEFAULT ROLES
-- =========================================================
INSERT INTO roles (role_name, description) VALUES
('super_admin', 'Full system access'),
('organizer', 'Can manage own events'),
('staff', 'Can scan attendance and assist'),
('attendee', 'Can register and access own records');