const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
const fs = require('fs');
const path = require('path');
require("dotenv").config();

module.exports = {
  getAll,
  getByTenderId,
  getByVendorId,
  create,
  update,
  delete: _delete,
  submit,
  uploadFiles,
};

async function getAll(params) {
  try {
    const filterConditions = {};
    
    if (params.tender_id) {
      filterConditions.tender_id = params.tender_id;
    }
    
    if (params.tendervendor_id) {
      filterConditions.tendervendor_id = params.tendervendor_id;
    }

    let quotations = await dbSAPPS.TenderQuotation.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.TenderVendor,
          as: "tendervendor",
          attributes: ["id", "vendor_name", "email"],
        },
        {
          model: dbSAPPS.Tender,
          as: "tender",
          attributes: ["id", "code", "display_name"],
        },
        {
          model: dbSAPPS.TenderItem,
          required: false,
          attributes: ["id", "display_name", "specification", "unit", "quantity"],
        },
      ],
    });

    quotations = quotations.map((quotation) => {
      const quotationData = quotation.dataValues;
      return {
        ...quotationData,
        tendervendor: quotation.tendervendor?.dataValues,
        tender: quotation.tender?.dataValues,
        tenderitem: quotation.TenderItem ? quotation.TenderItem.dataValues : null,
      };
    });

    return {
      quotations,
      success: true,
      message: "Quotations loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load quotations!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getByTenderId(tenderId) {
  try {
    const quotations = await dbSAPPS.TenderQuotation.findAll({
      where: { tender_id: tenderId },
      include: [
        {
          model: dbSAPPS.TenderVendor,
          as: "tendervendor",
          attributes: ["id", "vendor_name", "email"],
        },
        {
          model: dbSAPPS.TenderItem,
          required: false,
          attributes: ["id", "display_name", "specification", "unit", "quantity"],
        },
      ],
    });

    return {
      quotations: quotations.map((q) => q.dataValues),
      success: true,
      message: "Quotations loaded successfully!",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load quotations!",
    };
  }
}

async function getByVendorId(vendorId, tenderId) {
  try {
    const filterConditions = { tendervendor_id: vendorId };
    if (tenderId) {
      filterConditions.tender_id = tenderId;
    }

    const quotations = await dbSAPPS.TenderQuotation.findAll({
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.TenderItem,
          required: false,
          as: "tenderitem",
          attributes: ["id", "display_name", "specification", "unit", "quantity"],
        },
        {
          model: dbSAPPS.TenderQuotationFile,
          as: "files",
          required: false,
          attributes: ["id", "filename", "size", "type", "tenderquotation_id"],
        },
      ],
    });

    return {
      quotations: quotations.map((q) => {
        const quotationData = q.dataValues;
        return {
          ...quotationData,
          files: q.files ? q.files.map((file) => file.dataValues) : [],
        };
      }),
      success: true,
      message: "Quotations loaded successfully!",
    };
  } catch (error) {
    return {
      success: false,
      message: error.message || "Failed to load quotations!",
    };
  }
}

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Validate required fields
    if (!params.tender_id) {
      return {
        success: false,
        message: "Tender ID is required!",
      };
    }

    if (!params.tendervendor_id) {
      return {
        success: false,
        message: "Tender Vendor ID is required!",
      };
    }

    // // Check if vendor is invited to this tender
    // const invitation = await dbSAPPS.TenderInvitation.findOne({
    //   where: {
    //     tender_id: params.tender_id,
    //     tendervendor_id: params.tendervendor_id,
    //   },
    //   transaction,
    // });

    // if (!invitation) {
    //   return {
    //     success: false,
    //     message: "Vendor is not invited to this tender!",
    //   };
    // }

    // Validate quotations array
    if (!params.quotations || !Array.isArray(params.quotations) || params.quotations.length === 0) {
      return {
        success: false,
        message: "At least one quotation item is required!",
      };
    }

    // Get existing quotations to find their IDs for file deletion
    const existingQuotations = await dbSAPPS.TenderQuotation.findAll({
      where: {
        tender_id: params.tender_id,
        tendervendor_id: params.tendervendor_id,
      },
      attributes: ['id'],
      transaction,
    });

    // Delete existing quotation files first (to avoid foreign key constraint error)
    if (existingQuotations.length > 0) {
      const existingQuotationIds = existingQuotations.map(q => q.id);
      
      // Get existing files to delete from storage
      const existingFiles = await dbSAPPS.TenderQuotationFile.findAll({
        where: {
          tenderquotation_id: {
            [Op.in]: existingQuotationIds,
          },
        },
        transaction,
      });

      // Delete files from storage
      const fileDirectory = path.resolve(__dirname, "../../../../public/data/documents/tenderquotations");
      for (const file of existingFiles) {
        const filePath = path.join(fileDirectory, file.filename);
        if (fs.existsSync(filePath)) {
          try {
            fs.unlinkSync(filePath);
          } catch (error) {
            console.error(`Error deleting file ${file.filename}:`, error);
            // Continue even if file deletion fails
          }
        }
      }

      // Delete file records from database
      await dbSAPPS.TenderQuotationFile.destroy({
        where: {
          tenderquotation_id: {
            [Op.in]: existingQuotationIds,
          },
        },
        transaction,
      });
    }

    // Delete existing quotations for this vendor and tender
    await dbSAPPS.TenderQuotation.destroy({
      where: {
        tender_id: params.tender_id,
        tendervendor_id: params.tendervendor_id,
      },
      transaction,
    });

    // Create quotations
    const quotationsData = params.quotations.map((quotation) => ({
      tender_id: params.tender_id,
      tendervendor_id: params.tendervendor_id,
      item_id: quotation.item_id,
      unit: quotation.unit || null,
      quantity: quotation.quantity || null,
      price: quotation.price || null,
      delivery_time: quotation.delivery_time || null,
      note: quotation.note || null,
      submitted: false,
      submitted_at: null,
    }));

    const createdQuotations = await dbSAPPS.TenderQuotation.bulkCreate(quotationsData, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Quotations saved successfully!",
      quotations: createdQuotations.map(q => ({ id: q.id })),
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save quotations, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const quotation = await dbSAPPS.TenderQuotation.findByPk(params.id, { transaction });
    
    if (!quotation) {
      return {
        success: false,
        message: "Quotation not found!",
      };
    }

    // Update fields
    if (params.unit !== undefined) quotation.unit = params.unit;
    if (params.quantity !== undefined) quotation.quantity = params.quantity;
    if (params.price !== undefined) quotation.price = params.price;
    if (params.delivery_time !== undefined) quotation.delivery_time = params.delivery_time;
    if (params.note !== undefined) quotation.note = params.note;

    await quotation.save({ transaction });
    await transaction.commit();

    return {
      success: true,
      message: "Quotation updated successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to update quotation!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function submit(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    if (!params.tender_id || !params.tendervendor_id) {
      return {
        success: false,
        message: "Tender ID and Vendor ID are required!",
      };
    }

    // Get all quotations for this vendor and tender
    const quotations = await dbSAPPS.TenderQuotation.findAll({
      where: {
        tender_id: params.tender_id,
        tendervendor_id: params.tendervendor_id,
      },
      transaction,
    });

    if (quotations.length === 0) {
      return {
        success: false,
        message: "No quotations found to submit!",
      };
    }

    // Validate all quotations have required fields
    for (const quotation of quotations) {
      if (!quotation.price || quotation.price <= 0) {
        return {
          success: false,
          message: `Price is required for all items!`,
        };
      }
    }

    // Update all quotations to submitted
    const submittedAt = new Date();
    await dbSAPPS.TenderQuotation.update(
      {
        submitted: true,
        submitted_at: submittedAt,
      },
      {
        where: {
          tender_id: params.tender_id,
          tendervendor_id: params.tendervendor_id,
        },
        transaction,
      }
    );

    await transaction.commit();
    return {
      success: true,
      message: "Quotations submitted successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to submit quotations!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  try {
    const quotation = await dbSAPPS.TenderQuotation.findByPk(id);
    if (!quotation) {
      return {
        success: false,
        message: "Quotation not found!",
      };
    }

    // Get associated files before deleting quotation
    const associatedFiles = await dbSAPPS.TenderQuotationFile.findAll({
      where: { tenderquotation_id: id },
    });

    // Delete files from storage
    const fileDirectory = path.resolve(__dirname, "../../../../public/data/documents/tenderquotations");
    for (const file of associatedFiles) {
      const filePath = path.join(fileDirectory, file.filename);
      if (fs.existsSync(filePath)) {
        try {
          fs.unlinkSync(filePath);
        } catch (error) {
          console.error(`Error deleting file ${file.filename}:`, error);
          // Continue even if file deletion fails
        }
      }
    }

    await quotation.destroy();

    return {
      success: true,
      message: "Quotation deleted successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to delete quotation!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function uploadFiles(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    if (!params.tenderquotation_id) {
      return {
        success: false,
        message: "Tender Quotation ID is required!",
      };
    }

    if (!params.filesData || !Array.isArray(params.filesData) || params.filesData.length === 0) {
      return {
        success: false,
        message: "At least one file is required!",
      };
    }

    // Verify quotation exists
    const quotation = await dbSAPPS.TenderQuotation.findByPk(params.tenderquotation_id, { transaction });
    if (!quotation) {
      await transaction.rollback();
      return {
        success: false,
        message: "Quotation not found!",
      };
    }

    // Get existing files for this quotation
    const existingFiles = await dbSAPPS.TenderQuotationFile.findAll({
      where: { tenderquotation_id: params.tenderquotation_id },
      transaction,
    });

    // Delete existing files from storage before uploading new ones
    const fileDirectory = path.resolve(__dirname, "../../../../public/data/documents/tenderquotations");
    for (const file of existingFiles) {
      const filePath = path.join(fileDirectory, file.filename);
      if (fs.existsSync(filePath)) {
        try {
          fs.unlinkSync(filePath);
        } catch (error) {
          console.error(`Error deleting file ${file.filename}:`, error);
          // Continue even if file deletion fails
        }
      }
    }

    // Delete existing file records from database
    await dbSAPPS.TenderQuotationFile.destroy({
      where: { tenderquotation_id: params.tenderquotation_id },
      transaction,
    });

    // Create new file records
    const filesData = params.filesData.map((file) => ({
      tenderquotation_id: params.tenderquotation_id,
      filename: file.filename,
      size: file.size,
      type: file.type,
      creation: new Date(),
      modified: new Date(),
      modified_by: params.modified_by || 1,
      owner: params.owner || 1,
      enabled: true,
    }));

    await dbSAPPS.TenderQuotationFile.bulkCreate(filesData, { transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Files uploaded successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to upload files!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
