<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use InvalidArgumentException;

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

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

    /**
     * @return array<int, array<string, mixed>>
     */
    public function monthlyRevenue(int $year, int $compareYear, string $type = 'total', ?string $customerCode = null): array
    {
        switch ($type) {
            case 'cash':
                $amountExpression = 'NVL(CASH_VALUE, 0)';
                break;
            case 'insurance':
                $amountExpression = 'NVL(CREDIT_VALUE, 0)';
                break;
            case 'total':
            case 'moh':
                $amountExpression = 'NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)';
                break;
            default:
                throw new InvalidArgumentException('Invalid revenue type: ' . $type);
        }

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

    /**
     * @return array<int, array<string, mixed>>
     */
    public function monthlyProfitLoss(int $year, int $compareYear, int $companyId): array
    {
        $sql = "
            WITH pl AS (
                SELECT YEARID, MONTHID, CURR AS AMOUNT
                FROM income_statments
                WHERE COMP_ID = :company_id
                  AND YEARID IN (:year, :compare_year)
            )
            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, [
            'company_id' => $companyId,
            'year' => $year,
            'compare_year' => $compareYear,
        ]);
    }
}
