237 lines
8.6 KiB
SQL
237 lines
8.6 KiB
SQL
-- 1) Vidéos manuelles (source unique, simple)
|
||
DROP VIEW IF EXISTS manual_playlists_videos;
|
||
CREATE VIEW manual_playlists_videos AS
|
||
SELECT
|
||
p.id AS playlist_id,
|
||
p.name AS playlist_name,
|
||
'manual' AS playlist_type,
|
||
vp.video_file_name
|
||
FROM playlists p
|
||
JOIN video_playlists vp ON p.id = vp.playlist_id
|
||
WHERE p.type = 'manual';
|
||
|
||
-- 2) Vidéos dynamiques : tout sauf include_playlists / exclude_playlists
|
||
-- (ceci applique labels, date_after/date_before/date_delta_days, difficulty, day_of_week, address_keyword)
|
||
DROP VIEW IF EXISTS dynamic_playlist_videos_base;
|
||
CREATE VIEW dynamic_playlist_videos_base AS
|
||
SELECT DISTINCT
|
||
p.id AS playlist_id,
|
||
p.name AS playlist_name,
|
||
'dynamic' AS playlist_type,
|
||
v.file_name AS video_file_name
|
||
FROM playlists p
|
||
JOIN videos v
|
||
WHERE p.type = 'dynamic'
|
||
|
||
/* --- include_labels (AND/OR) --- */
|
||
AND (
|
||
json_array_length(json_extract(p.rules_json, '$.include_labels')) = 0
|
||
OR (
|
||
json_extract(p.rules_json, '$.label_logic') = 'OR'
|
||
AND EXISTS (
|
||
SELECT 1 FROM json_each(json_extract(p.rules_json, '$.include_labels')) jl
|
||
JOIN labels l ON l.name = jl.value
|
||
JOIN video_labels vl ON vl.label_id = l.id AND vl.video_file_name = v.file_name
|
||
)
|
||
)
|
||
OR (
|
||
json_extract(p.rules_json, '$.label_logic') = 'AND'
|
||
AND NOT EXISTS (
|
||
-- il existe un label requis qui n'est pas présent pour la video
|
||
SELECT 1
|
||
FROM json_each(json_extract(p.rules_json, '$.include_labels')) jl
|
||
WHERE jl.value NOT IN (
|
||
SELECT l2.name
|
||
FROM labels l2
|
||
JOIN video_labels vl2 ON l2.id = vl2.label_id
|
||
WHERE vl2.video_file_name = v.file_name
|
||
)
|
||
)
|
||
)
|
||
)
|
||
|
||
/* --- exclude_labels --- */
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM json_each(json_extract(p.rules_json, '$.exclude_labels')) je
|
||
JOIN labels lx ON lx.name = je.value
|
||
JOIN video_labels vlx ON vlx.label_id = lx.id AND vlx.video_file_name = v.file_name
|
||
)
|
||
|
||
/* --- date_after / date_delta_days / date_before --- */
|
||
AND (
|
||
json_extract(p.rules_json, '$.date_after') IS NULL
|
||
OR v.record_datetime >= json_extract(p.rules_json, '$.date_after')
|
||
OR (
|
||
json_extract(p.rules_json, '$.date_delta_days') IS NOT NULL
|
||
AND v.record_datetime >= date('now', (json_extract(p.rules_json, '$.date_delta_days') || ' days'))
|
||
)
|
||
)
|
||
AND (
|
||
json_extract(p.rules_json, '$.date_before') IS NULL
|
||
OR v.record_datetime <= json_extract(p.rules_json, '$.date_before')
|
||
)
|
||
|
||
/* --- difficulty, day_of_week, address_keyword --- */
|
||
AND (
|
||
json_extract(p.rules_json, '$.difficulty') IS NULL
|
||
OR v.difficulty_level = json_extract(p.rules_json, '$.difficulty')
|
||
)
|
||
AND (
|
||
json_extract(p.rules_json, '$.day_of_week') IS NULL
|
||
OR v.day_of_week = json_extract(p.rules_json, '$.day_of_week')
|
||
)
|
||
AND (
|
||
json_extract(p.rules_json, '$.address_keyword') IS NULL
|
||
OR (
|
||
v.address NOT LIKE '%unknown%'
|
||
AND v.address LIKE '%' || json_extract(p.rules_json, '$.address_keyword') || '%'
|
||
)
|
||
);
|
||
|
||
|
||
-- 3) Mapping direct parent -> child playlist ids (extrait JSON include_playlists)
|
||
DROP VIEW IF EXISTS playlist_direct_includes;
|
||
CREATE VIEW playlist_direct_includes AS
|
||
SELECT
|
||
p.id AS parent_playlist_id,
|
||
CAST(inc.value AS INTEGER) AS child_playlist_id
|
||
FROM playlists p
|
||
JOIN json_each(json_extract(p.rules_json, '$.include_playlists')) inc
|
||
ON json_type(inc.value) IN ('integer', 'text')
|
||
WHERE json_array_length(json_extract(p.rules_json, '$.include_playlists')) > 0;
|
||
|
||
|
||
-- 4) Fermeture transitive des inclusions : parent -> descendant child
|
||
DROP VIEW IF EXISTS playlist_includes_recursive;
|
||
CREATE VIEW playlist_includes_recursive AS
|
||
WITH RECURSIVE rec(parent_playlist_id, child_playlist_id) AS (
|
||
-- base : les inclusions directes
|
||
SELECT parent_playlist_id, child_playlist_id FROM playlist_direct_includes
|
||
|
||
UNION ALL
|
||
|
||
-- récursion : si A inclut B et B inclut C, alors A inclut C
|
||
SELECT d.parent_playlist_id, di.child_playlist_id
|
||
FROM playlist_direct_includes d
|
||
JOIN rec di ON di.parent_playlist_id = d.child_playlist_id
|
||
)
|
||
SELECT DISTINCT parent_playlist_id AS parent_id, child_playlist_id AS child_id FROM rec;
|
||
|
||
|
||
-- 5) De même pour exclusions directes et récursives
|
||
DROP VIEW IF EXISTS playlist_direct_excludes;
|
||
CREATE VIEW playlist_direct_excludes AS
|
||
SELECT
|
||
p.id AS parent_playlist_id,
|
||
CAST(exc.value AS INTEGER) AS child_playlist_id
|
||
FROM playlists p
|
||
JOIN json_each(json_extract(p.rules_json, '$.exclude_playlists')) exc
|
||
ON json_type(exc.value) IN ('integer', 'text')
|
||
WHERE json_array_length(json_extract(p.rules_json, '$.exclude_playlists')) > 0;
|
||
|
||
DROP VIEW IF EXISTS playlist_excludes_recursive;
|
||
CREATE VIEW playlist_excludes_recursive AS
|
||
WITH RECURSIVE rec_ex(parent_playlist_id, child_playlist_id) AS (
|
||
SELECT parent_playlist_id, child_playlist_id FROM playlist_direct_excludes
|
||
UNION ALL
|
||
SELECT d.parent_playlist_id, di.child_playlist_id
|
||
FROM playlist_direct_excludes d
|
||
JOIN rec_ex di ON di.parent_playlist_id = d.child_playlist_id
|
||
)
|
||
SELECT DISTINCT parent_playlist_id AS parent_id, child_playlist_id AS child_id FROM rec_ex;
|
||
|
||
|
||
-- 6) vidéos apportées par les playlists incluses (manuelles + dynamic_base)
|
||
-- For each parent, collect videos that belong to any included child playlist (direct or transitive).
|
||
-- union logique (rules.logic = 'OR')
|
||
DROP VIEW IF EXISTS playlist_includes_union;
|
||
CREATE VIEW playlist_includes_union AS
|
||
-- union de base + vidéos incluses
|
||
SELECT DISTINCT db.playlist_id, db.playlist_name, db.playlist_type, db.video_file_name
|
||
FROM dynamic_playlist_videos_base db
|
||
WHERE
|
||
json_extract((SELECT rules_json FROM playlists WHERE id = db.playlist_id), '$.logic') = 'OR'
|
||
|
||
UNION
|
||
|
||
SELECT DISTINCT iv.parent_playlist_id AS playlist_id,
|
||
(SELECT name FROM playlists WHERE id = iv.parent_playlist_id) AS playlist_name,
|
||
'dynamic' AS playlist_type,
|
||
iv.video_file_name
|
||
FROM playlist_included_videos iv
|
||
JOIN playlists p ON p.id = iv.parent_playlist_id
|
||
WHERE json_extract(p.rules_json, '$.logic') = 'OR';
|
||
|
||
|
||
|
||
-- 7) intersection logique (rules.logic = 'AND')
|
||
DROP VIEW IF EXISTS playlist_includes_intersection;
|
||
CREATE VIEW playlist_includes_intersection AS
|
||
-- Cas 1️⃣ : AND + playlists incluses => intersection stricte
|
||
SELECT DISTINCT db.playlist_id, db.playlist_name, db.playlist_type, db.video_file_name
|
||
FROM dynamic_playlist_videos_base db
|
||
WHERE json_extract((SELECT rules_json FROM playlists WHERE id = db.playlist_id), '$.logic') = 'AND'
|
||
AND json_array_length(json_extract((SELECT rules_json FROM playlists WHERE id = db.playlist_id), '$.include_playlists')) > 0
|
||
AND db.video_file_name IN (
|
||
SELECT iv.video_file_name
|
||
FROM playlist_included_videos iv
|
||
WHERE iv.parent_playlist_id = db.playlist_id
|
||
)
|
||
|
||
UNION ALL
|
||
|
||
-- Cas 2️⃣ : AND + aucune playlist incluse => garder la base telle quelle
|
||
SELECT DISTINCT db.playlist_id, db.playlist_name, db.playlist_type, db.video_file_name
|
||
FROM dynamic_playlist_videos_base db
|
||
WHERE json_extract((SELECT rules_json FROM playlists WHERE id = db.playlist_id), '$.logic') = 'AND'
|
||
AND (
|
||
json_array_length(json_extract((SELECT rules_json FROM playlists WHERE id = db.playlist_id), '$.include_playlists')) IS NULL
|
||
OR json_array_length(json_extract((SELECT rules_json FROM playlists WHERE id = db.playlist_id), '$.include_playlists')) = 0
|
||
);
|
||
|
||
|
||
|
||
|
||
-- 8) regroupement des deux logiques
|
||
DROP VIEW IF EXISTS playlist_after_includes;
|
||
CREATE VIEW playlist_after_includes AS
|
||
SELECT * FROM playlist_includes_union
|
||
UNION ALL
|
||
SELECT * FROM playlist_includes_intersection;
|
||
|
||
|
||
|
||
-- 9) exclusions
|
||
DROP VIEW IF EXISTS playlist_excluded_videos;
|
||
CREATE VIEW playlist_excluded_videos AS
|
||
SELECT per.parent_id AS parent_playlist_id, mpv.video_file_name
|
||
FROM playlist_excludes_recursive per
|
||
JOIN manual_playlists_videos mpv ON mpv.playlist_id = per.child_id
|
||
|
||
UNION
|
||
|
||
SELECT per.parent_id AS parent_playlist_id, dpb.video_file_name
|
||
FROM playlist_excludes_recursive per
|
||
JOIN dynamic_playlist_videos_base dpb ON dpb.playlist_id = per.child_id;
|
||
|
||
|
||
|
||
-- 10) Appliquer exclusions
|
||
DROP VIEW IF EXISTS playlist_after_excludes;
|
||
CREATE VIEW playlist_after_excludes AS
|
||
SELECT pai.playlist_id, pai.playlist_name, pai.playlist_type, pai.video_file_name
|
||
FROM playlist_after_includes pai
|
||
LEFT JOIN playlist_excluded_videos pev
|
||
ON pev.parent_playlist_id = pai.playlist_id AND pev.video_file_name = pai.video_file_name
|
||
WHERE pev.parent_playlist_id IS NULL;
|
||
|
||
-- 11) résultat final « flat »
|
||
|
||
DROP VIEW IF EXISTS playlist_videos_flat;
|
||
CREATE VIEW playlist_videos_flat AS
|
||
SELECT playlist_id, playlist_name, playlist_type, video_file_name
|
||
FROM manual_playlists_videos
|
||
UNION ALL
|
||
SELECT playlist_id, playlist_name, playlist_type, video_file_name
|
||
FROM playlist_after_excludes;
|