const express = require('express');
const router = express.Router();
const { Order, OrderItem, Book, User, sequelize } = require('../models');
const { Op } = require('sequelize');

router.get('/dashboard', async (req, res) => {
    try {
        // Status that count as revenue
        // Status that count as revenue (Case insensitive handling via list)
        const revenueStatus = [
            'delivered', 'completed',
            'Delivered', 'Completed',
            'selesai', 'diterima'
        ];

        // Helper to percentages
        const calculateChange = (current, previous) => {
            if (previous === 0) return current > 0 ? "+ 100%" : "0%";
            const percent = ((current - previous) / previous) * 100;
            return `${percent >= 0 ? "+ " : ""}${percent.toFixed(1)}%`;
        };

        // Helper for last 7 days chart data
        const getSparklineData = async (model, aggregateField = null, dateField = 'created_at') => {
            const sevenDaysAgo = new Date();
            sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 6);
            sevenDaysAgo.setHours(0, 0, 0, 0);

            const results = await model.findAll({
                attributes: [
                    [sequelize.fn('DATE', sequelize.col(dateField)), 'date'],
                    aggregateField
                        ? [sequelize.fn('SUM', sequelize.col(aggregateField)), 'value']
                        : [sequelize.fn('COUNT', sequelize.col('id')), 'value']
                ],
                where: {
                    [dateField]: { [Op.gte]: sevenDaysAgo },
                    ...(aggregateField === 'total_amount' ? { status: { [Op.in]: revenueStatus } } : {}) // Filter valid revenue
                },
                group: [sequelize.fn('DATE', sequelize.col(dateField))],
                order: [[sequelize.fn('DATE', sequelize.col(dateField)), 'ASC']]
            });

            // Fill missing days with 0
            const dataMap = {};
            results.forEach(r => {
                dataMap[r.dataValues.date] = parseInt(r.dataValues.value || 0);
            });

            const chartData = [];
            for (let i = 6; i >= 0; i--) {
                const d = new Date();
                d.setDate(d.getDate() - i);
                const dateStr = d.toISOString().split('T')[0];
                chartData.push(dataMap[dateStr] || 0);
            }
            return chartData;
        };

        // --- 1. Summary Stats with Trends ---
        const now = new Date();
        const startCurrentMonth = new Date(now.getFullYear(), now.getMonth(), 1);
        const startLastMonth = new Date(now.getFullYear(), now.getMonth() - 1, 1);
        const endLastMonth = new Date(now.getFullYear(), now.getMonth(), 0);

        // Overall Aggregates (for total values)
        const revenueAgg = await Order.sum('total_amount', {
            where: {
                status: { [Op.in]: revenueStatus }
            }
        });
        const totalOrders = await Order.count();
        const booksSoldAgg = await OrderItem.sum('quantity', {
            include: [{
                model: Order,
                where: { status: { [Op.in]: revenueStatus } },
                attributes: []
            }]
        });

        // Revenue Change
        const revenueCurrentMonth = await Order.sum('total_amount', {
            where: {
                status: { [Op.in]: revenueStatus },
                createdAt: { [Op.gte]: startCurrentMonth }
            }
        }) || 0;
        const revenueLastMonth = await Order.sum('total_amount', {
            where: {
                status: { [Op.in]: revenueStatus },
                createdAt: {
                    [Op.between]: [startLastMonth, endLastMonth]
                }
            }
        }) || 0;
        const revenueChange = calculateChange(revenueCurrentMonth, revenueLastMonth);
        const revenueChart = await getSparklineData(Order, 'total_amount');

        // Orders Change
        const ordersCurrentMonth = await Order.count({
            where: { createdAt: { [Op.gte]: startCurrentMonth } }
        });
        const ordersLastMonth = await Order.count({
            where: { createdAt: { [Op.between]: [startLastMonth, endLastMonth] } }
        });
        const ordersChange = calculateChange(ordersCurrentMonth, ordersLastMonth);
        const ordersChart = await getSparklineData(Order, null);

        // Books Sold Change (Approximation via Order Items for simplicity or precise query)
        // For 'Books Sold', let's use Sum of Quantity
        // Note: Logic for sparkline on joined table is complex in Sequelize, simplifying to Order count or implementing separate query.
        // Let's do a simple count of OrderItems created (sold)
        // Books Sold Change 
        // Logic for sparkline on joined table is complex in Sequelize, implementing separate queries.

        const booksCurrentMonth = await OrderItem.sum('quantity', {
            include: [{
                model: Order,
                where: {
                    createdAt: { [Op.gte]: startCurrentMonth },
                    status: { [Op.in]: revenueStatus }
                },
                attributes: []
            }]
        }) || 0;

        const booksLastMonth = await OrderItem.sum('quantity', {
            include: [{
                model: Order,
                where: {
                    createdAt: { [Op.between]: [startLastMonth, endLastMonth] },
                    status: { [Op.in]: revenueStatus }
                },
                attributes: []
            }]
        }) || 0;

        const booksChange = calculateChange(booksCurrentMonth, booksLastMonth);

        // Books Chart (Custom Query for Join)
        const sevenDaysAgo = new Date();
        sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 6);
        sevenDaysAgo.setHours(0, 0, 0, 0);

        const booksChartResults = await OrderItem.findAll({
            attributes: [
                [sequelize.fn('DATE', sequelize.col('Order.created_at')), 'date'],
                [sequelize.fn('SUM', sequelize.col('quantity')), 'value']
            ],
            include: [{
                model: Order,
                attributes: [],
                where: {
                    createdAt: { [Op.gte]: sevenDaysAgo },
                    status: { [Op.in]: revenueStatus }
                }
            }],
            group: [sequelize.fn('DATE', sequelize.col('Order.created_at'))],
            order: [[sequelize.fn('DATE', sequelize.col('Order.created_at')), 'ASC']]
        });

        const booksDataMap = {};
        booksChartResults.forEach(r => {
            booksDataMap[r.dataValues.date] = parseInt(r.dataValues.value || 0);
        });

        const booksChart = [];
        for (let i = 6; i >= 0; i--) {
            const d = new Date();
            d.setDate(d.getDate() - i);
            const dateStr = d.toISOString().split('T')[0];
            booksChart.push(booksDataMap[dateStr] || 0);
        }


        // 2. Recent Transactions (Latest 5)
        const recentOrders = await Order.findAll({
            limit: 5,
            order: [['createdAt', 'DESC']],
            include: [{
                model: User,
                attributes: ['name']
            }]
        });

        // 3. Order Status Distribution
        const statusDistribution = await Order.findAll({
            attributes: ['status', [sequelize.fn('COUNT', sequelize.col('status')), 'count']],
            group: ['status']
        });

        // 4. Best Sellers (Top 5)
        const bestSellers = await OrderItem.findAll({
            attributes: [
                'book_id',
                [sequelize.fn('SUM', sequelize.col('quantity')), 'total_sold']
            ],
            include: [{
                model: Book,
                attributes: ['title', 'author', 'price', 'image']
            }, {
                model: Order,
                attributes: [],
                where: { status: { [Op.in]: revenueStatus } }
            }],
            group: ['book_id', 'Book.id', 'Book.title', 'Book.author', 'Book.price', 'Book.image'],
            order: [[sequelize.literal('total_sold'), 'DESC']],
            limit: 5
        });

        // 5. Monthly Revenue (Last 12 Months)
        const monthlyRevenue = [];
        for (let i = 11; i >= 0; i--) {
            const d = new Date();
            d.setMonth(d.getMonth() - i);
            const m = d.getMonth() + 1;
            const y = d.getFullYear();

            // Query for this specific month
            const startOfMonth = new Date(y, m - 1, 1);
            const endOfMonth = new Date(y, m, 0);
            endOfMonth.setHours(23, 59, 59, 999);

            const revenue = await Order.sum('total_amount', {
                where: {
                    status: { [Op.in]: revenueStatus },
                    created_at: { [Op.between]: [startOfMonth, endOfMonth] }
                }
            }) || 0;

            monthlyRevenue.push({
                year: y,
                month: m,
                revenue: parseInt(revenue)
            });
        }

        res.json({
            summary: {
                totalRevenue: revenueAgg || 0,
                revenueChange,
                revenueChart,
                totalOrders: totalOrders || 0,
                ordersChange,
                ordersChart,
                booksSold: booksSoldAgg || 0,
                booksChange,
                booksChart
            },
            recentOrders: recentOrders.map(o => ({
                id: o.id,
                name: o.User ? o.User.name : 'Guest',
                date: o.createdAt,
                amount: o.total_amount,
                status: o.status
            })),
            statusDistribution: statusDistribution.map(s => ({
                status: s.status,
                count: s.dataValues.count
            })),
            bestSellers: bestSellers.map(b => ({
                title: b.Book.title,
                author: b.Book.author,
                price: b.Book.price,
                image: b.Book.image,
                sold: b.dataValues.total_sold
            })),
            monthlyRevenue: monthlyRevenue.map(m => ({
                year: m.year,
                month: m.month,
                revenue: m.revenue
            }))
        });

    } catch (err) {
        console.error("Dashboard Error:", err);
        res.status(500).json({ message: "Server Error", error: err.message });
    }
});

module.exports = router;
