/**
 * Script de execução de migrations
 * Executa todas as migrations SQL em ordem
 */

import { resolve } from "node:path";
// Carregar variáveis de ambiente
import { config } from "dotenv";

// Tentar carregar .env.local primeiro, depois .env
config({ path: resolve(process.cwd(), ".env.local") });
config({ path: resolve(process.cwd(), ".env") });

import { readFileSync } from "node:fs";
import { join } from "node:path";
import { getPool, healthCheck, query } from "../index";

const MIGRATIONS_DIR = join(__dirname, ".");

// Lista de migrations em ordem
const MIGRATIONS = [
  "001_users.sql",
  "002_servers.sql",
  "003_fix_servers_location.sql",
  "004_listings.sql",
  "005_bids.sql",
  "006_transactions.sql",
  "007_escrow_deposits.sql",
  "008_disputes.sql",
  "009_item_price_history.sql",
  "010_notifications.sql",
  "011_donations.sql",
  "012_telemetry_and_ads_enhancement.sql",
  "013_add_trade_item_support.sql",
  "014_add_bid_increment.sql",
  "015_add_purchase_intentions.sql",
  "016_add_payment_method_change_count.sql",
  "016_create_unified_items_table.sql",
  "017_add_items_foreign_key.sql",
  "018_migrate_listings_to_unified_items.sql",
  "019_subscription_tiers.sql",
  "020_purchase_intentions_custom_offer.sql",
];

async function runMigrations() {
  console.log("🚀 Iniciando execução de migrations...\n");

  // Verificar conexão com banco
  console.log("🔍 Verificando conexão com PostgreSQL...");
  const isHealthy = await healthCheck();

  if (!isHealthy) {
    console.error(
      "❌ Falha na conexão com PostgreSQL. Verifique a variável PG_DB.",
    );
    process.exit(1);
  }

  console.log("✅ Conexão estabelecida com sucesso\n");

  // Verificar tabela de controle de migrations (opcional)
  try {
    await query(`
      CREATE TABLE IF NOT EXISTS schema_migrations (
        version VARCHAR(255) PRIMARY KEY,
        executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
  } catch (error) {
    console.error("❌ Erro ao criar tabela de controle:", error);
    process.exit(1);
  }

  // Executar cada migration
  for (const migrationFile of MIGRATIONS) {
    const migrationPath = join(MIGRATIONS_DIR, migrationFile);
    const version = migrationFile.replace(".sql", "");

    try {
      // Verificar se já foi executada
      const checkResult = await query<{ version: string }>(
        "SELECT version FROM schema_migrations WHERE version = $1",
        [version],
      );

      if (checkResult.rows.length > 0) {
        console.log(`⏭️  Migration ${version} já executada, pulando...`);
        continue;
      }

      console.log(`📝 Executando migration: ${migrationFile}...`);

      // Ler arquivo SQL
      const sql = readFileSync(migrationPath, "utf-8").replace(/^\uFEFF/, "");

      // Executar migration
      await query(sql);

      // Registrar como executada
      await query("INSERT INTO schema_migrations (version) VALUES ($1)", [
        version,
      ]);

      console.log(`✅ Migration ${version} executada com sucesso\n`);
    } catch (error) {
      console.error(`❌ Erro ao executar migration ${version}:`, error);
      process.exit(1);
    }
  }

  console.log("🎉 Todas as migrations foram executadas com sucesso!");

  // Fechar pool
  const pool = getPool();
  await pool.end();
  process.exit(0);
}

// Executar
runMigrations().catch((error) => {
  console.error("❌ Erro fatal:", error);
  process.exit(1);
});