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,
  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 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.TenderQuotation,
          as: "quotations",
          attributes: ["id", "item_id", "unit", "quantity", "price", "delivery_time", "note", "submitted", "submitted_at",  "tender_id"],
        }
      ],
    });

    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),
      };
    });

    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"],
        }
      ],
    });

    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),
      };
    });

    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) {
  try {
    const transaction = await dbSAPPS.sequelize.transaction();
     const tender = await dbSAPPS.Tender.findByPk(id, { transaction });
       if (!tender) return { success: false, message: "Tender not found!" };
   
       const tenderFiles = await dbSAPPS.TenderFile.findAll({
         where: {
           tender_id: id,
         },
         transaction,
       });
   
       if (tenderFiles.length > 0) {
         try {
           // Hapus file lama
           await Promise.all(
             tenderFiles.map(async (file) => {
               const filePath = path.join(__dirname, "../../../../public/data/documents/tenders/", file.filename);
               await fs.promises.unlink(filePath);
             })
           );
         } catch (err) {
           console.warn("Gagal hapus file lama:", err.message);
           // Bisa lanjut tanpa gagal
         }
       }  
    
    await tender.destroy();

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