<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class PaymentReviewRepository
{
    private $db;

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

    private function buildPaymentQueryParts(array $filters = []): array
    {
        $tafsiliLookupExpr = "COALESCE((
            SELECT JSON_UNQUOTE(JSON_EXTRACT(li.meta_json, '$.tafsili_code'))
            FROM lookup_groups lg
            JOIN lookup_items li ON li.group_id = lg.id
            WHERE lg.code = 'payment_destination_banks'
              AND li.meta_json IS NOT NULL
              AND li.meta_json <> ''
              AND JSON_VALID(li.meta_json)
              AND (
                  li.code = t.destination_bank
                  OR li.value = t.destination_bank
                  OR li.title = t.destination_bank
                  OR li.title = t.destination_bank_title
              )
            ORDER BY
                (li.code = t.destination_bank) DESC,
                (li.value = t.destination_bank) DESC,
                li.sort_order ASC,
                li.id ASC
            LIMIT 1
        ), '')";
        $hasTafsiliColumn = $this->db->hasColumn('transactions', 'destination_tafsili_code');
        $tafsiliExpr = $hasTafsiliColumn
            ? "COALESCE(t.destination_tafsili_code, '')"
            : $tafsiliLookupExpr;
        $tafsiliFilterExpr = $tafsiliExpr;
        $hasFinancialCache = $this->db->hasColumn('transactions', 'is_cheque_payment')
            && $this->db->hasColumn('transactions', 'payment_screen_at')
            && $this->db->hasColumn('transactions', 'payment_screen_date');
        $imageExprParts = ["NULLIF(t.receipt_image_path, '')"];
        if ($this->db->hasColumn('transactions', 'check_image_path')) {
            $imageExprParts[] = "NULLIF(t.check_image_path, '')";
        }
        if ($this->db->hasColumn('transactions', 'check_sayadi_image_path')) {
            $imageExprParts[] = "NULLIF(t.check_sayadi_image_path, '')";
        }
        $imageExpr = "COALESCE(" . implode(', ', $imageExprParts) . ", '')";

        $where = ["COALESCE(t.amount, 0) > 0"];
        $where[] = $hasFinancialCache
            ? "(t.is_cheque_payment = 0 AND (t.method IS NULL OR t.method NOT IN ('check','cheque','چک')))"
            : "(t.method IS NULL OR t.method NOT IN ('check','cheque','چک'))";
        $params = [];

        $sepidarStatus = (string)($filters['sepidar_status'] ?? '');
        if ($sepidarStatus === 'registered') {
            $where[] = "pr.sepidar_registered_at IS NOT NULL";
        } elseif ($sepidarStatus === 'pending') {
            $where[] = "pr.sepidar_registered_at IS NULL";
        }

        if (!empty($filters['search'])) {
            $where[] = "(
                d.title LIKE ?
                OR COALESCE(p_by_id.customer_code, p_by_didar.customer_code) LIKE ?
                OR COALESCE(p_by_id.mobile_phone, p_by_didar.mobile_phone) LIKE ?
                OR COALESCE(p_by_id.first_name, p_by_didar.first_name) LIKE ?
                OR COALESCE(p_by_id.last_name, p_by_didar.last_name) LIKE ?
            )";
            $s = '%' . $filters['search'] . '%';
            array_push($params, $s, $s, $s, $s, $s);
        }

        if (!empty($filters['date_from'])) {
            $where[] = $hasFinancialCache
                ? "t.payment_screen_date >= ?"
                : "DATE(COALESCE(t.deposit_date, t.payment_date, t.payment_time, t.created_at)) >= ?";
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = $hasFinancialCache
                ? "t.payment_screen_date <= ?"
                : "DATE(COALESCE(t.deposit_date, t.payment_date, t.payment_time, t.created_at)) <= ?";
            $params[] = $filters['date_to'];
        }

        if (!empty($filters['lead_owner_id'])) {
            $where[] = "COALESCE(p_by_id.owner_didar_id, p_by_didar.owner_didar_id) = ?";
            $params[] = $filters['lead_owner_id'];
        }

        if (!empty($filters['service'])) {
            $where[] = "(
                d.requested_services LIKE ?
                OR d.title LIKE ?
                OR EXISTS (
                    SELECT 1
                    FROM deal_products dp
                    WHERE dp.deal_id = d.id
                      AND (
                          dp.product_name_snapshot LIKE ?
                          OR dp.service_title_snapshot LIKE ?
                      )
                )
            )";
            $service = '%' . $filters['service'] . '%';
            array_push($params, $service, $service, $service, $service);
        }

        $tafsiliStatus = (string)($filters['tafsili_status'] ?? '');
        if ($tafsiliStatus === 'has') {
            $where[] = "{$tafsiliFilterExpr} <> ''";
        } elseif ($tafsiliStatus === 'missing') {
            $where[] = "{$tafsiliFilterExpr} = ''";
        }

        $imageStatus = (string)($filters['image_status'] ?? '');
        if ($imageStatus === 'has') {
            $where[] = "{$imageExpr} <> ''";
        } elseif ($imageStatus === 'missing') {
            $where[] = "{$imageExpr} = ''";
        }

        $orderBy = $hasFinancialCache
            ? "t.payment_screen_at DESC, t.id DESC"
            : "COALESCE(t.deposit_date, t.payment_date, t.payment_time, t.created_at) DESC, t.id DESC";
        if ($sepidarStatus === 'registered') {
            $orderBy = $hasFinancialCache
                ? "pr.sepidar_registered_at DESC, t.payment_screen_at DESC, t.id DESC"
                : "pr.sepidar_registered_at DESC, COALESCE(t.deposit_date, t.payment_date, t.payment_time, t.created_at) DESC, t.id DESC";
        }

        return [
            'where' => $where,
            'params' => $params,
            'tafsili_expr' => $tafsiliExpr,
            'order_by' => $orderBy,
            'has_financial_cache' => $hasFinancialCache,
        ];
    }

    public function countPayments(array $filters = []): int
    {
        $pdo = $this->db->getPdo();
        $parts = $this->buildPaymentQueryParts($filters);
        $sql = "
            SELECT COUNT(t.id)
            FROM transactions t
            JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p_by_id ON d.person_id IS NOT NULL AND p_by_id.id = d.person_id
            LEFT JOIN persons p_by_didar ON d.person_id IS NULL AND p_by_didar.didar_contact_id = d.contact_didar_id
            LEFT JOIN payment_reviews pr ON pr.transaction_id = t.id
            WHERE " . implode(' AND ', $parts['where']) . "
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($parts['params']);
        return (int)$stmt->fetchColumn();
    }

    public function listPayments(array $filters = []): array
    {
        $pdo = $this->db->getPdo();
        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $ownerNameExpr = $hasDisplayName
            ? "COALESCE(u.display_name, CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), d.owner_didar_id, '')"
            : "COALESCE(CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')), u.username, d.owner_didar_id, '')";
        $parts = $this->buildPaymentQueryParts($filters);
        $limit = isset($filters['limit']) ? max(1, min(200, (int)$filters['limit'])) : 50;
        $offset = isset($filters['offset']) ? max(0, (int)$filters['offset']) : 0;
        $payerExprParts = [];
        foreach (['depositor_name', 'account_owner_name', 'destination_account_owner', 'check_owner_name'] as $payerColumn) {
            if ($this->db->hasColumn('transactions', $payerColumn)) {
                $payerExprParts[] = "NULLIF(t.{$payerColumn}, '')";
            }
        }
        $payerExprParts[] = "NULLIF(TRIM(CONCAT_WS(' ', COALESCE(p_by_id.first_name, p_by_didar.first_name), COALESCE(p_by_id.last_name, p_by_didar.last_name))), '')";
        $payerNameExpr = "COALESCE(" . implode(', ', $payerExprParts) . ", '')";

        $optionalTransactionSelects = [];
        foreach ([
            'depositor_name',
            'account_owner_name',
            'destination_account_owner',
            'check_owner_name',
            'payment_record_id',
            'payment_uuid',
            'payment_owner_user_id',
            'payment_owner_didar_id',
            'payment_owner_name_snapshot',
            'commission_status',
            'commission_effective_date',
            'registered_by_name',
        ] as $column) {
            $optionalTransactionSelects[] = $this->db->hasColumn('transactions', $column)
                ? "t.{$column} AS {$column}"
                : "NULL AS {$column}";
        }
        $optionalTransactionSql = implode(",\n                ", $optionalTransactionSelects);
        $sql = "
            SELECT
                t.id AS transaction_id,
                t.deal_id,
                d.didar_deal_id,
                d.title AS deal_title,
                {$ownerNameExpr} AS owner_name,
                TRIM(CONCAT_WS(' ', COALESCE(p_by_id.first_name, p_by_didar.first_name), COALESCE(p_by_id.last_name, p_by_didar.last_name))) AS contact_name,
                {$payerNameExpr} AS payer_name,
                COALESCE(p_by_id.customer_code, p_by_didar.customer_code) AS customer_code,
                COALESCE(p_by_id.mobile_phone, p_by_didar.mobile_phone) AS mobile_phone,
                d.service_cost,
                d.payable_amount,
                t.amount AS paid_amount,
                t.created_at AS payment_registered_at,
                t.payment_date,
                t.receipt_image_path,
                t.destination_bank,
                t.destination_bank_title,
                t.destination_card_full,
                t.destination_account_number,
                t.destination_sheba,
                {$parts['tafsili_expr']} AS destination_tafsili_code,
                {$optionalTransactionSql},
                pr.sepidar_registered_at,
                pr.registered_by
            FROM transactions t
            JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p_by_id ON d.person_id IS NOT NULL AND p_by_id.id = d.person_id
            LEFT JOIN persons p_by_didar ON d.person_id IS NULL AND p_by_didar.didar_contact_id = d.contact_didar_id
            LEFT JOIN users u ON d.owner_didar_id = u.didar_user_id
            LEFT JOIN payment_reviews pr ON pr.transaction_id = t.id
            WHERE " . implode(' AND ', $parts['where']) . "
            ORDER BY {$parts['order_by']}
            LIMIT {$limit} OFFSET {$offset}
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($parts['params']);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function getPaymentTransaction(int $transactionId): ?array
    {
        $stmt = $this->db->getPdo()->prepare(
            "SELECT id, deal_id, amount FROM transactions WHERE id = ? AND COALESCE(amount, 0) > 0 AND (method IS NULL OR method NOT IN ('check','cheque','چک')) LIMIT 1"
        );
        $stmt->execute([$transactionId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function markSepidarRegistered(int $transactionId, int $dealId, float $amount, string $by): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            INSERT INTO payment_reviews (transaction_id, deal_id, amount, sepidar_registered_at, registered_by)
            VALUES (?, ?, ?, NOW(), ?)
            ON DUPLICATE KEY UPDATE
                deal_id = VALUES(deal_id),
                amount = VALUES(amount),
                registered_by = IF(sepidar_registered_at IS NULL, VALUES(registered_by), registered_by),
                sepidar_registered_at = IF(sepidar_registered_at IS NULL, NOW(), sepidar_registered_at)
        ");
        $stmt->execute([$transactionId, $dealId, $amount, $by]);

        $review = $this->getReview($transactionId);
        return $review ?: [];
    }

    public function getReview(int $transactionId): ?array
    {
        $stmt = $this->db->getPdo()->prepare("SELECT * FROM payment_reviews WHERE transaction_id = ? LIMIT 1");
        $stmt->execute([$transactionId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}
