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

module.exports = {
  getAll,
  option,
  count,
  create,
  update,
  enabled,
  delete: _delete,
};
const formatWithModifier = async (provinces) => {
  return Promise.all(
    provinces.map(async (province) => {
      const modifier = await dbSAPPS.User.findOne({
        where: {
          id: province.modified_by,
        },
        attributes: ["full_name"],
      });

      return {
        ...province,
        modifier: modifier?.full_name,
      };
    })
  );
};

async function getAll(params) {
  try {
    // base filter conditions
    const filterConditions = {};

    // column-level filters from frontend (TableData.jsx)
    if (params.f_code) {
      filterConditions.code = {
        [Op.like]: `%${params.f_code}%`,
      };
    }

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

    // global search q across key fields
    const andConditions = [];
    if (params.q) {
      andConditions.push({
        [Op.or]: [
          { display_name: { [Op.like]: `%${params.q}%` } },
          { code: { [Op.like]: `%${params.q}%` } },
        ],
      });
    }

    const whereClause = {
      ...filterConditions,
      ...(andConditions.length
        ? {
            [Op.and]: andConditions,
          }
        : {}),
    };

    // robust pagination defaults
    const rawSize = parseInt(params?.pageSize, 10);
    const pageSize = Number.isFinite(rawSize) && rawSize > 0 ? Math.min(rawSize, 100) : 10;
    const rawIndex = parseInt(params?.pageIndex, 10);
    const pageIndex = Number.isFinite(rawIndex) && rawIndex >= 0 ? rawIndex : 0;

    // optional whitelist for sorting (safe defaults)
    const allowedSortBy = ["modified", "display_name", "code"];
    const sortBy = allowedSortBy.includes(params?.sortBy) ? params.sortBy : "modified";
    const sortDir = (params?.sortDir || "DESC").toString().toUpperCase() === "ASC" ? "ASC" : "DESC";

    const { rows, count } = await dbSAPPS.Province.findAndCountAll({
      where: whereClause,
      order: [[sortBy, sortDir]],
      offset: pageIndex * pageSize,
      limit: pageSize,
      include: [
        {
          model: dbSAPPS.City,
          as: "cities",
          attributes: ["id", "display_name", "code"],
        },
      ],
    });

    let provinces = rows.map((province) => {
      return {
        ...province.dataValues
      };
    });

    provinces = await formatWithModifier(provinces);

    return {
      provinces,
      // pagination meta for frontend
      totalCount: count,
      pageIndex,
      pageSize,
      sortBy,
      sortDir,
      success: true,
      message: "provinces loaded successfully!",
    };
  } catch (error) {
    const errorMessage = error.message || "Failed to load provinces!";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

async function option(params) {
  try {
    const provinces = await dbSAPPS.Province.findAll({
      attributes: ["id", "display_name", "code"],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
      include: [
        {
          model: dbSAPPS.City,
          as: "cities",
          attributes: ["id", "display_name", "code"],
          where: { enabled: true },
          required: false,
        },
      ],
    });

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

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

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

async function create(params) {
  const transaction = await dbSAPPS.sequelize.transaction();
  try {
    // validate existing
    const existingDisplayName = await dbSAPPS.Province.findOne({
      where: { display_name: params.display_name },
      transaction,
    });
    if (existingDisplayName) {
      return {
        success: false,
        message: `"${params.display_name}" is already taken.`,
      };
    }

    const existingCode = await dbSAPPS.Province.findOne({
      where: { code: params.code },
      transaction,
    });
    if (existingCode) {
      return {
        success: false,
        message: `"${params.code}" is already taken.`,
      };
    }

    if (!params.enabled) params.enabled = true;

    // save
    await dbSAPPS.Province.create(params, { transaction });

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

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

    if (params.display_name && params.display_name !== province.display_name) {
      const existingProvinceName = await dbSAPPS.Province.findOne({
        where: { display_name: params.display_name },
      });
      if (existingProvinceName) {
        return {
          success: false,
          message: `Province name "${params.display_name}" is already taken. Try again!`,
        };
      }
    }

    if (params.code && params.code !== province.code) {
      const existingProvinceCode = await dbSAPPS.Province.findOne({
        where: { code: params.code },
      });
      if (existingProvinceCode) {
        return {
          success: false,
          message: `Province code "${params.code}" is already taken. Try again!`,
        };
      }
    }

    await province.update(params);

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

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

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

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

    await province.destroy();

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