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,

  saveSellingIn,
  sellingBySurveyId,

  saveExpense,
  submitExpense,
  expenseBySurveyId,

  saveTreatment,
  treatmentBySurveyId,

  bulkUpdateSubmit,
};

const stringNameMessage = "Survey";

// SURVEY
// async function getAll(params) {
//   try {
//     // console.log('params survey : ', params)
//     const filterConditions = {};
//         if (params.doc_id) {
//             filterConditions.id = {
//                 [Op.eq]: parseInt(params.doc_id),
//             }
//         } else {
//             if ((params.access_read_all_data === true || params.access_read_all_data === "true")) {
//                 filterConditions.territory_id = {
//                     [Op.not]: null,
//                 };
//             } else {
//                 if (params.territoryIds && params.territoryIds.length > 0) {
//                     filterConditions.territory_id = {
//                         [Op.in]: params.territoryIds.map(item => parseInt(item)),
//                     };
//                 } else {
//                     if (params.territory_id) {
//                         filterConditions.territory_id = {
//                             [Op.eq]: parseInt(params.territory_id),
//                         };
//                     }
//                 }
//             }
//         }

//     const surveys = await dbSAPPS.Survey.findAll({
//       order: [["modified", "DESC"]],
//       where: filterConditions,
//       include: [
//         {
//           model: dbSAPPS.Company,
//           as: "company",
//           attributes: ["id", "display_name"],
//         },
//         {
//           model: dbSAPPS.Territory,
//           as: "territory",
//           attributes: ["id", "display_name"],
//         },
//         {
//           model: dbSAPPS.Period,
//           as: "period",
//           attributes: ["id", "display_name", "start_date", "end_date", "year_id"],
//         },
//         {
//           model: dbSAPPS.SurveySellingIn,
//           as: "surveysellingins",
//           attributes: ["id", "survey_id", "is_saved", "is_submitted", "status", "outlet_id", "company_id", "sellingin_details"],
//         },
//         {
//           model: dbSAPPS.SurveyExpense,
//           as: "surveyexpenses",
//           attributes: ["id", "survey_id", "is_saved", "is_submitted", "status", "expense_details"],
//           include: [
//             {
//                 model: dbSAPPS.AccountEntries,
//                 as: "accountentries",
//                 attributes: ['id', 'kerja_sama', 'outlet_id', 'period_id', 'sales_id', 'status', 'gimmickreq_id','type','surveyexpense_id', 'territory_id', 'year_id', 'quantity', 'createdAt', 'updatedAt']
//             }
//           ]
//         },
//         {
//           model: dbSAPPS.SurveyTreatment,
//           as: "surveytreatments",
//           attributes: ["id", "survey_id", "outlet_id", "is_saved", "is_submitted", "status", "treatment_details"],
//         },
//       ]
//     });

//     // Extract IDs for optimized queries
//     const surveyIds = surveys.map(survey => survey.id).filter(id => id != null);
//     const territoryIds = [...new Set(surveys.map(survey => survey.territory_id).filter(id => id != null))];

//     // Optimize: Filter workflows by doc_id and doctype_id upfront instead of fetching all
//     const workflows = surveyIds.length > 0 ? await dbSAPPS.Workflow.findAll({
//       where: {
//         doc_id: {
//           [Op.in]: surveyIds,
//         },
//         doctype_id: 23,
//       },
//       attributes: ['id', 'doc_id', 'doctype_id', 'status'],
//       include: [{
//         model: dbSAPPS.WorkflowApprover,
//         as: 'workflowapprovers',
//         attributes: ['id', 'workflow_id', 'territory_id', 'status'],
//         include: [{
//           model: dbSAPPS.Territory,
//           as: 'territory',
//           attributes: ['id', 'name', 'display_name']
//         }]
//       }]
//     }) : [];

//     // Optimize: Filter territoryItemGroup by territory_ids upfront instead of fetching all
//     const territoryItemGroup = territoryIds.length > 0 ? await dbSAPPS.TerritoryItemGroup.findAll({
//       where: {
//         territory_id: {
//           [Op.in]: territoryIds,
//         },
//       },
//       attributes: ['territory_id', 'itemgroup_id'],
//       include: [{
//         model: dbSAPPS.ItemGroup,
//         as: 'itemgroup',
//         attributes: ['id', 'display_name']
//       }]
//     }) : [];

//     // Optimize: Use Maps for O(1) lookups instead of O(n) find/filter operations
//     const workflowMap = new Map();
//     workflows.forEach(wf => {
//       if (wf && wf.doc_id != null) {
//         workflowMap.set(wf.doc_id, wf.dataValues || wf);
//       }
//     });

//     const territoryItemGroupMap = new Map();
//     territoryItemGroup.forEach(ig => {
//       if (ig && ig.territory_id != null) {
//         const territoryId = ig.territory_id;
//         if (!territoryItemGroupMap.has(territoryId)) {
//           territoryItemGroupMap.set(territoryId, []);
//         }
//         if (ig.itemgroup) {
//           territoryItemGroupMap.get(territoryId).push(ig.itemgroup);
//         }
//       }
//     });

//     // Optimize: Remove unnecessary Promise.all since map function is synchronous
//     const data = surveys.map((item) => {
//       const workflow = workflowMap.get(item.id) || null;
//       const itemGroups = (item.territory_id != null && territoryItemGroupMap.has(item.territory_id))
//         ? territoryItemGroupMap.get(item.territory_id)
//         : [];

//       return {
//         ...item.toJSON(),
//         workflow: workflow,
//         itemGroups: itemGroups || []
//       };
//     });

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

// OLD 2-22-2026
// async function getAll(params) {
//     try {
//       const filterConditions = {};

//       // =========================
//       // FILTER BUILDING
//       // =========================
//       if (params.doc_id) {
//         filterConditions.id = {
//           [Op.eq]: parseInt(params.doc_id),
//         };
//       } else {
//         if (
//           params.access_read_all_data === true ||
//           params.access_read_all_data === "true" ||
//           params.administrator_of_msd === true ||
//           params.administrator_of_msd === "true"
//         ) {
//           filterConditions.territory_id = {
//             [Op.not]: null,
//           };
//         } else {
//           if (params.territoryIds?.length) {
//             filterConditions.territory_id = {
//               [Op.in]: params.territoryIds.map(Number),
//             };
//           } else if (params.territory_id) {
//             filterConditions.territory_id = {
//               [Op.eq]: parseInt(params.territory_id),
//             };
//           }
//         }
//       }

//       // =========================
//       // PAGINATION (OPTIONAL - untuk lazy load)
//       // =========================
//       const limit = params.limit != null ? parseInt(params.limit, 10) : null;
//       const offset = params.offset != null ? parseInt(params.offset, 10) : 0;
//       const usePagination = limit != null && limit > 0;

//       const findOptions = {
//         order: [["modified", "DESC"]],
//         where: filterConditions,
//         include: [
//           {
//             model: dbSAPPS.Company,
//             as: "company",
//             attributes: ["id", "display_name"],
//           },
//           {
//             model: dbSAPPS.Territory,
//             as: "territory",
//             attributes: ["id", "display_name"],
//           },
//           {
//             model: dbSAPPS.Period,
//             as: "period",
//             attributes: ["id", "display_name", "start_date", "end_date", "year_id"],
//           },
//           {
//             model: dbSAPPS.SurveySellingIn,
//             as: "surveysellingins",
//             attributes: ["id", "survey_id", "is_saved", "is_submitted", "status", "outlet_id", "company_id", "sellingin_details"],
//           },
//           {
//             model: dbSAPPS.SurveyExpense,
//             as: "surveyexpenses",
//             attributes: ["id", "survey_id", "is_saved", "is_submitted", "status", "expense_details"],
//             include: [
//               {
//                 model: dbSAPPS.AccountEntries,
//                 as: "accountentries",
//                 attributes: [
//                   "id",
//                   "kerja_sama",
//                   "outlet_id",
//                   "period_id",
//                   "sales_id",
//                   "status",
//                   "gimmickreq_id",
//                   "type",
//                   "surveyexpense_id",
//                   "territory_id",
//                   "year_id",
//                   "quantity",
//                   "createdAt",
//                   "updatedAt",
//                 ],
//               },
//             ],
//           },
//           {
//             model: dbSAPPS.SurveyTreatment,
//             as: "surveytreatments",
//             attributes: ["id", "survey_id", "outlet_id", "is_saved", "is_submitted", "status", "treatment_details"],
//           },
//         ],
//       };
//       if (usePagination) {
//         findOptions.limit = limit;
//         findOptions.offset = offset;
//       }

//       const [surveys, totalCount] = usePagination
//         ? await Promise.all([
//             dbSAPPS.Survey.findAll(findOptions),
//             dbSAPPS.Survey.count({ where: filterConditions }),
//           ])
//         : [await dbSAPPS.Survey.findAll(findOptions), null];

//       // =========================
//       // PREPARE IDS FOR OPTIMIZED QUERY
//       // =========================
//       const surveyIds = surveys.map((s) => s.id);
//       const territoryIds = [...new Set(surveys.map((s) => s.territory_id))];

//       // =========================
//       // PARALLEL LOAD OPTIMIZED
//       // =========================
//       const [workflows, territoryItemGroup] = await Promise.all([
//         dbSAPPS.Workflow.findAll({
//           attributes: ["id", "doc_id", "doctype_id", "status"],
//           where: {
//             doctype_id: 23,
//             doc_id: { [Op.in]: surveyIds },
//           },
//           include: [
//             {
//               model: dbSAPPS.WorkflowApprover,
//               as: "workflowapprovers",
//               attributes: ["id", "workflow_id", "territory_id", "status"],
//               include: [
//                 {
//                   model: dbSAPPS.Territory,
//                   as: "territory",
//                   attributes: ["id", "name", "display_name"],
//                 },
//               ],
//             },
//           ],
//         }),

//         dbSAPPS.TerritoryItemGroup.findAll({
//           attributes: ["territory_id", "itemgroup_id"],
//           where: {
//             territory_id: { [Op.in]: territoryIds },
//           },
//           include: [
//             {
//               model: dbSAPPS.ItemGroup,
//               as: "itemgroup",
//               attributes: ["id", "display_name"],
//             },
//           ],
//         }),
//       ]);

//       // =========================
//       // MAP LOOKUP (O(1))
//       // =========================
//       const workflowMap = new Map();
//       workflows.forEach((wf) => {
//         workflowMap.set(wf.doc_id, wf);
//       });

//       const itemGroupMap = new Map();
//       territoryItemGroup.forEach((ig) => {
//         if (!itemGroupMap.has(ig.territory_id)) {
//           itemGroupMap.set(ig.territory_id, []);
//         }
//         itemGroupMap.get(ig.territory_id).push(ig.itemgroup);
//       });

//       // =========================
//       // FINAL MERGE DATA
//       // =========================
//       const data = surveys.map((item) => {
//         const workflow = workflowMap.get(item.id);
//         const itemGroups = itemGroupMap.get(item.territory_id) || [];

//         return {
//           ...item.toJSON(),
//           workflow: workflow ? workflow.dataValues : null,
//           itemGroups,
//         };
//       });

//       const result = {
//         surveys: data,
//         success: true,
//         message: "Survey loaded successfully!",
//       };
//       if (usePagination && totalCount != null) {
//         result.totalCount = totalCount;
//       }
//       return result;
//     } catch (error) {
//       return {
//         success: false,
//         message: error.message || "Failed to load Survey !",
//       };
//     }
//   }

// NEW 2-22-2026
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"],
    doc_no: ["ASC", "DESC"],
  };
  const sortBy = SORT_WHITELIST[params?.sortBy] ? params.sortBy : "modified";
  const sortDirRaw = (params?.sortDir || "DESC").toUpperCase();
  const sortDir = SORT_WHITELIST[sortBy].includes(sortDirRaw) ? sortDirRaw : "DESC";

  // uuntuk filter custom
  if (params.period_id) {
    filterConditions.period_id = {
      [Op.eq]: parseInt(params.period_id),
    };
  }

  if (params.doc_id) {
    filterConditions.id = {
      [Op.eq]: parseInt(params.doc_id),
    };
  } else {
    if (
      params.access_read_all_data === true ||
      params.access_read_all_data === "true" ||
      params.administrator_of_msd === true ||
      params.administrator_of_msd === "true"
    ) {
      filterConditions.territory_id = {
        [Op.not]: null,
      };
    } else {
      if (params.territoryIds && params.territoryIds.length > 0) {
        filterConditions.territory_id = {
          [Op.in]: params.territoryIds.map((item) => parseInt(item)),
        };
      } else {
        if (params.territory_id) {
          filterConditions.territory_id = {
            [Op.eq]: parseInt(params.territory_id),
          };
        }
      }
    }
  }

  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({
      doc_id: params?.doc_id || null,
      access_read_all_data: params?.access_read_all_data || false,
      administrator_of_msd: params?.administrator_of_msd || false,
      territoryIds: Array.isArray(params?.territoryIds) ? params.territoryIds : null,
      territory_id: params?.territory_id || null,
      valid_from: params?.valid_from || null,
      valid_to: params?.valid_to || null,
      search: params?.search || null,
      f_doc_no: params?.f_doc_no || null,
      f_territory: params?.f_territory || null,
      f_period: params?.f_period || null,
      f_company: params?.f_company || 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 = [
        { doc_no: { [LikeOp]: `%${term}%` } },
        { "$company.display_name$": { [LikeOp]: `%${term}%` } },
        { "$territory.display_name$": { [LikeOp]: `%${term}%` } },
        { "$period.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_doc_no) andFilters.push({ doc_no: { [LikeOp]: `%${String(params.f_doc_no).trim()}%` } });
    if (params?.f_company)
      andFilters.push({ "$company.display_name$": { [LikeOp]: `%${String(params.f_company).trim()}%` } });
    if (params?.f_territory)
      andFilters.push({ "$territory.display_name$": { [LikeOp]: `%${String(params.f_territory).trim()}%` } });
    if (params?.f_period)
      andFilters.push({ "$period.display_name$": { [LikeOp]: `%${String(params.f_period).trim()}%` } });
    if (andFilters.length) {
      const existingAnd = filterConditions[Op.and] ? filterConditions[Op.and] : [];
      filterConditions[Op.and] = [...existingAnd, ...andFilters];
    }

    const surveysResult = await dbSAPPS.Survey.findAndCountAll({
      order: [[sortBy, sortDir]],
      where: {
        ...filterConditions,
      },
      attributes: [
        "id",
        "doc_no",
        "company_id",
        "territory_id",
        "year_id",
        "period_id",
        "enabled",
        "creation",
        "modified",
        "owner",
      ],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
          attributes: ["id", "display_name"],
        },
      ],
      limit: pageSize,
      offset: pageIndex * pageSize,
      distinct: true,
      subQuery: false,
    });

    // Optimize: Batch fetch workflows, callplandestinations, and callplanvisits instead of N+1
    const surveyIds = surveysResult.rows.map((p) => p.id);
    let workflows = [];

    let surveysellingins = [];
    let surveyexpenses = [];
    let surveytreatments = [];
    let territoryItemGroup = [];

    if (surveyIds.length > 0) {
      [workflows, surveysellingins, surveyexpenses, surveytreatments, territoryItemGroup] = await Promise.all([
        dbSAPPS.Workflow.findAll({
          where: {
            doc_id: { [Op.in]: surveyIds },
            doctype_id: 23,
          },
          attributes: ["id", "doc_id", "doctype_id", "status"],
          include: [
            {
              model: dbSAPPS.WorkflowApprover,
              as: "workflowapprovers",
              attributes: ["id", "workflow_id", "territory_id", "status"],
              include: [
                {
                  model: dbSAPPS.Territory,
                  as: "territory",
                  attributes: ["id", "name", "display_name"],
                },
              ],
            },
          ],
        }),
        dbSAPPS.SurveySellingIn.findAll({
          where: { survey_id: { [Op.in]: surveyIds } },
          attributes: [
            "id",
            "survey_id",
            "is_saved",
            "is_submitted",
            "status",
            "outlet_id",
            "company_id",
            "sellingin_details",
          ],
          include: [
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
            },
          ],
        }),
        dbSAPPS.SurveyExpense.findAll({
          where: { survey_id: { [Op.in]: surveyIds } },
          attributes: ["id", "survey_id", "is_saved", "is_submitted", "status", "expense_details"],
          include: [
            {
              model: dbSAPPS.AccountEntries,
              as: "accountentries",
              attributes: [
                "id",
                "kerja_sama",
                "outlet_id",
                "period_id",
                "sales_id",
                "status",
                "gimmickreq_id",
                "type",
                "surveyexpense_id",
                "territory_id",
                "year_id",
                "quantity",
                "createdAt",
                "updatedAt",
              ],
            },
          ],
        }),
        dbSAPPS.SurveyTreatment.findAll({
          where: { survey_id: { [Op.in]: surveyIds } },
          attributes: ["id", "survey_id", "is_saved", "is_submitted", "status", "treatment_details", "outlet_id"],
          include: [
            {
              model: dbSAPPS.Outlet,
              as: "outlet",
              attributes: ["id", "display_name"],
            },
          ],
        }),
        dbSAPPS.TerritoryItemGroup.findAll({
          where: { territory_id: { [Op.in]: surveysResult.rows.map((p) => p.territory_id) } },
          attributes: ["territory_id", "itemgroup_id"],
          include: [
            {
              model: dbSAPPS.ItemGroup,
              as: "itemgroup",
              attributes: ["id", "display_name"],
            },
          ],
        }),
      ]);
    }

    const workflowMap = {};
    workflows.forEach((w) => {
      workflowMap[w.doc_id] = w;
    });

    const sellingInMap = {};
    surveysellingins.forEach((d) => {
      if (!sellingInMap[d.survey_id]) {
        sellingInMap[d.survey_id] = [];
      }
      sellingInMap[d.survey_id].push(d);
    });

    const expenseMap = {};
    surveyexpenses.forEach((v) => {
      if (!expenseMap[v.survey_id]) {
        expenseMap[v.survey_id] = [];
      }
      expenseMap[v.survey_id].push(v);
    });

    const treatmentMap = {};
    surveytreatments.forEach((t) => {
      if (!treatmentMap[t.survey_id]) {
        treatmentMap[t.survey_id] = [];
      }
      treatmentMap[t.survey_id].push(t);
    });

    const territoryItemGroupMap = {};
    territoryItemGroup.forEach((t) => {
      if (!territoryItemGroupMap[t.territory_id]) {
        territoryItemGroupMap[t.territory_id] = [];
      }
      territoryItemGroupMap[t.territory_id].push(t);
    });

    const data = surveysResult.rows.map((item) => {
      return {
        ...item.dataValues,
        workflow: workflowMap[item.id] ? workflowMap[item.id] : null,
        surveysellingins: sellingInMap[item.id] || [],
        surveyexpenses: expenseMap[item.id] || [],
        surveytreatments: treatmentMap[item.id] || [],
        itemGroups: territoryItemGroupMap[item.territory_id] || [],
      };
    });

    const result = {
      surveys: data,
      totalCount: surveysResult.count,
      pageIndex,
      pageSize,
      sortBy,
      sortDir,
      success: true,
      message: `${stringNameMessage} loaded successfully!`,
    };

    // 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.doc_no) {
      filterConditions.doc_no = params.doc_no;
    }
    if (params.territory_id) {
      filterConditions.territory_id = params.territory_id;
    }
    if (params.survey_id) {
      filterConditions.survey_id = params.survey_id;
    }
    if (params.year_id) {
      filterConditions.year_id = params.year_id;
    }
    if (params.period_id) {
      filterConditions.period_id = params.period_id;
    }

    const surveys = await dbSAPPS.Survey.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Territory,
          as: "territory",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Period,
          as: "period",
        },
        {
          model: dbSAPPS.SurveySellingIn,
          as: "surveysellingins",
        },
        {
          model: dbSAPPS.SurveyExpense,
          as: "surveyexpenses",
        },
        {
          model: dbSAPPS.SurveyTreatment,
          as: "surveytreatments",
        },
      ],
    });

    const workflows = await dbSAPPS.Workflow.findAll({
      attributes: ["id", "doc_id", "doctype_id", "status"],
      include: [
        {
          model: dbSAPPS.WorkflowApprover,
          as: "workflowapprovers",
          attributes: ["id", "workflow_id", "territory_id", "status"],
          include: [
            {
              model: dbSAPPS.Territory,
              as: "territory",
              attributes: ["id", "name", "display_name"],
            },
          ],
        },
      ],
    });

    const territoryItemGroup = await dbSAPPS.TerritoryItemGroup.findAll({
      attributes: ["territory_id", "itemgroup_id"],
      include: [
        {
          model: dbSAPPS.ItemGroup,
          as: "itemgroup",
          attributes: ["id", "display_name"],
        },
      ],
    });

    const data = await Promise.all(
      surveys.map(async (item) => {
        const workflow = workflows.find((wf) => wf.doc_id === item.id && wf.doctype_id === 23);
        const itemGroup = territoryItemGroup
          .filter((ig) => ig.territory_id === item.territory_id)
          .map((ig) => ig.dataValues);
        return {
          ...item.toJSON(),
          workflow: workflow ? workflow.dataValues : null,
          itemGroup: itemGroup ? itemGroup.dataValues : null,
        };
      }),
    );

    return {
      surveys: data,
      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 callPlans = await dbSAPPS.Survey.findAll({
      attributes: ["id", "display_name", "code", "company_id"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Specialist,
          as: "specialists",
          attributes: ["id", "display_name", "code"],
          include: [
            {
              model: dbSAPPS.SubSpecialist,
              as: "subspecialists",
              attributes: ["id", "display_name", "code"],
            },
          ],
        },
        {
          model: dbSAPPS.ClientSubtype,
          as: "clientsubtypes",
          attributes: ["id", "display_name", "code"],
        },
      ],
    });

    return {
      success: true,
      callPlans,
      message: "Client Types 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.Survey.count({
      where: { enabled: true },
    });

    return {
      success: true,
      count,
      message: "Client Types 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 {
    // periksa apakah data dengan doc_no, territory_id, year_id, period_id sudah ada di dalam database
    const existingData = await dbSAPPS.Survey.findOne({
      where: {
        territory_id: params.territory_id,
        year_id: params.year_id,
        period_id: params.period_id,
        company_id: params.company_id,
      },
    });
    if (existingData) {
      return { success: false, message: `Survey already exists.` };
    }

    // 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
    if (!params.owner) params.owner = 1; // Default jika tidak ada nilai

    const territory = await dbSAPPS.Territory.findByPk(params.territory_id);
    if (!territory) {
      return {
        success: false,
        message: "Territory not found!",
      };
    }
    const period = await dbSAPPS.Period.findByPk(params.period_id);
    if (!period) {
      return {
        success: false,
        message: "Period not found!",
      };
    }

    const docNo = `SMS-SURVEY-${territory.display_name.toUpperCase()}-${period.display_name.toUpperCase()}`;
    params.doc_no = docNo;

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

    const survey = await dbSAPPS.Survey.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: survey?.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 callPlan = await dbSAPPS.Survey.findByPk(id);
    if (!callPlan)
      return {
        success: false,
        message: `${stringNameMessage} not found!`,
      };

    // Update Call Plan
    await callPlan.update(params, { transaction });

    // Pastikan `callplandestinations` adalah array yang valid
    if (params.callplandestinations) {
      // Jika formatnya string JSON, ubah menjadi array
      if (typeof params.callplandestinations === "string") {
        try {
          params.callplandestinations = JSON.parse(params.callplandestinations);
        } catch (err) {
          return {
            success: false,
            message: "Invalid JSON format for callplandestinations",
          };
        }
      }

      // Jika `callplandestinations` adalah array dan memiliki data
      if (Array.isArray(params.callplandestinations) && params.callplandestinations.length > 0) {
        // console.log(' params.callplandestinations: ', params.callplandestinations);
        const uniqueDestinations = new Set();
        const duplicateIds = [];

        for (const destination of params.callplandestinations) {
          const identifier = `${destination.client_id}-${destination.outlet_id}`;
          if (uniqueDestinations.has(identifier)) {
            duplicateIds.push(identifier);
          } else {
            uniqueDestinations.add(identifier);
          }
        }

        if (duplicateIds.length > 0) {
          await transaction.rollback();
          return {
            success: false,
            message: `Duplicate client_id and outlet_id pairs: ${duplicateIds.join(", ")}`,
          };
        }

        // Hapus data lama terlebih dahulu
        await dbSAPPS.SurveyDestination.destroy({ where: { callplan_id: id }, transaction });

        // Persiapkan data baru untuk disimpan
        const callplandestinationsData = params.callplandestinations.map((destination) => ({
          ...destination,
          creation: Date.now(),
          modified: Date.now(),
          modified_by: params.modified_by || 1,
          owner: params.owner || 1,
          callplan_id: parseInt(id),
        }));

        // Masukkan data anak yang baru
        try {
          await dbSAPPS.SurveyDestination.bulkCreate(callplandestinationsData, { transaction });
        } catch (err) {
          console.log(" err: ", err);
          await transaction.rollback();
          return {
            success: false,
            message: `Error saving callplandestinations: ${err.message || "Unknown error"}`,
          };
        }
      } else {
        // Jika tidak ada data, hapus semua child terkait
        await dbSAPPS.SurveyDestination.destroy({ where: { callplan_id: id }, transaction });
      }

      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: callPlan?.id,
            status: "Draft",
          };

          console.log("workflowData : ", workflowData);

          // saya mau update dulu
          // Cari dulu workflow sebelum update apakah sudah ada atau belum
          const existingWorkflow = await dbSAPPS.Workflow.findOne({
            where: {
              doctype_id: workflowData.doctype_id,
              doc_id: workflowData.doc_id,
            },
            transaction,
          });

          if (existingWorkflow) {
            // Jika workflow sudah ada, update status menjadi 'Draft'
            await existingWorkflow.update(
              {
                modified: Date.now(),
                modified_by: params.modified_by || 1,
                owner: params.owner || 1,
                status: "Draft",
              },
              { transaction, lock: transaction.LOCK.UPDATE },
            );

            await dbSAPPS.WorkflowApprover.destroy(
              {
                where: {
                  workflow_id: existingWorkflow.id,
                },
              },
              { transaction, lock: transaction.LOCK.UPDATE },
            );

            const workflowApproversData = workflowTerritories?.map((workflowTerritory) => ({
              creation: Date.now(),
              modified: Date.now(),
              modified_by: params.modified_by || 1,
              owner: params.owner || 1,
              workflow_id: existingWorkflow.id,
              territory_id: workflowTerritory?.territory_id,
              status: null,
            }));

            const approvers = await dbSAPPS.WorkflowApprover.bulkCreate(
              workflowApproversData.map((data) => ({
                ...data,
                workflow_id: existingWorkflow.id,
              })),
              { transaction, lock: transaction.LOCK.UPDATE },
            );

            if (
              approvers?.length !== workflowTerritories.length ||
              !approvers.every(
                (approver) => approver.workflow_id === existingWorkflow.id && approver.territory_id !== null,
              )
            ) {
              await transaction.rollback();
              return {
                success: false,
                message: "Failed to create workflow approvers. Please try again later.",
              };
            }
          } else {
            // Jika workflow belum ada, create baru
            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.",
                };
              }
            } else {
              await transaction.rollback();
              return {
                success: false,
                message: `Failed to create workflow. Please try again later.`,
              };
            }
          }
        }
      }
    }

    // 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 survey = await dbSAPPS.Survey.findByPk(id, { transaction });
    if (!survey) return { success: false, message: "Survey not found!" };
    const workflows = await dbSAPPS.Workflow.findAll({
      where: {
        doc_id: {
          [Op.eq]: survey.id,
        },
        doctype_id: {
          //  [Op.eq]: survey.doctype_id
          [Op.eq]: 23,
        },
      },
      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 survey 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));
    await survey.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 clienttype = await dbSAPPS.Survey.findByPk(id);
    if (!clienttype)
      return {
        success: false,
        message: "Client Type 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 clienttype.update(updateData);

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

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

  try {
    // periksa apakah data dengan doc_no, territory_id, year_id, period_id sudah ada di dalam database
    // const existingData = await dbSAPPS.SurveySellingIn.findOne({ where: { survey_id: params.survey_id, outlet_id: params.outlet_id, company_id: params.company_id } });

    const existingData = await dbSAPPS.SurveySellingIn.findOne({
      where: { survey_id: params.survey_id, outlet_id: params.outlet_id, company_id: params.company_id },
      transaction,
    });
    if (existingData) {
      await existingData.update(params, { transaction });
    } else {
      await dbSAPPS.SurveySellingIn.create(params, { transaction });
    }

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

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

    if (params.survey_id) {
      filterConditions.survey_id = params.survey_id;
    }

    if (params.outlet_id) {
      filterConditions.outlet_id = params.outlet_id;
    }

    if (params.company_id) {
      filterConditions.company_id = params.company_id;
    }

    const sellingIns = await dbSAPPS.SurveySellingIn.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
      ],
    });

    return {
      sellingIns,
      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 saveExpense(params) {
  const transaction = await dbSAPPS.sequelize.transaction();

  try {
    let surveyExpenseRecord = await dbSAPPS.SurveyExpense.findOne({
      where: { survey_id: params.survey_id },
      transaction,
    });

    if (!params.period_id || !params.year_id || !params.territory_id) {
      return {
        success: false,
        message: "Period, Year, and Territory are required!",
      };
    }

    if (surveyExpenseRecord) {
      await surveyExpenseRecord.update(params, { transaction });
    } else {
      surveyExpenseRecord = await dbSAPPS.SurveyExpense.create({ ...params, status: "DRAFT" }, { transaction });
    }

    await transaction.commit();
    return {
      success: true,
      message: `Survey Expense has been saved!`,
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || `An error occurred during saving Survey Expense.`,
    };
  }
}

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

  try {
    let surveyExpenseRecord = await dbSAPPS.SurveyExpense.findOne({
      where: { survey_id: params.survey_id },
      transaction,
    });

    if (!params.period_id || !params.year_id || !params.territory_id) {
      return {
        success: false,
        message: "Period, Year, and Territory are required!",
      };
    }

    if (surveyExpenseRecord) {
      await surveyExpenseRecord.update(params, { transaction });
    } else {
      surveyExpenseRecord = await dbSAPPS.SurveyExpense.create({ ...params, status: 2 }, { transaction });
    }

    const balances = [];

    if (params.expense_details) {
      const surveyExpenseId = surveyExpenseRecord.id;
      const expenseDetails = params.expense_details;

      for (const outlet of expenseDetails) {
        const outletId = outlet.outlet_id;
        for (const sale of outlet.sales_detail) {
          const salesId = sale.id;
          const periodId = params.period_id;
          const yearId = params.year_id;
          const territoryId = params.territory_id;
          const itemId = sale.item_id;

          const clients = [
            ...(sale.doctor_data || []),
            ...(sale.patient_data || []),
            ...(sale.assistant_data || []),
            ...(sale.procurement_data || []),
          ];

          for (const client of clients) {
            const clientId =
              client.doctor_id || client.patient_id || client.assistant_id || client.procurement_id || null;
            const kerjaSama = client.kerja_sama || null;
            const quantity =
              client.qty_doctor || client.qty_patient || client.qty_assistant || client.qty_procurement || null;

            if (!clientId) continue;

            balances.push({
              type: "debit",
              surveyexpense_id: surveyExpenseId,
              sales_id: salesId,
              outlet_id: outletId,
              client_id: clientId,
              territory_id: territoryId,
              item_id: itemId,
              period_id: periodId,
              year_id: yearId,
              kerja_sama: kerjaSama,
              quantity: quantity,
              status: 0,
              enabled: params.enabled,
              modified_by: params.modified_by,
              owner: params.owner,
              creation: new Date(),
              modified: new Date(),
            });
          }
        }
      }
      if (balances.length > 0) {
        await dbSAPPS.AccountEntries.bulkCreate(balances, { transaction });
      }
    }

    await transaction.commit();
    return {
      success: true,
      message: `Survey Expense has been submitted!`,
    };
  } catch (error) {
    await transaction.rollback();
    return {
      success: false,
      message: error.message || `An error occurred during saving Survey Expense.`,
    };
  }
}

async function expenseBySurveyId(id) {
  try {
    const expense = await dbSAPPS.SurveyExpense.findOne({
      where: { survey_id: id },
      include: [
        {
          model: dbSAPPS.AccountEntries,
          as: "accountentries",
          attributes: [
            "id",
            "kerja_sama",
            "outlet_id",
            "period_id",
            "sales_id",
            "status",
            "surveyexpense_id",
            "territory_id",
            "year_id",
            "quantity",
            "createdAt",
            "updatedAt",
          ],
        },
      ],
    });
    return {
      expense,
      success: true,
      message: `Survey Expense loaded successfully!`,
    };
  } catch (error) {
    console.error("Error retrieving Survey Expense: ", error);
    const errorMessage = error.message || "Failed to retrieve Survey Expense. Please try again later.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

  try {
    const existingData = await dbSAPPS.SurveyTreatment.findOne({
      where: { survey_id: params.survey_id, outlet_id: params.outlet_id },
      transaction,
    });
    if (existingData) {
      await existingData.update(params, { transaction });
    } else {
      await dbSAPPS.SurveyTreatment.create(params, { transaction });
    }

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

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

    if (params.survey_id) {
      filterConditions.survey_id = params.survey_id;
    }

    if (params.outlet_id) {
      filterConditions.outlet_id = params.outlet_id;
    }

    const treatment = await dbSAPPS.SurveyTreatment.findAll({
      order: [["modified", "DESC"]],
      where: filterConditions,
      include: [
        {
          model: dbSAPPS.Outlet,
          as: "outlet",
          attributes: ["id", "display_name"],
        },
      ],
    });

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

async function bulkUpdateSubmit(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    //  update doc_status sellingIn, expense, treatment
    const updateConditions = params.map((param) => ({
      model: param.sellingInExist
        ? dbSAPPS.SurveySellingIn
        : param.expenseExist
          ? dbSAPPS.SurveyExpense
          : dbSAPPS.SurveyTreatment,
      where: { survey_id: param.survey_id },
      update: { status: param.doc_status },
    }));

    await Promise.all(
      updateConditions.map((condition) => condition.model.update(condition.update, condition.where, { transaction })),
    );

    // Commit the transaction if all operations succeed
    await transaction.commit();
    return {
      success: true,
      message: "Bulk update data has been successfully !",
    };
  } catch (error) {
    // Rollback the transaction in case of error
    await transaction.rollback();
    const errorMessage = error.message || "An error occurred during Bulk update processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}
