waseel/app/(api)/admin/stats+api.ts

import { requireOwner, withCors, preflight } from "@/lib/admin";
import { sql } from "@/lib/db";

export async function OPTIONS(request: Request) {
  return preflight(request);
}

export async function GET(request: Request) {
  const auth = await requireOwner(request);
  if ("error" in auth) return withCors(request, auth.error);

  try {
    // Money only ever comes from rides that actually happened.
    //
    // "Pending payment" used to be `payment_status <> 'paid'`, which swept in
    // every cancelled and expired ride — a rider who changed their mind before
    // a driver was even assigned showed up as outstanding revenue the company
    // was owed. Settled/outstanding are now scoped to completed rides, and the
    // top line is split three ways: what riders paid, what drivers keep, and
    // what the company actually earns.
    const [totals] = await sql<{
      users: number;
      drivers: number;
      rides: number;
      completed_rides: number;
      cancelled_rides: number;
      gross_fares: number;
      driver_payouts: number;
      company_revenue: number;
      company_collected: number;
      company_outstanding: number;
      driver_outstanding: number;
      rides_today: number;
      avg_fare: number;
      pending_count: number;
      pending_revenue: number;
      new_users_7d: number;
    }>`
      SELECT
        (SELECT COUNT(*)::int FROM users) AS users,
        (SELECT COUNT(*)::int FROM drivers) AS drivers,
        (SELECT COUNT(*)::int FROM rides) AS rides,
        (SELECT COUNT(*)::int FROM rides WHERE status = 'completed') AS completed_rides,
        (SELECT COUNT(*)::int FROM rides WHERE status IN ('cancelled','expired')) AS cancelled_rides,

        -- What riders were charged, across every completed ride.
        (SELECT COALESCE(SUM(fare_price) / 100.0, 0)::float8
           FROM rides WHERE status = 'completed') AS gross_fares,

        -- Revenue and payouts count rides whose money actually materialised.
        --
        -- Scoping these to paid rides is what makes the books reconcile:
        --   gross_fares      = paid fares + pending_revenue
        --   paid fares       = company_revenue + driver_payouts
        --   company_revenue  = company_collected + company_outstanding
        -- Counting fees on a fare nobody ever paid would show revenue that can
        -- never be collected and never be chased — it belongs in the
        -- uncollected line below, not the top line.
        (SELECT COALESCE(SUM(COALESCE(driver_payout_cents, 0)) / 100.0, 0)::float8
           FROM rides WHERE status = 'completed'
             AND payment_status IN ('paid','cash_collected')) AS driver_payouts,

        (SELECT COALESCE(SUM(COALESCE(platform_fee_cents, 0)) / 100.0, 0)::float8
           FROM rides WHERE status = 'completed'
             AND payment_status IN ('paid','cash_collected')) AS company_revenue,

        -- Earned and actually in hand: card fees, plus cash commission a
        -- driver has since remitted.
        (SELECT COALESCE(SUM(platform_fee_cents) / 100.0, 0)::float8
           FROM rides
          WHERE status = 'completed'
            AND payment_status IN ('paid','cash_collected')
            AND platform_fee_settled_at IS NOT NULL) AS company_collected,

        -- Earned but still sitting in a driver's pocket. This is the number an
        -- operator chases at the end of a shift.
        (SELECT COALESCE(SUM(platform_fee_cents) / 100.0, 0)::float8
           FROM rides
          WHERE status = 'completed'
            AND payment_status IN ('paid','cash_collected')
            AND platform_fee_settled_at IS NULL) AS company_outstanding,

        -- The mirror: payouts the company still owes its drivers.
        (SELECT COALESCE(SUM(driver_payout_cents) / 100.0, 0)::float8
           FROM rides
          WHERE status = 'completed'
            AND payment_status IN ('paid','cash_collected')
            AND driver_payout_settled_at IS NULL) AS driver_outstanding,

        (SELECT COUNT(*)::int FROM rides WHERE created_at >= CURRENT_DATE) AS rides_today,
        (SELECT COALESCE(ROUND(AVG(fare_price) / 100.0, 2), 0)::float8
           FROM rides WHERE status = 'completed') AS avg_fare,

        -- Completed rides whose money never actually landed: a cash fare the
        -- driver didn't collect, or a card ride that never settled.
        (SELECT COUNT(*)::int FROM rides
          WHERE status = 'completed'
            AND payment_status NOT IN ('paid','cash_collected')) AS pending_count,
        (SELECT COALESCE(SUM(fare_price) / 100.0, 0)::float8 FROM rides
          WHERE status = 'completed'
            AND payment_status NOT IN ('paid','cash_collected')) AS pending_revenue,

        (SELECT COUNT(*)::int FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '7 days') AS new_users_7d
    `;

    const trend = await sql<{
      day: string;
      rides: number;
      revenue: number;
      payouts: number;
    }>`
      SELECT
        TO_CHAR(DAY, 'YYYY-MM-DD') AS day,
        COUNT(r.ride_id)::int AS rides,
        COALESCE(SUM(COALESCE(r.platform_fee_cents, 0))
          FILTER (WHERE r.status = 'completed') / 100.0, 0)::float8 AS revenue,
        COALESCE(SUM(COALESCE(r.driver_payout_cents, 0))
          FILTER (WHERE r.status = 'completed') / 100.0, 0)::float8 AS payouts
      FROM generate_series(
        CURRENT_DATE - INTERVAL '13 days',
        CURRENT_DATE,
        INTERVAL '1 day'
      ) AS DAY
      LEFT JOIN rides r ON r.created_at >= DAY AND r.created_at < DAY + INTERVAL '1 day'
      GROUP BY DAY
      ORDER BY DAY
    `;

    // Ranked by what each driver actually earned, not by what their riders
    // were charged — and counting only rides that happened.
    const topDrivers = await sql<{
      driver_id: number;
      name: string;
      rides: number;
      earnings: number;
      company_revenue: number;
    }>`
      SELECT
        d.id AS driver_id,
        d.first_name || ' ' || d.last_name AS name,
        COUNT(r.ride_id) FILTER (WHERE r.status = 'completed')::int AS rides,
        COALESCE(SUM(COALESCE(r.driver_payout_cents, 0))
          FILTER (WHERE r.status = 'completed') / 100.0, 0)::float8 AS earnings,
        COALESCE(SUM(COALESCE(r.platform_fee_cents, 0))
          FILTER (WHERE r.status = 'completed') / 100.0, 0)::float8 AS company_revenue
      FROM drivers d
      LEFT JOIN rides r ON r.driver_id = d.id
      GROUP BY d.id, d.first_name, d.last_name
      ORDER BY earnings DESC, rides DESC
      LIMIT 5
    `;

    return withCors(request, Response.json({ data: { totals, trend, topDrivers } }));
  } catch (error) {
    console.error("[ADMIN_STATS]: ", error);
    return withCors(request,
      Response.json({ error: "Internal Server Error" }, { status: 500 }),
    );
  }
}