<?php

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

    public function allWithRole(): array
    {
        return $this->db->query(
            "SELECT u.*, r.role_name FROM users u JOIN roles r ON r.id = u.role_id ORDER BY u.id DESC"
        )->fetchAll();
    }

    public function usernameExists(string $username, ?int $excludeId = null): bool
    {
        $sql = "SELECT COUNT(*) as cnt FROM users WHERE username = :username";
        $params = ['username' => $username];
        if ($excludeId) {
            $sql .= " AND id != :id";
            $params['id'] = $excludeId;
        }
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetch()['cnt'] > 0;
    }

    public function ridersWithStats(): array
    {
        return $this->db->query(
            "SELECT u.*,
                    (SELECT COUNT(*) FROM deliveries d WHERE d.rider_id = u.id AND d.status IN ('ASSIGNED','PICKED_UP')) as active_count,
                    (SELECT COUNT(*) FROM deliveries d WHERE d.rider_id = u.id AND d.status = 'DELIVERED') as delivered_count,
                    (SELECT COUNT(*) FROM deliveries d WHERE d.rider_id = u.id AND d.status = 'DELIVERED' AND DATE(d.delivered_at) = CURDATE()) as delivered_today,
                    (SELECT COUNT(*) FROM deliveries d WHERE d.rider_id = u.id AND d.status = 'FAILED') as failed_count
             FROM users u JOIN roles r ON r.id = u.role_id
             WHERE r.role_name = 'RIDER'
             ORDER BY u.is_active DESC, u.full_name ASC"
        )->fetchAll();
    }

    public function riderStatsSummary(): array
    {
        $stmt = $this->db->query(
            "SELECT
                (SELECT COUNT(*) FROM users u JOIN roles r ON r.id=u.role_id WHERE r.role_name='RIDER') as total_riders,
                (SELECT COUNT(*) FROM users u JOIN roles r ON r.id=u.role_id WHERE r.role_name='RIDER' AND u.is_active=1) as active_riders,
                (SELECT COUNT(*) FROM deliveries WHERE status IN ('ASSIGNED','PICKED_UP')) as on_delivery_now,
                (SELECT COUNT(*) FROM deliveries WHERE status='DELIVERED' AND DATE(delivered_at)=CURDATE()) as delivered_today"
        );
        return $stmt->fetch();
    }
}