const db = require('../config/db');
const { generateRentalCode } = require('../utils/codeGenerator');

class RentalService {
    async getAll() {
        const [rows] = await db.execute(`
            SELECT r.*, c.full_name as customer_name, u.username as created_by
            FROM rentals r
            JOIN customers c ON r.customer_id = c.id
            JOIN users u ON r.created_by_user_id = u.id
            ORDER BY r.created_at DESC
        `);

        if (rows.length === 0) return [];

        const rentalIds = rows.map(r => r.id);
        const [items] = await db.execute(`
            SELECT ri.*, i.item_name, i.item_code
            FROM rental_items ri
            JOIN items i ON ri.item_id = i.id
            WHERE ri.rental_id IN (${rentalIds.join(',')})
        `);

        const rentalsWithItems = rows.map(rental => {
            return {
                ...rental,
                items: items.filter(item => item.rental_id === rental.id)
            };
        });

        return rentalsWithItems;
    }

    async getById(id) {
        const [rentals] = await db.execute(`
            SELECT r.*, c.full_name as customer_name, u.username as created_by
            FROM rentals r
            JOIN customers c ON r.customer_id = c.id
            JOIN users u ON r.created_by_user_id = u.id
            WHERE r.id = ?
        `, [id]);

        if (rentals.length === 0) return null;

        const [items] = await db.execute(`
            SELECT ri.*, i.item_name, i.item_code
            FROM rental_items ri
            JOIN items i ON ri.item_id = i.id
            WHERE ri.rental_id = ?
        `, [id]);

        return { ...rentals[0], items };
    }

    async startRental(rentalData, userId) {
        const { customer_id, items, advance_payment, total_amount } = rentalData;
        const connection = await db.getConnection();
        await connection.beginTransaction();

        try {
            // 1. Create Rental Header
            const rental_code = generateRentalCode();
            const [rentalResult] = await connection.execute(
                'INSERT INTO rentals (rental_code, customer_id, created_by_user_id, rental_start, total_amount, advance_payment, paid_amount, status) VALUES (?, ?, ?, NOW(), ?, ?, ?, ?)',
                [rental_code, customer_id, userId, total_amount || 0, advance_payment || 0, advance_payment || 0, 'ONGOING']
            );
            const rental_id = rentalResult.insertId;

            // 2. Process Items
            for (const item of items) {
                const [itemRows] = await connection.execute('SELECT * FROM items WHERE id = ? FOR UPDATE', [item.item_id]);
                const itemData = itemRows[0];

                if (!itemData || itemData.available_quantity < item.quantity) {
                    throw new Error(`Item ${item.item_id} does not have enough stock`);
                }

                await connection.execute(
                    'INSERT INTO rental_items (rental_id, item_id, quantity, rate_at_rent_time, rate_type_at_rent_time, returned_quantity) VALUES (?, ?, ?, ?, ?, 0)',
                    [rental_id, item.item_id, item.quantity, itemData.rental_rate, itemData.rate_type]
                );

                const newAvailable = itemData.available_quantity - item.quantity;
                const newStatus = newAvailable === 0 ? 'RENTED' : 'AVAILABLE';
                await connection.execute('UPDATE items SET available_quantity = ?, status = ? WHERE id = ?', [newAvailable, newStatus, item.item_id]);
            }

            await connection.commit();
            return { rental_id, rental_code };
        } catch (err) {
            await connection.rollback();
            throw err;
        } finally {
            connection.release();
        }
    }

    async returnRental(rentalId, itemsToReturn, paymentDetails) {
        const connection = await db.getConnection();
        await connection.beginTransaction();

        try {
            // 1. Get Rental
            const [rentals] = await connection.execute('SELECT * FROM rentals WHERE id = ? FOR UPDATE', [rentalId]);
            const rental = rentals[0];
            if (!rental || (rental.status !== 'ONGOING' && rental.status !== 'PARTIAL')) {
                throw new Error('Active rental not found');
            }

            // 2. Process Returned Items
            for (const item of itemsToReturn) {
                // Update rental_items table
                await connection.execute(
                    'UPDATE rental_items SET returned_quantity = returned_quantity + ? WHERE rental_id = ? AND item_id = ?',
                    [item.quantity, rentalId, item.itemId]
                );

                // Update items stock
                await connection.execute(
                    'UPDATE items SET available_quantity = available_quantity + ?, status = "AVAILABLE" WHERE id = ?',
                    [item.quantity, item.itemId]
                );
            }

            // 3. Update Financials
            const currentFine = Number(rental.fine_amount) || 0;
            const currentDiscount = Number(rental.discount_amount) || 0;
            const currentPaid = Number(rental.paid_amount) || 0;

            const addFine = Number(paymentDetails?.fineAmount) || 0;
            const addDiscount = Number(paymentDetails?.discount) || 0;
            const addPaid = Number(paymentDetails?.paidAmountToday) || 0;

            const newFine = currentFine + addFine;
            const newDiscount = currentDiscount + addDiscount;
            const newPaid = currentPaid + addPaid;

            // 4. Determine Status
            // Check if ALL items are returned
            const [allItems] = await connection.execute(
                'SELECT SUM(quantity) as total, SUM(returned_quantity) as returned FROM rental_items WHERE rental_id = ?',
                [rentalId]
            );

            const totalItems = Number(allItems[0]?.total) || 0;
            const returnedItems = Number(allItems[0]?.returned) || 0;

            const isFullyReturned = totalItems > 0 && totalItems === returnedItems;
            const newStatus = isFullyReturned ? 'COMPLETED' : 'PARTIAL';

            await connection.execute(
                'UPDATE rentals SET fine_amount = ?, discount_amount = ?, paid_amount = ?, status = ?, rental_end = NOW() WHERE id = ?',
                [newFine, newDiscount, newPaid, newStatus, rentalId]
            );

            // 5. Create Payment record if money was paid
            if (addPaid > 0) {
                await connection.execute(
                    'INSERT INTO payments (rental_id, amount, method, status, paid_at) VALUES (?, ?, ?, ?, NOW())',
                    [rentalId, addPaid, 'CASH', 'PAID']
                );
            }

            await connection.commit();
            console.log(`[ReturnRental] Return successful for ${rentalId}`);
            return { success: true };
        } catch (err) {
            console.error(`[ReturnRental] Error processing return: ${err.message}`);
            await connection.rollback();
            throw err;
        } finally {
            connection.release();
        }
    }
}

module.exports = new RentalService();
