sam-api/app/Services/EntertainmentService.php

<?php

namespace App\Services;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

/**
 * 접대비 현황 서비스 (D1.7 리스크 감지형)
 *
 * CEO 대시보드용 접대비 리스크 데이터를 제공합니다.
 * 카드 4개: 주말/심야, 기피업종, 고액결제, 증빙미비
 */
class EntertainmentService extends Service
{
    // 고액 결제 기준 (1회 50만원 초과)
    private const HIGH_AMOUNT_THRESHOLD = 500000;

    // 기피업종 MCC 코드 (유흥, 귀금속, 숙박 등)
    private const PROHIBITED_MCC_CODES = [
        '5813', // 음주업소
        '7011', // 숙박업
        '5944', // 귀금속
        '7941', // 레저/스포츠
        '7992', // 골프장
        '7273', // 데이트서비스
        '5932', // 골동품
    ];

    // 심야 시간대 (22시 ~ 06시)
    private const LATE_NIGHT_START = 22;

    private const LATE_NIGHT_END = 6;

    /**
     * 접대비 리스크 현황 요약 조회 (D1.7)
     *
     * @return array{cards: array, check_points: array}
     */
    public function getSummary(
        ?string $limitType = 'quarterly',
        ?string $companyType = 'medium',
        ?int $year = null,
        ?int $quarter = null
    ): array {
        $tenantId = $this->tenantId();
        $now = Carbon::now();

        $year = $year ?? $now->year;
        $quarter = $quarter ?? $now->quarter;

        // 기간 범위 계산
        if ($limitType === 'annual') {
            $startDate = Carbon::create($year, 1, 1)->format('Y-m-d');
            $endDate = Carbon::create($year, 12, 31)->format('Y-m-d');
        } else {
            $startDate = Carbon::create($year, ($quarter - 1) * 3 + 1, 1)->format('Y-m-d');
            $endDate = Carbon::create($year, $quarter * 3, 1)->endOfMonth()->format('Y-m-d');
        }

        // 리스크 감지 쿼리
        $weekendLateNight = $this->getWeekendLateNightRisk($tenantId, $startDate, $endDate);
        $prohibitedBiz = $this->getProhibitedBizTypeRisk($tenantId, $startDate, $endDate);
        $highAmount = $this->getHighAmountRisk($tenantId, $startDate, $endDate);
        $missingReceipt = $this->getMissingReceiptRisk($tenantId, $startDate, $endDate);

        // 카드 데이터 구성
        $cards = [
            [
                'id' => 'et_weekend',
                'label' => '주말/심야',
                'amount' => (int) $weekendLateNight['total'],
                'subLabel' => "{$weekendLateNight['count']}건",
            ],
            [
                'id' => 'et_prohibited',
                'label' => '기피업종',
                'amount' => (int) $prohibitedBiz['total'],
                'subLabel' => $prohibitedBiz['count'] > 0 ? "불인정 {$prohibitedBiz['count']}건" : '0건',
            ],
            [
                'id' => 'et_high_amount',
                'label' => '고액 결제',
                'amount' => (int) $highAmount['total'],
                'subLabel' => "{$highAmount['count']}건",
            ],
            [
                'id' => 'et_no_receipt',
                'label' => '증빙 미비',
                'amount' => (int) $missingReceipt['total'],
                'subLabel' => "{$missingReceipt['count']}건",
            ],
        ];

        // 체크포인트 생성
        $checkPoints = $this->generateRiskCheckPoints(
            $weekendLateNight,
            $prohibitedBiz,
            $highAmount,
            $missingReceipt
        );

        return [
            'cards' => $cards,
            'check_points' => $checkPoints,
        ];
    }

    /**
     * 주말/심야 사용 리스크 조회
     * expense_date가 주말(토/일) OR barobill join으로 use_time 22~06시
     */
    private function getWeekendLateNightRisk(int $tenantId, string $startDate, string $endDate): array
    {
        // 주말 사용 (토요일=7, 일요일=1 in MySQL DAYOFWEEK)
        $weekendResult = DB::table('expense_accounts')
            ->where('tenant_id', $tenantId)
            ->where('account_type', 'entertainment')
            ->whereBetween('expense_date', [$startDate, $endDate])
            ->whereNull('deleted_at')
            ->whereRaw('DAYOFWEEK(expense_date) IN (1, 7)')
            ->selectRaw('COUNT(*) as count, COALESCE(SUM(amount), 0) as total')
            ->first();

        // 심야 사용 (barobill 카드 거래 내역에서 시간 확인)
        $lateNightResult = DB::table('expense_accounts as ea')
            ->leftJoin('barobill_card_transactions as bct', function ($join) {
                $join->on('ea.receipt_no', '=', 'bct.approval_num')
                    ->on('ea.tenant_id', '=', 'bct.tenant_id');
            })
            ->where('ea.tenant_id', $tenantId)
            ->where('ea.account_type', 'entertainment')
            ->whereBetween('ea.expense_date', [$startDate, $endDate])
            ->whereNull('ea.deleted_at')
            ->whereRaw('DAYOFWEEK(ea.expense_date) NOT IN (1, 7)') // 주말 제외 (중복 방지)
            ->whereNotNull('bct.use_time')
            ->where(function ($q) {
                $q->whereRaw('CAST(SUBSTRING(bct.use_time, 1, 2) AS UNSIGNED) >= ?', [self::LATE_NIGHT_START])
                    ->orWhereRaw('CAST(SUBSTRING(bct.use_time, 1, 2) AS UNSIGNED) < ?', [self::LATE_NIGHT_END]);
            })
            ->selectRaw('COUNT(*) as count, COALESCE(SUM(ea.amount), 0) as total')
            ->first();

        $totalCount = ($weekendResult->count ?? 0) + ($lateNightResult->count ?? 0);
        $totalAmount = ($weekendResult->total ?? 0) + ($lateNightResult->total ?? 0);

        return ['count' => $totalCount, 'total' => $totalAmount];
    }

    /**
     * 기피업종 사용 리스크 조회
     * barobill의 merchant_biz_type가 MCC 코드 매칭
     */
    private function getProhibitedBizTypeRisk(int $tenantId, string $startDate, string $endDate): array
    {
        $result = DB::table('expense_accounts as ea')
            ->join('barobill_card_transactions as bct', function ($join) {
                $join->on('ea.receipt_no', '=', 'bct.approval_num')
                    ->on('ea.tenant_id', '=', 'bct.tenant_id');
            })
            ->where('ea.tenant_id', $tenantId)
            ->where('ea.account_type', 'entertainment')
            ->whereBetween('ea.expense_date', [$startDate, $endDate])
            ->whereNull('ea.deleted_at')
            ->whereIn('bct.merchant_biz_type', self::PROHIBITED_MCC_CODES)
            ->selectRaw('COUNT(*) as count, COALESCE(SUM(ea.amount), 0) as total')
            ->first();

        return [
            'count' => $result->count ?? 0,
            'total' => $result->total ?? 0,
        ];
    }

    /**
     * 고액 결제 리스크 조회
     * 1회 50만원 초과 결제
     */
    private function getHighAmountRisk(int $tenantId, string $startDate, string $endDate): array
    {
        $result = DB::table('expense_accounts')
            ->where('tenant_id', $tenantId)
            ->where('account_type', 'entertainment')
            ->whereBetween('expense_date', [$startDate, $endDate])
            ->whereNull('deleted_at')
            ->where('amount', '>', self::HIGH_AMOUNT_THRESHOLD)
            ->selectRaw('COUNT(*) as count, COALESCE(SUM(amount), 0) as total')
            ->first();

        return [
            'count' => $result->count ?? 0,
            'total' => $result->total ?? 0,
        ];
    }

    /**
     * 증빙 미비 리스크 조회
     * receipt_no가 NULL 또는 빈 값
     */
    private function getMissingReceiptRisk(int $tenantId, string $startDate, string $endDate): array
    {
        $result = DB::table('expense_accounts')
            ->where('tenant_id', $tenantId)
            ->where('account_type', 'entertainment')
            ->whereBetween('expense_date', [$startDate, $endDate])
            ->whereNull('deleted_at')
            ->where(function ($q) {
                $q->whereNull('receipt_no')
                    ->orWhere('receipt_no', '');
            })
            ->selectRaw('COUNT(*) as count, COALESCE(SUM(amount), 0) as total')
            ->first();

        return [
            'count' => $result->count ?? 0,
            'total' => $result->total ?? 0,
        ];
    }

    /**
     * 접대비 상세 정보 조회 (모달용)
     *
     * @param  string|null  $companyType  법인 유형 (large|medium|small, 기본: medium)
     * @param  int|null  $year  연도 (기본: 현재 연도)
     * @param  int|null  $quarter  분기 (1-4, 기본: 현재 분기)
     */
    public function getDetail(
        ?string $companyType = 'medium',
        ?int $year = null,
        ?int $quarter = null,
        ?string $startDate = null,
        ?string $endDate = null
    ): array {
        $tenantId = $this->tenantId();
        $now = Carbon::now();

        $year = $year ?? $now->year;
        $companyType = $companyType ?? 'medium';
        $quarter = $quarter ?? $now->quarter;

        // 연간 기간 범위 (summary, calculation, quarterly, monthly_usage용 - 항상 연간)
        $annualStartDate = Carbon::create($year, 1, 1)->format('Y-m-d');
        $annualEndDate = Carbon::create($year, 12, 31)->format('Y-m-d');

        // 거래/리스크 필터 기간 (start_date/end_date 전달 시 사용, 없으면 분기 기본)
        if ($startDate && $endDate) {
            $filterStartDate = $startDate;
            $filterEndDate = $endDate;
        } else {
            $filterStartDate = Carbon::create($year, ($quarter - 1) * 3 + 1, 1)->format('Y-m-d');
            $filterEndDate = Carbon::create($year, $quarter * 3, 1)->endOfMonth()->format('Y-m-d');
        }

        // 기본한도 계산 (중소기업: 3,600만, 일반법인: 1,200만)
        $baseLimit = $companyType === 'large' ? 12000000 : 36000000;

        // 수입금액 조회 (sales 테이블)
        $revenue = $this->getAnnualRevenue($tenantId, $year);

        // 수입금액별 추가한도 계산
        $revenueAdditional = $this->calculateRevenueAdditionalLimit($revenue);

        // 연간 총 한도
        $annualLimit = $baseLimit + $revenueAdditional;
        $quarterlyLimit = $annualLimit / 4;

        // 연간/분기 사용액 조회
        $annualUsed = $this->getUsedAmount($tenantId, $annualStartDate, $annualEndDate);
        $quarterlyUsed = $this->getUsedAmount($tenantId, $filterStartDate, $filterEndDate);

        // 잔여/초과 계산
        $annualRemaining = max(0, $annualLimit - $annualUsed);
        $annualExceeded = max(0, $annualUsed - $annualLimit);

        // 1. 요약 데이터
        $summary = [
            'annual_limit' => (int) $annualLimit,
            'annual_remaining' => (int) $annualRemaining,
            'annual_used' => (int) $annualUsed,
            'annual_exceeded' => (int) $annualExceeded,
        ];

        // 2. 리스크 검토 카드 (날짜 필터 적용)
        $weekendLateNight = $this->getWeekendLateNightRisk($tenantId, $filterStartDate, $filterEndDate);
        $prohibitedBiz = $this->getProhibitedBizTypeRisk($tenantId, $filterStartDate, $filterEndDate);
        $highAmount = $this->getHighAmountRisk($tenantId, $filterStartDate, $filterEndDate);
        $missingReceipt = $this->getMissingReceiptRisk($tenantId, $filterStartDate, $filterEndDate);

        $riskReview = [
            ['label' => '주말/심야', 'amount' => (int) $weekendLateNight['total'], 'count' => $weekendLateNight['count']],
            ['label' => '기피업종', 'amount' => (int) $prohibitedBiz['total'], 'count' => $prohibitedBiz['count']],
            ['label' => '고액 결제', 'amount' => (int) $highAmount['total'], 'count' => $highAmount['count']],
            ['label' => '증빙 미비', 'amount' => (int) $missingReceipt['total'], 'count' => $missingReceipt['count']],
        ];

        // 3. 월별 사용 추이
        $monthlyUsage = $this->getMonthlyUsageTrend($tenantId, $year);

        // 4. 사용자별 분포 (날짜 필터 적용)
        $userDistribution = $this->getUserDistribution($tenantId, $filterStartDate, $filterEndDate);

        // 5. 거래 내역 (날짜 필터 적용)
        $transactions = $this->getTransactions($tenantId, $filterStartDate, $filterEndDate);

        // 6. 손금한도 계산 정보
        $calculation = [
            'company_type' => $companyType,
            'base_limit' => (int) $baseLimit,
            'revenue' => (int) $revenue,
            'revenue_additional' => (int) $revenueAdditional,
            'annual_limit' => (int) $annualLimit,
        ];

        // 7. 분기별 현황
        $quarterly = $this->getQuarterlyStatus($tenantId, $year, $quarterlyLimit);

        return [
            'summary' => $summary,
            'risk_review' => $riskReview,
            'monthly_usage' => $monthlyUsage,
            'user_distribution' => $userDistribution,
            'transactions' => $transactions,
            'calculation' => $calculation,
            'quarterly' => $quarterly,
        ];
    }

    /**
     * 접대비 사용액 조회
     */
    private function getUsedAmount(int $tenantId, string $startDate, string $endDate): float
    {
        return DB::table('expense_accounts')
            ->where('tenant_id', $tenantId)
            ->where('account_type', 'entertainment')
            ->whereBetween('expense_date', [$startDate, $endDate])
            ->whereNull('deleted_at')
            ->sum('amount') ?: 0;
    }

    /**
     * 연간 수입금액(매출) 조회
     */
    private function getAnnualRevenue(int $tenantId, int $year): float
    {
        return DB::table('sales')
            ->where('tenant_id', $tenantId)
            ->whereYear('sale_date', $year)
            ->whereNull('deleted_at')
            ->sum('total_amount') ?: 0;
    }

    /**
     * 수입금액별 추가한도 계산 (세법 기준)
     * 100억 이하: 수입금액 × 0.2%
     * 100억 초과 ~ 500억 이하: 2,000만 + (수입금액 - 100억) × 0.1%
     * 500억 초과: 6,000만 + (수입금액 - 500억) × 0.03%
     */
    private function calculateRevenueAdditionalLimit(float $revenue): float
    {
        $b10 = 10000000000; // 100억
        $b50 = 50000000000; // 500억

        if ($revenue <= $b10) {
            return $revenue * 0.002;
        } elseif ($revenue <= $b50) {
            return 20000000 + ($revenue - $b10) * 0.001;
        } else {
            return 60000000 + ($revenue - $b50) * 0.0003;
        }
    }

    /**
     * 월별 사용 추이 조회
     */
    private function getMonthlyUsageTrend(int $tenantId, int $year): array
    {
        $monthlyData = DB::table('expense_accounts')
            ->select(DB::raw('MONTH(expense_date) as month'), DB::raw('SUM(amount) as amount'))
            ->where('tenant_id', $tenantId)
            ->where('account_type', 'entertainment')
            ->whereYear('expense_date', $year)
            ->whereNull('deleted_at')
            ->groupBy(DB::raw('MONTH(expense_date)'))
            ->orderBy('month')
            ->get();

        $result = [];
        for ($i = 1; $i <= 12; $i++) {
            $found = $monthlyData->firstWhere('month', $i);
            $result[] = [
                'month' => $i,
                'label' => $i . '월',
                'amount' => $found ? (int) $found->amount : 0,
            ];
        }

        return $result;
    }

    /**
     * 사용자별 분포 조회
     */
    private function getUserDistribution(int $tenantId, string $startDate, string $endDate): array
    {
        $colors = ['#60A5FA', '#34D399', '#FBBF24', '#F87171', '#A78BFA', '#FB923C'];

        $distribution = DB::table('expense_accounts as ea')
            ->leftJoin('users as u', 'ea.created_by', '=', 'u.id')
            ->select('u.name as user_name', DB::raw('SUM(ea.amount) as amount'))
            ->where('ea.tenant_id', $tenantId)
            ->where('ea.account_type', 'entertainment')
            ->whereBetween('ea.expense_date', [$startDate, $endDate])
            ->whereNull('ea.deleted_at')
            ->groupBy('ea.created_by', 'u.name')
            ->orderByDesc('amount')
            ->limit(5)
            ->get();

        $total = $distribution->sum('amount');
        $result = [];
        $idx = 0;

        foreach ($distribution as $item) {
            $result[] = [
                'user_name' => $item->user_name ?? '사용자',
                'amount' => (int) $item->amount,
                'percentage' => $total > 0 ? round(($item->amount / $total) * 100, 1) : 0,
                'color' => $colors[$idx % count($colors)],
            ];
            $idx++;
        }

        return $result;
    }

    /**
     * 거래 내역 조회
     */
    private function getTransactions(int $tenantId, string $startDate, string $endDate): array
    {
        $transactions = DB::table('expense_accounts as ea')
            ->leftJoin('users as u', 'ea.created_by', '=', 'u.id')
            ->leftJoin('barobill_card_transactions as bct', function ($join) {
                $join->on('ea.receipt_no', '=', 'bct.approval_num')
                    ->on('ea.tenant_id', '=', 'bct.tenant_id');
            })
            ->select([
                'ea.id',
                'ea.card_no',
                'u.name as user_name',
                'ea.expense_date',
                'ea.vendor_name',
                'ea.amount',
                'ea.receipt_no',
                'bct.use_time',
                'bct.merchant_biz_type',
            ])
            ->where('ea.tenant_id', $tenantId)
            ->where('ea.account_type', 'entertainment')
            ->whereBetween('ea.expense_date', [$startDate, $endDate])
            ->whereNull('ea.deleted_at')
            ->orderByDesc('ea.expense_date')
            ->limit(100)
            ->get();

        $result = [];
        foreach ($transactions as $t) {
            $riskType = $this->detectTransactionRiskType($t);

            $result[] = [
                'id' => $t->id,
                'card_name' => $t->card_no ? '카드 *' . substr($t->card_no, -4) : '카드명',
                'user_name' => $t->user_name ?? '사용자',
                'expense_date' => Carbon::parse($t->expense_date)->format('Y-m-d H:i'),
                'vendor_name' => $t->vendor_name ?? '가맹점명',
                'amount' => (int) $t->amount,
                'risk_type' => $riskType,
            ];
        }

        return $result;
    }

    /**
     * 거래 건별 리스크 유형 감지
     */
    private function detectTransactionRiskType(object $transaction): string
    {
        // 기피업종
        if ($transaction->merchant_biz_type && in_array($transaction->merchant_biz_type, self::PROHIBITED_MCC_CODES)) {
            return '기피업종';
        }

        // 고액 결제
        if ($transaction->amount > self::HIGH_AMOUNT_THRESHOLD) {
            return '고액 결제';
        }

        // 증빙 미비
        if (empty($transaction->receipt_no)) {
            return '증빙 미비';
        }

        // 주말/심야 감지
        $expenseDate = Carbon::parse($transaction->expense_date);
        if ($expenseDate->isWeekend()) {
            return '주말/심야';
        }
        if ($transaction->use_time) {
            $hour = (int) substr($transaction->use_time, 0, 2);
            if ($hour >= self::LATE_NIGHT_START || $hour < self::LATE_NIGHT_END) {
                return '주말/심야';
            }
        }

        return '정상';
    }

    /**
     * 분기별 현황 조회
     */
    private function getQuarterlyStatus(int $tenantId, int $year, float $quarterlyLimit): array
    {
        $result = [];
        $previousRemaining = 0;

        for ($q = 1; $q <= 4; $q++) {
            $startDate = Carbon::create($year, ($q - 1) * 3 + 1, 1)->format('Y-m-d');
            $endDate = Carbon::create($year, $q * 3, 1)->endOfMonth()->format('Y-m-d');

            $used = $this->getUsedAmount($tenantId, $startDate, $endDate);
            $carryover = $previousRemaining > 0 ? $previousRemaining : 0;
            $totalLimit = $quarterlyLimit + $carryover;
            $remaining = max(0, $totalLimit - $used);
            $exceeded = max(0, $used - $totalLimit);

            $result[] = [
                'quarter' => $q,
                'limit' => (int) $quarterlyLimit,
                'carryover' => (int) $carryover,
                'used' => (int) $used,
                'remaining' => (int) $remaining,
                'exceeded' => (int) $exceeded,
            ];

            $previousRemaining = $remaining;
        }

        return $result;
    }

    /**
     * 리스크 감지 체크포인트 생성
     */
    private function generateRiskCheckPoints(
        array $weekendLateNight,
        array $prohibitedBiz,
        array $highAmount,
        array $missingReceipt
    ): array {
        $checkPoints = [];
        $totalRiskCount = $weekendLateNight['count'] + $prohibitedBiz['count']
            + $highAmount['count'] + $missingReceipt['count'];

        // 주말/심야
        if ($weekendLateNight['count'] > 0) {
            $amountFormatted = number_format($weekendLateNight['total'] / 10000);
            $checkPoints[] = [
                'id' => 'et_cp_weekend',
                'type' => 'warning',
                'message' => "주말/심야 사용 {$weekendLateNight['count']}건({$amountFormatted}만원) 감지. 업무관련성 소명자료로 증빙해주세요.",
                'highlights' => [
                    ['text' => "{$weekendLateNight['count']}건({$amountFormatted}만원)", 'color' => 'red'],
                ],
            ];
        }

        // 기피업종
        if ($prohibitedBiz['count'] > 0) {
            $amountFormatted = number_format($prohibitedBiz['total'] / 10000);
            $checkPoints[] = [
                'id' => 'et_cp_prohibited',
                'type' => 'error',
                'message' => "기피업종 사용 {$prohibitedBiz['count']}건({$amountFormatted}만원) 감지. 유흥업종 결제는 접대비 불인정 사유입니다.",
                'highlights' => [
                    ['text' => "{$prohibitedBiz['count']}건({$amountFormatted}만원)", 'color' => 'red'],
                    ['text' => '접대비 불인정', 'color' => 'red'],
                ],
            ];
        }

        // 고액 결제
        if ($highAmount['count'] > 0) {
            $amountFormatted = number_format($highAmount['total'] / 10000);
            $checkPoints[] = [
                'id' => 'et_cp_high',
                'type' => 'warning',
                'message' => "고액 결제 {$highAmount['count']}건({$amountFormatted}만원) 감지. 1회 50만원 초과 결제입니다. 증빙이 필요합니다.",
                'highlights' => [
                    ['text' => "{$highAmount['count']}건({$amountFormatted}만원)", 'color' => 'red'],
                ],
            ];
        }

        // 증빙 미비
        if ($missingReceipt['count'] > 0) {
            $amountFormatted = number_format($missingReceipt['total'] / 10000);
            $checkPoints[] = [
                'id' => 'et_cp_receipt',
                'type' => 'error',
                'message' => "미증빙 {$missingReceipt['count']}건({$amountFormatted}만원) 감지. 증빙이 필요합니다.",
                'highlights' => [
                    ['text' => "{$missingReceipt['count']}건({$amountFormatted}만원)", 'color' => 'red'],
                ],
            ];
        }

        // 리스크 0건이면 정상 메시지
        if ($totalRiskCount === 0) {
            $checkPoints[] = [
                'id' => 'et_cp_normal',
                'type' => 'success',
                'message' => '접대비 사용 현황이 정상입니다.',
                'highlights' => [
                    ['text' => '정상', 'color' => 'green'],
                ],
            ];
        }

        return $checkPoints;
    }
}