-- ============================================================ -- 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; -- ------------------------------------------------------------ -- USUÁRIOS DO SISTEMA -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(180) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','reseller','operator') NOT NULL DEFAULT 'operator', two_fa_secret VARCHAR(64) NULL, two_fa_enabled TINYINT(1) NOT NULL DEFAULT 0, permissions JSON NULL, -- ex: {"channels":true,"movies":true} is_active TINYINT(1) NOT NULL DEFAULT 1, last_login DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 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; -- ------------------------------------------------------------ -- USUÁRIO ADMIN PADRÃO (senha: Admin@123) -- ------------------------------------------------------------ INSERT IGNORE INTO users (name, email, password_hash, role, two_fa_enabled) VALUES ( 'Admin Master', 'admin@iptv.local', '$2b$12$LQv3c1yqBWVHxkd0LQ1Ns.vXJ5FvZdE4g3z1Wq8sE6K7mN9pR2uOa', 'admin', 0 );