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

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

/**
 * Format an array of uccostrealizations by appending a modifier field to each company.
 * The modifier field is the full name of the user who last modified the company.
 * @param {Object[]} uccostrealizations - The array of uccostrealizations to format.
 * @returns {Promise<Object[]>} - The formatted array of uccostrealizations.
 */
const formatWithModifier = async (uccostrealizations) => {
  // Ambil semua modified_by yang unik
  const modifierIds = [...new Set(uccostrealizations.map((u) => u.modified_by).filter(Boolean))];
  // Query semua user sekaligus
  const users = await dbSAPPS.User.findAll({
    where: { id: modifierIds },
    attributes: ["id", "full_name"],
    raw: true,
  });
  // Buat map id -> full_name
  const userMap = Object.fromEntries(users.map((u) => [u.id, u.full_name]));

  // Map hasil
  return uccostrealizations.map((uccostrealization) => ({
    ...(uccostrealization.get ? uccostrealization.get({ plain: true }) : uccostrealization),
    modifier: userMap[uccostrealization.modified_by] || null,
  }));
};

async function getAll(params) {
  try {
    // filter
    const filterConditions = {};
    if (params?.territory_id) {
      filterConditions.territory_id = params.territory_id;
    }

    if (params?.upcountry_id) {
      filterConditions.upcountry_id = params.upcountry_id;
    }

    let uccostrealizations = await dbSAPPS.UcCostRealization.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "name", "display_name"],
        },
        {
          model: dbSAPPS.UpCountry,
          as: "upcountry",
          attributes: ["id", "doc_no", "creation"],
        },
        {
          model: dbSAPPS.Designation,
          as: "designation",
          attributes: ["id", "name", "display_name"],
        },
        {
          model: dbSAPPS.Employee,
          as: "employee",
          attributes: ["id", "employee_name"],
        },
      ],
    });

    uccostrealizations = await Promise.all(
      uccostrealizations.map(async (uccostrealization) => {
        // workflow
        const workflow = await dbSAPPS.Workflow.findOne({
          where: { doc_id: uccostrealization.id, doctype_id: 224 },
          attributes: ["id", "doc_id", "doctype_id", "status", "modified_by", "modified"],
          include: [
            {
              model: dbSAPPS.WorkflowApprover,
              as: "workflowapprovers",
              attributes: ["id", "workflow_id", "designation_id", "territory_id", "status", "modified"],
              include: [
                {
                  model: dbSAPPS.Designation,
                  as: "designation",
                  attributes: ["id", "name", "display_name"],
                  include: [
                    {
                      model: dbSAPPS.Employee,
                      as: "employee",
                      attributes: ["id", "employee_name"],
                    },
                  ],
                },
                {
                  model: dbSAPPS.Territory,
                  as: "territory",
                  attributes: ["id", "name", "display_name"],
                  include: [
                    {
                      model: dbSAPPS.Employee,
                      as: "employees",
                      attributes: ["id", "employee_name"],
                      include: [
                        {
                          model: dbSAPPS.Designation,
                          as: "designation",
                          attributes: ["display_name"],
                        },
                      ],
                    },
                  ],
                },
              ],
            },
          ],
        });

        return {
          ...uccostrealization.dataValues,
          territory_name: uccostrealization.territory ? uccostrealization.territory.display_name : null,
          designation_name: uccostrealization.designation ? uccostrealization.designation.display_name : null,
          employee_name: uccostrealization.employee ? uccostrealization.employee.employee_name : null,
          workflow: workflow ? workflow.dataValues : null,
        };
      })
    );

    uccostrealizations = await formatWithModifier(uccostrealizations);

    return {
      uccostrealizations,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Get uccostrealizations with their respective settings.
 * @param {Object} params - An object that contains parameters to filter the uccostrealizations.
 * @param {Array} params.companiesId - Array of company IDs to filter by.
 * @returns {Promise<Object>} Resolves with an object that contains the uccostrealizations and a success flag.
 * @example
 * const result = await option({ companiesId: [1, 2] });
 * console.log(result);
 */
async function option(params) {
  try {
    // filter
    const filterConditions = {};

    let options = [];
    options = await dbSAPPS.UcCostRealization.findAll({
      where: filterConditions,
      attributes: ["id", "name", "display_name"],
      order: [["name", "ASC"]],
    });

    return {
      options,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Count active uccostrealizations
 *
 * @returns {Promise<{
 *   count: number,
 *   success: boolean,
 *   message: string
 * }>}
 */
async function count(params) {
  try {
    // filter
    const filterConditions = {};

    const count = await dbSAPPS.UcCostRealization.count({
      where: filterConditions,
    });

    return {
      count,
      success: true,
      message: "Loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error?.message || "Failed to load, please try again!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

/**
 * Creates a new uccostrealization
 *
 * @param {object} params - uccostrealization data to be created.
 * @param {string} params.name - uccostrealization name.
 * @param {string} params.display_name - uccostrealization display name.
 * @param {boolean} [params.enabled=true] - uccostrealization enabled status.
 * @param {number} [params.creation=Date.now()] - uccostrealization creation timestamp.
 * @param {number} [params.modified=Date.now()] - uccostrealization last modification timestamp.
 * @param {number} [params.owner=1] - User ID of the uccostrealization owner.
 * @param {number} [params.modified_by=1] - User ID of the last modifier of the uccostrealization.
 *
 * @returns {Promise<{success: boolean, message: string}>} Result of the uccostrealization creation process.
 */
async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const existingDisplayName = await dbSAPPS.UcCostRealization.findOne({
      where: {
        territory_id: params.territory_id,
        upcountry_id: params.upcountry_id,
        category: params.category,
        amount: params.amount,
        upload_date: params.upload_date,
      },
      transaction,
    });

    if (existingDisplayName) {
      return {
        success: false,
        message: `Data is already exist.`,
      };
    }

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

    // save workflow
    if (Array.isArray(params?.workflowTerritories) && params?.workflowTerritories.length > 0) {
      const workflowSettings = await dbSAPPS.WorkflowSetting.findAll(
        {
          where: {
            doctype_id: params.doctype_id,
            type: "Territory",
            enabled: true,
          },
        },
        { transaction }
      );

      const limitWorkflow = workflowSettings[0]?.limit || 0;
      const workflowTerritories = params?.workflowTerritories.slice(0, limitWorkflow);

      if (!workflowTerritories || workflowTerritories.length === 0) {
        await transaction.rollback();
        return {
          success: false,
          message: `Workflow Territories is required. Transaction rolled back.`,
        };
      }

      const workflowData = {
        creation: new Date(), // ✅ Date object
        modified: new Date(), // ✅ Date object
        modified_by: params.modified_by || 1,
        owner: params.owner || 1,
        doctype_id: params.doctype_id,
        doc_id: uccostrealization.id,
        status: "Draft",
      };

      const workflow = await dbSAPPS.Workflow.create(workflowData, {
        transaction,
        lock: transaction.LOCK.UPDATE,
      });

      if (workflow) {
        const workflowApproversData = workflowTerritories.map((workflowTerritory) => {
          if (!workflowTerritory.territory_id) {
            throw new Error("Missing territory_id in workflow territories.");
          }

          return {
            creation: new Date(), // ✅ Date object
            modified: new Date(), // ✅ Date object
            modified_by: params.modified_by || 1,
            owner: params.owner || 1,
            workflow_id: workflow.id,
            territory_id: workflowTerritory.territory_id,
            status: null,
          };
        });

        const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(workflowApproversData, {
          transaction,
          lock: transaction.LOCK.UPDATE,
          validate: true,
        });

        if (approvers?.length !== workflowTerritories.length) {
          await transaction.rollback();
          return {
            success: false,
            message: "Failed to create workflow approvers. Please try again later.",
          };
        }
      }
    }

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

/**
 * Update a uccostrealization
 * @param {number} id - the id of the uccostrealization
 * @param {object} params - uccostrealization data to be updated.
 * @returns {Promise<{success: boolean, message: string}>} Result of the update process.
 */
async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const uccostrealization = await dbSAPPS.UcCostRealization.findByPk(id, { transaction });
    if (!uccostrealization) {
      return {
        success: false,
        message: "ID not found!",
      };
    }

    // Hapus file lama jika ada file baru dan file lama berbeda
    if (params.filename && uccostrealization.filename) {
      const oldFilePath = path.resolve(
        __dirname,
        "../../../../public/data/images/upcountry/",
        uccostrealization.filename
      );
      try {
        await fs.unlink(oldFilePath);
        console.log("Deleted old file:", oldFilePath);
      } catch (err) {
        console.warn("Gagal hapus file lama:", err.message);
      }
    }
    // update
    await uccostrealization.update(params, { transaction });

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

/**
 * Delete a uccostrealization
 * @param {number} id - the id of the uccostrealization
 * @returns {Promise<object>} - the result of the delete
 */
async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const uccostrealization = await dbSAPPS.UcCostRealization.findByPk(id, { transaction });
    if (!uccostrealization) return { success: false, message: "ID not found!" };

    // hapus file jika ada
    if (uccostrealization.filename) {
      const filePath = path.resolve(__dirname, "../../../../public/data/images/upcountry/", uccostrealization.filename);
      try {
        await fs.unlink(filePath);
        console.log("Deleted file:", filePath);
      } catch (err) {
        console.warn("Gagal hapus file:", err.message);
      }
    }

    // delete workflows
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.eq]: uccostrealization.id,
        },
        doctype_id: {
          [Op.eq]: 224,
        },
      },
      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"],
            },
          ],
        },
      ],
    });
    if (!workflows) return { success: false, message: "Workflows for upCountry not found!" };

    for (const workflow of workflows) {
      if (workflow) {
        await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: workflow.id }, transaction });
        await dbSAPPS.Workflow.destroy({ where: { id: workflow.id }, transaction });
      }
    }
    await new Promise((resolve) => setTimeout(resolve, 500));

    // delete
    await uccostrealization.destroy({ transaction });

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