<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Course extends Model {
    public function search($params = []) {
        $sql = "SELECT * FROM courses WHERE 1=1";
        $binds = [];

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

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

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

        foreach ($courses as &$course) {
            $course['fees'] = $this->getFees($course['id']);
        }

        return $courses;
    }

    public function getFees($courseId) {
        $stmt = $this->db->prepare("SELECT * FROM course_fees WHERE course_id = :course_id");
        $stmt->execute(['course_id' => $courseId]);
        return $stmt->fetchAll();
    }

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

    public function save($data, $fees = []) {
        $this->db->beginTransaction();
        try {
            if (!empty($data['id'])) {
                $sql = "UPDATE courses SET name = :name, category = :category, duration_months = :duration_months, status = :status WHERE id = :id";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    'name' => $data['name'],
                    'category' => $data['category'] ?? null,
                    'duration_months' => $data['duration_months'] ?? 0,
                    'status' => $data['status'] ?? 'Active',
                    'id' => $data['id']
                ]);
                $courseId = $data['id'];
            } else {
                $sql = "INSERT INTO courses (name, category, duration_months, status) VALUES (:name, :category, :duration_months, :status)";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    'name' => $data['name'],
                    'category' => $data['category'] ?? null,
                    'duration_months' => $data['duration_months'] ?? 0,
                    'status' => $data['status'] ?? 'Active'
                ]);
                $courseId = $this->db->lastInsertId();
            }

            // Sync Fees
            $this->db->prepare("DELETE FROM course_fees WHERE course_id = :course_id")->execute(['course_id' => $courseId]);
            foreach ($fees as $fee) {
                $stmt = $this->db->prepare("INSERT INTO course_fees (course_id, fee_type, amount, frequency) VALUES (:course_id, :fee_type, :amount, :frequency)");
                $stmt->execute([
                    'course_id' => $courseId,
                    'fee_type' => $fee['fee_type'],
                    'amount' => $fee['amount'],
                    'frequency' => $fee['frequency'] ?? 'One-time'
                ]);
            }

            $this->db->commit();
            return $courseId;
        } catch (\Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

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