// Database Initialization Script
// Run this to automatically create all tables in MySQL
import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
import { fileURLToPath } from 'url';
import path from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

dotenv.config({ path: path.join(__dirname, '../.env') });

const schema = `
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    identifier VARCHAR(50) UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('ADMIN', 'GURU', 'SEKRETARIS') NOT NULL DEFAULT 'GURU',
    class_name VARCHAR(20) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_identifier (identifier),
    INDEX idx_role (role)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nip VARCHAR(30) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nip (nip),
    INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nis VARCHAR(20) UNIQUE,
    class_name VARCHAR(20) NOT NULL,
    gender ENUM('L', 'P') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nis (nis),
    INDEX idx_class (class_name),
    INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id VARCHAR(50) UNIQUE NOT NULL,
    date DATE NOT NULL,
    teacher_name VARCHAR(100) NOT NULL,
    nip VARCHAR(30),
    class_name VARCHAR(20) NOT NULL,
    subject VARCHAR(100) NOT NULL,
    topic TEXT,
    start_time VARCHAR(10),
    end_time VARCHAR(10),
    students_present INT DEFAULT 0,
    students_absent INT DEFAULT 0,
    sakit INT DEFAULT 0,
    izin INT DEFAULT 0,
    alpha INT DEFAULT 0,
    dispen INT DEFAULT 0,
    notes TEXT,
    photo_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_journal_id (journal_id),
    INDEX idx_date (date),
    INDEX idx_teacher (teacher_name),
    INDEX idx_class (class_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS class_journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id VARCHAR(50) UNIQUE NOT NULL,
    date DATE NOT NULL,
    teacher_name VARCHAR(100) NOT NULL,
    subject VARCHAR(100) NOT NULL,
    class_name VARCHAR(20) NOT NULL,
    teacher_presence ENUM('Hadir', 'Tugas', 'Tidak Hadir') DEFAULT 'Hadir',
    start_time VARCHAR(10),
    end_time VARCHAR(10),
    students_present INT DEFAULT 0,
    students_absent INT DEFAULT 0,
    sakit INT DEFAULT 0,
    izin INT DEFAULT 0,
    alpha INT DEFAULT 0,
    dispen INT DEFAULT 0,
    notes TEXT,
    class_secretary VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_journal_id (journal_id),
    INDEX idx_date (date),
    INDEX idx_class (class_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS attendance_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_type ENUM('teacher', 'class') NOT NULL,
    journal_id VARCHAR(50) NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    status ENUM('H', 'S', 'I', 'A', 'D') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_journal (journal_type, journal_id),
    INDEX idx_student (student_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS school_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
`;

async function initDatabase() {
    console.log('╔═══════════════════════════════════════════════════════════╗');
    console.log('║     🗄️  Database Initialization Script                    ║');
    console.log('╠═══════════════════════════════════════════════════════════╣');

    const dbHost = process.env.DB_HOST || 'localhost';
    const dbUser = process.env.DB_USER || 'smanabid';
    const dbPass = process.env.DB_PASSWORD || '';
    const dbPort = process.env.DB_PORT || 3306;
    const dbName = process.env.DB_NAME || 'smanabid_db_jurnal';

    console.log(`║  Host: ${dbHost}:${dbPort}`);
    console.log(`║  User: ${dbUser}`);
    console.log(`║  Database: ${dbName}`);
    console.log('╠═══════════════════════════════════════════════════════════╣');

    let connection;

    try {
        console.log('║  Connecting to MySQL server...                            ║');
        connection = await mysql.createConnection({
            host: dbHost,
            user: dbUser,
            password: dbPass,
            port: dbPort,
            multipleStatements: true
        });
        console.log('║  ✅ Connected to MySQL server                              ║');

        console.log(`║  Creating database: ${dbName}...`);
        await connection.query(`CREATE DATABASE IF NOT EXISTS \`${dbName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
        console.log('║  ✅ Database created/verified                              ║');

        await connection.query(`USE \`${dbName}\``);
        console.log('║  ✅ Using database                                         ║');

        console.log('║  Creating tables...                                        ║');
        await connection.query(schema);
        console.log('║  ✅ All tables created successfully                        ║');

        // Insert seed data
        console.log('║  Inserting seed data...                                    ║');

        // Default settings
        await connection.query(`
            INSERT IGNORE INTO school_settings (setting_key, setting_value) VALUES
            ('semester', 'Ganjil'), ('academic_year', '2025/2026'),
            ('headmaster_name', ''), ('headmaster_nip', ''),
            ('logo_url', ''), ('kop_url', '')
        `);

        // Admin user
        await connection.query(`
            INSERT IGNORE INTO users (name, identifier, password, role) VALUES
            ('Administrator', 'admin', 'admin123', 'ADMIN'),
            ('Smanab Admin', 'AdminJurnal#1', 'Smanab100%', 'ADMIN')
        `);

        // Sample Teachers
        await connection.query(`
            INSERT IGNORE INTO teachers (name, nip) VALUES
            ('I Ketut Suardika, S.Pd', '196512311990031234'),
            ('Ni Made Dewi Lestari, S.Pd', '198001152006042001'),
            ('I Wayan Dharma Putra, M.Pd', '197503222000121004')
        `);

        // Sample Subjects
        await connection.query(`
            INSERT IGNORE INTO subjects (name) VALUES
            ('Matematika'), ('Bahasa Indonesia'), ('Bahasa Inggris'),
            ('Fisika'), ('Kimia'), ('Biologi'), ('Sejarah'),
            ('Geografi'), ('Ekonomi'), ('Pendidikan Agama'),
            ('PJOK'), ('Seni Budaya'), ('Informatika')
        `);

        // Note: Sample students removed - Admin manages student data via import/manual entry

        console.log('║  ✅ Seed data inserted                                     ║');
        console.log('╠═══════════════════════════════════════════════════════════╣');
        console.log('║  🎉 Database initialization completed successfully!       ║');
        console.log('║                                                           ║');
        console.log('║  Default admin login:                                     ║');
        console.log('║  - Username: admin                                        ║');
        console.log('║  - Password: admin123                                     ║');
        console.log('╚═══════════════════════════════════════════════════════════╝');

        return true;
    } catch (error) {
        console.log('╠═══════════════════════════════════════════════════════════╣');
        console.error('║  ❌ Error:', error.message);
        console.log('║');
        console.log('║  Kemungkinan penyebab:');
        console.log('║  1. MySQL server belum berjalan');
        console.log('║  2. User/password salah');
        console.log('║  3. User tidak memiliki permission');
        console.log('║');
        console.log('║  Solusi:');
        console.log('║  - Pastikan MySQL sudah running');
        console.log('║  - Cek credential di file .env');
        console.log('║  - Jalankan: mysql -u root -p');
        console.log('║    CREATE USER smanabid@localhost IDENTIFIED BY password;');
        console.log('║    GRANT ALL ON smanabid_db_jurnal.* TO smanabid@localhost;');
        console.log('╚═══════════════════════════════════════════════════════════╝');
        return false;
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// Run
initDatabase().then(success => {
    process.exit(success ? 0 : 1);
});