<?php

declare(strict_types=1);

namespace App\Repositories;

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

/**
 * Independent implementation of the confirmed legacy Revenue contract.
 *
 * This repository intentionally does not call RevenueRepository. It reproduces
 * the confirmed old function families independently so Phase 10.9 can compare
 * two execution paths against the same Oracle source.
 */
final class LegacyRevenueRepository
{
    /** @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,
        ?string $customerCode = null,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $expression = $this->amountExpression($type);

        if ($fromDate !== null && $toDate !== null) {
            $current = $this->revenuePeriodRows($expression, $customerCode, $fromDate, $toDate);
            $compare = $this->revenuePeriodRows(
                $expression,
                $customerCode,
                RevenueRepository::shiftDateToYear($fromDate, $compareYear),
                RevenueRepository::shiftDateToYear($toDate, $compareYear)
            );
        } else {
            $current = $this->revenueYearRows($expression, $customerCode, $year);
            $compare = $this->revenueYearRows($expression, $customerCode, $compareYear);
        }

        return $this->mergeMonthRows($current, $compare);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function monthlyProfitLoss(
        int $year,
        int $compareYear,
        int $companyId,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $startMonth = 1;
        $endMonth = 12;
        if ($fromDate !== null && $toDate !== null) {
            $startMonth = (int)(new DateTimeImmutable($fromDate))->format('n');
            $endMonth = (int)(new DateTimeImmutable($toDate))->format('n');
        }

        $current = $this->profitLossYearRows($year, $companyId, $startMonth, $endMonth);
        $compare = $this->profitLossYearRows($compareYear, $companyId, $startMonth, $endMonth);
        return $this->mergeMonthRows($current, $compare);
    }

    /** @return array<int, array<string, mixed>> */
    private function revenueYearRows(string $expression, ?string $customerCode, int $year): array
    {
        $whereCustomer = '';
        $params = ['legacy_year' => $year];
        if ($customerCode !== null && $customerCode !== '') {
            $whereCustomer = ' AND CUSTOMER_CODE = :legacy_customer_code ';
            $params['legacy_customer_code'] = $customerCode;
        }

        $sql = "
            SELECT
                months.code,
                months.name_en AS month_name,
                NVL(rev.amount, 0) AS period_value
            FROM (SELECT code, name_en FROM Sec_Lookup_Detail WHERE Parent_Id = 1) months
            LEFT JOIN (
                SELECT
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) AS monthid,
                    SUM($expression) AS amount
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE YEARID = :legacy_year
                  $whereCustomer
                GROUP BY TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))
            ) rev ON rev.monthid = months.code
            ORDER BY months.code
        ";
        return Database::fetchAll($this->hospitalCode, $sql, $params);
    }

    /** @return array<int, array<string, mixed>> */
    private function revenuePeriodRows(string $expression, ?string $customerCode, string $fromDate, string $toDate): array
    {
        $whereCustomer = '';
        $params = [
            'legacy_from_date' => $fromDate,
            'legacy_to_date' => $toDate,
        ];
        if ($customerCode !== null && $customerCode !== '') {
            $whereCustomer = ' AND CUSTOMER_CODE = :legacy_customer_code ';
            $params['legacy_customer_code'] = $customerCode;
        }

        $sql = "
            SELECT
                months.code,
                months.name_en AS month_name,
                NVL(rev.amount, 0) AS period_value
            FROM (SELECT code, name_en FROM Sec_Lookup_Detail WHERE Parent_Id = 1) months
            LEFT JOIN (
                SELECT
                    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) AS monthid,
                    SUM($expression) AS amount
                FROM T_INP_OUTP_REVENUE_BATCH
                WHERE VISIT_END_DATE >= TO_DATE(:legacy_from_date, 'YYYY-MM-DD')
                  AND VISIT_END_DATE < TO_DATE(:legacy_to_date, 'YYYY-MM-DD') + 1
                  $whereCustomer
                GROUP BY TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM'))
            ) rev ON rev.monthid = months.code
            ORDER BY months.code
        ";
        return Database::fetchAll($this->hospitalCode, $sql, $params);
    }

    /** @return array<int, array<string, mixed>> */
    private function profitLossYearRows(int $year, int $companyId, int $startMonth, int $endMonth): array
    {
        $sql = "
            SELECT
                months.code,
                months.name_en AS month_name,
                NVL(pl.amount, 0) AS period_value
            FROM (SELECT code, name_en FROM Sec_Lookup_Detail WHERE Parent_Id = 1) months
            LEFT JOIN (
                SELECT MONTHID, SUM(NVL(CURR, 0)) AS amount
                FROM income_statments
                WHERE COMP_ID = :legacy_company_id
                  AND YEARID = :legacy_year
                  AND MONTHID BETWEEN :legacy_start_month AND :legacy_end_month
                GROUP BY MONTHID
            ) pl ON pl.monthid = months.code
            ORDER BY months.code
        ";
        return Database::fetchAll($this->hospitalCode, $sql, [
            'legacy_company_id' => $companyId,
            'legacy_year' => $year,
            'legacy_start_month' => $startMonth,
            'legacy_end_month' => $endMonth,
        ]);
    }

    /**
     * @param array<int, array<string, mixed>> $current
     * @param array<int, array<string, mixed>> $compare
     * @return array<int, array<string, mixed>>
     */
    private function mergeMonthRows(array $current, array $compare): array
    {
        $compareByCode = [];
        foreach ($compare as $row) {
            $compareByCode[(int)($row['code'] ?? 0)] = $row;
        }

        $rows = [];
        foreach ($current as $row) {
            $code = (int)($row['code'] ?? 0);
            $other = $compareByCode[$code] ?? [];
            $rows[] = [
                'code' => $code,
                'month_name' => (string)($row['month_name'] ?? $other['month_name'] ?? ''),
                'current_value' => (float)($row['period_value'] ?? 0),
                'compare_value' => (float)($other['period_value'] ?? 0),
            ];
        }
        return $rows;
    }

    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 legacy revenue type: ' . $type);
        }
    }
}
