<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Admission extends Model {
    public function search($params = []) {
        $sql = "SELECT a.*, s.name as student_name, s.student_id as student_id_code, s.photo as student_photo,
                c.name as course_name, b.name as batch_name,
                (SELECT i.id FROM invoices i WHERE i.admission_id = a.id LIMIT 1) as invoice_id
                FROM admissions a 
                INNER JOIN students s ON a.student_id = s.id 
                INNER JOIN courses c ON a.course_id = c.id 
                LEFT JOIN batches b ON a.batch_id = b.id 
                WHERE 1=1";
        $binds = [];

        if (!empty($params['search'])) {
            $sql .= " AND (s.name LIKE :search OR s.student_id LIKE :search)";
            $binds['search'] = '%' . $params['search'] . '%';
        }

        if (!empty($params['filter_course'])) {
            $sql .= " AND a.course_id = :course_id";
            $binds['course_id'] = $params['filter_course'];
        }

        $sql .= " ORDER BY a.id DESC";
        
        // Handle pagination
        $page = isset($params['page']) ? (int)$params['page'] : 1;
        $perPage = 10;
        $offset = ($page - 1) * $perPage;
        
        // Get total count for pagination
        $countSql = "SELECT COUNT(*) FROM admissions a 
                     INNER JOIN students s ON a.student_id = s.id 
                     INNER JOIN courses c ON a.course_id = c.id 
                     LEFT JOIN batches b ON a.batch_id = b.id 
                     WHERE 1=1";
        
        if (!empty($params['search'])) {
            $countSql .= " AND (s.name LIKE :search OR s.student_id LIKE :search)";
        }

        if (!empty($params['filter_course'])) {
            $countSql .= " AND a.course_id = :course_id";
        }

        $countStmt = $this->db->prepare($countSql);
        if (!empty($binds)) {
            foreach ($binds as $key => $val) {
                $countStmt->bindValue(":$key", $val);
            }
        }
        $countStmt->execute();
        $totalRecords = $countStmt->fetchColumn();
        
        // Add limit and offset
        $sql .= " LIMIT :limit OFFSET :offset";
        $stmt = $this->db->prepare($sql);
        
        foreach ($binds as $key => $val) {
            $stmt->bindValue(":$key", $val);
        }
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        
        $stmt->execute();
        $records = $stmt->fetchAll();

        return [
            'data' => $records,
            'total' => $totalRecords,
            'page' => $page,
            'per_page' => $perPage,
            'last_page' => ceil($totalRecords / $perPage)
        ];
    }

    public function find($id) {
        $stmt = $this->db->prepare("SELECT * FROM admissions WHERE id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        return $stmt->fetch();
    }

    public function save($data) {
        if (!empty($data['id'])) {
            $sql = "UPDATE admissions SET student_id = :student_id, course_id = :course_id, 
                    batch_id = :batch_id, admission_date = :admission_date, status = :status 
                    WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([
                'id' => $data['id'],
                'student_id' => $data['student_id'],
                'course_id' => $data['course_id'],
                'batch_id' => $data['batch_id'],
                'admission_date' => $data['admission_date'],
                'status' => $data['status']
            ]);
        } else {
            $sql = "INSERT INTO admissions (student_id, course_id, batch_id, admission_date, status) 
                    VALUES (:student_id, :course_id, :batch_id, :admission_date, :status)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                'student_id' => $data['student_id'],
                'course_id' => $data['course_id'],
                'batch_id' => $data['batch_id'],
                'admission_date' => $data['admission_date'],
                'status' => $data['status']
            ]);
            return $this->db->lastInsertId();
        }
    }

    public function delete($id) {
        $stmt = $this->db->prepare("DELETE FROM admissions WHERE id = :id");
        return $stmt->execute(['id' => $id]);
    }
}
