-- sql/master_schema_rollup.sql
-- ============================================================================
-- MASTER SCHEMA SCRIPT
-- ============================================================================
-- Purpose:
-- This file contains the master SQL schema for the entire Postgres database.
-- It is designed to be a "one-click" script that can be run in a PostgreSQL
-- database to set up the entire backend from scratch, including:
--   1. Enabling required Postgres extensions.
--   2. Creating all tables with relationships and constraints.
--   3. Seeding essential initial data (categories, master items).
--   6. Defining database functions for business logic.
--   7. Setting up triggers for automation (e.g., creating user profiles).
--
-- Usage:
-- For a fresh setup, run this entire script. To reset the environment,
-- first run 'delete_all_tables.sql.txt' and then run this script.

-- ============================================================================
-- PART 2: TABLES
-- ============================================================================
-- 0. NEW TABLE: A centralized table for storing all physical addresses.
CREATE TABLE IF NOT EXISTS public.addresses (
    address_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    address_line_1 TEXT NOT NULL UNIQUE,
    address_line_2 TEXT,
    city TEXT NOT NULL,
    province_state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL,
    latitude NUMERIC(9, 6),
    longitude NUMERIC(9, 6),
    location GEOGRAPHY(Point, 4326),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.addresses IS 'A centralized table for storing all physical addresses for users and stores.';
COMMENT ON COLUMN public.addresses.latitude IS 'The geographic latitude.';
COMMENT ON COLUMN public.addresses.longitude IS 'The geographic longitude.';
COMMENT ON COLUMN public.addresses.location IS 'A PostGIS geography type for efficient spatial queries.';
CREATE INDEX IF NOT EXISTS addresses_location_idx ON public.addresses USING GIST (location);


-- 1. Users table for authentication.
CREATE TABLE IF NOT EXISTS public.users (
    user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT,
    refresh_token TEXT,
    failed_login_attempts INTEGER DEFAULT 0,
    last_failed_login TIMESTAMPTZ,
    last_login_ip TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.users IS 'Stores user authentication information.';
COMMENT ON COLUMN public.users.refresh_token IS 'Stores the long-lived refresh token for re-authentication.';
COMMENT ON COLUMN public.users.failed_login_attempts IS 'Tracks the number of consecutive failed login attempts.';
COMMENT ON COLUMN public.users.last_failed_login IS 'Timestamp of the last failed login attempt.';
COMMENT ON COLUMN public.users.last_login_ip IS 'The IP address from which the user last successfully logged in.';
-- Add an index on the refresh_token for faster lookups when refreshing tokens.
CREATE INDEX IF NOT EXISTS idx_users_refresh_token ON public.users(refresh_token);
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON public.users (email);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON public.users (created_at DESC);

-- 2.  Log key user activities for analytics.
-- This needs to be created early as many triggers will insert into it.
CREATE TABLE IF NOT EXISTS public.activity_log (
    activity_log_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID REFERENCES public.users(user_id) ON DELETE SET NULL,
    action TEXT NOT NULL,
    display_text TEXT NOT NULL,
    icon TEXT,
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.activity_log IS 'Logs key user and system actions for auditing and display in an activity feed.';
CREATE INDEX IF NOT EXISTS idx_activity_log_user_id ON public.activity_log(user_id);

-- 3. for public user profiles.
-- This table is linked to the users table and stores non-sensitive user data.
-- This table now references the new `addresses` table for the user's home address.
CREATE TABLE IF NOT EXISTS public.profiles (
    user_id UUID PRIMARY KEY REFERENCES public.users(user_id) ON DELETE CASCADE,    
    full_name TEXT,
    avatar_url TEXT,
    address_id BIGINT REFERENCES public.addresses(address_id) ON DELETE SET NULL,
    points INTEGER DEFAULT 0 NOT NULL,
    preferences JSONB,
    role TEXT CHECK (role IN ('admin', 'user')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_by UUID REFERENCES public.users(user_id) ON DELETE SET NULL,
    updated_by UUID REFERENCES public.users(user_id) ON DELETE SET NULL
);
COMMENT ON TABLE public.profiles IS 'Stores public-facing user data, linked to the public.users table.';
COMMENT ON COLUMN public.profiles.address_id IS 'A foreign key to the user''s primary address in the `addresses` table.';
COMMENT ON COLUMN public.profiles.points IS 'A simple integer column to store a user''s total accumulated points from achievements.';

-- 4. The 'stores' table for normalized store data.
CREATE TABLE IF NOT EXISTS public.stores (
    store_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    logo_url TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_by UUID REFERENCES public.users(user_id) ON DELETE SET NULL
);
COMMENT ON TABLE public.stores IS 'Stores metadata for grocery store chains (e.g., Safeway, Kroger).';

-- 5. The 'categories' table for normalized category data.
CREATE TABLE IF NOT EXISTS public.categories (
    category_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.categories IS 'Stores a predefined list of grocery item categories (e.g., ''Fruits & Vegetables'', ''Dairy & Eggs'').';

-- 6.  flyers' table
CREATE TABLE IF NOT EXISTS public.flyers (
    flyer_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    file_name TEXT NOT NULL,
    image_url TEXT NOT NULL,
    icon_url TEXT,
    checksum TEXT UNIQUE,
    store_id BIGINT REFERENCES public.stores(store_id),
    valid_from DATE,
    valid_to DATE,
    store_address TEXT,
    item_count INTEGER DEFAULT 0 NOT NULL,
    uploaded_by UUID REFERENCES public.users(user_id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.flyers IS 'Stores metadata for each processed flyer, linking it to a store and its validity period.';
CREATE INDEX IF NOT EXISTS idx_flyers_store_id ON public.flyers(store_id);
COMMENT ON COLUMN public.flyers.file_name IS 'The original name of the uploaded flyer file (e.g., "flyer_week_1.pdf").';
COMMENT ON COLUMN public.flyers.image_url IS 'The public URL of the primary flyer image stored on Server for now.';
COMMENT ON COLUMN public.flyers.icon_url IS 'The public URL of the 64x64 icon version of the flyer image.';
COMMENT ON COLUMN public.flyers.checksum IS 'A SHA-256 hash of the original file content to prevent duplicate processing.';
COMMENT ON COLUMN public.flyers.store_id IS 'Foreign key linking this flyer to a specific store in the `stores` table.';
COMMENT ON COLUMN public.flyers.valid_from IS 'The start date of the sale period for this flyer, extracted by the AI.';
COMMENT ON COLUMN public.flyers.valid_to IS 'The end date of the sale period for this flyer, extracted by the AI.';
COMMENT ON COLUMN public.flyers.store_address IS 'The physical store address if it was successfully extracted from the flyer image.';
COMMENT ON COLUMN public.flyers.item_count IS 'A cached count of the number of items in this flyer, maintained by a trigger.';
COMMENT ON COLUMN public.flyers.uploaded_by IS 'The user who uploaded the flyer. Can be null for anonymous or system uploads.';

CREATE INDEX IF NOT EXISTS idx_flyers_created_at ON public.flyers (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_flyers_valid_to_file_name ON public.flyers (valid_to DESC, file_name ASC);
-- 7. The 'master_grocery_items' table. This is the master dictionary.
CREATE TABLE IF NOT EXISTS public.master_grocery_items (
    master_grocery_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    category_id BIGINT REFERENCES public.categories(category_id),
    is_allergen BOOLEAN DEFAULT false,
    allergy_info JSONB,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_by UUID REFERENCES public.users(user_id) ON DELETE SET NULL
);
COMMENT ON TABLE public.master_grocery_items IS 'The master dictionary of canonical grocery items. Each item has a unique name and is linked to a category.';
CREATE INDEX IF NOT EXISTS idx_master_grocery_items_category_id ON public.master_grocery_items(category_id);
CREATE INDEX IF NOT EXISTS idx_master_grocery_items_name ON public.master_grocery_items (name);

-- 8. The 'user_watched_items' table. This links to the master list.
CREATE TABLE IF NOT EXISTS public.user_watched_items (
    user_watched_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,    
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(user_id, master_item_id)
);
COMMENT ON TABLE public.user_watched_items IS 'A linking table that represents a user''s personal watchlist of grocery items.';
CREATE INDEX IF NOT EXISTS idx_user_watched_items_master_item_id ON public.user_watched_items(master_item_id);

-- 9. The 'flyer_items' table. This stores individual items from flyers.
CREATE TABLE IF NOT EXISTS public.flyer_items (
  flyer_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  flyer_id BIGINT REFERENCES public.flyers(flyer_id) ON DELETE CASCADE,
  item TEXT NOT NULL,
  price_display TEXT NOT NULL,
  price_in_cents INTEGER,
  quantity_num NUMERIC,
  quantity TEXT NOT NULL,
  category_id BIGINT REFERENCES public.categories(category_id),  
  category_name TEXT,
  unit_price JSONB,
  view_count INTEGER DEFAULT 0 NOT NULL,
  click_count INTEGER DEFAULT 0 NOT NULL,
  master_item_id BIGINT REFERENCES public.master_grocery_items(master_grocery_item_id),
  product_id BIGINT,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.flyer_items IS 'Stores individual items extracted from a specific flyer.';
COMMENT ON COLUMN public.flyer_items.flyer_id IS 'Foreign key linking this item to its parent flyer in the `flyers` table.';
COMMENT ON COLUMN public.flyer_items.item IS 'The raw item name as it appears in the flyer (e.g., "Granny Smith Apples").';
COMMENT ON COLUMN public.flyer_items.price_display IS 'The raw price string from the flyer (e.g., "$3.99", "2 for $5.00").';
COMMENT ON COLUMN public.flyer_items.price_in_cents IS 'The normalized price for a single item, in cents, for easier sorting and comparison.';
COMMENT ON COLUMN public.flyer_items.quantity IS 'The raw quantity or deal description string (e.g., "per lb", "500g bag").';
COMMENT ON COLUMN public.flyer_items.quantity_num IS 'The parsed primary numeric value from the quantity string (e.g., 500 from "500g").';
COMMENT ON COLUMN public.flyer_items.master_item_id IS 'Foreign key linking this flyer item to its canonical entry in `master_grocery_items`. Null if no match was found.';
COMMENT ON COLUMN public.flyer_items.category_id IS 'Foreign key to the item''s category. Can be redundant if master_item_id is set, but useful.';
COMMENT ON COLUMN public.flyer_items.category_name IS 'The denormalized name of the category for faster UI display without extra joins.';
COMMENT ON COLUMN public.flyer_items.unit_price IS 'A JSONB object storing the calculated unit price (e.g., {"value": 1.99, "unit": "lb"}) for standardized price comparisons.';
COMMENT ON COLUMN public.flyer_items.product_id IS 'A foreign key for future use, to link to a specific product with a UPC code in the `products` table.';
CREATE INDEX IF NOT EXISTS idx_flyer_items_flyer_id ON public.flyer_items(flyer_id);
CREATE INDEX IF NOT EXISTS idx_flyer_items_master_item_id ON public.flyer_items(master_item_id);
CREATE INDEX IF NOT EXISTS idx_flyer_items_category_id ON public.flyer_items(category_id);
CREATE INDEX IF NOT EXISTS idx_flyer_items_product_id ON public.flyer_items(product_id);
-- Add a GIN index to the 'item' column for fast fuzzy text searching.
-- This requires the pg_trgm extension.
CREATE INDEX IF NOT EXISTS flyer_items_item_trgm_idx ON public.flyer_items USING GIN (item gin_trgm_ops);

-- 10. For user-defined alerts on watched items.
CREATE TABLE IF NOT EXISTS public.user_alerts (
    user_alert_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_watched_item_id BIGINT NOT NULL REFERENCES public.user_watched_items(user_watched_item_id) ON DELETE CASCADE,
    alert_type TEXT NOT NULL CHECK (alert_type IN ('PRICE_BELOW', 'PERCENT_OFF_AVERAGE')),
    threshold_value NUMERIC NOT NULL,
    is_active BOOLEAN DEFAULT true NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.user_alerts IS 'Stores user-configured alert rules for their watched items.';
COMMENT ON COLUMN public.user_alerts.alert_type IS 'The condition that triggers the alert, e.g., ''PRICE_BELOW''.';
COMMENT ON COLUMN public.user_alerts.threshold_value IS 'The numeric threshold for the alert condition (e.g., price in cents, or percentage).';
CREATE INDEX IF NOT EXISTS idx_user_alerts_user_watched_item_id ON public.user_alerts(user_watched_item_id);

-- 11. Store notifications for users.
CREATE TABLE IF NOT EXISTS public.notifications (
    notification_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    link_url TEXT,
    is_read BOOLEAN DEFAULT false NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.notifications IS 'A central log of notifications generated for users, such as price alerts.';
COMMENT ON COLUMN public.notifications.content IS 'The notification message displayed to the user.';
COMMENT ON COLUMN public.notifications.link_url IS 'A URL to navigate to when the notification is clicked.';
CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON public.notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_user_id_created_at ON public.notifications (user_id, created_at DESC);

-- 12. Store individual store locations with geographic data.
CREATE TABLE IF NOT EXISTS public.store_locations (
    store_location_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    store_id BIGINT REFERENCES public.stores(store_id) ON DELETE CASCADE,
    address_id BIGINT NOT NULL REFERENCES public.addresses(address_id) ON DELETE CASCADE,    
    UNIQUE(store_id, address_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.store_locations IS 'Stores physical locations of stores with geographic data for proximity searches.';
CREATE INDEX IF NOT EXISTS idx_store_locations_store_id ON public.store_locations(store_id);

-- 13. For aggregated, historical price data for master items.
CREATE TABLE IF NOT EXISTS public.item_price_history (
    item_price_history_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    summary_date DATE NOT NULL,
    store_location_id BIGINT REFERENCES public.store_locations(store_location_id) ON DELETE CASCADE,
    min_price_in_cents INTEGER,
    max_price_in_cents INTEGER,
    avg_price_in_cents INTEGER,
    data_points_count INTEGER DEFAULT 0 NOT NULL,
    UNIQUE(master_item_id, summary_date, store_location_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.item_price_history IS 'Serves as a summary table to speed up charting and analytics.';
COMMENT ON COLUMN public.item_price_history.summary_date IS 'The date for which the price data is summarized.';
COMMENT ON COLUMN public.item_price_history.min_price_in_cents IS 'The lowest price found for this item on this day, in cents,';
COMMENT ON COLUMN public.item_price_history.max_price_in_cents IS 'The highest price found for this item on this day, in cents.';
COMMENT ON COLUMN public.item_price_history.avg_price_in_cents IS 'The average price found for this item on this day, in cents.';
COMMENT ON COLUMN public.item_price_history.data_points_count IS 'How many data points were used for this summary.';
CREATE INDEX IF NOT EXISTS idx_item_price_history_master_item_id ON public.item_price_history(master_item_id);
CREATE INDEX IF NOT EXISTS idx_item_price_history_store_location_id ON public.item_price_history(store_location_id);

-- 14. Map various names to a single master grocery item.
CREATE TABLE IF NOT EXISTS public.master_item_aliases (
    master_item_alias_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    alias TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.master_item_aliases IS 'Stores synonyms or alternative names for master items to improve matching.';
COMMENT ON COLUMN public.master_item_aliases.alias IS 'An alternative name, e.g., "Ground Chuck" for the master item "Ground Beef".';
CREATE INDEX IF NOT EXISTS idx_master_item_aliases_master_item_id ON public.master_item_aliases(master_item_id);

-- 15. For user shopping lists.
CREATE TABLE IF NOT EXISTS public.shopping_lists (
    shopping_list_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.shopping_lists IS 'Stores user-created shopping lists, e.g., "Weekly Groceries".';
CREATE INDEX IF NOT EXISTS idx_shopping_lists_user_id ON public.shopping_lists(user_id);

-- 16. For items in a user's shopping list.
CREATE TABLE IF NOT EXISTS public.shopping_list_items (
    shopping_list_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    shopping_list_id BIGINT NOT NULL REFERENCES public.shopping_lists(shopping_list_id) ON DELETE CASCADE,
    master_item_id BIGINT REFERENCES public.master_grocery_items(master_grocery_item_id),
    custom_item_name TEXT,
    quantity NUMERIC DEFAULT 1 NOT NULL,
    is_purchased BOOLEAN DEFAULT false NOT NULL,
    notes TEXT,
    added_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    CONSTRAINT must_have_item_identifier CHECK (master_item_id IS NOT NULL OR custom_item_name IS NOT NULL)
);
COMMENT ON TABLE public.shopping_list_items IS 'Contains individual items for a specific shopping list.';
COMMENT ON COLUMN public.shopping_list_items.custom_item_name IS 'For items not in the master list, e.g., "Grandma''s special spice mix".';
COMMENT ON COLUMN public.shopping_list_items.is_purchased IS 'Lets users check items off their list as they shop.';
CREATE INDEX IF NOT EXISTS idx_shopping_list_items_shopping_list_id ON public.shopping_list_items(shopping_list_id);
CREATE INDEX IF NOT EXISTS idx_shopping_list_items_master_item_id ON public.shopping_list_items(master_item_id);

-- 17. Manage shared access to shopping lists.
CREATE TABLE IF NOT EXISTS public.shared_shopping_lists (
    shared_shopping_list_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    shopping_list_id BIGINT NOT NULL REFERENCES public.shopping_lists(shopping_list_id) ON DELETE CASCADE,
    shared_by_user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    shared_with_user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    permission_level TEXT NOT NULL CHECK (permission_level IN ('view', 'edit')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,    
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(shopping_list_id, shared_with_user_id)
);
COMMENT ON TABLE public.shared_shopping_lists IS 'Allows users to share shopping lists with others and set permissions.';
CREATE INDEX IF NOT EXISTS idx_shared_shopping_lists_shopping_list_id ON public.shared_shopping_lists(shopping_list_id);
CREATE INDEX IF NOT EXISTS idx_shared_shopping_lists_shared_by_user_id ON public.shared_shopping_lists(shared_by_user_id);
CREATE INDEX IF NOT EXISTS idx_shared_shopping_lists_shared_with_user_id ON public.shared_shopping_lists(shared_with_user_id);

-- 18. Store a user's collection of planned meals for a date range.
CREATE TABLE IF NOT EXISTS public.menu_plans (
    menu_plan_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    CONSTRAINT date_range_check CHECK (end_date >= start_date)
);
COMMENT ON TABLE public.menu_plans IS 'Represents a user''s meal plan for a specific period, e.g., "Week of Oct 23".';
CREATE INDEX IF NOT EXISTS idx_menu_plans_user_id ON public.menu_plans(user_id);

-- 19. Manage shared access to menu plans.
CREATE TABLE IF NOT EXISTS public.shared_menu_plans (
    shared_menu_plan_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    menu_plan_id BIGINT NOT NULL REFERENCES public.menu_plans(menu_plan_id) ON DELETE CASCADE,
    shared_by_user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    shared_with_user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    permission_level TEXT NOT NULL CHECK (permission_level IN ('view', 'edit')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,    
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(menu_plan_id, shared_with_user_id)
);
COMMENT ON TABLE public.shared_menu_plans IS 'Allows users to share and collaborate on meal plans.';
CREATE INDEX IF NOT EXISTS idx_shared_menu_plans_menu_plan_id ON public.shared_menu_plans(menu_plan_id);
CREATE INDEX IF NOT EXISTS idx_shared_menu_plans_shared_by_user_id ON public.shared_menu_plans(shared_by_user_id);
CREATE INDEX IF NOT EXISTS idx_shared_menu_plans_shared_with_user_id ON public.shared_menu_plans(shared_with_user_id);

-- 20. Store user-submitted corrections for flyer items.
CREATE TABLE IF NOT EXISTS public.suggested_corrections (
    suggested_correction_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    flyer_item_id BIGINT NOT NULL REFERENCES public.flyer_items(flyer_item_id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES public.users(user_id),
    correction_type TEXT NOT NULL,
    suggested_value TEXT NOT NULL,
    status TEXT DEFAULT 'pending' NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    reviewed_notes TEXT,
    reviewed_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.suggested_corrections IS 'A queue for user-submitted data corrections, enabling crowdsourced data quality improvements.';
COMMENT ON COLUMN public.suggested_corrections.correction_type IS 'The type of error the user is reporting.';
COMMENT ON COLUMN public.suggested_corrections.suggested_value IS 'The corrected value proposed by the user (e.g., a new price or master_item_id).';
COMMENT ON COLUMN public.suggested_corrections.status IS 'The moderation status of the correction: pending, approved, or rejected.';
CREATE INDEX IF NOT EXISTS idx_suggested_corrections_flyer_item_id ON public.suggested_corrections(flyer_item_id);
CREATE INDEX IF NOT EXISTS idx_suggested_corrections_user_id ON public.suggested_corrections(user_id);
CREATE INDEX IF NOT EXISTS idx_suggested_corrections_pending ON public.suggested_corrections (created_at) WHERE status = 'pending';

-- 21. For prices submitted directly by users from in-store.
CREATE TABLE IF NOT EXISTS public.user_submitted_prices (
    user_submitted_price_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id),
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id),
    store_id BIGINT NOT NULL REFERENCES public.stores(store_id),
    price_in_cents INTEGER NOT NULL,
    photo_url TEXT,
    upvotes INTEGER DEFAULT 0 NOT NULL,
    downvotes INTEGER DEFAULT 0 NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.user_submitted_prices IS 'Stores item prices submitted by users directly from physical stores.';
COMMENT ON COLUMN public.user_submitted_prices.photo_url IS 'URL to user-submitted photo evidence of the price.';
COMMENT ON COLUMN public.user_submitted_prices.upvotes IS 'Community validation score indicating accuracy.';
CREATE INDEX IF NOT EXISTS idx_user_submitted_prices_user_id ON public.user_submitted_prices(user_id);
CREATE INDEX IF NOT EXISTS idx_user_submitted_prices_master_item_id ON public.user_submitted_prices(master_item_id);

-- 22. Log flyer items that could not be automatically matched to a master item.
CREATE TABLE IF NOT EXISTS public.unmatched_flyer_items (
    unmatched_flyer_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    flyer_item_id BIGINT NOT NULL REFERENCES public.flyer_items(flyer_item_id) ON DELETE CASCADE,    status TEXT DEFAULT 'pending' NOT NULL CHECK (status IN ('pending', 'resolved', 'ignored')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    reviewed_at TIMESTAMPTZ,
    UNIQUE(flyer_item_id),
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.unmatched_flyer_items IS 'A queue for reviewing flyer items that the system failed to automatically match.';
CREATE INDEX IF NOT EXISTS idx_unmatched_flyer_items_flyer_item_id ON public.unmatched_flyer_items(flyer_item_id);
CREATE INDEX IF NOT EXISTS idx_unmatched_flyer_items_pending ON public.unmatched_flyer_items (created_at) WHERE status = 'pending';

-- 23. Store brand information.
CREATE TABLE IF NOT EXISTS public.brands (
    brand_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    logo_url TEXT,
    store_id BIGINT REFERENCES public.stores(store_id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.brands IS 'Stores brand names like "Coca-Cola", "Maple Leaf", or "Kraft".';
COMMENT ON COLUMN public.brands.store_id IS 'If this is a store-specific brand (e.g., President''s Choice), this links to the parent store.';

-- 24. For specific products, linking a master item with a brand and size.
CREATE TABLE IF NOT EXISTS public.products (
    product_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id),
    brand_id BIGINT REFERENCES public.brands(brand_id),
    name TEXT NOT NULL,
    description TEXT,
    size TEXT,
    upc_code TEXT UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.products IS 'Represents a specific, sellable product, combining a generic item with a brand and size.';
COMMENT ON COLUMN public.products.upc_code IS 'Universal Product Code, if available, for exact product matching.';
COMMENT ON COLUMN public.products.brand_id IS 'Can be null for generic/store-brand items.';
COMMENT ON COLUMN public.products.name IS 'Prime Raised without Antibiotics Chicken Breast.';
COMMENT ON COLUMN public.products.size IS 'e.g., "4L", "500g".';
CREATE INDEX IF NOT EXISTS idx_products_master_item_id ON public.products(master_item_id);
CREATE INDEX IF NOT EXISTS idx_products_brand_id ON public.products(brand_id);

-- 25. Linking table for when one flyer is valid for multiple locations.
CREATE TABLE IF NOT EXISTS public.flyer_locations (
    flyer_id BIGINT NOT NULL REFERENCES public.flyers(flyer_id) ON DELETE CASCADE,
    store_location_id BIGINT NOT NULL REFERENCES public.store_locations(store_location_id) ON DELETE CASCADE,
    PRIMARY KEY (flyer_id, store_location_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.flyer_locations IS 'A linking table associating a single flyer with multiple store locations where its deals are valid.';
CREATE INDEX IF NOT EXISTS idx_flyer_locations_flyer_id ON public.flyer_locations(flyer_id);
CREATE INDEX IF NOT EXISTS idx_flyer_locations_store_location_id ON public.flyer_locations(store_location_id);

-- 26. Store recipes, which can be user-created or pre-populated.
CREATE TABLE IF NOT EXISTS public.recipes (
    recipe_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID REFERENCES public.users(user_id) ON DELETE CASCADE,
    original_recipe_id BIGINT REFERENCES public.recipes(recipe_id) ON DELETE SET NULL,
    name TEXT NOT NULL,
    description TEXT,
    instructions TEXT,
    prep_time_minutes INTEGER,
    cook_time_minutes INTEGER,
    servings INTEGER,
    photo_url TEXT,
    calories_per_serving INTEGER,
    protein_grams NUMERIC,
    fat_grams NUMERIC,
    carb_grams NUMERIC,
    avg_rating NUMERIC(2,1) DEFAULT 0.0 NOT NULL,
    status TEXT DEFAULT 'private' NOT NULL CHECK (status IN ('private', 'pending_review', 'public', 'rejected')),
    rating_count INTEGER DEFAULT 0 NOT NULL,
    fork_count INTEGER DEFAULT 0 NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipes IS 'Stores recipes that can be used to generate shopping lists.';
COMMENT ON COLUMN public.recipes.servings IS 'The number of servings this recipe yields.';
COMMENT ON COLUMN public.recipes.original_recipe_id IS 'If this recipe is a variation of another, this points to the original.';
COMMENT ON COLUMN public.recipes.user_id IS 'Can be a system recipe (user_id is NULL) or user-submitted.';
COMMENT ON COLUMN public.recipes.avg_rating IS 'Aggregated rating data for fast sorting/display.';
COMMENT ON COLUMN public.recipes.calories_per_serving IS 'Optional nutritional information.';
COMMENT ON COLUMN public.recipes.protein_grams IS 'Optional nutritional information.';
COMMENT ON COLUMN public.recipes.fat_grams IS 'Optional nutritional information.';
COMMENT ON COLUMN public.recipes.carb_grams IS 'Optional nutritional information.';
CREATE INDEX IF NOT EXISTS idx_recipes_user_id ON public.recipes(user_id);
CREATE INDEX IF NOT EXISTS idx_recipes_original_recipe_id ON public.recipes(original_recipe_id);
-- Add a partial unique index to ensure system-wide recipes (user_id IS NULL) have unique names.
-- This allows different users to have recipes with the same name.
CREATE UNIQUE INDEX IF NOT EXISTS idx_recipes_unique_system_recipe_name ON public.recipes(name) WHERE user_id IS NULL;

-- 27. For ingredients required for each recipe.
CREATE TABLE IF NOT EXISTS public.recipe_ingredients (
    recipe_ingredient_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id),
    quantity NUMERIC NOT NULL,
    unit TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_ingredients IS 'Defines the ingredients and quantities needed for a recipe.';
COMMENT ON COLUMN public.recipe_ingredients.unit IS 'e.g., "cups", "tbsp", "g", "each".';
CREATE INDEX IF NOT EXISTS idx_recipe_ingredients_recipe_id ON public.recipe_ingredients(recipe_id);
-- This index is crucial for functions that find recipes based on ingredients.
CREATE INDEX IF NOT EXISTS idx_recipe_ingredients_master_item_id ON public.recipe_ingredients(master_item_id);

-- 28.  Suggest ingredient substitutions for a recipe.
CREATE TABLE IF NOT EXISTS public.recipe_ingredient_substitutions (
    recipe_ingredient_substitution_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    recipe_ingredient_id BIGINT NOT NULL REFERENCES public.recipe_ingredients(recipe_ingredient_id) ON DELETE CASCADE,
    substitute_master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    notes TEXT,
    UNIQUE(recipe_ingredient_id, substitute_master_item_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_ingredient_substitutions IS 'Stores suggested alternative ingredients for a recipe item (e.g., "butter" for "olive oil").';
CREATE INDEX IF NOT EXISTS idx_recipe_ingredient_substitutions_recipe_ingredient_id ON public.recipe_ingredient_substitutions(recipe_ingredient_id);
CREATE INDEX IF NOT EXISTS idx_recipe_ingredient_substitutions_substitute_master_item_id ON public.recipe_ingredient_substitutions(substitute_master_item_id);

-- 29.  Store a predefined list of tags for recipes.
CREATE TABLE IF NOT EXISTS public.tags (
    tag_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.tags IS 'Stores tags for categorizing recipes, e.g., "Vegetarian", "Quick & Easy".';

-- 30.  Associate multiple tags with a recipe.
CREATE TABLE IF NOT EXISTS public.recipe_tags (
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    tag_id BIGINT NOT NULL REFERENCES public.tags(tag_id) ON DELETE CASCADE,
    PRIMARY KEY (recipe_id, tag_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_tags IS 'A linking table to associate multiple tags with a single recipe.';
CREATE INDEX IF NOT EXISTS idx_recipe_tags_recipe_id ON public.recipe_tags(recipe_id);
-- This index is crucial for functions that find recipes based on tags.
CREATE INDEX IF NOT EXISTS idx_recipe_tags_tag_id ON public.recipe_tags(tag_id);

-- 31. Store a predefined list of kitchen appliances.
CREATE TABLE IF NOT EXISTS public.appliances (
    appliance_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.appliances IS 'A predefined list of kitchen appliances (e.g., Air Fryer, Instant Pot).';

-- 32.  Associate recipes with required appliances.
CREATE TABLE IF NOT EXISTS public.recipe_appliances (
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    appliance_id BIGINT NOT NULL REFERENCES public.appliances(appliance_id) ON DELETE CASCADE,
    PRIMARY KEY (recipe_id, appliance_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_appliances IS 'Links recipes to the specific kitchen appliances they require.';
CREATE INDEX IF NOT EXISTS idx_recipe_appliances_recipe_id ON public.recipe_appliances(recipe_id);
CREATE INDEX IF NOT EXISTS idx_recipe_appliances_appliance_id ON public.recipe_appliances(appliance_id);

-- 33.  Store individual user ratings for recipes.
CREATE TABLE IF NOT EXISTS public.recipe_ratings (
    recipe_rating_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(recipe_id, user_id),
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_ratings IS 'Stores individual user ratings for recipes, ensuring a user can only rate a recipe once.';
CREATE INDEX IF NOT EXISTS idx_recipe_ratings_recipe_id ON public.recipe_ratings(recipe_id);
CREATE INDEX IF NOT EXISTS idx_recipe_ratings_user_id ON public.recipe_ratings(user_id);

-- 34.  For user comments on recipes to enable discussion.
CREATE TABLE IF NOT EXISTS public.recipe_comments (
    recipe_comment_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    parent_comment_id BIGINT REFERENCES public.recipe_comments(recipe_comment_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    status TEXT DEFAULT 'visible' NOT NULL CHECK (status IN ('visible', 'hidden', 'reported')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_comments IS 'Allows for threaded discussions and comments on recipes.';
COMMENT ON COLUMN public.recipe_comments.parent_comment_id IS 'For threaded comments.';
CREATE INDEX IF NOT EXISTS idx_recipe_comments_recipe_id ON public.recipe_comments(recipe_id);
CREATE INDEX IF NOT EXISTS idx_recipe_comments_user_id ON public.recipe_comments(user_id);
CREATE INDEX IF NOT EXISTS idx_recipe_comments_parent_comment_id ON public.recipe_comments(parent_comment_id);

-- 35. For users to define locations within their pantry.
CREATE TABLE IF NOT EXISTS public.pantry_locations (
    pantry_location_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    name TEXT NOT NULL,    
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(user_id, name)
);
COMMENT ON TABLE public.pantry_locations IS 'User-defined locations for organizing pantry items (e.g., "Fridge", "Freezer", "Spice Rack").';
CREATE INDEX IF NOT EXISTS idx_pantry_locations_user_id ON public.pantry_locations(user_id);

-- 36. Associate a recipe with a specific date and meal type within a menu plan.
CREATE TABLE IF NOT EXISTS public.planned_meals (
    planned_meal_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    menu_plan_id BIGINT NOT NULL REFERENCES public.menu_plans(menu_plan_id) ON DELETE CASCADE,
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    plan_date DATE NOT NULL,    
    meal_type TEXT NOT NULL,
    servings_to_cook INTEGER,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.planned_meals IS 'Assigns a recipe to a specific day and meal type within a user''s menu plan.';
COMMENT ON COLUMN public.planned_meals.meal_type IS 'The designated meal for the recipe, e.g., ''Breakfast'', ''Lunch'', ''Dinner''.';
CREATE INDEX IF NOT EXISTS idx_planned_meals_menu_plan_id ON public.planned_meals(menu_plan_id);
CREATE INDEX IF NOT EXISTS idx_planned_meals_recipe_id ON public.planned_meals(recipe_id);

-- 37. Track the grocery items a user currently has in their pantry.
CREATE TABLE IF NOT EXISTS public.pantry_items (
    pantry_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    quantity NUMERIC NOT NULL,
    unit TEXT,
    best_before_date DATE,
    pantry_location_id BIGINT REFERENCES public.pantry_locations(pantry_location_id) ON DELETE SET NULL,
    notification_sent_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,    
    UNIQUE(user_id, master_item_id, unit)
);
COMMENT ON TABLE public.pantry_items IS 'Tracks a user''s personal inventory of grocery items to enable smart shopping lists.';
COMMENT ON COLUMN public.pantry_items.quantity IS 'The current amount of the item. Convention: use grams for weight, mL for volume where applicable.';
COMMENT ON COLUMN public.pantry_items.pantry_location_id IS 'Links the item to a user-defined location like "Fridge" or "Freezer".';
COMMENT ON COLUMN public.pantry_items.unit IS 'e.g., ''g'', ''ml'', ''items''. Should align with recipe_ingredients.unit and quantity convention.';
CREATE INDEX IF NOT EXISTS idx_pantry_items_user_id ON public.pantry_items(user_id);
CREATE INDEX IF NOT EXISTS idx_pantry_items_master_item_id ON public.pantry_items(master_item_id);
CREATE INDEX IF NOT EXISTS idx_pantry_items_pantry_location_id ON public.pantry_items(pantry_location_id);

-- 38. Store password reset tokens.
CREATE TABLE IF NOT EXISTS public.password_reset_tokens (
    password_reset_token_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.password_reset_tokens IS 'Stores secure, single-use tokens for password reset requests.';
COMMENT ON COLUMN public.password_reset_tokens.token_hash IS 'A bcrypt hash of the reset token sent to the user.';
COMMENT ON COLUMN public.password_reset_tokens.expires_at IS 'The timestamp when this token is no longer valid.';
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id ON public.password_reset_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_token_hash ON public.password_reset_tokens(token_hash);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_expires_at ON public.password_reset_tokens (expires_at);

-- 39. Store unit conversion factors for specific master grocery items.
CREATE TABLE IF NOT EXISTS public.unit_conversions (
    unit_conversion_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    from_unit TEXT NOT NULL,
    to_unit TEXT NOT NULL,
    factor NUMERIC NOT NULL,
    UNIQUE(master_item_id, from_unit, to_unit),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.unit_conversions IS 'Stores item-specific unit conversion factors (e.g., grams of flour to cups).';
COMMENT ON COLUMN public.unit_conversions.factor IS 'The multiplication factor to convert from_unit to to_unit.';
CREATE INDEX IF NOT EXISTS idx_unit_conversions_master_item_id ON public.unit_conversions(master_item_id);

-- 40. For users to create their own private aliases for items.
CREATE TABLE IF NOT EXISTS public.user_item_aliases (
    user_item_alias_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    master_item_id BIGINT NOT NULL REFERENCES public.master_grocery_items(master_grocery_item_id) ON DELETE CASCADE,
    alias TEXT NOT NULL,
    UNIQUE(user_id, alias),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.user_item_aliases IS 'Allows users to create personal aliases for grocery items (e.g., "Dad''s Cereal").';
CREATE INDEX IF NOT EXISTS idx_user_item_aliases_user_id ON public.user_item_aliases(user_id);
CREATE INDEX IF NOT EXISTS idx_user_item_aliases_master_item_id ON public.user_item_aliases(master_item_id);

-- 41. For users to mark their favorite recipes.
CREATE TABLE IF NOT EXISTS public.favorite_recipes (
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (user_id, recipe_id),
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.favorite_recipes IS 'A simple linking table for users to mark their favorite recipes.';
CREATE INDEX IF NOT EXISTS idx_favorite_recipes_user_id ON public.favorite_recipes(user_id);
CREATE INDEX IF NOT EXISTS idx_favorite_recipes_recipe_id ON public.favorite_recipes(recipe_id);

-- 42. For users to mark their favorite stores.
CREATE TABLE IF NOT EXISTS public.favorite_stores (
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    store_id BIGINT NOT NULL REFERENCES public.stores(store_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (user_id, store_id),
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.favorite_stores IS 'A simple linking table for users to mark their favorite stores.';
CREATE INDEX IF NOT EXISTS idx_favorite_stores_user_id ON public.favorite_stores(user_id);
CREATE INDEX IF NOT EXISTS idx_favorite_stores_store_id ON public.favorite_stores(store_id);

-- 43. For users to group recipes into collections.
CREATE TABLE IF NOT EXISTS public.recipe_collections (
    recipe_collection_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_collections IS 'Allows users to create personal collections of recipes (e.g., "Holiday Baking").';
CREATE INDEX IF NOT EXISTS idx_recipe_collections_user_id ON public.recipe_collections(user_id);

-- 44. Associate recipes with a user's collection.
CREATE TABLE IF NOT EXISTS public.recipe_collection_items (
    collection_id BIGINT NOT NULL REFERENCES public.recipe_collections(recipe_collection_id) ON DELETE CASCADE,
    recipe_id BIGINT NOT NULL REFERENCES public.recipes(recipe_id) ON DELETE CASCADE,
    added_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (collection_id, recipe_id),
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.recipe_collection_items IS 'Links recipes to a user-defined collection.';
CREATE INDEX IF NOT EXISTS idx_recipe_collection_items_collection_id ON public.recipe_collection_items(collection_id);
CREATE INDEX IF NOT EXISTS idx_recipe_collection_items_recipe_id ON public.recipe_collection_items(recipe_id);

-- 44a. Manage shared access to recipe collections.
CREATE TABLE IF NOT EXISTS public.shared_recipe_collections (
    shared_collection_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    recipe_collection_id BIGINT NOT NULL REFERENCES public.recipe_collections(recipe_collection_id) ON DELETE CASCADE,
    shared_by_user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    shared_with_user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    permission_level TEXT NOT NULL CHECK (permission_level IN ('view', 'edit')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(recipe_collection_id, shared_with_user_id)
);

-- 45. Log user search queries for analysis.
CREATE TABLE IF NOT EXISTS public.search_queries (
    search_query_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID REFERENCES public.users(user_id) ON DELETE SET NULL,
    query_text TEXT NOT NULL,
    result_count INTEGER,
    was_successful BOOLEAN,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.search_queries IS 'Logs user search queries to analyze search effectiveness and identify gaps in data.';
COMMENT ON COLUMN public.search_queries.was_successful IS 'Indicates if the user interacted with a search result.';
CREATE INDEX IF NOT EXISTS idx_search_queries_user_id ON public.search_queries(user_id);

-- 46. Store historical records of completed shopping trips.
CREATE TABLE IF NOT EXISTS public.shopping_trips (
    shopping_trip_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    shopping_list_id BIGINT REFERENCES public.shopping_lists(shopping_list_id) ON DELETE SET NULL,
    completed_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    total_spent_cents INTEGER,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.shopping_trips IS 'A historical record of a completed shopping trip.';
COMMENT ON COLUMN public.shopping_trips.total_spent_cents IS 'The total amount spent on this shopping trip, if provided by the user.';
CREATE INDEX IF NOT EXISTS idx_shopping_trips_user_id ON public.shopping_trips(user_id);
CREATE INDEX IF NOT EXISTS idx_shopping_trips_shopping_list_id ON public.shopping_trips(shopping_list_id);

-- 47. Store the items purchased during a specific shopping trip.
CREATE TABLE IF NOT EXISTS public.shopping_trip_items (
    shopping_trip_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    shopping_trip_id BIGINT NOT NULL REFERENCES public.shopping_trips(shopping_trip_id) ON DELETE CASCADE,
    master_item_id BIGINT REFERENCES public.master_grocery_items(master_grocery_item_id),
    custom_item_name TEXT,
    quantity NUMERIC NOT NULL,
    price_paid_cents INTEGER,    
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    CONSTRAINT trip_must_have_item_identifier CHECK (master_item_id IS NOT NULL OR custom_item_name IS NOT NULL)
);
COMMENT ON TABLE public.shopping_trip_items IS 'A historical log of items purchased during a shopping trip.';
COMMENT ON COLUMN public.shopping_trip_items.price_paid_cents IS 'The actual price paid for the item during the trip, if provided.';
CREATE INDEX IF NOT EXISTS idx_shopping_trip_items_shopping_trip_id ON public.shopping_trip_items(shopping_trip_id);
CREATE INDEX IF NOT EXISTS idx_shopping_trip_items_master_item_id ON public.shopping_trip_items(master_item_id);

-- 48. Store predefined dietary restrictions (diets and allergies).
CREATE TABLE IF NOT EXISTS public.dietary_restrictions (
    dietary_restriction_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL CHECK (type IN ('diet', 'allergy')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.dietary_restrictions IS 'A predefined list of common diets (e.g., Vegan) and allergies (e.g., Nut Allergy).';

-- 49. For a user's specific dietary restrictions.
CREATE TABLE IF NOT EXISTS public.user_dietary_restrictions (
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    restriction_id BIGINT NOT NULL REFERENCES public.dietary_restrictions(dietary_restriction_id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, restriction_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.user_dietary_restrictions IS 'Connects users to their selected dietary needs and allergies.';
-- This index is crucial for functions that filter recipes based on user diets/allergies.
CREATE INDEX IF NOT EXISTS idx_user_dietary_restrictions_user_id ON public.user_dietary_restrictions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_dietary_restrictions_restriction_id ON public.user_dietary_restrictions(restriction_id);

-- 50. For a user's owned kitchen appliances.
CREATE TABLE IF NOT EXISTS public.user_appliances (
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    appliance_id BIGINT NOT NULL REFERENCES public.appliances(appliance_id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, appliance_id),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.user_appliances IS 'Tracks the kitchen appliances a user owns to help with recipe recommendations.';
CREATE INDEX IF NOT EXISTS idx_user_appliances_user_id ON public.user_appliances(user_id);
CREATE INDEX IF NOT EXISTS idx_user_appliances_appliance_id ON public.user_appliances(appliance_id);

-- 51. Manage the social graph (following relationships).
CREATE TABLE IF NOT EXISTS public.user_follows (
    follower_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    following_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (follower_id, following_id),    
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    CONSTRAINT cant_follow_self CHECK (follower_id <> following_id)
);
COMMENT ON TABLE public.user_follows IS 'Stores user following relationships to build a social graph.';
-- This index is crucial for efficiently generating a user's activity feed.
CREATE INDEX IF NOT EXISTS idx_user_follows_follower_id ON public.user_follows(follower_id);
CREATE INDEX IF NOT EXISTS idx_user_follows_following_id ON public.user_follows(following_id);

-- 52. Store uploaded user receipts for purchase tracking and analysis.
CREATE TABLE IF NOT EXISTS public.receipts (
    receipt_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    store_id BIGINT REFERENCES public.stores(store_id),
    receipt_image_url TEXT NOT NULL,
    transaction_date TIMESTAMPTZ,
    total_amount_cents INTEGER,
    status TEXT DEFAULT 'pending' NOT NULL CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    raw_text TEXT,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    processed_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.receipts IS 'Stores uploaded user receipts for purchase tracking and analysis.';
CREATE INDEX IF NOT EXISTS idx_receipts_user_id ON public.receipts(user_id);
CREATE INDEX IF NOT EXISTS idx_receipts_store_id ON public.receipts(store_id);

-- 53. Store individual line items extracted from a user receipt.
CREATE TABLE IF NOT EXISTS public.receipt_items (
    receipt_item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    receipt_id BIGINT NOT NULL REFERENCES public.receipts(receipt_id) ON DELETE CASCADE,
    raw_item_description TEXT NOT NULL,
    quantity NUMERIC DEFAULT 1 NOT NULL,
    price_paid_cents INTEGER NOT NULL,
    master_item_id BIGINT REFERENCES public.master_grocery_items(master_grocery_item_id),
    product_id BIGINT REFERENCES public.products(product_id),
    status TEXT DEFAULT 'unmatched' NOT NULL CHECK (status IN ('unmatched', 'matched', 'needs_review', 'ignored')),
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.receipt_items IS 'Stores individual line items extracted from a user receipt.';
CREATE INDEX IF NOT EXISTS idx_receipt_items_receipt_id ON public.receipt_items(receipt_id);
CREATE INDEX IF NOT EXISTS idx_receipt_items_master_item_id ON public.receipt_items(master_item_id);

-- 54. Store schema metadata to detect changes during deployment.
CREATE TABLE IF NOT EXISTS public.schema_info (
    environment TEXT PRIMARY KEY,
    schema_hash TEXT NOT NULL,
    deployed_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.schema_info IS 'Stores metadata about the deployed schema, such as a hash of the schema file, to detect changes.';
COMMENT ON COLUMN public.schema_info.environment IS 'The deployment environment (e.g., ''development'', ''test'', ''production'').';
COMMENT ON COLUMN public.schema_info.schema_hash IS 'A SHA-256 hash of the master_schema_rollup.sql file at the time of deployment.';

-- 55. Store user reactions to various entities (e.g., recipes, comments).
CREATE TABLE IF NOT EXISTS public.user_reactions (
    reaction_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    reaction_type TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    UNIQUE(user_id, entity_type, entity_id, reaction_type)
);
COMMENT ON TABLE public.user_reactions IS 'Stores user reactions (e.g., like, love) to different content types.';
COMMENT ON COLUMN public.user_reactions.entity_type IS 'The type of content being reacted to (e.g., ''recipe'', ''comment'').';
COMMENT ON COLUMN public.user_reactions.entity_id IS 'The ID of the specific content item (e.g., a recipe_id or recipe_comment_id).';
COMMENT ON COLUMN public.user_reactions.reaction_type IS 'The type of reaction (e.g., ''like'', ''love'', ''helpful'').';
CREATE INDEX IF NOT EXISTS idx_user_reactions_user_id ON public.user_reactions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_reactions_entity ON public.user_reactions(entity_type, entity_id);

-- 56. Store user-defined budgets for spending analysis.
CREATE TABLE IF NOT EXISTS public.budgets (
    budget_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    amount_cents INTEGER NOT NULL,
    period TEXT NOT NULL CHECK (period IN ('weekly', 'monthly')),
    start_date DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.budgets IS 'Allows users to set weekly or monthly grocery budgets for spending tracking.';
CREATE INDEX IF NOT EXISTS idx_budgets_user_id ON public.budgets(user_id);

-- 57. Static table defining available achievements for gamification.
CREATE TABLE IF NOT EXISTS public.achievements (
    achievement_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    description TEXT NOT NULL,
    icon TEXT,
    points_value INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
COMMENT ON TABLE public.achievements IS 'A static table defining the available achievements users can earn.';

-- 58. Linking table to award users achievements.
CREATE TABLE IF NOT EXISTS public.user_achievements (
    user_id UUID NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
    achievement_id BIGINT NOT NULL REFERENCES public.achievements(achievement_id) ON DELETE CASCADE,
    achieved_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    PRIMARY KEY (user_id, achievement_id)
);
COMMENT ON TABLE public.user_achievements IS 'A linking table to award users badges or points for completing certain actions.';
CREATE INDEX IF NOT EXISTS idx_user_achievements_user_id ON public.user_achievements(user_id);
CREATE INDEX IF NOT EXISTS idx_user_achievements_achievement_id ON public.user_achievements(achievement_id);



-- ============================================================================
-- PART 2: DATA SEEDING
-- ============================================================================
-- Pre-populate the categories table.
INSERT INTO public.categories (name) VALUES
    ('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')
ON CONFLICT (name) DO NOTHING;

-- Pre-populate the master grocery items dictionary.
DO $$
DECLARE
    fv_cat_id BIGINT; ms_cat_id BIGINT; de_cat_id BIGINT; bb_cat_id BIGINT; pdg_cat_id BIGINT;
    bev_cat_id BIGINT; ff_cat_id BIGINT; snk_cat_id BIGINT; hc_cat_id BIGINT; pch_cat_id BIGINT;
    bc_cat_id BIGINT; ps_cat_id BIGINT; dpf_cat_id BIGINT; cg_cat_id BIGINT; cs_cat_id BIGINT;
    bkc_cat_id BIGINT;
BEGIN
    SELECT category_id INTO fv_cat_id FROM public.categories WHERE name = 'Fruits & Vegetables';
    SELECT category_id INTO ms_cat_id FROM public.categories WHERE name = 'Meat & Seafood';
    SELECT category_id INTO de_cat_id FROM public.categories WHERE name = 'Dairy & Eggs';
    SELECT category_id INTO bb_cat_id FROM public.categories WHERE name = 'Bakery & Bread';
    SELECT category_id INTO pdg_cat_id FROM public.categories WHERE name = 'Pantry & Dry Goods';
    SELECT category_id INTO bev_cat_id FROM public.categories WHERE name = 'Beverages';
    SELECT category_id INTO ff_cat_id FROM public.categories WHERE name = 'Frozen Foods';
    SELECT category_id INTO snk_cat_id FROM public.categories WHERE name = 'Snacks';
    SELECT category_id INTO hc_cat_id FROM public.categories WHERE name = 'Household & Cleaning';
    SELECT category_id INTO pch_cat_id FROM public.categories WHERE name = 'Personal Care & Health';
    SELECT category_id INTO bc_cat_id FROM public.categories WHERE name = 'Baby & Child';
    SELECT category_id INTO ps_cat_id FROM public.categories WHERE name = 'Pet Supplies';
    SELECT category_id INTO dpf_cat_id FROM public.categories WHERE name = 'Deli & Prepared Foods';
    SELECT category_id INTO cg_cat_id FROM public.categories WHERE name = 'Canned Goods';
    SELECT category_id INTO cs_cat_id FROM public.categories WHERE name = 'Condiments & Spices';
    SELECT category_id INTO bkc_cat_id FROM public.categories WHERE name = 'Breakfast & Cereal';

    INSERT INTO public.master_grocery_items (name, category_id) VALUES
    ('apples', fv_cat_id), ('bananas', fv_cat_id), ('oranges', fv_cat_id), ('grapes', fv_cat_id), ('strawberries', fv_cat_id), ('blueberries', fv_cat_id), ('raspberries', fv_cat_id), ('avocados', fv_cat_id), ('tomatoes', fv_cat_id), ('potatoes', fv_cat_id), ('onions', fv_cat_id), ('garlic', fv_cat_id), ('carrots', fv_cat_id), ('broccoli', fv_cat_id), ('spinach', fv_cat_id), ('lettuce', fv_cat_id), ('bell peppers', fv_cat_id), ('cucumbers', fv_cat_id), ('mushrooms', fv_cat_id), ('lemons', fv_cat_id), ('limes', fv_cat_id), ('celery', fv_cat_id), ('corn', fv_cat_id), ('sweet potatoes', fv_cat_id), ('zucchini', fv_cat_id), ('cauliflower', fv_cat_id), ('green beans', fv_cat_id), ('peas', fv_cat_id), ('asparagus', fv_cat_id),
    ('chicken breast', ms_cat_id), ('chicken thighs', ms_cat_id), ('ground beef', ms_cat_id), ('steak', ms_cat_id), ('pork chops', ms_cat_id), ('bacon', ms_cat_id), ('sausage', ms_cat_id), ('salmon', ms_cat_id), ('shrimp', ms_cat_id), ('tilapia', ms_cat_id), ('cod', ms_cat_id), ('tuna', ms_cat_id), ('ham', ms_cat_id), ('turkey', ms_cat_id),
    ('milk', de_cat_id), ('cheese', de_cat_id), ('yogurt', de_cat_id), ('butter', de_cat_id), ('eggs', de_cat_id), ('cream cheese', de_cat_id), ('sour cream', de_cat_id), ('cottage cheese', de_cat_id),
    ('bread', bb_cat_id), ('bagels', bb_cat_id), ('tortillas', bb_cat_id), ('croissants', bb_cat_id), ('muffins', bb_cat_id), ('baguette', bb_cat_id), ('pita bread', bb_cat_id),
    ('rice', pdg_cat_id), ('pasta', pdg_cat_id), ('flour', pdg_cat_id), ('sugar', pdg_cat_id), ('salt', pdg_cat_id), ('pepper', pdg_cat_id), ('olive oil', pdg_cat_id), ('vegetable oil', pdg_cat_id), ('canned tomatoes', pdg_cat_id), ('canned beans', pdg_cat_id), ('peanut butter', pdg_cat_id), ('jam', pdg_cat_id), ('honey', pdg_cat_id), ('syrup', pdg_cat_id), ('nuts', pdg_cat_id), ('dried fruit', pdg_cat_id), ('crackers', pdg_cat_id), ('quinoa', pdg_cat_id), ('lentils', pdg_cat_id),
    ('water', bev_cat_id), ('juice', bev_cat_id), ('soda', bev_cat_id), ('coffee', bev_cat_id), ('tea', bev_cat_id), ('almond milk', bev_cat_id), ('soy milk', bev_cat_id), ('coconut water', bev_cat_id),
    ('frozen pizza', ff_cat_id), ('frozen vegetables', ff_cat_id), ('frozen fruit', ff_cat_id), ('ice cream', ff_cat_id), ('frozen dinners', ff_cat_id), ('french fries', ff_cat_id), ('frozen fish', ff_cat_id),
    ('chips', snk_cat_id), ('pretzels', snk_cat_id), ('popcorn', snk_cat_id), ('granola bars', snk_cat_id), ('cookies', snk_cat_id), ('chocolate', snk_cat_id), ('candy', snk_cat_id),
    ('paper towels', hc_cat_id), ('toilet paper', hc_cat_id), ('trash bags', hc_cat_id), ('dish soap', hc_cat_id), ('laundry detergent', hc_cat_id), ('all-purpose cleaner', hc_cat_id), ('sponges', hc_cat_id),
    ('soap', pch_cat_id), ('shampoo', pch_cat_id), ('conditioner', pch_cat_id), ('toothpaste', pch_cat_id), ('deodorant', pch_cat_id), ('vitamins', pch_cat_id), ('pain reliever', pch_cat_id),
    ('diapers', bc_cat_id), ('baby wipes', bc_cat_id), ('baby food', bc_cat_id), ('formula', bc_cat_id),
    ('dog food', ps_cat_id), ('cat food', ps_cat_id), ('cat litter', ps_cat_id),
    ('deli meat', dpf_cat_id), ('deli cheese', dpf_cat_id), ('rotisserie chicken', dpf_cat_id), ('prepared salads', dpf_cat_id),
    ('canned soup', cg_cat_id), ('canned corn', cg_cat_id), ('canned tuna', cg_cat_id), ('canned chicken', cg_cat_id),
    ('ketchup', cs_cat_id), ('mustard', cs_cat_id), ('mayonnaise', cs_cat_id), ('soy sauce', cs_cat_id), ('hot sauce', cs_cat_id), ('bbq sauce', cs_cat_id), ('salad dressing', cs_cat_id), ('cinnamon', cs_cat_id), ('oregano', cs_cat_id), ('paprika', cs_cat_id), ('garlic powder', cs_cat_id),
    ('cereal', bkc_cat_id), ('oatmeal', bkc_cat_id), ('granola', bkc_cat_id), ('pancake mix', bkc_cat_id)
    ON CONFLICT (name) DO NOTHING;
END $$;

-- Pre-populate recipes, ingredients, and tags to make the recommendation engine effective.
DO $$
DECLARE
    -- Recipe IDs
    chicken_recipe_id BIGINT;
    bolognese_recipe_id BIGINT;
    stir_fry_recipe_id BIGINT;

    -- Store & Brand IDs
    loblaws_id BIGINT; 
    coke_id BIGINT; 
    kraft_id BIGINT; 
    maple_leaf_id BIGINT; 
    dempsters_id BIGINT; 
    no_name_id BIGINT; 
    pc_id BIGINT;

    -- Ingredient (Master Item) IDs
    chicken_breast_id BIGINT; rice_id BIGINT; broccoli_id BIGINT;
    ground_beef_id BIGINT; pasta_item_id BIGINT; tomatoes_id BIGINT; onions_id BIGINT; garlic_id BIGINT;
    bell_peppers_id BIGINT; carrots_id BIGINT; soy_sauce_id BIGINT;
    soda_item_id BIGINT; turkey_item_id BIGINT; bread_item_id BIGINT; cheese_item_id BIGINT;

    -- Tag IDs
    quick_easy_tag BIGINT; healthy_tag BIGINT; chicken_tag BIGINT;
    family_tag BIGINT; beef_tag BIGINT; weeknight_tag BIGINT; vegetarian_tag BIGINT;
BEGIN
    -- Insert a store for the store brands
    INSERT INTO public.stores (name) VALUES ('Loblaws') ON CONFLICT (name) DO NOTHING;
    SELECT store_id INTO loblaws_id FROM public.stores WHERE name = 'Loblaws';

    -- Insert brands and get their IDs
    INSERT INTO public.brands (name) VALUES ('Coca-Cola'), ('Kraft'), ('Maple Leaf'), ('Dempster''s'), ('No Name'), ('President''s Choice')
    ON CONFLICT (name) DO NOTHING;

    -- Insert sample recipes and get their IDs
    INSERT INTO public.recipes (name, description, instructions, prep_time_minutes, cook_time_minutes, servings) VALUES
    ('Simple Chicken and Rice', 'A quick and healthy weeknight meal with chicken, rice, and broccoli.', '1. Cook rice according to package directions. 2. Steam broccoli. 3. Pan-sear chicken breast until cooked through. 4. Combine and serve.', 10, 20, 4),
    ('Classic Spaghetti Bolognese', 'A rich and hearty meat sauce served over spaghetti, perfect for the whole family.', '1. Brown ground beef with onions and garlic. 2. Add tomatoes and simmer for 30 minutes. 3. Cook pasta. 4. Serve sauce over pasta.', 15, 45, 6),
    ('Vegetable Stir-fry', 'A fast, flavorful, and vegetarian stir-fry loaded with fresh vegetables.', '1. Chop all vegetables. 2. Heat oil in a wok or large pan. 3. Stir-fry vegetables for 5-7 minutes until tender-crisp. 4. Add soy sauce and serve immediately.', 10, 10, 3)    
    ON CONFLICT (name) WHERE user_id IS NULL DO NOTHING;

    SELECT recipe_id INTO chicken_recipe_id FROM public.recipes WHERE name = 'Simple Chicken and Rice';
    SELECT recipe_id INTO bolognese_recipe_id FROM public.recipes WHERE name = 'Classic Spaghetti Bolognese';
    SELECT recipe_id INTO stir_fry_recipe_id FROM public.recipes WHERE name = 'Vegetable Stir-fry';

    -- Link store brands to their store
    UPDATE public.brands SET store_id = loblaws_id WHERE name = 'No Name';
    UPDATE public.brands SET store_id = loblaws_id WHERE name = 'President''s Choice';

    SELECT brand_id INTO coke_id FROM public.brands WHERE name = 'Coca-Cola';
    SELECT brand_id INTO kraft_id FROM public.brands WHERE name = 'Kraft';
    SELECT brand_id INTO maple_leaf_id FROM public.brands WHERE name = 'Maple Leaf';
    SELECT brand_id INTO dempsters_id FROM public.brands WHERE name = 'Dempster''s';
    SELECT brand_id INTO no_name_id FROM public.brands WHERE name = 'No Name';
    SELECT brand_id INTO pc_id FROM public.brands WHERE name = 'President''s Choice';

    -- Get ingredient IDs from master_grocery_items
    SELECT mgi.master_grocery_item_id INTO chicken_breast_id FROM public.master_grocery_items mgi WHERE mgi.name = 'chicken breast';
    SELECT mgi.master_grocery_item_id INTO rice_id FROM public.master_grocery_items mgi WHERE mgi.name = 'rice';
    SELECT mgi.master_grocery_item_id INTO broccoli_id FROM public.master_grocery_items mgi WHERE mgi.name = 'broccoli';
    SELECT mgi.master_grocery_item_id INTO ground_beef_id FROM public.master_grocery_items mgi WHERE mgi.name = 'ground beef';
    SELECT mgi.master_grocery_item_id INTO pasta_item_id FROM public.master_grocery_items mgi WHERE mgi.name = 'pasta';
    SELECT mgi.master_grocery_item_id INTO tomatoes_id FROM public.master_grocery_items mgi WHERE mgi.name = 'tomatoes';
    SELECT mgi.master_grocery_item_id INTO onions_id FROM public.master_grocery_items mgi WHERE mgi.name = 'onions';
    SELECT mgi.master_grocery_item_id INTO garlic_id FROM public.master_grocery_items mgi WHERE mgi.name = 'garlic';
    SELECT mgi.master_grocery_item_id INTO bell_peppers_id FROM public.master_grocery_items mgi WHERE mgi.name = 'bell peppers';
    SELECT mgi.master_grocery_item_id INTO carrots_id FROM public.master_grocery_items mgi WHERE mgi.name = 'carrots';
    SELECT mgi.master_grocery_item_id INTO soy_sauce_id FROM public.master_grocery_items mgi WHERE mgi.name = 'soy sauce';
    SELECT mgi.master_grocery_item_id INTO soda_item_id FROM public.master_grocery_items mgi WHERE mgi.name = 'soda';
    SELECT mgi.master_grocery_item_id INTO turkey_item_id FROM public.master_grocery_items mgi WHERE mgi.name = 'turkey';
    SELECT mgi.master_grocery_item_id INTO bread_item_id FROM public.master_grocery_items mgi WHERE mgi.name = 'bread';
    SELECT mgi.master_grocery_item_id INTO cheese_item_id FROM public.master_grocery_items mgi WHERE mgi.name = 'cheese';

    -- Insert ingredients for each recipe
    INSERT INTO public.recipe_ingredients (recipe_id, master_item_id, quantity, unit) VALUES
    (chicken_recipe_id, chicken_breast_id, 2, 'items'), (chicken_recipe_id, rice_id, 200, 'g'), (chicken_recipe_id, broccoli_id, 300, 'g'),
    (bolognese_recipe_id, ground_beef_id, 500, 'g'), (bolognese_recipe_id, pasta_item_id, 400, 'g'), (bolognese_recipe_id, tomatoes_id, 800, 'g'), (bolognese_recipe_id, onions_id, 1, 'items'), (bolognese_recipe_id, garlic_id, 2, 'cloves'),
    (stir_fry_recipe_id, broccoli_id, 200, 'g'), (stir_fry_recipe_id, bell_peppers_id, 1, 'items'), (stir_fry_recipe_id, carrots_id, 2, 'items'), (stir_fry_recipe_id, onions_id, 1, 'items'), (stir_fry_recipe_id, soy_sauce_id, 50, 'ml')
    ON CONFLICT (recipe_ingredient_id) DO NOTHING;

    -- Insert specific products, linking master items and brands
    INSERT INTO public.products (master_item_id, brand_id, name, size, upc_code) VALUES
    (soda_item_id, coke_id, 'Coca-Cola Classic', '2L Bottle', '067000004114'),
    (pasta_item_id, kraft_id, 'Kraft Dinner Original Macaroni & Cheese', '225g Box', '068100058918'),
    (turkey_item_id, maple_leaf_id, 'Maple Leaf Natural Selections Sliced Turkey Breast', '175g', '063100123456'),
    (bread_item_id, dempsters_id, 'Dempster''s 100% Whole Wheat Bread', '675g Loaf', '068721001005'),
    (cheese_item_id, no_name_id, 'No Name Cheddar Cheese Block', '400g', '060383037575'),
    (cheese_item_id, pc_id, 'PC Old Cheddar Cheese', '400g', '060383000005')
    ON CONFLICT (upc_code) DO NOTHING;

    -- Insert tags and get their IDs
    INSERT INTO public.tags (name) VALUES ('Quick & Easy'), ('Healthy'), ('Chicken'), ('Family Friendly'), ('Beef'), ('Weeknight Dinner'), ('Vegetarian')
    ON CONFLICT (name) DO NOTHING;

    SELECT tag_id INTO quick_easy_tag FROM public.tags WHERE name = 'Quick & Easy';
    SELECT tag_id INTO healthy_tag FROM public.tags WHERE name = 'Healthy';
    SELECT tag_id INTO chicken_tag FROM public.tags WHERE name = 'Chicken';
    SELECT tag_id INTO family_tag FROM public.tags WHERE name = 'Family Friendly';
    SELECT tag_id INTO beef_tag FROM public.tags WHERE name = 'Beef';
    SELECT tag_id INTO weeknight_tag FROM public.tags WHERE name = 'Weeknight Dinner';
    SELECT tag_id INTO vegetarian_tag FROM public.tags WHERE name = 'Vegetarian';

    -- Link tags to recipes
    INSERT INTO public.recipe_tags (recipe_id, tag_id) VALUES
    (chicken_recipe_id, quick_easy_tag), (chicken_recipe_id, healthy_tag), (chicken_recipe_id, chicken_tag), (chicken_recipe_id, weeknight_tag),
    (bolognese_recipe_id, family_tag), (bolognese_recipe_id, beef_tag), (bolognese_recipe_id, weeknight_tag),
    (stir_fry_recipe_id, quick_easy_tag), (stir_fry_recipe_id, healthy_tag), (stir_fry_recipe_id, vegetarian_tag)
    ON CONFLICT (recipe_id, tag_id) DO NOTHING;
END $$;

-- Pre-populate the unit_conversions table with common cooking conversions.
DO $$
DECLARE
    flour_id BIGINT; sugar_id BIGINT; butter_id BIGINT; milk_id BIGINT; water_id BIGINT; rice_id BIGINT;
BEGIN
    -- Get master item IDs
    SELECT mgi.master_grocery_item_id INTO flour_id FROM public.master_grocery_items mgi WHERE mgi.name = 'flour';
    SELECT mgi.master_grocery_item_id INTO sugar_id FROM public.master_grocery_items mgi WHERE mgi.name = 'sugar';
    SELECT mgi.master_grocery_item_id INTO butter_id FROM public.master_grocery_items mgi WHERE mgi.name = 'butter';
    SELECT mgi.master_grocery_item_id INTO milk_id FROM public.master_grocery_items mgi WHERE mgi.name = 'milk';
    SELECT mgi.master_grocery_item_id INTO water_id FROM public.master_grocery_items mgi WHERE mgi.name = 'water';
    SELECT mgi.master_grocery_item_id INTO rice_id FROM public.master_grocery_items mgi WHERE mgi.name = 'rice';

    -- Insert conversion factors
    INSERT INTO public.unit_conversions (master_item_id, from_unit, to_unit, factor) VALUES
    -- 1 gram of flour is approx 0.00833 cups
    (flour_id, 'g', 'cup', 0.00833), (flour_id, 'cup', 'g', 120),
    -- 1 gram of sugar is approx 0.005 cups
    (sugar_id, 'g', 'cup', 0.005), (sugar_id, 'cup', 'g', 200),
    -- 1 gram of butter is approx 0.0044 cups
    (butter_id, 'g', 'cup', 0.0044), (butter_id, 'cup', 'g', 227),
    -- 1 ml of water/milk is approx 0.0042 cups
    (water_id, 'ml', 'cup', 0.0042), (water_id, 'cup', 'ml', 240),
    (milk_id, 'ml', 'cup', 0.0042), (milk_id, 'cup', 'ml', 240)
    ON CONFLICT (master_item_id, from_unit, to_unit) DO NOTHING;
END $$;

-- Pre-populate the appliances table.
INSERT INTO public.appliances (name) VALUES
('Oven'), ('Microwave'), ('Stovetop'), ('Blender'), ('Food Processor'), ('Stand Mixer'), ('Hand Mixer'), ('Air Fryer'), ('Instant Pot'), ('Slow Cooker'), ('Grill'), ('Toaster')
ON CONFLICT (name) DO NOTHING;

-- Pre-populate the dietary_restrictions table.
INSERT INTO public.dietary_restrictions (name, type) VALUES
('Vegetarian', 'diet'), ('Vegan', 'diet'), ('Gluten-Free', 'diet'), ('Keto', 'diet'),
('Dairy', 'allergy'), ('Eggs', 'allergy'), ('Fish', 'allergy'), ('Shellfish', 'allergy'),
('Tree Nuts', 'allergy'), ('Peanuts', 'allergy'), ('Soy', 'allergy'), ('Wheat', 'allergy')
ON CONFLICT (name) DO NOTHING;

-- Pre-populate the achievements table.
INSERT INTO public.achievements (name, description, icon, points_value) VALUES
('First Recipe', 'Create your very first recipe.', 'chef-hat', 10),
('Recipe Sharer', 'Share a recipe with another user for the first time.', 'share-2', 15),
('List Sharer', 'Share a shopping list with another user for the first time.', 'list', 20),
('First Favorite', 'Mark a recipe as one of your favorites.', 'heart', 5),
('First Fork', 'Make a personal copy of a public recipe.', 'git-fork', 10),
('First Budget Created', 'Create your first budget to track spending.', 'piggy-bank', 15)
ON CONFLICT (name) DO NOTHING;

-- ============================================================================
-- PART 3: DATABASE FUNCTIONS
-- ============================================================================
-- Function to find the best current sale price for a user's watched items.
-- This function queries all currently active flyers to find the lowest price
DROP FUNCTION IF EXISTS public.get_best_sale_prices_for_user(UUID);

-- for each item on a specific user's watchlist.
CREATE OR REPLACE FUNCTION public.get_best_sale_prices_for_user(p_user_id UUID)
RETURNS TABLE (
    master_item_id BIGINT,
    item_name TEXT,
    best_price_in_cents INTEGER,
    store_name TEXT,
    flyer_id BIGINT,
    flyer_icon_url TEXT,
    flyer_image_url TEXT,
    flyer_valid_from DATE,
    flyer_valid_to DATE
)
LANGUAGE plpgsql
SECURITY INVOKER -- Runs with the privileges of the calling user.
AS $$
BEGIN
    RETURN QUERY
    WITH UserWatchedSales AS (
        -- This CTE gathers all sales from active flyers that match the user's watched items.
        SELECT
            uwi.master_item_id,
            mgi.name AS item_name,
            fi.price_in_cents,
            s.name AS store_name,
        f.flyer_id AS flyer_id,
            f.image_url AS flyer_image_url,
            f.icon_url AS flyer_icon_url,
            f.valid_from AS flyer_valid_from,
            f.valid_to AS flyer_valid_to,
            -- We use ROW_NUMBER to rank sales for the same item, prioritizing the lowest price.
            ROW_NUMBER() OVER (PARTITION BY uwi.master_item_id ORDER BY fi.price_in_cents ASC, f.valid_to DESC, s.name ASC) as rn
        FROM
            public.user_watched_items uwi
    JOIN public.master_grocery_items mgi ON uwi.master_item_id = mgi.master_grocery_item_id
        JOIN public.flyer_items fi ON uwi.master_item_id = fi.master_item_id
    JOIN public.flyers f ON fi.flyer_id = f.flyer_id
    JOIN public.stores s ON f.store_id = s.store_id
        WHERE uwi.user_id = p_user_id
          AND f.valid_from <= CURRENT_DATE
          AND f.valid_to >= CURRENT_DATE
          AND fi.price_in_cents IS NOT NULL
    )
    -- The final select returns only the top-ranked sale (rn = 1) for each item.
    SELECT uws.master_item_id, uws.item_name, uws.price_in_cents, uws.store_name, uws.flyer_id, uws.flyer_icon_url, uws.flyer_image_url, uws.flyer_valid_from, uws.flyer_valid_to
    FROM UserWatchedSales uws
    WHERE uws.rn = 1;
END;
$$;

-- Function to generate a smart shopping list from a menu plan, subtracting pantry items.
DROP FUNCTION IF EXISTS public.generate_shopping_list_for_menu_plan(BIGINT, UUID);

-- This function calculates the total ingredients needed for a user's menu plan,
-- scales them by desired servings, and then subtracts what the user already has
-- in their pantry to determine what needs to be bought.
CREATE OR REPLACE FUNCTION public.generate_shopping_list_for_menu_plan(p_menu_plan_id BIGINT, p_user_id UUID)
RETURNS TABLE (
    master_item_id BIGINT,
    item_name TEXT,
    required_quantity NUMERIC,
    pantry_quantity NUMERIC,
    shopping_list_quantity NUMERIC,
    unit TEXT
)
LANGUAGE plpgsql
SECURITY INVOKER -- Runs with the privileges of the calling user.
AS $$
BEGIN
    RETURN QUERY
    WITH RequiredIngredients AS (
        -- This CTE calculates the total quantity of each ingredient needed for the menu plan.
        -- It accounts for scaling the recipe based on the number of servings the user plans to cook.
        SELECT
            ri.master_item_id,
            ri.unit,
            SUM(
                ri.quantity * -- The base ingredient quantity from the recipe
                -- Calculate the scaling factor. Default to 1 if servings_to_cook is not set.
                (COALESCE(pm.servings_to_cook, r.servings)::NUMERIC / NULLIF(r.servings, 0)::NUMERIC)
            ) AS total_required
        FROM public.menu_plans mp
        JOIN public.planned_meals pm ON mp.menu_plan_id = pm.menu_plan_id
        JOIN public.recipe_ingredients ri ON pm.recipe_id = ri.recipe_id
        JOIN public.recipes r ON pm.recipe_id = r.recipe_id
        WHERE mp.menu_plan_id = p_menu_plan_id AND mp.user_id = p_user_id
        GROUP BY ri.master_item_id, ri.unit
    )
    -- This final select compares the required ingredients with the user's pantry.
    SELECT
        req.master_item_id,
        mgi.name AS item_name,
        req.total_required AS required_quantity,
        COALESCE(pi.quantity, 0) AS pantry_quantity,
        -- Calculate the amount to buy. If pantry has enough, this will be 0 or less, so GREATEST(0, ...) ensures we don't get negative values.
        GREATEST(0, req.total_required - COALESCE(pi.quantity, 0)) AS shopping_list_quantity,
        req.unit
    FROM RequiredIngredients req
    JOIN public.master_grocery_items mgi ON req.master_item_id = mgi.master_grocery_item_id
    LEFT JOIN public.pantry_items pi
        ON req.master_item_id = pi.master_item_id
        AND req.unit = pi.unit -- Critical: only subtract if units match to avoid errors (e.g., subtracting 2 "items" from 500 "grams").
        AND pi.user_id = p_user_id
    WHERE
        -- Only include items that actually need to be purchased.
        GREATEST(0, req.total_required - COALESCE(pi.quantity, 0)) > 0;
END;
$$;

-- Function to find recipes based on the percentage of their ingredients that are currently on sale.
-- For example, you can ask for recipes where at least 50% of the ingredients are on sale.
DROP FUNCTION IF EXISTS public.get_recipes_by_sale_percentage(NUMERIC);

CREATE OR REPLACE FUNCTION public.get_recipes_by_sale_percentage(p_min_sale_percentage NUMERIC DEFAULT 100.0)
RETURNS TABLE (recipe_details JSONB)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH BestCurrentPrices AS (
    -- CTE 1: For every distinct item on sale, find its single best price and the store offering it.
    SELECT
      bcp.master_item_id,
      bcp.price_in_cents,
      bcp.store_name
    FROM (
      SELECT
        fi.master_item_id,
        fi.price_in_cents,
        s.name as store_name,
        ROW_NUMBER() OVER(PARTITION BY fi.master_item_id ORDER BY fi.price_in_cents ASC, f.valid_to DESC) as rn
      FROM public.flyer_items fi
      JOIN public.flyers f ON fi.flyer_id = f.flyer_id
      JOIN public.stores s ON f.store_id = s.store_id
      WHERE fi.master_item_id IS NOT NULL
        AND fi.price_in_cents IS NOT NULL
        AND CURRENT_DATE BETWEEN f.valid_from AND f.valid_to
    ) bcp
    WHERE bcp.rn = 1
  ),
  RecipeIngredientStats AS (
    -- CTE 2: For each recipe, count its total ingredients and how many of them are on sale.
    SELECT
      ri.recipe_id,
      COUNT(ri.master_item_id) AS total_ingredients,
      COUNT(bcp.master_item_id) AS sale_ingredients -- COUNT(column) only counts non-NULL values.
    FROM public.recipe_ingredients ri
    LEFT JOIN BestCurrentPrices bcp ON ri.master_item_id = bcp.master_item_id
    GROUP BY ri.recipe_id
  ),
  EligibleRecipes AS (
    -- CTE 3: Filter recipes based on the minimum sale percentage provided as an argument.
    SELECT
      ris.recipe_id
    FROM RecipeIngredientStats ris
    WHERE ris.total_ingredients > 0 -- Avoid division by zero
      AND (ris.sale_ingredients * 100.0 / ris.total_ingredients) >= p_min_sale_percentage
  ),
  RecipeSaleDetails AS (
    -- CTE 4: Gather details for the eligible recipes and ALL their ingredients, noting which are on sale.
    SELECT
      r.recipe_id AS recipe_id,
      r.name AS recipe_name,
      mgi.name AS item_name,
      bcp.price_in_cents AS best_price_in_cents, -- This will be NULL if the ingredient is not on sale.
      bcp.store_name
    FROM public.recipes r
    JOIN EligibleRecipes er ON r.recipe_id = er.recipe_id
    JOIN public.recipe_ingredients ri ON r.recipe_id = ri.recipe_id
    JOIN public.master_grocery_items mgi ON ri.master_item_id = mgi.master_grocery_item_id
    LEFT JOIN BestCurrentPrices bcp ON ri.master_item_id = bcp.master_item_id -- LEFT JOIN to include all ingredients, not just sale ones.
  )
  -- Final Step: Aggregate the details into a single JSON object for each recipe.
  SELECT
    jsonb_build_object(
      'id', rsd.recipe_id,
      'name', rsd.recipe_name,
      'ingredients', jsonb_agg(
        jsonb_build_object(
          'item_name', rsd.item_name,
          'on_sale', (rsd.best_price_in_cents IS NOT NULL),
          'best_price_in_cents', rsd.best_price_in_cents,
          'store_name', rsd.store_name
        )
        ORDER BY (rsd.best_price_in_cents IS NOT NULL) DESC, rsd.item_name ASC -- Show sale items first in the list.
      )
    )
  FROM RecipeSaleDetails rsd
  GROUP BY rsd.recipe_id, rsd.recipe_name;
$$;

-- Function to add items generated from a menu plan directly to a user's shopping list.
DROP FUNCTION IF EXISTS public.add_menu_plan_to_shopping_list(BIGINT, BIGINT, UUID);

-- This acts as a utility function to chain `generate_shopping_list_for_menu_plan` with an INSERT action.
CREATE OR REPLACE FUNCTION public.add_menu_plan_to_shopping_list(
    p_menu_plan_id BIGINT,
    p_shopping_list_id BIGINT,
    p_user_id UUID
)
RETURNS TABLE (
    master_item_id BIGINT,
    item_name TEXT,
    quantity_added NUMERIC
)
LANGUAGE plpgsql
-- SECURITY DEFINER is used here to perform actions with elevated privileges,
-- but it's safe because we first perform a strict ownership check inside the function.
SECURITY DEFINER
AS $$
DECLARE
    list_owner_id UUID;
    item_to_add RECORD;
BEGIN
    -- Security Check: Ensure the user calling this function owns the target shopping list.
    SELECT user_id INTO list_owner_id
    FROM public.shopping_lists
    WHERE shopping_list_id = p_shopping_list_id;

    IF list_owner_id IS NULL OR list_owner_id <> p_user_id THEN
        RAISE EXCEPTION 'Permission denied: You do not own shopping list %', p_shopping_list_id;
    END IF;

    -- Loop through the items generated by the smart shopping list function.
    FOR item_to_add IN
        SELECT * FROM public.generate_shopping_list_for_menu_plan(p_menu_plan_id, p_user_id)
    LOOP
        -- Insert the item into the shopping list. If it already exists, add to the quantity.
        INSERT INTO public.shopping_list_items (shopping_list_id, master_item_id, quantity)
        VALUES (p_shopping_list_id, item_to_add.master_item_id, item_to_add.shopping_list_quantity)
        ON CONFLICT (shopping_list_id, master_item_id)
        DO UPDATE SET
            quantity = shopping_list_items.quantity + EXCLUDED.quantity;

        -- Return the details of the item that was added/updated.
        RETURN QUERY SELECT item_to_add.master_item_id, item_to_add.item_name, item_to_add.shopping_list_quantity;
    END LOOP;
END;
$$;

-- Function to find recipes that have at least a specified number of ingredients currently on sale.
DROP FUNCTION IF EXISTS public.get_recipes_by_min_sale_ingredients(INTEGER);

CREATE OR REPLACE FUNCTION public.get_recipes_by_min_sale_ingredients(p_min_sale_ingredients INTEGER)
RETURNS TABLE (
    recipe_id BIGINT,
    recipe_name TEXT,
    description TEXT,
    sale_ingredients_count BIGINT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH CurrentSaleItems AS (
    -- CTE 1: Get a distinct list of all master item IDs that are currently on sale.
    SELECT DISTINCT fi.master_item_id
    FROM public.flyer_items fi
    JOIN public.flyers f ON fi.flyer_id = f.flyer_id
    WHERE fi.master_item_id IS NOT NULL
      AND fi.price_in_cents IS NOT NULL
      AND CURRENT_DATE BETWEEN f.valid_from AND f.valid_to
  ),
  RecipeIngredientStats AS (
    -- CTE 2: For each recipe, count how many of its ingredients are on the sale list.
    SELECT
      ri.recipe_id,
      COUNT(csi.master_item_id) AS sale_ingredients_count
    FROM public.recipe_ingredients ri
    LEFT JOIN CurrentSaleItems csi ON ri.master_item_id = csi.master_item_id
    GROUP BY ri.recipe_id
  )
  -- Final Step: Select recipes that meet the minimum sale ingredient count and order them.
  SELECT
    r.recipe_id,
    r.name,
    r.description,
    ris.sale_ingredients_count
  FROM public.recipes r
  JOIN RecipeIngredientStats ris ON r.recipe_id = ris.recipe_id
  WHERE ris.sale_ingredients_count >= p_min_sale_ingredients
  ORDER BY
    ris.sale_ingredients_count DESC,
    r.avg_rating DESC;
$$;

-- Function to find the most frequently advertised items in a given period.
DROP FUNCTION IF EXISTS public.get_most_frequent_sale_items(INTEGER, INTEGER);

-- This helps identify which items go on sale most often.
CREATE OR REPLACE FUNCTION public.get_most_frequent_sale_items(days_interval INTEGER, result_limit INTEGER)
RETURNS TABLE (
    item_name TEXT,
    sale_occurrence_count BIGINT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
      mgi.name AS item_name,
      COUNT(DISTINCT fi.flyer_id) AS sale_occurrence_count -- Count distinct flyers the item appeared in
  FROM
      public.flyer_items fi
  JOIN
      public.flyers f ON fi.flyer_id = f.flyer_id
  JOIN
      public.master_grocery_items mgi ON fi.master_item_id = mgi.master_grocery_item_id
  WHERE
      fi.master_item_id IS NOT NULL
      -- Filter for flyers that have been active in the last X days.
      AND f.valid_to >= (CURRENT_DATE - (days_interval || ' days')::INTERVAL)
      AND f.valid_from <= CURRENT_DATE
  GROUP BY
      mgi.master_grocery_item_id, mgi.name
  ORDER BY
      sale_occurrence_count DESC
  LIMIT result_limit;
$$;

-- Function to find recipes by a specific ingredient AND a specific tag.
-- This allows for more refined recipe searching, e.g., "Find me a quick & easy recipe with chicken breast".
-- We drop it first to handle cases where the return signature might change during development.
DROP FUNCTION IF EXISTS public.find_recipes_by_ingredient_and_tag(TEXT, TEXT);

CREATE OR REPLACE FUNCTION public.find_recipes_by_ingredient_and_tag(p_ingredient_name TEXT, p_tag_name TEXT)
RETURNS TABLE (
    recipe_id BIGINT,
    name TEXT,
    description TEXT,
    prep_time_minutes INTEGER,
    cook_time_minutes INTEGER,
    avg_rating NUMERIC
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
      r.recipe_id, r.name, r.description, r.prep_time_minutes, r.cook_time_minutes, r.avg_rating
  FROM
      public.recipes r
  WHERE
      -- Check that the recipe has the required ingredient using an EXISTS subquery.
      EXISTS (
          SELECT 1 FROM public.recipe_ingredients ri
          JOIN public.master_grocery_items mgi ON ri.master_item_id = mgi.master_grocery_item_id
          WHERE ri.recipe_id = r.recipe_id AND mgi.name = p_ingredient_name
      )
      AND
      -- Check that the recipe has the required tag using another EXISTS subquery.
      EXISTS (
          SELECT 1 FROM public.recipe_tags rt
          JOIN public.tags t ON rt.tag_id = t.tag_id
          WHERE rt.recipe_id = r.recipe_id AND t.name = p_tag_name
      )
  ORDER BY
      r.avg_rating DESC, r.name ASC;
$$;

-- Function to suggest a master_item_id for a given flyer item name.
DROP FUNCTION IF EXISTS public.suggest_master_item_for_flyer_item(TEXT);

-- This function uses trigram similarity to find the best match from both the
-- master_grocery_items table and the master_item_aliases table.
CREATE OR REPLACE FUNCTION public.suggest_master_item_for_flyer_item(p_flyer_item_name TEXT)
RETURNS BIGINT
LANGUAGE plpgsql
STABLE -- This function does not modify the database.
AS $$
DECLARE
    suggested_id BIGINT;
    -- A similarity score between 0 and 1. A higher value means a better match.
    -- This threshold can be adjusted based on observed performance. 0.4 is a reasonable starting point.
    similarity_threshold REAL := 0.4;
BEGIN
    WITH candidates AS (
        -- Search for matches in the primary master_grocery_items table
        SELECT
            master_grocery_item_id AS master_item_id,
            similarity(name, p_flyer_item_name) AS score
        FROM public.master_grocery_items
        WHERE name % p_flyer_item_name -- The '%' operator uses the trigram index for pre-filtering, making the search much faster.

        UNION ALL

        -- Search for matches in the master_item_aliases table
        SELECT
            master_item_id,
            similarity(alias, p_flyer_item_name) AS score
        FROM public.master_item_aliases
        WHERE alias % p_flyer_item_name
    )
    -- Select the master_item_id with the highest similarity score, provided it's above our threshold.
    SELECT master_item_id INTO suggested_id FROM candidates WHERE score >= similarity_threshold ORDER BY score DESC, master_item_id LIMIT 1;

    RETURN suggested_id;
END;
$$;

-- Function to find recipes that can be made entirely from items in a user's pantry.
DROP FUNCTION IF EXISTS public.find_recipes_from_pantry(UUID);

-- This function checks each recipe and returns it only if every ingredient is present
-- in the specified user's pantry.
CREATE OR REPLACE FUNCTION public.find_recipes_from_pantry(p_user_id UUID)
RETURNS TABLE(
    recipe_id BIGINT,
    name TEXT,
    description TEXT,
    prep_time_minutes INTEGER,
    cook_time_minutes INTEGER,
    avg_rating NUMERIC,
    missing_ingredients_count BIGINT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH UserPantryItems AS (
    -- CTE 1: Get a distinct set of master item IDs from the user's pantry.
    SELECT master_item_id, quantity, unit
    FROM public.pantry_items
    WHERE user_id = p_user_id AND quantity > 0
  ),
  RecipeIngredientStats AS (
    -- CTE 2: For each recipe, count its total ingredients and how many of those are in the user's pantry.
    SELECT
        ri.recipe_id,
        -- Count how many ingredients DO NOT meet the pantry requirements.
        -- An ingredient is missing if it's not in the pantry OR if the quantity is insufficient.
        -- The filter condition handles this logic.
        COUNT(*) FILTER (
            WHERE upi.master_item_id IS NULL -- The item is not in the pantry at all
            OR upi.quantity < ri.quantity -- The user has the item, but not enough of it
        ) AS missing_ingredients_count
    FROM public.recipe_ingredients ri
    -- LEFT JOIN to the user's pantry on both item and unit.
    -- We only compare quantities if the units match (e.g., 'g' vs 'g').
    LEFT JOIN UserPantryItems upi
        ON ri.master_item_id = upi.master_item_id
        AND ri.unit = upi.unit
    GROUP BY ri.recipe_id
  )
  -- Final Step: Select recipes where the total ingredient count matches the pantry ingredient count.
  SELECT
    r.recipe_id,
    r.name,
    r.description,
    r.prep_time_minutes,
    r.cook_time_minutes,
    r.avg_rating,
    ris.missing_ingredients_count
  FROM public.recipes r
  JOIN RecipeIngredientStats ris ON r.recipe_id = ris.recipe_id
  -- Order by recipes with the fewest missing ingredients first, then by rating.
  -- Recipes with 0 missing ingredients are the ones that can be made.
  ORDER BY ris.missing_ingredients_count ASC, r.avg_rating DESC, r.name ASC;
$$;

-- Function to suggest alternative units for a given pantry item.
DROP FUNCTION IF EXISTS public.suggest_pantry_item_conversions(BIGINT);

-- For example, if a user has 500g of flour, this function might suggest "4.1 cups".
CREATE OR REPLACE FUNCTION public.suggest_pantry_item_conversions(p_pantry_item_id BIGINT)
RETURNS TABLE (
    suggested_quantity NUMERIC,
    suggested_unit TEXT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    -- Calculate the converted quantity by multiplying the original quantity by the conversion factor.
    -- Round to 2 decimal places for readability.
    ROUND(pi.quantity * uc.factor, 2) AS suggested_quantity,
    uc.to_unit AS suggested_unit
  FROM public.pantry_items pi
  -- Join with the unit_conversions table to find available conversion rules.
  JOIN public.unit_conversions uc
    ON pi.master_item_id = uc.master_item_id
    AND pi.unit = uc.from_unit
  WHERE
    pi.pantry_item_id = p_pantry_item_id
    -- Exclude suggesting a conversion back to the same unit.
    AND pi.unit <> uc.to_unit;
$$;

-- Function to recommend recipes to a user based on their watched items and highly-rated recipes.
DROP FUNCTION IF EXISTS public.recommend_recipes_for_user(UUID, INTEGER);

-- It calculates a score based on ingredient matches from the user's watchlist and similarity
-- to other recipes the user has liked.
CREATE OR REPLACE FUNCTION public.recommend_recipes_for_user(p_user_id UUID, p_limit INTEGER DEFAULT 10)
RETURNS TABLE (
    recipe_id BIGINT,
    recipe_name TEXT,
    recipe_description TEXT,
    avg_rating NUMERIC,
    recommendation_score NUMERIC,
    recommendation_reason TEXT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
WITH UserHighRatedRecipes AS (
    -- CTE 1: Get recipes the user has rated 4 stars or higher.
    SELECT rr.recipe_id, rr.rating
    FROM public.recipe_ratings rr
    WHERE rr.user_id = p_user_id AND rr.rating >= 4
),
UserWatchedItems AS (
    -- CTE 2: Get the user's watchlist of grocery items.
    SELECT uwi.master_item_id
    FROM public.user_watched_items uwi
    WHERE uwi.user_id = p_user_id
),
RecipeScores AS (
    -- CTE 3: Calculate a score for each recipe based on two factors.
    SELECT
        r.recipe_id AS recipe_id,
        -- Score from watched items: +5 points for each watched ingredient in the recipe.
        (
            SELECT 5 * COUNT(*)
            FROM public.recipe_ingredients ri
            WHERE ri.recipe_id = r.recipe_id AND ri.master_item_id IN (SELECT master_item_id FROM UserWatchedItems)
        ) AS watched_item_score,
        -- Score from similarity to highly-rated recipes.
        (
            SELECT COALESCE(SUM(
                -- +2 points for each shared ingredient with a highly-rated recipe.
                (
                    SELECT 2 * COUNT(*)
                    FROM public.recipe_ingredients ri1
                    JOIN public.recipe_ingredients ri2 ON ri1.master_item_id = ri2.master_item_id
                    WHERE ri1.recipe_id = r.recipe_id AND ri2.recipe_id = uhr.recipe_id
                ) +
                -- +3 points for each shared tag with a highly-rated recipe.
                (
                    SELECT 3 * COUNT(*)
                    FROM public.recipe_tags rt1
                    JOIN public.recipe_tags rt2 ON rt1.tag_id = rt2.tag_id
                    WHERE rt1.recipe_id = r.recipe_id AND rt2.recipe_id = uhr.recipe_id
                )
            ), 0)
            FROM UserHighRatedRecipes uhr
            WHERE uhr.recipe_id <> r.recipe_id -- Don't compare a recipe to itself.
        ) AS similarity_score
    FROM public.recipes r
),
RankedRecommendations AS (
    -- CTE 4: Combine scores and generate a human-readable reason for the recommendation.
    SELECT
        rs.recipe_id,
        rs.watched_item_score + rs.similarity_score AS total_score,
        -- Create a reason string based on which score is higher.
        CASE
            WHEN rs.watched_item_score > rs.similarity_score THEN 'Contains items from your watchlist'
            WHEN rs.similarity_score > 0 THEN 'Similar to recipes you''ve liked'
            ELSE 'A popular recipe you might like'
        END AS reason
    FROM RecipeScores rs
    WHERE rs.watched_item_score + rs.similarity_score > 0
      -- Exclude recipes the user has already rated to avoid recommending things they've already seen.
      AND rs.recipe_id NOT IN (SELECT recipe_id FROM public.recipe_ratings WHERE user_id = p_user_id)
)
-- Final Selection: Join back to the recipes table to get full details and order by the final score.
SELECT
    r.recipe_id,
    r.name,
    r.description,
    r.avg_rating,
    rr.total_score,
    rr.reason
FROM RankedRecommendations rr
JOIN public.recipes r ON rr.recipe_id = r.recipe_id
ORDER BY
    rr.total_score DESC,
    r.avg_rating DESC, -- As a tie-breaker, prefer higher-rated recipes.
    r.rating_count DESC,
    r.name ASC
LIMIT p_limit;
$$;

-- Function to get a user's favorite recipes.
DROP FUNCTION IF EXISTS public.get_user_favorite_recipes(UUID);

CREATE OR REPLACE FUNCTION public.get_user_favorite_recipes(p_user_id UUID)
RETURNS TABLE (
    recipe_id BIGINT,
    name TEXT,
    description TEXT,
    avg_rating NUMERIC,
    photo_url TEXT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    r.recipe_id, r.name, r.description, r.avg_rating, r.photo_url
  FROM public.recipes r
  JOIN public.favorite_recipes fr ON r.recipe_id = fr.recipe_id
  WHERE fr.user_id = p_user_id
  ORDER BY r.name ASC;
$$;

-- Function to get a paginated list of recent activities for the audit log.
DROP FUNCTION IF EXISTS public.get_activity_log(INTEGER, INTEGER);

CREATE OR REPLACE FUNCTION public.get_activity_log(p_limit INTEGER DEFAULT 20, p_offset INTEGER DEFAULT 0)
RETURNS TABLE (
    activity_log_id BIGINT,
    user_id UUID,    
    action TEXT,
    display_text TEXT,
    icon TEXT,
    details JSONB,
    created_at TIMESTAMPTZ,
    user_full_name TEXT, -- Added
    user_avatar_url TEXT -- Added
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    al.activity_log_id, al.user_id, al.action, al.display_text, al.icon, al.details, al.created_at,
    p.full_name, p.avatar_url
  FROM public.activity_log al
  LEFT JOIN public.profiles p ON al.user_id = p.user_id
  ORDER BY al.created_at DESC
  LIMIT p_limit
  OFFSET p_offset;
$$;

-- Function to get a user's profile by their ID, combining data from users and profiles tables.
DROP FUNCTION IF EXISTS public.get_user_profile_by_id(UUID);

CREATE OR REPLACE FUNCTION public.get_user_profile_by_id(p_user_id UUID)
RETURNS TABLE (
    user_id UUID,
    email TEXT,
    full_name TEXT,
    avatar_url TEXT,
    preferences JSONB,
    role TEXT,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    u.user_id,
    u.email,
    p.full_name,
    p.avatar_url,
    p.preferences,
    p.role,
    p.created_at,
    p.updated_at
  FROM public.users u
  JOIN public.profiles p ON u.user_id = p.user_id
  WHERE u.user_id = p_user_id;
$$;

-- Function to get recipes that are compatible with a user's dietary restrictions (allergies).
DROP FUNCTION IF EXISTS public.get_recipes_for_user_diets(UUID);

-- It filters out any recipe containing an ingredient that the user is allergic to.
CREATE OR REPLACE FUNCTION public.get_recipes_for_user_diets(p_user_id UUID)
RETURNS SETOF public.recipes
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH UserAllergens AS (
    -- CTE 1: Find all master item IDs that are allergens for the given user.
    SELECT mgi.master_grocery_item_id
    FROM public.master_grocery_items mgi
    JOIN public.dietary_restrictions dr ON mgi.allergy_info->>'type' = dr.name
    JOIN public.user_dietary_restrictions udr ON dr.dietary_restriction_id = udr.restriction_id
    WHERE udr.user_id = p_user_id
      AND dr.type = 'allergy'
      AND mgi.is_allergen = true
  ),
  ForbiddenRecipes AS (
    -- CTE 2: Find all recipe IDs that contain one or more of the user's allergens.
    SELECT DISTINCT ri.recipe_id
    FROM public.recipe_ingredients ri
    WHERE ri.master_item_id IN (SELECT master_grocery_item_id FROM UserAllergens)
  )
  -- Final Selection: Return all recipes that are NOT in the forbidden list.
  SELECT *
  FROM public.recipes r
  WHERE r.recipe_id NOT IN (SELECT recipe_id FROM ForbiddenRecipes)
  ORDER BY r.avg_rating DESC, r.name ASC;
$$;

-- Function to get a personalized activity feed for a user based on who they follow.
DROP FUNCTION IF EXISTS public.get_user_feed(UUID, INTEGER, INTEGER);

-- It aggregates recent activities from followed users.
CREATE OR REPLACE FUNCTION public.get_user_feed(p_user_id UUID, p_limit INTEGER DEFAULT 20, p_offset INTEGER DEFAULT 0)
RETURNS TABLE (
    activity_log_id BIGINT,
    user_id UUID,
    action TEXT,
    display_text TEXT,
    icon TEXT,
    details JSONB,
    created_at TIMESTAMPTZ,
    user_full_name TEXT,
    user_avatar_url TEXT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH FollowedUsers AS (
    -- CTE 1: Get the IDs of all users that the current user is following.
    SELECT following_id FROM public.user_follows WHERE follower_id = p_user_id
  )
  -- Final Selection: Get activities from the log where the user_id is in the followed list.
  SELECT
    al.activity_log_id, al.user_id, al.action, al.display_text, al.icon, al.details, al.created_at,
    p.full_name, p.avatar_url
  FROM public.activity_log al
  JOIN public.profiles p ON al.user_id = p.user_id
  WHERE
    al.user_id IN (SELECT following_id FROM FollowedUsers)
    -- We can filter for specific action types to make the feed more relevant.
    AND al.action IN (
        'recipe_created',
        'recipe_favorited',
        'list_shared'
        -- 'new_recipe_rating' could be added here later
    )
  ORDER BY al.created_at DESC, al.display_text, al.icon
  LIMIT p_limit
  OFFSET p_offset;
$$;

-- Function to archive a shopping list into a historical shopping trip.
DROP FUNCTION IF EXISTS public.complete_shopping_list(BIGINT, UUID, INTEGER);

-- It creates a shopping_trip record, copies purchased items to shopping_trip_items,
-- and then deletes the purchased items from the original shopping list.
CREATE OR REPLACE FUNCTION public.complete_shopping_list(
    p_shopping_list_id BIGINT,
    p_user_id UUID,
    p_total_spent_cents INTEGER DEFAULT NULL
)
RETURNS BIGINT -- Returns the ID of the new shopping_trip record.
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    list_owner_id UUID;
    new_trip_id BIGINT;
BEGIN
    -- Security Check: Ensure the user calling this function owns the target shopping list.
    SELECT user_id INTO list_owner_id
    FROM public.shopping_lists
    WHERE shopping_list_id = p_shopping_list_id;

    IF list_owner_id IS NULL OR list_owner_id <> p_user_id THEN
        RAISE EXCEPTION 'Permission denied: You do not own shopping list %', p_shopping_list_id;
    END IF;

    -- 1. Create a new shopping_trip record.
    INSERT INTO public.shopping_trips (user_id, shopping_list_id, total_spent_cents)
    VALUES (p_user_id, p_shopping_list_id, p_total_spent_cents)
    RETURNING shopping_trip_id INTO new_trip_id;

    -- 2. Copy purchased items from the shopping list to the new shopping_trip_items table.
    INSERT INTO public.shopping_trip_items (shopping_trip_id, master_item_id, custom_item_name, quantity)
    SELECT new_trip_id, master_item_id, custom_item_name, quantity
    FROM public.shopping_list_items
    WHERE shopping_list_id = p_shopping_list_id AND is_purchased = true;

    -- 3. Delete the purchased items from the original shopping list.
    DELETE FROM public.shopping_list_items
    WHERE shopping_list_id = p_shopping_list_id AND is_purchased = true;

    RETURN new_trip_id;
END;
$$;

-- Function to find better deals for items on a recently processed receipt.
DROP FUNCTION IF EXISTS public.find_deals_for_receipt_items(BIGINT);

-- It compares the price paid on the receipt with current flyer prices.
CREATE OR REPLACE FUNCTION public.find_deals_for_receipt_items(p_receipt_id BIGINT)
RETURNS TABLE (
    receipt_item_id BIGINT,
    master_item_id BIGINT,
    item_name TEXT,
    price_paid_cents INTEGER,
    current_best_price_in_cents INTEGER,
    potential_savings_cents INTEGER,
    deal_store_name TEXT,
    flyer_id BIGINT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH ReceiptItems AS (
    -- CTE 1: Get all matched items from the specified receipt.
    SELECT
      ri.receipt_item_id AS receipt_item_id,
      ri.master_item_id,
      mgi.name AS item_name,
      ri.price_paid_cents
    FROM public.receipt_items ri
    JOIN public.master_grocery_items mgi ON ri.master_item_id = mgi.master_grocery_item_id
    WHERE ri.receipt_id = p_receipt_id
      AND ri.master_item_id IS NOT NULL
  ),
  BestCurrentPrices AS (
    -- CTE 2: Find the single best price for every item currently on sale.
    SELECT DISTINCT ON (fi.master_item_id)
      fi.master_item_id,
      fi.price_in_cents,
      s.name AS store_name,
      f.flyer_id AS flyer_id
    FROM public.flyer_items fi
    JOIN public.flyers f ON fi.flyer_id = f.flyer_id
    JOIN public.stores s ON f.store_id = s.store_id
    WHERE fi.master_item_id IS NOT NULL
      AND fi.price_in_cents IS NOT NULL
      AND CURRENT_DATE BETWEEN f.valid_from AND f.valid_to
    ORDER BY fi.master_item_id, fi.price_in_cents ASC
  )
  -- Final Selection: Join receipt items with current deals and find savings.
  SELECT
    ri.receipt_item_id,
    ri.master_item_id,
    ri.item_name,
    ri.price_paid_cents,
    bcp.price_in_cents AS current_best_price_in_cents,
    (ri.price_paid_cents - bcp.price_in_cents) AS potential_savings_cents,
    bcp.store_name AS deal_store_name,
    bcp.flyer_id
  FROM ReceiptItems ri
  JOIN BestCurrentPrices bcp ON ri.master_item_id = bcp.master_item_id
  -- Only return rows where the current sale price is better than the price paid.
  WHERE bcp.price_in_cents < ri.price_paid_cents
  ORDER BY potential_savings_cents DESC;
$$;

-- Function to approve a suggested correction and apply it.
DROP FUNCTION IF EXISTS public.approve_correction(BIGINT);

-- This is a SECURITY DEFINER function to allow an admin to update tables
-- they might not have direct RLS access to.
CREATE OR REPLACE FUNCTION public.approve_correction(p_correction_id BIGINT)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    correction_record RECORD;
BEGIN
    -- 1. Fetch the correction details, ensuring it's still pending.
    SELECT * INTO correction_record
    FROM public.suggested_corrections
    WHERE suggested_correction_id = p_correction_id AND status = 'pending';

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Correction with ID % not found or already processed.', p_correction_id;
    END IF;

    -- 2. Apply the correction based on its type.
    IF correction_record.correction_type = 'INCORRECT_ITEM_LINK' THEN
        UPDATE public.flyer_items
        SET master_item_id = correction_record.suggested_value::BIGINT
        WHERE flyer_item_id = correction_record.flyer_item_id;
    ELSIF correction_record.correction_type = 'WRONG_PRICE' THEN
        UPDATE public.flyer_items
        SET price_in_cents = correction_record.suggested_value::INTEGER
        WHERE flyer_item_id = correction_record.flyer_item_id;
    END IF;

    -- 3. Update the correction status to 'approved'.
    UPDATE public.suggested_corrections
    SET status = 'approved', reviewed_at = now()
    WHERE suggested_correction_id = p_correction_id;
END;
$$;

-- Function to award an achievement to a user if they don't already have it.
DROP FUNCTION IF EXISTS public.award_achievement(UUID, TEXT);

CREATE OR REPLACE FUNCTION public.award_achievement(p_user_id UUID, p_achievement_name TEXT)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER -- Allows updating points on the profile table.
AS $$
DECLARE
    v_achievement_id BIGINT;
    v_points_value INTEGER;
BEGIN
    -- Find the achievement by name to get its ID and point value.
    SELECT achievement_id, points_value INTO v_achievement_id, v_points_value
    FROM public.achievements WHERE name = p_achievement_name;

    -- If the achievement doesn't exist, do nothing.
    IF v_achievement_id IS NULL THEN
        RETURN;
    END IF;

    -- Insert the achievement for the user.
    -- ON CONFLICT DO NOTHING ensures that if the user already has the achievement,
    -- we don't try to insert it again, and the rest of the function is skipped.
    INSERT INTO public.user_achievements (user_id, achievement_id)
    VALUES (p_user_id, v_achievement_id)
    ON CONFLICT (user_id, achievement_id) DO NOTHING;

    -- If the insert was successful (i.e., the user didn't have the achievement),
    -- update their total points. The `GET DIAGNOSTICS` command checks the row count of the last query.
    IF FOUND THEN
        UPDATE public.profiles SET points = points + v_points_value WHERE user_id = p_user_id;
    END IF;
END;
$$;

/**
 * Creates a personal, editable copy (a "fork") of a public recipe for a user.
 * @param p_user_id The ID of the user forking the recipe.
 * Creates a personal, editable copy (a "fork") of a public recipe for a user.
 * @param userId The ID of the user forking the recipe.
 * @param originalRecipeId The ID of the recipe to fork.
 * @returns A promise that resolves to the newly created forked Recipe object.
 */
-- Function to create a personal, editable copy (a "fork") of a public recipe for a user.
DROP FUNCTION IF EXISTS public.fork_recipe(UUID, BIGINT);

CREATE OR REPLACE FUNCTION public.fork_recipe(p_user_id UUID, p_original_recipe_id BIGINT)
RETURNS SETOF public.recipes
LANGUAGE sql
SECURITY INVOKER
AS $$
    -- The entire forking logic is now encapsulated in a single, atomic database function.
    SELECT * FROM public.fork_recipe(p_user_id, p_original_recipe_id);
$$;


-- ============================================================================
-- PART 4: TRIGGERS
-- ============================================================================

-- 1. Set up the trigger to automatically create a profile when a new user signs up.
-- This function is adapted to work with the new `public.users` table.
DROP FUNCTION IF EXISTS public.handle_new_user();

-- It no longer needs to read from `new.raw_user_meta_data`.
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  new_profile_id UUID;
  user_meta_data JSONB;
BEGIN
  -- The user's metadata (full_name, avatar_url) is passed via a temporary session variable.
  user_meta_data := current_setting('my_app.user_metadata', true)::JSONB;

  INSERT INTO public.profiles (user_id, role, full_name, avatar_url)
  VALUES (new.user_id, 'user', user_meta_data->>'full_name', user_meta_data->>'avatar_url')
  RETURNING user_id INTO new_profile_id;

  -- Also create a default shopping list for the new user.
  INSERT INTO public.shopping_lists (user_id, name)
  VALUES (new.user_id, 'Main Shopping List');

  -- Log the new user event
  INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
  VALUES (new.user_id, 'user_registered',
          COALESCE(user_meta_data->>'full_name', new.email) || ' has registered.',
          'user-plus',
          jsonb_build_object('email', new.email)
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- PART 4: TRIGGERS
-- ============================================================================

-- This trigger calls the function after a new user is created.
DROP TRIGGER IF EXISTS on_auth_user_created ON public.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON public.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- 2. Create a reusable function to automatically update 'updated_at' columns.
DROP FUNCTION IF EXISTS public.handle_updated_at();

CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Dynamically apply the 'handle_updated_at' trigger to all tables in the public schema
-- that have an 'updated_at' column. This is more maintainable than creating a separate
-- trigger for each table.
DO $$
DECLARE
    t_name TEXT;
BEGIN
    FOR t_name IN
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = 'public' AND column_name = 'updated_at'
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS on_%s_updated ON public.%I;
                        CREATE TRIGGER on_%s_updated
                        BEFORE UPDATE ON public.%I
                        FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();',
                        t_name, t_name, t_name, t_name);
    END LOOP;
END;
$$;

-- 3. Create a trigger function to populate the item_price_history table on insert.
DROP FUNCTION IF EXISTS public.update_price_history_on_flyer_item_insert();

CREATE OR REPLACE FUNCTION public.update_price_history_on_flyer_item_insert()
RETURNS TRIGGER AS $$
DECLARE
    flyer_valid_from DATE;
    flyer_valid_to DATE;
    current_summary_date DATE;
    flyer_location_id BIGINT;
BEGIN
    -- If the item could not be matched, add it to the unmatched queue for review.
    IF NEW.master_item_id IS NULL THEN
        INSERT INTO public.unmatched_flyer_items (flyer_item_id)
        VALUES (NEW.flyer_item_id)
        ON CONFLICT (flyer_item_id) DO NOTHING;
    END IF;

    -- Only run if the new flyer item is linked to a master item and has a price.
    IF NEW.master_item_id IS NULL OR NEW.price_in_cents IS NULL THEN
        RETURN NEW;
    END IF;

    -- Get the validity dates of the flyer and the store_id.
    SELECT valid_from, valid_to INTO flyer_valid_from, flyer_valid_to
    FROM public.flyers
    WHERE flyer_id = NEW.flyer_id;

    -- This single, set-based query is much more performant than looping.
    -- It generates all date/location pairs and inserts/updates them in one operation.
    INSERT INTO public.item_price_history (master_item_id, summary_date, store_location_id, min_price_in_cents, max_price_in_cents, avg_price_in_cents, data_points_count)
    SELECT
        NEW.master_item_id,
        d.day,
        fl.store_location_id,
        NEW.price_in_cents,
        NEW.price_in_cents,
        NEW.price_in_cents,
        1
    FROM public.flyer_locations fl
    CROSS JOIN generate_series(flyer_valid_from, flyer_valid_to, '1 day'::interval) AS d(day)
    WHERE fl.flyer_id = NEW.flyer_id
    ON CONFLICT (master_item_id, summary_date, store_location_id)
    DO UPDATE SET
        min_price_in_cents = LEAST(item_price_history.min_price_in_cents, EXCLUDED.min_price_in_cents),
        max_price_in_cents = GREATEST(item_price_history.max_price_in_cents, EXCLUDED.max_price_in_cents),
        avg_price_in_cents = ROUND(((item_price_history.avg_price_in_cents * item_price_history.data_points_count) + EXCLUDED.avg_price_in_cents) / (item_price_history.data_points_count + 1.0)),
        data_points_count = item_price_history.data_points_count + 1;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger on the flyer_items table for insert.
DROP TRIGGER IF EXISTS trigger_update_price_history ON public.flyer_items;
CREATE TRIGGER trigger_update_price_history
  AFTER INSERT ON public.flyer_items
  FOR EACH ROW EXECUTE FUNCTION public.update_price_history_on_flyer_item_insert();

-- 4. Create a trigger function to recalculate price history when a flyer item is deleted.
DROP FUNCTION IF EXISTS public.recalculate_price_history_on_flyer_item_delete();

CREATE OR REPLACE FUNCTION public.recalculate_price_history_on_flyer_item_delete()
RETURNS TRIGGER AS $$
DECLARE
    affected_dates RECORD;
BEGIN
    -- Only run if the deleted item was linked to a master item and had a price.
    IF OLD.master_item_id IS NULL OR OLD.price_in_cents IS NULL THEN
        RETURN OLD;
    END IF;

    -- This single, set-based query is much more performant than looping.
    -- It recalculates aggregates for all affected dates and locations at once.
    WITH affected_days_and_locations AS (
        -- 1. Get all date/location pairs affected by the deleted item's flyer.
        SELECT DISTINCT
            generate_series(f.valid_from, f.valid_to, '1 day'::interval)::date AS summary_date,
            fl.store_location_id
        FROM public.flyers f
        JOIN public.flyer_locations fl ON f.flyer_id = fl.flyer_id
        WHERE f.flyer_id = OLD.flyer_id
    ),
    new_aggregates AS (
        -- 2. For each affected date/location, recalculate the aggregates from all other relevant flyer items.
        SELECT
            adl.summary_date,
            adl.store_location_id,
            MIN(fi.price_in_cents) AS min_price,
            MAX(fi.price_in_cents) AS max_price,
            ROUND(AVG(fi.price_in_cents))::int AS avg_price,
            COUNT(fi.flyer_item_id)::int AS data_points
        FROM affected_days_and_locations adl
        LEFT JOIN public.flyer_items fi ON fi.master_item_id = OLD.master_item_id AND fi.price_in_cents IS NOT NULL
        LEFT JOIN public.flyers f ON fi.flyer_id = f.flyer_id AND adl.summary_date BETWEEN f.valid_from AND f.valid_to
        LEFT JOIN public.flyer_locations fl ON fi.flyer_id = fl.flyer_id AND adl.store_location_id = fl.store_location_id
        WHERE fl.flyer_id IS NOT NULL -- Ensure the join was successful
        GROUP BY adl.summary_date, adl.store_location_id
    )
    -- 3. Update the history table with the new aggregates.
    UPDATE public.item_price_history iph
    SET
        min_price_in_cents = na.min_price,
        max_price_in_cents = na.max_price,
        avg_price_in_cents = na.avg_price,
        data_points_count = na.data_points
    FROM new_aggregates na
    WHERE iph.master_item_id = OLD.master_item_id
      AND iph.summary_date = na.summary_date
      AND iph.store_location_id = na.store_location_id;

    -- 4. Delete any history records that no longer have any data points.
    DELETE FROM public.item_price_history iph
    WHERE iph.master_item_id = OLD.master_item_id
      AND NOT EXISTS (
          SELECT 1 FROM new_aggregates na
          WHERE na.summary_date = iph.summary_date AND na.store_location_id = iph.store_location_id
      );

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger on the flyer_items table for DELETE operations.
DROP TRIGGER IF EXISTS trigger_recalculate_price_history_on_delete ON public.flyer_items;
CREATE TRIGGER trigger_recalculate_price_history_on_delete
  AFTER DELETE ON public.flyer_items
  FOR EACH ROW EXECUTE FUNCTION public.recalculate_price_history_on_flyer_item_delete();

-- 5. Trigger function to update the average rating on the recipes table.
DROP FUNCTION IF EXISTS public.update_recipe_rating_aggregates();

CREATE OR REPLACE FUNCTION public.update_recipe_rating_aggregates()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE public.recipes
    SET
        avg_rating = (
            SELECT AVG(rating)
            FROM public.recipe_ratings
            WHERE recipe_id = COALESCE(NEW.recipe_id, OLD.recipe_id) -- This is correct, no change needed
        ),
        rating_count = (
            SELECT COUNT(*)
            FROM public.recipe_ratings
            WHERE recipe_id = COALESCE(NEW.recipe_id, OLD.recipe_id) -- This is correct, no change needed
        )
    WHERE recipe_id = COALESCE(NEW.recipe_id, OLD.recipe_id);

    RETURN NULL; -- The result is ignored since this is an AFTER trigger.
END;
$$ LANGUAGE plpgsql;

-- Trigger to call the function after any change to recipe_ratings.
DROP TRIGGER IF EXISTS on_recipe_rating_change ON public.recipe_ratings;
CREATE TRIGGER on_recipe_rating_change
  AFTER INSERT OR UPDATE OR DELETE ON public.recipe_ratings
  FOR EACH ROW EXECUTE FUNCTION public.update_recipe_rating_aggregates();

-- 6. Trigger function to log the creation of a new recipe.
DROP FUNCTION IF EXISTS public.log_new_recipe();

CREATE OR REPLACE FUNCTION public.log_new_recipe()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.user_id,
        'recipe_created',
        (SELECT full_name FROM public.profiles WHERE user_id = NEW.user_id) || ' created a new recipe: ' || NEW.name,
        'chef-hat',
        jsonb_build_object('recipe_id', NEW.recipe_id, 'recipe_name', NEW.name)
    );

    -- Award 'First Recipe' achievement if it's their first one.
    PERFORM public.award_achievement(NEW.user_id, 'First Recipe');

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to call the function after a new recipe is inserted.
DROP TRIGGER IF EXISTS on_new_recipe_created ON public.recipes;
CREATE TRIGGER on_new_recipe_created
  AFTER INSERT ON public.recipes
  FOR EACH ROW
  WHEN (NEW.user_id IS NOT NULL) -- Only log activity for user-created recipes.
  EXECUTE FUNCTION public.log_new_recipe();

-- 7a. Trigger function to update the item_count on the flyers table.
DROP FUNCTION IF EXISTS public.update_flyer_item_count();

CREATE OR REPLACE FUNCTION public.update_flyer_item_count()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        UPDATE public.flyers SET item_count = item_count + 1 WHERE flyer_id = NEW.flyer_id;
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE public.flyers SET item_count = item_count - 1 WHERE flyer_id = OLD.flyer_id;
    END IF;
    RETURN NULL; -- The result is ignored since this is an AFTER trigger.
END;
$$ LANGUAGE plpgsql;

-- Trigger to call the function after any change to flyer_items.
-- This ensures the item_count on the parent flyer is always accurate.
DROP TRIGGER IF EXISTS on_flyer_item_change ON public.flyer_items;
CREATE TRIGGER on_flyer_item_change
  AFTER INSERT OR DELETE ON public.flyer_items
  FOR EACH ROW EXECUTE FUNCTION public.update_flyer_item_count();

-- 7. Trigger function to log the creation of a new flyer.
DROP FUNCTION IF EXISTS public.log_new_flyer();

CREATE OR REPLACE FUNCTION public.log_new_flyer()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.activity_log (action, display_text, icon, details)
    VALUES (
        'flyer_uploaded',
        'A new flyer for ' || (SELECT name FROM public.stores WHERE store_id = NEW.store_id) || ' has been uploaded.',
        'file-text',
        jsonb_build_object(
            'flyer_id', NEW.flyer_id,
            'store_name', (SELECT name FROM public.stores WHERE store_id = NEW.store_id),
            'valid_from', to_char(NEW.valid_from, 'YYYY-MM-DD'),
            'valid_to', to_char(NEW.valid_to, 'YYYY-MM-DD')
        )
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to call the function after a new flyer is inserted.
DROP TRIGGER IF EXISTS on_new_flyer_created ON public.flyers;
CREATE TRIGGER on_new_flyer_created
  AFTER INSERT ON public.flyers
  FOR EACH ROW EXECUTE FUNCTION public.log_new_flyer();

-- 8. Trigger function to log when a user favorites a recipe.
DROP FUNCTION IF EXISTS public.log_new_favorite_recipe();

CREATE OR REPLACE FUNCTION public.log_new_favorite_recipe()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.user_id,
        'recipe_favorited',
        (SELECT full_name FROM public.profiles WHERE user_id = NEW.user_id) || ' favorited the recipe: ' || (SELECT name FROM public.recipes WHERE recipe_id = NEW.recipe_id),
        'heart',
        jsonb_build_object(
            'recipe_id', NEW.recipe_id
        )
    );

    -- Award 'First Favorite' achievement.
    PERFORM public.award_achievement(NEW.user_id, 'First Favorite');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 8. Trigger to log when a user favorites a recipe.
-- 9. Trigger function to log when a user shares a shopping list.
DROP FUNCTION IF EXISTS public.log_new_list_share();

CREATE OR REPLACE FUNCTION public.log_new_list_share()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.shared_by_user_id,
        'list_shared',
        (SELECT full_name FROM public.profiles WHERE user_id = NEW.shared_by_user_id) || ' shared a shopping list.',
        'share-2',
        jsonb_build_object(
            'list_name', (SELECT name FROM public.shopping_lists WHERE shopping_list_id = NEW.shopping_list_id),
            'shared_with_user_id', NEW.shared_with_user_id
        )
    );

    -- Award 'List Sharer' achievement.
    PERFORM public.award_achievement(NEW.shared_by_user_id, 'List Sharer');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 9a. Trigger function to log when a user shares a recipe collection.
DROP FUNCTION IF EXISTS public.log_new_recipe_collection_share();

CREATE OR REPLACE FUNCTION public.log_new_recipe_collection_share()
RETURNS TRIGGER AS $$
BEGIN
    -- Log the activity
    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.shared_by_user_id, 'recipe_collection_shared',
        (SELECT full_name FROM public.profiles WHERE user_id = NEW.shared_by_user_id) || ' shared a recipe collection.',
        'book',
        jsonb_build_object('collection_id', NEW.recipe_collection_id, 'shared_with_user_id', NEW.shared_with_user_id)
    );

    -- Award 'Recipe Sharer' achievement.
    PERFORM public.award_achievement(NEW.shared_by_user_id, 'Recipe Sharer');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS on_new_favorite_recipe ON public.favorite_recipes;
CREATE TRIGGER on_new_favorite_recipe
  AFTER INSERT ON public.favorite_recipes
  FOR EACH ROW EXECUTE FUNCTION public.log_new_favorite_recipe();

DROP TRIGGER IF EXISTS on_new_list_share ON public.shared_shopping_lists;
CREATE TRIGGER on_new_list_share
  AFTER INSERT ON public.shared_shopping_lists
  FOR EACH ROW EXECUTE FUNCTION public.log_new_list_share();

DROP TRIGGER IF EXISTS on_new_recipe_collection_share ON public.shared_recipe_collections;
CREATE TRIGGER on_new_recipe_collection_share
  AFTER INSERT ON public.shared_recipe_collections
  FOR EACH ROW EXECUTE FUNCTION public.log_new_recipe_collection_share();

-- =================================================================
-- Function: get_best_sale_prices_for_all_users()
-- Description: Retrieves the best sale price for every item on every user's watchlist.
-- This is a highly efficient function designed for the daily deal check background job.
-- It replaces the need to call get_best_sale_prices_for_user for each user individually.
-- Returns: TABLE(...) - A set of records including user details and deal information.
-- =================================================================
CREATE OR REPLACE FUNCTION public.get_best_sale_prices_for_all_users()
RETURNS TABLE(
    user_id uuid,
    email text,
    full_name text,
    master_item_id integer,
    item_name text,
    best_price_in_cents integer,
    store_name text,
    flyer_id integer,
    valid_to date
) AS $$
BEGIN
    RETURN QUERY
    WITH
    -- Step 1: Find all flyer items that are currently on sale and have a valid price.
    current_sales AS (
        SELECT
            fi.master_item_id,
            fi.price_in_cents,
            f.store_name,
            f.flyer_id,
            f.valid_to
        FROM public.flyer_items fi
        JOIN public.flyers f ON fi.flyer_id = f.flyer_id
        WHERE
            fi.master_item_id IS NOT NULL
            AND fi.price_in_cents IS NOT NULL
            AND f.valid_to >= CURRENT_DATE
    ),
    -- Step 2: For each master item, find its absolute best (lowest) price across all current sales.
    -- We use a window function to rank the sales for each item by price.
    best_prices AS (
        SELECT
            cs.master_item_id,
            cs.price_in_cents AS best_price_in_cents,
            cs.store_name,
            cs.flyer_id,
            cs.valid_to,
            -- Rank items by price, ascending. The best price will have a rank of 1.
            ROW_NUMBER() OVER(PARTITION BY cs.master_item_id ORDER BY cs.price_in_cents ASC) as price_rank
        FROM current_sales cs
    )
    -- Step 3: Join the best-priced items with the user watchlist and user details.
    SELECT
        u.user_id,
        u.email,
        p.full_name,
        bp.master_item_id,
        mgi.name AS item_name,
        bp.best_price_in_cents,
        bp.store_name,
        bp.flyer_id,
        bp.valid_to
    FROM public.user_watched_items uwi
    -- Join with users and profiles to get user details for notifications.
    JOIN public.users u ON uwi.user_id = u.user_id
    JOIN public.profiles p ON u.user_id = p.user_id
    -- Join with the best-priced items.
    JOIN best_prices bp ON uwi.master_item_id = bp.master_item_id
    -- Join with master items to get the item name.
    JOIN public.master_grocery_items mgi ON bp.master_item_id = mgi.master_grocery_item_id
    WHERE
        -- Only include the items that are at their absolute best price (rank = 1).
        bp.price_rank = 1;
END;
$$ LANGUAGE plpgsql;
