const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const fs = require('node:fs');
const util = require('node:util');
const { pipeline } = require('node:stream');
const { empty } = require("../Helpers/Common");
const { log } = require("node:console");
const pump = util.promisify(pipeline);


const ProjectPost = sequelize.define('project_posts', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    task_id:{type:Sequelize.TINYINT,allowNull:false,defaultValue:null},
    message:{type:Sequelize.TEXT,allowNull:false,defaultValue:null},
    post_date :{type:Sequelize.DATE,allowNull:true,defaultValue:null},
    pinned:{type:Sequelize.TINYINT(2),allowNull:false,defaultValue:0},
    pinned_by:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
},{timestamps:false})

const Projectpostlikes = sequelize.define('project_post_likes',{
     id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
     post_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
     user_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
},{timestamps:false})

const ProjectpostStarred = sequelize.define('project_post_starred',{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    post_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    user_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
},{timestamps:false,tableName:'project_post_starred'})

const Projectpostcommentlikes = sequelize.define('project_post_comment_likes',{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    comment_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    user_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
},{timestamps:false,tableName:'project_post_comment_likes'})

const PostComments=sequelize.define('project_post_comments',{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    post_id:{type:Sequelize.INTEGER,allowNull:false,defaultValue:null},
    message:{type:Sequelize.TEXT,allowNull:false,defaultValue:null},
    comment_id:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
},{timestamps:false,tableName:'project_post_comments'})
////////////////////////////

const addProjectPost=async(data)=>{
    try {
        let result = await ProjectPost.create(data)
        return result.id
    } catch (error) {
        console.log(error)
    }
}
const getAllProjectPostsByTask=async(id,userId)=>{
    try{
let query=await sequelize.query(" select pp.*,u.profile_pic, concat(u.first_name,' ',u.last_name) as username, concat(pu.first_name,' ',pu.last_name) as pinned_by_username,coalesce(pps.id,0) as starred,coalesce(ppl.id,0) as liked,coalesce(pplc.count,0) as likes_count,coalesce(ppc.count,0) as comments_count from project_posts pp"
                +" left join users u on pp.added_by=u.id"
                +" left join users pu on pp.pinned_by=pu.id"
                +` left join project_post_starred pps on pps.user_id=${userId} and pps.post_id=pp.id`
                +` left join project_post_likes ppl on ppl.user_id=${userId} and ppl.post_id=pp.id`
                +" left join (select count(id) as count, post_id from project_post_likes group by post_id) pplc on pplc.post_id=pp.id"
                +" left join (select count(id) as count, post_id from project_post_comments where status!=2 and comment_id is null group by post_id) ppc on ppc.post_id=pp.id"
                +` where pp.task_id=${id} and pp.status!=2 order by pp.pinned DESC, pp.id DESC`);

            let data=[]
            if(query[0].length>0){
                row=query[0]
                for(let i=0;i<row.length;i++){
                    console.log("################",row[i].id);
                    row[i].comments=await getAllProjectPostComments(row[i].id,0,userId)
                    console.log("#######MIDhunnnnnnnnnnnnnn",row[i].comments);
                }
                console.log("$$$$$$$$$$$$$$$",row);
                data=row;
            }
        return data;
    }
    catch(error){

    }
}
const ispostLiked=async(userId,id)=>{
    try {
       if(!empty(userId)||!empty(id)){
        
        const query = `SELECT id FROM project_post_likes where post_id=${id} and user_id=${userId}`;
        const [rows] = await sequelize.query(query);
        if (rows && rows.length >= 1) {
            return true;
          } else {
            return false;
          }
       }
    } catch (error) {
        console.log(error)
    }
}

const deleteProjectpostLike = async (userId, postId) => {
    try {
        const res = await Projectpostlikes.destroy({
            where: {
                user_id: userId,
                post_id: postId,
            },
        });

        if (res) {
            return true;
        } else {
            console.log(`No job post like found for user ${userId} and post ${postId}`);
            return false;
        }
    } catch (error) {
        // Handle the error
        console.log(error);
        return false;
    }
};

const addProjectPostLike=async(data)=>{
    try {
        const result = await Projectpostlikes.create(data)
        return result.id
    } catch (error) {
        console.log(error)
    }
}
const getprojectpostlikesCount=async(post_id)=>{
   const result = await sequelize.query(`
   select count(id) as count from project_post_likes where post_id=${post_id}
   `)
   if(!empty(result)){
    if(result[0].length ===1){
        return result[0]
    }else{
        return 0
    }
   }
}
const isPostStarred=async(user_id,post_id,)=>{
    try {
        if(!empty(post_id)||!empty(user_id)){
            const query = `SELECT id FROM project_post_starred where post_id=${post_id} and user_id=${user_id}`;
            const [rows] = await sequelize.query(query);
            if (rows && rows.length >= 1) {
                return true;
              } else {
                return false;
              }
           }
    } catch (error) {
        console.log(error)
    }
}
const isPostCommentLiked=async(commentId,userId)=>{
    try {
       
        if(!empty(commentId)||!empty(userId)){
            const query = `SELECT id FROM project_post_comment_likes where comment_id=${commentId} and 
            user_id=${userId}`;
            const [rows] = await sequelize.query(query);
            console.log(rows,'----row')
            if (rows && rows.length >= 1) {
                return true;
              } else {
                return false;
              }
           }
    } catch (error) {
        console.log(error)
    }
}

const deleteProjectPostCommentLike=async(userId,commentId)=>{
    try {
        const res = await Projectpostcommentlikes.destroy({
            where: {
                user_id: userId,
                comment_id: commentId,
            },
        })
        if (res) {
            return true;
        } else {
            return false;
        }
    } catch (error) {
        console.log(error)
    }
}
const deleteStarredPost = async (userId, postId) => {
    try {
        const res = await ProjectpostStarred.destroy({
            where: {
                user_id: userId,
                post_id: postId,
            },
        });

        if (res) {
            return true;
        } else {
            console.log(`No project post starred found for user ${userId} and post ${postId}`);
            return false;
        }
    } catch (error) {
        // Handle the error
        console.log(error);
        return false;
    }
};

const addProjectPostCommentLike=async(data)=>{
    try {
        const result = await Projectpostcommentlikes.create(data)
        return result.id
    } catch (error) {
        console.log(error)
    }
}
const addStarredPost=async(data)=>{
    try {

        const result = await ProjectpostStarred.create(data)
        return result.id
    } catch (error) {
        console.log(error)
    }
}

const getprojectPostByid=async(id)=>{
    try {
        console.log(id,'[[[[[[[[[[[[[[[[[[[[')
       let result = await sequelize.query(`
       SELECT
       pp.*,
       u.profile_pic,
       CONCAT(u.first_name, ' ', u.last_name) AS username,
       CONCAT(pu.first_name, ' ', pu.last_name) AS pinned_by_username
     FROM
       project_posts pp
     LEFT JOIN
       users u ON pp.added_by = u.id
     LEFT JOIN
       users pu ON pp.pinned_by = pu.id
     WHERE
       pp.id = ${id}`) 
       console.log(result,'----------------res')
       if(result){
        if(result[0].length ===1){
            return result[0]
        }else{
            return false
        }
       }else{return false}
    } catch (error) {
        console.log(error)
    }
}
const updateProjectPost=async(id,data)=>{
  let result = await ProjectPost.update(data,{
    where:{
        id:id
    },returning:true
  })
  if(result){
    return true
  }else{
    return false
  }
}
const updateprojectPostComments=async(id,data)=>{
    try {
       let result = await PostComments.update(data,{
        where:{
            id:id
        },returning :true
       })
       if(result){
        return true
       }else{
        return false
       }
    } catch (error) {
        console.log(error)
    }
}

const addPostCommnets=async(data)=>{
    try {
        let result = await PostComments.create(data)
        return result.id
    } catch (error) {
        console.log(error,'000000000000000')
    }
}
const getprojectPostCommentscount=async(postId)=>{
    let result = await sequelize.query(` select count(id) as count from project_post_comments where id=${postId} and comment_id is NULL and status!=2
    `)
    if(result){
       
        if(result[0][0].length ===1){
           return result[0][0]['count'] 
        } else{
            return false
        }
    }else{
        return false
    }
}
const getAllUserReplace=async(req,res)=>{
    try {
        let result = await sequelize.query(`
        SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) AS username
        FROM users AS u
        ORDER BY u.id DESC;
        
        `)
        const data = {};
        if(result[0].length>0){
            result.forEach((row) => {
                data[`{@${row.id}}`] = `<span class="mentioned-user">@${row.username}</span>`;
              });
        }
        return data;
    } catch (error) {
        console.log(error)
    }
}
const getProjectPostCommentViewById=async(id,userId=0)=>{
    try {
        let result = await sequelize.query(`
        SELECT
  pc.*,
  u.profile_pic,
  CONCAT(u.first_name, ' ', u.last_name) AS username,
  COALESCE(ppl.id, 0) AS liked,
  COALESCE(pplc.count, 0) AS likes_count
FROM
  project_post_comments pc
LEFT JOIN
  users u ON pc.added_by = u.id
LEFT JOIN
  project_post_comment_likes ppl ON ppl.user_id = ${userId} AND ppl.comment_id = pc.id
LEFT JOIN
  (SELECT COUNT(id) AS count, comment_id FROM project_post_comment_likes GROUP BY comment_id) pplc
  ON pplc.comment_id = pc.id
WHERE
  pc.id = ${id};
        `)
        if(result[0].length===1){
           return result[0]
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}
const getprojectPostCommentById=async(id)=>{
    try {
        let result = await sequelize.query(`
        select pc.* from project_post_comments as pc where pc.id = ${id}
        `)
        if(!empty(result)){
            if(result[0].length ===1){
                return result
            }else{
                return false
            }
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}
const getAllProjectPostComments=async(postId,limit=0,userId=0,commentId=0)=>{
    try {
        let whereCondition=""
        if(commentId){
            whereCondition=" and pc.comment_id"
        }
        else{
            whereCondition=" and pc.comment_id is NULL"
        }
      let result = await sequelize.query(`
      SELECT pc.*,u.profile_pic, concat(u.first_name,' ',u.last_name) as username,coalesce(ppl.id,0) as liked,coalesce(pplc.count,0) as likes_count FROM project_post_comments pc
    LEFT JOIN users u ON pc.added_by = u.id
    LEFT JOIN project_post_comment_likes ppl ON (ppl.user_id = ${userId} AND ppl.comment_id = pc.id)
    LEFT JOIN (select count(id) as count, comment_id from project_post_comment_likes group by comment_id) pplc ON pplc.comment_id = pc.id
    WHERE pc.post_id = ${postId} AND pc.status != 2 ${whereCondition}
 
    ORDER BY pc.id DESC
    
      `)  
      if(result.length>0){
         const data = result[0].map(item=>item)
         return data
      }else{
        return false
      }
    } catch (error) {
        console.log(error)
    }
}

module.exports={
    addProjectPost,
    getAllProjectPostsByTask,
    ispostLiked,
    deleteProjectpostLike,
    addProjectPostLike,
    getprojectpostlikesCount,
    isPostStarred,
    deleteStarredPost,
    addStarredPost,
    getprojectPostByid,
    updateProjectPost,
    addPostCommnets,
    getprojectPostCommentscount,
    getAllUserReplace,
    getProjectPostCommentViewById,
    isPostCommentLiked,
    deleteProjectPostCommentLike,
    addProjectPostCommentLike,
    getprojectPostCommentById,
    updateprojectPostComments,
    getAllProjectPostComments,
}
