<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

use ApiModel\Domain\Repository\BudgetRepositoryInterface;
use ApiModel\Infrastructure\Persistence\Database;
use PDO;

final class PdoBudgetRepository implements BudgetRepositoryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    // Años Fiscales
    public function findFiscalYearById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.fiscal_years WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function findFiscalYearByYear(int $year): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.fiscal_years WHERE year = ?');
        $st->execute([$year]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listFiscalYears(): array
    {
        $st = $this->pdo->query('SELECT * FROM budget.fiscal_years ORDER BY year DESC');
        return $st->fetchAll();
    }

    public function insertFiscalYear(string $id, int $year, string $status): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.fiscal_years (id, year, status) VALUES (?, ?, ?)'
        );
        $st->execute([$id, $year, $status]);
    }

    public function updateFiscalYearStatus(string $id, string $status): void
    {
        $st = $this->pdo->prepare(
            'UPDATE budget.fiscal_years SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
        );
        $st->execute([$status, $id]);
    }

    // Clasificadores Presupuestarios
    public function findClassifierById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.classifiers WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function findClassifierByCode(string $code): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.classifiers WHERE code = ?');
        $st->execute([$code]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listClassifiers(): array
    {
        $st = $this->pdo->query('SELECT * FROM budget.classifiers ORDER BY code ASC');
        return $st->fetchAll();
    }

    public function insertClassifier(string $id, string $code, string $name, string $type, ?string $parentId, ?string $accountId): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.classifiers (id, code, name, type, parent_id, account_id, is_active)
             VALUES (?, ?, ?, ?, ?, ?, TRUE)'
        );
        $st->execute([$id, $code, $name, $type, $parentId, $accountId]);
    }

    public function updateClassifier(string $id, string $name, bool $isActive, ?string $accountId): void
    {
        $st = $this->pdo->prepare(
            'UPDATE budget.classifiers 
             SET name = ?, is_active = ?, account_id = ?, updated_at = CURRENT_TIMESTAMP 
             WHERE id = ?'
        );
        $st->execute([$name, Database::bool($isActive), $accountId, $id]);
    }

    // Unidades Organizacionales
    public function findOrgUnitById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.organizational_units WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function findOrgUnitByCode(string $code): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.organizational_units WHERE code = ?');
        $st->execute([$code]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listOrgUnits(): array
    {
        $st = $this->pdo->query('SELECT * FROM budget.organizational_units ORDER BY code ASC');
        return $st->fetchAll();
    }

    public function insertOrgUnit(string $id, string $code, string $name, ?string $parentId): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.organizational_units (id, code, name, parent_id, is_active)
             VALUES (?, ?, ?, ?, TRUE)'
        );
        $st->execute([$id, $code, $name, $parentId]);
    }

    public function updateOrgUnit(string $id, string $name, bool $isActive): void
    {
        $st = $this->pdo->prepare(
            'UPDATE budget.organizational_units 
             SET name = ?, is_active = ?, updated_at = CURRENT_TIMESTAMP 
             WHERE id = ?'
        );
        $st->execute([$name, Database::bool($isActive), $id]);
    }

    // Formulación (PIA / PIM)
    public function findFormulation(string $fiscalYearId, string $orgUnitId, string $classifierId): ?array
    {
        $st = $this->pdo->prepare(
            'SELECT * FROM budget.formulations 
             WHERE fiscal_year_id = ? AND org_unit_id = ? AND classifier_id = ?'
        );
        $st->execute([$fiscalYearId, $orgUnitId, $classifierId]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listFormulations(string $fiscalYearId, ?string $orgUnitId): array
    {
        if ($orgUnitId) {
            $st = $this->pdo->prepare(
                'SELECT f.*, c.code as classifier_code, c.name as classifier_name, c.type as classifier_type,
                        o.code as org_code, o.name as org_name
                 FROM budget.formulations f
                 JOIN budget.classifiers c ON f.classifier_id = c.id
                 JOIN budget.organizational_units o ON f.org_unit_id = o.id
                 WHERE f.fiscal_year_id = ? AND f.org_unit_id = ?
                 ORDER BY c.code ASC'
            );
            $st->execute([$fiscalYearId, $orgUnitId]);
        } else {
            $st = $this->pdo->prepare(
                'SELECT f.*, c.code as classifier_code, c.name as classifier_name, c.type as classifier_type,
                        o.code as org_code, o.name as org_name
                 FROM budget.formulations f
                 JOIN budget.classifiers c ON f.classifier_id = c.id
                 JOIN budget.organizational_units o ON f.org_unit_id = o.id
                 WHERE f.fiscal_year_id = ?
                 ORDER BY o.code ASC, c.code ASC'
            );
            $st->execute([$fiscalYearId]);
        }
        return $st->fetchAll();
    }

    public function saveFormulation(string $id, string $fiscalYearId, string $orgUnitId, string $classifierId, float $requestedAmount, float $approvedAmount, float $currentAmount, string $status): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.formulations (id, fiscal_year_id, org_unit_id, classifier_id, requested_amount, approved_amount, current_amount, status)
             VALUES (:id, :fiscal_year_id, :org_unit_id, :classifier_id, :requested, :approved, :current, :status)
             ON CONFLICT (fiscal_year_id, org_unit_id, classifier_id) DO UPDATE 
             SET requested_amount = EXCLUDED.requested_amount,
                 approved_amount = EXCLUDED.approved_amount,
                 current_amount = EXCLUDED.current_amount,
                 status = EXCLUDED.status,
                 updated_at = CURRENT_TIMESTAMP'
        );
        $st->execute([
            'id' => $id,
            'fiscal_year_id' => $fiscalYearId,
            'org_unit_id' => $orgUnitId,
            'classifier_id' => $classifierId,
            'requested' => $requestedAmount,
            'approved' => $approvedAmount,
            'current' => $currentAmount,
            'status' => $status
        ]);
    }

    public function updateFormulationCurrentAmount(string $fiscalYearId, string $orgUnitId, string $classifierId, float $delta): void
    {
        $st = $this->pdo->prepare(
            'UPDATE budget.formulations 
             SET current_amount = current_amount + ?, updated_at = CURRENT_TIMESTAMP 
             WHERE fiscal_year_id = ? AND org_unit_id = ? AND classifier_id = ?'
        );
        $st->execute([$delta, $fiscalYearId, $orgUnitId, $classifierId]);
    }

    // Modificaciones
    public function findModificationById(string $id): ?array
    {
        $st = $this->pdo->prepare('SELECT * FROM budget.modifications WHERE id = ?');
        $st->execute([$id]);
        $row = $st->fetch();
        return $row !== false ? $row : null;
    }

    public function listModifications(string $fiscalYearId): array
    {
        $st = $this->pdo->prepare(
            'SELECT m.*, u.name as creator_name 
             FROM budget.modifications m
             JOIN auth.users u ON m.created_by = u.id
             WHERE m.fiscal_year_id = ? 
             ORDER BY m.approved_at DESC, m.created_at DESC'
        );
        $st->execute([$fiscalYearId]);
        return $st->fetchAll();
    }

    public function insertModification(string $id, string $fiscalYearId, string $type, string $documentNumber, ?string $description, string $approvedAt, string $status, string $createdBy): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.modifications (id, fiscal_year_id, type, document_number, description, approved_at, status, created_by)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $st->execute([$id, $fiscalYearId, $type, $documentNumber, $description, $approvedAt, $status, $createdBy]);
    }

    public function insertModificationEntry(string $id, string $modificationId, string $orgUnitId, string $classifierId, float $amount): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.modification_entries (id, modification_id, org_unit_id, classifier_id, amount)
             VALUES (?, ?, ?, ?, ?)'
        );
        $st->execute([$id, $modificationId, $orgUnitId, $classifierId, $amount]);
    }

    public function updateModificationStatus(string $id, string $status): void
    {
        $st = $this->pdo->prepare(
            'UPDATE budget.modifications SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'
        );
        $st->execute([$status, $id]);
    }

    public function getModificationEntries(string $modificationId): array
    {
        $st = $this->pdo->prepare(
            'SELECT me.*, c.code as classifier_code, c.name as classifier_name,
                    o.code as org_code, o.name as org_name
             FROM budget.modification_entries me
             JOIN budget.classifiers c ON me.classifier_id = c.id
             JOIN budget.organizational_units o ON me.org_unit_id = o.id
             WHERE me.modification_id = ?
             ORDER BY o.code ASC, c.code ASC'
        );
        $st->execute([$modificationId]);
        return $st->fetchAll();
    }

    // Ejecuciones
    public function insertExecution(string $id, string $fiscalYearId, string $orgUnitId, string $classifierId, string $phase, string $sourceDocumentType, string $sourceDocumentId, ?string $refExecutionId, float $amount, ?string $description, string $createdBy): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO budget.executions (id, fiscal_year_id, org_unit_id, classifier_id, phase, source_document_type, source_document_id, ref_execution_id, amount, description, created_by)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $st->execute([$id, $fiscalYearId, $orgUnitId, $classifierId, $phase, $sourceDocumentType, $sourceDocumentId, $refExecutionId, $amount, $description, $createdBy]);
    }

    public function listExecutions(string $fiscalYearId, ?string $orgUnitId, ?string $classifierId, ?string $startDate = null, ?string $endDate = null): array
    {
        $sql = 'SELECT e.*, c.code as classifier_code, c.name as classifier_name,
                       o.code as org_code, o.name as org_name, u.name as creator_name
                FROM budget.executions e
                JOIN budget.classifiers c ON e.classifier_id = c.id
                JOIN budget.organizational_units o ON e.org_unit_id = o.id
                JOIN auth.users u ON e.created_by = u.id
                WHERE e.fiscal_year_id = :fiscal_year_id';
        $params = ['fiscal_year_id' => $fiscalYearId];

        if ($orgUnitId) {
            $sql .= ' AND e.org_unit_id = :org_unit_id';
            $params['org_unit_id'] = $orgUnitId;
        }
        if ($classifierId) {
            $sql .= ' AND e.classifier_id = :classifier_id';
            $params['classifier_id'] = $classifierId;
        }
        if ($startDate) {
            $sql .= ' AND e.created_at >= :start_date';
            $params['start_date'] = $startDate . ' 00:00:00';
        }
        if ($endDate) {
            $sql .= ' AND e.created_at <= :end_date';
            $params['end_date'] = $endDate . ' 23:59:59';
        }

        $sql .= ' ORDER BY e.created_at DESC';

        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll();
    }

    public function deleteExecutionsBySource(string $sourceDocumentType, string $sourceDocumentId): void
    {
        $st = $this->pdo->prepare(
            'DELETE FROM budget.executions WHERE source_document_type = ? AND source_document_id = ?'
        );
        $st->execute([$sourceDocumentType, $sourceDocumentId]);
    }

    // Disponibilidad y Reporte Consolidado
    public function getAvailability(string $fiscalYearId, string $orgUnitId, string $classifierId): array
    {
        $sql = '
            SELECT 
                f.approved_amount,
                f.current_amount,
                COALESCE(pre.amount, 0) as precommitted,
                COALESCE(comm.amount, 0) as committed,
                COALESCE(acc.amount, 0) as accrued,
                COALESCE(p.amount, 0) as paid,
                (f.current_amount - COALESCE(pre.amount, 0) - COALESCE(comm.amount, 0) - COALESCE(acc.amount, 0)) as available
            FROM budget.formulations f
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = \'pre_commitment\' 
                  AND id NOT IN (SELECT ref_execution_id FROM budget.executions WHERE ref_execution_id IS NOT NULL)
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) pre ON f.fiscal_year_id = pre.fiscal_year_id AND f.org_unit_id = pre.org_unit_id AND f.classifier_id = pre.classifier_id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = \'commitment\' 
                  AND id NOT IN (SELECT ref_execution_id FROM budget.executions WHERE ref_execution_id IS NOT NULL AND phase = \'accrued\')
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) comm ON f.fiscal_year_id = comm.fiscal_year_id AND f.org_unit_id = comm.org_unit_id AND f.classifier_id = comm.classifier_id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = \'accrued\' 
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) acc ON f.fiscal_year_id = acc.fiscal_year_id AND f.org_unit_id = acc.org_unit_id AND f.classifier_id = acc.classifier_id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = \'paid\' 
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) p ON f.fiscal_year_id = p.fiscal_year_id AND f.org_unit_id = p.org_unit_id AND f.classifier_id = p.classifier_id
            WHERE f.fiscal_year_id = ? AND f.org_unit_id = ? AND f.classifier_id = ?
        ';

        $st = $this->pdo->prepare($sql);
        $st->execute([$fiscalYearId, $orgUnitId, $classifierId]);
        $row = $st->fetch();

        if ($row !== false) {
            return [
                'approved_amount' => (float)$row['approved_amount'],
                'current_amount' => (float)$row['current_amount'],
                'precommitted' => (float)$row['precommitted'],
                'committed' => (float)$row['committed'],
                'accrued' => (float)$row['accrued'],
                'paid' => (float)$row['paid'],
                'available' => (float)$row['available'],
            ];
        }

        return [
            'approved_amount' => 0.0,
            'current_amount' => 0.0,
            'precommitted' => 0.0,
            'committed' => 0.0,
            'accrued' => 0.0,
            'paid' => 0.0,
            'available' => 0.0,
        ];
    }

    public function getExecutionSummary(string $fiscalYearId, ?string $orgUnitId, ?string $startDate = null, ?string $endDate = null): array
    {
        $execWhere = '';
        $params = ['fiscal_year_id' => $fiscalYearId];

        if ($startDate) {
            $execWhere .= ' AND created_at >= :start_date';
            $params['start_date'] = $startDate . ' 00:00:00';
        }
        if ($endDate) {
            $execWhere .= ' AND created_at <= :end_date';
            $params['end_date'] = $endDate . ' 23:59:59';
        }

        $sql = "
            SELECT 
                f.id as formulation_id,
                f.fiscal_year_id,
                f.org_unit_id,
                f.classifier_id,
                f.approved_amount,
                f.current_amount,
                c.code as classifier_code,
                c.name as classifier_name,
                c.type as classifier_type,
                o.code as org_code,
                o.name as org_name,
                COALESCE(pre.amount, 0) as precommitted,
                COALESCE(comm.amount, 0) as committed,
                COALESCE(acc.amount, 0) as accrued,
                COALESCE(p.amount, 0) as paid,
                (f.current_amount - COALESCE(pre.amount, 0) - COALESCE(comm.amount, 0) - COALESCE(acc.amount, 0)) as available
            FROM budget.formulations f
            JOIN budget.classifiers c ON f.classifier_id = c.id
            JOIN budget.organizational_units o ON f.org_unit_id = o.id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = 'pre_commitment' 
                  AND id NOT IN (SELECT ref_execution_id FROM budget.executions WHERE ref_execution_id IS NOT NULL)
                  {$execWhere}
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) pre ON f.fiscal_year_id = pre.fiscal_year_id AND f.org_unit_id = pre.org_unit_id AND f.classifier_id = pre.classifier_id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = 'commitment' 
                  AND id NOT IN (SELECT ref_execution_id FROM budget.executions WHERE ref_execution_id IS NOT NULL AND phase = 'accrued')
                  {$execWhere}
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) comm ON f.fiscal_year_id = comm.fiscal_year_id AND f.org_unit_id = comm.org_unit_id AND f.classifier_id = comm.classifier_id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = 'accrued' 
                  {$execWhere}
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) acc ON f.fiscal_year_id = acc.fiscal_year_id AND f.org_unit_id = acc.org_unit_id AND f.classifier_id = acc.classifier_id
            LEFT JOIN (
                SELECT fiscal_year_id, org_unit_id, classifier_id, SUM(amount) as amount 
                FROM budget.executions 
                WHERE phase = 'paid' 
                  {$execWhere}
                GROUP BY fiscal_year_id, org_unit_id, classifier_id
            ) p ON f.fiscal_year_id = p.fiscal_year_id AND f.org_unit_id = p.org_unit_id AND f.classifier_id = p.classifier_id
            WHERE f.fiscal_year_id = :fiscal_year_id
        ";
        
        if ($orgUnitId) {
            $sql .= ' AND f.org_unit_id = :org_unit_id';
            $params['org_unit_id'] = $orgUnitId;
        }

        $sql .= ' ORDER BY o.code ASC, c.code ASC';

        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll();
    }
}
