<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;
use App\Helpers\Paginator;
use PDO;

final class CampaignModel extends BaseModel
{
    protected string $table = 'campaigns';

    protected array $fillable = [
        'business_line_id', 'email_template_id', 'parent_campaign_id', 'name', 'subject', 'body_html',
        'audience_segment', 'audience_filter', 'audience_segment_id', 'status', 'scheduled_at',
        'is_recurring_parent', 'recurrence_rule',
    ];

    protected bool $softDeletes = true;
    protected bool $auditStamps = true;

    public function findWithRelations(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT c.*, bl.name AS business_line_name, seg.name AS segment_name,
                    CONCAT(u.first_name, " ", u.last_name) AS created_by_name
             FROM campaigns c
             LEFT JOIN business_lines bl ON bl.id = c.business_line_id
             LEFT JOIN audience_segments seg ON seg.id = c.audience_segment_id
             LEFT JOIN users u ON u.id = c.created_by
             WHERE c.id = :id AND c.deleted_at IS NULL'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();

        return $row === false ? null : $row;
    }

    public function paginatedList(array $filters, Paginator $paginator): array
    {
        [$where, $bindings] = $this->buildFilterClause($filters);

        $countStmt = $this->db->prepare("SELECT COUNT(*) FROM campaigns c {$where}");
        $countStmt->execute($bindings);
        $paginator->setTotal((int) $countStmt->fetchColumn());

        $sql = "SELECT c.*, bl.name AS business_line_name
                FROM campaigns c
                LEFT JOIN business_lines bl ON bl.id = c.business_line_id
                {$where}
                ORDER BY COALESCE(c.scheduled_at, c.created_at) DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($bindings as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue('limit', $paginator->perPage(), PDO::PARAM_INT);
        $stmt->bindValue('offset', $paginator->offset(), PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function addRecipients(int $campaignId, array $recipients): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO campaign_recipients (campaign_id, customer_id, lead_id, email_address, status, unsubscribe_token)
             VALUES (:campaign_id, :customer_id, :lead_id, :email, "pending", :token)'
        );

        $count = 0;
        foreach ($recipients as $r) {
            $stmt->execute([
                'campaign_id' => $campaignId,
                'customer_id' => $r['customer_id'] ?? null,
                'lead_id' => $r['lead_id'] ?? null,
                'email' => $r['email'],
                'token' => bin2hex(random_bytes(32)),
            ]);
            $count++;
        }

        $this->update($campaignId, ['recipients_count' => $count]);

        return $count;
    }

    public function recipients(int $campaignId): array
    {
        $stmt = $this->db->prepare('SELECT * FROM campaign_recipients WHERE campaign_id = :id ORDER BY id');
        $stmt->execute(['id' => $campaignId]);

        return $stmt->fetchAll();
    }

    /** Recomputes recipients_count/opens_count/clicks_count/unsubscribes_count from the live recipient rows — call after any tracking event. */
    public function refreshStats(int $campaignId): void
    {
        $stmt = $this->db->prepare(
            "SELECT
                COUNT(*) AS total,
                SUM(status IN ('sent','delivered','opened','clicked')) AS sent,
                SUM(opened_at IS NOT NULL) AS opens,
                SUM(clicked_at IS NOT NULL) AS clicks,
                SUM(bounced_at IS NOT NULL) AS bounces,
                SUM(replied_at IS NOT NULL) AS replies,
                SUM(status = 'unsubscribed') AS unsubscribes
             FROM campaign_recipients WHERE campaign_id = :id"
        );
        $stmt->execute(['id' => $campaignId]);
        $stats = $stmt->fetch();

        $this->db->prepare(
            'UPDATE campaigns SET recipients_count = :total, opens_count = :opens, clicks_count = :clicks, unsubscribes_count = :unsub
             WHERE id = :id'
        )->execute([
            'total' => (int) $stats['total'],
            'opens' => (int) $stats['opens'],
            'clicks' => (int) $stats['clicks'],
            'unsub' => (int) $stats['unsubscribes'],
            'id' => $campaignId,
        ]);
    }

    /** Dashboard widget data: recent campaign performance, all business lines. */
    public function dashboardStats(): array
    {
        $totals = $this->db->query(
            "SELECT COUNT(*) AS total_campaigns,
                    SUM(status = 'sent') AS sent_campaigns,
                    SUM(recipients_count) AS total_recipients,
                    SUM(opens_count) AS total_opens,
                    SUM(clicks_count) AS total_clicks
             FROM campaigns WHERE deleted_at IS NULL"
        )->fetch();

        $recent = $this->db->query(
            "SELECT id, name, status, recipients_count, opens_count, clicks_count, sent_at
             FROM campaigns WHERE deleted_at IS NULL AND status = 'sent'
             ORDER BY sent_at DESC LIMIT 5"
        )->fetchAll();

        return ['totals' => $totals, 'recent' => $recent];
    }

    private function buildFilterClause(array $filters): array
    {
        $conditions = ['c.deleted_at IS NULL'];
        $bindings = [];

        if (!empty($filters['search'])) {
            $conditions[] = 'c.name LIKE :search';
            $bindings['search'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['status'])) {
            $conditions[] = 'c.status = :status';
            $bindings['status'] = $filters['status'];
        }
        if (!empty($filters['business_line_id'])) {
            $conditions[] = 'c.business_line_id = :bl';
            $bindings['bl'] = $filters['business_line_id'];
        }

        return ['WHERE ' . implode(' AND ', $conditions), $bindings];
    }
}
