<?php

namespace App\Models;

use CodeIgniter\Model;

class PaymentModel extends Model
{
    protected $table = 'payment';

    protected $primaryKey = 'id';

    protected $allowedFields = [
        'class_id',
        'class_diary_id',
        'student_id',
        'fee',
        'paid_date',
        'status',
        'payment_method_id'
    ];

    public function getCurrentMonthTotal()
    {
        return $this->selectSum('fee')
            ->where('YEAR(paid_date)', date('Y'))
            ->where('MONTH(paid_date)', date('m'))
            ->where('status', 1)
            ->first()['fee'] ?? 0;
    }

    public function getPendingFeeCount($studentId)
    {
        return $this->select("
            SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS pending_count,
            SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS paid_count
        ")
        ->where('student_id', $studentId)
        ->first();
    }

    public function getMonthlyIncome()
    {
        return $this->select("
                MONTH(paid_date) AS month_no,
                DATE_FORMAT(paid_date, '%b') AS month_name,
                SUM(fee) AS total_income
            ")
            ->where('YEAR(paid_date)', date('Y'))
            ->groupBy('MONTH(paid_date)')
            ->orderBy('MONTH(paid_date)', 'ASC')
            ->findAll();
    }

    public function getAllPendingPayments()
    {
        return $this->db->table('payment')
            ->select("
            student.id AS student_id,
            CONCAT(student.first_name,' ',student.last_name) AS student_name,

            class.id AS class_id,
            class.name AS class_name,
            class.code AS class_code,

            class_student.fee AS class_fee,

            CONCAT(
                COUNT(DISTINCT class_diary.id),
                ' (',
                GROUP_CONCAT(
                    DISTINCT DATE_FORMAT(class_diary.date,'%Y-%m-%d')
                    ORDER BY class_diary.date
                    SEPARATOR ', '
                ),
                ')'
            ) AS class_dates,

            0 AS paid_amount,

            class_student.fee AS pending_amount
        ", false)

            ->join(
                'student',
                'student.id = payment.student_id',
                'left'
            )

            ->join(
                'class',
                'class.id = payment.class_id',
                'left'
            )

            ->join(
                'class_student',
                'class_student.class_id = payment.class_id
             AND class_student.student_id = payment.student_id',
                'left'
            )

            ->join(
                'class_diary',
                'class_diary.id = payment.class_diary_id',
                'left'
            )

            ->join(
                'class_attendance',
                'class_attendance.class_diary_id = payment.class_diary_id
             AND class_attendance.student_id = payment.student_id',
                'left'
            )

            ->where('class_student.status', 1)
            ->where('class_attendance.attendance_status', 1)

            ->groupStart()
            ->where('payment.fee IS NULL', null, false)
            ->orWhere('payment.fee', '')
            ->orWhere('payment.fee', 0)
            ->groupEnd()

            ->groupBy([
                'payment.student_id',
                'payment.class_id'
            ])

            ->orderBy('student.first_name', 'ASC')

            ->get()
            ->getResultArray();
    }

    //protected $useTimestamps = true;
}