/**
 * Database Connection Manager
 * Robust retry mechanism untuk database connection dengan data integrity protection
 */

const { Sequelize } = require("sequelize");
const mysql = require("mysql2/promise");
const fs = require("fs");
const path = require("path");

class DatabaseConnectionManager {
	constructor(config, options = {}) {
		this.config = config;
		this.options = {
			maxRetries: options.maxRetries || 5,
			retryDelay: options.retryDelay || 1000,
			maxRetryDelay: options.maxRetryDelay || 30000,
			connectionTimeout: options.connectionTimeout || 10000,
			pool: {
				max: options.pool?.max || 50,
				min: options.pool?.min || 5,
				acquire: options.pool?.acquire || 30000,
				idle: options.pool?.idle || 10000,
			},
			...options,
		};
		// Do not create/modify DB in production unless explicitly enabled
		this.options.allowCreateDatabase = options.allowCreateDatabase !== undefined ? options.allowCreateDatabase : process.env.NODE_ENV !== "production";

		this.sequelize = null;
		this.isConnecting = false;
		this.connectionAttempts = 0;
		this.lastConnectionError = null;
		this.isClosed = false; // Flag to track if connection manager is closed
		this.logDir = path.join(__dirname, "../logs");
		this.connectionLogFile = path.join(this.logDir, "database-connections.log");

		this.env = {
			ssl: (process.env.DB_SSL || "").toLowerCase(),
			rejectUnauthorized: String(process.env.DB_SSL_REJECT_UNAUTHORIZED || "false").toLowerCase() === "true",
			timezone: process.env.DB_TIMEZONE || "+07:00",
			debug: String(process.env.DEBUG_DB || "false").toLowerCase() === "true",
		};

		this.ensureLogDirectory();
	}

	ensureLogDirectory() {
		if (!fs.existsSync(this.logDir)) {
			fs.mkdirSync(this.logDir, { recursive: true });
		}
	}

	logConnection(message, data = {}) {
		const logEntry = { timestamp: new Date().toISOString(), pid: process.pid, message, ...data };
		try {
			fs.appendFile(this.connectionLogFile, JSON.stringify(logEntry) + "\n", "utf8", (err) => {
				if (err) console.error("Error writing connection log:", err);
			});
		} catch (err) {
			console.error("Error writing connection log:", err);
		}
	}

	calculateRetryDelay(attempt) {
		const baseDelay = Math.min(this.options.retryDelay * Math.pow(2, attempt), this.options.maxRetryDelay);
		const jitter = Math.random() * baseDelay * 0.2;
		return Math.floor(baseDelay + jitter);
	}

	async createDatabaseIfNotExists() {
		const { host, port, user, password, database } = this.config;
		if (!this.options.allowCreateDatabase) {
			this.logConnection("Skipping database create/verify step", { environment: process.env.NODE_ENV || "development", database });
			return;
		}
		let connection = null;
		try {
			// Use timeout 0 (no timeout) if connectionTimeout is 0, otherwise use the configured timeout
			// This matches reference version behavior for cPanel compatibility
			const connectionConfig = { host, port, user, password };
			if (this.options.connectionTimeout !== 0) {
				connectionConfig.connectTimeout = this.options.connectionTimeout;
			}
			connection = await mysql.createConnection(connectionConfig);
			await connection.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
			this.logConnection("Database created/verified", { database });
		} catch (error) {
			this.logConnection("Failed to create/verify database", { error: error.message, code: error.code });
			throw error;
		} finally {
			if (connection) {
				try {
					await connection.end();
				} catch (_) {}
			}
		}
	}

	async connectWithRetry(attempt = 0) {
		// Track connection start time for latency calculation
		if (attempt === 0) {
			this.connectionStartTime = Date.now();
		}
		
		if (this.isConnecting) {
			return new Promise((resolve, reject) => {
				let checkCount = 0;
				const maxChecks = 300;
				const iv = setInterval(() => {
					checkCount++;
					if (checkCount >= maxChecks) {
						clearInterval(iv);
						this.isConnecting = false;
						reject(new Error("Connection timeout: exceeded maximum wait time"));
						return;
					}
					if (!this.isConnecting) {
						clearInterval(iv);
						if (this.sequelize && this.sequelize.authenticate) resolve(this.sequelize);
						else reject(new Error("Connection failed"));
					}
				}, 100);
				setTimeout(() => {
					clearInterval(iv);
					this.isConnecting = false;
					reject(new Error("Connection timeout"));
				}, 30000);
			});
		}

		this.isConnecting = true;
		this.connectionAttempts = attempt + 1;

		try {
			try {
				await this.createDatabaseIfNotExists();
			} catch (error) {
				if (attempt < this.options.maxRetries) {
					const delay = this.calculateRetryDelay(attempt);
					this.logConnection("Retrying database creation", { attempt: attempt + 1, maxRetries: this.options.maxRetries, delay, error: error.message });
					await new Promise((r) => setTimeout(r, delay));
					this.isConnecting = false;
					return this.connectWithRetry(attempt + 1);
				}
				throw error;
			}

			const { host, port, user, password, database } = this.config;

			// Use timeout 0 (no timeout) if connectionTimeout is 0, matching reference version for cPanel compatibility
			const dialectOptions = { multipleStatements: true };
			if (this.options.connectionTimeout !== 0) {
				dialectOptions.connectTimeout = this.options.connectionTimeout;
			} else {
				dialectOptions.connectTimeout = 0; // Explicitly set to 0 for no timeout (cPanel compatible)
			}
			if (this.env.ssl === "true" || this.env.ssl === "require") {
				dialectOptions.ssl = { require: true, rejectUnauthorized: this.env.rejectUnauthorized };
			}

			let sequelize = new Sequelize(database, user, password, {
				host,
				dialect: "mysql",
				port,
				dialectOptions,
				timezone: this.env.timezone,
				pool: {
					max: this.options.pool.max,
					min: this.options.pool.min,
					acquire: this.options.pool.acquire, // 0 means no timeout (cPanel compatible)
					idle: this.options.pool.idle,
					handleDisconnects: true,
				},
				logging: this.env.debug ? console.log : this.options.logging !== undefined ? this.options.logging : false,
				benchmark: false,
				retry: {
					max: 3,
					match: [
						/ETIMEDOUT/,
						/EHOSTUNREACH/,
						/ECONNRESET/,
						/ECONNREFUSED/,
						/ENOTFOUND/,
						/SequelizeConnectionError/,
						/SequelizeConnectionRefusedError/,
						/SequelizeHostNotFoundError/,
						/SequelizeHostNotReachableError/,
						/SequelizeInvalidConnectionError/,
						/SequelizeConnectionTimedOutError/,
					],
				},
			});

			try {
				const authStartTime = Date.now();
				await sequelize.authenticate();
				const authLatency = Date.now() - authStartTime;
				
				// Track successful connection with latency
				if (global.networkConnectivityMonitor) {
					global.networkConnectivityMonitor.trackConnectionAttempt(
						this.config.host, // Use host as identifier
						true,
						authLatency
					);
				}
			} catch (authErr) {
			// Track failed connection
			if (global.networkConnectivityMonitor) {
				const connectionLatency = Date.now() - (this.connectionStartTime || Date.now());
				global.networkConnectivityMonitor.trackConnectionAttempt(
					this.config.host, // Use host as identifier
					false,
					connectionLatency,
					authErr.message
				);
			}
				// Auto SSL fallback: if server requires SSL, retry once with SSL
				const msg = String(authErr.message || "").toLowerCase();
				const needsSSL = msg.includes("ssl") || msg.includes("tls");
				const alreadySSL = !!dialectOptions.ssl;
				if (needsSSL && !alreadySSL) {
					this.logConnection("Auth failed - retrying with SSL requirement");
					dialectOptions.ssl = { require: true, rejectUnauthorized: this.env.rejectUnauthorized };
					// Ensure connectTimeout is set correctly for SSL retry
					if (this.options.connectionTimeout !== 0 && !dialectOptions.connectTimeout) {
						dialectOptions.connectTimeout = this.options.connectionTimeout;
					} else if (this.options.connectionTimeout === 0) {
						dialectOptions.connectTimeout = 0;
					}
					sequelize = new Sequelize(database, user, password, {
						host,
						dialect: "mysql",
						port,
						dialectOptions,
						timezone: this.env.timezone,
						pool: {
							max: this.options.pool.max,
							min: this.options.pool.min,
							acquire: this.options.pool.acquire, // 0 means no timeout (cPanel compatible)
							idle: this.options.pool.idle,
							handleDisconnects: true,
						},
						logging: this.env.debug ? console.log : this.options.logging !== undefined ? this.options.logging : false,
						benchmark: false,
						retry: {
							max: 3,
							match: [
								/ETIMEDOUT/,
								/EHOSTUNREACH/,
								/ECONNRESET/,
								/ECONNREFUSED/,
								/ENOTFOUND/,
								/SequelizeConnectionError/,
								/SequelizeConnectionRefusedError/,
								/SequelizeHostNotFoundError/,
								/SequelizeHostNotReachableError/,
								/SequelizeInvalidConnectionError/,
								/SequelizeConnectionTimedOutError/,
							],
						},
					});
					await sequelize.authenticate();
				} else {
					throw authErr;
				}
			}

			this.sequelize = sequelize;
			this.lastConnectionError = null;
			this.connectionAttempts = 0;

			this.logConnection("Database connected successfully", { host, port, database, attempt: attempt + 1 });
			this.setupConnectionHandlers(sequelize);
			this.isConnecting = false;
			
			// Track successful connection (for monitoring)
			if (global.networkConnectivityMonitor) {
				const connectionLatency = Date.now() - (this.connectionStartTime || Date.now());
				global.networkConnectivityMonitor.trackConnectionAttempt(
					host, // Use host as identifier (or get client IP if available)
					true,
					connectionLatency
				);
			}
			
			return sequelize;
		} catch (error) {
			this.lastConnectionError = error;
			const isRetryable = this.isRetryableError(error);
			if (isRetryable && attempt < this.options.maxRetries) {
				// Intelligent backoff: If network is unreachable, wait longer
				let delay = this.calculateRetryDelay(attempt);
				if (error.code === 'ENETUNREACH' || error.code === 'EHOSTUNREACH') {
					delay = Math.max(delay, 5000); // Minimum 5s wait for network issues
					console.log(`[DatabaseConnectionManager] Network unreachable, waiting ${delay}ms...`);
				}

				this.logConnection("Retrying database connection", {
					attempt: attempt + 1,
					maxRetries: this.options.maxRetries,
					delay,
					error: error.message,
					code: error.code,
				});
				if (this.sequelize) {
					try {
						await this.sequelize.close();
					} catch (_) {}
					this.sequelize = null;
				}
				await new Promise((r) => setTimeout(r, delay));
				this.isConnecting = false;
				return this.connectWithRetry(attempt + 1);
			}

			this.isConnecting = false;
			this.logConnection("Database connection failed permanently", { attempt: attempt + 1, error: error.message, code: error.code, stack: error.stack });
			
			// Enhanced error information for network issues
			const NetworkDiagnostics = require('./networkDiagnostics');
			const networkErrorInfo = NetworkDiagnostics.getNetworkErrorInfo(error);
			
			console.error("========================================");
			console.error("DATABASE CONNECTION FAILED");
			console.error("========================================");
			console.error("Error:", error.message);
			console.error("Code:", error.code);
			console.error("Error Type:", networkErrorInfo.errorType);
			console.error("Attempts:", attempt + 1);
			console.error("Max retries:", this.options.maxRetries);
			console.error("========================================");
			console.error("DIAGNOSTIC INFORMATION:");
			console.error(`  - Host: ${this.config.host}`);
			console.error(`  - Port: ${this.config.port}`);
			console.error(`  - Error Type: ${networkErrorInfo.errorType}`);
			console.error(`  - User Message: ${networkErrorInfo.userMessage}`);
			console.error(`  - Suggestion: ${networkErrorInfo.suggestion}`);
			console.error("========================================");
			console.error("TROUBLESHOOTING STEPS:");
			
			if (networkErrorInfo.errorType === 'FIREWALL_BLOCKED' || networkErrorInfo.errorType === 'TIMEOUT') {
				console.error("1. Gunakan VPN kantor untuk mengakses database server");
				console.error("2. Periksa apakah IP Anda dalam whitelist database server");
				console.error("3. Periksa firewall rules di database server");
				console.error("4. Pastikan port 3307 accessible dari network Anda");
			} else if (networkErrorInfo.errorType === 'NETWORK_UNREACHABLE') {
				console.error("1. Periksa network routing configuration");
				console.error("2. Coba gunakan VPN kantor");
				console.error("3. Periksa network connectivity ke database server");
			} else {
				console.error("1. Periksa koneksi network");
				console.error("2. Periksa konfigurasi database (host, port)");
				console.error("3. Coba gunakan VPN kantor jika tersedia");
			}
			
			console.error("========================================");
			
			// Enhance error with diagnostic information
			error.networkDiagnostic = networkErrorInfo;
			error.userMessage = networkErrorInfo.userMessage;
			error.suggestion = networkErrorInfo.suggestion;
			
			// Track failed connection (for monitoring)
			if (global.networkConnectivityMonitor) {
				const connectionLatency = Date.now() - (this.connectionStartTime || Date.now());
				global.networkConnectivityMonitor.trackConnectionAttempt(
					this.config.host, // Use host as identifier
					false,
					connectionLatency,
					error.message
				);
			}
			
			throw error;
		}
	}

	isRetryableError(error) {
		const retryableCodes = ["ECONNREFUSED", "ETIMEDOUT", "ENOTFOUND", "ECONNRESET", "EHOSTUNREACH", "ER_LOCK_WAIT_TIMEOUT", "ER_LOCK_DEADLOCK"];
		const retryableMessages = [
			"Connection lost",
			"Connection terminated",
			"Unable to connect",
			"SequelizeConnectionError",
			"SequelizeConnectionRefusedError",
			"SequelizeHostNotFoundError",
			"SequelizeHostNotReachableError",
			"SequelizeInvalidConnectionError",
			"SequelizeConnectionTimedOutError",
		];
		if (error.code && retryableCodes.includes(error.code)) return true;
		if (error.message && retryableMessages.some((msg) => error.message.includes(msg))) return true;
		if (error.name && retryableMessages.some((msg) => error.name.includes(msg))) return true;
		return false;
	}

	setupConnectionHandlers(sequelize) {
		try {
			const poolInfo = sequelize.connectionManager?.pool
				? { size: sequelize.connectionManager.pool.size || "unknown", available: sequelize.connectionManager.pool.available || "unknown" }
				: { status: "pool not initialized yet" };
			this.logConnection("Connection handlers setup completed", poolInfo);
		} catch (_) {
			this.logConnection("Connection handlers setup completed", { note: "Pool info not available" });
		}
	}

	async reconnect() {
		if (this.isClosed) {
			const error = new Error("Cannot reconnect: Connection manager is closed");
			this.logConnection("Reconnection blocked - manager is closed");
			throw error;
		}
		if (this.isConnecting) return;
		console.log("Reconnecting to database...");
		this.logConnection("Initiating reconnection");
		try {
			if (this.sequelize) {
				try {
					await this.sequelize.close();
				} catch (_) {}
				this.sequelize = null;
			}
			await this.connectWithRetry(0);
			console.log("Database reconnected successfully");
			this.logConnection("Reconnection successful");
		} catch (error) {
			console.error("Reconnection failed:", error);
			this.logConnection("Reconnection failed", { error: error.message, code: error.code });
			throw error;
		}
	}

	async ensureConnection() {
		if (this.isClosed) {
			const error = new Error("Cannot ensure connection: Connection manager is closed");
			this.logConnection("ensureConnection blocked - manager is closed");
			throw error;
		}
		if (this.isConnecting) {
			return new Promise((resolve, reject) => {
				let checkCount = 0;
				const maxChecks = 300;
				const iv = setInterval(() => {
					checkCount++;
					if (checkCount >= maxChecks) {
						clearInterval(iv);
						reject(new Error("Connection timeout: ensureConnection exceeded maximum wait time"));
						return;
					}
					if (this.isClosed) {
						clearInterval(iv);
						reject(new Error("Connection manager was closed during ensureConnection"));
						return;
					}
					if (!this.isConnecting && this.sequelize) {
						clearInterval(iv);
						resolve(this.sequelize);
					}
				}, 100);
			});
		}
		if (this.sequelize) {
			try {
				await Promise.race([this.sequelize.authenticate(), new Promise((_, reject) => setTimeout(() => reject(new Error("Authentication timeout")), 5000))]);
				return this.sequelize;
			} catch (_) {
				if (this.isClosed) {
					throw new Error("Connection manager is closed");
				}
				if (!this.isConnecting) return this.reconnect();
				return this.ensureConnection();
			}
		}
		return this.connectWithRetry(0);
	}

	getConnection() {
		if (this.isClosed) {
			const error = new Error("ConnectionManager.getConnection was called after the connection manager was closed!");
			this.logConnection("getConnection blocked - manager is closed");
			throw error;
		}
		return this.sequelize;
	}

	getConnectionStats() {
		return {
			isConnected: !!this.sequelize && !this.isClosed,
			isClosed: this.isClosed,
			connectionAttempts: this.connectionAttempts,
			lastConnectionError: this.lastConnectionError ? { message: this.lastConnectionError.message, code: this.lastConnectionError.code } : null,
			pool: this.sequelize?.connectionManager?.pool && !this.isClosed
				? {
						size: this.sequelize.connectionManager.pool.size,
						available: this.sequelize.connectionManager.pool.available,
						using: this.sequelize.connectionManager.pool.using,
						waiting: this.sequelize.connectionManager.pool.waiting,
				  }
				: null,
		};
	}

	/**
	 * Close connection manager gracefully
	 */
	async close() {
		if (this.isClosed) {
			console.log("[DatabaseConnectionManager] Connection manager already closed");
			return;
		}

		console.log("[DatabaseConnectionManager] Closing connection manager...");
		this.isClosed = true;
		this.logConnection("Connection manager closing");

		// Stop any ongoing connection attempts
		this.isConnecting = false;

		// Close sequelize connection if exists
		if (this.sequelize) {
			try {
				await this.sequelize.close();
				console.log("[DatabaseConnectionManager] Sequelize connection closed");
				this.logConnection("Sequelize connection closed");
			} catch (error) {
				console.error("[DatabaseConnectionManager] Error closing sequelize:", error.message);
				this.logConnection("Error closing sequelize", { error: error.message });
			}
			this.sequelize = null;
		}

		console.log("[DatabaseConnectionManager] Connection manager closed");
		this.logConnection("Connection manager closed");
	}

	/**
	 * Check if connection manager is closed
	 */
	isConnectionManagerClosed() {
		return this.isClosed;
	}
}

module.exports = DatabaseConnectionManager;
