<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Database;

final class CampaignRecipient extends BaseModel
{
    protected static string $table = 'campaign_recipients';
    protected static bool $softDelete = false;

    public static function byToken(string $token): ?array
    {
        if (!preg_match('/^[a-f0-9]{64}$/', $token)) {
            return null;
        }
        return Database::instance()->fetch(
            'SELECT * FROM campaign_recipients WHERE token = :t LIMIT 1',
            ['t' => $token]
        );
    }

    public static function forAccount(int $accountId): array
    {
        return static::db()->fetchAll(
            "SELECT r.*, c.name AS campaign_name, c.subject
             FROM campaign_recipients r
             INNER JOIN campaigns c ON c.id = r.campaign_id
             WHERE r.tenant_id = :tid AND r.account_id = :aid
             ORDER BY r.sent_at DESC, r.id DESC
             LIMIT 50",
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }

    public static function forContact(int $contactId): array
    {
        return static::db()->fetchAll(
            "SELECT r.*, c.name AS campaign_name, c.subject
             FROM campaign_recipients r
             INNER JOIN campaigns c ON c.id = r.campaign_id
             WHERE r.tenant_id = :tid AND r.contact_id = :cid
             ORDER BY r.sent_at DESC, r.id DESC
             LIMIT 50",
            ['tid' => static::tenantId(), 'cid' => $contactId]
        );
    }

    public static function bounces(int $page, int $perPage): array
    {
        $where = "r.tenant_id = :tid AND r.status = 'bounced'";
        $params = ['tid' => static::tenantId()];
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM campaign_recipients r WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT r.*, c.name AS campaign_name
             FROM campaign_recipients r
             INNER JOIN campaigns c ON c.id = r.campaign_id
             WHERE $where ORDER BY r.bounced_at DESC LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function markBounce(int $id): void
    {
        $row = static::find($id);
        if (!$row) {
            return;
        }
        static::update($id, [
            'status' => 'bounced',
            'bounced_at' => date('Y-m-d H:i:s'),
        ]);
        static::db()->query(
            "UPDATE contacts SET email_status = 'bounced' WHERE id = :id AND tenant_id = :tid",
            ['id' => (int) $row['contact_id'], 'tid' => static::tenantId()]
        );
    }
}
