/**
 * DbRouter — explicit read/write pool routing with circuit breaker.
 *
 * Architecture:
 *   App → DbRouter → writePool (primary, :5000 via PgBouncer)
 *                   → readPool  (replicas, :5001 via PgBouncer)
 *
 * All DB calls go through this router. Write operations (INSERT/UPDATE/DELETE)
 * always use writePool. Read operations (SELECT) use readPool by default.
 * The circuit breaker tracks consecutive failures and marks pools degraded,
 * automatically retrying after 30s.
 *
 * Setup (Phase 3b): call DbRouter.initialize() once at startup with
 * HA environment variables (PG_WRITER_HOST, PG_READER_HOST, PG_WRITER_PORT, etc.).
 */

import { Sequelize } from 'sequelize';
import { LoggingService } from '#services/file-system/logService';
import Logger from '#utils/logger';

const logger = new LoggingService();

export type PoolName = 'write' | 'read';

interface PoolState {
	pool: Sequelize;
	degraded: boolean;
	consecutiveFailures: number;
	lastFailure: number;
}

const DEGRADED_AFTER_FAILURES = 3;
const RETRY_AFTER_MS = 30_000; // 30s
const POOL_CONFIG = {
	max: 50,
	min: 5,
	acquire: 30_000,
	idle: 10_000,
	evict: 60_000,
};

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

function makeSequelize(
	name: PoolName,
	host: string,
	port: number,
	username: string,
	password: string,
	database: string,
): Sequelize {
	const label = `[${name.toUpperCase()}]`;
	return new Sequelize({
		username,
		password,
		database,
		host,
		port,
		dialect: 'postgres',
		pool: POOL_CONFIG,
		benchmark: true,
		logging(sql, timing) {
			const ms = typeof timing === 'number' ? timing : null;
			if (ms !== null && ms >= 500) {
				void logger.logAsync('slow_query', 'DbRouter', `${label} [${ms}ms] ${sql}`, null);
			} else {
				void logger.logDBAsync(`${label} ${sql}`);
			}
		},
	});
}

export class DbRouter {
	private static writeState: PoolState | null = null;
	private static readState: PoolState | null = null;
	private static initialized = false;

	/**
	 * Initialize both write and read pools from environment variables.
	 * Call once at server startup (before any DB operation).
	 *
	 * Write pool env vars:
	 *   PG_WRITER_HOST, PG_WRITER_PORT, PG_WRITER_USER, PG_WRITER_PASSWORD, PG_WRITER_DATABASE
	 *   Or a single PG_WRITER_URL (takes precedence)
	 *
	 * Read pool env vars (same pattern):
	 *   PG_READER_HOST, PG_READER_PORT, PG_READER_USER, PG_READER_PASSWORD, PG_READER_DATABASE
	 *   Or PG_READER_URL
	 *
	 * Dev fallback: PG_WRITER_URL defaults to local Postgres.
	 */
	static initialize(): void {
		if (this.initialized) return;

		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 writeConn = parseConnectionString(writeUrl);
		const readConn = parseConnectionString(readUrl);

		this.writeState = {
			pool: makeSequelize(
				'write',
				writeConn.host,
				writeConn.port,
				writeConn.username,
				writeConn.password,
				writeConn.database,
			),
			degraded: false,
			consecutiveFailures: 0,
			lastFailure: 0,
		};

		this.readState = {
			pool: makeSequelize(
				'read',
				readConn.host,
				readConn.port,
				readConn.username,
				readConn.password,
				readConn.database,
			),
			degraded: false,
			consecutiveFailures: 0,
			lastFailure: 0,
		};

		this.initialized = true;
		void logger.logAsync('info', 'DbRouter', `Initialized — write=${writeConn.host}:${writeConn.port}, read=${readConn.host}:${readConn.port}`, null);
	}

	/**
	 * Get the active pool, checking circuit breaker state.
	 * If a pool is degraded and the retry window has passed, it is restored.
	 */
	private static getPool(name: PoolName): Sequelize {
		const state = name === 'write' ? this.writeState : this.readState;
		if (!state) {
			throw new Error(`[DbRouter] Pool '${name}' not initialized. Call initialize() first.`);
		}

		// Circuit breaker: check if we should retry a degraded pool
		if (state.degraded) {
			const now = Date.now();
			if (now - state.lastFailure >= RETRY_AFTER_MS) {
				state.degraded = false;
				state.consecutiveFailures = 0;
				void logger.logAsync('info', 'DbRouter', `Pool '${name}' restored after circuit-breaker retry window`, null);
			} else {
				void logger.logAsync(
					'warn',
					'DbRouter',
					`Pool '${name}' is degraded, retry in ${Math.round((RETRY_AFTER_MS - (now - state.lastFailure)) / 1000)}s`,
					null,
				);
			}
		}

		return state.pool;
	}

	private static getState(name: PoolName): PoolState {
		const state = name === 'write' ? this.writeState : this.readState;
		if (!state) throw new Error(`[DbRouter] Pool '${name}' not initialized`);
		return state;
	}

	/**
	 * Record a successful query on a pool.
	 */
	private static recordSuccess(name: PoolName): void {
		const state = this.getState(name);
		state.consecutiveFailures = 0;
	}

	/**
	 * Record a failed query on a pool. If failures exceed the threshold,
	 * the pool is marked degraded (circuit breaker trips).
	 */
	private static recordFailure(name: PoolName): void {
		const state = this.getState(name);
		state.consecutiveFailures++;
		state.lastFailure = Date.now();

		if (state.consecutiveFailures >= DEGRADED_AFTER_FAILURES && !state.degraded) {
			state.degraded = true;
			void logger.logAsync(
				'error',
				'DbRouter',
				`Pool '${name}' DEGRADED after ${state.consecutiveFailures} consecutive failures. Retrying in ${RETRY_AFTER_MS / 1000}s.`,
				null,
			);
		}
	}

	/**
	 * Execute a write query (INSERT/UPDATE/DELETE/ALTER/etc.) through the write pool.
	 * Automatically records success/failure for circuit breaker tracking.
	 */
	static async write<T>(fn: (pool: Sequelize) => Promise<T>): Promise<T> {
		const pool = this.getPool('write');
		try {
			const result = await fn(pool);
			this.recordSuccess('write');
			return result;
		} catch (err) {
			this.recordFailure('write');
			throw err;
		}
	}

	/**
	 * Execute a read query (SELECT) through the read pool.
	 * Falls back to write pool if read pool is degraded.
	 */
	static async read<T>(fn: (pool: Sequelize) => Promise<T>): Promise<T> {
		// Try read pool first
		let pool = this.getPool('read');
		let poolName: PoolName = 'read';

		// If read pool is degraded, fall back to write pool
		if (this.getState('read').degraded) {
			pool = this.getPool('write');
			poolName = 'write';
			void logger.logAsync('warn', 'DbRouter', 'Read pool degraded — falling back to write pool', null);
		}

		try {
			const result = await fn(pool);
			this.recordSuccess(poolName);
			return result;
		} catch (err) {
			this.recordFailure(poolName);
			throw err;
		}
	}

	/**
	 * Health check both pools.
	 */
	static async healthCheck(): Promise<{ write: boolean; read: boolean }> {
		const check = async (name: PoolName): Promise<boolean> => {
			const state = this.getState(name);
			try {
				await state.pool.authenticate();
				this.recordSuccess(name);
				return true;
			} catch {
				this.recordFailure(name);
				return false;
			}
		};

		const [write, read] = await Promise.all([check('write'), check('read')]);
		return { write, read };
	}

	/**
	 * Get pool status including circuit breaker state.
	 */
	static getStatus(): { write: PoolStatus; read: PoolStatus } {
		const status = (name: PoolName): PoolStatus => {
			const state = this.getState(name);
			return {
				degraded: state.degraded,
				consecutiveFailures: state.consecutiveFailures,
				lastFailure: state.lastFailure,
			};
		};

		return {
			write: status('write'),
			read: status('read'),
		};
	}

	/**
	 * Close both pools. Call on graceful shutdown.
	 */
	static async close(): Promise<void> {
		await Promise.all([
			this.writeState?.pool.close(),
			this.readState?.pool.close(),
		]);
		this.writeState = null;
		this.readState = null;
		this.initialized = false;
		void logger.logAsync('info', 'DbRouter', 'Both pools closed', null);
	}
}

interface PoolStatus {
	degraded: boolean;
	consecutiveFailures: number;
	lastFailure: number;
}
