<?php

namespace App\Services\Member;

use App\Models\Cases;
use App\Models\CaseProgress;
use App\Models\MemberSolution;
use App\Models\SolutionRating;
use App\Models\User;
use Illuminate\Support\Facades\DB;

// ══════════════════════════════════════════════════════════════════
//  InPractice — MemberSolutionService
//  Location: app/Services/Member/MemberSolutionService.php
//
//  All business logic for:
//    - Loading solutions for a case (N+1 safe)
//    - Storing / updating / deleting member solutions
//    - Rating member solutions (1–5)
//    - Rating the InPractice solution (stored on case_progress)
//    - Updating cached aggregates (avg_rating, highly_rated_solutions_count)
//
//  Performance rules followed:
//    - Solutions loaded with ['user'] in one query
//    - Current user's ratings loaded in ONE separate query, mapped in PHP
//    - avg_rating and ratings_count cached on solution row — no AVG() on read
//    - highly_rated_solutions_count cached on user row — no COUNT() on read
// ══════════════════════════════════════════════════════════════════

class MemberSolutionService
{
    // ── Load all solutions for a case ─────────────────────────
    // Returns:
    //   solutions        — array of formatted solution objects
    //   my_solution      — current user's solution or null
    //   my_case_rating   — current user's rating of InPractice solution or null

    public function getSolutions(Cases $case, User $user): array
    {
        // One query — all solutions with their authors
        // user: id, nickname, profession, highly_rated_solutions_count only
        $solutions = MemberSolution::forCase($case->id)
            ->with(['user:id,nickname,profession,highly_rated_solutions_count'])
            ->topRated()
            ->get();

        // One query — all ratings this user has given on this case's solutions
        // Map by solution_id in PHP — zero N+1
        $myRatings = SolutionRating::whereIn('solution_id', $solutions->pluck('id'))
            ->where('user_id', $user->id)
            ->pluck('rating', 'solution_id');

        // Current user's own solution (null if not submitted yet)
        $mySolution = $solutions->firstWhere('user_id', $user->id);

        // Format each solution for Vue
        $formatted = $solutions->map(function ($solution) use ($user, $myRatings) {
            return $this->formatSolution($solution, $user, $myRatings);
        })->values()->toArray();

        // Current user's rating of the InPractice solution
        $progress = CaseProgress::where('case_id', $case->id)
            ->where('user_id', $user->id)
            ->first();

        return [
            'solutions'      => $formatted,
            'my_solution_id' => $mySolution?->id,
            'my_case_rating' => $progress?->case_rating,
        ];
    }

    // ── Store a new solution ───────────────────────────────────

    public function store(User $user, Cases $case, string $content): MemberSolution
    {
        return DB::transaction(function () use ($user, $case, $content) {
            return MemberSolution::create([
                'case_id' => $case->id,
                'user_id' => $user->id,
                'content' => $content,
            ]);
        });
    }

    // ── Update existing solution ───────────────────────────────

    public function update(User $user, MemberSolution $solution, string $content): MemberSolution
    {
        abort_if($solution->user_id !== $user->id, 403);

        $solution->update(['content' => $content]);

        return $solution->fresh();
    }

    // ── Delete solution — returns rating count for the alert ──

    public function getRatingCountForDelete(MemberSolution $solution, User $user): int
    {
        abort_if($solution->user_id !== $user->id, 403);

        return $solution->ratings_count;
    }

    public function delete(User $user, MemberSolution $solution): void
    {
        abort_if($solution->user_id !== $user->id, 403);

        DB::transaction(function () use ($user, $solution) {
            // Recalculate user's highly_rated badge count
            // This solution's contribution must be removed
            if ($solution->avg_rating >= 4.0 && $solution->ratings_count > 0) {
                $user->decrement('highly_rated_solutions_count');
            }

            // Ratings cascade-deleted via FK constraint
            $solution->delete();
        });
    }

    // ── Rate a member solution (1–5) ──────────────────────────
    // Upsert — member can change their rating
    // Updates cached avg_rating + ratings_count on solution
    // Updates cached highly_rated_solutions_count on solution author

    public function rateSolution(User $rater, MemberSolution $solution, int $rating): array
    {
        // Cannot rate own solution
        abort_if($solution->user_id === $rater->id, 403);

        DB::transaction(function () use ($rater, $solution, $rating) {
            $previous = SolutionRating::where('solution_id', $solution->id)
                ->where('user_id', $rater->id)
                ->first();

            // Upsert the rating
            SolutionRating::updateOrCreate(
                ['solution_id' => $solution->id, 'user_id' => $rater->id],
                ['rating' => $rating]
            );

            // Recalculate cached aggregates on the solution
            $this->recalculateSolutionAggregates($solution);

            // Refresh to get new avg_rating
            $solution->refresh();

            // Update author's highly_rated badge count
            $this->recalculateUserBadge($solution->user);
        });

        $solution->refresh();

        return [
            'avg_rating'     => round($solution->avg_rating, 1),
            'ratings_count'  => $solution->ratings_count,
            'my_rating'      => $rating,
            'badge'          => $solution->badge,
        ];
    }

    // ── Rate the InPractice solution ──────────────────────────
    // Stored on case_progress — one rating per member per case
    // Idempotent — member can update their rating

    public function rateCase(User $user, Cases $case, int $rating): array
    {
        $progress = CaseProgress::where('case_id', $case->id)
            ->where('user_id', $user->id)
            ->first();

        abort_if(!$progress || !$progress->solution_revealed, 403);

        $progress->update([
            'case_rating'   => $rating,
            'case_rated_at' => now(),
        ]);

        return ['case_rating' => $rating];
    }

    // ── Private helpers ───────────────────────────────────────

    private function recalculateSolutionAggregates(MemberSolution $solution): void
    {
        $result = SolutionRating::where('solution_id', $solution->id)
            ->selectRaw('COUNT(*) as cnt, AVG(rating) as avg')
            ->first();

        $solution->update([
            'ratings_count' => $result->cnt ?? 0,
            'avg_rating'    => round($result->avg ?? 0, 2),
        ]);
    }

    private function recalculateUserBadge(User $author): void
    {
        // Count how many of this user's solutions have avg_rating >= 4
        // and at least 1 rating (prevents gaming with self-review)
        $count = MemberSolution::where('user_id', $author->id)
            ->where('avg_rating', '>=', 4.0)
            ->where('ratings_count', '>', 0)
            ->count();

        $author->update(['highly_rated_solutions_count' => $count]);
    }

    private function formatSolution(
        MemberSolution $solution,
        User $currentUser,
        $myRatings
    ): array {
        $badge = MemberSolution::resolveBadge(
            $solution->user->highly_rated_solutions_count ?? 0
        );

        return [
            'id'            => $solution->id,
            'content'       => $solution->content,
            'avg_rating'    => round($solution->avg_rating, 1),
            'ratings_count' => $solution->ratings_count,
            'is_mine'       => $solution->user_id === $currentUser->id,
            'my_rating'     => $myRatings[$solution->id] ?? null,
            'created_at'    => $solution->created_at->diffForHumans(),
            'updated_at'    => $solution->updated_at->diffForHumans(),
            'was_edited'    => $solution->updated_at->gt($solution->created_at->addMinute()),
            'author' => [
                'nickname'   => $solution->user->nickname,
                'profession' => $solution->user->profession,
                'badge'      => $badge,
            ],
        ];
    }
}
