// src/services/db/expiry.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool } from './connection.db';
import { NotFoundError, handleDbError } from './errors.db';
import type { Logger } from 'pino';
import type {
  StorageLocation,
  InventorySource,
  ExpirySource,
  AlertMethod,
  ExpiryAlertType,
  UserInventoryItem,
  AddInventoryItemRequest,
  UpdateInventoryItemRequest,
  ExpiryDateRange,
  AddExpiryRangeRequest,
  ExpiryAlertSettings,
  UpdateExpiryAlertSettingsRequest,
  ExpiryAlertLogRecord,
  InventoryQueryOptions,
  ExpiryRangeQueryOptions,
} from '../../types/expiry';

/**
 * Database row type for pantry_items table.
 */
interface PantryItemRow {
  pantry_item_id: number;
  user_id: string;
  master_item_id: number | null;
  quantity: number;
  unit: string | null;
  best_before_date: string | null;
  pantry_location_id: number | null;
  notification_sent_at: string | null;
  updated_at: string;
  purchase_date: string | null;
  source: string | null;
  receipt_item_id: number | null;
  product_id: number | null;
  expiry_source: string | null;
  is_consumed: boolean | null;
  consumed_at: string | null;
}

/**
 * Extended pantry item row with joined master item and category names.
 */
interface PantryItemWithDetailsRow extends PantryItemRow {
  item_name: string;
  category_name: string | null;
  location_name: string | null;
}

/**
 * Repository for expiry date tracking and inventory management.
 * Handles pantry items, expiry date ranges, alerts, and alert logs.
 */
export class ExpiryRepository {
  private db: Pick<Pool | PoolClient, 'query'>;

  constructor(db: Pick<Pool | PoolClient, 'query'> = getPool()) {
    this.db = db;
  }

  // ============================================================================
  // INVENTORY ITEMS (pantry_items)
  // ============================================================================

  /**
   * Adds a new item to the user's inventory.
   */
  async addInventoryItem(
    userId: string,
    item: AddInventoryItemRequest,
    logger: Logger,
  ): Promise<UserInventoryItem> {
    try {
      // Get item name from master_item if not provided directly
      let itemName = item.item_name;
      if (!itemName && item.master_item_id) {
        const nameRes = await this.db.query<{ name: string }>(
          `SELECT name FROM public.master_grocery_items WHERE master_grocery_item_id = $1`,
          [item.master_item_id],
        );
        if (nameRes.rowCount && nameRes.rowCount > 0) {
          itemName = nameRes.rows[0].name;
        }
      }

      // Get or create pantry location if provided
      let pantryLocationId: number | null = null;
      if (item.location) {
        const locationRes = await this.db.query<{ pantry_location_id: number }>(
          `INSERT INTO public.pantry_locations (user_id, name)
           VALUES ($1, $2)
           ON CONFLICT (user_id, name) DO UPDATE SET name = EXCLUDED.name
           RETURNING pantry_location_id`,
          [userId, item.location],
        );
        pantryLocationId = locationRes.rows[0].pantry_location_id;
      }

      const res = await this.db.query<PantryItemRow>(
        `INSERT INTO public.pantry_items
         (user_id, master_item_id, product_id, quantity, unit, best_before_date,
          purchase_date, source, pantry_location_id, expiry_source)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
         RETURNING *`,
        [
          userId,
          item.master_item_id || null,
          item.product_id || null,
          item.quantity ?? 1,
          item.unit || null,
          item.expiry_date || null,
          item.purchase_date || null,
          item.source,
          pantryLocationId,
          item.expiry_date ? 'manual' : null,
        ],
      );

      return this.mapPantryItemToInventoryItem(res.rows[0], itemName, item.location || null);
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in addInventoryItem',
        { userId, item },
        {
          fkMessage: 'The specified product or master item does not exist.',
          defaultMessage: 'Failed to add item to inventory.',
        },
      );
    }
  }

  /**
   * Updates an existing inventory item.
   */
  async updateInventoryItem(
    inventoryId: number,
    userId: string,
    updates: UpdateInventoryItemRequest,
    logger: Logger,
  ): Promise<UserInventoryItem> {
    try {
      // Build dynamic SET clause
      const setClauses: string[] = ['updated_at = NOW()'];
      const values: (string | number | boolean | null)[] = [];
      let paramIndex = 1;

      if (updates.quantity !== undefined) {
        setClauses.push(`quantity = $${paramIndex++}`);
        values.push(updates.quantity);
      }

      if (updates.unit !== undefined) {
        setClauses.push(`unit = $${paramIndex++}`);
        values.push(updates.unit);
      }

      if (updates.expiry_date !== undefined) {
        setClauses.push(`best_before_date = $${paramIndex++}`);
        values.push(updates.expiry_date);
        setClauses.push(`expiry_source = 'manual'`);
      }

      if (updates.location !== undefined) {
        // Get or create pantry location
        const locationRes = await this.db.query<{ pantry_location_id: number }>(
          `INSERT INTO public.pantry_locations (user_id, name)
           VALUES ($1, $2)
           ON CONFLICT (user_id, name) DO UPDATE SET name = EXCLUDED.name
           RETURNING pantry_location_id`,
          [userId, updates.location],
        );
        setClauses.push(`pantry_location_id = $${paramIndex++}`);
        values.push(locationRes.rows[0].pantry_location_id);
      }

      if (updates.notes !== undefined) {
        // Note: pantry_items doesn't have notes column, so we skip this
        // Could add via ALTER TABLE if needed
      }

      if (updates.is_consumed !== undefined) {
        setClauses.push(`is_consumed = $${paramIndex++}`);
        values.push(updates.is_consumed);
        if (updates.is_consumed) {
          setClauses.push(`consumed_at = NOW()`);
        } else {
          setClauses.push(`consumed_at = NULL`);
        }
      }

      values.push(inventoryId, userId);

      const res = await this.db.query<PantryItemWithDetailsRow>(
        `UPDATE public.pantry_items
         SET ${setClauses.join(', ')}
         WHERE pantry_item_id = $${paramIndex++} AND user_id = $${paramIndex}
         RETURNING *,
           (SELECT name FROM public.master_grocery_items WHERE master_grocery_item_id = pantry_items.master_item_id) AS item_name,
           (SELECT c.name FROM public.master_grocery_items mgi
            JOIN public.categories c ON mgi.category_id = c.category_id
            WHERE mgi.master_grocery_item_id = pantry_items.master_item_id) AS category_name,
           (SELECT name FROM public.pantry_locations WHERE pantry_location_id = pantry_items.pantry_location_id) AS location_name`,
        values,
      );

      if (res.rowCount === 0) {
        throw new NotFoundError('Inventory item not found or user does not have permission.');
      }

      return this.mapPantryItemWithDetailsToInventoryItem(res.rows[0]);
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in updateInventoryItem',
        { inventoryId, userId, updates },
        { defaultMessage: 'Failed to update inventory item.' },
      );
    }
  }

  /**
   * Marks an inventory item as consumed.
   */
  async markAsConsumed(inventoryId: number, userId: string, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query(
        `UPDATE public.pantry_items
         SET is_consumed = true, consumed_at = NOW(), updated_at = NOW()
         WHERE pantry_item_id = $1 AND user_id = $2`,
        [inventoryId, userId],
      );

      if (res.rowCount === 0) {
        throw new NotFoundError('Inventory item not found or user does not have permission.');
      }
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in markAsConsumed',
        { inventoryId, userId },
        {
          defaultMessage: 'Failed to mark item as consumed.',
        },
      );
    }
  }

  /**
   * Deletes an inventory item.
   */
  async deleteInventoryItem(inventoryId: number, userId: string, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query(
        `DELETE FROM public.pantry_items WHERE pantry_item_id = $1 AND user_id = $2`,
        [inventoryId, userId],
      );

      if (res.rowCount === 0) {
        throw new NotFoundError('Inventory item not found or user does not have permission.');
      }
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in deleteInventoryItem',
        { inventoryId, userId },
        {
          defaultMessage: 'Failed to delete inventory item.',
        },
      );
    }
  }

  /**
   * Gets a single inventory item by ID.
   */
  async getInventoryItemById(
    inventoryId: number,
    userId: string,
    logger: Logger,
  ): Promise<UserInventoryItem> {
    try {
      const res = await this.db.query<PantryItemWithDetailsRow>(
        `SELECT
          pi.*,
          COALESCE(mgi.name, 'Unknown Item') AS item_name,
          c.name AS category_name,
          pl.name AS location_name
         FROM public.pantry_items pi
         LEFT JOIN public.master_grocery_items mgi ON pi.master_item_id = mgi.master_grocery_item_id
         LEFT JOIN public.categories c ON mgi.category_id = c.category_id
         LEFT JOIN public.pantry_locations pl ON pi.pantry_location_id = pl.pantry_location_id
         WHERE pi.pantry_item_id = $1 AND pi.user_id = $2`,
        [inventoryId, userId],
      );

      if (res.rowCount === 0) {
        throw new NotFoundError('Inventory item not found.');
      }

      return this.mapPantryItemWithDetailsToInventoryItem(res.rows[0]);
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getInventoryItemById',
        { inventoryId, userId },
        {
          defaultMessage: 'Failed to get inventory item.',
        },
      );
    }
  }

  /**
   * Gets user's inventory with filtering and pagination.
   */
  async getInventory(
    options: InventoryQueryOptions,
    logger: Logger,
  ): Promise<{ items: UserInventoryItem[]; total: number }> {
    const {
      user_id,
      location,
      is_consumed = false,
      expiring_within_days,
      category_id,
      search,
      limit = 50,
      offset = 0,
      sort_by = 'expiry_date',
      sort_order = 'asc',
    } = options;

    try {
      // Build dynamic WHERE clause
      const conditions: string[] = ['pi.user_id = $1'];
      const params: (string | number | boolean)[] = [user_id];
      let paramIndex = 2;

      // Filter by consumed status
      if (is_consumed !== undefined) {
        conditions.push(`(pi.is_consumed = $${paramIndex++} OR pi.is_consumed IS NULL)`);
        params.push(is_consumed);
      }

      // Filter by location
      if (location) {
        conditions.push(`pl.name = $${paramIndex++}`);
        params.push(location);
      }

      // Filter by expiring within N days
      if (expiring_within_days !== undefined) {
        conditions.push(`pi.best_before_date IS NOT NULL`);
        conditions.push(`pi.best_before_date <= CURRENT_DATE + $${paramIndex++}`);
        params.push(expiring_within_days);
      }

      // Filter by category
      if (category_id) {
        conditions.push(`mgi.category_id = $${paramIndex++}`);
        params.push(category_id);
      }

      // Search by item name
      if (search) {
        conditions.push(`mgi.name ILIKE $${paramIndex++}`);
        params.push(`%${search}%`);
      }

      const whereClause = conditions.join(' AND ');

      // Determine sort column
      let sortColumn: string;
      switch (sort_by) {
        case 'expiry_date':
          sortColumn = 'pi.best_before_date';
          break;
        case 'purchase_date':
          sortColumn = 'pi.purchase_date';
          break;
        case 'item_name':
          sortColumn = 'mgi.name';
          break;
        default:
          sortColumn = 'pi.updated_at';
      }

      // Get total count
      const countQuery = `
        SELECT COUNT(*)
        FROM public.pantry_items pi
        LEFT JOIN public.master_grocery_items mgi ON pi.master_item_id = mgi.master_grocery_item_id
        LEFT JOIN public.pantry_locations pl ON pi.pantry_location_id = pl.pantry_location_id
        WHERE ${whereClause}
      `;
      const countRes = await this.db.query<{ count: string }>(countQuery, params);
      const total = parseInt(countRes.rows[0].count, 10);

      // Get paginated results
      const dataParams = [...params, limit, offset];
      const dataQuery = `
        SELECT
          pi.*,
          COALESCE(mgi.name, 'Unknown Item') AS item_name,
          c.name AS category_name,
          pl.name AS location_name
        FROM public.pantry_items pi
        LEFT JOIN public.master_grocery_items mgi ON pi.master_item_id = mgi.master_grocery_item_id
        LEFT JOIN public.categories c ON mgi.category_id = c.category_id
        LEFT JOIN public.pantry_locations pl ON pi.pantry_location_id = pl.pantry_location_id
        WHERE ${whereClause}
        ORDER BY ${sortColumn} ${sort_order === 'desc' ? 'DESC' : 'ASC'} NULLS LAST
        LIMIT $${paramIndex++} OFFSET $${paramIndex}
      `;
      const dataRes = await this.db.query<PantryItemWithDetailsRow>(dataQuery, dataParams);

      return {
        items: dataRes.rows.map((row) => this.mapPantryItemWithDetailsToInventoryItem(row)),
        total,
      };
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getInventory',
        { options },
        {
          defaultMessage: 'Failed to get inventory.',
        },
      );
    }
  }

  /**
   * Gets items expiring within a specified number of days.
   */
  async getExpiringItems(
    userId: string,
    daysAhead: number,
    logger: Logger,
  ): Promise<UserInventoryItem[]> {
    try {
      const res = await this.db.query<PantryItemWithDetailsRow>(
        `SELECT
          pi.*,
          COALESCE(mgi.name, 'Unknown Item') AS item_name,
          c.name AS category_name,
          pl.name AS location_name
         FROM public.pantry_items pi
         LEFT JOIN public.master_grocery_items mgi ON pi.master_item_id = mgi.master_grocery_item_id
         LEFT JOIN public.categories c ON mgi.category_id = c.category_id
         LEFT JOIN public.pantry_locations pl ON pi.pantry_location_id = pl.pantry_location_id
         WHERE pi.user_id = $1
           AND pi.best_before_date IS NOT NULL
           AND pi.best_before_date >= CURRENT_DATE
           AND pi.best_before_date <= CURRENT_DATE + $2::integer
           AND (pi.is_consumed = false OR pi.is_consumed IS NULL)
         ORDER BY pi.best_before_date ASC`,
        [userId, daysAhead],
      );

      return res.rows.map((row) => this.mapPantryItemWithDetailsToInventoryItem(row));
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getExpiringItems',
        { userId, daysAhead },
        {
          defaultMessage: 'Failed to get expiring items.',
        },
      );
    }
  }

  /**
   * Gets items that are already expired.
   */
  async getExpiredItems(userId: string, logger: Logger): Promise<UserInventoryItem[]> {
    try {
      const res = await this.db.query<PantryItemWithDetailsRow>(
        `SELECT
          pi.*,
          COALESCE(mgi.name, 'Unknown Item') AS item_name,
          c.name AS category_name,
          pl.name AS location_name
         FROM public.pantry_items pi
         LEFT JOIN public.master_grocery_items mgi ON pi.master_item_id = mgi.master_grocery_item_id
         LEFT JOIN public.categories c ON mgi.category_id = c.category_id
         LEFT JOIN public.pantry_locations pl ON pi.pantry_location_id = pl.pantry_location_id
         WHERE pi.user_id = $1
           AND pi.best_before_date IS NOT NULL
           AND pi.best_before_date < CURRENT_DATE
           AND (pi.is_consumed = false OR pi.is_consumed IS NULL)
         ORDER BY pi.best_before_date ASC`,
        [userId],
      );

      return res.rows.map((row) => this.mapPantryItemWithDetailsToInventoryItem(row));
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getExpiredItems',
        { userId },
        {
          defaultMessage: 'Failed to get expired items.',
        },
      );
    }
  }

  // ============================================================================
  // EXPIRY DATE RANGES (Reference Data)
  // ============================================================================

  /**
   * Gets the expiry date range for an item based on master item, category, or pattern.
   * Returns the most specific match found.
   */
  async getExpiryRangeForItem(
    storageLocation: StorageLocation,
    logger: Logger,
    options: {
      masterItemId?: number;
      categoryId?: number;
      itemName?: string;
    } = {},
  ): Promise<ExpiryDateRange | null> {
    const { masterItemId, categoryId, itemName } = options;

    try {
      // Try to find by master_item_id first (most specific)
      if (masterItemId) {
        const res = await this.db.query<ExpiryDateRange>(
          `SELECT * FROM public.expiry_date_ranges
           WHERE master_item_id = $1 AND storage_location = $2`,
          [masterItemId, storageLocation],
        );
        if (res.rowCount && res.rowCount > 0) {
          return res.rows[0];
        }
      }

      // Try category_id
      if (categoryId) {
        const res = await this.db.query<ExpiryDateRange>(
          `SELECT * FROM public.expiry_date_ranges
           WHERE category_id = $1 AND storage_location = $2 AND master_item_id IS NULL`,
          [categoryId, storageLocation],
        );
        if (res.rowCount && res.rowCount > 0) {
          return res.rows[0];
        }
      }

      // Try item_pattern matching
      if (itemName) {
        const res = await this.db.query<ExpiryDateRange>(
          `SELECT * FROM public.expiry_date_ranges
           WHERE item_pattern IS NOT NULL
             AND storage_location = $1
             AND $2 ~* item_pattern
           ORDER BY LENGTH(item_pattern) DESC
           LIMIT 1`,
          [storageLocation, itemName],
        );
        if (res.rowCount && res.rowCount > 0) {
          return res.rows[0];
        }
      }

      return null;
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getExpiryRangeForItem',
        { storageLocation, masterItemId, categoryId, itemName },
        { defaultMessage: 'Failed to get expiry range for item.' },
      );
    }
  }

  /**
   * Adds a new expiry date range (admin operation).
   */
  async addExpiryRange(range: AddExpiryRangeRequest, logger: Logger): Promise<ExpiryDateRange> {
    try {
      const res = await this.db.query<ExpiryDateRange>(
        `INSERT INTO public.expiry_date_ranges
         (master_item_id, category_id, item_pattern, storage_location, min_days, max_days, typical_days, notes, source)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
         RETURNING *`,
        [
          range.master_item_id || null,
          range.category_id || null,
          range.item_pattern || null,
          range.storage_location,
          range.min_days,
          range.max_days,
          range.typical_days,
          range.notes || null,
          range.source || null,
        ],
      );

      return res.rows[0];
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in addExpiryRange',
        { range },
        {
          fkMessage: 'The specified master item or category does not exist.',
          uniqueMessage: 'An expiry range for this item/location combination already exists.',
          checkMessage: 'Invalid storage location or day values.',
          defaultMessage: 'Failed to add expiry range.',
        },
      );
    }
  }

  /**
   * Gets all expiry ranges with optional filtering.
   */
  async getExpiryRanges(
    options: ExpiryRangeQueryOptions,
    logger: Logger,
  ): Promise<{ ranges: ExpiryDateRange[]; total: number }> {
    const {
      master_item_id,
      category_id,
      storage_location,
      source,
      limit = 50,
      offset = 0,
    } = options;

    try {
      const conditions: string[] = [];
      const params: (string | number)[] = [];
      let paramIndex = 1;

      if (master_item_id) {
        conditions.push(`master_item_id = $${paramIndex++}`);
        params.push(master_item_id);
      }

      if (category_id) {
        conditions.push(`category_id = $${paramIndex++}`);
        params.push(category_id);
      }

      if (storage_location) {
        conditions.push(`storage_location = $${paramIndex++}`);
        params.push(storage_location);
      }

      if (source) {
        conditions.push(`source = $${paramIndex++}`);
        params.push(source);
      }

      const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

      // Get total count
      const countRes = await this.db.query<{ count: string }>(
        `SELECT COUNT(*) FROM public.expiry_date_ranges ${whereClause}`,
        params,
      );
      const total = parseInt(countRes.rows[0].count, 10);

      // Get paginated results
      const dataParams = [...params, limit, offset];
      const dataRes = await this.db.query<ExpiryDateRange>(
        `SELECT * FROM public.expiry_date_ranges
         ${whereClause}
         ORDER BY storage_location, master_item_id NULLS LAST, category_id NULLS LAST
         LIMIT $${paramIndex++} OFFSET $${paramIndex}`,
        dataParams,
      );

      return { ranges: dataRes.rows, total };
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getExpiryRanges',
        { options },
        {
          defaultMessage: 'Failed to get expiry ranges.',
        },
      );
    }
  }

  // ============================================================================
  // EXPIRY ALERTS
  // ============================================================================

  /**
   * Gets a user's expiry alert settings.
   */
  async getUserAlertSettings(userId: string, logger: Logger): Promise<ExpiryAlertSettings[]> {
    try {
      const res = await this.db.query<ExpiryAlertSettings>(
        `SELECT * FROM public.expiry_alerts WHERE user_id = $1 ORDER BY alert_method`,
        [userId],
      );
      return res.rows;
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getUserAlertSettings',
        { userId },
        {
          defaultMessage: 'Failed to get alert settings.',
        },
      );
    }
  }

  /**
   * Creates or updates a user's expiry alert settings for a specific method.
   */
  async upsertAlertSettings(
    userId: string,
    alertMethod: AlertMethod,
    settings: UpdateExpiryAlertSettingsRequest,
    logger: Logger,
  ): Promise<ExpiryAlertSettings> {
    try {
      const res = await this.db.query<ExpiryAlertSettings>(
        `INSERT INTO public.expiry_alerts (user_id, alert_method, days_before_expiry, is_enabled)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (user_id, alert_method) DO UPDATE SET
           days_before_expiry = COALESCE($3, expiry_alerts.days_before_expiry),
           is_enabled = COALESCE($4, expiry_alerts.is_enabled),
           updated_at = NOW()
         RETURNING *`,
        [userId, alertMethod, settings.days_before_expiry ?? 3, settings.is_enabled ?? true],
      );

      return res.rows[0];
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in upsertAlertSettings',
        { userId, alertMethod, settings },
        {
          checkMessage: 'Invalid alert method or days value.',
          defaultMessage: 'Failed to update alert settings.',
        },
      );
    }
  }

  /**
   * Logs a sent expiry alert.
   */
  async logAlert(
    userId: string,
    alertType: ExpiryAlertType,
    alertMethod: AlertMethod,
    itemName: string,
    logger: Logger,
    options: {
      pantryItemId?: number | null;
      expiryDate?: string | null;
      daysUntilExpiry?: number | null;
    } = {},
  ): Promise<ExpiryAlertLogRecord> {
    try {
      const res = await this.db.query<ExpiryAlertLogRecord>(
        `INSERT INTO public.expiry_alert_log
         (user_id, pantry_item_id, alert_type, alert_method, item_name, expiry_date, days_until_expiry)
         VALUES ($1, $2, $3, $4, $5, $6, $7)
         RETURNING *`,
        [
          userId,
          options.pantryItemId || null,
          alertType,
          alertMethod,
          itemName,
          options.expiryDate || null,
          options.daysUntilExpiry ?? null,
        ],
      );

      return res.rows[0];
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in logAlert',
        { userId, alertType, alertMethod, itemName },
        {
          defaultMessage: 'Failed to log expiry alert.',
        },
      );
    }
  }

  /**
   * Gets users who have enabled alerts and have expiring items.
   * Used by the alert worker to batch process notifications.
   */
  async getUsersWithExpiringItems(
    logger: Logger,
  ): Promise<
    Array<{ user_id: string; email: string; alert_method: AlertMethod; days_before_expiry: number }>
  > {
    try {
      const res = await this.db.query<{
        user_id: string;
        email: string;
        alert_method: AlertMethod;
        days_before_expiry: number;
      }>(
        `SELECT DISTINCT
          ea.user_id,
          u.email,
          ea.alert_method,
          ea.days_before_expiry
         FROM public.expiry_alerts ea
         JOIN public.users u ON ea.user_id = u.user_id
         JOIN public.pantry_items pi ON ea.user_id = pi.user_id
         WHERE ea.is_enabled = true
           AND pi.best_before_date IS NOT NULL
           AND pi.best_before_date <= CURRENT_DATE + ea.days_before_expiry
           AND (pi.is_consumed = false OR pi.is_consumed IS NULL)
           AND (ea.last_alert_sent_at IS NULL OR ea.last_alert_sent_at < CURRENT_DATE)`,
      );

      return res.rows;
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getUsersWithExpiringItems',
        {},
        {
          defaultMessage: 'Failed to get users with expiring items.',
        },
      );
    }
  }

  /**
   * Updates the last_alert_sent_at timestamp for a user's alert setting.
   */
  async markAlertSent(userId: string, alertMethod: AlertMethod, logger: Logger): Promise<void> {
    try {
      await this.db.query(
        `UPDATE public.expiry_alerts
         SET last_alert_sent_at = NOW(), updated_at = NOW()
         WHERE user_id = $1 AND alert_method = $2`,
        [userId, alertMethod],
      );
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in markAlertSent',
        { userId, alertMethod },
        {
          defaultMessage: 'Failed to mark alert as sent.',
        },
      );
    }
  }

  // ============================================================================
  // HELPER METHODS
  // ============================================================================

  /**
   * Maps a basic pantry item row to UserInventoryItem.
   */
  private mapPantryItemToInventoryItem(
    row: PantryItemRow,
    itemName: string,
    locationName: string | null = null,
  ): UserInventoryItem {
    const daysUntilExpiry = row.best_before_date
      ? Math.ceil((new Date(row.best_before_date).getTime() - Date.now()) / (1000 * 60 * 60 * 24))
      : null;

    return {
      inventory_id: row.pantry_item_id,
      user_id: row.user_id,
      product_id: row.product_id,
      master_item_id: row.master_item_id,
      item_name: itemName,
      quantity: Number(row.quantity),
      unit: row.unit,
      purchase_date: row.purchase_date,
      expiry_date: row.best_before_date,
      source: (row.source as InventorySource) || 'manual',
      location: locationName as StorageLocation | null,
      notes: null,
      is_consumed: row.is_consumed ?? false,
      consumed_at: row.consumed_at,
      expiry_source: row.expiry_source as ExpirySource | null,
      receipt_item_id: row.receipt_item_id,
      pantry_location_id: row.pantry_location_id,
      notification_sent_at: row.notification_sent_at,
      created_at: row.updated_at, // pantry_items doesn't have created_at
      updated_at: row.updated_at,
      days_until_expiry: daysUntilExpiry,
      expiry_status: this.calculateExpiryStatus(daysUntilExpiry),
    };
  }

  // ============================================================================
  // RECIPE SUGGESTIONS
  // ============================================================================

  /**
   * Finds recipes that use the user's expiring inventory items.
   * Returns recipes ranked by how many expiring items they use.
   * @param userId The user's ID
   * @param daysAhead Number of days to look ahead for expiring items
   * @param limit Maximum number of recipes to return
   * @param offset Offset for pagination
   * @param logger Pino logger instance
   * @returns Recipes with their matching expiring ingredients and counts
   */
  async getRecipesForExpiringItems(
    userId: string,
    daysAhead: number,
    limit: number,
    offset: number,
    logger: Logger,
  ): Promise<{
    recipes: Array<{
      recipe_id: number;
      recipe_name: string;
      description: string | null;
      prep_time_minutes: number | null;
      cook_time_minutes: number | null;
      servings: number | null;
      photo_url: string | null;
      matching_master_item_ids: number[];
      match_count: number;
    }>;
    total: number;
  }> {
    try {
      // First, get the expiring items' master_item_ids
      const expiringItemsQuery = `
        SELECT DISTINCT pi.master_item_id
        FROM public.pantry_items pi
        WHERE pi.user_id = $1
          AND pi.master_item_id IS NOT NULL
          AND pi.best_before_date IS NOT NULL
          AND pi.best_before_date >= CURRENT_DATE
          AND pi.best_before_date <= CURRENT_DATE + $2::integer
          AND (pi.is_consumed = false OR pi.is_consumed IS NULL)
      `;
      const expiringRes = await this.db.query<{ master_item_id: number }>(expiringItemsQuery, [
        userId,
        daysAhead,
      ]);

      if (expiringRes.rows.length === 0) {
        return { recipes: [], total: 0 };
      }

      const masterItemIds = expiringRes.rows.map((row) => row.master_item_id);

      // Get total count of matching recipes
      const countQuery = `
        SELECT COUNT(DISTINCT r.recipe_id) as total
        FROM public.recipes r
        INNER JOIN public.recipe_ingredients ri ON r.recipe_id = ri.recipe_id
        WHERE ri.master_item_id = ANY($1)
          AND r.status = 'public'
      `;
      const countRes = await this.db.query<{ total: string }>(countQuery, [masterItemIds]);
      const total = parseInt(countRes.rows[0].total, 10);

      // Get recipes that use any of the expiring items, ranked by match count
      const recipesQuery = `
        SELECT
          r.recipe_id,
          r.name as recipe_name,
          r.description,
          r.prep_time_minutes,
          r.cook_time_minutes,
          r.servings,
          r.photo_url,
          array_agg(DISTINCT ri.master_item_id) FILTER (WHERE ri.master_item_id = ANY($1)) as matching_master_item_ids,
          COUNT(DISTINCT ri.master_item_id) FILTER (WHERE ri.master_item_id = ANY($1)) as match_count
        FROM public.recipes r
        INNER JOIN public.recipe_ingredients ri ON r.recipe_id = ri.recipe_id
        WHERE ri.master_item_id = ANY($1)
          AND r.status = 'public'
        GROUP BY r.recipe_id, r.name, r.description, r.prep_time_minutes, r.cook_time_minutes, r.servings, r.photo_url
        ORDER BY match_count DESC, r.name ASC
        LIMIT $2 OFFSET $3
      `;
      const recipesRes = await this.db.query<{
        recipe_id: number;
        recipe_name: string;
        description: string | null;
        prep_time_minutes: number | null;
        cook_time_minutes: number | null;
        servings: number | null;
        photo_url: string | null;
        matching_master_item_ids: number[];
        match_count: string;
      }>(recipesQuery, [masterItemIds, limit, offset]);

      return {
        recipes: recipesRes.rows.map((row) => ({
          recipe_id: row.recipe_id,
          recipe_name: row.recipe_name,
          description: row.description,
          prep_time_minutes: row.prep_time_minutes,
          cook_time_minutes: row.cook_time_minutes,
          servings: row.servings,
          photo_url: row.photo_url,
          matching_master_item_ids: row.matching_master_item_ids || [],
          match_count: parseInt(row.match_count, 10),
        })),
        total,
      };
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in getRecipesForExpiringItems',
        { userId, daysAhead },
        {
          defaultMessage: 'Failed to get recipe suggestions for expiring items.',
        },
      );
    }
  }

  // ============================================================================
  // PRIVATE HELPERS
  // ============================================================================

  /**
   * Maps a pantry item row with joined details to UserInventoryItem.
   */
  private mapPantryItemWithDetailsToInventoryItem(
    row: PantryItemWithDetailsRow,
  ): UserInventoryItem {
    const daysUntilExpiry = row.best_before_date
      ? Math.ceil((new Date(row.best_before_date).getTime() - Date.now()) / (1000 * 60 * 60 * 24))
      : null;

    return {
      inventory_id: row.pantry_item_id,
      user_id: row.user_id,
      product_id: row.product_id,
      master_item_id: row.master_item_id,
      item_name: row.item_name,
      quantity: Number(row.quantity),
      unit: row.unit,
      purchase_date: row.purchase_date,
      expiry_date: row.best_before_date,
      source: (row.source as InventorySource) || 'manual',
      location: row.location_name as StorageLocation | null,
      notes: null,
      is_consumed: row.is_consumed ?? false,
      consumed_at: row.consumed_at,
      expiry_source: row.expiry_source as ExpirySource | null,
      receipt_item_id: row.receipt_item_id,
      pantry_location_id: row.pantry_location_id,
      notification_sent_at: row.notification_sent_at,
      created_at: row.updated_at,
      updated_at: row.updated_at,
      days_until_expiry: daysUntilExpiry,
      expiry_status: this.calculateExpiryStatus(daysUntilExpiry),
      master_item_name: row.item_name,
      category_name: row.category_name ?? undefined,
    };
  }

  /**
   * Calculates the expiry status based on days until expiry.
   */
  private calculateExpiryStatus(
    daysUntilExpiry: number | null,
  ): 'fresh' | 'expiring_soon' | 'expired' | 'unknown' {
    if (daysUntilExpiry === null) {
      return 'unknown';
    }
    if (daysUntilExpiry < 0) {
      return 'expired';
    }
    if (daysUntilExpiry <= 7) {
      return 'expiring_soon';
    }
    return 'fresh';
  }
}
