const express = require('express');
const cors = require('cors');
const sqlite3 = require('sqlite3').verbose();
const geoip = require('geoip-lite');
const moment = require('moment');
const helmet = require('helmet');
const path = require('path');

const app = express();
const PORT = process.env.PORT || 3001;

// Middleware
app.use(helmet());
app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, '../')));

// Database setup
const db = new sqlite3.Database('./admin.db');

// Criar tabelas se não existirem
db.serialize(() => {
    // Tabela de visitas
    db.run(`CREATE TABLE IF NOT EXISTS visits (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ip TEXT NOT NULL,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        user_agent TEXT,
        country TEXT,
        region TEXT,
        city TEXT,
        latitude REAL,
        longitude REAL,
        action TEXT,
        metadata TEXT,
        utm_source TEXT,
        utm_medium TEXT,
        utm_campaign TEXT
    )`);

    // Tabela de consultas CNPJ
    db.run(`CREATE TABLE IF NOT EXISTS cnpj_consultas (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ip TEXT NOT NULL,
        cnpj TEXT NOT NULL,
        razao_social TEXT,
        success BOOLEAN DEFAULT 1,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        response_time INTEGER,
        error_message TEXT,
        utm_source TEXT,
        utm_medium TEXT,
        utm_campaign TEXT
    )`);

    // Tabela de transações PIX
    db.run(`CREATE TABLE IF NOT EXISTS pix_transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pix_id TEXT UNIQUE NOT NULL,
        ip TEXT NOT NULL,
        cnpj TEXT,
        valor DECIMAL(10,2),
        status TEXT DEFAULT 'gerado',
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        paid_at DATETIME,
        qr_code TEXT,
        copy_paste TEXT,
        utm_source TEXT,
        utm_medium TEXT,
        utm_campaign TEXT
    )`);
});

// Função para obter informações de geolocalização
function getGeoInfo(ip) {
    // Para IPs locais, retornar dados padrão
    if (ip === '127.0.0.1' || ip === '::1' || ip.startsWith('192.168.') || ip.startsWith('10.')) {
        return {
            country: 'BR',
            region: 'SP',
            city: 'São Paulo',
            ll: [-23.5505, -46.6333]
        };
    }
    
    return geoip.lookup(ip) || {
        country: 'BR',
        region: 'Unknown',
        city: 'Unknown',
        ll: [0, 0]
    };
}

// Função para obter IP real do cliente
function getRealIP(req) {
    return req.headers['x-forwarded-for'] || 
           req.headers['x-real-ip'] || 
           req.connection.remoteAddress || 
           req.socket.remoteAddress ||
           (req.connection.socket ? req.connection.socket.remoteAddress : null) ||
           '127.0.0.1';
}

// API Endpoints

// 1. Track visit/action
app.post('/api/track', (req, res) => {
    const ip = getRealIP(req);
    const { action, metadata } = req.body;
    const userAgent = req.headers['user-agent'];
    const geo = getGeoInfo(ip);
    const utm = metadata.utm || {};

    try {
        switch (action) {
            case 'visit':
                db.run(`INSERT INTO visits (ip, user_agent, country, region, city, latitude, longitude, action, metadata, utm_source, utm_medium, utm_campaign)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, 
                        [ip, userAgent, geo.country, geo.region, geo.city, geo.ll[0], geo.ll[1], action, JSON.stringify(metadata), utm.utm_source, utm.utm_medium, utm.utm_campaign]);
                break;
            case 'cnpj_consulta':
                db.run(`INSERT INTO cnpj_consultas (ip, cnpj, razao_social, success, utm_source, utm_medium, utm_campaign)
                        VALUES (?, ?, ?, ?, ?, ?, ?)`, 
                        [ip, metadata.cnpj, metadata.empresaName, metadata.success, utm.utm_source, utm.utm_medium, utm.utm_campaign]);
                break;
            case 'pix_gerado':
            case 'pix_pago':
                db.run(`INSERT INTO pix_transactions (pix_id, ip, cnpj, valor, status, utm_source, utm_medium, utm_campaign)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, 
                        [moment().valueOf(), ip, metadata.cnpj, metadata.valor, action, utm.utm_source, utm.utm_medium, utm.utm_campaign]);
                break;
        }
        res.status(200).send({ message: 'Event tracked successfully' });
    } catch (error) {
        console.error('Error tracking event:', error);
        res.status(500).send({ message: 'Internal Server Error' });
    }
});

// 2. Get dashboard stats
app.get('/api/stats', (req, res) => {
    const query = `
        SELECT
            (SELECT COUNT(*) FROM visits) AS total_visits,
            (SELECT COUNT(*) FROM cnpj_consultas) AS cnpj_consultas,
            (SELECT COUNT(*) FROM pix_transactions WHERE status = 'pix_gerado') AS pix_gerados,
            (SELECT COUNT(*) FROM pix_transactions WHERE status = 'pix_pago') AS pix_pagos;
    `;
    db.get(query, (err, row) => {
        if (err) {
            res.status(500).json({ error: err.message });
            return;
        }
        res.json(row);
    });
});

// 3. Get recent events
app.get('/api/events', (req, res) => {
    const query = `
        SELECT 'visit' as type, timestamp, metadata FROM visits ORDER BY timestamp DESC LIMIT 10;
    `;
    // This could be expanded to union other event types
    db.all(query, (err, rows) => {
        if (err) {
            res.status(500).json({ error: err.message });
            return;
        }
        res.json(rows);
    });
});


// Servir o admin dashboard
app.get('/admin', (req, res) => {
    res.sendFile(path.join(__dirname, 'index.html'));
});

// Error handling
app.use((err, req, res, next) => {
    console.error(err.stack);
    res.status(500).json({ error: 'Algo deu errado!' });
});

// Start server
app.listen(PORT, () => {
    console.log(`Servidor admin rodando na porta ${PORT}`);
    console.log(`📊 Admin Dashboard: http://localhost:${PORT}/admin`);
    console.log(`🔗 API Base: http://localhost:${PORT}/api`);
});

// Graceful shutdown
process.on('SIGINT', () => {
    console.log('\n📴 Fechando servidor...');
    db.close((err) => {
        if (err) {
            console.error(err.message);
        } else {
            console.log('💾 Conexão com o banco fechada.');
        }
        process.exit(0);
    });
});
