import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

// Create connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASS || '',
  database: process.env.DB_NAME || 'db_bukuiduk',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  charset: 'utf8mb4'
});

// Initialize database and tables
export const initDatabase = async () => {
  let conn;
  try {
    // First, create connection without database to create it if needed
    const initPool = mysql.createPool({
      host: process.env.DB_HOST || 'localhost',
      port: process.env.DB_PORT || 3306,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASS || '',
      waitForConnections: true,
      connectionLimit: 2,
    });

    conn = await initPool.getConnection();

    // Create database if not exists
    await conn.query(`CREATE DATABASE IF NOT EXISTS \`${process.env.DB_NAME || 'db_bukuiduk'}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log(`✓ Database '${process.env.DB_NAME || 'db_bukuiduk'}' ready`);

    await conn.release();
    await initPool.end();

    // Now create tables using main pool
    const mainConn = await pool.getConnection();

    // Users table
    await mainConn.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        full_name VARCHAR(255),
        role ENUM('admin', 'operator') DEFAULT 'operator',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✓ Table users ready');

    // Students table with all fields from the application
    await mainConn.query(`
      CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nis VARCHAR(50) NOT NULL,
        nama VARCHAR(255) NOT NULL,
        tahun_ajaran VARCHAR(20),
        foto_diterima_url LONGTEXT,
        foto_lulus_url LONGTEXT,
        
        -- A. Keterangan Diri
        nama_panggilan VARCHAR(100),
        jenis_kelamin ENUM('L', 'P') DEFAULT 'L',
        tempat_lahir VARCHAR(100),
        tanggal_lahir VARCHAR(50),
        agama VARCHAR(50),
        kewarganegaraan VARCHAR(100) DEFAULT 'Indonesia',
        anak_keberapa VARCHAR(20),
        jumlah_saudara_kandung VARCHAR(20),
        jumlah_saudara_tiri VARCHAR(20),
        status_yatim VARCHAR(50),
        bahasa_sehari_hari VARCHAR(100),
        
        -- B. Tempat Tinggal
        alamat TEXT,
        no_telp VARCHAR(50),
        jenis_tempat_tinggal VARCHAR(100),
        jarak_ke_sekolah VARCHAR(50),
        
        -- C. Kesehatan
        golongan_darah VARCHAR(10),
        penyakit VARCHAR(255),
        kelainan_jasmani VARCHAR(255),
        tinggi_berat VARCHAR(100),
        
        -- D. Pendidikan
        pendidikan_asal VARCHAR(255),
        no_ijasah_asal VARCHAR(100),
        lama_belajar_asal VARCHAR(50),
        pindahan_dari VARCHAR(255),
        alasan_pindah TEXT,
        diterima_di_kelas VARCHAR(50),
        program_jurusan VARCHAR(100),
        tanggal_diterima VARCHAR(50),
        hobby VARCHAR(255),
        cita_cita VARCHAR(255),
        
        -- E. Ayah
        ayah_nama VARCHAR(255),
        ayah_nik VARCHAR(50),
        ayah_tahun_lahir VARCHAR(20),
        ayah_agama VARCHAR(50),
        ayah_warga_negara VARCHAR(100),
        ayah_pendidikan VARCHAR(100),
        ayah_pekerjaan VARCHAR(100),
        ayah_penghasilan VARCHAR(100),
        ayah_alamat TEXT,
        ayah_no_telp VARCHAR(50),
        
        -- F. Ibu
        ibu_nama VARCHAR(255),
        ibu_nik VARCHAR(50),
        ibu_tahun_lahir VARCHAR(20),
        ibu_agama VARCHAR(50),
        ibu_warga_negara VARCHAR(100),
        ibu_pendidikan VARCHAR(100),
        ibu_pekerjaan VARCHAR(100),
        ibu_penghasilan VARCHAR(100),
        ibu_alamat TEXT,
        ibu_no_telp VARCHAR(50),
        
        -- G. Wali
        wali_nama VARCHAR(255),
        wali_nik VARCHAR(50),
        wali_tahun_lahir VARCHAR(20),
        wali_agama VARCHAR(50),
        wali_pendidikan VARCHAR(100),
        wali_pekerjaan VARCHAR(100),
        wali_penghasilan VARCHAR(100),
        
        -- H. Beasiswa
        bea_siswa1_tahun VARCHAR(20),
        bea_siswa1_kelas VARCHAR(50),
        bea_siswa1_dari VARCHAR(255),
        bea_siswa2_tahun VARCHAR(20),
        bea_siswa2_kelas VARCHAR(50),
        bea_siswa2_dari VARCHAR(255),
        bea_siswa3_tahun VARCHAR(20),
        bea_siswa3_kelas VARCHAR(50),
        bea_siswa3_dari VARCHAR(255),
        
        -- Meninggalkan Sekolah
        tanggal_keluar VARCHAR(50),
        alasan_keluar TEXT,
        tamat_belajar_tahun VARCHAR(20),
        no_ijasah_tamat VARCHAR(100),
        
        -- I. Setelah Pendidikan
        pekerjaan_perusahaan VARCHAR(255),
        pekerjaan_bidang_usaha VARCHAR(255),
        pekerjaan_penghasilan VARCHAR(100),
        pekerjaan_sesuai_kompetensi VARCHAR(20),
        lanjut_perguruan_tinggi VARCHAR(255),
        lanjut_program_studi VARCHAR(255),
        
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        INDEX idx_nis (nis),
        INDEX idx_nama (nama),
        INDEX idx_tahun_ajaran (tahun_ajaran)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✓ Table students ready');

    // Settings table
    await mainConn.query(`
      CREATE TABLE IF NOT EXISTS settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(100) NOT NULL UNIQUE,
        setting_value LONGTEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✓ Table settings ready');

    // Student Legers table for PDF semester files
    await mainConn.query(`
      CREATE TABLE IF NOT EXISTS student_legers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        nis VARCHAR(50) NOT NULL,
        nama VARCHAR(255) NOT NULL,
        semester INT NOT NULL CHECK (semester >= 1 AND semester <= 6),
        file_name VARCHAR(255) NOT NULL,
        file_data LONGTEXT NOT NULL,
        file_size INT,
        uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        INDEX idx_student_id (student_id),
        INDEX idx_nis (nis),
        INDEX idx_semester (semester),
        UNIQUE KEY unique_student_semester (student_id, semester),
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✓ Table student_legers ready');

    // Student Documents table for Ijazah and Sertifikat
    await mainConn.query(`
      CREATE TABLE IF NOT EXISTS student_documents (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        nis VARCHAR(50) NOT NULL,
        nama VARCHAR(255) NOT NULL,
        doc_type ENUM('ijazah', 'sertifikat') NOT NULL,
        doc_name VARCHAR(255),
        file_name VARCHAR(255) NOT NULL,
        file_data LONGTEXT NOT NULL,
        file_size INT,
        uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        INDEX idx_student_id (student_id),
        INDEX idx_doc_type (doc_type),
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✓ Table student_documents ready');

    // Insert default user if not exists
    const [existingUsers] = await mainConn.query('SELECT * FROM users WHERE username = ?', ['Kesiswaan']);
    if (existingUsers.length === 0) {
      const bcryptModule = await import('bcryptjs');
      const bcrypt = bcryptModule.default;
      const hashedPassword = await bcrypt.hash('Smanab#1', 10);
      await mainConn.query(
        'INSERT INTO users (username, password, full_name, role) VALUES (?, ?, ?, ?)',
        ['Kesiswaan', hashedPassword, 'Administrator Kesiswaan', 'admin']
      );
      console.log('✓ Default user created: Kesiswaan');
    }

    // Insert default settings if not exists
    const [existingSettings] = await mainConn.query('SELECT * FROM settings WHERE setting_key = ?', ['app_settings']);
    if (existingSettings.length === 0) {
      const defaultSettings = {
        schoolName: 'SMA NEGERI 1 ABIANSEMAL',
        logoUrl: 'https://iili.io/KN7pUR2.png',
        faviconUrl: '',
        tahunAjaran: '2025/2026',
        margins: { top: 20, right: 20, bottom: 20, left: 20 }
      };
      await mainConn.query(
        'INSERT INTO settings (setting_key, setting_value) VALUES (?, ?)',
        ['app_settings', JSON.stringify(defaultSettings)]
      );
      console.log('✓ Default settings created');
    }

    // Academic Years table
    await mainConn.query(`
      CREATE TABLE IF NOT EXISTS academic_years (
        id INT AUTO_INCREMENT PRIMARY KEY,
        year_name VARCHAR(20) NOT NULL UNIQUE,
        is_active BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✓ Table academic_years ready');

    // Insert default academic year if none exist
    const [existingYears] = await mainConn.query('SELECT * FROM academic_years');
    if (existingYears.length === 0) {
      await mainConn.query(
        'INSERT INTO academic_years (year_name, is_active) VALUES (?, ?)',
        ['2025/2026', true]
      );
      console.log('✓ Default academic year created');
    }

    mainConn.release();
    console.log('✓ Database initialization complete');

  } catch (error) {
    console.error('Database initialization error:', error);
    throw error;
  }
};

export default pool;