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

module.exports = {
  getAll,
  getApplicantByUserID,
  getApplicantByOwner,
  filter,
  filterByEmployeeRequest,
  filterByJobPosting,
  option,
  count,
  create,
  update,
  enabled,
  delete: _delete,
};

async function getAll() {
  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "posting_no"],
          include: [
            {
              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"] },
          ],
        },
        {
          model: dbSAPPS.EmployeeRequest,
          as: "employeerequest",
          attributes: ["id", "required_designation_id", "owner"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "required_designation",
              attributes: ["display_name"],
            },
          ],
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: applicants.map((item) => item.employeerequest_id),
        },
        doctype_id: 109,
      },
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const data = applicants.map((item) => {
      const workflow = workflows.find(
        (wf) => wf.doc_id === item.employeerequest_id
      );
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    return {
      applicants: data,
      success: true,
      message: "applicants loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to load applicants!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function getApplicantByOwner(params) {
  const filterConditions = {};

  if (params.user_id) {
    filterConditions.owner = {
      [Op.eq]: params.user_id,
    };
  
  }

  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "posting_no"],
          include: [
            {
              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"] },
          ],
        },
        {
          model: dbSAPPS.EmployeeRequest,
          as: "employeerequest",
          attributes: ["id", "required_designation_id", "owner"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "required_designation",
              attributes: ["display_name"],
            },
          ],
        },
      ],
    });


    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: applicants.map((item) => item.employeerequest_id),
        },
        doctype_id: 109,
      },
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const data = applicants.map((item) => {
      const workflow = workflows.find(
        (wf) => wf.doc_id === item.employeerequest_id
      );
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    return {
      applicants: data,
      success: true,
      message: "applicants loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to load applicants!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function getApplicantByUserID(params) {
  const filterConditions = {};

  if (params.user_id) {
    // filterConditions.interview_info_user1[7][0] = {
    //   [Op.eq]: params.user_id,
    // };
  
  }

  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "posting_no"],
          include: [
            {
              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"] },
          ],
        },
        {
          model: dbSAPPS.EmployeeRequest,
          as: "employeerequest",
          attributes: ["id", "required_designation_id", "owner"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "required_designation",
              attributes: ["display_name"],
            },
          ],
        },
      ],
    });

    // 2) Filter secara manual berdasarkan JSON.parse kolom interview_info*
  const filteredApplicants = applicants.filter(app => {
    const cols = ['interview_info', 'interview_info_user1', 'interview_info_user2'];

    for (const col of cols) {
      let arr;
      try {
        arr = JSON.parse(app[col] || '[]');
      } catch {
        continue;  // skip jika JSON invalid
      }
      // cek apakah arr[7][0] == params.user_id
      if (Array.isArray(arr) &&
          arr[7] != null &&
          String(arr[7][0]) === String(params.user_id)) {
        return true;
      }
    }
    return false;
  });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: filteredApplicants.map((item) => item.employeerequest_id),
        },
        doctype_id: 109,
      },
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const data = filteredApplicants.map((item) => {
      const workflow = workflows.find(
        (wf) => wf.doc_id === item.employeerequest_id
      );
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

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

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

  if (params.company_id) {
    filterConditions.company_id = {
      [Op.eq]: params.company_id,
    };
  }

  if (params.employeerequest_id) {
    filterConditions.employeerequest_id = {
      [Op.eq]: params.employeerequest_id,
    };
  }

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

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

  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "posting_no"],
          include: [
            {
              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"] },
          ],
        },
        {
          model: dbSAPPS.EmployeeRequest,
          as: "employeerequest",
          attributes: ["id", "required_designation_id", "owner"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "required_designation",
              attributes: ["display_name"],
            },
          ],
        },
      ],
    });

    return {
      success: true,
      applicants,
      message: "applicants loaded successfully!",
    };
  } catch (error) {
    const errorMessage =
      error || "Failed to retrieve applicants. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function filterByEmployeeRequest(params) {
  const filterConditions = {};

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

  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "posting_no"],
          include: [
            {
              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"] },
          ],
        },
        {
          model: dbSAPPS.EmployeeRequest,
          as: "employeerequest",
          attributes: ["id", "required_designation_id", "owner"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "required_designation",
              attributes: ["display_name"],
            },
          ],
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: applicants.map((item) => item.employeerequest_id),
        },
        doctype_id: 109,
      },
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const data = applicants.map((item) => {
      const workflow = workflows.find(
        (wf) => wf.doc_id === item.employeerequest_id
      );
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

    return {
      success: true,
      applicants: data,
      message: "applicants loaded successfully!",
    };
  } catch (error) {
    const errorMessage =
      error || "Failed to retrieve applicants. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
async function filterByJobPosting(params) {
  const filterConditions = {};
  if (params.designation_id) {
    filterConditions.jobposting_id = {
      [Op.like]: `%${params.designation_id}%`,
    };
  }

  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "posting_no"],
          include: [
            {
              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"] },
          ],
        },
        {
          model: dbSAPPS.JobPosting,
          as: "jobposting",
          attributes: ["id", "designation_id", "owner"],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["display_name"],
            },
          ],
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.in]: applicants.map((item) => item.employeerequest_id),
        },
        doctype_id: 109,
      },
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: [
            "id",
            "workflow_id",
            "designation_id",
            "territory_id",
            "status",
          ],
          include: [
            {
              model: dbSAPPS.Designation,
              as: "designation",
              attributes: ["id", "name", "display_name"],
            },
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const data = applicants.map((item) => {
      const workflow = workflows.find(
        (wf) => wf.doc_id === item.employeerequest_id
      );
      return {
        ...item.dataValues,
        workflow: workflow ? workflow.dataValues : null,
      };
    });

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

async function option() {
  try {
    const applicants = await dbSAPPS.Applicant.findAll({
      attributes: ["id", "name", "employeerequest_id", "jobposting_id"],
      where: { enabled: true },
      order: [["name", "ASC"]],
      include: [
        // {
        //     model: dbSAPPS.EmployeeRequest,
        //     as: "department",
        //     attributes: ["id", "display_name", "name"],
        // },
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name", "name"],
        },
      ],
    });

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

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

    return {
      count,
      success: true,
      message: "applicants loaded successfully!",
    };
  } catch (error) {
    const errorMessage =
      error || "Failed to counting applicants. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function create(params) {
  try {
    const newParams = {
      ...params,
      id_address: JSON.parse(params.id_address),
      current_address: JSON.parse(params.current_address),
      father_info: JSON.parse(params.father_info),
      mother_info: JSON.parse(params.mother_info),
      spouse_info: JSON.parse(params.spouse_info),
      work_answer: JSON.parse(params.work_answer),
      company_answer: JSON.parse(params.company_answer),
      sibling_info: JSON.parse(params.sibling_info),
      children_info: JSON.parse(params.children_info),
      education_info: JSON.parse(params.education_info),
      last_company: JSON.parse(params.last_company),
      training_info: JSON.parse(params.training_info),
      foreign_language: JSON.parse(params.foreign_language),
      reference: JSON.parse(params.reference),
      interview_info: JSON.parse(params.interview_info),
      interview_info_user1: JSON.parse(params.interview_info_user1),
      interview_info_user2: JSON.parse(params.interview_info_user2),
      work_interest: JSON.parse(params.work_interest),
      driving_license: JSON.parse(params.driving_license),
      employeerequest_id: params.employeerequest_id ? params.employeerequest_id : null,
      jobposting_id: params.jobposting_id ? params.jobposting_id : null
    };
    await dbSAPPS.Applicant.create(newParams);
    return {
      success: true,
      message: "Data has been successfully adeded!",
    };
  } catch (error) {
    const errorMessage = error || "An error occurred during data creation.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function update(id, params) {
  try {
    const applicant = await dbSAPPS.Applicant.findByPk(id);
    if (!applicant)
      return {
        success: false,
        message: "Data not found!",
      };

    const newParams = {
      ...params,
      id_address: JSON.parse(params.id_address),
      current_address: JSON.parse(params.current_address),
      father_info: JSON.parse(params.father_info),
      mother_info: JSON.parse(params.mother_info),
      spouse_info: JSON.parse(params.spouse_info),
      work_answer: JSON.parse(params.work_answer),
      company_answer: JSON.parse(params.company_answer),
      sibling_info: JSON.parse(params.sibling_info),
      education_info: JSON.parse(params.education_info),
      last_company: JSON.parse(params.last_company),
      training_info: JSON.parse(params.training_info),
      foreign_language: JSON.parse(params.foreign_language),
      reference: JSON.parse(params.reference),
      interview_info: JSON.parse(params.interview_info),
      interview_info_user1: JSON.parse(params.interview_info_user1),
      interview_info_user2: JSON.parse(params.interview_info_user2),
      work_interest: JSON.parse(params.work_interest),
      driving_license: JSON.parse(params.driving_license),
      jobposting_id: params.jobposting_id ? params.jobposting_id : null,
      employeerequest_id: params.employeerequest_id ? params.employeerequest_id : null,
    };

    await applicant.update(newParams);
    return {
      success: true,
      message: "Your data has been saved!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to update applicant data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function enabled(id, params) {
  try {
    const applicant = await dbSAPPS.Applicant.findByPk(id);
    if (!applicant)
      return {
        success: false,
        message: "Data 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 applicant.update(updateData);
    return {
      success: true,
      message: "Data has been updated!",
    };
  } catch (error) {
    const errorMessage = error || "Failed to update Data.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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