<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use DateTimeImmutable;
use InvalidArgumentException;

final class RevenueRepository
{
    /** @var string */
    private $hospitalCode;

    /** @var string */
    private $lastRevenueSource = 'batch';

    /** @var array<string,mixed> */
    private $lastSummaryStatus = [];

    public function __construct(string $hospitalCode)
    {
        $this->hospitalCode = $hospitalCode;
    }

    /**
     * Current revenue implementation used by the executive Revenue page.
     *
     * When $fromDate and $toDate are provided, the current period is filtered
     * by VISIT_END_DATE and compared with the same month/day window in
     * $compareYear. The UI requires both dates to be within one year, matching
     * the legacy Revenue page contract.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyRevenue(
        int $year,
        int $compareYear,
        string $type = 'total',
        ?string $customerCode = null,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $amountExpression = $this->amountExpression($type);

        if ($fromDate !== null && $toDate !== null) {
            return $this->monthlyRevenueRange(
                $year,
                $compareYear,
                $amountExpression,
                $customerCode,
                $fromDate,
                $toDate
            );
        }

        if ((bool) \config('app.use_revenue_mv', false)) {
            return $this->monthlyRevenueFromMv($year, $compareYear, $type, $customerCode);
        }

        $whereCustomer = '';
        $params = [
            'year' => $year,
            'compare_year' => $compareYear,
        ];

        if ($customerCode !== null && $customerCode !== '') {
            $whereCustomer = ' AND CUSTOMER_CODE = :customer_code ';
            $params['customer_code'] = $customerCode;
        }

        $sql = "
            WITH rev AS (
                SELECT
                    YEARID,
                    VISIT_MONTH AS MONTHID,
                    SUM($amountExpression) AS AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE YEARID IN (:year, :compare_year)
                $whereCustomer
                GROUP BY
                    YEARID,
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.amount END), 0) AS current_value,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.amount END), 0) AS compare_value
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        return Database::fetchAll($this->hospitalCode, $sql, $params);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function monthlyRevenueRange(
        int $year,
        int $compareYear,
        string $amountExpression,
        ?string $customerCode,
        string $fromDate,
        string $toDate
    ): array {
        $compareFrom = self::shiftDateToYear($fromDate, $compareYear);
        $compareTo = self::shiftDateToYear($toDate, $compareYear);

        $whereCustomer = '';
        $params = [
            'current_from' => $fromDate,
            'current_to' => $toDate,
            'compare_from' => $compareFrom,
            'compare_to' => $compareTo,
        ];

        if ($customerCode !== null && $customerCode !== '') {
            $whereCustomer = ' AND CUSTOMER_CODE = :customer_code ';
            $params['customer_code'] = $customerCode;
        }

        $sql = "
            WITH rev AS (
                SELECT
                    'CURRENT' AS PERIOD_KEY,
                    VISIT_MONTH AS MONTHID,
                    SUM($amountExpression) AS AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE VISIT_END_DATE >= TO_DATE(:current_from, 'YYYY-MM-DD')
                  AND VISIT_END_DATE < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                  $whereCustomer
                GROUP BY TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))

                UNION ALL

                SELECT
                    'COMPARE' AS PERIOD_KEY,
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) AS MONTHID,
                    SUM($amountExpression) AS AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE VISIT_END_DATE >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                  AND VISIT_END_DATE < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                  $whereCustomer
                GROUP BY TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.period_key = 'CURRENT' THEN r.amount END), 0) AS current_value,
                NVL(MAX(CASE WHEN r.period_key = 'COMPARE' THEN r.amount END), 0) AS compare_value
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        return Database::fetchAll($this->hospitalCode, $sql, $params);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function monthlyRevenueFromMv(int $year, int $compareYear, string $type, ?string $customerCode): array
    {
        switch ($type) {
            case 'cash':
                $amountColumn = 'CASH_AMOUNT';
                break;
            case 'insurance':
                $amountColumn = 'CREDIT_AMOUNT';
                break;
            case 'total':
            case 'moh':
            default:
                $amountColumn = 'TOTAL_AMOUNT';
                break;
        }

        $whereCustomer = '';
        $params = [
            'year' => $year,
            'compare_year' => $compareYear,
        ];

        if ($customerCode !== null && $customerCode !== '') {
            $whereCustomer = ' AND CUSTOMER_CODE = :customer_code ';
            $params['customer_code'] = $customerCode;
        }

        $sql = "
            WITH rev AS (
                SELECT YEARID, MONTHID, SUM($amountColumn) AS AMOUNT
                FROM MV_REVENUE_MONTHLY
                WHERE YEARID IN (:year, :compare_year)
                $whereCustomer
                GROUP BY YEARID, MONTHID
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.amount END), 0) AS current_value,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.amount END), 0) AS compare_value
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        return Database::fetchAll($this->hospitalCode, $sql, $params);
    }



    /**
     * R45-P2 consolidated Revenue contract.
     *
     * Returns the four Revenue series required by the executive page from one
     * Oracle aggregation instead of four independent scans of
     * T_INP_OUTP_REVENUE_BATCH. The legacy monthlyRevenue() method remains
     * available for parity verification and backwards compatibility.
     *
     * @return array<string, array<int, array<string, mixed>>>
     */
    public function monthlyRevenueBundle(
        int $year,
        int $compareYear,
        string $mohCustomerCode,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        if ((bool) \config('app.use_revenue_summary', false)) {
            try {
                $status = $this->revenueSummaryStatus();
                $this->lastSummaryStatus = $status;
                if (!empty($status['usable'])) {
                    $this->lastRevenueSource = 'summary';
                    return $this->monthlyRevenueBundleFromSummary(
                        $year,
                        $compareYear,
                        $fromDate,
                        $toDate
                    );
                }
                $this->lastRevenueSource = 'batch_fallback';
            } catch (\Throwable $summaryError) {
                $this->lastRevenueSource = 'batch_fallback';
                $this->lastSummaryStatus = [
                    'usable' => false,
                    'status' => 'UNAVAILABLE',
                    'reason' => $summaryError->getMessage(),
                ];
            }
        }

        if ($fromDate !== null && $toDate !== null) {
            return $this->monthlyRevenueBundleYearMonthRange(
                $year,
                $compareYear,
                $mohCustomerCode,
                $fromDate,
                $toDate
            );
        }

        $sql = "
            WITH rev AS (
                SELECT
                    YEARID,
                    VISIT_MONTH AS MONTHID,
                    SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)) AS TOTAL_AMOUNT,
                    SUM(NVL(CASH_VALUE, 0)) AS CASH_AMOUNT,
                    SUM(NVL(CREDIT_VALUE, 0)) AS INSURANCE_AMOUNT,
                    SUM(
                        CASE
                            WHEN CUSTOMER_CODE = :moh_customer_code
                            THEN NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)
                            ELSE 0
                        END
                    ) AS MOH_AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE YEARID IN (:year, :compare_year)
                GROUP BY YEARID, VISIT_MONTH
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.total_amount END), 0) AS current_total,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.total_amount END), 0) AS compare_total,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.cash_amount END), 0) AS current_cash,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.cash_amount END), 0) AS compare_cash,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.insurance_amount END), 0) AS current_insurance,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.insurance_amount END), 0) AS compare_insurance,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.moh_amount END), 0) AS current_moh,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.moh_amount END), 0) AS compare_moh
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'year' => $year,
            'compare_year' => $compareYear,
            'moh_customer_code' => $mohCustomerCode,
        ]);

        return $this->splitRevenueBundleRows($rows);
    }


    /**
     * R45-P3 published monthly Summary Layer reader.
     *
     * @return array<string, array<int, array<string, mixed>>>
     */
    private function monthlyRevenueBundleFromSummary(
        int $year,
        int $compareYear,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $startMonth = 1;
        $endMonth = 12;
        if ($fromDate !== null && $toDate !== null) {
            $startMonth = (int)(new DateTimeImmutable($fromDate))->format('n');
            $endMonth = (int)(new DateTimeImmutable($toDate))->format('n');
        }

        $sql = "
            WITH rev AS (
                SELECT
                    YEAR_ID AS YEARID,
                    MONTH_ID AS MONTHID,
                    TOTAL_REVENUE AS TOTAL_AMOUNT,
                    CASH_REVENUE AS CASH_AMOUNT,
                    INSURANCE_REVENUE AS INSURANCE_AMOUNT,
                    MOH_REVENUE AS MOH_AMOUNT
                FROM DASH_REVENUE_MONTHLY_SUMMARY
                WHERE BRANCH_ID = :branch_id
                  AND YEAR_ID IN (:year, :compare_year)
                  AND MONTH_ID BETWEEN :start_month AND :end_month
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.total_amount END), 0) AS current_total,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.total_amount END), 0) AS compare_total,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.cash_amount END), 0) AS current_cash,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.cash_amount END), 0) AS compare_cash,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.insurance_amount END), 0) AS current_insurance,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.insurance_amount END), 0) AS compare_insurance,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.moh_amount END), 0) AS current_moh,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.moh_amount END), 0) AS compare_moh
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'branch_id' => 1,
            'year' => $year,
            'compare_year' => $compareYear,
            'start_month' => $startMonth,
            'end_month' => $endMonth,
        ]);

        return $this->splitRevenueBundleRows($rows);
    }

    /** @return array<string,mixed> */
    public function revenueSummaryStatus(): array
    {
        $maxAgeHours = (int) \config('app.revenue_summary_max_age_hours', 30);
        $row = Database::fetchOne($this->hospitalCode, "
            SELECT *
            FROM (
                SELECT
                    RUN_ID,
                    BRANCH_ID,
                    STATUS,
                    FROM_DATE,
                    TO_DATE,
                    SOURCE_ROW_COUNT,
                    SUMMARY_ROW_COUNT,
                    STARTED_AT,
                    FINISHED_AT,
                    ROUND((SYSDATE - CAST(FINISHED_AT AS DATE)) * 24, 3) AS AGE_HOURS
                FROM DASH_REVENUE_SUMMARY_RUN
                WHERE BRANCH_ID = :branch_id
                  AND STATUS = 'READY'
                ORDER BY RUN_ID DESC
            )
            WHERE ROWNUM = 1
        ", ['branch_id' => 1]);

        if (!$row) {
            return [
                'usable' => false,
                'status' => 'MISSING_READY_RUN',
                'max_age_hours' => $maxAgeHours,
            ];
        }

        $ageHours = (float)($row['age_hours'] ?? 999999);
        $row['usable'] = $ageHours <= $maxAgeHours;
        $row['max_age_hours'] = $maxAgeHours;
        $row['reason'] = $row['usable'] ? 'READY_AND_FRESH' : 'STALE';
        return $row;
    }

    public function lastRevenueSource(): string
    {
        return $this->lastRevenueSource;
    }

    /** @return array<string,mixed> */
    public function lastSummaryStatus(): array
    {
        return $this->lastSummaryStatus;
    }

    /**
     * R45-P2.2 production path for fair/current-year and manual ranges.
     *
     * The executive comparison contract is month-granular. Using YEARID and
     * VISIT_MONTH directly avoids functions on VISIT_END_DATE and allows the
     * existing YEARID/VISIT_MONTH index to participate in the plan.
     *
     * @return array<string, array<int, array<string, mixed>>>
     */
    private function monthlyRevenueBundleYearMonthRange(
        int $year,
        int $compareYear,
        string $mohCustomerCode,
        string $fromDate,
        string $toDate
    ): array {
        $startMonth = (int)(new DateTimeImmutable($fromDate))->format('n');
        $endMonth = (int)(new DateTimeImmutable($toDate))->format('n');

        $sql = "
            WITH rev AS (
                SELECT
                    YEARID,
                    VISIT_MONTH AS MONTHID,
                    SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)) AS TOTAL_AMOUNT,
                    SUM(NVL(CASH_VALUE, 0)) AS CASH_AMOUNT,
                    SUM(NVL(CREDIT_VALUE, 0)) AS INSURANCE_AMOUNT,
                    SUM(
                        CASE
                            WHEN CUSTOMER_CODE = :moh_customer_code
                            THEN NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)
                            ELSE 0
                        END
                    ) AS MOH_AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE YEARID IN (:year, :compare_year)
                  AND VISIT_MONTH BETWEEN :start_month AND :end_month
                GROUP BY YEARID, VISIT_MONTH
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.total_amount END), 0) AS current_total,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.total_amount END), 0) AS compare_total,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.cash_amount END), 0) AS current_cash,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.cash_amount END), 0) AS compare_cash,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.insurance_amount END), 0) AS current_insurance,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.insurance_amount END), 0) AS compare_insurance,
                NVL(MAX(CASE WHEN r.yearid = :year THEN r.moh_amount END), 0) AS current_moh,
                NVL(MAX(CASE WHEN r.yearid = :compare_year THEN r.moh_amount END), 0) AS compare_moh
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'year' => $year,
            'compare_year' => $compareYear,
            'start_month' => $startMonth,
            'end_month' => $endMonth,
            'moh_customer_code' => $mohCustomerCode,
        ]);

        return $this->splitRevenueBundleRows($rows);
    }

    /** @return array<string, array<int, array<string, mixed>>> */
    private function monthlyRevenueBundleRange(
        int $year,
        int $compareYear,
        string $mohCustomerCode,
        string $fromDate,
        string $toDate
    ): array {
        $compareFrom = self::shiftDateToYear($fromDate, $compareYear);
        $compareTo = self::shiftDateToYear($toDate, $compareYear);

        $sql = "
            WITH rev AS (
                SELECT
                    'CURRENT' AS PERIOD_KEY,
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) AS MONTHID,
                    SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)) AS TOTAL_AMOUNT,
                    SUM(NVL(CASH_VALUE, 0)) AS CASH_AMOUNT,
                    SUM(NVL(CREDIT_VALUE, 0)) AS INSURANCE_AMOUNT,
                    SUM(
                        CASE
                            WHEN CUSTOMER_CODE = :moh_customer_code
                            THEN NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)
                            ELSE 0
                        END
                    ) AS MOH_AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE VISIT_END_DATE >= TO_DATE(:current_from, 'YYYY-MM-DD')
                  AND VISIT_END_DATE < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))

                UNION ALL

                SELECT
                    'COMPARE' AS PERIOD_KEY,
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) AS MONTHID,
                    SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)) AS TOTAL_AMOUNT,
                    SUM(NVL(CASH_VALUE, 0)) AS CASH_AMOUNT,
                    SUM(NVL(CREDIT_VALUE, 0)) AS INSURANCE_AMOUNT,
                    SUM(
                        CASE
                            WHEN CUSTOMER_CODE = :moh_customer_code
                            THEN NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)
                            ELSE 0
                        END
                    ) AS MOH_AMOUNT
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE VISIT_END_DATE >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                  AND VISIT_END_DATE < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN r.period_key = 'CURRENT' THEN r.total_amount END), 0) AS current_total,
                NVL(MAX(CASE WHEN r.period_key = 'COMPARE' THEN r.total_amount END), 0) AS compare_total,
                NVL(MAX(CASE WHEN r.period_key = 'CURRENT' THEN r.cash_amount END), 0) AS current_cash,
                NVL(MAX(CASE WHEN r.period_key = 'COMPARE' THEN r.cash_amount END), 0) AS compare_cash,
                NVL(MAX(CASE WHEN r.period_key = 'CURRENT' THEN r.insurance_amount END), 0) AS current_insurance,
                NVL(MAX(CASE WHEN r.period_key = 'COMPARE' THEN r.insurance_amount END), 0) AS compare_insurance,
                NVL(MAX(CASE WHEN r.period_key = 'CURRENT' THEN r.moh_amount END), 0) AS current_moh,
                NVL(MAX(CASE WHEN r.period_key = 'COMPARE' THEN r.moh_amount END), 0) AS compare_moh
            FROM Sec_Lookup_Detail m
            LEFT JOIN rev r ON r.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'current_from' => $fromDate,
            'current_to' => $toDate,
            'compare_from' => $compareFrom,
            'compare_to' => $compareTo,
            'moh_customer_code' => $mohCustomerCode,
        ]);

        return $this->splitRevenueBundleRows($rows);
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<string, array<int, array<string, mixed>>>
     */
    private function splitRevenueBundleRows(array $rows): array
    {
        $result = [
            'total' => [],
            'cash' => [],
            'insurance' => [],
            'moh' => [],
        ];

        foreach ($rows as $row) {
            $base = [
                'code' => (int)($row['code'] ?? 0),
                'month_name' => (string)($row['month_name'] ?? ''),
            ];
            foreach (array_keys($result) as $type) {
                $result[$type][] = $base + [
                    'current_value' => (float)($row['current_' . $type] ?? 0),
                    'compare_value' => (float)($row['compare_' . $type] ?? 0),
                ];
            }
        }

        return $result;
    }

    /**
     * Profit/Loss is stored as a monthly statement. In date-range mode the
     * selected window is therefore normalized to the inclusive month range.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyProfitLoss(
        int $year,
        int $compareYear,
        int $companyId,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $monthFilter = '';
        $params = [
            'company_id' => $companyId,
            'year' => $year,
            'compare_year' => $compareYear,
        ];

        if ($fromDate !== null && $toDate !== null) {
            $startMonth = (int)(new DateTimeImmutable($fromDate))->format('n');
            $endMonth = (int)(new DateTimeImmutable($toDate))->format('n');
            $monthFilter = ' AND MONTHID BETWEEN :start_month AND :end_month ';
            $params['start_month'] = $startMonth;
            $params['end_month'] = $endMonth;
        }

        $sql = "
            WITH pl AS (
                SELECT YEARID, MONTHID, SUM(NVL(CURR, 0)) AS AMOUNT
                FROM income_statments
                WHERE COMP_ID = :company_id
                  AND YEARID IN (:year, :compare_year)
                  $monthFilter
                GROUP BY YEARID, MONTHID
            )
            SELECT
                m.code,
                m.name_en AS month_name,
                NVL(MAX(CASE WHEN pl.yearid = :year THEN pl.amount END), 0) AS current_value,
                NVL(MAX(CASE WHEN pl.yearid = :compare_year THEN pl.amount END), 0) AS compare_value
            FROM Sec_Lookup_Detail m
            LEFT JOIN pl ON pl.monthid = m.code
            WHERE m.parent_id = 1
            GROUP BY m.code, m.name_en
            ORDER BY m.code
        ";

        return Database::fetchAll($this->hospitalCode, $sql, $params);
    }

    private function amountExpression(string $type): string
    {
        switch ($type) {
            case 'cash':
                return 'NVL(CASH_VALUE, 0)';
            case 'insurance':
                return 'NVL(CREDIT_VALUE, 0)';
            case 'total':
            case 'moh':
                return 'NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)';
            default:
                throw new InvalidArgumentException('Invalid revenue type: ' . $type);
        }
    }

    public static function shiftDateToYear(string $date, int $targetYear): string
    {
        $value = new DateTimeImmutable($date);
        $month = (int)$value->format('n');
        $day = (int)$value->format('j');

        while ($day > 28 && !checkdate($month, $day, $targetYear)) {
            $day--;
        }

        return sprintf('%04d-%02d-%02d', $targetYear, $month, $day);
    }
}
