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

declare(strict_types=1);

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

class Contact extends Model
{
    protected string $table = 'contact_messages';
    protected string $primaryKey = 'id';
    
    /**
     * Create new contact message
     */
    public function createMessage(array $data): int
    {
        $security = new Security();
        
        $messageData = [
            'name' => $security->sanitize($data['name']),
            'email' => $security->sanitize($data['email']),
            'subject' => !empty($data['subject']) ? $security->sanitize($data['subject']) : null,
            'message' => $security->sanitize($data['message']),
            'ip_address' => $security->getClientIp(),
            'status' => 'new',
            'created_at' => date('Y-m-d H:i:s')
        ];
        
        return $this->create($messageData);
    }
    
    /**
     * Get messages with pagination and filtering
     */
    public function getMessages(int $page = 1, int $limit = 20, ?string $status = null): array
    {
        $sql = "SELECT cm.*, u.name as replied_by_name, u.avatar as replied_by_avatar 
                FROM {$this->table} cm 
                LEFT JOIN users u ON cm.replied_by = u.id 
                WHERE 1=1";
        
        $params = [];
        
        if ($status) {
            $sql .= " AND cm.status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY cm.created_at DESC";
        
        return $this->paginate($sql, $params, $page, $limit);
    }
    
    /**
     * Get message by ID
     */
    public function getMessage(int $id): ?array
    {
        $sql = "SELECT cm.*, u.name as replied_by_name, u.avatar as replied_by_avatar 
                FROM {$this->table} cm 
                LEFT JOIN users u ON cm.replied_by = u.id 
                WHERE cm.id = ?";
        
        return $this->db->fetchOne($sql, [$id]);
    }
    
    /**
     * Mark as read
     */
    public function markAsRead(int $id): bool
    {
        return $this->update($id, ['status' => 'read']);
    }
    
    /**
     * Mark as replied
     */
    public function markAsReplied(int $id, int $userId): bool
    {
        return $this->update($id, [
            'status' => 'replied',
            'replied_by' => $userId,
            'replied_at' => date('Y-m-d H:i:s')
        ]);
    }
    
    /**
     * Archive message
     */
    public function archive(int $id): bool
    {
        return $this->update($id, ['status' => 'archived']);
    }
    
    /**
     * Get unread count
     */
    public function getUnreadCount(): int
    {
        $result = $this->db->fetchOne("SELECT COUNT(*) as count FROM {$this->table} WHERE status = 'new'");
        return (int) ($result['count'] ?? 0);
    }
    
    /**
     * Get stats
     */
    public function getStats(): array
    {
        $sql = "SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) as new_count,
                    SUM(CASE WHEN status = 'read' THEN 1 ELSE 0 END) as read_count,
                    SUM(CASE WHEN status = 'replied' THEN 1 ELSE 0 END) as replied_count,
                    SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) as archived_count
                FROM {$this->table}";
        
        return $this->db->fetchOne($sql);
    }
    
    /**
     * Get recent messages
     */
    public function getRecent(int $limit = 5): array
    {
        $sql = "SELECT cm.*, u.name as replied_by_name 
                FROM {$this->table} cm 
                LEFT JOIN users u ON cm.replied_by = u.id 
                ORDER BY cm.created_at DESC 
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Search messages
     */
    public function search(string $query, int $page = 1, int $limit = 20): array
    {
        $sql = "SELECT cm.*, u.name as replied_by_name 
                FROM {$this->table} cm 
                LEFT JOIN users u ON cm.replied_by = u.id 
                WHERE cm.name LIKE ? OR cm.email LIKE ? OR cm.subject LIKE ? OR cm.message LIKE ? 
                ORDER BY cm.created_at DESC";
        
        $searchTerm = "%{$query}%";
        $params = [$searchTerm, $searchTerm, $searchTerm, $searchTerm];
        
        return $this->paginate($sql, $params, $page, $limit);
    }
    
    /**
     * Get messages by date range
     */
    public function getByDateRange(string $startDate, string $endDate, ?string $status = null): array
    {
        $sql = "SELECT cm.*, u.name as replied_by_name 
                FROM {$this->table} cm 
                LEFT JOIN users u ON cm.replied_by = u.id 
                WHERE cm.created_at BETWEEN ? AND ?";
        
        $params = [$startDate, $endDate];
        
        if ($status) {
            $sql .= " AND cm.status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY cm.created_at DESC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Bulk update status
     */
    public function bulkUpdateStatus(array $ids, string $status): int
    {
        if (empty($ids)) {
            return 0;
        }
        
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $sql = "UPDATE {$this->table} SET status = ? WHERE id IN ({$placeholders})";
        
        $params = array_merge([$status], $ids);
        
        $this->db->query($sql, $params);
        return count($ids);
    }
    
    /**
     * Bulk delete
     */
    public function bulkDelete(array $ids): int
    {
        if (empty($ids)) {
            return 0;
        }
        
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $sql = "DELETE FROM {$this->table} WHERE id IN ({$placeholders})";
        
        $this->db->query($sql, $ids);
        return count($ids);
    }
    
    /**
     * Get daily stats
     */
    public function getDailyStats(int $days = 30): array
    {
        $sql = "SELECT 
                    DATE(created_at) as date,
                    COUNT(*) as total,
                    SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) as new_count
                FROM {$this->table}
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                GROUP BY DATE(created_at)
                ORDER BY date DESC";
        
        return $this->db->fetchAll($sql, [$days]);
    }
}
