const sequelize = require("../config/dbconnection")
const { INTEGER, STRING, TEXT, FLOAT, TINYINT, CHAR, Sequelize } = require("sequelize");
const requestProposals = sequelize.define('rfq_proposals', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    request_id:{type:Sequelize.INTEGER,allowNull:false},
    vendor_id:{type:Sequelize.INTEGER,allowNull:true},
    title:{type:Sequelize.STRING,allowNull:true,},
    description:{type:Sequelize.STRING,allowNull:false,},
    attachment_url:{type:Sequelize.TEXT,allowNull:true,},
    attachment_type:{type:Sequelize.TEXT,allowNull:true,},
    budget:{type:Sequelize.FLOAT,allowNull:false,},
    tax:{type:Sequelize.FLOAT,allowNull:true,},
    avalara_transaction_id: { type: TEXT, allowNull: true, defaultValue: null },
    avalara_code: { type: TEXT, allowNull: true, defaultValue: null },
    added_by: {type:Sequelize.INTEGER,allowNull:false,},
     timestamp: {type:Sequelize.INTEGER,allowNull:false,},
    status:{type:Sequelize.INTEGER,allowNull:false,defaultValue:1},
    paymentMode	:{type:Sequelize.STRING,allowNull:true}
   
},{timestamps:false})
const CustomerOrder = sequelize.define('vendor_proposals',{
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    customer_id:{type:INTEGER,allowNull:true,defaultValue:null},
    customer_name:{type:STRING,allowNull:true},
    customer_phone:{type:INTEGER,allowNull:true,defaultValue:'9345534'},
    customer_address:{type:TEXT,allowNull:true},
    tax:{type:FLOAT,allowNull:false},
    tax_category:{type:INTEGER,allowNull:true, defaultValue:null},
    tax_amount:{type:FLOAT(12,2),allowNull:false, defaultValue:0.00},
    sub_total:{type:FLOAT,allowNull:true},
    total:{type:FLOAT(14,2),allowNull:true,defaultValue:null},
    tariff:{type:FLOAT(14,2),allowNull:true,defaultValue:null},
    shipping_cost:{type:FLOAT(14,2),allowNull:true,defaultValue:null},
    budget:{type:FLOAT(12,2),allowNull:true,defaultValue:null},
    request_id:{type:INTEGER,allowNull:false},
    extra_discount:{type:FLOAT,allowNull:true},
    timestamp:{type:INTEGER,allowNull:false,},
    status:{type:TINYINT,allowNull:false},
},{timestamps: false})
const CustomerOrderProduct = sequelize.define('vendor_order_products',{
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    order_id:{type:INTEGER,allowNull:true,defaultValue:null},
    product_name:{type:STRING,allowNull:true,defaultValue:null},
    material:{type:INTEGER,allowNull:true,defaultValue:0},
    product_price:{type:FLOAT,allowNull:true,defaultValue:null},
    product_retail_price:{type:FLOAT,allowNull:true,defaultValue:null},
    product_markup:{type:FLOAT,allowNull:true,defaultValue:null},
    product_qty:{type:INTEGER,allowNull:true,defaultValue:1},
    location_quantity:{type:INTEGER,allowNull:true,defaultValue:1},
    product_id:{type:INTEGER,allowNull:true,defaultValue:null},
    workflow:{type:INTEGER,allowNull:true,defaultValue:null},
    product_sub_total:{type:FLOAT,allowNull:true,defaultValue:null},
    product_tax:{type:FLOAT,allowNull:true,defaultValue:null},
    product_total:{type:FLOAT,allowNull:true,defaultValue:null},
    discount:{type:FLOAT,allowNull:true,defaultValue:null},
    timestamp:{type:INTEGER,allowNull:true,defaultValue:null},
    status:{type:INTEGER,allowNull:true,defaultValue:1},
},{timestamps:false})
const RequestServices = sequelize.define('vendor_services', {
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    request_id: { type: INTEGER, allowNull: false },
    labour_cost_service_id: { type: INTEGER, allowNull: false, },
    recipe_id: { type: INTEGER, allowNull: true, defaultValue: null },
    product_id: { type: INTEGER, allowNull: true, defaultValue: null },
    qty: { type: FLOAT(12, 2), allowNull: false },
    markup: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
    unit_cost: { type: FLOAT(12, 2), allowNull: false },
    total_cost: { type: FLOAT(12, 2), allowNull: false },
    job_cost:{type: INTEGER, allowNull: true, defaultValue:0},
    taken:{type: TINYINT, allowNull: false, defaultValue:0}
  }, { timestamps: false, tableName: 'vendor_services' })
const addProposal=async(data)=>{
    try {
        let results = await requestProposals.create(data)
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error,'Error in adding request update ')
    }
}
const updateProposal=async(id,data)=>{
    try {
        let results = await requestProposals.update(data, {
            where: {
                id: id
            },
            returning: true,
        });
        if (results) {
           return true
        }
    }
    catch (error) {
        console.error("Error updating proposal:", error);
    }
}
const deleteProposal=async(id)=>{
    try {
        const deleted = await requestProposals.destroy({
            where: {
                id: id
            },
        });
        if (deleted) {
            return true
        }
    }
    catch (error) {
        console.error("Error deleting proposal:", error);
    }
}
const getProposalById=async(id)=>{
    let results = await sequelize.query(`select * from rfq_proposals where id=${id}`)
    if (results[0].length === 1) {
        return results[0]
    }
    else {
        return false
    }
}
const getAllPropsalsByRequestId=async(requestId,userId,userType)=>{
    let results=""
    if(userType!=1){
     results = await sequelize.query(`select r.*,CONCAT(u.first_name,' ',u.last_name) AS vendor_name,c.email,c.phone,u.profile_pic from rfq_proposals r
         left join users u on r.vendor_id=u.id 
         left join partners c on r.vendor_id=c.user_id
          where r.request_id=${requestId} and r.added_by=${userId} and r.status!=0`)
    }
   else{
    results = await sequelize.query(`select r.*,CONCAT(u.first_name,' ',u.last_name) AS vendor_name,c.email,c.phone,u.profile_pic,c.id as partnerId from rfq_proposals r 
        left join users u on r.vendor_id=u.id
         left join partners c on r.vendor_id=c.user_id where r.request_id=${requestId} and r.status!=0`)
   }
   
    if (results) {
        return results[0]
    }
    else {
        return false
    }
}

const updateProposalStatus = async (id, data,request_id) => {
    console.log('data+++++++++++++++++', id, data,request_id)

    try {
        const dataaaaaaa= await requestProposals.update(data, {
           
            where: {
                id: id,
                request_id:request_id,
                status: 1
            }
        });
        console.log('data+++++++++++++++++', dataaaaaaa)
        await requestProposals.update({ status: 3 }, {
            where: {
                request_id: request_id, 
                status: 1,
                id: { [Sequelize.Op.ne]: id }
            }
        });
        return true;
    } catch (error) {
        console.error("Error updating proposal status:", error);
        return false;
    }
}
const updateProposalStatusPayment = async (id, data,request_id) => {
    console.log('data+++++++++++++++++', id, data,request_id)

    try {
        const dataaaaaaa= await requestProposals.update(data, {
           
            where: {
                id: id,
                request_id:request_id,
                status: 2
            }
        });
        console.log('data+++++++++++++++++', dataaaaaaa)
        await requestProposals.update({ status: 3 }, {
            where: {
                request_id: request_id, 
                status: 1,
                id: { [Sequelize.Op.ne]: id }
            }
        });
        return true;
    } catch (error) {
        console.error("Error updating proposal status:", error);
        return false;
    }
}
const createCustomerOrder=async(data)=>{
    console.log("cccfffffffff", data);
    let res = await CustomerOrder.create(data)
    return res.id;
}


const createCustomerOrderProduct=async(data)=>{
    let res = await CustomerOrderProduct.create(data)
    return res.id;
}
const addRequestServices = async (data) => {
    try {
      const result = RequestServices.create(data)
      if (result) {
        return true
      } else {
        return false
      }
    } catch (error) {
      console.log(error, 'error in addRequestServices')
      return false
    }
  }
  const updateCustomerOrder=async(order_id,data)=>{
    console.log("############Updated",data);
    let result = await CustomerOrder.update(data,{
     where:{
       id:order_id
     }
    }) 
    console.log("############Updated",result);
    return true;
    // if (result[0] > 0) {
    //     return result.id;
    //   } else {
    //     return false;
    //   }
}
const deletedCustomerOrderProduct = async (order_id) => {
    try {
      const result = await CustomerOrderProduct.destroy({
        where: {
          order_id: order_id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };
  const deleteRequestServices= async (order_id) => {
    try {
      const result = await RequestServices.destroy({
        where: {
            request_id: order_id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };


//   const findProposalById = async (id) => {
//     try {
//         let query = `
//         SELECT 
//             rp.id, 
//             rp.request_id, 
//             rp.title, 
//             rp.description, 
//             rp.budget,
//             p.labour_cost_services,
//             pt.quickbookCustomerName AS vendorQbName, 
//             pt.syncWithQuickBook AS vendorSyncWithQb,
//             pop.product_name,
//             pop.product_price,
//             pop.product_qty,
//             pop.location_quantity,
//             ps.qty,ps.unit_cost,
//             lcs.name AS labour_cost_service_name
//         FROM rfq_proposals rp
//         LEFT JOIN proposals p ON rp.request_id = p.id
//          LEFT JOIN vendor_order_products pop ON rp.id = pop.order_id
//         LEFT JOIN  vendor_services ps ON rp.id = ps.request_id
//         LEFT JOIN partners pt ON rp.vendor_id = pt.id
//         LEFT JOIN labour_cost_services lcs ON ps.labour_cost_service_id = lcs.id
//         WHERE rp.id = :id
//     `;
//         let result = await sequelize.query(query, {
//             replacements: { id: id },
//             type: sequelize.QueryTypes.SELECT
//         });

//         if (result.length > 0) {
//             return result
//         } else {
//             return null;
//         }
//     } catch (error) {
//         console.log(error, 'Error in finding proposal by ID');
//     }
// }



const findProposalById = async (id) => {
    try {
        let query = `
        SELECT 
            rp.id, 
            rp.request_id, 
            rp.title, 
            rp.description, 
            rp.budget,
            rp.vendor_id,
            p.labour_cost_services,
            pt.name,pt.partnerQbId,
            pt.quickbookCustomerName AS vendorQbName,pt.address,pt.zip_code,pt.city,st.short_name AS state, 
            pt.syncWithQuickBook AS vendorSyncWithQb,
            pop.product_name,
            pop.product_price,
            pop.product_qty,
            pop.location_quantity,
            ps.qty,
            ps.unit_cost,
            lcs.name AS labour_cost_service_name,
            lcs.syncWithQuickbooks AS service_syncWithQuickbooks,
            lcs.quickbookId AS service_quickbookId,lcs.taxCode,
            pr.syncWithQuickbooks,
            pr.quickbookId,pr.taxCode,pr.meterial AS isMaterial,
            vpr.tax_amount
        FROM rfq_proposals rp
        LEFT JOIN proposals p ON rp.request_id = p.id
        left join proposal_orders c_ord on rp.request_id = c_ord.request_id
        LEFT JOIN vendor_order_products pop ON rp.id = pop.order_id
        LEFT JOIN vendor_services ps ON rp.id = ps.request_id
        LEFT JOIN partners pt ON rp.vendor_id = pt.user_id
        LEFT JOIN states st ON pt.state_id = st.id
        LEFT JOIN labour_cost_services lcs ON ps.labour_cost_service_id = lcs.id
        LEFT JOIN products pr ON pop.product_id = pr.id
        LEFT JOIN vendor_proposals vpr ON vpr.request_id = rp.id
        WHERE rp.id = :id
    `;

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

        if (result.length > 0) {
            const structuredData = {
                id: result[0].id,
                request_id: result[0].request_id,
                title: result[0].title,
                description: result[0].description,
                budget: result[0].budget,
                labour_cost_services: result[0].labour_cost_services,
                name:result[0].name,
                vendorQbName: result[0].vendorQbName,
                partnerQbId: result[0].partnerQbId,
                tax_amount: result[0].tax_amount,
                vendorSyncWithQb: result[0].vendorSyncWithQb,
                vendorAddress: result[0].address,
                vendor_zip_code: result[0].zip_code,
                vendorCity: result[0].city,
                vendorState: result[0].state,
                rfpProducts: [],
                rfpServices: []
            };

            result.forEach(row => {
                // Add product data to rfpProducts array
                if(row.product_name){
                    const product = {
                        product_name: row.product_name,
                        product_price: row.product_price,
                        product_qty: row.product_qty,
                        location_quantity: row.location_quantity,
                        syncWithQuickbooks:row.syncWithQuickbooks,
                        quickbookId:row.quickbookId,
                        taxCode:row.taxCode,
                        isMaterial:row.isMaterial
                    };

                    if (!structuredData.rfpProducts.some(p => p.product_name === product.product_name)) {
                        structuredData.rfpProducts.push(product);
                    }
                }
                // Add service data to rfpServices array
                if(row.labour_cost_service_name){
                    const service = {
                        qty: row.qty,
                        unit_cost: row.unit_cost,
                        labour_cost_service_name: row.labour_cost_service_name,
                        syncWithQuickbooks:row.service_syncWithQuickbooks,
                        quickbookId:row.service_quickbookId,
                        taxCode:row.taxCode,
                    };

                    if (!structuredData.rfpServices.some(s => s.labour_cost_service_name === service.labour_cost_service_name)) {
                        structuredData.rfpServices.push(service);
                    }
                }
            });

            return structuredData;
        } else {
            return null;
        }
    } catch (error) {
        console.log(error, 'Error in finding proposal by ID');
    }
};





module.exports={addProposal,updateProposal,deleteProposal,getProposalById,getAllPropsalsByRequestId,updateProposalStatus,updateProposalStatusPayment,
    createCustomerOrder,createCustomerOrderProduct,addRequestServices,updateCustomerOrder,deletedCustomerOrderProduct,
    deleteRequestServices,
    findProposalById
}