<?php

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

// ══════════════════════════════════════════════════════════════════
//  InPractice — Forum Reply Ratings Migration
//  Location: database/migrations/2025_01_01_000010_add_forum_reply_ratings.php
//
//  Three changes:
//    1. Add avg_rating + ratings_count to forum_replies
//       (cached columns — never compute AVG on read)
//    2. Create forum_reply_ratings table
//       (one rating per member per reply — same pattern as case_solution_ratings)
//    3. Add highly_rated_replies_count to users
//       (separate track from highly_rated_solutions_count — Field Advisor badges)
// ══════════════════════════════════════════════════════════════════

return new class extends Migration
{
    public function up(): void
    {
        // ── 1. Add rating cache columns to forum_replies ───────
        Schema::table('forum_replies', function (Blueprint $table) {
            $table->decimal('avg_rating', 3, 2)->default(0.00)->after('like_count');
            $table->unsignedInteger('ratings_count')->default(0)->after('avg_rating');
        });

        // ── 2. Create forum_reply_ratings table ────────────────
        Schema::create('forum_reply_ratings', function (Blueprint $table) {
            $table->id();

            $table->foreignId('forum_reply_id')
                  ->constrained('forum_replies')
                  ->cascadeOnDelete();

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

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

            $table->timestamp('created_at')->useCurrent();

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

        // ── 3. Add Field Advisor badge counter to users ────────
        Schema::table('users', function (Blueprint $table) {
            $table->unsignedInteger('highly_rated_replies_count')
                  ->default(0)
                  ->after('highly_rated_solutions_count');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('forum_reply_ratings');

        Schema::table('forum_replies', function (Blueprint $table) {
            $table->dropColumn(['avg_rating', 'ratings_count']);
        });

        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('highly_rated_replies_count');
        });
    }
};
