-- ============================================================
-- HOTFIX REVENDAS / HTTP 500
-- Corrige banco para resellers.php e painel de revenda.
-- Pode rodar varias vezes sem erro.
-- ============================================================

SET @db := DATABASE();

-- =====================
-- ADMINS / REVENDEDORES
-- =====================
SET @tbl := 'admins';

-- Garante que role aceite reseller
ALTER TABLE admins MODIFY role ENUM('master','admin','reseller') NOT NULL DEFAULT 'master';

-- Garante status usado pelo painel
ALTER TABLE admins MODIFY status ENUM('active','blocked') NOT NULL DEFAULT 'active';

-- Garante vinculo do login da revenda com o provedor/DNS
SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND COLUMN_NAME='dns_id')=0,
  'ALTER TABLE admins ADD COLUMN dns_id INT NULL AFTER status',
  'SELECT 1');
PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;

SET @q := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl AND INDEX_NAME='idx_admin_dns_id')=0,
  'ALTER TABLE admins ADD INDEX idx_admin_dns_id (dns_id)',
  'SELECT 1');
PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st;

-- =====================
-- DNS_SERVERS / PERSONALIZACAO
-- =====================
SET @tbl := 'dns_servers';

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 AFTER base_url', '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 AFTER dns_code', '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;

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

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;

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