flyer-crawler.projectium.com/src/types.ts

// src/types.ts
export interface Store {
  readonly store_id: number;
  name: string;
  logo_url?: string | null;
  readonly created_by?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export type FlyerStatus = 'processed' | 'needs_review' | 'archived';

export interface Flyer {
  readonly flyer_id: number;
  file_name: string;
  image_url: string;
  icon_url: string; // URL for the 64x64 icon version of the flyer
  readonly checksum?: string;
  readonly store_id?: number; // Legacy field - kept for backward compatibility
  valid_from?: string | null;
  valid_to?: string | null;
  store_address?: string | null; // Legacy field - will be deprecated
  status: FlyerStatus;
  item_count: number;
  readonly uploaded_by?: string | null; // UUID of the user who uploaded it, can be null for anonymous uploads

  // Store relationship (legacy - single store)
  store?: Store;

  // Store locations relationship (many-to-many via flyer_locations table)
  // This is the correct relationship - a flyer can be valid at multiple store locations
  locations?: Array<{
    store_location_id: number;
    store: Store;
    address: Address;
  }>;

  readonly created_at: string;
  readonly updated_at: string;
}

/**
 * Represents the data required to insert a new flyer into the database.
 * It's a subset of the full Flyer type, excluding generated fields like `flyer_id`.
 */
export interface FlyerInsert {
  file_name: string;
  image_url: string;
  icon_url: string;
  checksum: string;
  store_name: string;
  valid_from: string | null;
  valid_to: string | null;
  store_address: string | null;
  status: FlyerStatus;
  item_count: number;
  uploaded_by?: string | null;
}

/**
 * Represents the data required to insert a new flyer into the database, with store_id resolved.
 * This is an internal type for the database service.
 */
export type FlyerDbInsert = Omit<FlyerInsert, 'store_name'> & { store_id: number };

/**
 * Represents the data required to insert a new flyer item into the database.
 * It's a subset of the full FlyerItem type.
 */
export type FlyerItemInsert = Omit<
  FlyerItem,
  'flyer_item_id' | 'flyer_id' | 'created_at' | 'updated_at'
>;

export interface UnitPrice {
  value: number;
  unit: string; // e.g., 'g', 'kg', 'ml', 'l', 'oz', 'lb', 'each'
}

export interface FlyerItem {
  readonly flyer_item_id: number;
  readonly flyer_id: number;
  item: string;
  price_display: string;
  price_in_cents?: number | null;
  quantity: string;
  quantity_num?: number | null;
  master_item_id?: number; // Can be updated by admin correction
  master_item_name?: string | null;
  category_id?: number | null; // Can be updated by admin correction
  category_name?: string | null;
  unit_price?: UnitPrice | null;
  product_id?: number | null; // Can be updated by admin correction
  readonly view_count: number;
  readonly click_count: number;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface MasterGroceryItem {
  readonly master_grocery_item_id: number;
  name: string;
  category_id?: number | null; // Can be updated by admin
  category_name?: string | null;
  is_allergen?: boolean;
  allergy_info?: unknown | null; // JSONB
  readonly created_by?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Category {
  readonly category_id: number;
  name: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Brand {
  readonly brand_id: number;
  name: string;
  logo_url?: string | null;
  readonly store_id?: number | null;
  store_name?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Product {
  readonly product_id: number;
  readonly master_item_id: number;
  readonly brand_id?: number | null;
  name: string;
  description?: string | null;
  size?: string | null;
  upc_code?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface DealItem {
  item: string;
  price_display: string;
  price_in_cents: number | null;
  quantity: string;
  storeName: string;
  master_item_name?: string | null;
  unit_price?: UnitPrice | null;
}

// User-specific types
export interface User {
  readonly user_id: string; // UUID
  email: string;
  readonly created_at: string;
  readonly updated_at: string;
}

/**
 * Represents the user data including the password hash, used for authentication checks.
 * This type is internal to the backend and should not be sent to the client.
 */
export interface UserWithPasswordHash extends User {
  password_hash: string | null;
  readonly failed_login_attempts: number;
  readonly last_failed_login: string | null; // TIMESTAMPTZ
  readonly last_login_at?: string | null; // TIMESTAMPTZ
  readonly last_login_ip?: string | null;
}
export interface Profile {
  full_name?: string | null;
  avatar_url?: string | null;
  address_id?: number | null; // Can be updated
  readonly points: number;
  readonly role: 'admin' | 'user';
  preferences?: {
    darkMode?: boolean;
    unitSystem?: 'metric' | 'imperial';
  } | null;
  readonly created_by?: string | null;
  readonly updated_by?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

/**
 * Represents the combined user and profile data object returned by the backend's /users/profile endpoint.
 * It embeds the User object within the Profile object.
 * It also includes the full Address object if one is associated with the profile.
 */
export type UserProfile = Profile & {
  user: User;
  address?: Address | null;
};

export interface SuggestedCorrection {
  readonly suggested_correction_id: number;
  readonly flyer_item_id: number;
  readonly user_id: string;
  correction_type: string;
  suggested_value: string;
  status: 'pending' | 'approved' | 'rejected';
  readonly reviewed_at?: string | null;
  reviewed_notes?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
  // Joined data
  user_email?: string;
  flyer_item_name?: string;
  flyer_item_price_display?: string;
}

/**
 * Represents the complete data package for a user export.
 */
export interface UserDataExport {
  profile: Profile;
  watchedItems: MasterGroceryItem[];
  shoppingLists: ShoppingList[];
  // Add other user-specific data models here as they are implemented
  // e.g., pantryItems: PantryItem[];
  // e.g., recipes: Recipe[];
}

export interface UserAlert {
  readonly user_alert_id: number;
  readonly user_watched_item_id: number;
  alert_type: 'PRICE_BELOW' | 'PERCENT_OFF_AVERAGE';
  threshold_value: number;
  is_active: boolean;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Notification {
  readonly notification_id: number;
  readonly user_id: string; // UUID
  content: string;
  link_url?: string | null;
  is_read: boolean;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface ShoppingList {
  readonly shopping_list_id: number;
  readonly user_id: string; // UUID
  name: string;
  items: ShoppingListItem[]; // Nested items
  readonly created_at: string;
  readonly updated_at: string;
}

export interface ShoppingListItem {
  readonly shopping_list_item_id: number;
  readonly shopping_list_id: number;
  readonly master_item_id?: number | null;
  custom_item_name?: string | null;
  quantity: number;
  is_purchased: boolean;
  notes?: string | null;
  readonly added_at: string;
  readonly updated_at: string;
  // Joined data for display
  master_item?: {
    name: string;
  } | null;
}

export interface UserSubmittedPrice {
  readonly user_submitted_price_id: number;
  readonly user_id: string; // UUID
  readonly master_item_id: number;
  readonly store_location_id: number; // Specific store location (provides geographic specificity)
  price_in_cents: number;
  photo_url?: string | null;
  readonly upvotes: number;
  readonly downvotes: number;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface ItemPriceHistory {
  readonly item_price_history_id: number;
  readonly master_item_id: number;
  summary_date: string; // DATE
  readonly store_location_id?: number | null;
  min_price_in_cents?: number | null;
  max_price_in_cents?: number | null;
  avg_price_in_cents?: number | null;
  data_points_count: number;
  readonly created_at: string;
  readonly updated_at: string;
}

/**
 * Represents a single data point for an item's price on a specific day.
 * This is the raw structure returned by the price history API endpoint.
 */
export interface HistoricalPriceDataPoint {
  master_item_id: number;
  avg_price_in_cents: number | null;
  summary_date: string; // DATE
}

export interface MasterItemAlias {
  readonly master_item_alias_id: number;
  readonly master_item_id: number;
  alias: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Recipe {
  readonly recipe_id: number;
  readonly user_id?: string | null; // UUID
  readonly original_recipe_id?: number | null;
  name: string;
  description?: string | null;
  instructions?: string | null;
  prep_time_minutes?: number | null;
  cook_time_minutes?: number | null;
  servings?: number | null;
  photo_url?: string | null;
  calories_per_serving?: number | null;
  protein_grams?: number | null;
  fat_grams?: number | null;
  carb_grams?: number | null;
  readonly avg_rating: number;
  status: 'private' | 'pending_review' | 'public' | 'rejected';
  readonly rating_count: number;
  readonly fork_count: number;
  comments?: RecipeComment[];
  ingredients?: RecipeIngredient[];
  readonly created_at: string;
  readonly updated_at: string;
}

export interface RecipeIngredient {
  readonly recipe_ingredient_id: number;
  readonly recipe_id: number;
  readonly master_item_id: number;
  quantity: number;
  unit: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface RecipeIngredientSubstitution {
  readonly recipe_ingredient_substitution_id: number;
  readonly recipe_ingredient_id: number;
  readonly substitute_master_item_id: number;
  notes?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Tag {
  readonly tag_id: number;
  name: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface RecipeTag {
  recipe_id: number;
  tag_id: number;
}

export interface RecipeRating {
  readonly recipe_rating_id: number;
  readonly recipe_id: number;
  readonly user_id: string; // UUID
  rating: number;
  comment?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface RecipeComment {
  readonly recipe_comment_id: number;
  readonly recipe_id: number;
  readonly user_id: string; // UUID
  readonly parent_comment_id?: number | null;
  content: string;
  status: 'visible' | 'hidden' | 'reported';
  readonly created_at: string;
  readonly updated_at: string;
  user_full_name?: string; // Joined data
  user_avatar_url?: string; // Joined data
}

export interface MenuPlan {
  readonly menu_plan_id: number;
  readonly user_id: string; // UUID
  name: string;
  start_date: string; // DATE
  end_date: string; // DATE
  planned_meals?: PlannedMeal[];
  readonly created_at: string;
  readonly updated_at: string;
}

export interface SharedMenuPlan {
  readonly shared_menu_plan_id: number;
  readonly menu_plan_id: number;
  readonly shared_by_user_id: string; // UUID
  readonly shared_with_user_id: string; // UUID
  permission_level: 'view' | 'edit';
  readonly created_at: string;
  readonly updated_at: string;
}

export interface PlannedMeal {
  readonly planned_meal_id: number;
  readonly menu_plan_id: number;
  readonly recipe_id: number;
  plan_date: string; // DATE
  meal_type: string;
  servings_to_cook?: number | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface PantryItem {
  readonly pantry_item_id: number;
  readonly user_id: string; // UUID
  readonly master_item_id: number;
  quantity: number;
  unit?: string | null;
  best_before_date?: string | null; // DATE
  pantry_location_id?: number | null;
  readonly notification_sent_at?: string | null; // TIMESTAMPTZ
  purchase_date?: string | null; // DATE
  source?: string | null; // 'manual', 'receipt_scan', 'upc_scan'
  receipt_item_id?: number | null;
  product_id?: number | null;
  expiry_source?: string | null; // 'manual', 'calculated', 'package', 'receipt'
  is_consumed?: boolean;
  consumed_at?: string | null; // TIMESTAMPTZ
  readonly updated_at: string;
}

export interface UserItemAlias {
  readonly user_item_alias_id: number;
  readonly user_id: string; // UUID
  readonly master_item_id: number;
  alias: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface FavoriteRecipe {
  readonly user_id: string; // UUID
  readonly recipe_id: number;
  readonly created_at: string;
}

export interface FavoriteStore {
  readonly user_id: string; // UUID
  readonly store_id: number;
  readonly created_at: string;
}

export interface RecipeCollection {
  readonly recipe_collection_id: number;
  readonly user_id: string; // UUID
  name: string;
  description?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface RecipeCollectionItem {
  readonly collection_id: number;
  readonly recipe_id: number;
  readonly added_at: string;
}

export interface SharedShoppingList {
  readonly shared_shopping_list_id: number;
  readonly shopping_list_id: number;
  readonly shared_by_user_id: string; // UUID
  readonly shared_with_user_id: string; // UUID
  permission_level: 'view' | 'edit';
  readonly created_at: string;
  readonly updated_at: string;
}

export interface SharedRecipeCollection {
  readonly shared_collection_id: number;
  readonly recipe_collection_id: number;
  readonly shared_by_user_id: string; // UUID
  readonly shared_with_user_id: string; // UUID
  permission_level: 'view' | 'edit';
  readonly created_at: string;
  readonly updated_at: string;
}

export interface DietaryRestriction {
  readonly dietary_restriction_id: number;
  name: string;
  type: 'diet' | 'allergy';
  readonly created_at: string;
  readonly updated_at: string;
}

export interface UserDietaryRestriction {
  readonly user_id: string; // UUID
  readonly restriction_id: number;
  readonly created_at: string;
}

export interface Appliance {
  readonly appliance_id: number;
  name: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface UserAppliance {
  readonly user_id: string; // UUID
  readonly appliance_id: number;
  readonly created_at: string;
}

export interface RecipeAppliance {
  readonly recipe_id: number;
  readonly appliance_id: number;
  readonly created_at: string;
}

export interface UserFollow {
  readonly follower_id: string; // UUID
  readonly following_id: string; // UUID
  readonly created_at: string;
}
/**
 * The list of possible actions for an activity log.
 * Using a specific type union instead of a generic 'string' allows for better type checking.
 */
export type ActivityLogAction =
  | 'flyer_processed'
  | 'recipe_created'
  | 'user_registered'
  | 'recipe_favorited'
  | 'list_shared'
  | 'login_failed_password'
  | 'password_reset';

/**
 * Base interface for all log items, containing common properties.
 */
interface ActivityLogItemBase {
  readonly activity_log_id: number;
  readonly user_id: string | null;
  action: ActivityLogAction;
  display_text: string;
  icon?: string | null;
  readonly created_at: string;
  readonly updated_at: string;
  // Joined data for display in feeds
  user_full_name?: string;
  user_avatar_url?: string;
}

// --- Discriminated Union for Activity Log Details ---

interface FlyerProcessedLog extends ActivityLogItemBase {
  action: 'flyer_processed';
  details: {
    flyer_id: number;
    store_name: string;
  };
}

interface RecipeCreatedLog extends ActivityLogItemBase {
  action: 'recipe_created';
  details: {
    recipe_id: number;
    recipe_name: string;
  };
}

interface UserRegisteredLog extends ActivityLogItemBase {
  action: 'user_registered';
  details: {
    full_name: string;
  };
}

interface RecipeFavoritedLog extends ActivityLogItemBase {
  action: 'recipe_favorited';
  details: {
    recipe_name: string;
  };
}

interface ListSharedLog extends ActivityLogItemBase {
  action: 'list_shared';
  details: {
    list_name: string;
    shopping_list_id: number;
    shared_with_name: string;
  };
}

/**
 * The final ActivityLogItem type is a union of all specific log types.
 * TypeScript will now correctly infer the shape of 'details' based on the 'action' property.
 */
export type ActivityLogItem =
  | FlyerProcessedLog
  | RecipeCreatedLog
  | UserRegisteredLog
  | RecipeFavoritedLog
  | ListSharedLog;

export interface PantryLocation {
  readonly pantry_location_id: number;
  readonly user_id: string; // UUID
  name: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface SearchQuery {
  readonly search_query_id: number;
  readonly user_id?: string | null; // UUID
  query_text: string;
  result_count?: number | null;
  was_successful?: boolean | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface ShoppingTripItem {
  readonly shopping_trip_item_id: number;
  readonly shopping_trip_id: number;
  readonly master_item_id?: number | null;
  custom_item_name?: string | null;
  quantity: number;
  price_paid_cents?: number | null;
  readonly created_at: string;
  readonly updated_at: string;
  // Joined data for display
  master_item_name?: string | null;
}

export interface ShoppingTrip {
  readonly shopping_trip_id: number;
  readonly user_id: string; // UUID
  readonly shopping_list_id?: number | null;
  readonly completed_at: string;
  total_spent_cents?: number | null;
  items: ShoppingTripItem[]; // Nested items
  readonly updated_at: string;
}

export interface Receipt {
  readonly receipt_id: number;
  readonly user_id: string; // UUID
  store_location_id?: number | null; // Specific store location (nullable if not yet matched)
  receipt_image_url: string;
  transaction_date?: string | null;
  total_amount_cents?: number | null;
  status: 'pending' | 'processing' | 'completed' | 'failed';
  raw_text?: string | null;
  readonly processed_at?: string | null;
  items?: ReceiptItem[];
  readonly created_at: string;
  readonly updated_at: string;
}

export interface ReceiptItem {
  readonly receipt_item_id: number;
  readonly receipt_id: number;
  raw_item_description: string;
  quantity: number;
  price_paid_cents: number;
  master_item_id?: number | null; // Can be updated by admin correction
  product_id?: number | null; // Can be updated by admin correction
  status: 'unmatched' | 'matched' | 'needs_review' | 'ignored';
  upc_code?: string | null;
  line_number?: number | null;
  match_confidence?: number | null;
  is_discount: boolean;
  unit_price_cents?: number | null;
  unit_type?: string | null;
  added_to_pantry: boolean;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface ReceiptDeal {
  readonly receipt_item_id: number;
  readonly master_item_id: number;
  item_name: string;
  price_paid_cents: number;
  current_best_price_in_cents: number;
  potential_savings_cents: number;
  deal_store_name: string;
  flyer_id: number;
}

/**
 * Represents a geographic point in GeoJSON format.
 * This is a standard way to represent point data from PostGIS.
 */
export interface GeoJSONPoint {
  type: 'Point';
  coordinates: [number, number]; // [longitude, latitude]
}

export interface StoreLocation {
  readonly store_location_id: number;
  readonly store_id?: number | null;
  readonly address_id: number;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface Address {
  readonly address_id: number;
  address_line_1: string;
  address_line_2?: string | null;
  city: string;
  province_state: string;
  postal_code: string;
  country: string;
  latitude?: number | null;
  longitude?: number | null;
  readonly location?: GeoJSONPoint | null;
  readonly created_at: string;
  readonly updated_at: string;
}

// Extended type for store location with full address data
export interface StoreLocationWithAddress extends StoreLocation {
  address: Address;
}

// Extended type for store with all its locations
export interface StoreWithLocations extends Store {
  locations: StoreLocationWithAddress[];
}

// Request type for creating a store with optional address
export interface CreateStoreRequest {
  name: string;
  logo_url?: string | null;
  address?: {
    address_line_1: string;
    city: string;
    province_state: string;
    postal_code: string;
    country?: string;
    address_line_2?: string;
  };
}

export interface FlyerLocation {
  readonly flyer_id: number;
  readonly store_location_id: number;
  readonly created_at: string;
  readonly updated_at: string;
}

export enum AnalysisType {
  QUICK_INSIGHTS = 'QUICK_INSIGHTS',
  DEEP_DIVE = 'DEEP_DIVE',
  WEB_SEARCH = 'WEB_SEARCH',
  PLAN_TRIP = 'PLAN_TRIP',
  GENERATE_IMAGE = 'GENERATE_IMAGE',
  COMPARE_PRICES = 'COMPARE_PRICES',
}

/**
 * Represents a source for a grounded response, normalized for consistent use in the UI.
 */
export interface Source {
  uri: string;
  title: string;
}

/**
 * Represents a response that may include sources, such as from a web search or map plan.
 */
export interface GroundedResponse {
  text: string;
  sources: Source[];
}

/**
 * Defines the shape of the state managed by the useAiAnalysis hook's reducer.
 * This centralizes all state related to AI analysis into a single, predictable object.
 */
export interface AiAnalysisState {
  // The type of analysis currently being performed, if any.
  loadingAnalysis: AnalysisType | null;
  // A general error message for any failed analysis.
  error: string | null;
  // Stores the text result for each analysis type.
  results: { [key in AnalysisType]?: string };
  // Stores the sources for analyses that provide them.
  sources: { [key in AnalysisType]?: Source[] };
  // Stores the URL of the last generated image.
  generatedImageUrl: string | null;
}

/**
 * Defines the actions that can be dispatched to the AiAnalysisReducer.
 * This uses a discriminated union for strict type checking.
 */
export type AiAnalysisAction =
  // Dispatched when any analysis starts.
  | { type: 'FETCH_START'; payload: { analysisType: AnalysisType } }
  // Dispatched when an analysis that returns a simple string succeeds.
  | { type: 'FETCH_SUCCESS_TEXT'; payload: { analysisType: AnalysisType; data: string } }
  // Dispatched when an analysis that returns text and sources succeeds.
  | {
      type: 'FETCH_SUCCESS_GROUNDED';
      payload: { analysisType: AnalysisType; data: GroundedResponse };
    }
  // Dispatched when the image generation succeeds.
  | { type: 'FETCH_SUCCESS_IMAGE'; payload: { data: string } }
  // Dispatched when any analysis fails.
  | { type: 'FETCH_ERROR'; payload: { error: string } }
  // Dispatched to clear errors or reset state if needed.
  | { type: 'CLEAR_ERROR' }
  // Dispatched to reset the state to its initial values.
  | { type: 'RESET_STATE' };
export type StageStatus = 'pending' | 'in-progress' | 'completed' | 'error';

export interface ProcessingStage {
  name: string;
  status: StageStatus;
  detail?: string;
  critical?: boolean;
  progress?: { current: number; total: number } | null;
}

export const CATEGORIES = [
  'Fruits & Vegetables',
  'Meat & Seafood',
  'Dairy & Eggs',
  'Bakery & Bread',
  'Pantry & Dry Goods',
  'Beverages',
  'Frozen Foods',
  'Snacks',
  'Household & Cleaning',
  'Personal Care & Health',
  'Baby & Child',
  'Pet Supplies',
  'Deli & Prepared Foods',
  'Canned Goods',
  'Condiments & Spices',
  'Breakfast & Cereal',
  'Organic',
  'International Foods',
  'Other/Miscellaneous',
];

/**
 * Represents the core data extracted from a flyer by the AI service.
 * This is the structure returned from the backend to the frontend.
 */
export interface ExtractedCoreData {
  store_name: string;
  valid_from: string | null;
  valid_to: string | null;
  store_address: string | null;
  items: ExtractedFlyerItem[];
}

/**
 * Represents the shape of a single flyer item as returned by the AI service,
 * before it is saved to the database. This is a Data Transfer Object (DTO).
 * It intentionally omits database-generated fields like `flyer_item_id`, `created_at`, etc.
 */
export interface ExtractedFlyerItem {
  item: string;
  price_display: string;
  price_in_cents: number | null;
  quantity: string;
  category_name: string;
  master_item_id?: number; // Kept optional as AI might not find a match
  unit_price?: UnitPrice | null;
}

/**
 * Represents the logo data extracted from a flyer by the AI service.
 */
export interface ExtractedLogoData {
  store_logo_base_64: string | null;
}

/**
 * Represents the data extracted from a receipt image by the AI service.
 */
export interface ExtractedReceiptData {
  raw_text: string;
  items: {
    raw_item_description: string;
    quantity: number;
    price_paid_cents: number;
  }[];
}

/**
 * Represents an item that frequently appears on sale.
 * Returned by the `get_most_frequent_sale_items` database function.
 */
export interface MostFrequentSaleItem {
  master_item_id: number;
  item_name: string;
  sale_count: number;
}

/**
 * Represents a recipe that can be made from items in a user's pantry.
 * Returned by the `find_recipes_from_pantry` database function.
 */
export interface PantryRecipe extends Recipe {
  missing_ingredients_count: number;
  pantry_ingredients_count: number;
}

/**
 * Represents a recommended recipe for a user.
 * Returned by the `recommend_recipes_for_user` database function.
 */
export interface RecommendedRecipe extends Recipe {
  recommendation_score: number;
  reason: string;
}

/**
 * Represents the best current sale price for a user's watched item.
 * Returned by the `get_best_sale_prices_for_user` database function.
 */
export interface WatchedItemDeal {
  master_item_id: number;
  item_name: string;
  best_price_in_cents: number;
  store: {
    store_id: number;
    name: string;
    logo_url: string | null;
    locations: {
      address_line_1: string;
      city: string;
      province_state: string;
      postal_code: string;
    }[];
  };
  flyer_id: number;
  valid_to: string; // Date string
}

/**
 * Represents a suggested unit conversion for a pantry item.
 * Returned by the `suggest_pantry_item_conversions` database function.
 */
export interface PantryItemConversion {
  to_unit: string;
  converted_quantity: number;
}

/**
 * Represents an item needed for a menu plan, considering pantry stock.
 * Returned by `generate_shopping_list_for_menu_plan` and `add_menu_plan_to_shopping_list`.
 */
export interface MenuPlanShoppingListItem {
  master_item_id: number;
  item_name: string;
  quantity_needed: number;
}

/**
 * Represents an unmatched flyer item pending manual review.
 * Returned by `getUnmatchedFlyerItems`.
 */
export interface UnmatchedFlyerItem {
  readonly unmatched_flyer_item_id: number;
  status: 'pending' | 'resolved' | 'ignored'; // 'resolved' is used instead of 'reviewed' from the DB for clarity
  readonly reviewed_at?: string | null;
  readonly flyer_item_id: number;
  flyer_item_name: string;
  price_display: string;
  flyer_id: number;
  store_name: string;
  readonly created_at: string;
  readonly updated_at: string;
}

/**
 * Represents a user-defined budget for tracking grocery spending.
 */
export interface Budget {
  readonly budget_id: number;
  readonly user_id: string; // UUID
  name: string;
  amount_cents: number;
  period: 'weekly' | 'monthly';
  start_date: string; // DATE
  readonly created_at: string;
  readonly updated_at: string;
}

/**
 * Represents the aggregated spending for a single category.
 * Returned by the `get_spending_by_category` database function.
 */
export interface SpendingByCategory {
  category_id: number;
  category_name: string;
  total_spent_cents: number;
}

/**
 * Represents a single defined achievement in the system.
 */
export interface Achievement {
  readonly achievement_id: number;
  name: string;
  description: string;
  icon?: string | null;
  points_value: number;
  readonly created_at: string;
}

/**
 * Represents an achievement that has been awarded to a user.
 */
export interface UserAchievement {
  readonly user_id: string; // UUID
  readonly achievement_id: number;
  readonly achieved_at: string; // TIMESTAMPTZ
}

/**
 * Represents a user's entry on the leaderboard.
 * Returned by the `getLeaderboard` database function.
 */
export interface LeaderboardUser {
  readonly user_id: string;
  full_name: string | null;
  avatar_url: string | null;
  points: number;
  readonly rank: string; // RANK() returns a bigint, which the pg driver returns as a string.
}

/**
 * Defines the shape of the user data returned for the admin user list.
 * This is a public-facing type and does not include sensitive fields.
 */
export interface AdminUserView {
  readonly user_id: string;
  email: string;
  role: 'admin' | 'user';
  full_name: string | null;
  avatar_url: string | null;
  readonly created_at: string;
}

export interface PriceHistoryData {
  master_item_id: number;
  price_in_cents: number;
  date: string; // ISO date string
}

export interface UserReaction {
  readonly reaction_id: number;
  readonly user_id: string; // UUID
  readonly entity_type: string;
  readonly entity_id: string;
  reaction_type: string;
  readonly created_at: string;
  readonly updated_at: string;
}

export interface UnitConversion {
  readonly unit_conversion_id: number;
  readonly master_item_id: number;
  from_unit: string;
  to_unit: string;
  factor: number;
  readonly created_at: string;
  readonly updated_at: string;
}

// ============================================================================
// UPC SCANNING TYPES
// ============================================================================

export type UpcScanSource = 'image_upload' | 'manual_entry' | 'phone_app' | 'camera_scan';

export interface UpcScanHistory {
  readonly scan_id: number;
  readonly user_id: string; // UUID
  upc_code: string;
  product_id?: number | null;
  scan_source: UpcScanSource;
  scan_confidence?: number | null;
  raw_image_path?: string | null;
  lookup_successful: boolean;
  readonly created_at: string;
  readonly updated_at: string;
}

export type UpcExternalSource = 'openfoodfacts' | 'upcitemdb' | 'manual' | 'unknown';

export interface UpcExternalLookup {
  readonly lookup_id: number;
  upc_code: string;
  product_name?: string | null;
  brand_name?: string | null;
  category?: string | null;
  description?: string | null;
  image_url?: string | null;
  external_source: UpcExternalSource;
  lookup_data?: unknown | null; // JSONB
  lookup_successful: boolean;
  readonly created_at: string;
  readonly updated_at: string;
}

// ============================================================================
// EXPIRY TRACKING TYPES
// ============================================================================

export type StorageLocation = 'fridge' | 'freezer' | 'pantry' | 'room_temp';
export type ExpiryDataSource = 'usda' | 'fda' | 'manual' | 'community';

export interface ExpiryDateRange {
  readonly expiry_range_id: number;
  master_item_id?: number | null;
  category_id?: number | null;
  item_pattern?: string | null;
  storage_location: StorageLocation;
  min_days: number;
  max_days: number;
  typical_days: number;
  notes?: string | null;
  source?: ExpiryDataSource | null;
  readonly created_at: string;
  readonly updated_at: string;
}

export type ExpiryAlertMethod = 'email' | 'push' | 'in_app';

export interface ExpiryAlert {
  readonly expiry_alert_id: number;
  readonly user_id: string; // UUID
  days_before_expiry: number;
  alert_method: ExpiryAlertMethod;
  is_enabled: boolean;
  last_alert_sent_at?: string | null; // TIMESTAMPTZ
  readonly created_at: string;
  readonly updated_at: string;
}

export type ExpiryAlertType = 'expiring_soon' | 'expired' | 'expiry_reminder';

export interface ExpiryAlertLog {
  readonly alert_log_id: number;
  readonly user_id: string; // UUID
  pantry_item_id?: number | null;
  alert_type: ExpiryAlertType;
  alert_method: ExpiryAlertMethod;
  item_name: string;
  expiry_date?: string | null; // DATE
  days_until_expiry?: number | null;
  readonly sent_at: string; // TIMESTAMPTZ
}

// ============================================================================
// RECEIPT PROCESSING TYPES
// ============================================================================

export type ReceiptProcessingStep =
  | 'upload'
  | 'ocr_extraction'
  | 'text_parsing'
  | 'store_detection'
  | 'item_extraction'
  | 'item_matching'
  | 'price_parsing'
  | 'finalization';

export type ReceiptProcessingStatus = 'started' | 'completed' | 'failed' | 'skipped';

export type ReceiptProcessingProvider =
  | 'tesseract'
  | 'openai'
  | 'anthropic'
  | 'google_vision'
  | 'aws_textract'
  | 'internal';

export interface ReceiptProcessingLog {
  readonly log_id: number;
  readonly receipt_id: number;
  processing_step: ReceiptProcessingStep;
  status: ReceiptProcessingStatus;
  provider?: ReceiptProcessingProvider | null;
  duration_ms?: number | null;
  tokens_used?: number | null;
  cost_cents?: number | null;
  input_data?: unknown | null; // JSONB
  output_data?: unknown | null; // JSONB
  error_message?: string | null;
  readonly created_at: string;
}

export type StoreReceiptPatternType =
  | 'header_regex'
  | 'footer_regex'
  | 'phone_number'
  | 'address_fragment'
  | 'store_number_format';

export interface StoreReceiptPattern {
  readonly pattern_id: number;
  readonly store_id: number;
  pattern_type: StoreReceiptPatternType;
  pattern_value: string;
  priority: number;
  is_active: boolean;
  readonly created_at: string;
  readonly updated_at: string;
}