<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Student extends Model {
    public function search($params = []) {
        $sql = "SELECT s.*, 
                (SELECT COUNT(*) FROM admissions a WHERE a.student_id = s.id AND a.status = 'Active') as active_courses
                FROM students s 
                WHERE 1=1";
        $binds = [];

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

        $sql .= " ORDER BY s.id DESC";
        $stmt = $this->db->prepare($sql);
        if (!empty($binds)) {
            $stmt->execute($binds);
        } else {
            $stmt->execute();
        }
        $students = $stmt->fetchAll();

        foreach ($students as &$student) {
            $student['admissions'] = $this->getAdmissions($student['id']);
        }
        return $students;
    }

    public function getAdmissions($studentId) {
        $stmt = $this->db->prepare("SELECT a.*, c.name as course_name 
                                    FROM admissions a 
                                    JOIN courses c ON a.course_id = c.id 
                                    WHERE a.student_id = :student_id AND a.status = 'Active'");
        $stmt->execute(['student_id' => $studentId]);
        return $stmt->fetchAll();
    }

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

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

    public function generateStudentId() {
        $stmt = $this->db->query("SELECT id FROM students ORDER BY id DESC LIMIT 1");
        $lastId = $stmt->fetchColumn() ?: 0;
        return 'STU-' . date('Y') . '-' . str_pad($lastId + 1, 3, '0', STR_PAD_LEFT);
    }

    public function save($data, $password = null) {
        $inTransaction = $this->db->inTransaction();
        if (!$inTransaction) $this->db->beginTransaction();
        
        try {
            // If new student
            if (empty($data['id'])) {
                // Check if phone already exists to give a better error message
                $existing = $this->findByPhone($data['phone']);
                if ($existing) {
                    throw new \Exception("A student with this phone number already exists.");
                }

                $data['student_id'] = $this->generateStudentId();
                if (empty($data['email'])) {
                    $data['email'] = $data['phone'] . '@student.com';
                }
                
                $sql = "INSERT INTO students (student_id, name, phone, alt_phone, email, status, address, photo, guardian_name, guardian_phone) 
                        VALUES (:student_id, :name, :phone, :alt_phone, :email, :status, :address, :photo, :guardian_name, :guardian_phone)";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    'student_id' => $data['student_id'],
                    'name' => $data['name'],
                    'phone' => $data['phone'],
                    'alt_phone' => $data['alt_phone'] ?? null,
                    'email' => $data['email'],
                    'status' => $data['status'] ?? 'Active',
                    'address' => $data['address'] ?? null,
                    'photo' => $data['photo'] ?? null,
                    'guardian_name' => $data['guardian_name'] ?? null,
                    'guardian_phone' => $data['guardian_phone'] ?? null
                ]);
                $studentId = $this->db->lastInsertId();

                // Create User Account
                if ($password) {
                    $hash = password_hash($password, PASSWORD_DEFAULT);
                    $stmtUser = $this->db->prepare("INSERT INTO users (name, email, password, role) VALUES (:name, :email, :password, 'student')");
                    $stmtUser->execute([
                        'name' => $data['name'],
                        'email' => $data['email'],
                        'password' => $hash
                    ]);
                }
            } else {
                // Update
                $oldStudent = $this->find($data['id']);
                if (!$oldStudent) {
                    throw new \Exception("Student record not found.");
                }

                $sql = "UPDATE students SET 
                        name = :name, phone = :phone, alt_phone = :alt_phone, email = :email, 
                        status = :status, address = :address, photo = :photo, 
                        guardian_name = :guardian_name, guardian_phone = :guardian_phone 
                        WHERE id = :id";
                
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    'name' => $data['name'],
                    'phone' => $data['phone'],
                    'alt_phone' => $data['alt_phone'] ?? null,
                    'email' => $data['email'],
                    'status' => $data['status'] ?? 'Active',
                    'address' => $data['address'] ?? null,
                    'photo' => $data['photo'] ?? null,
                    'guardian_name' => $data['guardian_name'] ?? null,
                    'guardian_phone' => $data['guardian_phone'] ?? null,
                    'id' => $data['id']
                ]);
                $studentId = $data['id'];

                // Sync User Account (Name and Email might have changed)
                $stmtUser = $this->db->prepare("UPDATE users SET name = :name, email = :email WHERE email = :old_email");
                $stmtUser->execute([
                    'name' => $data['name'],
                    'email' => $data['email'],
                    'old_email' => $oldStudent['email']
                ]);

                // Update User Password if provided
                if ($password) {
                    $hash = password_hash($password, PASSWORD_DEFAULT);
                    $stmtPass = $this->db->prepare("UPDATE users SET password = :password WHERE email = :email");
                    $stmtPass->execute(['password' => $hash, 'email' => $data['email']]);
                }
            }

            if (!$inTransaction) $this->db->commit();
            return $studentId;
        } catch (\Exception $e) {
            if (!$inTransaction) $this->db->rollBack();
            throw $e;
        }
    }

    public function delete($id) {
        $student = $this->find($id);
        if ($student) {
            // Optionally delete photo
            if ($student['photo']) {
                $filePath = __DIR__ . '/../../public' . $student['photo'];
                if (file_exists($filePath)) unlink($filePath);
            }
            // Delete user account
            $stmtUser = $this->db->prepare("DELETE FROM users WHERE email = :email");
            $stmtUser->execute(['email' => $student['email']]);

            $stmt = $this->db->prepare("DELETE FROM students WHERE id = :id");
            return $stmt->execute(['id' => $id]);
        }
        return false;
    }
}
