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

module.exports = {
  getAll,
  show,
  option,
  count,
  award,
  create,
  update,
  enabled,
  delete: _delete,
};

const formatWithModifier = async (tenders) => {
  return Promise.all(
    tenders.map(async (tender) => {
      const modifier = await dbSAPPS.User.findOne({
        where: {
          id: tender.modified_by,
        },
        attributes: ["full_name"],
      });

      return {
        ...tender,
        modifier: modifier?.full_name,
      };
    }),
  );
};

async function award(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const tender = await dbSAPPS.Tender.findByPk(params.id);
    if (!tender) {
      await transaction.rollback();
      return {
        success: false,
        message: "Tender not found!",
      };
    }
    const quotations = await dbSAPPS.TenderQuotation.findAll({
      where: {
        tendervendor_id: params.tenderVendorId,
      },
    });

    if (quotations.length === 0) {
      await transaction.rollback();
      return {
        success: false,
        message: "Quotation not found!",
      };
    }

    // update quotation
    quotations.forEach(async (quotation) => {
      quotation.is_winner = true;
      quotation.winner_at = new Date();
      await quotation.update({ is_winner: true, winner_at: new Date() }, { transaction });
    });
    
    // update tender
    tender.status = "tertutup";
    await tender.save({ transaction });

    await transaction.commit();
    return {
      success: true,
      message: "Tender has been awarded successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    console.error("Error awarding tender: ", error);
    return {
      success: false,
      message: "Failed to award tender!",
    };
  }
}

async function getAll(params) {
  try {
    // filter
    const filterConditions = {};
    let tenders = [];
    tenders = await dbSAPPS.Tender.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.TenderCategory,
          as: "tendercategory",
          attributes: ["id", "display_name", "code"],
        },
        {
          model: dbSAPPS.TenderItem,
          as: "items",
          attributes: ["id", "display_name", "quantity", "specification", "unit", "tender_id"],
        },
        {
          model: dbSAPPS.TenderFile,
          as: "files",
          attributes: ["id", "filename", "type", "size", "tender_id"],
        },
        {
          model: dbSAPPS.TenderInvitation,
          as: "invitations",
          attributes: ["id", "tendervendor_id", "tender_id"],
        },
        {
          model: dbSAPPS.TenderPaymentTerm,
          as: "tenderpaymentterm",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.TenderQuotation,
          as: "quotations",
          attributes: [
            "id",
            "item_id",
            "unit",
            "quantity",
            "price",
            "delivery_time",
            "note",
            "submitted",
            "submitted_at",
            "tender_id",
            "tendervendor_id",
            "is_winner",
            "winner_at",
          ],
          include: [
            {
              model: dbSAPPS.TenderVendor,
              as: "tendervendor",
              attributes: ["id", "vendor_name", "owner"],
            },
            {
              model: dbSAPPS.TenderItem,
              as: "tenderitem",
              attributes: ["id", "display_name"],
            },
          ],
        },
      ],
    });

    tenders = tenders.map((tender) => {
      return {
        ...tender.dataValues,
        tendercategory: tender.tendercategory?.dataValues,
        items: tender.items?.map((item) => item.dataValues),
        files: tender.files?.map((file) => file.dataValues),
        quotations: tender.quotations?.map((quotation) => ({
          ...quotation.dataValues,
          tendervendor: quotation.tendervendor?.dataValues,
        })),
      };
    });

    tenders = await formatWithModifier(tenders);

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

async function show(params) {
  try {
    // filter
    const filterConditions = {};
    let tenders = [];
    tenders = await dbSAPPS.Tender.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.TenderCategory,
          as: "tendercategory",
          attributes: ["id", "display_name", "code"],
        },
        {
          model: dbSAPPS.TenderItem,
          as: "items",
          attributes: ["id", "display_name", "quantity", "specification", "unit", "tender_id"],
        },
        {
          model: dbSAPPS.TenderFile,
          as: "files",
          attributes: ["id", "filename", "type", "size", "tender_id"],
        },
        {
          model: dbSAPPS.TenderInvitation,
          as: "invitations",
          attributes: ["id", "tendervendor_id", "tender_id"],
        },
        {
          model: dbSAPPS.TenderQuotation,
          as: "quotations",
          attributes: [
            "id",
            "item_id",
            "unit",
            "quantity",
            "price",
            "delivery_time",
            "note",
            "submitted",
            "submitted_at",
            "tender_id",
            "tendervendor_id",
          ],
          include: [
            {
              model: dbSAPPS.TenderVendor,
              as: "tendervendor",
              attributes: ["id", "vendor_name"],
            },
            {
              model: dbSAPPS.TenderItem,
              as: "tenderitem",
              attributes: ["id", "display_name"],
            },
          ],
        },
      ],
    });

    tenders = tenders.map((tender) => {
      return {
        ...tender.dataValues,
        tendercategory: tender.tendercategory?.dataValues,
        category: tender.tendercategory?.display_name,
        items: tender.items?.map((item) => item.dataValues),
        files: tender.files?.map((file) => file.dataValues),
        quotations: tender.quotations?.map((quotation) => ({
          ...quotation.dataValues,
          tendervendor: quotation.tendervendor?.dataValues,
        })),
      };
    });

    tenders = await formatWithModifier(tenders);

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

async function option(params) {
  try {
    const tenders = await dbSAPPS.Tender.findAll({
      attributes: ["id", "display_name", "code"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
    });

    return {
      success: true,
      tenders,
      message: "Tender loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving Tender: ", error);
    const errorMessage = error.message || "Failed to retrieve Tender. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function count(params) {
  try {
    const count = await dbSAPPS.Tender.count({
      where: { enabled: true },
    });

    return {
      success: true,
      count,
      message: "Tender count loaded successfully!",
    };
  } catch (error) {
    console.error("Error counting Tender: ", error);
    return {
      success: false,
      message: "Failed to retrieve tenders count. Please try again later.",
    };
  }
}

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const existingDisplayName = await dbSAPPS.Tender.findOne({
      where: { display_name: params.display_name },
      transaction,
    });
    if (existingDisplayName) {
      return {
        success: false,
        message: `"${params.display_name}" is already taken.`,
      };
    }

    // buat kode
    const code = await generateCode();
    params.code = code;

    //  Jika tidak ada nilai untuk beberapa field, set default value
    if (!params.creation) params.creation = Date.now();
    if (!params.modified) params.modified = Date.now();
    if (!params.modified_by) params.modified_by = 1; // Default jika tidak ada nilai
    if (!params.enabled) params.enabled = true;

    // save
    const tender = await dbSAPPS.Tender.create(params, { transaction });

    if (params.items) {
      // Pastikan `momparticipants` adalah array yang valid
      if (typeof params.items === "string") {
        try {
          params.items = JSON.parse(params.items);
        } catch (err) {
          return { success: false, message: "Invalid JSON format for items" };
        }
      }

      if (Array.isArray(params.items) && params?.items?.length > 0) {
        const items = params.items.map((item) => ({
          ...item,
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by || 1,
          owner: params.owner || 1,
          tender_id: tender.id, // Assign ID dari MOM yang baru saja dibuat
        }));

        // Insert data anak menggunakan bulkCreate
        await dbSAPPS.TenderItem.bulkCreate(items, { transaction });
      }
    }

    if (params.filesData) {
      // Persiapkan data anak untuk disimpan
      const files = params.filesData.map((file) => ({
        ...file,
        creation: Date.now(),
        modified: Date.now(),
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        tender_id: tender.id, // Assign ID dari MOM yang baru saja dibuat
      }));

      // Insert data anak menggunakan bulkCreate
      await dbSAPPS.TenderFile.bulkCreate(files, { transaction });
    }

    await transaction.commit();
    return {
      success: true,
      message: "Saved successfully!",
    };
  } catch (error) {
    await transaction.rollback();
    const errorMessage = error?.message || "Failed to save, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // 1. Periksa apakah Tender yang akan diupdate ada
    const existingTender = await dbSAPPS.Tender.findByPk(params.id, { transaction });
    if (!existingTender) {
      await transaction.rollback();
      return { success: false, message: "Tender not found." };
    }

    params.modified = Date.now();
    if (!params.modified_by) params.modified_by = 1; // Default jika tidak ada nilai

    // 5. Update data tender utama
    await existingTender.update(params, { transaction });

    // Cek dan proses items
    if (params.items) {
      // Pastikan `items` adalah array yang valid
      if (typeof params.items === "string") {
        try {
          params.items = JSON.parse(params.items);
        } catch (err) {
          await transaction.rollback();
          return { success: false, message: "Invalid JSON format for items." };
        }
      }

      if (!Array.isArray(params.items) || params.items.length < 1) {
        await transaction.rollback();
        return { success: false, message: "Items cannot be empty." };
      }

      // Hapus item yang sudah ada untuk tender ini
      await dbSAPPS.TenderItem.destroy({
        where: { tender_id: existingTender.id },
        transaction,
      });

      // Persiapkan data item baru untuk disimpan
      const items = params.items.map((item) => ({
        ...item,
        creation: Date.now(),
        modified: Date.now(),
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        tender_id: existingTender.id, // Assign ID dari MOM yang diupdate
      }));

      // Insert data item baru menggunakan bulkCreate
      await dbSAPPS.TenderItem.bulkCreate(items, { transaction });
    }

    // Cek dan proses filesData
    let existingFiles = [];
    if (params.existingFiles) {
      try {
        existingFiles =
          typeof params.existingFiles === "string" ? JSON.parse(params.existingFiles) : params.existingFiles;
      } catch (err) {
        await transaction.rollback();
        return { success: false, message: "Invalid JSON format for existingFiles." };
      }
    }

    const allFiles = [...existingFiles, ...(params.filesData || [])];

    // --- Ambil file lama dari database ---
    const oldFiles = await dbSAPPS.TenderFile.findAll({
      where: { tender_id: existingTender.id },
      transaction,
    });

    // --- Tentukan file mana yang DIHAPUS oleh user ---
    const filesToDelete = oldFiles.filter((old) => !existingFiles.some((f) => f.filename === old.filename));

    // --- Hapus file yang dihapus user dari storage ---
    for (const file of filesToDelete) {
      const filePath = path.resolve(__dirname, "../../../../public/data/documents/tenders", file.filename);

      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
      }
    }

    // --- Hapus semua record lama ---
    await dbSAPPS.TenderFile.destroy({
      where: { tender_id: existingTender.id },
      transaction,
    });

    // --- Insert file baru jika ada ---
    if (allFiles.length > 0) {
      const files = allFiles.map((file) => ({
        ...file,
        creation: Date.now(),
        modified: Date.now(),
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        tender_id: existingTender.id,
      }));

      await dbSAPPS.TenderFile.bulkCreate(files, { transaction });
    }

    // Commit transaksi jika semua berhasil
    await transaction.commit();
    return {
      success: true,
      message: "Tender has been updated successfully!",
    };
  } catch (error) {
    // Rollback transaksi jika ada error
    await transaction.rollback();
    const errorMessage = error.message || "An error occurred during updating tender.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function generateCode() {
  const currentYear = new Date().getFullYear();
  const prefix = `TND-${currentYear}-`;

  const lastTender = await dbSAPPS.Tender.findOne({
    where: {
      code: {
        [Op.like]: `${prefix}%`,
      },
    },
    order: [["code", "DESC"]],
  });

  if (!lastTender) {
    return `${prefix}0001`;
  }

  const lastCodeNumber = parseInt(lastTender.code.split("-")[2]);
  const newCodeNumber = lastCodeNumber + 1;
  return `${prefix}${newCodeNumber.toString().padStart(4, "0")}`;
}

async function enabled(id, params) {
  try {
    const tender = await dbSAPPS.Tender.findByPk(id);
    if (!tender)
      return {
        success: false,
        message: "Tender Category not found!",
      };

    const updateData = {};
    updateData.enabled = params.enabled;
    if (params.modified_by) updateData.modified_by = params.modified_by;
    if (params.modified) updateData.modified = params.modified;

    await tender.update(updateData);

    return {
      success: true,
      message: "Tender Category has been updated!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to update tender category.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();

  try {
    const tender = await dbSAPPS.Tender.findByPk(id, { transaction });
    if (!tender) {
      await transaction.rollback();
      return { success: false, message: "Tender not found!" };
    }

    // Ambil quotation + files SEKALI
    const quotations = await dbSAPPS.TenderQuotation.findAll({
      where: { tender_id: id },
      include: [
        {
          model: dbSAPPS.TenderQuotationFile,
          attributes: ["filename"],
          as: "files",
        },
      ],
      transaction,
    });

    // Kumpulin file path (TANPA await)
    const fileTasks = [];

    for (const q of quotations) {
      for (const f of q.files) {
        const filePath = path.join(__dirname, "../../../../public/data/documents/tenderquotations/", f.filename);
        fileTasks.push(fs.promises.unlink(filePath).catch(() => null));
      }
    }

    // 🔥 BULK DELETE (ngebut)
    await dbSAPPS.TenderQuotationFile.destroy({
      where: {
        tenderquotation_id: quotations.map((q) => q.id),
      },
      transaction,
    });

    await dbSAPPS.TenderQuotation.destroy({
      where: { tender_id: id },
      transaction,
    });

    // Tender files
    const tenderFiles = await dbSAPPS.TenderFile.findAll({
      where: { tender_id: id },
      attributes: ["filename"],
      transaction,
    });

    for (const f of tenderFiles) {
      const filePath = path.join(__dirname, "../../../../public/data/documents/tenders/", f.filename);
      fileTasks.push(fs.promises.unlink(filePath).catch(() => null));
    }

    await dbSAPPS.TenderFile.destroy({
      where: { tender_id: id },
      transaction,
    });

    // terakhir parent
    await tender.destroy({ transaction });

    await transaction.commit();

    // 🔥 file delete async (background)
    Promise.allSettled(fileTasks);

    return {
      success: true,
      message: "Tender successfully deleted!",
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || "Failed to delete tender.",
    };
  }
}
