<?php
namespace App\Controllers;

use CodeIgniter\Controller;
use Config\Database;

class Students extends BaseController
{
    public function index()
    {
        $db = Database::connect();
        $req = $this->request;

        // Filters and search
        $q = trim((string) $req->getGet('q'));
        $classId = (int) ($req->getGet('class_id') ?? 0);
        $sectionId = (int) ($req->getGet('section_id') ?? 0);
        $nationality = trim((string) ($req->getGet('nationality') ?? ''));
        $religion = trim((string) ($req->getGet('religion') ?? ''));

        // Load filter options
        $classes = $sections = $nationalities = $religions = [];
        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 { $nationalities = $db->table('nationalities')->orderBy('name_en','asc')->get()->getResultArray(); } catch (\Throwable $e) { $nationalities = []; }
        try { $religions = $db->table('religions')->orderBy('name_en','asc')->get()->getResultArray(); } catch (\Throwable $e) { $religions = []; }

        // Build SQL to join latest enrollment per student
        $sql = "SELECT s.*, e.class_id, e.section_id, COALESCE(c.name_en, c.name_ar) AS class_name, COALESCE(sec.name_en, sec.name_ar) AS section_name
                FROM students s
                LEFT JOIN (
                  SELECT e1.* FROM enrollments e1
                  JOIN (SELECT student_id, MAX(id) AS max_id FROM enrollments GROUP BY student_id) em
                    ON em.student_id = e1.student_id AND em.max_id = e1.id
                ) e ON e.student_id = s.id
                LEFT JOIN classes c ON c.id = e.class_id
                LEFT JOIN sections sec ON sec.id = e.section_id
                WHERE 1=1";
        $params = [];

        // Apply filters
        if ($classId > 0) { $sql .= " AND e.class_id = ?"; $params[] = $classId; }
        if ($sectionId > 0) { $sql .= " AND e.section_id = ?"; $params[] = $sectionId; }
        if ($nationality !== '') { $sql .= " AND s.nationality = ?"; $params[] = $nationality; }
        if ($religion !== '') { $sql .= " AND s.religion = ?"; $params[] = $religion; }

        // Free-text search across fields
        if ($q !== '') {
            $like = '%' . $q . '%';
            $sql .= " AND (s.student_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR s.parent_name LIKE ? OR s.parent_phone LIKE ? OR s.address LIKE ? OR s.nationality LIKE ? OR s.religion LIKE ? OR COALESCE(c.name_en, c.name) LIKE ? OR COALESCE(sec.name_en, sec.name) LIKE ?)";
            array_push($params, $like, $like, $like, $like, $like, $like, $like, $like, $like, $like);
        }

        $sql .= " ORDER BY s.id DESC";

        $students = [];
        try { $students = $db->query($sql, $params)->getResultArray(); } catch (\Throwable $e) { $students = []; }

        // Counts
        $totalAll = 0; $totalFiltered = count($students);
        try { $totalAll = $db->table('students')->countAllResults(); } catch (\Throwable $e) { $totalAll = $totalFiltered; }

        return view('students/index', [
            'title' => lang('App.students'),
            'students' => $students,
            'totalAll' => $totalAll,
            'totalFiltered' => $totalFiltered,
            'filters' => [
                'q' => $q,
                'class_id' => $classId,
                'section_id' => $sectionId,
                'nationality' => $nationality,
                'religion' => $religion,
            ],
            'classes' => $classes,
            'sections' => $sections,
            'nationalities' => $nationalities,
            'religions' => $religions,
        ]);
    }

    public function print()
    {
        $db = Database::connect();
        $req = $this->request;

        // Reuse same filters
        $q = trim((string) $req->getGet('q'));
        $classId = (int) ($req->getGet('class_id') ?? 0);
        $sectionId = (int) ($req->getGet('section_id') ?? 0);
        $nationality = trim((string) ($req->getGet('nationality') ?? ''));
        $religion = trim((string) ($req->getGet('religion') ?? ''));

        $sql = "SELECT s.*, e.class_id, e.section_id, COALESCE(c.name_en, c.name_ar) AS class_name, COALESCE(sec.name_en, sec.name_ar) AS section_name
                FROM students s
                LEFT JOIN (
                  SELECT e1.* FROM enrollments e1
                  JOIN (SELECT student_id, MAX(id) AS max_id FROM enrollments GROUP BY student_id) em
                    ON em.student_id = e1.student_id AND em.max_id = e1.id
                ) e ON e.student_id = s.id
                LEFT JOIN classes c ON c.id = e.class_id
                LEFT JOIN sections sec ON sec.id = e.section_id
                WHERE 1=1";
        $params = [];
        if ($classId > 0) { $sql .= " AND e.class_id = ?"; $params[] = $classId; }
        if ($sectionId > 0) { $sql .= " AND e.section_id = ?"; $params[] = $sectionId; }
        if ($nationality !== '') { $sql .= " AND s.nationality = ?"; $params[] = $nationality; }
        if ($religion !== '') { $sql .= " AND s.religion = ?"; $params[] = $religion; }
        if ($q !== '') {
            $like = '%' . $q . '%';
            $sql .= " AND (s.student_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR s.parent_name LIKE ? OR s.parent_phone LIKE ? OR s.address LIKE ? OR s.nationality LIKE ? OR s.religion LIKE ? OR COALESCE(c.name_en, c.name) LIKE ? OR COALESCE(sec.name_en, sec.name) LIKE ?)";
            array_push($params, $like, $like, $like, $like, $like, $like, $like, $like, $like, $like);
        }
        $sql .= " ORDER BY s.id DESC";

        $students = [];
        try { $students = $db->query($sql, $params)->getResultArray(); } catch (\Throwable $e) { $students = []; }
        $totalFiltered = count($students);

        return view('students/print', [
            'title' => lang('App.students') . ' - ' . lang('App.print'),
            'students' => $students,
            'totalFiltered' => $totalFiltered,
            'filters' => [
                'q' => $q,
                'class_id' => $classId,
                'section_id' => $sectionId,
                'nationality' => $nationality,
                'religion' => $religion,
            ],
        ]);
    }

    public function profile($id)
    {
        $db = Database::connect();
        $student = $db->table('students')->where('id', (int)$id)->get()->getRowArray();
        if (!$student) {
            throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound('Student not found');
        }
        // Load option lists for dropdowns
        $nationalities = $religions = [];
        try { $nationalities = $db->table('nationalities')->orderBy('name_en','asc')->get()->getResultArray(); } catch (\Throwable $e) { $nationalities = []; }
        try { $religions = $db->table('religions')->orderBy('name_en','asc')->get()->getResultArray(); } catch (\Throwable $e) { $religions = []; }

        return view('students/profile', [
            'title' => lang('App.profile'),
            'student' => $student,
            'nationalities' => $nationalities,
            'religions' => $religions,
        ]);
    }

    public function update($id)
    {
        if (! auth()->user() || ! auth()->user()->can('meta.modify')) {
            return redirect()->back()->with('error', 'You do not have modify permission.');
        }
        $db = Database::connect();
        $req = $this->request;
        $id = (int)$id;

        $data = [
            'first_name' => $req->getPost('first_name') ?? '',
            'last_name'  => $req->getPost('last_name') ?? '',
            'name_ar'    => $req->getPost('name_ar') ?? null,
            'gender'     => $req->getPost('gender') === 'Male' ? 'M' : ($req->getPost('gender') === 'Female' ? 'F' : null),
            'dob'        => $req->getPost('dob') ?: null,
            'nationality'=> $req->getPost('nationality') ?: null,
            'religion'   => $req->getPost('religion') ?: null,
            'parent_name'=> $req->getPost('parent_name') ?: null,
            'parent_phone' => $req->getPost('parent_phone') ?? null,
            'address'    => $req->getPost('address') ?? null,
        ];

        // Handle optional photo update
        $photo = $req->getFile('photo');
        if ($photo && $photo->isValid() && !$photo->hasMoved()) {
            $allowedTypes = ['image/jpeg','image/jpg','image/png','image/gif'];
            if (in_array($photo->getMimeType(), $allowedTypes) && $photo->getSize() <= 2097152) {
                // Fetch student code to prefix
                $stu = $db->table('students')->select('student_id')->where('id', $id)->get()->getRowArray();
                $prefix = $stu ? $stu['student_id'] : 'STU';
                $newName = $prefix . '_' . $photo->getRandomName();
                if ($photo->move(WRITEPATH . 'uploads/photos', $newName)) {
                    $data['photo'] = $newName;
                }
            }
        }

        $db->table('students')->where('id', $id)->update($data);
        return redirect()->to(base_url('students'));
    }

    public function delete($id)
    {
        if (! auth()->user() || ! auth()->user()->can('meta.delete')) {
            return redirect()->back()->with('error', 'You do not have delete permission.');
        }
        $db = Database::connect();
        $db->table('students')->where('id', (int)$id)->delete();
        return redirect()->to(base_url('students'));
    }

    public function ledger($id)
    {
        $db = Database::connect();
        $student = [];
        try { $student = $db->table('students')->where('id', (int)$id)->get()->getRowArray(); } catch (\Throwable $e) { $student = []; }
        if (! $student) {
            throw \CodeIgniter\Exceptions\PageNotFoundException::forPageNotFound('Student not found');
        }

        // Determine selected and previous school year
        $req = $this->request;
        $selectedYearId = (int) ($req->getGet('school_year_id') ?? 0);
        $years = [];
        try {
            $years = $db->table('school_years')->select('id, school_year, status')->orderBy('id','desc')->get()->getResultArray();
        } catch (\Throwable $e) { $years = []; }

        // Prefer active year, else latest; override with provided selection
        $activeYear = null;
        foreach ($years as $y) { if (($y['status'] ?? '') === 'active') { $activeYear = $y; break; } }
        $currentYear = null;
        if ($selectedYearId > 0) { foreach ($years as $y) { if ((int)$y['id'] === $selectedYearId) { $currentYear = $y; break; } } }
        if (! $currentYear) { $currentYear = $activeYear ?: ($years[0] ?? null); }

        // Find previous year by id less than current
        $previousYear = null;
        if ($currentYear) {
            foreach ($years as $y) { if ((int)$y['id'] < (int)$currentYear['id']) { $previousYear = $y; break; } }
        }

        // Fetch Fixed payment types only for standard charges
        $standardKeys = ['tuition','books','miscellaneous','registration'];
        $standards = [ 'Tuition' => 0.0, 'Books' => 0.0, 'Miscellaneous' => 0.0, 'Registration Fee' => 0.0 ];
        try {
            $pts = $db->table('payment_types')
                ->select('id, description_en, amount, option')
                ->where('option', 'Fixed')
                ->get()->getResultArray();
            foreach ($pts as $pt) {
                $desc = strtolower(trim($pt['description_en'] ?? ''));
                foreach ($standardKeys as $key) {
                    if (strpos($desc, $key) !== false) {
                        if ($key === 'registration') { $standards['Registration Fee'] = (float)($pt['amount'] ?? 0); }
                        elseif ($key === 'miscellaneous') { $standards['Miscellaneous'] = (float)($pt['amount'] ?? 0); }
                        elseif ($key === 'books') { $standards['Books'] = (float)($pt['amount'] ?? 0); }
                        elseif ($key === 'tuition') { $standards['Tuition'] = (float)($pt['amount'] ?? 0); }
                    }
                }
            }
        } catch (\Throwable $e) { /* defaults remain */ }

        // Helper to sum payments for a given year
        $sumPayments = function($yearId) use ($db, $id) {
            if (! $yearId) return 0.0;
            $total = 0.0;
            try {
                $items = $db->table('receipt_items ri')
                    ->select('ri.amount')
                    ->join('receipts r', 'r.id = ri.receipt_id')
                    ->where('r.student_id', (int)$id)
                    ->where('r.school_year_id', (int)($yearId))
                    ->get()->getResultArray();
                foreach ($items as $it) { $total += (float)($it['amount'] ?? 0); }
            } catch (\Throwable $e) { /* ignore */ }
            return round($total, 2);
        };

        $currentCharges = array_sum($standards);
        $currentPayments = $sumPayments($currentYear['id'] ?? null);

        $prevCharges = $previousYear ? array_sum($standards) : 0.0; // assume same standards last year
        $prevPayments = $sumPayments($previousYear['id'] ?? null);
        $openingBalance = round($prevCharges - $prevPayments, 2);

        // Build ledger entries
        $entries = [];
        $balance = $openingBalance;
        if ($openingBalance != 0.0) {
            $entries[] = [ 'date' => null, 'type' => 'Opening Balance', 'debit' => max($openingBalance, 0), 'credit' => max(-$openingBalance, 0), 'balance' => $balance ];
        }
        // Debits: standard charges for current year
        foreach ($standards as $label => $amt) {
            if ($amt > 0) {
                $balance += $amt;
                $entries[] = [ 'date' => null, 'type' => $label . ' (Charge)', 'debit' => $amt, 'credit' => 0.0, 'balance' => $balance ];
            }
        }
        // Credits: payments in current year by receipt item
        try {
            $payItems = $db->table('receipt_items ri')
                ->select('ri.description, ri.amount, r.created_at')
                ->join('receipts r', 'r.id = ri.receipt_id')
                ->where('r.student_id', (int)$id)
                ->where('r.school_year_id', (int)($currentYear['id'] ?? 0))
                ->orderBy('r.created_at','asc')
                ->get()->getResultArray();
            foreach ($payItems as $it) {
                $amt = (float)($it['amount'] ?? 0);
                $balance -= $amt;
                $entries[] = [ 'date' => $it['created_at'] ?? null, 'type' => ($it['description'] ?: 'Payment'), 'debit' => 0.0, 'credit' => $amt, 'balance' => $balance ];
            }
        } catch (\Throwable $e) { /* ignore */ }

        return view('students/ledger', [
            'title' => 'Student Ledger',
            'student' => $student,
            'currentYear' => $currentYear,
            'previousYear' => $previousYear,
            'years' => $years,
            'standards' => $standards,
            'entries' => $entries,
            'openingBalance' => $openingBalance,
            'summary' => [
                'charges' => $currentCharges,
                'payments' => $currentPayments,
                'closing' => $balance,
            ],
        ]);
    }
}