import 'dotenv/config';
import { readFileSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import mysql from 'mysql2/promise';
import { readFileSync as readConfig } from 'node:fs';

const __dirname = dirname(fileURLToPath(import.meta.url));

const env = process.env.NODE_ENV ?? 'development';
const configPath = join(__dirname, '..', 'config', 'config.json');
const raw = readConfig(configPath, 'utf-8');
const allConfigs = JSON.parse(raw);
const dbConfig = allConfigs[env]?.db;

if (!dbConfig) {
  console.error(`No database config found for environment: ${env}`);
  process.exit(1);
}

async function runMigrations() {
  console.log(`Running migrations for environment: ${env}`);

  const connection = await mysql.createConnection({
    host: dbConfig.host,
    port: dbConfig.port,
    user: dbConfig.user,
    password: dbConfig.password,
    multipleStatements: true,
  });

  await connection.query(`CREATE DATABASE IF NOT EXISTS \`${dbConfig.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
  await connection.query(`USE \`${dbConfig.database}\``);

  const migrationsDir = __dirname;
  const files = ['001_create_tables.sql', '002_seed_data.sql'];

  for (const file of files) {
    const filePath = join(migrationsDir, file);
    console.log(`Running: ${file}`);
    const sql = readFileSync(filePath, 'utf-8');
    const statements = sql.split(';').filter((s) => s.trim().length > 0);

    for (const statement of statements) {
      try {
        await connection.query(statement);
      } catch (err: unknown) {
        const msg = err instanceof Error ? err.message : String(err);
        if (msg.includes('already exists') || msg.includes('Duplicate')) {
          console.log(`  Skipped (already exists): ${statement.slice(0, 60)}...`);
        } else {
          console.error(`  Error: ${msg}`);
          console.error(`  SQL: ${statement.slice(0, 200)}`);
        }
      }
    }
    console.log(`  Done: ${file}`);
  }

  await connection.end();
  console.log('Migrations complete.');
}

runMigrations().catch((err) => {
  console.error('Migration failed:', err);
  process.exit(1);
});