#!/usr/bin/env node
/**
 * Migration runner — applies SQL migration files in order.
 * Tracks applied migrations in the `schema_migrations` table so each file
 * is executed exactly once, making repeated runs safe.
 */
import { readFileSync, readdirSync, existsSync } from 'fs';
import { join, dirname } from 'path';
import { fileURLToPath } from 'url';
import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

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

// Load environment variables
const envPath = join(__dirname, '.env');
if (!existsSync(envPath)) {
  console.error('Error: .env file not found!');
  console.error('Please run the setup script first or create .env manually.');
  process.exit(1);
}

dotenv.config({ path: envPath });

const config = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'cloudvault',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'cloudvault',
  multipleStatements: true
};

async function ensureMigrationsTable(connection) {
  await connection.query(`
    CREATE TABLE IF NOT EXISTS \`schema_migrations\` (
      \`id\`         int          NOT NULL AUTO_INCREMENT,
      \`filename\`   varchar(255) NOT NULL,
      \`applied_at\` timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (\`id\`),
      UNIQUE KEY \`schema_migrations_filename_unique\` (\`filename\`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);
}

async function getAppliedMigrations(connection) {
  const [rows] = await connection.query(
    'SELECT filename FROM `schema_migrations` ORDER BY id'
  );
  return new Set(rows.map(r => r.filename));
}

async function recordMigration(connection, filename) {
  await connection.query(
    'INSERT INTO `schema_migrations` (filename) VALUES (?)',
    [filename]
  );
}

async function runMigrations() {
  console.log('Running migrations...');
  console.log(`Database: ${config.database} on ${config.host}:${config.port}`);

  let connection;
  try {
    connection = await mysql.createConnection(config);
    console.log('✓ Connected to database');

    await ensureMigrationsTable(connection);

    const applied = await getAppliedMigrations(connection);
    console.log(`✓ Already applied: ${applied.size} migration(s)`);

    const migrationsDir = join(__dirname, 'drizzle');
    const files = readdirSync(migrationsDir)
      .filter(f => f.endsWith('.sql'))
      .sort(); // lexicographic order matches the 0000_, 0001_, … naming scheme

    const pending = files.filter(f => !applied.has(f));
    console.log(`  Pending: ${pending.length} migration(s)`);

    if (pending.length === 0) {
      console.log('✓ Schema is up to date — nothing to do');
      return;
    }

    for (const file of pending) {
      const filePath = join(migrationsDir, file);
      const sql = readFileSync(filePath, 'utf8');

      console.log(`  Applying ${file}...`);
      await connection.query(sql);
      await recordMigration(connection, file);
      console.log(`  ✓ ${file} applied`);
    }

    console.log(`✓ ${pending.length} migration(s) applied successfully`);
  } catch (error) {
    console.error('✗ Migration failed:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

runMigrations();
