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

/**
 * Audit Partition Manager
 * 
 * This script creates monthly partitions for audit tables and performs cleanup.
 * Can be run manually or scheduled via cron.
 * 
 * Usage:
 * - Create partitions for next 3 months: pnpm ts-node scripts/audit-partition-manager.ts create 3
 * - Drop partitions older than 24 months: pnpm ts-node scripts/audit-partition-manager.ts cleanup 24
 * 
 * Cron setup (add to crontab):
 * - Monthly partition creation (1st of each month at 00:00):
 *   0 0 1 * * cd /path/to/bekind-backend && pnpm ts-node scripts/audit-partition-manager.ts create 3
 * - Monthly partition cleanup (1st of each month at 02:00):
 *   0 2 1 * * cd /path/to/bekind-backend && pnpm ts-node scripts/audit-partition-manager.ts cleanup 24
 */

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 createPartition(monthsAhead: number = 3) {
	const client = await pool.connect();
	try {
		Logger.info(`Creating partitions for next ${monthsAhead} months...`);

		for (let i = 0; i < monthsAhead; i++) {
			const date = new Date();
			date.setMonth(date.getMonth() + i);
			const year = date.getFullYear();
			const month = String(date.getMonth() + 1).padStart(2, '0');
			const partitionName = `system_audit_logs_${year}_${month}`;
			const startDate = `${year}-${month}-01`;
			const endDate = new Date(year, date.getMonth() + 1, 1).toISOString().split('T')[0];

			// Check if partition already exists
			const checkResult = await client.query(`
				SELECT EXISTS (
					SELECT 1 FROM information_schema.tables 
					WHERE table_schema = 'audit' 
					AND table_name = $1
				);
			`, [partitionName]);

			if (checkResult.rows[0].exists) {
				Logger.info(`Partition ${partitionName} already exists, skipping`);
				continue;
			}

			// Create partition
			await client.query(`
				CREATE TABLE audit.${partitionName} PARTITION OF audit.system_audit_logs
				FOR VALUES FROM ('${startDate}') TO ('${endDate}');
			`);

			Logger.info(`Created partition ${partitionName} (${startDate} to ${endDate})`);
		}

		Logger.info('Partition creation completed successfully');
	} catch (error) {
		Logger.error('Error creating partitions:', error);
		throw error;
	} finally {
		client.release();
	}
}

async function dropOldPartitions(monthsToKeep: number = 24) {
	const client = await pool.connect();
	try {
		Logger.info(`Dropping partitions older than ${monthsToKeep} months...`);

		const cutoffDate = new Date();
		cutoffDate.setMonth(cutoffDate.getMonth() - monthsToKeep);
		const year = cutoffDate.getFullYear();
		const month = String(cutoffDate.getMonth() + 1).padStart(2, '0');
		const cutoffPartition = `system_audit_logs_${year}_${month}`;

		// Get all partitions older than cutoff
		const result = await client.query(`
			SELECT table_name 
			FROM information_schema.tables 
			WHERE table_schema = 'audit' 
			AND table_name LIKE 'system_audit_logs_%'
			AND table_name < $1
			ORDER BY table_name;
		`, [cutoffPartition]);

		const partitions = result.rows.map((row: any) => row.table_name);

		if (partitions.length === 0) {
			Logger.info('No old partitions to drop');
			return;
		}

		// Drop each partition
		for (const partition of partitions) {
			await client.query(`DROP TABLE IF EXISTS audit.${partition} CASCADE;`);
			Logger.info(`Dropped partition ${partition}`);
		}

		Logger.info(`Dropped ${partitions.length} old partitions`);
	} catch (error) {
		Logger.error('Error dropping partitions:', error);
		throw error;
	} finally {
		client.release();
	}
}

async function listPartitions() {
	const client = await pool.connect();
	try {
		const result = await client.query(`
			SELECT table_name, table_size
			FROM (
				SELECT 
					table_name,
					pg_total_relation_size('audit.' || table_name) as table_size
				FROM information_schema.tables 
				WHERE table_schema = 'audit' 
				AND table_name LIKE 'system_audit_logs_%'
				ORDER BY table_name DESC
			) t;
		`);

		Logger.info('Current audit partitions:');
		result.rows.forEach((row: any) => {
			const sizeMB = (row.table_size / 1024 / 1024).toFixed(2);
			Logger.info(`  ${row.table_name}: ${sizeMB} MB`);
		});
	} catch (error) {
		Logger.error('Error listing partitions:', error);
		throw error;
	} finally {
		client.release();
	}
}

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

	try {
		switch (command) {
			case 'create':
				const months = argument ? parseInt(argument) : 3;
				await createPartition(months);
				break;
			case 'cleanup':
				const monthsToKeep = argument ? parseInt(argument) : 24;
				await dropOldPartitions(monthsToKeep);
				break;
			case 'list':
				await listPartitions();
				break;
			default:
				Logger.info('Usage:');
				Logger.info('  pnpm ts-node scripts/audit-partition-manager.ts create [months]');
				Logger.info('  pnpm ts-node scripts/audit-partition-manager.ts cleanup [months]');
				Logger.info('  pnpm ts-node scripts/audit-partition-manager.ts list');
				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 { createPartition, dropOldPartitions, listPartitions };
