import { pool } from "./config/db.js";

async function migrate() {
  const connection = await pool.getConnection();
  try {
    console.log("Starting payment methods migration...");
    await connection.beginTransaction();

    // 1. Create payment_methods table if not exists
    await connection.query(`
      CREATE TABLE IF NOT EXISTS payment_methods (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL UNIQUE,
        code VARCHAR(50) NOT NULL UNIQUE,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `);
    console.log("Table 'payment_methods' verified/created.");

    // 2. Seed default payment methods if they do not exist
    const defaultMethods = [
      { name: "Efectivo", code: "cash" },
      { name: "Tarjeta de Crédito", code: "card" },
      { name: "Transferencia Bancaria", code: "transfer" },
      { name: "Otro", code: "other" }
    ];

    for (const m of defaultMethods) {
      await connection.query(`
        INSERT INTO payment_methods (name, code, is_active)
        VALUES (:name, :code, 1)
        ON DUPLICATE KEY UPDATE name = name
      `, { name: m.name, code: m.code });
    }
    console.log("Default payment methods seeded.");

    // 3. Modify payments table method column to VARCHAR(50) for dynamic values
    await connection.query(`
      ALTER TABLE payments MODIFY COLUMN method VARCHAR(50) NOT NULL DEFAULT 'cash'
    `);
    console.log("Table 'payments' method column modified to VARCHAR(50).");

    await connection.commit();
    console.log("Payment methods migration complete!");
    process.exit(0);
  } catch (err) {
    await connection.rollback();
    console.error("Migration failed:", err);
    process.exit(1);
  } finally {
    connection.release();
  }
}

migrate();
