-- ============================================================
-- Mowe-Ibafo X Community Fitness Walk 2026 — Database Schema
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+01:00";

-- ── Admins ────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admins` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(100) NOT NULL,
  `email`      VARCHAR(150) NOT NULL UNIQUE,
  `password`   VARCHAR(255) NOT NULL,
  `role`       ENUM('super','admin') NOT NULL DEFAULT 'admin',
  `last_login` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin: admin@mxcommunity.com / Admin@2026
-- The password hash below was generated by PHP password_hash('Admin@2026', PASSWORD_BCRYPT, ['cost' => 12])
INSERT INTO `admins` (`name`, `email`, `password`, `role`) VALUES
('Super Admin', 'admin@mxcommunity.com', '$2y$12$oyhJZMNF/8CnnALoApooouH/YpprmCSEJsdVNV6t.QhE.wUibxo/a', 'super')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `password` = VALUES(`password`), `role` = VALUES(`role`);

-- ── Event Settings ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `event_settings` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_name`          VARCHAR(200) NOT NULL DEFAULT 'Mowe-Ibafo X Community Fitness Walk 2026',
  `description`         TEXT,
  `event_date`          DATE DEFAULT NULL,
  `event_time`          TIME DEFAULT NULL,
  `venue`               VARCHAR(300) DEFAULT NULL,
  `registration_open`   TINYINT(1) NOT NULL DEFAULT 1,
  `banner_path`         VARCHAR(500) DEFAULT NULL,
  `contact_email`       VARCHAR(150) DEFAULT NULL,
  `contact_phone`       VARCHAR(30) DEFAULT NULL,
  `whatsapp_number`     VARCHAR(30) DEFAULT '2347061038567',
  `twitter_url`         VARCHAR(300) DEFAULT 'https://x.com/MoweTwitta',
  `instagram_url`       VARCHAR(300) DEFAULT NULL,
  `facebook_url`        VARCHAR(300) DEFAULT NULL,
  `updated_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `event_settings` (`event_name`, `description`, `registration_open`, `contact_email`, `whatsapp_number`, `twitter_url`)
VALUES ('Mowe-Ibafo X Community Fitness Walk 2026',
        'Promote healthy living, community unity, wellness, and active lifestyles.',
        1, 'hello@mxcommunity.com', '2347061038567', 'https://x.com/MoweTwitta');

-- ── Participants ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `participants` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `participant_id`  VARCHAR(30) NOT NULL UNIQUE,
  `full_name`       VARCHAR(150) NOT NULL,
  `email`           VARCHAR(150) NOT NULL,
  `phone`           VARCHAR(30) NOT NULL,
  `photo_path`      VARCHAR(500) NOT NULL,
  `flyer_path`      VARCHAR(500) DEFAULT NULL,
  `qr_path`         VARCHAR(500) DEFAULT NULL,
  `status`          ENUM('registered','verified','cancelled') NOT NULL DEFAULT 'registered',
  `ip_address`      VARCHAR(45) DEFAULT NULL,
  `registered_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email`  (`email`),
  INDEX `idx_phone`  (`phone`),
  INDEX `idx_status` (`status`),
  INDEX `idx_date`   (`registered_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Sponsors ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sponsors` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `business_name` VARCHAR(200) NOT NULL,
  `logo_path`     VARCHAR(500) DEFAULT NULL,
  `website_url`   VARCHAR(500) DEFAULT NULL,
  `whatsapp`      VARCHAR(30) DEFAULT NULL,
  `description`   TEXT DEFAULT NULL,
  `priority`      INT UNSIGNED NOT NULL DEFAULT 0,
  `status`        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status`   (`status`),
  INDEX `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Gallery ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `gallery` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `image_path`  VARCHAR(500) NOT NULL,
  `caption`     VARCHAR(300) DEFAULT NULL,
  `category`    VARCHAR(100) DEFAULT 'general',
  `sort_order`  INT UNSIGNED NOT NULL DEFAULT 0,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_status`     (`status`),
  INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Contact Messages ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `contacts` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(150) NOT NULL,
  `email`       VARCHAR(150) NOT NULL,
  `phone`       VARCHAR(30) DEFAULT NULL,
  `subject`     VARCHAR(300) NOT NULL,
  `message`     TEXT NOT NULL,
  `reply`       TEXT DEFAULT NULL,
  `status`      ENUM('new','read','replied','resolved') NOT NULL DEFAULT 'new',
  `ip_address`  VARCHAR(45) DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_email`  (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Email Log ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `email_log` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `recipient`    VARCHAR(150) NOT NULL,
  `subject`      VARCHAR(300) NOT NULL,
  `status`       ENUM('sent','failed') NOT NULL DEFAULT 'sent',
  `sent_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;