CREATE DATABASE IF NOT EXISTS rhif CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rhif;

CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'editor') NOT NULL DEFAULT 'editor',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS media (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(500) NOT NULL,
    file_type ENUM('image', 'video', 'video_link') NOT NULL,
    caption VARCHAR(255) NULL,
    uploaded_by INT UNSIGNED NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tags VARCHAR(500) NULL,
    CONSTRAINT fk_media_admin FOREIGN KEY (uploaded_by) REFERENCES admins(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS gallery_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    media_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(80) NOT NULL,
    poster_path VARCHAR(500) NULL,
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_gallery_media (media_id),
    INDEX idx_gallery_category (category, is_active, display_order),
    CONSTRAINT fk_gallery_media FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS team_members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    role_title VARCHAR(160) NOT NULL,
    bio TEXT NULL,
    photo_id INT UNSIGNED NULL,
    email VARCHAR(190) NULL,
    member_type ENUM('team', 'volunteer') NOT NULL DEFAULT 'team',
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_team_photo FOREIGN KEY (photo_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS board_members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    role_title VARCHAR(160) NOT NULL,
    bio TEXT NULL,
    photo_id INT UNSIGNED NULL,
    email VARCHAR(190) NULL,
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_board_photo FOREIGN KEY (photo_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    content_html MEDIUMTEXT NULL,
    location VARCHAR(255) NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NULL,
    status ENUM('draft', 'upcoming', 'ongoing', 'completed') NOT NULL DEFAULT 'draft',
    cover_image_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_event_cover FOREIGN KEY (cover_image_id) REFERENCES media(id) ON DELETE SET NULL,
    CONSTRAINT fk_event_admin FOREIGN KEY (created_by) REFERENCES admins(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS event_media (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    media_id INT UNSIGNED NOT NULL,
    media_role ENUM('before', 'during', 'after') NULL,
    UNIQUE KEY uq_event_media (event_id, media_id),
    CONSTRAINT fk_event_media_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_media_media FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS event_notes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    note_content MEDIUMTEXT NOT NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_event_note_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_note_admin FOREIGN KEY (created_by) REFERENCES admins(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS programmes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    content_html MEDIUMTEXT NULL,
    cover_image_id INT UNSIGNED NULL,
    inner_image_id INT UNSIGNED NULL,
    status ENUM('draft', 'active', 'completed') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_programme_cover FOREIGN KEY (cover_image_id) REFERENCES media(id) ON DELETE SET NULL,
    CONSTRAINT fk_programme_inner FOREIGN KEY (inner_image_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS projects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    summary TEXT NULL,
    description TEXT NULL,
    cover_image_id INT UNSIGNED NULL,
    status ENUM('draft', 'active', 'completed') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_project_cover FOREIGN KEY (cover_image_id) REFERENCES media(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscribers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    age TINYINT UNSIGNED NULL,
    phone VARCHAR(40) NULL,
    location VARCHAR(190) NULL,
    password_hash VARCHAR(255) NULL,
    subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    source VARCHAR(80) NULL,
    INDEX idx_subscribers_active_date (is_active, subscribed_at)
) ENGINE=InnoDB;

SET @age_column_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'subscribers'
      AND COLUMN_NAME = 'age'
);
SET @add_age_column = IF(
    @age_column_exists = 0,
    'ALTER TABLE subscribers ADD COLUMN age TINYINT UNSIGNED NULL AFTER email',
    'SELECT 1'
);
PREPARE add_age_column_statement FROM @add_age_column;
EXECUTE add_age_column_statement;
DEALLOCATE PREPARE add_age_column_statement;

SET @location_column_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'subscribers'
      AND COLUMN_NAME = 'location'
);
SET @add_location_column = IF(
    @location_column_exists = 0,
    'ALTER TABLE subscribers ADD COLUMN location VARCHAR(190) NULL AFTER phone',
    'SELECT 1'
);
PREPARE add_location_column_statement FROM @add_location_column;
EXECUTE add_location_column_statement;
DEALLOCATE PREPARE add_location_column_statement;

CREATE TABLE IF NOT EXISTS email_campaigns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    sent_by INT UNSIGNED NOT NULL,
    sent_at DATETIME NULL,
    recipient_count INT UNSIGNED NOT NULL DEFAULT 0,
    related_event_id INT UNSIGNED NULL,
    CONSTRAINT fk_campaign_admin FOREIGN KEY (sent_by) REFERENCES admins(id),
    CONSTRAINT fk_campaign_event FOREIGN KEY (related_event_id) REFERENCES events(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS email_campaign_recipients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT UNSIGNED NOT NULL,
    subscriber_id INT UNSIGNED NOT NULL,
    status ENUM('sent', 'failed', 'bounced') NOT NULL DEFAULT 'sent',
    UNIQUE KEY uq_campaign_subscriber (campaign_id, subscriber_id),
    CONSTRAINT fk_campaign_recipient_campaign FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_campaign_recipient_subscriber FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contact_submissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(40) NULL,
    subject VARCHAR(255) NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'read', 'archived') NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_status_date (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS volunteer_applications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(40) NULL,
    interest VARCHAR(160) NULL,
    message TEXT NULL,
    status ENUM('new', 'reviewing', 'accepted', 'declined') NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_volunteer_status_date (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS activity_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,
    action VARCHAR(120) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id INT UNSIGNED NULL,
    details JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_activity_admin FOREIGN KEY (admin_id) REFERENCES admins(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS site_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;