require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

const init = async () => {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    multipleStatements: true,
  });

  console.log('[Init] Connected to MySQL');

  const schemaPath = path.resolve(__dirname, '..', 'schema.sql');
  const schema = fs.readFileSync(schemaPath, 'utf8');

  const modifiedSchema = schema
    .replace("'$2b$10$YOUR_HASH_HERE'", "await hashPassword('admin123')")
    .replace('CREATE DATABASE IF NOT EXISTS tokkie_shuttles;', 'CREATE DATABASE IF NOT EXISTS tokkie_shuttles;')
    .replace('USE tokkie_shuttles;', 'USE tokkie_shuttles;');

  try {
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`${process.env.DB_NAME || 'tokkie_shuttles'}\`;`);
    await connection.query(`USE \`${process.env.DB_NAME || 'tokkie_shuttles'}\`;`);

    const statements = schema
      .replace(/DELIMITER \$\$[\s\S]*?DELIMITER ;/g, '')
      .replace(/CREATE PROCEDURE[\s\S]*?END\$\$/g, '')
      .split(';')
      .filter((s) => s.trim().length > 0);

    for (const statement of statements) {
      if (statement.trim().startsWith('INSERT INTO users')) {
        continue;
      }
      if (statement.trim().length > 5) {
        try {
          await connection.query(statement);
        } catch (err) {
          if (err.errno === 1050 || err.errno === 1061) continue;
          console.log(`[Init] Skipping statement (${err.errno}):`, statement.substring(0, 50));
        }
      }
    }

    const bcrypt = require('bcryptjs');
    const hash = await bcrypt.hash('admin123', 10);
    try {
      await connection.query(
        `INSERT IGNORE INTO users (username, email, password_hash, full_name, role, is_active) VALUES (?, ?, ?, ?, ?, ?)`,
        ['admin', 'admin@tokkie.com', hash, 'System Admin', 'admin', true]
      );
      console.log('[Init] Admin user created');
    } catch (err) {
      console.log('[Init] Admin user may already exist:', err.message);
    }

    console.log('[Init] Schema applied successfully');
  } catch (error) {
    console.error('[Init] Error:', error.message);
  } finally {
    await connection.end();
    process.exit(0);
  }
};

init();
