require('dotenv').config();
const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const UAParser = require('ua-parser-js');

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

// Middleware
app.use(cors());
app.use(express.json({ limit: '50mb' })); // Increased limit for base64 images
app.use(express.urlencoded({ extended: true, limit: '50mb' }));
app.use(express.static('.')); // Serve static files from current directory

// Database connection pool
const pool = mysql.createPool({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Test database connection
async function testConnection() {
    try {
        const connection = await pool.getConnection();
        console.log('✅ Database connected successfully');
        connection.release();
    } catch (error) {
        console.error('❌ Database connection failed:', error.message);
        process.exit(1);
    }
}

// ============================================
// HELPER FUNCTIONS
// ============================================

// Get client IP address
function getClientIp(req) {
    return req.headers['x-forwarded-for']?.split(',')[0].trim() ||
        req.headers['x-real-ip'] ||
        req.socket.remoteAddress ||
        req.connection.remoteAddress ||
        'unknown';
}

// Parse user agent
function parseUserAgent(userAgentString) {
    const parser = new UAParser(userAgentString);
    const result = parser.getResult();
    return `${result.browser.name || 'Unknown'} ${result.browser.version || ''} on ${result.os.name || 'Unknown'} ${result.os.version || ''}`;
}

// ============================================
// API ENDPOINTS
// ============================================

// Health check
app.get('/api/health', (req, res) => {
    res.json({ status: 'OK', message: 'Server is running' });
});

// Create new documentation
app.post('/api/dokumentasi', async (req, res) => {
    try {
        const { nama, noAnggota, jenisPerjanjian, tanggal, catatan, foto } = req.body;

        // Validation
        if (!nama || !noAnggota || !jenisPerjanjian || !tanggal || !foto) {
            return res.status(400).json({
                error: 'Missing required fields',
                required: ['nama', 'noAnggota', 'jenisPerjanjian', 'tanggal', 'foto']
            });
        }

        // Get IP and browser info
        const ipAddress = getClientIp(req);
        const browserInfo = parseUserAgent(req.headers['user-agent'] || '');

        const [result] = await pool.execute(
            `INSERT INTO dokumentasi (nama, no_anggota, jenis_perjanjian, tanggal, catatan, foto, ip_address, browser_info) 
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [nama, noAnggota, jenisPerjanjian, tanggal, catatan || null, foto, ipAddress, browserInfo]
        );

        // Log activity
        await pool.execute(
            `INSERT INTO activity_log (action, dokumentasi_id, details) VALUES (?, ?, ?)`,
            ['CREATE', result.insertId, `Created documentation for ${nama} from ${ipAddress}`]
        );

        res.status(201).json({
            success: true,
            message: 'Dokumentasi berhasil disimpan',
            id: result.insertId
        });
    } catch (error) {
        console.error('Error creating documentation:', error);
        res.status(500).json({
            error: 'Failed to create documentation',
            details: error.message
        });
    }
});

// Get all documentation (with optional search)
app.get('/api/dokumentasi', async (req, res) => {
    try {
        const { search } = req.query;

        let query = 'SELECT id, nama, no_anggota as noAnggota, jenis_perjanjian as jenisPerjanjian, tanggal, catatan, foto, timestamp, ip_address as ipAddress, browser_info as browserInfo FROM dokumentasi';
        let params = [];

        if (search) {
            query += ' WHERE nama LIKE ? OR no_anggota LIKE ?';
            params = [`%${search}%`, `%${search}%`];
        }

        query += ' ORDER BY timestamp DESC';

        const [rows] = await pool.execute(query, params);

        res.json({
            success: true,
            count: rows.length,
            data: rows
        });
    } catch (error) {
        console.error('Error fetching documentation:', error);
        res.status(500).json({
            error: 'Failed to fetch documentation',
            details: error.message
        });
    }
});

// Get single documentation by ID
app.get('/api/dokumentasi/:id', async (req, res) => {
    try {
        const { id } = req.params;

        const [rows] = await pool.execute(
            `SELECT id, nama, no_anggota as noAnggota, jenis_perjanjian as jenisPerjanjian, 
                    tanggal, catatan, foto, timestamp, ip_address as ipAddress, browser_info as browserInfo 
             FROM dokumentasi WHERE id = ?`,
            [id]
        );

        if (rows.length === 0) {
            return res.status(404).json({
                error: 'Documentation not found'
            });
        }

        res.json({
            success: true,
            data: rows[0]
        });
    } catch (error) {
        console.error('Error fetching documentation:', error);
        res.status(500).json({
            error: 'Failed to fetch documentation',
            details: error.message
        });
    }
});

// Delete documentation
app.delete('/api/dokumentasi/:id', async (req, res) => {
    try {
        const { id } = req.params;

        // Check if exists
        const [existing] = await pool.execute(
            'SELECT nama FROM dokumentasi WHERE id = ?',
            [id]
        );

        if (existing.length === 0) {
            return res.status(404).json({
                error: 'Documentation not found'
            });
        }

        // Delete (activity_log will cascade delete automatically)
        await pool.execute('DELETE FROM dokumentasi WHERE id = ?', [id]);

        res.json({
            success: true,
            message: 'Dokumentasi berhasil dihapus'
        });
    } catch (error) {
        console.error('Error deleting documentation:', error);
        res.status(500).json({
            error: 'Failed to delete documentation',
            details: error.message
        });
    }
});

// Get statistics
app.get('/api/stats', async (req, res) => {
    try {
        const [countResult] = await pool.execute(
            'SELECT COUNT(*) as total FROM dokumentasi'
        );

        const [recentResult] = await pool.execute(
            'SELECT COUNT(*) as recent FROM dokumentasi WHERE DATE(timestamp) = CURDATE()'
        );

        res.json({
            success: true,
            data: {
                total: countResult[0].total,
                today: recentResult[0].recent
            }
        });
    } catch (error) {
        console.error('Error fetching stats:', error);
        res.status(500).json({
            error: 'Failed to fetch statistics',
            details: error.message
        });
    }
});

// ============================================
// ERROR HANDLING
// ============================================

// 404 handler
app.use((req, res) => {
    res.status(404).json({ error: 'Endpoint not found' });
});

// Global error handler
app.use((err, req, res, next) => {
    console.error('Unhandled error:', err);
    res.status(500).json({
        error: 'Internal server error',
        details: err.message
    });
});

// ============================================
// START SERVER
// ============================================

async function startServer() {
    try {
        await testConnection();

        app.listen(PORT, () => {
            console.log(`\n🚀 Server running on http://localhost:${PORT}`);
            console.log(`📊 API endpoints:`);
            console.log(`   GET    /api/health`);
            console.log(`   POST   /api/dokumentasi`);
            console.log(`   GET    /api/dokumentasi`);
            console.log(`   GET    /api/dokumentasi/:id`);
            console.log(`   DELETE /api/dokumentasi/:id`);
            console.log(`   GET    /api/stats`);
            console.log(`\n✨ Application ready at http://localhost:${PORT}/index.html\n`);
        });
    } catch (error) {
        console.error('Failed to start server:', error);
        process.exit(1);
    }
}

startServer();

// Graceful shutdown
process.on('SIGTERM', async () => {
    console.log('SIGTERM signal received: closing HTTP server');
    await pool.end();
    process.exit(0);
});

process.on('SIGINT', async () => {
    console.log('\nSIGINT signal received: closing HTTP server');
    await pool.end();
    process.exit(0);
});