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

const runDebug = async () => {
    try {
        console.log("--- DEBUG START ---");

        // 1. Check recent orders and their statuses
        const orders = await Order.findAll({
            limit: 10,
            order: [['createdAt', 'DESC']],
            attributes: ['id', 'status', 'total_amount', 'created_at']
        });

        console.log("\nLast 10 Orders:");
        orders.forEach(o => {
            console.log(`Order #${o.id}: Status='${o.status}'`);
        });

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

        console.log("\nStatus Counts:");
        statuses.forEach(s => {
            console.log(`'${s.status}': ${s.dataValues.count}`);
        });

        // 3. Run Bestseller Query
        const revenueStatus = [
            'paid', 'shipped', 'delivered', 'completed', 'processing',
            'Paid', 'Shipped', 'Delivered', 'Completed', 'Processing',
            'selesai', 'diterima', 'dikirim', 'diproses'
        ];

        const bestSellers = await OrderItem.findAll({
            attributes: [
                'book_id',
                [sequelize.fn('SUM', sequelize.col('quantity')), 'total_sold']
            ],
            include: [{
                model: Book,
                attributes: ['title']
            }, {
                model: Order,
                attributes: ['status'], // Select status to see why it matched
                where: {
                    status: { [Op.in]: revenueStatus }
                }
            }],
            group: ['book_id', 'Book.id', 'Book.title'], // Minimal group
            order: [[sequelize.literal('total_sold'), 'DESC']],
            limit: 5
        });

        console.log("\nBestsellers Query Result:");
        bestSellers.forEach(item => {
            console.log(`Book: ${item.Book.title} (${item.book_id}) - Sold: ${item.dataValues.total_sold}`);
        });

        // 4. Trace specific book sales (10 Dosa Besar Soeharto)
        // Find the book ID first
        const book = await Book.findOne({ where: { title: { [Op.like]: '%Soeharto%' } } });
        if (book) {
            console.log(`\nFound Book: ${book.title} (ID: ${book.id})`);
            const items = await OrderItem.findAll({
                where: { book_id: book.id },
                include: [{ model: Order, attributes: ['id', 'status', 'created_at'] }]
            });
            console.log("Sales records for this book:");
            items.forEach(i => {
                console.log(`- Order #${i.Order.id} (${i.Order.status}): Qty ${i.quantity}`);
            });
        } else {
            console.log("\nCould not find book with 'Soeharto' in title");
        }

        console.log("--- DEBUG END ---");

    } catch (error) {
        console.error("Debug Error:", error);
    } finally {
        await sequelize.close();
    }
};

runDebug();
