<?php
/**
 * User Model
 * Maps to `users` table
 */

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

class User extends Model {
    protected string $table = 'users';

    public function findByUsernameOrEmail(string $identifier): ?array {
        $sql = "SELECT * FROM `users` WHERE (`username` = :id1 OR `email` = :id2) AND `deleted_at` IS NULL LIMIT 1";
        return $this->fetchOne($sql, [':id1' => $identifier, ':id2' => $identifier]);
    }

    public function findByEmail(string $email): ?array {
        $sql = "SELECT * FROM `users` WHERE `email` = :email LIMIT 1";
        return $this->fetchOne($sql, [':email' => $email]);
    }

    public function findByUsername(string $username): ?array {
        $sql = "SELECT * FROM `users` WHERE `username` = :username LIMIT 1";
        return $this->fetchOne($sql, [':username' => $username]);
    }

    public function findByPhone(string $phone): ?array {
        $sql = "SELECT * FROM `users` WHERE `phone` = :phone LIMIT 1";
        return $this->fetchOne($sql, [':phone' => $phone]);
    }

    public function getUserRoles(int $userId): array {
        $sql = "SELECT r.name FROM `user_roles` ur 
                JOIN `roles` r ON ur.role_id = r.id 
                WHERE ur.user_id = :user_id";
        $rows = $this->fetchAll($sql, [':user_id' => $userId]);
        return array_column($rows, 'name');
    }

    public function assignRole(int $userId, string $roleName): bool {
        $sqlRole = "SELECT id FROM `roles` WHERE `name` = :name LIMIT 1";
        $role = $this->fetchOne($sqlRole, [':name' => $roleName]);
        if (!$role) {
            return false;
        }

        $roleId = (int)$role['id'];
        $sql = "INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`, `assigned_at`) VALUES (:user_id, :role_id, NOW())";
        $this->query($sql, [':user_id' => $userId, ':role_id' => $roleId]);
        return true;
    }

    public function updateLastLogin(int $userId): void {
        $this->query("UPDATE `users` SET `last_login_at` = NOW() WHERE `id` = :id", [':id' => $userId]);
    }

    public function softDelete(int $userId): bool {
        $sql = "UPDATE `users` SET `status` = 'deleted', `deleted_at` = NOW() WHERE `id` = :id";
        $this->query($sql, [':id' => $userId]);
        return true;
    }

    // =========================================================
    // Admin Management Methods
    // =========================================================

    /**
     * Paginated user search for Admin panel
     */
    public function searchUsers(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildAdminUserConditions($filters);

        $sql = "SELECT u.id, u.username, u.email, u.first_name, u.last_name, u.phone,
                u.status, u.is_super_admin, u.created_at, u.last_login_at,
                GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ',') as roles
                FROM `users` u
                LEFT JOIN `user_roles` ur ON ur.user_id = u.id
                LEFT JOIN `roles` r ON r.id = ur.role_id
                WHERE {$whereSql}
                GROUP BY u.id
                ORDER BY u.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countUsers(array $filters = []): int {
        [$whereSql, $params] = $this->buildAdminUserConditions($filters);
        $sql = "SELECT COUNT(DISTINCT u.id) FROM `users` u
                LEFT JOIN `user_roles` ur ON ur.user_id = u.id
                LEFT JOIN `roles` r ON r.id = ur.role_id
                WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    private function buildAdminUserConditions(array $filters): array {
        $conditions = ['u.deleted_at IS NULL'];
        $params = [];

        if (!empty($filters['q'])) {
            $conditions[] = '(u.username LIKE :q OR u.email LIKE :q OR u.phone LIKE :q OR u.first_name LIKE :q OR u.last_name LIKE :q)';
            $params[':q'] = '%' . trim($filters['q']) . '%';
        }
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = 'u.status = :status';
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['role']) && $filters['role'] !== 'all') {
            $conditions[] = 'r.name = :role';
            $params[':role'] = $filters['role'];
        }

        return [implode(' AND ', $conditions), $params];
    }

    public function findWithRoles(int $userId): ?array {
        $sql = "SELECT u.*,
                GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ',') as roles
                FROM `users` u
                LEFT JOIN `user_roles` ur ON ur.user_id = u.id
                LEFT JOIN `roles` r ON r.id = ur.role_id
                WHERE u.id = :id AND u.deleted_at IS NULL
                GROUP BY u.id
                LIMIT 1";
        return $this->fetchOne($sql, [':id' => $userId]);
    }

    public function suspendUser(int $userId): bool {
        $sql = "UPDATE `users` SET `status` = 'suspended', `updated_at` = NOW() WHERE `id` = :id AND `is_super_admin` = 0";
        $stmt = $this->query($sql, [':id' => $userId]);
        return $stmt->rowCount() > 0;
    }

    public function reactivateUser(int $userId): bool {
        $sql = "UPDATE `users` SET `status` = 'active', `updated_at` = NOW() WHERE `id` = :id";
        $stmt = $this->query($sql, [':id' => $userId]);
        return $stmt->rowCount() > 0;
    }

    public function getAdminUsers(): array {
        $sql = "SELECT u.id, u.username, u.email, u.first_name, u.last_name,
                u.status, u.is_super_admin, u.created_at, u.last_login_at,
                GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ',') as roles
                FROM `users` u
                JOIN `user_roles` ur ON ur.user_id = u.id
                JOIN `roles` r ON r.id = ur.role_id
                WHERE r.name IN ('admin','operation_admin','finance_admin','support_admin')
                AND u.deleted_at IS NULL
                GROUP BY u.id
                ORDER BY u.is_super_admin DESC, u.created_at ASC";
        return $this->fetchAll($sql);
    }

    public function removeRole(int $userId, string $roleName): void {
        $sql = "DELETE ur FROM `user_roles` ur
                JOIN `roles` r ON r.id = ur.role_id
                WHERE ur.user_id = :uid AND r.name = :role";
        $this->query($sql, [':uid' => $userId, ':role' => $roleName]);
    }

    public function getUserLoginHistory(int $userId, int $limit = 20): array {
        $sql = "SELECT * FROM `login_history` WHERE user_id = :uid ORDER BY created_at DESC LIMIT :lim";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':lim', $limit,  PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getPlatformUserStats(): array {
        $sql = "SELECT
                COUNT(*) as total_users,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users,
                SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) as suspended_users,
                SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as new_today
                FROM `users` WHERE deleted_at IS NULL";
        return $this->fetchOne($sql) ?: [];
    }
}