const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const cors = require('cors');
const bodyParser = require('body-parser');
const fs = require('fs');
const path = require('path');
const bcrypt = require('bcryptjs');

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

// Middleware
app.use(cors());
app.use(bodyParser.json());
app.use(express.static('../')); // Serve frontend files from parent directory

// Initialize database
const db = new sqlite3.Database('inventory.db', (err) => {
    if (err) {
        console.error('Error opening database:', err);
    } else {
        console.log('Connected to SQLite database');
        // Initialize schema
        const schema = fs.readFileSync(path.join(__dirname, 'schema.sql'), 'utf8');
        db.exec(schema, (err) => {
            if (err) {
                console.error('Error initializing schema:', err);
            } else {
                console.log('Database initialized successfully');
                initializeAdmin();
            }
        });
    }
});

// Seed Admin User
function initializeAdmin() {
    const adminUsername = 'admin';
    const adminPassword = 'saadislamabad'; // As per requirements

    db.get('SELECT * FROM users WHERE username = ?', [adminUsername], (err, row) => {
        if (err) return console.error('Error checking admin:', err);

        if (!row) {
            console.log('Creating default admin user...');
            const hashedPassword = bcrypt.hashSync(adminPassword, 10);
            const permissions = JSON.stringify(['*']); // All permissions

            db.run('INSERT INTO users (username, password, role, permissions, status) VALUES (?, ?, ?, ?, ?)',
                [adminUsername, hashedPassword, 'Admin', permissions, 'Active'],
                (err) => {
                    if (err) console.error('Error creating admin:', err);
                    else console.log('Default admin created successfully');
                }
            );
        }
    });
}

// ============================================
// AUTH API
// ============================================
app.post('/api/login', (req, res) => {
    const { username, password } = req.body;

    db.get('SELECT * FROM users WHERE username = ?', [username], (err, user) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!user) return res.status(401).json({ error: 'Invalid credentials' });

        const validPassword = bcrypt.compareSync(password, user.password);
        if (!validPassword) return res.status(401).json({ error: 'Invalid credentials' });

        // Check if user account is disabled
        if (user.status === 'Disabled') {
            return res.status(403).json({ error: 'Account disabled. Please contact administrator.' });
        }

        // Return user info without password
        const { password: _, ...userWithoutPassword } = user;
        res.json(userWithoutPassword);
    });
});

app.get('/api/users/public', (req, res) => {
    // Return list of users for login dropdown (id, username, role only)
    db.all('SELECT id, username, role FROM users ORDER BY username', [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.get('/api/users/status/:username', (req, res) => {
    const { username } = req.params;
    db.get('SELECT status FROM users WHERE username = ?', [username], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!row) return res.status(404).json({ error: 'User not found' });
        res.json({ status: row.status });
    });
});

app.post('/api/users', (req, res) => {
    const { username, password, role, permissions } = req.body;

    // Define default permissions based on role if not provided
    let finalPermissions = permissions;
    if (!finalPermissions || finalPermissions.length === 0) {
        switch (role) {
            case 'Admin':
                finalPermissions = ['*'];
                break;
            case 'Manager':
                finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'invoices.html', 'returns.html', 'credit.html', 'expenses.html'];
                break;
            case 'Distributor':
                finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'invoices.html', 'returns.html', 'credit.html'];
                break;
            case 'Cashier':
                finalPermissions = ['dashboard.html', 'billing.html', 'invoices.html', 'credit.html'];
                break;
            case 'Staff':
                finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'credit.html'];
                break;
            default:
                finalPermissions = ['dashboard.html'];
        }
    }

    // Hash password
    const hashedPassword = bcrypt.hashSync(password, 10);
    const permissionsStr = JSON.stringify(finalPermissions);

    db.run('INSERT INTO users (username, password, role, permissions, status) VALUES (?, ?, ?, ?, ?)',
        [username, hashedPassword, role, permissionsStr, 'Active'],
        function (err) {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ id: this.lastID, success: true });
        }
    );
});

app.get('/api/users', (req, res) => {
    db.all('SELECT id, username, role, permissions, status, created_at FROM users ORDER BY created_at DESC', [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

// Get user permissions
app.get('/api/user-permissions', (req, res) => {
    // This endpoint can be used to get current user's permissions
    // For now, it's just a placeholder since permissions are stored in localStorage after login
    res.json({ message: 'Permissions available in user session' });
});

app.put('/api/users/:id', (req, res) => {
    const userId = req.params.id;
    const { username, password, role, permissions, status } = req.body;

    // Get current user data to prevent demoting self
    db.get('SELECT id, role FROM users WHERE id = ?', [userId], (err, currentUser) => {
        if (err) return res.status(500).json({ error: err.message });

        db.serialize(() => {
            // Build update query dynamically or just handling password optionality
            if (password && password.trim() !== '') {
                const hashedPassword = bcrypt.hashSync(password, 10);

                // If changing role to Admin, ensure proper permissions
                let finalPermissions = permissions;
                if (role === 'Admin') {
                    finalPermissions = ['*'];
                } else if (!finalPermissions || finalPermissions.length === 0) {
                    // Use default permissions based on role if not provided
                    switch (role) {
                        case 'Manager':
                            finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'invoices.html', 'returns.html', 'credit.html', 'expenses.html'];
                            break;
                        case 'Distributor':
                            finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'invoices.html', 'returns.html'];
                            break;
                        case 'Cashier':
                            finalPermissions = ['dashboard.html', 'billing.html', 'invoices.html'];
                            break;
                        case 'Staff':
                            finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html'];
                            break;
                        default:
                            finalPermissions = ['dashboard.html'];
                    }
                }

                db.run('UPDATE users SET username = ?, password = ?, role = ?, permissions = ?, status = ? WHERE id = ?',
                    [username, hashedPassword, role, JSON.stringify(finalPermissions), status, userId],
                    (err) => {
                        if (err) return res.status(500).json({ error: err.message });
                        res.json({ success: true });
                    }
                );
            } else {
                // If changing role to Admin, ensure proper permissions
                let finalPermissions = permissions;
                if (role === 'Admin') {
                    finalPermissions = ['*'];
                } else if (!finalPermissions || finalPermissions.length === 0) {
                    // Use default permissions based on role if not provided
                    switch (role) {
                        case 'Manager':
                            finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'invoices.html', 'returns.html', 'credit.html', 'expenses.html'];
                            break;
                        case 'Distributor':
                            finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html', 'invoices.html', 'returns.html'];
                            break;
                        case 'Cashier':
                            finalPermissions = ['dashboard.html', 'billing.html', 'invoices.html'];
                            break;
                        case 'Staff':
                            finalPermissions = ['dashboard.html', 'inventory.html', 'billing.html'];
                            break;
                        default:
                            finalPermissions = ['dashboard.html'];
                    }
                }

                db.run('UPDATE users SET username = ?, role = ?, permissions = ?, status = ? WHERE id = ?',
                    [username, role, JSON.stringify(finalPermissions), status, userId],
                    (err) => {
                        if (err) return res.status(500).json({ error: err.message });
                        res.json({ success: true });
                    }
                );
            }
        });
    });
});

app.delete('/api/users/:id', (req, res) => {
    const userId = req.params.id;

    // Prevent deleting the last admin or self (optional, but good practice)
    // For now, simple delete
    db.run('DELETE FROM users WHERE id = ?', [userId], (err) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true });
    });
});

// ============================================
// DASHBOARD API
// ============================================
app.get('/api/dashboard', (req, res) => {
    db.serialize(() => {
        // Get today's date
        const today = new Date().toISOString().split('T')[0];

        // Get inventory value (sum of current_stock * price for all SKUs)
        db.get(`
            SELECT SUM(current_stock * price) as inventory_value 
            FROM skus
        `, [], (err, inventoryRow) => {
            if (err) return res.status(500).json({ error: err.message });

            // Get today's sales total and count
            db.get(`
                SELECT 
                    COALESCE(SUM(total_amount), 0) as today_sales_total,
                    COUNT(*) as today_sales_count
                FROM transactions 
                WHERE transaction_date = ?
            `, [today], (err, salesRow) => {
                if (err) return res.status(500).json({ error: err.message });

                // Get outstanding credit (sum of customer outstanding balances)
                db.get(`
                    SELECT COALESCE(SUM(total_outstanding), 0) as outstanding_credit
                    FROM customers
                `, [], (err, creditRow) => {
                    if (err) return res.status(500).json({ error: err.message });

                    // Get today's expenses
                    db.get(`
                        SELECT COALESCE(SUM(amount), 0) as today_expenses
                        FROM expenses
                        WHERE expense_date = ?
                    `, [today], (err, expenseRow) => {
                        if (err) return res.status(500).json({ error: err.message });

                        res.json({
                            inventory_value: inventoryRow.inventory_value || 0,
                            today_sales_total: salesRow.today_sales_total,
                            today_sales_count: salesRow.today_sales_count,
                            outstanding_credit: creditRow.outstanding_credit,
                            today_expenses: expenseRow.today_expenses
                        });
                    });
                });
            });
        });
    });
});

// ============================================
// PRODUCTS API
// ============================================
app.get('/api/products', (req, res) => {
    db.all('SELECT * FROM products ORDER BY name', [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.post('/api/products', (req, res) => {
    const { name, description } = req.body;

    // Validate input
    if (!name || name.trim() === '') {
        return res.status(400).json({ error: 'Product name is required' });
    }

    if (name.length > 50) {
        return res.status(400).json({ error: 'Product name must be 50 characters or less' });
    }

    db.run('INSERT INTO products (name, description) VALUES (?, ?)', [name.trim(), description || ''],
        function (err) {
            if (err) {
                // Handle duplicate name error
                if (err.message.includes('UNIQUE constraint failed')) {
                    return res.status(400).json({ error: 'Product name already exists' });
                }
                return res.status(500).json({ error: err.message });
            }
            res.json({ id: this.lastID, name: name.trim(), description: description || '' });
        }
    );
});

// ============================================
// SKU API
// ============================================
app.get('/api/skus', (req, res) => {
    const query = `
        SELECT s.*, p.name as product_name
        FROM skus s
        JOIN products p ON s.product_id = p.id
        ORDER BY p.name, s.flavour, s.weight
    `;
    db.all(query, [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.get('/api/skus/:id', (req, res) => {
    const query = `
        SELECT s.*, p.name as product_name
        FROM skus s
        JOIN products p ON s.product_id = p.id
        WHERE s.id = ?
    `;
    db.get(query, [req.params.id], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(row);
    });
});

app.post('/api/skus', (req, res) => {
    const { sku_code, product_id, flavour, weight, current_stock, price, tag } = req.body;
    const query = `
        INSERT INTO skus (sku_code, product_id, flavour, weight, current_stock, price, tag)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    `;
    db.run(query, [sku_code, product_id, flavour, weight, current_stock, price, tag || ''], function (err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ id: this.lastID, ...req.body });
    });
});

app.put('/api/skus/:id', (req, res) => {
    const { current_stock, price, tag } = req.body;
    db.run('UPDATE skus SET current_stock = ?, price = ?, tag = ? WHERE id = ?', [current_stock, price, tag || '', req.params.id], (err) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true });
    });
});

app.delete('/api/skus/:id', (req, res) => {
    // Check if used in transactions or returns first could be good, but for now simple delete
    // In a real app we might soft-delete or check constraints
    db.run('DELETE FROM skus WHERE id = ?', [req.params.id], (err) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ success: true });
    });
});

// ============================================
// TRANSACTIONS API (Billing/Sales)
// ============================================
app.post('/api/transactions', (req, res) => {
    const { transaction_date, payment_type, customer_name, items, discount_percentage, discount_amount, amount_paid } = req.body;

    // Calculate total
    let total_amount = 0;
    items.forEach(item => {
        total_amount += item.quantity * item.unit_price;
    });

    // Apply discount - only manual discount allowed (no automatic discount)
    let final_total = total_amount;
    let applied_discount_percentage = discount_percentage || 0;
    let applied_discount_amount = discount_amount || 0;

    // Only apply discount if it was manually entered
    if (applied_discount_percentage > 0 || applied_discount_amount > 0) {
        final_total = total_amount - applied_discount_amount;
    }

    // For cash-only flow, ensure payment type is always 'Cash'
    const effective_payment_type = 'Cash';

    db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // Insert transaction
        const transQuery = `
            INSERT INTO transactions (transaction_date, payment_type, customer_name, total_amount, discount_percentage, discount_amount, amount_paid)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        `;
        db.run(transQuery, [transaction_date, 'Cash', customer_name, total_amount, applied_discount_percentage, applied_discount_amount, amount_paid || final_total], function (err) {
            if (err) {
                db.run('ROLLBACK');
                return res.status(500).json({ error: err.message });
            }

            const transactionId = this.lastID;
            let completed = 0;

            items.forEach(item => {
                // Insert transaction item
                const itemQuery = `
                    INSERT INTO transaction_items (transaction_id, sku_id, quantity, unit_price, subtotal)
                    VALUES (?, ?, ?, ?, ?)
                `;
                const subtotal = item.quantity * item.unit_price;
                db.run(itemQuery, [transactionId, item.sku_id, item.quantity, item.unit_price, subtotal], (err) => {
                    if (err) {
                        db.run('ROLLBACK');
                        return res.status(500).json({ error: err.message });
                    }

                    // Update stock
                    db.run('UPDATE skus SET current_stock = current_stock - ? WHERE id = ?', [item.quantity, item.sku_id], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: err.message });
                        }

                        completed++;
                        if (completed === items.length) {
                            // Cash payment logic - move this inside the completion check
                            const finalizeCash = () => {
                                db.run('COMMIT', (err) => {
                                    if (err) {
                                        return res.status(500).json({ error: 'Commit failed: ' + err.message });
                                    }
                                    try {
                                        updateMonthlySummary(final_total);
                                    } catch (summaryErr) {
                                        console.error('Summary update failed:', summaryErr);
                                    }

                                    res.json({
                                        id: transactionId,
                                        total_amount,
                                        final_total,
                                        discount_percentage: applied_discount_percentage,
                                        discount_amount: applied_discount_amount,
                                        amount_paid: amount_paid || final_total,
                                        success: true
                                    });
                                });
                            };

                            // Call finalize for cash payments
                            finalizeCash();
                        }
                    });
                });
            });
        });
    });
});


app.get('/api/transactions', (req, res) => {
    const { start_date, end_date } = req.query;
    let query = 'SELECT * FROM transactions WHERE 1=1';
    const params = [];

    if (start_date) {
        query += ' AND transaction_date >= ?';
        params.push(start_date);
    }
    if (end_date) {
        query += ' AND transaction_date <= ?';
        params.push(end_date);
    }

    query += ' ORDER BY transaction_date DESC, id DESC';
    db.all(query, params, (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.get('/api/transactions/:id', (req, res) => {
    db.get('SELECT * FROM transactions WHERE id = ?', [req.params.id], (err, transaction) => {
        if (err) return res.status(500).json({ error: err.message });

        const itemsQuery = `
            SELECT ti.*, s.sku_code, s.flavour, s.weight, p.name as product_name
            FROM transaction_items ti
            JOIN skus s ON ti.sku_id = s.id
            JOIN products p ON s.product_id = p.id
            WHERE ti.transaction_id = ?
        `;
        db.all(itemsQuery, [req.params.id], (err, items) => {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ ...transaction, items });
        });
    });
});

app.delete('/api/transactions/:id', (req, res) => {
    const transactionId = req.params.id;

    // 1. Get items to restore stock
    db.all('SELECT * FROM transaction_items WHERE transaction_id = ?', [transactionId], (err, items) => {
        if (err) return res.status(500).json({ error: err.message });

        db.serialize(() => {
            db.run('BEGIN TRANSACTION');

            // 2. Restore stock
            items.forEach(item => {
                db.run('UPDATE skus SET current_stock = current_stock + ? WHERE id = ?', [item.quantity, item.sku_id]);
            });

            // 3. Delete items
            db.run('DELETE FROM transaction_items WHERE transaction_id = ?', [transactionId]);

            // 4. Delete transaction
            db.run('DELETE FROM transactions WHERE id = ?', [transactionId], (err) => {
                if (err) {
                    db.run('ROLLBACK');
                    return res.status(500).json({ error: err.message });
                }
                db.run('COMMIT');
                res.json({ success: true });
            });
        });
    });
});

// ============================================
// RETURNS API
// ============================================
app.get('/api/returns', (req, res) => {
    const { start_date, end_date } = req.query;
    let query = 'SELECT * FROM returns WHERE 1=1';
    const params = [];

    if (start_date) {
        query += ' AND return_date >= ?';
        params.push(start_date);
    }
    if (end_date) {
        query += ' AND return_date <= ?';
        params.push(end_date);
    }

    query += ' ORDER BY return_date DESC, id DESC';
    db.all(query, params, (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });

        // If no returns, return empty array
        if (rows.length === 0) return res.json([]);

        // Fetch items for these returns
        const returnIds = rows.map(r => r.id);
        const placeholders = returnIds.map(() => '?').join(',');
        const itemsQuery = `
            SELECT ri.*, s.sku_code, s.flavour, s.weight, p.name as product_name
            FROM return_items ri
            JOIN skus s ON ri.sku_id = s.id
            JOIN products p ON s.product_id = p.id
            WHERE ri.return_id IN (${placeholders})
        `;

        db.all(itemsQuery, returnIds, (err, items) => {
            if (err) return res.status(500).json({ error: err.message });

            // Attach items to returns
            const returnsWithItems = rows.map(r => ({
                ...r,
                items: items.filter(i => i.return_id === r.id)
            }));

            res.json(returnsWithItems);
        });
    });
});

app.get('/api/damaged-items', (req, res) => {
    const query = `
        SELECT r.*, ri.quantity, ri.sku_id, s.sku_code, s.flavour, s.weight, p.name as product_name
        FROM returns r
        JOIN return_items ri ON r.id = ri.return_id
        JOIN skus s ON ri.sku_id = s.id
        JOIN products p ON s.product_id = p.id
        WHERE r.return_type = 'Damaged'
        ORDER BY r.return_date DESC
    `;
    db.all(query, [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.post('/api/returns', (req, res) => {
    const { return_date, original_transaction_id, customer_name, reason, return_type, items } = req.body;

    const type = return_type || 'Normal'; // Default to Normal if not specified

    db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // Insert return
        const returnQuery = `
            INSERT INTO returns (return_date, original_transaction_id, customer_name, reason, return_type)
            VALUES (?, ?, ?, ?, ?)
        `;
        db.run(returnQuery, [return_date, original_transaction_id, customer_name, reason, type], function (err) {
            if (err) {
                db.run('ROLLBACK');
                return res.status(500).json({ error: err.message });
            }

            const returnId = this.lastID;
            let completed = 0;

            items.forEach(item => {
                // Insert return item
                db.run('INSERT INTO return_items (return_id, sku_id, quantity) VALUES (?, ?, ?)',
                    [returnId, item.sku_id, item.quantity], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: err.message });
                        }

                        if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: err.message });
                        }

                        // Update stock ONLY if it's a Normal return
                        if (type === 'Normal') {
                            db.run('UPDATE skus SET current_stock = current_stock + ? WHERE id = ?',
                                [item.quantity, item.sku_id], (err) => {
                                    if (err) {
                                        db.run('ROLLBACK');
                                        return res.status(500).json({ error: err.message });
                                    }

                                    completed++;
                                    if (completed === items.length) {
                                        db.run('COMMIT');
                                        res.json({ id: returnId, success: true });
                                    }
                                });
                        } else {
                            // For Damaged items, we don't update stock
                            completed++;
                            if (completed === items.length) {
                                db.run('COMMIT');
                                res.json({ id: returnId, success: true });
                            }
                        }
                    });
            });
        });
    });
});

// ============================================
// UPDATE RETURN API
// ============================================
app.put('/api/returns/:id', (req, res) => {
    const { return_date, customer_name, reason, return_type, items } = req.body;
    const returnId = req.params.id;

    db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // Get old return data to calculate stock adjustments
        db.get('SELECT return_type FROM returns WHERE id = ?', [returnId], (err, oldReturn) => {
            if (err) {
                db.run('ROLLBACK');
                return res.status(500).json({ error: err.message });
            }

            // Get old return items
            db.all('SELECT sku_id, quantity FROM return_items WHERE return_id = ?', [returnId], (err, oldItems) => {
                if (err) {
                    db.run('ROLLBACK');
                    return res.status(500).json({ error: err.message });
                }

                // Reverse old stock adjustments (if it was Normal return)
                if (oldReturn.return_type === 'Normal') {
                    oldItems.forEach(item => {
                        db.run('UPDATE skus SET current_stock = current_stock - ? WHERE id = ?', [item.quantity, item.sku_id]);
                    });
                }

                // Update return record
                db.run('UPDATE returns SET return_date = ?, customer_name = ?, reason = ?, return_type = ? WHERE id = ?',
                    [return_date, customer_name, reason, return_type, returnId], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: err.message });
                        }

                        // Delete old return items
                        db.run('DELETE FROM return_items WHERE return_id = ?', [returnId], (err) => {
                            if (err) {
                                db.run('ROLLBACK');
                                return res.status(500).json({ error: err.message });
                            }

                            // Insert new return items
                            let completed = 0;
                            items.forEach(item => {
                                db.run('INSERT INTO return_items (return_id, sku_id, quantity) VALUES (?, ?, ?)',
                                    [returnId, item.sku_id, item.quantity], (err) => {
                                        if (err) {
                                            db.run('ROLLBACK');
                                            return res.status(500).json({ error: err.message });
                                        }

                                        // Apply new stock adjustments (if Normal return)
                                        if (return_type === 'Normal') {
                                            db.run('UPDATE skus SET current_stock = current_stock + ? WHERE id = ?',
                                                [item.quantity, item.sku_id], (err) => {
                                                    if (err) {
                                                        db.run('ROLLBACK');
                                                        return res.status(500).json({ error: err.message });
                                                    }

                                                    completed++;
                                                    if (completed === items.length) {
                                                        db.run('COMMIT');
                                                        res.json({ success: true });
                                                    }
                                                });
                                        } else {
                                            // Damaged - no stock adjustment
                                            completed++;
                                            if (completed === items.length) {
                                                db.run('COMMIT');
                                                res.json({ success: true });
                                            }
                                        }
                                    });
                            });
                        });
                    });
            });
        });
    });
});

app.delete('/api/returns/:id', (req, res) => {
    const returnId = req.params.id;

    db.get('SELECT * FROM returns WHERE id = ?', [returnId], (err, returnRecord) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!returnRecord) return res.status(404).json({ error: 'Return not found' });

        db.all('SELECT * FROM return_items WHERE return_id = ?', [returnId], (err, items) => {
            if (err) return res.status(500).json({ error: err.message });

            db.serialize(() => {
                db.run('BEGIN TRANSACTION');

                // If it was a Normal return, it ADDED to stock. So we must SUBTRACT to reverse it.
                // If it was Damaged, it didn't affect stock (kept it away). So deleting record just removes record.
                if (returnRecord.return_type === 'Normal') {
                    items.forEach(item => {
                        db.run('UPDATE skus SET current_stock = current_stock - ? WHERE id = ?', [item.quantity, item.sku_id]);
                    });
                }

                db.run('DELETE FROM return_items WHERE return_id = ?', [returnId]);
                db.run('DELETE FROM returns WHERE id = ?', [returnId], (err) => {
                    if (err) {
                        db.run('ROLLBACK');
                        return res.status(500).json({ error: err.message });
                    }
                    db.run('COMMIT');
                    res.json({ success: true });
                });
            });
        });
    });
});

// ============================================
// EXPENSES API
// ============================================
app.get('/api/expenses', (req, res) => {
    const { start_date, end_date, category } = req.query;
    let query = 'SELECT * FROM expenses WHERE 1=1';
    const params = [];

    if (start_date) {
        query += ' AND expense_date >= ?';
        params.push(start_date);
    }
    if (end_date) {
        query += ' AND expense_date <= ?';
        params.push(end_date);
    }
    if (category) {
        query += ' AND category = ?';
        params.push(category);
    }

    query += ' ORDER BY expense_date DESC, id DESC';
    db.all(query, params, (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.post('/api/expenses', (req, res) => {
    const { expense_date, description, category, amount } = req.body;
    const query = `
        INSERT INTO expenses (expense_date, description, category, amount)
        VALUES (?, ?, ?, ?)
    `;
    db.run(query, [expense_date, description, category, amount], function (err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ id: this.lastID, success: true });
    });
});

app.delete('/api/expenses/:id', (req, res) => {
    const expenseId = req.params.id;
    db.run('DELETE FROM expenses WHERE id = ?', [expenseId], function (err) {
        if (err) return res.status(500).json({ error: err.message });
        if (this.changes === 0) return res.status(404).json({ error: 'Expense not found' });
        res.json({ success: true });
    });
});

// ============================================
// CUSTOMER PAYMENTS API (for customer-level credit payments)
// ============================================
app.post('/api/customer-payments', (req, res) => {
    const { customer_id, payment_date, amount_paid, notes } = req.body;

    if (!customer_id || !payment_date || !amount_paid) {
        return res.status(400).json({ error: 'Customer ID, payment date, and amount are required' });
    }

    db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // Insert credit payment record first
        const paymentQuery = `
            INSERT INTO credit_payments (transaction_id, customer_id, payment_date, amount_paid, notes)
            VALUES (NULL, ?, ?, ?, ?)
        `;
        db.run(paymentQuery, [customer_id, payment_date, amount_paid, notes || 'Credit payment'], function (err) {
            if (err) {
                db.run('ROLLBACK');
                return res.status(500).json({ error: err.message });
            }

            const paymentId = this.lastID;

            // Then update customer's outstanding amount
            // First, get current outstanding to calculate new value
            db.get('SELECT total_outstanding FROM customers WHERE id = ?', [customer_id], (err, customer) => {
                if (err) {
                    db.run('ROLLBACK');
                    return res.status(500).json({ error: err.message });
                }

                if (!customer) {
                    db.run('ROLLBACK');
                    return res.status(404).json({ error: 'Customer not found' });
                }

                // Calculate new outstanding (ensure it doesn't go below 0)
                const newOutstanding = Math.max(0, customer.total_outstanding - amount_paid);

                // Update customer's outstanding amount
                db.run('UPDATE customers SET total_outstanding = ? WHERE id = ?',
                    [newOutstanding, customer_id], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: err.message });
                        }

                        db.run('COMMIT');
                        res.json({ id: paymentId, success: true });
                    });
            });
        });
    });
});

// ============================================
// CREDIT PAYMENTS API (transaction-based, kept for backward compatibility)
// ============================================
app.get('/api/credit-payments', (req, res) => {
    const { start_date, end_date, customer_name } = req.query;
    let query = `
        SELECT cp.*, c.name as customer_name, t.total_amount, t.transaction_date, t.customer_name as transaction_customer_name
        FROM credit_payments cp
        LEFT JOIN customers c ON cp.customer_id = c.id
        LEFT JOIN transactions t ON cp.transaction_id = t.id
        WHERE 1=1
    `;
    const params = [];

    if (start_date) {
        query += ' AND cp.payment_date >= ?';
        params.push(start_date);
    }
    if (end_date) {
        query += ' AND cp.payment_date <= ?';
        params.push(end_date);
    }

    query += ' ORDER BY cp.payment_date DESC, cp.id DESC';

    db.all(query, params, (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

app.post('/api/credit-payments', (req, res) => {
    const { transaction_id, customer_name, payment_date, amount_paid, notes } = req.body;

    // Get transaction details to get customer name if not provided
    db.get('SELECT customer_name FROM transactions WHERE id = ?', [transaction_id], (err, transaction) => {
        if (err) return res.status(500).json({ error: err.message });

        const actual_customer_name = customer_name || transaction.customer_name;

        db.serialize(() => {
            db.run('BEGIN TRANSACTION');

            if (actual_customer_name && actual_customer_name !== 'Walk-in Customer') {
                // Insert credit payment record with customer_id lookup
                const query = `
                    INSERT INTO credit_payments (transaction_id, customer_id, payment_date, amount_paid, notes)
                    VALUES (?, (SELECT id FROM customers WHERE name = ?), ?, ?, ?)
                `;
                db.run(query, [transaction_id, actual_customer_name, payment_date, amount_paid, notes], function (err) {
                    if (err) {
                        db.run('ROLLBACK');
                        return res.status(500).json({ error: err.message });
                    }

                    const paymentId = this.lastID;

                    // Update customer's outstanding amount
                    db.run('UPDATE customers SET total_outstanding = total_outstanding - ? WHERE name = ?', [amount_paid, actual_customer_name], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: err.message });
                        }

                        db.run('COMMIT');
                        res.json({ id: paymentId, success: true });
                    });
                });
            } else {
                // For walk-in customers or when no customer name, insert payment without customer_id
                const query = `
                    INSERT INTO credit_payments (transaction_id, customer_id, payment_date, amount_paid, notes)
                    VALUES (?, NULL, ?, ?, ?)
                `;
                db.run(query, [transaction_id, payment_date, amount_paid, notes], function (err) {
                    if (err) {
                        db.run('ROLLBACK');
                        return res.status(500).json({ error: err.message });
                    }

                    const paymentId = this.lastID;

                    // For walk-in customers, don't update customer outstanding
                    db.run('COMMIT');
                    res.json({ id: paymentId, success: true });
                });
            }
        });
    });
});

// Helper function to get current month-year
function getCurrentMonthYear() {
    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, '0'); // Month is 0-indexed
    return `${year}-${month}`;
}

// Function to initialize monthly summary for current month if it doesn't exist
function initializeMonthlySummary() {
    const monthYear = getCurrentMonthYear();

    db.get('SELECT * FROM monthly_summary WHERE month_year = ?', [monthYear], (err, row) => {
        if (err) {
            console.error('Error checking monthly summary:', err);
            return;
        }

        if (!row) {
            // Initialize current month's summary
            db.run('INSERT OR IGNORE INTO monthly_summary (month_year, total_sales, total_transactions) VALUES (?, 0, 0)', [monthYear], (err) => {
                if (err) {
                    console.error('Error initializing monthly summary:', err);
                } else {
                    console.log(`Initialized monthly summary for ${monthYear}`);
                }
            });
        }
    });
}

// Function to update monthly summary when a transaction occurs
function updateMonthlySummary(transactionAmount) {
    const monthYear = getCurrentMonthYear();

    db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // Get current summary
        db.get('SELECT total_sales, total_transactions FROM monthly_summary WHERE month_year = ?', [monthYear], (err, row) => {
            if (err) {
                db.run('ROLLBACK');
                console.error('Error getting monthly summary:', err);
                return;
            }

            if (row) {
                // Update existing summary
                const newTotalSales = row.total_sales + transactionAmount;
                const newTotalTransactions = row.total_transactions + 1;

                db.run('UPDATE monthly_summary SET total_sales = ?, total_transactions = ? WHERE month_year = ?',
                    [newTotalSales, newTotalTransactions, monthYear], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            console.error('Error updating monthly summary:', err);
                        } else {
                            db.run('COMMIT');
                        }
                    });
            } else {
                // Create new summary for the month
                db.run('INSERT OR IGNORE INTO monthly_summary (month_year, total_sales, total_transactions) VALUES (?, ?, ?)',
                    [monthYear, transactionAmount, 1], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            console.error('Error creating monthly summary:', err);
                        } else {
                            db.run('COMMIT');
                        }
                    });
            }
        });
    });
}

// Initialize monthly summary on startup
initializeMonthlySummary();

// Function to check and reset monthly summary at the beginning of each month
function checkAndResetMonthlyIfNeeded() {
    const currentMonthYear = getCurrentMonthYear();

    // Check if current month's summary exists
    db.get('SELECT * FROM monthly_summary WHERE month_year = ?', [currentMonthYear], (err, row) => {
        if (err) {
            console.error('Error checking monthly summary for reset:', err);
            return;
        }

        if (!row) {
            // Current month doesn't exist, initialize it with zeros
            db.run('INSERT OR IGNORE INTO monthly_summary (month_year, total_sales, total_transactions) VALUES (?, 0, 0)', [currentMonthYear], (err) => {
                if (err) {
                    console.error('Error initializing current month:', err);
                } else {
                    console.log(`Initialized monthly summary for ${currentMonthYear}`);
                }
            });
        }
    });
}

// Run the check on startup
checkAndResetMonthlyIfNeeded();

// Schedule monthly reset for the 1st of each month at midnight
const schedule = require('node-schedule');
schedule.scheduleJob('0 0 1 * *', function () { // Run on the 1st of every month at 00:00
    console.log('Monthly reset check triggered automatically');
    checkAndResetMonthlyIfNeeded();
});

// ============================================
// STANDALONE CREDITS API
// ============================================

app.post('/api/standalone-credits', (req, res) => {
    const { customer_id, customer_name, total_amount, paid_amount } = req.body;

    // Validate inputs
    if (!customer_id || !customer_name || typeof total_amount !== 'number' || total_amount < 0) {
        return res.status(400).json({ error: 'Customer ID, name, and valid total amount are required' });
    }

    if (typeof paid_amount !== 'number' || paid_amount < 0 || paid_amount > total_amount) {
        return res.status(400).json({ error: 'Paid amount must be a valid number between 0 and total amount' });
    }

    db.run('INSERT INTO standalone_credits (customer_id, customer_name, total_amount, paid_amount) VALUES (?, ?, ?, ?)',
        [customer_id, customer_name, total_amount, paid_amount || 0], function (err) {
            if (err) {
                console.error('Error inserting standalone credit:', err);
                return res.status(500).json({ error: err.message });
            }
            res.json({ id: this.lastID, success: true });
        }
    );
});

app.get('/api/standalone-credits', (req, res) => {
    const { search } = req.query;
    let query = 'SELECT * FROM standalone_credits';
    const params = [];

    if (search) {
        query += ' WHERE customer_id LIKE ? OR customer_name LIKE ?';
        const searchParam = `%${search}%`;
        params.push(searchParam, searchParam);
    }

    query += ' ORDER BY created_at DESC';

    db.all(query, params, (err, rows) => {
        if (err) {
            console.error('Error fetching standalone credits:', err);
            return res.status(500).json({ error: err.message });
        }
        res.json(rows);
    });
});

app.put('/api/standalone-credits/:id', (req, res) => {
    const creditId = req.params.id;
    const { paid_amount } = req.body;

    // Validate input
    if (typeof paid_amount !== 'number' || paid_amount < 0) {
        return res.status(400).json({ error: 'Valid paid amount is required' });
    }

    // Get current record to validate total amount
    db.get('SELECT total_amount FROM standalone_credits WHERE id = ?', [creditId], (err, record) => {
        if (err) {
            console.error('Error fetching credit record:', err);
            return res.status(500).json({ error: err.message });
        }

        if (!record) {
            return res.status(404).json({ error: 'Credit record not found' });
        }

        if (paid_amount > record.total_amount) {
            return res.status(400).json({ error: 'Paid amount cannot exceed total amount' });
        }

        db.run('UPDATE standalone_credits SET paid_amount = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
            [paid_amount, creditId], (err) => {
                if (err) {
                    console.error('Error updating standalone credit:', err);
                    return res.status(500).json({ error: err.message });
                }
                res.json({ success: true });
            }
        );
    });
});

app.delete('/api/standalone-credits/:id', (req, res) => {
    const creditId = req.params.id;

    db.run('DELETE FROM standalone_credits WHERE id = ?', [creditId], (err) => {
        if (err) {
            console.error('Error deleting standalone credit:', err);
            return res.status(500).json({ error: err.message });
        }

        if (this.changes === 0) {
            return res.status(404).json({ error: 'Credit record not found' });
        }

        res.json({ success: true });
    });
});

// ============================================
// CUSTOMERS API
// ============================================
app.get('/api/customers', (req, res) => {
    const query = `
        SELECT c.*, 
               COALESCE(SUM(cp.amount_paid), 0) as total_paid,
               c.total_outstanding as current_outstanding
        FROM customers c
        LEFT JOIN credit_payments cp ON c.id = cp.customer_id
        GROUP BY c.id
        ORDER BY c.name
    `;
    db.all(query, [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(rows);
    });
});

// Clear customer credit (set to 0)
app.put('/api/customers/:id/clear-credit', (req, res) => {
    const customerId = req.params.id;
    const { payment_date, notes } = req.body;

    console.log('Clearing credit for customer ID:', customerId);
    console.log('Request body:', req.body);

    db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // Get current outstanding amount
        db.get('SELECT name, total_outstanding FROM customers WHERE id = ?', [customerId], (err, customer) => {
            if (err) {
                db.run('ROLLBACK');
                console.error('Database error:', err);
                return res.status(500).json({ error: err.message });
            }

            if (!customer) {
                db.run('ROLLBACK');
                return res.status(404).json({ error: 'Customer not found' });
            }

            const amountToClear = customer.total_outstanding;

            if (amountToClear <= 0) {
                db.run('ROLLBACK');
                return res.status(400).json({ error: 'No outstanding balance to clear' });
            }

            // Insert customer credit adjustment record (separate from transaction-based payments)
            db.run(
                'INSERT INTO customer_credit_adjustments (customer_id, adjustment_date, amount_cleared, notes, adjustment_type) VALUES (?, ?, ?, ?, ?)',
                [customerId, payment_date, amountToClear, notes || 'Credit cleared', 'CLEAR'],
                function (err) {
                    if (err) {
                        db.run('ROLLBACK');
                        console.error('Insert adjustment error:', err);
                        return res.status(500).json({ error: err.message });
                    }

                    // Update customer's outstanding amount to 0
                    db.run('UPDATE customers SET total_outstanding = 0 WHERE id = ?', [customerId], (err) => {
                        if (err) {
                            db.run('ROLLBACK');
                            console.error('Update customer error:', err);
                            return res.status(500).json({ error: err.message });
                        }

                        db.run('COMMIT');
                        console.log('Credit cleared successfully for customer:', customer.name);
                        res.json({
                            success: true,
                            cleared_amount: amountToClear,
                            customer_name: customer.name
                        });
                    });
                }
            );
        });
    });
});

app.post('/api/customers', (req, res) => {
    const { name, phone, email, address } = req.body;

    if (!name || name.trim() === '') {
        return res.status(400).json({ error: 'Customer name is required' });
    }

    db.run('INSERT INTO customers (name, phone, email, address) VALUES (?, ?, ?, ?)',
        [name.trim(), phone || null, email || null, address || null],
        function (err) {
            if (err) {
                if (err.message.includes('UNIQUE constraint failed')) {
                    return res.status(400).json({ error: 'Customer name already exists' });
                }
                return res.status(500).json({ error: err.message });
            }
            res.json({ id: this.lastID, name: name.trim(), success: true });
        }
    );
});

app.put('/api/customers/:id', (req, res) => {
    const { name, phone, email, address } = req.body;
    const customerId = req.params.id;

    if (!name || name.trim() === '') {
        return res.status(400).json({ error: 'Customer name is required' });
    }

    db.run('UPDATE customers SET name = ?, phone = ?, email = ?, address = ? WHERE id = ?',
        [name.trim(), phone || null, email || null, address || null, customerId],
        (err) => {
            if (err) {
                if (err.message.includes('UNIQUE constraint failed')) {
                    return res.status(400).json({ error: 'Customer name already exists' });
                }
                return res.status(500).json({ error: err.message });
            }
            res.json({ success: true });
        }
    );
});

app.delete('/api/customers/:id', (req, res) => {
    const customerId = req.params.id;

    // Check if customer has outstanding balance
    db.get('SELECT total_outstanding FROM customers WHERE id = ?', [customerId], (err, customer) => {
        if (err) return res.status(500).json({ error: err.message });
        if (!customer) return res.status(404).json({ error: 'Customer not found' });

        if (customer.total_outstanding > 0) {
            return res.status(400).json({
                error: 'Cannot delete customer with outstanding balance. Please settle all dues first.'
            });
        }

        db.run('DELETE FROM customers WHERE id = ?', [customerId], (err) => {
            if (err) return res.status(500).json({ error: err.message });
            res.json({ success: true });
        });
    });
});

// ============================================
// DASHBOARD/SUMMARY API
// ============================================
app.get('/api/dashboard', (req, res) => {
    const today = new Date().toISOString().split('T')[0];
    const results = {};

    // Get inventory value
    db.get('SELECT SUM(current_stock * price) as total_value FROM skus', [], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        results.inventory_value = row.total_value || 0;

        // Get today's sales
        db.get('SELECT COUNT(*) as count, SUM(total_amount) as total FROM transactions WHERE transaction_date = ?',
            [today], (err, row) => {
                if (err) return res.status(500).json({ error: err.message });
                results.today_sales_count = row.count || 0;
                results.today_sales_total = row.total || 0;

                // Get outstanding credit (from customers table)
                db.get('SELECT SUM(total_outstanding) as total_outstanding FROM customers', [], (err, row) => {
                    if (err) return res.status(500).json({ error: err.message });
                    results.outstanding_credit = row.total_outstanding || 0;

                    // Get today's expenses
                    db.get('SELECT SUM(amount) as total FROM expenses WHERE expense_date = ?',
                        [today], (err, row) => {
                            if (err) return res.status(500).json({ error: err.message });
                            results.today_expenses = row.total || 0;

                            // Get current month's sales summary
                            const currentMonthYear = getCurrentMonthYear();
                            db.get('SELECT total_sales, total_transactions FROM monthly_summary WHERE month_year = ?', [currentMonthYear], (err, row) => {
                                if (err) {
                                    console.error('Error getting monthly summary:', err);
                                    results.monthly_sales_total = 0;
                                    results.monthly_sales_count = 0;
                                } else {
                                    results.monthly_sales_total = row ? row.total_sales || 0 : 0;
                                    results.monthly_sales_count = row ? row.total_transactions || 0 : 0;
                                }

                                res.json(results);
                            });
                        });
                });
            });
    });
});

// Monthly Reports API
app.get('/api/monthly-report', (req, res) => {
    const { month_year } = req.query;
    const targetMonthYear = month_year || getCurrentMonthYear();

    // Get detailed monthly report
    const transactionsQuery = `
        SELECT t.*, ti.sku_id, ti.quantity, ti.unit_price, ti.subtotal,
               s.sku_code, s.flavour, s.weight, p.name as product_name
        FROM transactions t
        LEFT JOIN transaction_items ti ON t.id = ti.transaction_id
        LEFT JOIN skus s ON ti.sku_id = s.id
        LEFT JOIN products p ON s.product_id = p.id
        WHERE strftime('%Y-%m', t.transaction_date) = ?
        ORDER BY t.transaction_date DESC, t.id DESC
    `;

    db.all(transactionsQuery, [targetMonthYear], (err, transactions) => {
        if (err) return res.status(500).json({ error: err.message });

        // Group items by transaction
        const groupedTransactions = {};
        transactions.forEach(tx => {
            if (!groupedTransactions[tx.id]) {
                groupedTransactions[tx.id] = {
                    id: tx.id,
                    transaction_date: tx.transaction_date,
                    payment_type: tx.payment_type,
                    customer_name: tx.customer_name,
                    total_amount: tx.total_amount,
                    discount_percentage: tx.discount_percentage,
                    discount_amount: tx.discount_amount,
                    items: []
                };
            }

            if (tx.sku_id) { // Only add items that exist
                groupedTransactions[tx.id].items.push({
                    sku_id: tx.sku_id,
                    sku_code: tx.sku_code,
                    product_name: tx.product_name,
                    flavour: tx.flavour,
                    weight: tx.weight,
                    quantity: tx.quantity,
                    unit_price: tx.unit_price,
                    subtotal: tx.subtotal
                });
            }
        });

        const result = {
            month_year: targetMonthYear,
            transactions: Object.values(groupedTransactions),
            summary: {
                total_sales: Object.values(groupedTransactions).reduce((sum, tx) => sum + (tx.total_amount || 0), 0),
                total_transactions: Object.keys(groupedTransactions).length,
                total_discounts: Object.values(groupedTransactions).reduce((sum, tx) => sum + (tx.discount_amount || 0), 0)
            }
        };

        res.json(result);
    });
});

// Endpoint to reset monthly values (admin only)
app.post('/api/reset-monthly', (req, res) => {
    // In a real app, you'd want to verify admin privileges here
    const nextMonthYear = req.body.next_month_year; // Expected format: YYYY-MM

    if (!nextMonthYear) {
        return res.status(400).json({ error: 'Month year required in format YYYY-MM' });
    }

    // Insert new month with zeros, or update if exists
    db.run('INSERT OR REPLACE INTO monthly_summary (month_year, total_sales, total_transactions) VALUES (?, 0, 0)',
        [nextMonthYear], (err) => {
            if (err) {
                return res.status(500).json({ error: err.message });
            }
            res.json({ success: true, message: `Monthly values reset for ${nextMonthYear}` });
        });
});

// Start server
app.listen(PORT, () => {
    console.log(`Server running on http://localhost:${PORT}`);
});