-- ============================================================
-- 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;