/**
 * One-time backfill script: set is_attendance_request, is_leave, is_absence_clarification
 * on existing attendances based on FK columns (attendancerequest_id, leaveapplication_id,
 * absenceclarification_id / status_absenceclarification).
 *
 * Run from SAPPS-BACKEND root: node scripts/backfill-attendance-flags.js
 * Prerequisite: attendances table must already have columns is_attendance_request, is_leave, is_absence_clarification.
 */

const path = require("path");
require("dotenv").config({ path: path.join(__dirname, "../.env") });

const config = require("../config.json");
const { Sequelize } = require("sequelize");

const dbConfig = config.databaseConfigSAPPS || config;
const sequelize = new Sequelize(dbConfig.database, dbConfig.user, dbConfig.password, {
	host: dbConfig.host,
	port: dbConfig.port || 3306,
	dialect: "mysql",
	logging: false,
	define: { timestamps: false },
});

async function backfill() {
	const t = await sequelize.transaction();
	try {
		// 1. is_attendance_request
		await sequelize.query(
			"UPDATE attendances SET is_attendance_request = 1 WHERE attendancerequest_id IS NOT NULL AND (is_attendance_request IS NULL OR is_attendance_request = 0)",
			{ transaction: t }
		);
		await sequelize.query(
			"UPDATE attendances SET is_attendance_request = 0 WHERE attendancerequest_id IS NULL AND is_attendance_request = 1",
			{ transaction: t }
		);
		await sequelize.query(
			"UPDATE attendances SET is_leave = 1 WHERE leaveapplication_id IS NOT NULL AND (is_leave IS NULL OR is_leave = 0)",
			{ transaction: t }
		);
		await sequelize.query(
			"UPDATE attendances SET is_leave = 0 WHERE leaveapplication_id IS NULL AND is_leave = 1",
			{ transaction: t }
		);
		await sequelize.query(
			"UPDATE attendances SET is_absence_clarification = 1 WHERE (absenceclarification_id IS NOT NULL OR status_absenceclarification = 1) AND (is_absence_clarification IS NULL OR is_absence_clarification = 0)",
			{ transaction: t }
		);
		await sequelize.query(
			"UPDATE attendances SET is_absence_clarification = 0 WHERE absenceclarification_id IS NULL AND (status_absenceclarification IS NULL OR status_absenceclarification = 0) AND is_absence_clarification = 1",
			{ transaction: t }
		);

		await t.commit();
		console.log("Backfill attendance flags completed successfully.");
	} catch (err) {
		await t.rollback();
		console.error("Backfill failed:", err.message);
		process.exit(1);
	} finally {
		await sequelize.close();
	}
}

backfill();
