const pool = require('../config/dbConnection');
const baseModel = require("./baseModel");

class LiveBooking extends baseModel {
    
    /**
     * Save or update a booking based on ReservationNo (upsert)
     */
    async saveOrUpdate(bookingData) {
        try {
            // Check if booking exists
            const existing = await pool.query(
                'SELECT id FROM live_bookings WHERE ReservationNo = ?',
                [bookingData.ReservationNo]
            );

            // Map and normalize field names to match database schema
            const dataToSave = {};
            
            // Define allowed fields that exist in the database
            const allowedFields = [
                'ReservationNo', 'VoucherNo', 'BookingStatus', 'Source', 'Room', 'RoomShortCode',
                'GuestName', 'Mobile', 'Email', 'Adult', 'Child', 'NoOfGuest', 'ReservationDate',
                'ArrivalDate', 'DepartureDate', 'NoOfNights', 'TotalExclusivTax', 'TotalTax',
                'OtherRevenueExclusiveTax', 'OtherRevenueInclusiveTax', 'TACommision', 'DueAmount',
                'Deposit', 'Status', 'TransactionStatus', 'TotalInclusiveTax', 'FolioNo', 'Country',
                'Phone', 'Address', 'Salutation', 'FirstName', 'LastName', 'ReservationGuarantee',
                'CancelDate', 'BaseRateExclusiveTax', 'BaseRateInclusiveTax', 'ExtraCharges',
                'Createdatetime', 'SyncDate'
            ];

            // Field name mapping (API field name => Database field name)
            const fieldMapping = {
                'Total Tax': 'TotalTax',  // Remove space
                'salutation': 'Salutation',  // Fix case
                'totalTax': 'TotalTax',  // Alternative case
                'TotalTax': 'TotalTax',  // Keep as is
                'TACommision': 'TACommision',  // Keep as is (note: typo in original schema)
                'TACommission': 'TACommision'  // Alternative spelling
            };

            // Fields to skip (not in database schema)
            const skipFields = ['RoomInfo', 'PaymentType', 'RatePlan', 'ArrivalTime', 'DepartureTime', 'RoomNo', 'BedType'];

            // Process each field
            for (const [key, value] of Object.entries(bookingData)) {
                // Skip null/undefined values
                if (value === null || value === undefined) {
                    continue;
                }

                // Skip fields that don't exist in database
                if (skipFields.includes(key)) {
                    continue;
                }

                // Map field name
                let dbFieldName = fieldMapping[key];
                
                // If not in mapping, try to normalize the field name
                if (!dbFieldName) {
                    // Remove spaces from field names
                    dbFieldName = key.replace(/\s+/g, '');
                    
                    // Fix case for known fields
                    if (dbFieldName.toLowerCase() === 'salutation') {
                        dbFieldName = 'Salutation';
                    } else if (dbFieldName.toLowerCase() === 'totaltax') {
                        dbFieldName = 'TotalTax';
                    }
                }

                // Only include fields that exist in database
                if (!allowedFields.includes(dbFieldName)) {
                    continue;
                }

                // Handle JSON/object fields
                if (['BaseRateExclusiveTax', 'BaseRateInclusiveTax', 'ExtraCharges'].includes(dbFieldName)) {
                    if (typeof value === 'object' && value !== null) {
                        // Convert object/array to JSON string
                        if (Array.isArray(value) && value.length === 0) {
                            dataToSave[dbFieldName] = null;  // Empty array = null
                        } else {
                            try {
                                dataToSave[dbFieldName] = JSON.stringify(value);
                            } catch (e) {
                                dataToSave[dbFieldName] = null;
                            }
                        }
                    } else if (typeof value === 'string') {
                        // Already a string, use as is (but validate it's valid JSON)
                        if (value === '[]' || value === '{}') {
                            dataToSave[dbFieldName] = null;
                        } else {
                            dataToSave[dbFieldName] = value;
                        }
                    } else {
                        dataToSave[dbFieldName] = null;
                    }
                } else {
                    // Regular field - convert to appropriate type
                    if (typeof value === 'object' && value !== null) {
                        // Skip object fields that aren't JSON fields
                        continue;
                    }
                    dataToSave[dbFieldName] = value;
                }
            }

            // Ensure required field exists
            if (!dataToSave.ReservationNo) {
                throw new Error('ReservationNo is required');
            }

            // Log what we're saving (for debugging)
            if (Object.keys(dataToSave).length === 0) {
                console.warn(`Warning: No valid fields to save for booking ${bookingData.ReservationNo}`);
                return { id: null, action: 'skipped' };
            }

            if (existing[0].length > 0) {
                // Update existing booking
                const result = await pool.query(
                    'UPDATE live_bookings SET ? WHERE ReservationNo = ?',
                    [dataToSave, bookingData.ReservationNo]
                );
                return { id: existing[0][0].id, action: 'updated' };
            } else {
                // Insert new booking
                const result = await pool.query(
                    'INSERT INTO live_bookings SET ?',
                    [dataToSave]
                );
                return { id: result[0].insertId, action: 'inserted' };
            }
        } catch (error) {
            console.error('Error saving/updating live booking:', error);
            throw error;
        }
    }

    /**
     * Get bookings with filters and pagination
     */
    async getBookings(filters = {}, page = 1, limit = 50) {
        try {
            let query = 'SELECT * FROM live_bookings WHERE 1=1';
            const queryParams = [];

            // Apply filters
            if (filters.ReservationNo) {
                query += ' AND ReservationNo LIKE ?';
                queryParams.push(`%${filters.ReservationNo}%`);
            }
            if (filters.BookingStatus) {
                query += ' AND BookingStatus = ?';
                queryParams.push(filters.BookingStatus);
            }
            if (filters.GuestName) {
                query += ' AND (GuestName LIKE ? OR FirstName LIKE ? OR LastName LIKE ?)';
                const namePattern = `%${filters.GuestName}%`;
                queryParams.push(namePattern, namePattern, namePattern);
            }
            if (filters.Email) {
                query += ' AND Email LIKE ?';
                queryParams.push(`%${filters.Email}%`);
            }
            if (filters.Mobile) {
                query += ' AND Mobile LIKE ?';
                queryParams.push(`%${filters.Mobile}%`);
            }
            if (filters.ArrivalDateFrom) {
                query += ' AND ArrivalDate >= ?';
                queryParams.push(filters.ArrivalDateFrom);
            }
            if (filters.ArrivalDateTo) {
                query += ' AND ArrivalDate <= ?';
                queryParams.push(filters.ArrivalDateTo);
            }
            if (filters.DepartureDateFrom) {
                query += ' AND DepartureDate >= ?';
                queryParams.push(filters.DepartureDateFrom);
            }
            if (filters.DepartureDateTo) {
                query += ' AND DepartureDate <= ?';
                queryParams.push(filters.DepartureDateTo);
            }
            if (filters.Source) {
                query += ' AND Source LIKE ?';
                queryParams.push(`%${filters.Source}%`);
            }
            if (filters.Room) {
                query += ' AND Room LIKE ?';
                queryParams.push(`%${filters.Room}%`);
            }

            // Get total count (before pagination)
            const countQuery = query.replace('SELECT *', 'SELECT COUNT(*) as total');
            const countResult = await pool.query(countQuery, queryParams);
            const total = countResult[0][0].total || 0;

            // Add ordering and pagination
            query += ' ORDER BY ArrivalDate DESC, ReservationNo DESC';
            const offset = (page - 1) * limit;
            query += ' LIMIT ? OFFSET ?';
            queryParams.push(limit, offset);

            const results = await pool.query(query, queryParams);
            
            // Parse JSON fields
            const bookings = results[0].map(booking => {
                if (booking.BaseRateExclusiveTax) {
                    try {
                        booking.BaseRateExclusiveTax = JSON.parse(booking.BaseRateExclusiveTax);
                    } catch (e) {
                        // Keep as string if not valid JSON
                    }
                }
                if (booking.BaseRateInclusiveTax) {
                    try {
                        booking.BaseRateInclusiveTax = JSON.parse(booking.BaseRateInclusiveTax);
                    } catch (e) {
                        // Keep as string if not valid JSON
                    }
                }
                if (booking.ExtraCharges) {
                    try {
                        booking.ExtraCharges = JSON.parse(booking.ExtraCharges);
                    } catch (e) {
                        // Keep as string if not valid JSON
                    }
                }
                return booking;
            });

            return {
                bookings,
                total,
                page,
                limit,
                totalPages: Math.ceil(total / limit),
                count: bookings.length  // Add count for pagination display
            };
        } catch (error) {
            console.error('Error getting live bookings:', error);
            throw error;
        }
    }

    /**
     * Get distinct booking statuses for filter dropdown
     */
    async getDistinctStatuses() {
        try {
            const result = await pool.query(
                'SELECT DISTINCT BookingStatus FROM live_bookings WHERE BookingStatus IS NOT NULL ORDER BY BookingStatus'
            );
            return result[0].map(row => row.BookingStatus);
        } catch (error) {
            console.error('Error getting distinct statuses:', error);
            return [];
        }
    }

    /**
     * Get distinct sources for filter dropdown
     */
    async getDistinctSources() {
        try {
            const result = await pool.query(
                'SELECT DISTINCT Source FROM live_bookings WHERE Source IS NOT NULL ORDER BY Source'
            );
            return result[0].map(row => row.Source);
        } catch (error) {
            console.error('Error getting distinct sources:', error);
            return [];
        }
    }

    /**
     * Get statistics
     */
    async getStatistics() {
        try {
            const totalResult = await pool.query('SELECT COUNT(*) as total FROM live_bookings');
            const total = totalResult[0][0].total || 0;

            const statusResult = await pool.query(
                'SELECT BookingStatus, COUNT(*) as count FROM live_bookings GROUP BY BookingStatus'
            );
            
            // Get date range - always start from 2025-10-01 (sync start date)
            const dateRangeResult = await pool.query(
                'SELECT MAX(ArrivalDate) as latest FROM live_bookings WHERE ArrivalDate IS NOT NULL'
            );

            return {
                total,
                statusBreakdown: statusResult[0] || [],
                dateRange: {
                    earliest: '2025-10-01',  // Always show sync start date
                    latest: dateRangeResult[0][0]?.latest || 'N/A'
                }
            };
        } catch (error) {
            console.error('Error getting statistics:', error);
            return { total: 0, statusBreakdown: [], dateRange: { earliest: '2025-10-01', latest: 'N/A' } };
        }
    }

    /**
     * Bulk save/update bookings
     */
    async bulkSaveOrUpdate(bookings, syncDate) {
        let saved = 0;
        let updated = 0;
        let errors = 0;

        for (const booking of bookings) {
            try {
                const bookingData = {
                    ...booking,
                    SyncDate: syncDate
                };
                const result = await this.saveOrUpdate(bookingData);
                if (result.action === 'inserted') {
                    saved++;
                } else {
                    updated++;
                }
            } catch (error) {
                console.error(`Error saving booking ${booking.ReservationNo}:`, error.message);
                errors++;
            }
        }

        return { saved, updated, errors };
    }
}

module.exports = LiveBooking;

