-- ============================================================
-- AW PAINEL APP ROKU - BANCO SQL UNICO
-- Banco principal + todos os patches aplicados em um arquivo
-- Gerado para instalacao limpa sem pasta sql/ separada
-- ============================================================

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

CREATE TABLE IF NOT EXISTS dns_servers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    base_url VARCHAR(255) NOT NULL,
    dns_code VARCHAR(30) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    logo_url VARCHAR(500) NULL,
    background_url VARCHAR(500) NULL,
    home_background_url VARCHAR(500) NULL,
    startup_banner_url VARCHAR(500) NULL,
    home_banner_url VARCHAR(500) NULL,
    premium_home_banner VARCHAR(500) NULL,
    loading_logo_url VARCHAR(500) NULL,
    app_title VARCHAR(150) NULL,
    login_title_text VARCHAR(150) NULL,
    login_info_text VARCHAR(255) NULL,
    support_whatsapp VARCHAR(30) NULL,
    home_theme VARCHAR(50) NOT NULL DEFAULT 'classic',
    streaming_category_ids VARCHAR(255) NULL,
    kids_category_ids VARCHAR(255) NULL,
    home_streaming_text VARCHAR(100) NOT NULL DEFAULT 'Streamings Populares',
    home_kids_text VARCHAR(100) NOT NULL DEFAULT 'Área Kids',
    server_type ENUM('xtream','m3u','custom') DEFAULT 'xtream',
    status ENUM('active','inactive') DEFAULT 'active',
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    content_type ENUM('live','movie','series') NOT NULL,
    content_id VARCHAR(80) NOT NULL,
    title VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_fav (device_id, content_type, content_id)
);

CREATE TABLE IF NOT EXISTS watch_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    content_type ENUM('movie','series') NOT NULL,
    content_id VARCHAR(80) NOT NULL,
    episode_id VARCHAR(80) NULL,
    position_seconds INT DEFAULT 0,
    duration_seconds INT DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_progress (device_id, content_type, content_id, episode_id)
);

CREATE TABLE IF NOT EXISTS app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL
);

CREATE TABLE IF NOT EXISTS device_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NULL,
    level ENUM('info','warning','error') DEFAULT 'info',
    message TEXT NOT NULL,
    context TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO app_settings(setting_key, setting_value)
VALUES ('app_name','AW Player Roku'),('maintenance_message','')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

-- ============================================================
-- PATCHES CONSOLIDADOS
-- ============================================================

-- ------------------------------------------------------------
-- PATCH: atualizar_app_version_display.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) UNIQUE NOT NULL,
  setting_value TEXT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings (setting_key, setting_value, updated_at) VALUES
('app_version_display','1.8.216',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_key=setting_key;


-- ------------------------------------------------------------
-- PATCH: atualizar_home_background_v217.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings(setting_key, setting_value, updated_at)
VALUES
('home_background_url','',NOW()),
('app_version_display','1.8.217',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_value=setting_value, updated_at=updated_at;


-- ------------------------------------------------------------
-- PATCH: atualizar_loading_painel.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings(setting_key, setting_value, updated_at)
VALUES
('login_mode','provider_code',NOW()),
('logo_url','',NOW()),
('background_url','',NOW()),
('startup_banner_url','',NOW()),
('home_banner_url','',NOW()),
('app_banner_url','',NOW()),
('support_whatsapp','',NOW()),
('app_title','AW PLAYER ROKU',NOW()),
('app_subtitle','Canais, favoritos, filmes e séries em uma experiência moderna',NOW()),
('startup_loading_text','Carregando sistema de ativação',NOW()),
('startup_hint_text','Cadastre o código no Painel AW Player Roku',NOW()),
('login_title_text','AW PLAYER ROKU',NOW()),
('login_info_text','Digite código do provedor, usuário e senha.',NOW()),
('loading_logo_url','',NOW()),
('loading_title_text','',NOW()),
('loading_sub_text','',NOW()),
('loading_style','static',NOW()),
('player_seek_movie_seconds','30',NOW()),
('player_seek_series_seconds','300',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_value=setting_value, updated_at=updated_at;


-- ------------------------------------------------------------
-- PATCH: atualizar_logo_fundo.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings(setting_key, setting_value, updated_at)
VALUES
('login_mode','provider_code',NOW()),
('logo_url','',NOW()),
('background_url','',NOW()),
('startup_banner_url','',NOW()),
('home_banner_url','',NOW()),
('app_banner_url','',NOW()),
('support_whatsapp','',NOW()),
('app_title','AW PLAYER ROKU',NOW()),
('app_subtitle','Canais, favoritos, filmes e séries em uma experiência moderna',NOW()),
('startup_loading_text','Carregando sistema de ativação',NOW()),
('startup_hint_text','Cadastre o código no Painel AW Player Roku',NOW()),
('login_title_text','AW PLAYER ROKU',NOW()),
('login_info_text','Digite código do provedor, usuário e senha.',NOW()),
('loading_logo_url','',NOW()),
('loading_title_text','',NOW()),
('loading_sub_text','',NOW()),
('loading_style','static',NOW()),
('player_seek_movie_seconds','30',NOW()),
('player_seek_series_seconds','300',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_value=setting_value, updated_at=updated_at;


-- ------------------------------------------------------------
-- PATCH: atualizar_player_seek.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings(setting_key, setting_value, updated_at)
VALUES
('player_seek_movie_seconds','30',NOW()),
('player_seek_series_seconds','300',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_value=setting_value, updated_at=updated_at;


-- ------------------------------------------------------------
-- PATCH: atualizar_provedores_ativacoes.sql
-- ------------------------------------------------------------
-- AW Player Roku - Provedores com contagem e aparelhos inativos
-- Pode rodar mais de uma vez sem problema.

-- Garante campos usados pelo painel/API em instalações antigas
SET @db := DATABASE();
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ------------------------------------------------------------
-- PATCH: atualizar_proximo_episodio.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings(setting_key, setting_value, updated_at)
VALUES
('next_episode_enabled','1',NOW()),
('next_episode_prompt_seconds','60',NOW()),
('next_episode_button_text','Próximo episódio',NOW()),
('next_episode_button_color','blue',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_value=setting_value, updated_at=updated_at;


-- ------------------------------------------------------------
-- PATCH: atualizar_textos_app_v213.sql
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roku_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) UNIQUE NOT NULL,
  setting_value TEXT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings (setting_key, setting_value, updated_at) VALUES
('app_version_display','1.8.216',NOW()),
('app_title','AW PLAYER ROKU',NOW()),
('app_subtitle','Canais, filmes e séries em uma experiência moderna',NOW()),
('startup_loading_text','Carregando sistema',NOW()),
('startup_hint_text','Aguarde...',NOW()),
('login_title_text','AW PLAYER REPRODUTOR',NOW()),
('login_info_text','Digite código do provedor, usuário e senha.',NOW()),
('home_live_text','TV ao vivo',NOW()),
('home_movies_text','Filmes',NOW()),
('home_series_text','Séries',NOW()),
('home_list_text','Lista',NOW()),
('home_settings_text','Configurações',NOW()),
('home_reload_text','Recarregar',NOW()),
('home_expiration_prefix','Vencimento da lista:',NOW()),
('list_title_text','MINHA LISTA',NOW()),
('list_username_label','Usuário:',NOW()),
('list_expiration_label','Vencimento:',NOW()),
('list_device_label','Chave do dispositivo:',NOW()),
('list_version_label','Versão do aplicativo:',NOW()),
('list_change_login_text','Trocar lista / Login',NOW()),
('list_delete_text','Excluir lista',NOW()),
('list_back_text','Voltar',NOW()),
('login_provider_label','Provedor',NOW()),
('login_user_label','Usuário',NOW()),
('login_password_label','Senha',NOW()),
('login_button_text','ENTRAR',NOW()),
('login_keyboard_done_text','OK',NOW()),
('login_keyboard_clear_text','LIMPAR',NOW()),
('login_keyboard_space_text','ESPAÇO',NOW()),
('login_keyboard_back_text','VOLTAR',NOW()),
('loading_title_text','',NOW()),
('loading_sub_text','',NOW()),
('loading_style','static',NOW()),
('player_seek_movie_seconds','30',NOW()),
('player_seek_series_seconds','300',NOW()),
('next_episode_enabled','1',NOW()),
('next_episode_prompt_seconds','60',NOW()),
('next_episode_button_text','Próximo episódio',NOW()),
('next_episode_button_color','blue',NOW()),
('login_mode','provider_code',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_key=setting_key;


-- ------------------------------------------------------------
-- PATCH: personalizacao_por_revenda_dns.sql
-- Cada codigo/provedor pode ter sua propria aparencia no app Roku
-- ------------------------------------------------------------
SET @db = DATABASE();
SET @tbl = 'dns_servers';PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;


-- ============================================================
-- PATCH BANCO NOVO - TEMAS / BANNER PREMIUM / STREAMING / KIDS
-- Pode rodar mais de uma vez sem erro.
-- Corrige: Unknown column 'premium_home_banner'
-- ============================================================

SET @db := DATABASE();
SET @tbl := 'dns_servers';PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;

-- Corrige valores nulos/vazios antes de travar NOT NULL
UPDATE dns_servers SET home_theme='classic' WHERE home_theme IS NULL OR home_theme='' OR home_theme='default' OR home_theme='premium';
UPDATE dns_servers SET home_streaming_text='Streamings Populares' WHERE home_streaming_text IS NULL OR home_streaming_text='';
UPDATE dns_servers SET home_kids_text='Área Kids' WHERE home_kids_text IS NULL OR home_kids_text='';
UPDATE dns_servers SET is_active=1 WHERE is_active IS NULL;

ALTER TABLE dns_servers MODIFY home_theme VARCHAR(50) NOT NULL DEFAULT 'classic';
ALTER TABLE dns_servers MODIFY home_streaming_text VARCHAR(100) NOT NULL DEFAULT 'Streamings Populares';
ALTER TABLE dns_servers MODIFY home_kids_text VARCHAR(100) NOT NULL DEFAULT 'Área Kids';

-- Configuracoes globais novas
CREATE TABLE IF NOT EXISTS roku_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) UNIQUE NOT NULL,
  setting_value TEXT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roku_settings(setting_key, setting_value, updated_at) VALUES
('home_theme','classic',NOW()),
('premium_home_banner','',NOW()),
('home_streaming_text','Streamings Populares',NOW()),
('home_kids_text','Área Kids',NOW()),
('assets_version', UNIX_TIMESTAMP(), NOW())
ON DUPLICATE KEY UPDATE setting_key=setting_key;PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;

