<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\DealPayment;
use PDO;

class DealPaymentRepository
{
    private $db;
    private $columnCache = [];

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function getByDealDidarId(string $dealDidarId): array
    {
        if (trim($dealDidarId) === '') {
            return [];
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT * FROM deal_payments
            WHERE deal_didar_id = ?
            ORDER BY receipt_date ASC, id ASC
        ");
        $stmt->execute([$dealDidarId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(static function ($row) {
            return new DealPayment($row);
        }, $rows);
    }

    public function save(DealPayment $payment): int
    {
        $pdo = $this->db->getPdo();
        if (!empty($payment->id)) {
            $payload = $this->buildWritablePayload($payment, false);
            if (!empty($payload)) {
                $setParts = array_map(static function ($column) {
                    return "{$column} = ?";
                }, array_keys($payload));
                $params = array_values($payload);
                $params[] = (int)$payment->id;
                $stmt = $pdo->prepare('UPDATE deal_payments SET ' . implode(', ', $setParts) . ' WHERE id = ?');
                $stmt->execute($params);
            }
            return (int)$payment->id;
        }

        $payload = $this->buildWritablePayload($payment, true);
        $columns = array_keys($payload);
        $placeholders = implode(',', array_fill(0, count($columns), '?'));
        $stmt = $pdo->prepare("INSERT INTO deal_payments (" . implode(',', $columns) . ") VALUES ({$placeholders})");
        $stmt->execute(array_values($payload));
        return (int)$pdo->lastInsertId();
    }

    public function findById(int $id): ?DealPayment
    {
        if ($id <= 0) {
            return null;
        }
        $stmt = $this->db->getPdo()->prepare("SELECT * FROM deal_payments WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new DealPayment($row) : null;
    }

    private function buildWritablePayload(DealPayment $payment, bool $includeDealId): array
    {
        $values = [
            'deal_didar_id' => trim((string)$payment->deal_didar_id),
            'payment_type' => strtoupper(trim((string)$payment->payment_type)) ?: 'CASH',
            'payment_status' => strtoupper(trim((string)$payment->payment_status)) ?: 'PENDING',
            'payment_amount' => (float)$payment->payment_amount,
            'receipt_date' => $payment->receipt_date ?: null,
            'deposit_date' => $payment->deposit_date ?: $this->deriveDepositDate($payment),
            'cheque_due_date' => $payment->cheque_due_date ?: null,
            'cheque_cashed_date' => $payment->cheque_cashed_date ?: null,
            'is_accepted_by_finance' => !empty($payment->is_accepted_by_finance) ? 1 : 0,
            'accepted_by_user_id' => $payment->accepted_by_user_id ? (int)$payment->accepted_by_user_id : null,
            'accepted_at' => $payment->accepted_at ?: null,
            'rejected_by_user_id' => $payment->rejected_by_user_id ? (int)$payment->rejected_by_user_id : null,
            'rejected_at' => $payment->rejected_at ?: null,
            'reference_no' => $payment->reference_no ?: null,
            'notes' => $payment->notes ?: null,
            'created_by_user_id' => $payment->created_by_user_id ? (int)$payment->created_by_user_id : null,
        ];

        $payload = [];
        foreach ($values as $column => $value) {
            if (!$includeDealId && $column === 'deal_didar_id') {
                continue;
            }
            if ($this->hasColumn($column)) {
                $payload[$column] = $value;
            }
        }
        return $payload;
    }

    private function deriveDepositDate(DealPayment $payment): ?string
    {
        $source = $payment->cheque_cashed_date ?: $payment->receipt_date;
        if (!$source) {
            return null;
        }
        $timestamp = strtotime((string)$source);
        return $timestamp ? date('Y-m-d', $timestamp) : null;
    }

    private function hasColumn(string $column): bool
    {
        if (isset($this->columnCache[$column])) {
            return $this->columnCache[$column];
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT COUNT(*) AS cnt
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'deal_payments'
              AND COLUMN_NAME = ?
        ");
        $stmt->execute([$column]);
        $exists = ((int)($stmt->fetch(PDO::FETCH_ASSOC)['cnt'] ?? 0)) > 0;
        $this->columnCache[$column] = $exists;
        return $exists;
    }
}
