<?php

declare(strict_types=1);

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Carbon;

class Metrics
{
    /* =========================
     *  Public API (used by widget)
     * ========================= */

    public function revenueMtdFormatted(): string
    {
        [$start, $end] = $this->currentMonthRange();

        // Prefer a payments table if it exists, otherwise fall back to jobs totals.
        $sum = 0.0;

        if ($this->tableExists('payments')) {
            // Try common payment amount columns in order
            $amountCol = $this->firstExistingColumn('payments', [
                'amount_cents', 'amount', 'captured_cents', 'captured_amount',
            ]);

            if ($amountCol) {
                $sum = $this->safeSum('payments', $amountCol, $start, $end);
                // Convert cents to dollars if needed
                if ($this->isCentsColumn($amountCol)) {
                    $sum = $sum / 100.0;
                }
            }
        }

        // Fallback: sum from jobs if payments table/column isn’t available
        if ($sum === 0.0 && $this->tableExists('jobs')) {
            // Try the most likely money columns that could exist on jobs
            $jobAmountCol = $this->firstExistingColumn('jobs', [
                // cents-first variants
                'amount_due_cents', 'outstanding_cents', 'total_due_cents',
                // dollar variants
                'amount_due', 'balance_due', 'total_due',
                // VEVS-style totals
                'total_price', 'sub_total', 'car_rental_fee',
            ]);

            if ($jobAmountCol) {
                $sum = $this->safeSum('jobs', $jobAmountCol, $start, $end);
                if ($this->isCentsColumn($jobAmountCol)) {
                    $sum = $sum / 100.0;
                }
            }
        }

        return $this->formatNzd($sum);
    }

    public function mtdDeltaText(): string
    {
        return 'vs last month';
    }

    public function mtdSparkline(): array
    {
        // Keep this simple for now; you can wire a real series later.
        return [8, 12, 15, 22, 19, 26, 31];
    }

    public function accountsReceivableFormatted(): string
    {
        // Pick a “balance/unpaid” column on jobs and sum it.
        if (!$this->tableExists('jobs')) {
            return $this->formatNzd(0.0);
        }

        $col = $this->firstExistingColumn('jobs', [
            'amount_due_cents', 'outstanding_cents', 'total_due_cents',
            'amount_due', 'balance_due', 'total_due',
            'total_price', // last-resort fallback (VEVS)
        ]);

        if (!$col) {
            return $this->formatNzd(0.0);
        }

        $sum = $this->safeSum('jobs', $col);
        if ($this->isCentsColumn($col)) {
            $sum = $sum / 100.0;
        }

        return $this->formatNzd($sum);
    }

    public function unpaidCount(): int
    {
        if (!$this->tableExists('jobs')) {
            return 0;
        }

        // Find an amount/balance column and count rows > 0
        foreach ([
            'amount_due_cents', 'outstanding_cents', 'total_due_cents',
            'amount_due', 'balance_due', 'total_due',
            'total_price',
        ] as $col) {
            if (Schema::hasColumn('jobs', $col)) {
                $threshold = $this->isCentsColumn($col) ? 0 : 0.0;
                try {
                    return (int) DB::table('jobs')->where($col, '>', $threshold)->count();
                } catch (\Throwable $e) {
                    // Try next candidate
                }
            }
        }

        return 0;
    }

    public function utilizationPercent(): int
    {
        // Placeholder until you track availability; adjust as you wire real data.
        return 72;
    }

    public function fleetOutCount(): int
    {
        // Placeholder
        return 9;
    }

    public function fleetTotal(): int
    {
        // Placeholder
        return 12;
    }

    /* =========================
     *  Helpers
     * ========================= */

    private function formatNzd(float $amount): string
    {
        return 'NZ$ ' . number_format($amount, 2);
    }

    private function isCentsColumn(string $column): bool
    {
        return str_contains($column, 'cents');
    }

    private function tableExists(string $table): bool
    {
        try {
            return Schema::hasTable($table);
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Return the first column that exists on $table from the list.
     */
    private function firstExistingColumn(string $table, array $candidates): ?string
    {
        foreach ($candidates as $col) {
            try {
                if (Schema::hasColumn($table, $col)) {
                    return $col;
                }
            } catch (\Throwable $e) {
                // ignore and continue
            }
        }
        return null;
    }

    /**
     * Sum a column, optionally constrained to a created_at range if the table has created_at.
     */
    private function safeSum(string $table, string $column, ?Carbon $start = null, ?Carbon $end = null): float
    {
        try {
            $query = DB::table($table);

            // Only apply the date window if this table actually has created_at
            if ($start && $end && Schema::hasColumn($table, 'created_at')) {
                $query->whereBetween('created_at', [$start, $end]);
            }

            $val = $query->sum($column);
            return is_numeric($val) ? (float) $val : 0.0;
        } catch (\Throwable $e) {
            return 0.0;
        }
    }

    /**
     * Start/end of the current calendar month.
     */
    private function currentMonthRange(): array
    {
        $start = now()->copy()->startOfMonth();
        $end   = now()->copy()->endOfMonth();
        return [$start, $end];
    }
}
