<?php

require_once __DIR__ . '/db.php';

function sparrow_marketing_statuses(): array
{
    return ['Draft', 'Ready', 'Scheduled', 'Posted'];
}

function sparrow_marketing_normalize_status(string $status): string
{
    $allowed = sparrow_marketing_statuses();

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

function sparrow_get_marketing_posts(): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT *
        FROM marketing_posts
        ORDER BY created_at DESC, id DESC
    ");

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

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

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

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

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

    return $post ?: null;
}

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

    $title = trim($data['title'] ?? '');
    $image_path = trim($data['image_path'] ?? '');
    $message = trim($data['message'] ?? '');
    $short_prayer = trim($data['short_prayer'] ?? '');
    $promotion = trim($data['promotion'] ?? '');
    $hashtags = trim($data['hashtags'] ?? '');
    $platform_facebook = !empty($data['platform_facebook']) ? 1 : 0;
$platform_instagram = !empty($data['platform_instagram']) ? 1 : 0;
$show_public_blog = !empty($data['show_public_blog']) ? 1 : 0;
$status = sparrow_marketing_normalize_status(trim($data['status'] ?? 'Draft'));
    $scheduled_at = trim($data['scheduled_at'] ?? '');
    $posted_at = trim($data['posted_at'] ?? '');

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

    $scheduled_at = $scheduled_at !== '' ? $scheduled_at : null;
    $posted_at = $posted_at !== '' ? $posted_at : null;

    if ($id > 0) {
        $stmt = $pdo->prepare("
            UPDATE marketing_posts
            SET
                title = :title,
                image_path = :image_path,
                message = :message,
                short_prayer = :short_prayer,
                promotion = :promotion,
                hashtags = :hashtags,
                platform_facebook = :platform_facebook,
                platform_instagram = :platform_instagram,
				show_public_blog = :show_public_blog,
                status = :status,
                scheduled_at = :scheduled_at,
                posted_at = :posted_at
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            ':title' => $title,
            ':image_path' => $image_path,
            ':message' => $message,
            ':short_prayer' => $short_prayer,
            ':promotion' => $promotion,
            ':hashtags' => $hashtags,
            ':platform_facebook' => $platform_facebook,
            ':platform_instagram' => $platform_instagram,
			':show_public_blog' => $show_public_blog,
            ':status' => $status,
            ':scheduled_at' => $scheduled_at,
            ':posted_at' => $posted_at,
            ':id' => $id,
        ]);

        return $id;
    }

    $stmt = $pdo->prepare("
        INSERT INTO marketing_posts (
            title,
            image_path,
            message,
            short_prayer,
            promotion,
            hashtags,
            platform_facebook,
            platform_instagram,
			show_public_blog,
            status,
            scheduled_at,
            posted_at
        ) VALUES (
            :title,
            :image_path,
            :message,
            :short_prayer,
            :promotion,
            :hashtags,
            :platform_facebook,
            :platform_instagram,
			:show_public_blog,
            :status,
            :scheduled_at,
            :posted_at
        )
    ");

    $stmt->execute([
        ':title' => $title,
        ':image_path' => $image_path,
        ':message' => $message,
        ':short_prayer' => $short_prayer,
        ':promotion' => $promotion,
        ':hashtags' => $hashtags,
        ':platform_facebook' => $platform_facebook,
        ':platform_instagram' => $platform_instagram,
		':show_public_blog' => $show_public_blog,
        ':status' => $status,
        ':scheduled_at' => $scheduled_at,
        ':posted_at' => $posted_at,
    ]);

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

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

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

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

function sparrow_compose_marketing_caption(array $post): string
{
    $parts = [];

    if (!empty($post['title'])) {
        $parts[] = trim($post['title']);
    }

    if (!empty($post['message'])) {
        $parts[] = trim($post['message']);
    }

    if (!empty($post['promotion'])) {
        $parts[] = trim($post['promotion']);
    }

    if (!empty($post['short_prayer'])) {
        $parts[] = 'Prayer: ' . trim($post['short_prayer']);
    }

    if (!empty($post['hashtags'])) {
        $parts[] = trim($post['hashtags']);
    }

    return trim(implode("\n\n", $parts));
}

function sparrow_create_marketing_post_from_gallery_item(array $gallery_item, string $image_path = ''): int
{
    $title = trim($gallery_item['title'] ?? '');
    $category = trim($gallery_item['category'] ?? '');
    $description = trim($gallery_item['description'] ?? '');

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

    $message_parts = [];

    $message_parts[] = 'Take a look at this custom ' . strtolower($category ?: 'piece') . ' from Sparrow & Seed Creations.';

    if ($description !== '') {
        $message_parts[] = $description;
    }

    $hashtags = '#sparrowandseedcreations #custommade #handmadegifts';

    if ($category !== '') {
        $clean_category = strtolower(preg_replace('/[^a-zA-Z0-9]+/', '', $category));

        if ($clean_category !== '') {
            $hashtags .= ' #' . $clean_category;
        }
    }

    return sparrow_save_marketing_post([
        'title' => $title,
        'image_path' => $image_path,
        'message' => implode("\n\n", $message_parts),
        'short_prayer' => '',
        'promotion' => '',
        'hashtags' => $hashtags,
        'platform_facebook' => 1,
        'platform_instagram' => 1,
		'show_public_blog' => 0,
        'status' => 'Draft',
        'scheduled_at' => '',
        'posted_at' => '',
    ]);
}

function sparrow_count_public_blog_posts(): int
{
    $pdo = sparrow_db();

    $stmt = $pdo->query("
        SELECT COUNT(*)
        FROM marketing_posts
        WHERE show_public_blog = 1
    ");

    return (int) $stmt->fetchColumn();
}

function sparrow_get_public_blog_posts(int $limit = 6, int $offset = 0): array
{
    $pdo = sparrow_db();

    $stmt = $pdo->prepare("
        SELECT *
        FROM marketing_posts
        WHERE show_public_blog = 1
        ORDER BY
            COALESCE(posted_at, created_at) DESC,
            id DESC
        LIMIT :limit OFFSET :offset
    ");

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

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

function sparrow_blog_post_display_date(array $post): string
{
    $date = $post['posted_at'] ?: $post['created_at'];

    return date('F j, Y', strtotime($date));
}