<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\Support;
use PDO;

/**
 * Support Repository
 * Manages CRUD operations for CRM support logs
 */
class SupportRepository
{
    private Connection $db;
    
    public function __construct(Connection $db)
    {
        $this->db = $db;
    }
    
    /**
     * Create a new support log entry
     */
    public function create(Support $support): int
    {
        $pdo = $this->db->getPdo();
        
        $sql = "INSERT INTO crm_support_logs 
                (contact_didar_id, channel, direction, result_note, created_by_didar_id, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            $support->contact_didar_id,
            $support->channel,
            $support->direction,
            $support->result_note,
            $support->created_by_didar_id
        ]);
        
        return (int)$pdo->lastInsertId();
    }
    
    /**
     * Update an existing support log entry
     * Only admin can update
     */
    public function update(Support $support): bool
    {
        $pdo = $this->db->getPdo();
        
        $sql = "UPDATE crm_support_logs 
                SET channel = ?, 
                    direction = ?, 
                    result_note = ?, 
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?";
        
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $support->channel,
            $support->direction,
            $support->result_note,
            $support->id
        ]);
    }
    
    /**
     * Delete a support log entry
     * Only admin can delete
     */
    public function delete(int $id): bool
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM crm_support_logs WHERE id = ?");
        return $stmt->execute([$id]);
    }
    
    /**
     * Get support logs by contact ID
     */
    public function getByContactId(string $contactId, int $limit = 50): array
    {
        $pdo = $this->db->getPdo();
        
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $creatorNameField = $hasDisplayName 
            ? "u.display_name" 
            : "COALESCE(CONCAT(u.first_name, ' ', u.last_name), '')";
        
        $sql = "SELECT 
                    s.*,
                    $creatorNameField as creator_name,
                    CONCAT(p.first_name, ' ', p.last_name) as contact_name,
                    p.mobile_phone as contact_phone
                FROM crm_support_logs s
                LEFT JOIN users u ON s.created_by_didar_id = u.didar_user_id
                LEFT JOIN persons p ON s.contact_didar_id = p.didar_contact_id
                WHERE s.contact_didar_id = ?
                ORDER BY s.created_at DESC
                LIMIT ?";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$contactId, $limit]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return array_map(function($row) {
            return new Support($row);
        }, $rows);
    }
    
    /**
     * Get a single support log by ID
     */
    public function getById(int $id): ?Support
    {
        $pdo = $this->db->getPdo();
        
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $creatorNameField = $hasDisplayName 
            ? "u.display_name" 
            : "COALESCE(CONCAT(u.first_name, ' ', u.last_name), '')";
        
        $sql = "SELECT 
                    s.*,
                    $creatorNameField as creator_name,
                    CONCAT(p.first_name, ' ', p.last_name) as contact_name,
                    p.mobile_phone as contact_phone
                FROM crm_support_logs s
                LEFT JOIN users u ON s.created_by_didar_id = u.didar_user_id
                LEFT JOIN persons p ON s.contact_didar_id = p.didar_contact_id
                WHERE s.id = ?";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $row ? new Support($row) : null;
    }
    
    /**
     * Get all support logs with pagination
     */
    public function getAll(int $limit = 50, int $offset = 0): array
    {
        $pdo = $this->db->getPdo();
        
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $creatorNameField = $hasDisplayName 
            ? "u.display_name" 
            : "COALESCE(CONCAT(u.first_name, ' ', u.last_name), '')";
        
        $sql = "SELECT 
                    s.*,
                    $creatorNameField as creator_name,
                    CONCAT(p.first_name, ' ', p.last_name) as contact_name,
                    p.mobile_phone as contact_phone
                FROM crm_support_logs s
                LEFT JOIN users u ON s.created_by_didar_id = u.didar_user_id
                LEFT JOIN persons p ON s.contact_didar_id = p.didar_contact_id
                ORDER BY s.created_at DESC
                LIMIT ? OFFSET ?";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$limit, $offset]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return array_map(function($row) {
            return new Support($row);
        }, $rows);
    }
    
    /**
     * Count total support logs
     */
    public function count(): int
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("SELECT COUNT(*) FROM crm_support_logs");
        return (int)$stmt->fetchColumn();
    }
}

