<?php

class ReportController extends Controller
{
    public function index(Request $request): void
    {
        $db = $this->db();

        $this->ensureReportsSeeded($db);

        $reports = $db->fetchAll(
            "SELECT r.*, 
                    (SELECT COUNT(*) FROM report_favorites rf WHERE rf.report_id = r.id AND rf.user_id = ?) AS is_favorite
             FROM report_definitions r 
             WHERE r.is_active = 1 
             ORDER BY r.category, r.report_name_en",
            [Auth::id()]
        );

        $categories = [];
        foreach ($reports as $r) {
            $cat = $r['category'] ?: 'other';
            $categories[$cat][] = $r;
        }

        $favorites = $db->fetchAll(
            "SELECT rf.*, rd.report_code, rd.report_name_en, rd.report_name_ar, rd.category, rd.description_en, rd.description_ar
             FROM report_favorites rf
             JOIN report_definitions rd ON rf.report_id = rd.id
             WHERE rf.user_id = ? AND rd.is_active = 1
             ORDER BY rf.created_at DESC",
            [Auth::id()]
        );

        $this->view('index', [
            'title'       => lang('reports.browser_title'),
            'breadcrumbs' => [['label' => lang('reports.reports')]],
            'categories'  => $categories,
            'favorites'   => $favorites,
        ]);
    }

    public function view(Request $request): void
    {
        $db = $this->db();
        $id = (int) $request->routeParam('id');

        $report = $db->fetchOne("SELECT * FROM report_definitions WHERE id = ? AND is_active = 1", [$id]);
        if (!$report) {
            $this->abort(404, 'Report not found');
        }

        $parameters = $db->fetchAll(
            "SELECT * FROM report_parameters WHERE report_id = ? ORDER BY sort_order, id",
            [$id]
        );

        $favorites = $db->fetchAll(
            "SELECT * FROM report_favorites WHERE report_id = ? AND user_id = ? ORDER BY favorite_name",
            [$id, Auth::id()]
        );

        $recentRuns = $db->fetchAll(
            "SELECT * FROM report_generated WHERE report_id = ? AND generated_by = ? ORDER BY generated_at DESC LIMIT 10",
            [$id, Auth::id()]
        );

        $this->view('view', [
            'title'       => ($report['report_name_' . Lang::current()] ?: $report['report_name_en']),
            'breadcrumbs' => [
                ['label' => lang('reports.reports'), 'url' => '/reports'],
                ['label' => ($report['report_name_' . Lang::current()] ?: $report['report_name_en'])],
            ],
            'report'     => $report,
            'parameters' => $parameters,
            'favorites'  => $favorites,
            'recentRuns' => $recentRuns,
        ]);
    }

    public function kpis(Request $request): void
    {
        $this->view('kpis', [
            'title'       => lang('reports.kpi_dashboard'),
            'breadcrumbs' => [
                ['label' => lang('reports.reports'), 'url' => '/reports'],
                ['label' => lang('reports.kpi_dashboard')],
            ],
        ]);
    }

    public function custom(Request $request): void
    {
        $this->view('custom', [
            'title'       => lang('reports.custom_builder'),
            'breadcrumbs' => [
                ['label' => lang('reports.reports'), 'url' => '/reports'],
                ['label' => lang('reports.custom_builder')],
            ],
        ]);
    }

    public function dashboardConfig(Request $request): void
    {
        $db = $this->db();

        $allWidgets = $db->fetchAll("SELECT * FROM dashboard_widgets WHERE is_active = 1 ORDER BY sort_order");

        $userLayout = $db->fetchOne(
            "SELECT * FROM dashboard_user_layouts WHERE user_id = ? AND is_default = 1",
            [Auth::id()]
        );

        $this->view('dashboard_config', [
            'title'       => lang('reports.dashboard_config'),
            'breadcrumbs' => [
                ['label' => lang('reports.reports'), 'url' => '/reports'],
                ['label' => lang('reports.dashboard_config')],
            ],
            'allWidgets'  => $allWidgets,
            'userLayout'  => $userLayout,
        ]);
    }

    private function ensureReportsSeeded(Database $db): void
    {
        $count = (int) $db->fetchColumn("SELECT COUNT(*) FROM report_definitions");
        if ($count === 0) {
            ReportSeeder::seed($db);
        }
    }
}