const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const { Users } = require("./admin");
const Conversations = sequelize.define(
  "conversations",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    type: {
      type: Sequelize.ENUM("single", "group"),
      allowNull: false,
      defaultValue: "single",
    },
    group_name: {
      type: Sequelize.STRING(200),
      allowNull: true,
      defaultValue: null,
    },
    created_by: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    status: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 1,
    },
    created_at: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    // updated_at: {
    //   type: Sequelize.DATE,
    //   allowNull: true,
    //   defaultValue: null,
    // },
  },
  { tableName: "conversations", timestamps: false }
);
const ConversationMembers = sequelize.define(
  "conversation_members",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    conversation_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "conversations", key: "id" },
      onDelete: "CASCADE",
    },
    user_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "users", key: "id" },
      onDelete: "CASCADE",
    },
    is_admin: {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue:0
    },
    added_at: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    unread_msg:{type:Sequelize.INTEGER,allowNull:true,defaultValue:1},
    phone_notif:{type:Sequelize.TINYINT,allowNull:true,defaultValue:1},
    email_notif:{type:Sequelize.TINYINT,allowNull:true,defaultValue:1},
  },
  { tableName: "conversation_members", timestamps: false }
);
const Messages = sequelize.define(
  "messages",
  {
    id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
    conversation_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "conversations", key: "id" },
      onDelete: "CASCADE",
    },
    chat_log_id: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue:null,
    },
    sender_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "users", key: "id" },
      onDelete: "CASCADE",
    },
    // message: { type: Sequelize.TEXT, allowNull: true },
    message: {
      type: Sequelize.TEXT,
      allowNull: true,
      charset: "utf8mb4",
      collate: "utf8mb4_unicode_ci",
    },
    chat_log_msg: {
      type: Sequelize.TEXT,
      allowNull: true,
      charset: "utf8mb4",
      collate: "utf8mb4_unicode_ci",
    },
    message_type: { type: Sequelize.STRING(250), allowNull: true, defaultValue: 'text' },
    attachment_url: { type: Sequelize.STRING(2000), allowNull: true, defaultValue: null },
    conversation_type: { type: Sequelize.TINYINT, allowNull: false },
    created_at: { type: Sequelize.INTEGER, allowNull: false },
    read_status:{type:Sequelize.INTEGER,allowNull:true,defaultValue:1},
  },
  { tableName: "messages", timestamps: false }
);

const ChatLog = sequelize.define("chat_logs",{
    id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
    conversation_id: {type: Sequelize.INTEGER, allowNull: false,},
    name: { type: Sequelize.TEXT, allowNull: true },
    status: { type: Sequelize.INTEGER, allowNull: false,defaultValue:1 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
  },
  { tableName: "chat_logs", timestamps: false }
);
const ReadMessage = sequelize.define("read_message",{
    id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
    conversation_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "conversations", key: "id" },
      onDelete: "CASCADE",
    },
    message_id: {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: { model: "messages", key: "id" },
      onDelete: "CASCADE",
    },
    user_id:{type: Sequelize.INTEGER, allowNull: true},
    read_status: { type: Sequelize.INTEGER, allowNull: false,defaultValue:1 },
  },
  { tableName: "read_message", timestamps: false }
);
const SingleChatMembers = sequelize.define("single_chat_members",{
    id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
    sender_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "users", key: "id" },
      onDelete: "CASCADE",
    },
    receiver_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: { model: "users", key: "id" },
      onDelete: "CASCADE",
    },
    phone_notif:{type:Sequelize.TINYINT,allowNull:true,defaultValue:1},
    email_notif:{type:Sequelize.TINYINT,allowNull:true,defaultValue:1},
  },
  { tableName: "single_chat_members", timestamps: false }
);

Conversations.hasMany(Messages, { foreignKey: "conversation_id" });
ReadMessage.hasMany(Messages, { foreignKey: "conversation_id" });
Conversations.hasMany(ConversationMembers, { foreignKey: "conversation_id" });
ConversationMembers.belongsTo(Users, { foreignKey: "user_id" });
Messages.belongsTo(Users, { foreignKey: "sender_id", targetKey: "id" });

const createConversation = async (data, members, created_at) => {
  try {
    const transaction = await sequelize.transaction();
    const newConversation = await Conversations.create(data, { transaction });
    const memberData = members.map((user_id) => ({
      conversation_id: newConversation.id,
      user_id,
      added_at: created_at,
    }));
    await ConversationMembers.bulkCreate(memberData, { transaction });
    await transaction.commit();
    return newConversation.id;
  } catch (error) {
    // Rollback transaction if any error occurs
    await transaction.rollback();
    console.error("Error creating conversation:", error);
    return null;
  }
};

const getChatList = async (userId) => {
  try {
    console.log("Fetching chat list for user:", userId);

    // 1️⃣ Fetch team members
    const teamMembersQuery = `
        SELECT DISTINCT 
          u.id AS user_id, 
          u.first_name, 
          u.last_name, 
          CONCAT(u.first_name,' ',u.last_name) AS full_name,
          u.profile_pic,
          u.email,
          sm.phone,
          ur.name AS position,
          u.active,
          u.chat_status,
          u.last_activity,
          CASE 
            WHEN u.user_type = 1 THEN 'admin'
            WHEN u.user_type = 2 THEN 'team_member'
            ELSE 'other'
          END AS user_type,
          u.user_type AS user_type_id
        FROM staff_members sm
        LEFT JOIN users u ON sm.user_id = u.id
        LEFT JOIN user_roles ur ON u.user_type = ur.id  
        WHERE sm.status = 1 AND sm.user_id != :userId
      `;

    const teamMembers = await sequelize.query(teamMembersQuery, {
      replacements: { userId },
      type: Sequelize.QueryTypes.SELECT,
    });

    // 2️⃣ Fetch partners (clients) - use user_id from partners table to link to users
    const partnersQuery = `
        SELECT DISTINCT 
          p.user_id AS user_id, 
          p.name AS first_name, 
          p.last_name, 
          CONCAT(p.name, ' ', COALESCE(p.last_name, '')) AS full_name,
          p.thumbnail AS profile_pic,
          p.email,
          p.phone,
          'Client' AS position,
          CASE WHEN p.onboard_complete = 0 THEN 'N' ELSE 'Y' END AS active,
          'online' AS chat_status,
          p.timestamp AS last_activity,
          'client' AS user_type,
          p.partner_type,
          p.isVendor,
          p.customerTypeId,
          p.id AS partner_id
        FROM partners p
        WHERE p.status = 1 AND p.isVendor = 0 AND p.user_id IS NOT NULL
      `;

    const partners = await sequelize.query(partnersQuery, {
      type: Sequelize.QueryTypes.SELECT,
    });

    let singleUser = [];
    if(userId != 1){
      // 3️⃣ Fetch the user with ID = 1 separately
      const singleUserQuery = `
        SELECT 
          u.id,
          u.id AS user_id, 
          u.first_name, 
          u.last_name, 
          CONCAT(u.first_name,' ',u.last_name) AS full_name,
          u.profile_pic,
          u.email,
          NULL AS phone,
          ur.name AS position,
          u.active,
          u.chat_status,
          u.last_activity,
          CASE 
            WHEN u.user_type = 1 THEN 'admin'
            WHEN u.user_type = 2 THEN 'team_member'
            ELSE 'other'
          END AS user_type,
          u.user_type AS user_type_id
        FROM users u
        LEFT JOIN user_roles ur ON u.user_type = ur.id  
        WHERE u.id = 1
      `;

      singleUser = await sequelize.query(singleUserQuery, {
        type: Sequelize.QueryTypes.SELECT,
      });
    }

    const finalTeamMembers = [...singleUser, ...teamMembers, ...partners ];

    console.log("✅ Team Members Found:", finalTeamMembers.length);

    // 3️⃣ Fetch Conversations (ONLY if the user is in conversation_members)
    const chatListQuery = `
    SELECT 
      CONCAT('chat_', c.id) AS new_conversation_id,  -- Ensure unique ID
      c.id AS conversation_id,
      c.type AS conversation_type,
      c.group_name,
      
      CASE 
        WHEN c.type = 'group' THEN NULL  
        ELSE u.id 
      END AS user_id,
  
      (SELECT m.message 
       FROM messages m 
       WHERE m.conversation_id = c.id 
       ORDER BY m.created_at DESC 
       LIMIT 1) AS last_message,
  
      (SELECT m.sender_id 
       FROM messages m 
       WHERE m.conversation_id = c.id 
       ORDER BY m.created_at DESC 
       LIMIT 1) AS last_message_sender_id,
  
      (SELECT u2.first_name 
       FROM messages m 
       LEFT JOIN users u2 ON u2.id = m.sender_id 
       WHERE m.conversation_id = c.id 
       ORDER BY m.created_at DESC 
       LIMIT 1) AS last_message_sender,
  
      (SELECT m.created_at 
       FROM messages m 
       WHERE m.conversation_id = c.id 
       ORDER BY m.created_at DESC 
       LIMIT 1) AS last_message_time,
  
      CASE 
        WHEN c.type = 'group' THEN 
          (
            SELECT COUNT(*) 
            FROM read_message rm 
            WHERE rm.conversation_id = c.id 
              AND rm.user_id = :userId 
              AND rm.read_status = 1
          )
        ELSE 
          (
            SELECT COUNT(*) 
            FROM messages m 
            WHERE m.conversation_id = c.id 
              AND m.sender_id != :userId 
              AND m.read_status = 1
          )
      END AS unread_messages
  
    FROM conversations c
    LEFT JOIN conversation_members cm ON c.id = cm.conversation_id
    LEFT JOIN users u ON cm.user_id = u.id AND u.id != :userId
    WHERE cm.user_id = :userId
    GROUP BY c.id
    ORDER BY 
      CASE WHEN 
        (SELECT m.created_at FROM messages m WHERE m.conversation_id = c.id ORDER BY m.created_at DESC LIMIT 1)
        IS NULL THEN 1 ELSE 0 END,
      (SELECT m.created_at FROM messages m WHERE m.conversation_id = c.id ORDER BY m.created_at DESC LIMIT 1) DESC;
  `;
  

    let chatList = await sequelize.query(chatListQuery, {
      replacements: { userId },
      type: Sequelize.QueryTypes.SELECT,
    });

    console.log("✅ Existing Conversations Found:", chatList.length);

    
    // 3️⃣ Fetch Participants Separately
    const conversationIds = chatList.map(chat => chat.conversation_id).filter(id => id !== null);
    console.log('conversationIds++++++++', conversationIds)
    
    if (conversationIds.length > 0) {
      const participantsQuery = `
          SELECT 
              cm.conversation_id,
              cm.is_admin,
              cm.email_notif,
              cm.phone_notif,
              u.id AS user_id,
              COALESCE(u.first_name, p.name) AS first_name,
              COALESCE(u.last_name, p.last_name) AS last_name,
              CONCAT(COALESCE(u.first_name, p.name),' ',COALESCE(u.last_name, p.last_name)) AS full_name,
              COALESCE(u.profile_pic, p.thumbnail) AS profile_pic,
              COALESCE(u.email, p.email) AS email,
              COALESCE(sm.phone, p.phone) AS phone,
              COALESCE(ur.name, 'Client') AS position,
              COALESCE(u.active, CASE WHEN p.onboard_complete = 0 THEN 'N' ELSE 'Y' END) AS active,
              COALESCE(u.chat_status, 'online') AS chat_status,
              COALESCE(u.last_activity, p.timestamp) AS last_activity,
              CASE 
                WHEN p.id IS NOT NULL THEN 'client'
                WHEN u.user_type = 1 THEN 'admin'
                WHEN u.user_type = 2 THEN 'team_member'
                ELSE 'other'
              END AS user_type,
              u.user_type AS user_type_id
          FROM conversation_members cm
          LEFT JOIN users u ON cm.user_id = u.id
          LEFT JOIN staff_members sm ON sm.user_id = u.id 
          LEFT JOIN user_roles ur ON u.user_type = ur.id
          LEFT JOIN partners p ON p.user_id = u.id
          WHERE cm.conversation_id IN (:conversationIds);
      `;

      const participants = await sequelize.query(participantsQuery, {
        replacements: { conversationIds },
        type: Sequelize.QueryTypes.SELECT,
      });

      // 4️⃣ Attach participants to conversations
      const participantsMap = {};
      participants.forEach((p) => {
        if (!participantsMap[p.conversation_id]) {
          participantsMap[p.conversation_id] = [];
        }
        participantsMap[p.conversation_id].push(p);
      });

      chatList.forEach((chat) => {
        chat.participants = participantsMap[chat.conversation_id] || [];
      });
    }

    // 4️⃣ Fetch Last Message & Last Message Time for Single Chats
    const singleChatUsers = finalTeamMembers.map(member => member.user_id).filter(id => id !== null);

    if (singleChatUsers.length > 0) {
      const singleChatQuery = `
        SELECT 
          m1.conversation_id,
          m1.sender_id,
          COALESCE(u.first_name, p.name) AS sender_name,
          m1.message AS last_message,
          m1.message_type,
          m1.created_at AS last_message_time
        FROM messages m1
        LEFT JOIN users u ON u.id = m1.sender_id
        LEFT JOIN partners p ON p.user_id = m1.sender_id
        WHERE m1.id = (
          SELECT MAX(m2.id) 
          FROM messages m2
          WHERE 
            (m2.conversation_id = m1.conversation_id AND m2.sender_id = :userId) 
            OR 
            (m2.sender_id = m1.sender_id AND m2.conversation_id = :userId)
        );
      `;
    
      const singleChatMessages = await sequelize.query(singleChatQuery, {
        replacements: { userId },
        type: Sequelize.QueryTypes.SELECT,
      });

      // Map last messages to user IDs
      const lastMessagesMap = {};
      singleChatMessages.forEach(msg => {
        const otherUserId = msg.sender_id === userId ? msg.conversation_id : msg.sender_id;
        lastMessagesMap[otherUserId] = {
          // last_message: msg.message_type === 'text' ? (msg.sender_id == userId ? `You: ${msg.last_message}` : msg.last_message) : (msg.sender_id == userId ? `You sent a photo` : 'Sent a photo'),
          last_message: msg.message_type === 'text' 
            // ? (msg.sender_id === userId ? `You: ${msg.last_message}` : `${msg.sender_name}: ${msg.last_message}`) 
            ? (msg.sender_id === userId ? `You: ${msg.last_message}` : `${msg.last_message}`) 
            : (msg.sender_id === userId ? `You sent a photo` : `${msg.sender_name} sent a photo`),
          last_message_time: msg.last_message_time,
        };
      });

      const unreadQuery = `
          SELECT 
            sender_id,
            COUNT(*) AS unread_count
          FROM messages
          WHERE 
            conversation_id = :userId AND 
            read_status = 1
          GROUP BY sender_id
        `;

        const unreadMessagesRaw = await sequelize.query(unreadQuery, {
          replacements: { userId },
          type: Sequelize.QueryTypes.SELECT,
        });
        const unreadMessagesMap = {};
        
        unreadMessagesRaw.forEach(row => {
          unreadMessagesMap[row.sender_id] = parseInt(row.unread_count, 10);
        });
        console.log('unreadMessagesMap11111111', unreadMessagesMap)
      // 5️⃣ Add team members without conversations (Only for single chats)
      const userChatMap = new Map(
        chatList.map((chat) => {
          chat.unread_messages = chat.unread_messages || 0;
          return [chat.conversation_id ?? chat.new_conversation_id, chat];
        })
      );

      finalTeamMembers.forEach((member, index) => {
        if (member.user_id !== null) {
          const newUserId = Number(member.user_id);
          const chatId = `user_${newUserId}`; // Unique ID for single chat users
          if (!userChatMap.has(chatId)) {
            userChatMap.set(chatId, {
              id: index + 1,
              conversation_id: null,
              new_conversation_id: chatId,
              conversation_type: "single",
              group_name: null,
              user_id: newUserId,
              first_name: member?.first_name,
              last_name: member?.last_name,
              full_name: member?.full_name,
              profile_pic: member?.profile_pic,
              phone: member?.phone || null,
              email: member?.email,
              position: member?.position, 
              active: member?.active,
              last_activity: member?.last_activity,
              chat_status: member?.chat_status,
              user_type: member?.user_type, // Add user type for frontend display
              partner_type: member?.partner_type,
              isVendor: member?.isVendor,
              customerTypeId: member?.customerTypeId,
              partner_id: member?.partner_id, // Add partner_id for reference
              last_message: lastMessagesMap[newUserId]?.last_message || null, // Assign last message
              last_message_time: lastMessagesMap[newUserId]?.last_message_time || null, // Assign last message time
              participants: [],
              unread_messages: unreadMessagesMap[newUserId] || 0,
            });
          }
        }
      });

      // Convert to array and sort
      const finalChatList = Array.from(userChatMap.values()).sort((a, b) => {
        // Sort by last message time in descending order (latest first)
        if (a.last_message_time && b.last_message_time) {
            return new Date(b.last_message_time) - new Date(a.last_message_time);
        }
        
        // If one has a last message time and the other does not, keep the one with time first
        if (a.last_message_time) return -1;
        if (b.last_message_time) return 1;

        // If neither has a last message, sort alphabetically
        return (a.group_name || a.first_name || "").localeCompare(b.group_name || b.first_name || "");
      });

      console.log("✅ Final Chat List Count (Sorted):", finalChatList.length);
      return finalChatList;
    }

  } catch (error) {
    console.error("❌ Error fetching chat list:", error);
    return null;
  }
};
// const readMessages =async (messageId, userId, conversationId, conversationType)=>{
//   try {
//     await sequelize.query(
//       `
//       UPDATE messages
//       SET read_status = 0
//       WHERE id = :messageId AND receiver_id = :userId
//       `,
//       {
//         replacements: { messageId, userId },
//         type: Sequelize.QueryTypes.UPDATE,
//       }
//     );
//     await sequelize.query(
//       `
//       INSERT INTO read_message (conversation_id, user_id, message_id, read_status)
//       VALUES (:conversationId, :userId, :messageId, 1)
//       ON DUPLICATE KEY UPDATE read_status = 1
//       `,
//       {
//         replacements: { conversationId, userId, messageId },
//         type: Sequelize.QueryTypes.INSERT,
//       }
//     );
//     return true;
//   } catch (error) {
//     console.error("❌ Error read the messages:", error);
//     return null;
//   }
// }
const readMessages = async (messageIdsStr, userId, conversationId, conversationType) => {
  try {
    if (!userId || !conversationId || !messageIdsStr) {
      return { status: 0, message: "Missing required data" };
    }

    const messageIds = messageIdsStr
      .split(',')
      .map(id => parseInt(id.trim(), 10))
      .filter(Boolean);

    if (messageIds.length === 0) {
      return { status: 0, message: "No valid message IDs provided." };
    }

    let updated = 0;

    console.log('messageIds+++++++++++++++',messageIds )
    if (conversationType === 2 || conversationType === "group") {
      // ✅ Group: insert directly to read_message
      for (const msgId of messageIds) {
         await Messages.update(
          { read_status: 0 },
          {
            where: {
              id: msgId,
            },
          });
        await ReadMessage.update({
          read_status: 0
        },{
          where:{
            message_id: msgId,
            conversation_id: conversationId,
            user_id: userId            
          }
        });
        updated++;
      }
    } else {
      // ✅ Single: update read_status directly in messages table
   
      for (const msgId of messageIds) {
        
        console.log('messageIds+++++++++++++++',conversationId,msgId,userId) 
        const [affected] = await Messages.update(
          { read_status: 0 },
          {
            where: {
              id: msgId,
            },
          }
        );
      
        if (affected > 0) {
          updated++;
        }
      }
      
    }
    console.log('messageIds+++++++++++++++', updated)
return true;
    // return {
    //   status: 1,
    //   message: `${updated} message(s) marked as read.`,
    // };
  } catch (error) {
    console.error("❌ Error in readMessages:", error);
    return { status: 0, message: "Failed to mark messages as read" };
  }
};


const createGroup = async (groupName, createdBy, members, created_at) => {
  try {
    console.log("🚀 Creating group:", groupName, "by user:", createdBy);

    const transaction = await sequelize.transaction();

    try {
      // ✅ 1️⃣ Create the group conversation
      const newConversation = await Conversations.create(
        {
          type: "group",
          group_name: groupName,
          created_by: createdBy,
          created_at: created_at,
        },
        { transaction }
      );

      console.log("✅ Group Created with ID:", newConversation.id);

      // ✅ 2️⃣ Add all members to `conversation_members`
      const memberData = members.map((userId) => ({
        conversation_id: newConversation.id,
        user_id: userId,
        added_at: created_at,
        is_admin: (userId === createdBy) ? 1 : 0
      }));

      await ConversationMembers.bulkCreate(memberData, { transaction });

      // ✅ 3️⃣ Commit the transaction
      await transaction.commit();

      return {
        status: 1,
        message: "Group chat created successfully",
        conversationId: newConversation.id,
      };
    } catch (error) {
      console.error("❌ Transaction Error:", error);
      await transaction.rollback();
      return { status: 0, message: "Failed to create group chat" };
    }
  } catch (error) {
    console.error("❌ Error creating group chat:", error);
    return { status: 0, message: "Server error" };
  }
};
const updateGroupName = async (conversationId, newGroupName) => {
  try {
    const result = await Conversations.update(
      { group_name: newGroupName },
      { where: { id: conversationId, type: 'group' } }
    );

    return result[0] > 0; // Returns true if updated
  } catch (error) {
    console.error("❌ Error updating group name:", error);
    return false;
  }
};

const sendMessage = async (data) => {
  try{
    const result = await Messages.create(data);
    if(result){
      return result?.dataValues?.id;
    }else{
      return false;
    }
  }catch(error){
    console.log('sendMessage error : ',error)
    return false;
  }
}

const editMessage = async ({ messageId, senderId, message }) => {
  try {
    const [updatedCount] = await Messages.update(
      { message: message, chat_log_msg: message },
      { where: { id: messageId, sender_id: senderId } }
    );
    return updatedCount > 0;
  } catch (error) {
    console.log('editMessage error : ', error);
    return false;
  }
};
const CreateReadMessage = async (data,userId) => {
  try{
    const members = await ConversationMembers.findAll({
      where: { conversation_id: data.conversation_id,user_id: { [Sequelize.Op.ne]: userId} },
      attributes: ['user_id','conversation_id'],
    });
    let results = [];

    console.log('members+++++++++++++++1', members)
    for(const member of members){
      let userId = member.user_id
      let result = await ReadMessage.create({...data,user_id:userId});
      if (result) {
        results.push(result);
      }
    }
    if (results.length > 0) {
      return results.map(r => r.id); // return an array of created IDs
    } else {
      return false;
    }
  }catch(error){
    console.log('CreateReadMessage error: ',error)
    return false;
  }
}

const fetchMessages = async (conversationId, userId, conversationType,chatOpenTime) => {
  try {
    let whereCondition;
    let replacements = { conversationId, userId };
    let readStatusJoin = '';
    let readStatusSelect = '';
    if (conversationType === 'single') {
      whereCondition = `(
        (m.conversation_id = :conversationId AND m.sender_id = :userId) 
        OR 
        (m.conversation_id = :userId AND m.sender_id = :conversationId)
      ) AND m.conversation_type = 1`; 
      readStatusSelect = `m.read_status AS read_status`;
    } else {
      whereCondition = `m.conversation_id = :conversationId AND m.conversation_type = 2`; 
      readStatusJoin = `
        LEFT JOIN read_message rm 
          ON rm.message_id = m.id 
          AND rm.conversation_id = :conversationId 
          AND rm.user_id = :userId
      `;
      readStatusSelect = `IFNULL(rm.read_status, 0) AS read_status`;
    }

    // const result = await sequelize.query(
    //   `SELECT m.*, u.first_name, u.profile_pic, cl.name AS chat_log_title 
    //    FROM messages m 
    //    LEFT JOIN users u ON u.id = m.sender_id
    //    LEFT JOIN chat_logs cl ON cl.id = m.chat_log_id
    //    WHERE ${whereCondition} 
    //    ORDER BY m.id ASC`,
    //   {
    //     replacements,
    //     type: sequelize.QueryTypes.SELECT,
    //   }
    // );
    const query = `
    SELECT 
      m.*, 
      COALESCE(u.first_name, p.name) AS first_name, 
      COALESCE(u.profile_pic, p.thumbnail) AS profile_pic, 
      cl.name AS chat_log_title,cl.timestamp AS chatlogTime,
      ${readStatusSelect}
    FROM messages m
    LEFT JOIN users u ON u.id = m.sender_id
    LEFT JOIN partners p ON p.user_id = m.sender_id
    LEFT JOIN chat_logs cl ON cl.id = m.chat_log_id
    ${readStatusJoin}
    WHERE ${whereCondition}
    ORDER BY m.id ASC
  `;
  const result = await sequelize.query(query, {
    replacements,
    type: sequelize.QueryTypes.SELECT,
  });
    return result.length ? result : [];

  } catch (error) {
    console.error('fetchMessages error:', error);
    return [];
  }
};


// const fetchNewMessages = async (conversationId, userId, conversationType, chatOpenTime,selectedChatLogId) => {
//   try {
//     let whereCondition;
//     let replacements = { conversationId, userId };

//     if (conversationType === 'single') {
//       whereCondition = `(
//         (m.conversation_id = :conversationId AND m.sender_id = :userId) 
//         OR 
//         (m.conversation_id = :userId AND m.sender_id = :conversationId)
//       ) AND m.conversation_type = 1`;
//     } else {
//       whereCondition = `m.conversation_id = :conversationId AND m.conversation_type = 2`;
//     }

//     // Apply chatOpenTime filter if it exists
//     if (chatOpenTime && !selectedChatLogId) {
//       whereCondition += ` AND m.created_at >= :chatOpenTime`;
//       replacements.chatOpenTime = chatOpenTime;
//     }

//       // Apply selectedChatLogId filter if provided
//       if (selectedChatLogId) {
//         whereCondition += ` AND m.chat_log_id = :selectedChatLogId`;
//         replacements.selectedChatLogId = selectedChatLogId;
//       }
  

//     const result = await sequelize.query(
//       `SELECT m.*, u.first_name, u.profile_pic 
//        FROM messages m 
//        LEFT JOIN users u ON u.id = m.sender_id
//        WHERE ${whereCondition} 
//        ORDER BY m.id ASC`,
//       {
//         replacements,
//         type: sequelize.QueryTypes.SELECT,
//       }
//     );

//     return result.length ? result : [];

//   } catch (error) {
//     console.error('fetchMessages error:', error);
//     return [];
//   }
// };
const fetchNewMessages = async (conversationId, userId, conversationType, chatOpenTime, selectedChatLogId) => {
  try {
    let whereCondition;
    let replacements = { conversationId, userId };
    let readStatusJoin = '';
    let readStatusSelect = '';

    if (conversationType === 'single') {
      whereCondition = `(
        (m.conversation_id = :conversationId AND m.sender_id = :userId) 
        OR 
        (m.conversation_id = :userId AND m.sender_id = :conversationId)
      ) AND m.conversation_type = 1`;

      readStatusSelect = `m.read_status AS read_status`; // ✅ From messages
    } else {
      whereCondition = `m.conversation_id = :conversationId AND m.conversation_type = 2`;

      readStatusJoin = `
        LEFT JOIN read_message rm 
          ON rm.message_id = m.id 
          AND rm.conversation_id = :conversationId 
          AND rm.user_id = :userId
      `;

      readStatusSelect = `IFNULL(rm.read_status, 0) AS read_status`; // ✅ From read_message or 0
    }

    // ⏱️ Filter by chatOpenTime
    if (chatOpenTime && !selectedChatLogId) {
      whereCondition += ` AND m.created_at >= :chatOpenTime`;
      replacements.chatOpenTime = chatOpenTime;
    }

    // 📝 Filter by selectedChatLogId
    if (selectedChatLogId) {
      whereCondition += ` AND m.chat_log_id = :selectedChatLogId`;
      replacements.selectedChatLogId = selectedChatLogId;
    }

    const query = `
      SELECT 
        m.*, 
        COALESCE(u.first_name, p.name) AS first_name, 
        COALESCE(u.profile_pic, p.thumbnail) AS profile_pic,
        ${readStatusSelect}
      FROM messages m
      LEFT JOIN users u ON u.id = m.sender_id
      LEFT JOIN partners p ON p.user_id = m.sender_id
      ${readStatusJoin}
      WHERE ${whereCondition}
      ORDER BY m.id ASC
    `;

    const result = await sequelize.query(query, {
      replacements,
      type: sequelize.QueryTypes.SELECT,
    });

    return result.length ? result : [];

  } catch (error) {
    console.error('❌ fetchNewMessages error:', error);
    return [];
  }
};


const getChatStatusByUserId = async (userId) => {
  try {
    const result = await Users.findOne({
      where: { id: userId },
      attributes: ['chat_status'],
    });

    if (result) {
      return result;
    } else {
      return false;
    }
  } catch (error) {
    console.log('getChatStatusByUserId Error', error);
    return false;
  }
};

const addChatLog = async (data) => {
  try{
    const result = await ChatLog.create(data);
    if(result){
      return result?.dataValues?.id;
    }else{
      return false;
    }
  }catch(error){
    console.log('addChatLog error : ',error)
    return false;
  }
}

const updateChatLog = async (data, chatLogId) => {
  try{
    const result = await ChatLog.update(data,{
      where : { id : chatLogId}
    });
    console.log('result==',result);
    console.log('data==',data);
    console.log('chatLogId==',chatLogId);
    if(result){
      return true;
    }else{
      return false;
    }
  }catch(error){
    console.log('updateChatLog error : ',error)
    return false;
  }
}

const deleteChatLog = async (chatLogId) => {
  try{
    const result = await ChatLog.destroy({
      where : { id : chatLogId}
    });
    if(result){
      return true;
    }else{
      return false;
    }
  }catch(error){
    console.log('deleteChatLog error : ',error)
    return false;
  }
}

const deleteMessagesByChatLogId = async (chatLogId) => {
  try{
    const getmesgageId = await Messages.findAll({
      where : { chat_log_id : chatLogId}
    }); 
    if(getmesgageId.length > 0){
      for(const message of getmesgageId){
        const read= await ReadMessage.destroy({
          where : { message_id : message.id}
        });
        
      }
    } 
    const result = await Messages.destroy({
      where : { chat_log_id : chatLogId}
    });
    if(result){
      return true;
    }else{
      return false;
    }
  }catch(error){
    console.log('deleteMessagesByChatLogId error : ',error)
    return false;
  }
}

const removeMemberFromGroup = async (conversationId, userId) => {
  try{
    const result = await ConversationMembers.destroy({
      where : { conversation_id: conversationId, user_id: userId }
    });
    if(result){
      return true;
    }else{
      return false;
    }
  }catch(error){
    console.log('removeMemberFromGroup error : ',error)
    return false;
  }
}

const checkIfGroupAdmin = async (conversationId, userId) => {
  try {
    const result = await ConversationMembers.findOne({
      where: { conversation_id: conversationId, user_id: userId, is_admin: 1 }
    });
    
    return !!result;
  } catch (error) {
    console.error('checkIfGroupAdmin error:', error);
    return false;
  }
};

const checkIfAnyOtherGroupAdminIsAvailable = async (conversationId, userId) => {
  try {
    const result = await ConversationMembers.findOne({
      where: {
        conversation_id: conversationId,
        is_admin: 1,
        user_id: { [Sequelize.Op.ne]: userId }
      }
    });

    return !!result;
  } catch (error) {
    console.error('checkIfAnyOtherGroupAdminIsAvailable error:', error);
    return false;
  }
};

const updateConversationMembers = async (data, conversationId, userId) => {
  console.log('data, conversationId, userId', data, conversationId, userId)
  try{
    const result = await ConversationMembers.update(data,{
      where : { conversation_id: conversationId, user_id:userId }
    });
    
    console.log('resultresultresult',result )
    if(result){
      return true;
    }else{
      return false;
    }
  }catch(error){
    console.log('updateConversationMembers error : ',error)
    return false;
  }
}
const getConversationMembers = async (conversationId, userId) => {
  try {
    const result = await ConversationMembers.findOne({
      where: { conversation_id: conversationId, user_id: userId }
    });

    return result; // Will return the user data or null if not found
  } catch (error) {
    console.log('getConversationMembers error:', error);
    return null;
  }
};
const getConversationMembersData = async (conversationId, userId) => {
  try {
    const results = await sequelize.query(
      `
     SELECT cm.*, c.group_name, COALESCE(u.alert_status, 1) AS alert_status,
            COALESCE(u.first_name, p.name) AS first_name,
            COALESCE(u.last_name, p.last_name) AS last_name,
            COALESCE(u.email, p.email) AS email,
            COALESCE(u.profile_pic, p.thumbnail) AS profile_pic,
            cm.phone_notif,
            cm.email_notif
      FROM conversation_members cm
      LEFT JOIN conversations c ON c.id = cm.conversation_id
      LEFT JOIN users u ON u.id = cm.user_id
      LEFT JOIN partners p ON p.user_id = cm.user_id
      WHERE cm.conversation_id = :conversationId AND cm.user_id != :userId
      `,
      {
        replacements: { conversationId, userId },
        type: sequelize.QueryTypes.SELECT
      }
    );
    return results.length ? results : []; // return array of members

  } catch (error) {
    console.log('getConversationMembers error (raw query):', error);
    return [];
  }
};



const addMembersToGroup = async (data) => {
  try {
    const result = await ConversationMembers.bulkCreate(data);
    return !!result;
  } catch (error) {
    console.error('addMembersToGroup error:', error);
    return false;
  }
};
const incrementNotifiCount =async(conversation_id,sender_id,amount)=>{
  try {
    const result =  ConversationMembers.update(
      {
        unread_msg: sequelize.literal(`unread_msg + ${amount}`)
      },
      {
        where: {
          conversation_id,
          user_id: { [Sequelize.Op.ne]: sender_id } // ✅ should be user_id not sender_id
        }
    }
    );
    return result;
  } catch (error) {
    console.error("Error incrementing unread message count:", error);
  }
}
const addSingleChatMembers = async (data) => {
  try{
    const result = await SingleChatMembers.create(data);
    if(result){
      return result?.dataValues?.id;
    }else{
      return false;
    }
  }catch(error){
    console.log('addChatLog error : ',error)
    return false;
  }
}
const isSingleChatMembersExists = async (receiver_id, sender_id) => {
  try {
    const result = await SingleChatMembers.findOne({
      where: {
        receiver_id,
        sender_id,
      },
    });

    if (result) {
      return result?.dataValues?.id;
    } else {
      return false;
    }
  } catch (error) {
    console.log('isSingleChatMembersExists error:', error);
    return false;
  }
};
const singleChatMemberData = async (receiver_id, sender_id) => {
  try {
    const query = `
      SELECT scm.*, 
       ur.alert_status AS receiver_alert_status,
       us.alert_status AS sender_alert_status
FROM single_chat_members scm
LEFT JOIN users ur ON ur.id = scm.receiver_id
LEFT JOIN users us ON us.id = scm.sender_id
WHERE scm.receiver_id = :receiver_id AND scm.sender_id = :sender_id;

    `;
    const result = await sequelize.query(query, {
      replacements: { receiver_id, sender_id },
      type: Sequelize.QueryTypes.SELECT,
    });

    console.log('results1111111111111111', result)
    if (result.length > 0) {
      return result[0];
    } else {
      return false;
    }
  } catch (error) {
    console.log('isSingleChatMembersExists error:', error);
    return false;
  }
};
const updateSingleChatMembers = async (data, id) => {
  console.log('heiiiiiiiiiiiiiiiiiii', data, id)
  try {
    const [affectedRows] = await SingleChatMembers.update(data, {
      where: { id },
    });
    // if (affectedRows > 0) {
      return true; // Return the updated record ID
    // } else {
    //   return false; // No rows updated (maybe invalid ID)
    // }
  } catch (error) {
    console.log('isSingleChatMembersExists error:', error);
    return false;
  }
};

module.exports = {
  createConversation,
  getChatList,
  createGroup,
  updateGroupName,
  sendMessage,
  editMessage,
  fetchMessages,
  CreateReadMessage,
  fetchNewMessages,
  getChatStatusByUserId,
  addChatLog,
  updateChatLog,
  deleteChatLog,
  deleteMessagesByChatLogId,
  removeMemberFromGroup,
  checkIfGroupAdmin,
  checkIfAnyOtherGroupAdminIsAvailable,
  updateConversationMembers,
  addMembersToGroup,
  getConversationMembersData,
  readMessages,
  incrementNotifiCount,
  getConversationMembers,
  addSingleChatMembers,
  isSingleChatMembersExists,
  updateSingleChatMembers,
  singleChatMemberData
};
