<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class CommissionLineRepository
{
    private $db;

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

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

        $values = [
            'payment_record_id' => (int)($payload['payment_record_id'] ?? 0),
            'deal_didar_id' => trim((string)($payload['deal_didar_id'] ?? '')),
            'beneficiary_user_id' => (int)($payload['beneficiary_user_id'] ?? 0),
            'beneficiary_type' => strtoupper(trim((string)($payload['beneficiary_type'] ?? 'DIRECT_PAYMENT_OWNER'))) ?: 'DIRECT_PAYMENT_OWNER',
            'payment_owner_user_id' => (int)($payload['payment_owner_user_id'] ?? 0),
            'product_id' => isset($payload['product_id']) && $payload['product_id'] !== '' ? (int)$payload['product_id'] : null,
            'commission_rule_id' => isset($payload['commission_rule_id']) && $payload['commission_rule_id'] !== '' ? (int)$payload['commission_rule_id'] : null,
            'commission_rule_assignment_id' => isset($payload['commission_rule_assignment_id']) && $payload['commission_rule_assignment_id'] !== '' ? (int)$payload['commission_rule_assignment_id'] : null,
            'payment_amount_snapshot' => (float)($payload['payment_amount_snapshot'] ?? 0),
            'commission_base_amount' => (float)($payload['commission_base_amount'] ?? 0),
            'applied_rate_percent' => (float)($payload['applied_rate_percent'] ?? 0),
            'calculated_commission' => (float)($payload['calculated_commission'] ?? 0),
            'effective_date' => trim((string)($payload['effective_date'] ?? '')),
            'billing_month' => trim((string)($payload['billing_month'] ?? '')),
            'rule_snapshot_json' => $this->jsonOrNull($payload['rule_snapshot_json'] ?? ($payload['rule_snapshot'] ?? null)),
            'tier_snapshot_json' => $this->jsonOrNull($payload['tier_snapshot_json'] ?? ($payload['tier_snapshot'] ?? null)),
            'payment_snapshot_json' => $this->jsonOrNull($payload['payment_snapshot_json'] ?? ($payload['payment_snapshot'] ?? null)),
            'product_snapshot_json' => $this->jsonOrNull($payload['product_snapshot_json'] ?? ($payload['product_snapshot'] ?? null)),
            'hierarchy_snapshot_json' => $this->jsonOrNull($payload['hierarchy_snapshot_json'] ?? ($payload['hierarchy_snapshot'] ?? null)),
            'status' => strtoupper(trim((string)($payload['status'] ?? 'CALCULATED'))) ?: 'CALCULATED',
            'is_reversal' => !empty($payload['is_reversal']) ? 1 : 0,
            'reversal_of_line_id' => isset($payload['reversal_of_line_id']) && $payload['reversal_of_line_id'] !== '' ? (int)$payload['reversal_of_line_id'] : null,
            'refund_id' => isset($payload['refund_id']) && $payload['refund_id'] !== '' ? (int)$payload['refund_id'] : null,
            'calculation_run_id' => $this->nullableString($payload['calculation_run_id'] ?? null),
            'source_hash' => $sourceHash,
        ];

        foreach (['payment_record_id', 'beneficiary_user_id', 'payment_owner_user_id'] as $requiredInt) {
            if ((int)$values[$requiredInt] <= 0) {
                throw new \InvalidArgumentException($requiredInt . ' is required');
            }
        }
        if ($values['deal_didar_id'] === '' || $values['effective_date'] === '' || $values['billing_month'] === '') {
            throw new \InvalidArgumentException('deal_didar_id, effective_date and billing_month are required');
        }

        $columns = array_keys($values);
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            INSERT INTO commission_lines (" . implode(',', $columns) . ")
            VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")
            ON DUPLICATE KEY UPDATE
                id = LAST_INSERT_ID(id),
                payment_amount_snapshot = IF(status = 'VOID', VALUES(payment_amount_snapshot), payment_amount_snapshot),
                commission_base_amount = IF(status = 'VOID', VALUES(commission_base_amount), commission_base_amount),
                applied_rate_percent = IF(status = 'VOID', VALUES(applied_rate_percent), applied_rate_percent),
                calculated_commission = IF(status = 'VOID', VALUES(calculated_commission), calculated_commission),
                commission_rule_id = IF(status = 'VOID', VALUES(commission_rule_id), commission_rule_id),
                commission_rule_assignment_id = IF(status = 'VOID', VALUES(commission_rule_assignment_id), commission_rule_assignment_id),
                rule_snapshot_json = IF(status = 'VOID', VALUES(rule_snapshot_json), rule_snapshot_json),
                tier_snapshot_json = IF(status = 'VOID', VALUES(tier_snapshot_json), tier_snapshot_json),
                payment_snapshot_json = IF(status = 'VOID', VALUES(payment_snapshot_json), payment_snapshot_json),
                product_snapshot_json = IF(status = 'VOID', VALUES(product_snapshot_json), product_snapshot_json),
                hierarchy_snapshot_json = IF(status = 'VOID', VALUES(hierarchy_snapshot_json), hierarchy_snapshot_json),
                status = IF(status = 'VOID', VALUES(status), status)
        ");
        $stmt->execute(array_values($values));
        return (int)$pdo->lastInsertId();
    }

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

    public function listByPaymentRecordId(int $paymentRecordId): array
    {
        if ($paymentRecordId <= 0) {
            return [];
        }
        $stmt = $this->db->getPdo()->prepare('SELECT * FROM commission_lines WHERE payment_record_id = ? ORDER BY id ASC');
        $stmt->execute([$paymentRecordId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    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);
    }
}
