includes/queries.php - master - TorX/rasp

includes/queries.php
277 строк8 KB
tormovies
Add a posted flag for upcoming releases in admin and public lists.
07 авг 2026, 15:02
07 авг 2026, 15:023708742
<?php
declare(strict_types=1);

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/helpers.php';

function list_artists(): array
{
    return db()->query('SELECT * FROM artists ORDER BY name ASC')->fetchAll();
}

function create_artist(string $name): int
{
    $name = trim($name);
    if ($name === '') {
        throw new InvalidArgumentException('Укажите имя артиста');
    }
    $stmt = db()->prepare('INSERT INTO artists (name, created_at) VALUES (?, ?)');
    $stmt->execute([$name, date('c')]);
    return (int) db()->lastInsertId();
}

function delete_artist(int $id): void
{
    $stmt = db()->prepare('DELETE FROM artists WHERE id = ?');
    $stmt->execute([$id]);
}

function find_artist_by_name(string $name): ?array
{
    $stmt = db()->prepare('SELECT * FROM artists WHERE name = ? LIMIT 1');
    $stmt->execute([trim($name)]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function find_or_create_artist(string $name): array
{
    $existing = find_artist_by_name($name);
    if ($existing) {
        return $existing;
    }
    $id = create_artist($name);
    $stmt = db()->prepare('SELECT * FROM artists WHERE id = ?');
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function release_select_sql(): string
{
    return 'SELECT r.*, a.name AS artist_name
            FROM releases r
            INNER JOIN artists a ON a.id = r.artist_id';
}

function list_releases(?string $from = null, ?string $to = null, string $pitch = 'all'): array
{
    $sql = release_select_sql() . ' WHERE 1=1';
    $params = [];
    if ($from) {
        $sql .= ' AND r.release_date >= ?';
        $params[] = $from;
    }
    if ($to) {
        $sql .= ' AND r.release_date <= ?';
        $params[] = $to;
    }
    if ($pitch === 'with') {
        $sql .= ' AND r.has_pitch = 1';
    } elseif ($pitch === 'without') {
        $sql .= ' AND r.has_pitch = 0';
    }
    $sql .= ' ORDER BY r.release_date ASC, a.name ASC, r.title ASC';
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function list_upcoming(string $pitch = 'all'): array
{
    return list_releases(today_iso(), null, $pitch);
}

function get_month_releases(int $year, int $month): array
{
    $from = sprintf('%04d-%02d-01', $year, $month);
    $last = (int) date('t', mktime(0, 0, 0, $month, 1, $year));
    $to = sprintf('%04d-%02d-%02d', $year, $month, $last);
    return list_releases($from, $to);
}

function get_release(int $id): ?array
{
    $stmt = db()->prepare(release_select_sql() . ' WHERE r.id = ? LIMIT 1');
    $stmt->execute([$id]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function create_release(array $input): int
{
    $payload = normalize_release_input($input);
    $now = date('c');
    $stmt = db()->prepare('
        INSERT INTO releases
          (artist_id, title, release_date, genre, lyric_author, label, type, has_pitch, is_posted, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ');
    $stmt->execute([
        $payload['artist_id'],
        $payload['title'],
        $payload['release_date'],
        $payload['genre'],
        $payload['lyric_author'],
        $payload['label'],
        $payload['type'],
        $payload['has_pitch'] ? 1 : 0,
        $payload['is_posted'] ? 1 : 0,
        $now,
        $now,
    ]);
    return (int) db()->lastInsertId();
}

function update_release(int $id, array $input): void
{
    $payload = normalize_release_input($input);
    $stmt = db()->prepare('
        UPDATE releases SET
          artist_id = ?, title = ?, release_date = ?, genre = ?, lyric_author = ?,
          label = ?, type = ?, has_pitch = ?, is_posted = ?, updated_at = ?
        WHERE id = ?
    ');
    $stmt->execute([
        $payload['artist_id'],
        $payload['title'],
        $payload['release_date'],
        $payload['genre'],
        $payload['lyric_author'],
        $payload['label'],
        $payload['type'],
        $payload['has_pitch'] ? 1 : 0,
        $payload['is_posted'] ? 1 : 0,
        date('c'),
        $id,
    ]);
}

function delete_release(int $id): void
{
    $stmt = db()->prepare('DELETE FROM releases WHERE id = ?');
    $stmt->execute([$id]);
}

function set_release_pitch(int $id, bool $hasPitch): void
{
    $stmt = db()->prepare('UPDATE releases SET has_pitch = ?, updated_at = ? WHERE id = ?');
    $stmt->execute([$hasPitch ? 1 : 0, date('c'), $id]);
}

function set_release_posted(int $id, bool $isPosted): void
{
    $stmt = db()->prepare('UPDATE releases SET is_posted = ?, updated_at = ? WHERE id = ?');
    $stmt->execute([$isPosted ? 1 : 0, date('c'), $id]);
}

function normalize_release_input(array $input): array
{
    $title = trim((string) ($input['title'] ?? ''));
    $genre = trim((string) ($input['genre'] ?? ''));
    $lyricAuthor = trim((string) ($input['lyric_author'] ?? ''));
    $label = trim((string) ($input['label'] ?? ''));
    $type = (string) ($input['type'] ?? 'single');
    $artistId = (int) ($input['artist_id'] ?? 0);
    $releaseDate = trim((string) ($input['release_date'] ?? ''));

    if ($artistId <= 0) {
        throw new InvalidArgumentException('Выберите артиста');
    }
    if ($title === '') {
        throw new InvalidArgumentException('Укажите название');
    }
    if ($releaseDate === '') {
        throw new InvalidArgumentException('Укажите дату');
    }
    if ($genre === '') {
        throw new InvalidArgumentException('Укажите жанр');
    }
    if ($lyricAuthor === '') {
        throw new InvalidArgumentException('Укажите автора текста');
    }
    if (!in_array($type, RELEASE_TYPES, true)) {
        $type = 'single';
    }

    return [
        'artist_id' => $artistId,
        'title' => $title,
        'release_date' => $releaseDate,
        'genre' => $genre,
        'lyric_author' => $lyricAuthor,
        'label' => $label,
        'type' => $type,
        'has_pitch' => !empty($input['has_pitch']),
        'is_posted' => !empty($input['is_posted']),
    ];
}

function distinct_values(string $column): array
{
    $allowed = ['genre', 'lyric_author', 'label'];
    if (!in_array($column, $allowed, true)) {
        return [];
    }
    $stmt = db()->query("SELECT DISTINCT {$column} AS value FROM releases WHERE {$column} <> '' ORDER BY {$column} ASC");
    return array_column($stmt->fetchAll(), 'value');
}

function find_duplicate_release(int $artistId, string $title, string $releaseDate): ?array
{
    $stmt = db()->prepare('
        SELECT id FROM releases
        WHERE artist_id = ? AND title = ? AND release_date = ?
        LIMIT 1
    ');
    $stmt->execute([$artistId, $title, $releaseDate]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function import_schedule_text(string $text, bool $defaultHasPitch): array
{
    $result = ['created' => 0, 'skipped' => 0, 'artists_created' => 0, 'errors' => []];
    $lines = preg_split('/\r\n|\r|\n/', $text) ?: [];

    foreach ($lines as $i => $raw) {
        if (trim($raw) === '') {
            continue;
        }
        $parsed = parse_import_line($raw);
        if (is_string($parsed)) {
            $result['errors'][] = ['line' => $i + 1, 'text' => trim($raw), 'error' => $parsed];
            continue;
        }
        try {
            $before = find_artist_by_name($parsed['artist_name']);
            $artist = find_or_create_artist($parsed['artist_name']);
            if (!$before) {
                $result['artists_created']++;
            }
            if (find_duplicate_release((int) $artist['id'], $parsed['title'], $parsed['release_date'])) {
                $result['skipped']++;
                continue;
            }
            create_release([
                'artist_id' => (int) $artist['id'],
                'title' => $parsed['title'],
                'release_date' => $parsed['release_date'],
                'genre' => $parsed['genre'],
                'lyric_author' => $parsed['lyric_author'],
                'label' => $parsed['label'],
                'type' => $parsed['type'] ?? 'single',
                'has_pitch' => $parsed['has_pitch'] ?? $defaultHasPitch,
            ]);
            $result['created']++;
        } catch (Throwable $e) {
            $result['errors'][] = ['line' => $i + 1, 'text' => trim($raw), 'error' => $e->getMessage()];
        }
    }

    return $result;
}

function export_text(array $releases): string
{
    return implode("\n", array_map('format_export_line', $releases));
}