/**
 * Adds subject and recipient_email columns to the tickets table.
 * Run from SAT_backend: node run-ticket-migration.js
 */
require('dotenv').config();
const mysql = require('mysql2/promise');

const MIGRATION_SQL = [
  'ALTER TABLE tickets ADD COLUMN subject VARCHAR(255) NULL AFTER description',
  'ALTER TABLE tickets ADD COLUMN recipient_email VARCHAR(255) NULL AFTER subject'
];

async function run() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: process.env.MYSQL_HOST,
      user: process.env.MYSQL_USER,
      password: process.env.MYSQL_PASSWORD,
      database: "hse"
    });
    for (const sql of MIGRATION_SQL) {
      try {
        await connection.query(sql);
        const col = sql.includes('subject') ? 'subject' : 'recipient_email';
        console.log('Added column:', col);
      } catch (err) {
        if (err.code === 'ER_DUP_FIELDNAME') {
          console.log('Column already exists, skipping:', err.sqlMessage?.split("'")[1] || '');
        } else {
          throw err;
        }
      }
    }
    console.log('Ticket migration completed.');
  } catch (err) {
    console.error('Migration failed:', err.message);
    process.exit(1);
  } finally {
    if (connection) await connection.end();
  }
}

run();
