<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class RiderModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function all(int $page = 1, int $perPage = ITEMS_PER_PAGE, string $search = ''): array
    {
        $offset = ($page - 1) * $perPage;
        $where  = "r.name = 'rider'";
        $params = [];

        if ($search !== '') {
            $where   .= " AND (u.name LIKE ? OR u.phone LIKE ? OR u.email LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }

        $params[] = $perPage;
        $params[] = $offset;

        return $this->db->fetchAll(
            "SELECT u.*,
                    r.label AS role_label,
                    COUNT(DISTINCT o.id) AS total_deliveries,
                    SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS completed,
                    SUM(CASE WHEN o.status IN ('picked_up','on_the_way') THEN 1 ELSE 0 END) AS active_orders
               FROM users u
               JOIN roles r ON r.id = u.role_id
               LEFT JOIN orders o ON o.rider_id = u.id
              WHERE $where
              GROUP BY u.id
              ORDER BY u.name ASC
              LIMIT ? OFFSET ?",
            $params
        );
    }

    public function count(string $search = ''): int
    {
        $where  = "r.name = 'rider'";
        $params = [];

        if ($search !== '') {
            $where   .= " AND (u.name LIKE ? OR u.phone LIKE ? OR u.email LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }

        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM users u JOIN roles r ON r.id = u.role_id WHERE $where",
            $params
        );
    }

    public function find(int $id): ?array
    {
        return $this->db->fetchOne(
            "SELECT u.*, r.name AS role_name
               FROM users u JOIN roles r ON r.id = u.role_id
              WHERE u.id = ? AND r.name = 'rider'",
            [$id]
        );
    }

    /** All active riders for dropdown in order assign */
    public function activeList(): array
    {
        return $this->db->fetchAll(
            "SELECT u.id, u.name, u.phone,
                    COUNT(o.id) AS active_orders
               FROM users u
               JOIN roles r ON r.id = u.role_id
               LEFT JOIN orders o ON o.rider_id = u.id
                    AND o.status IN ('picked_up','on_the_way')
              WHERE r.name = 'rider' AND u.is_active = 1
              GROUP BY u.id
              ORDER BY active_orders ASC, u.name ASC"
        );
    }

    public function riderRoleId(): int
    {
        return (int) $this->db->fetchValue(
            "SELECT id FROM roles WHERE name = 'rider'"
        );
    }

    public function emailExists(string $email, int $excludeId = 0): bool
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM users WHERE email = ? AND id != ?",
            [$email, $excludeId]
        ) > 0;
    }

    public function create(array $data): int
    {
        return $this->db->insert('users', $data);
    }

    public function update(int $id, array $data): int
    {
        return $this->db->update('users', $data, ['id' => $id]);
    }

    public function delete(int $id): int
    {
        // Unassign from active orders first
        $this->db->update('orders', ['rider_id' => null],
            ['rider_id' => $id]);
        return $this->db->delete('users', ['id' => $id]);
    }

    public function toggle(int $id): bool
    {
        $current = (int) $this->db->fetchValue(
            "SELECT is_active FROM users WHERE id = ?", [$id]
        );
        $this->db->update('users', ['is_active' => $current ? 0 : 1], ['id' => $id]);
        return !$current;
    }

    public function deliveryHistory(int $riderId, int $page = 1, int $perPage = 15): array
    {
        $offset = ($page - 1) * $perPage;
        return $this->db->fetchAll(
            "SELECT o.id, o.order_number, o.status, o.total_amount,
                    o.delivery_address, o.placed_at, o.updated_at,
                    COALESCE(cu.name, o.guest_name) AS customer_name,
                    COALESCE(cu.phone, o.guest_phone) AS customer_phone
               FROM orders o
               LEFT JOIN users cu ON cu.id = o.customer_id
              WHERE o.rider_id = ?
              ORDER BY o.updated_at DESC
              LIMIT ? OFFSET ?",
            [$riderId, $perPage, $offset]
        );
    }

    public function deliveryHistoryCount(int $riderId): int
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM orders WHERE rider_id = ?", [$riderId]
        );
    }
}
