<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Database;
use RuntimeException;

class AccountReceivable extends BaseModel
{
    protected string $table = 'ventas';

    public function summary(int $companyId, array $filters = []): array
    {
        [$extraConditions, $extraBindings] = $this->buildOpenBalanceFilterClauses(
            $filters,
            'v',
            'vp_total',
            'summary_'
        );

        $conditions = array_merge([
            'v.empresa_id = :empresa_id_main',
            'v.estado <> "anulada"',
            '(v.total - COALESCE(vp_total.pagado, 0)) > 0',
        ], $extraConditions);

        $sql = 'SELECT
                    COALESCE(SUM(v.total), 0) AS total_credito,
                    COALESCE(SUM(COALESCE(vp_total.pagado, 0)), 0) AS total_abonado,
                    COALESCE(SUM(v.total - COALESCE(vp_total.pagado, 0)), 0) AS total_saldo,
                    COALESCE(SUM(CASE WHEN COALESCE(vp_total.pagado, 0) <= 0 THEN 1 ELSE 0 END), 0) AS cuentas_pendientes,
                    COALESCE(SUM(CASE WHEN COALESCE(vp_total.pagado, 0) > 0 AND COALESCE(vp_total.pagado, 0) < v.total THEN 1 ELSE 0 END), 0) AS cuentas_parciales
                FROM ventas v
                LEFT JOIN (
                    SELECT venta_id, empresa_id, SUM(monto) AS pagado
                    FROM venta_pagos
                    WHERE empresa_id = :empresa_id_sub
                    GROUP BY venta_id, empresa_id
                ) vp_total ON vp_total.venta_id = v.id AND vp_total.empresa_id = v.empresa_id
                WHERE ' . implode(' AND ', $conditions);

        $stmt = $this->db()->prepare($sql);
        $stmt->execute(array_merge([
            ':empresa_id_sub' => $companyId,
            ':empresa_id_main' => $companyId,
        ], $extraBindings));

        return $stmt->fetch() ?: [
            'total_credito' => 0,
            'total_abonado' => 0,
            'total_saldo' => 0,
            'cuentas_pendientes' => 0,
            'cuentas_parciales' => 0,
        ];
    }

    public function openSales(int $companyId, array $filters = []): array
    {
        [$extraConditions, $extraBindings] = $this->buildOpenBalanceFilterClauses(
            $filters,
            'v',
            'vp_total',
            'list_'
        );

        $conditions = array_merge([
            'v.empresa_id = :empresa_id_main',
            'v.estado <> "anulada"',
            '(v.total - COALESCE(vp_total.pagado, 0)) > 0',
        ], $extraConditions);

        $sql = 'SELECT
                    v.id,
                    v.fecha,
                    v.total,
                    v.estado,
                    c.id AS cliente_id,
                    c.nombre AS cliente,
                    c.documento AS cliente_documento,
                    u.nombre AS cajero,
                    COALESCE(vp_total.pagado, 0) AS pagado,
                    (v.total - COALESCE(vp_total.pagado, 0)) AS saldo,
                    GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) AS dias_cartera,
                    CASE
                        WHEN COALESCE(vp_total.pagado, 0) <= 0 THEN "pendiente"
                        WHEN COALESCE(vp_total.pagado, 0) < v.total THEN "parcial"
                        ELSE "pagada"
                    END AS estado_cobro,
                    CASE
                        WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) <= 30 THEN "0-30"
                        WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) <= 60 THEN "31-60"
                        WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) <= 90 THEN "61-90"
                        ELSE "91+"
                    END AS rango_antiguedad
                FROM ventas v
                LEFT JOIN clientes c ON c.id = v.cliente_id
                LEFT JOIN usuarios u ON u.id = v.usuario_id
                LEFT JOIN (
                    SELECT venta_id, empresa_id, SUM(monto) AS pagado
                    FROM venta_pagos
                    WHERE empresa_id = :empresa_id_sub
                    GROUP BY venta_id, empresa_id
                ) vp_total ON vp_total.venta_id = v.id AND vp_total.empresa_id = v.empresa_id
                WHERE ' . implode(' AND ', $conditions) . '
                ORDER BY v.id DESC';

        $stmt = $this->db()->prepare($sql);
        $stmt->execute(array_merge([
            ':empresa_id_sub' => $companyId,
            ':empresa_id_main' => $companyId,
        ], $extraBindings));

        return $stmt->fetchAll() ?: [];
    }

    public function agingSummary(int $companyId, array $filters = []): array
    {
        [$extraConditions, $extraBindings] = $this->buildOpenBalanceFilterClauses(
            $filters,
            'v',
            'vp_total',
            'aging_'
        );

        $conditions = array_merge([
            'v.empresa_id = :empresa_id_main',
            'v.estado <> "anulada"',
            '(v.total - COALESCE(vp_total.pagado, 0)) > 0',
        ], $extraConditions);

        $sql = 'SELECT
                    COALESCE(SUM(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) <= 30 THEN (v.total - COALESCE(vp_total.pagado, 0)) ELSE 0 END), 0) AS saldo_0_30,
                    COALESCE(SUM(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) BETWEEN 31 AND 60 THEN (v.total - COALESCE(vp_total.pagado, 0)) ELSE 0 END), 0) AS saldo_31_60,
                    COALESCE(SUM(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) BETWEEN 61 AND 90 THEN (v.total - COALESCE(vp_total.pagado, 0)) ELSE 0 END), 0) AS saldo_61_90,
                    COALESCE(SUM(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) >= 91 THEN (v.total - COALESCE(vp_total.pagado, 0)) ELSE 0 END), 0) AS saldo_91_mas,
                    COALESCE(COUNT(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) <= 30 THEN 1 END), 0) AS cuentas_0_30,
                    COALESCE(COUNT(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) BETWEEN 31 AND 60 THEN 1 END), 0) AS cuentas_31_60,
                    COALESCE(COUNT(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) BETWEEN 61 AND 90 THEN 1 END), 0) AS cuentas_61_90,
                    COALESCE(COUNT(CASE WHEN GREATEST(DATEDIFF(CURDATE(), DATE(v.fecha)), 0) >= 91 THEN 1 END), 0) AS cuentas_91_mas
                FROM ventas v
                LEFT JOIN (
                    SELECT venta_id, empresa_id, SUM(monto) AS pagado
                    FROM venta_pagos
                    WHERE empresa_id = :empresa_id_sub
                    GROUP BY venta_id, empresa_id
                ) vp_total ON vp_total.venta_id = v.id AND vp_total.empresa_id = v.empresa_id
                WHERE ' . implode(' AND ', $conditions);

        $stmt = $this->db()->prepare($sql);
        $stmt->execute(array_merge([
            ':empresa_id_sub' => $companyId,
            ':empresa_id_main' => $companyId,
        ], $extraBindings));

        return $stmt->fetch() ?: [
            'saldo_0_30' => 0,
            'saldo_31_60' => 0,
            'saldo_61_90' => 0,
            'saldo_91_mas' => 0,
            'cuentas_0_30' => 0,
            'cuentas_31_60' => 0,
            'cuentas_61_90' => 0,
            'cuentas_91_mas' => 0,
        ];
    }

    public function recentPayments(int $companyId, int $limit = 50): array
    {
        $limit = max(1, min(100, $limit));

        $sql = 'SELECT
                    vp.id,
                    vp.venta_id,
                    vp.monto,
                    vp.metodo_pago,
                    vp.referencia,
                    vp.created_at AS fecha_pago,
                    v.total AS venta_total,
                    c.nombre AS cliente
                FROM venta_pagos vp
                INNER JOIN ventas v ON v.id = vp.venta_id AND v.empresa_id = vp.empresa_id
                LEFT JOIN clientes c ON c.id = v.cliente_id
                WHERE vp.empresa_id = :empresa_id
                ORDER BY vp.id DESC
                LIMIT :limite';

        $stmt = $this->db()->prepare($sql);
        $stmt->bindValue(':empresa_id', $companyId, \PDO::PARAM_INT);
        $stmt->bindValue(':limite', $limit, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll() ?: [];
    }

    public function registerPayment(
        int $companyId,
        int $saleId,
        float $amount,
        string $method,
        string $reference = ''
    ): int {
        if ($saleId <= 0) {
            throw new RuntimeException('Venta invalida para registrar abono.');
        }

        if ($amount <= 0) {
            throw new RuntimeException('El monto del abono debe ser mayor que cero.');
        }

        $method = trim($method);
        if ($method === '') {
            $method = 'efectivo';
        }

        return Database::transaction(function () use ($companyId, $saleId, $amount, $method, $reference): int {
            $pdo = Database::connection();

            $saleStmt = $pdo->prepare(
                'SELECT id, total, estado
                 FROM ventas
                 WHERE id = :venta_id
                   AND empresa_id = :empresa_id
                 LIMIT 1
                 FOR UPDATE'
            );
            $saleStmt->execute([
                ':venta_id' => $saleId,
                ':empresa_id' => $companyId,
            ]);
            $sale = $saleStmt->fetch();

            if (!$sale) {
                throw new RuntimeException('La venta no existe en la empresa activa.');
            }

            if (($sale['estado'] ?? '') === 'anulada') {
                throw new RuntimeException('No se puede registrar abono en una venta anulada.');
            }

            $paidStmt = $pdo->prepare(
                'SELECT COALESCE(SUM(monto), 0)
                 FROM venta_pagos
                 WHERE venta_id = :venta_id_pago
                   AND empresa_id = :empresa_id_pago'
            );
            $paidStmt->execute([
                ':venta_id_pago' => $saleId,
                ':empresa_id_pago' => $companyId,
            ]);
            $alreadyPaid = (float) $paidStmt->fetchColumn();

            $total = (float) ($sale['total'] ?? 0);
            $balance = $total - $alreadyPaid;

            if ($balance <= 0.0001) {
                throw new RuntimeException('La venta ya esta totalmente pagada.');
            }

            if ($amount > ($balance + 0.0001)) {
                throw new RuntimeException(
                    'El abono no puede superar el saldo pendiente de ' . number_format($balance, 2, '.', '')
                );
            }

            $paymentStmt = $pdo->prepare(
                'INSERT INTO venta_pagos (
                    venta_id, empresa_id, metodo_pago, monto, referencia, created_at
                ) VALUES (
                    :venta_id, :empresa_id, :metodo_pago, :monto, :referencia, NOW()
                )'
            );
            $paymentStmt->execute([
                ':venta_id' => $saleId,
                ':empresa_id' => $companyId,
                ':metodo_pago' => $method,
                ':monto' => $amount,
                ':referencia' => trim($reference) !== '' ? trim($reference) : null,
            ]);
            $paymentId = (int) $pdo->lastInsertId();

            $newPaid = $alreadyPaid + $amount;
            $newStatus = $newPaid >= ($total - 0.0001) ? 'pagada' : 'pendiente';

            $updateStmt = $pdo->prepare(
                'UPDATE ventas
                 SET estado = :estado
                 WHERE id = :venta_id_update
                   AND empresa_id = :empresa_id_update'
            );
            $updateStmt->execute([
                ':estado' => $newStatus,
                ':venta_id_update' => $saleId,
                ':empresa_id_update' => $companyId,
            ]);

            return $paymentId;
        });
    }

    private function buildOpenBalanceFilterClauses(
        array $filters,
        string $saleAlias,
        string $paidAlias,
        string $prefix
    ): array {
        $conditions = [];
        $bindings = [];

        $customerId = (int) ($filters['cliente_id'] ?? 0);
        if ($customerId > 0) {
            $key = ':' . $prefix . 'cliente_id';
            $conditions[] = $saleAlias . '.cliente_id = ' . $key;
            $bindings[$key] = $customerId;
        }

        $dateFrom = trim((string) ($filters['fecha_desde'] ?? ''));
        if ($dateFrom !== '') {
            $key = ':' . $prefix . 'fecha_desde';
            $conditions[] = 'DATE(' . $saleAlias . '.fecha) >= ' . $key;
            $bindings[$key] = $dateFrom;
        }

        $dateTo = trim((string) ($filters['fecha_hasta'] ?? ''));
        if ($dateTo !== '') {
            $key = ':' . $prefix . 'fecha_hasta';
            $conditions[] = 'DATE(' . $saleAlias . '.fecha) <= ' . $key;
            $bindings[$key] = $dateTo;
        }

        $status = strtolower(trim((string) ($filters['estado'] ?? 'todos')));
        if ($status === 'pendiente') {
            $conditions[] = 'COALESCE(' . $paidAlias . '.pagado, 0) <= 0';
        } elseif ($status === 'parcial') {
            $conditions[] = 'COALESCE(' . $paidAlias . '.pagado, 0) > 0';
            $conditions[] = 'COALESCE(' . $paidAlias . '.pagado, 0) < ' . $saleAlias . '.total';
        }

        return [$conditions, $bindings];
    }
}
