// src/services/db/conversion.db.ts
import type { Logger } from 'pino';
import { getPool } from './connection.db';
import { handleDbError, NotFoundError } from './errors.db';
import type { UnitConversion } from '../../types';

export const conversionRepo = {
  /**
   * Fetches unit conversions, optionally filtered by master_item_id.
   */
  async getConversions(
    filters: { masterItemId?: number },
    logger: Logger,
  ): Promise<UnitConversion[]> {
    const { masterItemId } = filters;
    try {
      let query = 'SELECT * FROM public.unit_conversions';
      const params: (string | number)[] = [];

      if (masterItemId) {
        query += ' WHERE master_item_id = $1';
        params.push(masterItemId);
      }

      query += ' ORDER BY master_item_id, from_unit, to_unit';

      const result = await getPool().query<UnitConversion>(query, params);
      return result.rows;
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getConversions',
        { filters },
        {
          defaultMessage: 'Failed to retrieve unit conversions.',
        },
      );
    }
  },

  /**
   * Creates a new unit conversion rule.
   */
  async createConversion(
    conversionData: Omit<UnitConversion, 'unit_conversion_id' | 'created_at' | 'updated_at'>,
    logger: Logger,
  ): Promise<UnitConversion> {
    const { master_item_id, from_unit, to_unit, factor } = conversionData;
    try {
      const res = await getPool().query<UnitConversion>(
        'INSERT INTO public.unit_conversions (master_item_id, from_unit, to_unit, factor) VALUES ($1, $2, $3, $4) RETURNING *',
        [master_item_id, from_unit, to_unit, factor],
      );
      return res.rows[0];
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in createConversion',
        { conversionData },
        {
          fkMessage: 'The specified master item does not exist.',
          uniqueMessage: 'This conversion rule already exists for this item.',
          checkMessage:
            'Invalid unit conversion data provided (e.g., factor must be > 0, units cannot be the same).',
          defaultMessage: 'Failed to create unit conversion.',
        },
      );
    }
  },

  /**
   * Deletes a unit conversion rule.
   */
  async deleteConversion(conversionId: number, logger: Logger): Promise<void> {
    try {
      const res = await getPool().query(
        'DELETE FROM public.unit_conversions WHERE unit_conversion_id = $1',
        [conversionId],
      );
      if (res.rowCount === 0) {
        throw new NotFoundError(`Unit conversion with ID ${conversionId} not found.`);
      }
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in deleteConversion',
        { conversionId },
        {
          defaultMessage: 'Failed to delete unit conversion.',
        },
      );
    }
  },
};
