KCI-CAMPAIGN-TRACKER/app/Repositories/CampaignRepository.php

<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Models\Campaign;
use Core\Repository;

class CampaignRepository extends Repository
{
    protected string $table      = 'campaign';
    protected string $primaryKey = 'id';

    public function count(): int
    {
        $row = $this->database->first('SELECT COUNT(*) AS total FROM campaign');
        return (int) ($row['total'] ?? 0);
    }

    /** @return list<array<string, mixed>> */
    public function recentWithType(int $limit = 5): array
    {
        return $this->database->query(
            "SELECT TOP ({$limit}) c.id, c.created_at, ct.name AS campaign_type_name
             FROM campaign c
             INNER JOIN campaign_type ct ON c.campaign_type_id = ct.id
             ORDER BY c.id DESC"
        );
    }

    /** @return list<array<string, mixed>> */
    public function countByType(): array
    {
        return $this->database->query(
            'SELECT ct.name AS campaign_type_name, COUNT(c.id) AS campaign_count
             FROM campaign_type ct
             LEFT JOIN campaign c ON c.campaign_type_id = ct.id
             GROUP BY ct.id, ct.name
             ORDER BY campaign_count DESC, ct.name ASC'
        );
    }

    /**
     * All campaigns joined with their campaign type name, ordered by id desc.
     *
     * @return list<array<string, mixed>>
     */
    public function allWithType(): array
    {
        return $this->database->query(
            'SELECT c.id, c.campaign_type_id, c.attribute_values,
                    c.created_at, c.updated_at,
                    ct.name AS campaign_type_name,
                    ct.attributes AS campaign_type_attributes
             FROM campaign c
             INNER JOIN campaign_type ct ON c.campaign_type_id = ct.id
             ORDER BY c.id DESC'
        );
    }

    /**
     * Single campaign joined with its campaign type name and attributes.
     */
    public function findWithType(int $id): ?array
    {
        return $this->database->first(
            'SELECT c.id, c.campaign_type_id, c.attribute_values,
                    c.created_at, c.updated_at,
                    ct.name AS campaign_type_name,
                    ct.attributes AS campaign_type_attributes
             FROM campaign c
             INNER JOIN campaign_type ct ON c.campaign_type_id = ct.id
             WHERE c.id = :id',
            ['id' => $id]
        );
    }

    /**
     * Return the most recently inserted campaign for a given type.
     * Used after an INSERT to retrieve the generated id for audit logging.
     */
    public function findLatestByType(int $typeId): ?array
    {
        return $this->database->first(
            'SELECT TOP (1) * FROM campaign
             WHERE campaign_type_id = :type_id
             ORDER BY id DESC',
            ['type_id' => $typeId]
        );
    }

    public function create(Campaign $campaign): bool
    {
        return $this->database->execute(
            'INSERT INTO campaign (campaign_type_id, attribute_values)
             VALUES (:campaign_type_id, :attribute_values)',
            [
                'campaign_type_id' => $campaign->campaignTypeId,
                'attribute_values' => json_encode($campaign->attributeValues, JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE),
            ]
        );
    }

    public function update(Campaign $campaign): bool
    {
        return $this->database->execute(
            'UPDATE campaign
             SET campaign_type_id = :campaign_type_id,
                 attribute_values = :attribute_values,
                 updated_at       = CURRENT_TIMESTAMP
             WHERE id = :id',
            [
                'campaign_type_id' => $campaign->campaignTypeId,
                'attribute_values' => json_encode($campaign->attributeValues, JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE),
                'id'               => $campaign->id,
            ]
        );
    }
}