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

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

async function getAll() {
  try {
    const items = await dbSAPPS.Item.findAll({
      order: [["modified", "DESC"]],
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ItemGroup,
          as: "itemgroups",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ItemCompetitor,
          as: "itemcompetitors",
          attributes: ["id", "display_name", "item_id"],
        },
      ],
    });

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

async function filter(params) {
  try {
    const { 
      limit, 
      offset, 
      page, 
      q, 
      companiesId,
      company_id,
      ...otherParams 
    } = params;

    const queryLimit = limit ? parseInt(limit) : 10;
    const queryOffset = offset ? parseInt(offset) : (page ? (parseInt(page) - 1) * queryLimit : 0);

    const whereConditions = {};
    const andConditions = [];

    // Global Search (q)
    if (q) {
      andConditions.push({
        [Op.or]: [
          { display_name: { [Op.like]: `%${q}%` } },
          { code: { [Op.like]: `%${q}%` } },
          { sku_code: { [Op.like]: `%${q}%` } },
          { alternative_sku_code: { [Op.like]: `%${q}%` } },
        ]
      });
    }

    // Standard Filters
    if (params.display_name) {
        andConditions.push({ display_name: { [Op.like]: `%${params.display_name}%` } });
    }
    
    if (params.code) {
        andConditions.push({ code: { [Op.like]: `%${params.code}%` } });
    }

    // Handle company_id
    if (company_id) {
         andConditions.push({ company_id: company_id });
    } else if (companiesId) {
         // If companiesId is passed as array or list
         if (Array.isArray(companiesId)) {
             andConditions.push({ company_id: { [Op.in]: companiesId } });
         } else {
             // If string comma separated
             const ids = String(companiesId).split(',').map(id => id.trim());
             if (ids.length > 0) {
                 andConditions.push({ company_id: { [Op.in]: ids } });
             }
         }
    }

    // Handle f_ filters (for column filters from MRT)
    Object.keys(otherParams).forEach(key => {
        if (key.startsWith('f_')) {
            const field = key.substring(2);
            let value = otherParams[key];
            if (value !== undefined && value !== null && value !== '') {
               // Handle different types if needed, defaults to like for strings
               if (field === 'enabled') {
                   const isEnabled = value === 'true' || value === '1';
                   andConditions.push({ [field]: isEnabled });
               } else if (field === 'id' || field.endsWith('_id')) {
                    andConditions.push({ [field]: value });
               } else {
                    andConditions.push({ [field]: { [Op.like]: `%${value}%` } });
               }
            }
        }
    });
    
    // Explicit params (legacy support)
    if (params.itemgroup_id) {
        andConditions.push({ itemgroup_id: params.itemgroup_id });
    }
    if (params.enabled !== undefined) {
         const isEnabled = params.enabled === '1' || params.enabled === 'true' || params.enabled === true;
         andConditions.push({ enabled: isEnabled });
    }

    if (andConditions.length > 0) {
      whereConditions[Op.and] = andConditions;
    }

    const { count, rows } = await dbSAPPS.Item.findAndCountAll({
      where: whereConditions,
      limit: queryLimit,
      offset: queryOffset,
      order: [["modified", "DESC"]], // Default sort, can be parameterized
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ItemGroup,
          as: "itemgroups",
          attributes: ["id", "display_name"],
        },
      ],
      distinct: true, // Important for counts with includes
    });

    return {
      success: true,
      items: rows,
      total: count,
      message: "Items loaded successfully!",
    };
  } catch (error) {
    console.error("Error filtering items: ", error);
    return {
      success: false,
      message: error.message || "Failed to retrieve items.",
    };
  }
}


// INI UNTUK MODUL TIM SALES YANG ADA ITEM GROUP
async function getItemByCompanyIdAndItemGroupId(params) {
  try {
    const { company_id, itemgroup_id } = params;
    const items = await dbSAPPS.Item.findAll({
      where: { company_id: company_id, itemgroup_id: itemgroup_id },
      include: [
        {
          model: dbSAPPS.Company,
          as: "company",
          attributes: ["id", "display_name"],
        },
        {
          model: dbSAPPS.ItemGroup,
          as: "itemgroups",
          attributes: ["id", "display_name"],
        },
      ],
    });
    return {
      success: true,
      items,
      message: "items loaded successfully!",
    };
  } catch (error) {
    console.error("Error retrieving items: ", error);
    return {
      success: false,
      message: error.message || "Failed to retrieve items. Please try again later.",
    };
  }
}

async function option() {
  try {
    const items = await dbSAPPS.Item.findAll({
      attributes: [
        "id",
        "display_name",
        "item_cor",
        "itemgroup_id",
        "code",
        "company_id",
        "sku_code",
        "alternative_sku_code",
        "price",
        "stock_uom",
        "valid_from",
        "standard_rate",
      ],
      where: { enabled: true },
      order: [["display_name", "ASC"]],
      include: [
        {
          model: dbSAPPS.ItemGroup,
          as: "itemgroups",
          attributes: ["id", "display_name"],
        },
      ],
    });

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

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

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

async function create(params) {
  try {
    if (!params.display_name)
      return {
        success: false,
        message: "items name is required!",
      };

    const existingNameNNumber = await dbSAPPS.Item.findOne({
      where: {
        display_name: params.display_name,
        code: params.code,
      },
    });
    if (existingNameNNumber)
      return {
        success: false,
        message: `Item name "${params.display_name}" and code "${params.code}" is already taken.`,
      };

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

    const newitems = await dbSAPPS.Item.create(params);

    return {
      success: true,
      message: "Item data has been successfully added!",
      items: newitems,
    };
  } catch (error) {
    const errorMessage = error.message || "An error occurred during create items processing.";
    return {
      success: false,
      message: errorMessage,
    };
  }
}

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

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

    await items.update(params);

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

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

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

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

    await items.destroy();

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