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

module.exports = {
  getAll,
  getFindingByItemId,
  filter,
  option,
  count,
  create,
  createOrUpdateFindingItemAudit,
  update,
  enabled,
  delete: _delete,
};

async function getAll(params) {
  try {
    console.log("PARAM IA : ", params);
    // filters
    const filterConditions = {};

    // harus diaktifkan
    filterConditions.enabled = {
      [Op.eq]: true,
    };

    if (
      params.access_read_all_data === true ||
      params.access_read_all_data === "true" ||
      params.administrator_of_qmr === "true" ||
      params.administrator_of_qmr === true
    ) {
      filterConditions.department_id = {
        [Op.not]: null,
      };
    } else {
      if (params.department_id) {
        filterConditions.department_id = {
          [Op.eq]: params.department_id,
        };
      }
    }

    // Mengambil semua internal audit dan mengurutkannya berdasarkan kolom 'modified' secara menurun
    const internalAudits = await dbSAPPS.InternalAudit.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.InternalAuditItem,
          as: "internalaudititems",
          where: {
            enabled: true, // ✅ hanya ambil item yang enabled
          },
          order: [["order_no", "ASC"]], // ✅ urutkan berdasarkan id terbaru
          required: false, // ✅ biar audit tetap keluar meskipun ga punya item
          separate: true, // ✅ biar querynya terpisah dan ordernya berfungsi
          subQuery: false, // ✅ ini yang membuat order tidak berfungsi
          include: [
            {
              model: dbSAPPS.InternalAuditFinding,
              as: "internalauditfindings",
            },
          ],
        },
      ],
    });

    // Mengembalikan respons dengan status 200 OK
    return {
      internalAudits,
      success: true,
      message: "internal audits loaded successfully!",
    };
  } catch (error) {
    // Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
    const errorMessage = error.message || "Failed to load internal audits!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function filter(params) {
  // Membuat objek filter dengan validasi
  const filterConditions = {};

  if (params.display_name) {
    filterConditions.display_name = {
      [Op.like]: `%${params.display_name}%`,
    };
  }

  if (params.code) {
    filterConditions.code = {
      [Op.like]: `%${params.code}%`,
    };
  }

  if (params.enabled) {
    filterConditions.enabled = {
      [Op.eq]: params.enabled === "1" ? true : false,
    };
  }

  try {
    // Mencari pengguna berdasarkan filter conditions
    const internalAudits = await dbSAPPS.InternalAudit.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
      include: [
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.InternalAuditItem,
          as: "internalaudititems",
          attributes: ["id", "item_check", "is_compliant"],
        },
      ],
    });

    // Mengembalikan hasil pencarian
    return {
      success: true,
      internalAudits,
      message: "internal audits loaded successfully!",
    };
  } catch (error) {
    // Logging error untuk keperluan debugging
    console.error("Error retrieving internal audits: ", error);
    const errorMessage = error.message || "Failed to retrieve internal audits. Please try again later.";
    // Mengembalikan respons dengan status 500 Internal Server Error
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function option() {
  try {
    // Mencari pengguna yang diaktifkan dengan atribut tertentu
    const internalAudits = await dbSAPPS.InternalAudit.findAll({
      attributes: ["id", "doc_no", "department_id"],
      where: { enabled: true },
      include: [
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.InternalAuditItem,
          as: "internalaudititems",
          attributes: ["id", "item_check", "is_compliant"],
        },
      ],
    });

    // Mengembalikan hasil pencarian
    return {
      success: true,
      internalAudits,
      message: "internal audits loaded successfully!",
    };
  } catch (error) {
    // Logging error untuk keperluan debugging
    console.error("Error retrieving internal audits: ", error);
    const errorMessage = error.message || "Failed to retrieve internal audits. Please try again later.";
    // Mengembalikan respons dengan status 500 Internal Server Error
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function count() {
  try {
    // Menghitung jumlah pengguna yang diaktifkan
    const count = await dbSAPPS.InternalAudit.count({
      where: { enabled: true },
    });

    // Mengembalikan hasil hitung
    return {
      success: true,
      count,
      message: "internal audits loaded successfully!",
    };
  } catch (error) {
    // Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
    console.error("Error counting internal audits: ", error);
    return {
      success: false,
      message: "Failed to retrieve internal audits count. Please try again later.",
    };
  }
}

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Periksa apakah nama sudah ada di dalam database
    const existingInternalAudit = await dbSAPPS.InternalAudit.findOne({
      where: { doc_no: params.doc_no, department_id: params.department_id },
    });
    if (existingInternalAudit) {
      return {
        success: false,
        message: `Nomor Dokumen : "${params.doc_no}" dan Departemen : "${params.department_id}" sudah ada.`,
      };
    }

    // 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

    // Buat Internal Audit baru
    const internalAudit = await dbSAPPS.InternalAudit.create(params, { transaction });

    // Cek dan proses jika ada internalaudititems
    if (params.internalaudititems && params.internalaudititems.length > 0) {
      // Pastikan `internalaudititems` adalah array yang valid
      if (typeof params.internalaudititems === "string") {
        try {
          params.internalaudititems = JSON.parse(params.internalaudititems);
        } catch (err) {
          return { success: false, message: "Invalid JSON format for participiants" };
        }
      }

      // Persiapkan data anak untuk disimpan
      const items = params.internalaudititems.map((item) => ({
        ...item,
        creation: Date.now(),
        modified: Date.now(),
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        enabled: true,
        is_saved: 1,
        internalaudit_id: internalAudit.id, // Assign ID dari MOM yang baru saja dibuat
      }));

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

    // Commit transaksi jika semua berhasil
    await transaction.commit();
    return {
      success: true,
      message: "Internal Audit has been created!",
    };
  } catch (error) {
    // Rollback transaksi jika ada error
    await transaction.rollback();
    const errorMessage = error.message || "An error occurred during creating internal audit.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Periksa apakah Internal Audit dengan ID yang diberikan ada
    const internalAudit = await dbSAPPS.InternalAudit.findByPk(id);
    if (!internalAudit) {
      return { success: false, message: `Internal Audit with ID: "${id}" not found.` };
    }

    // Update fields yang diberikan
    Object.assign(internalAudit, {
      ...params,
      modified: Date.now(),
      enabled: 1,
      modified_by: params.modified_by || 1,
    });

    // Simpan perubahan ke database
    await internalAudit.save({ transaction });

    // Proses internalaudititems jika ada
    if (params.internalaudititems) {
      if (typeof params.internalaudititems === "string") {
        try {
          params.internalaudititems = JSON.parse(params.internalaudititems);
        } catch (err) {
          return { success: false, message: "Invalid JSON format for internalaudititems" };
        }
      }

      if (Array.isArray(params.internalaudititems)) {
        // 1. Ambil semua item lama
        const existingItems = await dbSAPPS.InternalAuditItem.findAll({
          where: { internalaudit_id: id },
          transaction,
        });
        const existingIds = existingItems.map((i) => i.id);

        // 2. Ambil id dari item baru
        const newIds = params.internalaudititems.filter((i) => i.id).map((i) => i.id);

        // 3. Disable (enabled = 0) item lama yang tidak ada di item baru
        const toDisable = existingIds.filter((idOld) => !newIds.includes(idOld));
        if (toDisable.length > 0) {
          await dbSAPPS.InternalAuditItem.update(
            {
              enabled: 0,
              modified: new Date(),
              modified_by: params.modified_by || 1,
            },
            {
              where: { id: toDisable },
              transaction,
            }
          );
        }

        // 4. Upsert item baru
        for (const item of params.internalaudititems) {
          // Kalau ada nomor_urut, lakukan shifting
          if (item.order_no) {
            await dbSAPPS.InternalAuditItem.update(
              { order_no: dbSAPPS.sequelize.literal("order_no + 1") },
              {
                where: {
                  internalaudit_id: id,
                  order_no: { [dbSAPPS.Sequelize.Op.gte]: item.order_no },
                  id: { [dbSAPPS.Sequelize.Op.ne]: item.id || 0 }, // biar gak geser diri sendiri
                },
                transaction,
              }
            );
          }

          // Lanjut upsert
          await dbSAPPS.InternalAuditItem.upsert(
            {
              ...item,
              creation: item.creation || new Date(),
              modified: new Date(),
              modified_by: params.modified_by || 1,
              owner: params.owner || 1,
              is_saved: 1,
              enabled: 1,
              internalaudit_id: id,
            },
            { transaction }
          );
        }
      }
    }

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

async function enabled(id, params) {
  try {
    const internalAudit = await dbSAPPS.InternalAudit.findByPk(id);
    if (!internalAudit)
      return {
        success: false,
        message: "internal audit 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 internalAudit.update(updateData);

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

async function _delete(id) {
  try {
    // Mengambil pengguna dari database
    const internalAudit = await dbSAPPS.InternalAudit.findByPk(id);
    if (!internalAudit) return { success: false, message: "internal audit not found!" };

    // Menghapus pengguna dari database
    await internalAudit.destroy();

    // Respons sukses
    return {
      success: true,
      message: "internal audit successfully deleted!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to delete internal audit.";
    // Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getFindingByItemId(id) {
  try {
    // Mengambil semua pengguna dan mengurutkannya berdasarkan kolom 'modified' secara menurun
    const findingItems = await dbSAPPS.InternalAuditFinding.findOne({
      order: [["creation", "DESC"]],
      where: { internalaudititem_id: id },
    });

    // Mengembalikan respons dengan status 200 OK
    return {
      findingItems,
      success: true,
      message: "finding items loaded successfully!",
    };
  } catch (error) {
    // Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
    const errorMessage = error.message || "Failed to load finding items!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function createOrUpdateFindingItemAudit(params) {
  console.log("params", params);
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const auditItemData = JSON.parse(params?.itemcheck);

    console.log("internalAuditItem : ", auditItemData?.id);
    // Cek apakah data sudah ada di dalam database
    let internalAuditFinding = await dbSAPPS.InternalAuditFinding.findOne({
      where: { internalaudititem_id: auditItemData?.id },
    });

    if (internalAuditFinding) {
      // Hapus file lama jika ada dan ada file baru
      if (params.files && internalAuditFinding.files) {
        try {
          await fs.unlink(internalAuditFinding.files);
        } catch (err) {
          console.warn("Gagal hapus file lama:", err.message);
          // Bisa lanjut tanpa gagal
        }
      }

      Object.assign(internalAuditFinding, {
        ...params,
        internalaudititem_id: auditItemData?.id,
        modified: Date.now(),
        modified_by: params.modified_by || 1,
      });
      await internalAuditFinding.save({ transaction });
    } else {
      if (!params.creation) params.creation = Date.now();
      if (!params.modified) params.modified = Date.now();
      if (!params.modified_by) params.modified_by = 1;

      params.internalaudititem_id = auditItemData?.id;

      await dbSAPPS.InternalAuditFinding.create(params, { transaction });
    }

    if (params?.verification_result === "efektif") {
      const internalAuditItem = await dbSAPPS.InternalAuditItem.findByPk(auditItemData?.id);
      if (internalAuditItem) {
        Object.assign(internalAuditItem, {
          is_compliant: true,
          is_saved: 1,
          modified: Date.now(),
          modified_by: params.modified_by || 1,
        });
        await internalAuditItem.save({ transaction });
      }
    }

    await transaction.commit();
    return {
      success: true,
      message: "Finding item has been saved!",
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || "An error occurred during save finding item.",
    };
  }
}
