<?php

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

// [INTEGRATION] Creates 13 new tables matching Flutter SQLite v25 schema.
// These tables exist only on the device and were never synced to the server.
return new class extends Migration
{
    public function up(): void
    {
        // [INTEGRATION] units — unit-of-measure labels (kg, pcs, box, etc.)
        if (!Schema::hasTable('units')) {
            Schema::create('units', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('name');
                $table->string('abbreviation')->nullable();
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
                $table->unique(['user_id', 'store_id', 'name']);
            });
        }

        // [INTEGRATION] discounts — product/category/cart discount definitions
        if (!Schema::hasTable('discounts')) {
            Schema::create('discounts', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('name');
                $table->string('code')->nullable();
                $table->string('scope', 20)->default('cart'); // product|category|cart
                $table->string('type', 20)->default('percent'); // fixed|percent
                $table->decimal('value', 14, 4)->default(0);
                $table->decimal('min_purchase', 14, 4)->default(0);
                $table->string('product_id')->nullable();
                $table->string('product_name')->nullable();
                $table->string('category_id')->nullable();
                $table->string('category_name')->nullable();
                $table->unsignedInteger('max_uses')->default(0);
                $table->unsignedInteger('used_count')->default(0);
                $table->date('valid_from')->nullable();
                $table->date('valid_to')->nullable();
                $table->boolean('is_active')->default(true);
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] taxes — tax rate configurations (VAT, GST, etc.)
        if (!Schema::hasTable('taxes')) {
            Schema::create('taxes', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('name');
                $table->string('abbreviation');
                $table->decimal('rate', 8, 4)->default(0);
                $table->string('tax_type', 20)->default('exclusive'); // exclusive|inclusive
                $table->string('applies_to', 20)->default('all'); // all|products|services
                $table->boolean('is_active')->default(true);
                $table->boolean('is_default')->default(false);
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] fee_configs — configurable checkout fees (delivery, service, etc.)
        if (!Schema::hasTable('fee_configs')) {
            Schema::create('fee_configs', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('name');
                $table->string('fee_type', 30)->default('delivery'); // delivery|service|shipping|custom
                $table->decimal('amount', 14, 4)->default(0);
                $table->string('amount_type', 20)->default('fixed'); // fixed|percent
                $table->string('tax_id')->nullable();
                $table->boolean('is_active')->default(true);
                $table->boolean('is_default')->default(false);
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] order_fees — per-order fee line items (applied from fee_configs)
        if (!Schema::hasTable('order_fees')) {
            Schema::create('order_fees', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->string('order_id')->index();
                $table->string('fee_config_id')->nullable();
                $table->string('name');
                $table->string('fee_type', 30)->default('delivery');
                $table->decimal('amount', 14, 4)->default(0);
                $table->timestamps();
            });
        }

        // [INTEGRATION] product_variants — size/colour/etc. variants of a product
        if (!Schema::hasTable('product_variants')) {
            Schema::create('product_variants', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('product_id')->index();
                $table->string('sku')->nullable();
                $table->string('variant_name');
                $table->text('attributes_json')->nullable();
                $table->decimal('cost_price', 14, 4)->default(0);
                $table->decimal('sell_price_modifier', 14, 4)->default(0);
                $table->integer('stock')->default(0);
                $table->integer('reorder_point')->default(0);
                $table->string('barcode')->nullable();
                $table->boolean('is_active')->default(true);
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] product_batches — batch/lot tracking with expiry dates
        if (!Schema::hasTable('product_batches')) {
            Schema::create('product_batches', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('product_id')->index();
                $table->string('batch_number');
                $table->date('expiry_date')->nullable();
                $table->date('manufacture_date')->nullable();
                $table->integer('quantity')->default(0);
                $table->decimal('cost_price', 14, 4)->default(0);
                $table->string('supplier_id')->nullable();
                $table->string('purchase_id')->nullable();
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] stock_movements — audit trail for all stock-level changes
        if (!Schema::hasTable('stock_movements')) {
            Schema::create('stock_movements', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('product_id')->index();
                $table->string('variant_id')->nullable();
                $table->string('batch_id')->nullable();
                // movement_type: sale|purchase|adjustment|return|supplier_return|customer_return|opening_stock
                $table->string('movement_type', 30);
                $table->integer('quantity'); // positive=IN, negative=OUT
                $table->decimal('unit_cost', 14, 4)->default(0);
                $table->string('reference_type')->nullable();
                $table->string('reference_id')->nullable();
                $table->text('notes')->nullable();
                $table->string('created_by')->nullable();
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] service_product_usage — materials consumed per service sale
        if (!Schema::hasTable('service_product_usage')) {
            Schema::create('service_product_usage', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('service_id')->index(); // FK → products (type='service')
                $table->string('product_id')->index(); // FK → products (type='product')
                $table->decimal('quantity_required', 10, 4)->default(1);
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] supplier_returns — stock returned to a supplier
        if (!Schema::hasTable('supplier_returns')) {
            Schema::create('supplier_returns', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('supplier_id')->nullable();
                $table->string('supplier_name')->nullable();
                $table->string('return_number');
                $table->date('return_date');
                $table->string('purchase_id')->nullable();
                $table->decimal('total_amount', 14, 4)->default(0);
                $table->string('status', 30)->default('completed');
                $table->text('reason')->nullable();
                $table->text('notes')->nullable();
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] supplier_return_items — line items for a supplier return
        if (!Schema::hasTable('supplier_return_items')) {
            Schema::create('supplier_return_items', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->string('return_id')->index();
                $table->string('product_id');
                $table->string('product_name');
                $table->integer('quantity')->default(0);
                $table->decimal('unit_cost', 14, 4)->default(0);
                $table->decimal('subtotal', 14, 4)->default(0);
                $table->timestamps();
            });
        }

        // [INTEGRATION] customer_returns — items returned by a customer
        if (!Schema::hasTable('customer_returns')) {
            Schema::create('customer_returns', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('store_id')->nullable()->index();
                $table->string('local_id')->nullable()->index();
                $table->string('order_id')->nullable();
                $table->string('invoice_number')->nullable();
                $table->string('return_number');
                $table->string('customer_id')->nullable();
                $table->string('customer_name')->default('Walk-in');
                $table->date('return_date');
                $table->decimal('total_amount', 14, 4)->default(0);
                $table->string('refund_method', 30)->default('cash');
                $table->string('status', 30)->default('completed');
                $table->text('reason')->nullable();
                $table->text('notes')->nullable();
                $table->string('device_id')->nullable();
                $table->unsignedInteger('sync_version')->default(0);
                $table->boolean('is_deleted')->default(false);
                $table->timestamps();
            });
        }

        // [INTEGRATION] customer_return_items — line items for a customer return
        if (!Schema::hasTable('customer_return_items')) {
            Schema::create('customer_return_items', function (Blueprint $table) {
                $table->ulid('id')->primary();
                $table->string('return_id')->index();
                $table->string('product_id');
                $table->string('product_name');
                $table->integer('quantity')->default(0);
                $table->decimal('unit_price', 14, 4)->default(0);
                $table->decimal('cost_price', 14, 4)->default(0);
                $table->decimal('subtotal', 14, 4)->default(0);
                $table->boolean('restock')->default(true);
                $table->timestamps();
            });
        }
    }

    public function down(): void
    {
        // Drop in reverse dependency order
        Schema::dropIfExists('customer_return_items');
        Schema::dropIfExists('customer_returns');
        Schema::dropIfExists('supplier_return_items');
        Schema::dropIfExists('supplier_returns');
        Schema::dropIfExists('service_product_usage');
        Schema::dropIfExists('stock_movements');
        Schema::dropIfExists('product_batches');
        Schema::dropIfExists('product_variants');
        Schema::dropIfExists('order_fees');
        Schema::dropIfExists('fee_configs');
        Schema::dropIfExists('taxes');
        Schema::dropIfExists('discounts');
        Schema::dropIfExists('units');
    }
};
