-- 统计全部素材中的重复命名（整个素材库）
-- ============================================

-- 1. 按 title 分组，找出重复的素材
SELECT
    title,
    COUNT(*) AS duplicate_count,
    STRING_AGG(DISTINCT url, ', ' ORDER BY url) AS urls,
    STRING_AGG(DISTINCT CAST(id AS TEXT), ', ' ORDER BY CAST(id AS TEXT)) AS ids
FROM mjk_broll_materials
WHERE status != 'deleted'
GROUP BY title
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, title;

-- 2. 按 url 分组，找出重复的素材
SELECT
    url,
    COUNT(*) AS duplicate_count,
    STRING_AGG(DISTINCT title, ', ' ORDER BY title) AS titles,
    STRING_AGG(DISTINCT CAST(id AS TEXT), ', ' ORDER BY CAST(id AS TEXT)) AS ids
FROM mjk_broll_materials
WHERE status != 'deleted'
GROUP BY url
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, url;

-- 3. 统计概览：总素材数、唯一 title 数、唯一 url 数
SELECT
    COUNT(*) AS total_materials,
    COUNT(DISTINCT title) AS unique_titles,
    COUNT(DISTINCT url) AS unique_urls,
    COUNT(*) - COUNT(DISTINCT title) AS title_duplicates,
    COUNT(*) - COUNT(DISTINCT url) AS url_duplicates
FROM mjk_broll_materials
WHERE status != 'deleted';
