setup_database.js

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

async function setupDatabase() {
    const config = {
        host: process.env.DB_HOST || 'localhost',
        user: process.env.DB_USER || 'root',
        password: process.env.DB_PASSWORD,
        multipleStatements: true // Needed for running SQL files
    };

    console.log(`🔌 Connecting to MySQL at ${config.host} as ${config.user}...`);

    let connection;
    try {
        connection = await mysql.createConnection(config);
        console.log('✅ Connected to MySQL server.');

        // Create Database
        const dbName = process.env.DB_NAME || 'uni';
        console.log(`🔨 Recreating database '${dbName}'...`);
        await connection.query(`DROP DATABASE IF EXISTS ${dbName}`);
        await connection.query(`CREATE DATABASE ${dbName}`);
        await connection.query(`USE ${dbName}`);
        console.log(`📂 Selected database '${dbName}'.`);

        // Read and Execute Schema
        console.log('📜 Importing schema from university_schema.sql...');
        const schemaSql = fs.readFileSync(path.join(__dirname, 'university_schema.sql'), 'utf8').replace(/^\uFEFF/, '');
        await connection.query(schemaSql);
        console.log('✅ Schema imported.');

        // Read and Execute Data
        console.log('Buildings data from ssn_buildings.sql...');
        const dataSql = fs.readFileSync(path.join(__dirname, 'ssn_buildings.sql'), 'utf8');
        // ssn_buildings.sql has "USE uni;" which might conflict if db name is different, but we selected it.
        // It's safer to remove "USE uni;" from the file content or just run it.
        // Mysql2 with multipleStatements should handle it, but let's be safe.
        await connection.query(dataSql);
        console.log('✅ Buildings data imported.');

        console.log('\n🎉 Database setup complete! You can now run "npm start".');

    } catch (err) {
        console.error('❌ Error setting up database:', err.message);
        if (err.code === 'ER_ACCESS_DENIED_ERROR') {
            console.error('\n👉 HINT: detailed check your DB_PASSWORD in the .env file.');
        }
    } finally {
        if (connection) await connection.end();
    }
}

setupDatabase();