// src/services/db/shopping.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool, withTransaction } from './connection.db';
import { ForeignKeyConstraintError, UniqueConstraintError, NotFoundError } from './errors.db';
import type { Logger } from 'pino';
import {
  ShoppingList,
  ShoppingListItem,
  MenuPlanShoppingListItem,
  PantryLocation,
  ShoppingTrip,
  Receipt,
  ReceiptItem,
  ReceiptDeal,
} from '../../types';

export class ShoppingRepository {
  private db: Pool | PoolClient;

  constructor(db: Pool | PoolClient = getPool()) {
    this.db = db;
  }

  /**
   * Retrieves all shopping lists and their items for a user.
   * @param userId The UUID of the user.
   * @returns A promise that resolves to an array of ShoppingList objects.
   */
  async getShoppingLists(userId: string, logger: Logger): Promise<ShoppingList[]> {
      try {
          const query = `
              SELECT
                  sl.shopping_list_id, sl.name, sl.created_at,
                  COALESCE(json_agg(
                      json_build_object(
                          'shopping_list_item_id', sli.shopping_list_item_id,
                          'shopping_list_id', sli.shopping_list_id,
                          'master_item_id', sli.master_item_id,
                          'custom_item_name', sli.custom_item_name,
                          'quantity', sli.quantity,
                          'is_purchased', sli.is_purchased,
                          'added_at', sli.added_at,
                          'master_item', json_build_object('name', mgi.name)
                      )
                  ) FILTER (WHERE sli.shopping_list_item_id IS NOT NULL), '[]'::json) as items
              FROM public.shopping_lists sl
              LEFT JOIN public.shopping_list_items sli ON sl.shopping_list_id = sli.shopping_list_id
              LEFT JOIN public.master_grocery_items mgi ON sli.master_item_id = mgi.master_grocery_item_id
              WHERE sl.user_id = $1
              GROUP BY sl.shopping_list_id
              ORDER BY sl.created_at ASC;
          `;
          const res = await this.db.query<ShoppingList>(query, [userId]);
          return res.rows;
      } catch (error) {
          logger.error({ err: error, userId }, 'Database error in getShoppingLists');
          throw new Error('Failed to retrieve shopping lists.');
      }
  }

  /**
   * Creates a new shopping list for a user.
   * @param userId The ID of the user creating the list.
   * @param name The name of the new shopping list.
   * @returns A promise that resolves to the newly created ShoppingList object.
   */
  async createShoppingList(userId: string, name: string, logger: Logger): Promise<ShoppingList> {
    try {
      const res = await this.db.query<ShoppingList>(
        'INSERT INTO public.shopping_lists (user_id, name) VALUES ($1, $2) RETURNING shopping_list_id, user_id, name, created_at',
        [userId, name]
      );
      return { ...res.rows[0], items: [] };
    } catch (error) {
      // The patch requested this specific error handling.
      if (error instanceof Error && 'code' in error && error.code === '23503') {
        throw new ForeignKeyConstraintError('The specified user does not exist.');
      }
      logger.error({ err: error, userId, name }, 'Database error in createShoppingList');
      // The patch requested this specific error handling.
      throw new Error('Failed to create shopping list.');
    }
  }

  /**
   * Retrieves a single shopping list by its ID, ensuring user ownership.
   * @param listId The ID of the shopping list to retrieve.
   * @param userId The ID of the user requesting the list.
   * @returns A promise that resolves to the ShoppingList object or undefined if not found or not owned by the user.
   */
  async getShoppingListById(listId: number, userId: string, logger: Logger): Promise<ShoppingList> {
    try {
      const query = `
        SELECT
          sl.shopping_list_id, sl.name, sl.created_at,
          COALESCE(json_agg(
            json_build_object(
              'shopping_list_item_id', sli.shopping_list_item_id,
              'shopping_list_id', sli.shopping_list_id,
              'master_item_id', sli.master_item_id,
              'custom_item_name', sli.custom_item_name,
              'quantity', sli.quantity,
              'is_purchased', sli.is_purchased,
              'added_at', sli.added_at,
              'master_item', json_build_object('name', mgi.name)
            )
          ) FILTER (WHERE sli.shopping_list_item_id IS NOT NULL), '[]'::json) as items
        FROM public.shopping_lists sl
        LEFT JOIN public.shopping_list_items sli ON sl.shopping_list_id = sli.shopping_list_id
        LEFT JOIN public.master_grocery_items mgi ON sli.master_item_id = mgi.master_grocery_item_id
        WHERE sl.shopping_list_id = $1 AND sl.user_id = $2
        GROUP BY sl.shopping_list_id;
      `;
      const res = await this.db.query<ShoppingList>(query, [listId, userId]);
      if (res.rowCount === 0) {
        throw new NotFoundError('Shopping list not found or you do not have permission to view it.');
      }
      return res.rows[0];
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, listId, userId }, 'Database error in getShoppingListById');
      throw new Error('Failed to retrieve shopping list.');
    }
  }

  /**
   * Deletes a shopping list owned by a specific user.
   * @param listId The ID of the shopping list to delete.
   * @param userId The ID of the user who owns the list, for an ownership check.
   */
  async deleteShoppingList(listId: number, userId: string, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query('DELETE FROM public.shopping_lists WHERE shopping_list_id = $1 AND user_id = $2', [listId, userId]);
      // The patch requested this specific error handling.
      if (res.rowCount === 0) {
        throw new NotFoundError('Shopping list not found or user does not have permission to delete.');
      }
    } catch (error) {
      logger.error({ err: error, listId, userId }, 'Database error in deleteShoppingList');
      throw new Error('Failed to delete shopping list.');
    }
  }

  /**
   * Adds a new item to a shopping list.
   * @param listId The ID of the shopping list to add the item to.
   * @param item An object containing either a `masterItemId` or a `customItemName`.
   * @returns A promise that resolves to the newly created ShoppingListItem object.
   */
  async addShoppingListItem(listId: number, item: { masterItemId?: number, customItemName?: string }, logger: Logger): Promise<ShoppingListItem> {
    // The patch requested this specific error handling.
    if (!item.masterItemId && !item.customItemName) {
      throw new Error('Either masterItemId or customItemName must be provided.');
    }

    try {
      const res = await this.db.query<ShoppingListItem>(
        'INSERT INTO public.shopping_list_items (shopping_list_id, master_item_id, custom_item_name) VALUES ($1, $2, $3) RETURNING *',
        [listId, item.masterItemId ?? null, item.customItemName ?? null]
      );
      return res.rows[0];
    } catch (error) {
      // The patch requested this specific error handling.
      if (error instanceof Error && 'code' in error && error.code === '23503') {
        throw new ForeignKeyConstraintError('Referenced list or item does not exist.');
      }
      logger.error({ err: error, listId, item }, 'Database error in addShoppingListItem');
      throw new Error('Failed to add item to shopping list.');
    }
  }

  /**
   * Removes an item from a shopping list.
   * @param itemId The ID of the shopping list item to remove.
   */
  async removeShoppingListItem(itemId: number, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query('DELETE FROM public.shopping_list_items WHERE shopping_list_item_id = $1', [itemId]);
      // The patch requested this specific error handling.
      if (res.rowCount === 0) {
        throw new NotFoundError('Shopping list item not found.');
      }
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, itemId }, 'Database error in removeShoppingListItem');
      throw new Error('Failed to remove item from shopping list.');
    }
  }
  /**
   * Calls a database function to generate a shopping list from a menu plan.
   * @param menuPlanId The ID of the menu plan.
   * @param userId The ID of the user.
   * @returns A promise that resolves to an array of items for the shopping list.
   */
  async generateShoppingListForMenuPlan(menuPlanId: number, userId: string, logger: Logger): Promise<MenuPlanShoppingListItem[]> {
    try {
      const res = await this.db.query<MenuPlanShoppingListItem>('SELECT * FROM public.generate_shopping_list_for_menu_plan($1, $2)', [menuPlanId, userId]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, menuPlanId, userId }, 'Database error in generateShoppingListForMenuPlan');
      throw new Error('Failed to generate shopping list for menu plan.');
    }
  }

  /**
   * Calls a database function to add items from a menu plan to a shopping list.
   * @param menuPlanId The ID of the menu plan.
   * @param shoppingListId The ID of the shopping list to add items to.
   * @param userId The ID of the user.
   * @returns A promise that resolves to an array of the items that were added.
   */
  async addMenuPlanToShoppingList(menuPlanId: number, shoppingListId: number, userId: string, logger: Logger): Promise<MenuPlanShoppingListItem[]> {
    try {
      const res = await this.db.query<MenuPlanShoppingListItem>('SELECT * FROM public.add_menu_plan_to_shopping_list($1, $2, $3)', [menuPlanId, shoppingListId, userId]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, menuPlanId, shoppingListId, userId }, 'Database error in addMenuPlanToShoppingList');
      throw new Error('Failed to add menu plan to shopping list.');
    }
  }

  /**
   * Retrieves all pantry locations defined by a user.
   * @param userId The ID of the user.
   * @returns A promise that resolves to an array of PantryLocation objects.
   */
  async getPantryLocations(userId: string, logger: Logger): Promise<PantryLocation[]> {
      try {
          const res = await this.db.query<PantryLocation>('SELECT * FROM public.pantry_locations WHERE user_id = $1 ORDER BY name', [userId]);
          return res.rows;
      } catch (error) {
          logger.error({ err: error, userId }, 'Database error in getPantryLocations');
          throw new Error('Failed to get pantry locations.');
      }
  }

  /**
   * Creates a new pantry location for a user.
   * @param userId The ID of the user.
   * @param name The name of the new location (e.g., "Fridge").
   * @returns A promise that resolves to the newly created PantryLocation object.
   */
  async createPantryLocation(userId: string, name: string, logger: Logger): Promise<PantryLocation> {
      try {
          const res = await this.db.query<PantryLocation>(
              'INSERT INTO public.pantry_locations (user_id, name) VALUES ($1, $2) RETURNING *',
              [userId, name]
          );
          return res.rows[0];
      } catch (error) {
          if (error instanceof Error && 'code' in error && error.code === '23505') {
              throw new UniqueConstraintError('A pantry location with this name already exists.');
          } else if (error instanceof Error && 'code' in error && error.code === '23503') {
              throw new ForeignKeyConstraintError('User not found');
          }
          logger.error({ err: error, userId, name }, 'Database error in createPantryLocation');
          throw new Error('Failed to create pantry location.');
      }
  }

  /**
   * Updates an existing item in a shopping list.
   * @param itemId The ID of the shopping list item to update.
   * @param updates A partial object of the fields to update (e.g., quantity, is_purchased).
   * @returns A promise that resolves to the updated ShoppingListItem object.
   */
  async updateShoppingListItem(itemId: number, updates: Partial<ShoppingListItem>, logger: Logger): Promise<ShoppingListItem> {
    try {
      if (Object.keys(updates).length === 0) {
        throw new Error('No valid fields to update.');
      }
      const setClauses = [];
      const values = [];
      let valueIndex = 1;

      if ('quantity' in updates) {
          setClauses.push(`quantity = $${valueIndex++}`);
          values.push(updates.quantity);
      }
      if ('is_purchased' in updates) {
          setClauses.push(`is_purchased = $${valueIndex++}`);
          values.push(updates.is_purchased);
      }
      if ('notes' in updates) {
          setClauses.push(`notes = $${valueIndex++}`);
          values.push(updates.notes);
      }

      if (setClauses.length === 0) {
          throw new Error("No valid fields to update.");
      }

      values.push(itemId);
      const query = `UPDATE public.shopping_list_items SET ${setClauses.join(', ')} WHERE shopping_list_item_id = $${valueIndex} RETURNING *`;

      const res = await this.db.query<ShoppingListItem>(query, values);
      // The patch requested this specific error handling.
      if (res.rowCount === 0) {
        throw new NotFoundError('Shopping list item not found.');
      }
      return res.rows[0];
    } catch (error) {
      // Re-throw specific, known errors to allow for more precise error handling in the calling code.
      if (error instanceof NotFoundError || (error instanceof Error && error.message.startsWith('No valid fields'))) {
        throw error;
      }
      logger.error({ err: error, itemId, updates }, 'Database error in updateShoppingListItem');
      throw new Error('Failed to update shopping list item.');
    }
  }

  /**
   * Archives a shopping list into a historical shopping trip.
   * @param shoppingListId The ID of the shopping list to complete.
   * @param userId The ID of the user owning the list.
   * @param totalSpentCents Optional total amount spent on the trip.
   * @returns A promise that resolves to the ID of the newly created shopping trip.
   */
  async completeShoppingList(shoppingListId: number, userId: string, logger: Logger, totalSpentCents?: number): Promise<number> {
      try {
          const res = await this.db.query<{ complete_shopping_list: number }>(
              'SELECT public.complete_shopping_list($1, $2, $3)',
              [shoppingListId, userId, totalSpentCents]
          );
          return res.rows[0].complete_shopping_list;
      } catch (error) {
          // The patch requested this specific error handling.
          if (error instanceof Error && 'code' in error && error.code === '23503') {
            throw new ForeignKeyConstraintError('The specified shopping list does not exist.');
          }
          logger.error({ err: error, shoppingListId, userId }, 'Database error in completeShoppingList');
          throw new Error('Failed to complete shopping list.');
      }
  }

  /**
   * Retrieves the historical shopping trips for a user, including all purchased items.
   * @param userId The ID of the user.
   * @returns A promise that resolves to an array of ShoppingTrip objects.
   */
  async getShoppingTripHistory(userId: string, logger: Logger): Promise<ShoppingTrip[]> {
      try {
          const query = `
              SELECT
                  st.shopping_trip_id, st.user_id, st.shopping_list_id, st.completed_at, st.total_spent_cents,
                  COALESCE(
                      json_agg(
                          json_build_object(
                              'shopping_trip_item_id', sti.shopping_trip_item_id,
                              'master_item_id', sti.master_item_id,
                              'custom_item_name', sti.custom_item_name,
                              'quantity', sti.quantity,
                              'price_paid_cents', sti.price_paid_cents,
                              'master_item_name', mgi.name
                          ) ORDER BY mgi.name ASC, sti.custom_item_name ASC
                      ) FILTER (WHERE sti.shopping_trip_item_id IS NOT NULL),
                      '[]'::json
                  ) as items
              FROM public.shopping_trips st
              LEFT JOIN public.shopping_trip_items sti ON st.shopping_trip_id = sti.shopping_trip_id
              LEFT JOIN public.master_grocery_items mgi ON sti.master_item_id = mgi.master_grocery_item_id
              WHERE st.user_id = $1
              GROUP BY st.shopping_trip_id
              ORDER BY st.completed_at DESC;
          `;
          const res = await this.db.query<ShoppingTrip>(query, [userId]);
          return res.rows;
      } catch (error) {
          logger.error({ err: error, userId }, 'Database error in getShoppingTripHistory');
          throw new Error('Failed to retrieve shopping trip history.');
      }
  }

  /**
   * Creates a new receipt record in the database.
   * @param userId The ID of the user uploading the receipt.
   * @param receiptImageUrl The URL where the receipt image is stored.
   * @returns A promise that resolves to the newly created Receipt object.
   */
  async createReceipt(userId: string, receiptImageUrl: string, logger: Logger): Promise<Receipt> {
      try {
          const res = await this.db.query<Receipt>(
              `INSERT INTO public.receipts (user_id, receipt_image_url, status)
               VALUES ($1, $2, 'pending')
               RETURNING *`,
              [userId, receiptImageUrl]
          );
          return res.rows[0];
      } catch (error) {
          // The patch requested this specific error handling.
          if (error instanceof Error && 'code' in error && error.code === '23503') {
            throw new ForeignKeyConstraintError('User not found');
          }
          logger.error({ err: error, userId, receiptImageUrl }, 'Database error in createReceipt');
          throw new Error('Failed to create receipt record.');
      }
  }

  /**
   * Processes extracted receipt items, updates the receipt status, and saves the items.
   * @param receiptId The ID of the receipt being processed.
   * @param items An array of items extracted from the receipt.
   * @returns A promise that resolves when the operation is complete.
   */
  async processReceiptItems(
    receiptId: number,
    items: Omit<ReceiptItem, 'receipt_item_id' | 'receipt_id' | 'status' | 'master_item_id' | 'product_id' | 'quantity'>[],
    logger: Logger
  ): Promise<void> {
    try {
      await withTransaction(async (client) => {
        const itemsWithQuantity = items.map(item => ({ ...item, quantity: 1 }));
        // Use the transactional client for this operation
        await client.query('SELECT public.process_receipt_items($1, $2, $3)', [receiptId, JSON.stringify(itemsWithQuantity), JSON.stringify(itemsWithQuantity)]);
        logger.info(`Successfully processed items for receipt ID: ${receiptId}`);
      });
    } catch (error) {
      logger.error({ err: error, receiptId }, 'Database transaction error in processReceiptItems');
      // After the transaction fails and is rolled back by withTransaction,
      // update the receipt status in a separate, non-transactional query.
      try {
        await this.db.query("UPDATE public.receipts SET status = 'failed' WHERE receipt_id = $1", [receiptId]);
      } catch (updateError) {
        logger.error({ updateError, receiptId }, 'Failed to update receipt status to "failed" after transaction rollback.');
      }
      throw new Error('Failed to process and save receipt items.');
    }
  }

  /**
   * Finds better deals for items on a recently processed receipt.
   * @param receiptId The ID of the receipt to check.
   * @returns A promise that resolves to an array of potential deals.
   */
  async findDealsForReceipt(receiptId: number, logger: Logger): Promise<ReceiptDeal[]> {
    try {
      const res = await this.db.query<ReceiptDeal>('SELECT * FROM public.find_deals_for_receipt_items($1)', [receiptId]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, receiptId }, 'Database error in findDealsForReceipt');
      throw new Error('Failed to find deals for receipt.');
    }
  }

  /**
   * Finds the owner of a specific receipt.
   * @param receiptId The ID of the receipt.
   * @returns A promise that resolves to an object containing the user_id, or undefined if not found.
   */
  async findReceiptOwner(receiptId: number, logger: Logger): Promise<{ user_id: string } | undefined> {
    try {
      const res = await this.db.query<{ user_id: string }>(
        'SELECT user_id FROM public.receipts WHERE receipt_id = $1',
        [receiptId]
      );
      return res.rows[0];
    } catch (error) {
      logger.error({ err: error, receiptId }, 'Database error in findReceiptOwner');
      throw new Error('Failed to retrieve receipt owner from database.');
    }
  }
}