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

-- Removido para Hostinger/phpMyAdmin: CREATE DATABASE IF NOT EXISTS aw_player_roku CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Removido para Hostinger/phpMyAdmin: USE aw_player_roku;

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('master','admin','reseller') DEFAULT 'master',
    status ENUM('active','blocked') DEFAULT 'active',
    dns_id INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS resellers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(30),
    password_hash VARCHAR(255) NOT NULL,
    credits INT DEFAULT 0,
    expires_at DATETIME NULL,
    status ENUM('active','blocked','expired') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

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 roku_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NULL,
    dns_id INT NULL,
    device_id VARCHAR(160) UNIQUE NOT NULL,
    activation_code VARCHAR(30) UNIQUE NOT NULL,
    customer_name VARCHAR(120) NULL,
    username VARCHAR(100) NULL,
    password VARCHAR(100) NULL,
    token VARCHAR(255) NULL,
    status ENUM('pending','active','blocked','expired') DEFAULT 'pending',
    expires_at DATETIME NULL,
    last_seen DATETIME NULL,
    device_model VARCHAR(120) NULL,
    app_version VARCHAR(30) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX(status), INDEX(activation_code), INDEX(token)
);

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 IGNORE INTO admins (name,email,password_hash,role,status)
VALUES ('Administrador AW','admin@awplayer.local','$2y$10$e0MYzXyjpJS7Pd0RVvHwHeKsjh5i3eGgyT.4.iKxMwjUzxGCf/5oK','master','active');

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();

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE roku_devices ADD COLUMN updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
    'SELECT 1'
  ) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='roku_devices' AND COLUMN_NAME='updated_at'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE roku_devices ADD COLUMN device_name VARCHAR(160) NULL',
    'SELECT 1'
  ) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='roku_devices' AND COLUMN_NAME='device_name'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE dns_servers ADD COLUMN dns_code VARCHAR(30) NULL',
    'SELECT 1'
  ) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dns_servers' AND COLUMN_NAME='dns_code'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE dns_servers ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1',
    'SELECT 1'
  ) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dns_servers' AND COLUMN_NAME='is_active'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE dns_servers ADD COLUMN updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
    'SELECT 1'
  ) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dns_servers' AND COLUMN_NAME='updated_at'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Adiciona status inactive para permitir desativar sem apagar aparelho/token
ALTER TABLE roku_devices MODIFY status ENUM('pending','active','inactive','blocked','expired') DEFAULT 'pending';
UPDATE roku_devices SET status='pending' WHERE status IS NULL OR status='';

-- Índices úteis para relatório por provedor
SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE roku_devices ADD INDEX idx_roku_devices_dns_status (dns_id, status)',
    'SELECT 1'
  ) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='roku_devices' AND INDEX_NAME='idx_roku_devices_dns_status'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE dns_servers ADD INDEX idx_dns_code (dns_code)',
    'SELECT 1'
  ) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dns_servers' AND INDEX_NAME='idx_dns_code'
);
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;


-- ------------------------------------------------------------
-- LOGIN PADRAO DO PAINEL
-- Email: admin@awplayer.local
-- Senha: admin123
-- ------------------------------------------------------------
UPDATE admins
SET password_hash='$2y$12$KtNP1tS6skdqQAiC7OJ81u75llkYgUDh6IyZj1Vkvwb0uDlMPgJr6',
    status='active',
    role='master'
WHERE email='admin@awplayer.local';

INSERT INTO admins (name,email,password_hash,role,status)
SELECT 'Administrador AW','admin@awplayer.local','$2y$12$KtNP1tS6skdqQAiC7OJ81u75llkYgUDh6IyZj1Vkvwb0uDlMPgJr6','master','active'
WHERE NOT EXISTS (SELECT 1 FROM admins WHERE email='admin@awplayer.local');


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

SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='logo_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN logo_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='background_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN background_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_background_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN home_background_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='startup_banner_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN startup_banner_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_banner_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN home_banner_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='loading_logo_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN loading_logo_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='app_title')=0,
  'ALTER TABLE dns_servers ADD COLUMN app_title VARCHAR(150) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='login_title_text')=0,
  'ALTER TABLE dns_servers ADD COLUMN login_title_text VARCHAR(150) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='login_info_text')=0,
  'ALTER TABLE dns_servers ADD COLUMN login_info_text VARCHAR(255) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q = IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='support_whatsapp')=0,
  'ALTER TABLE dns_servers ADD COLUMN support_whatsapp VARCHAR(30) NULL', 'SELECT 1'); 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';

-- Garante colunas principais de personalizacao por provedor/revenda
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='dns_code')=0,
  'ALTER TABLE dns_servers ADD COLUMN dns_code VARCHAR(30) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='is_active')=0,
  'ALTER TABLE dns_servers ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='logo_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN logo_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='background_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN background_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_background_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN home_background_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='startup_banner_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN startup_banner_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_banner_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN home_banner_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='premium_home_banner')=0,
  'ALTER TABLE dns_servers ADD COLUMN premium_home_banner VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='loading_logo_url')=0,
  'ALTER TABLE dns_servers ADD COLUMN loading_logo_url VARCHAR(500) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='app_title')=0,
  'ALTER TABLE dns_servers ADD COLUMN app_title VARCHAR(150) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='login_title_text')=0,
  'ALTER TABLE dns_servers ADD COLUMN login_title_text VARCHAR(150) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='login_info_text')=0,
  'ALTER TABLE dns_servers ADD COLUMN login_info_text VARCHAR(255) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='support_whatsapp')=0,
  'ALTER TABLE dns_servers ADD COLUMN support_whatsapp VARCHAR(30) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_theme')=0,
  "ALTER TABLE dns_servers ADD COLUMN home_theme VARCHAR(50) NOT NULL DEFAULT 'classic'", 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='streaming_category_ids')=0,
  'ALTER TABLE dns_servers ADD COLUMN streaming_category_ids VARCHAR(255) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='kids_category_ids')=0,
  'ALTER TABLE dns_servers ADD COLUMN kids_category_ids VARCHAR(255) NULL', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_streaming_text')=0,
  "ALTER TABLE dns_servers ADD COLUMN home_streaming_text VARCHAR(100) NOT NULL DEFAULT 'Streamings Populares'", 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='home_kids_text')=0,
  "ALTER TABLE dns_servers ADD COLUMN home_kids_text VARCHAR(100) NOT NULL DEFAULT 'Área Kids'", 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='updated_at')=0,
  'ALTER TABLE dns_servers ADD COLUMN updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1'); 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;

-- Indices seguros
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND INDEX_NAME='idx_dns_code')=0,
  'ALTER TABLE dns_servers ADD INDEX idx_dns_code (dns_code)', 'SELECT 1'); PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;