const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const moment = require("moment");
const { getAgoString } = require("../Helpers/Common");
const RequestMessages = sequelize.define(
  "request_messages",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    request_id: { type: Sequelize.INTEGER, allowNull: false },
    user_id: { type: Sequelize.INTEGER, allowNull: false },
    from_status: { type: Sequelize.INTEGER, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: false },
    message: { type: Sequelize.STRING, allowNull: false },
    image: { type: Sequelize.STRING, allowNull: false },
    image_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
  },
  { timestamps: false }
);
const PartnerMessages = sequelize.define(
  "partner_messages",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    user_id: { type: Sequelize.INTEGER, allowNull: false },
    to_id: { type: Sequelize.INTEGER, allowNull: false },
    message: { type: Sequelize.TEXT, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    seen_user: { type: Sequelize.CHAR(1), allowNull: false, defaultValue: "N" },
    seen_to: { type: Sequelize.CHAR(1), allowNull: false, defaultValue: "N" },
  },
  { timestamps: false }
);
const RequestMessageLastSeen = sequelize.define(
  "request_message_last_seen",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    request_id: { type: Sequelize.INTEGER, allowNull: false },
    user_id: { type: Sequelize.INTEGER, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: false },
    message: { type: Sequelize.STRING, allowNull: true },
    image: { type: Sequelize.STRING, allowNull: true },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
  },
  { timestamps: false ,
    tableName: "request_message_last_seen",}
);
const partnerReview=sequelize.define("partner_reviews",{
  id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
  },
  partner_id: { type: Sequelize.INTEGER, allowNull: false },
    request_id: { type: Sequelize.INTEGER, allowNull: true },
    skills: { type: Sequelize.STRING, allowNull: true },
    quality_rating: { type: Sequelize.TINYINT, allowNull: false },
    delivery_rating: { type: Sequelize.TINYINT, allowNull: false },
    value_for_money_rating:{ type: Sequelize.TINYINT ,allowNull: true },
    review_comment:{ type: Sequelize.STRING, allowNull: true },
    added_by:{ type: Sequelize.INTEGER, allowNull: true },
    status:{ type: Sequelize.INTEGER, allowNull: true },
  
    timestamp: { type: Sequelize.INTEGER, allowNull: false,defaultValue:1 },
  },{ timestamps: false }
  )

const jobNoteMessages = sequelize.define(
  "job_note_messages",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    request_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    design_order_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    user_id: { type: Sequelize.INTEGER, allowNull: false },
    from_status: { type: Sequelize.INTEGER, allowNull: false },
    partner_id: { type: Sequelize.TEXT, allowNull: false }, //multiple userids
    message: { type: Sequelize.TEXT, allowNull: false },
    image: { type: Sequelize.STRING, allowNull: false },
    image_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
  },
  { timestamps: false }
);

const setPartnerMessage = async (to, userId, message, timestamp) => {
  try {
    let data = {};
    data.to_id = to;
    data.user_id = userId;
    data.message = message;
    data.timestamp = timestamp;
    data.seen_user = "Y";
console.log(data,'+++++++++++++++++data')
    const result = await PartnerMessages.create(data);
    return result.id;
  } catch (error) {
    console.error(error);
    throw error;
  }
};

const setRequestMessage = async (data) => {
  try {
    let results = await RequestMessages.create(data);

    if (results) {
      
      return results;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error adding the projects ", error);
    return false;
  }
};

const setJobNoteMessage = async (data) => {
  try {
    let results = await jobNoteMessages.create(data);

    if (results) {
      
      return results;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error adding the job notes ", error);
    return false;
  }
};

const getRequestMessageImagesBydesignOrderId = async (design_id) => {
  try {
    let results = await RequestMessages.findAll({
      where : { 
        request_id : design_id, 
        from_status : 1, 
        image_id: {
          [Sequelize.Op.ne]: null
        }
      }
    });
    if(results && results.length > 0){
      return results
    }else{
      return []
    }
  } catch (error) {
    console.error("Error fetching request message images: ", error);
    return [];
  }
};

const updateRequestMessage = async (data, design_id) => {
  try {
    let results = await RequestMessages.update(data,{
      where : { request_id : design_id, from_status : 1}
    });
    if (results) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error adding the projects ", error);
    return false;
  }
};

const updateJobNoteMessage = async (data, design_order_id) => {
  try {
    let results = await jobNoteMessages.update(data, {
      where: { design_order_id: design_order_id, from_status: 1 }
    });
    if (results) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error updating job note messages ", error);
    return false;
  }
};
const updateLastSeen = async (requestId, userId, to) => {
  try {
    let query = await sequelize.query(
      `select id from request_message_last_seen where request_id=${requestId}  and user_id = ${userId} and partner_id = ${to}`
    );
    
    if (query[0].length > 0) {
      const updatedRows = await RequestMessageLastSeen.update(
        { timestamp: moment().unix() },
        {
          where: {
            id: query[0][0].id,
          },
          returning: true,
        }
      );
      if (updatedRows) {
        if (updatedRows[1] > 0) {
          return true;
        } else {
          return false;
        }
      }
    } else {
      let data = {};
      data.request_id = requestId;
      data.partner_id = to;
      data.user_id = userId;
      data.timestamp = moment().unix();
      let results = await RequestMessageLastSeen.create(data);
      
      if (results) {
        return results;
      } else {
        return false;
      }
    }
  } catch (error) {
    console.error("Error updating last seen ", error);
    return false;
  }
};

//previos
// const getLastUserMsg = async (requestId, partnerId, userId,from_status) => {

//   let query = await sequelize.query(
//     `select ra.id as image_id,ra.approved,ra.reason,ra.status AS image_status,concat(s.first_name," ", s.last_name) as approved_by,u.id,u.profile_pic,c.id as chat_id, concat(u.first_name,' ',u.last_name) as name , c.partner_id ,c.message,c.timestamp,c.image,c.image_id,c.request_id,p.isVendor from request_messages c ` +
//       ` left join request_attachments ra on ra.id = c.image_id` +
//       ` left join users s on s.id=ra.approved_by` +
//       ` inner join users u on u.id = c.user_id` +
//       ` left join partners p on p.user_id=u.id`+
//       ` where c.partner_id=${partnerId} and c.request_id=${requestId} and c.from_status=${from_status} order by timestamp asc`
//   );

//   let data = [];
//   if (query[0] && query[0].length > 0) {
  
//     data = query[0].map((row) => {
//       if (row["id"] === userId) {
//         row["direction"] = "right";
//       } else {
//         row["direction"] = "left";
//       }
//       return row;
//     });
//     console.log("##############",data);
//     return data;
//   } else {
//     return data;
//   }
// };


const getLastUserMsg = async (requestId, partnerId, userId, from_status) => {
  // Step 1: Fetch all request attachments related to the given requestId
  let allAttachmentsQuery = await sequelize.query(
      `SELECT ra.id AS attachment_id, ra.product_ids, ra.location_ids,
              GROUP_CONCAT(CONCAT(cop.id, ':', p.name) ORDER BY cop.id SEPARATOR ', ') AS product_id_name_map
       FROM request_attachments ra
       LEFT JOIN request_locations rl ON FIND_IN_SET(rl.id, ra.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
       WHERE ra.request_id = ${requestId}
       GROUP BY ra.id`,
      { type: sequelize.QueryTypes.SELECT }
  );

  // Collect all product_ids from all attachments
  let allLocationIds = allAttachmentsQuery
      .map(item => item.location_ids ? item.location_ids?.split(',').map(id => id.trim()) : [])
      .flat();
  let allProductIds = allAttachmentsQuery
      .map(item => item.product_ids ? item.product_ids?.split(',').map(id => id.trim()) : [])
      .flat();

  // Step 2: Fetch messages and join relevant attachment data
  let query = await sequelize.query(
      `SELECT 
          ra.id AS image_id,
          ra.approved,
          ra.reason,
          ra.status AS image_status,
          ra.product_ids,
          ra.location_ids,
          GROUP_CONCAT(CONCAT(cop.id, ':', p.name) ORDER BY cop.id SEPARATOR ', ') AS product_id_name_map,
          CONCAT(s.first_name, " ", s.last_name) AS approved_by,
          u.id,
          u.profile_pic,
          c.id AS chat_id,
          CONCAT(u.first_name, ' ', u.last_name) AS name,
          c.partner_id,
          c.message,
          c.timestamp,
          c.image,
          c.image_id,
          c.request_id,
          ptn.isVendor
      FROM request_messages c
      LEFT JOIN request_attachments ra ON ra.id = c.image_id
      LEFT JOIN request_locations rl ON FIND_IN_SET(rl.id, ra.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 s ON s.id = ra.approved_by
      INNER JOIN users u ON u.id = c.user_id
      LEFT JOIN partners ptn ON ptn.user_id = u.id
      WHERE c.partner_id = ${partnerId} 
        AND c.request_id = ${requestId} 
        AND c.from_status = ${from_status}
      GROUP BY c.id, ra.id
      ORDER BY c.timestamp ASC`,
      { type: sequelize.QueryTypes.SELECT }
  );

  let data = [];

  if (query.length > 0) {
      data = query.map((row) => {
          let linkedProductsIds = [];
          let linkedLocationIds = [];

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

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

          // Step 4: Convert product_id_name_map into an array of objects
          let productNames = row.product_id_name_map
              ? row.product_id_name_map.split(', ').map(pair => {
                  let [custOrderProductId, productName] = pair.split(':');
                  return {
                      product_id: custOrderProductId.trim(),
                      product_name: productName.trim()
                  };
              })
              : [];

          // Step 5: 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

          // Step 5: Get linkedLocationIds (all location_ids except the current row’s location_ids)
          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

          // Step 6: Set message direction based on user ID
          row["direction"] = row["id"] === userId ? "right" : "left";

          return {
              ...row,
              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
              productIds: row.product_ids, // Keeping original product_ids as well
              locationIds: row.location_ids // Keeping original location_ids as well
          };
      });

      console.log("##############", data);
  }

  return data;
};

const getLastUserJobNoteMsg = async (requestId, partnerId, userId,userType, from_status, designOrderId = null) => {
  console.log('partnerIdpartnerId',partnerId)

  let baseWhere = '';
  if (designOrderId) {
    baseWhere = `c.design_order_id = ${designOrderId} AND c.from_status = ${from_status}`;
  } else {
    baseWhere = `c.request_id = ${requestId} AND c.from_status = ${from_status}`;
  }

// if (userType === 1 || userType === 8) {
//     // Admins or vendors see all messages related to requestId
//     baseWhere += ``;
// } else if (partnerId){
//     // Other users only see their own or messages sent to them
//     baseWhere += ` AND (
//       c.user_id = ${userId} OR 
//       FIND_IN_SET(${partnerId}, c.partner_id)
//     )`;
// }else {
//   // Team members (no partner ID, allow messages sent by/received by userId only)
//       baseWhere += ` AND (
//       c.user_id = ${userId} OR 
//       FIND_IN_SET(${userId}, c.partner_id)
//     )`;
// }

  // Step 1: Fetch all request attachments related to the given requestId
  let allAttachmentsQuery = await sequelize.query(
      `SELECT ra.id AS attachment_id, ra.product_ids, ra.location_ids,
              GROUP_CONCAT(CONCAT(cop.id, ':', p.name) ORDER BY cop.id SEPARATOR ', ') AS product_id_name_map
       FROM request_attachments ra
       LEFT JOIN request_locations rl ON FIND_IN_SET(rl.id, ra.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
       WHERE ra.request_id = ${requestId}
       GROUP BY ra.id`,
      { type: sequelize.QueryTypes.SELECT }
  );

  // Collect all product_ids from all attachments
  let allLocationIds = allAttachmentsQuery
      .map(item => item.location_ids ? item.location_ids?.split(',').map(id => id.trim()) : [])
      .flat();
  let allProductIds = allAttachmentsQuery
      .map(item => item.product_ids ? item.product_ids?.split(',').map(id => id.trim()) : [])
      .flat();

  // Step 2: Fetch messages and join relevant attachment data
  let query = await sequelize.query(
      `SELECT 
          ra.id AS image_id,
          ra.approved,
          ra.reason,
          ra.status AS image_status,
          ra.product_ids,
          ra.location_ids,
          GROUP_CONCAT(CONCAT(cop.id, ':', p.name) ORDER BY cop.id SEPARATOR ', ') AS product_id_name_map,
          CONCAT(s.first_name, " ", s.last_name) AS approved_by,
          u.id,
          u.profile_pic,
          c.id AS chat_id,
          CONCAT(u.first_name, ' ', u.last_name) AS name,
          c.partner_id,
          c.message,
          c.timestamp,
          c.image,
          c.image_id,
          c.request_id,
          ptn.isVendor
      FROM job_note_messages c
      LEFT JOIN request_attachments ra ON ra.id = c.image_id
      LEFT JOIN request_locations rl ON FIND_IN_SET(rl.id, ra.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 s ON s.id = ra.approved_by
      INNER JOIN users u ON u.id = c.user_id
      LEFT JOIN partners ptn ON ptn.user_id = u.id
      WHERE ${baseWhere}
      GROUP BY c.id, ra.id
      ORDER BY c.timestamp ASC`,
      { type: sequelize.QueryTypes.SELECT }
  );

  let data = [];

  if (query.length > 0) {
      data = query.map((row) => {
          let linkedProductsIds = [];
          let linkedLocationIds = [];

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

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

          // Step 4: Convert product_id_name_map into an array of objects
          let productNames = row.product_id_name_map
              ? row.product_id_name_map.split(', ').map(pair => {
                  let [custOrderProductId, productName] = pair.split(':');
                  return {
                      product_id: custOrderProductId.trim(),
                      product_name: productName.trim()
                  };
              })
              : [];

          // Step 5: 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

          // Step 5: Get linkedLocationIds (all location_ids except the current row’s location_ids)
          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

          // Step 6: Set message direction based on user ID
          row["direction"] = row["id"] === userId ? "right" : "left";

          return {
              ...row,
              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
              productIds: row.product_ids, // Keeping original product_ids as well
              locationIds: row.location_ids // Keeping original location_ids as well
          };
      });

      console.log("##############", data);
  }

  return data;
};


const getLastUserPartnerMsg = async (userId, lastUser) => {
  try {
    let query = await sequelize.query(
      `SELECT u.id,c.id AS chat_id, CONCAT(u.first_name,' ',u.last_name) AS name , c.to_id ,c.message,c.timestamp` +
        ` FROM partner_messages c ` +
        ` INNER JOIN users u ON u.id = c.user_id` +
        ` WHERE c.user_id IN (${userId},${lastUser}) AND c.to_id IN (${userId},${lastUser})` +
        ` ORDER BY timestamp asc`
    );

    if (query.length > 0) {
      const data = query[0].map((row) => {
        return {
          id: row.id,
          chat_id: row.chat_id,
          name: row.name,
          to_id: row.to_id,
          message: row.message,
          timestamp: getAgoString(new Date(row.timestamp * 1000)),
          direction: row.id == userId ? "right" : "left",
        };
      });
      console.log("last message data ", data);
      return data;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    reply.code(500).send({ error: "Internal Server Error" });
  }
};
const updateMsg = async (chatId, userId) => {
  try {
    
    const result = await PartnerMessages.update(
      { seen_to: "Y" },
      {
        where: {
          id: chatId,
          to_id: userId,
        },
      }
    );
    console.log("result ------", result);
    if (result) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    throw error;
  }
};
const addReview=async(data)=>{
  try {
    let results = await partnerReview.create(data);
       console.log(results,'-------------results')
    if (results) {
      
      return results;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error adding the projects ", error);
    return false;
  }
}
module.exports = {
  setRequestMessage,
  getRequestMessageImagesBydesignOrderId,
  updateRequestMessage,
  updateJobNoteMessage,
  updateLastSeen,
  getLastUserMsg,
  setPartnerMessage,
  getLastUserPartnerMsg,
  updateMsg,addReview,
  setJobNoteMessage,
  getLastUserJobNoteMsg
};
