<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use App\Core\SchemaRuntime;
use App\Shared\Parties\PartyLedgerSupport;
use App\Shared\Schema\PartySchemaSupport;
use PDO;

final class PartyStatementService
{
    public function __construct(private PDO $pdo)
    {
    }

    public function ensureSchema(): void
    {
        SchemaRuntime::ensureMany($this->pdo, [
            [
                'key' => 'parties_schema',
                'callback' => function (): void { PartySchemaSupport::ensurePartiesSchema($this->pdo); },
                'migration' => 'sql/migrate_v1_18_parties.sql',
            ],
            [
                'key' => 'party_ledger_schema',
                'callback' => function (): void { PartySchemaSupport::ensurePartyLedgerSchema($this->pdo); },
                'migration' => 'sql/migrate_v1_20_party_ledger.sql',
            ],
            [
                'key' => 'parties_migration_bridge',
                'callback' => function (): void { PartySchemaSupport::ensurePartiesMigrateFromCustomersSuppliers($this->pdo); },
                'migration' => 'sql/migrate_v1_18_parties.sql',
            ],
        ]);
    }

    public function loadStatement(string $role, int $partyId, string $from, string $to, array $filters = []): array
    {
        $role = $this->normalizeRole($role);
        $party = null;
        $rows = [];
        $opening = 0.0;
        $closing = null;
        $totals = [
            'debit' => 0.0,
            'credit' => 0.0,
            'closing' => 0.0,
            'rows_count' => 0,
        ];

        if ($from === '') {
            $from = date('Y-m-01');
        }
        if ($to === '') {
            $to = date('Y-m-d');
        }

        if ($partyId > 0) {
            $party = $this->findParty($partyId, $role);
            if ($party) {
                $fromDate = date('Y-m-d', strtotime($from));
                $toDate = date('Y-m-d', strtotime($to));
                $opening = (float) ($party['opening_balance'] ?? 0.0) + PartyLedgerSupport::balance($this->pdo, $partyId, $fromDate);
                $rows = $this->fetchRows($partyId, $fromDate, $toDate, $filters);
                $closing = $opening;
                foreach ($rows as $row) {
                    $debit = (float) ($row['debit'] ?? 0.0);
                    $credit = (float) ($row['credit'] ?? 0.0);
                    $totals['debit'] += $debit;
                    $totals['credit'] += $credit;
                    $closing += $debit - $credit;
                }
                $totals['closing'] = $closing;
                $totals['rows_count'] = count($rows);
                $from = $fromDate;
                $to = $toDate;
            }
        }

        return [
            'role' => $role,
            'partyId' => $partyId,
            'party' => $party,
            'from' => $from,
            'to' => $to,
            'opening' => $opening,
            'closing' => $closing,
            'totals' => $totals,
            'rows' => $rows,
            'filters' => $filters,
        ];
    }

    public function printPayload(string $role, int $partyId, string $from, string $to, array $filters = []): array
    {
        $data = $this->loadStatement($role, $partyId, $from, $to, $filters);
        if (($data['party'] ?? null) === null) {
            throw new \RuntimeException('Not found');
        }

        $title = $data['role'] === 'supplier' ? 'كشف حساب المورد' : 'كشف حساب العميل';
        $party = is_array($data['party']) ? $data['party'] : [];
        $periodLabel = trim(($data['from'] ?? '') . ' → ' . ($data['to'] ?? ''));

        return $data + [
            'title' => $title,
            'meta' => [
                'date' => date('Y-m-d'),
                'title_en' => $data['role'] === 'supplier' ? 'Supplier Statement' : 'Customer Statement',
                'no' => (string)($party['code'] ?? ''),
                'period' => $periodLabel,
            ],
            'company' => company_profile($this->pdo),
            'footer' => [
                'note' => 'كشف الحساب المعروض يعكس الرصيد والحركات ضمن الفترة المحددة فقط.',
            ],
        ];
    }

    private function normalizeRole(string $role): string
    {
        return strtolower(trim($role)) === 'supplier' ? 'supplier' : 'customer';
    }

    private function findParty(int $partyId, string $role): ?array
    {
        $whereRole = $role === 'supplier' ? 'is_supplier=1' : 'is_customer=1';
        $st = $this->pdo->prepare("SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code, opening_balance FROM parties WHERE id=? AND {$whereRole} LIMIT 1");
        $st->execute([$partyId]);
        $party = $st->fetch();
        if (is_array($party)) {
            return $party;
        }

        $fallback = $this->pdo->prepare("SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code, opening_balance FROM parties WHERE id=? LIMIT 1");
        $fallback->execute([$partyId]);
        $party = $fallback->fetch();
        return is_array($party) ? $party : null;
    }

    private function fetchRows(int $partyId, string $fromDate, string $toDate, array $filters = []): array
    {
        $hasEventAt = column_exists($this->pdo, 'party_ledger', 'event_at');
        $where = ["pl.party_id=?", "pl.doc_date BETWEEN ? AND ?"];
        $params = [$partyId, $fromDate, $toDate];

        $docType = trim((string)($filters['doc_type'] ?? ''));
        if ($docType !== '') {
            $where[] = 'pl.doc_type = ?';
            $params[] = $docType;
        }

        $docNo = trim((string)($filters['doc_no'] ?? ''));
        if ($docNo !== '') {
            $where[] = 'pl.doc_no LIKE ?';
            $params[] = '%' . $docNo . '%';
        }

        $direction = trim((string)($filters['direction'] ?? ''));
        if ($direction === 'debit') {
            $where[] = 'pl.debit > 0';
        } elseif ($direction === 'credit') {
            $where[] = 'pl.credit > 0';
        }

        if (($filters['amount_min'] ?? '') !== '' && is_numeric($filters['amount_min'])) {
            $where[] = '(pl.debit >= ? OR pl.credit >= ?)';
            $params[] = (float)$filters['amount_min'];
            $params[] = (float)$filters['amount_min'];
        }
        if (($filters['amount_max'] ?? '') !== '' && is_numeric($filters['amount_max'])) {
            $where[] = '(pl.debit <= ? OR pl.credit <= ?)';
            $params[] = (float)$filters['amount_max'];
            $params[] = (float)$filters['amount_max'];
        }

        $q = trim((string)($filters['q'] ?? ''));
        if ($q !== '') {
            $where[] = "(pl.doc_no LIKE ? OR pl.doc_type LIKE ? OR pl.notes LIKE ? OR si_inv.notes LIKE ? OR si_rcpt.collection_note LIKE ? OR cr.notes LIKE ? OR cp.notes LIKE ? OR sp.notes LIKE ? OR pi_inv.notes LIKE ? OR pi_pay.payment_note LIKE ? OR pr.notes LIKE ? OR sr.notes LIKE ?)";
            for ($i = 0; $i < 12; $i++) {
                $params[] = '%' . $q . '%';
            }
        }

        $sql = "SELECT pl.doc_date, " . ($hasEventAt ? "pl.event_at, " : "NULL AS event_at, ") . "pl.doc_type, pl.doc_no, pl.doc_id, pl.debit, pl.credit,
                       COALESCE(
                         NULLIF(pl.notes,''),
                         NULLIF(si_inv.notes,''),
                         NULLIF(si_rcpt.collection_note,''),
                         NULLIF(cr.notes,''),
                         NULLIF(cp.notes,''),
                         NULLIF(sp.notes,''),
                         NULLIF(pi_inv.notes,''),
                         NULLIF(pi_pay.payment_note,''),
                         NULLIF(pr.notes,''),
                         NULLIF(sr.notes,'')
                       ) AS notes
                FROM party_ledger pl
                LEFT JOIN sales_invoices si_inv ON si_inv.id = pl.doc_id AND pl.doc_type='SALES_INV'
                LEFT JOIN sales_invoices si_rcpt ON si_rcpt.id = pl.doc_id AND pl.doc_type IN ('SALES_RCPT','SALES_RECEIPT')
                LEFT JOIN customer_receipts cr ON cr.id = pl.doc_id AND pl.doc_type='RCPT'
                LEFT JOIN cash_payments cp ON cp.id = pl.doc_id AND pl.doc_type='PAY'
                LEFT JOIN supplier_payments sp ON sp.id = pl.doc_id AND pl.doc_type IN ('PAYMENT','SUPPLIER_PAYMENT')
                LEFT JOIN purchase_invoices pi_inv ON pi_inv.id = pl.doc_id AND pl.doc_type='PURCHASE_INV'
                LEFT JOIN purchase_invoices pi_pay ON pi_pay.id = pl.doc_id AND pl.doc_type='PURCHASE_PAY'
                LEFT JOIN purchase_returns pr ON pr.id = pl.doc_id AND pl.doc_type='PURCHASE_RET'
                LEFT JOIN sales_returns sr ON sr.id = pl.doc_id AND pl.doc_type='SALES_RET'
                WHERE " . implode(' AND ', $where) . "
                ORDER BY pl.doc_date, " . ($hasEventAt ? "pl.event_at, " : "pl.created_at, ") . "pl.id";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }
}
