-- =====================================================================
-- ESL Price Tag Demo Scheduling Portal
-- Full database schema (covers all planned modules)
-- Engine: InnoDB | Charset: utf8mb4
--
-- Import this file once, via CyberPanel's phpMyAdmin/Database Manager,
-- or via:  mysql -u <user> -p <database> < database/schema.sql
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- admin_users
-- Single admin account model (per project requirements). The table
-- structure supports multiple accounts should that ever be needed,
-- but only one active login is expected/created.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username                VARCHAR(60)  NOT NULL,
    email                   VARCHAR(190) NOT NULL,
    password_hash           VARCHAR(255) NOT NULL,
    password_reset_token    VARCHAR(64)  NULL,
    password_reset_expires  DATETIME     NULL,
    last_login_at           DATETIME     NULL,
    last_login_ip           VARCHAR(45)  NULL,
    status                  ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_admin_username (username),
    UNIQUE KEY uq_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- settings
-- Simple key/value store for all admin-configurable application
-- settings (session duration, buffer time, YouTube stream ID, mobile
-- app URL reference, company info, notification email, etc).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key    VARCHAR(100) NOT NULL,
    setting_value  TEXT NULL,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- business_hours
-- Weekly recurring availability template. day_of_week: 0=Sunday .. 6=Saturday
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS business_hours (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    day_of_week  TINYINT UNSIGNED NOT NULL,
    is_active    TINYINT(1) NOT NULL DEFAULT 0,
    start_time   TIME NOT NULL,
    end_time     TIME NOT NULL,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_business_hours_day (day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- blocked_dates
-- Specific calendar dates (holidays, maintenance, etc.) with no
-- availability regardless of the weekly business_hours template.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS blocked_dates (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blocked_date  DATE NOT NULL,
    reason        VARCHAR(255) NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_blocked_date (blocked_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- bookings
-- Core scheduling table. Only one non-cancelled booking may exist for
-- a given date/start_time combination — enforced at the application
-- layer inside the booking engine (Module 4) using a transaction plus
-- this supporting index.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bookings (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_name         VARCHAR(150) NOT NULL,
    customer_email        VARCHAR(190) NOT NULL,
    customer_phone        VARCHAR(30)  NOT NULL,
    booking_date          DATE NOT NULL,
    start_time            TIME NOT NULL,
    end_time              TIME NOT NULL,
    status                ENUM('confirmed','cancelled','completed','no_show') NOT NULL DEFAULT 'confirmed',
    access_token          CHAR(64) NOT NULL,
    admin_notes           TEXT NULL,
    cancellation_reason   VARCHAR(255) NULL,
    cancelled_at          DATETIME NULL,
    reminder_sent_at      DATETIME NULL,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_bookings_access_token (access_token),
    KEY idx_bookings_date_time_status (booking_date, start_time, status),
    KEY idx_bookings_email (customer_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- email_templates
-- Editable templates used by PHPMailer for outgoing notifications.
-- Supported placeholder tokens are documented in Module 7.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_templates (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_key  VARCHAR(50) NOT NULL,
    subject       VARCHAR(255) NOT NULL,
    body_html     MEDIUMTEXT NOT NULL,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_email_templates_key (template_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- email_logs
-- Audit trail of every email sent by the system.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_logs (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_id       INT UNSIGNED NULL,
    template_key     VARCHAR(50) NOT NULL,
    recipient_email  VARCHAR(190) NOT NULL,
    status           ENUM('sent','failed') NOT NULL,
    error_message    TEXT NULL,
    sent_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_email_logs_booking (booking_id),
    CONSTRAINT fk_email_logs_booking FOREIGN KEY (booking_id)
        REFERENCES bookings (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- activity_logs
-- Audit trail of administrative actions (login, settings changes,
-- booking cancellations, etc.).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_logs (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id     INT UNSIGNED NULL,
    action       VARCHAR(100) NOT NULL,
    description  TEXT NULL,
    ip_address   VARCHAR(45) NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activity_logs_admin (admin_id),
    CONSTRAINT fk_activity_logs_admin FOREIGN KEY (admin_id)
        REFERENCES admin_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- Default seed data (safe to import — contains no credentials)
-- =====================================================================

-- Default weekly business hours: Sunday-Thursday 09:00-17:00 active,
-- Friday/Saturday inactive (typical Saudi Arabia work week default).
-- Adjust freely from Admin > Settings > Business Hours (Module 3).
INSERT INTO business_hours (day_of_week, is_active, start_time, end_time) VALUES
    (0, 1, '09:00:00', '17:00:00'), -- Sunday
    (1, 1, '09:00:00', '17:00:00'), -- Monday
    (2, 1, '09:00:00', '17:00:00'), -- Tuesday
    (3, 1, '09:00:00', '17:00:00'), -- Wednesday
    (4, 1, '09:00:00', '17:00:00'), -- Thursday
    (5, 0, '09:00:00', '17:00:00'), -- Friday
    (6, 0, '09:00:00', '17:00:00')  -- Saturday
ON DUPLICATE KEY UPDATE day_of_week = day_of_week;

-- Default application settings.
INSERT INTO settings (setting_key, setting_value) VALUES
    ('company_name', 'ESL Tag Demo Center'),
    ('notification_email', ''),
    ('session_duration_minutes', '30'),
    ('buffer_minutes', '10'),
    ('booking_lead_time_hours', '2'),
    ('booking_horizon_days', '30'),
    ('mobile_app_url', ''),
    ('youtube_video_id', ''),
    ('timezone', 'Asia/Riyadh')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Default email templates (editable via Admin > Settings > Email Templates in Module 7).
INSERT INTO email_templates (template_key, subject, body_html) VALUES
(
    'confirmation',
    'Your ESL Tag Demo Session is Confirmed',
    '<p>Hi {{customer_name}},</p><p>Your 30-minute ESL Price Tag demo session is confirmed for:</p><p><strong>{{booking_date}} at {{start_time}}</strong></p><p>You can view your session details or cancel here: <a href="{{session_link}}">{{session_link}}</a></p><p>We look forward to seeing you.</p>'
),
(
    'reminder',
    'Reminder: Your ESL Tag Demo Session Starts Soon',
    '<p>Hi {{customer_name}},</p><p>This is a reminder that your ESL Price Tag demo session begins at <strong>{{start_time}}</strong> on {{booking_date}}.</p><p>Session details: <a href="{{session_link}}">{{session_link}}</a></p>'
),
(
    'cancellation',
    'Your ESL Tag Demo Session Has Been Cancelled',
    '<p>Hi {{customer_name}},</p><p>Your demo session scheduled for {{booking_date}} at {{start_time}} has been cancelled.</p><p>You are welcome to book a new session at any time.</p>'
)
ON DUPLICATE KEY UPDATE template_key = template_key;

-- NOTE: No admin_users row is seeded here for security reasons.
-- Create the first administrator account using the CLI installer
-- introduced in Module 2: php bin/create-admin.php