<?php

declare(strict_types=1);

namespace App\Repositories;

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

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

    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,
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) 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,
                    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(: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);
    }

    /**
     * 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);
    }
}
