-- ============================================================
--  DEVELOPER SERVER PANEL — MySQL Schema
--  Banco: iptv_panel
-- ============================================================

CREATE DATABASE IF NOT EXISTS iptv_panel
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE iptv_panel;

-- ------------------------------------------------------------
-- SERVIDORES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS servers (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100)  NOT NULL,
  ip            VARCHAR(45)   NOT NULL,
  port          SMALLINT UNSIGNED NOT NULL DEFAULT 8080,
  dns           VARCHAR(255)  NULL,
  location      VARCHAR(120)  NULL,
  os            VARCHAR(80)   NULL,
  cpu_info      VARCHAR(120)  NULL,
  ram_gb        SMALLINT      NULL,
  disk_gb       INT           NULL,
  status        ENUM('online','offline','degraded') NOT NULL DEFAULT 'offline',
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- MÉTRICAS DOS SERVIDORES (série temporal leve)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS server_metrics (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  server_id     INT UNSIGNED    NOT NULL,
  cpu_pct       DECIMAL(5,2)    NOT NULL DEFAULT 0,
  ram_pct       DECIMAL(5,2)    NOT NULL DEFAULT 0,
  disk_pct      DECIMAL(5,2)    NOT NULL DEFAULT 0,
  net_mbps      DECIMAL(10,2)   NOT NULL DEFAULT 0,
  ping_ms       SMALLINT        NOT NULL DEFAULT 0,
  users_online  INT UNSIGNED    NOT NULL DEFAULT 0,
  recorded_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
  INDEX idx_srv_time (server_id, recorded_at)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- CATEGORIAS DE CANAIS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS channel_categories (
  id    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT IGNORE INTO channel_categories (name) VALUES
  ('Aberta'),('Esportes'),('Filmes/Séries'),('Notícias'),
  ('Infantil'),('Adulto'),('Internacional'),('Música'),('Documentários');

-- ------------------------------------------------------------
-- CANAIS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS channels (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150)  NOT NULL,
  category_id SMALLINT UNSIGNED NULL,
  stream_url  TEXT          NOT NULL,
  logo_url    VARCHAR(500)  NULL,
  country     CHAR(2)       NULL,
  quality     ENUM('SD','HD','FHD','4K') NOT NULL DEFAULT 'HD',
  epg_id      VARCHAR(120)  NULL,
  epg_enabled TINYINT(1)   NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  server_id   INT UNSIGNED  NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES channel_categories(id) ON DELETE SET NULL,
  FOREIGN KEY (server_id)   REFERENCES servers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- FILMES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS movies (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(200)  NOT NULL,
  cover_url    VARCHAR(500)  NULL,
  synopsis     TEXT          NULL,
  genre        VARCHAR(120)  NULL,
  year         SMALLINT      NULL,
  duration_min SMALLINT      NULL,
  rating       DECIMAL(3,1)  NULL,
  quality      ENUM('SD','HD','FHD','4K') NOT NULL DEFAULT 'HD',
  content_url  TEXT          NOT NULL,
  server_id    INT UNSIGNED  NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE SET NULL,
  FULLTEXT INDEX ft_movies (title, synopsis)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SÉRIES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS series (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200)  NOT NULL,
  cover_url   VARCHAR(500)  NULL,
  synopsis    TEXT          NULL,
  genre       VARCHAR(120)  NULL,
  year        SMALLINT      NULL,
  status      ENUM('ongoing','completed','cancelled') NOT NULL DEFAULT 'ongoing',
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FULLTEXT INDEX ft_series (title, synopsis)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS seasons (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  series_id  INT UNSIGNED NOT NULL,
  number     TINYINT UNSIGNED NOT NULL,
  title      VARCHAR(150) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (series_id) REFERENCES series(id) ON DELETE CASCADE,
  UNIQUE KEY uq_series_season (series_id, number)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS episodes (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  season_id    INT UNSIGNED  NOT NULL,
  number       SMALLINT UNSIGNED NOT NULL,
  title        VARCHAR(200)  NULL,
  duration_min SMALLINT      NULL,
  content_url  TEXT          NOT NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (season_id) REFERENCES seasons(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- LOGS DE SISTEMA
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_logs (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  level      ENUM('info','warn','error') NOT NULL DEFAULT 'info',
  message    TEXT           NOT NULL,
  context    JSON           NULL,
  user_id    INT UNSIGNED   NULL,
  ip         VARCHAR(45)    NULL,
  created_at DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_level_time (level, created_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- NOTIFICAÇÕES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type       ENUM('alert','info','success','warning') NOT NULL DEFAULT 'info',
  title      VARCHAR(200)  NOT NULL,
  body       TEXT          NULL,
  channel    ENUM('system','telegram','whatsapp','email') NOT NULL DEFAULT 'system',
  is_read    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SESSÕES JWT (blacklist para logout)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS token_blacklist (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token_jti  VARCHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME    NOT NULL,
  INDEX idx_exp (expires_at)
) ENGINE=InnoDB;
