<?php

require_once __DIR__ . '/db.php';

function sparrow_gallery_categories(): array
{
    return [
        'Shirts',
        'Koozies',
        'Tumblers',
		'Pens',
        '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,
    ]);
}

function sparrow_merge_gallery_items(array $selected_ids, int $keep_item_id): array
{
    $pdo = sparrow_db();

    $selected_ids = array_values(array_unique(array_filter(
        array_map('intval', $selected_ids),
        static fn (int $id): bool => $id > 0
    )));

    if (count($selected_ids) < 2) {
        throw new InvalidArgumentException(
            'Select at least two gallery items to merge.'
        );
    }

    if (!in_array($keep_item_id, $selected_ids, true)) {
        throw new InvalidArgumentException(
            'Choose one of the selected items to keep.'
        );
    }

    $placeholders = implode(
        ', ',
        array_fill(0, count($selected_ids), '?')
    );

    $pdo->beginTransaction();

    try {
        /*
         * Lock and verify every selected gallery item.
         */
        $stmt = $pdo->prepare("
            SELECT id, title
            FROM gallery_items
            WHERE id IN ({$placeholders})
            FOR UPDATE
        ");

        $stmt->execute($selected_ids);
        $found_items = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (count($found_items) !== count($selected_ids)) {
            throw new RuntimeException(
                'One or more selected gallery items could not be found.'
            );
        }

        $keep_title = '';

        foreach ($found_items as $found_item) {
            if ((int) $found_item['id'] === $keep_item_id) {
                $keep_title = $found_item['title'];
                break;
            }
        }

        $remove_ids = array_values(array_filter(
            $selected_ids,
            static fn (int $id): bool => $id !== $keep_item_id
        ));

        $remove_placeholders = implode(
            ', ',
            array_fill(0, count($remove_ids), '?')
        );

        /*
         * Remember the image paths already attached to the kept item.
         * This prevents an exact same file path from being added twice.
         */
        $stmt = $pdo->prepare("
            SELECT image_path
            FROM gallery_item_images
            WHERE gallery_item_id = ?
        ");

        $stmt->execute([$keep_item_id]);

        $existing_paths = [];

        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $image_path) {
            $existing_paths[$image_path] = true;
        }

        /*
         * Continue sort order after the kept item's current images.
         */
        $stmt = $pdo->prepare("
            SELECT COALESCE(MAX(sort_order), -1)
            FROM gallery_item_images
            WHERE gallery_item_id = ?
        ");

        $stmt->execute([$keep_item_id]);
        $next_sort_order = (int) $stmt->fetchColumn() + 1;

        /*
         * Get all images belonging to the items being removed.
         */
        $stmt = $pdo->prepare("
            SELECT id, image_path
            FROM gallery_item_images
            WHERE gallery_item_id IN ({$remove_placeholders})
            ORDER BY gallery_item_id ASC, is_primary DESC, sort_order ASC, id ASC
        ");

        $stmt->execute($remove_ids);
        $images_to_move = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $move_stmt = $pdo->prepare("
            UPDATE gallery_item_images
            SET
                gallery_item_id = :keep_item_id,
                is_primary = 0,
                sort_order = :sort_order
            WHERE id = :image_id
            LIMIT 1
        ");

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

        $images_moved = 0;
        $duplicates_skipped = 0;

        foreach ($images_to_move as $image) {
            $image_id = (int) $image['id'];
            $image_path = (string) $image['image_path'];

            if (isset($existing_paths[$image_path])) {
                /*
                 * Only remove the duplicate database record.
                 * The actual file remains available to the kept item.
                 */
                $delete_duplicate_stmt->execute([
                    ':image_id' => $image_id,
                ]);

                $duplicates_skipped++;
                continue;
            }

            $move_stmt->execute([
                ':keep_item_id' => $keep_item_id,
                ':sort_order' => $next_sort_order,
                ':image_id' => $image_id,
            ]);

            $existing_paths[$image_path] = true;
            $next_sort_order++;
            $images_moved++;
        }

        /*
         * Remove the duplicate gallery items after their images are safe.
         */
        $stmt = $pdo->prepare("
            DELETE FROM gallery_items
            WHERE id IN ({$remove_placeholders})
        ");

        $stmt->execute($remove_ids);

        /*
         * Make sure the kept item has one primary image.
         */
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM gallery_item_images
            WHERE gallery_item_id = ?
            AND is_primary = 1
        ");

        $stmt->execute([$keep_item_id]);
        $has_primary = (int) $stmt->fetchColumn() > 0;

        if (!$has_primary) {
            $stmt = $pdo->prepare("
                SELECT id
                FROM gallery_item_images
                WHERE gallery_item_id = ?
                ORDER BY sort_order ASC, id ASC
                LIMIT 1
            ");

            $stmt->execute([$keep_item_id]);
            $first_image_id = (int) $stmt->fetchColumn();

            if ($first_image_id > 0) {
                $stmt = $pdo->prepare("
                    UPDATE gallery_item_images
                    SET is_primary = 1
                    WHERE id = ?
                    LIMIT 1
                ");

                $stmt->execute([$first_image_id]);
            }
        }

        $pdo->commit();

        return [
            'keep_item_id' => $keep_item_id,
            'keep_title' => $keep_title,
            'items_merged' => count($remove_ids),
            'images_moved' => $images_moved,
            'duplicates_skipped' => $duplicates_skipped,
        ];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $e;
    }
}

function sparrow_bulk_update_gallery_items(
    array $selected_ids,
    string $category = '',
    string $status = '',
    ?int $show_public = null
): int {
    $pdo = sparrow_db();

    $selected_ids = array_values(array_unique(array_filter(
        array_map('intval', $selected_ids),
        static fn (int $id): bool => $id > 0
    )));

    if (!$selected_ids) {
        throw new InvalidArgumentException(
            'Select at least one gallery item.'
        );
    }

    $category = trim($category);
    $status = trim($status);

    $updates = [];
    $params = [];

    if ($category !== '') {
        if (!in_array($category, sparrow_gallery_categories(), true)) {
            throw new InvalidArgumentException(
                'Choose a valid gallery category.'
            );
        }

        $updates[] = 'category = ?';
        $params[] = $category;
    }

    if ($status !== '') {
        $allowed_statuses = [
            'Draft',
            'Published',
            'Hidden',
        ];

        if (!in_array($status, $allowed_statuses, true)) {
            throw new InvalidArgumentException(
                'Choose a valid gallery status.'
            );
        }

        $updates[] = 'status = ?';
        $params[] = $status;
    }

    if ($show_public !== null) {
        if (!in_array($show_public, [0, 1], true)) {
            throw new InvalidArgumentException(
                'Choose a valid website visibility.'
            );
        }

        $updates[] = 'show_public = ?';
        $params[] = $show_public;
    }

    if (!$updates) {
        throw new InvalidArgumentException(
            'Choose at least one change to apply.'
        );
    }

    $placeholders = implode(
        ', ',
        array_fill(0, count($selected_ids), '?')
    );

    $params = array_merge($params, $selected_ids);

    $stmt = $pdo->prepare("
        UPDATE gallery_items
        SET " . implode(', ', $updates) . "
        WHERE id IN ({$placeholders})
    ");

    $stmt->execute($params);

    return count($selected_ids);
}