const Sequelize = require('sequelize');

/**
 * Migration: Create Connection Tracking Tables
 * Purpose: Track IP addresses that fail to connect to database/backend
 * 
 * This migration creates three tables:
 * 1. connection_attempts - Detailed log of every connection attempt
 * 2. isp_statistics - Aggregated statistics per ISP
 * 3. blocked_ips - IPs that frequently fail or are blocked
 */

module.exports = {
	up: async (queryInterface) => {
		// 1. Connection Attempts Table - Detailed Logging
		await queryInterface.createTable('connection_attempts', {
			id: {
				type: Sequelize.INTEGER,
				primaryKey: true,
				autoIncrement: true,
			},
			ip_address: {
				type: Sequelize.STRING(45),
				allowNull: false,
				comment: 'Client IP address (supports IPv4 and IPv6)',
			},
			user_agent: {
				type: Sequelize.TEXT,
				allowNull: true,
				comment: 'Browser/client user agent string',
			},
			isp_name: {
				type: Sequelize.STRING(100),
				allowNull: true,
				comment: 'Full ISP name from geolocation',
			},
			isp_detected: {
				type: Sequelize.STRING(50),
				allowNull: true,
				comment: 'Standardized ISP identifier (telkomsel, indihome, etc)',
			},
			country: {
				type: Sequelize.STRING(50),
				allowNull: true,
			},
			city: {
				type: Sequelize.STRING(100),
				allowNull: true,
			},
			connection_status: {
				type: Sequelize.ENUM('success', 'failed', 'timeout'),
				allowNull: false,
				comment: 'Overall connection status',
			},
			error_type: {
				type: Sequelize.STRING(100),
				allowNull: true,
				comment: 'Type of error (ECONNREFUSED, ETIMEDOUT, etc)',
			},
			error_message: {
				type: Sequelize.TEXT,
				allowNull: true,
			},
			error_code: {
				type: Sequelize.STRING(50),
				allowNull: true,
			},
			attempt_timestamp: {
				type: Sequelize.DATE,
				allowNull: false,
				defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
			},
			response_time_ms: {
				type: Sequelize.INTEGER,
				allowNull: true,
				comment: 'Response time in milliseconds',
			},
			endpoint_attempted: {
				type: Sequelize.STRING(255),
				allowNull: true,
				comment: 'API endpoint or route attempted',
			},
			request_method: {
				type: Sequelize.STRING(10),
				allowNull: true,
				comment: 'HTTP method (GET, POST, etc)',
			},
			asn: {
				type: Sequelize.STRING(50),
				allowNull: true,
				comment: 'Autonomous System Number',
			},
			asn_org: {
				type: Sequelize.STRING(100),
				allowNull: true,
				comment: 'ASN Organization',
			},
		});

		// Add indexes for performance
		await queryInterface.addIndex('connection_attempts', ['ip_address'], {
			name: 'idx_connection_attempts_ip',
		});
		await queryInterface.addIndex('connection_attempts', ['connection_status'], {
			name: 'idx_connection_attempts_status',
		});
		await queryInterface.addIndex('connection_attempts', ['attempt_timestamp'], {
			name: 'idx_connection_attempts_timestamp',
		});
		await queryInterface.addIndex('connection_attempts', ['isp_detected'], {
			name: 'idx_connection_attempts_isp',
		});
		await queryInterface.addIndex('connection_attempts', ['endpoint_attempted'], {
			name: 'idx_connection_attempts_endpoint',
		});

		// 2. ISP Statistics Table - Aggregated Stats
		await queryInterface.createTable('isp_statistics', {
			id: {
				type: Sequelize.INTEGER,
				primaryKey: true,
				autoIncrement: true,
			},
			isp_name: {
				type: Sequelize.STRING(50),
				allowNull: false,
				unique: true,
				comment: 'Standardized ISP name',
			},
			total_attempts: {
				type: Sequelize.INTEGER,
				defaultValue: 0,
				comment: 'Total connection attempts',
			},
			successful_attempts: {
				type: Sequelize.INTEGER,
				defaultValue: 0,
			},
			failed_attempts: {
				type: Sequelize.INTEGER,
				defaultValue: 0,
			},
			timeout_count: {
				type: Sequelize.INTEGER,
				defaultValue: 0,
			},
			blocked_count: {
				type: Sequelize.INTEGER,
				defaultValue: 0,
				comment: 'Connection refused count',
			},
			avg_response_time_ms: {
				type: Sequelize.DECIMAL(10, 2),
				defaultValue: 0,
				comment: 'Average response time',
			},
			success_rate: {
				type: Sequelize.DECIMAL(5, 2),
				defaultValue: 0,
				comment: 'Success rate percentage',
			},
			last_success: {
				type: Sequelize.DATE,
				allowNull: true,
			},
			last_failure: {
				type: Sequelize.DATE,
				allowNull: true,
			},
			created_at: {
				type: Sequelize.DATE,
				allowNull: false,
				defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
			},
			updated_at: {
				type: Sequelize.DATE,
				allowNull: false,
				defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
			},
		});

		await queryInterface.addIndex('isp_statistics', ['isp_name'], {
			name: 'idx_isp_statistics_name',
			unique: true,
		});

		// 3. Blocked IPs Table - Problematic IPs
		await queryInterface.createTable('blocked_ips', {
			id: {
				type: Sequelize.INTEGER,
				primaryKey: true,
				autoIncrement: true,
			},
			ip_address: {
				type: Sequelize.STRING(45),
				allowNull: false,
				comment: 'Blocked IP address',
			},
			isp_name: {
				type: Sequelize.STRING(50),
				allowNull: true,
			},
			block_reason: {
				type: Sequelize.STRING(255),
				allowNull: true,
				comment: 'Reason for blocking',
			},
			first_blocked: {
				type: Sequelize.DATE,
				allowNull: false,
				defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
			},
			last_attempt: {
				type: Sequelize.DATE,
				allowNull: false,
				defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
			},
			attempt_count: {
				type: Sequelize.INTEGER,
				defaultValue: 1,
				comment: 'Number of failed attempts',
			},
			status: {
				type: Sequelize.ENUM('temporary', 'permanent', 'resolved', 'whitelisted'),
				defaultValue: 'temporary',
			},
			notes: {
				type: Sequelize.TEXT,
				allowNull: true,
			},
		});

		await queryInterface.addIndex('blocked_ips', ['ip_address'], {
			name: 'idx_blocked_ips_ip',
		});
		await queryInterface.addIndex('blocked_ips', ['status'], {
			name: 'idx_blocked_ips_status',
		});
	},

	down: async (queryInterface) => {
		await queryInterface.dropTable('connection_attempts');
		await queryInterface.dropTable('isp_statistics');
		await queryInterface.dropTable('blocked_ips');
	},
};
