<?php

declare(strict_types=1);

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

final class BackfillInvoiceStudentsList extends Migration
{
    public function up(): void
    {
        foreach (['invoice_students_list', 'invoices', 'enrollments', 'students', 'student_class'] as $table) {
            if (! $this->db->tableExists($table)) {
                return;
            }
        }

        $invoiceFilters = [];
        if ($this->db->fieldExists('invoice_number', 'invoices')) {
            $invoiceFilters[] = "COALESCE(i.invoice_number, '') NOT LIKE 'CF-%'";
        }
        if ($this->db->fieldExists('semester', 'invoices')) {
            $invoiceFilters[] = "LOWER(TRIM(COALESCE(i.semester, ''))) <> 'opening balance'";
        }
        if ($this->db->fieldExists('description', 'invoices')) {
            $invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%carried over%'";
            $invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%carry-forward%'";
            $invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%carry over%'";
            $invoiceFilters[] = "LOWER(COALESCE(i.description, '')) NOT LIKE '%previous school year%'";
        }

        $nonEventFilter = $this->db->fieldExists('is_event_only', 'student_class')
            ? 'AND COALESCE(sc.is_event_only, 0) = 0'
            : '';

        $where = $invoiceFilters === [] ? '' : 'AND ' . implode("\n            AND ", $invoiceFilters);

        $this->db->query(
            "INSERT INTO invoice_students_list (
                invoice_id,
                student_id,
                student_firstname,
                student_lastname,
                school_id,
                enrolled,
                tuition_fee,
                school_year,
                created_at,
                updated_at
            )
            SELECT
                i.id AS invoice_id,
                e.student_id,
                MIN(COALESCE(s.firstname, '')) AS student_firstname,
                MIN(COALESCE(s.lastname, '')) AS student_lastname,
                MIN(COALESCE(s.school_id, 0)) AS school_id,
                MAX(CASE
                    WHEN LOWER(TRIM(COALESCE(e.enrollment_status, ''))) IN ('enrolled', 'payment pending') THEN 1
                    ELSE 0
                END) AS enrolled,
                0.00 AS tuition_fee,
                i.school_year,
                UTC_TIMESTAMP(),
                UTC_TIMESTAMP()
            FROM invoices i
            INNER JOIN enrollments e
                ON e.parent_id = i.parent_id
               AND e.school_year = i.school_year
            INNER JOIN students s
                ON s.id = e.student_id
            WHERE LOWER(TRIM(COALESCE(e.enrollment_status, ''))) IN (
                    'enrolled',
                    'payment pending',
                    'withdrawn',
                    'refund pending',
                    'withdraw under review'
                )
              AND EXISTS (
                    SELECT 1
                    FROM student_class sc
                    WHERE sc.student_id = e.student_id
                      AND sc.school_year = i.school_year
                      {$nonEventFilter}
                )
              AND NOT EXISTS (
                    SELECT 1
                    FROM invoice_students_list isl
                    WHERE isl.invoice_id = i.id
                      AND isl.student_id = e.student_id
                )
              {$where}
            GROUP BY i.id, e.student_id, i.school_year"
        );
    }

    public function down(): void
    {
        // Backfilled invoice snapshots are intentionally retained.
    }
}