<?php
// init_db.php - Initialize database (MySQL or SQLite)
require __DIR__ . '/includes/db.php';

// Check if configuration file exists
$configFile = __DIR__ . '/config/database.php';
if (!file_exists($configFile)) {
    die("Configuration file missing. Please copy config/database.php.example to config/database.php\n");
}

// Load configuration to detect database type
require_once $configFile;

// Determine database type (default to SQLite if not specified)
$dbType = defined('DB_TYPE') ? DB_TYPE : 'sqlite';

echo "Initializing $dbType database...\n\n";

// Branch to appropriate initialization based on database type
if ($dbType === 'mysql') {
    initialize_mysql_database();
} else {
    initialize_sqlite_database();
}

echo "\nDatabase initialization complete!\n";

/**
 * Initialize SQLite database
 */
function initialize_sqlite_database() {
    $db = get_db();

    // Students table (added photo column)
    $db->exec("CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nis TEXT UNIQUE,
        name TEXT NOT NULL,
        grade TEXT,
        photo TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )");

    // Admins table (stores hashed password)
    $db->exec("CREATE TABLE IF NOT EXISTS admins (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE,
        password TEXT
    )");

    // Settings table
    $db->exec("CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT
    )");

    echo "✓ Tables created successfully\n";

    // Insert default site name if not exists
    $ins = $db->prepare('INSERT OR IGNORE INTO settings (key,value) VALUES (:k,:v)');
    $ins->execute([':k' => 'site_name', ':v' => 'Sekolah Kita']);
    echo "✓ Default settings inserted\n";

    // Create uploads folder
    $uploads = __DIR__ . '/data/uploads';
    if (!is_dir($uploads)) {
        mkdir($uploads, 0755, true);
        echo "✓ Upload directory created\n";
    } else {
        echo "✓ Upload directory exists\n";
    }

    // Insert sample student
    $stmt = $db->prepare('INSERT OR IGNORE INTO students (nis, name, grade) VALUES (:nis,:name,:grade)');
    $stmt->execute([':nis' => '001', ':name' => 'Siswa Contoh', ':grade' => '10A']);
    echo "✓ Sample student data inserted\n";

    // Create default admin if not exists (username: admin, password: admin123)
    require_once __DIR__ . '/includes/auth.php';
    create_admin_if_not_exists('admin', 'admin123');
    echo "✓ Default admin account created (username: admin, password: admin123)\n";

    echo "\nSQLite database initialized at data/school.db\n";
}

/**
 * Initialize MySQL database
 */
function initialize_mysql_database() {
    // First, connect to MySQL server without selecting a database
    $host = defined('DB_HOST') ? DB_HOST : 'localhost';
    $dbname = defined('DB_NAME') ? DB_NAME : 'mygb_school';
    $user = defined('DB_USER') ? DB_USER : 'root';
    $pass = defined('DB_PASS') ? DB_PASS : '';
    $port = defined('DB_PORT') ? DB_PORT : 3306;
    $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';
    $collation = defined('DB_COLLATION') ? DB_COLLATION : 'utf8mb4_unicode_ci';

    try {
        // Connect to MySQL server (without database)
        $mysqli = new mysqli($host, $user, $pass, '', $port);
        
        if ($mysqli->connect_error) {
            die("MySQL Connection Failed: " . $mysqli->connect_error . "\n");
        }
        
        echo "✓ Connected to MySQL server\n";

        // Create database if not exists
        $sql = "CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET $charset COLLATE $collation";
        if (!$mysqli->query($sql)) {
            die("Error creating database: " . $mysqli->error . "\n");
        }
        echo "✓ Database '$dbname' created/verified\n";

        // Select the database
        if (!$mysqli->select_db($dbname)) {
            die("Error selecting database: " . $mysqli->error . "\n");
        }

        // Set charset
        if (!$mysqli->set_charset($charset)) {
            die("Error setting charset: " . $mysqli->error . "\n");
        }

        // Create tables with MySQL schema
        create_mysql_tables($mysqli);

        // Insert default data
        insert_mysql_default_data($mysqli);

        // Create upload directories
        create_upload_directories();

        $mysqli->close();

    } catch (Exception $e) {
        die("Error: " . $e->getMessage() . "\n");
    }
}

/**
 * Create all MySQL tables with proper schema
 */
function create_mysql_tables($mysqli) {
    echo "\nCreating tables...\n";

    // Students table
    $sql = "CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nis VARCHAR(50) UNIQUE NOT NULL,
        name VARCHAR(255) NOT NULL,
        grade VARCHAR(50),
        photo VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_nis (nis),
        INDEX idx_grade (grade)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    if (!$mysqli->query($sql)) {
        die("Error creating students table: " . $mysqli->error . "\n");
    }
    echo "  ✓ students table created\n";

    // Admins table
    $sql = "CREATE TABLE IF NOT EXISTS admins (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_username (username)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    if (!$mysqli->query($sql)) {
        die("Error creating admins table: " . $mysqli->error . "\n");
    }
    echo "  ✓ admins table created\n";

    // Settings table
    $sql = "CREATE TABLE IF NOT EXISTS settings (
        `key` VARCHAR(100) PRIMARY KEY,
        value TEXT,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    if (!$mysqli->query($sql)) {
        die("Error creating settings table: " . $mysqli->error . "\n");
    }
    echo "  ✓ settings table created\n";

    echo "✓ All tables created successfully\n";
}

/**
 * Insert default data into MySQL database
 */
function insert_mysql_default_data($mysqli) {
    echo "\nInserting default data...\n";

    // Insert default settings
    $settings = [
        'site_name' => 'Sekolah Kita',
        'site_description' => 'Website Sekolah',
        'site_logo' => '',
        'site_address' => '',
        'site_phone' => '',
        'site_email' => ''
    ];

    $stmt = $mysqli->prepare("INSERT IGNORE INTO settings (`key`, value) VALUES (?, ?)");
    foreach ($settings as $key => $value) {
        $stmt->bind_param('ss', $key, $value);
        $stmt->execute();
    }
    $stmt->close();
    echo "  ✓ Default settings inserted\n";

    // Insert sample student
    $stmt = $mysqli->prepare("INSERT IGNORE INTO students (nis, name, grade) VALUES (?, ?, ?)");
    $nis = '001';
    $name = 'Siswa Contoh';
    $grade = '10A';
    $stmt->bind_param('sss', $nis, $name, $grade);
    $stmt->execute();
    $stmt->close();
    echo "  ✓ Sample student data inserted\n";

    // Create default admin account
    require_once __DIR__ . '/includes/auth.php';
    
    // Check if admin exists
    $result = $mysqli->query("SELECT COUNT(*) as count FROM admins WHERE username = 'admin'");
    $row = $result->fetch_assoc();
    
    if ($row['count'] == 0) {
        $username = 'admin';
        $password = password_hash('admin123', PASSWORD_DEFAULT);
        
        $stmt = $mysqli->prepare("INSERT INTO admins (username, password) VALUES (?, ?)");
        $stmt->bind_param('ss', $username, $password);
        $stmt->execute();
        $stmt->close();
        
        echo "  ✓ Default admin account created (username: admin, password: admin123)\n";
    } else {
        echo "  ✓ Admin account already exists\n";
    }

    echo "✓ Default data inserted successfully\n";
}

/**
 * Create upload directories
 */
function create_upload_directories() {
    echo "\nCreating upload directories...\n";
    
    $uploads = __DIR__ . '/data/uploads';
    if (!is_dir($uploads)) {
        mkdir($uploads, 0755, true);
        echo "  ✓ Upload directory created at data/uploads\n";
    } else {
        echo "  ✓ Upload directory already exists\n";
    }
    
    echo "✓ Upload directories ready\n";
}
