-- ============================================================
-- PrepPlus — Complete Database Install Script
-- Import this file via phpMyAdmin to set up a fresh database.
-- ============================================================
-- HOW TO USE (no terminal required):
--   1. Log in to cPanel → phpMyAdmin
--   2. Select your PrepPlus database from the left sidebar
--   3. Click the "Import" tab at the top
--   4. Choose this file (install.sql) and click "Go"
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET time_zone = '+00:00';

-- ── Drop tables in safe order (children before parents) ─────────────────────

DROP TABLE IF EXISTS discussion_memberships;
DROP TABLE IF EXISTS discussion_posts;
DROP TABLE IF EXISTS discussion_groups;
DROP TABLE IF EXISTS attempts;
DROP TABLE IF EXISTS exams;
DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS topics;
DROP TABLE IF EXISTS textbook_resources;
DROP TABLE IF EXISTS curricula;
DROP TABLE IF EXISTS wallet_transactions;
DROP TABLE IF EXISTS withdrawal_requests;
DROP TABLE IF EXISTS competition_results;
DROP TABLE IF EXISTS competitions;
DROP TABLE IF EXISTS referrals;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS licenses;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS institutions;
DROP TABLE IF EXISTS users;

-- ── 1. institutions ──────────────────────────────────────────────────────────

CREATE TABLE institutions (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  code       VARCHAR(128) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2. users ─────────────────────────────────────────────────────────────────

CREATE TABLE users (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(200)    DEFAULT NULL,
  email          VARCHAR(255)    NOT NULL UNIQUE,
  phone          VARCHAR(32)     DEFAULT NULL,
  password_hash  VARCHAR(255)    NOT NULL,
  device_id      VARCHAR(255)    DEFAULT NULL,
  referral_code  VARCHAR(64)     DEFAULT NULL,
  referred_by_id BIGINT UNSIGNED DEFAULT NULL,
  wallet_balance DECIMAL(12,2)   DEFAULT 0.00,
  is_active      TINYINT(1)      DEFAULT 0,
  role           ENUM('student','admin') NOT NULL DEFAULT 'student',
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_referral_code (referral_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. subjects ──────────────────────────────────────────────────────────────

CREATE TABLE subjects (
  id             SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code           VARCHAR(32)   NOT NULL UNIQUE,
  name           VARCHAR(150)  NOT NULL,
  institution_id BIGINT UNSIGNED DEFAULT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (institution_id) REFERENCES institutions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 4. topics ────────────────────────────────────────────────────────────────

CREATE TABLE topics (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subject_id SMALLINT UNSIGNED NOT NULL,
  name       VARCHAR(200) NOT NULL,
  meta       JSON         DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 5. licenses ──────────────────────────────────────────────────────────────

CREATE TABLE licenses (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id            BIGINT UNSIGNED NOT NULL,
  device_fingerprint VARCHAR(512) NOT NULL,
  license_key        VARCHAR(255) NOT NULL UNIQUE,
  status             ENUM('active','revoked','expired') DEFAULT 'active',
  issued_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at         TIMESTAMP NULL DEFAULT NULL,
  meta               JSON DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 6. questions ─────────────────────────────────────────────────────────────

CREATE TABLE questions (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subject_id            SMALLINT UNSIGNED NOT NULL,
  topic_id              INT UNSIGNED      DEFAULT NULL,
  year                  SMALLINT UNSIGNED DEFAULT NULL,
  difficulty            ENUM('easy','medium','hard') DEFAULT 'medium',
  exam_body             ENUM('waec','neco','nabteb','jamb','post_utme','general') DEFAULT 'general',
  question_type         ENUM('mcq','theory') DEFAULT 'mcq',
  stem                  TEXT         NOT NULL,
  options               JSON         DEFAULT NULL,
  answer                VARCHAR(255) DEFAULT NULL,
  model_answer          TEXT         DEFAULT NULL,
  diagram_url           TEXT         DEFAULT NULL,
  comprehension_passage TEXT         DEFAULT NULL,
  comprehension_image   TEXT         DEFAULT NULL,
  explanation           TEXT         DEFAULT NULL,
  tags                  JSON         DEFAULT NULL,
  created_by            BIGINT UNSIGNED DEFAULT NULL,
  created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_subject_year  (subject_id, year),
  INDEX idx_exam_body     (exam_body),
  INDEX idx_question_type (question_type),
  FULLTEXT INDEX ft_stem  (stem),
  FOREIGN KEY (subject_id) REFERENCES subjects(id)  ON DELETE CASCADE,
  FOREIGN KEY (topic_id)   REFERENCES topics(id)    ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id)     ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 7. exams ─────────────────────────────────────────────────────────────────

CREATE TABLE exams (
  id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id              BIGINT UNSIGNED NOT NULL,
  institution_id       BIGINT UNSIGNED DEFAULT NULL,
  type                 ENUM('exam','practice') NOT NULL,
  exam_body            ENUM('waec','neco','nabteb','jamb','post_utme','general') DEFAULT NULL,
  question_type_filter ENUM('mcq','theory','mixed') DEFAULT 'mcq',
  subjects             JSON         NOT NULL,
  question_ids         JSON         DEFAULT NULL,
  time_limit_minutes   INT UNSIGNED DEFAULT NULL,
  config               JSON         DEFAULT NULL,
  started_at           TIMESTAMP    NULL DEFAULT NULL,
  finished_at          TIMESTAMP    NULL DEFAULT NULL,
  score                DECIMAL(6,2) DEFAULT NULL,
  created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)        REFERENCES users(id)        ON DELETE CASCADE,
  FOREIGN KEY (institution_id) REFERENCES institutions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 8. attempts ──────────────────────────────────────────────────────────────

CREATE TABLE attempts (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  exam_id             BIGINT UNSIGNED NOT NULL,
  question_id         BIGINT UNSIGNED NOT NULL,
  local_attempt_uuid  VARCHAR(128) DEFAULT NULL,
  answer_given        VARCHAR(255) DEFAULT NULL,
  is_correct          TINYINT(1)   DEFAULT NULL,
  time_taken_seconds  INT UNSIGNED DEFAULT NULL,
  meta                JSON         DEFAULT NULL,
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (exam_id)     REFERENCES exams(id)     ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 9. discussion_groups ─────────────────────────────────────────────────────

CREATE TABLE discussion_groups (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(255) NOT NULL,
  scope          ENUM('school','general') DEFAULT 'general',
  school_code    VARCHAR(128) DEFAULT NULL,
  institution_id BIGINT UNSIGNED DEFAULT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (institution_id) REFERENCES institutions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 10. discussion_posts ─────────────────────────────────────────────────────

CREATE TABLE discussion_posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id   BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  parent_id  BIGINT UNSIGNED DEFAULT NULL,
  content    TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (group_id)  REFERENCES discussion_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)   REFERENCES users(id)             ON DELETE CASCADE,
  FOREIGN KEY (parent_id) REFERENCES discussion_posts(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 11. discussion_memberships ───────────────────────────────────────────────

CREATE TABLE discussion_memberships (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id   BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  joined_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_group_user (group_id, user_id),
  FOREIGN KEY (group_id) REFERENCES discussion_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)  REFERENCES users(id)             ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 12. competitions ─────────────────────────────────────────────────────────

CREATE TABLE competitions (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  week_start DATE NOT NULL,
  rules      TEXT DEFAULT NULL,
  prize      JSON DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 13. competition_results ──────────────────────────────────────────────────

CREATE TABLE competition_results (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  competition_id BIGINT UNSIGNED NOT NULL,
  user_id        BIGINT UNSIGNED NOT NULL,
  score          DECIMAL(6,2) NOT NULL,
  rank           INT DEFAULT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)        REFERENCES users(id)        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 14. referrals ────────────────────────────────────────────────────────────

CREATE TABLE referrals (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  referrer_id     BIGINT UNSIGNED NOT NULL,
  referee_id      BIGINT UNSIGNED DEFAULT NULL,
  code            VARCHAR(128) NOT NULL,
  reward_amount   DECIMAL(10,2) DEFAULT 0.00,
  generation      TINYINT UNSIGNED DEFAULT 1,
  commission_rate DECIMAL(5,2) DEFAULT 0.00,
  status          ENUM('pending','earned','redeemed') DEFAULT 'pending',
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (referee_id)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 15. payments ─────────────────────────────────────────────────────────────

CREATE TABLE payments (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id             BIGINT UNSIGNED DEFAULT NULL,
  paystack_reference  VARCHAR(255) NOT NULL UNIQUE,
  amount              DECIMAL(12,2) NOT NULL,
  currency            VARCHAR(8) DEFAULT 'NGN',
  status              ENUM('pending','success','failed') DEFAULT 'pending',
  metadata            JSON DEFAULT NULL,
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 16. wallet_transactions ──────────────────────────────────────────────────

CREATE TABLE wallet_transactions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  direction   ENUM('credit','debit') NOT NULL,
  amount      DECIMAL(12,2) NOT NULL,
  type        VARCHAR(100) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  reference   VARCHAR(255) DEFAULT NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_wallet (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 17. withdrawal_requests ──────────────────────────────────────────────────

CREATE TABLE withdrawal_requests (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id        BIGINT UNSIGNED NOT NULL,
  amount         DECIMAL(12,2) NOT NULL,
  bank_name      VARCHAR(100) NOT NULL,
  account_number VARCHAR(20)  NOT NULL,
  account_name   VARCHAR(200) NOT NULL,
  status         ENUM('pending','approved','rejected') DEFAULT 'pending',
  admin_note     VARCHAR(255) DEFAULT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 18. textbook_resources ───────────────────────────────────────────────────

CREATE TABLE textbook_resources (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subject_id    SMALLINT UNSIGNED NOT NULL,
  title         VARCHAR(255) NOT NULL,
  description   TEXT DEFAULT NULL,
  type          ENUM('pdf','video','link','note') DEFAULT 'link',
  url           VARCHAR(1000) DEFAULT NULL,
  class_level   VARCHAR(20)  DEFAULT NULL,
  term          VARCHAR(30)  DEFAULT NULL,
  thumbnail_url VARCHAR(500) DEFAULT NULL,
  author        VARCHAR(120) DEFAULT NULL,
  doc_type      VARCHAR(20)  DEFAULT 'textbook',
  content       LONGTEXT     DEFAULT NULL,
  has_content   TINYINT(1)   DEFAULT 0,
  created_by    BIGINT UNSIGNED DEFAULT NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 19. curricula ────────────────────────────────────────────────────────────

CREATE TABLE curricula (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subject_name VARCHAR(120) NOT NULL,
  doc_type     ENUM('curriculum','scheme_of_work','syllabus') DEFAULT 'syllabus',
  exam_body    ENUM('waec','neco','nabteb','general') DEFAULT 'general',
  title        VARCHAR(255) DEFAULT NULL,
  content      LONGTEXT NOT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Re-enable FK checks ───────────────────────────────────────────────────────

SET FOREIGN_KEY_CHECKS = 1;

-- ── Seed: default admin user ─────────────────────────────────────────────────
-- Password is:  Admin@123   (bcrypt hash below)
-- CHANGE THIS PASSWORD immediately after first login!

INSERT INTO users (name, email, password_hash, is_active, role, referral_code)
VALUES (
  'Admin',
  'admin@prepplus.ng',
  '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
  1,
  'admin',
  'ADMIN001'
);

-- ── Done ─────────────────────────────────────────────────────────────────────
-- All 19 tables created. Seed admin user created.
-- Login: admin@prepplus.ng / Admin@123  (change immediately!)