import { Pool } from 'pg';
import { PoolConfig } from 'pg';
import Logger from '../src/utils/logger';

/**
 * Audit Data Retention Script
 * 
 * This script performs cleanup tasks for audit data:
 * - Clean up processed audit_outbox records
 * - Recover stuck PROCESSING events
 * - Archive old audit logs (optional)
 * 
 * Usage:
 * - Cleanup processed outbox records: pnpm ts-node scripts/audit-cleanup.ts outbox
 * - Recover stuck events: pnpm ts-node scripts/audit-cleanup.ts recover
 * - Full cleanup: pnpm ts-node scripts/audit-cleanup.ts full
 * 
 * Cron setup (add to crontab):
 * - Daily outbox cleanup (2 AM): 0 2 * * * cd /path/to/bekind-backend && pnpm ts-node scripts/audit-cleanup.ts outbox
 * - Recovery cron (every 5 min): */5 * * * * cd /path/to/bekind-backend && pnpm ts-node scripts/audit-cleanup.ts recover
 */

interface Config {
	database: {
		host: string;
		port: number;
		database: string;
		user: string;
		password: string;
	};
}

const config: Config = {
	database: {
		host: process.env.DB_HOST || 'localhost',
		port: parseInt(process.env.DB_PORT || '5432'),
		database: process.env.DB_NAME || 'bekind',
		user: process.env.DB_USER || 'postgres',
		password: process.env.DB_PASSWORD || '',
	},
};

const pool = new Pool(config.database as PoolConfig);

async function cleanupOutbox() {
	const client = await pool.connect();
	try {
		Logger.info('Cleaning up processed audit_outbox records...');

		// Delete COMPLETED records older than 7 days
		const result = await client.query(`
			DELETE FROM audit.audit_outbox
			WHERE status = 'COMPLETED'
			AND processed_at < NOW() - INTERVAL '7 days'
			RETURNING id;
		`);

		const deletedCount = result.rowCount || 0;
		Logger.info(`Deleted ${deletedCount} processed outbox records`);
	} catch (error) {
		Logger.error('Error cleaning up outbox:', error);
		throw error;
	} finally {
		client.release();
	}
}

async function recoverStuckEvents() {
	const client = await pool.connect();
	try {
		Logger.info('Recovering stuck PROCESSING events...');

		// Reset PROCESSING events that have been stuck for more than 5 minutes
		const result = await client.query(`
			UPDATE audit.audit_outbox
			SET status = 'PENDING',
				retry_count = retry_count + 1,
				updated_at = NOW()
			WHERE status = 'PROCESSING'
			AND updated_at < NOW() - INTERVAL '5 minutes'
			RETURNING id;
		`);

		const recoveredCount = result.rowCount || 0;
		Logger.info(`Recovered ${recoveredCount} stuck events`);
	} catch (error) {
		Logger.error('Error recovering stuck events:', error);
		throw error;
	} finally {
		client.release();
	}
}

async function cleanupFailedEvents() {
	const client = await pool.connect();
	try {
		Logger.info('Cleaning up FAILED events with too many retries...');

		// Delete FAILED events with more than 5 retries and older than 30 days
		const result = await client.query(`
			DELETE FROM audit.audit_outbox
			WHERE status = 'FAILED'
			AND retry_count >= 5
			AND created_at < NOW() - INTERVAL '30 days'
			RETURNING id;
		`);

		const deletedCount = result.rowCount || 0;
		Logger.info(`Deleted ${deletedCount} old failed events`);
	} catch (error) {
		Logger.error('Error cleaning up failed events:', error);
		throw error;
	} finally {
		client.release();
	}
}

async function getOutboxStats() {
	const client = await pool.connect();
	try {
		const result = await client.query(`
			SELECT 
				status,
				COUNT(*) as count,
				MIN(created_at) as oldest,
				MAX(created_at) as newest
			FROM audit.audit_outbox
			GROUP BY status
			ORDER BY status;
		`);

		Logger.info('Audit outbox statistics:');
		result.rows.forEach((row: any) => {
			Logger.info(`  ${row.status}: ${row.count} records (oldest: ${row.oldest}, newest: ${row.newest})`);
		});
	} catch (error) {
		Logger.error('Error getting outbox stats:', error);
		throw error;
	} finally {
		client.release();
	}
}

async function main() {
	const command = process.argv[2];

	try {
		switch (command) {
			case 'outbox':
				await cleanupOutbox();
				break;
			case 'recover':
				await recoverStuckEvents();
				break;
			case 'failed':
				await cleanupFailedEvents();
				break;
			case 'full':
				await recoverStuckEvents();
				await cleanupOutbox();
				await cleanupFailedEvents();
				break;
			case 'stats':
				await getOutboxStats();
				break;
			default:
				Logger.info('Usage:');
				Logger.info('  pnpm ts-node scripts/audit-cleanup.ts outbox');
				Logger.info('  pnpm ts-node scripts/audit-cleanup.ts recover');
				Logger.info('  pnpm ts-node scripts/audit-cleanup.ts failed');
				Logger.info('  pnpm ts-node scripts/audit-cleanup.ts full');
				Logger.info('  pnpm ts-node scripts/audit-cleanup.ts stats');
				process.exit(1);
		}

		await pool.end();
		process.exit(0);
	} catch (error) {
		Logger.error('Script failed:', error);
		await pool.end();
		process.exit(1);
	}
}

if (require.main === module) {
	main();
}

export { cleanupOutbox, recoverStuckEvents, cleanupFailedEvents, getOutboxStats };
