-- ============================================================
--  Asaju Gaming Centre -- MySQL 5.0.45 Compatible Schema
--
--  Changes from the original:
--  1. JSON column type replaced with TEXT (JSON type needs 5.7+)
--  2. utf8mb4 charset replaced with utf8 (utf8mb4 needs 5.5.3+)
--  3. utf8mb4_unicode_ci collation replaced with utf8_general_ci
--  4. PRIMARY KEY and UNIQUE KEY moved out of column definition
--     and declared separately (more reliable on 5.0.x)
-- ============================================================

CREATE DATABASE IF NOT EXISTS asaju_db
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

USE asaju_db;

-- ── Students ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS students (
    id                 INT UNSIGNED        NOT NULL AUTO_INCREMENT,
    full_name          VARCHAR(150)        NOT NULL,
    email              VARCHAR(200)        NOT NULL,
    phone              VARCHAR(20)         NOT NULL,
    subjects           TEXT                NOT NULL,
    payment_status     ENUM('trial','paid') NOT NULL DEFAULT 'trial',
    registration_date  DATE                NOT NULL,
    trial_end_date     DATE                NOT NULL,
    lessons_completed  SMALLINT UNSIGNED   NOT NULL DEFAULT 0,
    total_score        MEDIUMINT UNSIGNED  NOT NULL DEFAULT 0,
    created_at         TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ── Payments ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payments (
    id             INT UNSIGNED       NOT NULL AUTO_INCREMENT,
    student_id     INT UNSIGNED       NOT NULL,
    student_name   VARCHAR(150)       NOT NULL,
    email          VARCHAR(200)       NOT NULL,
    phone          VARCHAR(20)        NOT NULL,
    amount         MEDIUMINT UNSIGNED NOT NULL,
    method         VARCHAR(50)        NOT NULL,
    payment_date   DATE               NOT NULL,
    expiry_date    DATE               NOT NULL,
    paid_at        TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_payment_student
        FOREIGN KEY (student_id) REFERENCES students(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;