<?php

namespace App\Http\Controllers\Report;

use App\Http\Controllers\Controller;
use App\Models\Sale;
use App\Models\SaleDetail;
use App\Models\Purchase;
use App\Models\Branch;
use Illuminate\Http\Request;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Barryvdh\DomPDF\Facade\Pdf;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class ProfitLossController extends Controller
{
    public function __construct()
    {
        $this->middleware(['permission:profit.index'])->only('index','exportPdf','exportExcel','exportProductAnalysisPdf','exportProductAnalysisExcel');
    }

    /**
     * Menampilkan halaman laporan Laba Rugi
     *
     * Laporan ini menghitung:
     * - Laba Kotor = Pendapatan - HPP
     * - Laba Bersih = Pendapatan - Diskon + Pajak - HPP
     * - Margin Laba = (Laba Bersih / Pendapatan) × 100%
     */
    public function index(Request $request)
    {
        // Setup data dasar
        $title = 'profit_loss';
        $branches = Branch::all(); // Ambil semua cabang untuk dropdown filter

        /* ===================================================================
         * BAGIAN 1: AMBIL PARAMETER FILTER DARI USER
         * =================================================================== */
        // Default periode: bulan ini (dari tanggal 1 sampai akhir bulan)
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth()->format('Y-m-d'));
        $branchId = $request->get('branch_id'); // Filter cabang (opsional)

        /* ===================================================================
         * BAGIAN 2: QUERY DATA PENJUALAN (SALES)
         * =================================================================== */
        // Ambil semua transaksi penjualan dalam periode yang dipilih
        $salesQuery = Sale::whereBetween('sale_date', [$startDate, $endDate])
            ->where('status', 'completed'); // Hanya transaksi yang sudah selesai

        // Jika user memilih cabang tertentu, filter berdasarkan cabang
        if ($branchId) {
            $salesQuery->where('branch_id', $branchId);
        }

        $sales = $salesQuery->get(); // Eksekusi query

        /* ===================================================================
         * BAGIAN 3: HITUNG METRIK PENJUALAN
         * =================================================================== */
        // Total Pendapatan = jumlah semua total_amount dari penjualan
        $totalRevenue = $sales->sum('total_amount');

        // Total Diskon = jumlah semua diskon yang diberikan
        $totalSalesDiscount = $sales->sum('discount');

        // Total Pajak = jumlah semua pajak yang dikenakan
        $totalSalesTax = $sales->sum('tax');

        /* ===================================================================
         * BAGIAN 4: HITUNG HARGA POKOK PENJUALAN (HPP/COGS)
         * =================================================================== */
        // HPP = Total biaya pembelian barang yang sudah terjual
        // Rumus: SUM(quantity × purchase_price)
        $saleDetailsQuery = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
            $query->whereBetween('sale_date', [$startDate, $endDate])
                  ->where('status', 'completed');
            if ($branchId) {
                $query->where('branch_id', $branchId);
            }
        });

        // Kalikan jumlah terjual dengan harga beli, lalu jumlahkan semua
        $totalCOGS = $saleDetailsQuery->sum(DB::raw('quantity * purchase_price'));

        /* ===================================================================
         * BAGIAN 5: HITUNG LABA KOTOR (GROSS PROFIT)
         * =================================================================== */
        // Laba Kotor = Pendapatan - HPP
        // Ini adalah laba sebelum dikurangi biaya operasional
        $grossProfit = $totalRevenue - $totalCOGS;

        /* ===================================================================
         * BAGIAN 6: QUERY DATA PEMBELIAN (PURCHASES)
         * =================================================================== */
        // Ambil data pembelian untuk referensi (saat ini belum digunakan dalam kalkulasi)
        $purchasesQuery = Purchase::whereBetween('purchase_date', [$startDate, $endDate])
            ->where('status', 'completed');

        if ($branchId) {
            $purchasesQuery->where('branch_id', $branchId);
        }

        $purchases = $purchasesQuery->get();
        $totalPurchases = $purchases->sum('total_amount');

        /* ===================================================================
         * BAGIAN 7: HITUNG LABA BERSIH (NET PROFIT)
         * =================================================================== */
        // Laba Bersih = Pendapatan - Diskon + Pajak - HPP
        // Catatan: Belum termasuk biaya operasional (gaji, sewa, listrik, dll)
        $netProfit = $totalRevenue - $totalSalesDiscount + $totalSalesTax - $totalCOGS;

        /* ===================================================================
         * BAGIAN 8: HITUNG MARGIN LABA (PROFIT MARGIN)
         * =================================================================== */
        // Margin Laba = (Laba Bersih ÷ Pendapatan) × 100%
        // Menunjukkan berapa persen keuntungan dari total penjualan
        $profitMargin = $totalRevenue > 0 ? ($netProfit / $totalRevenue) * 100 : 0;

        /* ===================================================================
         * BAGIAN 9: DATA UNTUK GRAFIK - PENJUALAN HARIAN
         * =================================================================== */
        // Ambil total penjualan per hari untuk ditampilkan di chart
        // Hasilnya: [{date: '2025-01-01', total: 5000000}, {date: '2025-01-02', total: 3000000}, ...]
        $dailySales = Sale::whereBetween('sale_date', [$startDate, $endDate])
            ->where('status', 'completed')
            ->when($branchId, function($query) use ($branchId) {
                return $query->where('branch_id', $branchId);
            })
            ->select(
                DB::raw('DATE(sale_date) as date'),      // Tanggal
                DB::raw('SUM(total_amount) as total')    // Total penjualan per hari
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        /* ===================================================================
         * BAGIAN 10: DATA UNTUK GRAFIK - PEMBELIAN HARIAN
         * =================================================================== */
        // Sama seperti dailySales, tapi untuk pembelian
        $dailyPurchases = Purchase::whereBetween('purchase_date', [$startDate, $endDate])
            ->where('status', 'completed')
            ->when($branchId, function($query) use ($branchId) {
                return $query->where('branch_id', $branchId);
            })
            ->select(
                DB::raw('DATE(purchase_date) as date'),  // Tanggal
                DB::raw('SUM(total_amount) as total')    // Total pembelian per hari
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        /* ===================================================================
         * BAGIAN 11: TOP 10 PRODUK TERLARIS
         * =================================================================== */
        // Produk dengan laba paling tinggi (sorted by profit, bukan quantity)
        $topProducts = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
                $query->whereBetween('sale_date', [$startDate, $endDate])
                      ->where('status', 'completed');
                if ($branchId) {
                    $query->where('branch_id', $branchId);
                }
            })
            ->select(
                'product_id',
                DB::raw('SUM(quantity) as total_qty'),                                              // Total terjual
                DB::raw('SUM(quantity * selling_price - discount) as total_revenue'),              // Total pendapatan
                DB::raw('SUM(quantity * (selling_price - purchase_price) - discount) as total_profit') // Total laba
            )
            ->groupBy('product_id')
            ->orderBy('total_profit', 'desc') // Urutkan dari laba tertinggi
            ->limit(10)                       // Ambil 10 teratas
            ->with('product')                 // Load data produk
            ->get();

        /* ===================================================================
         * BAGIAN 12: DETAIL LABA RUGI PER PRODUK
         * =================================================================== */
        // Data lengkap untuk tabel "Laporan Laba Rugi Per Produk"
        $productDetails = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
                $query->whereBetween('sale_date', [$startDate, $endDate])
                      ->where('status', 'completed');
                if ($branchId) {
                    $query->where('branch_id', $branchId);
                }
            })
            ->select(
                'product_id',
                DB::raw('SUM(quantity) as total_qty'),                                                       // Jumlah terjual
                DB::raw('SUM(quantity * selling_price) as total_jual'),                                     // Total penjualan
                DB::raw('SUM(quantity * purchase_price) as harga_pokok'),                                   // HPP
                DB::raw('SUM(discount) as potongan'),                                                       // Diskon
                DB::raw('SUM(quantity * (selling_price - purchase_price)) as laba_kotor'),                 // Laba Kotor
                DB::raw('(SUM(quantity * (selling_price - purchase_price)) / SUM(quantity * selling_price) * 100) as laba_persen') // Margin %
            )
            ->groupBy('product_id')
            ->with('product.brand') // Load data produk dan brand
            ->get();

        /* ===================================================================
         * BAGIAN 13: TOP 10 PELANGGAN
         * =================================================================== */
        // Pelanggan yang paling banyak berbelanja (sorted by total spending)
        $topCustomers = Sale::whereBetween('sale_date', [$startDate, $endDate])
            ->where('status', 'completed')
            ->when($branchId, function($query) use ($branchId) {
                return $query->where('branch_id', $branchId);
            })
            ->select(
                'customer_id',
                DB::raw('COUNT(*) as total_transactions'),        // Total transaksi
                DB::raw('SUM(total_amount) as total_spending'),   // Total belanja
                DB::raw('AVG(total_amount) as avg_spending')      // Rata-rata belanja per transaksi
            )
            ->whereNotNull('customer_id') // Hanya yang punya customer_id (tidak termasuk guest)
            ->groupBy('customer_id')
            ->orderBy('total_spending', 'desc') // Urutkan dari yang paling banyak belanja
            ->limit(10)
            ->with('customer') // Load data customer
            ->get();

        /* ===================================================================
         * BAGIAN 14: KIRIM DATA KE VIEW
         * =================================================================== */
        return view('report.profit-loss.index', compact(
            'title',
            'branches',
            'startDate',
            'endDate',
            'branchId',
            'totalRevenue',        // Total Penjualan
            'totalCOGS',           // Harga Pokok Penjualan (HPP)
            'grossProfit',         // Laba Kotor
            'netProfit',           // Laba Bersih
            'profitMargin',        // Margin Laba (%)
            'totalSalesDiscount',  // Total Diskon
            'totalSalesTax',       // Total Pajak
            'totalPurchases',      // Total Pembelian
            'dailySales',          // Data penjualan harian (untuk chart)
            'dailyPurchases',      // Data pembelian harian (untuk chart)
            'topProducts',         // 10 produk terlaris
            'topCustomers',        // 10 pelanggan terbaik
            'productDetails',      // Detail laba rugi per produk
            'sales'                // Data penjualan mentah
        ));
    }

    public function exportPdf(Request $request)
    {
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth()->format('Y-m-d'));
        $branchId = $request->get('branch_id');

        $branch = $branchId ? Branch::find($branchId) : null;

        // Get all data (reuse logic from index)
        $salesQuery = Sale::whereBetween('sale_date', [$startDate, $endDate])
            ->where('status', 'completed');
        if ($branchId) {
            $salesQuery->where('branch_id', $branchId);
        }
        $sales = $salesQuery->get();
        $totalRevenue = $sales->sum('total_amount');
        $totalSalesDiscount = $sales->sum('discount');
        $totalSalesTax = $sales->sum('tax');

        $saleDetailsQuery = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
            $query->whereBetween('sale_date', [$startDate, $endDate])
                  ->where('status', 'completed');
            if ($branchId) {
                $query->where('branch_id', $branchId);
            }
        });
        $totalCOGS = $saleDetailsQuery->sum(DB::raw('quantity * purchase_price'));
        $grossProfit = $totalRevenue - $totalCOGS;
        $netProfit = $totalRevenue - $totalSalesDiscount + $totalSalesTax - $totalCOGS;
        $profitMargin = $totalRevenue > 0 ? ($netProfit / $totalRevenue) * 100 : 0;

        $pdf = Pdf::loadView('report.profit-loss.pdf', compact(
            'startDate',
            'endDate',
            'branch',
            'totalRevenue',
            'totalCOGS',
            'grossProfit',
            'netProfit',
            'profitMargin',
            'totalSalesDiscount',
            'totalSalesTax'
        ));

        return $pdf->stream('Laporan-Laba-Rugi-' . date('Ymd') . '.pdf');
    }

    public function exportExcel(Request $request)
    {
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth()->format('Y-m-d'));
        $branchId = $request->get('branch_id');

        $branch = $branchId ? Branch::find($branchId) : null;

        // Get sales data
        $salesQuery = Sale::whereBetween('sale_date', [$startDate, $endDate])
            ->where('status', 'completed');
        if ($branchId) {
            $salesQuery->where('branch_id', $branchId);
        }
        $sales = $salesQuery->get();

        $totalRevenue = $sales->sum('total_amount');
        $totalSalesDiscount = $sales->sum('discount');
        $totalSalesTax = $sales->sum('tax');

        $saleDetailsQuery = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
            $query->whereBetween('sale_date', [$startDate, $endDate])
                  ->where('status', 'completed');
            if ($branchId) {
                $query->where('branch_id', $branchId);
            }
        });
        $totalCOGS = $saleDetailsQuery->sum(DB::raw('quantity * purchase_price'));
        $grossProfit = $totalRevenue - $totalCOGS;
        $netProfit = $totalRevenue - $totalSalesDiscount + $totalSalesTax - $totalCOGS;
        $profitMargin = $totalRevenue > 0 ? ($netProfit / $totalRevenue) * 100 : 0;
        $totalPendapatanBersih = $totalRevenue - $totalSalesDiscount + $totalSalesTax;

        // Create Excel file using PhpSpreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set document properties
        $spreadsheet->getProperties()
            ->setCreator('UMI CELL')
            ->setTitle('Laporan Laba Rugi')
            ->setSubject('Laporan Laba Rugi')
            ->setDescription('Laporan Laba Rugi periode ' . $startDate . ' - ' . $endDate);

        // Company Header
        $sheet->setCellValue('A1', 'UMI CELL');
        $sheet->mergeCells('A1:C1');
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A2', 'Jl. Raya Semarang-Boja, Jatisari, Kec. Mijen, Kota Semarang Jawa Tengah 50275');
        $sheet->mergeCells('A2:C2');
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A2')->getFont()->setSize(9);

        $sheet->setCellValue('A3', 'Telp: 0882005564342');
        $sheet->mergeCells('A3:C3');
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A3')->getFont()->setSize(9);

        // Report Title
        $sheet->setCellValue('A5', 'LAPORAN LABA RUGI');
        $sheet->mergeCells('A5:C5');
        $sheet->getStyle('A5')->getFont()->setBold(true)->setSize(14);
        $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A6', 'Periode: ' . date('d M Y', strtotime($startDate)) . ' - ' . date('d M Y', strtotime($endDate)));
        $sheet->mergeCells('A6:C6');
        $sheet->getStyle('A6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A7', 'Cabang: ' . ($branch ? $branch->name : 'Semua Cabang'));
        $sheet->mergeCells('A7:C7');
        $sheet->getStyle('A7')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // Laporan Laba Rugi Table
        $row = 9;

        // PENDAPATAN Section
        $sheet->setCellValue('A' . $row, 'PENDAPATAN');
        $sheet->mergeCells('A' . $row . ':C' . $row);
        $sheet->getStyle('A' . $row . ':C' . $row)->getFont()->setBold(true);
        $sheet->getStyle('A' . $row . ':C' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFF0F0F0');
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Penjualan
        $sheet->setCellValue('A' . $row, '    Penjualan');
        $sheet->setCellValue('C' . $row, 'Rp ' . number_format($totalRevenue, 0, ',', '.'));
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Diskon Penjualan
        $sheet->setCellValue('A' . $row, '    Diskon Penjualan');
        $sheet->setCellValue('C' . $row, '(Rp ' . number_format($totalSalesDiscount, 0, ',', '.') . ')');
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Pajak
        $sheet->setCellValue('A' . $row, '    Pajak');
        $sheet->setCellValue('C' . $row, 'Rp ' . number_format($totalSalesTax, 0, ',', '.'));
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Total Pendapatan Bersih
        $sheet->setCellValue('A' . $row, 'Total Pendapatan Bersih');
        $sheet->setCellValue('C' . $row, 'Rp ' . number_format($totalPendapatanBersih, 0, ',', '.'));
        $sheet->getStyle('A' . $row . ':C' . $row)->getFont()->setBold(true);
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // HPP Section
        $sheet->setCellValue('A' . $row, 'HARGA POKOK PENJUALAN (HPP)');
        $sheet->mergeCells('A' . $row . ':C' . $row);
        $sheet->getStyle('A' . $row . ':C' . $row)->getFont()->setBold(true);
        $sheet->getStyle('A' . $row . ':C' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFF0F0F0');
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        $sheet->setCellValue('A' . $row, '    HPP');
        $sheet->setCellValue('C' . $row, 'Rp ' . number_format($totalCOGS, 0, ',', '.'));
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Laba Kotor
        $sheet->setCellValue('A' . $row, 'LABA KOTOR');
        $sheet->setCellValue('C' . $row, 'Rp ' . number_format($grossProfit, 0, ',', '.'));
        $sheet->getStyle('A' . $row . ':C' . $row)->getFont()->setBold(true);
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFD4EDDA');
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Laba Bersih
        $sheet->setCellValue('A' . $row, 'LABA BERSIH');
        $sheet->setCellValue('C' . $row, 'Rp ' . number_format($netProfit, 0, ',', '.'));
        $sheet->getStyle('A' . $row . ':C' . $row)->getFont()->setBold(true);
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($netProfit >= 0 ? 'FFD4EDDA' : 'FFF8D7DA');
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Margin Laba
        $sheet->setCellValue('A' . $row, 'MARGIN LABA');
        $sheet->setCellValue('C' . $row, number_format($profitMargin, 2) . '%');
        $sheet->getStyle('A' . $row . ':C' . $row)->getFont()->setBold(true);
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':C' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Footer - Timestamp
        $row += 2;
        $sheet->setCellValue('C' . $row, 'Dicetak pada: ' . date('d M Y H:i'));
        $sheet->getStyle('C' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('C' . $row)->getFont()->setSize(9);

        // Set column widths
        $sheet->getColumnDimension('A')->setWidth(40);
        $sheet->getColumnDimension('B')->setWidth(5);
        $sheet->getColumnDimension('C')->setWidth(25);

        // Create writer and download
        $filename = 'Laporan-Laba-Rugi-' . date('Ymd') . '.xlsx';
        $writer = new Xlsx($spreadsheet);

        // Set headers for download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit;
    }

    public function exportProductAnalysisPdf(Request $request)
    {
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth()->format('Y-m-d'));
        $branchId = $request->get('branch_id');

        $branch = $branchId ? Branch::find($branchId) : null;

        // Get detailed profit per product
        $productDetails = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
                $query->whereBetween('sale_date', [$startDate, $endDate])
                      ->where('status', 'completed');
                if ($branchId) {
                    $query->where('branch_id', $branchId);
                }
            })
            ->select(
                'product_id',
                DB::raw('SUM(quantity) as total_qty'),
                DB::raw('SUM(quantity * selling_price) as total_jual'),
                DB::raw('SUM(quantity * purchase_price) as harga_pokok'),
                DB::raw('SUM(discount) as potongan'),
                DB::raw('SUM(quantity * (selling_price - purchase_price)) as laba_kotor'),
                DB::raw('(SUM(quantity * (selling_price - purchase_price)) / SUM(quantity * selling_price) * 100) as laba_persen')
            )
            ->groupBy('product_id')
            ->with('product.brand')
            ->get();

        $pdf = Pdf::loadView('report.profit-loss.product-analysis-pdf', compact(
            'startDate',
            'endDate',
            'branch',
            'productDetails'
        ));

        // Set paper size to A4 portrait landscape for better table display
        $pdf->setPaper('A4', 'portrait');

        return $pdf->stream('Laporan-Analisa-Laba-Rugi-Produk-' . date('Ymd') . '.pdf');
    }

    public function exportProductAnalysisExcel(Request $request)
    {
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth()->format('Y-m-d'));
        $branchId = $request->get('branch_id');

        $branch = $branchId ? Branch::find($branchId) : null;

        // Get detailed profit per product
        $productDetails = SaleDetail::whereHas('sale', function($query) use ($startDate, $endDate, $branchId) {
                $query->whereBetween('sale_date', [$startDate, $endDate])
                      ->where('status', 'completed');
                if ($branchId) {
                    $query->where('branch_id', $branchId);
                }
            })
            ->select(
                'product_id',
                DB::raw('SUM(quantity) as total_qty'),
                DB::raw('SUM(quantity * selling_price) as total_jual'),
                DB::raw('SUM(quantity * purchase_price) as harga_pokok'),
                DB::raw('SUM(discount) as potongan'),
                DB::raw('SUM(quantity * (selling_price - purchase_price)) as laba_kotor'),
                DB::raw('(SUM(quantity * (selling_price - purchase_price)) / SUM(quantity * selling_price) * 100) as laba_persen')
            )
            ->groupBy('product_id')
            ->with('product.brand')
            ->get();

        // Create Excel file using PhpSpreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set document properties
        $spreadsheet->getProperties()
            ->setCreator('UMI CELL')
            ->setTitle('Laporan Laba Rugi Per Produk')
            ->setSubject('Laporan Laba Rugi Per Produk')
            ->setDescription('Laporan Laba Rugi Per Produk periode ' . $startDate . ' - ' . $endDate);

        // Company Header
        $sheet->setCellValue('A1', 'UMI CELL');
        $sheet->mergeCells('A1:G1');
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A2', 'Jl. Raya Semarang-Boja, Jatisari, Kec. Mijen, Kota Semarang Jawa Tengah 50275');
        $sheet->mergeCells('A2:G2');
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A2')->getFont()->setSize(9);

        $sheet->setCellValue('A3', 'Telp: 0882005564342');
        $sheet->mergeCells('A3:G3');
        $sheet->getStyle('A3')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A3')->getFont()->setSize(9);

        // Report Title
        $sheet->setCellValue('A5', 'LAPORAN LABA RUGI PER PRODUK');
        $sheet->mergeCells('A5:G5');
        $sheet->getStyle('A5')->getFont()->setBold(true)->setSize(14);
        $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A6', 'Periode: ' . date('d M Y', strtotime($startDate)) . ' - ' . date('d M Y', strtotime($endDate)));
        $sheet->mergeCells('A6:G6');
        $sheet->getStyle('A6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A7', 'Cabang: ' . ($branch ? $branch->name : 'Semua Cabang'));
        $sheet->mergeCells('A7:G7');
        $sheet->getStyle('A7')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // Table Header
        $row = 9;

        // First header row
        $sheet->setCellValue('A' . $row, 'Nota');
        $sheet->setCellValue('B' . $row, 'Nama Item');
        $sheet->setCellValue('C' . $row, 'Total Jual');
        $sheet->setCellValue('D' . $row, 'Harga Pokok');
        $sheet->setCellValue('E' . $row, 'Potongan');
        $sheet->setCellValue('F' . $row, 'Laba');
        $sheet->mergeCells('F' . $row . ':G' . $row);

        $sheet->getStyle('A' . $row . ':G' . $row)->getFont()->setBold(true);
        $sheet->getStyle('A' . $row . ':G' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A' . $row . ':G' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
        $sheet->getStyle('A' . $row . ':G' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Second header row
        $sheet->setCellValue('F' . $row, 'Laba Kotor');
        $sheet->setCellValue('G' . $row, 'Laba %');
        $sheet->getStyle('F' . $row . ':G' . $row)->getFont()->setBold(true);
        $sheet->getStyle('F' . $row . ':G' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('F' . $row . ':G' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
        $sheet->getStyle('F' . $row . ':G' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Merge cells for first header row
        $sheet->mergeCells('A9:A10');
        $sheet->mergeCells('B9:B10');
        $sheet->mergeCells('C9:C10');
        $sheet->mergeCells('D9:D10');
        $sheet->mergeCells('E9:E10');

        // Vertical alignment for merged cells
        $sheet->getStyle('A9:E9')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        // Table Data
        $counter = 1;
        $totalJualAll = 0;
        $hargaPokokAll = 0;
        $potonganAll = 0;
        $labaKotorAll = 0;

        foreach ($productDetails as $detail) {
            // Product row
            $sheet->setCellValue('A' . $row, $counter++);
            $productName = ($detail->product->name ?? 'N/A');
            if ($detail->product && $detail->product->brand) {
                $productName .= ' - ' . $detail->product->brand->name;
            }
            $sheet->setCellValue('B' . $row, $productName);
            $sheet->setCellValue('C' . $row, number_format($detail->total_jual, 0, ',', '.'));
            $sheet->setCellValue('D' . $row, number_format($detail->harga_pokok, 0, ',', '.'));
            $sheet->setCellValue('E' . $row, number_format($detail->potongan, 0, ',', '.'));
            $sheet->setCellValue('F' . $row, number_format($detail->laba_kotor, 0, ',', '.'));
            $sheet->setCellValue('G' . $row, number_format($detail->laba_persen, 2));

            $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle('C' . $row . ':G' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle('A' . $row . ':G' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
            $row++;

            // Sub Total row
            $sheet->setCellValue('A' . $row, 'Sub Total');
            $sheet->mergeCells('A' . $row . ':B' . $row);
            $sheet->setCellValue('C' . $row, number_format($detail->total_jual, 0, ',', '.'));
            $sheet->setCellValue('D' . $row, number_format($detail->harga_pokok, 0, ',', '.'));
            $sheet->setCellValue('E' . $row, number_format($detail->potongan, 0, ',', '.'));
            $sheet->setCellValue('F' . $row, number_format($detail->laba_kotor, 0, ',', '.'));
            $sheet->setCellValue('G' . $row, '');

            $sheet->getStyle('A' . $row . ':G' . $row)->getFont()->setBold(true);
            $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle('C' . $row . ':F' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle('A' . $row . ':G' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFF5F5F5');
            $sheet->getStyle('A' . $row . ':G' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
            $row++;

            // Accumulate totals
            $totalJualAll += $detail->total_jual;
            $hargaPokokAll += $detail->harga_pokok;
            $potonganAll += $detail->potongan;
            $labaKotorAll += $detail->laba_kotor;
        }

        // Grand Total row
        $sheet->setCellValue('A' . $row, 'TOTAL');
        $sheet->mergeCells('A' . $row . ':B' . $row);
        $sheet->setCellValue('C' . $row, number_format($totalJualAll, 0, ',', '.'));
        $sheet->setCellValue('D' . $row, number_format($hargaPokokAll, 0, ',', '.'));
        $sheet->setCellValue('E' . $row, number_format($potonganAll, 0, ',', '.'));
        $sheet->setCellValue('F' . $row, number_format($labaKotorAll, 0, ',', '.'));
        $sheet->setCellValue('G' . $row, '');

        $sheet->getStyle('A' . $row . ':G' . $row)->getFont()->setBold(true);
        $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('C' . $row . ':F' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('A' . $row . ':G' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFD4EDDA');
        $sheet->getStyle('A' . $row . ':G' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $row++;

        // Footer - Timestamp
        $row += 2;
        $sheet->setCellValue('G' . $row, 'Dicetak pada: ' . date('d M Y H:i'));
        $sheet->getStyle('G' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('G' . $row)->getFont()->setSize(9);

        // Set column widths
        $sheet->getColumnDimension('A')->setWidth(10);
        $sheet->getColumnDimension('B')->setWidth(35);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(12);

        // Create writer and download
        $filename = 'Laporan-Laba-Rugi-Per-Produk-' . date('Ymd') . '.xlsx';
        $writer = new Xlsx($spreadsheet);

        // Set headers for download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit;
    }
}
