CREATE DATABASE IF NOT EXISTS streaming_core CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE streaming_core;
CREATE TABLE admin_users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(64) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE clients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(64) NOT NULL UNIQUE,password VARCHAR(128) NOT NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,is_trial TINYINT(1) NOT NULL DEFAULT 0,expires_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,max_connections SMALLINT UNSIGNED NOT NULL DEFAULT 1,bouquet_id BIGINT UNSIGNED NOT NULL DEFAULT 0,INDEX idx_client_active(username,enabled,expires_at)) ENGINE=InnoDB;
CREATE TABLE categories (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,type ENUM('live','movie','series') NOT NULL,name VARCHAR(255) NOT NULL,parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0,position INT NOT NULL DEFAULT 0,is_adult TINYINT(1) NOT NULL DEFAULT 0,UNIQUE KEY uq_categories_type_name(type,name),INDEX idx_category_type(type,position)) ENGINE=InnoDB;
CREATE TABLE streams (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,stream_type ENUM('live','movie') NOT NULL,name VARCHAR(255) NOT NULL,category_id BIGINT UNSIGNED NOT NULL,link TEXT NOT NULL,delivery_mode VARCHAR(32) NOT NULL DEFAULT 'padrao',container_extension VARCHAR(16) NOT NULL DEFAULT 'ts',stream_icon TEXT NULL,epg_channel_id VARCHAR(128) NULL,rating VARCHAR(32) NULL,rating_5based VARCHAR(32) NULL,plot TEXT NULL,genre TEXT NULL,director TEXT NULL,actors TEXT NULL,release_date VARCHAR(64) NULL,duration VARCHAR(64) NULL,duration_secs INT NULL,backdrop_path TEXT NULL,tmdb_id BIGINT NULL,is_adult TINYINT(1) NOT NULL DEFAULT 0,added INT NULL,source_fingerprint CHAR(64) NULL,updated_at DATETIME NULL,UNIQUE KEY uq_stream_source(source_fingerprint),INDEX idx_stream_type_cat(stream_type,category_id),INDEX idx_stream_epg(epg_channel_id),INDEX idx_stream_tmdb(tmdb_id)) ENGINE=InnoDB;
CREATE TABLE series (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,category_id BIGINT UNSIGNED NOT NULL,cover TEXT NULL,plot TEXT NULL,cast TEXT NULL,director TEXT NULL,genre TEXT NULL,release_date VARCHAR(64) NULL,rating VARCHAR(32) NULL,rating_5based VARCHAR(32) NULL,backdrop_path TEXT NULL,tmdb_id BIGINT NULL,is_adult TINYINT(1) NOT NULL DEFAULT 0,source_fingerprint CHAR(64) NULL,updated_at DATETIME NULL,UNIQUE KEY uq_series_source(source_fingerprint),INDEX idx_series_cat(category_id),INDEX idx_series_tmdb(tmdb_id)) ENGINE=InnoDB;
CREATE TABLE series_seasons (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,series_id BIGINT UNSIGNED NOT NULL,season_number INT NOT NULL,name VARCHAR(255) NULL,episode_count INT NOT NULL DEFAULT 0,UNIQUE KEY uq_season(series_id,season_number)) ENGINE=InnoDB;
CREATE TABLE series_episodes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,series_id BIGINT UNSIGNED NOT NULL,season INT NOT NULL DEFAULT 1,episode_num INT NOT NULL DEFAULT 1,title VARCHAR(255) NOT NULL,link TEXT NOT NULL,delivery_mode VARCHAR(32) NOT NULL DEFAULT 'padrao',container_extension VARCHAR(16) NOT NULL DEFAULT 'mp4',movie_image TEXT NULL,plot TEXT NULL,duration VARCHAR(64) NULL,air_date VARCHAR(64) NULL,source_fingerprint CHAR(64) NULL,updated_at DATETIME NULL,UNIQUE KEY uq_episode_source(source_fingerprint),INDEX idx_episode(series_id,season,episode_num)) ENGINE=InnoDB;
CREATE TABLE bouquets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL) ENGINE=InnoDB;
CREATE TABLE bouquet_items (bouquet_id BIGINT UNSIGNED NOT NULL,category_id BIGINT UNSIGNED NOT NULL,PRIMARY KEY(bouquet_id,category_id),INDEX idx_bi_cat(category_id)) ENGINE=InnoDB;
CREATE TABLE connections (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(64) NOT NULL,ip VARCHAR(45) NOT NULL,device_id VARCHAR(128) NOT NULL DEFAULT '',user_agent VARCHAR(512) NULL,type ENUM('live','vod','series') NOT NULL,stream_name VARCHAR(255) NULL,last_activity DATETIME NOT NULL,INDEX idx_conn_user(username),INDEX idx_conn_activity(last_activity)) ENGINE=InnoDB;
CREATE TABLE epg (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,channel_id VARCHAR(128) NOT NULL,start DATETIME NOT NULL,end DATETIME NOT NULL,title VARCHAR(255) NOT NULL,description TEXT NULL,INDEX idx_epg(channel_id,start,end)) ENGINE=InnoDB;
CREATE TABLE import_jobs (id CHAR(32) PRIMARY KEY,source TEXT NOT NULL,source_type ENUM('url','file') NOT NULL,source_path TEXT NULL,source_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,status ENUM('queued','running','completed','failed','cancelled') NOT NULL DEFAULT 'queued',selected_json LONGTEXT NULL,tmdb_enabled TINYINT(1) NOT NULL DEFAULT 1,total_items BIGINT UNSIGNED NOT NULL DEFAULT 0,processed_items BIGINT UNSIGNED NOT NULL DEFAULT 0,offset_items BIGINT UNSIGNED NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,completed_at DATETIME NULL,error_message TEXT NULL,INDEX idx_import_jobs_status(status,updated_at)) ENGINE=InnoDB;
CREATE TABLE import_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id CHAR(32) NULL,level ENUM('debug','info','warning','error') NOT NULL,event VARCHAR(64) NOT NULL,message TEXT NOT NULL,context_json LONGTEXT NULL,created_at DATETIME NOT NULL,INDEX idx_import_logs_job(job_id,id),INDEX idx_import_logs_created(created_at)) ENGINE=InnoDB;
CREATE TABLE tmdb_cache (cache_key CHAR(40) PRIMARY KEY,payload_json LONGTEXT NOT NULL,expires_at DATETIME NOT NULL,INDEX idx_tmdb_cache_exp(expires_at)) ENGINE=InnoDB;