<?php
/**
 * ZLO Platform - Newsletter Model
 */

declare(strict_types=1);

require_once __DIR__ . '/../core/Model.php';

class Newsletter extends Model
{
    protected string $table = 'newsletter_subscribers';
    protected string $primaryKey = 'id';
    
    /**
     * Subscribe email
     */
    public function subscribe(string $email, ?array $metadata = null): int
    {
        $security = new Security();
        
        // Check if already subscribed
        $existing = $this->db->fetchOne(
            "SELECT * FROM {$this->table} WHERE email = ?",
            [$email]
        );
        
        if ($existing) {
            if ($existing['status'] === 'unsubscribed') {
                // Resubscribe
                $this->db->query(
                    "UPDATE {$this->table} SET status = 'subscribed', unsubscribed_at = NULL, updated_at = NOW() WHERE id = ?",
                    [$existing['id']]
                );
                return $existing['id'];
            }
            // Already subscribed
            return $existing['id'];
        }
        
        // Create new subscription
        $data = [
            'email' => $security->sanitize($email),
            'status' => 'subscribed',
            'ip_address' => $security->getClientIp(),
            'metadata' => $metadata ? json_encode($metadata) : null,
            'subscribed_at' => date('Y-m-d H:i:s'),
            'created_at' => date('Y-m-d H:i:s')
        ];
        
        return $this->create($data);
    }
    
    /**
     * Unsubscribe email
     */
    public function unsubscribe(string $email): bool
    {
        return $this->db->query(
            "UPDATE {$this->table} SET status = 'unsubscribed', unsubscribed_at = NOW(), updated_at = NOW() WHERE email = ?",
            [$email]
        );
    }
    
    /**
     * Get subscribers
     */
    public function getSubscribers(int $page = 1, int $limit = 20, ?string $status = null): array
    {
        $sql = "SELECT * FROM {$this->table} WHERE 1=1";
        $params = [];
        
        if ($status) {
            $sql .= " AND status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY created_at DESC";
        
        return $this->paginate($sql, $params, $page, $limit);
    }
    
    /**
     * Get active subscribers (for sending)
     */
    public function getActiveSubscribers(): array
    {
        return $this->db->fetchAll(
            "SELECT email FROM {$this->table} WHERE status = 'subscribed' ORDER BY subscribed_at DESC"
        );
    }
    
    /**
     * Get stats
     */
    public function getStats(): array
    {
        $sql = "SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'subscribed' THEN 1 ELSE 0 END) as subscribed,
                    SUM(CASE WHEN status = 'unsubscribed' THEN 1 ELSE 0 END) as unsubscribed,
                    SUM(CASE WHEN status = 'bounced' THEN 1 ELSE 0 END) as bounced
                FROM {$this->table}";
        
        return $this->db->fetchOne($sql);
    }
    
    /**
     * Get growth stats
     */
    public function getGrowthStats(int $days = 30): array
    {
        $sql = "SELECT 
                    DATE(subscribed_at) as date,
                    COUNT(*) as new_subscribers
                FROM {$this->table}
                WHERE subscribed_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                GROUP BY DATE(subscribed_at)
                ORDER BY date DESC";
        
        return $this->db->fetchAll($sql, [$days]);
    }
    
    /**
     * Verify subscriber (double opt-in)
     */
    public function verify(string $token): bool
    {
        return $this->db->query(
            "UPDATE {$this->table} SET status = 'subscribed', verified_at = NOW(), updated_at = NOW() 
             WHERE verification_token = ?",
            [$token]
        );
    }
    
    /**
     * Mark as bounced
     */
    public function markBounced(string $email): bool
    {
        return $this->db->query(
            "UPDATE {$this->table} SET status = 'bounced', updated_at = NOW() WHERE email = ?",
            [$email]
        );
    }
    
    /**
     * Delete subscriber
     */
    public function deleteSubscriber(int $id): bool
    {
        return $this->delete($id);
    }
    
    /**
     * Bulk import subscribers
     */
    public function bulkImport(array $emails): array
    {
        $results = [
            'imported' => 0,
            'skipped' => 0,
            'failed' => 0
        ];
        
        $security = new Security();
        
        foreach ($emails as $email) {
            $email = trim($email);
            
            if (!$security->validateEmail($email)) {
                $results['failed']++;
                continue;
            }
            
            $existing = $this->db->fetchOne(
                "SELECT id FROM {$this->table} WHERE email = ?",
                [$email]
            );
            
            if ($existing) {
                $results['skipped']++;
                continue;
            }
            
            try {
                $this->create([
                    'email' => $email,
                    'status' => 'subscribed',
                    'subscribed_at' => date('Y-m-d H:i:s'),
                    'created_at' => date('Y-m-d H:i:s')
                ]);
                $results['imported']++;
            } catch (Exception $e) {
                $results['failed']++;
            }
        }
        
        return $results;
    }
    
    /**
     * Export subscribers
     */
    public function export(?string $status = null): array
    {
        $sql = "SELECT email, status, subscribed_at, unsubscribed_at FROM {$this->table}";
        $params = [];
        
        if ($status) {
            $sql .= " WHERE status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY subscribed_at DESC";
        
        return $this->db->fetchAll($sql, $params);
    }
}
