<?php
namespace Models;
use Core\Model;

class Exam extends Model {
    protected string $table = 'exams';

    public function getBySchool(int $schoolId, int $page = 1): array {
        $sql = "SELECT e.*, t.name AS term_name, ay.name AS year_name
                FROM exams e
                JOIN terms t ON t.id = e.term_id
                JOIN academic_years ay ON ay.id = t.academic_year_id
                WHERE e.school_id = ? ORDER BY e.id DESC";
        return $this->db->paginate($sql, [$schoolId], $page, 15);
    }

    public function getPublished(int $schoolId): array {
        return $this->db->fetchAll(
            "SELECT e.*, t.name AS term_name FROM exams e
             JOIN terms t ON t.id = e.term_id
             WHERE e.school_id=? AND e.is_published=1 ORDER BY e.id DESC", [$schoolId]
        );
    }
}

class Term extends Model {
    protected string $table = 'terms';
    public function getCurrent(int $schoolId): ?array {
        return $this->db->fetch(
            "SELECT t.* 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", [$schoolId]
        );
    }
    public function getBySchool(int $schoolId): array {
        return $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", [$schoolId]
        );
    }
}

class AcademicYear extends Model {
    protected string $table = 'academic_years';
    public function getBySchool(int $schoolId): array {
        return $this->where('school_id=?', [$schoolId], 'id DESC');
    }
}

class SchoolClass extends Model {
    protected string $table = 'classes';
    public function getBySchool(int $schoolId): array {
        return $this->where('school_id=?', [$schoolId], 'level ASC');
    }
}

class Stream extends Model {
    protected string $table = 'streams';
    public function getBySchool(int $schoolId): array {
        return $this->db->fetchAll(
            "SELECT st.*, cl.name AS class_name, cl.level,
                    CONCAT(cl.name,' ',st.name) AS full_name
             FROM streams st JOIN classes cl ON cl.id = st.class_id
             WHERE cl.school_id=? ORDER BY cl.level, st.name", [$schoolId]
        );
    }
    public function getByClass(int $classId): array {
        return $this->where('class_id=?', [$classId], 'name ASC');
    }
    public function getWithClass(int $id): ?array {
        return $this->db->fetch(
            "SELECT st.*, cl.name AS class_name, cl.school_id
             FROM streams st JOIN classes cl ON cl.id = st.class_id
             WHERE st.id=?", [$id]
        );
    }
}

class Subject extends Model {
    protected string $table = 'subjects';
    public function getBySchool(int $schoolId): array {
        return $this->db->fetchAll(
            "SELECT s.*, d.name AS dept_name FROM subjects s
             LEFT JOIN departments d ON d.id = s.department_id
             WHERE s.school_id=? ORDER BY s.name", [$schoolId]
        );
    }
}

class Department extends Model {
    protected string $table = 'departments';
    public function getBySchool(int $schoolId): array {
        return $this->where('school_id=?', [$schoolId], 'name ASC');
    }
}

class Teacher extends Model {
    protected string $table = 'teachers';
    public function getByUserId(int $userId): ?array {
        return $this->db->fetch(
            "SELECT t.*, u.name, u.email, u.phone FROM teachers t
             JOIN users u ON u.id = t.user_id WHERE t.user_id=?", [$userId]
        );
    }
    public function countBySchool(int $schoolId): int {
        return (int)($this->db->fetch("SELECT COUNT(*) as c FROM teachers WHERE school_id=?", [$schoolId])['c'] ?? 0);
    }
    public function getSubjectStreams(int $teacherId, int $termId): array {
        return $this->db->fetchAll(
            "SELECT tss.*, sub.name AS subject_name, st.name AS stream_name,
                    cl.name AS class_name
             FROM teacher_subject_streams tss
             JOIN subjects sub ON sub.id = tss.subject_id
             JOIN streams st ON st.id = tss.stream_id
             JOIN classes cl ON cl.id = st.class_id
             WHERE tss.teacher_id=? AND tss.term_id=?", [$teacherId, $termId]
        );
    }
}

class ParentModel extends Model {
    protected string $table = 'parents';
    public function getByUserId(int $userId): ?array {
        return $this->findBy('user_id', $userId);
    }
    public function getStudents(int $parentId): array {
        return $this->db->fetchAll(
            "SELECT s.*, u.name, u.email, cl.name AS class_name, st.name AS stream_name
             FROM parent_student ps
             JOIN students s ON s.id = ps.student_id
             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 ps.parent_id=?", [$parentId]
        );
    }
}

class Attendance extends Model {
    protected string $table = 'attendances';
    public function getSummary(int $schoolId, string $date): array {
        $rows = $this->db->fetchAll(
            "SELECT a.status, COUNT(*) as cnt FROM attendances a
             JOIN students s ON s.id = a.student_id
             WHERE s.school_id=? AND a.date=?
             GROUP BY a.status", [$schoolId, $date]
        );
        $out = ['present'=>0,'absent'=>0,'late'=>0,'excused'=>0];
        foreach ($rows as $r) $out[$r['status']] = (int)$r['cnt'];
        return $out;
    }
    public function getByStudentDate(int $studentId, string $date): ?array {
        return $this->db->fetch("SELECT * FROM attendances WHERE student_id=? AND date=?", [$studentId, $date]);
    }
    public function upsert(int $studentId, int $streamId, string $date, string $status, int $recordedBy): void {
        $existing = $this->getByStudentDate($studentId, $date);
        $data = ['status'=>$status,'recorded_by'=>$recordedBy,'updated_at'=>date('Y-m-d H:i:s')];
        if ($existing) {
            $this->db->update('attendances', $data, 'id=?', [$existing['id']]);
        } else {
            $this->db->insert('attendances', array_merge($data, [
                'student_id'=>$studentId,'stream_id'=>$streamId,
                'date'=>$date,'created_at'=>date('Y-m-d H:i:s'),
            ]));
        }
    }
    public function getStudentSummary(int $studentId): array {
        $rows = $this->db->fetchAll(
            "SELECT status, COUNT(*) as cnt FROM attendances WHERE student_id=? GROUP BY status", [$studentId]
        );
        $out = ['present'=>0,'absent'=>0,'late'=>0,'excused'=>0];
        foreach ($rows as $r) $out[$r['status']] = (int)$r['cnt'];
        return $out;
    }
}

class FeeInvoice extends Model {
    protected string $table = 'fee_invoices';
    public function getWithDetails(int $schoolId, array $filters = [], int $page = 1, int $perPage = 20): array {
        $where = ['s.school_id=?'];
        $params = [$schoolId];
        if (!empty($filters['status']))      { $where[] = 'fi.status=?';       $params[] = $filters['status']; }
        if (!empty($filters['term_id']))     { $where[] = 'fi.term_id=?';      $params[] = $filters['term_id']; }
        if (!empty($filters['class_id']))    { $where[] = 'cl.id=?';           $params[] = $filters['class_id']; }
        if (!empty($filters['stream_id']))   { $where[] = 'st.id=?';           $params[] = $filters['stream_id']; }
        if (isset($filters['balance_min']) && $filters['balance_min'] !== '') { $where[] = '(fi.total_amount + IFNULL(fi.opening_balance,0) - fi.amount_paid) >= ?'; $params[] = (float)$filters['balance_min']; }
        if (isset($filters['balance_max']) && $filters['balance_max'] !== '') { $where[] = '(fi.total_amount + IFNULL(fi.opening_balance,0) - fi.amount_paid) <= ?'; $params[] = (float)$filters['balance_max']; }
        if (!empty($filters['search']))      { $where[] = '(u.name LIKE ? OR s.assessment_no LIKE ?)'; $params[] = '%'.$filters['search'].'%'; $params[] = '%'.$filters['search'].'%'; }
        $w = implode(' AND ', $where);
        $sql = "SELECT fi.*, u.name AS student_name, s.assessment_no,
                       cl.name AS class_name, st.name AS stream_name, t.name AS term_name,
                       (fi.total_amount + IFNULL(fi.opening_balance,0) - fi.amount_paid) AS outstanding
                FROM fee_invoices fi
                JOIN students s ON s.id = fi.student_id
                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
                JOIN terms t ON t.id = fi.term_id
                WHERE $w ORDER BY fi.id DESC";
        return $this->db->paginate($sql, $params, $page, $perPage);
    }
    public function getSummary(int $schoolId, int $termId = 0): array {
        $where = 's.school_id=?';
        $params = [$schoolId];
        if ($termId) { $where .= ' AND fi.term_id=?'; $params[] = $termId; }
        return $this->db->fetch(
            "SELECT SUM(fi.total_amount) AS expected, SUM(fi.amount_paid) AS collected,
                    COUNT(*) AS total_students, COUNT(*) AS total_invoices,
                    SUM(CASE WHEN fi.status='paid' THEN 1 ELSE 0 END) AS paid_count,
                    SUM(CASE WHEN fi.status='partial' THEN 1 ELSE 0 END) AS partial_count,
                    SUM(CASE WHEN fi.status='unpaid' THEN 1 ELSE 0 END) AS unpaid_count
             FROM fee_invoices fi JOIN students s ON s.id=fi.student_id
             WHERE $where", $params
        ) ?? ['expected'=>0,'collected'=>0,'total_students'=>0,'total_invoices'=>0,'paid_count'=>0,'partial_count'=>0,'unpaid_count'=>0];
    }
    public function getByStudent(int $studentId): array {
        return $this->db->fetchAll(
            "SELECT fi.*, t.name AS term_name FROM fee_invoices fi
             JOIN terms t ON t.id = fi.term_id
             WHERE fi.student_id=? ORDER BY fi.id DESC", [$studentId]
        );
    }
    public function updatePaid(int $invoiceId): void {
        $total = $this->db->fetch("SELECT SUM(amount) as t FROM payments WHERE invoice_id=?", [$invoiceId])['t'] ?? 0;
        $inv   = $this->find($invoiceId);
        $bal   = $inv['total_amount'] - $total;
        $status = $total >= $inv['total_amount'] ? 'paid' : ($total > 0 ? 'partial' : 'unpaid');
        $this->update($invoiceId, ['amount_paid'=>$total,'balance'=>$bal,'status'=>$status]);
    }
}

class Payment extends Model {
    protected string $table = 'payments';
    public function getByInvoice(int $invoiceId): array {
        return $this->where('invoice_id=?', [$invoiceId], 'id DESC');
    }
    public function getWithDetails(int $id): ?array {
        return $this->db->fetch(
            "SELECT p.*, fi.total_amount, fi.amount_paid, fi.student_id,
                    u.name AS student_name, s.assessment_no, t.name AS term_name,
                    ru.name AS received_by_name
             FROM payments p
             JOIN fee_invoices fi ON fi.id = p.invoice_id
             JOIN students s ON s.id = fi.student_id
             JOIN users u ON u.id = s.user_id
             JOIN terms t ON t.id = fi.term_id
             LEFT JOIN users ru ON ru.id = p.received_by
             WHERE p.id=?", [$id]
        );
    }
}

class FeeStructure extends Model {
    protected string $table = 'fee_structures';
    public function getBySchool(int $schoolId): array {
        return $this->db->fetchAll(
            "SELECT fs.*, t.name AS term_name, c.name AS class_name
             FROM fee_structures fs
             JOIN terms t ON t.id = fs.term_id
             LEFT JOIN classes c ON c.id = fs.class_id
             WHERE fs.school_id=? ORDER BY fs.id DESC", [$schoolId]
        );
    }
}

class Assignment extends Model {
    protected string $table = 'assignments';
    public function getByStream(int $streamId): array {
        return $this->db->fetchAll(
            "SELECT a.*, sub.name AS subject_name, u.name AS teacher_name
             FROM assignments a
             JOIN subjects sub ON sub.id = a.subject_id
             JOIN teachers t ON t.id = a.teacher_id
             JOIN users u ON u.id = t.user_id
             WHERE a.stream_id=? ORDER BY a.due_date DESC", [$streamId]
        );
    }
    public function getByTeacher(int $teacherId): array {
        return $this->db->fetchAll(
            "SELECT a.*, sub.name AS subject_name,
                    CONCAT(cl.name,' ',st.name) AS stream_name
             FROM assignments a
             JOIN subjects sub ON sub.id = a.subject_id
             JOIN streams st ON st.id = a.stream_id
             JOIN classes cl ON cl.id = st.class_id
             WHERE a.teacher_id=? ORDER BY a.created_at DESC LIMIT 20", [$teacherId]
        );
    }
}

class School extends Model {
    protected string $table = 'schools';
}

class Timetable extends Model {
    protected string $table = 'timetables';
    public function getByTeacher(int $teacherId): array {
        return $this->db->fetchAll(
            "SELECT tt.*, sub.name AS subject_name,
                    CONCAT(cl.name,' ',st.name) AS stream_name
             FROM timetables tt
             JOIN subjects sub ON sub.id = tt.subject_id
             JOIN streams st ON st.id = tt.stream_id
             JOIN classes cl ON cl.id = st.class_id
             WHERE tt.teacher_id=? ORDER BY tt.day_of_week, tt.start_time", [$teacherId]
        );
    }
}

