const { empty } = require("../Helpers/Common");
const sequelize = require("../config/dbconnection");
const Sequelize = require('sequelize')
const Clients = sequelize.define('partners', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    contact_name: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    last_name: { type: Sequelize.STRING, allowNull: true },
    email: { type: Sequelize.STRING, allowNull: false },
    phone: { type: Sequelize.STRING, allowNull: true },
    phone_country_code : {type : Sequelize.STRING,allowNull:false,defaultValue:1},
    // profile_pic:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    thumbnail:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    fax_number: { type: Sequelize.STRING, allowNull: true },
    address: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    city: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    state_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    zip_code: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    remit_to_address: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    remit_to_city: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    remit_to_state_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    remit_to_zip_code: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    remit_to_country: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    about_company: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    workflows: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    contact_tags: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    partner_type: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 1 },
    subject: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    email_content: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    rank_id: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    access_link_id: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    facebook_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    twitter_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    linkedin_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    vimeo_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    youtube_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    behance_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    dribble_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    instagram_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    pinterest_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    blog_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    website_url: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    internal_note: { type: Sequelize.STRING, allowNull: true },
    onboard_complete: { type: Sequelize.TINYINT, allowNull: false,defaultValue:0 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    user_id: { type: Sequelize.INTEGER, allowNull: true },
    isVendor: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
    quickbookCustomerName:{ type: Sequelize.STRING, allowNull: true },
    syncWithQuickBook: { type: Sequelize.INTEGER, allowNull: true },
    avalaraTaxType: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    avalaraCustomerId: { type: Sequelize.STRING, allowNull: true,defaultValue:null },
    partnerQbId: { type: Sequelize.INTEGER, allowNull: true,defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    card_order:{type: Sequelize.INTEGER, allowNull: false, defaultValue: 1},
    customerTypeId:{type: Sequelize.INTEGER, allowNull: true,defaultValue: null},
    sales_rep_names: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
}, { timestamps: false })

const ClientTaxExemptAttachments = sequelize.define('client_tax_exempt_attachments', {
  id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
  },
  client_id: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  extension: {
    type: Sequelize.STRING(10),
    allowNull: false,
  },
  file_type: {
    type: Sequelize.STRING(200),
    allowNull: false,
  },
  size: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  url: {
    type: Sequelize.TEXT,
    allowNull: false,
  },
  timestamp: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  status: {
    type: Sequelize.TINYINT,
    defaultValue: 1,
  },
}, {
  timestamps: false,
  tableName: 'client_tax_exempt_attachments',
});

const States = sequelize.define('states', {
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  name: { type: Sequelize.STRING(120), allowNull: false },
  short_name: { type: Sequelize.STRING(10), allowNull: false },
  added_by: { type: Sequelize.INTEGER, allowNull: false },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  status: { type: Sequelize.TINYINT(2), allowNull: false, defaultValue: 1 },
}, { timestamps: false });

const ClientAdditionalEmails = sequelize.define('client_additional_emails', {
  id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
  },
  client_id: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  contact_name: {
    type: Sequelize.STRING(255),
    allowNull: true,
  },
  email: {
    type: Sequelize.STRING(255),
    allowNull: false,
  },
  timestamp: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
  status: {
    type: Sequelize.TINYINT,
    defaultValue: 1,
  },
}, {
  timestamps: false,
  tableName: 'client_additional_emails',
});

const addClientAdditionalEmails = async (data) => {
  try {
    if (!Array.isArray(data) || data.length === 0) {
      return [];
    }
    const res = await ClientAdditionalEmails.bulkCreate(data, {
      validate: true,
      returning: true,
    });
    return res;
  } catch (error) {
    console.error('Error adding additional emails:', error.message || error);
    throw new Error('Failed to add additional emails');
  }
};

const getClientAdditionalEmailsByClientId = async (clientId) => {
  try {
    if (!clientId) {
      throw new Error('Client ID is required');
    }
    const emails = await ClientAdditionalEmails.findAll({
      where: {
        client_id: clientId,
        status: 1,
      },
      order: [['timestamp', 'DESC']],
    });
    return emails;
  } catch (error) {
    console.error('Error fetching client additional emails:', error);
    throw new Error('Server error: ' + error.message);
  }
};

const updateClientAdditionalEmail = async (emailId, updateFields) => {
  try {
    const updatedRows = await ClientAdditionalEmails.update(
      updateFields,
      { where: { id: emailId } }
    );
    return updatedRows[0] > 0;
  } catch (error) {
    console.error('Error updating additional email:', error.message || error);
    throw new Error('Failed to update additional email');
  }
};



const getAllClients = async (all = false, userType, userId,page,rowPerpage,search,isVendor=0,listView,orderBy='name',order='asc',From=null,teamId=null,requestClientId=null) => {
    let offset =''
    let whereCondition = ``;
    let OrderBy = ``;
    
    // Map frontend orderBy field names to database column names
    const orderByMap = {
        'name': 'p.name',
        'customer_type_name': 'ct.name',
        'status': 'p.status',
        'image': 'p.id' // Default to id for image column
    };
    
    const dbOrderBy = orderByMap[orderBy] || 'p.name';
    const sortOrder = (order == 'asc' || order == 'desc') ? (order?.toLowerCase() === 'desc' ? 'DESC' : 'ASC') : 'ASC';
    
    // For name field, use numeric sorting first, then alphabetical
    if (orderBy === 'name') {
        if (sortOrder === 'ASC') {
            OrderBy = `ORDER BY CAST(p.name AS UNSIGNED), p.name ASC`;
        } else {
            OrderBy = `ORDER BY CAST(p.name AS UNSIGNED) DESC, p.name DESC`;
        }
    } else {
        OrderBy = `ORDER BY ${dbOrderBy} ${sortOrder}`;
    }
    if(page && rowPerpage){
     offset = (page - 1) * rowPerpage;
     whereCondition= ` LIMIT ${rowPerpage} OFFSET ${offset}`
    }
    let totalCount=0


    let content = ''
    if (!empty(search)) {
        let normalizedSearch = search.trim().toLowerCase();
        content = `AND LOWER(REPLACE(REPLACE(TRIM(p.name), '(', ''), ')', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`;
    }
    
    // Filter by sales rep when From is "addSalesOrder"
    let salesRepFilter = '';
    if (From === 'addSalesOrder' && teamId && userType == 12) {
        salesRepFilter = `AND (
            FIND_IN_SET(${teamId}, p.sales_rep_names) > 0
            ${requestClientId ? ` OR p.user_id = ${requestClientId}` : ``}
        )`;
    }
      
    // if (all) {
    //     whereCondition = "p.status!=2";
    // } else {
    //     whereCondition = "p.status=1";

    // }
    if (userType != 1) {
    }
    let result = await sequelize.query(`SELECT p.*,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active,ur.name AS userRole,u.username,u.company_name,u.user_type,u.status,pt.name as partner_type_name,pt.bg_color,r.name as rank_name,r.rank_image,u.profile_pic,s.short_name as state,(coalesce(rr.rating,0)/coalesce(pr.no_of_reviews,1))  as rating,coalesce(pr.no_of_reviews,0) as no_of_reviews,coalesce(rp.no_of_requests,0) as no_of_requests, concat (ct.name,' ',ct.billing_type,'%') as customer_type_name,ct.billing_type,coalesce(prp.no_of_projects,0) as no_of_projects FROM partners p`
        + ` LEFT JOIN users u ON p.user_id=u.id `
        + ` LEFT JOIN partner_types pt ON p.partner_type=pt.id `
        + ` LEFT JOIN ranks r ON p.rank_id=r.id `
        + ` LEFT JOIN states s ON p.state_id=s.id `
        + ` LEFT JOIN customer_type ct ON p.customerTypeId=ct.id `
        + ` LEFT JOIN  user_roles ur ON u.user_type= ur.id `
        + ` LEFT JOIN (select count(rt.id) as no_of_requests,rt.partner_id from request_partners rt left join requests r on r.id=rt.request_id where rt.status=1 and rt.accept_status!=2 group by rt.partner_id ) rp ON p.id=rp.partner_id ` // and r.status!=0
        + ` LEFT JOIN (select count(prt.id) as no_of_projects,prt.partner_id from request_partners prt left join projects prj on prj.request_id=prt.request_id where prt.status=1 and prt.accept_status!=2 group by prt.partner_id ) prp ON p.id=prp.partner_id ` // and prj.status!=3
        + ` 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 p.status != 2 ${content} and p.isVendor=${isVendor} ${salesRepFilter} ${OrderBy}` 
        + `${whereCondition}`)

        totalCount = await sequelize.query(`SELECT COUNT(p.id) as totalCount FROM partners p WHERE p.status != 2 AND p.isVendor = ${isVendor} ${content} ${salesRepFilter}`);

    let data = [];
    return {data:result[0],totalCount:totalCount[0][0].totalCount};
    // return result[0];
}

const addClientTaxExemptAttachments = async (data) => {
    try {
        // Ensure data is an array and not empty
        if (!Array.isArray(data) || data.length === 0) {
            throw new Error('No data provided for bulk creation');
        }

        // Perform bulk create
        let res = await ClientTaxExemptAttachments.bulkCreate(data, {
            validate: true,  // This ensures validation before inserting
            returning: true, // If you want the inserted rows back (optional)
        });

        return res; // Return the inserted rows
    } catch (error) {
        // Log the error
        console.error('Error:', error.message || error);
        return { error: error.message || 'An unknown error occurred' }; // Return a structured error
    }
};
  
const getClientTaxExemptAttachmentsByClientId = async (clientId) => {
    try {
        // Validate clientId input
        if (!clientId) {
            throw new Error('Client ID is required');
        }

        // Perform a join between 'client_tax_exempt_attachments' and 'partners' (table name is 'partners')
        const attachments = await sequelize.query(`
        SELECT 
          ca.id AS attachment_id, 
          ca.client_id, 
          ca.extension, 
          ca.file_type, 
          ca.size, 
          ca.url, 
          ca.timestamp, 
          ca.status, 
          p.name AS client_name, 
          p.email AS client_email
        FROM client_tax_exempt_attachments ca
        LEFT JOIN partners p ON ca.client_id = p.id  -- Use 'partners' table here
        WHERE ca.client_id = :clientId AND ca.status != 0
        ORDER BY ca.timestamp DESC
      `, {
            replacements: { clientId },  // Prevent SQL injection by using replacements
            type: sequelize.QueryTypes.SELECT  // Specify the query type as SELECT
        });

        // If no attachments found, return an empty array
        if (attachments.length === 0) {
            return [];
        }

        // Return the attachments for the given clientId
        return attachments;
    } catch (error) {
        console.error('Error fetching client attachments:', error);
        throw new Error('Server error: ' + error.message);
    }
};

const updateClientTaxExemptAttachmentsByClientId = async (clientId, files) => {
    try {
        // Check if files are provided
        if (!Array.isArray(files) || files.length === 0) {
            throw new Error('No files to update');
        }

        // Loop through each file and update its status
        for (const file of files) {
            // Update the status of each attachment to 0 (or another status)
            await ClientTaxExemptAttachments.update(
                { status: 0 },  // Set status to 0 (or other status as needed)
                { where: { client_id: clientId, url: file.url } }  // Ensure you're targeting the correct file
            );
        }
        console.log(`Attachments status updated for client ${clientId}`);
    } catch (error) {
        console.error("Error updating attachment status:", error.message);
        throw new Error("Failed to update attachment status");
    }
};

const deleteClientTaxExemptAttachmentsByClientId = async (clientId) => {
    try {
        await ClientTaxExemptAttachments.destroy({
            where: { client_id: clientId }
        });
    } catch (error) {
        console.error("Error deleting client tax exempt attachments:", error.message);
        throw new Error("Failed to delete client tax exempt attachments");
    }
};

const getAllClientsForOrder = async (all = false, userType, userId,page,rowPerpage,search,isVendor=0) => {
    let offset =''
    let whereCondition = ``;
    if(page && rowPerpage){
     offset = (page - 1) * rowPerpage;
     whereCondition= ` LIMIT ${rowPerpage} OFFSET ${offset}`
    }
    let totalCount=0
    

    let content = ''
    if (!empty(search)) {
        let normalizedSearch = search.trim().toLowerCase();
        content = `AND LOWER(REPLACE(REPLACE(TRIM(p.name), '(', ''), ')', '')) LIKE '%${normalizedSearch.replace(/'/g, "''")}%'`;
    }
    
    // if (all) {
    //     whereCondition = "p.status!=2";
    // } else {
    //     whereCondition = "p.status=1";

    // }
    if (userType != 1) {
    }
    let result = await sequelize.query(`SELECT p.*,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active,u.username,u.company_name,u.user_type,u.status,pt.name as partner_type_name,pt.bg_color,r.name as rank_name,r.rank_image,u.profile_pic,s.short_name as state,(coalesce(rr.rating,0)/coalesce(pr.no_of_reviews,1))  as rating,coalesce(pr.no_of_reviews,0) as no_of_reviews,coalesce(rp.no_of_requests,0) as no_of_requests, concat (ct.name,' ',ct.billing_type,'%') as customer_type_name,ct.billing_type,coalesce(prp.no_of_projects,0) as no_of_projects FROM partners p`
        + ` LEFT JOIN users u ON p.user_id=u.id `
        + ` LEFT JOIN partner_types pt ON p.partner_type=pt.id `
        + ` LEFT JOIN ranks r ON p.rank_id=r.id `
        + ` LEFT JOIN states s ON p.state_id=s.id `
        + ` LEFT JOIN customer_type ct ON p.customerTypeId=ct.id `
        + ` LEFT JOIN (select count(rt.id) as no_of_requests,rt.partner_id from request_partners rt left join requests r on r.id=rt.request_id where rt.status=1 and rt.accept_status!=2 group by rt.partner_id ) rp ON p.id=rp.partner_id ` // and r.status!=0
        + ` LEFT JOIN (select count(prt.id) as no_of_projects,prt.partner_id from request_partners prt left join projects prj on prj.request_id=prt.request_id where prt.status=1 and prt.accept_status!=2 group by prt.partner_id ) prp ON p.id=prp.partner_id ` // and prj.status!=3
        + ` 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 p.status != 2 ${content} and p.isVendor=${isVendor} and p.status != 0 ORDER BY p.card_order ASC` 
        + `${whereCondition}`)

        totalCount = await sequelize.query(`SELECT COUNT(p.id) as totalCount FROM partners p WHERE p.status != 2 AND p.isVendor = ${isVendor} ${content}`);

    let data = [];
    return {data:result[0],totalCount:totalCount[0][0].totalCount};
    // return result[0];
}
const getAllClientsDataFilters = async (From=null,teamId=null,userType=null) => {
    try {
        let whereCondition = `p.status != 0 AND p.isVendor = 0`;
        let salesRepFilter = '';
        if (From === 'addSalesOrder' && teamId && userType !== 8 && userType !== 1) {
            salesRepFilter = `AND FIND_IN_SET(${teamId}, p.sales_rep_names) > 0`;
        }

        let query = await sequelize.query(`
            SELECT 
                p.*, 
                u.username, 
                u.company_name, 
                u.user_type, 
                u.status, 
                pt.name AS partner_type_name, 
                pt.bg_color, 
                r.name AS rank_name, 
                r.rank_image, 
                u.profile_pic, 
                s.short_name AS state, 
                (COALESCE(pr.rating, 0) / COALESCE(pr.no_of_reviews, 1)) AS rating, 
                COALESCE(pr.no_of_reviews, 0) AS no_of_reviews, 
                COALESCE(rp.no_of_requests, 0) AS no_of_requests, 
                CONCAT(ct.name, ' ', ct.billing_type, '%') AS customer_type_name, 
                ct.billing_type, 
                COALESCE(prp.no_of_projects, 0) AS no_of_projects,
                GROUP_CONCAT(DISTINCT p.id) AS client_ids, 
                GROUP_CONCAT(DISTINCT p.name) AS client_names
            FROM partners p
            LEFT JOIN users u ON p.user_id = u.id
            LEFT JOIN partner_types pt ON p.partner_type = pt.id
            LEFT JOIN ranks r ON p.rank_id = r.id
            LEFT JOIN states s ON p.state_id = s.id
            LEFT JOIN customer_type ct ON p.customerTypeId = ct.id
            LEFT JOIN (
                SELECT COUNT(rt.id) AS no_of_requests, rt.partner_id 
                FROM request_partners rt 
                WHERE rt.status = 1 AND rt.accept_status != 2 
                GROUP BY rt.partner_id
            ) rp ON p.id = rp.partner_id
            LEFT JOIN (
                SELECT COUNT(prt.id) AS no_of_projects, prt.partner_id 
                FROM request_partners prt 
                LEFT JOIN projects prj ON prj.request_id = prt.request_id 
                WHERE prt.status = 1 AND prt.accept_status != 2 
                GROUP BY prt.partner_id
            ) prp ON p.id = prp.partner_id
            LEFT JOIN (
                SELECT 
                    partner_id,
                    COUNT(id) AS no_of_reviews,
                    (SUM(quality_rating) + SUM(delivery_rating) + SUM(value_for_money_rating)) / 3 AS rating
                FROM partner_reviews 
                WHERE status = 1 
                GROUP BY partner_id
            ) pr ON p.id = pr.partner_id
            WHERE ${whereCondition} ${salesRepFilter}
            GROUP BY p.id 
            ORDER BY p.card_order ASC
        `);
        return query[0] || [];
    } catch (error) {
        console.log(error);
        throw new Error("Server error: " + error);
    }
};
// const getAllClientsDataFilters = async (From=null,teamId=null,userType=null) => {
//   try {
//       let whereCondition = `p.status != 0 AND p.isVendor = 0`;
//       let salesRepFilter = '';
//       if (From === 'addSalesOrder' && teamId && userType !== 8 && userType !== 1) {
//           salesRepFilter = `AND FIND_IN_SET(${teamId}, p.sales_rep_names) > 0`;
//       }

//       let query = await sequelize.query(`
//           SELECT 
//               p.*, 
//               u.username, 
//               u.company_name, 
//               u.user_type, 
//               u.status, 
//               pt.name AS partner_type_name, 
//               pt.bg_color, 
//               r.name AS rank_name, 
//               r.rank_image, 
//               u.profile_pic, 
//               s.short_name AS state, 
//               (COALESCE(rr.rating, 0) / COALESCE(pr.no_of_reviews, 1)) AS rating, 
//               COALESCE(pr.no_of_reviews, 0) AS no_of_reviews, 
//               COALESCE(rp.no_of_requests, 0) AS no_of_requests, 
//               CONCAT(ct.name, ' ', ct.billing_type, '%') AS customer_type_name, 
//               ct.billing_type, 
//               COALESCE(prp.no_of_projects, 0) AS no_of_projects,
//               GROUP_CONCAT(DISTINCT p.id) AS client_ids, 
//               GROUP_CONCAT(DISTINCT p.name) AS client_names
//           FROM partners p
//           LEFT JOIN users u ON p.user_id = u.id
//           LEFT JOIN partner_types pt ON p.partner_type = pt.id
//           LEFT JOIN ranks r ON p.rank_id = r.id
//           LEFT JOIN states s ON p.state_id = s.id
//           LEFT JOIN customer_type ct ON p.customerTypeId = ct.id
//           LEFT JOIN (
//               SELECT COUNT(rt.id) AS no_of_requests, rt.partner_id 
//               FROM request_partners rt 
//               LEFT JOIN requests r ON r.id = rt.request_id 
//               WHERE rt.status = 1 AND rt.accept_status != 2 
//               GROUP BY rt.partner_id
//           ) rp ON p.id = rp.partner_id
//           LEFT JOIN (
//               SELECT COUNT(prt.id) AS no_of_projects, prt.partner_id 
//               FROM request_partners prt 
//               LEFT JOIN projects prj ON prj.request_id = prt.request_id 
//               WHERE prt.status = 1 AND prt.accept_status != 2 
//               GROUP BY prt.partner_id
//           ) prp ON p.id = prp.partner_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 ${whereCondition} ${salesRepFilter}
//           GROUP BY p.id 
//           ORDER BY p.card_order 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 getAllWorkflows = async () => {
    let query = await sequelize.query(` select ow.* from onboard_workflows ow where ow.status=1 order by ow.id DESC`)
    return query[0];
}
const getAllPartnerTypes = async () => {
    let query = await sequelize.query(` select pt.* from partner_types pt where pt.status=1 order by pt.id DESC`)
    return query[0];
}
const getAllServices = async () => {

    let result = await sequelize.query(`SELECT s.*, company_name as authorized_by, coalesce(p.no_of_partners, 0) as no_of_partners FROM services s `
        + ` LEFT JOIN users u ON s.added_by=u.id `
        + ` LEFT JOIN (select count(ps.id) as no_of_partners,ps.service_id from partner_services ps left join partners p on p.id=ps.partner_id where ps.status=1 and p.status=1 group by ps.service_id) p ON s.id=p.service_id`
        + ` WHERE s.status = 1 ORDER BY s.id ASC`
    );

    let data = []
    if (result[0].length > 0) {
        data = result[0];
    }
    return data;
}
const getAllRanks = async () => {
    let result = await sequelize.query(
        `SELECT r.*, company_name as authorized_by,coalesce(p.no_of_partners,0) as no_of_partners  FROM ranks r`
        + ` LEFT JOIN users u ON r.added_by = u.id`
        + ` LEFT JOIN (SELECT count(id) as no_of_partners,rank_id from partners where status=1 group by rank_id) p on r.id=p.rank_id `
        + ` where r.status=1`
    )
    let data = []
    if (result[0].length > 0) {
        data = result[0];
    }
    return data;
}
const getAllStates = async () => {
    let result = await sequelize.query(`select s.*, company_name as authorized_by,coalesce(p.no_of_partners,0) as no_of_partners from states s `
        + ` left join users u on s.added_by=u.id`
        + ` left join (select count(id) as no_of_partners,state_id from partners where status=1 group by state_id) p on s.id=p.state_id`
        + ` where s.status=1 order by s.name ASC`)
    let data = []
    if (result[0].length > 0) {
        data = result[0];
    }
    return data;
}
const getAllAvailabilities = async () => {
    const query = `SELECT p.onboard_complete, COALESCE(COUNT(p.id), 0) AS no_of_partners FROM partners p
            WHERE p.status != 2   GROUP BY p.onboard_complete `;

    const data = {
        '0': {
            'id': '0',
            'name': 'Onboarding not Completed',
            'no_of_partners': 0
        },
        '1': {
            'id': '1',
            'name': 'Onboarding Completed',
            'no_of_partners': 0
        }
    };

    try {
        const [rows] = await sequelize.query(query);

        if (rows.length > 0) {
            rows.forEach(row => {
                data[row.onboard_complete].no_of_partners = row.no_of_partners;
            });
        }

        return data;
    } catch (error) {
        console.error(error);
        return data;
    }
}
const getPartnerByEmail = async (email) => {
    let query = await sequelize.query(`select email from partners where email="${email}"`)
    data = []
    if (query[0].length > 0) {
        return true
    }
    else {
        return false;
    }
}
const getPartnerByEmailData = async (email) => {
    let query = await sequelize.query(`select email from partners where email="${email}"`)
    data = []
    if (query[0].length > 0) {
        return query[0][0]
    }
    else {
        return false;
    }
}
const getPartnerByUserId = async (id) => {
    let query = await sequelize.query(` select p.*,u.first_name,u.last_name,pt.name as partner_type_name,s.name as state_name from partners p`
        + ` left join users u on u.id=p.user_id`
        + ` left join states s on s.id=p.state_id`
        + ` left join partner_types pt on pt.id=p.partner_type`
        + ` where user_id=${id}`)
    let result = []
    result = query[0]
    if (result.length === 1) {
        return result[0];
    } else {
        return false;
    }
}
const addClient = async (data) => {
    try {
        console.log("#########", data);
        let results = await Clients.create(data)
        if (results) {
            return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the Clients ", error);
        return false;
    }
}
const getClientById = async (id) => {
    let query = await sequelize.query(` select p.*,u2.first_name,u2.last_name,(case when p.onboard_complete=0 then 'N' else 'Y' end) as active,u2.username,u2.company_name,u2.user_type,u2.status,pt.name as partner_type_name,pt.description as partner_type_description,pt.bg_color,r.name as rank_name,r.rank_image,u2.profile_pic,s.short_name as state,s2.name as remit_to_state_name,(coalesce(rr.rating,0)/coalesce(pr.no_of_reviews,1))  as rating,rr.*,coalesce(pr.no_of_reviews,0) as no_of_reviews,coalesce(rp.no_of_requests,0) as no_of_requests, concat(u.first_name,' ',u.last_name) as authorized_by,
     u2.admin_password as pass,p.customerTypeId, ur.isClientUserRole, ct.billing_type, 
    (SELECT GROUP_CONCAT(CONCAT(sm.first_name, ' ', sm.last_name)) FROM staff_members sm WHERE FIND_IN_SET(sm.id, p.sales_rep_names)) AS sales_rep_full_names
    from partners p`
        + ` left join users u2 on p.user_id=u2.id `
        + ` left join user_roles ur on u2.user_type=ur.id `
        + ` left join users u on p.added_by=u.id`
        + ` left join partner_types pt on p.partner_type=pt.id`
        + ` left join customer_type ct on p.customerTypeId = ct.id`
        + ` left join ranks r on p.rank_id=r.id`
        + ` left join contact_tag t on p.contact_tags = t.id`
        + ` left join states s on p.state_id=s.id`
        + ` left join states s2 on p.remit_to_state_id=s2.id`
        + ` left join (select count(id) as no_of_requests,partner_id from request_partners where status=1 and accept_status!=2 group by partner_id ) rp on p.id=rp.partner_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,review_comment,quality_rating,request_id,skills,delivery_rating,value_for_money_rating,recommended from partner_reviews where status=1 group by partner_id ) rr on p.id=rr.partner_id`
        + ` where p.id=${id} and p.status!=2`)
        console.log("#########################hiiiiiiiii");
    if (query[0].length === 1) {
        const client = query[0][0];
        const additionalEmails = await getClientAdditionalEmailsByClientId(id);
        client.additionalEmails = additionalEmails;
        return client;
    } else {
        return false;
    }

}
const updateClient = async (data, id) => {
    try {
        console.log("$$$$$$$$$$$$$$$$$$$$data",data);
        console.log("$$$$$$$$$$$$$$$$$$$$data",id);
        const updatedRows = await Clients.update(data, {
            where: {
                id: id
            },
            returning: true,
        });
        console.log("$$$$$$$$$$$$$$$$$$$$Client",updatedRows);
        if (updatedRows) {
            if (updatedRows[1] > 0) {
                console.log("updatedRows -if-- ", updatedRows[1]);
                return true;
            }
            else {
                console.log("updatedRows -else-- ", updatedRows[1]);
                return false;
            }
        }
        console.log("---------updatedRows------------", updatedRows)
    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }
}
const updateClientProfileImg = async (data, id) => {
    try {
        console.log("$$$$$$$$$$$$$$$$$$$$",id);
        const updatedRows = await Clients.update(data, {
            where: {
                user_id: id
            },
            returning: true,
        });
        if (updatedRows) {
           return true
        }
    }
    catch (error) {
        console.error("Error updating project:", error);
        return false;
    }
}
const deleteClient = async (id) => {
    try {
        const deleted = await Clients.destroy({
            where: {
                id: id
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}
const getRequests = async (order, id, exclude) => {
    let whereCondition = ""
    if (exclude && id != 0) {
        whereCondition = ` g.id not in (select request_id from request_partners where status=1 and partner_id=${id})`
    }
    else if (!exclude && id != 0) {
        whereCondition = `g.id in (select request_id from request_partners where status=1 and partner_id=${id})`
    }
    let query
    if (id != 0) {
       
        query = await sequelize.query(`select g.*,coalesce(p.no_of_partners,0) as no_of_partners,prm.id as relationship_manager_id from requests g `
            + ` left join (select count(id) as no_of_partners,request_id from request_partners pg where status=1 group by request_id) p on g.id=p.request_id`
            +` left join partner_relationship_managers prm on g.id=prm.team_id and prm.partner_id=${id}`
            + ` where g.status=2 and ${whereCondition} `)
    }
    else {
        query = await sequelize.query(`select g.*,coalesce(p.no_of_partners,0) as no_of_partners from requests g `
            + ` left join (select count(id) as no_of_partners,request_id from request_partners pg where status=1 group by request_id) p on g.id=p.request_id`
            + ` where g.status=2 and ${whereCondition} `)
    }

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

}
const getInviteLinkToken=async(token)=>{
    let query=await sequelize.query(`select l.* from onboard_workflow_links l`
    +` where l.token="${token}" and l.status=1`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getAllSignAgrementsOfPartner=async(id)=>{
    let query=await sequelize.query(" select pd.*,d.name as agreement_name,d.content from partner_documents pd"
    +` left join documents d on d.id=pd.document_id`
    +` where pd.status=1 and pd.document_type=2 and pd.partner_id=${id} order by pd.id desc`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getAllSignAgrementsOfRfq=async(id)=>{
    let query=await sequelize.query(" select pd.*,d.name as agreement_name,d.content from rfq_documents pd"
    +` left join documents d on d.id=pd.document_id`
    +` where pd.status=1 and pd.document_type=2 and pd.request_id=${id} order by pd.id desc`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getAllFieldTemplatesPartner=async(id)=>{
    let query=await sequelize.query(` select  * from partner_stages where stage_type=1 and partner_id=${id}`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getQuestionnairTemplatesPartner=async(id)=>{
    let query=await sequelize.query(` select  * from partner_stages where stage_type=5 and partner_id=${id}`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getAllFieldTemplatesRfq=async(id)=>{
    let query=await sequelize.query(` select  * from rfq_stages where stage_type=1 and request_id=${id}`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getQuestionnairTemplatesRfq=async(id)=>{
    let query=await sequelize.query(` select  * from rfq_stages where stage_type=5 and request_id=${id}`)
    let data = []
    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
    
const getClientByUserId=async(userId)=>{
    let query=await sequelize.query(` select * from  partners where user_id=${userId}`)
    let data = []
    if (query[0].length > 0) {
        data = query[0][0];
    }
    return data;
}
const getAllClientpayments=async(id)=>{
    try {
      let result = await sequelize.query(`Select rp.*,r.name from request_payments as rp`
      +` left join request_partners p on p.request_id=rp.request_id`
      +` left join requests r on r.id=rp.request_id`
      +` where p.partner_id=${id}`)  
      if(!empty(result[0]) &&result[0].length>0){
         return result[0]
      }else{
        return []
      }
    } catch (error) {
        console.log(error,'error in fecthing client payments')
    }
}
const fetchClientIDFromUID=async(id)=>{
    let result = await sequelize.query(
        `SELECT id FROM partners WHERE user_id = :userId`,
        {
            replacements: { userId: id },
            type: sequelize.QueryTypes.SELECT
        }
    );
    return result[0].id
}

const updateCardOrderClientVendor = async (cards) => {
    if (!cards || !cards.length) {
      return false;
    }
    try {
      const updatePromises = cards.map((card, index) => {
        return Clients.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 findClientByUserId = async (userId) => {
    try {
      const client = await Clients.findOne({
        where: {
          user_id: userId
        }
      });
  
      if (!client) {
        return { message: "Client not found" };
      }
  
      return client;
    } catch (error) {
      console.error("Error finding client:", error);
      throw new Error("Database error");
    }
  };

  const findVendorByName = async(name)=>{
    try {
        const vendor = await Clients.findOne({
          where: {
            name: name,
            isVendor: 1,
            status: 1,
          },
        });
        return vendor.id;
      } catch (error) {
        console.error("Error finding vendor by name:", error);
        return null;
      }
  }

  const getPartnerByName = async (name) => {
    const result = await Clients.findOne({
      where: {
        name: name,
        status: 1,
      },
    });
    return result;
  };

const getAllStatesModel = async () => {
  const result = await sequelize.query(
    `SELECT * FROM states WHERE status = 1 ORDER BY name ASC`,
    { type: sequelize.QueryTypes.SELECT }
  );
  return result;
};

const getStateByName = async (name) => {
  const result = await States.findOne({
    where: {
      name: name,
      status: 1,
    },
  });
  return result;
};

const getPartnerTypeByName = async (name) => {
  const result = await sequelize.query(
    `SELECT * FROM partner_types WHERE name = :name AND status = 1`,
    {
      replacements: { name },
      type: sequelize.QueryTypes.SELECT,
    }
  );
  return result.length > 0 ? result[0] : null;
};

const getCustomerTypeByName = async (name) => {
  const result = await sequelize.query(
    `SELECT * FROM customer_type WHERE name = :name AND status = 1`,
    {
      replacements: { name },
      type: sequelize.QueryTypes.SELECT,
    }
  );
  return result.length > 0 ? result[0] : null;
};

const deleteClientTaxExemptAttachments = async (clientId) => {
    try {
      await ClientTaxExemptAttachments.destroy({
        where: {
          client_id: clientId,
        },
      });
      console.log(`Attachments for client ${clientId} deleted successfully`);
    } catch (error) {
      console.error("Error deleting attachments:", error.message);
      throw new Error("Failed to delete attachments");
    }
  };

const getSalesRepByName = async (fullName) => {
  const [firstName, ...rest] = fullName.split(' ');
  const lastName = rest.join(' ');

  const result = await sequelize.query(
    `SELECT id FROM staff_members WHERE first_name = :firstName AND last_name = :lastName AND status = 1`,
    {
      replacements: { firstName, lastName },
      type: sequelize.QueryTypes.SELECT,
    }
  );
  return result.length > 0 ? result[0].id : null;
};

  const getSalesRepByNameProject = async (fullName) => {
    const [firstName, ...rest] = fullName.split(' ');
    const lastName = rest.join(' ');
  
    const result = await sequelize.query(
      `SELECT id FROM staff_members WHERE first_name = :firstName AND last_name = :lastName AND status = 1`,
      {
        replacements: { firstName, lastName },
        type: sequelize.QueryTypes.SELECT,
      }
    );
    return result.length > 0 ? result[0].id : null;
  };

  const getAllPartnerQbIds = async (isVendor=0) => {
    const result = await sequelize.query(
      `SELECT partnerQbId FROM partners WHERE status = 1 AND syncWithQuickBook = 1 AND isVendor = :isVendor`,
      {
        replacements: { isVendor: isVendor ? 1 : 0 },
        type: sequelize.QueryTypes.SELECT
      }
    );
    return result;
  };

  const updateVendorByQbId = async (quickbookId,data) => {
    try {
      const result = await Clients.update(data, {
        where: {
          partnerQbId: quickbookId
        }
      });
    if(result){
      return true
    }
    else {
      return false
    }
  }
  catch (error) {
    console.log('updateVendorByQbId error', error);
    return false
  }
}

module.exports = {
    addClientTaxExemptAttachments,
    getClientTaxExemptAttachmentsByClientId,
    updateClientTaxExemptAttachmentsByClientId,
    deleteClientTaxExemptAttachmentsByClientId,
    deleteClientTaxExemptAttachments,
    getAllClients, getAllWorkflows, getAllPartnerTypes, getPartnerByEmail,
    getAllServices, getAllRanks, getAllStates, getAllAvailabilities, addClient, getClientById,
    updateClient, deleteClient, getRequests,getPartnerByUserId,getPartnerByEmailData,getInviteLinkToken,
    getAllSignAgrementsOfPartner,getAllFieldTemplatesPartner,getQuestionnairTemplatesPartner,updateClientProfileImg,
    getClientByUserId,getAllClientpayments,getAllFieldTemplatesRfq,getQuestionnairTemplatesRfq,
    getAllSignAgrementsOfRfq,getAllClientsForOrder,
    fetchClientIDFromUID,updateCardOrderClientVendor,findClientByUserId,findVendorByName,getAllClientsDataFilters,getPartnerByName,
    getAllStatesModel,
    getStateByName,
    getPartnerTypeByName,
    getCustomerTypeByName,
    addClientAdditionalEmails,
    getClientAdditionalEmailsByClientId,
    updateClientAdditionalEmail,
    getSalesRepByName,
    getSalesRepByNameProject,
    getAllPartnerQbIds,
    updateVendorByQbId,
}