<?php

require_once __DIR__ . '/db.php';

function sparrow_get_shop_products(bool $include_inactive = false): array
{
    $pdo = sparrow_db();

    $where = $include_inactive ? '' : 'WHERE is_active = 1';

    $stmt = $pdo->query("
        SELECT *
        FROM shop_products
        {$where}
        ORDER BY sort_order ASC, created_at DESC, id DESC
    ");

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

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

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

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

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

    return $product ?: null;
}

function sparrow_create_shop_product(array $data): int
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        INSERT INTO shop_products (
            product_name,
            slug,
            category,
            description,
            short_description,
            price,
            quantity_available,
            image_path,
            source_image_urls,
            is_active,
            is_featured,
            sort_order,
            source_product_id,
            meta_title,
            meta_description,
            meta_keywords,
            created_at
        ) VALUES (
            :product_name,
            :slug,
            :category,
            :description,
            :short_description,
            :price,
            :quantity_available,
            :image_path,
            :source_image_urls,
            :is_active,
            :is_featured,
            :sort_order,
            :source_product_id,
            :meta_title,
            :meta_description,
            :meta_keywords,
            NOW()
        )
    ");

    $stmt->execute([
        ':product_name' => trim($data['product_name'] ?? ''),
        ':slug' => trim($data['slug'] ?? ''),
        ':category' => trim($data['category'] ?? ''),
        ':description' => trim($data['description'] ?? ''),
        ':short_description' => trim($data['short_description'] ?? ''),
        ':price' => (float) ($data['price'] ?? 0),
        ':quantity_available' => (int) ($data['quantity_available'] ?? 1),
        ':image_path' => trim($data['image_path'] ?? '') ?: null,
        ':source_image_urls' => trim($data['source_image_urls'] ?? '') ?: null,
        ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ':is_featured' => !empty($data['is_featured']) ? 1 : 0,
        ':sort_order' => (int) ($data['sort_order'] ?? 0),
        ':source_product_id' => trim($data['source_product_id'] ?? ''),
        ':meta_title' => trim($data['meta_title'] ?? ''),
        ':meta_description' => trim($data['meta_description'] ?? ''),
        ':meta_keywords' => trim($data['meta_keywords'] ?? ''),
    ]);

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

function sparrow_update_shop_product(int $id, array $data): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE shop_products
        SET
            product_name = :product_name,
            slug = :slug,
            category = :category,
            description = :description,
            short_description = :short_description,
            price = :price,
            quantity_available = :quantity_available,
            image_path = :image_path,
            source_image_urls = :source_image_urls,
            is_active = :is_active,
            is_featured = :is_featured,
            sort_order = :sort_order,
            source_product_id = :source_product_id,
            meta_title = :meta_title,
            meta_description = :meta_description,
            meta_keywords = :meta_keywords,
            updated_at = NOW()
        WHERE id = :id
        LIMIT 1
    ");

    $stmt->execute([
        ':product_name' => trim($data['product_name'] ?? ''),
        ':slug' => trim($data['slug'] ?? ''),
        ':category' => trim($data['category'] ?? ''),
        ':description' => trim($data['description'] ?? ''),
        ':short_description' => trim($data['short_description'] ?? ''),
        ':price' => (float) ($data['price'] ?? 0),
        ':quantity_available' => (int) ($data['quantity_available'] ?? 1),
        ':image_path' => trim($data['image_path'] ?? '') ?: null,
        ':source_image_urls' => trim($data['source_image_urls'] ?? '') ?: null,
        ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ':is_featured' => !empty($data['is_featured']) ? 1 : 0,
        ':sort_order' => (int) ($data['sort_order'] ?? 0),
        ':source_product_id' => trim($data['source_product_id'] ?? ''),
        ':meta_title' => trim($data['meta_title'] ?? ''),
        ':meta_description' => trim($data['meta_description'] ?? ''),
        ':meta_keywords' => trim($data['meta_keywords'] ?? ''),
        ':id' => $id,
    ]);
}

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

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

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

function sparrow_set_shop_product_active(int $id, bool $is_active): void
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        UPDATE shop_products
        SET
            is_active = :is_active,
            updated_at = NOW()
        WHERE id = :id
        LIMIT 1
    ");

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

function sparrow_get_featured_shop_products(int $limit = 4, bool $random = false): array
{
    $pdo = sparrow_db();

    $order_by = $random
        ? 'RAND()'
        : 'sort_order ASC, created_at DESC, id DESC';

    $stmt = $pdo->prepare("
        SELECT *
        FROM shop_products
        WHERE is_active = 1
        AND is_featured = 1
        ORDER BY {$order_by}
        LIMIT :limit_count
    ");

    $stmt->bindValue(':limit_count', $limit, PDO::PARAM_INT);
    $stmt->execute();

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