require('dotenv').config();
const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
const crypto = require('crypto');

let db;

async function initDB() {
  try {
    console.log('INIT 1: Connecting...');
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST || '127.0.0.1',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || ''
    });
    const dbName = process.env.DB_NAME || 'nuralilm_db';
    await connection.query('CREATE DATABASE IF NOT EXISTS `' + dbName + '`');
    console.log('INIT 2: DB Created...');
    await connection.end();

    // Pool TANPA multipleStatements (security: mencegah SQL injection multi-query)
    db = mysql.createPool({
      host: process.env.DB_HOST || '127.0.0.1',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: dbName,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
      // multipleStatements DIHAPUS — security best practice
    });

    // Buat tabel satu per satu (bukan multi-statement)
    const tables = [
      `CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nama VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50) DEFAULT 'Editor',
        status VARCHAR(50) DEFAULT 'Aktif',
        last_active DATETIME NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS materi (
        id INT AUTO_INCREMENT PRIMARY KEY,
        judul VARCHAR(255) NOT NULL,
        jenis VARCHAR(50) DEFAULT 'Tulisan',
        kategori VARCHAR(100),
        status VARCHAR(50) DEFAULT 'Draft',
        excerpt TEXT,
        konten LONGTEXT,
        url TEXT,
        durasi VARCHAR(50),
        gambar TEXT,
        penulis_id INT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (penulis_id) REFERENCES users(id) ON DELETE SET NULL
      )`,

      `CREATE TABLE IF NOT EXISTS risalah (
        id INT AUTO_INCREMENT PRIMARY KEY,
        judul VARCHAR(255) NOT NULL,
        khatib VARCHAR(255),
        tanggal DATE,
        excerpt TEXT,
        konten LONGTEXT,
        pdf_url TEXT,
        status VARCHAR(50) DEFAULT 'Draft',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS berita (
        id INT AUTO_INCREMENT PRIMARY KEY,
        judul VARCHAR(255) NOT NULL,
        penulis VARCHAR(255),
        kategori VARCHAR(100) DEFAULT 'Umum',
        konten LONGTEXT,
        gambar TEXT,
        status VARCHAR(50) DEFAULT 'Draft',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS galeri (
        id INT AUTO_INCREMENT PRIMARY KEY,
        judul VARCHAR(255) NOT NULL,
        album VARCHAR(100) DEFAULT 'Umum',
        url TEXT NOT NULL,
        status VARCHAR(50) DEFAULT 'Draft',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS konsultasi (
        id INT AUTO_INCREMENT PRIMARY KEY,
        penanya VARCHAR(255) NOT NULL,
        email VARCHAR(255),
        pertanyaan TEXT NOT NULL,
        status VARCHAR(50) DEFAULT 'pending',
        rating INT DEFAULT NULL,
        ulasan TEXT DEFAULT NULL,
        ustadz_id INT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (ustadz_id) REFERENCES users(id) ON DELETE SET NULL
      )`,

      `CREATE TABLE IF NOT EXISTS konsultasi_reply (
        id INT AUTO_INCREMENT PRIMARY KEY,
        konsultasi_id INT NOT NULL,
        dari VARCHAR(50) DEFAULT 'admin',
        nama VARCHAR(255),
        teks TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (konsultasi_id) REFERENCES konsultasi(id) ON DELETE CASCADE
      )`,

      `CREATE TABLE IF NOT EXISTS otp_codes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) NOT NULL,
        code VARCHAR(6) NOT NULL,
        expires_at DATETIME NOT NULL,
        used BOOLEAN DEFAULT FALSE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_expires (expires_at)
      )`,

      `CREATE TABLE IF NOT EXISTS newsletter (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        status VARCHAR(50) DEFAULT 'Aktif',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS donasi (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nama VARCHAR(255) DEFAULT 'Anonim',
        email VARCHAR(255),
        nominal INT NOT NULL,
        metode VARCHAR(100),
        pesan TEXT,
        status VARCHAR(50) DEFAULT 'Pending',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS program (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nama VARCHAR(255) NOT NULL,
        lokasi VARCHAR(255),
        tanggal DATE,
        waktu VARCHAR(100),
        desc_text TEXT,
        status VARCHAR(50) DEFAULT 'Upcoming',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      `CREATE TABLE IF NOT EXISTS logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        level VARCHAR(50) DEFAULT 'INFO',
        msg TEXT NOT NULL,
        user_id INT,
        ip VARCHAR(100),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,

      "CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(100) PRIMARY KEY, value TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)"
    ];

    console.log('INIT 3: Creating tables...');
    for (const sql of tables) {
      await db.query(sql);
    }

    console.log('INIT 4: Tables created! Checking admin...');
    const [adminRows] = await db.query('SELECT id FROM users WHERE email = ?', ['admin@nuralilm.id']);
    if (adminRows.length === 0) {
      const randomPassword = crypto.randomBytes(12).toString('base64url');
      const hash = bcrypt.hashSync(randomPassword, 10);
      await db.query('INSERT INTO users (nama, email, password, role) VALUES (?, ?, ?, ?)', ['Administrator', 'admin@nuralilm.id', hash, 'Admin']);
      console.log('✅ Admin user created: admin@nuralilm.id');
      console.log(`🔑 Password awal: ${randomPassword}`);
      console.log('⚠️  SEGERA ganti password ini setelah login pertama!');
    }

    console.log('INIT 5: Admin checked! Checking settings...');
    const defaultSettings = [
      ['site_name', "Nūr Al-'Ilm"],
      ['site_desc', 'Platform Islami terpercaya'],
      ['contact_email', 'info@nuralilm.id'],
      ['contact_phone', '+62 812-3456-7890'],
      ['prayer_city', 'Surabaya'],
      ['prayer_country', 'Indonesia'],
      ['prayer_method', '11'],
      ['feature_konsultasi', '1'],
      ['feature_newsletter', '1'],
      ['feature_donasi', '1'],
      ['maintenance_mode', '0'],
    ];

    for (const [k, v] of defaultSettings) {
      await db.query('INSERT IGNORE INTO settings (`key`, value) VALUES (?, ?)', [k, v]);
    }

    console.log('✅ Database initialized using MySQL (multipleStatements: DISABLED)');

  } catch (error) {
    console.error('❌ Failed to initialize MySQL database:', error);
    process.exit(1);
  }
}

function getDB() {
  return db;
}

module.exports = { initDB, getDB };
