<?php

namespace App\Http\Controllers\Admin\System;

use App\Http\Controllers\Admin\PageController;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
use Symfony\Component\HttpFoundation\StreamedResponse;

class ReportBuilderController extends PageController
{
    /** @var array<string, array{table: string, columns: list<string>}> */
    private const QUERIES = [
        'students' => ['table' => 'students', 'columns' => ['admission_number', 'first_name', 'last_name', 'gender', 'status']],
        'staff' => ['table' => 'staff', 'columns' => ['staff_number', 'first_name', 'last_name', 'job_title', 'department', 'status']],
        'invoices' => ['table' => 'invoices', 'columns' => ['invoice_number', 'student_id', 'issue_date', 'total_amount', 'balance_due', 'status']],
        'receipts' => ['table' => 'receipts', 'columns' => ['receipt_number', 'student_id', 'payment_date', 'amount', 'payment_method']],
        'attendance' => ['table' => 'attendance_records', 'columns' => ['attendance_id', 'student_id', 'status', 'remarks']],
    ];

    public function index(): View
    {
        return $this->page('admin.report-builder.index', [
            'crumbTitle' => 'Report Builder',
            'crumbSub' => 'Custom queries & exports',
            'queries' => array_keys(self::QUERIES),
        ]);
    }

    public function export(Request $request): StreamedResponse
    {
        $validated = $request->validate([
            'query' => ['required', 'string', 'in:'.implode(',', array_keys(self::QUERIES))],
        ]);

        $config = self::QUERIES[$validated['query']];
        $rows = DB::table($config['table'])
            ->select($config['columns'])
            ->limit(5000)
            ->get();

        $filename = $validated['query'].'_'.now()->format('Y-m-d').'.csv';

        return response()->streamDownload(function () use ($config, $rows) {
            $handle = fopen('php://output', 'w');
            fputcsv($handle, $config['columns']);

            foreach ($rows as $row) {
                fputcsv($handle, (array) $row);
            }

            fclose($handle);
        }, $filename, ['Content-Type' => 'text/csv']);
    }
}
