require('dotenv').config();

const db = require('../config/database');
const bcrypt = require('bcryptjs');

const connection = db.promise();

const tablesToReset = [
    'dispatch',
    'notifications',
    'ambulance_requests',
    'token_blacklist',
    'driver',
    'ambulance',
    'clients',
    'users',
    'masm_scheme',
    'chat'
];

async function resetTables() {
    console.log('🔄 Resetting tables...');
    await connection.query('SET FOREIGN_KEY_CHECKS = 0');
    for (const table of tablesToReset) {
        try {
            await connection.query(`TRUNCATE TABLE ${table}`);
            console.log(`  • Cleared ${table}`);
        } catch (err) {
            console.warn(`  ! Skipped ${table}: ${err.message}`);
        }
    }
    await connection.query('SET FOREIGN_KEY_CHECKS = 1');
}

async function insertMasmScheme() {
    console.log('🩺 Seeding MASM scheme...');
    const [result] = await connection.query(
        'INSERT INTO masm_scheme (name, `desc`) VALUES (?, ?)',
        ['Standard Health Plan', 'Default plan used for seed data']
    );
    return result.insertId;
}

async function insertUser(user) {
    // Hash password with bcrypt
    const hashedPassword = await bcrypt.hash(user.password, 10);
    
    const [result] = await connection.query(
        `INSERT INTO users 
            (first_name, last_name, email, phone_number, password, role, OTP, is_verified) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
            user.first_name,
            user.last_name,
            user.email,
            user.phone_number || null,
            hashedPassword,
            user.role,
            null,
            user.is_verified ?? true
        ]
    );
    return result.insertId;
}

async function insertClient({ user_id, insurance_scheme_id, phone_number }) {
    await connection.query(
        `INSERT INTO clients 
            (user_id, insurance_scheme_id, phone_number, is_cash_client, membership_number, address) 
         VALUES (?, ?, ?, ?, ?, ?)`,
        [user_id, insurance_scheme_id, phone_number, false, 'MEM-001', '123 Seed Street']
    );
}

async function insertAmbulance() {
    console.log('🚑 Seeding ambulance...');
    const [result] = await connection.query(
        `INSERT INTO ambulance (vehicle_number, license_plate, model, make, status) 
         VALUES (?, ?, ?, ?, ?)`,
        ['AMB-001', 'AB-1234', 'Sprinter', 'Mercedes', 'available']
    );
    return result.insertId;
}

async function insertDriver({ user_id, ambulance_id }) {
    console.log('👨‍⚕️ Seeding driver profile...');
    const [result] = await connection.query(
        `INSERT INTO driver (user_id, license_number, ambulance_id, status) 
         VALUES (?, ?, ?, ?)`,
        [user_id, 'DRV-001', ambulance_id, 'on-duty']
    );
    return result.insertId;
}

async function insertRequest(data) {
    const [result] = await connection.query(
        `INSERT INTO ambulance_requests 
            (request_by, is_urgent, location, destination, latitude, longitude, destination_latitude, destination_longitude, status, comment, trip_started_at, trip_completed_at, created_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
            data.request_by,
            data.is_urgent,
            data.location,
            data.destination,
            data.latitude,
            data.longitude,
            data.destination_latitude || null,
            data.destination_longitude || null,
            data.status || 'pending',
            data.comment || null,
            data.trip_started_at || null,
            data.trip_completed_at || null,
            data.created_at || null
        ]
    );
    return result.insertId;
}

async function insertDispatch({ ambulance_request_id, ambulance_id, driver_id, created_at }) {
    console.log('🛫 Seeding dispatch record...');
    await connection.query(
        `INSERT INTO dispatch (ambulance_request_id, ambulance_id, driver_id, created_at) 
         VALUES (?, ?, ?, ?)`,
        [ambulance_request_id, ambulance_id, driver_id, created_at || null]
    );
}

async function insertNotification({ user_id, text, request_id = null, is_read = false, timestamp = null }) {
    await connection.query(
        `INSERT INTO notifications (user_id, request_id, text, is_read, timestamp)
         VALUES (?, ?, ?, ?, ?)`,
        [user_id, request_id, text, is_read, timestamp || null]
    );
}

async function seed() {
    try {
        await resetTables();

        const schemeId = await insertMasmScheme();

        console.log('👥 Seeding users...');
        const driverUserId = await insertUser({
            first_name: 'Daisy',
            last_name: 'Driver',
            email: 'driver@example.com',
            phone_number: '+265880000010',
            password: 'password123',
            role: 'driver',
            is_verified: true
        });

        const clientUserId = await insertUser({
            first_name: 'Carlos',
            last_name: 'Client',
            email: 'client@example.com',
            phone_number: '+265880000001',
            password: 'password123',
            role: 'client',
            is_verified: true
        });

        await insertClient({
            user_id: clientUserId,
            insurance_scheme_id: schemeId,
            phone_number: '+265880000001'
        });

        const ambulanceId = await insertAmbulance();
        const driverId = await insertDriver({ user_id: driverUserId, ambulance_id: ambulanceId });

        console.log('📋 Seeding ambulance requests...');
        
        // Active dispatched trip (for current active work)
        const now = new Date();
        const dispatchedRequestId = await insertRequest({
            request_by: clientUserId,
            is_urgent: true,
            location: 'City Center',
            destination: 'General Hospital',
            latitude: -13.962100,
            longitude: 33.787200,
            destination_latitude: -13.970000,
            destination_longitude: 33.790000,
            status: 'dispatched',
            comment: 'High priority - cardiac arrest',
            created_at: new Date(now.getTime() - 30 * 60 * 1000) // 30 minutes ago
        });

        await insertDispatch({
            ambulance_request_id: dispatchedRequestId,
            ambulance_id: ambulanceId,
            driver_id: driverId,
            created_at: new Date(now.getTime() - 30 * 60 * 1000) // Same as request created_at
        });

        // Completed trips (for stats calculation)
        const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000);
        const twoDaysAgo = new Date(now.getTime() - 2 * 24 * 60 * 60 * 1000);
        const threeDaysAgo = new Date(now.getTime() - 3 * 24 * 60 * 60 * 1000);
        const weekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);

        // Completed trip 1 - Yesterday
        const completed1Id = await insertRequest({
            request_by: clientUserId,
            is_urgent: true,
            location: 'Area 18',
            destination: 'Specialist Hospital',
            latitude: -13.950000,
            longitude: 33.800000,
            destination_latitude: -13.955000,
            destination_longitude: 33.805000,
            status: 'completed',
            comment: 'Completed successfully',
            created_at: new Date(yesterday.getTime() - 2 * 60 * 60 * 1000), // 2 hours before completion
            trip_started_at: new Date(yesterday.getTime() - 90 * 60 * 1000), // 90 minutes ago
            trip_completed_at: yesterday
        });
        await insertDispatch({
            ambulance_request_id: completed1Id,
            ambulance_id: ambulanceId,
            driver_id: driverId,
            created_at: new Date(yesterday.getTime() - 2 * 60 * 60 * 1000) // Same as request created_at
        });

        // Completed trip 2 - Two days ago
        const completed2Id = await insertRequest({
            request_by: clientUserId,
            is_urgent: false,
            location: 'Area 10',
            destination: 'District Hospital',
            latitude: -13.965000,
            longitude: 33.775000,
            destination_latitude: -13.970000,
            destination_longitude: 33.780000,
            status: 'completed',
            comment: 'Routine transfer completed',
            created_at: new Date(twoDaysAgo.getTime() - 3 * 60 * 60 * 1000),
            trip_started_at: new Date(twoDaysAgo.getTime() - 2 * 60 * 60 * 1000),
            trip_completed_at: twoDaysAgo
        });
        await insertDispatch({
            ambulance_request_id: completed2Id,
            ambulance_id: ambulanceId,
            driver_id: driverId,
            created_at: new Date(twoDaysAgo.getTime() - 3 * 60 * 60 * 1000) // Same as request created_at
        });

        // Completed trip 3 - Three days ago
        const completed3Id = await insertRequest({
            request_by: clientUserId,
            is_urgent: true,
            location: 'Area 25',
            destination: 'Central Hospital',
            latitude: -13.940000,
            longitude: 33.810000,
            destination_latitude: -13.945000,
            destination_longitude: 33.815000,
            status: 'completed',
            comment: 'Emergency response completed',
            created_at: new Date(threeDaysAgo.getTime() - 1 * 60 * 60 * 1000),
            trip_started_at: new Date(threeDaysAgo.getTime() - 45 * 60 * 1000),
            trip_completed_at: threeDaysAgo
        });
        await insertDispatch({
            ambulance_request_id: completed3Id,
            ambulance_id: ambulanceId,
            driver_id: driverId,
            created_at: new Date(threeDaysAgo.getTime() - 1 * 60 * 60 * 1000) // Same as request created_at
        });

        // Completed trip 4 - One week ago
        const completed4Id = await insertRequest({
            request_by: clientUserId,
            is_urgent: false,
            location: 'Old Town',
            destination: 'Regional Clinic',
            latitude: -13.970400,
            longitude: 33.790100,
            destination_latitude: -13.975000,
            destination_longitude: 33.795000,
            status: 'completed',
            comment: 'Weekly checkup transport',
            created_at: new Date(weekAgo.getTime() - 4 * 60 * 60 * 1000),
            trip_started_at: new Date(weekAgo.getTime() - 3 * 60 * 60 * 1000),
            trip_completed_at: weekAgo
        });
        await insertDispatch({
            ambulance_request_id: completed4Id,
            ambulance_id: ambulanceId,
            driver_id: driverId,
            created_at: new Date(weekAgo.getTime() - 4 * 60 * 60 * 1000) // Same as request created_at
        });

        // Completed trip 5 - Yesterday (another one)
        const completed5Id = await insertRequest({
            request_by: clientUserId,
            is_urgent: false,
            location: 'Area 12',
            destination: 'Community Health Center',
            latitude: -13.960000,
            longitude: 33.785000,
            destination_latitude: -13.965000,
            destination_longitude: 33.790000,
            status: 'completed',
            comment: 'Patient transport completed',
            created_at: new Date(yesterday.getTime() - 5 * 60 * 60 * 1000),
            trip_started_at: new Date(yesterday.getTime() - 4 * 60 * 60 * 1000),
            trip_completed_at: new Date(yesterday.getTime() - 3 * 60 * 60 * 1000)
        });
        await insertDispatch({
            ambulance_request_id: completed5Id,
            ambulance_id: ambulanceId,
            driver_id: driverId,
            created_at: new Date(yesterday.getTime() - 5 * 60 * 60 * 1000) // Same as request created_at
        });

        // Pending requests (not dispatched yet) - for testing self-assignment
        await insertRequest({
            request_by: clientUserId,
            is_urgent: true,
            location: 'Area 15',
            destination: 'Local Clinic',
            latitude: -13.955000,
            longitude: 33.795000,
            destination_latitude: -13.960000,
            destination_longitude: 33.800000,
            status: 'pending',
            comment: 'Urgent - Patient requires immediate assessment'
        });

        await insertRequest({
            request_by: clientUserId,
            is_urgent: false,
            location: 'Area 20',
            destination: 'City Hospital',
            latitude: -13.945000,
            longitude: 33.780000,
            destination_latitude: -13.950000,
            destination_longitude: 33.785000,
            status: 'pending',
            comment: 'Routine patient transfer needed'
        });

        await insertRequest({
            request_by: clientUserId,
            is_urgent: true,
            location: 'Area 5',
            destination: 'Emergency Center',
            latitude: -13.975000,
            longitude: 33.770000,
            destination_latitude: -13.980000,
            destination_longitude: 33.775000,
            status: 'pending',
            comment: 'Emergency - Critical patient needs transport'
        });

        await insertRequest({
            request_by: clientUserId,
            is_urgent: false,
            location: 'Area 25',
            destination: 'Specialist Clinic',
            latitude: -13.935000,
            longitude: 33.790000,
            destination_latitude: -13.940000,
            destination_longitude: 33.795000,
            status: 'pending',
            comment: 'Regular appointment transport'
        });

        // Seed sample notifications for the driver (after trips exist)
        console.log('🔔 Seeding driver notifications...');
        await insertNotification({
            user_id: driverUserId,
            request_id: dispatchedRequestId,
            text: `You have been assigned to ambulance request #${dispatchedRequestId}. Dispatch ID: 1.`,
            is_read: false,
        });
        await insertNotification({
            user_id: driverUserId,
            request_id: completed1Id,
            text: `Trip #${completed1Id} has been completed successfully.`,
            is_read: true,
        });
        await insertNotification({
            user_id: driverUserId,
            request_id: completed2Id,
            text: `A new high-priority request (${completed2Id}) is available nearby.`,
            is_read: false,
        });

        console.log('\n✅ Seed complete!');
        console.log('\n📊 Seeded data:');
        console.log('  • 1 active dispatched trip');
        console.log('  • 5 completed trips (with timestamps for stats)');
        console.log('  • 4 pending requests (for self-assignment testing)');
        console.log('\n🔐 Login credentials:');
        console.log('  Driver:');
        console.log('    Email:    driver@example.com');
        console.log('    Password: password123');
        console.log('  Client:');
        console.log('    Email:    client@example.com');
        console.log('    Password: password123');
    } catch (error) {
        console.error('❌ Seeding failed:', error);
        process.exitCode = 1;
    } finally {
        db.end();
    }
}

seed();
