import sequelize from '#services/database/sequelize/sequelizeService';
import { QueryTypes } from 'sequelize';
import LoggingService from '#services/file-system/logService';

/**
 * Partition Management Service
 *
 * DEBT-01: Automatically creates monthly partitions for audit tables
 * to prevent system failure when partitions are not manually created.
 *
 * Tables requiring partition management:
 * - auth_audit_logs (partitioned by RANGE(created_at))
 * - refresh_token_audit_logs (partitioned by RANGE(created_at))
 * - notification_delivery_attempts (partitioned by RANGE(created_at))
 */
class PartitionManagementService {
	private static instance: PartitionManagementService;
	private logger: LoggingService;

	// SEC-01: Whitelist of allowed table names for partition management
	private static readonly ALLOWED_TABLES = [
		'auth_audit_logs',
		'refresh_token_audit_logs',
		'notification_delivery_attempts',
	] as const;

	private constructor() {
		this.logger = new LoggingService();
	}

	static getInstance(): PartitionManagementService {
		if (!PartitionManagementService.instance) {
			PartitionManagementService.instance = new PartitionManagementService();
		}
		return PartitionManagementService.instance;
	}

	/**
	 * SEC-01: Validate and sanitize partition name to prevent SQL injection
	 * @param tableName - The base table name
	 * @param year - Year (e.g., 2026)
	 * @param month - Month (1-12)
	 * @returns Validated partition name or throws error
	 */
	private static validatePartitionName(tableName: string, year: number, month: number): string {
		// Validate tableName is in whitelist
		if (!PartitionManagementService.ALLOWED_TABLES.includes(tableName as any)) {
			throw new Error(`Invalid table name: ${tableName}`);
		}

		// Validate year and month are numbers and within reasonable ranges
		if (!Number.isInteger(year) || year < 2000 || year > 2100) {
			throw new Error(`Invalid year: ${year}`);
		}

		if (!Number.isInteger(month) || month < 1 || month > 12) {
			throw new Error(`Invalid month: ${month}`);
		}

		// Construct partition name with validated components
		return `${tableName}_${year}_${String(month).padStart(2, '0')}`;
	}

	/**
	 * Create a monthly partition for a table
	 * @param tableName - The name of the partitioned table
	 * @param year - Year (e.g., 2026)
	 * @param month - Month (1-12)
	 */
	async createMonthlyPartition(tableName: string, year: number, month: number): Promise<boolean> {
		// SEC-01: Validate partition name before use
		const partitionName = PartitionManagementService.validatePartitionName(tableName, year, month);
		const startDate = new Date(year, month - 1, 1);
		const endDate = new Date(year, month, 1);

		const startDateStr = startDate.toISOString();
		const endDateStr = endDate.toISOString();

		try {
			// Create partition atomically using IF NOT EXISTS to handle race conditions
			// SEC-01: Use validated partition names - both tableName and partitionName are now validated
			await sequelize.query(
				`
				CREATE TABLE IF NOT EXISTS ${partitionName}
				PARTITION OF ${tableName}
				FOR VALUES FROM (:startDate) TO (:endDate)
				`,
				{
					replacements: { startDate: startDateStr, endDate: endDateStr },
					type: QueryTypes.RAW,
				},
			);

			this.logger
				.logInfoAsync('Partition', `Ensured partition ${partitionName} exists for ${tableName}`, null)
				.catch(() => {});
			return true;
		} catch (error) {
			this.logger.logErrorAsync('Partition', error as Error, { tableName, partitionName }).catch(() => {});
			return false;
		}
	}

	/**
	 * Create partitions for all audit tables for a specific month
	 * @param year - Year (e.g., 2026)
	 * @param month - Month (1-12)
	 */
	async createPartitionsForMonth(year: number, month: number): Promise<void> {
		const tables = ['auth_audit_logs', 'refresh_token_audit_logs', 'notification_delivery_attempts'];

		const results = await Promise.allSettled(tables.map((table) => this.createMonthlyPartition(table, year, month)));

		const failed = results.filter((r) => r.status === 'rejected' || (r.status === 'fulfilled' && !r.value));
		if (failed.length > 0) {
			this.logger.logInfoAsync('Partition', `Failed to create ${failed.length} partitions`, null).catch(() => {});
		}
	}

	/**
	 * Create partitions for current and next N months
	 * @param monthsAhead - Number of months ahead to create partitions for (default: 3)
	 */
	async ensurePartitions(monthsAhead = 3): Promise<void> {
		const now = new Date();
		const currentYear = now.getFullYear();
		const currentMonth = now.getMonth() + 1;

		for (let i = 0; i <= monthsAhead; i++) {
			const date = new Date(currentYear, currentMonth - 1 + i, 1);
			const year = date.getFullYear();
			const month = date.getMonth() + 1;
			await this.createPartitionsForMonth(year, month);
		}
	}

	/**
	 * Drop old partitions (older than specified months)
	 * @param retainMonths - Number of months to retain (default: 12)
	 */
	async dropOldPartitions(retainMonths = 12): Promise<number> {
		const cutoffDate = new Date();
		cutoffDate.setMonth(cutoffDate.getMonth() - retainMonths);

		const tables = ['auth_audit_logs', 'refresh_token_audit_logs', 'notification_delivery_attempts'];
		let droppedCount = 0;

		for (const table of tables) {
			try {
				// Get all partitions for this table
				const partitions = await sequelize.query(
					`
					SELECT tablename
					FROM pg_tables
					WHERE schemaname = 'public'
					AND tablename LIKE :pattern
					ORDER BY tablename
					`,
					{
						replacements: { pattern: `${table}_%` },
						type: QueryTypes.SELECT,
					},
				);

				for (const partition of partitions as any[]) {
					const partitionName = partition.tablename;
					// Extract year and month from partition name (e.g., auth_audit_logs_2025_01)
					const match = partitionName.match(/^(.+?)_(\d{4})_(\d{2})$/);
					if (match) {
						const tableName = match[1];
						const year = parseInt(match[2], 10);
						const month = parseInt(match[3], 10);
						const partitionDate = new Date(year, month - 1, 1);

						if (partitionDate < cutoffDate) {
							// SEC-01: Validate partition name before DROP
							try {
								PartitionManagementService.validatePartitionName(tableName, year, month);
								await sequelize.query(`DROP TABLE IF EXISTS ${partitionName}`, { type: QueryTypes.RAW });
								droppedCount++;
								this.logger.logInfoAsync('Partition', `Dropped old partition ${partitionName}`, null).catch(() => {});
							} catch (validationError) {
								this.logger.logErrorAsync('Partition', validationError as Error, { partitionName }).catch(() => {});
							}
						}
					}
				}
			} catch (error) {
				this.logger.logErrorAsync('Partition', error as Error, { table }).catch(() => {});
			}
		}

		return droppedCount;
	}
}

export default PartitionManagementService;
