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

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

async function getAll() {
  try {
    const rows = await dbSAPPS.ClientType.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          attributes: ["id", "display_name"],
          as: "company",
        },
        {
          model: dbSAPPS.User,
          attributes: ["id", "full_name"],
          as: "modifier",
          required: false,
        },
        {
          model: dbSAPPS.Specialist,
          as: "specialists",
          include: [
            {
              model: dbSAPPS.SubSpecialist,
              as: 'subspecialists'
            }
          ]
        },
        {
          model: dbSAPPS.ClientSubtype,
          as: "clientsubtypes",
        },
      ],
  });

    const clientTypes = rows.map((row) => {
      const plain = row.get ? row.get({ plain: true }) : row;
      return { ...plain, modifier: plain.modifier?.full_name ?? null };
    });

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

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

  // Handle 'f_' prefix from frontend table filters
  const cleanParams = { ...params };
  Object.keys(cleanParams).forEach((key) => {
    if (key.startsWith("f_")) {
      cleanParams[key.substring(2)] = cleanParams[key];
    }
  });

  if (cleanParams.q) {
    filterConditions[Op.or] = [
      { display_name: { [Op.like]: `%${cleanParams.q}%` } },
      { code: { [Op.like]: `%${cleanParams.q}%` } },
    ];
  }

  if (cleanParams.display_name) {
    if (filterConditions[Op.or]) {
      // If global search exists, AND it with column filter? Usually pure AND.
      // But if we use 'where: filterConditions', Op.or is at top level.
      // We need to be careful not to overwrite.
      // Sequelize handles multiple fields in 'where' as AND.
      // But if we add display_name here, it is ANDed with Op.or. Correct.
    }
    filterConditions.display_name = {
      [Op.like]: `%${cleanParams.display_name}%`,
    };
  }

  if (cleanParams.companiesId) {
    // Determine scope
    let scopeIds = cleanParams.companiesId;
    if (!Array.isArray(scopeIds)) scopeIds = [scopeIds];
    
    // If 'q' or other filters restrict rows, we must ALSO apply scope.
    // Scope is mandatory.
    filterConditions.company_id = { [Op.in]: scopeIds };
  } else if (cleanParams.company_id) {
    filterConditions.company_id = cleanParams.company_id;
  }
  
  // Note: if 'f_company_name' is sent, we can't filter by included model literal easily in top-level 'where'
  // unless we use '$company.name$' syntax or where on include.
  // TableData sends 'f_company.name'. 
  // cleanParams['company.name'] would exist.
  // We can handle it if needed.
  if (cleanParams['company.name']) {
     // This requires where on associated model or $col$
     // filterConditions['$company.display_name$'] = { [Op.like]: `%${cleanParams['company.name']}%` };
     // Assuming alias is "company" and field is "display_name".
     // ClientTypeColumns uses accessor "company.name", but backend model is "company.display_name" usually?
     // verify model 'Company'.
     // For now I'll skip complex relation filter unless requested strictly.
  }

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

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

  try {
    const rows = await dbSAPPS.ClientType.findAll({
      where: filterConditions,
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.User,
          attributes: ["id", "full_name"],
          as: "modifier",
          required: false,
        },
        {
          model: dbSAPPS.Specialist,
          as: "specialists",
          include: [
            {
              model: dbSAPPS.SubSpecialist,
              as: 'subspecialists'
            }
          ]
        },
        {
          model: dbSAPPS.ClientSubtype,
          as: "clientsubtypes",
        },
      ],
    });

    const clientTypes = rows.map((row) => {
      const plain = row.get ? row.get({ plain: true }) : row;
      return { ...plain, modifier: plain.modifier?.full_name ?? null };
    });

    return {
      success: true,
      clientTypes,
      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 option() {
  try {
    const clientTypes = await dbSAPPS.ClientType.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,
      clientTypes,
      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.ClientType.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) {
  try {
    if (!params.company_id)
      return {
        success: false,
        message: "Company is required!",
      };

    if (!params.display_name)
      return {
        success: false,
        message: "Client type is required!",
      };

    const existingClientType = await dbSAPPS.ClientType.findOne({
      where: {
        display_name: params.display_name,
        company_id: params.company_id,
      },
    });

    if (existingClientType)
      return {
        success: false,
        message: `Client Type of "${params.display_name}" is already taken.`,
      };

    if (!params.enabled) params.enabled = true;
    const newClientType = await dbSAPPS.ClientType.create(params);

    return {
      success: true,
      message: "Data Client Type has been successfully adeded!",
      clientTypes: newClientType,
    };
  } catch (error) {
    const errorMessage =
      error.message || "An error occurred during Client type creation.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

    if (
      params.display_name &&
      params.display_name !== clientType.display_name &&
      params.company_id &&
      params.company_id !== clientType.company_id
    ) {
      const existingClientTypeName = await dbSAPPS.ClientType.findOne({
        where: {
          display_name: params.display_name,
          company_id: params.company_id,
        },
      });
      if (existingClientTypeName) {
        return {
          success: false,
          message: `Client type of "${params.display_name}" is already taken. Try again!`,
        };
      }
    }

    await clientType.update(params);

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

async function enabled(id, params) {
  try {
    const clienttype = await dbSAPPS.ClientType.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 _delete(id) {
  try {
    const display_name = await dbSAPPS.ClientType.findByPk(id);
    if (!display_name)
      return { success: false, message: "Client type not found!" };

    await display_name.destroy();

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