-- Buat database
CREATE DATABASE IF NOT EXISTS disdikcom_podcast;
USE disdikcom_podcast;

-- Tabel podcasts (untuk live podcast)
CREATE TABLE IF NOT EXISTS podcasts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    host_id INT NOT NULL,
    cover_image VARCHAR(500),
    audio_url VARCHAR(500),
    preview_url VARCHAR(500),
    duration INT DEFAULT 0,
    is_live BOOLEAN DEFAULT FALSE,
    listener_count INT DEFAULT 0,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (host_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_host_id (host_id),
    INDEX idx_status (status),
    INDEX idx_is_live (is_live),
    FULLTEXT INDEX idx_search (title, description)
);

-- Tabel podcast_likes (many-to-many)
CREATE TABLE IF NOT EXISTS podcast_likes (
    user_id INT NOT NULL,
    podcast_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, podcast_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (podcast_id) REFERENCES podcasts(id) ON DELETE CASCADE,
    INDEX idx_podcast_id (podcast_id)
);

-- Tabel podcast_comments
CREATE TABLE IF NOT EXISTS podcast_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    podcast_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    parent_id INT,
    is_approved BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (podcast_id) REFERENCES podcasts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES podcast_comments(id) ON DELETE CASCADE,
    INDEX idx_podcast_id (podcast_id),
    INDEX idx_user_id (user_id)
);

-- Tambah kolom ke podcasts (opsional)
ALTER TABLE podcasts ADD COLUMN recorded_episode_id INT NULL;

-- Insert sample podcasts
INSERT INTO podcasts (title, description, host_id, duration, is_live, status, listener_count) VALUES
('Podcast Pendidikan Jatim', 'Membahas perkembangan pendidikan di Jawa Timur', 1, 3600, TRUE, 'published', 1234),
('Inspirasi Guru Digital', 'Berbagi tips dan trik mengajar di era digital', 2, 2700, FALSE, 'published', 567),
('Merdeka Belajar', 'Diskusi tentang implementasi Kurikulum Merdeka', 1, 3000, TRUE, 'published', 892);