/**
 * DIAGNOSIS & AUTO-FIX SCRIPT
 * Run this to fix "Loading..." issues on dashboard
 */
const db = require("../apps/sapps/helpers/db");
const { Sequelize } = require("sequelize");

async function diagnoseAndFix() {
	console.log("\n🔧 STARTING IP TRACKING DIAGNOSIS...\n");

	try {
		console.log("1️⃣  Connecting to database...");
		// Force initialize database
		await db.retryInitialization();

		if (!db.isReady()) {
			console.log("   Waiting for database initialization...");
			await new Promise((r) => setTimeout(r, 5000));
		}

		console.log("   ✅ Database connected!");

		console.log("\n2️⃣  Checking Models...");
		const models = ["ConnectionAttempt", "ISPStatistics", "BlockedIP"];
		let modelsOk = true;

		models.forEach((model) => {
			if (db[model]) {
				console.log(`   ✅ Model '${model}' loaded correctly`);
			} else {
				console.error(`   ❌ Model '${model}' NOT FOUND in db object!`);
				modelsOk = false;
			}
		});

		if (!modelsOk) {
			console.log("\n   ⚠️  CRITICAL: Models not loaded. Please restart application!");
			process.exit(1);
		}

		console.log("\n3️⃣  Verifying Physical Tables...");
		const queryInterface = db.sequelize.getQueryInterface();

		// Define clean table structures
		const tables = {
			connection_attempts: `
                CREATE TABLE IF NOT EXISTS connection_attempts (
                    id int(11) NOT NULL AUTO_INCREMENT,
                    ip_address varchar(45) NOT NULL,
                    isp_name varchar(100) DEFAULT NULL,
                    isp_detected varchar(50) DEFAULT NULL,
                    connection_status enum('success','failed','timeout') NOT NULL,
                    error_type varchar(100) DEFAULT NULL,
                    error_message text,
                    attempt_timestamp datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    response_time_ms int(11) DEFAULT NULL,
                    endpoint_attempted varchar(255) DEFAULT NULL,
                    request_method varchar(10) DEFAULT NULL,
                    country varchar(50) DEFAULT NULL,
                    city varchar(100) DEFAULT NULL,
                    user_agent text,
                    error_code varchar(50) DEFAULT NULL,
                    asn varchar(50) DEFAULT NULL,
                    asn_org varchar(100) DEFAULT NULL,
                    PRIMARY KEY (id),
                    KEY idx_ip (ip_address),
                    KEY idx_time (attempt_timestamp)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            `,
			isp_statistics: `
                CREATE TABLE IF NOT EXISTS isp_statistics (
                    id int(11) NOT NULL AUTO_INCREMENT,
                    isp_name varchar(50) NOT NULL,
                    total_attempts int(11) DEFAULT '0',
                    successful_attempts int(11) DEFAULT '0',
                    failed_attempts int(11) DEFAULT '0',
                    timeout_count int(11) DEFAULT '0',
                    blocked_count int(11) DEFAULT '0',
                    avg_response_time_ms decimal(10,2) DEFAULT '0.00',
                    success_rate decimal(5,2) DEFAULT '0.00',
                    last_success datetime DEFAULT NULL,
                    last_failure datetime DEFAULT NULL,
                    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (id),
                    UNIQUE KEY idx_isp_name (isp_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            `,
			blocked_ips: `
                CREATE TABLE IF NOT EXISTS blocked_ips (
                    id int(11) NOT NULL AUTO_INCREMENT,
                    ip_address varchar(45) NOT NULL,
                    isp_name varchar(50) DEFAULT NULL,
                    block_reason varchar(255) DEFAULT NULL,
                    first_blocked datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    last_attempt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    attempt_count int(11) DEFAULT '1',
                    status enum('temporary','permanent','resolved','whitelisted') DEFAULT 'temporary',
                    notes text,
                    PRIMARY KEY (id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            `,
		};

		for (const [tableName, createQuery] of Object.entries(tables)) {
			try {
				// Try simple count to check existence
				await db.sequelize.query(`SELECT 1 FROM ${tableName} LIMIT 1`);
				console.log(`   ✅ Table '${tableName}' exists and is accessible`);
			} catch (err) {
				console.log(`   ⚠️  Table '${tableName}' missing or inaccessible. Creating...`);
				try {
					await db.sequelize.query(createQuery);
					console.log(`   ✨ Table '${tableName}' CREATED SUCCESSFULLY!`);
				} catch (createErr) {
					console.error(`   ❌ Failed to create table '${tableName}':`, createErr.message);
				}
			}
		}

		console.log("\n4️⃣  Inserting Test Data...");
		try {
			await db.ConnectionAttempt.create({
				ip_address: "127.0.0.1",
				isp_detected: "localhost",
				connection_status: "success",
				endpoint_attempted: "diagnosis-check",
				request_method: "CHECK",
				attempt_timestamp: new Date(),
			});
			console.log("   ✅ Test data inserted successfully");
		} catch (err) {
			console.error("   ❌ Failed to insert test data:", err.message);
		}

		console.log("\n5️⃣  Verifying API Data Flow...");
		const count = await db.ConnectionAttempt.count();
		console.log(`   📊 Total records in ConnectionAttempt: ${count}`);

		if (count > 0) {
			console.log("\n✅ DIAGNOSIS COMPLETE: System looks healthy!");
			console.log("👉 Please restart your backend server now: npm start");
		} else {
			console.log("\n⚠️  DIAGNOSIS WARNING: Tables exist but data insertion failed.");
		}
	} catch (error) {
		console.error("\n❌ FATAL ERROR during diagnosis:", error);
	} finally {
		process.exit(0);
	}
}

diagnoseAndFix();
