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

async function initializeDatabase() {
    try {
        const connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD
        });

        console.log('Connected to MySQL server.');

        // Create database if not exists
        await connection.query(`CREATE DATABASE IF NOT EXISTS \`${process.env.DB_NAME}\`;`);
        console.log(`Database '${process.env.DB_NAME}' checked/created.`);

        await connection.query(`USE \`${process.env.DB_NAME}\`;`);

        // Create table for form fields (dynamic schema)
        const createFieldsTable = `
            CREATE TABLE IF NOT EXISTS form_fields (
                id INT AUTO_INCREMENT PRIMARY KEY,
                label VARCHAR(255) NOT NULL,
                type VARCHAR(50) NOT NULL,
                options JSON,
                is_required BOOLEAN DEFAULT TRUE,
                order_index INT DEFAULT 0
            );
        `;
        await connection.query(createFieldsTable);
        console.log('Table "form_fields" checked/created.');

        // Create table for form responses (JSON storage)
        const createResponsesTable = `
            CREATE TABLE IF NOT EXISTS responses (
                id INT AUTO_INCREMENT PRIMARY KEY,
                data JSON NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        `;
        await connection.query(createResponsesTable);
        console.log('Table "responses" checked/created.');

        // Insert default fields if empty
        const [rows] = await connection.query('SELECT COUNT(*) as count FROM form_fields');
        if (rows[0].count === 0) {
            const defaultFields = [
                ['Nama Lengkap', 'text', null, true, 1],
                ['Tanggal', 'date', null, true, 2],
                ['Status Kehadiran', 'select', JSON.stringify(['Hadir', 'Izin', 'Sakit', 'Alpa']), true, 3],
                ['Keterangan', 'text', null, false, 4],
                ['Tanda Tangan', 'signature', null, true, 5]
            ];
            
            const insertQuery = 'INSERT INTO form_fields (label, type, options, is_required, order_index) VALUES ?';
            await connection.query(insertQuery, [defaultFields]);
            console.log('Inserted default form fields.');
        }

        await connection.end();
        console.log('Database initialization completed successfully.');
    } catch (error) {
        console.error('Error initializing database:', error);
    }
}

initializeDatabase();
