<?php

namespace App\Repositories;

use App\Models\Contract;
use App\Models\ContractDetail;

class ReportRepository
{
    /**
     * Contratos con relaciones para reporte maestro de matrículas.
     */
    public function getContractsForReport($periodId = null, $filters = [])
    {
        $query = Contract::with([
            'period',
            'statusContract',
            'statusPayment',
            'statusSignature',
            'financialParent.relationship',
            'financialParent.user',
            'details.student.course',
            'details.student.courseLetter',
            'details.studentCourse',
            'details.studentCourseLetter',
            'details.paymentConceptDetail.payment_concept',
            'details.paymentConceptDetail.currency_type',
        ])->orderBy('id', 'desc');

        $this->applyCommonFilters($query, $periodId, $filters);

        return $query->get();
    }

    /**
     * Contratos con relaciones para reporte financiero.
     */
    public function getContractsForFinancialReport($periodId = null, $filters = [])
    {
        $query = Contract::with([
            'period',
            'statusContract',
            'statusPayment',
            'statusSignature',
            'financialParent.relationship',
            'financialParent.user',
            'presencialPayments.paymentMethodRecord',
            'details.student.course',
            'details.student.courseLetter',
            'details.studentCourse',
            'details.studentCourseLetter',
            'details.paymentConceptDetail.payment_concept',
            'details.paymentConceptDetail.currency_type',
        ])->orderBy('id', 'desc');

        $this->applyCommonFilters($query, $periodId, $filters);

        if (!empty($filters['fecha_desde'])) {
            $query->whereDate('date_contract', '>=', $filters['fecha_desde']);
        }
        if (!empty($filters['fecha_hasta'])) {
            $query->whereDate('date_contract', '<=', $filters['fecha_hasta']);
        }

        return $query->get();
    }

    /**
     * Contratos por período para reporte comparativo (stats + financiero).
     */
    public function getContractsByPeriodForComparative($periodId)
    {
        return Contract::with([
            'statusContract',
            'statusPayment',
            'details.studentCourse',
            'details.paymentConceptDetail.payment_concept',
            'details.paymentConceptDetail.currency_type',
        ])->where('period_id', $periodId)->get();
    }

    /**
     * Student IDs activos (no cancelados) en un período.
     */
    public function activeStudentIdsForPeriod($periodId)
    {
        return ContractDetail::whereHas('contract', function ($q) use ($periodId) {
            $q->where('period_id', $periodId)
              ->whereHas('statusContract', fn($sc) => $sc->where('code', '!=', 'canceled'));
        })->whereNotNull('student_id')
            ->distinct()
            ->pluck('student_id');
    }

    /**
     * Contratos que contienen ciertos estudiantes en un período (para movimientos).
     */
    public function getContractsForStudentIds($studentIds, $periodId)
    {
        return Contract::with([
            'period',
            'statusContract',
            'statusPayment',
            'statusSignature',
            'financialParent.relationship',
            'details.student.course',
            'details.student.courseLetter',
            'details.studentCourse',
            'details.studentCourseLetter',
            'details.paymentConceptDetail.payment_concept',
            'details.paymentConceptDetail.currency_type',
        ])
            ->where('period_id', $periodId)
            ->whereHas('statusContract', fn($q) => $q->where('code', '!=', 'canceled'))
            ->whereHas('details', fn($q) => $q->whereIn('student_id', $studentIds))
            ->orderBy('id', 'desc')
            ->get();
    }

    /**
     * Contratos con relaciones para reporte Odoo.
     */
    public function getContractsForOdooReport($periodId = null, $filters = [])
    {
        $query = Contract::with([
            'period',
            'statusContract',
            'financialParent',
            'details.student.course',
            'details.studentCourse',
            'details.paymentConceptDetail.payment_concept',
            'details.paymentConceptDetail.currency_type',
        ])->orderBy('id', 'asc');

        $this->applyCommonFilters($query, $periodId, $filters);

        // Excluir cancelados por defecto si no se filtra explícitamente
        if (empty($filters['status_contract'])) {
            $query->whereHas('statusContract', fn($q) => $q->where('code', '!=', 'canceled'));
        }

        return $query->get();
    }

    /**
     * Aplica filtros comunes a la query de contratos.
     */
    protected function applyCommonFilters($query, $periodId, $filters)
    {
        if ($periodId > 0) {
            $query->where('period_id', $periodId);
        }

        if (!empty($filters['status_contract'])) {
            $query->whereHas('statusContract', fn($q) => $q->where('code', $filters['status_contract']));
        }

        if (!empty($filters['status_payment'])) {
            $query->whereHas('statusPayment', fn($q) => $q->where('code', $filters['status_payment']));
        }

        if (!empty($filters['status_signature'])) {
            $query->whereHas('statusSignature', fn($q) => $q->where('code', $filters['status_signature']));
        }

        if (!empty($filters['financial_parent_id'])) {
            $query->where('financial_parent_id', $filters['financial_parent_id']);
        }

        if (!empty($filters['course_id'])) {
            $query->whereHas('details', fn($q) => $q->where('student_course_id', $filters['course_id']));
        }

        if (!empty($filters['concept'])) {
            $query->whereHas('details.paymentConceptDetail.payment_concept', fn($q) => $q->where('code', $filters['concept']));
        }

        if (!empty($filters['registration_years'])) {
            $years = is_array($filters['registration_years'])
                ? $filters['registration_years']
                : explode(',', $filters['registration_years']);
            $query->whereIn('registration_year', $years);
        }

        // Filtro por fecha de pago de matrícula:
        // detalles paid=1 con paid_at en el rango, cuyo concepto sea matrícula (ENROLLMENT/ENROLLMENT_PG)
        $matriculaDesde = $filters['fecha_pago_matricula_desde'] ?? null;
        $matriculaHasta = $filters['fecha_pago_matricula_hasta'] ?? null;
        if (!empty($matriculaDesde) || !empty($matriculaHasta)) {
            $query->whereHas('details', function ($q) use ($matriculaDesde, $matriculaHasta) {
                $q->where('paid', true)
                    ->whereHas('paymentConceptDetail', function ($pcd) {
                        $pcd->where('code', 'NOT LIKE', 'CUOTA_INCORPORACION%')
                            ->whereHas('payment_concept', fn($pc) => $pc->where('code', 'matricula'));
                    });
                if (!empty($matriculaDesde)) {
                    $q->whereDate('paid_at', '>=', $matriculaDesde);
                }
                if (!empty($matriculaHasta)) {
                    $q->whereDate('paid_at', '<=', $matriculaHasta);
                }
            });
        }
    }
}