<?php

namespace Modules\WhatsApp\Console;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Modules\WhatsApp\Entities\WaSetting;
use Modules\WhatsApp\Entities\Services\WaSender;

/**
 * WhatsApps a daily low-stock summary to each business that turned the alert on
 * (Settings -> Inventory alerts) and set a recipient number. "Low" = an in-stock
 * variation/location whose qty_available has fallen below that product's
 * alert_quantity (its reorder level). One message per business per day (deduped).
 *
 * Opt-in; a no-op when disabled, when no recipient is set, or when nothing is low.
 * Schedule: daily (e.g. 08:00).
 */
class SendLowStockAlerts extends Command
{
    protected $signature = 'whatsapp:low-stock-alerts {--business= : Only this business id} {--limit=20 : Max line items per message}';
    protected $description = 'Send a WhatsApp low-stock summary to opted-in businesses.';

    public function handle(): int
    {
        if (!Schema::hasTable('wa_settings') || !Schema::hasTable('variation_location_details')) {
            $this->info('Inventory or WhatsApp tables missing — nothing to do.');
            return self::SUCCESS;
        }

        $only  = (int) $this->option('business');
        $limit = max(1, min(50, (int) $this->option('limit')));

        $businessIds = DB::table('wa_settings')
            ->where('key', 'low_stock_enabled')->where('value', '1')
            ->pluck('business_id')->map(fn ($b) => (int) $b)->unique();

        $sent = 0;
        foreach ($businessIds as $bid) {
            if ($only && $only !== $bid) {
                continue;
            }
            if (WaSetting::get($bid, 'enabled', '1') !== '1') {
                continue; // WhatsApp master switch off
            }

            $to = trim((string) WaSetting::get($bid, 'low_stock_number', ''));
            if ($to === '') {
                continue;
            }

            $rows = $this->lowStockBase($bid)
                ->leftJoin('variations as v', 'v.id', '=', 'vld.variation_id')
                ->leftJoin('business_locations as bl', 'bl.id', '=', 'vld.location_id')
                ->orderBy('vld.qty_available')
                ->limit($limit)
                ->get([
                    DB::raw('IF(v.name = "DUMMY" OR v.name IS NULL, p.name, CONCAT(p.name, " - ", v.name)) as product_name'),
                    'vld.qty_available as qty',
                    'p.alert_quantity as alert',
                    'bl.name as location',
                ]);

            if ($rows->isEmpty()) {
                continue;
            }

            $total = $this->lowStockBase($bid)->count();
            (new WaSender())->send([
                'business_id' => $bid,
                'to'          => $to,
                'body'        => $this->compose($bid, $rows, $total),
                'source'      => 'low_stock',
                'dedupe_key'  => 'low_stock:' . $bid . ':' . now()->toDateString(),
            ]);
            $sent++;
        }

        $this->info("Queued low-stock alert(s) for {$sent} business(es).");
        return self::SUCCESS;
    }

    /**
     * Base query: in-stock variation/location rows below the product's reorder
     * level. Returns a fresh builder each call (so it can be counted and listed).
     */
    private function lowStockBase(int $businessId)
    {
        return DB::table('variation_location_details as vld')
            ->join('products as p', 'p.id', '=', 'vld.product_id')
            ->where('p.business_id', $businessId)
            ->where('p.enable_stock', 1)
            ->where('p.not_for_selling', 0)
            ->where('p.is_inactive', 0)
            ->whereNotNull('p.alert_quantity')
            ->where('p.alert_quantity', '>', 0)
            ->whereColumn('vld.qty_available', '<', 'p.alert_quantity');
    }

    private function compose(int $businessId, $rows, int $total): string
    {
        $name = (string) (DB::table('business')->where('id', $businessId)->value('name') ?? 'your business');

        $lines = $rows->map(function ($r) {
            $qty   = $this->num($r->qty);
            $alert = $this->num($r->alert);
            $loc   = $r->location ? ' @ ' . $r->location : '';
            return "- {$r->product_name}: {$qty} left (reorder {$alert}){$loc}";
        })->implode("\n");

        $more   = $total - $rows->count();
        $footer = $more > 0 ? "\n…and {$more} more item(s)." : '';

        return "⚠️ Low stock at {$name} — {$total} item(s) at/below reorder level:\n" . $lines . $footer;
    }

    /** Trim trailing zeros from a decimal qty (12.0000 -> 12, 2.5000 -> 2.5). */
    private function num($value): string
    {
        return rtrim(rtrim(number_format((float) $value, 4, '.', ''), '0'), '.');
    }
}
