-- sql/Initial_triggers_and_functions.sql
-- This file contains all trigger functions and trigger definitions for the database.

-- ============================================================================
-- PART 0: OBSERVABILITY HELPERS (ADR-050)
-- ============================================================================
-- These functions provide structured logging capabilities for database functions.
-- Logs are emitted via RAISE statements and can be captured by Logstash for
-- forwarding to error tracking systems (see ADR-015).

-- Function to emit structured log messages from PL/pgSQL functions.
-- This enables observability for database operations that might otherwise fail silently.
DROP FUNCTION IF EXISTS public.fn_log(TEXT, TEXT, TEXT, JSONB);

CREATE OR REPLACE FUNCTION public.fn_log(
    p_level TEXT,           -- 'DEBUG', 'INFO', 'NOTICE', 'WARNING', 'ERROR'
    p_function_name TEXT,   -- The calling function name
    p_message TEXT,         -- Human-readable message
    p_context JSONB DEFAULT NULL  -- Additional context (user_id, params, etc.)
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    log_line TEXT;
BEGIN
    -- Build structured JSON log line for Logstash parsing
    log_line := jsonb_build_object(
        'timestamp', now(),
        'level', p_level,
        'source', 'postgresql',
        'function', p_function_name,
        'message', p_message,
        'context', COALESCE(p_context, '{}'::jsonb)
    )::text;

    -- Use appropriate RAISE level based on severity
    -- Note: We use RAISE LOG for errors to ensure they're always captured
    -- regardless of client_min_messages setting
    CASE UPPER(p_level)
        WHEN 'DEBUG' THEN RAISE DEBUG '%', log_line;
        WHEN 'INFO' THEN RAISE INFO '%', log_line;
        WHEN 'NOTICE' THEN RAISE NOTICE '%', log_line;
        WHEN 'WARNING' THEN RAISE WARNING '%', log_line;
        WHEN 'ERROR' THEN RAISE LOG '%', log_line;
        ELSE RAISE NOTICE '%', log_line;
    END CASE;
END;
$$;

COMMENT ON FUNCTION public.fn_log IS 'Emits structured JSON log messages for database function observability (ADR-050)';

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

-- This function queries all currently active flyers to find the lowest price
-- 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 $$
DECLARE
    v_watched_items_count INTEGER;
    v_result_count INTEGER;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object('user_id', p_user_id);

    -- Tier 2 logging: Check if user has any watched items
    SELECT COUNT(*) INTO v_watched_items_count
    FROM public.user_watched_items
    WHERE user_id = p_user_id;

    IF v_watched_items_count = 0 THEN
        PERFORM fn_log('NOTICE', 'get_best_sale_prices_for_user',
            'User has no watched items',
            v_context);
        RETURN; -- Return empty result set
    END IF;

    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;

    -- Tier 2 logging: Check if any sales were found
    GET DIAGNOSTICS v_result_count = ROW_COUNT;
    IF v_result_count = 0 THEN
        PERFORM fn_log('NOTICE', 'get_best_sale_prices_for_user',
            'No sales found for watched items',
            v_context || jsonb_build_object('watched_items_count', v_watched_items_count));
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'get_best_sale_prices_for_user',
            'Unexpected error getting best sale prices: ' || SQLERRM,
            v_context);
        RAISE;
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 $$
DECLARE
    v_menu_plan_exists BOOLEAN;
    v_planned_meals_count INTEGER;
    v_result_count INTEGER;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'menu_plan_id', p_menu_plan_id,
        'user_id', p_user_id
    );

    -- Tier 2 logging: Check if menu plan exists and belongs to user
    SELECT EXISTS(
        SELECT 1 FROM public.menu_plans
        WHERE menu_plan_id = p_menu_plan_id AND user_id = p_user_id
    ) INTO v_menu_plan_exists;

    IF NOT v_menu_plan_exists THEN
        PERFORM fn_log('NOTICE', 'generate_shopping_list_for_menu_plan',
            'Menu plan not found or does not belong to user',
            v_context);
        RETURN; -- Return empty result set
    END IF;

    -- Tier 2 logging: Check if menu plan has any recipes
    SELECT COUNT(*) INTO v_planned_meals_count
    FROM public.planned_meals
    WHERE menu_plan_id = p_menu_plan_id;

    IF v_planned_meals_count = 0 THEN
        PERFORM fn_log('NOTICE', 'generate_shopping_list_for_menu_plan',
            'Menu plan has no recipes',
            v_context);
        RETURN; -- Return empty result set
    END IF;

    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;

    -- Tier 2 logging: Check if any items need to be purchased
    GET DIAGNOSTICS v_result_count = ROW_COUNT;
    IF v_result_count = 0 THEN
        PERFORM fn_log('NOTICE', 'generate_shopping_list_for_menu_plan',
            'All ingredients already in pantry (no shopping needed)',
            v_context || jsonb_build_object('planned_meals_count', v_planned_meals_count));
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'generate_shopping_list_for_menu_plan',
            'Unexpected error generating shopping list: ' || SQLERRM,
            v_context);
        RAISE;
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 -- Indicates the function cannot modify the database and is safe for read-only queries.
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 -- Join to count how many ingredients are on sale
    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,
      ris.total_ingredients,
      ris.sale_ingredients
    FROM RecipeIngredientStats ris
    WHERE ris.total_ingredients > 0 -- Avoid division by zero and recipes with no ingredients
      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 not on sale
      bcp.store_name -- This will be NULL if not on sale
    FROM public.recipes r
    JOIN EligibleRecipes er ON r.recipe_id = er.recipe_id -- Join with the filtered eligible recipes
    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;
    v_items_added INTEGER := 0;
    v_context JSONB;
BEGIN
    -- Build context for logging
    v_context := jsonb_build_object(
        'user_id', p_user_id,
        'menu_plan_id', p_menu_plan_id,
        'shopping_list_id', p_shopping_list_id
    );

    -- 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 THEN
        PERFORM fn_log('WARNING', 'add_menu_plan_to_shopping_list',
            'Shopping list not found',
            v_context);
        RAISE EXCEPTION 'Permission denied: You do not own shopping list %', p_shopping_list_id;
    END IF;

    IF list_owner_id <> p_user_id THEN
        PERFORM fn_log('WARNING', 'add_menu_plan_to_shopping_list',
            'Permission denied: user does not own list',
            v_context || jsonb_build_object('list_owner_id', list_owner_id));
        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;

        v_items_added := v_items_added + 1;

        -- 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;

    -- Log completion (items_added = 0 is normal if pantry has everything)
    PERFORM fn_log('INFO', 'add_menu_plan_to_shopping_list',
        'Menu plan items added to shopping list',
        v_context || jsonb_build_object('items_added', v_items_added));
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
      -- Only consider items linked to our master list
      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;
    best_score REAL;
    -- 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;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object('flyer_item_name', p_flyer_item_name, 'similarity_threshold', similarity_threshold);

    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, score INTO suggested_id, best_score FROM candidates WHERE score >= similarity_threshold ORDER BY score DESC, master_item_id LIMIT 1;

    -- Tier 2 logging: Log when no match found (anomaly detection)
    IF suggested_id IS NULL THEN
        PERFORM fn_log('INFO', 'suggest_master_item_for_flyer_item',
            'No master item match found for flyer item',
            v_context || jsonb_build_object('best_score', best_score));
    END IF;

    RETURN suggested_id;
END;
$$;

-- 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 plpgsql
STABLE
SECURITY INVOKER
AS $$
DECLARE
    v_count INTEGER;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object('user_id', p_user_id, 'limit', p_limit);

    -- Execute the recommendation query
    RETURN QUERY
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;

    -- Tier 2 logging: Log when no recommendations generated (anomaly detection)
    GET DIAGNOSTICS v_count = ROW_COUNT;
    IF v_count = 0 THEN
        PERFORM fn_log('INFO', 'recommend_recipes_for_user',
            'No recipe recommendations generated for user',
            v_context);
    END IF;
END;
$$;

-- 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;
    v_context JSONB;
BEGIN
    -- Build context for logging
    v_context := jsonb_build_object('correction_id', p_correction_id);

    -- 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
        PERFORM fn_log('WARNING', 'approve_correction',
            'Correction not found or already processed',
            v_context);
        RAISE EXCEPTION 'Correction with ID % not found or already processed.', p_correction_id;
    END IF;

    -- Add correction details to context
    v_context := v_context || jsonb_build_object(
        'correction_type', correction_record.correction_type,
        'flyer_item_id', correction_record.flyer_item_id,
        'suggested_value', correction_record.suggested_value
    );

    -- 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;

    -- Log successful correction approval
    PERFORM fn_log('INFO', 'approve_correction',
        'Correction approved and applied',
        v_context);
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 plpgsql
SECURITY INVOKER
AS $$
DECLARE
    new_recipe_id BIGINT;
    v_context JSONB;
BEGIN
    -- Build context for logging
    v_context := jsonb_build_object(
        'user_id', p_user_id,
        'original_recipe_id', p_original_recipe_id
    );

    -- 1. Create a copy of the recipe, linking it to the new user and the original recipe.
    INSERT INTO public.recipes (
        user_id,
        original_recipe_id,
        name,
        description,
        instructions,
        prep_time_minutes,
        cook_time_minutes,
        servings,
        photo_url,
        calories_per_serving,
        protein_grams,
        fat_grams,
        carb_grams,
        status -- Forked recipes should be private by default
    )
    SELECT
        p_user_id,
        p_original_recipe_id,
        original.name || ' (Fork)', -- Append '(Fork)' to distinguish it
        original.description,
        original.instructions,
        original.prep_time_minutes,
        original.cook_time_minutes,
        original.servings,
        original.photo_url,
        original.calories_per_serving,
        original.protein_grams,
        original.fat_grams,
        original.carb_grams,
        'private'
    FROM public.recipes AS original
    WHERE original.recipe_id = p_original_recipe_id
    RETURNING recipe_id INTO new_recipe_id;

    -- If the original recipe didn't exist, new_recipe_id will be null.
    IF new_recipe_id IS NULL THEN
        PERFORM fn_log('ERROR', 'fork_recipe',
            'Original recipe not found',
            v_context);
        RAISE EXCEPTION 'Cannot fork recipe: Original recipe with ID % not found', p_original_recipe_id;
    END IF;

    -- 2. Copy all ingredients, tags, and appliances from the original recipe to the new one.
    INSERT INTO public.recipe_ingredients (recipe_id, master_item_id, quantity, unit) SELECT new_recipe_id, master_item_id, quantity, unit FROM public.recipe_ingredients WHERE recipe_id = p_original_recipe_id;
    INSERT INTO public.recipe_tags (recipe_id, tag_id) SELECT new_recipe_id, tag_id FROM public.recipe_tags WHERE recipe_id = p_original_recipe_id;
    INSERT INTO public.recipe_appliances (recipe_id, appliance_id) SELECT new_recipe_id, appliance_id FROM public.recipe_appliances WHERE recipe_id = p_original_recipe_id;

    -- Log successful fork
    PERFORM fn_log('INFO', 'fork_recipe',
        'Recipe forked successfully',
        v_context || jsonb_build_object('new_recipe_id', new_recipe_id));

    -- 3. Return the newly created recipe record.
    RETURN QUERY SELECT * FROM public.recipes WHERE recipe_id = new_recipe_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 plpgsql
STABLE
SECURITY INVOKER
AS $$
DECLARE
    v_pantry_item_count INTEGER;
    v_result_count INTEGER;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object('user_id', p_user_id);

    -- Tier 2 logging: Check if user has any pantry items
    SELECT COUNT(*) INTO v_pantry_item_count
    FROM public.pantry_items
    WHERE user_id = p_user_id AND quantity > 0;

    IF v_pantry_item_count = 0 THEN
        PERFORM fn_log('NOTICE', 'find_recipes_from_pantry',
            'User has empty pantry',
            v_context);
        RETURN; -- Return empty result set
    END IF;

    -- Execute the main query and return results
    RETURN QUERY
    WITH UserPantryItems AS (
        -- CTE 1: Get a distinct set of master item IDs from the user's pantry.
        SELECT pi.master_item_id, pi.quantity, pi.unit
        FROM public.pantry_items pi
        WHERE pi.user_id = p_user_id AND pi.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;

    -- Tier 2 logging: Check if any recipes were found
    GET DIAGNOSTICS v_result_count = ROW_COUNT;
    IF v_result_count = 0 THEN
        PERFORM fn_log('NOTICE', 'find_recipes_from_pantry',
            'No recipes found matching pantry items',
            v_context || jsonb_build_object('pantry_item_count', v_pantry_item_count));
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'find_recipes_from_pantry',
            'Unexpected error finding recipes from pantry: ' || SQLERRM,
            v_context);
        RAISE;
END;
$$;

-- 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 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,
    user_avatar_url TEXT
)
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
  -- Join with profiles to get user details for display.
  -- LEFT JOIN is used because some activities might be system-generated (user_id is NULL).
  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;
    v_items_count INTEGER;
    v_context JSONB;
BEGIN
    -- Build context for logging
    v_context := jsonb_build_object(
        'user_id', p_user_id,
        'shopping_list_id', p_shopping_list_id,
        'total_spent_cents', p_total_spent_cents
    );

    -- 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 THEN
        PERFORM fn_log('WARNING', 'complete_shopping_list',
            'Shopping list not found',
            v_context);
        RAISE EXCEPTION 'Permission denied: You do not own shopping list %', p_shopping_list_id;
    END IF;

    IF list_owner_id <> p_user_id THEN
        PERFORM fn_log('WARNING', 'complete_shopping_list',
            'Permission denied: user does not own list',
            v_context || jsonb_build_object('list_owner_id', list_owner_id));
        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;

    GET DIAGNOSTICS v_items_count = ROW_COUNT;

    -- 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;

    -- Log successful completion
    PERFORM fn_log('INFO', 'complete_shopping_list',
        'Shopping list completed successfully',
        v_context || jsonb_build_object('trip_id', new_trip_id, 'items_archived', v_items_count));

    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 get a user's spending breakdown by category for a given date range.
DROP FUNCTION IF EXISTS public.get_spending_by_category(UUID, DATE, DATE);

CREATE OR REPLACE FUNCTION public.get_spending_by_category(p_user_id UUID, p_start_date DATE, p_end_date DATE)
RETURNS TABLE (
    category_id BIGINT,
    category_name TEXT,
    total_spent_cents BIGINT
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  WITH all_purchases AS (
    -- CTE 1: Combine purchases from completed shopping trips.
    -- We only consider items that have a price paid.
    SELECT
      sti.master_item_id,
      sti.price_paid_cents
    FROM public.shopping_trip_items sti
    JOIN public.shopping_trips st ON sti.shopping_trip_id = st.shopping_trip_id
    WHERE st.user_id = p_user_id
      AND st.completed_at::date BETWEEN p_start_date AND p_end_date
      AND sti.price_paid_cents IS NOT NULL

    UNION ALL

    -- CTE 2: Combine purchases from processed receipts.
    SELECT
      ri.master_item_id,
      ri.price_paid_cents
    FROM public.receipt_items ri
    JOIN public.receipts r ON ri.receipt_id = r.receipt_id
    WHERE r.user_id = p_user_id
      AND r.transaction_date::date BETWEEN p_start_date AND p_end_date
      AND ri.master_item_id IS NOT NULL -- Only include items matched to a master item
  )
  -- Final Aggregation: Group all combined purchases by category and sum the spending.
  SELECT
    c.category_id,
    c.name AS category_name,
    SUM(ap.price_paid_cents)::BIGINT AS total_spent_cents
  FROM all_purchases ap
  -- Join with master_grocery_items to get the category_id for each purchase.
  JOIN public.master_grocery_items mgi ON ap.master_item_id = mgi.master_grocery_item_id
  -- Join with categories to get the category name for display.
  JOIN public.categories c ON mgi.category_id = c.category_id
  GROUP BY
    c.category_id, c.name
  HAVING
    SUM(ap.price_paid_cents) > 0
  ORDER BY
    total_spent_cents DESC;
$$;

-- 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;
    v_context JSONB;
    v_rows_inserted INTEGER;
BEGIN
    -- Build context for logging
    v_context := jsonb_build_object('user_id', p_user_id, 'achievement_name', p_achievement_name);

    -- 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, log error and raise exception.
    IF v_achievement_id IS NULL THEN
        PERFORM fn_log('ERROR', 'award_achievement',
            'Achievement not found: ' || p_achievement_name, v_context);
        RAISE EXCEPTION 'Achievement "%" does not exist in the achievements table', p_achievement_name;
    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.
    INSERT INTO public.user_achievements (user_id, achievement_id)
    VALUES (p_user_id, v_achievement_id)
    ON CONFLICT (user_id, achievement_id) DO NOTHING;

    -- Check if the insert actually added a row
    GET DIAGNOSTICS v_rows_inserted = ROW_COUNT;

    IF v_rows_inserted = 0 THEN
        -- Log duplicate award attempt
        PERFORM fn_log('NOTICE', 'award_achievement',
            'Achievement already awarded (duplicate): ' || p_achievement_name, v_context);
    ELSE
        -- Award was successful, update points
        UPDATE public.profiles SET points = points + v_points_value WHERE user_id = p_user_id;
        PERFORM fn_log('INFO', 'award_achievement',
            'Achievement awarded: ' || p_achievement_name,
            v_context || jsonb_build_object('points_awarded', v_points_value));
    END IF;
END;
$$;

-- =================================================================
-- 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.
-- =================================================================
DROP FUNCTION IF EXISTS public.get_best_sale_prices_for_all_users();

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 bigint,
    item_name text,
    best_price_in_cents integer,
    store_name text,
    flyer_id bigint,
    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,
            s.name as store_name,
            f.flyer_id,
            f.valid_to
        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 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;

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

-- 1. Trigger to automatically create a profile when a new user signs up.
-- This function is called by a trigger on the `public.users` table.
DROP FUNCTION IF EXISTS public.handle_new_user();

-- It creates a corresponding profile and a default shopping list for the new user.
-- It now accepts full_name and avatar_url from the user's metadata.
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  new_profile_id UUID;
  user_meta_data JSONB;
  v_context JSONB;
BEGIN
  -- Build context for logging
  v_context := jsonb_build_object('user_id', new.user_id, 'email', new.email);

  -- 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;

  -- Create the user profile
  BEGIN
    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;
  EXCEPTION WHEN OTHERS THEN
    PERFORM fn_log('ERROR', 'handle_new_user',
        'Failed to create profile: ' || SQLERRM,
        v_context || jsonb_build_object('sqlstate', SQLSTATE));
    RAISE;
  END;

  -- 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)
  );

  -- Award the 'Welcome Aboard' achievement for new user registration
  PERFORM public.award_achievement(new.user_id, 'Welcome Aboard');

  -- Log successful user creation
  PERFORM fn_log('INFO', 'handle_new_user',
      'New user created successfully',
      v_context || jsonb_build_object('full_name', user_meta_data->>'full_name'));

  RETURN new;
END;
$$ LANGUAGE plpgsql;

-- 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.
-- CASCADE drops dependent triggers; they are recreated by the DO block below
DROP FUNCTION IF EXISTS public.handle_updated_at() CASCADE;

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;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'flyer_item_id', NEW.flyer_item_id,
        'flyer_id', NEW.flyer_id,
        'master_item_id', NEW.master_item_id,
        'price_in_cents', NEW.price_in_cents
    );

    -- 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;

    -- Tier 3 logging: Log when flyer has missing validity dates (degrades gracefully)
    IF flyer_valid_from IS NULL OR flyer_valid_to IS NULL THEN
        PERFORM fn_log('WARNING', 'update_price_history_on_flyer_item_insert',
            'Flyer missing validity dates - skipping price history update',
            v_context);
        RETURN NEW;
    END IF;

    -- 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;
EXCEPTION
    WHEN OTHERS THEN
        -- Tier 3 logging: Log unexpected errors in trigger
        PERFORM fn_log('ERROR', 'update_price_history_on_flyer_item_insert',
            'Unexpected error in price history update: ' || SQLERRM,
            v_context);
        -- Re-raise the exception to ensure trigger failure is visible
        RAISE;
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.
    -- We need to recreate the CTE since CTEs are scoped to a single statement.
    WITH affected_days_and_locations AS (
        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 (
        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
        GROUP BY adl.summary_date, adl.store_location_id
    )
    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 $$
DECLARE
    v_recipe_id BIGINT;
    v_rows_updated INTEGER;
    v_context JSONB;
BEGIN
    v_recipe_id := COALESCE(NEW.recipe_id, OLD.recipe_id);
    v_context := jsonb_build_object('recipe_id', v_recipe_id);

    UPDATE public.recipes
    SET
        avg_rating = (
            SELECT AVG(rating)
            FROM public.recipe_ratings
            WHERE recipe_id = v_recipe_id
        ),
        rating_count = (
            SELECT COUNT(*)
            FROM public.recipe_ratings
            WHERE recipe_id = v_recipe_id
        )
    WHERE recipe_id = v_recipe_id;

    -- Tier 3 logging: Log when recipe update fails
    GET DIAGNOSTICS v_rows_updated = ROW_COUNT;
    IF v_rows_updated = 0 THEN
        PERFORM fn_log('ERROR', 'update_recipe_rating_aggregates',
            'Recipe not found for rating aggregate update',
            v_context);
    END IF;

    RETURN NULL; -- The result is ignored since this is an AFTER trigger.
EXCEPTION
    WHEN OTHERS THEN
        -- Tier 3 logging: Log unexpected errors in trigger
        PERFORM fn_log('ERROR', 'update_recipe_rating_aggregates',
            'Unexpected error in rating aggregate update: ' || SQLERRM,
            v_context);
        -- Re-raise the exception to ensure trigger failure is visible
        RAISE;
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 $$
DECLARE
    v_full_name TEXT;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'user_id', NEW.user_id,
        'recipe_id', NEW.recipe_id,
        'recipe_name', NEW.name
    );

    -- Get user's full name (Tier 3 logging: Log if profile lookup fails)
    SELECT full_name INTO v_full_name FROM public.profiles WHERE user_id = NEW.user_id;
    IF v_full_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_recipe',
            'Profile not found for user creating recipe',
            v_context);
        v_full_name := 'Unknown User';
    END IF;

    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.user_id,
        'recipe_created',
        v_full_name || ' 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;
EXCEPTION
    WHEN OTHERS THEN
        -- Tier 3 logging: Log unexpected errors in trigger
        PERFORM fn_log('ERROR', 'log_new_recipe',
            'Unexpected error in recipe activity logging: ' || SQLERRM,
            v_context);
        -- Re-raise the exception to ensure trigger failure is visible
        RAISE;
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 $$
DECLARE
    v_rows_updated INTEGER;
    v_context JSONB;
    v_flyer_id BIGINT;
BEGIN
    -- Determine which flyer_id to use based on operation
    IF (TG_OP = 'INSERT') THEN
        v_flyer_id := NEW.flyer_id;
        v_context := jsonb_build_object('flyer_id', NEW.flyer_id, 'operation', 'INSERT');

        UPDATE public.flyers SET item_count = item_count + 1 WHERE flyer_id = NEW.flyer_id;
    ELSIF (TG_OP = 'DELETE') THEN
        v_flyer_id := OLD.flyer_id;
        v_context := jsonb_build_object('flyer_id', OLD.flyer_id, 'operation', 'DELETE');

        UPDATE public.flyers SET item_count = item_count - 1 WHERE flyer_id = OLD.flyer_id;
    END IF;

    -- Tier 3 logging: Log if flyer not found (expected during CASCADE delete, so INFO level)
    GET DIAGNOSTICS v_rows_updated = ROW_COUNT;
    IF v_rows_updated = 0 THEN
        PERFORM fn_log('INFO', 'update_flyer_item_count',
            'Flyer not found for item count update (likely CASCADE delete)',
            v_context);
    END IF;

    RETURN NULL; -- The result is ignored since this is an AFTER trigger.
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'update_flyer_item_count',
            'Unexpected error updating flyer item count: ' || SQLERRM,
            v_context);
        RAISE;
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 $$
DECLARE
    v_store_name TEXT;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'flyer_id', NEW.flyer_id,
        'store_id', NEW.store_id,
        'uploaded_by', NEW.uploaded_by,
        'valid_from', NEW.valid_from,
        'valid_to', NEW.valid_to
    );

    -- If the flyer was uploaded by a registered user, award the 'First-Upload' achievement.
    -- The award_achievement function handles checking if the user already has it.
    IF NEW.uploaded_by IS NOT NULL THEN
        PERFORM public.award_achievement(NEW.uploaded_by, 'First-Upload');
    END IF;

    -- Get store name (Tier 3 logging: Log if store lookup fails)
    SELECT name INTO v_store_name FROM public.stores WHERE store_id = NEW.store_id;
    IF v_store_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_flyer',
            'Store not found for flyer',
            v_context);
        v_store_name := 'Unknown Store';
    END IF;

    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.uploaded_by, -- Log the user who uploaded it
        'flyer_uploaded',
        'A new flyer for ' || v_store_name || ' has been uploaded.',
        'file-text',
        jsonb_build_object(
            'flyer_id', NEW.flyer_id,
            'store_name', v_store_name,
            'valid_from', to_char(NEW.valid_from, 'YYYY-MM-DD'),
            'valid_to', to_char(NEW.valid_to, 'YYYY-MM-DD')
        )
    );
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        -- Tier 3 logging: Log unexpected errors in trigger
        PERFORM fn_log('ERROR', 'log_new_flyer',
            'Unexpected error in flyer activity logging: ' || SQLERRM,
            v_context);
        -- Re-raise the exception to ensure trigger failure is visible
        RAISE;
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 $$
DECLARE
    v_user_name TEXT;
    v_recipe_name TEXT;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'user_id', NEW.user_id,
        'recipe_id', NEW.recipe_id
    );

    -- Get user name (Tier 3 logging: Log if profile lookup fails)
    SELECT full_name INTO v_user_name FROM public.profiles WHERE user_id = NEW.user_id;
    IF v_user_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_favorite_recipe',
            'Profile not found for user',
            v_context);
        v_user_name := 'Unknown User';
    END IF;

    -- Get recipe name (Tier 3 logging: Log if recipe lookup fails)
    SELECT name INTO v_recipe_name FROM public.recipes WHERE recipe_id = NEW.recipe_id;
    IF v_recipe_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_favorite_recipe',
            'Recipe not found',
            v_context);
        v_recipe_name := 'Unknown Recipe';
    END IF;

    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.user_id,
        'recipe_favorited',
        v_user_name || ' favorited the recipe: ' || v_recipe_name,
        'heart',
        jsonb_build_object(
            'recipe_id', NEW.recipe_id
        )
    );

    -- Award 'First Favorite' achievement.
    PERFORM public.award_achievement(NEW.user_id, 'First Favorite');
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'log_new_favorite_recipe',
            'Unexpected error in favorite recipe activity logging: ' || SQLERRM,
            v_context);
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- Trigger to call the function after a recipe is favorited.
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();

-- 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 $$
DECLARE
    v_user_name TEXT;
    v_list_name TEXT;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'shared_by_user_id', NEW.shared_by_user_id,
        'shopping_list_id', NEW.shopping_list_id,
        'shared_with_user_id', NEW.shared_with_user_id
    );

    -- Get user name (Tier 3 logging: Log if profile lookup fails)
    SELECT full_name INTO v_user_name FROM public.profiles WHERE user_id = NEW.shared_by_user_id;
    IF v_user_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_list_share',
            'Profile not found for sharing user',
            v_context);
        v_user_name := 'Unknown User';
    END IF;

    -- Get list name (Tier 3 logging: Log if list lookup fails)
    SELECT name INTO v_list_name FROM public.shopping_lists WHERE shopping_list_id = NEW.shopping_list_id;
    IF v_list_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_list_share',
            'Shopping list not found',
            v_context);
        v_list_name := 'Unknown List';
    END IF;

    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.shared_by_user_id,
        'list_shared',
        v_user_name || ' shared a shopping list.',
        'share-2',
        jsonb_build_object(
            'shopping_list_id', NEW.shopping_list_id,
            'list_name', v_list_name,
            '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;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'log_new_list_share',
            'Unexpected error in list share activity logging: ' || SQLERRM,
            v_context);
        RAISE;
END;
$$ LANGUAGE plpgsql;
        
-- Trigger to call the function after a shopping list is shared.
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();

-- 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 $$
DECLARE
    v_user_name TEXT;
    v_context JSONB;
BEGIN
    v_context := jsonb_build_object(
        'shared_by_user_id', NEW.shared_by_user_id,
        'recipe_collection_id', NEW.recipe_collection_id,
        'shared_with_user_id', NEW.shared_with_user_id
    );

    -- Get user name (Tier 3 logging: Log if profile lookup fails)
    SELECT full_name INTO v_user_name FROM public.profiles WHERE user_id = NEW.shared_by_user_id;
    IF v_user_name IS NULL THEN
        PERFORM fn_log('ERROR', 'log_new_recipe_collection_share',
            'Profile not found for sharing user',
            v_context);
        v_user_name := 'Unknown User';
    END IF;

    -- Log the activity
    INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
    VALUES (
        NEW.shared_by_user_id, 'recipe_collection_shared',
        v_user_name || ' 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;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'log_new_recipe_collection_share',
            'Unexpected error in recipe collection share activity logging: ' || SQLERRM,
            v_context);
        RAISE;
END;
$$ LANGUAGE plpgsql;

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();

-- 10. Trigger function to geocode a store location's address.
-- This function is triggered when an address is inserted or updated, and is
-- designed to be extensible for external geocoding services to populate the
-- latitude, longitude, and location fields.
DROP FUNCTION IF EXISTS public.geocode_address();

CREATE OR REPLACE FUNCTION public.geocode_address()
RETURNS TRIGGER AS $$
DECLARE
    full_address TEXT;
BEGIN
    -- Only proceed if an address component has actually changed.
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND (
        NEW.address_line_1 IS DISTINCT FROM OLD.address_line_1 OR
        NEW.address_line_2 IS DISTINCT FROM OLD.address_line_2 OR
        NEW.city IS DISTINCT FROM OLD.city OR
        NEW.province_state IS DISTINCT FROM OLD.province_state OR
        NEW.postal_code IS DISTINCT FROM OLD.postal_code OR
        NEW.country IS DISTINCT FROM OLD.country
    )) THEN
        -- Concatenate address parts into a single string for the geocoder.
        full_address := CONCAT_WS(', ', NEW.address_line_1, NEW.address_line_2, NEW.city, NEW.province_state, NEW.postal_code, NEW.country);

        -- Placeholder for Geocoding API Call
        -- In a real application, you would call a service here and update NEW.latitude, NEW.longitude, and NEW.location.
        -- e.g., NEW.latitude := result.lat; NEW.longitude := result.lon;
        -- NEW.location := ST_SetSRID(ST_MakePoint(NEW.longitude, NEW.latitude), 4326);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- This trigger calls the geocoding function when an address changes.
DROP TRIGGER IF EXISTS on_address_change_geocode ON public.addresses;
CREATE TRIGGER on_address_change_geocode
  BEFORE INSERT OR UPDATE ON public.addresses
  FOR EACH ROW EXECUTE FUNCTION public.geocode_address();

-- 11. Trigger function to increment the fork_count on the original recipe.
DROP FUNCTION IF EXISTS public.increment_recipe_fork_count();

CREATE OR REPLACE FUNCTION public.increment_recipe_fork_count()
RETURNS TRIGGER AS $$
DECLARE
    v_rows_updated INTEGER;
    v_context JSONB;
BEGIN
    -- Only run if the recipe is a fork (original_recipe_id is not null).
    IF NEW.original_recipe_id IS NOT NULL THEN
        v_context := jsonb_build_object(
            'recipe_id', NEW.recipe_id,
            'original_recipe_id', NEW.original_recipe_id,
            'user_id', NEW.user_id
        );

        -- Tier 3 logging: Log if original recipe not found
        UPDATE public.recipes SET fork_count = fork_count + 1 WHERE recipe_id = NEW.original_recipe_id;
        GET DIAGNOSTICS v_rows_updated = ROW_COUNT;

        IF v_rows_updated = 0 THEN
            PERFORM fn_log('ERROR', 'increment_recipe_fork_count',
                'Original recipe not found for fork count increment',
                v_context);
        END IF;

        -- Award 'First Fork' achievement.
        PERFORM public.award_achievement(NEW.user_id, 'First Fork');
    END IF;
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        PERFORM fn_log('ERROR', 'increment_recipe_fork_count',
            'Unexpected error incrementing fork count: ' || SQLERRM,
            v_context);
        RAISE;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS on_recipe_fork ON public.recipes;
CREATE TRIGGER on_recipe_fork
  AFTER INSERT ON public.recipes
  FOR EACH ROW EXECUTE FUNCTION public.increment_recipe_fork_count();