-- ============================================================
-- CS2 Skin Tracker — catalog audit (read-only)
-- Run against the skintracker database as any role with SELECT
-- (e.g. skintracker_readonly). Pure SELECTs, safe to re-run.
--
-- Purpose: sanity-check the weapon/glove skin catalog for the
-- gaps that break tradeup math and pricing joins.
-- ============================================================

SET search_path = skintracker;

-- 1. Totals — quick scale check.
SELECT 'skins' AS table, count(*) FROM skins
UNION ALL SELECT 'weapons',          count(*) FROM weapons
UNION ALL SELECT 'collections',      count(*) FROM collections
UNION ALL SELECT 'skin_collections', count(*) FROM skin_collections
UNION ALL SELECT 'skin_conditions',  count(*) FROM skin_conditions;

-- 2. Orphan skins: belong to NO collection/container.
--    Expected: only "Howl" (Contraband, removed from its collection).
SELECT s.id, s.slug, s.name, s.rarity
FROM skins s
LEFT JOIN skin_collections sc ON sc.skins_id = s.id
WHERE sc.skins_id IS NULL
ORDER BY s.name;

-- 3. Missing float bounds.
--    Expected: only Vanilla (default) knives, which have no wear range.
SELECT s.id, s.slug, s.name, s.float_min, s.float_max
FROM skins s
WHERE s.float_min IS NULL OR s.float_max IS NULL
ORDER BY s.name;

-- 4. Rarity hygiene: any nulls/blanks, and the distribution.
SELECT coalesce(nullif(rarity, ''), '<empty/null>') AS rarity, count(*)
FROM skins
GROUP BY 1
ORDER BY 2 DESC;

-- 5. Inverted or out-of-range float bounds (should return 0 rows).
SELECT id, slug, name, float_min, float_max
FROM skins
WHERE float_min > float_max OR float_min < 0 OR float_max > 1;

-- 6. Duplicate slugs (unique index should keep this empty).
SELECT slug, count(*)
FROM skins
GROUP BY slug
HAVING count(*) > 1;

-- 7. Collection type split (expect only Collection / Container).
SELECT coalesce(type, '<null>') AS type, count(*)
FROM collections
GROUP BY 1
ORDER BY 2 DESC;

-- 8. Skins with no weapon link (should return 0 rows).
SELECT s.id, s.slug
FROM skins s
LEFT JOIN weapons w ON w.id = s.weapon_id
WHERE w.id IS NULL;

-- 9. Skin count per weapon type — confirms only weapon/glove items.
--    Note: Zeus x27 (type "Equipment") skins ARE valid catalog/tradeup items.
SELECT w.type, count(s.id) AS skins
FROM weapons w
LEFT JOIN skins s ON s.weapon_id = w.id
GROUP BY w.type
ORDER BY skins DESC;

-- 10. StatTrak / Souvenir availability.
SELECT
    sum(CASE WHEN stat_trak_available THEN 1 ELSE 0 END) AS stattrak,
    sum(CASE WHEN souvenir_available  THEN 1 ELSE 0 END) AS souvenir,
    count(*)                                             AS total
FROM skins;

-- 11. true_float split (capped range vs full 0.0–1.0; null = unknown bounds).
SELECT coalesce(true_float::text, '<null>') AS true_float, count(*)
FROM skins
GROUP BY 1
ORDER BY 2 DESC;