var pool = require('../config/dbConnection');
const S3Helper = require('../helpers/s3Helper');
const bucket = process.env.AWS_PROPERTY_BUCKET;
const baseModel = require("../models/baseModel");
class Property extends baseModel{

    async getAll(params={}) {
        try{
            var query = 'SELECT * FROM properties';
            var whereAppended = false;
            if(params.id || params.slug){
                if(params.id){
                    query += ` where id = ${params.id}`;
                }else{
                    query += ` where slug = '${params.slug}'`;
                }
                whereAppended = true;
            }else{
                if(params.collection){
                    var matchingProperties = await pool.query('select * from property_collections where collection_id in (select id from collections where slug = ?)',[params.collection]);
                    matchingProperties = await matchingProperties[0].map(item => `'${item.property_id}'`).join(',');
                    query += ` where id in (${matchingProperties})`;
                    whereAppended = true;
                }
                if(params.channelIds){
                    if(whereAppended){
                        query += ` and channel_id in (${params.channelIds})`;
                    }else{
                        query += ` where channel_id in (${params.channelIds})`;
                    }
                    whereAppended = true;
                }
                if(params.destination){
                    if(whereAppended){
                        query += ` and destination = ${params.destination}`;
                    }else{
                        query += ` where destination = ${params.destination}`;
                    }
                    whereAppended = true;
                }
                if(params.featured){
                    if(whereAppended){
                        query += ` and featured_property = ${params.destination}`;
                    }else{
                        query += ` where featured_property = ${params.featured}`;
                    }
                    whereAppended = true;
                }
            }
            console.log(query);
            const results = await pool.query(query);
            return results[0];
        }catch(error){
            console.log("error in get all");
            throw error;
        }   
    }

    async listPropertiesDetails(params={}){
        try {
            var properties = await this.getAll(params);
            const propertysWithDetails = [];
            if(properties.length == 0){
                return propertysWithDetails;
            }
            var propertyIDs = properties.map(item => `${item.id}`).join(',');
            var amenityResults = await pool.query(`select ac.id as category_id, ac.name as category,pa.property_id,pa.amenity_id,a.name,pa.amenity_description,a.icon FROM amenities a, property_amenities pa, amenity_categories ac where pa.amenity_id = a.id and a.category_id = ac.id and pa.property_id in (${propertyIDs}) order by category_id`);
            var collectionResults = await pool.query(`select pc.property_id,pc.collection_id,c.active from property_collections pc, collections c where pc.property_id in (${propertyIDs}) and pc.collection_id = c.id`);
            var mediaResults = await pool.query(`select pm.*,s.display from property_media pm, settings s where pm.property_id in (${propertyIDs}) and pm.media_type_id = s.id order by s.value,pm.display_order`);
            var presignedURLs = await this.getPresignedURLs(mediaResults[0]);
            var photoCategories = await pool.query("select id,display from settings where setting = 'media_type' and category='photos'");
            var hosts = await pool.query(`select property_id,host_id from property_hosts where property_id in (${propertyIDs})`);
            for(let i=0; i< properties.length; i++){
                var property = properties[i];
                property = await this.propertyDetailsAmenities(property,amenityResults[0]);
                property = await this.propertyDetailsCollections(property,collectionResults[0]);
                property = await this.propertyDetailsFiles(property,mediaResults[0],presignedURLs);
                property = await this.propertyDetailsPhotos(property,mediaResults[0],presignedURLs,photoCategories[0]);
                property = await this.propertyHosts(property,hosts[0]);
                propertysWithDetails.push(property);
            }
            return propertysWithDetails;
        } catch (error) {
            console.log("error in listPropertiesDetails");
            console.log(error);
            throw error;
        }
    }

    async propertyDetailsAmenities(property,amenityResults){
        var ams = amenityResults.filter(am => am.property_id == property.id);
        var amenities = [];
        var amenitiesWithDescriptions = [];
        ams.forEach(amenity =>{
            amenitiesWithDescriptions.push({"amenity_id":amenity.amenity_id,"name":amenity.name,"category":amenity.category_id,"category_name":amenity.category,"description":amenity.amenity_description,"icon":amenity.icon});
            amenities.push(amenity.amenity_id);
        });
        property.amenitiesWithDescriptions = amenitiesWithDescriptions;
        property.amenities = amenities;
        property.pool = false;
        if(amenities.includes(1)){
            property.pool = true;
        }
        property.pooltype = "";
        if(amenities.includes(67)){
            property.pooltype = "Private Pool";
        }
        if(amenities.includes(68)){
            property.pooltype = "Shared Pool";
        }
        return property;
    }

    async propertyDetailsCollections(property,collectionResults){
        var colls = collectionResults.filter(col => col.property_id == property.id);
        var collections = [];
        colls.forEach(collection => {
            collections.push(collection.collection_id);
        });
        property.collections = collections;
        return property;
    }

    async propertyHosts(property,hostResults){
        var colls = hostResults.filter(col => col.property_id == property.id);
        var hosts = [];
        colls.forEach(host => {
            hosts.push(host.host_id);
        });
        property.hosts = hosts;
        return property;
    }

    async propertyDetailsFiles(property,mediaResults,presignedURLs){
        var xFileIds = [4,5,6];
        var xFileMap = {4:'display_image',5:'virtual_tour',6:'meals_menu'};
        var meds = mediaResults.filter(m => m.property_id == property.id && xFileIds.includes(m.media_type_id));
        meds.forEach(med =>{
            var presigned = presignedURLs?.find(psu => psu.fileName == med.media_filename);
            var fName = xFileMap[med.media_type_id];
            property[fName] = presigned?.url;
            if(med?.media_type_id == 4){
                property["display_image_id"] = med.id;
            }
        });
        return property;
    }

    async propertyDetailsPhotos(property,mediaResults,presignedURLs,photoCategories){
        var xFileIds = [4,5,6];
        var meds = mediaResults.filter(m => m.property_id == property.id && !xFileIds.includes(m.media_type_id));
        var flatCats = meds.flatMap(m=>m.display);
        var medCats = ['Pool','Living & Dining Space','Kitchen & Pantry','Bedroom','Bathrooms','Shared Spaces','Garden or Outdoor Areas','Facade & Exterior'];

        medCats = medCats.filter(function(item) {
            return flatCats.indexOf(item) > -1;
        });
        var medias = [];
        meds.forEach(med =>{
            var presigned = presignedURLs?.find(psu => psu.fileName == med.media_filename);
            medias.push({"media_id":med.id,"catgeory_name":med.display,"order":med.display_order,"title":med.title,"description":med.description,"filename":presigned?.url});
        });
        var mediaByCategory = {};
        var mediaByCategoryId = {};
        medCats.forEach(medCat =>{
            mediaByCategory[medCat] = medias.filter(m=> m.catgeory_name == medCat);
            var catIdSetting = photoCategories.find(c => c.display == medCat);
            if(catIdSetting){
                mediaByCategoryId[catIdSetting.id] = medias.filter(m=> m.catgeory_name == medCat);
            }
        });
        property.medias = mediaByCategory;
        property.mediasById = mediaByCategoryId;
        return property;
    }

    async getPresignedURLs(meds){
        const presignedUrlPromises = meds.map(async(object)=>{
            const urlParams = { Bucket: bucket+"/"+object.property_id, Key: object.media_filename, Expires: 3600 };
            const url = await S3Helper.getSignedUrlPromise(urlParams);
            return { fileName: object.media_filename, url };
        });
        try {
            const presignedUrls = await Promise.all(presignedUrlPromises);
            return presignedUrls;
        } catch (error) {
            console.log(error);
        }
    }

    async getAmenityImageURLs(amenities){
        const presignedUrlPromises = amenities.map(async(object)=>{
            const urlParams = { Bucket: amenityBucket, Key: object.media_filename, Expires: 3600 };
            const url = await S3Helper.getSignedUrlPromise(urlParams);
            return { fileName: object.media_filename, url };
        });
        try {
            const presignedUrls = await Promise.all(presignedUrlPromises);
            return presignedUrls;
        } catch (error) {
            console.log(error);
        }
    }

    async getDisplayImage(id){
        return this.getMediaURL(id,4);
    }

    async getMediaURL(property_id,media_type_id){
        const result = await pool.query('SELECT * FROM property_media WHERE property_id = ? and media_type_id = ?', [property_id,media_type_id]);
        const media = result[0][0];
        const urlParams = { Bucket: bucket+"/"+media.property_id, Key: media.media_filename, Expires: 3600 };
        const url = await S3Helper.getSignedUrlPromise(urlParams);
        return url;
    }

    async getById(id) {
        const result = await pool.query('SELECT * FROM properties WHERE id = ?', [id]);
        return result[0];
    }
    
    async getBySlug(slug) {
        const result = await pool.query('SELECT * FROM properties WHERE slug = ?', [slug]);
        return result[0];
    }

    async getByEzeeId(uniqueId){
        try {
            const result = await pool.query('SELECT * FROM properties WHERE channel_id = ?', [uniqueId]);
            return result[0][0];
        } catch (error) {
            console.log(error);
            throw error;
        }
    }

    async store(fieldValues) {
        try {
            const result = await pool.query('INSERT INTO properties SET ?', fieldValues);
            return result[0].insertId;
        } catch (error) {
            console.log(error);
            throw error;
        }
    }
  
    async save(fieldValues,id){
        return await pool.query('UPDATE properties SET ? WHERE id = ?',[fieldValues, id]);
    }

    async getMediaById(id){
        const media = await pool.query('select * from property_media where id = ?',[id]);
        return media[0][0];
    }

    async updateMediaById(id,filename){
        return await pool.query('update property_media set media_filename = ? where id = ?',[filename,id]);
    }

    async deleteMediaFile(id){
        await pool.query('delete from property_media where id = ?',[id]);
        return;
    }

    async clearAmenitiesForProperty(propertyId){
        return await pool.query('delete from property_amenities where property_id = ?',[propertyId]);
    }

    async clearCollectionsForProperty(propertyId){
        return await pool.query('delete from property_collections where property_id = ?',[propertyId]);
    }

    async clearHostsForProperty(propertyId){
        return await pool.query('delete from property_hosts where property_id = ?',[propertyId]);
    }

    async deleteProperty(propertyId){
        try {
            console.log(`=== Deleting Property ${propertyId} ===`);
            
            // First delete all related records
            await this.clearAmenitiesForProperty(propertyId);
            console.log(`Amenities cleared for property ${propertyId}`);
            
            await this.clearCollectionsForProperty(propertyId);
            console.log(`Collections cleared for property ${propertyId}`);
            
            await this.clearHostsForProperty(propertyId);
            console.log(`Hosts cleared for property ${propertyId}`);
            
            // Delete all media files
            const mediaResult = await pool.query('select media_filename from property_media where property_id = ?', [propertyId]);
            if (mediaResult[0] && mediaResult[0].length > 0) {
                for (const media of mediaResult[0]) {
                    await pool.query('delete from property_media where property_id = ? and media_filename = ?', [propertyId, media.media_filename]);
                    console.log(`Media record deleted: ${media.media_filename}`);
                }
            }
            
            // Finally delete the main property record
            const result = await pool.query('delete from properties where id = ?', [propertyId]);
            console.log(`Property ${propertyId} deleted successfully`);
            
            return result[0].affectedRows > 0;
        } catch (error) {
            console.log(`Error deleting property ${propertyId}:`, error);
            throw error;
        }
    }

    async nextDisplayOrder(propertyId,categoryId){
        const result = await pool.query('select max(display_order) as max_display from property_media where property_id = ? and media_type_id = ?',[propertyId,categoryId]);
        return result[0][0].max_display + 1;
    }

    async updateMediaOrder(id,order){
        console.log(`changing order of ${id} to ${order}`);
        return await pool.query('update property_media set display_order = ? where id = ?',[order,id]);
    }

    async update(id, { name, description,photo }) {
        return await pool.query('UPDATE destinations SET name = ?, description = ?, photo = ? WHERE id = ?', [name, description, photo, id]);
    }
  
    async updatePhotoLink(id, photo) {
        return await pool.query('UPDATE destinations SET photo = ? WHERE id = ?', [photo, id]);
    }

    async deleteDestination(id) {
        return await pool.query('DELETE FROM destinations WHERE id = ?', [id]);
    }

    async setPropertyAmenity(values){
        const result = await pool.query('INSERT INTO property_amenities SET ?', values);
        return result[0].insertId;
    }

    async setPropertyCollection(values){
        const result = await pool.query('INSERT INTO property_collections SET ?', values);
        return result[0].insertId;
    }

    async setPropertyHosts(values){
        const result = await pool.query('INSERT INTO property_hosts SET ?', values);
        return result[0].insertId;
    }

    async setPropertyMedia(values){
        const result = await pool.query('INSERT INTO property_media SET ?', values);
        return result[0].insertId;
    }

    async userFavourites(userId){
        const results = await pool.query('select property_id from user_favourites where user_id = ?',[userId]);
        return results[0];
    }

    async favourite(userId,propertyId){
        const deleteRecord = await pool.query('DELETE FROM user_favourites WHERE user_id = ? and property_id', [userId,propertyId]);
        const result = await pool.query('INSERT INTO user_favourites (user_id,property_id) values (?,?)', [userId,propertyId]);
        return result[0].insertId;
    }

    async unFavourite(userId,propertyId){
        return await pool.query('DELETE FROM user_favourites WHERE user_id = ? and property_id', [userId,propertyId]);
    }

    async forBookingInfoDisplay(id){
        const result = await pool.query('select listing_name as property_name,address_line_1 as address from properties where id = ?', [id]);
        var property = result[0][0];
        property.cover_image = await this.getMediaURL(id,4);
        return property;
    }

    /* Admin panel functions */
    async listing(){
        const results = await pool.query('select pm.media_filename,p.* from properties p,property_media pm where p.id = pm.property_id and pm.media_type_id = 4 order by p.display_order');
        return results[0];
    }

    async getMaxDisplayOrder(){
        const results = await pool.query('select max(display_order) as maxOrder from properties');
        return results[0][0].maxOrder + 1;
    }

    async propertiesList(){
        try{
            const results = await pool.query("select id,channel_id, listing_name,internal_name from properties");
            return results[0];
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    async amenitiesByProperty(propertyId){
        try{
            const results = await pool.query("select * from amenities where id in (select amenity_id from property_amenities where property_id = ?)",[propertyId]);
            return results[0];
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    async propertiesByHost(hostId){
        try{
            const results = await pool.query("select p.id,p.listing_name,p.internal_name,p.slug,p.number_of_bedrooms,p.number_of_bathrooms,p.number_of_guests,p.number_of_extra_guests,p.google_latitude,p.google_longitude,p.address_line_1,p.address_line_2,p.city,p.state,p.country,pm.media_filename from properties p, property_media pm where p.id in (select property_id from property_hosts where host_id = ?) and p.id = pm.property_id and pm.media_type_id = 4",[hostId]);
            
            // Calculate current financial year start date (April 1st of current year or previous year)
            const now = new Date();
            const currentYear = now.getFullYear();
            const currentMonth = now.getMonth() + 1; // getMonth() returns 0-11, so add 1
            
            let financialYearStart;
            if (currentMonth >= 4) {
                // If current month is April or later, financial year started this year
                financialYearStart = `${currentYear}-04-01`;
            } else {
                // If current month is before April, financial year started last year
                financialYearStart = `${currentYear - 1}-04-01`;
            }
            
            // Filter for current financial year only
            const gbvs = await pool.query("SELECT count(bt.id) as count,sum(bt.totalAmountBeforeTax) as gbv,sum(bt.taCommision) as taCommision,p.id as property FROM booking_tariffs bt,bookings b, properties p, property_hosts ph where bt.booking_id = b.id and b.property_id = p.id and b.currentStatus not in ('Void','Cancel') and p.id = ph.property_id and ph.host_id = ? and b.end >= ? group by property",[hostId, financialYearStart]);
            const nights = await pool.query("SELECT count(br.id) as nights,p.id as property FROM booking_rentalInfo br,bookings b, properties p, property_hosts ph where br.booking_id = b.id and b.property_id = p.id and b.currentStatus not in ('Void','Cancel') and p.id = ph.property_id and ph.host_id = ? and b.end >= ? group by property",[hostId, financialYearStart]);
            return {properties:results[0],gbvs:gbvs[0],nights:nights[0]};
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    async propertyPerformance(propertyId,start,end){
        try{
            const performance = await pool.query("SELECT count(bt.id) as count, sum(totalAmountAfterTax) as totalAmountAfterTax, sum(totalAmountBeforeTax) as totalAmountBeforeTax, sum(totalTax) as totalTax, sum(taCommision) as taCommision FROM bookings b, booking_tariffs bt where b.property_id = ? and b.end >= ? and b.end <= ? and b.id = bt.booking_id",[propertyId,start,end]);
            const serviceRequests = await pool.query("SELECT count(id) as count, sum(amount) as amount FROM `service_requests` where property_id = ? and created_at >= ? and created_at <= ?",[propertyId,start,end]);
            const nights = await pool.query("SELECT count(*) as count FROM booking_rentalInfo where booking_id in (select id from bookings where property_id = ? and end >= ? and end <= ?)",[propertyId,start,end]);
            return {performance:performance[0][0],serviceRequests:serviceRequests[0][0],nights:nights[0][0]};
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    async earningsByMonth(propertyId,start,end){
        try{
            // Use same logic as propertiesByHost: include bookings that ended during the financial year
            // Also use same calculation: gbv - taCommision (net booking value)
            const earnings = await pool.query("SELECT count(bt.id) as count, sum(bt.totalAmountBeforeTax - bt.taCommision) amount,DATE_FORMAT(b.end,'%m-%Y') as date FROM booking_tariffs bt, bookings b where b.property_id = ? and b.end >= ? and b.currentStatus not in ('Void','Cancel') and b.id = bt.booking_id group by date",[propertyId,start]);
            const nights = await pool.query("select count(id) as count,DATE_FORMAT(effectiveDate,'%m-%Y') as date from booking_rentalInfo where booking_id in (select id from bookings where property_id = ? and end >= ? and currentStatus not in ('Void','Cancel')) and effectiveDate >= ? group by date",[propertyId,start,start]); 
            
            console.log(`Earnings data for property ${propertyId}:`, earnings[0]);
            console.log(`Nights data for property ${propertyId}:`, nights[0]);
            
            return {earnings:earnings[0],nights:nights[0]};
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    // New method for stacked earnings data by month with proper owner earnings calculation
    async stackedEarningsByMonth(propertyId, start, end) {
        try {
            // Get checkout earnings (completed bookings) with proper owner earnings calculation
            const checkoutEarnings = await pool.query(`
                SELECT 
                    DATE_FORMAT(b.end, '%d') as day,
                    SUM(bt.totalAmountBeforeTax + bt.totalTax - bt.taCommision) as amount,
                    COUNT(bt.id) as count,
                    'checkedOut' as status,
                    SUM(bt.totalAmountBeforeTax + bt.totalTax) as totalCharges,
                    SUM(bt.taCommision) as totalCommission,
                    SUM(bt.totalTax) as totalGST
                FROM booking_tariffs bt 
                JOIN bookings b ON b.id = bt.booking_id 
                WHERE b.property_id = ? 
                AND b.end BETWEEN ? AND ? 
                AND b.currentStatus NOT IN ('Void', 'Cancel', 'Block')
                GROUP BY DATE_FORMAT(b.end, '%d')
            `, [propertyId, start, end]);

            // Get currently hosting earnings (ongoing bookings) with proper owner earnings calculation
            // These are bookings that started before the month and ended after the month (ongoing during the month)
            const currentlyHostingEarnings = await pool.query(`
                SELECT 
                    DATE_FORMAT(?, '%d') as day,
                    SUM(bt.totalAmountBeforeTax + bt.totalTax - bt.taCommision) as amount,
                    COUNT(bt.id) as count,
                    'currentlyHosting' as status,
                    SUM(bt.totalAmountBeforeTax + bt.totalTax) as totalCharges,
                    SUM(bt.taCommision) as totalCommission,
                    SUM(bt.totalTax) as totalGST
                FROM booking_tariffs bt 
                JOIN bookings b ON b.id = bt.booking_id 
                WHERE b.property_id = ? 
                AND b.start < ? 
                AND b.end > ?
                AND b.currentStatus NOT IN ('Void', 'Cancel', 'Block')
            `, [start, propertyId, start, end]);

            // Get checking in earnings (upcoming bookings starting in this month) with proper owner earnings calculation
            // Only include upcoming bookings if the selected month is current or future
            const currentDate = new Date();
            const selectedDate = new Date(start);
            const isCurrentOrFutureMonth = selectedDate >= new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
            
            let checkingInEarnings = { 0: [] }; // Default empty result
            
            if (isCurrentOrFutureMonth) {
                checkingInEarnings = await pool.query(`
                    SELECT 
                        DATE_FORMAT(b.start, '%d') as day,
                        SUM(bt.totalAmountBeforeTax + bt.totalTax - bt.taCommision) as amount,
                        COUNT(bt.id) as count,
                        'checkingIn' as status,
                        SUM(bt.totalAmountBeforeTax + bt.totalTax) as totalCharges,
                        SUM(bt.taCommision) as totalCommission,
                        SUM(bt.totalTax) as totalGST
                    FROM booking_tariffs bt 
                    JOIN bookings b ON b.id = bt.booking_id 
                    WHERE b.property_id = ? 
                    AND b.start BETWEEN ? AND ? 
                    AND b.currentStatus NOT IN ('Void', 'Cancel', 'Block')
                    GROUP BY DATE_FORMAT(b.start, '%d')
                `, [propertyId, start, end]);
            }

            return {
                checkout: checkoutEarnings[0],
                currentlyHosting: currentlyHostingEarnings[0],
                checkingIn: checkingInEarnings[0]
            };
        } catch (error) {
            console.log(error);
            throw error;
        }
    }

    // New method to get detailed booking information for owners app (similar to admin panel)
    async getDetailedBookingsForMonth(propertyId, start, end) {
        try {
            const bookings = await pool.query(`
                SELECT 
                    b.id,
                    b.uniqueId,
                    CONCAT(b.firstname, ' ', b.lastname) as guestName,
                    DATE_FORMAT(b.start, '%d %b %y') as checkInDate,
                    DATE_FORMAT(b.end, '%d %b %y') as checkOutDate,
                    b.nights,
                    b.currentStatus,
                    b.listing_name as propertyName,
                    bt.totalAmountBeforeTax / b.nights as price_per_night,
                    bt.totalAmountBeforeTax + bt.totalTax as totalCharges,
                    bt.totalAmountBeforeTax,
                    bt.totalTax as gstAmount,
                    bt.taCommision as otaCommission,
                    bt.totalAmountBeforeTax + bt.totalTax - bt.taCommision as ownerEarnings,
                    b.source,
                    CASE 
                        WHEN (bt.totalAmountBeforeTax / b.nights) <= 7500 THEN '5%'
                        ELSE '18%'
                    END as gstRate,
                    b.start,
                    b.end,
                    CASE 
                        WHEN b.end BETWEEN ? AND ? THEN 'checkedOut'
                        WHEN b.start < ? AND b.end > ? THEN 'currentlyHosting'
                        WHEN b.start BETWEEN ? AND ? THEN 'checkingIn'
                        ELSE 'other'
                    END as bookingStatus
                FROM bookings b
                JOIN booking_tariffs bt ON b.id = bt.booking_id
                WHERE b.property_id = ? 
                AND (
                    (b.end BETWEEN ? AND ?) OR 
                    (b.start BETWEEN ? AND ?) OR
                    (b.start < ? AND b.end > ?)
                )
                AND b.currentStatus NOT IN ('Void', 'Cancel', 'Block')
                ORDER BY b.start DESC
            `, [start, end, start, end, start, end, propertyId, start, end, start, end, start, end]);
            
            return bookings[0];
        } catch (error) {
            console.log(error);
            throw error;
        }
    }

    async calendar(propertyId,start,end){
        try{
            const bookings = await pool.query("SELECT id,start,end,currentStatus,status FROM bookings where property_id = ? and currentStatus not in ('Void','Cancel') and (DATE(start) BETWEEN ? AND ? OR DATE(end) BETWEEN ? AND ?)",[propertyId,start,end,start,end]);
            return bookings[0];
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    async otaLinks(propertyId){
        try{
            const links = await pool.query("SELECT p.property_id,s.display as channel,p.link FROM property_listings p, settings s where p.property_id = ? and p.ota_id = s.id;",[propertyId]);
            return links[0];
        }catch(error){
            console.log(error);
            throw error;
        }
    }

    async referAProperty(values){
        const result = await pool.query('INSERT INTO property_referrals SET ?', values);
        return result[0].insertId;
    }

    async getPropertyEarnings(propertyId) {
        try {
            const earnings = await pool.query(`
                SELECT 
                    DATE_FORMAT(b.start, '%Y-%m') as month,
                    SUM(bt.totalAmountBeforeTax) as amount,
                    COUNT(b.id) as count
                FROM booking_tariffs bt 
                JOIN bookings b ON b.id = bt.booking_id 
                WHERE b.property_id = ? 
                AND b.currentStatus NOT IN ('Void', 'Cancel', 'Block')
                GROUP BY DATE_FORMAT(b.start, '%Y-%m')
                ORDER BY month DESC
                LIMIT 12
            `, [propertyId]);
            return earnings[0];
        } catch (error) {
            console.log(error);
            return [];
        }
    }

    async getPropertyMedia(propertyId) {
        try {
            const media = await pool.query(`
                SELECT 
                    pm.*,
                    s.display as media_type_name
                FROM property_media pm 
                JOIN settings s ON pm.media_type_id = s.id 
                WHERE pm.property_id = ? 
                ORDER BY pm.display_order
            `, [propertyId]);
            return media[0];
        } catch (error) {
            console.log(error);
            return [];
        }
    }

    async checkPropertyAccess(propertyId, userId) {
        try {
            const result = await pool.query(`
                SELECT COUNT(*) as count 
                FROM property_hosts 
                WHERE property_id = ? AND host_id = ?
            `, [propertyId, userId]);
            
            return result[0][0].count > 0;
        } catch (error) {
            console.log(error);
            return false;
        }
    }
}
module.exports = Property;