comv3/player/includes/db.php

<?php
/**
 * db.php — SQLite database bootstrap & helpers
 */

define('DB_PATH', __DIR__ . '/../data/media.db');
define('MEDIA_DIR', __DIR__ . '/../media/');
define('MEDIA_URL', 'media/');

function get_db(): PDO {
    static $pdo = null;
    if ($pdo) return $pdo;

    $dir = dirname(DB_PATH);
    if (!is_dir($dir)) mkdir($dir, 0755, true);

    $pdo = new PDO('sqlite:' . DB_PATH);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->exec('PRAGMA journal_mode=WAL');
    $pdo->exec('PRAGMA foreign_keys=ON');

    // Schema
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS settings (
            key   TEXT PRIMARY KEY,
            value TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS videos (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            title       TEXT    NOT NULL DEFAULT 'Untitled',
            description TEXT    NOT NULL DEFAULT '',
            slug        TEXT    NOT NULL UNIQUE,
            thumbnail   TEXT    NOT NULL DEFAULT '',
            duration    INTEGER NOT NULL DEFAULT 0,
            sort_order  INTEGER NOT NULL DEFAULT 0,
            published   INTEGER NOT NULL DEFAULT 1,
            created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
            updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
        );

        CREATE TABLE IF NOT EXISTS video_sources (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            video_id   INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
            label      TEXT    NOT NULL DEFAULT '',
            file_path  TEXT    NOT NULL,
            mime_type  TEXT    NOT NULL DEFAULT 'video/mp4',
            quality    TEXT    NOT NULL DEFAULT '720p',
            sort_order INTEGER NOT NULL DEFAULT 0
        );

        CREATE INDEX IF NOT EXISTS idx_videos_published  ON videos(published);
        CREATE INDEX IF NOT EXISTS idx_videos_sort       ON videos(sort_order, id);
        CREATE INDEX IF NOT EXISTS idx_sources_video     ON video_sources(video_id);
    ");

    // Default settings
    $defaults = [
        'catalogue_per_page' => '10',
        'admin_password'     => password_hash('admin', PASSWORD_DEFAULT),
        'site_title'         => 'Ty Clifford',
        'site_sub'           => 'tyclifford.com / media',
    ];
    $ins = $pdo->prepare("INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)");
    foreach ($defaults as $k => $v) $ins->execute([$k, $v]);

    return $pdo;
}

function setting(string $key, string $fallback = ''): string {
    $row = get_db()->prepare("SELECT value FROM settings WHERE key=?");
    $row->execute([$key]);
    $r = $row->fetch();
    return $r ? $r['value'] : $fallback;
}

function set_setting(string $key, string $value): void {
    get_db()->prepare("INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)")
            ->execute([$key, $value]);
}

function make_slug(string $title, int $id = 0): string {
    $slug = strtolower(trim(preg_replace('/[^a-z0-9]+/i', '-', $title), '-'));
    if ($id) {
        $exists = get_db()->prepare("SELECT id FROM videos WHERE slug=? AND id!=?");
        $exists->execute([$slug, $id]);
    } else {
        $exists = get_db()->prepare("SELECT id FROM videos WHERE slug=?");
        $exists->execute([$slug]);
    }
    if ($exists->fetch()) $slug .= '-' . ($id ?: time());
    return $slug ?: 'video-' . time();
}

function mime_from_ext(string $path): string {
    $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
    return match($ext) {
        'mp4'  => 'video/mp4',
        'webm' => 'video/webm',
        'ogv'  => 'video/ogg',
        'mov'  => 'video/quicktime',
        'mkv'  => 'video/x-matroska',
        'avi'  => 'video/x-msvideo',
        default => 'video/mp4',
    };
}

function format_duration(int $seconds): string {
    if ($seconds <= 0) return '';
    $h = intdiv($seconds, 3600);
    $m = intdiv($seconds % 3600, 60);
    $s = $seconds % 60;
    if ($h) return sprintf('%d:%02d:%02d', $h, $m, $s);
    return sprintf('%d:%02d', $m, $s);
}

function h(string $s): string {
    return htmlspecialchars($s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function get_videos_paginated(int $page, int $per_page, bool $published_only = true): array {
    $db = get_db();
    $where = $published_only ? 'WHERE v.published=1' : '';
    $offset = ($page - 1) * $per_page;
    $total = $db->query("SELECT COUNT(*) FROM videos v $where")->fetchColumn();
    $rows  = $db->prepare("
        SELECT v.*, GROUP_CONCAT(vs.file_path,'||') AS source_paths
        FROM videos v
        LEFT JOIN video_sources vs ON vs.video_id = v.id
        $where
        GROUP BY v.id
        ORDER BY v.sort_order ASC, v.id DESC
        LIMIT ? OFFSET ?
    ");
    $rows->execute([$per_page, $offset]);
    return [
        'videos'       => $rows->fetchAll(),
        'total'        => (int)$total,
        'page'         => $page,
        'per_page'     => $per_page,
        'total_pages'  => (int)ceil($total / $per_page),
    ];
}

function get_video_by_slug(string $slug): ?array {
    $db  = get_db();
    $row = $db->prepare("SELECT * FROM videos WHERE slug=? AND published=1");
    $row->execute([$slug]);
    $v = $row->fetch();
    if (!$v) return null;
    $sources = $db->prepare("SELECT * FROM video_sources WHERE video_id=? ORDER BY sort_order ASC");
    $sources->execute([$v['id']]);
    $v['sources'] = $sources->fetchAll();
    return $v;
}

function get_video_by_id(int $id): ?array {
    $db  = get_db();
    $row = $db->prepare("SELECT * FROM videos WHERE id=?");
    $row->execute([$id]);
    $v = $row->fetch();
    if (!$v) return null;
    $sources = $db->prepare("SELECT * FROM video_sources WHERE video_id=? ORDER BY sort_order ASC");
    $sources->execute([$v['id']]);
    $v['sources'] = $sources->fetchAll();
    return $v;
}