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

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

async function getAll(params) {
  const filterConditions = {};

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

  if (params.doc_id) {
    filterConditions.id = params.doc_id;
  }
  try {
    const jobPostings = await dbSAPPS.JobPosting.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.JobPostingChild,
          as: "jobpostingchilds",
          attributes: [
            "id",
            "jobposting_id",
            "employeerequest_id",
            "applicant_id",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.EmployeeRequest,
              as: "employeerequest",
              attributes: ["id", "no_fpk", "required_designation_id"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "required_designation",
                  attributes: ["display_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Applicant,
              as: "applicant",
              attributes: ["name"],
            },
            {
              model: dbSAPPS.User,
              as: "user",
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Applicant,
          as: "applicants",
          attributes: [
            "id",
            "modified",
            "modified_by",
            "applicant_status",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.User,
              as: "modifier",
              attributes: ["full_name"],
            },
          ],
        },
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    return {
      //   jobPostings: data,
      jobPostings,
      success: true,
      message: "Job postings loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load Job postings!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function getOne(id) {
  const jobPostings = await dbSAPPS.JobPosting.findByPk(id, {
    include: [
      {
        model: dbSAPPS.JobPostingChild,
        as: "jobpostingchilds",
        attributes: [
          "id",
          "jobposting_id",
          "employeerequest_id",
          "applicant_id",
          "name",
        ],
        include: [
          {
            model: dbSAPPS.EmployeeRequest,
            as: "employeerequest",
            attributes: ["id", "no_fpk", "required_designation_id"],
            include: [
              {
                model: dbSAPPS.Designation,
                as: "required_designation",
                attributes: ["display_name"],
              },
            ],
          },
          {
            model: dbSAPPS.Applicant,
            as: "applicant",
            attributes: ["name"],
          },
          {
            model: dbSAPPS.User,
            as: "user",
            include: [
              {
                model: dbSAPPS.Employee,
                as: "employee",
                attributes: ["employee_name"],
                include: [
                  {
                    model: dbSAPPS.Designation,
                    as: "designation",
                    attributes: ["display_name"],
                  },
                ],
              },
            ],
          },
        ],
      },
      {
        model: dbSAPPS.Applicant,
        as: "applicants",
        attributes: [
          "id",
          "modified",
          "modified_by",
          "applicant_status",
          "name",
        ],
        include: [
          {
            model: dbSAPPS.User,
            as: "modifier",
            attributes: ["full_name"],
          },
        ],
      },
      {
        model: dbSAPPS.User,
        as: "user",
        include: [
          {
            model: dbSAPPS.Employee,
            as: "employee",
            attributes: ["employee_name"],
            include: [
              {
                model: dbSAPPS.Designation,
                as: "designation",
                attributes: ["display_name"],
              },
            ],
          },
        ],
      },
      {
        model: dbSAPPS.Company,
        as: "company",
        attributes: ["display_name"],
      },
      {
        model: dbSAPPS.Designation,
        as: "designation",
        attributes: ["display_name"],
      },
      {
        model: dbSAPPS.Province,
        as: "province",
        attributes: ["display_name"],
      },
      { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
    ],
  });

  return {
    //   jobPostings: data,
    jobPostings,
    success: true,
    message: "Job postings loaded successfully!",
  };
}
async function show() {
  try {
    const jobPostings = await dbSAPPS.JobPosting.findAll({
      where: {
        enabled : true
      },
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.JobPostingChild,
          as: "jobpostingchilds",
          attributes: [
            "id",
            "jobposting_id",
            "employeerequest_id",
            "applicant_id",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.EmployeeRequest,
              as: "employeerequest",
              attributes: ["id", "no_fpk", "required_designation_id"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "required_designation",
                  attributes: ["display_name"],
                },
              ],
            },
            {
              model: dbSAPPS.Applicant,
              as: "applicant",
              attributes: ["name"],
            },
            {
              model: dbSAPPS.User,
              as: "user",
              include: [
                {
                  model: dbSAPPS.Employee,
                  as: "employee",
                  attributes: ["employee_name"],
                  include: [
                    {
                      model: dbSAPPS.Designation,
                      as: "designation",
                      attributes: ["display_name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Applicant,
          as: "applicants",
          attributes: [
            "id",
            "modified",
            "modified_by",
            "applicant_status",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.User,
              as: "modifier",
              attributes: ["full_name"],
            },
          ],
        },
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

    return {
      //   jobPostings: data,
      jobPostings,
      success: true,
      message: "Job postings loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load jobposting!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function filter(params) {
  const filterConditions = {};

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

  try {
    const jobPostings = await dbSAPPS.JobPosting.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
      include: [
        {
          model: dbSAPPS.JobPostingChild,
          as: "jobpostingchilds",
          attributes: [
            "id",
            "jobposting_id",
            "employeerequest_id",
            "applicant_id",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.EmployeeRequest,
              as: "employeerequest",
              attributes: ["id", "no_fpk", "required_designation_id"],
            },
            {
              model: dbSAPPS.Applicant,
              as: "applicant",
              attributes: ["name", "employeerequest_id"],
            },
          ],
        },
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

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

async function option() {
  try {
    const jobPostings = await dbSAPPS.JobPosting.findAll({
      attributes: ["id", "posting_no", "designation_id"],
      where: { enabled: true },
      order: [["creation", "ASC"]],
      include: [
        {
          model: dbSAPPS.JobPostingChild,
          as: "jobpostingchilds",
          attributes: [
            "id",
            "jobposting_id",
            "employeerequest_id",
            "applicant_id",
            "name",
          ],
          include: [
            {
              model: dbSAPPS.EmployeeRequest,
              as: "employeerequest",
              attributes: ["id", "no_fpk", "required_designation_id"],
            },
            {
              model: dbSAPPS.Applicant,
              as: "applicant",
              attributes: ["name", "employeerequest_id"],
            },
          ],
        },
        {
          model: dbSAPPS.User,
          as: "user",
          include: [
            {
              model: dbSAPPS.Employee,
              as: "employee",
              attributes: ["employee_name"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["display_name"],
                },
              ],
            },
          ],
        },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["name"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["display_name"],
        },
        {
          model: dbSAPPS.Province,
          as: "province",
          attributes: ["display_name"],
        },
        { model: dbSAPPS.City, as: "city", attributes: ["display_name"] },
      ],
    });

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

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

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

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // Validasi parameter yang diperlukan
    if (!params.designation_id) {
      return {
        success: false,
        message: "Designation is required!",
      };
    }
    if (!params.company_id) {
      return {
        success: false,
        message: "Company is required!",
      };
    }

    // Generate next Number
    const generateNextNumber = async () => {
      const now = new Date();
      const currentYear = now.getFullYear();
      const yearString = currentYear.toString(); // Ambil dua digit terakhir dari tahun

      const existingDTs = await dbSAPPS.JobPosting.findAll({
        where: {
          posting_no: {
            [Op.like]: `SAP/JOBPOST/${yearString}/%`, // Menggunakan wildcard di depan untuk mencocokkan semua prefix
          },
        },
        transaction,
      });
      // Extract and find max sequence number
      let maxNumber = 0;
      existingDTs.forEach((dt) => {
        const parts = dt.posting_no.split("/");
        if (parts.length === 4) {
          const numberPart = parseInt(parts[3], 10);
          if (!isNaN(numberPart)) {
            maxNumber = Math.max(maxNumber, numberPart);
          }
        }
      });

      // Increment and format with leading zeros
      const nextNumber = (maxNumber + 1).toString();
      return `SAP/JOBPOST/${yearString}/${nextNumber}`;
    };

    // Generate the new number
    const newNumber = await generateNextNumber();
    const newParams = {
      ...params,
      posting_no: newNumber,
    };

    // Cek apakah job posting sudah ada
    const existingJobPost = await dbSAPPS.JobPosting.findOne({
      where: {
        posting_no: params.posting_no,
      },
    });

    if (existingJobPost) {
      return {
        success: false,
        message: `Job Post with "${params.posting_no}" is already taken.`,
      };
    }

    // Set default value untuk enabled
    if (!params.enabled) params.enabled = true;

    const newJobPosting = await dbSAPPS.JobPosting.create(newParams, {
      transaction,
    });

    // Proses jobpostingchilds jika ada
    if (params.jobpostingchilds) {
      // Pastikan data adalah array
      if (typeof params.jobpostingchilds === "string") {
        params.jobpostingchilds = JSON.parse(params.jobpostingchilds);
      }

      // Enforce robust limitation: Only 1 Employee Request per Job Posting
      if (params.jobpostingchilds.length > 1) {
        throw new Error(
          "Limit exceeded: A Job Posting can only have one associated Employee Request."
        );
      }

      // Validasi dan persiapkan data untuk bulkCreate
      const jobPostingsChildData = params.jobpostingchilds.map(
        (jobPostingChild) => {
          // Validasi field yang diperlukan
          if (!jobPostingChild.employeerequest_id) {
            throw new Error("Missing required fields in job posting child.");
          }

          return {
            ...jobPostingChild,
            creation: Date.now(),
            modified: Date.now(),
            modified_by: params.modified_by,
            owner: params.owner,
            jobposting_id: newJobPosting.id,
            applicant_id: jobPostingChild.applicant_id
              ? jobPostingChild.applicant_id
              : null,
            name: jobPostingChild.name ? jobPostingChild.name : "",
            applicant_name: jobPostingChild.applicant_name
              ? jobPostingChild.applicant_name
              : "",
          };
        }
      );

      // Lakukan bulkCreate
      try {
        await dbSAPPS.JobPostingChild.bulkCreate(jobPostingsChildData, {
          transaction,
        });
      } catch (bulkCreateError) {
        await transaction.rollback();
        return {
          success: false,
          message:
            "Failed to create job posting children: " + bulkCreateError.message,
        };
      }
    }

    // Commit transaksi jika semua berhasil
    await transaction.commit();
    return {
      success: true,
      message: "Job Posting has been successfully added!",
      jobPostings: newJobPosting,
    };
  } catch (error) {
    // Rollback transaksi jika terjadi kesalahan
    await transaction.rollback();
    const errorMessage =
      error.message || "An error occurred during Job posting creation.";
    console.error("Error creating job posting:", error); // Log kesalahan untuk debugging
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    // Validasi jika posting_no diubah
    if (params.posting_no && params.posting_no !== jobPosting.posting_no) {
      const existingJobPostingNo = await dbSAPPS.JobPosting.findOne({
        where: {
          posting_no: params.posting_no,
        },
      });
      if (existingJobPostingNo) {
        return {
          success: false,
          message: `Job Posting with "${params.posting_no}" is already taken. Try again!`,
        };
      }
    }

    // Update
    await jobPosting.update(params, { transaction });

    // Proses jobpostingchilds jika ada
    if (params.jobpostingchilds) {
      // Pastikan data adalah array
      if (typeof params.jobpostingchilds === "string") {
        params.jobpostingchilds = JSON.parse(params.jobpostingchilds);
      }

      // Enforce robust limitation: Only 1 Employee Request per Job Posting
      if (params.jobpostingchilds.length > 1) {
        throw new Error(
          "Limit exceeded: A Job Posting can only have one associated Employee Request."
        );
      }

      // Hapus child yang ada sebelum menambahkan yang baru
      await dbSAPPS.JobPostingChild.destroy({
        where: { jobposting_id: id },
        transaction,
      });

      const jobPostingsChildData = [];
      for (const jobPostingChild of params.jobpostingchilds) {
        if (!jobPostingChild.employeerequest_id) {
          throw new Error("Missing required fields in job posting child.");
        }

        // Update enabled employee request apabila ada applicant_id
        if (jobPostingChild.applicant_id) {
          const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(
            jobPostingChild.employeerequest_id
          );
          if (employeeRequest) {
            const updateData = {
              enabled: false, // DISABLE jika sudah ada applicant_id
              modified_by: params.modified_by || null,
              modified: params.modified || new Date(),
            };
            await employeeRequest.update(updateData, { transaction });
          }
        }

        jobPostingsChildData.push({
          ...jobPostingChild,
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by,
          owner: params.owner,
          jobposting_id: id,
          applicant_id: jobPostingChild.applicant_id || null,
          name: jobPostingChild.name || "",
          applicant_name: jobPostingChild.applicant_name || "",
        });
      }
      // Persiapkan data untuk bulkCreate
      // const jobPostingsChildData = params.jobpostingchilds.map(
      //   (jobPostingChild) => {
      //     // Validasi field yang diperlukan
      //     if (!jobPostingChild.employeerequest_id) {
      //       throw new Error("Missing required fields in job posting child.");
      //     }

      //     // Update enabled employee request apabila memang ada applicant_idnya
      //     if (jobPostingChild?.applicant_id) {
      //       const employeeRequest = await dbSAPPS.EmployeeRequest.findByPk(jobPostingChild?.employeerequest_id);
      //       const updateData = {};
      //       updateData.enabled = params.enabled;
      //       if (params.modified_by) updateData.modified_by = params.modified_by;
      //       if (params.modified) updateData.modified = params.modified;

      //       await employeeRequest.update(updateData);
      //     }

      //     return {
      //       ...jobPostingChild,
      //       creation: Date.now(),
      //       modified: Date.now(),
      //       modified_by: params.modified_by,
      //       owner: params.owner,
      //       jobposting_id: id,
      //       applicant_id: jobPostingChild.applicant_id ? jobPostingChild.applicant_id : null,
      //       name: jobPostingChild.name ? jobPostingChild.name : '',
      //       applicant_name: jobPostingChild.applicant_name ? jobPostingChild.applicant_name : '',
      //     };
      //   }
      // );

      // Lakukan bulkCreate untuk child yang baru
      await dbSAPPS.JobPostingChild.bulkCreate(jobPostingsChildData, {
        transaction,
      });
    }

    // Commit transaksi jika semua berhasil
    await transaction.commit();

    return {
      success: true,
      message: "Your data job posting has been saved!",
    };
  } catch (error) {
    // Rollback transaksi jika terjadi kesalahan
    await transaction.rollback();
    const errorMessage = error.message || "Failed to update job posting data.";
    console.error("Error updating job posting:", error); // Log kesalahan untuk debugging
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const jobPosting = await dbSAPPS.JobPosting.findByPk(id);
    if (!jobPosting)
      return { success: false, message: "Job Posting not found!" };
    await new Promise((resolve) => setTimeout(resolve, 500));
    await jobPosting.destroy();

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