<?php

namespace App\Repositories\Mypage;

use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Support\Facades\DB;

final class UsageRepository
{

    /**
     * 상품별 핀 발행(확인) 방법 조회
     * - gc_products.pin_check_methods: JSON 배열
     * - gc_products.is_buyback_allowed: BUYBACK 안전장치
     */
    public function getProductsIssueOptions(array $productIds): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $productIds), fn($n) => $n > 0)));
        if (empty($ids)) return [];

        $rows = DB::table('gc_products')
            ->select(['id', 'pin_check_methods', 'is_buyback_allowed'])
            ->whereIn('id', $ids)
            ->get();

        $out = [];
        foreach ($rows as $r) {
            $raw = $r->pin_check_methods ?? null;
            $hasPinCheckMethods = !($raw === null || trim((string)$raw) === '');

            $out[(int)$r->id] = [
                'pin_check_methods' => $this->decodeJsonArray($raw),
                'has_pin_check_methods' => $hasPinCheckMethods,
                'is_buyback_allowed' => (bool)($r->is_buyback_allowed ?? false),
            ];
        }
        return $out;
    }

    private function decodeJsonArray($json): array
    {
        if (is_array($json)) return $json;

        $json = trim((string)$json);
        if ($json === '') return [];

        $decoded = json_decode($json, true);
        return is_array($decoded) ? $decoded : [];
    }

    public function findAttemptWithOrder(int $attemptId): ?object
    {
        return DB::table('gc_payment_attempts as a')
            ->leftJoin('gc_pin_order as o', 'o.id', '=', 'a.order_id')
            ->select([
                'a.id as attempt_id',
                'a.provider as attempt_provider',
                'a.oid as attempt_oid',
                'a.mem_no as attempt_mem_no',
                'a.order_id as attempt_order_id',
                'a.pay_method as attempt_pay_method',
                'a.status as attempt_status',
                'a.pg_tid as attempt_pg_tid',
                'a.return_code as attempt_return_code',
                'a.return_msg as attempt_return_msg',
                'a.request_payload as attempt_request_payload',
                'a.response_payload as attempt_response_payload',
                'a.return_payload as attempt_return_payload',
                'a.noti_payload as attempt_noti_payload',
                'a.created_at as attempt_created_at',
                'a.updated_at as attempt_updated_at',

                //  추가: cancel_status 필드들
                'a.cancel_status as attempt_cancel_status',
                'a.cancel_requested_at as attempt_cancel_requested_at',
                'a.cancel_done_at as attempt_cancel_done_at',
                'a.cancel_last_code as attempt_cancel_last_code',
                'a.cancel_last_msg as attempt_cancel_last_msg',

                'o.id as order_id',
                'o.oid as order_oid',
                'o.mem_no as order_mem_no',
                'o.stat_pay as order_stat_pay',
                'o.products_name as order_product_name',
                'o.products_id as order_product_id',
                'o.provider as order_provider',
                'o.pay_method as order_pay_method',
                'o.pg_tid as order_pg_tid',
                'o.ret_code as order_ret_code',
                'o.ret_msg as order_ret_msg',
                'o.subtotal_amount as order_subtotal_amount',
                'o.fee_amount as order_fee_amount',
                'o.pg_fee_amount as order_pg_fee_amount',
                'o.pay_money as order_pay_money',
                'o.pay_data as order_pay_data',
                'o.ret_data as order_ret_data',
                'o.created_at as order_created_at',
                'o.updated_at as order_updated_at',

                //  추가: order cancel_status 필드들
                'o.cancel_status as order_cancel_status',
                'o.cancel_requested_at as order_cancel_requested_at',
                'o.cancel_done_at as order_cancel_done_at',
                'o.cancel_last_code as order_cancel_last_code',
                'o.cancel_last_msg as order_cancel_last_msg',
                'o.cancel_reason as order_cancel_reason',
            ])
            ->where('a.id', $attemptId)
            ->first();
    }

    /**
     * 리스트: 검색/페이징
     */
    public function paginateAttemptsWithOrder(int $memNo, array $filters, int $perPage = 20): LengthAwarePaginator
    {
        $q = trim((string)($filters['q'] ?? ''));
        $method = trim((string)($filters['method'] ?? ''));
        $status = trim((string)($filters['status'] ?? ''));
        $from = trim((string)($filters['from'] ?? ''));
        $to   = trim((string)($filters['to'] ?? ''));

        // 주문아이템 집계
        $oiAgg = DB::table('gc_pin_order_items')
            ->selectRaw('order_id, SUM(qty) as total_qty, MIN(item_name) as first_item_name')
            ->groupBy('order_id');

        // 발행 집계
        $issueAgg = DB::table('gc_pin_issues')
            ->selectRaw('order_id, COUNT(*) as issued_count')
            ->groupBy('order_id');

        $qb = DB::table('gc_payment_attempts as a')
            ->leftJoin('gc_pin_order as o', 'o.id', '=', 'a.order_id')
            ->leftJoinSub($oiAgg, 'oi', 'oi.order_id', '=', 'o.id')
            ->leftJoinSub($issueAgg, 'gi', 'gi.order_id', '=', 'o.id')
            ->where('a.mem_no', $memNo)

            // 리스트 기본 노출 대상
            ->where(function ($s) {
                // 1) 취소완료
                $s->where(function ($x) {
                    $x->where('a.cancel_status', 'success')
                        ->orWhere('o.cancel_status', 'success');
                })

                    // 2) 결제완료
                    ->orWhere(function ($x) {
                        $x->where('a.status', 'paid')
                            ->orWhere('o.stat_pay', 'p');
                    })

                    // 3) 입금대기(가상계좌)
                    ->orWhere(function ($x) {
                        $x->where('a.status', 'issued')
                            ->orWhere('o.stat_pay', 'w');
                    });
            })

            ->select([
                'a.id as attempt_id',
                'o.id as order_id',
                'o.oid as order_oid',
                DB::raw("COALESCE(o.products_name, oi.first_item_name) as product_name"),
                'oi.first_item_name as item_name',
                DB::raw("COALESCE(oi.total_qty, 0) as total_qty"),
                'a.pay_method as pay_method',
                'o.pay_money as pay_money',
                'a.status as attempt_status',
                'o.stat_pay as order_stat_pay',
                'a.cancel_status as attempt_cancel_status',
                'o.cancel_status as order_cancel_status',
                DB::raw("COALESCE(gi.issued_count, 0) as issued_count"),
                'a.created_at as created_at',
            ])
            ->orderByDesc('a.id');

        // 주문번호 검색
        if ($q !== '') {
            $qb->where(function ($w) use ($q) {
                $w->where('o.oid', $q)
                    ->orWhere('a.oid', $q);
            });
        }

        // 결제수단
        if ($method !== '') {
            $qb->where('a.pay_method', $method);
        }

        /**
         * 상태 필터는 화면 의미 기준
         *
         * pay         : 입금/결제 진행 상태
         * issue_wait  : 결제완료 + 발행대기
         * issue_done  : 결제완료 + 발행완료
         * cancelled   : 결제취소
         * failed      : 결제실패
         *
         * 하위호환:
         * paid        : issue_wait + issue_done
         * issued      : pay
         * cancel      : cancelled
         * canceled    : cancelled
         */
        if ($status !== '') {
            if (in_array($status, ['cancel', 'cancelled', 'canceled'], true)) {
                $qb->where(function ($x) {
                    $x->where('a.cancel_status', 'success')
                        ->orWhere('o.cancel_status', 'success');
                });
            } elseif ($status === 'issue_done') {
                $qb->where(function ($x) {
                    $x->where('a.status', 'paid')
                        ->orWhere('o.stat_pay', 'p');
                });
                $qb->whereRaw('COALESCE(gi.issued_count, 0) > 0');
            } elseif ($status === 'issue_wait') {
                $qb->where(function ($x) {
                    $x->where('a.status', 'paid')
                        ->orWhere('o.stat_pay', 'p');
                });
                $qb->whereRaw('COALESCE(gi.issued_count, 0) = 0');
            } elseif ($status === 'pay' || $status === 'issued') {
                $qb->where(function ($x) {
                    $x->where('a.status', 'issued')
                        ->orWhere('o.stat_pay', 'w')
                        ->orWhere('a.status', 'ready')
                        ->orWhere('a.status', 'redirected');
                });
            } elseif ($status === 'paid') {
                // 하위호환: 결제완료 전체(발행대기+발행완료)
                $qb->where(function ($x) {
                    $x->where('a.status', 'paid')
                        ->orWhere('o.stat_pay', 'p');
                });
            } elseif ($status === 'failed') {
                $qb->where(function ($x) {
                    $x->where('a.status', 'failed')
                        ->orWhere('o.stat_pay', 'f');
                });
            } else {
                $qb->where('a.status', $status);
            }
        }

        // 날짜 필터
        if ($from !== '') {
            $qb->whereDate('a.created_at', '>=', $from);
        }
        if ($to !== '') {
            $qb->whereDate('a.created_at', '<=', $to);
        }

        return $qb->paginate($perPage)->appends($filters);
    }

    public function getOrderItems(int $orderId)
    {
        return DB::table('gc_pin_order_items')
            ->where('order_id', $orderId)
            ->orderBy('id', 'asc')
            ->get();
    }

    public function countAssignedPins(int $orderId): int
    {
        return (int) DB::table('gc_pins')
            ->where('order_id', $orderId)
            ->count();
    }

    public function getAssignedPinsStatusSummary(int $orderId): array
    {
        $rows = DB::table('gc_pins')
            ->selectRaw('status, COUNT(*) as cnt')
            ->where('order_id', $orderId)
            ->groupBy('status')
            ->get();

        $out = [];
        foreach ($rows as $r) $out[(string)$r->status] = (int)$r->cnt;
        return $out;
    }

    /**
     * 핀 목록(오픈 전/후 표시용) — 핀 반납(홀드 해제)은 이번 범위 제외
     */
    public function getPinsForOrder(int $orderId): array
    {
        $rows = DB::table('gc_pins')
            ->where('order_id', $orderId)
            ->orderBy('id', 'asc')
            ->get();

        return array_map(fn($r) => (array)$r, $rows->all());
    }

    /**
     * 취소 로그 조회
     */
    public function getCancelLogsForAttempt(int $attemptId, int $limit = 20): array
    {
        $rows = DB::table('gc_payment_cancel_logs')
            ->where('attempt_id', $attemptId)
            ->orderByDesc('id')
            ->limit($limit)
            ->get();

        return array_map(fn($r) => (array)$r, $rows->all());
    }

    public function getIssuesForOrder(int $orderId): array
    {
        $rows = DB::table('gc_pin_issues')
            ->where('order_id', $orderId)
            ->orderBy('id', 'asc')
            ->get();

        return array_map(function ($r) {
            $arr = (array)$r;
            $arr['pins_json_decoded'] = $this->decodeJsonArray($arr['pins_json'] ?? null);
            $arr['issue_logs_json_decoded'] = $this->decodeJsonArray($arr['issue_logs_json'] ?? null);
            return $arr;
        }, $rows->all());
    }

    public function getIssuesForOrderForUpdate(int $orderId): array
    {
        $rows = DB::table('gc_pin_issues')
            ->where('order_id', $orderId)
            ->orderBy('id', 'asc')
            ->lockForUpdate()
            ->get();

        return array_map(fn ($r) => (array)$r, $rows->all());
    }

    /**
     * 핀번호 확인 / SMS / 출금완료 등 강한 잠금
     */
    public function hasLockedOrOpenedIssues(int $orderId): bool
    {
        return DB::table('gc_pin_issues')
            ->where('order_id', $orderId)
            ->where(function ($q) {
                $q->whereNotNull('opened_at')
                    ->orWhereNotNull('sms_sent_at')
                    ->orWhereNotNull('payout_done_at')
                    ->orWhere('cancel_status', 'LOCKED');
            })
            ->exists();
    }


    public function hasAnyIssuedIssues(int $orderId): bool
    {
        return DB::table('gc_pin_issues')
            ->where('order_id', $orderId)
            ->where(function ($q) {
                $q->whereIn('issue_status', ['PROCESSING', 'ISSUED'])
                    ->orWhere('pin_count', '>', 0)
                    ->orWhereNotNull('issued_at');
            })
            ->exists();
    }

    public function markIssueOpened(int $issueId, string $openedAt, array $logs, ?string $reason = null): void
    {
        DB::table('gc_pin_issues')
            ->where('id', $issueId)
            ->update([
                'opened_at' => $openedAt,
                'cancel_status' => 'LOCKED',
                'cancel_locked_reason' => $reason ?? '핀번호 확인 완료',
                'issue_logs_json' => json_encode($logs, JSON_UNESCAPED_UNICODE),
                'updated_at' => $openedAt,
            ]);
    }

    public function getSkuForOrderItem(int $productId, int $skuId): ?object
    {
        return DB::table('gc_product_skus')
            ->where('product_id', $productId)
            ->where('id', $skuId)
            ->where('is_active', 1)
            ->first();
    }
}