/**
 * 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.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.appendFileSync(this.connectionLogFile, JSON.stringify(logEntry) + "\n", "utf8");
		} 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) {
		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 {
				await sequelize.authenticate();
			} catch (authErr) {
				// 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;
			return sequelize;
		} catch (error) {
			this.lastConnectionError = error;
			const isRetryable = this.isRetryableError(error);

			// IMPORTANT: Do NOT retry ER_HOST_IS_BLOCKED - it requires manual intervention
			// This error means the host is blocked by MySQL and needs to be unblocked manually
			if (error.code === "ER_HOST_IS_BLOCKED" || error.errno === 1129) {
				this.isConnecting = false;
				this.logConnection("Host is blocked - requires manual intervention", {
					error: error.message,
					code: error.code,
					errno: error.errno,
					action: "Run 'mariadb-admin flush-hosts' on MySQL server or wait for host to be unblocked",
				});
				console.error("========================================");
				console.error("HOST IS BLOCKED BY MYSQL/MARIADB");
				console.error("========================================");
				console.error("Error:", error.message);
				console.error("Solution: Run on MySQL server:");
				console.error("  mysql -u root -p");
				console.error("  FLUSH HOSTS;");
				console.error("Or:");
				console.error("  mariadb-admin flush-hosts");
				console.error("========================================");
				throw 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 });
			console.error("========================================");
			console.error("DATABASE CONNECTION FAILED");
			console.error("========================================");
			console.error("Error:", error.message);
			console.error("Code:", error.code);
			console.error("Attempts:", attempt + 1);
			console.error("Max retries:", this.options.maxRetries);
			console.error("========================================");
			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.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;
		}
	}

	// Flag untuk menandai apakah connection manager sudah ditutup
	isConnectionManagerClosed() {
		return this._isClosed === true;
	}

	// Method untuk menutup connection manager dengan aman
	async close() {
		if (this._isClosed) {
			this.logConnection("Connection manager already closed, skipping close");
			return;
		}
		this._isClosed = true;
		this.isConnecting = false;

		if (this.sequelize) {
			try {
				await this.sequelize.close();
				this.logConnection("Connection manager closed successfully");
			} catch (err) {
				this.logConnection("Error closing connection manager", { error: err.message });
			}
			this.sequelize = null;
		}
	}

	async ensureConnection() {
		// Proteksi: Jika connection manager sudah ditutup, coba reconnect
		if (this.isConnectionManagerClosed()) {
			this.logConnection("Connection manager was closed, attempting to reconnect");
			this._isClosed = false;
			return this.connectWithRetry(0);
		}

		if (this.isConnecting) {
			return new Promise((resolve, reject) => {
				let checkCount = 0;
				const maxChecks = 300;
				const iv = setInterval(() => {
					checkCount++;
					// Periksa juga apakah connection manager sudah ditutup
					if (this.isConnectionManagerClosed()) {
						clearInterval(iv);
						this._isClosed = false;
						return this.connectWithRetry(0).then(resolve).catch(reject);
					}
					if (checkCount >= maxChecks) {
						clearInterval(iv);
						// Jika timeout, coba reconnect daripada reject langsung
						if (!this.isConnectionManagerClosed() && this.sequelize) {
							resolve(this.sequelize);
						} else {
							this._isClosed = false;
							return this.connectWithRetry(0).then(resolve).catch(reject);
						}
						return;
					}
					if (!this.isConnecting && this.sequelize && !this.isConnectionManagerClosed()) {
						clearInterval(iv);
						resolve(this.sequelize);
					}
				}, 100);
			});
		}

		// Proteksi tambahan: jika sequelize null atau sudah ditutup
		if (!this.sequelize || this.isConnectionManagerClosed()) {
			this._isClosed = false;
			return this.connectWithRetry(0);
		}

		try {
			await Promise.race([this.sequelize.authenticate(), new Promise((_, reject) => setTimeout(() => reject(new Error("Authentication timeout")), 5000))]);
			return this.sequelize;
		} catch (err) {
			// Jika error, periksa apakah connection manager sudah ditutup
			if (this.isConnectionManagerClosed()) {
				this._isClosed = false;
				return this.connectWithRetry(0);
			}
			// Untuk error lainnya, coba reconnect
			if (!this.isConnecting) {
				return this.reconnect();
			}
			return this.ensureConnection();
		}
	}

	getConnection() {
		return this.sequelize;
	}

	getConnectionStats() {
		return {
			isConnected: !!this.sequelize,
			connectionAttempts: this.connectionAttempts,
			lastConnectionError: this.lastConnectionError ? { message: this.lastConnectionError.message, code: this.lastConnectionError.code } : null,
			pool: this.sequelize?.connectionManager?.pool
				? {
						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,
		};
	}
}

module.exports = DatabaseConnectionManager;
