



















RENAME TABLE studios TO channels;




CREATE TABLE IF NOT EXISTS video_channels (
    video_id   INT UNSIGNED NOT NULL,
    channel_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (video_id, channel_id),
    KEY idx_channel_video (channel_id, video_id),
    FOREIGN KEY (video_id)   REFERENCES videos(id)   ON DELETE CASCADE,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO video_channels (video_id, channel_id)
    SELECT video_id, studio_id FROM video_studios;
DROP TABLE IF EXISTS video_studios;


CREATE TABLE IF NOT EXISTS channel_translations (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_id  INT UNSIGNED NOT NULL,
    lang_code   VARCHAR(5) NOT NULL,
    name        VARCHAR(200) DEFAULT NULL,
    slug        VARCHAR(220) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_channel_lang (channel_id, lang_code),
    KEY idx_lang_slug (lang_code, slug),
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO channel_translations (id, channel_id, lang_code, name, slug, description, created_at, updated_at)
    SELECT id, studio_id, lang_code, name, slug, description, created_at, updated_at FROM studio_translations;
DROP TABLE IF EXISTS studio_translations;



ALTER TABLE channels ADD COLUMN total_impressions BIGINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE channels ADD COLUMN total_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE channels ADD COLUMN album_count INT UNSIGNED NOT NULL DEFAULT 0;



ALTER TABLE channels ADD FULLTEXT KEY ft_name (name);
ALTER TABLE channels ADD KEY idx_channel_videocount (video_count, name);
