<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class PaymentRecordRepository
{
    private $db;

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

    public function upsertFromLegacySource(array $payload): int
    {
        $sourceHash = trim((string)($payload['source_hash'] ?? ''));
        if ($sourceHash === '') {
            throw new \InvalidArgumentException('source_hash is required');
        }

        $paymentUuid = trim((string)($payload['payment_uuid'] ?? ''));
        if ($paymentUuid === '') {
            $paymentUuid = $this->uuidV4();
        }

        $values = [
            'source_type' => strtoupper(trim((string)($payload['source_type'] ?? 'MANUAL'))) ?: 'MANUAL',
            'source_id' => $this->nullableString($payload['source_id'] ?? null),
            'source_hash' => $sourceHash,
            'deal_id' => isset($payload['deal_id']) && $payload['deal_id'] !== '' ? (int)$payload['deal_id'] : null,
            'deal_didar_id' => $this->nullableString($payload['deal_didar_id'] ?? null),
            'contact_didar_id' => $this->nullableString($payload['contact_didar_id'] ?? null),
            'payment_uuid' => $paymentUuid,
            'payment_sequence' => isset($payload['payment_sequence']) && $payload['payment_sequence'] !== '' ? (int)$payload['payment_sequence'] : null,
            'amount' => (float)($payload['amount'] ?? 0),
            'currency' => $this->nullableString($payload['currency'] ?? 'IRR') ?: 'IRR',
            'method' => $this->nullableString($payload['method'] ?? null),
            'payment_date' => $this->nullableString($payload['payment_date'] ?? null),
            'deposit_date' => $this->nullableString($payload['deposit_date'] ?? null),
            'effective_date' => $this->nullableString($payload['effective_date'] ?? null),
            'registered_at' => $this->nullableString($payload['registered_at'] ?? null),
            'is_deleted_placeholder' => !empty($payload['is_deleted_placeholder']) ? 1 : 0,
            'is_refunded' => !empty($payload['is_refunded']) ? 1 : 0,
            'refunded_amount' => (float)($payload['refunded_amount'] ?? 0),
            'refund_status' => strtoupper(trim((string)($payload['refund_status'] ?? 'NONE'))) ?: 'NONE',
            'payment_owner_user_id' => isset($payload['payment_owner_user_id']) && $payload['payment_owner_user_id'] !== '' ? (int)$payload['payment_owner_user_id'] : null,
            'payment_owner_didar_id' => $this->nullableString($payload['payment_owner_didar_id'] ?? null),
            'payment_owner_name_snapshot' => $this->nullableString($payload['payment_owner_name_snapshot'] ?? null),
            'payment_owner_locked_at' => $this->nullableString($payload['payment_owner_locked_at'] ?? null),
            'payment_owner_source' => strtoupper(trim((string)($payload['payment_owner_source'] ?? 'UNKNOWN'))) ?: 'UNKNOWN',
            'supervisor_user_id_snapshot' => isset($payload['supervisor_user_id_snapshot']) && $payload['supervisor_user_id_snapshot'] !== '' ? (int)$payload['supervisor_user_id_snapshot'] : null,
            'manager_user_id_snapshot' => isset($payload['manager_user_id_snapshot']) && $payload['manager_user_id_snapshot'] !== '' ? (int)$payload['manager_user_id_snapshot'] : null,
            'hierarchy_snapshot_at' => $this->nullableString($payload['hierarchy_snapshot_at'] ?? null),
            'cheque_status' => strtolower(trim((string)($payload['cheque_status'] ?? 'not_cheque'))) ?: 'not_cheque',
            'cheque_collection_status' => strtolower(trim((string)($payload['cheque_collection_status'] ?? 'not_cheque'))) ?: 'not_cheque',
            'cheque_due_date' => $this->nullableString($payload['cheque_due_date'] ?? null),
            'cheque_collection_date' => $this->nullableString($payload['cheque_collection_date'] ?? null),
            'finance_status' => strtoupper(trim((string)($payload['finance_status'] ?? 'NONE'))) ?: 'NONE',
            'sepidar_registered_at' => $this->nullableString($payload['sepidar_registered_at'] ?? null),
            'commission_status' => strtoupper(trim((string)($payload['commission_status'] ?? 'PENDING'))) ?: 'PENDING',
            'raw_snapshot_json' => $this->jsonOrNull($payload['raw_snapshot_json'] ?? ($payload['raw_snapshot'] ?? null)),
        ];

        $columns = array_keys($values);
        $updates = array_values(array_filter($columns, static function ($column) {
            return !in_array($column, ['id', 'source_hash', 'payment_uuid', 'created_at'], true);
        }));
        $assignments = array_map(static function ($column) {
            return "{$column} = VALUES({$column})";
        }, $updates);
        $assignments[] = 'updated_at = NOW()';

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            INSERT INTO payment_records (" . implode(',', $columns) . ")
            VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")
            ON DUPLICATE KEY UPDATE " . implode(', ', $assignments)
        );
        $stmt->execute(array_values($values));

        $id = (int)$pdo->lastInsertId();
        if ($id > 0) {
            return $id;
        }

        $existing = $this->findBySourceHash($sourceHash);
        if (!$existing) {
            $existing = $this->findByPaymentUuid($paymentUuid);
        }
        return (int)($existing['id'] ?? 0);
    }

    public function link(int $paymentRecordId, string $linkedTable, string $linkedId, string $linkType): void
    {
        if ($paymentRecordId <= 0 || $linkedTable === '' || $linkedId === '' || $linkType === '') {
            return;
        }
        $stmt = $this->db->getPdo()->prepare("
            INSERT INTO payment_record_links (payment_record_id, linked_table, linked_id, link_type)
            VALUES (?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE payment_record_id = VALUES(payment_record_id)
        ");
        $stmt->execute([$paymentRecordId, $linkedTable, $linkedId, strtoupper($linkType)]);
    }

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

    public function findBySourceHash(string $sourceHash): ?array
    {
        $sourceHash = trim($sourceHash);
        if ($sourceHash === '') {
            return null;
        }
        $stmt = $this->db->getPdo()->prepare('SELECT * FROM payment_records WHERE source_hash = ? LIMIT 1');
        $stmt->execute([$sourceHash]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findByPaymentUuid(string $paymentUuid): ?array
    {
        $paymentUuid = trim($paymentUuid);
        if ($paymentUuid === '') {
            return null;
        }
        $stmt = $this->db->getPdo()->prepare('SELECT * FROM payment_records WHERE payment_uuid = ? LIMIT 1');
        $stmt->execute([$paymentUuid]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findByTransactionId(int $transactionId): ?array
    {
        if ($transactionId <= 0) {
            return null;
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT pr.*
            FROM payment_records pr
            INNER JOIN payment_record_links l ON l.payment_record_id = pr.id
            WHERE l.linked_table = 'transactions'
              AND l.linked_id = ?
            ORDER BY pr.id DESC
            LIMIT 1
        ");
        $stmt->execute([(string)$transactionId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findByDealId(int $dealId): array
    {
        if ($dealId <= 0) {
            return [];
        }
        $stmt = $this->db->getPdo()->prepare('SELECT * FROM payment_records WHERE deal_id = ? ORDER BY effective_date ASC, id ASC');
        $stmt->execute([$dealId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function findEligibleForCommission(array $filters = []): array
    {
        $where = [
            "is_deleted_placeholder = 0",
            "amount > 0",
            "commission_status IN ('PENDING','CALCULATED','APPROVED','READY_FOR_PAYOUT')",
            "effective_date IS NOT NULL",
        ];
        $params = [];
        if (!empty($filters['date_from'])) {
            $where[] = 'effective_date >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'effective_date <= ?';
            $params[] = $filters['date_to'];
        }
        if (!empty($filters['payment_owner_user_id'])) {
            $where[] = 'payment_owner_user_id = ?';
            $params[] = (int)$filters['payment_owner_user_id'];
        }
        if (!empty($filters['deal_id'])) {
            $where[] = 'deal_id = ?';
            $params[] = (int)$filters['deal_id'];
        }
        $sql = 'SELECT * FROM payment_records WHERE ' . implode(' AND ', $where) . ' ORDER BY effective_date ASC, id ASC';
        $stmt = $this->db->getPdo()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function markCommissionStatus(int $paymentRecordId, string $status): bool
    {
        if ($paymentRecordId <= 0 || trim($status) === '') {
            return false;
        }
        $stmt = $this->db->getPdo()->prepare('UPDATE payment_records SET commission_status = ?, updated_at = NOW() WHERE id = ?');
        return $stmt->execute([strtoupper(trim($status)), $paymentRecordId]);
    }

    public function overrideOwner(int $paymentRecordId, array $snapshot): bool
    {
        if ($paymentRecordId <= 0) {
            return false;
        }
        $stmt = $this->db->getPdo()->prepare("
            UPDATE payment_records
            SET payment_owner_user_id = ?,
                payment_owner_didar_id = ?,
                payment_owner_name_snapshot = ?,
                payment_owner_locked_at = NOW(),
                payment_owner_source = 'ADMIN_OVERRIDE',
                supervisor_user_id_snapshot = ?,
                manager_user_id_snapshot = ?,
                hierarchy_snapshot_at = ?,
                updated_at = NOW()
            WHERE id = ?
        ");
        return $stmt->execute([
            isset($snapshot['payment_owner_user_id']) && $snapshot['payment_owner_user_id'] !== '' ? (int)$snapshot['payment_owner_user_id'] : null,
            $this->nullableString($snapshot['payment_owner_didar_id'] ?? null),
            $this->nullableString($snapshot['payment_owner_name_snapshot'] ?? null),
            isset($snapshot['supervisor_user_id_snapshot']) && $snapshot['supervisor_user_id_snapshot'] !== '' ? (int)$snapshot['supervisor_user_id_snapshot'] : null,
            isset($snapshot['manager_user_id_snapshot']) && $snapshot['manager_user_id_snapshot'] !== '' ? (int)$snapshot['manager_user_id_snapshot'] : null,
            $this->nullableString($snapshot['hierarchy_snapshot_at'] ?? null),
            $paymentRecordId,
        ]);
    }

    private function nullableString($value): ?string
    {
        $value = trim((string)$value);
        return $value === '' ? null : $value;
    }

    private function jsonOrNull($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }
        if (is_string($value)) {
            json_decode($value, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                return $value;
            }
        }
        return json_encode($value, JSON_UNESCAPED_UNICODE);
    }

    private function uuidV4(): string
    {
        $data = random_bytes(16);
        $data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
        $data[8] = chr((ord($data[8]) & 0x3f) | 0x80);
        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
    }
}
