"use strict";
const { query, insert } = require("./db");
const { hashPassword } = require("./auth");

async function createTables() {
  const stmts = [
    `CREATE TABLE IF NOT EXISTS credentials (
      id INT AUTO_INCREMENT PRIMARY KEY,
      role VARCHAR(50) NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS school_settings (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL DEFAULT 'ScholarExam Academy',
      motto TEXT NOT NULL DEFAULT 'Knowledge. Discipline. Excellence.',
      address TEXT NOT NULL DEFAULT '12 Education Avenue, Lagos',
      phone VARCHAR(50) NOT NULL DEFAULT '+234 800 000 0000',
      email VARCHAR(100) NOT NULL DEFAULT 'info@scholarexam.school',
      logo_url TEXT,
      primary_color VARCHAR(20) DEFAULT '#3b82f6',
      currency VARCHAR(10) NOT NULL DEFAULT 'NGN',
      grading_scale TEXT NOT NULL DEFAULT 'A:80-100,B:70-79,C:60-69,D:50-59,E:40-49,F:0-39',
      term_name VARCHAR(100) NOT NULL DEFAULT 'First Term',
      session_name VARCHAR(100) NOT NULL DEFAULT '2025/2026',
      principal_name VARCHAR(100) DEFAULT 'Dr. A. Okonkwo',
      next_term_begins VARCHAR(50),
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS system_status (
      id INT AUTO_INCREMENT PRIMARY KEY,
      locked TINYINT(1) NOT NULL DEFAULT 0,
      maintenance_message TEXT NOT NULL DEFAULT 'System is undergoing scheduled maintenance.',
      version VARCHAR(20) NOT NULL DEFAULT '1.0.0',
      last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS classes (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      level VARCHAR(100) NOT NULL,
      teacher VARCHAR(100) NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS subjects (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      code VARCHAR(20) NOT NULL,
      class_id INT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS students (
      id INT AUTO_INCREMENT PRIMARY KEY,
      student_id VARCHAR(50) NOT NULL UNIQUE,
      first_name VARCHAR(100) NOT NULL,
      last_name VARCHAR(100) NOT NULL,
      class_id INT NOT NULL,
      gender VARCHAR(20) NOT NULL,
      date_of_birth VARCHAR(20),
      guardian_name VARCHAR(100),
      guardian_phone VARCHAR(50),
      photo_url TEXT,
      address TEXT,
      admission_date DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS questions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      subject_id INT NOT NULL,
      type VARCHAR(30) NOT NULL,
      prompt TEXT NOT NULL,
      options JSON,
      correct_answer TEXT,
      marks INT NOT NULL DEFAULT 1,
      difficulty VARCHAR(20),
      explanation TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS exams (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      instructions TEXT,
      subject_id INT NOT NULL,
      class_id INT NOT NULL,
      duration_minutes INT NOT NULL DEFAULT 60,
      total_marks INT NOT NULL DEFAULT 100,
      pass_mark INT,
      status VARCHAR(20) NOT NULL DEFAULT 'draft',
      scheduled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      venue VARCHAR(100),
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS exam_questions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      exam_id INT NOT NULL,
      question_id INT NOT NULL,
      position INT NOT NULL DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS exam_sessions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      exam_id INT NOT NULL,
      student_id INT NOT NULL,
      started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      submitted_at DATETIME,
      status VARCHAR(20) NOT NULL DEFAULT 'in_progress'
    )`,
    `CREATE TABLE IF NOT EXISTS exam_answers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      session_id INT NOT NULL,
      question_id INT NOT NULL,
      answer TEXT NOT NULL DEFAULT '',
      is_correct TINYINT(1),
      awarded_marks INT NOT NULL DEFAULT 0
    )`,
    `CREATE TABLE IF NOT EXISTS exam_results (
      id INT AUTO_INCREMENT PRIMARY KEY,
      exam_id INT NOT NULL,
      student_id INT NOT NULL,
      session_id INT,
      score INT NOT NULL DEFAULT 0,
      total_marks INT NOT NULL DEFAULT 0,
      percentage FLOAT NOT NULL DEFAULT 0,
      grade VARCHAR(5) NOT NULL DEFAULT 'F',
      remark TEXT,
      submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS invoices (
      id INT AUTO_INCREMENT PRIMARY KEY,
      invoice_number VARCHAR(50) NOT NULL UNIQUE,
      student_id INT NOT NULL,
      term VARCHAR(100) NOT NULL,
      items JSON NOT NULL,
      total FLOAT NOT NULL DEFAULT 0,
      amount_paid FLOAT NOT NULL DEFAULT 0,
      status VARCHAR(20) NOT NULL DEFAULT 'pending',
      issued_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      due_date VARCHAR(20),
      notes TEXT
    )`,
    `CREATE TABLE IF NOT EXISTS timetable_entries (
      id INT AUTO_INCREMENT PRIMARY KEY,
      class_id INT NOT NULL,
      subject_id INT NOT NULL,
      date VARCHAR(20) NOT NULL,
      start_time VARCHAR(10) NOT NULL,
      end_time VARCHAR(10) NOT NULL,
      venue VARCHAR(100) NOT NULL,
      teacher VARCHAR(100) NOT NULL,
      term VARCHAR(100) NOT NULL
    )`,
    `CREATE TABLE IF NOT EXISTS activity_log (
      id INT AUTO_INCREMENT PRIMARY KEY,
      type VARCHAR(50) NOT NULL,
      message TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS messages (
      id INT AUTO_INCREMENT PRIMARY KEY,
      sender_role VARCHAR(50) NOT NULL,
      sender_name VARCHAR(100) NOT NULL,
      audience VARCHAR(50) NOT NULL,
      target_class_id INT,
      target_student_id INT,
      subject VARCHAR(255) NOT NULL,
      body TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS message_reads (
      id INT AUTO_INCREMENT PRIMARY KEY,
      message_id INT NOT NULL,
      reader_role VARCHAR(50) NOT NULL,
      reader_key VARCHAR(100) NOT NULL,
      read_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_read (message_id, reader_role, reader_key)
    )`,
    `CREATE TABLE IF NOT EXISTS attendance (
      id INT AUTO_INCREMENT PRIMARY KEY,
      class_id INT NOT NULL,
      student_id INT NOT NULL,
      date DATE NOT NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'present',
      marked_by VARCHAR(100) NOT NULL DEFAULT '',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_att (class_id, student_id, date)
    )`,
    `CREATE TABLE IF NOT EXISTS notices (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      body TEXT NOT NULL,
      audience VARCHAR(50) NOT NULL DEFAULT 'all',
      pinned TINYINT(1) NOT NULL DEFAULT 0,
      created_by VARCHAR(100) NOT NULL DEFAULT '',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS teachers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      email VARCHAR(100) NOT NULL DEFAULT '',
      phone VARCHAR(50) NOT NULL DEFAULT '',
      qualification VARCHAR(200) NOT NULL DEFAULT '',
      subjects JSON,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS gradebook (
      id INT AUTO_INCREMENT PRIMARY KEY,
      student_id INT NOT NULL,
      class_id INT NOT NULL,
      subject_id INT NOT NULL,
      term VARCHAR(100) NOT NULL DEFAULT '',
      academic_year VARCHAR(50) NOT NULL DEFAULT '',
      ca_score DECIMAL(6,2) NOT NULL DEFAULT 0,
      exam_score DECIMAL(6,2) NOT NULL DEFAULT 0,
      total_score DECIMAL(6,2) NOT NULL DEFAULT 0,
      grade VARCHAR(5) NOT NULL DEFAULT '',
      remark VARCHAR(50) NOT NULL DEFAULT '',
      recorded_by VARCHAR(100) NOT NULL DEFAULT '',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uniq_grade (student_id, subject_id, class_id, term, academic_year)
    )`,
    `CREATE TABLE IF NOT EXISTS promotions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      student_id INT NOT NULL,
      from_class_id INT NOT NULL,
      to_class_id INT,
      academic_year VARCHAR(50) NOT NULL DEFAULT '',
      action VARCHAR(30) NOT NULL DEFAULT 'promote',
      reason TEXT,
      promoted TINYINT(1) NOT NULL DEFAULT 0,
      processed_by VARCHAR(100) NOT NULL DEFAULT '',
      processed_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS fee_payments (
      id INT AUTO_INCREMENT PRIMARY KEY,
      invoice_id INT NOT NULL,
      student_id INT NOT NULL,
      amount DECIMAL(12,2) NOT NULL,
      method VARCHAR(50) NOT NULL DEFAULT 'cash',
      reference VARCHAR(100) NOT NULL DEFAULT '',
      notes TEXT,
      recorded_by VARCHAR(100) NOT NULL DEFAULT '',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS library_books (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      author VARCHAR(255) NOT NULL DEFAULT '',
      isbn VARCHAR(50) NOT NULL DEFAULT '',
      category VARCHAR(100) NOT NULL DEFAULT 'General',
      quantity INT NOT NULL DEFAULT 1,
      available INT NOT NULL DEFAULT 1,
      description TEXT,
      cover_url TEXT,
      added_by VARCHAR(100) NOT NULL DEFAULT '',
      added_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS library_borrowings (
      id INT AUTO_INCREMENT PRIMARY KEY,
      book_id INT NOT NULL,
      student_id INT NOT NULL,
      borrowed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      due_date DATE NOT NULL,
      returned_at DATETIME,
      status VARCHAR(20) NOT NULL DEFAULT 'borrowed',
      notes TEXT,
      processed_by VARCHAR(100) NOT NULL DEFAULT ''
    )`,
    `CREATE TABLE IF NOT EXISTS calendar_events (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      date DATE NOT NULL,
      end_date DATE,
      type VARCHAR(50) NOT NULL DEFAULT 'event',
      description TEXT,
      color VARCHAR(20) NOT NULL DEFAULT '#3b82f6',
      target_role VARCHAR(50) NOT NULL DEFAULT 'all',
      created_by VARCHAR(100) NOT NULL DEFAULT '',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    `CREATE TABLE IF NOT EXISTS disciplinary_records (
      id INT AUTO_INCREMENT PRIMARY KEY,
      student_id INT NOT NULL,
      class_id INT NOT NULL,
      incident TEXT NOT NULL,
      severity VARCHAR(20) NOT NULL DEFAULT 'minor',
      action TEXT,
      reported_by VARCHAR(100) NOT NULL DEFAULT '',
      date DATE NOT NULL,
      notes TEXT,
      status VARCHAR(20) NOT NULL DEFAULT 'open',
      resolved_at DATETIME,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
  ];

  for (const stmt of stmts) {
    await query(stmt);
  }
}

async function ensureSeed() {
  await createTables();

  // Credentials
  const creds = await query("SELECT role FROM credentials");
  const roles = new Set(creds.map((c) => c.role));
  if (!roles.has("admin")) {
    await query("INSERT INTO credentials (role, password_hash) VALUES (?, ?)", ["admin", hashPassword("admin123")]);
  }
  if (!roles.has("developer")) {
    await query("INSERT INTO credentials (role, password_hash) VALUES (?, ?)", ["developer", hashPassword("developer123")]);
  }

  // School settings
  const settings = await query("SELECT id FROM school_settings LIMIT 1");
  if (!settings.length) {
    await query("INSERT INTO school_settings (name) VALUES (?)", ["ScholarExam Academy"]);
  }

  // System status
  const status = await query("SELECT id FROM system_status LIMIT 1");
  if (!status.length) {
    await query("INSERT INTO system_status (locked) VALUES (0)");
  }

  // Classes
  const classes = await query("SELECT id FROM classes");
  if (!classes.length) {
    await query("INSERT INTO classes (name, level, teacher) VALUES (?, ?, ?)", ["JSS 1A", "Junior Secondary", "Mrs. Adeola Bello"]);
    await query("INSERT INTO classes (name, level, teacher) VALUES (?, ?, ?)", ["JSS 2B", "Junior Secondary", "Mr. Chinedu Okafor"]);
    await query("INSERT INTO classes (name, level, teacher) VALUES (?, ?, ?)", ["SSS 1A", "Senior Secondary", "Mrs. Funke Adeyemi"]);
  }

  const classList = await query("SELECT id FROM classes ORDER BY id LIMIT 3");
  if (!classList.length) return;
  const c1 = classList[0].id;
  const c3 = (classList[2] || classList[0]).id;

  // Subjects
  const subjs = await query("SELECT id FROM subjects");
  if (!subjs.length) {
    await query("INSERT INTO subjects (name, code, class_id) VALUES (?, ?, ?)", ["Mathematics", "MTH", c1]);
    await query("INSERT INTO subjects (name, code, class_id) VALUES (?, ?, ?)", ["English Language", "ENG", c1]);
    await query("INSERT INTO subjects (name, code, class_id) VALUES (?, ?, ?)", ["Basic Science", "BSC", c1]);
    await query("INSERT INTO subjects (name, code, class_id) VALUES (?, ?, ?)", ["Physics", "PHY", c3]);
    await query("INSERT INTO subjects (name, code, class_id) VALUES (?, ?, ?)", ["Chemistry", "CHM", c3]);
    await query("INSERT INTO subjects (name, code, class_id) VALUES (?, ?, ?)", ["Biology", "BIO", c3]);
  }

  // Students
  const studs = await query("SELECT id FROM students");
  if (!studs.length) {
    const yr = new Date().getFullYear();
    await query("INSERT INTO students (student_id, first_name, last_name, class_id, gender, guardian_name, guardian_phone) VALUES (?, ?, ?, ?, ?, ?, ?)",
      [`SCH/${yr}/0001`, "Aisha", "Bello", c1, "Female", "Mr. Bello", "+234 800 111 1111"]);
    await query("INSERT INTO students (student_id, first_name, last_name, class_id, gender, guardian_name, guardian_phone) VALUES (?, ?, ?, ?, ?, ?, ?)",
      [`SCH/${yr}/0002`, "Tunde", "Olawale", c1, "Male", "Mrs. Olawale", "+234 800 222 2222"]);
    await query("INSERT INTO students (student_id, first_name, last_name, class_id, gender, guardian_name, guardian_phone) VALUES (?, ?, ?, ?, ?, ?, ?)",
      [`SCH/${yr}/0003`, "Ngozi", "Eze", c3, "Female", "Mr. Eze", "+234 800 333 3333"]);
  }

  // Questions & Exam
  const qs = await query("SELECT id FROM questions");
  if (!qs.length) {
    const math = await query("SELECT id, class_id FROM subjects WHERE code = ? LIMIT 1", ["MTH"]);
    if (math.length) {
      const mathId = math[0].id;
      const mathClassId = math[0].class_id;
      const opts1 = JSON.stringify([{ key: "A", text: "8" }, { key: "B", text: "12" }, { key: "C", text: "10" }, { key: "D", text: "6" }]);
      const opts2 = JSON.stringify([{ key: "A", text: "5" }, { key: "B", text: "7" }, { key: "C", text: "21" }, { key: "D", text: "9" }]);
      const q1 = await query("INSERT INTO questions (subject_id, type, prompt, options, correct_answer, marks, difficulty) VALUES (?, ?, ?, ?, ?, ?, ?)",
        [mathId, "objective", "What is the value of $2 + 2 \\times 3$?", opts1, "A", 5, "easy"]);
      const q2 = await query("INSERT INTO questions (subject_id, type, prompt, options, correct_answer, marks, difficulty) VALUES (?, ?, ?, ?, ?, ?, ?)",
        [mathId, "objective", "Solve for x: $3x - 7 = 14$.", opts2, "B", 5, "easy"]);
      const q3 = await query("INSERT INTO questions (subject_id, type, prompt, marks) VALUES (?, ?, ?, ?)",
        [mathId, "theory", "State and prove the Pythagorean theorem.", 10]);

      const q1id = (await query("SELECT LAST_INSERT_ID() AS id FROM questions LIMIT 1"))[0]?.id;
      void q1id;

      // Get inserted question ids
      const insertedQs = await query("SELECT id FROM questions WHERE subject_id = ? ORDER BY id", [mathId]);

      const scheduledAt = new Date(Date.now() + 3 * 24 * 60 * 60 * 1000).toISOString().slice(0, 19).replace("T", " ");
      await query("INSERT INTO exams (title, instructions, subject_id, class_id, duration_minutes, total_marks, pass_mark, status, scheduled_at, venue) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        ["First Term Mathematics Test", "Read all questions carefully. Show your working for theory questions.", mathId, mathClassId, 45, 20, 10, "published", scheduledAt, "Hall A"]);

      const examId = (await query("SELECT LAST_INSERT_ID() AS id"))[0]?.id;
      void q1; void q2; void q3;

      for (let i = 0; i < insertedQs.length; i++) {
        await query("INSERT INTO exam_questions (exam_id, question_id, position) VALUES (?, ?, ?)", [examId, insertedQs[i].id, i]);
      }
    }
  }

  // Invoice
  const invs = await query("SELECT id FROM invoices");
  if (!invs.length) {
    const firstStudent = await query("SELECT id FROM students LIMIT 1");
    if (firstStudent.length) {
      const items = JSON.stringify([
        { description: "Tuition Fee", amount: 75000 },
        { description: "Examination Fee", amount: 5000 },
        { description: "Library Levy", amount: 2500 },
      ]);
      await query("INSERT INTO invoices (invoice_number, student_id, term, items, total, amount_paid, status) VALUES (?, ?, ?, ?, ?, ?, ?)",
        [`INV-${Date.now()}`, firstStudent[0].id, "First Term 2025/2026", items, 82500, 0, "pending"]);
    }
  }

  // Activity
  const acts = await query("SELECT id FROM activity_log");
  if (!acts.length) {
    await query("INSERT INTO activity_log (type, message) VALUES (?, ?)", ["system", "ScholarExam initialized successfully."]);
  }

  console.log("Seed complete");
}

module.exports = { ensureSeed, createTables };
