-- =====================================================================
--  Terminal-System – Grundstruktur (neutral)
--  Präfix: term_ ; idempotent (CREATE TABLE IF NOT EXISTS)
-- =====================================================================
SET NAMES utf8mb4;

-- Terminals: dauerhaftes, PIN-gesichertes Portal mit festem Hash-Link.
CREATE TABLE IF NOT EXISTS `term_terminals` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `terminal_hash`   CHAR(32)     NOT NULL,
  `terminal_label`  VARCHAR(190) NOT NULL,
  `terminal_email`  VARCHAR(190) NULL,
  `terminal_pin`    CHAR(64)     NOT NULL,          -- HMAC-Hash der PIN
  `terminal_active` TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME     NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_terminal_hash` (`terminal_hash`),
  KEY `idx_terminal_active` (`terminal_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ansichten: das, was über ein Terminal genutzt werden kann (neutraler Platzhalter;
-- der konkrete Inhalt/Modus wird später ausgestaltet).
CREATE TABLE IF NOT EXISTS `term_views` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `view_token`  CHAR(32)     NOT NULL,
  `view_label`  VARCHAR(190) NOT NULL,
  `view_active` TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME     NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_view_token` (`view_token`),
  KEY `idx_view_active` (`view_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- n:m-Zuordnung Terminal ↔ Ansicht.
CREATE TABLE IF NOT EXISTS `term_terminal_access` (
  `terminal_id` INT UNSIGNED NOT NULL,
  `view_id`     INT UNSIGNED NOT NULL,
  PRIMARY KEY (`terminal_id`, `view_id`),
  KEY `idx_tta_view` (`view_id`),
  CONSTRAINT `fk_tta_terminal` FOREIGN KEY (`terminal_id`) REFERENCES `term_terminals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tta_view`     FOREIGN KEY (`view_id`)     REFERENCES `term_views` (`id`)     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Modulares Ansichts-System
-- ---------------------------------------------------------------------
-- Modultyp je Ansicht (z.B. monthly_list). Bestehende Installationen:
--   ALTER TABLE term_views ADD COLUMN module VARCHAR(64) NOT NULL DEFAULT 'monthly_list' AFTER view_label;
-- Globale Ansichten (automatisch für ALLE Terminals frei, ohne Einzelzuordnung;
-- Deaktivieren weiterhin über view_active). Bestehende Installationen:
--   ALTER TABLE term_views ADD COLUMN view_global TINYINT(1) NOT NULL DEFAULT 0 AFTER view_active;

-- Universelle Widget-Datenbank (an WordPress wp-meta orientiert):
-- generischer Schlüssel/Wert-Speicher je Widget (= term_views.id).
CREATE TABLE IF NOT EXISTS `term_widget_meta` (
  `meta_id`    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `widget_id`  INT UNSIGNED NOT NULL,
  `meta_key`   VARCHAR(190) NOT NULL,
  `meta_value` LONGTEXT     NULL,
  `updated_at` DATETIME     NULL,
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `uq_widget_key` (`widget_id`, `meta_key`),
  KEY `idx_widget` (`widget_id`),
  CONSTRAINT `fk_wm_widget` FOREIGN KEY (`widget_id`) REFERENCES `term_views` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Terminal-Meta: Schlüssel/Wert je Terminal (z. B. Kachel-Reihenfolge 'tile_order').
-- Wird zur Laufzeit selbst angelegt (term_terminal_meta_ensure), hier nur Referenz.
CREATE TABLE IF NOT EXISTS `term_terminal_meta` (
  `meta_id`     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `terminal_id` INT UNSIGNED NOT NULL,
  `meta_key`    VARCHAR(190) NOT NULL,
  `meta_value`  LONGTEXT     NULL,
  `updated_at`  DATETIME     NULL,
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `uq_terminal_key` (`terminal_id`, `meta_key`),
  KEY `idx_terminal` (`terminal_id`),
  CONSTRAINT `fk_tm_terminal` FOREIGN KEY (`terminal_id`) REFERENCES `term_terminals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Modul „Pinnwand" (öffentlicher Feed) – Tabellen werden zur Laufzeit
-- selbst angelegt (module_pinnwand_ensure_schema), hier nur Referenz.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `term_posts` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `view_id`     INT UNSIGNED NOT NULL,
  `terminal_id` INT UNSIGNED NOT NULL,
  `body`        TEXT NULL,
  `link_url`    VARCHAR(1000) NULL,
  `status`      ENUM('active','hidden','deleted') NOT NULL DEFAULT 'active',
  `is_pinned`   TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL,
  `updated_at`  DATETIME NULL,
  `edited_at`   DATETIME NULL,   -- „bearbeitet"-Badge
  `deleted_at`  DATETIME NULL,   -- Soft-Delete
  PRIMARY KEY (`id`),
  KEY `idx_feed` (`view_id`, `status`, `id`),
  KEY `idx_term` (`terminal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Medien: post_id/comment_id NULL = hochgeladen, aber noch nicht zugeordnet
-- (Composer-Staging; >24h alte Waisen werden automatisch entfernt).
-- comment_id gesetzt = Bild-Anhang eines Kommentars (statt eines Beitrags).
CREATE TABLE IF NOT EXISTS `term_post_media` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`     INT UNSIGNED NULL,
  `comment_id`  INT UNSIGNED NULL,
  `view_id`     INT UNSIGNED NOT NULL,
  `terminal_id` INT UNSIGNED NOT NULL,
  `media_type`  ENUM('image','video') NOT NULL,
  `mime`        VARCHAR(60) NOT NULL,
  `file_base`   CHAR(32) NOT NULL,   -- zufälliger Dateiname (bin2hex(random_bytes(16)))
  `ext`         VARCHAR(8) NOT NULL,
  `file_size`   INT UNSIGNED NOT NULL DEFAULT 0,
  `width`       INT UNSIGNED NULL,
  `height`      INT UNSIGNED NULL,
  `duration`    INT UNSIGNED NULL,   -- Sekunden (nur Video, nur mit ffmpeg)
  `alt`         VARCHAR(200) NULL,
  `sort_order`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_base` (`file_base`),
  KEY `idx_post` (`post_id`),
  KEY `idx_comment` (`comment_id`),
  KEY `idx_staged` (`post_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reaktionen: eindeutig je (Beitrag, Terminal, Typ).
CREATE TABLE IF NOT EXISTS `term_post_reactions` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`     INT UNSIGNED NOT NULL,
  `terminal_id` INT UNSIGNED NOT NULL,
  `rtype`       VARCHAR(16) NOT NULL,
  `created_at`  DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_react` (`post_id`, `terminal_id`, `rtype`),
  KEY `idx_post` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Meldungen: eindeutig je (Beitrag, meldendes Terminal).
CREATE TABLE IF NOT EXISTS `term_post_reports` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`         INT UNSIGNED NOT NULL,
  `terminal_id`     INT UNSIGNED NOT NULL,   -- Melder
  `reason`          VARCHAR(24) NOT NULL,    -- spam|insult|inappropriate|privacy|fake|other
  `note`            VARCHAR(500) NULL,
  `status`          ENUM('open','resolved','dismissed') NOT NULL DEFAULT 'open',
  `mod_terminal_id` INT UNSIGNED NULL,
  `mod_note`        VARCHAR(500) NULL,
  `created_at`      DATETIME NOT NULL,
  `resolved_at`     DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_report` (`post_id`, `terminal_id`),
  KEY `idx_status` (`status`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Link-Vorschau-Cache (je URL; og:image wird lokal gespeichert, kein Hotlinking).
CREATE TABLE IF NOT EXISTS `term_post_links` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `url_hash`     CHAR(32) NOT NULL,      -- md5(lower(url))
  `url`          VARCHAR(1000) NOT NULL,
  `final_domain` VARCHAR(190) NULL,      -- nach Redirects
  `title`        VARCHAR(300) NULL,
  `descr`        VARCHAR(500) NULL,
  `img_file`     CHAR(32) NULL,          -- data/pinnwand/<view>/links/<base>*.jpg
  `fetch_status` VARCHAR(16) NOT NULL DEFAULT 'ok',
  `fetched_at`   DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_url` (`url_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pinnwand: Profile je (Ansicht, Terminal) – Anzeigename, Profil-/Header-Foto,
-- Lesemarken: notif_seen_at (🔔-Mitteilungen), feed_seen_at (Startseiten-Badge)
-- (wird wie die übrigen Pinnwand-Tabellen beim ersten Aufruf selbst angelegt)
CREATE TABLE IF NOT EXISTS `term_post_profiles` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `view_id`       INT UNSIGNED NOT NULL,
  `terminal_id`   INT UNSIGNED NOT NULL,
  `display_name`  VARCHAR(40) NULL,
  `avatar_file`   CHAR(32) NULL,
  `header_file`   CHAR(32) NULL,
  `notif_seen_at` DATETIME NULL,
  `feed_seen_at`  DATETIME NULL,
  `updated_at`    DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_profile` (`view_id`, `terminal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pinnwand: Kommentare zu Beiträgen (Soft-Delete; Autor = Terminal).
-- idx_notif für den Ungelesen-Zähler (fremde Kommentare auf eigene Beiträge).
CREATE TABLE IF NOT EXISTS `term_post_comments` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`     INT UNSIGNED NOT NULL,
  `view_id`     INT UNSIGNED NOT NULL,
  `terminal_id` INT UNSIGNED NOT NULL,
  `body`        TEXT NOT NULL,
  `created_at`  DATETIME NOT NULL,
  `edited_at`   DATETIME NULL,
  `deleted_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_post` (`post_id`, `id`),
  KEY `idx_notif` (`view_id`, `terminal_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;