<?php
namespace Controllers\Admin;

use Core\Controller;

class AnalyticsController extends Controller {

    public function index(): void {
        $this->requireRole('super-admin', 'admin');
        $sid    = $this->schoolId();
        $termId = (int)$this->request->get('term_id', 0);

        // Get current term if none selected
        if (!$termId) {
            $cur = $this->db->fetch(
                "SELECT t.id FROM terms t JOIN academic_years ay ON ay.id=t.academic_year_id
                 WHERE ay.school_id=? AND t.is_current=1 LIMIT 1", [$sid]
            );
            $termId = $cur['id'] ?? 0;
        }

        $terms = $this->db->fetchAll(
            "SELECT t.*, ay.name AS year_name FROM terms t
             JOIN academic_years ay ON ay.id=t.academic_year_id
             WHERE ay.school_id=? ORDER BY t.id DESC", [$sid]
        );

        // Overall performance distribution (EE/ME/AE/BE)
        $gradeDistribution = $this->db->fetchAll(
            "SELECT m.grade, COUNT(*) as cnt FROM marks m
             JOIN students s ON s.id=m.student_id
             JOIN exams e ON e.id=m.exam_id
             WHERE s.school_id=? AND e.term_id=?
             GROUP BY m.grade ORDER BY FIELD(m.grade,'EE','ME','AE','BE')",
            [$sid, $termId]
        );

        // Subject performance averages
        $subjectPerformance = $this->db->fetchAll(
            "SELECT sub.name, sub.id,
                    AVG(m.score) as avg_score,
                    COUNT(DISTINCT m.student_id) as learner_count,
                    SUM(CASE WHEN m.grade='EE' THEN 1 ELSE 0 END) as ee_count,
                    SUM(CASE WHEN m.grade='ME' THEN 1 ELSE 0 END) as me_count,
                    SUM(CASE WHEN m.grade='AE' THEN 1 ELSE 0 END) as ae_count,
                    SUM(CASE WHEN m.grade='BE' THEN 1 ELSE 0 END) as be_count
             FROM marks m
             JOIN subjects sub ON sub.id=m.subject_id
             JOIN students s ON s.id=m.student_id
             JOIN exams e ON e.id=m.exam_id
             WHERE s.school_id=? AND e.term_id=?
             GROUP BY sub.id ORDER BY avg_score DESC",
            [$sid, $termId]
        );

        // At-risk learners (BE in 2+ subjects)
        $atRisk = $this->db->fetchAll(
            "SELECT u.name, s.assessment_no, s.id as student_id,
                    COUNT(CASE WHEN m.grade='BE' THEN 1 END) as be_count,
                    AVG(m.score) as avg_score,
                    cl.name as class_name, st.name as stream_name
             FROM marks m
             JOIN students s ON s.id=m.student_id
             JOIN users u ON u.id=s.user_id
             JOIN exams e ON e.id=m.exam_id
             LEFT JOIN streams st ON st.id=s.stream_id
             LEFT JOIN classes cl ON cl.id=st.class_id
             WHERE s.school_id=? AND e.term_id=?
             GROUP BY s.id
             HAVING be_count >= 2
             ORDER BY be_count DESC, avg_score ASC
             LIMIT 20",
            [$sid, $termId]
        );

        // Fee collection analytics
        $feeAnalytics = $this->db->fetch(
            "SELECT
                SUM(fi.total_amount) as expected,
                SUM(fi.amount_paid) as collected,
                COUNT(CASE WHEN fi.status='paid' THEN 1 END) as fully_paid,
                COUNT(CASE WHEN fi.status='partial' THEN 1 END) as partial,
                COUNT(CASE WHEN fi.status='unpaid' THEN 1 END) as unpaid,
                COUNT(*) as total
             FROM fee_invoices fi
             JOIN students s ON s.id=fi.student_id
             WHERE s.school_id=? AND fi.term_id=?",
            [$sid, $termId]
        ) ?? [];

        // Monthly fee collection trend
        $feeMonthly = $this->db->fetchAll(
            "SELECT DATE_FORMAT(p.payment_date,'%b %Y') as month,
                    MONTH(p.payment_date) as m, YEAR(p.payment_date) as y,
                    SUM(p.amount) as total
             FROM payments p
             JOIN fee_invoices fi ON fi.id=p.invoice_id
             JOIN students s ON s.id=fi.student_id
             WHERE s.school_id=? AND YEAR(p.payment_date)=YEAR(NOW())
             GROUP BY y,m ORDER BY y,m",
            [$sid]
        );

        // Attendance rate per stream
        $attendanceByStream = $this->db->fetchAll(
            "SELECT CONCAT(cl.name,' ',st.name) as stream_name,
                    COUNT(CASE WHEN a.status='present' THEN 1 END) as present,
                    COUNT(*) as total
             FROM attendances a
             JOIN students s ON s.id=a.student_id
             JOIN streams st ON st.id=s.stream_id
             JOIN classes cl ON cl.id=st.class_id
             WHERE s.school_id=?
             AND a.date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY st.id ORDER BY stream_name",
            [$sid]
        );

        // Competency distribution
        $competencyStats = $this->db->fetchAll(
            "SELECT c.name, c.code,
                    COUNT(CASE WHEN cr.rating='EE' THEN 1 END) as ee,
                    COUNT(CASE WHEN cr.rating='ME' THEN 1 END) as me,
                    COUNT(CASE WHEN cr.rating='AE' THEN 1 END) as ae,
                    COUNT(CASE WHEN cr.rating='BE' THEN 1 END) as be
             FROM competencies c
             LEFT JOIN competency_ratings cr ON cr.competency_id=c.id AND cr.term_id=?
             LEFT JOIN students s ON s.id=cr.student_id AND s.school_id=?
             GROUP BY c.id",
            [$termId, $sid]
        );

        $this->view('admin/analytics/index', compact(
            'terms','termId','gradeDistribution','subjectPerformance',
            'atRisk','feeAnalytics','feeMonthly','attendanceByStream','competencyStats'
        ));
    }

    public function learnerProfile(string $studentId): void {
        $this->requireRole('super-admin', 'admin');

        $student = $this->db->fetch(
            "SELECT s.*, u.name, u.email, u.phone, u.avatar,
                    cl.name as class_name, st.name as stream_name
             FROM students s JOIN users u ON u.id=s.user_id
             LEFT JOIN streams st ON st.id=s.stream_id
             LEFT JOIN classes cl ON cl.id=st.class_id
             WHERE s.id=?", [(int)$studentId]
        );
        if (!$student) { $this->redirect('admin/analytics'); return; }

        // Performance per term (trend)
        $termTrend = $this->db->fetchAll(
            "SELECT t.name as term, ay.name as year,
                    AVG(m.score) as avg_score,
                    COUNT(CASE WHEN m.grade='EE' THEN 1 END) as ee,
                    COUNT(CASE WHEN m.grade='ME' THEN 1 END) as me,
                    COUNT(CASE WHEN m.grade='AE' THEN 1 END) as ae,
                    COUNT(CASE WHEN m.grade='BE' THEN 1 END) as be
             FROM marks m
             JOIN exams e ON e.id=m.exam_id
             JOIN terms t ON t.id=e.term_id
             JOIN academic_years ay ON ay.id=t.academic_year_id
             WHERE m.student_id=?
             GROUP BY t.id ORDER BY ay.name, t.id",
            [(int)$studentId]
        );

        // Per-subject performance
        $subjectBreakdown = $this->db->fetchAll(
            "SELECT sub.name as subject, AVG(m.score) as avg_score,
                    MAX(m.score) as best, MIN(m.score) as lowest,
                    COUNT(*) as assessments,
                    SUM(CASE WHEN m.grade='BE' THEN 1 ELSE 0 END) as be_count
             FROM marks m JOIN subjects sub ON sub.id=m.subject_id
             WHERE m.student_id=?
             GROUP BY sub.id ORDER BY avg_score DESC",
            [(int)$studentId]
        );

        // Competency ratings
        $competencies = $this->db->fetchAll(
            "SELECT c.name, c.code, cr.rating, cr.remarks,
                    t.name as term_name
             FROM competency_ratings cr
             JOIN competencies c ON c.id=cr.competency_id
             JOIN terms t ON t.id=cr.term_id
             WHERE cr.student_id=? ORDER BY cr.term_id DESC, c.name",
            [(int)$studentId]
        );

        // Attendance summary
        $attendance = $this->db->fetch(
            "SELECT
                COUNT(CASE WHEN status='present' THEN 1 END) as present,
                COUNT(CASE WHEN status='absent' THEN 1 END) as absent,
                COUNT(CASE WHEN status='late' THEN 1 END) as late,
                COUNT(*) as total
             FROM attendances WHERE student_id=?", [(int)$studentId]
        ) ?? [];

        // AI-style insights
        $insights = $this->generateInsights($student, $termTrend, $subjectBreakdown, $attendance);

        $this->view('admin/analytics/learner', compact(
            'student','termTrend','subjectBreakdown','competencies','attendance','insights'
        ));
    }

    private function generateInsights(array $student, array $trend, array $subjects, array $att): array {
        $insights = [];
        if (count($trend) >= 2) {
            $last = end($trend);
            $prev = $trend[count($trend)-2];
            $diff = ($last['avg_score'] ?? 0) - ($prev['avg_score'] ?? 0);
            if ($diff >= 5)      $insights[] = ['type'=>'success','msg'=>'Performance improved by '.round($diff,1).'% compared to last term.'];
            elseif ($diff <= -5) $insights[] = ['type'=>'danger', 'msg'=>'Performance dropped by '.round(abs($diff),1).'% — needs attention.'];
        }
        foreach ($subjects as $s) {
            if (($s['avg_score'] ?? 100) < 50)
                $insights[] = ['type'=>'warning','msg'=>$s['subject'].' is below ME level (avg: '.round($s['avg_score'],1).'%). Consider intervention.'];
        }
        $total = $att['total'] ?? 0;
        if ($total > 0) {
            $rate = round(($att['present'] / $total) * 100, 1);
            if ($rate < 75) $insights[] = ['type'=>'danger','msg'=>"Attendance rate is {$rate}% — below the 75% threshold."];
        }
        if (empty($insights)) $insights[] = ['type'=>'success','msg'=>'Learner is performing well across all areas.'];
        return $insights;
    }
}

