<?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
    {
        Schema::create('mpesa_transactions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->foreignId('branch_id')->nullable()->constrained('branches');
            $table->foreignId('payment_id')->nullable()->constrained('payments');
            $table->foreignId('order_id')->nullable()->constrained('orders');
            $table->enum('type', ['stk_push', 'c2b', 'b2c', 'b2b'])->default('stk_push');
            $table->string('phone_number', 20);
            $table->decimal('amount', 15, 2);
            $table->string('checkout_request_id', 100)->nullable();
            $table->string('merchant_request_id', 100)->nullable();
            $table->string('mpesa_receipt_number', 50)->nullable();
            $table->dateTime('transaction_date')->nullable();
            $table->string('result_code', 10)->nullable();
            $table->string('result_description', 500)->nullable();
            $table->string('account_reference', 100)->nullable();
            $table->string('transaction_description', 500)->nullable();
            $table->enum('status', ['initiated', 'pending', 'completed', 'failed', 'timeout', 'cancelled'])->default('initiated');
            $table->json('callback_payload')->nullable();
            $table->timestamps();

            $table->index('checkout_request_id', 'idx_mpesa_checkout_req');
            $table->index('mpesa_receipt_number', 'idx_mpesa_receipt');
            $table->index('phone_number', 'idx_mpesa_phone');
            $table->index('status', 'idx_mpesa_status');
            $table->index('company_id', 'idx_mpesa_company');
        });

        Schema::create('returns', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->foreignId('branch_id')->constrained('branches');
            $table->foreignId('original_order_id')->constrained('orders');
            $table->foreignId('return_order_id')->nullable()->constrained('orders');
            $table->foreignId('user_id')->constrained('users');
            $table->foreignId('customer_id')->nullable()->constrained('customers');
            $table->string('return_number', 50);
            $table->enum('reason', ['defective', 'wrong_item', 'customer_change_mind', 'overcharged', 'other']);
            $table->text('reason_notes')->nullable();
            $table->enum('status', ['pending', 'approved', 'rejected', 'completed'])->default('pending');
            $table->enum('refund_method', ['cash', 'mpesa', 'store_credit', 'loyalty_points'])->default('cash');
            $table->decimal('total_refund_amount', 15, 4)->default(0);
            $table->boolean('restocked')->default(true);
            $table->string('device_id', 100)->nullable();
            $table->timestamp('synced_at')->nullable();
            $table->timestamps();

            $table->index('original_order_id', 'idx_returns_original_order');
            $table->index('branch_id', 'idx_returns_branch');
            $table->index('status', 'idx_returns_status');
        });

        Schema::create('return_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('return_id')->constrained('returns')->cascadeOnDelete();
            $table->foreignId('order_item_id')->constrained('order_items');
            $table->foreignId('product_id')->constrained('products');
            $table->foreignId('variant_id')->nullable()->constrained('product_variants');
            $table->decimal('quantity', 15, 4);
            $table->decimal('unit_price', 15, 4);
            $table->decimal('refund_amount', 15, 4);
            $table->enum('condition', ['resellable', 'damaged', 'disposed'])->default('resellable');
            $table->timestamp('created_at')->nullable();

            $table->index('return_id', 'idx_ri_return');
        });

        Schema::create('expense_categories', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->string('name', 100);
            $table->text('description')->nullable();
            $table->timestamps();

            $table->index('company_id', 'idx_ec_company');
        });

        Schema::create('expenses', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->foreignId('branch_id')->constrained('branches');
            $table->foreignId('shift_id')->nullable()->constrained('shifts');
            $table->foreignId('expense_category_id')->nullable()->constrained('expense_categories');
            $table->foreignId('user_id')->constrained('users');
            $table->decimal('amount', 15, 2);
            $table->text('description');
            $table->enum('payment_method', ['cash', 'mpesa', 'card', 'bank_transfer', 'other'])->default('cash');
            $table->string('reference')->nullable();
            $table->string('receipt_url', 500)->nullable();
            $table->date('expense_date');
            $table->boolean('is_approved')->default(false);
            $table->foreignId('approved_by')->nullable()->constrained('users');
            $table->timestamp('approved_at')->nullable();
            $table->string('device_id', 100)->nullable();
            $table->timestamp('synced_at')->nullable();
            $table->timestamps();

            $table->index('branch_id', 'idx_expenses_branch');
            $table->index('expense_date', 'idx_expenses_date');
            $table->index('expense_category_id', 'idx_expenses_category');
        });

        Schema::create('dining_tables', function (Blueprint $table) {
            $table->id();
            $table->foreignId('branch_id')->constrained('branches');
            $table->string('name', 50);
            $table->unsignedTinyInteger('capacity')->default(4);
            $table->enum('status', ['available', 'occupied', 'reserved', 'cleaning'])->default('available');
            $table->foreignId('current_order_id')->nullable()->constrained('orders');
            $table->string('section', 100)->nullable();
            $table->timestamps();

            $table->index('branch_id', 'idx_dt_branch');
            $table->index('status', 'idx_dt_status');
        });

        Schema::create('kitchen_tickets', function (Blueprint $table) {
            $table->id();
            $table->foreignId('order_id')->constrained('orders');
            $table->foreignId('branch_id')->constrained('branches');
            $table->string('ticket_number', 20);
            $table->enum('status', ['pending', 'preparing', 'ready', 'served', 'cancelled'])->default('pending');
            $table->unsignedTinyInteger('priority')->default(5);
            $table->text('notes')->nullable();
            $table->timestamp('started_at')->nullable();
            $table->timestamp('ready_at')->nullable();
            $table->timestamp('served_at')->nullable();
            $table->timestamps();

            $table->index('order_id', 'idx_kt_order');
            $table->index('status', 'idx_kt_status');
            $table->index('branch_id', 'idx_kt_branch');
        });

        Schema::create('kitchen_ticket_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('kitchen_ticket_id')->constrained('kitchen_tickets')->cascadeOnDelete();
            $table->foreignId('order_item_id')->constrained('order_items');
            $table->decimal('quantity', 15, 4);
            $table->enum('status', ['pending', 'preparing', 'ready'])->default('pending');
            $table->text('notes')->nullable();
            $table->timestamps();

            $table->index('kitchen_ticket_id', 'idx_kti_ticket');
        });

        Schema::create('audit_logs', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->nullable()->constrained('companies');
            $table->foreignId('user_id')->nullable()->constrained('users');
            $table->string('action', 100);
            $table->string('auditable_type')->nullable();
            $table->unsignedBigInteger('auditable_id')->nullable();
            $table->json('old_values')->nullable();
            $table->json('new_values')->nullable();
            $table->string('ip_address', 45)->nullable();
            $table->string('user_agent', 500)->nullable();
            $table->string('device_id', 100)->nullable();
            $table->timestamp('created_at')->nullable();

            $table->index('user_id', 'idx_al_user');
            $table->index('action', 'idx_al_action');
            $table->index(['auditable_type', 'auditable_id'], 'idx_al_auditable');
            $table->index('created_at', 'idx_al_created');
            $table->index('company_id', 'idx_al_company');
        });

        Schema::create('sync_queue', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->foreignId('branch_id')->nullable()->constrained('branches');
            $table->string('device_id', 100);
            $table->string('entity_type', 100);
            $table->string('entity_local_id', 100);
            $table->unsignedBigInteger('entity_server_id')->nullable();
            $table->enum('operation', ['create', 'update', 'delete']);
            $table->json('payload');
            $table->enum('status', ['pending', 'processing', 'synced', 'failed'])->default('pending');
            $table->unsignedTinyInteger('attempts')->default(0);
            $table->text('error_message')->nullable();
            $table->timestamp('synced_at')->nullable();
            $table->timestamps();

            $table->index('device_id', 'idx_sq_device');
            $table->index('status', 'idx_sq_status');
            $table->index(['entity_type', 'entity_local_id'], 'idx_sq_entity');
        });

        Schema::create('daily_sales_summary', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->foreignId('branch_id')->constrained('branches');
            $table->date('summary_date');
            $table->unsignedInteger('total_orders')->default(0);
            $table->decimal('total_items_sold', 15, 4)->default(0);
            $table->decimal('gross_sales', 15, 4)->default(0);
            $table->decimal('total_discounts', 15, 4)->default(0);
            $table->decimal('total_taxes', 15, 4)->default(0);
            $table->decimal('net_sales', 15, 4)->default(0);
            $table->decimal('total_cost', 15, 4)->default(0);
            $table->decimal('gross_profit', 15, 4)->default(0);
            $table->decimal('total_returns', 15, 4)->default(0);
            $table->decimal('cash_payments', 15, 4)->default(0);
            $table->decimal('mpesa_payments', 15, 4)->default(0);
            $table->decimal('card_payments', 15, 4)->default(0);
            $table->decimal('other_payments', 15, 4)->default(0);
            $table->decimal('total_expenses', 15, 4)->default(0);
            $table->unsignedInteger('new_customers')->default(0);
            $table->timestamps();

            $table->unique(['branch_id', 'summary_date'], 'uq_dss_branch_date');
            $table->index(['company_id', 'summary_date'], 'idx_dss_company_date');
        });

        Schema::create('daily_product_summary', function (Blueprint $table) {
            $table->id();
            $table->foreignId('company_id')->constrained('companies');
            $table->foreignId('branch_id')->constrained('branches');
            $table->foreignId('product_id')->constrained('products');
            $table->date('summary_date');
            $table->decimal('quantity_sold', 15, 4)->default(0);
            $table->decimal('quantity_returned', 15, 4)->default(0);
            $table->decimal('gross_revenue', 15, 4)->default(0);
            $table->decimal('total_cost', 15, 4)->default(0);
            $table->decimal('gross_profit', 15, 4)->default(0);
            $table->timestamps();

            $table->unique(['branch_id', 'product_id', 'summary_date'], 'uq_dps_branch_product_date');
            $table->index(['company_id', 'summary_date'], 'idx_dps_company_date');
            $table->index('product_id', 'idx_dps_product');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('daily_product_summary');
        Schema::dropIfExists('daily_sales_summary');
        Schema::dropIfExists('sync_queue');
        Schema::dropIfExists('audit_logs');
        Schema::dropIfExists('kitchen_ticket_items');
        Schema::dropIfExists('kitchen_tickets');
        Schema::dropIfExists('dining_tables');
        Schema::dropIfExists('expenses');
        Schema::dropIfExists('expense_categories');
        Schema::dropIfExists('return_items');
        Schema::dropIfExists('returns');
        Schema::dropIfExists('mpesa_transactions');
    }
};
