require('dotenv').config();
const express = require('express');
const mysql = require('mysql2');
const cors = require('cors');
const multer = require('multer');
const path = require('path');
const fs = require('fs');

const app = express();
const port = process.env.PORT || 7860;
const host = '0.0.0.0'; // Listen on all network interfaces

// Hardcoded accounts
const ACCOUNTS = {
    master: {
        username: 'master',
        password: '9935156127',
        role: 'master'
    },
    slave: {
        username: 'sawan',
        password: '8381934081',
        role: 'slave'
    }
};

// Middleware
app.use(cors());
app.use(express.json());
app.use(express.static('public'));
app.use('/uploads', express.static('uploads'));

// Request logging middleware
app.use((req, res, next) => {
    console.log(`\n[${new Date().toISOString()}] ${req.method} ${req.url}`);
    console.log('Headers:', JSON.stringify(req.headers, null, 2));
    if (req.body && Object.keys(req.body).length > 0) {
        console.log('Body:', JSON.stringify(req.body, null, 2));
    }
    if (req.query && Object.keys(req.query).length > 0) {
        console.log('Query:', JSON.stringify(req.query, null, 2));
    }
    next();
});

// Authentication middleware
function authenticate(req, res, next) {
    const { username, password } = req.headers;
    
    console.log(`[Auth] Checking credentials for: ${username}`);
    
    if (!username || !password) {
        console.log('[Auth] Missing credentials');
        return res.status(401).json({ error: 'Missing authentication credentials' });
    }

    const account = Object.values(ACCOUNTS).find(
        acc => acc.username === username && acc.password === password
    );

    if (!account) {
        console.log('[Auth] Invalid credentials');
        return res.status(401).json({ error: 'Invalid credentials' });
    }

    console.log(`[Auth] Authenticated as: ${account.role}`);
    req.user = account;
    next();
}

// Authorization middleware for master-only routes
function requireMaster(req, res, next) {
    if (req.user.role !== 'master') {
        console.log(`[Auth] Access denied. User role: ${req.user.role}`);
        return res.status(403).json({ error: 'Access denied. Master account required.' });
    }
    next();
}

// Database Connection Pool
const db = mysql.createPool({
    host: 'gateway01.eu-central-1.prod.aws.tidbcloud.com',
    user: '2MchL9UQd5gPZWz.root',
    password: '3N5atD6gfsRDZl2t',
    database: 'test',
    port: 4000,
    ssl: {
        rejectUnauthorized: true
    },
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

function initDb() {
    console.log('\n[DB Init] Starting database initialization...');
    
    const createCardholdersTable = `
        CREATE TABLE IF NOT EXISTS cardholders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            phone VARCHAR(20),
            aadhar_card VARCHAR(20),
            aadhar_front_url TEXT,
            aadhar_back_url TEXT,
            nominee_card VARCHAR(20),
            nominee_front_url TEXT,
            nominee_back_url TEXT,
            samriddhi_card VARCHAR(20),
            account_no VARCHAR(50),
            installments_paid INT DEFAULT 0,
            area VARCHAR(100),
            profile_image_url TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `;

    const createAreasTable = `
        CREATE TABLE IF NOT EXISTS areas (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL UNIQUE
        )
    `;

    const createPaymentsTable = `
        CREATE TABLE IF NOT EXISTS payments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            cardholder_id INT NOT NULL,
            amount DECIMAL(10, 2) NOT NULL,
            payment_date DATE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (cardholder_id) REFERENCES cardholders(id) ON DELETE CASCADE
        )
    `;

    db.query(createCardholdersTable, (err) => {
        if (err) console.error('[DB Init] Error creating cardholders table:', err);
        else console.log('[DB Init] Cardholders table ready');
    });

    db.query(createAreasTable, (err) => {
        if (err) console.error('[DB Init] Error creating areas table:', err);
        else console.log('[DB Init] Areas table ready');
    });

    db.query(createPaymentsTable, (err) => {
        if (err) console.error('[DB Init] Error creating payments table:', err);
        else console.log('[DB Init] Payments table ready');
    });
}

// Check connection
db.getConnection((err, connection) => {
    if (err) {
        console.error('[DB Connection] Error connecting to database:', err);
        return;
    }
    console.log('[DB Connection] Connected to TiDB MySQL database via Pool');
    connection.release();
    initDb();
});

// API Endpoints

// Login endpoint (no authentication required)
app.post('/api/login', (req, res) => {
    console.log('\n[POST /api/login] Processing login request...');
    const { username, password } = req.body;

    if (!username || !password) {
        return res.status(400).json({ error: 'Username and password required' });
    }

    const account = Object.values(ACCOUNTS).find(
        acc => acc.username === username && acc.password === password
    );

    if (!account) {
        console.log('[POST /api/login] Invalid credentials');
        return res.status(401).json({ error: 'Invalid credentials' });
    }

    console.log(`[POST /api/login] Login successful for: ${account.role}`);
    res.json({ 
        message: 'Login successful', 
        role: account.role,
        username: account.username
    });
});

// Create Cardholder (Both master and slave can access)
app.post('/api/cardholders', authenticate, (req, res) => {
    console.log('\n[POST /api/cardholders] Processing request...');
    console.log(`[POST /api/cardholders] User role: ${req.user.role}`);
    
    const { 
        name, phone, aadhar, nominee, samriddhi, account, 
        installments, area, profile_image_url,
        aadhar_front_url, aadhar_back_url,
        nominee_front_url, nominee_back_url
    } = req.body;

    // Validate required fields
    if (!name) {
        console.log('[POST /api/cardholders] Validation failed: Name is required');
        return res.status(400).json({ error: 'Name is required' });
    }

    const sql = `INSERT INTO cardholders 
        (name, phone, aadhar_card, aadhar_front_url, aadhar_back_url, 
         nominee_card, nominee_front_url, nominee_back_url, 
         samriddhi_card, account_no, installments_paid, area, profile_image_url) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    
    const values = [
        name, phone, aadhar, aadhar_front_url, aadhar_back_url,
        nominee, nominee_front_url, nominee_back_url,
        samriddhi, account, installments || 0, area, profile_image_url
    ];
    
    console.log('[POST /api/cardholders] Executing SQL:', sql);
    console.log('[POST /api/cardholders] Values:', values);

    db.query(sql, values, (err, result) => {
        if (err) {
            console.error('[POST /api/cardholders] Database error:', err);
            res.status(500).json({ error: 'Database error: ' + err.message });
        } else {
            console.log('[POST /api/cardholders] Success! Inserted ID:', result.insertId);
            res.status(201).json({ message: 'Cardholder added successfully', id: result.insertId });
        }
    });
});

// Get All Cardholders (Master only)
app.get('/api/cardholders', authenticate, requireMaster, (req, res) => {
    console.log('\n[GET /api/cardholders] Processing request...');
    const { area } = req.query;
    let sql = 'SELECT * FROM cardholders';
    let params = [];

    if (area) {
        sql += ' WHERE area = ?';
        params.push(area);
        console.log('[GET /api/cardholders] Filtering by area:', area);
    }

    sql += ' ORDER BY created_at DESC';
    console.log('[GET /api/cardholders] Executing SQL:', sql);

    db.query(sql, params, (err, results) => {
        if (err) {
            console.error('[GET /api/cardholders] Database error:', err);
            res.status(500).json({ error: 'Database error' });
        } else {
            console.log('[GET /api/cardholders] Success! Returned', results.length, 'records');
            res.json(results);
        }
    });
});

// Update Cardholder (Master only)
app.put('/api/cardholders/:id', authenticate, requireMaster, (req, res) => {
    console.log('\n[PUT /api/cardholders/:id] Processing request...');
    const { id } = req.params;
    console.log('[PUT /api/cardholders/:id] Cardholder ID:', id);
    
    const name = req.body.name;
    const phone = req.body.phone;
    const area = req.body.area;
    const profile_image_url = req.body.profile_image_url;
    const aadhar_front_url = req.body.aadhar_front_url;
    const aadhar_back_url = req.body.aadhar_back_url;
    const nominee_front_url = req.body.nominee_front_url;
    const nominee_back_url = req.body.nominee_back_url;

    const aadhar = req.body.aadhar || req.body.aadhar_card;
    const nominee = req.body.nominee || req.body.nominee_card;
    const samriddhi = req.body.samriddhi || req.body.samriddhi_card;
    const account = req.body.account || req.body.account_no;
    const installments = req.body.installments !== undefined ? req.body.installments : req.body.installments_paid;

    const sql = `UPDATE cardholders SET 
        name=?, phone=?, aadhar_card=?, aadhar_front_url=?, aadhar_back_url=?,
        nominee_card=?, nominee_front_url=?, nominee_back_url=?,
        samriddhi_card=?, account_no=?, installments_paid=?, area=?, profile_image_url=? 
        WHERE id=?`;
    
    const values = [
        name, phone, aadhar, aadhar_front_url, aadhar_back_url,
        nominee, nominee_front_url, nominee_back_url,
        samriddhi, account, installments, area, profile_image_url, id
    ];
    
    console.log('[PUT /api/cardholders/:id] Executing SQL:', sql);
    console.log('[PUT /api/cardholders/:id] Values:', values);

    db.query(sql, values, (err, result) => {
        if (err) {
            console.error('[PUT /api/cardholders/:id] Database error:', err);
            res.status(500).json({ error: 'Database error: ' + err.message });
        } else {
            console.log('[PUT /api/cardholders/:id] Success! Affected rows:', result.affectedRows);
            res.json({ message: 'User updated successfully' });
        }
    });
});

// Delete Cardholder (Master only)
app.delete('/api/cardholders/:id', authenticate, requireMaster, (req, res) => {
    console.log('\n[DELETE /api/cardholders/:id] Processing request...');
    const { id } = req.params;
    console.log('[DELETE /api/cardholders/:id] Cardholder ID:', id);
    
    const sql = 'DELETE FROM cardholders WHERE id = ?';
    console.log('[DELETE /api/cardholders/:id] Executing SQL:', sql);
    
    db.query(sql, [id], (err, result) => {
        if (err) {
            console.error('[DELETE /api/cardholders/:id] Database error:', err);
            res.status(500).json({ error: 'Database error' });
        } else {
            console.log('[DELETE /api/cardholders/:id] Success! Affected rows:', result.affectedRows);
            res.json({ message: 'User deleted successfully' });
        }
    });
});

// --- Areas Management ---

// Get Areas (Both master and slave can access)
app.get('/api/areas', authenticate, (req, res) => {
    console.log('\n[GET /api/areas] Processing request...');
    const sql = 'SELECT * FROM areas ORDER BY name ASC';
    console.log('[GET /api/areas] Executing SQL:', sql);
    
    db.query(sql, (err, results) => {
        if (err) {
            console.error('[GET /api/areas] Database error:', err);
            res.status(500).json({ error: 'Database error' });
        } else {
            console.log('[GET /api/areas] Success! Returned', results.length, 'areas');
            res.json(results.map(row => row.name));
        }
    });
});

// Add Area (Both master and slave can access)
app.post('/api/areas', authenticate, (req, res) => {
    console.log('\n[POST /api/areas] Processing request...');
    console.log(`[POST /api/areas] User role: ${req.user.role}`);
    
    const { name } = req.body;
    
    if (!name) {
        console.log('[POST /api/areas] Validation failed: Area name is required');
        return res.status(400).json({ error: 'Area name is required' });
    }

    const sql = 'INSERT INTO areas (name) VALUES (?)';
    console.log('[POST /api/areas] Executing SQL:', sql);
    console.log('[POST /api/areas] Area name:', name);
    
    db.query(sql, [name], (err, result) => {
        if (err) {
            if (err.code === 'ER_DUP_ENTRY') {
                console.log('[POST /api/areas] Duplicate entry:', name);
                return res.status(409).json({ error: 'Area already exists' });
            }
            console.error('[POST /api/areas] Database error:', err);
            res.status(500).json({ error: 'Database error' });
        } else {
            console.log('[POST /api/areas] Success! Inserted ID:', result.insertId);
            res.status(201).json({ message: 'Area added', id: result.insertId });
        }
    });
});

// --- Payments (Master only) ---

app.post('/api/payments', authenticate, requireMaster, (req, res) => {
    console.log('\n[POST /api/payments] Processing request...');
    console.log('[POST /api/payments] Request body:', JSON.stringify(req.body, null, 2));
    
    const { cardholderId, amount, date } = req.body;

    // Detailed validation logging
    if (!cardholderId) {
        console.log('[POST /api/payments] Validation failed: cardholderId is missing');
        return res.status(400).json({ error: 'Missing cardholderId field' });
    }
    if (!amount) {
        console.log('[POST /api/payments] Validation failed: amount is missing');
        return res.status(400).json({ error: 'Missing amount field' });
    }
    if (!date) {
        console.log('[POST /api/payments] Validation failed: date is missing');
        return res.status(400).json({ error: 'Missing date field' });
    }

    console.log('[POST /api/payments] Validated data:', { cardholderId, amount, date });

    const insertPayment = 'INSERT INTO payments (cardholder_id, amount, payment_date) VALUES (?, ?, ?)';
    const updateCardholder = 'UPDATE cardholders SET installments_paid = installments_paid + 1 WHERE id = ?';

    console.log('[POST /api/payments] Getting connection from pool...');
    
    // Get a connection from the pool to use transactions
    db.getConnection((err, connection) => {
        if (err) {
            console.error('[POST /api/payments] Error getting connection:', err);
            return res.status(500).json({ error: 'Connection error: ' + err.message });
        }

        console.log('[POST /api/payments] Connection acquired. Starting transaction...');
        
        connection.beginTransaction(err => {
            if (err) {
                console.error('[POST /api/payments] Transaction begin error:', err);
                connection.release();
                return res.status(500).json({ error: 'Transaction error: ' + err.message });
            }

            console.log('[POST /api/payments] Inserting payment record...');
            console.log('[POST /api/payments] SQL:', insertPayment);
            console.log('[POST /api/payments] Values:', [cardholderId, amount, date]);

            connection.query(insertPayment, [cardholderId, amount, date], (err, result) => {
                if (err) {
                    console.error('[POST /api/payments] Error inserting payment:', err);
                    return connection.rollback(() => {
                        console.log('[POST /api/payments] Transaction rolled back');
                        connection.release();
                        res.status(500).json({ error: 'Database error inserting payment: ' + err.message });
                    });
                }

                console.log('[POST /api/payments] Payment inserted successfully. ID:', result.insertId);
                console.log('[POST /api/payments] Updating cardholder installment count...');
                console.log('[POST /api/payments] SQL:', updateCardholder);
                console.log('[POST /api/payments] Cardholder ID:', cardholderId);

                connection.query(updateCardholder, [cardholderId], (err, result) => {
                    if (err) {
                        console.error('[POST /api/payments] Error updating installment count:', err);
                        return connection.rollback(() => {
                            console.log('[POST /api/payments] Transaction rolled back');
                            connection.release();
                            res.status(500).json({ error: 'Database error updating installments: ' + err.message });
                        });
                    }

                    console.log('[POST /api/payments] Installment count updated. Affected rows:', result.affectedRows);
                    console.log('[POST /api/payments] Committing transaction...');

                    connection.commit(err => {
                        if (err) {
                            console.error('[POST /api/payments] Commit error:', err);
                            return connection.rollback(() => {
                                console.log('[POST /api/payments] Transaction rolled back');
                                connection.release();
                                res.status(500).json({ error: 'Commit error: ' + err.message });
                            });
                        }
                        console.log('[POST /api/payments] Transaction committed successfully!');
                        connection.release();
                        console.log('[POST /api/payments] Connection released back to pool');
                        res.status(201).json({ message: 'Payment recorded and installment added' });
                    });
                });
            });
        });
    });
});

// Get Payments for Cardholder (Master only)
app.get('/api/payments/:cardholderId', authenticate, requireMaster, (req, res) => {
    console.log('\n[GET /api/payments/:cardholderId] Processing request...');
    const { cardholderId } = req.params;
    console.log('[GET /api/payments/:cardholderId] Cardholder ID:', cardholderId);
    
    const sql = 'SELECT * FROM payments WHERE cardholder_id = ? ORDER BY payment_date DESC, created_at DESC';
    console.log('[GET /api/payments/:cardholderId] Executing SQL:', sql);

    db.query(sql, [cardholderId], (err, results) => {
        if (err) {
            console.error('[GET /api/payments/:cardholderId] Database error:', err);
            res.status(500).json({ error: 'Database error' });
        } else {
            console.log('[GET /api/payments/:cardholderId] Success! Returned', results.length, 'payments');
            res.json(results);
        }
    });
});

// --- System Reset (Master only) ---

app.post('/api/admin/flush', authenticate, requireMaster, (req, res) => {
    console.log('\n[POST /api/admin/flush] ⚠️  WARNING: System flush requested!');
    
    const dropPayments = 'DROP TABLE IF EXISTS payments';
    const dropCardholders = 'DROP TABLE IF EXISTS cardholders';
    const dropAreas = 'DROP TABLE IF EXISTS areas';

    console.log('[POST /api/admin/flush] Dropping payments table...');
    db.query(dropPayments, (err) => {
        if (err) console.error('[POST /api/admin/flush] Error dropping payments:', err);
        else console.log('[POST /api/admin/flush] Payments table dropped');

        console.log('[POST /api/admin/flush] Dropping cardholders table...');
        db.query(dropCardholders, (err) => {
            if (err) {
                console.error('[POST /api/admin/flush] Error dropping cardholders:', err);
                return res.status(500).json({ error: 'Failed to drop cardholders table' });
            }
            console.log('[POST /api/admin/flush] Cardholders table dropped');
            
            console.log('[POST /api/admin/flush] Dropping areas table...');
            db.query(dropAreas, (err) => {
                if (err) {
                    console.error('[POST /api/admin/flush] Error dropping areas:', err);
                    return res.status(500).json({ error: 'Failed to drop areas table' });
                }
                console.log('[POST /api/admin/flush] Areas table dropped');

                console.log('[POST /api/admin/flush] Reinitializing database...');
                initDb();
                console.log('[POST /api/admin/flush] ✅ System flush completed successfully');
                res.json({ message: 'System flushed and reset successfully' });
            });
        });
    });
});

// 404 handler
app.use((req, res) => {
    console.log(`\n[404] Route not found: ${req.method} ${req.url}`);
    res.status(404).json({ error: 'Route not found' });
});

// Error handler
app.use((err, req, res, next) => {
    console.error('\n[Error Handler] Unhandled error:', err);
    res.status(500).json({ error: 'Internal server error: ' + err.message });
});

// Start server on 0.0.0.0 to allow external connections
app.listen(port, host, () => {
    console.log('\n========================================');
    console.log('🚀 Server Started Successfully!');
    console.log('========================================');
    console.log(`Server running at http://${host}:${port}`);
    console.log(`Accessible locally at http://localhost:${port}`);
    console.log(`Accessible on network at http://<your-ip>:${port}`);
    console.log('========================================');
    console.log('\n📋 Account Information:');
    console.log('Master Account - Username: master, Password: 9935156127');
    console.log('Slave Account - Username: sawan, Password: 8381934081');
    console.log('========================================\n');
});