-- ============================================================
-- CS2 Skin Tracker — find_listings()
-- Run against the skintracker database as the app role (owner)
-- to (re)create the function. Safe to re-run (CREATE OR REPLACE).
--
-- Purpose: look up active listings for a specific skin by weapon
-- name + finish name, with an optional wear filter.
--
-- Examples:
--   SELECT * FROM skintracker.find_listings('AK-47', 'Blue Laminate');
--   SELECT * FROM skintracker.find_listings('m4a1-s', 'Player Two', 'ft');
--   SELECT cs_float_listing_id, price, wear_name, float_value, is_stat_trak
--   FROM skintracker.find_listings('M4A4', 'Howl', 'mw');
-- ============================================================

SET search_path = skintracker;

CREATE OR REPLACE FUNCTION skintracker.find_listings(
    p_weapon text,            -- e.g. 'AK-47', 'M4A4', 'M4A1-S' (case-insensitive)
    p_skin   text,            -- e.g. 'Blue Laminate', 'Electric Blue' (case-insensitive)
    p_wear   text DEFAULT NULL -- optional: fn | mw | ft | ww | bs (case-insensitive)
)
RETURNS SETOF skintracker.listings
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_wear_name text;
BEGIN
    -- Map the optional wear abbreviation to the full wear name CSFloat reports.
    -- NULL / blank means "any wear". An unrecognised value is an error rather than
    -- a silent empty result.
    IF p_wear IS NOT NULL AND btrim(p_wear) <> '' THEN
        v_wear_name := CASE lower(btrim(p_wear))
            WHEN 'fn' THEN 'Factory New'
            WHEN 'mw' THEN 'Minimal Wear'
            WHEN 'ft' THEN 'Field-Tested'
            WHEN 'ww' THEN 'Well-Worn'
            WHEN 'bs' THEN 'Battle-Scarred'
            ELSE NULL
        END;

        IF v_wear_name IS NULL THEN
            RAISE EXCEPTION 'Unknown wear abbreviation: "%". Use one of: fn, mw, ft, ww, bs.', p_wear;
        END IF;
    END IF;

    RETURN QUERY
    SELECT l.*
    FROM skintracker.listings l
    JOIN skintracker.skins    s ON s.id = l.skin_id
    JOIN skintracker.weapons  w ON w.id = s.weapon_id
    WHERE l.status = 'Active'
      AND lower(w.name) = lower(btrim(p_weapon))
      AND lower(s.name) = lower(btrim(p_skin))
      AND (v_wear_name IS NULL OR l.wear_name = v_wear_name)
    ORDER BY l.price ASC, l.float_value ASC;
END;
$$;

-- If you use the optional read-only reporting role (see 02_readonly_role.sql),
-- let it call the function:
-- GRANT EXECUTE ON FUNCTION skintracker.find_listings(text, text, text) TO skintracker_readonly;