<?php

namespace App\Repositories\Admin\Member;

use Illuminate\Support\Facades\DB;

final class AdminMemberMarketingRepository
{
    public function getAsOfDate(): ?string
    {
        $row = DB::table('mem_marketing_stats')->selectRaw('MAX(as_of_date) as d')->first();
        return $row?->d ? (string)$row->d : null;
    }

    public function countMatches(array $filters): int
    {
        return (int) $this->baseQuery($filters)->count();
    }

    public function fetchPreview(array $filters, int $limit = 10): array
    {
        return $this->baseQuery($filters)
            ->orderByDesc('ms.mem_no')
            ->limit($limit)
            ->get($this->selectCols())
            ->map(fn($r)=>(array)$r)
            ->all();
    }

    public function chunkExport(array $filters, int $chunkSize, callable $cb): void
    {
        $q = $this->baseQuery($filters)->orderByDesc('ms.mem_no');

        $last = null;
        while (true) {
            $qq = clone $q;
            if ($last !== null) $qq->where('ms.mem_no', '<', $last);

            $rows = $qq->limit($chunkSize)->get($this->selectCols());
            if ($rows->isEmpty()) break;

            $arr = $rows->map(fn($r)=>(array)$r)->all();
            $cb($arr);

            $last = (int) end($arr)['mem_no'];
        }
    }

    private function selectCols(): array
    {
        return [
            'ms.mem_no',
            'ms.as_of_date',

            'ms.stat_3',
            'ms.reg_at',
            'ms.last_login_at',
            'ms.has_login',
            'ms.days_since_reg',
            'ms.days_since_login',

            'ms.has_email',
            'ms.has_phone',
            'ms.optin_email',
            'ms.optin_sms',
            'ms.optin_push',

            'ms.first_purchase_at',
            'ms.last_purchase_at',
            'ms.purchase_count_total',
            'ms.purchase_amount_total',
            'ms.purchase_count_30d',
            'ms.purchase_amount_30d',
            'ms.purchase_count_90d',
            'ms.purchase_amount_90d',

            // mem_info (다운로드/표시용)
            'mi.name',
            'mi.email',
            'mi.cell_phone',
        ];
    }

    private function baseQuery(array $filters)
    {
        $q = DB::table('mem_marketing_stats as ms')
            ->join('mem_info as mi', 'mi.mem_no', '=', 'ms.mem_no');

        // ✅ 스냅샷(기준일) 고정: 기본은 최신
        $asOf = trim((string)($filters['as_of_date'] ?? ''));
        if ($asOf === '') $asOf = $this->getAsOfDate() ?: '';
        if ($asOf !== '') $q->where('ms.as_of_date', $asOf);

        // -----------------------------
        // 키워드 검색
        // -----------------------------
        $qf = (string)($filters['qf'] ?? 'all');   // all|mem_no|name|email|phone
        $keyword = trim((string)($filters['q'] ?? ''));
        $phoneEnc = (string)($filters['phone_enc'] ?? '');

        $digits = preg_replace('/\D+/', '', $keyword) ?? '';
        $isPhoneDigits = (bool) preg_match('/^\d{10,11}$/', $digits);

        if ($keyword !== '') {
            switch ($qf) {
                case 'mem_no':
                    if ($digits !== '' && ctype_digit($digits)) {
                        $q->where('ms.mem_no', (int)$digits);
                    } else {
                        $q->whereRaw('1=0');
                    }
                    break;

                case 'name':
                    $q->where('mi.name', 'like', "%{$keyword}%");
                    break;

                case 'email':
                    $q->where('mi.email', 'like', "%{$keyword}%");
                    break;

                case 'phone':
                    if ($isPhoneDigits || $phoneEnc !== '') {
                        $q->where(function ($w) use ($digits, $phoneEnc, $isPhoneDigits) {
                            // mem_info.cell_phone 이 평문/암호문 섞여 있을 수 있어 둘 다 비교
                            if ($isPhoneDigits) $w->orWhere('mi.cell_phone', $digits);
                            if ($phoneEnc !== '') $w->orWhere('mi.cell_phone', $phoneEnc);
                        });
                    } else {
                        $q->whereRaw('1=0');
                    }
                    break;

                default: // all
                    $q->where(function ($w) use ($keyword, $digits, $isPhoneDigits, $phoneEnc) {
                        if ($digits !== '' && ctype_digit($digits)) {
                            $w->orWhere('ms.mem_no', (int)$digits);
                        }

                        $w->orWhere('mi.name', 'like', "%{$keyword}%")
                            ->orWhere('mi.email', 'like', "%{$keyword}%");

                        if ($isPhoneDigits) $w->orWhere('mi.cell_phone', $digits);
                        if ($phoneEnc !== '') $w->orWhere('mi.cell_phone', $phoneEnc);
                    });
                    break;
            }
        }

        // -----------------------------
        // stat_3
        // -----------------------------
        $stat3 = trim((string)($filters['stat_3'] ?? ''));
        if ($stat3 !== '') {
            $q->where('ms.stat_3', (int)$stat3);
        }

        // -----------------------------
        // 가입일 범위 (달력)
        // -----------------------------
        $regFrom = trim((string)($filters['reg_from'] ?? ''));
        if ($regFrom !== '') {
            $q->where('ms.reg_at', '>=', $regFrom.' 00:00:00');
        }

        $regTo = trim((string)($filters['reg_to'] ?? ''));
        if ($regTo !== '') {
            $q->where('ms.reg_at', '<=', $regTo.' 23:59:59');
        }

        // -----------------------------
        // 로그인 기록 없음 / 미접속일수
        // -----------------------------
        $noLogin = (string)($filters['no_login'] ?? '0');
        if ($noLogin === '1') {
            $q->where('ms.has_login', 0);
        }

        $inactive = $filters['inactive_days_gte'] ?? null;
        if ($inactive !== null && $inactive !== '') {
            $n = max(0, (int)$inactive);

            $q->where(function ($w) use ($n) {
                // 1) 로그인 기록 있는 회원: 마지막 로그인 기준 미접속
                $w->where('ms.days_since_login', '>=', $n)

                    // 2) 로그인 기록 없는 회원: 가입일 기준으로 "미접속" 취급
                    ->orWhere(function ($w2) use ($n) {
                        $w2->where('ms.has_login', 0)
                            ->where('ms.days_since_reg', '>=', $n);
                    });
            });
        }

        $recentLogin = $filters['recent_login_days_lte'] ?? null;
        if ($recentLogin !== null && $recentLogin !== '') {
            $n = max(0, (int)$recentLogin);

            $q->where('ms.has_login', 1)
                ->whereNotNull('ms.days_since_login')
                ->where('ms.days_since_login', '<=', $n);
        }

        // -----------------------------
        // 수신동의 / 정보유무
        // -----------------------------
        $optSms = (string)($filters['optin_sms'] ?? 'all');
        if ($optSms === '1') $q->where('ms.optin_sms', 1);
        if ($optSms === '0') $q->where('ms.optin_sms', 0);

        $optEmail = (string)($filters['optin_email'] ?? 'all');
        if ($optEmail === '1') $q->where('ms.optin_email', 1);
        if ($optEmail === '0') $q->where('ms.optin_email', 0);

        $hasPhone = (string)($filters['has_phone'] ?? 'all');
        if ($hasPhone === '1') $q->where('ms.has_phone', 1);
        if ($hasPhone === '0') $q->where('ms.has_phone', 0);

        $hasEmail = (string)($filters['has_email'] ?? 'all');
        if ($hasEmail === '1') $q->where('ms.has_email', 1);
        if ($hasEmail === '0') $q->where('ms.has_email', 0);

        // -----------------------------
        // 구매조건
        // -----------------------------
        $hasPurchase = (string)($filters['has_purchase'] ?? 'all');
        if ($hasPurchase === '1') $q->where('ms.purchase_count_total', '>', 0);
        if ($hasPurchase === '0') $q->where('ms.purchase_count_total', '=', 0);

        $recent = (string)($filters['recent_purchase'] ?? 'all');
        if ($recent === '30') $q->where('ms.purchase_count_30d', '>', 0);
        if ($recent === '90') $q->where('ms.purchase_count_90d', '>', 0);

        $minCnt = $filters['min_purchase_count'] ?? null;
        if ($minCnt !== null && $minCnt !== '') $q->where('ms.purchase_count_total', '>=', (int)$minCnt);

        $minAmt = $filters['min_purchase_amount'] ?? null;
        if ($minAmt !== null && $minAmt !== '') $q->where('ms.purchase_amount_total', '>=', (int)$minAmt);

        return $q;
    }
}
