-- Phase 5.1 : catalogue video persistant (table `videos`).
--
-- Volontairement MINCE : pas de table de streams, pas de formats, pas de
-- commentaires. Le but est de casser la denormalisation `title`/`thumbnail`
-- dupliquee dans `watch_history` et `playlist_items` (phase 5.4), pas de
-- reimplementer le catalogue.
--
-- Migration 100 % additive et reversible : `DROP TABLE videos` suffit a revenir
-- en arriere, aucun `ALTER` sur une table existante.

CREATE TABLE IF NOT EXISTS videos (
  provider            TEXT NOT NULL,   -- nom long (convention existante)
  video_id            TEXT NOT NULL,
  title               TEXT,
  thumbnail           TEXT,
  duration_seconds    INTEGER,
  views               INTEGER,
  published_at        TEXT,
  url                 TEXT,
  kind                TEXT,            -- video | short | live | clip | channel
  channel_external_id TEXT,
  channel_name        TEXT,
  channel_avatar_url  TEXT,
  width               INTEGER,
  height              INTEGER,
  raw_json            TEXT,            -- debug, tronque a 4 Ko
  captured_at         TEXT NOT NULL,   -- fraicheur (indicateur UI §9.3)
  created_at          TEXT,
  PRIMARY KEY (provider, video_id)
);

-- Fraicheur : sert au tri « vu recemment » et a l'indicateur de perinatalite.
CREATE INDEX IF NOT EXISTS idx_videos_captured ON videos(captured_at DESC);

-- Requetes de contenu de chaine par identifiant externe.
CREATE INDEX IF NOT EXISTS idx_videos_channel ON videos(provider, channel_external_id);

-- Phase 5.3 : backfill depuis les tables denormalisees.
-- `INSERT OR IGNORE` : ne jamais ecraser une ligne deja observee dans `videos`
-- (plus riche que la source de backfill).
--
-- Le `provider` est NORMALISE en nom long : `watch_history` et `playlist_items`
-- conservent la forme recue (courte `yt` ou longue `youtube`) selon l'appelant,
-- alors que `videos` est cle par le nom long. Sans ce CASE, ('yt','id') et
-- ('youtube','id') creeraient deux lignes et les lectures normalisees
-- n'en verraient qu'une.
INSERT OR IGNORE INTO videos
  (provider, video_id, title, thumbnail, captured_at, created_at)
SELECT
  CASE LOWER(provider)
    WHEN 'yt' THEN 'youtube'  WHEN 'dm' THEN 'dailymotion'
    WHEN 'tw' THEN 'twitch'    WHEN 'pt' THEN 'peertube'
    WHEN 'od' THEN 'odysee'    WHEN 'ru' THEN 'rumble'
    ELSE LOWER(provider) END,
  video_id, title, thumbnail, last_watched_at, last_watched_at
  FROM watch_history
 WHERE video_id IS NOT NULL AND title IS NOT NULL;

INSERT OR IGNORE INTO videos
  (provider, video_id, title, thumbnail, captured_at, created_at)
SELECT
  CASE LOWER(provider)
    WHEN 'yt' THEN 'youtube'  WHEN 'dm' THEN 'dailymotion'
    WHEN 'tw' THEN 'twitch'    WHEN 'pt' THEN 'peertube'
    WHEN 'od' THEN 'odysee'    WHEN 'ru' THEN 'rumble'
    ELSE LOWER(provider) END,
  video_id, title, thumbnail, added_at, added_at
  FROM playlist_items
 WHERE video_id IS NOT NULL AND title IS NOT NULL;