<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * NewsletterSubscriber Model
 *
 * Manages newsletter subscribers — subscribe, unsubscribe, list, delete.
 * Supports linking to student_id for students who subscribe (if column exists).
 */
class NewsletterSubscriber extends Model
{
    protected string $table = 'newsletter_subscribers';

    /**
     * Check if the student_id column exists in the table.
     */
    private function hasStudentIdColumn(): bool
    {
        try {
            $check = $this->db->query("SHOW COLUMNS FROM `{$this->table}` LIKE 'student_id'");
            return $check && $check->fetch() !== false;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Get all active subscribers.
     */
    public function getActive(int $limit = 1000): array
    {
        $sql = "SELECT * FROM `{$this->table}`
                WHERE `status` = 'active' AND `is_verified` = 1
                ORDER BY `subscribed_at` DESC
                LIMIT :limit";

        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

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

    /**
     * Get all subscribers (for admin listing).
     */
    public function getAllSubscribers(string $search = '', string $statusFilter = ''): array
    {
        $where  = '1=1';
        $params = [];

        if (!empty($search)) {
            $where .= ' AND (`email` LIKE :search OR `name` LIKE :search2)';
            $params[':search']  = '%' . $search . '%';
            $params[':search2'] = '%' . $search . '%';
        }

        if (!empty($statusFilter) && in_array($statusFilter, ['active', 'unsubscribed', 'bounced'], true)) {
            $where .= ' AND `status` = :status';
            $params[':status'] = $statusFilter;
        }

        $sql = "SELECT * FROM `{$this->table}`
                WHERE {$where}
                ORDER BY `subscribed_at` DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

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

    /**
     * Subscribe an email address.
     * Returns: 'subscribed' | 'already_exists' | 'resubscribed'
     */
    public function subscribe(string $email, ?string $name = null, string $source = 'homepage', ?int $studentId = null): string
    {
        $email = strtolower(trim($email));
        $hasStudentId = $this->hasStudentIdColumn();

        // Check if email already exists
        $existing = $this->findByEmail($email);

        if ($existing) {
            if ($existing['status'] === 'active') {
                // Update student_id if provided, column exists, and not already set
                if ($hasStudentId && $studentId && empty($existing['student_id'])) {
                    $this->update($existing['id'], ['student_id' => $studentId]);
                }
                return 'already_exists';
            }
            // Re-subscribe: update status back to active
            if ($hasStudentId) {
                $sql = "UPDATE `{$this->table}`
                        SET `status` = 'active', `unsubscribed_at` = NULL,
                            `name` = :name, `source` = :source,
                            `verification_token` = :token, `is_verified` = 1,
                            `student_id` = COALESCE(:student_id, `student_id`)
                        WHERE `id` = :id";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    ':name'       => $name,
                    ':source'     => $source,
                    ':token'      => bin2hex(random_bytes(32)),
                    ':student_id' => $studentId,
                    ':id'         => $existing['id'],
                ]);
            } else {
                $sql = "UPDATE `{$this->table}`
                        SET `status` = 'active', `unsubscribed_at` = NULL,
                            `name` = :name, `source` = :source,
                            `verification_token` = :token, `is_verified` = 1
                        WHERE `id` = :id";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    ':name'   => $name,
                    ':source' => $source,
                    ':token'  => bin2hex(random_bytes(32)),
                    ':id'     => $existing['id'],
                ]);
            }
            return 'resubscribed';
        }

        // New subscriber
        if ($hasStudentId) {
            $sql = "INSERT INTO `{$this->table}` (`email`, `name`, `status`, `verification_token`, `is_verified`, `source`, `student_id`)
                    VALUES (:email, :name, 'active', :token, 1, :source, :student_id)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                ':email'      => $email,
                ':name'       => $name,
                ':token'      => bin2hex(random_bytes(32)),
                ':source'     => $source,
                ':student_id' => $studentId,
            ]);
        } else {
            $sql = "INSERT INTO `{$this->table}` (`email`, `name`, `status`, `verification_token`, `is_verified`, `source`)
                    VALUES (:email, :name, 'active', :token, 1, :source)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                ':email'  => $email,
                ':name'   => $name,
                ':token'  => bin2hex(random_bytes(32)),
                ':source' => $source,
            ]);
        }

        return 'subscribed';
    }

    /**
     * Subscribe a student by their student record.
     */
    public function subscribeStudent(int $studentId, string $email, string $name): string
    {
        return $this->subscribe($email, $name, 'student_registration', $studentId);
    }

    /**
     * Unsubscribe an email by token or email.
     */
    public function unsubscribe(string $tokenOrEmail): bool
    {
        $field = filter_var($tokenOrEmail, FILTER_VALIDATE_EMAIL) ? 'email' : 'verification_token';

        $sql = "UPDATE `{$this->table}`
                SET `status` = 'unsubscribed', `unsubscribed_at` = NOW()
                WHERE `{$field}` = :value AND `status` = 'active'";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':value' => $tokenOrEmail]);

        return $stmt->rowCount() > 0;
    }

    /**
     * Find a subscriber by email.
     */
    public function findByEmail(string $email): ?array
    {
        $sql = "SELECT * FROM `{$this->table}` WHERE `email` = :email LIMIT 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':email' => strtolower(trim($email))]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result !== false ? $result : null;
    }

    /**
     * Delete a subscriber by ID.
     */
    public function deleteSubscriber(int $id): bool
    {
        $sql = "DELETE FROM `{$this->table}` WHERE `id` = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        return $stmt->rowCount() > 0;
    }

    /**
     * Toggle subscriber status (active/unsubscribed).
     */
    public function toggleStatus(int $id): bool
    {
        $sub = $this->find($id);
        if (!$sub) {
            return false;
        }

        if ($sub['status'] === 'active') {
            $sql = "UPDATE `{$this->table}` SET `status` = 'unsubscribed', `unsubscribed_at` = NOW() WHERE `id` = :id";
        } else {
            $sql = "UPDATE `{$this->table}` SET `status` = 'active', `unsubscribed_at` = NULL WHERE `id` = :id";
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        return $stmt->rowCount() > 0;
    }

    /**
     * Get subscriber counts by status.
     */
    public function getCounts(): array
    {
        $sql = "SELECT `status`, COUNT(*) as cnt FROM `{$this->table}` GROUP BY `status`";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        $counts = ['active' => 0, 'unsubscribed' => 0, 'bounced' => 0, 'total' => 0];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $counts[$row['status']] = (int) $row['cnt'];
            $counts['total'] += (int) $row['cnt'];
        }

        return $counts;
    }

    /**
     * Get active subscriber email list for sending newsletter.
     */
    public function getActiveEmails(): array
    {
        $sql = "SELECT `email`, `name` FROM `{$this->table}`
                WHERE `status` = 'active' AND `is_verified` = 1
                ORDER BY `subscribed_at` ASC";

        return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Log a sent newsletter email.
     */
    public function logEmail(string $subject, ?string $bodyHtml, ?string $bodyText, int $recipientCount, int $sentCount, int $failedCount, string $status = 'sent'): int
    {
        $sql = "INSERT INTO `newsletter_emails` (`subject`, `body_html`, `body_text`, `recipient_count`, `sent_count`, `failed_count`, `status`, `sent_at`)
                VALUES (:subject, :body_html, :body_text, :recipient_count, :sent_count, :failed_count, :status, NOW())";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':subject'         => $subject,
            ':body_html'       => $bodyHtml,
            ':body_text'       => $bodyText,
            ':recipient_count' => $recipientCount,
            ':sent_count'      => $sentCount,
            ':failed_count'    => $failedCount,
            ':status'          => $status,
        ]);

        return (int) $this->db->lastInsertId();
    }

    /**
     * Get sent email logs (for admin).
     */
    public function getEmailLogs(int $limit = 50): array
    {
        $sql = "SELECT * FROM `newsletter_emails` ORDER BY `created_at` DESC LIMIT :limit";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

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