<?php
namespace App\Controllers;

use Config\Database;

class Reports extends BaseController
{
    public function students()
    {
        $db = Database::connect();
        $count = 0;
        try { $count = $db->table('students')->countAllResults(); } catch (\Throwable $e) { $count = 0; }
        return view('reports/students', [
            'title' => lang('App.students_reports'),
            'count' => $count,
        ]);
    }

    public function teachers()
    {
        $db = Database::connect();
        $count = 0;
        try { $count = $db->table('teachers')->countAllResults(); } catch (\Throwable $e) { $count = 0; }
        return view('reports/teachers', [
            'title' => lang('App.teachers_report'),
            'count' => $count,
        ]);
    }

    public function payments()
    {
        $db = Database::connect();
        $req = $this->request;
        $isArabic = (service('language')->getLocale() === 'ar');

        // Filters
        $from = trim((string)($req->getGet('from') ?? ''));
        $to = trim((string)($req->getGet('to') ?? ''));
        $classId = (int)($req->getGet('class_id') ?? 0);
        $sectionId = (int)($req->getGet('section_id') ?? 0);
        $paymentTypeId = (int)($req->getGet('payment_type_id') ?? 0);
        if ($from === '') { $from = date('Y-m-01'); }
        if ($to === '') { $to = date('Y-m-d'); }

        // Dropdown data
        $classes = $sections = $paymentTypes = [];
        try { $classes = $db->table('classes')->orderBy('name_en','asc')->get()->getResultArray(); } catch (\Throwable $e) { $classes = []; }
        try { $sections = $db->table('sections')->orderBy('name_en','asc')->get()->getResultArray(); } catch (\Throwable $e) { $sections = []; }
        try { $paymentTypes = $db->table('payment_types')->orderBy('id','asc')->get()->getResultArray(); } catch (\Throwable $e) { $paymentTypes = []; }

        // Build query: receipt items joined with receipts, students, classes, sections, payment types
        $builder = $db->table('receipt_items ri')
            ->select('r.created_at, r.receipt_no, s.first_name, s.last_name, s.name_ar, c.name_en as class_en, c.name_ar as class_ar, sec.name_en as section_en, sec.name_ar as section_ar, ri.amount, pt.description_en, pt.description_ar')
            ->join('receipts r', 'r.id = ri.receipt_id')
            ->join('students s', 's.id = r.student_id')
            ->join('classes c', 'c.id = r.class_id', 'left')
            ->join('sections sec', 'sec.id = r.section_id', 'left')
            ->join('payment_types pt', 'pt.id = ri.payment_type_id', 'left')
            ->where('r.created_at >=', $from . ' 00:00:00')
            ->where('r.created_at <=', $to . ' 23:59:59')
            ->orderBy('r.created_at','asc')
            ->orderBy('r.receipt_no','asc');
        if ($classId) { $builder = $builder->where('r.class_id', $classId); }
        if ($sectionId) { $builder = $builder->where('r.section_id', $sectionId); }
        if ($paymentTypeId) { $builder = $builder->where('ri.payment_type_id', $paymentTypeId); }

        $rows = [];
        try { $rows = $builder->get()->getResultArray(); } catch (\Throwable $e) { $rows = []; }

        // Map to display rows
        $display = [];
        $totalAmount = 0.0;
        foreach ($rows as $r) {
            $name = trim(($r['first_name'] ?? '') . ' ' . ($r['last_name'] ?? ''));
            if ($isArabic && isset($r['name_ar']) && $r['name_ar']) { $name = $r['name_ar']; }
            $className = $isArabic ? ($r['class_ar'] ?? $r['class_en'] ?? '') : ($r['class_en'] ?? $r['class_ar'] ?? '');
            $sectionName = $isArabic ? ($r['section_ar'] ?? $r['section_en'] ?? '') : ($r['section_en'] ?? $r['section_ar'] ?? '');
            $ptLabel = $isArabic ? ($r['description_ar'] ?? $r['description_en'] ?? '') : ($r['description_en'] ?? $r['description_ar'] ?? '');
            $amt = (float)($r['amount'] ?? 0);
            $totalAmount += $amt;
            $display[] = [
                'date' => $r['created_at'] ?? null,
                'receipt_no' => $r['receipt_no'] ?? '',
                'student' => $name,
                'class' => trim($className),
                'section' => trim($sectionName),
                'payment_type' => $ptLabel,
                'amount' => $amt,
            ];
        }

        // Selected payment type label for header display
        $selectedPaymentTypeLabel = '';
        if ($paymentTypeId) {
            try {
                $pt = $db->table('payment_types')->where('id', $paymentTypeId)->get()->getRowArray();
                if ($pt) { $selectedPaymentTypeLabel = $isArabic ? ($pt['description_ar'] ?? $pt['description_en'] ?? '') : ($pt['description_en'] ?? $pt['description_ar'] ?? ''); }
            } catch (\Throwable $e) { /* ignore */ }
        }

        return view('reports/payments', [
            'title' => lang('App.payments_reports'),
            'filters' => [ 'from' => $from, 'to' => $to, 'class_id' => $classId, 'section_id' => $sectionId, 'payment_type_id' => $paymentTypeId ],
            'classes' => $classes,
            'sections' => $sections,
            'paymentTypes' => $paymentTypes,
            'rows' => $display,
            'totalAmount' => $totalAmount,
            'selectedPaymentTypeLabel' => $selectedPaymentTypeLabel,
        ]);
    }

    public function accounting()
    {
        $db = Database::connect();
        $journalCount = $depositCount = 0;
        try { $journalCount = $db->table('journal_vouchers')->countAllResults(); } catch (\Throwable $e) { $journalCount = 0; }
        try { $depositCount = $db->table('bank_deposits')->countAllResults(); } catch (\Throwable $e) { $depositCount = 0; }
        return view('reports/accounting', [
            'title' => lang('App.accounting_reports'),
            'journalCount' => $journalCount,
            'depositCount' => $depositCount,
        ]);
    }

    public function deposits()
    {
        $db = Database::connect();
        $count = 0;
        try { $count = $db->table('bank_deposits')->countAllResults(); } catch (\Throwable $e) { $count = 0; }
        return view('reports/deposits', [
            'title' => lang('App.bank_deposits_reports'),
            'count' => $count,
        ]);
    }

    public function ministry()
    {
        // Placeholder view; future implementation may aggregate required ministry KPIs
        return view('reports/ministry', [
            'title' => lang('App.ministry_reports'),
        ]);
    }
}

