import sequelize from '#services/database/sequelize/sequelizeService';
import { QueryTypes } from 'sequelize';
import Logger from '#utils/logger';

/**
 * Audit Log Maintenance Job
 *
 * This job handles:
 * 1. Creating new monthly partitions for audit log tables
 * 2. Dropping old partitions beyond retention period
 * 3. Cleaning up expired sessions
 *
 * Should be scheduled to run daily (e.g., via cron or node-cron)
 */

export class AuditLogMaintenanceJob {
	/**
	 * Create new monthly partitions for audit log tables
	 */
	static async createPartitions(): Promise<void> {
		try {
			// Create auth_audit_logs partition for next month
			await sequelize.query(
				`
				SELECT create_auth_audit_log_partition()
			`,
				{ type: QueryTypes.SELECT },
			);

			// Create refresh_token_audit_logs partition for next month
			await sequelize.query(
				`
				SELECT create_refresh_token_audit_log_partition()
			`,
				{ type: QueryTypes.SELECT },
			);

			Logger.info('Audit log partitions created successfully');
		} catch (error) {
			Logger.error('Failed to create audit log partitions');
			throw error;
		}
	}

	/**
	 * Drop old partitions beyond retention period (default 3 months)
	 */
	static async dropOldPartitions(retentionMonths: number = 3): Promise<void> {
		try {
			await sequelize.query('SELECT drop_old_audit_log_partitions(:retentionMonths)', {
				replacements: { retentionMonths },
				type: QueryTypes.SELECT,
			});

			Logger.info(`Old audit log partitions dropped (retention: ${retentionMonths} months)`);
		} catch (error) {
			Logger.error('Failed to drop old audit log partitions');
			throw error;
		}
	}

	/**
	 * Clean up expired sessions
	 */
	static async cleanupExpiredSessions(): Promise<number> {
		try {
			const [result] = await sequelize.query<{
				cleanup_expired_sessions: number;
			}>('SELECT cleanup_expired_sessions() as cleanup_expired_sessions', {
				type: QueryTypes.SELECT,
			});

			const count = result?.cleanup_expired_sessions || 0;
			Logger.info(`Cleaned up ${count} expired sessions`);
			return count;
		} catch (error) {
			Logger.error('Failed to cleanup expired sessions');
			throw error;
		}
	}

	/**
	 * Clean up expired password reset tokens
	 */
	static async cleanupExpiredPasswordResetTokens(): Promise<number> {
		try {
			const [result] = await sequelize.query<{
				cleanup_expired_password_reset_tokens: number;
			}>('SELECT cleanup_expired_password_reset_tokens() as cleanup_expired_password_reset_tokens', {
				type: QueryTypes.SELECT,
			});

			const count = result?.cleanup_expired_password_reset_tokens || 0;
			Logger.info(`Cleaned up ${count} expired password reset tokens`);
			return count;
		} catch (error) {
			Logger.error('Failed to cleanup expired password reset tokens');
			throw error;
		}
	}

	/**
	 * Clean up expired email verify tokens
	 */
	static async cleanupExpiredEmailVerifyTokens(): Promise<number> {
		try {
			const [result] = await sequelize.query<{
				cleanup_expired_email_verify_tokens: number;
			}>('SELECT cleanup_expired_email_verify_tokens() as cleanup_expired_email_verify_tokens', {
				type: QueryTypes.SELECT,
			});

			const count = result?.cleanup_expired_email_verify_tokens || 0;
			Logger.info(`Cleaned up ${count} expired email verify tokens`);
			return count;
		} catch (error) {
			Logger.error('Failed to cleanup expired email verify tokens');
			throw error;
		}
	}

	/**
	 * Clean up completed audit outbox records older than retention period
	 */
	static async cleanupCompletedOutboxEvents(retentionDays: number = 7): Promise<number> {
		try {
			const [result] = await sequelize.query<{
				cleanup_completed_outbox_events: number;
			}>(
				'DELETE FROM audit.audit_outbox WHERE status = $1 AND created_at < NOW() - INTERVAL $2 DAY RETURNING COUNT(*) as cleanup_completed_outbox_events',
				{
					bind: ['COMPLETED', retentionDays],
					type: QueryTypes.SELECT,
				},
			);

			const count = result?.cleanup_completed_outbox_events || 0;
			Logger.info(`Cleaned up ${count} completed outbox events (retention: ${retentionDays} days)`);
			return count;
		} catch (error) {
			Logger.error('Failed to cleanup completed outbox events');
			throw error;
		}
	}

	/**
	 * Clean up failed audit outbox records older than retention period
	 */
	static async cleanupFailedOutboxEvents(retentionDays: number = 30): Promise<number> {
		try {
			const [result] = await sequelize.query<{
				cleanup_failed_outbox_events: number;
			}>(
				'DELETE FROM audit.audit_outbox WHERE status IN ($1, $2) AND created_at < NOW() - INTERVAL $3 DAY RETURNING COUNT(*) as cleanup_failed_outbox_events',
				{
					bind: ['FAILED', 'FAILED_PERMANENT', retentionDays],
					type: QueryTypes.SELECT,
				},
			);

			const count = result?.cleanup_failed_outbox_events || 0;
			Logger.info(`Cleaned up ${count} failed outbox events (retention: ${retentionDays} days)`);
			return count;
		} catch (error) {
			Logger.error('Failed to cleanup failed outbox events');
			throw error;
		}
	}

	/**
	 * Clean up resolved audit dead letter records older than retention period
	 */
	static async cleanupResolvedDeadLetters(retentionDays: number = 90): Promise<number> {
		try {
			const [result] = await sequelize.query<{
				cleanup_resolved_dead_letters: number;
			}>(
				'DELETE FROM audit.audit_dead_letter WHERE investigation_status = $1 AND created_at < NOW() - INTERVAL $2 DAY RETURNING COUNT(*) as cleanup_resolved_dead_letters',
				{
					bind: ['RESOLVED', retentionDays],
					type: QueryTypes.SELECT,
				},
			);

			const count = result?.cleanup_resolved_dead_letters || 0;
			Logger.info(`Cleaned up ${count} resolved dead letter records (retention: ${retentionDays} days)`);
			return count;
		} catch (error) {
			Logger.error('Failed to cleanup resolved dead letter records');
			throw error;
		}
	}

	/**
	 * Run all maintenance tasks
	 */
	static async runAll(): Promise<void> {
		Logger.info('Starting audit log maintenance job');

		try {
			// Create new partitions for next month
			await this.createPartitions();

			// Drop partitions older than 3 months
			await this.dropOldPartitions(3);

			// Cleanup expired sessions
			await this.cleanupExpiredSessions();

			// Cleanup expired tokens
			await this.cleanupExpiredPasswordResetTokens();
			await this.cleanupExpiredEmailVerifyTokens();

			// Cleanup audit outbox records
			await this.cleanupCompletedOutboxEvents(7);
			await this.cleanupFailedOutboxEvents(30);

			// Cleanup resolved dead letter records
			await this.cleanupResolvedDeadLetters(90);

			Logger.info('Audit log maintenance job completed successfully');
		} catch (error) {
			Logger.error('Audit log maintenance job failed');
			throw error;
		}
	}
}
