0c06e159bb
Complete church management system with bulletin management, media processing, live streaming integration, and web interface. Includes authentication, email notifications, database migrations, and comprehensive test suite.
102 lines
3.5 KiB
PL/PgSQL
102 lines
3.5 KiB
PL/PgSQL
-- 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(); |