<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * Creates the tickets table for the Communication Module.
     * 
     * Requirements: 1.1, 1.4, 6.5, 8.1
     */
    public function up(): void
    {
        Schema::create('tickets', function (Blueprint $table) {
            // Primary key
            $table->id();

            // Foreign key to users table
            $table->foreignId('user_id')
                ->constrained()
                ->onDelete('cascade')
                ->comment('The user who created the ticket');

            // Ticket details (respecting MySQL 5.7 191 char limit for indexed columns)
            $table->string('title', 191);
            $table->text('description');

            // Unique identifier for tracking
            $table->string('unique_identifier', 50)->unique()->comment('Human-readable ticket ID like TKT-XXXX');

            // Status tracking
            $table->enum('status', ['pending', 'in_progress', 'resolved', 'closed'])
                ->default('pending')
                ->comment('Current ticket status');
            
            $table->enum('priority', ['low', 'medium', 'high', 'urgent'])
                ->default('medium')
                ->comment('Ticket priority level');

            // Category for organization (optional)
            $table->string('category', 100)->nullable()->comment('Ticket category');

            // Assignment tracking
            $table->foreignId('assigned_to')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('Staff member assigned to handle ticket');

            // Timestamp for last activity (for sorting)
            $table->timestamp('last_activity_at')->nullable()->comment('Last reply or update time');

            // Resolution details
            $table->text('resolution_notes')->nullable()->comment('Notes on how ticket was resolved');
            $table->timestamp('resolved_at')->nullable()->comment('When ticket was resolved');
            $table->foreignId('resolved_by')
                ->nullable()
                ->constrained('users')
                ->nullOnDelete()
                ->comment('User who resolved the ticket');

            // Standard timestamps
            $table->timestamps();
            $table->softDeletes();

            // Indexes for performance
            $table->index('status');
            $table->index('priority');
            $table->index('created_at');
            $table->index('last_activity_at');
            $table->index(['user_id', 'status']);
            $table->index(['assigned_to', 'status']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('tickets');
    }
};
