sam-manage/app/Services/Quote/QuoteFormulaService.php

<?php

namespace App\Services\Quote;

use App\Models\Quote\QuoteFormula;
use App\Models\Quote\QuoteFormulaItem;
use App\Models\Quote\QuoteFormulaMapping;
use App\Models\Quote\QuoteFormulaRange;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class QuoteFormulaService
{
    /**
     * 수식 목록 조회 (페이지네이션)
     */
    public function getFormulas(array $filters = [], int $perPage = 15): LengthAwarePaginator
    {
        $tenantId = session('selected_tenant_id');

        $query = QuoteFormula::query()
            ->with(['category'])
            ->withCount(['ranges', 'mappings', 'items']);

        // 테넌트 필터
        if ($tenantId && $tenantId !== 'all') {
            $query->where('tenant_id', $tenantId);
        }

        // 카테고리 필터
        if (! empty($filters['category_id'])) {
            $query->where('category_id', $filters['category_id']);
        }

        // 제품 필터 (공통/특정)
        if (isset($filters['product_id'])) {
            if ($filters['product_id'] === 'common' || $filters['product_id'] === '') {
                $query->whereNull('product_id');
            } else {
                $query->where('product_id', $filters['product_id']);
            }
        }

        // 유형 필터
        if (! empty($filters['type'])) {
            $query->where('type', $filters['type']);
        }

        // 검색
        if (! empty($filters['search'])) {
            $search = $filters['search'];
            $query->where(function ($q) use ($search) {
                $q->where('name', 'like', "%{$search}%")
                    ->orWhere('variable', 'like', "%{$search}%")
                    ->orWhere('formula', 'like', "%{$search}%");
            });
        }

        // 활성화 필터
        if (isset($filters['is_active']) && $filters['is_active'] !== '') {
            $query->where('is_active', (bool) $filters['is_active']);
        }

        // 정렬
        $sortBy = $filters['sort_by'] ?? 'sort_order';
        $sortDirection = $filters['sort_direction'] ?? 'asc';

        if ($sortBy === 'category') {
            $query->join('quote_formula_categories', 'quote_formulas.category_id', '=', 'quote_formula_categories.id')
                ->orderBy('quote_formula_categories.sort_order', $sortDirection)
                ->orderBy('quote_formulas.sort_order', $sortDirection)
                ->select('quote_formulas.*');
        } else {
            $query->orderBy($sortBy, $sortDirection);
        }

        return $query->paginate($perPage);
    }

    /**
     * 카테고리별 수식 목록 (실행 순서용)
     */
    public function getFormulasByCategory(?int $productId = null): Collection
    {
        $tenantId = session('selected_tenant_id');

        return QuoteFormula::query()
            ->where('tenant_id', $tenantId)
            ->where('is_active', true)
            ->where(function ($q) use ($productId) {
                $q->whereNull('product_id');
                if ($productId) {
                    $q->orWhere('product_id', $productId);
                }
            })
            ->with(['category', 'ranges', 'mappings', 'items'])
            ->get()
            ->groupBy('category.code')
            ->sortBy(fn ($formulas) => $formulas->first()->category->sort_order);
    }

    /**
     * 수식 상세 조회
     */
    public function getFormulaById(int $id, bool $withRelations = false): ?QuoteFormula
    {
        $query = QuoteFormula::query();

        if ($withRelations) {
            $query->with(['category', 'ranges', 'mappings', 'items']);
        }

        return $query->find($id);
    }

    /**
     * 수식 생성
     */
    public function createFormula(array $data): QuoteFormula
    {
        $tenantId = session('selected_tenant_id');

        return DB::transaction(function () use ($data, $tenantId) {
            // 자동 sort_order
            if (! isset($data['sort_order'])) {
                $maxOrder = QuoteFormula::where('tenant_id', $tenantId)
                    ->where('category_id', $data['category_id'])
                    ->max('sort_order');
                $data['sort_order'] = ($maxOrder ?? 0) + 1;
            }

            $formula = QuoteFormula::create([
                'tenant_id' => $tenantId,
                'category_id' => $data['category_id'],
                'product_id' => $data['product_id'] ?? null,
                'name' => $data['name'],
                'variable' => $data['variable'],
                'type' => $data['type'],
                'formula' => $data['formula'] ?? null,
                'output_type' => $data['output_type'] ?? 'variable',
                'description' => $data['description'] ?? null,
                'sort_order' => $data['sort_order'],
                'is_active' => $data['is_active'] ?? true,
                'created_by' => auth()->id(),
            ]);

            // 범위별 규칙 저장
            if ($data['type'] === QuoteFormula::TYPE_RANGE && ! empty($data['ranges'])) {
                $this->saveRanges($formula, $data['ranges']);
            }

            // 매핑 규칙 저장
            if ($data['type'] === QuoteFormula::TYPE_MAPPING && ! empty($data['mappings'])) {
                $this->saveMappings($formula, $data['mappings']);
            }

            // 품목 출력 저장
            if (($data['output_type'] ?? 'variable') === QuoteFormula::OUTPUT_ITEM && ! empty($data['items'])) {
                $this->saveItems($formula, $data['items']);
            }

            return $formula->load(['ranges', 'mappings', 'items']);
        });
    }

    /**
     * 수식 수정
     */
    public function updateFormula(int $id, array $data): QuoteFormula
    {
        $formula = QuoteFormula::findOrFail($id);

        return DB::transaction(function () use ($formula, $data) {
            $formula->update([
                'category_id' => $data['category_id'] ?? $formula->category_id,
                'product_id' => array_key_exists('product_id', $data) ? $data['product_id'] : $formula->product_id,
                'name' => $data['name'] ?? $formula->name,
                'variable' => $data['variable'] ?? $formula->variable,
                'type' => $data['type'] ?? $formula->type,
                'formula' => array_key_exists('formula', $data) ? $data['formula'] : $formula->formula,
                'output_type' => $data['output_type'] ?? $formula->output_type,
                'description' => array_key_exists('description', $data) ? $data['description'] : $formula->description,
                'sort_order' => $data['sort_order'] ?? $formula->sort_order,
                'is_active' => $data['is_active'] ?? $formula->is_active,
                'updated_by' => auth()->id(),
            ]);

            // 범위 규칙 업데이트
            if (isset($data['ranges'])) {
                $formula->ranges()->delete();
                if ($formula->type === QuoteFormula::TYPE_RANGE) {
                    $this->saveRanges($formula, $data['ranges']);
                }
            }

            // 매핑 규칙 업데이트
            if (isset($data['mappings'])) {
                $formula->mappings()->delete();
                if ($formula->type === QuoteFormula::TYPE_MAPPING) {
                    $this->saveMappings($formula, $data['mappings']);
                }
            }

            // 품목 출력 업데이트
            if (isset($data['items'])) {
                $formula->items()->delete();
                if ($formula->output_type === QuoteFormula::OUTPUT_ITEM) {
                    $this->saveItems($formula, $data['items']);
                }
            }

            return $formula->fresh(['category', 'ranges', 'mappings', 'items']);
        });
    }

    /**
     * 수식 삭제
     */
    public function deleteFormula(int $id): void
    {
        $formula = QuoteFormula::findOrFail($id);
        $formula->delete();
    }

    /**
     * 수식 활성/비활성 토글
     */
    public function toggleActive(int $id): QuoteFormula
    {
        $formula = QuoteFormula::findOrFail($id);

        $formula->is_active = ! $formula->is_active;
        $formula->updated_by = auth()->id();
        $formula->save();

        return $formula;
    }

    /**
     * 수식 복원
     */
    public function restoreFormula(int $id): void
    {
        $formula = QuoteFormula::withTrashed()->findOrFail($id);
        $formula->restore();
    }

    /**
     * 수식 영구 삭제
     */
    public function forceDeleteFormula(int $id): void
    {
        $formula = QuoteFormula::withTrashed()->findOrFail($id);

        DB::transaction(function () use ($formula) {
            $formula->ranges()->delete();
            $formula->mappings()->delete();
            $formula->items()->delete();
            $formula->forceDelete();
        });
    }

    /**
     * 변수명 중복 체크 (테넌트 전체 범위)
     */
    public function isVariableExists(string $variable, ?int $excludeId = null): bool
    {
        $tenantId = session('selected_tenant_id');

        $query = QuoteFormula::where('tenant_id', $tenantId)
            ->where('variable', $variable);

        if ($excludeId) {
            $query->where('id', '!=', $excludeId);
        }

        return $query->exists();
    }

    /**
     * 수식 순서 변경
     */
    public function reorder(array $formulaIds): void
    {
        foreach ($formulaIds as $index => $id) {
            QuoteFormula::where('id', $id)->update([
                'sort_order' => $index + 1,
                'updated_by' => auth()->id(),
            ]);
        }
    }

    /**
     * 모든 활성 수식 조회
     */
    public function getAllActiveFormulas(): Collection
    {
        $tenantId = session('selected_tenant_id');

        return QuoteFormula::query()
            ->where('tenant_id', $tenantId)
            ->where('is_active', true)
            ->with(['category', 'ranges', 'mappings', 'items'])
            ->orderBy('category_id')
            ->orderBy('sort_order')
            ->get();
    }

    /**
     * 수식 복제
     */
    public function duplicateFormula(int $id): QuoteFormula
    {
        $original = QuoteFormula::with(['ranges', 'mappings', 'items'])->findOrFail($id);
        $tenantId = session('selected_tenant_id');

        return DB::transaction(function () use ($original, $tenantId) {
            // 새로운 변수명 생성
            $newVariable = $this->generateUniqueVariable($original->variable);

            // 수식 복제
            $formula = QuoteFormula::create([
                'tenant_id' => $tenantId,
                'category_id' => $original->category_id,
                'product_id' => $original->product_id,
                'name' => $original->name.' (복제)',
                'variable' => $newVariable,
                'type' => $original->type,
                'formula' => $original->formula,
                'output_type' => $original->output_type,
                'description' => $original->description,
                'sort_order' => $original->sort_order + 1,
                'is_active' => false, // 복제본은 비활성 상태로 생성
                'created_by' => auth()->id(),
            ]);

            // 범위 규칙 복제
            foreach ($original->ranges as $range) {
                QuoteFormulaRange::create([
                    'formula_id' => $formula->id,
                    'min_value' => $range->min_value,
                    'max_value' => $range->max_value,
                    'condition_variable' => $range->condition_variable,
                    'result_value' => $range->result_value,
                    'result_type' => $range->result_type,
                    'sort_order' => $range->sort_order,
                ]);
            }

            // 매핑 규칙 복제
            foreach ($original->mappings as $mapping) {
                QuoteFormulaMapping::create([
                    'formula_id' => $formula->id,
                    'source_variable' => $mapping->source_variable,
                    'source_value' => $mapping->source_value,
                    'result_value' => $mapping->result_value,
                    'result_type' => $mapping->result_type,
                    'sort_order' => $mapping->sort_order,
                ]);
            }

            // 품목 출력 복제
            foreach ($original->items as $item) {
                QuoteFormulaItem::create([
                    'formula_id' => $formula->id,
                    'item_code' => $item->item_code,
                    'item_name' => $item->item_name,
                    'specification' => $item->specification,
                    'unit' => $item->unit,
                    'quantity_formula' => $item->quantity_formula,
                    'unit_price_formula' => $item->unit_price_formula,
                    'sort_order' => $item->sort_order,
                ]);
            }

            return $formula->load(['category', 'ranges', 'mappings', 'items']);
        });
    }

    /**
     * 고유한 변수명 생성
     */
    private function generateUniqueVariable(string $baseVariable): string
    {
        $tenantId = session('selected_tenant_id');
        $suffix = 1;
        $newVariable = $baseVariable.'_COPY';

        while (QuoteFormula::where('tenant_id', $tenantId)->where('variable', $newVariable)->exists()) {
            $suffix++;
            $newVariable = $baseVariable.'_COPY'.$suffix;
        }

        return $newVariable;
    }

    /**
     * 변수 목록 조회 (수식 입력 시 참조용)
     */
    public function getAvailableVariables(?int $productId = null): array
    {
        $tenantId = session('selected_tenant_id');

        $formulas = QuoteFormula::query()
            ->where('tenant_id', $tenantId)
            ->where('is_active', true)
            ->where('output_type', 'variable')
            ->where(function ($q) use ($productId) {
                $q->whereNull('product_id');
                if ($productId) {
                    $q->orWhere('product_id', $productId);
                }
            })
            ->with('category')
            ->orderBy('category_id')
            ->orderBy('sort_order')
            ->get();

        return $formulas->map(fn ($f) => [
            'variable' => $f->variable,
            'name' => $f->name,
            'category' => $f->category->name,
            'type' => $f->type,
        ])->toArray();
    }

    /**
     * 수식 통계
     */
    public function getFormulaStats(): array
    {
        $tenantId = session('selected_tenant_id');

        return [
            'total' => QuoteFormula::where('tenant_id', $tenantId)->count(),
            'active' => QuoteFormula::where('tenant_id', $tenantId)->where('is_active', true)->count(),
            'by_type' => [
                'input' => QuoteFormula::where('tenant_id', $tenantId)->where('type', 'input')->count(),
                'calculation' => QuoteFormula::where('tenant_id', $tenantId)->where('type', 'calculation')->count(),
                'range' => QuoteFormula::where('tenant_id', $tenantId)->where('type', 'range')->count(),
                'mapping' => QuoteFormula::where('tenant_id', $tenantId)->where('type', 'mapping')->count(),
            ],
        ];
    }

    // =========================================================================
    // Private Methods
    // =========================================================================

    /**
     * 범위 규칙 저장
     */
    private function saveRanges(QuoteFormula $formula, array $ranges): void
    {
        foreach ($ranges as $index => $range) {
            QuoteFormulaRange::create([
                'formula_id' => $formula->id,
                'min_value' => $range['min_value'] ?? null,
                'max_value' => $range['max_value'] ?? null,
                'condition_variable' => $range['condition_variable'],
                'result_value' => $range['result_value'],
                'result_type' => $range['result_type'] ?? 'fixed',
                'sort_order' => $index + 1,
            ]);
        }
    }

    /**
     * 매핑 규칙 저장
     */
    private function saveMappings(QuoteFormula $formula, array $mappings): void
    {
        foreach ($mappings as $index => $mapping) {
            QuoteFormulaMapping::create([
                'formula_id' => $formula->id,
                'source_variable' => $mapping['source_variable'],
                'source_value' => $mapping['source_value'],
                'result_value' => $mapping['result_value'],
                'result_type' => $mapping['result_type'] ?? 'fixed',
                'sort_order' => $index + 1,
            ]);
        }
    }

    /**
     * 품목 출력 저장
     */
    private function saveItems(QuoteFormula $formula, array $items): void
    {
        foreach ($items as $index => $item) {
            QuoteFormulaItem::create([
                'formula_id' => $formula->id,
                'item_code' => $item['item_code'],
                'item_name' => $item['item_name'],
                'specification' => $item['specification'] ?? null,
                'unit' => $item['unit'],
                'quantity_formula' => $item['quantity_formula'],
                'unit_price_formula' => $item['unit_price_formula'] ?? null,
                'sort_order' => $index + 1,
            ]);
        }
    }
}