Admin_dash/app/api/visitors/route.ts

import { NextResponse } from 'next/server';
import { getVisitorsDb } from '@/lib/visitors-db';
import geoip from 'geoip-lite';

export const dynamic = 'force-dynamic';

export async function GET() {
    try {
        const db = await getVisitorsDb();

        if (!db) {
            return NextResponse.json({
                locations: [],
                totalVisitors: 0,
                active24h: 0,
                active7d: 0,
            });
        }

        // Count unique visitors across time windows
        const [total, last24h, last7d] = await Promise.all([
            db.get(`SELECT COUNT(DISTINCT ip_address) as count FROM visits`),
            db.get(`SELECT COUNT(DISTINCT ip_address) as count FROM visits WHERE visited_at >= datetime('now', '-24 hours')`),
            db.get(`SELECT COUNT(DISTINCT ip_address) as count FROM visits WHERE visited_at >= datetime('now', '-7 days')`),
        ]);

        // Get unique IPs from last 7 days for globe markers
        const recentIps = await db.all(
            `SELECT ip_address, COUNT(*) as hits FROM visits WHERE visited_at >= datetime('now', '-7 days') GROUP BY ip_address`
        );

        // Geo-locate IPs and group by location + country
        const locationMap = new Map<string, { lat: number; lon: number; city: string; country: string; count: number }>();
        const countryMap = new Map<string, { count: number; regions: Map<string, { count: number; cities: Map<string, number> }> }>();

        for (const row of recentIps) {
            const geo = geoip.lookup(row.ip_address);
            if (geo && geo.ll) {
                // Location grouping for globe markers
                const key = `${geo.ll[0]},${geo.ll[1]}`;
                const existing = locationMap.get(key);
                if (existing) {
                    existing.count += row.hits;
                } else {
                    locationMap.set(key, {
                        lat: geo.ll[0],
                        lon: geo.ll[1],
                        city: geo.city || 'Unknown',
                        country: geo.country || 'Unknown',
                        count: row.hits,
                    });
                }

                // Country aggregation
                const countryCode = geo.country || 'XX';
                const regionCode = geo.region || 'Unknown';
                const cityName = geo.city || 'Unknown';

                let country = countryMap.get(countryCode);
                if (!country) {
                    country = { count: 0, regions: new Map() };
                    countryMap.set(countryCode, country);
                }
                country.count += row.hits;

                let region = country.regions.get(regionCode);
                if (!region) {
                    region = { count: 0, cities: new Map() };
                    country.regions.set(regionCode, region);
                }
                region.count += row.hits;

                region.cities.set(cityName, (region.cities.get(cityName) || 0) + row.hits);
            }
        }

        const locations = Array.from(locationMap.values()).map(loc => ({
            location: [loc.lat, loc.lon],
            size: Math.min(0.1, 0.05 + loc.count * 0.005),
            city: loc.city,
            country: loc.country,
        }));

        const countries = Array.from(countryMap.entries())
            .map(([code, data]) => ({
                code,
                count: data.count,
                regions: Array.from(data.regions.entries())
                    .map(([region, rData]) => ({
                        region,
                        count: rData.count,
                        cities: Array.from(rData.cities.entries())
                            .map(([city, count]) => ({ city, count }))
                            .sort((a, b) => b.count - a.count),
                    }))
                    .sort((a, b) => b.count - a.count),
            }))
            .sort((a, b) => b.count - a.count);

        return NextResponse.json({
            locations,
            countries,
            totalVisitors: total?.count || 0,
            active24h: last24h?.count || 0,
            active7d: last7d?.count || 0,
        });
    } catch (error) {
        console.error('Visitor fetch error:', error);
        return NextResponse.json({
            locations: [],
            totalVisitors: 0,
            active24h: 0,
            active7d: 0,
        });
    }
}