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

-- Tabel users
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    role ENUM('admin', 'user', 'moderator') DEFAULT 'user',
    token_version INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role)
);

-- Tabel refresh_tokens (untuk menyimpan refresh token di database)
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token VARCHAR(500) NOT NULL,
    expires_at DATETIME NOT NULL,
    is_revoked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_token (token(255)),
    INDEX idx_expires_at (expires_at)
);

-- Tabel guest_sessions
CREATE TABLE IF NOT EXISTS guest_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(100) UNIQUE NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at DATETIME,
    INDEX idx_session_id (session_id),
    INDEX idx_expires_at (expires_at)
);

-- Tabel guest_activities (tracking aktivitas guest sebelum login)
CREATE TABLE IF NOT EXISTS guest_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(100) NOT NULL,
    activity_type ENUM('view', 'preview', 'search', 'click') NOT NULL,
    podcast_id INT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_activity_type (activity_type),
    FOREIGN KEY (session_id) REFERENCES guest_sessions(session_id) ON DELETE CASCADE
);

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

-- Tabel listener_history
CREATE TABLE IF NOT EXISTS listener_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    guest_session_id VARCHAR(100),
    podcast_id INT NOT NULL,
    listened_duration INT DEFAULT 0,
    completed BOOLEAN DEFAULT FALSE,
    listened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_guest_session (guest_session_id),
    INDEX idx_podcast_id (podcast_id),
    INDEX idx_listened_at (listened_at),
    FOREIGN KEY (podcast_id) REFERENCES podcasts(id) ON DELETE CASCADE
);

-- Tabel untuk rekaman sesi live
CREATE TABLE IF NOT EXISTS live_recordings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    podcast_id INT NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    started_by INT NOT NULL,  -- admin ID
    started_at DATETIME NOT NULL,
    stopped_at DATETIME,
    file_path VARCHAR(500),
    file_size INT DEFAULT 0,
    duration INT DEFAULT 0,  -- dalam detik
    status ENUM('recording', 'stopped', 'processing', 'completed', 'failed') DEFAULT 'recording',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (podcast_id) REFERENCES podcasts(id),
    FOREIGN KEY (started_by) REFERENCES users(id),
    INDEX idx_podcast_id (podcast_id),
    INDEX idx_session_id (session_id),
    INDEX idx_status (status)
);

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

-- Insert sample user (password: 'password123' - ganti dengan bcrypt hash real)
-- Untuk generate bcrypt hash: https://bcrypt-generator.com/
-- Contoh hash untuk 'password123': $2a$10$YourHashedPasswordHere
INSERT INTO users (email, password, name, role) VALUES
('admin@disdikjatim.com', '$2a$10$N9qo8uLOickgx2ZMRZoMy.Mr7u8qRrE5vFqPqVJjK6Z5xQ8tX9Yq', 'Admin Disdik', 'admin'),
('user@disdikjatim.com', '$2a$10$N9qo8uLOickgx2ZMRZoMy.Mr7u8qRrE5vFqPqVJjK6Z5xQ8tX9Yq', 'User Biasa', 'user');

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