<?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
    {
        $tafsiliExpr = "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
        ), '')";
        $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", "(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 p.customer_code LIKE ? OR p.mobile_phone LIKE ? OR p.first_name LIKE ? OR p.last_name LIKE ?)";
            $s = '%' . $filters['search'] . '%';
            array_push($params, $s, $s, $s, $s, $s);
        }

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

        if (!empty($filters['lead_owner_id'])) {
            $where[] = "p.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[] = "{$tafsiliExpr} <> ''";
        } elseif ($tafsiliStatus === 'missing') {
            $where[] = "{$tafsiliExpr} = ''";
        }

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

        $limit = isset($filters['limit']) ? max(1, min(1000, (int)$filters['limit'])) : 200;
        $orderBy = "COALESCE(t.created_at, t.payment_date, t.payment_time) DESC, t.id DESC";
        if ($sepidarStatus === 'registered') {
            $orderBy = "pr.sepidar_registered_at DESC, COALESCE(t.created_at, t.payment_date, t.payment_time) DESC, t.id DESC";
        }

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

    public function countPayments(array $filters = []): int
    {
        $pdo = $this->db->getPdo();
        $parts = $this->buildPaymentQueryParts($filters);
        $sql = "
            SELECT COUNT(DISTINCT t.id)
            FROM transactions t
            JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON (
                (d.person_id IS NOT NULL AND d.person_id = p.id)
                OR
                (d.person_id IS NULL AND d.contact_didar_id = p.didar_contact_id COLLATE utf8mb4_general_ci)
            )
            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;
        $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(' ', p.first_name, p.last_name)) AS contact_name,
                p.customer_code,
                p.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,
                pr.sepidar_registered_at,
                pr.registered_by
            FROM transactions t
            JOIN deals d ON d.id = t.deal_id
            LEFT JOIN persons p ON (
                (d.person_id IS NOT NULL AND d.person_id = p.id)
                OR
                (d.person_id IS NULL AND d.contact_didar_id = p.didar_contact_id COLLATE utf8mb4_general_ci)
            )
            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;
    }
}
