const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')

const Community = sequelize.define('community', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    description: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
    // type: { type: Sequelize.STRING, allowNull: false,},
    type: { type: Sequelize.STRING, allowNull: true, defaultValue:null },
    phone: { type: Sequelize.STRING, allowNull: true, defaultValue:null },
    email: { type: Sequelize.STRING, allowNull: true, defaultValue:null },
    address: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
    poNumber: { type: Sequelize.TEXT, allowNull: true, defaultValue:null }, // Comma-separated PO detail IDs
    city: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
    state: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
    zip: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
    clients: { type: Sequelize.TEXT, allowNull: true, defaultValue:null },
    thumbnail: { type: Sequelize.STRING(2000), allowNull: true, defaultValue:null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    tax_category :{ type: Sequelize.INTEGER, allowNull: true },
    card_order:{ type: Sequelize.INTEGER, allowNull: false,defaultValue: 1 },
    show_address:{ type: Sequelize.INTEGER, allowNull: false,defaultValue: 0 },
    lng: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    lat: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    sales_rep_names: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
}, { timestamps: false, tableName:'community' })

const CommunityType = sequelize.define('community_type', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
}, { timestamps: false, tableName:'community_type' })

const PoDetails = sequelize.define('po_details', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    community_id: { type: Sequelize.INTEGER, allowNull: false },
    po_number: { type: Sequelize.STRING, allowNull: false },
    po_name: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
}, { timestamps: false, tableName:'po_details' })

const addCommunity = async (data) => {
    console.log(data,"ts7");
    
    try {
        let results = await Community.create(data)
        if (results) {
            return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the community ", error);
        return false;
    }
}

const isCommunityNameExist = async (name,type, communityId = 0) => {
    let where = {};
    if (communityId !== 0) {
        where = { id: { [Sequelize.Op.not]: communityId } };
        // console.log("communityId -- ", communityId)
    }
    const results = await Community.findAll({
        attributes: ['id'],
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: name } },
                // { type: { [Sequelize.Op.eq]: type } },
                { status: { [Sequelize.Op.ne]: 2 } },
                where
            ]
        }
    });
    console.log("results : ", results.length)
    return results.length > 0;
}

// const getAllCommunities = async (all = false ,sort,filterClient, filterSalesRep, clientId, userType,isQuotation,From=null,teamId=null,filterData={}) => {
//     try {
//         let whereCondition;
//         let whereCondition2 = '';
//         let salesRepFilter = '';
//         let {userType:filterUserType,filterType:filterType} = filterData;   
//         let NewUserType = filterType? filterUserType : userType;
//         console.log("$$$$$$$$$$$$$$$$$userType",NewUserType);
//         if (From === 'addSalesOrder' && teamId && NewUserType !== 8 && NewUserType !== 1) {
//             salesRepFilter = `AND FIND_IN_SET(${teamId}, c.sales_rep_names) > 0`;
//         }
//         if (userType == 3) {
//             whereCondition2 = ` AND FIND_IN_SET(${clientId}, c.clients) `;
//         }

//         if (all) {
//             whereCondition = `c.status != 2 ${whereCondition2}`;
//         }
//         else {
//             whereCondition = `c.status = 1 ${whereCondition2}`;
//         }
//          let createdAt = ''
//          if (sort === 'new') {
//             createdAt = `ORDER BY c.id DESC `
//           } else if (sort === 'old') {
//             createdAt = `ORDER BY c.id ASC `
//           } else if (sort === 'custom_order') {
//             createdAt = `ORDER BY c.card_order ASC`
//           } else {
//             createdAt = `ORDER BY c.timestamp DESC`
//           }

//           if (filterClient && filterClient.length > 0) {
//             const clientConditions = filterClient
//               .map((id) => `FIND_IN_SET(${id}, c.clients)`)
//               .join(' OR ');
//             whereCondition += ` AND (${clientConditions})`;
//           }

//           if (filterSalesRep && filterSalesRep.length > 0) {
//             const salesRepConditions = filterSalesRep
//               .map((id) => `FIND_IN_SET(${id}, c.sales_rep_names)`)
//               .join(' OR ');
//             whereCondition += ` AND (${salesRepConditions})`;
//           }
//           let countCondition = '';
//           if (isQuotation) {
//             countCondition = ` AND rq.quotation = 1 `;
//           }else{
//             countCondition = ` and rq.status != 8 `;
//           }

//         // let query = await sequelize.query(
//         //     ` SELECT c.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, COALESCE(r.no_of_requests, 0) AS no_of_requests, COALESCE(p.no_of_projects, 0) AS no_of_projects`
//         //     + ` FROM community c `
//         //     + ` LEFT JOIN users u ON c.added_by = u.id `
//         //     + ` LEFT JOIN ( `
//         //     + ` SELECT COUNT(id) AS no_of_requests,community FROM requests WHERE status!=0 GROUP BY community `
//         //     + ` ) r ON c.id=r.community `
//         //     + ` LEFT JOIN ( `
//         //     + ` SELECT COUNT(p.id) AS no_of_projects,p.community FROM projects p WHERE NOT EXISTS (SELECT 1 FROM installations i WHERE i.project_id = p.id) GROUP BY p.community `
//         //     + ` ) p ON c.id=p.community `
//         //     + ` WHERE ${whereCondition} `
//         //     + ` ORDER BY c.id DESC`,
//         // )
//         let query=await sequelize.query(`SELECT c.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, 
//             COALESCE(r.no_of_requests, 0) AS no_of_requests, 
//             COALESCE(p.no_of_projects, 0) AS no_of_projects, 
//             COALESCE(q.no_of_quotations, 0) AS no_of_quotations,
//             COALESCE(i.completed_installs_count, 0) AS no_of_installs,
//             COALESCE(d.completed_design_count, 0) AS no_of_designs,
//             GROUP_CONCAT(DISTINCT cu.user_id) AS client_user_ids,
//             GROUP_CONCAT(DISTINCT cu.name) AS clientName,
//             MIN(up.profile_pic) AS client_profile_pic,
//             COALESCE((SELECT GROUP_CONCAT(DISTINCT sm.id ORDER BY sm.id) 
//             FROM staff_members sm 
//             WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS sales_rep_ids,
//             COALESCE((SELECT GROUP_CONCAT(DISTINCT CONCAT(sm.first_name,' ',COALESCE(sm.last_name,'')) ORDER BY sm.id SEPARATOR ', ') 
//             FROM staff_members sm 
//             WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS sales_rep_names
//         FROM community c `
//         +`  LEFT JOIN users u ON c.added_by = u.id `
//         +`  LEFT JOIN ( SELECT COUNT(rq.id) AS no_of_requests, rq.community FROM requests rq JOIN customer_orders co 
//                 on co.request_id = rq.id WHERE rq.status != 0 ${countCondition} GROUP BY rq.community ) r 
//                 ON c.id = r.community `
//         +`  LEFT JOIN ( SELECT COUNT(p.id) AS no_of_projects, p.community FROM projects p
//                 JOIN requests rt ON p.request_id = rt.id  
//                 WHERE rt.status != 0 
//                 AND (
//                     -- Hold tab: projects with hold = 1
//                     p.hold = 1 
//                     OR 
//                     -- Pre-Production tab: projects with status 0 or 1 and hold = 0
//                     ((p.status = 1 OR p.status = 0) AND p.hold = 0)
//                     OR 
//                     -- Production tab: projects with status != 0, 1, 3 and hold = 0
//                     (p.status != 0 AND p.status != 1 AND p.status != 3 AND p.hold = 0)
//                 )  GROUP BY p.community ) p 
//                 ON c.id = p.community `
//         // +`  LEFT JOIN ( SELECT COUNT(p.id) AS no_of_projects, p.community FROM projects p 
//         //         WHERE NOT EXISTS (SELECT 1 FROM installations i WHERE i.project_id = p.id) GROUP BY p.community ) p 
//         //         ON c.id = p.community `
//         +`  LEFT JOIN ( SELECT COUNT(rq.id) AS no_of_quotations, rq.community FROM requests rq WHERE rq.status = 8 GROUP BY rq.community ) q 
//                 ON c.id = q.community `
//         +`  LEFT JOIN (
//                 SELECT 
//                 COUNT(DISTINCT inst.request_id) AS completed_installs_count, 
//                 rt.community 
//                 FROM installations inst 
//                 JOIN requests rt ON inst.request_id = rt.id 
//                 WHERE inst.complete_status = 1 AND rt.installation_acceptance IS NOT NULL AND rt.status != 0 
//                 GROUP BY rt.community
//             ) i ON c.id = i.community `
//         +`  LEFT JOIN (
//                 SELECT 
//                     COUNT(do.id) AS completed_design_count, 
//                     do.community 
//                 FROM design_orders do 
//                 WHERE do.status = 3 
//                 GROUP BY do.community
//                 ) d ON c.id = d.community `
//         +` LEFT JOIN partners cu ON FIND_IN_SET(cu.id, c.clients) `
//         +` LEFT JOIN users up ON up.id = cu.user_id`
//         +` WHERE ${whereCondition} ${salesRepFilter}`
//         +` GROUP BY c.id  `
//         + `${createdAt} `)
//         // +` GROUP BY c.id   ORDER BY c.id ASC`)
//         let data;
//         if (query.length > 0) {
//             data = query[0];
//             // Fetch PO details for each community
//             for (let community of data) {
//                 const poDetails = await getPoDetailsByCommunityId(community.id);
//                 community.po_details = poDetails;
//             }
//             return data;
//         } else {
//             return [];
//         }
//     }
//     catch (error) {
//         console.log(error);
//         throw new Error("Server error"+error);
//     }
// }

const getAllCommunities = async (all = false ,sort,filterClient, filterSalesRep, clientId, userType,isQuotation,From=null,teamId=null,filterData={}) => {
    try {
        let whereCondition;
        let whereCondition2 = '';
        let salesRepFilter = '';
        let {userType:filterUserType,filterType:filterType} = filterData;   
        let NewUserType = filterType? filterUserType : userType;
        console.log("$$$$$$$$$$$$$$$$$userType",NewUserType);

        if (From === 'addSalesOrder' && teamId && NewUserType !== 8 && NewUserType !== 1) {
            salesRepFilter = `AND FIND_IN_SET(${teamId}, c.sales_rep_names) > 0`;
        }
        if (userType == 3) {
            whereCondition2 = ` AND FIND_IN_SET(${clientId}, c.clients) `;
        }

        if (all) {
            whereCondition = `c.status != 2 ${whereCondition2}`;
        }
        else {
            whereCondition = `c.status = 1 ${whereCondition2}`;
        }

        let createdAt = '';
        if (sort === 'new') {
            createdAt = `ORDER BY c.id DESC `;
        } else if (sort === 'old') {
            createdAt = `ORDER BY c.id ASC `;
        } else if (sort === 'custom_order') {
            createdAt = `ORDER BY c.card_order ASC`;
        } else {
            createdAt = `ORDER BY c.timestamp DESC`;
        }

        // Filter by client and salesRep if provided
        if (filterClient && filterClient.length > 0) {
            const clientConditions = filterClient
              .map((id) => `FIND_IN_SET(${id}, c.clients)`)
              .join(' OR ');
            whereCondition += ` AND (${clientConditions})`;
        }

        if (filterSalesRep && filterSalesRep.length > 0) {
            const salesRepConditions = filterSalesRep
              .map((id) => `FIND_IN_SET(${id}, c.sales_rep_names)`)
              .join(' OR ');
            whereCondition += ` AND (${salesRepConditions})`;
        }

        let countCondition = '';
        if (isQuotation) {
            countCondition = ` AND rq.quotation = 1 `;
        } else {
            countCondition = ` and rq.status != 8 `;
        }

        // Main query fetching community data
        let query = await sequelize.query(`
            SELECT c.*, 
                   CONCAT(u.first_name, ' ', u.last_name) AS authorized_by, 
                   COALESCE(r.no_of_requests, 0) AS no_of_requests, 
                   COALESCE(p.no_of_projects, 0) AS no_of_projects, 
                   COALESCE(q.no_of_quotations, 0) AS no_of_quotations,
                   COALESCE(i.completed_installs_count, 0) AS no_of_installs,
                   COALESCE(e.no_of_estimates, 0) AS no_of_estimates,
                   COALESCE(d.completed_design_count, 0) AS no_of_designs,
                   GROUP_CONCAT(DISTINCT cu.user_id) AS client_user_ids,
                   GROUP_CONCAT(DISTINCT cu.name) AS clientName,
                   MIN(up.profile_pic) AS client_profile_pic,
                   COALESCE((SELECT GROUP_CONCAT(DISTINCT sm.id ORDER BY sm.id) 
                   FROM staff_members sm 
                   WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' 
                   AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS sales_rep_ids,
                   COALESCE((SELECT GROUP_CONCAT(DISTINCT CONCAT(sm.first_name,' ',COALESCE(sm.last_name,'')) 
                   ORDER BY sm.id SEPARATOR ', ') 
                   FROM staff_members sm 
                   WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' 
                   AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS sales_rep_names
            FROM community c
            LEFT JOIN users u ON c.added_by = u.id
            LEFT JOIN ( 
                SELECT COUNT(rq.id) AS no_of_requests, rq.community 
                FROM requests rq 
                JOIN customer_orders co on co.request_id = rq.id 
                WHERE rq.status != 0 ${countCondition} 
                GROUP BY rq.community 
            ) r ON c.id = r.community 
            LEFT JOIN ( 
                SELECT COUNT(p.id) AS no_of_projects, p.community 
                FROM projects p
                JOIN requests rt ON p.request_id = rt.id  
                WHERE rt.status != 0 
                AND (
                    p.hold = 1 OR 
                    ((p.status = 1 OR p.status = 0) AND p.hold = 0) OR 
                    (p.status != 0 AND p.status != 1 AND p.status != 3 AND p.hold = 0)
                )  
                GROUP BY p.community 
            ) p ON c.id = p.community 
            LEFT JOIN ( 
                SELECT COUNT(rq.id) AS no_of_quotations, rq.community 
                FROM requests rq 
                WHERE rq.status = 8 
                GROUP BY rq.community 
            ) q ON c.id = q.community 
            LEFT JOIN ( 
                SELECT COUNT(DISTINCT rq.id) AS no_of_estimates, rq.community 
                FROM requests rq 
                LEFT JOIN design_orders d_ord ON d_ord.request_id = rq.id 
                WHERE ((rq.isFromEstimate = 1 and rq.status NOT IN (0,8)) OR (d_ord.request_id IS NOT NULL and d_ord.status <> 0 and d_ord.hold = 0 and rq.status = 0 and rq.isFromDesignOrder = 1)) 
                GROUP BY rq.community 
            ) e ON c.id = e.community 
            LEFT JOIN (
                SELECT COUNT(DISTINCT inst.request_id) AS completed_installs_count, rt.community 
                FROM installations inst 
                JOIN requests rt ON inst.request_id = rt.id 
                WHERE inst.complete_status = 1 AND rt.installation_acceptance IS NOT NULL AND rt.status != 0 
                GROUP BY rt.community
            ) i ON c.id = i.community 
            LEFT JOIN (
                SELECT COUNT(do.id) AS completed_design_count, do.community 
                FROM design_orders do 
                WHERE do.status = 3 
                GROUP BY do.community
            ) d ON c.id = d.community 
            LEFT JOIN partners cu ON FIND_IN_SET(cu.id, c.clients) 
            LEFT JOIN users up ON up.id = cu.user_id
            WHERE ${whereCondition} ${salesRepFilter}
            GROUP BY c.id  
            ${createdAt}
        `);

        let data;
        // if (query.length > 0) {
        //     data = query[0];

        //     // Fetch PO details for each community concurrently using Promise.all
        //     const poDetailsPromises = data.map(community => getPoDetailsByCommunityId(community.id));
        //     const poDetails = await Promise.all(poDetailsPromises);

        //     // Attach PO details to each community
        //     data.forEach((community, index) => {
        //         community.po_details = poDetails[index];
        //     });

        //     return data;
        if (query.length > 0) {
            data = query[0];

            // Step 1: Fetch all PO details in bulk for the communities
            const communityIds = data.map(community => community.id);

            // Fetch PO details for all communities at once
            const poDetails = await getPoDetailsForCommunities(communityIds);

            // Step 2: Map PO details to corresponding communities
            data.forEach(community => {
                // Find PO details for the current community
                community.po_details = poDetails.filter(po => po.community_id === community.id);
            });

            return data;
        } else {
            return [];
        }

    } catch (error) {
        console.log(error);
        throw new Error("Server error: " + error);
    }
}

// Modify getPoDetailsByCommunityId to handle multiple communityIds
const getPoDetailsForCommunities = async (communityIds) => {
    try {
        // Fetch PO details for all communities in one query
        const results = await PoDetails.findAll({
            where: {
                community_id: {
                    [Sequelize.Op.in]: communityIds  // Use Sequelize Op.in to fetch details for multiple community_ids
                },
                status: 1
            },
            order: [['timestamp', 'DESC']]
        });

        return results;
    } catch (error) {
        console.error("Error fetching PO details ", error);
        return [];
    }
};

const getAllCommunitiesWithProjectCount = async (all = false ,sort,filterClient, clientId, userType) => {
    try {
        let whereCondition;
        let whereCondition2 = '';

        if (userType == 3) {
            whereCondition2 = ` AND FIND_IN_SET(${clientId}, c.clients) `;
        }

        if (all) {
            whereCondition = `c.status != 2 ${whereCondition2}`;
        }
        else {
            whereCondition = `c.status = 1 ${whereCondition2}`;
        }
         let createdAt = ''
         if (sort === 'new') {
            createdAt = `ORDER BY c.id DESC `
          } else if (sort === 'old') {
            createdAt = `ORDER BY c.id ASC `
          } else if (sort === 'custom_order') {
            createdAt = `ORDER BY c.card_order ASC`
          } else {
            createdAt = `ORDER BY c.timestamp DESC`
          }

          if (filterClient && filterClient.length > 0) {
            const clientConditions = filterClient
              .map((id) => `FIND_IN_SET(${id}, c.clients)`)
              .join(' OR ');
            whereCondition += ` AND (${clientConditions})`;
          }
          
        let query=await sequelize.query(`SELECT c.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, 
            COALESCE(r.no_of_requests, 0) AS no_of_requests, 
            COALESCE(p.no_of_projects, 0) AS no_of_projects, 
            COALESCE(i.completed_installs_count, 0) AS no_of_installs,
            COALESCE(d.completed_design_count, 0) AS no_of_designs,
            GROUP_CONCAT(DISTINCT cu.user_id) AS client_user_ids,
            GROUP_CONCAT(DISTINCT cu.name) AS clientName,
            MIN(up.profile_pic) AS client_profile_pic,
            COALESCE(p.no_of_projects, 0) AS project_count,
            COALESCE((SELECT GROUP_CONCAT(DISTINCT CONCAT(sm.first_name,' ',COALESCE(sm.last_name,'')) ORDER BY sm.id SEPARATOR ', ') 
             FROM staff_members sm 
             WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS sales_rep_names
        FROM community c `
        +`  LEFT JOIN users u ON c.added_by = u.id `
        +`  LEFT JOIN ( SELECT COUNT(rq.id) AS no_of_requests, rq.community FROM requests rq JOIN customer_orders co 
                on co.request_id = rq.id WHERE rq.status != 0 and rq.status != 8 GROUP BY rq.community ) r 
                ON c.id = r.community `
        +`  LEFT JOIN ( SELECT COUNT(p.id) AS no_of_projects, p.community FROM projects p
                JOIN requests rt ON p.request_id = rt.id  
                WHERE rt.status != 0 
                AND (
                    -- Hold tab: projects with hold = 1
                    p.hold = 1 
                    OR 
                    -- Pre-Production tab: projects with status 0 or 1 and hold = 0
                    ((p.status = 1 OR p.status = 0) AND p.hold = 0)
                    OR 
                    -- Production tab: projects with status != 0, 1, 3 and hold = 0
                    (p.status != 0 AND p.status != 1 AND p.status != 3 AND p.hold = 0)
                )  GROUP BY p.community ) p 
                ON c.id = p.community `
        // +`  LEFT JOIN ( SELECT COUNT(p.id) AS no_of_projects, p.community FROM projects p 
        //         WHERE NOT EXISTS (SELECT 1 FROM installations i WHERE i.project_id = p.id) GROUP BY p.community ) p 
        //         ON c.id = p.community `
        +`  LEFT JOIN (
                SELECT 
                COUNT(DISTINCT inst.request_id) AS completed_installs_count, 
                rt.community 
                FROM installations inst 
                JOIN requests rt ON inst.request_id = rt.id 
                WHERE inst.complete_status = 1 AND rt.installation_acceptance IS NOT NULL AND rt.status != 0 
                GROUP BY rt.community
            ) i ON c.id = i.community `
        +`  LEFT JOIN (
                SELECT 
                    COUNT(do.id) AS completed_design_count, 
                    do.community 
                FROM design_orders do 
                WHERE do.status = 3 
                GROUP BY do.community
                ) d ON c.id = d.community `
        +` LEFT JOIN partners cu ON FIND_IN_SET(cu.id, c.clients) `
        +` LEFT JOIN users up ON up.id = cu.user_id`
        +` WHERE ${whereCondition}`
        +` GROUP BY c.id  `
        + `${createdAt} `)
        // +` GROUP BY c.id   ORDER BY c.id ASC`)
        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error"+error);
    }
}

const getAllCommunitiesCount = async (userId, userType, all = false) => {
    try {
        let whereCondition = all ? `c.status != 2` : `c.status = 1`;

        let userCondition = ``;
        let userTypeCondition = ``;

        if (userType == 3) {
            // Partners should only see communities they are linked with
            userCondition = ` AND pr.user_id = ${userId} `;
            userTypeCondition = `
                LEFT JOIN partners pr ON FIND_IN_SET(pr.id, c.clients) 
                AND pr.user_id = ${userId}
            `;
        }

        let query = await sequelize.query(`
        SELECT 
            c.*, 
            CONCAT(usr.first_name, ' ', usr.last_name) AS authorized_by, 
            COALESCE(r.no_of_requests, 0) AS no_of_requests, 
            COALESCE(p.no_of_projects, 0) AS no_of_projects, 
            GROUP_CONCAT(DISTINCT cu.user_id) AS client_user_ids, 
            GROUP_CONCAT(DISTINCT cu.name) AS clientName
        FROM community c
        LEFT JOIN users usr ON c.added_by = usr.id
        LEFT JOIN (
            SELECT COUNT(rq.id) AS no_of_requests, rq.community
            FROM requests rq 
            JOIN customer_orders co ON co.request_id = rq.id 
            WHERE rq.status != 0 AND rq.status != 8 
            GROUP BY rq.community
        ) r ON c.id = r.community
        LEFT JOIN (
            SELECT COUNT(p.id) AS no_of_projects, p.community
            FROM projects p
            WHERE NOT EXISTS (SELECT 1 FROM installations i WHERE i.project_id = p.id)
            GROUP BY p.community
        ) p ON c.id = p.community
        LEFT JOIN partners cu ON FIND_IN_SET(cu.id, c.clients)
        ${userTypeCondition} 
        WHERE ${whereCondition} ${userCondition}
        GROUP BY c.id 
        ORDER BY c.id ASC
        `);

        let data = query[0]?.length || 0 ;
        return data;
    } catch (error) {
        console.log(error);
        throw new Error("Server error: " + error);
    }
};


const getAllCommunitiesByClient = async (all = false,clientId = false,userType=null,From=null,teamId=null ) => {
    try {
        let whereCondition;
        console.log("$$$$$$$$$$$$$$$$$cccccccccc",clientId);
        console.log("$$$$$$$$$$$$$$$$$userType",userType);
        console.log("$$$$$$$$$$$$$$$$$From",From);
        console.log("$$$$$$$$$$$$$$$$$teamId",teamId);
        let salesRepFilter = '';
        if (From === 'addSalesOrder' && teamId && userType !== 8 && userType !== 1) {
            salesRepFilter = `AND FIND_IN_SET(${teamId}, c.sales_rep_names) > 0`;
        }
        if (all) {
            whereCondition = `c.status != 2  and FIND_IN_SET('${clientId}', c.clients) `;
        }
        else if (clientId){
            // whereCondition = `c.status = 1 and c.clients in(${clientId})`;
            whereCondition = `c.status = 1 and FIND_IN_SET('${clientId}', c.clients) `;
        }
        else {
            // whereCondition = `c.status = 1 and c.clients in(${clientId})`;
            whereCondition = `c.status = 1 `;
        }
        // let query = await sequelize.query(
        //     ` SELECT c.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, COALESCE(r.no_of_requests, 0) AS no_of_requests, COALESCE(p.no_of_projects, 0) AS no_of_projects`
        //     + ` FROM community c `
        //     + ` LEFT JOIN users u ON c.added_by = u.id `
        //     + ` LEFT JOIN ( `
        //     + ` SELECT COUNT(id) AS no_of_requests,community FROM requests WHERE status!=0 GROUP BY community `
        //     + ` ) r ON c.id=r.community `
        //     + ` LEFT JOIN ( `
        //     + ` SELECT COUNT(p.id) AS no_of_projects,p.community FROM projects p WHERE NOT EXISTS (SELECT 1 FROM installations i WHERE i.project_id = p.id) GROUP BY p.community `
        //     + ` ) p ON c.id=p.community `
        //     + ` WHERE ${whereCondition} `
        //     + ` ORDER BY c.id DESC`,
        // )
        let query=await sequelize.query(`SELECT c.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, COALESCE(r.no_of_requests, 0) AS no_of_requests, COALESCE(p.no_of_projects, 0) AS no_of_projects, GROUP_CONCAT(DISTINCT cu.user_id) AS client_user_ids,GROUP_CONCAT(DISTINCT cu.name) AS clientName,
            COALESCE((SELECT GROUP_CONCAT(DISTINCT sm.id ORDER BY sm.id) 
            FROM staff_members sm 
            WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS all_sales_rep_ids,
            COALESCE((SELECT GROUP_CONCAT(DISTINCT CONCAT(sm.first_name,' ',COALESCE(sm.last_name,'')) ORDER BY sm.id SEPARATOR ', ') 
            FROM staff_members sm 
            WHERE c.sales_rep_names IS NOT NULL AND c.sales_rep_names != '' AND FIND_IN_SET(sm.id, c.sales_rep_names) > 0), '') AS all_sales_rep_names
        FROM community c `
        +` LEFT JOIN users u ON c.added_by = u.id `
        +` LEFT JOIN ( SELECT COUNT(rq.id) AS no_of_requests, rq.community FROM requests rq JOIN customer_orders co on co.request_id = rq.id WHERE co.customer_id = ${clientId} and rq.status != 0 and rq.status != 8 GROUP BY rq.community 
        ) r ON c.id = r.community `
        +`  LEFT JOIN ( 
        SELECT COUNT(p.id) AS no_of_projects, p.community FROM projects p WHERE NOT EXISTS (SELECT 1 FROM installations i WHERE i.project_id = p.id) GROUP BY p.community 
        ) p ON c.id = p.community `
        +` LEFT JOIN partners cu ON FIND_IN_SET(cu.id, c.clients) `
        +` WHERE ${whereCondition} ${salesRepFilter}`
        +` GROUP BY c.id   ORDER BY c.id DESC`)
        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error"+error);
    }
}

// New function for client-specific project autocomplete filtering
const getCommunitiesForClientAutocomplete = async (userId, userType, isClient, clientIdFromUser = null) => {
    try {
        console.log('Community model called with:', { userId, userType, isClient, clientIdFromUser });
        let whereCondition = `c.status = 1`;
        
        // Apply client filtering for isClient or userType 3
        if (isClient || userType === 3) {
            console.log('Applying client filtering');
            let clientId = clientIdFromUser;
            
            // If clientId is not provided from user object, look it up in partners table
            if (!clientId) {
                console.log('Looking up client for user_id:', userId);
                
                // Get client details from partners table using user_id
                const clientQuery = await sequelize.query(`
                    SELECT p.id as client_id 
                    FROM partners p 
                    WHERE p.user_id = ${userId}
                `);
                
                console.log('Client query result:', clientQuery[0]);
                
                if (clientQuery[0] && clientQuery[0].length > 0) {
                    clientId = clientQuery[0][0].client_id;
                    console.log('Found client ID from partners table:', clientId);
                } else {
                    console.log('No client found in partners table for user_id:', userId);
                    // If no client found, return empty array
                    return [];
                }
            } else {
                console.log('Using client ID from user object:', clientId);
            }
            
            if (clientId) {
                whereCondition += ` AND FIND_IN_SET('${clientId}', c.clients)`;
            }
        }
        
        console.log('Final where condition:', whereCondition);
        
        let query = await sequelize.query(`
            SELECT c.id, c.name, c.description, c.type, c.clients, c.added_by, c.tax_category,c.poNumber
            FROM community c 
            WHERE ${whereCondition}
            ORDER BY c.name ASC
        `);
        
        console.log('Query result:', query[0]);
        
        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log('Error in getCommunitiesForClientAutocomplete:', error);
        throw new Error("Server error: " + error);
    }
}
const getCommunityById = async (id) => {
    try {
        let results = await sequelize.query(
            ` SELECT p.*,tc.type AS tax_type,tc.value AS tax_value, CONCAT(u.first_name,' ',u.last_name) AS authorized_by,
            COALESCE((SELECT GROUP_CONCAT(DISTINCT sm.id ORDER BY sm.id) 
             FROM staff_members sm 
             WHERE p.sales_rep_names IS NOT NULL AND p.sales_rep_names != '' AND FIND_IN_SET(sm.id, p.sales_rep_names) > 0), '') AS sales_rep_ids,
            COALESCE((SELECT GROUP_CONCAT(DISTINCT CONCAT(sm.first_name,' ',COALESCE(sm.last_name,'')) ORDER BY sm.id SEPARATOR ', ') 
             FROM staff_members sm 
             WHERE p.sales_rep_names IS NOT NULL AND p.sales_rep_names != '' AND FIND_IN_SET(sm.id, p.sales_rep_names) > 0), '') AS sales_rep_names
            FROM community p `
            + ` LEFT JOIN users u ON p.added_by = u.id `
            + ` LEFT JOIN tax_category tc ON tc.id = p.tax_category `
            + ` WHERE p.id = :id `,
            {
                replacements: {
                    id: id
                },
                type: Sequelize.QueryTypes.SELECT
            }
        );
        
        if (results?.length === 1) {
            // Fetch PO details for this community
            const poDetails = await getPoDetailsByCommunityId(id);
            results[0].po_details = poDetails;
            return results[0];
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const updateCommunity = async (id,data) => {
    let updateCommunityresult = await Community.update(data,{
        where : {
            id: id
        }
    });
    return updateCommunityresult ? true : false;
}

const updateCardOrderProject = async (cards) => {
    if (!cards || !cards.length) {
      return false;
    }
    try {
      const updatePromises = cards.map((card, index) => {
        return Community.update(
          { card_order: index },
          { where: { id: card.id } }
        );
      });
  
      await Promise.all(updatePromises);
  
      return true;
    } catch (error) {
      console.log("Error updating card order:", error);
      throw new Error("Error updating card order");
    }
  };

const addCommunityType = async (data) => {
    try {
        let results = await CommunityType.create(data)
        if (results) {
            return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the community type ", error);
        return false;
    }
}

const isCommunityTypeNameExist = async (name, communityTypeId = 0) => {
    let where = {};
    if (communityTypeId !== 0) {
        where = { id: { [Sequelize.Op.not]: communityTypeId } };
    }
    const results = await CommunityType.findAll({
        attributes: ['id'],
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: name } },
                { status: { [Sequelize.Op.ne]: 2 } },
                where
            ]
        }
    });
    console.log("results : ", results.length)
    return results.length > 0;
}

const updateCommunityLatLng = async (lat, lng, id) => {
  try {
    let result = await Community.update(
      { lat, lng },
      {
        where: { id: id }
      }
    );

    return result[0] > 0;
  } catch (error) {
    console.error("Error updating lat/lng:", error);
    return false;
  }
};

const getAllCommunityTypes = async (all = false ) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `c.status != 2`;
        }
        else {
            whereCondition = `c.status = 1`;
        }
        let query = await sequelize.query(
            ` SELECT c.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by `
            + ` FROM community_type c `
            + ` LEFT JOIN users u ON c.added_by = u.id `
            + ` WHERE ${whereCondition} `
            + ` ORDER BY c.id DESC`,
        )
        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getCommunityTypeByName = async (name) => {
  try {
    const query = await sequelize.query(
      `SELECT id FROM community_type WHERE name = :name AND status = 1`,
      { replacements: { name } }
    );
    return query[0][0] || null;
  } catch (error) {
    console.error("Error in getCommunityTypeByName:", error);
    throw new Error("Server error");
  }
};

const isPoNumberExist = async (poNumber, communityId = 0) => {
  try {
    let where = { poNumber: { [Sequelize.Op.eq]: poNumber } };
    if (communityId !== 0) {
      where = {
        [Sequelize.Op.and]: [
          { poNumber: { [Sequelize.Op.eq]: poNumber } },
          { id: { [Sequelize.Op.not]: communityId } },
        ],
      };
    }
    const query = await Community.findAll({
      attributes: ['id'],
      where: {
        [Sequelize.Op.and]: [
          where,
          { status: { [Sequelize.Op.ne]: 2 } },
        ],
      },
    });
    return query.length > 0;
  } catch (error) {
    console.error("Error in isPoNumberExist:", error);
    throw new Error("Server error");
  }
};
// , type
const isCommunityNameAndTypeExist = async (name) => {
  try {
    // AND type = :type
    const query = await sequelize.query(
      `SELECT id FROM community WHERE name = :name  AND status != 2`,
      { replacements: { name } }
    //   , type
    );
    return query[0].length > 0;
  } catch (error) {
    console.error("Error in isCommunityNameAndTypeExist:", error);
    throw new Error("Server error");
  }
};

// Get full community record by name & type (first active match)
// , type
const getCommunityByNameAndType = async (name) => {
  try {
    //  AND type = :type 
    const query = await sequelize.query(
      `SELECT * FROM community WHERE name = :name AND status != 2 LIMIT 1`,
      { replacements: { name } }
    //   , type
    );
    return query[0][0] || null;
  } catch (error) {
    console.error("Error in getCommunityByNameAndType:", error);
    throw new Error("Server error");
  }
};

// PO Details functions
const addPoDetails = async (data) => {
    try {
        let results = await PoDetails.create(data)
        if (results) {
            return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding PO details ", error);
        return false;
    }
}

const getPoDetailsByCommunityId = async (communityId) => {
    try {
        const results = await PoDetails.findAll({
            where: {
                community_id: communityId,
                status: 1
            },
            order: [['timestamp', 'DESC']]
        });
        return results;
    }
    catch (error) {
        console.error("Error fetching PO details ", error);
        return [];
    }
}

const updatePoDetails = async (id, data) => {
    try {
        let result = await PoDetails.update(data, {
            where: { id: id }
        });
        return result[0] > 0;
    }
    catch (error) {
        console.error("Error updating PO details ", error);
        return false;
    }
}

const deletePoDetails = async (id) => {
    try {
        let result = await PoDetails.update(
            { status: 0 },
            { where: { id: id } }
        );
        return result[0] > 0;
    }
    catch (error) {
        console.error("Error deleting PO details ", error);
        return false;
    }
}

const isPoNumberExistForCommunity = async (poNumber, communityId, poDetailId = 0) => {
    try {
        let where = {
            po_number: { [Sequelize.Op.eq]: poNumber },
            community_id: communityId,
            status: 1
        };
        if (poDetailId !== 0) {
            where.id = { [Sequelize.Op.not]: poDetailId };
        }
        const results = await PoDetails.findAll({
            attributes: ['id'],
            where: where
        });
        return results.length > 0;
    } catch (error) {
        console.error("Error in isPoNumberExistForCommunity:", error);
        return false;
    }
};

const getCommunityTypeById = async (id) => {
    try {
        let results = await sequelize.query(
            ` SELECT p.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by `
            + ` FROM community_type p `
            + ` LEFT JOIN users u ON p.added_by = u.id `
            + ` WHERE p.id = :id `,
            {
                replacements: {
                    id: id
                },
                type: Sequelize.QueryTypes.SELECT
            }
        );
        
        if (results?.length === 1) {
            return results[0];
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const updateCommunityType = async (id,data) => {
    let updateCommunityTyperesult = await CommunityType.update(data,{
        where : {
            id: id
        }
    });
    return updateCommunityTyperesult ? true : false;
}

const getPoDetailsById = async (id) => {
    try {
        let results = await PoDetails.findAll({
            where: { id: id }
        });
        return results;
    }
    catch (error) {
        console.error("Error fetching PO details by id ", error);
        return [];
    }
}

module.exports = {
    Community,
    addCommunity,
    isCommunityNameExist,
    getAllCommunities,
    getAllCommunitiesWithProjectCount,
    updateCommunity,
    getCommunityById,
    updateCardOrderProject,
    CommunityType,
    addCommunityType,
    getAllCommunitiesCount,
    isCommunityTypeNameExist,
    getAllCommunityTypes,
    getCommunityTypeById,
    updateCommunityType,
    getAllCommunitiesByClient,
    updateCommunityLatLng,
    getCommunitiesForClientAutocomplete,
    getCommunityTypeByName,
    isCommunityNameAndTypeExist,
    isPoNumberExist,
    getCommunityByNameAndType,
    PoDetails,
    addPoDetails,
    getPoDetailsByCommunityId,
    updatePoDetails,
    deletePoDetails,
    isPoNumberExistForCommunity,
    getPoDetailsById
};