const { Op, where } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
const moment = require("moment/moment");
require("dotenv").config();
const redisClient = require("../../../../redisClient");
// Choose case-insensitive operator when available (Postgres: ILIKE; others: LIKE)
const LikeOp = (() => {
  try {
    return dbSAPPS?.sequelize?.getDialect?.() === "postgres" ? Op.iLike : Op.like;
  } catch {
    return Op.like;
  }
})();

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

const stringNameMessage = "Sasaran Mutu";

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

  // Server-side pagination & sorting (robust defaults)
  const safeInt = (v, def) => {
    const n = parseInt(v, 10);
    return Number.isFinite(n) && n >= 0 ? n : def;
  };
  const pageIndex = safeInt(params?.pageIndex, 0);
  const pageSize = safeInt(params?.pageSize, 10);

  const SORT_WHITELIST = {
    modified: ["DESC", "ASC"],
    display_name: ["ASC", "DESC"],
    order_no: ["ASC", "DESC"],
  };
  const sortBy = SORT_WHITELIST[params?.sortBy] ? params.sortBy : "order_no";
  const sortDirRaw = (params?.sortDir || "DESC").toUpperCase();
  const sortDir = SORT_WHITELIST[sortBy].includes(sortDirRaw) ? sortDirRaw : "ASC";

  // uuntuk filter custom

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

  try {
    // Simple cache key based on request parameters
    const cacheKey = `cp:list:v2:${JSON.stringify({
      access_read_all_data: params?.access_read_all_data || false,
      administrator_of_msd: params?.administrator_of_msd || false,
      search: params?.search || null,
      display_name: params?.display_name || null,
      f_department_id: params?.f_department_id || null,
      pageIndex,
      pageSize,
      sortBy,
      sortDir,
    })}`;

    // const cached = await redisClient.get(cacheKey);
    // if (cached) {
    //   return cached;
    // }

    // Apply global search across doc_no, company, territory, and period names
    if (params?.search && String(params.search).trim().length > 0) {
      const rawTerm = String(params.search).trim();
      const term = rawTerm.length > 100 ? rawTerm.slice(0, 100) : rawTerm;
      const orSearch = [
        { display_name: { [LikeOp]: `%${term}%` } },
        { target: { [LikeOp]: `%${term}%` } },
        { "$department.display_name$": { [LikeOp]: `%${term}%` } },
      ];
      const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
      filterConditions[Op.and] = [...existingAnd, { [Op.or]: orSearch }];
    }

    // Apply per-column filters (AND semantics)
    const andFilters = [];
    if (params?.f_display_name)
      andFilters.push({ display_name: { [LikeOp]: `%${String(params.f_display_name).trim()}%` } });
    if (params?.f_target) andFilters.push({ target: { [LikeOp]: `%${String(params.f_target).trim()}%` } });
    if (params?.f_department_id)
      andFilters.push({ department_id: { [LikeOp]: `%${String(params.f_department_id).trim()}%` } });
    if (params?.f_enabled === "true") andFilters.push({ enabled: true });
    if (params?.f_enabled === "false") andFilters.push({ enabled: false });
    if (andFilters.length) {
      const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
      filterConditions[Op.and] = [...existingAnd, ...andFilters];
    }

    const sasaranResult = await dbSAPPS.SasaranMutu.findAndCountAll({
      order: [["order_no", "ASC"]],
      where: {
        ...filterConditions,
      },
      attributes: [
        "id",
        "display_name",
        "periode",
        "indicator",
        "target",
        "periode_evaluation",
        "calculation_method",
        "score",
        "note",
        "department_id",
        "enabled",
        "creation",
        "modified",
        "owner",
        "order_no",
      ],
      include: [
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["id", "display_name"],
        },
      ],
      limit: pageSize,
      offset: pageIndex * pageSize,
      distinct: true,
      subQuery: false,
    });

    const data = sasaranResult.rows.map((item) => item.dataValues);

    // compute summary counts over the full filtered set (no pagination)
    const summaryRows = await dbSAPPS.SasaranMutu.findAll({
      where: { ...filterConditions },
      attributes: ["score", "target"],
    });

    const total = Array.isArray(summaryRows) ? summaryRows.length : 0;
    let achieved = 0;
    let sumScore = 0;
    let scoreCount = 0;

    for (const r of summaryRows) {
      const score = r?.dataValues?.score;
      const target = r?.dataValues?.target;
      const s = Number(score);
      const t = Number(target);
      if (!Number.isNaN(s)) {
        sumScore += s;
        scoreCount += 1;
      }
      if (!Number.isNaN(s) && !Number.isNaN(t)) {
        if (s >= t) achieved += 1;
      }
    }

    let avgScore = 0;
    if (scoreCount > 0) {
      const avgRaw = sumScore / scoreCount;
      // If stored scores are fractions (0..1), convert to percent; if already 0..100, keep as is
      if (avgRaw <= 1) {
        avgScore = Math.round(avgRaw * 100);
      } else {
        // keep one decimal for finer display when needed
        avgScore = Math.round(avgRaw * 10) / 10;
      }
    }

    const result = {
      sasaranmutus: data,
      totalCount: sasaranResult.count,
      pageIndex,
      pageSize,
      sortBy,
      sortDir,
      success: true,
      message: `${stringNameMessage} loaded successfully!`,
      summaryCounts: {
        total,
        achieved,
        notAchieved: Math.max(0, total - achieved),
        avgScore,
      },
    };

    // Cache short-lived list responses to accelerate repeated views
    await redisClient.setEx(cacheKey, 30, result);
    return result;
  } catch (error) {
    const errorMessage = error.message || `Failed to load ${stringNameMessage}!`;
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    if (params.id) {
      filterConditions.id = params.id;
    }
    if (params.display_name) {
      filterConditions.display_name = { [LikeOp]: `%${String(params.display_name).trim()}%` };
    }
    if (params.department_id) {
      filterConditions.department_id = params.department_id;
    }
    if (typeof params.enabled !== "undefined") {
      filterConditions.enabled = params.enabled;
    }

    const sasaran = await dbSAPPS.SasaranMutu.findAll({
      order: [["order_no", "ASC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Department,
          as: "department",
          attributes: ["id", "display_name"],
        },
      ],
    });

    return {
      sasaranmutus: sasaran.map((s) => s.dataValues),
      success: true,
      message: `${stringNameMessage} loaded successfully!`,
    };
  } catch (error) {
    console.error("Error retrieving Client Types: ", error);
    const errorMessage = error.message || "Failed to retrieve Client Types. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function option() {
  try {
    const items = await dbSAPPS.SasaranMutu.findAll({
      attributes: ["id", "display_name"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
    });

    return {
      success: true,
      options: items.map((i) => i.dataValues),
      message: `${stringNameMessage} options loaded successfully!`,
    };
  } catch (error) {
    console.error("Error retrieving Client Types: ", error);
    const errorMessage = error.message || "Failed to retrieve Client Types. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function count() {
  try {
    const count = await dbSAPPS.SasaranMutu.count({ where: { enabled: true } });
    return { success: true, count, message: `${stringNameMessage} count loaded successfully!` };
  } catch (error) {
    console.error("Error counting Client Types: ", error);
    return {
      success: false,
      message: "Failed to retrieve Client Types count. Please try again later.",
    };
  }
}

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();

  try {
    // basic validation and defaults for Sasaran Mutu
    if (!params.display_name || !params.department_id) {
      return { success: false, message: "display_name and department_id are required" };
    }

    if (!params.creation) params.creation = Date.now();
    if (!params.modified) params.modified = Date.now();
    if (!params.modified_by) params.modified_by = 1;
    if (!params.owner) params.owner = 1;

    // ensure department exists
    const department = await dbSAPPS.Department.findByPk(params.department_id);
    if (!department) return { success: false, message: "Department not found!" };

    if (typeof params.enabled === "undefined" || params.enabled === null) params.enabled = true;

    const sasaran = await dbSAPPS.SasaranMutu.create(params, { transaction });

    // buat workflow approval
    if (params?.workflowTerritories) {
      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: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by || 1,
          owner: params.owner || 1,
          doctype_id: params.doctype_id,
          doc_id: sasaran?.id,
          status: "Draft",
        };

        // console.log('workflowData : ', workflowData);

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

        if (workflow) {
          const workflowApproversData = workflowTerritories?.map((workflowTerritory) => ({
            creation: Date.now(),
            modified: Date.now(),
            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,
          });
          if (
            approvers?.length !== workflowTerritories.length ||
            !approvers.every((approver) => approver.workflow_id === workflow.id && approver.territory_id !== null)
          ) {
            await transaction.rollback();
            return {
              success: false,
              message: "Failed to create workflow approvers. Please try again later.",
            };
          }
        }
      }
    }

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

async function update(id, params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const sasaran = await dbSAPPS.SasaranMutu.findByPk(id);
    if (!sasaran) return { success: false, message: `${stringNameMessage} not found!` };

    await sasaran.update(params, { transaction });

    // Commit transaksi jika semua sukses
    await transaction.commit();
    return {
      success: true,
      message: `${stringNameMessage} has been updated!`,
    };
  } catch (error) {
    // Rollback jika terjadi error
    await transaction.rollback();
    const errorMessage = error.message || `An error occurred during updating ${stringNameMessage}.`;
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function _delete(id) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    const sasaran = await dbSAPPS.SasaranMutu.findByPk(id, { transaction });
    if (!sasaran) return { success: false, message: `${stringNameMessage} not found!` };

    await sasaran.destroy({ transaction });

    await transaction.commit();

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

async function enabled(id, params) {
  try {
    const sas = await dbSAPPS.SasaranMutu.findByPk(id);
    if (!sas) return { success: false, message: `${stringNameMessage} not found!` };

    const updateData = { enabled: params.enabled };
    if (params.modified_by) updateData.modified_by = params.modified_by;
    if (params.modified) updateData.modified = params.modified;

    await sas.update(updateData);

    return { success: true, message: `${stringNameMessage} has been updated!` };
  } catch (error) {
    const errorMessage = error.message || "Failed to Update Client Type.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
