-- =====================================================================
--  ArNi CRM – Medien-Terminal (Filme & Musik). Prefix crm_media_*.
--  Zentral gespeichert, terminalübergreifend sichtbar. Idempotent.
-- =====================================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `crm_media_categories` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(190) NOT NULL,
  `type`       ENUM('film','music','both','youtube') NOT NULL DEFAULT 'both',
  `parent_id`  INT UNSIGNED NULL,          -- übergeordnete Kategorie (eine Ebene tief → Unterkategorie)
  `color`      VARCHAR(20)  NULL,
  `icon`       VARCHAR(40)  NULL,
  `sort`       INT NOT NULL DEFAULT 0,
  `sort_mode`  VARCHAR(20) NOT NULL DEFAULT 'default', -- default|published_asc|published_desc|title|added_asc|manual
  `active`     TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_mc_type` (`type`), KEY `idx_mc_active` (`active`), KEY `idx_mc_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_tags` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(120) NOT NULL,
  `slug`       VARCHAR(140) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_mt_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_items` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `media_type`  ENUM('film','music','youtube') NOT NULL,
  `yt_kind`     VARCHAR(20) NULL,           -- YouTube: video | channel; Film: movie | series
  `channel_id`  VARCHAR(64) NULL,           -- YouTube-Kanal-ID (UC…)
  `title`       VARCHAR(300) NOT NULL,
  `artist`      VARCHAR(300) NULL,          -- Musik
  `album`       VARCHAR(300) NULL,          -- Musik
  `category_id`    INT UNSIGNED NULL,       -- Hauptkategorie
  `subcategory_id` INT UNSIGNED NULL,        -- Unterkategorie (Kind von category_id)
  `subcategory` VARCHAR(190) NULL,           -- (Legacy Freitext, nicht mehr genutzt)
  `year`        SMALLINT UNSIGNED NULL,     -- Film
  `published_at` DATE NULL,                -- YouTube-Veröffentlichungsdatum
  `sort_pos`    INT NOT NULL DEFAULT 0,      -- manuelle Reihenfolge in Kategorie
  `runtime`     VARCHAR(40) NULL,           -- Film
  `genre`       VARCHAR(255) NULL,          -- Film
  `imdb_id`     VARCHAR(20) NULL,           -- Film
  `ext_id`      VARCHAR(40) NULL,           -- TMDb/OMDb-ID
  `ext_link`    VARCHAR(600) NULL,          -- IMDb/Plattform-Link
  `trailer_url` VARCHAR(600) NULL,          -- YouTube-Trailer (Film), im Terminal eingebettet
  `stream_sources` TEXT NULL,               -- Streaming-Quellen (JSON: [{platform,url}]) – Film
  `platform`    VARCHAR(40) NULL,           -- Musik (spotify/youtube/…)
  `image`       VARCHAR(600) NULL,          -- Poster/Cover
  `ext_rating`  DECIMAL(4,1) NULL,          -- IMDb/TMDb/OMDb
  `description` TEXT NULL,
  `own_note`    TEXT NULL,
  `own_rating`  DECIMAL(3,1) NULL,          -- Rating des Erstellers (Ø aus ratings)
  `status`      VARCHAR(20) NULL,           -- seen/want/recommended/rejected/favorite
  `recommend`   TINYINT(1) NOT NULL DEFAULT 0,
  `created_hash`     CHAR(32) NULL,
  `created_terminal` VARCHAR(190) NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_mi_type` (`media_type`), KEY `idx_mi_cat` (`category_id`), KEY `idx_mi_subcat` (`subcategory_id`),
  KEY `idx_mi_imdb` (`imdb_id`), KEY `idx_mi_platform` (`platform`),
  KEY `idx_mi_status` (`status`), KEY `idx_mi_title` (`title`(80)),
  KEY `idx_mi_link` (`ext_link`(120))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_item_tags` (
  `item_id` INT UNSIGNED NOT NULL,
  `tag_id`  INT UNSIGNED NOT NULL,
  PRIMARY KEY (`item_id`,`tag_id`), KEY `idx_mit_tag` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_ratings` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id`     INT UNSIGNED NOT NULL,
  `rater_hash`  CHAR(32) NOT NULL,
  `rater_label` VARCHAR(190) NULL,
  `value`       DECIMAL(3,1) NOT NULL,      -- 1..10
  `comment`     TEXT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_mr` (`item_id`,`rater_hash`), KEY `idx_mr_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_playlists` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `image`       VARCHAR(600) NULL,          -- Cover (vom ersten Track)
  `source`      VARCHAR(190) NULL,          -- Importname/-typ/-plattform
  `source_url`  VARCHAR(600) NULL,          -- Original-Import-Link (→ „In Spotify öffnen")
  `favorite`    TINYINT(1) NOT NULL DEFAULT 0,
  `created_hash`     CHAR(32) NULL,
  `created_terminal` VARCHAR(190) NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Playlist-Tags (eigene Zuordnung, nutzt crm_media_tags)
CREATE TABLE IF NOT EXISTS `crm_media_playlist_tags` (
  `playlist_id` INT UNSIGNED NOT NULL,
  `tag_id`      INT UNSIGNED NOT NULL,
  PRIMARY KEY (`playlist_id`,`tag_id`), KEY `idx_mpt_tag` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_playlist_items` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `playlist_id` INT UNSIGNED NOT NULL,
  `item_id`     INT UNSIGNED NULL,          -- optional Verknüpfung zu media_items
  `title`       VARCHAR(300) NULL,
  `artist`      VARCHAR(300) NULL,
  `album`       VARCHAR(300) NULL,
  `image`       VARCHAR(600) NULL,          -- Cover (lokal geladen)
  `platform`    VARCHAR(40) NULL,           -- spotify/youtube/soundcloud/…
  `link`        VARCHAR(600) NULL,          -- Original-Link (z. B. YouTube)
  `spotify_id`  VARCHAR(64) NULL,           -- Spotify-Track-ID (auch bei YouTube-Match → Web-Player)
  `position`    INT NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_mpi_pl` (`playlist_id`), KEY `idx_mpi_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_imports` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(190) NULL,
  `source_type` VARCHAR(30) NULL,
  `count`       INT NOT NULL DEFAULT 0,
  `created_hash` CHAR(32) NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_media_external_cache` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider`   VARCHAR(30) NOT NULL,
  `ext_key`    VARCHAR(300) NOT NULL,
  `payload`    LONGTEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_mec` (`provider`,`ext_key`(190))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- YouTube „Best of"-Favoriten je Kanal (Kanal-Ansicht-Slideshow)
CREATE TABLE IF NOT EXISTS `crm_media_channel_favs` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `channel_id`  VARCHAR(64) NOT NULL,       -- YouTube-Kanal-ID (UC…)
  `video_id`    VARCHAR(32) NOT NULL,
  `title` VARCHAR(300) NULL, `artist` VARCHAR(300) NULL, `image` VARCHAR(600) NULL,
  `runtime` VARCHAR(40) NULL, `published_at` DATE NULL,
  `created_hash` CHAR(32) NULL, `created_terminal` VARCHAR(190) NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_cf` (`video_id`), KEY `idx_cf_chan` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;