-- Create media library tables to replace Jellyfin
-- This will store all sermon metadata, file paths, and transcoding status

-- Main media items table
CREATE TABLE IF NOT EXISTS media_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    speaker VARCHAR(255),
    date DATE,
    description TEXT,
    scripture_reading TEXT,
    
    -- File information
    file_path VARCHAR(500) NOT NULL UNIQUE,
    file_size BIGINT,
    duration_seconds INTEGER,
    
    -- Media format info
    video_codec VARCHAR(50),
    audio_codec VARCHAR(50),
    resolution VARCHAR(20), -- e.g., "1920x1080"
    bitrate INTEGER,
    
    -- Thumbnail info
    thumbnail_path VARCHAR(500),
    thumbnail_generated_at TIMESTAMP WITH TIME ZONE,
    
    -- Metadata
    nfo_path VARCHAR(500),
    last_scanned TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Indexing
    CONSTRAINT unique_file_path UNIQUE (file_path)
);

-- Transcoded versions table
CREATE TABLE IF NOT EXISTS transcoded_media (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    media_item_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
    
    -- Format info
    target_codec VARCHAR(50) NOT NULL, -- e.g., "h264", "hevc"
    target_resolution VARCHAR(20), -- e.g., "1920x1080", "1280x720"
    target_bitrate INTEGER,
    
    -- File info
    file_path VARCHAR(500) NOT NULL UNIQUE,
    file_size BIGINT,
    
    -- Transcoding status
    status VARCHAR(20) DEFAULT 'pending', -- pending, processing, completed, failed
    transcoded_at TIMESTAMP WITH TIME ZONE,
    transcoding_started_at TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    
    -- Performance metrics
    transcoding_duration_seconds INTEGER,
    transcoding_method VARCHAR(50), -- e.g., "intel_vpl", "software"
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT unique_transcode_combo UNIQUE (media_item_id, target_codec, target_resolution, target_bitrate)
);

-- Media scanning status table
CREATE TABLE IF NOT EXISTS media_scan_status (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scan_path VARCHAR(500) NOT NULL,
    last_scan TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    files_found INTEGER DEFAULT 0,
    files_processed INTEGER DEFAULT 0,
    errors TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_media_items_date ON media_items(date DESC);
CREATE INDEX IF NOT EXISTS idx_media_items_speaker ON media_items(speaker);
CREATE INDEX IF NOT EXISTS idx_media_items_title ON media_items(title);
CREATE INDEX IF NOT EXISTS idx_media_items_last_scanned ON media_items(last_scanned);
CREATE INDEX IF NOT EXISTS idx_transcoded_media_item_id ON transcoded_media(media_item_id);
CREATE INDEX IF NOT EXISTS idx_transcoded_media_status ON transcoded_media(status);

-- Function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Triggers for updated_at
CREATE TRIGGER update_media_items_updated_at BEFORE UPDATE ON media_items 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_transcoded_media_updated_at BEFORE UPDATE ON transcoded_media 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();