#!/usr/bin/env bash
set -e

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
cd "$SCRIPT_DIR"

if [ ! -f ".env" ]; then
    echo "Error: .env file not found in $SCRIPT_DIR"
    exit 1
fi

echo "Installing backend dependencies if needed..."
if [ ! -d "backend/node_modules" ]; then
    cd "$SCRIPT_DIR/backend"
    npm install --omit=dev
    cd "$SCRIPT_DIR"
fi

echo "Initializing MySQL database and loading initial data..."

NODE_PATH=./backend/node_modules node -e "
const fs = require('fs');
const path = require('path');
const dotenv = require('dotenv');

dotenv.config();

let mysql;
try {
    mysql = require('mysql2/promise');
} catch (e) {
    mysql = require('./backend/node_modules/mysql2/promise');
}

const dbHost = process.env.DB_HOST || 'localhost';
const dbUser = process.env.DB_USER || 'root';
const dbPassword = process.env.DB_PASSWORD || '';

async function executeSqlFile(connection, filePath) {
    const fullPath = path.join(__dirname, filePath);
    console.log('Running SQL script:', fullPath);
    const sqlContent = fs.readFileSync(fullPath, 'utf8');
    await connection.query(sqlContent);
}

async function main() {
    let connection;
    try {
        console.log(\`Connecting to MySQL host \${dbHost} as \${dbUser}...\`);
        connection = await mysql.createConnection({
            host: dbHost,
            user: dbUser,
            password: dbPassword,
            multipleStatements: true
        });

        console.log('Executing database_creation.sql...');
        await executeSqlFile(connection, 'db/database_creation.sql');

        console.log('Executing populate_classroom_app.sql...');
        await executeSqlFile(connection, 'db/populate_classroom_app.sql');

        console.log('Database setup completed successfully!');
    } catch (err) {
        console.error('Error during database setup:', err);
        process.exit(1);
    } finally {
        if (connection) await connection.end();
    }
}

main();
"