/**
 * IP Analytics Service
 * Hanya pencatatan: IP yang bisa/tidak bisa akses database (connection_attempts, isp_statistics).
 * Bukan untuk sistem block maupun whitelist.
 */

const db = require('../../apps/sapps/helpers/db');
const { Op } = require('sequelize');

/**
 * Pastikan model IP Analytics (hanya pencatatan) sudah ter-load.
 * Hanya connection_attempts dan isp_statistics; bukan untuk block/whitelist.
 */
function ensureIPAnalyticsReady() {
	if (!db.ConnectionAttempt || !db.ISPStatistics) {
		const err = new Error('IP Analytics tables not available. Run: migrations/phpmyadmin-migrations.sql');
		err.code = 'TABLES_MISSING';
		throw err;
	}
	
	// Cek apakah connection manager sudah ditutup
	if (db.dbConnectionManager && db.dbConnectionManager.isConnectionManagerClosed()) {
		const err = new Error('Database connection manager is closed. System may be shutting down.');
		err.code = 'CONNECTION_CLOSED';
		throw err;
	}
}

class IPAnalyticsService {
	/**
	 * Get connection attempts with filtering
	 * @param {Object} options - Filter options
	 * @returns {Promise<Array>} Connection attempts
	 */
	static async getConnectionAttempts(options = {}) {
		ensureIPAnalyticsReady();
		try {
			const {
				isp = null,
				status = null,
				startDate = null,
				endDate = null,
				limit = 100,
				offset = 0,
				ip = null,
			} = options;

			const where = {};

			if (isp) {
				where.isp_detected = isp;
			}

			if (status) {
				where.connection_status = status;
			}

			if (ip) {
				where.ip_address = ip;
			}

			if (startDate || endDate) {
				where.attempt_timestamp = {};
				if (startDate) {
					where.attempt_timestamp[Op.gte] = new Date(startDate);
				}
				if (endDate) {
					where.attempt_timestamp[Op.lte] = new Date(endDate);
				}
			}

			const attempts = await db.ConnectionAttempt.findAll({
				where,
				limit: parseInt(limit),
				offset: parseInt(offset),
				order: [['attempt_timestamp', 'DESC']],
			});

			const total = await db.ConnectionAttempt.count({ where });

			return {
				data: attempts,
				total,
				limit: parseInt(limit),
				offset: parseInt(offset),
			};
		} catch (error) {
			console.error('[IPAnalyticsService] Error getting connection attempts:', error);
			throw error;
		}
	}

	/**
	 * Get ISP statistics
	 * @param {string} sortBy - Sort field
	 * @param {string} order - Sort order (ASC/DESC)
	 * @returns {Promise<Array>} ISP statistics
	 */
	static async getISPStatistics(sortBy = 'total_attempts', order = 'DESC') {
		ensureIPAnalyticsReady();
		try {
			const statistics = await db.ISPStatistics.findAll({
				order: [[sortBy, order]],
			});

			// Calculate additional metrics
			const enriched = statistics.map((stat) => {
				const data = stat.get({ plain: true });
				return {
					...data,
					failure_rate: (100 - parseFloat(data.success_rate || 0)).toFixed(2),
					avg_response_time_formatted: `${parseFloat(data.avg_response_time_ms || 0).toFixed(0)}ms`,
					is_problematic: parseFloat(data.success_rate || 0) < 80,
				};
			});

			return enriched;
		} catch (error) {
			console.error('[IPAnalyticsService] Error getting ISP statistics:', error);
			throw error;
		}
	}

	/**
	 * Get connection heatmap data (hourly breakdown)
	 * @param {Object} options - Filter options
	 * @returns {Promise<Object>} Heatmap data
	 */
	static async getConnectionHeatmap(options = {}) {
		ensureIPAnalyticsReady();
		try {
			const { days = 7, isp = null } = options;

			const startDate = new Date();
			startDate.setDate(startDate.getDate() - days);

			const where = {
				attempt_timestamp: {
					[Op.gte]: startDate,
				},
			};

			if (isp) {
				where.isp_detected = isp;
			}

			// Get all attempts in date range
			const attempts = await db.ConnectionAttempt.findAll({
				where,
				attributes: ['attempt_timestamp', 'connection_status'],
				raw: true,
			});

			// Group by hour
			const heatmap = {};
			attempts.forEach((attempt) => {
				const date = new Date(attempt.attempt_timestamp);
				const hour = date.getHours();
				const day = date.toISOString().split('T')[0];

				const key = `${day}_${hour}`;
				if (!heatmap[key]) {
					heatmap[key] = {
						date: day,
						hour: hour,
						total: 0,
						success: 0,
						failed: 0,
						timeout: 0,
					};
				}

				heatmap[key].total++;
				if (attempt.connection_status === 'success') {
					heatmap[key].success++;
				} else if (attempt.connection_status === 'failed') {
					heatmap[key].failed++;
				} else if (attempt.connection_status === 'timeout') {
					heatmap[key].timeout++;
				}
			});

			return Object.values(heatmap);
		} catch (error) {
			console.error('[IPAnalyticsService] Error getting connection heatmap:', error);
			throw error;
		}
	}

	/**
	 * Get top failing endpoints
	 * @param {number} limit - Number of results
	 * @returns {Promise<Array>} Top failing endpoints
	 */
	static async getTopFailingEndpoints(limit = 10) {
		ensureIPAnalyticsReady();
		try {
			const failures = await db.ConnectionAttempt.findAll({
				where: {
					connection_status: {
						[Op.in]: ['failed', 'timeout'],
					},
				},
				attributes: [
					'endpoint_attempted',
					[db.sequelize.fn('COUNT', '*'), 'failure_count'],
					[db.sequelize.fn('MAX', db.sequelize.col('error_type')), 'common_error'],
				],
				group: ['endpoint_attempted'],
				order: [[db.sequelize.fn('COUNT', '*'), 'DESC']],
				limit: parseInt(limit),
				raw: true,
			});

			return failures;
		} catch (error) {
			console.error('[IPAnalyticsService] Error getting top failing endpoints:', error);
			throw error;
		}
	}

	/**
	 * Get recent failures (for real-time monitoring)
	 * @param {number} minutes - Time window in minutes
	 * @param {number} limit - Max results
	 * @returns {Promise<Array>} Recent failures
	 */
	static async getRecentFailures(minutes = 15, limit = 50) {
		ensureIPAnalyticsReady();
		try {
			const since = new Date(Date.now() - minutes * 60 * 1000);

			const failures = await db.ConnectionAttempt.findAll({
				where: {
					connection_status: {
						[Op.in]: ['failed', 'timeout'],
					},
					attempt_timestamp: {
						[Op.gte]: since,
					},
				},
				limit: parseInt(limit),
				order: [['attempt_timestamp', 'DESC']],
			});

			return failures;
		} catch (error) {
			console.error('[IPAnalyticsService] Error getting recent failures:', error);
			throw error;
		}
	}

	/**
	 * Generate ISP comparison report
	 * @returns {Promise<Object>} Comparison data
	 */
	static async generateISPComparison() {
		try {
			const statistics = await this.getISPStatistics('success_rate', 'ASC');

			// Categorize ISPs
			const problematic = statistics.filter((s) => parseFloat(s.success_rate || 0) < 50);
			const moderate = statistics.filter(
				(s) => parseFloat(s.success_rate || 0) >= 50 && parseFloat(s.success_rate || 0) < 80
			);
			const good = statistics.filter((s) => parseFloat(s.success_rate || 0) >= 80);

			// Calculate overall metrics
			const totalAttempts = statistics.reduce((sum, s) => sum + s.total_attempts, 0);
			const totalSuccess = statistics.reduce((sum, s) => sum + s.successful_attempts, 0);
			const overallSuccessRate = totalAttempts > 0 ? ((totalSuccess / totalAttempts) * 100).toFixed(2) : 0;

			return {
				overall: {
					total_attempts: totalAttempts,
					success_rate: overallSuccessRate,
				},
				categories: {
					problematic: problematic.length,
					moderate: moderate.length,
					good: good.length,
				},
				isp_details: {
					problematic,
					moderate,
					good,
				},
				recommendations: this.generateRecommendations(statistics),
			};
		} catch (error) {
			console.error('[IPAnalyticsService] Error generating ISP comparison:', error);
			throw error;
		}
	}

	/**
	 * Generate recommendations based on ISP data
	 * @param {Array} statistics - ISP statistics
	 * @returns {Array} Recommendations
	 */
	static generateRecommendations(statistics) {
		const recommendations = [];

		// Check for ISPs with low success rate
		statistics.forEach((stat) => {
			const successRate = parseFloat(stat.success_rate || 0);
			const isp = stat.isp_name;

			if (successRate < 50 && stat.total_attempts > 10) {
				recommendations.push({
					severity: 'critical',
					isp: isp,
					issue: 'Very low success rate',
					message: `ISP "${isp}" has only ${successRate}% success rate. Recommend implementing VPN requirement for this ISP.`,
					action: 'REQUIRE_VPN',
				});
			} else if (successRate < 80 && stat.total_attempts > 10) {
				recommendations.push({
					severity: 'warning',
					isp: isp,
					issue: 'Moderate connection issues',
					message: `ISP "${isp}" has ${successRate}% success rate. Monitor closely and consider VPN recommendation.`,
					action: 'MONITOR',
				});
			}

			// Check for high timeout rates
			if (stat.timeout_count > stat.total_attempts * 0.3) {
				recommendations.push({
					severity: 'warning',
					isp: isp,
					issue: 'High timeout rate',
					message: `ISP "${isp}" has high timeout rate. May indicate network latency or firewall issues.`,
					action: 'CHECK_FIREWALL',
				});
			}

			// blocked_count = ECONNREFUSED (koneksi ditolak firewall/jaringan); hanya rekomendasi, bukan aksi block/whitelist
			if (stat.blocked_count > stat.total_attempts * 0.3) {
				recommendations.push({
					severity: 'critical',
					isp: isp,
					issue: 'Koneksi sering ditolak (ECONNREFUSED)',
					message: `Dari ISP "${isp}" banyak koneksi ditolak. Periksa firewall atau akses jaringan ke database (hanya catatan, bukan sistem block).`,
					action: 'CHECK_NETWORK',
				});
			}
		});

		return recommendations;
	}

	/**
	 * Export connection data to CSV format
	 * @param {Object} options - Filter options
	 * @returns {Promise<string>} CSV data
	 */
	static async exportToCSV(options = {}) {
		try {
			const { data } = await this.getConnectionAttempts({ ...options, limit: 10000 });

			// CSV header
			let csv =
				'Timestamp,IP Address,ISP,Country,City,Status,Error Type,Response Time (ms),Endpoint,Method\n';

			// CSV rows
			data.forEach((attempt) => {
				const row = [
					attempt.attempt_timestamp.toISOString(),
					attempt.ip_address,
					attempt.isp_detected || 'unknown',
					attempt.country || '',
					attempt.city || '',
					attempt.connection_status,
					attempt.error_type || '',
					attempt.response_time_ms || '',
					attempt.endpoint_attempted || '',
					attempt.request_method || '',
				]
					.map((field) => `"${String(field).replace(/"/g, '""')}"`)
					.join(',');

				csv += row + '\n';
			});

			return csv;
		} catch (error) {
			console.error('[IPAnalyticsService] Error exporting to CSV:', error);
			throw error;
		}
	}

	/**
	 * Get top N IPs that failed database connections
	 * @param {number} limit - Number of top IPs to return (default: 10)
	 * @returns {Promise<Array>} Top IPs with database connection failures
	 */
	static async getTopDatabaseFailureIPs(limit = 10) {
		ensureIPAnalyticsReady();
		try {
			const failures = await db.ConnectionAttempt.findAll({
				where: {
					connection_status: {
						[Op.in]: ['failed', 'timeout'],
					},
					endpoint_attempted: 'DATABASE_CONNECTION',
				},
				attributes: [
					'ip_address',
					[db.sequelize.fn('COUNT', db.sequelize.col('id')), 'failure_count'],
					[db.sequelize.fn('MAX', db.sequelize.col('attempt_timestamp')), 'last_failure'],
					[db.sequelize.fn('MIN', db.sequelize.col('attempt_timestamp')), 'first_failure'],
					[db.sequelize.fn('MAX', db.sequelize.col('error_type')), 'common_error_type'],
					[db.sequelize.fn('MAX', db.sequelize.col('error_message')), 'common_error_message'],
					[db.sequelize.fn('MAX', db.sequelize.col('isp_detected')), 'isp_detected'],
					[db.sequelize.fn('MAX', db.sequelize.col('country')), 'country'],
					[db.sequelize.fn('MAX', db.sequelize.col('city')), 'city'],
				],
				group: ['ip_address'],
				order: [[db.sequelize.fn('COUNT', db.sequelize.col('id')), 'DESC']],
				limit: parseInt(limit, 10),
				raw: true,
			});

			// Format the results
			const formatted = failures.map((failure) => ({
				ip_address: failure.ip_address,
				failure_count: parseInt(failure.failure_count, 10),
				last_failure: failure.last_failure ? new Date(failure.last_failure).toISOString() : null,
				last_failure_formatted: failure.last_failure ? new Date(failure.last_failure).toLocaleString() : null,
				first_failure: failure.first_failure ? new Date(failure.first_failure).toISOString() : null,
				first_failure_formatted: failure.first_failure ? new Date(failure.first_failure).toLocaleString() : null,
				common_error_type: failure.common_error_type || 'Unknown',
				common_error_message: failure.common_error_message || 'No error message',
				isp_detected: failure.isp_detected || 'Unknown',
				country: failure.country || 'Unknown',
				city: failure.city || 'Unknown',
			}));

			return formatted;
		} catch (error) {
			console.error('[IPAnalyticsService] Error getting top database failure IPs:', error);
			throw error;
		}
	}
}

module.exports = IPAnalyticsService;
