<?php

namespace Modules\IntegrationHub\Services;

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

class ODataExportService
{
    public function availableDatasets(): array
    {
        return array_values(array_filter(
            config('integrationhub.datasets', []),
            fn ($d) => $d !== 'executive_cockpit'
        ));
    }

    public function dataset(int $businessId, string $dataset, array $filters = []): array
    {
        $rows = match ($dataset) {
            'sales_summary' => $this->salesSummary($businessId, $filters),
            'inventory_snapshot' => $this->inventorySnapshot($businessId, $filters),
            'production_kpis' => $this->productionKpis($businessId, $filters),
            'maintenance_kpis' => $this->maintenanceKpis($businessId, $filters),
            'qms_kpis' => $this->qmsKpis($businessId, $filters),
            default => throw new \InvalidArgumentException("Unknown dataset: {$dataset}"),
        };

        $context = url("/integrationhub/api/odata/{$dataset}");

        return [
            '@odata.context' => $context,
            '@odata.count' => count($rows),
            'value' => $rows,
        ];
    }

    protected function salesSummary(int $businessId, array $filters): array
    {
        if (! Schema::hasTable('transactions')) {
            return [];
        }

        [$start, $end] = $this->dateRange($filters);

        $query = DB::table('transactions')
            ->select(
                DB::raw('DATE(transaction_date) as sale_date'),
                DB::raw('COUNT(*) as transaction_count'),
                DB::raw('SUM(final_total) as total_sales'),
                DB::raw('SUM(tax_amount) as total_tax')
            )
            ->where('business_id', $businessId)
            ->where('type', 'sell')
            ->where('status', 'final')
            ->whereBetween('transaction_date', [$start, $end])
            ->groupBy(DB::raw('DATE(transaction_date)'))
            ->orderBy('sale_date');

        if (! empty($filters['location_id'])) {
            $query->where('location_id', (int) $filters['location_id']);
        }

        return $query->get()->map(fn ($row) => [
            'sale_date' => $row->sale_date,
            'transaction_count' => (int) $row->transaction_count,
            'total_sales' => (float) $row->total_sales,
            'total_tax' => (float) $row->total_tax,
        ])->all();
    }

    protected function inventorySnapshot(int $businessId, array $filters): array
    {
        if (! Schema::hasTable('variation_location_details') || ! Schema::hasTable('products')) {
            return [];
        }

        $query = DB::table('variation_location_details as vld')
            ->join('product_variations as pv', 'pv.id', '=', 'vld.product_variation_id')
            ->join('products as p', 'p.id', '=', 'pv.product_id')
            ->leftJoin('variations as v', 'v.id', '=', 'vld.variation_id')
            ->select(
                'p.id as product_id',
                'p.name as product_name',
                'p.sku',
                'vld.location_id',
                'vld.qty_available',
                'p.alert_quantity'
            )
            ->where('p.business_id', $businessId)
            ->where('p.is_inactive', 0);

        if (! empty($filters['location_id'])) {
            $query->where('vld.location_id', (int) $filters['location_id']);
        }

        if (! empty($filters['low_stock_only'])) {
            $query->whereColumn('vld.qty_available', '<=', 'p.alert_quantity');
        }

        return $query->limit($this->pageLimit($filters))->get()->map(fn ($row) => [
            'product_id' => (int) $row->product_id,
            'product_name' => $row->product_name,
            'sku' => $row->sku,
            'location_id' => (int) $row->location_id,
            'qty_available' => (float) $row->qty_available,
            'alert_quantity' => (float) $row->alert_quantity,
            'is_low_stock' => (float) $row->qty_available <= (float) $row->alert_quantity,
        ])->all();
    }

    protected function productionKpis(int $businessId, array $filters): array
    {
        if (! Schema::hasTable('mfg_recipes') && ! Schema::hasTable('transactions')) {
            return [];
        }

        $kpis = [
            'period_start' => $this->dateRange($filters)[0]->toDateString(),
            'period_end' => $this->dateRange($filters)[1]->toDateString(),
            'production_orders' => 0,
            'completed_orders' => 0,
            'total_output_qty' => 0.0,
            'active_recipes' => 0,
        ];

        if (Schema::hasTable('mfg_recipes')) {
            $kpis['active_recipes'] = (int) DB::table('mfg_recipes')
                ->where('business_id', $businessId)
                ->count();
        }

        if (Schema::hasTable('transactions')) {
            [$start, $end] = $this->dateRange($filters);

            $productionQuery = DB::table('transactions')
                ->where('business_id', $businessId)
                ->where('type', 'production_purchase')
                ->whereBetween('transaction_date', [$start, $end]);

            $kpis['production_orders'] = (int) (clone $productionQuery)->count();
            $kpis['completed_orders'] = (int) (clone $productionQuery)->where('status', 'received')->count();
            $kpis['total_output_qty'] = (float) (clone $productionQuery)->sum('final_total');
        }

        return [$kpis];
    }

    protected function maintenanceKpis(int $businessId, array $filters): array
    {
        if (! Schema::hasTable('mt_work_orders')) {
            return [[
                'module_available' => false,
                'open_work_orders' => 0,
                'overdue_work_orders' => 0,
                'completed_this_period' => 0,
            ]];
        }

        [$start, $end] = $this->dateRange($filters);

        $open = DB::table('mt_work_orders')
            ->where('business_id', $businessId)
            ->whereIn('status', ['open', 'in_progress', 'pending'])
            ->count();

        $overdue = DB::table('mt_work_orders')
            ->where('business_id', $businessId)
            ->whereIn('status', ['open', 'in_progress', 'pending'])
            ->whereNotNull('due_date')
            ->where('due_date', '<', now())
            ->count();

        $completed = DB::table('mt_work_orders')
            ->where('business_id', $businessId)
            ->where('status', 'completed')
            ->whereBetween('updated_at', [$start, $end])
            ->count();

        return [[
            'module_available' => true,
            'open_work_orders' => (int) $open,
            'overdue_work_orders' => (int) $overdue,
            'completed_this_period' => (int) $completed,
            'period_start' => $start->toDateString(),
            'period_end' => $end->toDateString(),
        ]];
    }

    protected function qmsKpis(int $businessId, array $filters): array
    {
        if (! Schema::hasTable('qm_ncr_reports')) {
            return [[
                'module_available' => false,
                'open_ncrs' => 0,
                'open_capas' => 0,
                'overdue_calibrations' => 0,
            ]];
        }

        [$start, $end] = $this->dateRange($filters);

        $openNcrs = DB::table('qm_ncr_reports')
            ->where('business_id', $businessId)
            ->whereNotIn('status', ['closed', 'resolved'])
            ->count();

        $openCapas = Schema::hasTable('qm_capa_actions')
            ? DB::table('qm_capa_actions')
                ->where('business_id', $businessId)
                ->whereNotIn('status', ['closed', 'completed'])
                ->count()
            : 0;

        $overdueCalibrations = Schema::hasTable('qm_calibration_assets')
            ? DB::table('qm_calibration_assets')
                ->where('business_id', $businessId)
                ->whereNotNull('next_due_at')
                ->where('next_due_at', '<', now())
                ->count()
            : 0;

        $newNcrs = DB::table('qm_ncr_reports')
            ->where('business_id', $businessId)
            ->whereBetween('created_at', [$start, $end])
            ->count();

        return [[
            'module_available' => true,
            'open_ncrs' => (int) $openNcrs,
            'open_capas' => (int) $openCapas,
            'overdue_calibrations' => (int) $overdueCalibrations,
            'new_ncrs_this_period' => (int) $newNcrs,
            'period_start' => $start->toDateString(),
            'period_end' => $end->toDateString(),
        ]];
    }

  /**
     * @return array{0: Carbon, 1: Carbon}
     */
    protected function dateRange(array $filters): array
    {
        $start = ! empty($filters['start_date'])
            ? Carbon::parse($filters['start_date'])->startOfDay()
            : now()->subDays(30)->startOfDay();

        $end = ! empty($filters['end_date'])
            ? Carbon::parse($filters['end_date'])->endOfDay()
            : now()->endOfDay();

        return [$start, $end];
    }

    protected function pageLimit(array $filters): int
    {
        $limit = (int) ($filters['$top'] ?? config('integrationhub.odata.default_page_size', 100));
        $max = (int) config('integrationhub.odata.max_page_size', 500);

        return min(max($limit, 1), $max);
    }
}