-- Database schema for Podcast Hosting Server

CREATE DATABASE IF NOT EXISTS church_podcast;
USE church_podcast;

-- Episodes table
CREATE TABLE IF NOT EXISTS episodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    audio_file VARCHAR(255) NOT NULL,
    cover_image VARCHAR(255),
    release_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(50) PRIMARY KEY,
    `value` TEXT
);

-- Admins table
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'editor') DEFAULT 'editor',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Plays table (Analytics)
CREATE TABLE IF NOT EXISTS plays (
    id INT AUTO_INCREMENT PRIMARY KEY,
    episode_id INT NOT NULL,
    duration INT DEFAULT 0, -- seconds listened
    session_id VARCHAR(100) UNIQUE,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE
);

-- Default settings
INSERT IGNORE INTO settings (`key`, `value`) VALUES 
('site_title', 'Our Church Podcast'),
('banner_image', 'default-banner.jpg'),
('footer_copyright', 'Our Church'),
('footer_powered_by', 'Antigravity Podcast Server'),
('theme_primary_color', '#6366f1'),
('theme_bg_color', '#0f172a'),
('theme_text_color', '#f8fafc');

-- Subscriptions table (Push Notifications)
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint TEXT NOT NULL,
    p256dh VARCHAR(255) NOT NULL,
    auth VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Activity Log table
CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),
    action VARCHAR(255) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);