/**
 * Pincode Lookup Route
 * Returns location details for a given pincode
 * Supports local database + India Post API fallback
 */

const express = require('express');
const router = express.Router();
const axios = require('axios');
const { authenticateApiKey } = require('../middleware/auth');
const { rateLimit } = require('../middleware/rateLimit');
const { query } = require('../database/connection');
const { cacheGet, cacheSet } = require('../cache/redis');
const { logApiCall } = require('../services/analytics');

/**
 * Fetch pincode from India Post API and save to database
 */
async function fetchFromIndiaPostAPI(pincode) {
    try {
        console.log(`🌐 Fetching pincode ${pincode} from India Post API...`);

        const response = await axios.get(`https://api.postalpincode.in/pincode/${pincode}`, {
            timeout: 10000
        });

        if (response.data && response.data[0] && response.data[0].Status === 'Success') {
            const postOffices = response.data[0].PostOffice;
            const savedRecords = [];

            for (const po of postOffices) {
                try {
                    // Save to database for future lookups (UPSERT for self-healing)
                    await query(
                        `INSERT INTO pincodes (pincode, office_name, office_type, district, division, region, state, block, latitude, longitude, updated_at)
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())
                         ON CONFLICT (id) 
                         DO UPDATE SET 
                            office_name = EXCLUDED.office_name,
                            office_type = EXCLUDED.office_type,
                            district = EXCLUDED.district,
                            division = EXCLUDED.division,
                            region = EXCLUDED.region,
                            state = EXCLUDED.state,
                            block = EXCLUDED.block,
                            latitude = EXCLUDED.latitude,
                            longitude = EXCLUDED.longitude,
                            updated_at = NOW()`,
                        [
                            pincode,
                            po.Name || '',
                            po.BranchType || 'PO',
                            po.District || '',
                            po.Division || '',
                            po.Region || '',
                            po.State || '',
                            po.Block || '',
                            '0',
                            '0'
                        ]
                    );
                } catch (insertError) {
                    // Try to update if insert fails
                    try {
                        await query(
                            `UPDATE pincodes 
                             SET office_type = $3, district = $4, division = $5, region = $6, state = $7, block = $8, updated_at = NOW()
                             WHERE pincode = $1 AND office_name = $2`,
                            [
                                pincode,
                                po.Name || '',
                                po.BranchType || 'PO',
                                po.District || '',
                                po.Division || '',
                                po.Region || '',
                                po.State || '',
                                po.Block || ''
                            ]
                        );
                    } catch (updateError) {
                        // Ignore errors
                    }
                }

                savedRecords.push({
                    pincode: pincode,
                    office_name: po.Name || '',
                    office_type: po.BranchType || 'PO',
                    district: po.District || '',
                    division: po.Division || '',
                    region: po.Region || '',
                    state: po.State || '',
                    block: po.Block || '',
                    latitude: '0',
                    longitude: '0'
                });
            }

            console.log(`✅ Fetched and saved ${savedRecords.length} offices for pincode ${pincode}`);
            return { success: true, data: savedRecords, source: 'INDIA_POST_API' };
        }

        return { success: false, error: 'Invalid pincode' };
    } catch (error) {
        console.error(`❌ India Post API error for ${pincode}:`, error.message);
        return { success: false, error: error.message };
    }
}

const STATE_CODES = {
    'Andhra Pradesh': 'AP', 'Arunachal Pradesh': 'AR', 'Assam': 'AS', 'Bihar': 'BR',
    'Chhattisgarh': 'CG', 'Delhi': 'DL', 'Goa': 'GA', 'Gujarat': 'GJ', 'Haryana': 'HR',
    'Himachal Pradesh': 'HP', 'Jharkhand': 'JH', 'Karnataka': 'KA', 'Kerala': 'KL',
    'Madhya Pradesh': 'MP', 'Maharashtra': 'MH', 'Manipur': 'MN', 'Meghalaya': 'ML',
    'Mizoram': 'MZ', 'Nagaland': 'NL', 'Odisha': 'OD', 'Punjab': 'PB', 'Rajasthan': 'RJ',
    'Sikkim': 'SK', 'Tamil Nadu': 'TN', 'Telangana': 'TS', 'Tripura': 'TR',
    'Uttar Pradesh': 'UP', 'Uttarakhand': 'UK', 'West Bengal': 'WB'
};

router.use(authenticateApiKey);
router.use(rateLimit);

/**
 * GET /v1/pincode/search?q=query
 * Reverse search - find pincodes by office name, district, or state
 */
router.get('/search', async (req, res, next) => {
    const startTime = Date.now();
    let success = false;

    try {
        const { q } = req.query;

        if (!q || q.trim().length < 2) {
            return res.status(400).json({
                success: false,
                error: { code: 'INVALID_QUERY', message: 'Search query must be at least 2 characters' }
            });
        }

        const searchTerm = q.trim();
        const cacheKey = `pincode_search:${searchTerm.toLowerCase()}`;

        // Check cache first (60 second cache for search queries)
        let data = await cacheGet(cacheKey);

        if (!data) {
            // Case-insensitive search using ILIKE with wildcards
            const result = await query(
                `SELECT DISTINCT pincode, office_name, office_type, district, state, latitude, longitude
                 FROM pincodes 
                 WHERE office_name ILIKE $1 
                    OR district ILIKE $1 
                    OR state ILIKE $1
                 ORDER BY office_name
                 LIMIT 10`,
                [`%${searchTerm}%`]
            );

            data = result.rows;

            // Cache for 60 seconds
            if (data.length > 0) {
                await cacheSet(cacheKey, data, 60);
            }
        }

        success = true;

        res.json({
            success: true,
            data: {
                query: searchTerm,
                count: data.length,
                results: data.map(row => ({
                    pincode: row.pincode,
                    office_name: row.office_name,
                    office_type: row.office_type,
                    district: row.district,
                    state: row.state,
                    latitude: parseFloat(row.latitude) || null,
                    longitude: parseFloat(row.longitude) || null
                }))
            },
            meta: {
                request_id: `req_pin_search_${Date.now()}`,
                credits_used: 1,
                credits_remaining: req.user.remaining - 1
            }
        });

    } catch (error) {
        next(error);
    } finally {
        await logApiCall({
            userId: req.user.id,
            apiKeyId: req.user.apiKeyId,
            endpoint: '/v1/pincode/search',
            method: 'GET',
            params: { q: req.query?.q },
            status: success ? 200 : 500,
            duration: Date.now() - startTime,
            success,
            isTestKey: req.user.isTestKey
        });
    }
});

/**
 * GET /v1/pincode/:pincode
 * Lookup pincode details with India Post API fallback
 */
router.get('/:pincode', async (req, res, next) => {
    const startTime = Date.now();
    let success = false;
    let source = 'LOCAL_DB';

    try {
        const { pincode } = req.params;

        if (!/^\d{6}$/.test(pincode)) {
            return res.status(400).json({
                success: false,
                error: { code: 'INVALID_PINCODE', message: 'Pincode must be 6 digits' }
            });
        }

        const cacheKey = `pincode:${pincode}`;
        let data = await cacheGet(cacheKey);
        let needsRefresh = false;

        if (data) {
            source = 'CACHE';
            // Self-healing: Check if taluk is missing
            if (data.length > 0 && (!data[0].block || data[0].block === '')) {
                console.log(`🔄 Self-healing: Taluk missing for pincode ${pincode}, refetching...`);
                needsRefresh = true;
                data = null; // Force refetch
            }
        }

        if (!data) {
            // Try local database first
            const result = await query('SELECT * FROM pincodes WHERE pincode = $1', [pincode]);

            if (result.rows.length === 0 || needsRefresh) {
                // Fallback to India Post API (or forced refresh for self-healing)
                if (needsRefresh) {
                    console.log(`🔄 Refreshing pincode ${pincode} with India Post API...`);
                } else {
                    console.log(`📦 Pincode ${pincode} not in DB, trying India Post API...`);
                }

                const apiResult = await fetchFromIndiaPostAPI(pincode);

                if (!apiResult.success) {
                    return res.status(404).json({
                        success: false,
                        error: { code: 'PINCODE_NOT_FOUND', message: 'Pincode not found in any source' }
                    });
                }

                data = apiResult.data;
                source = 'INDIA_POST_API';
            } else {
                data = result.rows;
                // Double-check: if DB data also has empty taluk, force refresh
                if (data.length > 0 && (!data[0].block || data[0].block === '')) {
                    console.log(`🔄 Self-healing: DB data also missing taluk for ${pincode}, refetching...`);
                    const apiResult = await fetchFromIndiaPostAPI(pincode);

                    if (apiResult.success) {
                        data = apiResult.data;
                        source = 'INDIA_POST_API';
                    }
                }
            }

            await cacheSet(cacheKey, data, 604800); // Cache for 7 days
        }

        success = true;
        const primary = data[0];

        res.json({
            success: true,
            data: {
                pincode,
                source,
                district: primary.district,
                state: primary.state,
                state_code: STATE_CODES[primary.state] || '',
                latitude: parseFloat(primary.latitude) || null,
                longitude: parseFloat(primary.longitude) || null,
                offices: data.map(office => ({
                    name: office.office_name,
                    type: office.office_type,
                    taluk: office.block || ''
                }))
            },
            meta: {
                request_id: `req_pin_${Date.now()}`,
                credits_used: 1,
                credits_remaining: req.user.remaining - 1
            }
        });

    } catch (error) {
        next(error);
    } finally {
        await logApiCall({
            userId: req.user.id,
            apiKeyId: req.user.apiKeyId,
            endpoint: '/v1/pincode',
            method: 'GET',
            params: { pincode: req.params.pincode },
            status: success ? 200 : 500,
            duration: Date.now() - startTime,
            success,
            isTestKey: req.user.isTestKey
        });
    }
});

module.exports = router;
