/**
 * Fix sat_audit_schedules schema issues:
 * - created_at / updated_at missing DEFAULT (INSERT fails)
 * - wrong FK on id -> users(user_id); should be assigned_to_user_id -> users(user_id)
 *
 * Usage (from SAT_backend folder):
 *   node scripts/fix-sat-audit-schedules-table.js
 */
const path = require('path');
require('dotenv').config({ path: path.join(__dirname, '..', '.env') });
require('dotenv').config({ path: path.join(__dirname, '..', '..', 'Risk_assessment_backend', '.env') });

const pool = require('../src/config/db');

async function fkExists(tableName, constraintName) {
  const [rows] = await pool.query(
    `SELECT COUNT(*) AS c FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = ?
       AND CONSTRAINT_NAME = ?
       AND CONSTRAINT_TYPE = 'FOREIGN KEY'`,
    [tableName, constraintName]
  );
  return rows[0].c > 0;
}

async function main() {
  try {
    if (await fkExists('sat_audit_schedules', 'users_sat_audit_schedules_1')) {
      await pool.query('ALTER TABLE sat_audit_schedules DROP FOREIGN KEY users_sat_audit_schedules_1');
      console.log('[ok]   Dropped incorrect FK users_sat_audit_schedules_1 on id');
    } else {
      console.log('[skip] users_sat_audit_schedules_1 not present');
    }

    if (!(await fkExists('sat_audit_schedules', 'sat_audit_schedules_assigned_fk'))) {
      await pool.query(
        `ALTER TABLE sat_audit_schedules
         ADD CONSTRAINT sat_audit_schedules_assigned_fk
         FOREIGN KEY (assigned_to_user_id) REFERENCES users(user_id)`
      );
      console.log('[ok]   Added FK sat_audit_schedules_assigned_fk on assigned_to_user_id');
    } else {
      console.log('[skip] sat_audit_schedules_assigned_fk already exists');
    }

    await pool.query(
      `ALTER TABLE sat_audit_schedules
       MODIFY created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       MODIFY updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`
    );
    console.log('[ok]   created_at / updated_at defaults applied');

    console.log('sat_audit_schedules table is ready.');
  } catch (err) {
    console.error('[fail]', err.message);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
}

main();
