<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // SQLite enforces globally unique index names across all tables in the
        // database, unlike MySQL where index names only need to be unique per
        // table. The legacy `idx_email`/`idx_phone` names collide with the
        // identically named indexes on the `contact_submissions` table, so
        // prefix them on SQLite only; MySQL keeps the exact legacy names.
        $isSqlite = DB::getDriverName() === 'sqlite';

        Schema::create('subscriptions', function (Blueprint $table) use ($isSqlite) {
            $table->integer('id')->autoIncrement();
            $table->string('full_name');
            $table->string('phone', 20);
            $table->string('email', 254);
            $table->string('whatsapp', 20)->nullable();
            $table->text('address');
            $table->string('city', 100);
            $table->string('pincode', 6);
            $table->string('landmark', 200)->nullable();
            $table->string('plan', 50);
            $table->decimal('plan_price', 10, 2);
            $table->date('start_date');
            $table->text('special_instructions')->nullable();
            $table->boolean('terms_agreed');
            $table->dateTime('terms_agreed_at')->nullable();
            $table->boolean('promo_emails')->default(false);
            $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable();
            $table->string('order_id', 50);
            $table->enum('order_status', ['pending', 'confirmed', 'active', 'paused', 'cancelled', 'completed'])
                ->default('pending');
            $table->timestamp('created_at')->nullable();
            $table->timestamp('updated_at')->nullable();

            $table->unique('order_id', 'order_id');
            $table->unique(['phone', 'plan', 'start_date'], 'uq_sub_dedupe');
            $table->index('phone', $isSqlite ? 'subscriptions_idx_phone' : 'idx_phone');
            $table->index('email', $isSqlite ? 'subscriptions_idx_email' : 'idx_email');
            $table->index('order_id', 'idx_order_id');
            $table->index('start_date', 'idx_start_date');
            $table->index('order_status', 'idx_order_status');
        });

        if (! $isSqlite) {
            DB::statement(
                'ALTER TABLE `subscriptions` '.
                'MODIFY `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, '.
                'MODIFY `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
            );

            DB::statement(
                "ALTER TABLE `subscriptions` ADD CONSTRAINT `chk_phone_format` CHECK (regexp_like(`phone`,'^[6-9][0-9]{9}$'))"
            );
            DB::statement(
                "ALTER TABLE `subscriptions` ADD CONSTRAINT `chk_pincode_format` CHECK (regexp_like(`pincode`,'^[0-9]{6}$'))"
            );
            DB::statement(
                "ALTER TABLE `subscriptions` ADD CONSTRAINT `chk_plan` CHECK (`plan` in ('basic','premium','luxury'))"
            );
        }
    }

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