/**
 * 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();
