<?php

require_once __DIR__ . '/db.php';

function sparrow_gallery_categories(): array
{
    return [
        'Uncategorized',
        'Shirts',
        'Koozies',
        'Tumblers',
        'Hats',
        'Faith-Based',
        'Weddings',
        'Events',
        'Business Logos',
        'Gifts',
    ];
}

function sparrow_gallery_normalize_status(string $status): string
{
    $allowed = ['Draft', 'Published', 'Hidden'];

    return in_array($status, $allowed, true) ? $status : 'Published';
}

function sparrow_get_gallery_items(bool $include_hidden = true): array
{
    $pdo = sparrow_db();

    $where = $include_hidden
        ? ''
        : "WHERE gi.show_public = 1 AND gi.status = 'Published'";

    $stmt = $pdo->query("
        SELECT
            gi.*,
            (
                SELECT gii.image_path
                FROM gallery_item_images gii
                WHERE gii.gallery_item_id = gi.id
                ORDER BY gii.is_primary DESC, gii.sort_order ASC, gii.id ASC
                LIMIT 1
            ) AS primary_image,
            (
                SELECT COUNT(*)
                FROM gallery_item_images gii_count
                WHERE gii_count.gallery_item_id = gi.id
            ) AS image_count
        FROM gallery_items gi
        {$where}
        ORDER BY gi.featured DESC, gi.sort_order ASC, gi.created_at DESC, gi.id DESC
    ");

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_get_public_gallery_items(?string $category = null): array
{
    $pdo = sparrow_db();

    $sql = "
        SELECT
            gi.*,
            (
                SELECT gii.image_path
                FROM gallery_item_images gii
                WHERE gii.gallery_item_id = gi.id
                ORDER BY gii.is_primary DESC, gii.sort_order ASC, gii.id ASC
                LIMIT 1
            ) AS primary_image,
            (
                SELECT COUNT(*)
                FROM gallery_item_images gii_count
                WHERE gii_count.gallery_item_id = gi.id
            ) AS image_count
        FROM gallery_items gi
        WHERE gi.show_public = 1
        AND gi.status = 'Published'
    ";

    $params = [];

    if ($category !== null && trim($category) !== '') {
        $sql .= " AND gi.category = :category";
        $params[':category'] = trim($category);
    }

    $sql .= "
        ORDER BY gi.featured DESC, gi.sort_order ASC, gi.created_at DESC, gi.id DESC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_get_gallery_item(int $id): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM gallery_items
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $id,
    ]);

    $item = $stmt->fetch(PDO::FETCH_ASSOC);

    return $item ?: null;
}

function sparrow_get_gallery_item_images(int $gallery_item_id): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM gallery_item_images
        WHERE gallery_item_id = :gallery_item_id
        ORDER BY is_primary DESC, sort_order ASC, id ASC
    ");

    $stmt->execute([
        ':gallery_item_id' => $gallery_item_id,
    ]);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function sparrow_save_gallery_item(array $data, int $id = 0): int
{
    $pdo = sparrow_db();

    $title = trim($data['title'] ?? '');
    $category = trim($data['category'] ?? '');
    $description = trim($data['description'] ?? '');
    $status = sparrow_gallery_normalize_status(trim($data['status'] ?? 'Published'));
    $featured = !empty($data['featured']) ? 1 : 0;
    $show_public = !empty($data['show_public']) ? 1 : 0;
    $sort_order = (int) ($data['sort_order'] ?? 0);

    if ($title === '') {
        throw new InvalidArgumentException('Title is required.');
    }

    if ($category === '') {
        throw new InvalidArgumentException('Category is required.');
    }

    if ($id > 0) {
        $stmt = $pdo->prepare("
            UPDATE gallery_items
            SET
                title = :title,
                category = :category,
                description = :description,
                featured = :featured,
                show_public = :show_public,
                status = :status,
                sort_order = :sort_order
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            ':title' => $title,
            ':category' => $category,
            ':description' => $description,
            ':featured' => $featured,
            ':show_public' => $show_public,
            ':status' => $status,
            ':sort_order' => $sort_order,
            ':id' => $id,
        ]);

        return $id;
    }

    $stmt = $pdo->prepare("
        INSERT INTO gallery_items (
            title,
            category,
            description,
            featured,
            show_public,
            status,
            sort_order
        ) VALUES (
            :title,
            :category,
            :description,
            :featured,
            :show_public,
            :status,
            :sort_order
        )
    ");

    $stmt->execute([
        ':title' => $title,
        ':category' => $category,
        ':description' => $description,
        ':featured' => $featured,
        ':show_public' => $show_public,
        ':status' => $status,
        ':sort_order' => $sort_order,
    ]);

    return (int) $pdo->lastInsertId();
}

function sparrow_delete_gallery_item(int $id): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        DELETE FROM gallery_items
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $id,
    ]);
}

function sparrow_add_gallery_image(
    int $gallery_item_id,
    string $image_path,
    string $alt_text = '',
    bool $is_primary = false
): void {
    $pdo = sparrow_db();

    if ($is_primary) {
        $stmt = $pdo->prepare("
            UPDATE gallery_item_images
            SET is_primary = 0
            WHERE gallery_item_id = :gallery_item_id
        ");

        $stmt->execute([
            ':gallery_item_id' => $gallery_item_id,
        ]);
    }

    $stmt = $pdo->prepare("
        INSERT INTO gallery_item_images (
            gallery_item_id,
            image_path,
            alt_text,
            is_primary,
            sort_order
        ) VALUES (
            :gallery_item_id,
            :image_path,
            :alt_text,
            :is_primary,
            0
        )
    ");

    $stmt->execute([
        ':gallery_item_id' => $gallery_item_id,
        ':image_path' => $image_path,
        ':alt_text' => $alt_text,
        ':is_primary' => $is_primary ? 1 : 0,
    ]);
}

function sparrow_delete_gallery_image(int $image_id): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        DELETE FROM gallery_item_images
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $image_id,
    ]);
}

function sparrow_set_gallery_primary_image(int $image_id): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT gallery_item_id
        FROM gallery_item_images
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $image_id,
    ]);

    $gallery_item_id = (int) $stmt->fetchColumn();

    if ($gallery_item_id <= 0) {
        return;
    }

    $stmt = $pdo->prepare("
        UPDATE gallery_item_images
        SET is_primary = 0
        WHERE gallery_item_id = :gallery_item_id
    ");

    $stmt->execute([
        ':gallery_item_id' => $gallery_item_id,
    ]);

    $stmt = $pdo->prepare("
        UPDATE gallery_item_images
        SET is_primary = 1
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $image_id,
    ]);
}

function sparrow_get_gallery_image(int $image_id): ?array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM gallery_item_images
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':id' => $image_id,
    ]);

    $image = $stmt->fetch(PDO::FETCH_ASSOC);

    return $image ?: null;
}

function sparrow_update_gallery_image_details(int $image_id, string $alt_text, int $sort_order): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE gallery_item_images
        SET
            alt_text = :alt_text,
            sort_order = :sort_order
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':alt_text' => trim($alt_text),
        ':sort_order' => $sort_order,
        ':id' => $image_id,
    ]);
}

function sparrow_replace_gallery_image_path(int $image_id, string $image_path): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE gallery_item_images
        SET image_path = :image_path
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':image_path' => $image_path,
        ':id' => $image_id,
    ]);
}
