<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\User;
use App\Utils\Logger;
use PDO;
use PDOException;

class UserRepository
{
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function findByDidarId($didarId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM users WHERE didar_user_id = ? LIMIT 1");
        $stmt->execute([$didarId]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new User($data) : null;
    }

    public function findByEmail($email)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ? LIMIT 1");
        $stmt->execute([$email]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new User($data) : null;
    }

    public function findByUsernameOrDisplayName($identifier)
    {
        $pdo = $this->db->getPdo();
        $hasUsername = $this->db->hasColumn('users', 'username');
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        
        // Normalize identifier: trim and replace multiple spaces with single space
        $normalized = preg_replace('/\s+/', ' ', trim($identifier));
        
        // Try exact match first
        if ($hasUsername && $hasDisplayName) {
            $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? OR display_name = ? LIMIT 1");
            $stmt->execute([$normalized, $normalized]);
            $data = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($data) {
                return new User($data);
            }
            
            // Try LIKE match (case-insensitive, flexible spacing)
            $likePattern = '%' . str_replace(' ', '%', $normalized) . '%';
            $stmt = $pdo->prepare("SELECT * FROM users WHERE username LIKE ? OR display_name LIKE ? LIMIT 1");
            $stmt->execute([$likePattern, $likePattern]);
        } elseif ($hasDisplayName) {
            $stmt = $pdo->prepare("SELECT * FROM users WHERE display_name = ? LIMIT 1");
            $stmt->execute([$normalized]);
            $data = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($data) {
                return new User($data);
            }
            
            // Try LIKE match
            $likePattern = '%' . str_replace(' ', '%', $normalized) . '%';
            $stmt = $pdo->prepare("SELECT * FROM users WHERE display_name LIKE ? LIMIT 1");
            $stmt->execute([$likePattern]);
        } elseif ($hasUsername) {
            $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? LIMIT 1");
            $stmt->execute([$normalized]);
            $data = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($data) {
                return new User($data);
            }
            
            // Try LIKE match
            $likePattern = '%' . str_replace(' ', '%', $normalized) . '%';
            $stmt = $pdo->prepare("SELECT * FROM users WHERE username LIKE ? LIMIT 1");
            $stmt->execute([$likePattern]);
        } else {
            // Fallback: try to match by first_name + last_name
            $parts = explode(' ', $normalized, 2);
            if (count($parts) === 2) {
                $stmt = $pdo->prepare("SELECT * FROM users WHERE first_name = ? AND last_name = ? LIMIT 1");
                $stmt->execute([trim($parts[0]), trim($parts[1])]);
                $data = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($data) {
                    return new User($data);
                }
                
                // Try LIKE match for first_name and last_name
                $stmt = $pdo->prepare("SELECT * FROM users WHERE first_name LIKE ? AND last_name LIKE ? LIMIT 1");
                $stmt->execute(['%' . trim($parts[0]) . '%', '%' . trim($parts[1]) . '%']);
            } else {
                // Try to match by first_name only
                $stmt = $pdo->prepare("SELECT * FROM users WHERE first_name LIKE ? LIMIT 1");
                $stmt->execute(['%' . $normalized . '%']);
            }
        }
        
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new User($data) : null;
    }

    public function findById($id)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new User($data) : null;
    }

    public function save(User $user)
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $hasUsername = $this->db->hasColumn('users', 'username');
        
        if (!empty($user->id)) {
            $columns = [
                'didar_user_id = ?',
                'email = ?',
                'first_name = ?',
                'last_name = ?',
                'role = ?',
                'is_active = ?',
                'last_sync = ?',
            ];
            $params = [
                $user->didar_user_id,
                $user->email,
                $user->first_name,
                $user->last_name,
                $user->role ?? 'agent',
                $user->is_active ?? 1,
                $user->last_sync,
            ];
            if ($hasDisplayName) {
                $columns[] = 'display_name = ?';
                $params[] = $user->display_name;
            }
            if ($hasUsername) {
                $columns[] = 'username = ?';
                $params[] = $user->username;
            }
            $params[] = $user->id;
            $sql = "UPDATE users SET " . implode(', ', $columns) . " WHERE id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $user->id;
        }

        $columns = ['didar_user_id', 'email', 'first_name', 'last_name'];
        $placeholders = ['?', '?', '?', '?'];
        $values = [
            $user->didar_user_id,
            $user->email,
            $user->first_name,
            $user->last_name,
        ];

        if ($hasDisplayName) {
            $columns[] = 'display_name';
            $placeholders[] = '?';
            $values[] = $user->display_name;
        }
        if ($hasUsername) {
            $columns[] = 'username';
            $placeholders[] = '?';
            $values[] = $user->username;
        }
        $columns = array_merge($columns, ['role', 'is_active', 'last_sync']);
        $placeholders = array_merge($placeholders, ['?', '?', '?']);
        $values = array_merge($values, [
            $user->role ?? 'agent',
            $user->is_active ?? 1,
            $user->last_sync
        ]);

        $sql = "INSERT INTO users (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($values);
        
        return $pdo->lastInsertId();
    }

    public function updatePassword($userId, $password)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE users SET password = ? WHERE id = ?");
        return $stmt->execute([$password, $userId]);
    }

    public function updateRole($userId, $role)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE users SET role = ? WHERE id = ?");
        return $stmt->execute([$role, $userId]);
    }

    public function deleteById(int $userId): bool
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
        return $stmt->execute([$userId]);
    }

    public function getAll()
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("SELECT * FROM users ORDER BY created_at DESC");
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function($data) {
            return new User($data);
        }, $results);
    }

    public function countByRole($role)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE role = ?");
        $stmt->execute([$role]);
        return (int)$stmt->fetchColumn();
    }

    public function exists($didarId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE didar_user_id = ?");
        $stmt->execute([$didarId]);
        return $stmt->fetchColumn() > 0;
    }

    /**
     * Get users list for login dropdown (public endpoint).
     * Mirrors legacy behaviour from index.php but adapted for MySQL.
     */
    public function getLoginUsers(): array
    {
        $pdo = $this->db->getPdo();

        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $hasUsername    = $this->db->hasColumn('users', 'username');

        $orderBy = $hasDisplayName ? 'display_name' : 'first_name';

        if ($hasDisplayName && $hasUsername) {
            $sql = "SELECT id, username, display_name, first_name, last_name, email, role 
                    FROM users 
                    WHERE is_active = 1 
                    ORDER BY role DESC, {$orderBy}";
        } elseif ($hasDisplayName) {
            $sql = "SELECT id, display_name, first_name, last_name, email, role 
                    FROM users 
                    WHERE is_active = 1 
                    ORDER BY role DESC, {$orderBy}";
        } else {
            $sql = "SELECT id, first_name, last_name, email, role 
                    FROM users 
                    WHERE is_active = 1 
                    ORDER BY role DESC, {$orderBy}";
        }

        $stmt  = $pdo->query($sql);
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Normalise username and display_name fields for frontend
        foreach ($users as &$user) {
            if ($hasUsername) {
                if (empty($user['username'])) {
                    $user['username'] = ($hasDisplayName && !empty($user['display_name']))
                        ? $user['display_name']
                        : trim(($user['first_name'] ?? '') . ' ' . ($user['last_name'] ?? ''));
                }
            } else {
                $user['username'] = ($hasDisplayName && !empty($user['display_name']))
                    ? $user['display_name']
                    : trim(($user['first_name'] ?? '') . ' ' . ($user['last_name'] ?? ''));
            }

            if (!$hasDisplayName || empty($user['display_name'])) {
                $user['display_name'] = trim(($user['first_name'] ?? '') . ' ' . ($user['last_name'] ?? ''));
            }
        }
        unset($user);

        return $users;
    }

    /**
     * Find first active CRM specialist user
     * Returns null if no CRM specialist found
     */
    public function findCrmSpecialist()
    {
        try {
            $pdo = $this->db->getPdo();

            // First check if table exists and has the required columns
            $tableExists = $this->db->hasColumn('users', 'role');
            if (!$tableExists) {
                \App\Utils\Logger::logWarning("Users table or role column does not exist");
                return null;
            }

            $stmt = $pdo->prepare("SELECT * FROM users WHERE role = 'crm_specialist' AND is_active = 1 LIMIT 1");
            $stmt->execute();
            $data = $stmt->fetch(PDO::FETCH_ASSOC);

            return $data ? new User($data) : null;
        } catch (\Exception $e) {
            \App\Utils\Logger::logError("Error in findCrmSpecialist", $e);
            return null;
        }
    }
}

