<?php

namespace App\Http\Controllers\Api\Report;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class StockMovementController extends Controller
{
    public function getStockPositionReport(Request $request)
    {
        try {
            $user = auth()->user();
            $startDate = $request->query('start_date', Carbon::today()->startOfMonth()->toDateString());
            $endDate = $request->query('end_date', Carbon::today()->toDateString());
            
            // Keamanan Isolasi Cabang & Parameter Pencarian
            $branchId = ($user->role === 'owner' || $user->role === 'super_admin') ? ($request->query('branch_id') ?? 'all') : $user->branch_id;
            $search = $request->query('search');
            $productType = $request->query('product_type', 'all');

            // Resolving Nama Cabang untuk Laporan
            $branchNameReport = 'SEMUA GUDANG (KONSOLIDASI)';
            if ($branchId !== 'all') {
                $branchNameReport = DB::table('branches')->where('id', $branchId)->value('name') ?? 'Gudang';
            }

            // 1. Tarik Data Master & Stok Agregat (Booked & Bad Stock)
            $query = DB::table('products as p')
                ->leftJoin('product_stocks as ps', function($join) use ($branchId) {
                    $join->on('p.id', '=', 'ps.product_id');
                    if ($branchId !== 'all') {
                        $join->where('ps.branch_id', '=', $branchId);
                    }
                })
                ->select(
                    'p.id',
                    'p.sku',
                    'p.name as product_name',
                    'p.variant_name', 
                    'p.product_type', 
                    'p.unit',
                    'p.buy_unit',
                    'p.conversion_factor',
                    'p.unit_3_name',
                    'p.conversion_3_qty',
                    'p.unit_4_name',
                    'p.conversion_4_qty',
                    DB::raw('COALESCE(SUM(ps.reserved_qty), 0) as booked_stock'),
                    DB::raw('COALESCE(SUM(ps.bs_quantity), 0) as bad_stock')
                )
                ->where('p.is_active', 1)
                ->whereNotExists(function ($q) {
                    // Sembunyikan produk Parent / Induk agar tidak muncul di laporan
                    $q->select(DB::raw(1))
                      ->from('products as p_child')
                      ->whereRaw("p_child.variant_group_id = CONCAT('VAR-', p.id)");
                });

            // --- EKSEKUSI FILTER TIPE PRODUK ---
            if ($productType !== 'all') {
                $query->where('p.product_type', $productType);
            }

            if (!empty($search)) {
                $query->where(function($q) use ($search) {
                    $q->where('p.name', 'like', "%{$search}%")
                      ->orWhere('p.sku', 'like', "%{$search}%")
                      ->orWhere('p.variant_name', 'like', "%{$search}%");
                });
            }

            // Grouping mutlak untuk mencegah duplikasi data jika ada multi-cabang
            $query->groupBy(
                'p.id', 'p.sku', 'p.name', 'p.variant_name', 'p.product_type', 
                'p.unit', 'p.buy_unit', 'p.conversion_factor',
                'p.unit_3_name', 'p.conversion_3_qty', 'p.unit_4_name', 'p.conversion_4_qty'
            );

            $data = $query->orderBy('p.name', 'asc')->get();

            // 2. Proses Perhitungan Laporan (FORWARD CALCULATION ENGINE)
            $reportData = $data->map(function ($item) use ($startDate, $endDate, $branchId, $branchNameReport) {
                
                // A. SALDO AWAL MURNI (Transaksi fisik masuk/keluar biasa SEBELUM start_date)
                $openingBeforeDate = DB::table('stock_transactions')
                    ->where('product_id', $item->id)
                    ->when($branchId !== 'all', fn($q) => $q->where('branch_id', $branchId))
                    ->where('created_at', '<', $startDate . ' 00:00:00')
                    ->where('type', '!=', 'initial')
                    // PERBAIKAN: Hitung Saldo Awal berdasarkan Tipe Transaksi untuk menjamin akurasi
                    ->sum(DB::raw("
                        CASE 
                            WHEN type IN ('sales_delivery', 'return_out', 'fabrication_out') THEN -ABS(actual_quantity)
                            WHEN type IN ('purchase_receipt', 'return_in', 'fabrication_in') THEN ABS(actual_quantity)
                            ELSE actual_quantity 
                        END
                    "));

                // B. TOTAL INPUT SALDO AWAL (ALL TIME)
                $totalInitialStock = DB::table('stock_transactions')
                    ->where('product_id', $item->id)
                    ->when($branchId !== 'all', fn($q) => $q->where('branch_id', $branchId))
                    ->where('type', 'initial')
                    ->sum('actual_quantity');

                // C. MUTASI PERIODE (Masuk & Keluar reguler di dalam rentang tanggal terpilih)
                $periodTransactions = DB::table('stock_transactions')
                    ->where('product_id', $item->id)
                    ->when($branchId !== 'all', fn($q) => $q->where('branch_id', $branchId))
                    ->whereBetween('created_at', [$startDate . ' 00:00:00', $endDate . ' 23:59:59'])
                    // PERBAIKAN: Menambahkan 'fabrication_in' dan 'fabrication_out' ke dalam whitelist transaksi
                    ->whereIn('type', [
                        'purchase_receipt', 'sales_delivery', 'stock_adjustment', 'return_in', 'return_out', 
                        'fabrication_in', 'fabrication_out'
                    ]) 
                    ->select(
                        // MASUK (+) MURNI 
                        DB::raw("SUM(CASE WHEN type IN ('purchase_receipt', 'return_in', 'fabrication_in') OR (type = 'stock_adjustment' AND actual_quantity > 0) THEN ABS(actual_quantity) ELSE 0 END) as stock_in"),
                        // KELUAR (-) MURNI 
                        DB::raw("SUM(CASE WHEN type IN ('sales_delivery', 'return_out', 'fabrication_out') OR (type = 'stock_adjustment' AND actual_quantity < 0) THEN ABS(actual_quantity) ELSE 0 END) as stock_out")
                    )
                    ->first();

                // GABUNGKAN SALDO AWAL: Riwayat Sebelum + Setup Awal
                $openingBalance = (float)$openingBeforeDate + (float)$totalInitialStock;
                $stockIn = (float)($periodTransactions->stock_in ?? 0);
                $stockOut = (float)($periodTransactions->stock_out ?? 0);

                // D. SALDO AKHIR
                $closingBalance = $openingBalance + $stockIn - $stockOut;

                // Penggabungan Nama Produk Induk dengan Nama Varian
                $finalProductName = !empty($item->variant_name) 
                    ? $item->product_name . ' - ' . $item->variant_name 
                    : $item->product_name;

                return [
                    'id' => $item->id,
                    'sku' => $item->sku,
                    'product_name' => strtoupper($finalProductName),
                    'product_type' => $item->product_type ?? 'trading', 
                    'unit' => $item->unit,
                    'buy_unit' => $item->buy_unit,
                    'conversion_factor' => (float)$item->conversion_factor,
                    'unit_3_name' => $item->unit_3_name,
                    'conversion_3_qty' => (float)$item->conversion_3_qty,
                    'unit_4_name' => $item->unit_4_name,
                    'conversion_4_qty' => (float)$item->conversion_4_qty,
                    'branch_name' => strtoupper($branchNameReport),
                    'opening_balance' => (float)$openingBalance,
                    'stock_in' => $stockIn,
                    'stock_out' => $stockOut,
                    'booked_stock' => (float)$item->booked_stock,
                    'bad_stock' => (float)$item->bad_stock, 
                    'closing_balance' => $closingBalance 
                ];
            });

            return response()->json([
                'success' => true,
                'data' => $reportData,
                'summary' => [
                    'total_items' => $reportData->count(),
                    'total_in' => $reportData->sum('stock_in'),
                    'total_out' => $reportData->sum('stock_out'),
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
}