-- Migration: Add type and embed columns to servers table (for existing installs)
-- Description: server_name = actual name; embed = empty

-- Add type column if missing
SET @dbname = DATABASE();
SET @tablename = 'servers';
SET @columnname = 'type';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname,
         ' VARCHAR(50) NOT NULL DEFAULT ''sub'' COMMENT ''sub, dub, hindi, ml, etc.'' AFTER episode_id')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add embed column if missing (empty)
SET @columnname = 'embed';
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) NULL DEFAULT NULL COMMENT ''Empty'' AFTER server_name')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Make server_name nullable for existing rows
ALTER TABLE servers MODIFY COLUMN server_name VARCHAR(100) NULL DEFAULT NULL COMMENT 'Actual server name e.g. Vidcloud, AnimeWorld';

-- Replace unique key with (episode_id, type, server_name)
ALTER TABLE servers DROP INDEX unique_servers_entry;
ALTER TABLE servers ADD UNIQUE KEY unique_servers_entry (episode_id, type, server_name);
ALTER TABLE servers ADD INDEX idx_type (type);
