-- Database schema for ZapPlay TV Backend
-- Create database
CREATE DATABASE IF NOT EXISTS zapplay_tv CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE zapplay_tv;

-- Table: playlists
CREATE TABLE IF NOT EXISTS playlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    m3u8_url TEXT NOT NULL,
    user_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: categories
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    icon VARCHAR(255) NULL,
    playlist_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_playlist_id (playlist_id),
    FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: channels
CREATE TABLE IF NOT EXISTS channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    url TEXT NOT NULL,
    logo VARCHAR(500) NULL,
    category_id INT NULL,
    description TEXT NULL,
    language VARCHAR(50) NULL,
    country VARCHAR(50) NULL,
    is_hd TINYINT(1) DEFAULT 0,
    epg_id VARCHAR(255) NULL,
    playlist_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category_id (category_id),
    INDEX idx_playlist_id (playlist_id),
    INDEX idx_name (name),
    FULLTEXT idx_search (name, description),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: favorites
CREATE TABLE IF NOT EXISTS favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    channel_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_channel (user_id, channel_id),
    INDEX idx_user_id (user_id),
    INDEX idx_channel_id (channel_id),
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data
INSERT INTO playlists (name, m3u8_url, user_id) VALUES
('Default Playlist', 'https://example.com/playlist.m3u8', NULL);

SET @playlist_id = LAST_INSERT_ID();

INSERT INTO categories (name, icon, playlist_id) VALUES
('Entertainment', '🎬', @playlist_id),
('Sports', '⚽', @playlist_id),
('News', '📰', @playlist_id),
('Movies', '🎥', @playlist_id),
('Kids', '👶', @playlist_id);

SET @entertainment_id = (SELECT id FROM categories WHERE name = 'Entertainment' LIMIT 1);
SET @sports_id = (SELECT id FROM categories WHERE name = 'Sports' LIMIT 1);
SET @news_id = (SELECT id FROM categories WHERE name = 'News' LIMIT 1);

INSERT INTO channels (name, url, logo, category_id, description, language, country, is_hd, playlist_id) VALUES
('Channel 1', 'https://example.com/channel1.m3u8', 'https://example.com/logo1.png', @entertainment_id, 'Entertainment channel', 'en', 'US', 1, @playlist_id),
('Channel 2', 'https://example.com/channel2.m3u8', 'https://example.com/logo2.png', @sports_id, 'Sports channel', 'en', 'US', 1, @playlist_id),
('Channel 3', 'https://example.com/channel3.m3u8', 'https://example.com/logo3.png', @news_id, 'News channel', 'en', 'US', 0, @playlist_id);

