<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use Symfony\Component\HttpFoundation\StreamedResponse;
class ReportController extends Controller
{
    //
    //  public function salesReport(Request $request)
    // {
    //     $startDate = $request->input('start_date');
    //     $endDate   = $request->input('end_date');

    //     $query = DB::table('bills')
    //         ->select(
    //             'bills.bill_no',
    //             'bills.bill_date',
    //             DB::raw('SUM(bill_items.qty) as total_qty'),
    //             DB::raw('SUM(bill_items.amount) as total_sales')
    //         )
    //         ->join('bill_items', 'bills.id', '=', 'bill_items.bill_id')
    //         ->groupBy('bills.id', 'bills.bill_no', 'bills.bill_date')
    //         ->orderBy('bills.bill_date', 'asc');

    //     // If date filter applied
    //     if ($startDate && $endDate) {
    //         $query->whereBetween('bills.bill_date', [$startDate, $endDate]);
    //     }

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

    //     // Grand totals
    //     $totals = [
    //         'total_bills'  => $sales->count(),
    //         'total_qty'    => $sales->sum('total_qty'),
    //         'total_amount' => $sales->sum('total_sales'),
    //     ];

    //     return response()->json([
    //         'sales'  => $sales,
    //         'totals' => $totals,
    //     ]);
    // }

    public function salesReport(Request $request)
{
    $startDate = $request->input('start_date');
    $endDate   = $request->input('end_date');

    $query = DB::table('bills')
        ->join('bill_items', 'bills.id', '=', 'bill_items.bill_id')
        ->select(
            'bills.bill_date',
            DB::raw('COUNT(DISTINCT bills.id) as total_bills'),
            DB::raw('SUM(bill_items.qty) as total_qty'),
            DB::raw('SUM(bill_items.amount) as total_sales')
        )
        ->groupBy('bills.bill_date')
        ->orderBy('bills.bill_date', 'asc');

    // Apply date filter if given
    if ($startDate && $endDate) {
        $query->whereBetween('bills.bill_date', [$startDate, $endDate]);
    }

    $sales = $query->get();

    // Grand totals
    $totals = [
        'total_bills'  => $sales->sum('total_bills'),
        'total_qty'    => $sales->sum('total_qty'),
        'total_amount' => $sales->sum('total_sales'),
    ];

    return response()->json([
        'sales'  => $sales,
        'totals' => $totals,
    ]);
}

public function exportSalesReport(Request $request)
    {
        $startDate = $request->input('start_date');
        $endDate   = $request->input('end_date');

        // Get the same data as the sales report
        $query = DB::table('bills')
            ->join('bill_items', 'bills.id', '=', 'bill_items.bill_id')
            ->select(
                'bills.bill_date',
                DB::raw('COUNT(DISTINCT bills.id) as total_bills'),
                DB::raw('SUM(bill_items.qty) as total_qty'),
                DB::raw('SUM(bill_items.amount) as total_sales')
            )
            ->groupBy('bills.bill_date')
            ->orderBy('bills.bill_date', 'asc');

        if ($startDate && $endDate) {
            $query->whereBetween('bills.bill_date', [$startDate, $endDate]);
        }

        $sales = $query->get();

        // Calculate totals
        $totals = [
            'total_bills'  => $sales->sum('total_bills'),
            'total_qty'    => $sales->sum('total_qty'),
            'total_amount' => $sales->sum('total_sales'),
        ];

        // Create spreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set sheet title
        $title = 'Sales Report';
        if ($startDate && $endDate) {
            $title .= ' (' . date('d/m/Y', strtotime($startDate)) . ' - ' . date('d/m/Y', strtotime($endDate)) . ')';
        }
        $sheet->setTitle('Sales Report');

        // Set headers
        $headers = ['Bill Date', 'Total Bills', 'Total Qty', 'Total Sales Amount'];
        $sheet->fromArray($headers, null, 'A1');

        // Style header row
        $headerStyle = [
            'font' => [
                'bold' => true,
                'color' => ['argb' => 'FFFFFF'],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['argb' => '4472C4'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:D1')->applyFromArray($headerStyle);

        // Add data rows
        $row = 2;
        foreach ($sales as $sale) {
            $sheet->setCellValue('A' . $row, date('d/m/Y', strtotime($sale->bill_date)));
            $sheet->setCellValue('B' . $row, $sale->total_bills);
            $sheet->setCellValue('C' . $row, $sale->total_qty);
            $sheet->setCellValue('D' . $row, number_format((float)$sale->total_sales, 2, '.', ''));
            
            // Style data row
            $sheet->getStyle('A' . $row . ':D' . $row)->applyFromArray([
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['argb' => 'CCCCCC'],
                    ],
                ],
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                ],
            ]);
            
            $row++;
        }

        // Add empty row
        $row++;

        // Add totals row
        $sheet->setCellValue('A' . $row, 'TOTAL');
        $sheet->setCellValue('B' . $row, $totals['total_bills']);
        $sheet->setCellValue('C' . $row, $totals['total_qty']);
        $sheet->setCellValue('D' . $row, number_format((float)$totals['total_amount'], 2, '.', ''));

        // Style totals row
        $totalStyle = [
            'font' => [
                'bold' => true,
                'color' => ['argb' => '000000'],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['argb' => 'E2EFDA'],
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
            ],
        ];
        $sheet->getStyle('A' . $row . ':D' . $row)->applyFromArray($totalStyle);

        // Set column widths
        $sheet->getColumnDimension('A')->setWidth(15);
        $sheet->getColumnDimension('B')->setWidth(12);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(18);

        // Align currency column to right
        $sheet->getStyle('D:D')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        // Generate filename
        $filename = 'sales_report';
        if ($startDate && $endDate) {
            $filename .= '_' . date('Y-m-d', strtotime($startDate)) . '_to_' . date('Y-m-d', strtotime($endDate));
        } else {
            $filename .= '_' . date('Y-m-d');
        }
        $filename .= '.xlsx';

        // Create response
        return new StreamedResponse(function() use ($spreadsheet) {
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
        }, 200, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
            'Cache-Control' => 'max-age=0',
        ]);
    }

}
