-- ============================================================
-- Health Agency Platform — Database Schema
-- Engine: MySQL 5.7+ | Charset: utf8mb4
-- ============================================================

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

-- ------------------------------------------------------------
-- USERS
-- ------------------------------------------------------------
CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(120)        NOT NULL,
    email       VARCHAR(180)        NOT NULL UNIQUE,
    password    VARCHAR(255)        NOT NULL,
    role        ENUM('user','admin') NOT NULL DEFAULT 'user',
    avatar      VARCHAR(255)        DEFAULT NULL,
    created_at  TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- EVENTS  (announcements + past events)
-- ------------------------------------------------------------
CREATE TABLE events (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255)                NOT NULL,
    description TEXT                        NOT NULL,
    event_date  DATE                        NOT NULL,
    location    VARCHAR(255)                DEFAULT NULL,
    image       VARCHAR(255)                DEFAULT NULL,
    status      ENUM('upcoming','past')     NOT NULL DEFAULT 'upcoming',
    created_at  TIMESTAMP                   NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- CLASSES  (online paid sessions)
-- ------------------------------------------------------------
CREATE TABLE classes (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(255)               NOT NULL,
    description  TEXT                       NOT NULL,
    instructor   VARCHAR(120)               NOT NULL,
    price        DECIMAL(10,2)              NOT NULL,
    class_date   DATETIME                   NOT NULL,
    duration_min INT UNSIGNED               NOT NULL DEFAULT 60,
    meeting_link VARCHAR(500)               DEFAULT NULL,
    image        VARCHAR(255)               DEFAULT NULL,
    seats        INT UNSIGNED               NOT NULL DEFAULT 50,
    status       ENUM('active','cancelled','completed') NOT NULL DEFAULT 'active',
    created_at   TIMESTAMP                  NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- CLASS ENROLLMENTS
-- ------------------------------------------------------------
CREATE TABLE enrollments (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    class_id     INT UNSIGNED NOT NULL,
    payment_ref  VARCHAR(100) NOT NULL,
    amount_paid  DECIMAL(10,2) NOT NULL,
    enrolled_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_enrollment (user_id, class_id),
    FOREIGN KEY (user_id)  REFERENCES users(id)   ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PRODUCTS  (health products shop)
-- ------------------------------------------------------------
CREATE TABLE products (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(255)    NOT NULL,
    description  TEXT            NOT NULL,
    price        DECIMAL(10,2)   NOT NULL,
    stock        INT UNSIGNED    NOT NULL DEFAULT 0,
    image        VARCHAR(255)    DEFAULT NULL,
    category     VARCHAR(100)    DEFAULT NULL,
    featured     TINYINT(1)      NOT NULL DEFAULT 0,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ORDERS
-- ------------------------------------------------------------
CREATE TABLE orders (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED    NOT NULL,
    total        DECIMAL(10,2)   NOT NULL,
    status       ENUM('pending','paid','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',
    payment_ref  VARCHAR(100)    DEFAULT NULL,
    address      TEXT            NOT NULL,
    phone        VARCHAR(30)     NOT NULL,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ORDER ITEMS
-- ------------------------------------------------------------
CREATE TABLE order_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id    INT UNSIGNED    NOT NULL,
    product_id  INT UNSIGNED    NOT NULL,
    quantity    INT UNSIGNED    NOT NULL,
    unit_price  DECIMAL(10,2)   NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PAYMENTS  (unified log for class + product payments)
-- ------------------------------------------------------------
CREATE TABLE payments (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED    NOT NULL,
    reference    VARCHAR(100)    NOT NULL UNIQUE,
    amount       DECIMAL(10,2)   NOT NULL,
    type         ENUM('class','order') NOT NULL,
    related_id   INT UNSIGNED    NOT NULL,
    status       ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    gateway_resp JSON            DEFAULT NULL,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- MEDIA  (uploaded image library — filesystem + DB in sync)
-- ------------------------------------------------------------
CREATE TABLE media (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uploaded_by  INT UNSIGNED    NOT NULL,              -- admin user who uploaded
    filename     VARCHAR(255)    NOT NULL UNIQUE,       -- stored filename on disk
    original_name VARCHAR(255)   NOT NULL,              -- original file name from user
    url          VARCHAR(500)    NOT NULL,              -- full public URL
    file_size    INT UNSIGNED    NOT NULL,              -- bytes
    mime_type    VARCHAR(100)    NOT NULL,
    alt_text     VARCHAR(255)    DEFAULT NULL,          -- optional alt / caption
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SEED: default admin account  (password: Admin@1234)
-- Change this immediately after first login!
-- ------------------------------------------------------------
INSERT INTO users (name, email, password, role) VALUES (
    'Admin',
    'admin@healthagency.com',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'admin'
);

-- Run this if you already created the DB without the media table:
-- CREATE TABLE media ( ... ) — copy the CREATE TABLE above and run it in phpMyAdmin
