<?php

class ReportApiController extends Controller
{
    public function initReports(Request $request): void
    {
        ReportSeeder::seed($this->db());
        $this->json(['success' => true, 'message' => 'Reports initialized']);
    }

    public function execute(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->json(['success' => false, 'message' => 'Report not found'], 404);
            return;
        }

        $params     = $request->post('params', []);
        $draw       = (int) $request->post('draw', 1);
        $start      = (int) $request->post('start', 0);
        $length     = (int) $request->post('length', 25);
        $searchVal  = $request->post('search', [])['value'] ?? '';
        $orderCol   = (int) ($request->post('order', [])['0']['column'] ?? 0);
        $orderDir   = strtoupper($request->post('order', [])['0']['dir'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';

        if (!is_array($params)) {
            $params = [];
        }

        $result = ReportEngine::execute($db, $report, $params, $start, $length, $searchVal, $orderCol, $orderDir);

        $startTime = microtime(true);

        $db->insert('report_generated', [
            'report_id'       => $id,
            'generated_by'    => Auth::id(),
            'parameters_used' => json_encode($params, JSON_UNESCAPED_UNICODE),
            'columns_used'    => $report['default_columns'],
            'output_format'   => 'screen',
            'rows_count'      => $result['recordsFiltered'],
            'execution_time_ms' => (int) ((microtime(true) - $startTime) * 1000),
        ]);

        $this->json([
            'draw'            => $draw,
            'recordsTotal'    => $result['recordsTotal'],
            'recordsFiltered' => $result['recordsFiltered'],
            'data'            => $result['data'],
        ]);
    }

    public function export(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->json(['success' => false, 'message' => 'Report not found'], 404);
            return;
        }

        $params = $request->post('params', []);
        $format = $request->post('format', 'csv');
        if (!is_array($params)) $params = [];

        $result = ReportEngine::execute($db, $report, $params, 0, 100000, '', 0, 'ASC');

        $columns = json_decode($report['default_columns'], true) ?: [];
        $headers = array_map(function ($c) {
            return $c['label'] ?? $c['key'] ?? '';
        }, $columns);

        $rows = [];
        foreach ($result['data'] as $row) {
            $r = [];
            foreach ($columns as $col) {
                $key = $col['key'] ?? '';
                $r[] = $row[$key] ?? '';
            }
            $rows[] = $r;
        }

        $db->insert('report_generated', [
            'report_id'       => $id,
            'generated_by'    => Auth::id(),
            'parameters_used' => json_encode($params, JSON_UNESCAPED_UNICODE),
            'columns_used'    => $report['default_columns'],
            'output_format'   => $format,
            'rows_count'      => count($rows),
            'execution_time_ms' => 0,
        ]);

        $this->audit('export', 'report_definitions', $id, "Exported report: {$report['report_code']} as {$format}");

        $filename = $report['report_code'] . '_' . date('Y-m-d_His');

        if ($format === 'excel') {
            Export::excel($filename . '.xls', $headers, $rows);
        } else {
            Export::csv($filename . '.csv', $headers, $rows);
        }
    }

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

        try {
            $kpi = $db->fetchOne("SELECT * FROM vw_kpi_summary LIMIT 1");
        } catch (\Exception $e) {
            $kpi = [
                'total_active_members'   => (int) $db->fetchColumn("SELECT COUNT(*) FROM members WHERE status='active' AND is_deleted=0"),
                'new_members_this_month' => (int) $db->fetchColumn("SELECT COUNT(*) FROM members WHERE status='active' AND is_deleted=0 AND MONTH(activation_date)=MONTH(CURDATE()) AND YEAR(activation_date)=YEAR(CURDATE())"),
                'terminated_this_month'  => 0,
                'pending_applications'   => (int) $db->fetchColumn("SELECT COUNT(*) FROM applications WHERE status NOT IN ('approved','rejected','cancelled','expired') AND is_deleted=0"),
                'revenue_this_month'     => (float) $db->fetchColumn("SELECT COALESCE(SUM(amount),0) FROM payments WHERE status='completed' AND MONTH(payment_date)=MONTH(CURDATE()) AND YEAR(payment_date)=YEAR(CURDATE()) AND is_deleted=0"),
                'total_outstanding'      => (float) $db->fetchColumn("SELECT COALESCE(SUM(balance_due),0) FROM invoices WHERE status IN ('issued','partially_paid','overdue') AND is_deleted=0"),
                'total_overdue'          => (float) $db->fetchColumn("SELECT COALESCE(SUM(balance_due),0) FROM invoices WHERE status IN ('issued','partially_paid','overdue') AND due_date < CURDATE() AND is_deleted=0"),
                'active_freezes'         => (int) $db->fetchColumn("SELECT COUNT(*) FROM member_freezes WHERE status='active'"),
                'open_violations'        => (int) $db->fetchColumn("SELECT COUNT(*) FROM violations WHERE status NOT IN ('closed','dismissed')"),
                'open_complaints'        => (int) $db->fetchColumn("SELECT COUNT(*) FROM complaints WHERE status NOT IN ('resolved','closed')"),
                'overdue_installments'   => (int) $db->fetchColumn("SELECT COUNT(*) FROM installment_plan_schedules WHERE status='overdue'"),
                'cards_expiring_30_days' => (int) $db->fetchColumn("SELECT COUNT(*) FROM member_cards WHERE status='active' AND expiry_date IS NOT NULL AND expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)"),
            ];
        }

        $this->json(['success' => true, 'data' => $kpi]);
    }

    public function revenueChart(Request $request): void
    {
        $db = $this->db();
        $days = (int) $request->get('days', 30);
        if ($days < 7) $days = 7;
        if ($days > 365) $days = 365;

        $rows = $db->fetchAll(
            "SELECT DATE(payment_date) AS day, COALESCE(SUM(amount), 0) AS total
             FROM payments
             WHERE status = 'completed' AND is_deleted = 0
               AND payment_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
             GROUP BY DATE(payment_date)
             ORDER BY day",
            [$days]
        );

        $labels = [];
        $values = [];
        foreach ($rows as $r) {
            $labels[] = $r['day'];
            $values[] = (float) $r['total'];
        }

        $this->json(['success' => true, 'labels' => $labels, 'values' => $values]);
    }

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

        $rows = $db->fetchAll(
            "SELECT DATE_FORMAT(activation_date, '%Y-%m') AS month, COUNT(*) AS count
             FROM members
             WHERE status = 'active' AND is_deleted = 0
               AND activation_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
             GROUP BY DATE_FORMAT(activation_date, '%Y-%m')
             ORDER BY month"
        );

        $labels = [];
        $values = [];
        foreach ($rows as $r) {
            $labels[] = $r['month'];
            $values[] = (int) $r['count'];
        }

        $this->json(['success' => true, 'labels' => $labels, 'values' => $values]);
    }

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

        $validator = new Validator($request->all(), [
            'report_id'     => 'required|integer',
            'favorite_name' => 'required|string|max:200',
            'saved_parameters' => 'nullable|string',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $data = $validator->validated();
        $reportId = (int) $data['report_id'];
        $savedParams = $data['saved_parameters'] ?? null;

        $id = $db->insert('report_favorites', [
            'user_id'          => Auth::id(),
            'report_id'        => $reportId,
            'favorite_name'    => $data['favorite_name'],
            'saved_parameters' => $savedParams ? $savedParams : null,
            'is_default'       => 0,
        ]);

        $this->json(['success' => true, 'message' => 'Favorite saved', 'id' => $id]);
    }

    public function deleteFavorite(Request $request): void
    {
        $db = $this->db();
        $id = (int) $request->post('id', 0);

        $fav = $db->fetchOne("SELECT * FROM report_favorites WHERE id = ? AND user_id = ?", [$id, Auth::id()]);
        if (!$fav) {
            $this->json(['success' => false, 'message' => 'Favorite not found'], 404);
            return;
        }

        $db->delete('report_favorites', 'id = ?', [$id]);
        $this->json(['success' => true, 'message' => 'Favorite removed']);
    }

    public function listFavorites(Request $request): void
    {
        $db = $this->db();
        $reportId = (int) $request->get('report_id', 0);

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

        $this->json(['success' => true, 'data' => $favs]);
    }
}