<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

// ══════════════════════════════════════════════════════════════════
//  InPractice — Member Solutions & Ratings Migration
//
//  Two tables:
//    case_member_solutions  — member-written solutions per case
//    case_solution_ratings  — star ratings on solutions (1–5)
//
//  Also adds two columns to case_progress:
//    case_rating      — member's 1–5 rating of the InPractice solution
//    case_rated_at    — when they rated it
//
//  Performance:
//    - Indexes on all foreign keys and lookup columns
//    - Unique index on (solution_id, user_id) — one rating per member
//    - Unique index on (case_id, user_id) — one solution per member per case
//    - avg_rating + ratings_count cached on solution row — no aggregation on read
//    - highly_rated_count cached on users table — no aggregation on read
// ══════════════════════════════════════════════════════════════════

return new class extends Migration
{
    public function up(): void
    {
        // ── Member Solutions ───────────────────────────────────
        Schema::create('case_member_solutions', function (Blueprint $table) {
            $table->id();

            $table->foreignId('case_id')
                  ->constrained('cases')
                  ->cascadeOnDelete();

            $table->foreignId('user_id')
                  ->constrained('users')
                  ->cascadeOnDelete();

            $table->text('content');

            // Cached aggregates — updated on every rating change
            // Avoids COUNT/AVG queries on every page load
            $table->decimal('avg_rating', 3, 2)->default(0.00);
            $table->unsignedInteger('ratings_count')->default(0);

            $table->timestamps();

            // One solution per member per case
            $table->unique(['case_id', 'user_id']);

            // Performance indexes
            $table->index('case_id');
            $table->index('user_id');
            $table->index(['case_id', 'avg_rating']); // for sorting by top-rated
        });

        // ── Solution Ratings ───────────────────────────────────
        Schema::create('case_solution_ratings', function (Blueprint $table) {
            $table->id();

            $table->foreignId('solution_id')
                  ->constrained('case_member_solutions')
                  ->cascadeOnDelete(); // rating deleted when solution deleted

            $table->foreignId('user_id')
                  ->constrained('users')
                  ->cascadeOnDelete();

            $table->unsignedTinyInteger('rating'); // 1–5

            $table->timestamps();

            // One rating per member per solution — enforced at DB level
            $table->unique(['solution_id', 'user_id']);

            // Performance index
            $table->index('solution_id');
            $table->index('user_id');
        });

        // ── Add rating columns to case_progress ───────────────
        // Members rate the InPractice solution (separate from member solutions)
        Schema::table('case_progress', function (Blueprint $table) {
            $table->unsignedTinyInteger('case_rating')->nullable()->after('completed_at');
            $table->timestamp('case_rated_at')->nullable()->after('case_rating');
        });

        // ── Add achievement counter to users ───────────────────
        // Cached count of solutions with avg_rating >= 4
        // Updated on every rating save — never computed on page load
        Schema::table('users', function (Blueprint $table) {
            $table->unsignedInteger('highly_rated_solutions_count')
                  ->default(0)
                  ->after('language');
        });
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('highly_rated_solutions_count');
        });

        Schema::table('case_progress', function (Blueprint $table) {
            $table->dropColumn(['case_rating', 'case_rated_at']);
        });

        Schema::dropIfExists('case_solution_ratings');
        Schema::dropIfExists('case_member_solutions');
    }
};
