const express = require('express');
const router = express.Router();
const { db, getProductsByCategory, getUnreadMessageCount } = require('../database');

// Get products by category
router.get('/products/:category', (req, res) => {
    const products = getProductsByCategory(req.params.category);

    const productList = products.map(product => {
        let options = {};
        if (product.product_options) {
            try {
                options = JSON.parse(product.product_options);
            } catch (e) {
                options = {};
            }
        }
        return {
            id: product.id,
            product_name: product.product_name,
            category: product.category,
            options: options,
            product_rate: product.product_rate,
            stock_status: product.stock_status,
            image_filename: product.image_filename,
            created_at: product.created_at,
            updated_at: product.updated_at
        };
    });

    res.json(productList);
});

// Get unread message count
router.get('/unread-count', (req, res) => {
    if (!req.session.authenticated) {
        return res.json({ count: 0 });
    }

    const userEmail = req.session.user_email;
    if (!userEmail) {
        return res.json({ count: 0 });
    }

    const count = getUnreadMessageCount(userEmail);
    res.json({ count });
});

// Get dashboard data
router.get('/dashboard-data', (req, res) => {
    const oneWeekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString();

    const ordersThisWeek = db.prepare("SELECT COUNT(*) as count FROM orders WHERE datetime(created_at) >= ?").get(oneWeekAgo).count;
    const unplacedOrders = db.prepare("SELECT COUNT(*) as count FROM orders WHERE status IN ('inquiry placed', 'quote sent')").get().count;
    const deliveredCount = db.prepare("SELECT COUNT(*) as count FROM orders WHERE status = 'delivered'").get().count;
    const inStockProducts = db.prepare("SELECT COUNT(*) as count FROM products WHERE stock_status = 'in_stock'").get().count;
    const totalClients = db.prepare("SELECT COUNT(*) as count FROM users").get().count;

    res.json({
        orders_this_week: ordersThisWeek,
        delivered_count: deliveredCount,
        in_stock_products: inStockProducts,
        total_clients: totalClients,
        unplaced_orders: unplacedOrders
    });
});

// Get inquired items (MUST be before /orders/:category)
router.get('/orders/inquired', (req, res) => {
    if (!req.session.authenticated || !req.session.is_admin) {
        return res.status(401).json({ error: 'Unauthorized' });
    }

    try {
        const rows = db.prepare(`
            SELECT product_name, COUNT(*) as order_count
            FROM orders
            GROUP BY product_name
            ORDER BY order_count DESC, product_name ASC
        `).all();

        const result = rows.map(row => {
            const orders = db.prepare(`
                SELECT id, user_email, quantity, status, created_at, last_updated
                FROM orders
                WHERE product_name = ?
                ORDER BY created_at DESC
            `).all(row.product_name);

            return {
                product_name: row.product_name,
                order_count: row.order_count,
                orders: orders.map(o => ({
                    id: o.id,
                    client: o.user_email,
                    quantity: o.quantity,
                    status: o.status,
                    date: o.created_at,
                    last_updated: o.last_updated
                }))
            };
        });

        res.json(result);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// Get client orders breakdown (MUST be before /orders/:category)
router.get('/orders/clients', (req, res) => {
    if (!req.session.authenticated || !req.session.is_admin) {
        return res.status(401).json({ error: 'Unauthorized' });
    }

    try {
        const clients = db.prepare(`
            SELECT user_email, COUNT(*) as total_orders
            FROM orders
            GROUP BY user_email
            ORDER BY total_orders DESC, user_email ASC
        `).all();

        const clientData = clients.map(client => {
            const products = db.prepare(`
                SELECT product_name, COUNT(*) as product_count
                FROM orders
                WHERE user_email = ?
                GROUP BY product_name
                ORDER BY product_count DESC
            `).all(client.user_email);

            return {
                email: client.user_email,
                total_orders: client.total_orders,
                products: products.map(p => ({
                    product_name: p.product_name,
                    count: p.product_count
                }))
            };
        });

        res.json(clientData);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// Get orders by category (admin only)
router.get('/orders/:category', (req, res) => {
    if (!req.session.authenticated || !req.session.is_admin) {
        return res.status(401).json({ error: 'Unauthorized' });
    }

    const category = req.params.category;
    const oneWeekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString();

    let query, params = [];

    switch (category) {
        case 'week':
            query = `SELECT * FROM orders WHERE datetime(last_updated) >= ? ORDER BY datetime(last_updated) DESC`;
            params = [oneWeekAgo];
            break;
        case 'delivered':
            query = `SELECT * FROM orders WHERE status = 'delivered' ORDER BY datetime(last_updated) DESC`;
            break;
        case 'pending':
            query = `SELECT * FROM orders WHERE status IN ('dispatched', 'order placed') ORDER BY last_updated DESC`;
            break;
        case 'dispatched':
            query = `SELECT * FROM orders WHERE status = 'dispatched' ORDER BY datetime(last_updated) DESC`;
            break;
        case 'unplaced':
            query = `SELECT * FROM orders WHERE status IN ('inquiry received', 'quote sent') ORDER BY last_updated DESC`;
            break;
        default:
            return res.status(400).json({ error: 'Invalid category' });
    }

    try {
        const stmt = db.prepare(query);
        const rows = params.length ? stmt.all(...params) : stmt.all();

        const orders = rows.map(row => ({
            id: row.id,
            product_name: row.product_name,
            client_email: row.user_email,
            quantity: row.quantity,
            expected_date: row.expected_date,
            order_status: row.status,
            last_updated: row.last_updated,
            comments: row.comments || '',
            created_at: row.created_at
        }));

        res.json(orders);
    } catch (err) {
        console.error('Error in get_orders_by_category:', err);
        res.status(500).json({ error: err.message });
    }
});


// Get delivered orders by category
router.get('/delivered-by-category', (req, res) => {
    try {
        const rows = db.prepare(`
            SELECT o.id, o.product_name, o.user_email, o.last_updated, p.category
            FROM orders o
            JOIN products p ON LOWER(o.product_name) = LOWER(p.product_name)
            WHERE o.status = 'delivered'
            ORDER BY p.category, o.last_updated DESC
        `).all();

        const categories = {};
        rows.forEach(row => {
            if (!categories[row.category]) {
                categories[row.category] = [];
            }
            categories[row.category].push({
                id: row.id,
                product: row.product_name,
                client: row.user_email,
                delivered_at: row.last_updated
            });
        });

        res.json(categories);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// Timeline months
router.get('/timeline/months', (req, res) => {
    try {
        const rows = db.prepare(`
            SELECT DISTINCT strftime('%Y-%m', created_at) AS ym
            FROM orders
            WHERE created_at >= '2025-10-01'
            ORDER BY ym DESC
        `).all();

        res.json(rows.map(r => r.ym));
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// Timeline orders by month
router.get('/timeline/orders/:month', (req, res) => {
    try {
        const rows = db.prepare(`
            SELECT id, product_name, user_email, status, created_at
            FROM orders
            WHERE strftime('%Y-%m', created_at) = ?
            ORDER BY created_at DESC
        `).all(req.params.month);

        res.json(rows.map(r => ({
            id: r.id,
            product_name: r.product_name,
            user_email: r.user_email,
            status: r.status,
            created_at: r.created_at
        })));
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// Payment status
router.get('/payment-status', (req, res) => {
    try {
        const rows = db.prepare(`
            SELECT id, product_name, user_email, quantity, status,
                   payment_status, created_at, last_updated
            FROM orders
            WHERE status IN ('order placed', 'dispatched', 'delivered')
            ORDER BY last_updated DESC
        `).all();

        res.json(rows.map(r => ({
            id: r.id,
            product_name: r.product_name,
            user_email: r.user_email,
            quantity: r.quantity,
            status: r.status,
            payment_status: r.payment_status || 'unpaid',
            created_at: r.created_at,
            last_updated: r.last_updated
        })));
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// Update payment status
router.post('/payment-status/update/:order_id', (req, res) => {
    const newStatus = req.body.payment_status || 'unpaid';

    try {
        db.prepare('UPDATE orders SET payment_status = ? WHERE id = ?').run(newStatus, req.params.order_id);
        res.json({ success: true });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

module.exports = router;
