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

const logger = new LoggingService();

export interface PoolConfig {
	username: string;
	password: string;
	database: string;
	host: string;
	port: number;
	pool?: {
		max: number;
		min: number;
		acquire: number;
		idle: number;
		evict: number;
	};
	slowQueryThresholdMs?: number;
}

const slowQueryThresholdMs = 500;

export class MultiPoolService {
	private static readonly pools = new Map<string, Sequelize>();

	static getPool(name: string): Sequelize {
		const existing = this.pools.get(name);
		if (existing) return existing;

		throw new Error(`Pool '${name}' not found. Did you call createPool() first?`);
	}

	static hasPool(name: string): boolean {
		return this.pools.has(name);
	}

	static createPool(name: string, config: PoolConfig): Sequelize {
		const existing = this.pools.get(name);
		if (existing) {
			void existing.close();
			this.pools.delete(name);
		}

		const poolSettings = config.pool ?? { max: 20, min: 2, acquire: 30000, idle: 10000, evict: 60000 };

		const sequelizeInstance = new Sequelize({
			username: config.username,
			password: config.password,
			database: config.database,
			host: config.host,
			port: config.port,
			dialect: 'postgres',
			pool: poolSettings,
			benchmark: true,
			logging(sql, timing) {
				const durationMs = typeof timing === 'number' ? timing : null;
				if (durationMs !== null && durationMs >= (config.slowQueryThresholdMs ?? slowQueryThresholdMs)) {
					void logger.logAsync('slow_query', 'Sequelize', `[${durationMs}ms] ${sql}`, null);
				} else {
					void logger.logDBAsync(sql);
				}
			},
		});

		this.pools.set(name, sequelizeInstance);
		return sequelizeInstance;
	}

	static async closeAll(): Promise<void> {
		await Promise.all([...this.pools.values()].map((pool) => pool.close()));
		this.pools.clear();
	}

	static async healthCheck(name: string): Promise<boolean> {
		const pool = this.pools.get(name);
		if (!pool) return false;
		try {
			await pool.authenticate();
			return true;
		} catch {
			return false;
		}
	}

	/**
	 * Initialize HA write + read pools from environment variables.
	 * Used by Phase 3b (HA Cluster) instead of autoLoadPools.
	 *
	 * Write pool: reads PG_WRITER_URL (or PG_CONNECTION_STRING) env var.
	 * Read pool: reads PG_READER_URL env var (falls back to PG_WRITER_URL).
	 *
	 * Example:
	 *   PG_WRITER_URL=postgresql://postgres:@dmin123@haproxy:5000/sso
	 *   PG_READER_URL=postgresql://postgres:@dmin123@haproxy:5001/sso
	 *
	 * @param poolName Optional custom pool name (default: 'write' and 'read')
	 */
	static initializeHaPools(poolName?: { write?: string; read?: string }): void {
		const writeUrl =
			process.env.PG_WRITER_URL ??
			process.env.PG_CONNECTION_STRING ??
			'postgresql://postgres:@dmin123@localhost:5432/sso';
		const readUrl =
			process.env.PG_READER_URL ??
			process.env.PG_WRITER_URL ??
			writeUrl;

		const writeName = poolName?.write ?? 'write';
		const readName = poolName?.read ?? 'read';

		const parseUrl = (url: string) => {
			const u = new URL(url);
			return {
				username: decodeURIComponent(u.username),
				password: decodeURIComponent(u.password),
				database: u.pathname.replace(/^\//, ''),
				host: u.hostname,
				port: parseInt(u.port, 10) || 5432,
			};
		};

		const writeConfig = parseUrl(writeUrl);
		this.createPool(writeName, {
			username: writeConfig.username,
			password: writeConfig.password,
			database: writeConfig.database,
			host: writeConfig.host,
			port: writeConfig.port,
		});

		if (readUrl !== writeUrl) {
			const readConfig = parseUrl(readUrl);
			this.createPool(readName, {
				username: readConfig.username,
				password: readConfig.password,
				database: readConfig.database,
				host: readConfig.host,
				port: readConfig.port,
			});
		} else {
			// Same URL for read and write — reuse the write pool
			void logger.logAsync(
				'warn',
				'MultiPoolService',
				`PG_READER_URL not set — read pool uses same connection as write pool`,
				null,
			);
		}

		void logger.logAsync(
			'info',
			'MultiPoolService',
			`HA pools initialized: write=${writeConfig.host}:${writeConfig.port}, read=${readUrl === writeUrl ? '(shared)' : `${parseUrl(readUrl).host}:${parseUrl(readUrl).port}`}`,
			null,
		);
	}

	static async autoLoadPools(): Promise<void> {
		// Defensive: project_db_connections is a legacy multi-tenant table not used by SSO.
		// The SSO project does not use dynamic per-project database pools,
		// so if the table is missing we skip silently instead of crashing.
		let rows: Array<{ id: string; app_code: string; connection_string_env: string }>;
		try {
			const result = await sequelize.query(
				'SELECT id, app_code, connection_string_env FROM project_db_connections',
			);
			rows = result[0] as Array<{ id: string; app_code: string; connection_string_env: string }>;
		} catch (err) {
			void logger.logAsync(
				'warn',
				'MultiPoolService',
				`project_db_connections table unavailable — skipping multi-pool load: ${err}`,
				null,
			);
			return;
		}

		for (const row of rows) {
			const connStr = process.env[row.connection_string_env];
			if (!connStr) {
				void logger.logAsync('warn', 'MultiPoolService', `project_db_connections '${row.app_code}': env var '${row.connection_string_env}' not set, skipping`, null);
				continue;
			}
			try {
				const url = new URL(connStr);
				this.createPool(row.app_code, {
					username: url.username,
					password: url.password,
					database: url.pathname.replace(/^\//, ''),
					host: url.hostname,
					port: parseInt(url.port, 10) || 5432,
				});
				await this.healthCheck(row.app_code);
				void logger.logAsync('info', 'MultiPoolService', `Pool '${row.app_code}' loaded`, null);
			} catch (err) {
				void logger.logAsync('error', 'MultiPoolService', `Failed to load pool '${row.app_code}': ${err}`, null);
			}
		}
	}

	static listPools(): string[] {
		return [...this.pools.keys()];
	}
}
