const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const { empty } = require("../Helpers/Common");

const requestPartners = sequelize.define('request_partners', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    request_id: { type: Sequelize.INTEGER, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: false },
    last_seen: { type: Sequelize.INTEGER, allowNull: false, },
    added_by: { type: Sequelize.INTEGER, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false, },
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    accept_status: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 0 }
}, { timestamps: false })

const addRequestPartners = async (data) => {
    try {
        let results = await requestPartners.create(data)
        if (results) {
            return true;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'Error in adding partner ')
    }
}

const acceptOrRejectRequest = async (id, data) => {
    try {
        let result = await requestPartners.update(data, {
            where: { id: id }
        })
        return true;
    }
    catch (error) {
        return false;
    }
}

const updateRequestPartnersByRequestId = async (reqId, data) => {
    try {
        let result = await requestPartners.update(data, {
            where: { request_id: reqId }
        })
        return true
    }
    catch (error) {
        return false;
    }
}

const getRequestPartnerById = async (id) => {
    let query = await sequelize.query(`SELECT p.* FROM request_partners rp`
        + ` JOIN partners p ON p.id=rp.partner_id `
        + ` WHERE rp.id = ${id}`)
    if (query[0].length === 1) {
        return query[0][0];
    } else {
        return false;
    }
}

const fetchPartners = async (requestId) => {
    let query = await sequelize.query(`select id from request_partners where request_id=${requestId}`)
    let data = [];
    if (query[0].length >= 1) {
        query[0].forEach(row => {
            if (row.id !== undefined && row.id !== null) {
                data.push(row.id);
            }
        });

    }
    return data
}
const fetchPartnerUserIds = async (requestId) => {
    const query = await sequelize.query(
      `SELECT p.user_id
       FROM request_partners rp
       LEFT JOIN partners p ON p.id = rp.partner_id
       WHERE rp.request_id = :requestId`,
      {
        replacements: { requestId },
        type: sequelize.QueryTypes.SELECT
      }
    );
    return query.map(row => row.user_id).filter(Boolean);
  };
  
const getRequestPartnerByIds = async (partnerIds, requestId) => {
    let query = ""
    if (partnerIds?.length > 0) {
        query = await sequelize.query(`SELECT u.id,concat(u.first_name,' ',u.last_name) as name FROM partners t `
            + ` left join users u on t.user_id=u.id `
            + ` WHERE u.status=1 and t.id in (".implode(",",${partnerIds}).") ORDER BY u.id DESC`);
    }
    else {
        query = await sequelize.query(`SELECT u.id,concat(u.first_name,' ',u.last_name) as name, u.profile_pic,t.id as partner_id, r.status FROM request_partners r `
            + ` left join partners t on r.partner_id=t.id `
            + ` left join users u on t.user_id=u.id WHERE u.status=1 and r.request_id=${requestId} ORDER BY u.id DESC`);
    }
    let data = []
    if (query[0].length >= 1) {
        data = query[0].filter(row => !empty(row.id));
    }
    return data
}

const getRequestPartnersBulk = async (partnerIds, requestIds) => {
    let query;
    if (partnerIds?.length > 0) {
      // Fetch partners by multiple partner IDs
      query = await sequelize.query(
        `SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) AS name 
         FROM partners t
         LEFT JOIN users u ON t.user_id = u.id
         WHERE u.status = 1 AND t.id IN (:partnerIds)
         ORDER BY u.id DESC`,
        {
          replacements: { partnerIds }, // Use Sequelize replacements
          type: sequelize.QueryTypes.SELECT,
        }
      );
    } else if (requestIds?.length > 0) {
      // Fetch partners for multiple request IDs
      query = await sequelize.query(
        `SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) AS name, 
                u.profile_pic, r.status, r.request_id
         FROM request_partners r
         LEFT JOIN partners t ON r.partner_id = t.id
         LEFT JOIN users u ON t.user_id = u.id
         WHERE u.status = 1 AND r.request_id IN (:requestIds)
         ORDER BY r.request_id, u.id DESC`,
        {
          replacements: { requestIds },
          type: sequelize.QueryTypes.SELECT,
        }
      );
    } else {
      // Return empty array if no valid inputs
      return [];
    }
  
    // Filter rows where `id` is not empty or null
    return query.filter(row => row.id !== null && row.id !== undefined);
  };  

const requestAwaitingPartners = async (requestId, acceptStatus) => {
    let query = await sequelize.query(`SELECT p.id,p.name,p.isVendor,p.user_id,ur.isClientUserRole, accept_status, u.profile_pic,u.user_type, (case when p.onboard_complete=0 then 'N' else 'Y' end) as active, (coalesce(rr.rating, 0)/coalesce(pr.no_of_reviews, 1)) as rating, coalesce(pr.no_of_reviews, 0) as no_of_reviews FROM partners p `
        + ` JOIN users u ON p.user_id=u.id `
        + ` JOIN request_partners rp ON p.id=rp.partner_id `
        + ` JOIN user_roles ur ON u.user_type=ur.id `
        + ` LEFT JOIN (select count(id) as no_of_reviews,partner_id from partner_reviews where status=1 group by partner_id ) pr ON p.id=pr.partner_id `
        + ` LEFT JOIN (select (sum(quality_rating)+sum(delivery_rating)+sum(value_for_money_rating))/3 as rating,partner_id from partner_reviews where status=1 group by partner_id ) rr ON p.id=rr.partner_id WHERE rp.request_id =${requestId} AND rp.accept_status =${acceptStatus}`)
    let data = [];

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

const fetchAllRequestAttachments = async (requestId) => {
    let query = await sequelize.query(`
        SELECT r.*, 
               r.product_ids, 
               r.location_ids, 
               GROUP_CONCAT(CONCAT(cop.id, ':',REPLACE(cop.product_name, ',', '||')) ORDER BY cop.id SEPARATOR ', ') AS product_id_name_map,
               GROUP_CONCAT(CONCAT(cop.product_notes) ORDER BY cop.id SEPARATOR ', ') AS product_notes,
               CONCAT(u.first_name, " ", u.last_name) AS updated_by,
               u.profile_pic AS pic,
               CONCAT(s.first_name, " ", s.last_name) AS approved_by
        FROM request_attachments r
        LEFT JOIN request_locations rl ON FIND_IN_SET(rl.id, r.location_ids) > 0
        LEFT JOIN customer_order_products cop ON cop.id = rl.order_id
        LEFT JOIN products p ON p.id = cop.product_id
        LEFT JOIN users u ON u.id = r.added_by
        LEFT JOIN users s ON s.id = r.approved_by
        WHERE r.request_id = ${requestId} AND r.status = 1 
        GROUP BY r.id
        ORDER BY r.approved DESC
    `, { type: sequelize.QueryTypes.SELECT }); // ✅ Ensures correct query execution

    let data = [];

    if (query?.length > 0) {
        // Step 1: Collect all product_ids from all rows
        let allProductIds = query
            .map(item => item.product_ids ? item.product_ids.split(',').map(id => id.trim()) : [])
            .flat(); // Flatten to get a single array of all product_ids
        
        let allLocationIds = query
            .map(item => item.location_ids ? item.location_ids.split(',').map(id => id.trim()) : [])
            .flat(); // Flatten to get a single array of all location_ids

        data = query.map((item) => {
            let linkedProductsIds = [];
            let linkedLocationIds = [];

            // Step 2: Convert `product_ids` into an array
            let productIdsArray = item.product_ids 
                ? item.product_ids.split(',').map(id => id.trim()) 
                : []; // Default to empty array if null
            let locationIdsArray = item.location_ids 
                ? item.location_ids.split(',').map(id => id.trim()) 
                : []; // Default to empty array if null

            // Step 3: Convert product_id_name_map into an array of objects
            let productNames = item.product_id_name_map
                ? item.product_id_name_map.split(', ').map(pair => {
                    let [custOrderProductId, productName] = pair.split(':');
                    let productNotes = item.product_notes?.trim();
                    return {
                        product_id: custOrderProductId?.trim(),
                        product_name: productName?.replace(/\|\|/g, ','),
                        product_notes: productNotes
                    };
                })
                : [];

            // Step 4: Get linkedProductsIds (all product_ids except the current row’s product_ids)
            linkedProductsIds = allProductIds
                .filter(id => !productIdsArray.includes(id)) // Exclude current row's product_ids
                .filter((id, index, self) => self.indexOf(id) === index) // Ensure uniqueness
                .join(','); // Convert back to string
            linkedLocationIds = allLocationIds
                .filter(id => !locationIdsArray.includes(id)) // Exclude current row's location_ids
                .filter((id, index, self) => self.indexOf(id) === index) // Ensure uniqueness
                .join(','); // Convert back to string

            return {
                ...item,
                productNames, // Array of objects with customer_order_product_id mapped correctly
                linkedProductsIds, // Comma-separated string of other product_ids
                linkedLocationIds, // Comma-separated string of other location_ids
            };
        });
        console.log('data....',data)
    }

    return data;
};

const fetchAllRequestPayments=async(requestId)=>{
    let query = await sequelize.query(`select r.*,concat(u.first_name," ", u.last_name) as updated_by,u.profile_pic as pic from request_payments r
    left join users u on u.id=r.added_by
    where r.request_id=${requestId} and r.status=1`) 
    let data = [];

    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
module.exports = {
    requestPartners, addRequestPartners, updateRequestPartnersByRequestId, acceptOrRejectRequest, getRequestPartnerById,
    fetchPartners, getRequestPartnerByIds, getRequestPartnersBulk, requestAwaitingPartners,fetchAllRequestAttachments,
    fetchAllRequestPayments,fetchPartnerUserIds
}