<?php

namespace App\Models;

use CodeIgniter\Model;

class TabunganModel extends Model
{
    protected $table            = 'tabungan_transactions';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $allowedFields    = [
        'transaction_number', 'student_id', 'transaction_type',
        'amount', 'balance_after', 'transaction_date', 'notes',
        'recorded_by', 'academic_period_id',
    ];
    protected $useTimestamps    = true;

    public function getBalance($studentId, $periodId)
    {
        $last = $this->where('student_id', $studentId)
            ->where('academic_period_id', $periodId)
            ->orderBy('id', 'DESC')
            ->first();

        return $last ? (float) $last['balance_after'] : 0;
    }

    public function getHistory($studentId, $periodId)
    {
        return $this->select('tabungan_transactions.*, users.name as recorded_by_name')
            ->join('users', 'users.id = tabungan_transactions.recorded_by', 'left')
            ->where('tabungan_transactions.student_id', $studentId)
            ->where('tabungan_transactions.academic_period_id', $periodId)
            ->orderBy('tabungan_transactions.id', 'DESC')
            ->findAll();
    }

    public function getHistoryByPeriod($studentId, $periodId)
    {
        return $this->select('tabungan_transactions.*, users.name as recorded_by_name')
            ->join('users', 'users.id = tabungan_transactions.recorded_by', 'left')
            ->where('tabungan_transactions.student_id', $studentId)
            ->where('tabungan_transactions.academic_period_id', $periodId)
            ->orderBy('tabungan_transactions.transaction_date', 'ASC')
            ->orderBy('tabungan_transactions.id', 'ASC')
            ->findAll();
    }

    public function generateTransactionNumber($date)
    {
        $prefix = 'TRX-' . date('Ymd', strtotime($date));

        $last = $this->where('transaction_number LIKE', $prefix . '%')
            ->orderBy('id', 'DESC')
            ->first();

        if ($last && preg_match('/-(\d{3})$/', $last['transaction_number'], $m)) {
            $seq = (int) $m[1] + 1;
        } else {
            $seq = 1;
        }

        return $prefix . '-' . str_pad($seq, 3, '0', STR_PAD_LEFT);
    }

    public function getReportByClass($periodId, $classroomId = null)
    {
        $periodId = (int) $periodId;

        $builder = $this->db->table('student_academic_histories sah')
            ->select("
                s.id AS student_id,
                s.nis,
                s.full_name,
                s.father_phone,
                s.mother_phone,
                c.name AS classroom_name,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'setoran' THEN tt.amount ELSE 0 END), 0) AS total_setoran,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'penarikan' THEN tt.amount ELSE 0 END), 0) AS total_penarikan,
                COALESCE((
                    SELECT t2.balance_after
                    FROM tabungan_transactions t2
                    WHERE t2.student_id = s.id
                      AND t2.academic_period_id = {$periodId}
                    ORDER BY t2.id DESC
                    LIMIT 1
                ), 0) AS balance
            ")
            ->join('students s', 's.id = sah.student_id')
            ->join('classrooms c', 'c.id = sah.classroom_id')
            ->join('tabungan_transactions tt', 'tt.student_id = s.id AND tt.academic_period_id = ' . $periodId, 'left')
            ->where('sah.academic_period_id', $periodId)
            ->where('s.deleted_at', null)
            ->where('s.status', 'Aktif')
            ->groupBy('s.id, s.nis, s.full_name, c.name')
            ->orderBy('c.name', 'ASC')
            ->orderBy('s.full_name', 'ASC');

        if ($classroomId) {
            $builder->where('sah.classroom_id', $classroomId);
        }

        return $builder->get()->getResultArray();
    }

    public function getDailyReport($periodId, $date, array $studentIds)
    {
        $periodId = (int) $periodId;
        $date     = date('Y-m-d', strtotime($date));
        $dateEsc  = $this->db->escape($date);

        $builder = $this->db->table('student_academic_histories sah')
            ->select("
                s.id AS student_id,
                s.nis,
                s.full_name,
                s.father_phone,
                s.mother_phone,
                c.name AS classroom_name,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'setoran' THEN tt.amount ELSE 0 END), 0) AS total_setoran,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'penarikan' THEN tt.amount ELSE 0 END), 0) AS total_penarikan,
                COALESCE((
                    SELECT t2.balance_after
                    FROM tabungan_transactions t2
                    WHERE t2.student_id = s.id
                      AND t2.academic_period_id = {$periodId}
                      AND t2.transaction_date <= {$dateEsc}
                    ORDER BY t2.id DESC
                    LIMIT 1
                ), 0) AS balance
            ")
            ->join('students s', 's.id = sah.student_id')
            ->join('classrooms c', 'c.id = sah.classroom_id')
            ->join('tabungan_transactions tt', "tt.student_id = s.id AND tt.academic_period_id = {$periodId} AND tt.transaction_date = {$dateEsc}", 'left')
            ->where('sah.academic_period_id', $periodId)
            ->where('s.deleted_at', null)
            ->where('s.status', 'Aktif')
            ->whereIn('s.id', $studentIds)
            ->groupBy('s.id, s.nis, s.full_name, s.father_phone, s.mother_phone, c.name')
            ->orderBy('c.name', 'ASC')
            ->orderBy('s.full_name', 'ASC');

        return $builder->get()->getResultArray();
    }

    public function getMonthlyReport($periodId, $year, $month, array $studentIds)
    {
        $periodId  = (int) $periodId;
        $year      = (int) $year;
        $month     = (int) $month;
        $startDate = date('Y-m-d', strtotime("{$year}-{$month}-01"));
        $endDate   = date('Y-m-t', strtotime("{$year}-{$month}-01"));
        $startEsc  = $this->db->escape($startDate);
        $endEsc    = $this->db->escape($endDate);

        $builder = $this->db->table('student_academic_histories sah')
            ->select("
                s.id AS student_id,
                s.nis,
                s.full_name,
                s.father_phone,
                s.mother_phone,
                c.name AS classroom_name,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'setoran' THEN tt.amount ELSE 0 END), 0) AS total_setoran,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'penarikan' THEN tt.amount ELSE 0 END), 0) AS total_penarikan,
                COALESCE((
                    SELECT t2.balance_after
                    FROM tabungan_transactions t2
                    WHERE t2.student_id = s.id
                      AND t2.academic_period_id = {$periodId}
                      AND t2.transaction_date <= {$endEsc}
                    ORDER BY t2.id DESC
                    LIMIT 1
                ), 0) AS balance
            ")
            ->join('students s', 's.id = sah.student_id')
            ->join('classrooms c', 'c.id = sah.classroom_id')
            ->join('tabungan_transactions tt', "tt.student_id = s.id AND tt.academic_period_id = {$periodId} AND tt.transaction_date >= {$startEsc} AND tt.transaction_date <= {$endEsc}", 'left')
            ->where('sah.academic_period_id', $periodId)
            ->where('s.deleted_at', null)
            ->where('s.status', 'Aktif')
            ->whereIn('s.id', $studentIds)
            ->groupBy('s.id, s.nis, s.full_name, s.father_phone, s.mother_phone, c.name')
            ->orderBy('c.name', 'ASC')
            ->orderBy('s.full_name', 'ASC');

        return $builder->get()->getResultArray();
    }

    public function getYearlyReport($periodId, array $studentIds)
    {
        $periodId = (int) $periodId;

        $builder = $this->db->table('student_academic_histories sah')
            ->select("
                s.id AS student_id,
                s.nis,
                s.full_name,
                s.father_phone,
                s.mother_phone,
                c.name AS classroom_name,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'setoran' THEN tt.amount ELSE 0 END), 0) AS total_setoran,
                COALESCE(SUM(CASE WHEN tt.transaction_type = 'penarikan' THEN tt.amount ELSE 0 END), 0) AS total_penarikan,
                COALESCE((
                    SELECT t2.balance_after
                    FROM tabungan_transactions t2
                    WHERE t2.student_id = s.id
                      AND t2.academic_period_id = {$periodId}
                    ORDER BY t2.id DESC
                    LIMIT 1
                ), 0) AS balance
            ")
            ->join('students s', 's.id = sah.student_id')
            ->join('classrooms c', 'c.id = sah.classroom_id')
            ->join('tabungan_transactions tt', "tt.student_id = s.id AND tt.academic_period_id = {$periodId}", 'left')
            ->where('sah.academic_period_id', $periodId)
            ->where('s.deleted_at', null)
            ->where('s.status', 'Aktif')
            ->whereIn('s.id', $studentIds)
            ->groupBy('s.id, s.nis, s.full_name, s.father_phone, s.mother_phone, c.name')
            ->orderBy('c.name', 'ASC')
            ->orderBy('s.full_name', 'ASC');

        return $builder->get()->getResultArray();
    }
}