<?php

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

return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('care_dental_charts')) {
            Schema::create('care_dental_charts', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->string('owner_ref', 64)->index();
                $table->foreignId('organization_id')->constrained('care_organizations')->cascadeOnDelete();
                $table->foreignId('patient_id')->constrained('care_patients')->cascadeOnDelete();
                $table->string('notation', 16)->default('fdi');
                $table->timestamp('charted_at')->nullable();
                $table->string('charted_by')->nullable();
                $table->timestamps();

                $table->unique(['patient_id']);
                $table->index(['organization_id', 'patient_id']);
            });
        }

        if (! Schema::hasTable('care_dental_teeth')) {
            Schema::create('care_dental_teeth', function (Blueprint $table) {
                $table->id();
                $table->foreignId('dental_chart_id')->constrained('care_dental_charts')->cascadeOnDelete();
                $table->string('tooth_code', 8);
                $table->string('status', 32)->default('present');
                $table->json('surfaces')->nullable();
                $table->json('conditions')->nullable();
                $table->text('notes')->nullable();
                $table->timestamps();

                $table->unique(['dental_chart_id', 'tooth_code']);
            });
        }

        if (! Schema::hasTable('care_dental_chart_events')) {
            Schema::create('care_dental_chart_events', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->string('owner_ref', 64)->index();
                $table->foreignId('organization_id')->constrained('care_organizations')->cascadeOnDelete();
                $table->foreignId('dental_chart_id')->constrained('care_dental_charts')->cascadeOnDelete();
                $table->foreignId('patient_id')->constrained('care_patients')->cascadeOnDelete();
                $table->foreignId('visit_id')->nullable()->constrained('care_visits')->nullOnDelete();
                $table->string('tooth_code', 8)->nullable();
                $table->string('event_type', 64);
                $table->json('payload')->nullable();
                $table->string('recorded_by')->nullable();
                $table->timestamp('recorded_at')->nullable();
                $table->timestamps();

                $table->index(['dental_chart_id', 'recorded_at'], 'care_den_chart_events_chart_at_idx');
                $table->index(['visit_id']);
            });
        }

        if (! Schema::hasTable('care_dental_treatment_plans')) {
            Schema::create('care_dental_treatment_plans', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->string('owner_ref', 64)->index();
                $table->foreignId('organization_id')->constrained('care_organizations')->cascadeOnDelete();
                $table->foreignId('patient_id')->constrained('care_patients')->cascadeOnDelete();
                $table->foreignId('visit_id')->nullable()->constrained('care_visits')->nullOnDelete();
                $table->string('status', 32)->default('draft')->index();
                $table->string('title')->nullable();
                $table->unsignedInteger('estimate_minor')->default(0);
                $table->timestamp('accepted_at')->nullable();
                $table->timestamp('completed_at')->nullable();
                $table->string('created_by')->nullable();
                $table->timestamps();
                $table->softDeletes();

                $table->index(
                    ['organization_id', 'patient_id', 'status'],
                    'care_den_plans_org_pt_status_idx',
                );
            });
        } elseif (! $this->hasIndex('care_dental_treatment_plans', 'care_den_plans_org_pt_status_idx')) {
            // Partial deploy: table created before long auto index name failed (MySQL 64-char limit).
            Schema::table('care_dental_treatment_plans', function (Blueprint $table) {
                $table->index(
                    ['organization_id', 'patient_id', 'status'],
                    'care_den_plans_org_pt_status_idx',
                );
            });
        }

        if (! Schema::hasTable('care_dental_plan_items')) {
            Schema::create('care_dental_plan_items', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->foreignId('treatment_plan_id')->constrained('care_dental_treatment_plans')->cascadeOnDelete();
                $table->string('tooth_code', 8)->nullable();
                $table->json('surfaces')->nullable();
                $table->string('procedure_code', 64);
                $table->string('label');
                $table->unsignedSmallInteger('priority')->default(50);
                $table->string('status', 32)->default('proposed')->index();
                $table->unsignedInteger('estimate_minor')->default(0);
                $table->foreignId('bill_line_item_id')->nullable()->constrained('care_bill_line_items')->nullOnDelete();
                $table->text('notes')->nullable();
                $table->timestamps();

                $table->index(['treatment_plan_id', 'status'], 'care_den_plan_items_plan_status_idx');
            });
        }

        if (! Schema::hasTable('care_dental_procedures')) {
            Schema::create('care_dental_procedures', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->string('owner_ref', 64)->index();
                $table->foreignId('organization_id')->constrained('care_organizations')->cascadeOnDelete();
                $table->foreignId('patient_id')->constrained('care_patients')->cascadeOnDelete();
                $table->foreignId('visit_id')->constrained('care_visits')->cascadeOnDelete();
                $table->foreignId('plan_item_id')->nullable()->constrained('care_dental_plan_items')->nullOnDelete();
                $table->string('tooth_code', 8)->nullable();
                $table->json('surfaces')->nullable();
                $table->string('procedure_code', 64);
                $table->string('label');
                $table->string('anesthesia', 32)->nullable();
                $table->string('status', 32)->default('completed')->index();
                $table->unsignedInteger('amount_minor')->default(0);
                $table->foreignId('bill_line_item_id')->nullable()->constrained('care_bill_line_items')->nullOnDelete();
                $table->string('provider_ref')->nullable();
                $table->text('notes')->nullable();
                $table->timestamp('performed_at')->nullable();
                $table->timestamps();

                $table->index(['visit_id', 'status'], 'care_den_proc_visit_status_idx');
                $table->index(['organization_id', 'procedure_code'], 'care_den_proc_org_code_idx');
            });
        }

        if (! Schema::hasTable('care_dental_images')) {
            Schema::create('care_dental_images', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->string('owner_ref', 64)->index();
                $table->foreignId('organization_id')->constrained('care_organizations')->cascadeOnDelete();
                $table->foreignId('patient_id')->constrained('care_patients')->cascadeOnDelete();
                $table->foreignId('visit_id')->nullable()->constrained('care_visits')->nullOnDelete();
                $table->foreignId('patient_attachment_id')->constrained('care_patient_attachments')->cascadeOnDelete();
                $table->string('modality', 32);
                $table->json('tooth_codes')->nullable();
                $table->string('caption')->nullable();
                $table->string('uploaded_by')->nullable();
                $table->timestamp('captured_at')->nullable();
                $table->timestamps();

                $table->index(['visit_id', 'modality'], 'care_den_images_visit_mod_idx');
                $table->index(['patient_id', 'captured_at'], 'care_den_images_patient_cap_idx');
            });
        }

        if (! Schema::hasTable('care_dental_visit_notes')) {
            Schema::create('care_dental_visit_notes', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->string('owner_ref', 64)->index();
                $table->foreignId('organization_id')->constrained('care_organizations')->cascadeOnDelete();
                $table->foreignId('visit_id')->constrained('care_visits')->cascadeOnDelete();
                $table->foreignId('patient_id')->constrained('care_patients')->cascadeOnDelete();
                $table->text('chief_complaint')->nullable();
                $table->text('soft_tissue')->nullable();
                $table->text('occlusion')->nullable();
                $table->text('notes')->nullable();
                $table->string('recorded_by')->nullable();
                $table->timestamp('recorded_at')->nullable();
                $table->timestamps();

                $table->unique(['visit_id']);
            });
        }
    }

    protected function hasIndex(string $table, string $indexName): bool
    {
        $connection = Schema::getConnection();
        $database = $connection->getDatabaseName();
        $row = $connection->selectOne(
            'select 1 as ok from information_schema.statistics where table_schema = ? and table_name = ? and index_name = ? limit 1',
            [$database, $table, $indexName],
        );

        return $row !== null;
    }

    public function down(): void
    {
        Schema::dropIfExists('care_dental_visit_notes');
        Schema::dropIfExists('care_dental_images');
        Schema::dropIfExists('care_dental_procedures');
        Schema::dropIfExists('care_dental_plan_items');
        Schema::dropIfExists('care_dental_treatment_plans');
        Schema::dropIfExists('care_dental_chart_events');
        Schema::dropIfExists('care_dental_teeth');
        Schema::dropIfExists('care_dental_charts');
    }
};
