/**
 * CSV Data Import Script
 * Imports IFSC and Pincode data from CSV files into PostgreSQL
 * Usage: npm run db:import-csv
 */

require('dotenv').config();
const fs = require('fs');
const path = require('path');
const csv = require('csv-parser');
const { pool } = require('./connection');

const BATCH_SIZE = 500;

// Helper function to truncate decimal places and avoid numeric overflow
function truncateDecimal(value, decimals) {
    if (!value || value === '') return null;
    const num = parseFloat(value);
    if (isNaN(num)) return null;
    return Math.round(num * Math.pow(10, decimals)) / Math.pow(10, decimals);
}

// ============================================
// IFSC IMPORT
// ============================================
async function importIFSC() {
    const filePath = path.join(__dirname, '../../data/ifsc.csv');

    if (!fs.existsSync(filePath)) {
        console.log('⚠️  IFSC CSV file not found at:', filePath);
        return;
    }

    console.log('📦 Starting IFSC import...');

    let batch = [];
    let totalInserted = 0;
    let isFirstRow = true;

    return new Promise((resolve, reject) => {
        fs.createReadStream(filePath)
            .pipe(csv())
            .on('data', async (row) => {
                // DEBUG: Log first row to see CSV column names
                if (isFirstRow) {
                    console.log('📋 IFSC CSV Headers:', Object.keys(row));
                    console.log('📋 First Row Sample:', row);
                    isFirstRow = false;
                }

                // ============================================
                // COLUMN MAPPING - CUSTOMIZE HERE!
                // Map your CSV column names to database columns
                // Left side = DB column, Right side = CSV column name
                // ============================================
                const mappedRow = {
                    ifsc: row['IFSC'] || row['ifsc'] || row['IFSC_CODE'] || row['ifsc_code'],
                    bank_name: row['BANK'] || row['bank'] || row['BANK_NAME'] || row['bank_name'],
                    branch: row['BRANCH'] || row['branch'] || row['BRANCH_NAME'] || row['branch_name'],
                    address: row['ADDRESS'] || row['address'] || row['FULL_ADDRESS'],
                    city: row['CITY'] || row['city'] || row['CITY_NAME'],
                    district: row['DISTRICT'] || row['district'] || row['DISTRICT_NAME'],
                    state: row['STATE'] || row['state'] || row['STATE_NAME'],
                    contact: row['CONTACT'] || row['contact'] || row['PHONE'] || row['phone'],
                    upi_enabled: row['UPI'] === 'true' || row['UPI'] === 'TRUE' || row['UPI'] === '1' || false,
                    rtgs_enabled: row['RTGS'] === 'true' || row['RTGS'] === 'TRUE' || row['RTGS'] === '1' || true,
                    neft_enabled: row['NEFT'] === 'true' || row['NEFT'] === 'TRUE' || row['NEFT'] === '1' || true,
                    imps_enabled: row['IMPS'] === 'true' || row['IMPS'] === 'TRUE' || row['IMPS'] === '1' || true,
                    micr_code: row['MICR'] || row['micr'] || row['MICR_CODE'] || null,
                    swift_code: row['SWIFT'] || row['swift'] || row['SWIFT_CODE'] || null
                };

                // Skip if no IFSC code
                if (!mappedRow.ifsc) return;

                batch.push(mappedRow);

                // Insert batch when full
                if (batch.length >= BATCH_SIZE) {
                    const currentBatch = [...batch];
                    batch = [];
                    await insertIFSCBatch(currentBatch);
                    totalInserted += currentBatch.length;
                    process.stdout.write(`\r   Inserted: ${totalInserted} records`);
                }
            })
            .on('end', async () => {
                // Insert remaining rows
                if (batch.length > 0) {
                    await insertIFSCBatch(batch);
                    totalInserted += batch.length;
                }
                console.log(`\n✅ IFSC import complete! Total records: ${totalInserted}`);
                resolve(totalInserted);
            })
            .on('error', (error) => {
                console.error('❌ IFSC import error:', error.message);
                reject(error);
            });
    });
}

async function insertIFSCBatch(rows) {
    if (rows.length === 0) return;

    const values = [];
    const placeholders = [];
    let paramIndex = 1;

    for (const row of rows) {
        placeholders.push(`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2}, $${paramIndex + 3}, $${paramIndex + 4}, $${paramIndex + 5}, $${paramIndex + 6}, $${paramIndex + 7}, $${paramIndex + 8}, $${paramIndex + 9}, $${paramIndex + 10}, $${paramIndex + 11}, $${paramIndex + 12}, $${paramIndex + 13})`);
        values.push(
            row.ifsc,
            row.bank_name,
            row.branch,
            row.address,
            row.city,
            row.district,
            row.state,
            row.contact,
            row.upi_enabled,
            row.rtgs_enabled,
            row.neft_enabled,
            row.imps_enabled,
            row.micr_code,
            row.swift_code
        );
        paramIndex += 14;
    }

    const query = `
    INSERT INTO ifsc_codes (ifsc, bank_name, branch, address, city, district, state, contact, upi_enabled, rtgs_enabled, neft_enabled, imps_enabled, micr_code, swift_code)
    VALUES ${placeholders.join(', ')}
    ON CONFLICT (ifsc) DO NOTHING
  `;

    try {
        await pool.query(query, values);
    } catch (error) {
        console.error('\n❌ Batch insert error:', error.message);
    }
}

// ============================================
// PINCODE IMPORT
// ============================================
async function importPincode() {
    const filePath = path.join(__dirname, '../../data/pincode.csv');

    if (!fs.existsSync(filePath)) {
        console.log('⚠️  Pincode CSV file not found at:', filePath);
        return;
    }

    console.log('📦 Starting Pincode import...');

    let batch = [];
    let totalInserted = 0;
    let isFirstRow = true;

    return new Promise((resolve, reject) => {
        fs.createReadStream(filePath)
            .pipe(csv())
            .on('data', async (row) => {
                // DEBUG: Log first row to see CSV column names
                if (isFirstRow) {
                    console.log('📋 Pincode CSV Headers:', Object.keys(row));
                    console.log('📋 First Row Sample:', row);
                    isFirstRow = false;
                }

                // ============================================
                // COLUMN MAPPING - CUSTOMIZE HERE!
                // Map your CSV column names to database columns
                // Left side = DB column, Right side = CSV column name
                // ============================================
                const mappedRow = {
                    pincode: row['pincode'] || row['Pincode'] || row['PINCODE'] || row['PIN_CODE'],
                    office_name: row['officename'] || row['OfficeName'] || row['OFFICE_NAME'] || row['office_name'] || row['PostOfficeName'],
                    office_type: row['officetype'] || row['officeType'] || row['OfficeType'] || row['OFFICE_TYPE'] || row['office_type'],
                    district: row['district'] || row['Districtname'] || row['District'] || row['DISTRICT'] || row['district_name'],
                    division: row['divisionname'] || row['Division'] || row['DIVISION'] || row['division'],
                    region: row['regionname'] || row['Region'] || row['REGION'] || row['region'],
                    state: row['statename'] || row['state'] || row['State'] || row['STATE'] || row['state_name'],
                    // Truncate lat/long to 6 decimal places to prevent numeric overflow
                    latitude: truncateDecimal(row['latitude'] || row['Latitude'] || row['LAT'], 6),
                    longitude: truncateDecimal(row['longitude'] || row['Longitude'] || row['LNG'] || row['LONG'], 6)
                };

                // Skip if no pincode
                if (!mappedRow.pincode) return;

                batch.push(mappedRow);

                // Insert batch when full
                if (batch.length >= BATCH_SIZE) {
                    const currentBatch = [...batch];
                    batch = [];
                    await insertPincodeBatch(currentBatch);
                    totalInserted += currentBatch.length;
                    process.stdout.write(`\r   Inserted: ${totalInserted} records`);
                }
            })
            .on('end', async () => {
                // Insert remaining rows
                if (batch.length > 0) {
                    await insertPincodeBatch(batch);
                    totalInserted += batch.length;
                }
                console.log(`\n✅ Pincode import complete! Total records: ${totalInserted}`);
                resolve(totalInserted);
            })
            .on('error', (error) => {
                console.error('❌ Pincode import error:', error.message);
                reject(error);
            });
    });
}

async function insertPincodeBatch(rows) {
    if (rows.length === 0) return;

    const values = [];
    const placeholders = [];
    let paramIndex = 1;

    for (const row of rows) {
        placeholders.push(`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2}, $${paramIndex + 3}, $${paramIndex + 4}, $${paramIndex + 5}, $${paramIndex + 6}, $${paramIndex + 7}, $${paramIndex + 8})`);
        values.push(
            row.pincode,
            row.office_name,
            row.office_type,
            row.district,
            row.division,
            row.region,
            row.state,
            row.latitude,
            row.longitude
        );
        paramIndex += 9;
    }

    const query = `
    INSERT INTO pincodes (pincode, office_name, office_type, district, division, region, state, latitude, longitude)
    VALUES ${placeholders.join(', ')}
    ON CONFLICT DO NOTHING
  `;

    try {
        await pool.query(query, values);
    } catch (error) {
        console.error('\n❌ Batch insert error:', error.message);
    }
}

// ============================================
// PAN IMPORT
// ============================================
// ============================================
// PAN IMPORT
// ============================================
async function importPAN() {
    const filePath = path.join(__dirname, '../../data/pan.csv');

    if (!fs.existsSync(filePath)) {
        console.log('⚠️  PAN CSV file not found at:', filePath);
        return;
    }

    console.log('📦 Starting PAN import...');

    let batch = [];
    let totalInserted = 0;
    let isFirstRow = true;

    try {
        const stream = fs.createReadStream(filePath).pipe(csv());

        for await (const row of stream) {
            // DEBUG: Log first row to see CSV column names
            if (isFirstRow) {
                console.log('📋 PAN CSV Headers:', Object.keys(row));
                console.log('📋 First Row Sample:', row);
                isFirstRow = false;
            }

            const mappedRow = {
                pan_number: row['pan_number'] || row['PAN_NUMBER'],
                full_name: row['full_name'] || row['FULL_NAME'],
                father_name: row['father_name'] || row['FATHER_NAME'],
                date_of_birth: row['date_of_birth'] || row['DATE_OF_BIRTH'] || row['dob'],
                gender: row['gender'] || row['GENDER'],
                category: row['category'] || row['CATEGORY'] || null,
                status: row['status'] || row['STATUS'] || 'Active'
            };

            // Skip if no PAN number
            if (!mappedRow.pan_number) continue;

            batch.push(mappedRow);

            // Insert batch when full
            if (batch.length >= BATCH_SIZE) {
                const currentBatch = [...batch];
                batch = [];
                await insertPANBatch(currentBatch);
                totalInserted += currentBatch.length;
                process.stdout.write(`\r   Inserted: ${totalInserted} records`);
            }
        }

        // Insert remaining rows
        if (batch.length > 0) {
            await insertPANBatch(batch);
            totalInserted += batch.length;
        }

        console.log(`\n✅ PAN import complete! Total records: ${totalInserted}`);
        return totalInserted;

    } catch (error) {
        console.error('❌ PAN import error:', error.message);
        throw error;
    }
}

async function insertPANBatch(rows) {
    if (rows.length === 0) return;

    const values = [];
    const placeholders = [];
    let paramIndex = 1;

    for (const row of rows) {
        placeholders.push(`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2}, $${paramIndex + 3}, $${paramIndex + 4}, $${paramIndex + 5}, $${paramIndex + 6})`);
        values.push(
            row.pan_number,
            row.full_name,
            row.father_name,
            row.date_of_birth,
            row.gender,
            row.category,
            row.status
        );
        paramIndex += 7;
    }

    const query = `
    INSERT INTO pan_data (pan_number, full_name, father_name, date_of_birth, gender, category, status)
    VALUES ${placeholders.join(', ')}
    ON CONFLICT (pan_number) DO NOTHING
  `;

    try {
        await pool.query(query, values);
    } catch (error) {
        console.error('\n❌ Batch insert error:', error.message);
    }
}

// ============================================
// MAIN EXECUTION
// ============================================
async function runImport() {
    console.log('🚀 Starting CSV Data Import...\n');
    console.log('='.repeat(50));

    try {
        // Import IFSC codes first
        await importIFSC();

        console.log('');

        // Then import Pincodes
        await importPincode();

        console.log('');

        // Import PAN Data
        await importPAN();

        console.log('\n' + '='.repeat(50));
        console.log('✅ All imports completed successfully!');

    } catch (error) {
        console.error('\n❌ Import failed:', error.message);
        process.exit(1);
    } finally {
        await pool.end();
        process.exit(0);
    }
}

runImport();
