<?php
// Nowills SQC ERP — SALES & DISPATCH: customers, quotations, orders, weighbridge, delivery, invoicing, receipts
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('transporters', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('contact_person')->nullable();
            $table->string('phone', 30)->nullable();
            $table->string('vehicle_no', 30)->nullable();
            $table->decimal('rate_per_tonne', 10, 2)->default(0);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
        Schema::create('price_lists', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('currency', 10)->default('GHS');
            $table->date('effective_from');
            $table->date('effective_to')->nullable();
            $table->boolean('is_default')->default(false);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
        Schema::create('price_list_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('price_list_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->constrained();
            $table->decimal('rate_per_tonne', 12, 2)->default(0);
            $table->decimal('min_qty', 12, 3)->default(0);
            $table->timestamps();
            $table->unique(['price_list_id', 'product_id']);
        });
        Schema::create('customers', function (Blueprint $table) {
            $table->id();
            $table->string('code', 30)->unique();
            $table->string('name');
            $table->string('customer_type', 20)->default('company');
            $table->string('contact_person')->nullable();
            $table->string('phone', 30)->nullable();
            $table->string('phone2', 30)->nullable();
            $table->string('email')->nullable();
            $table->string('address')->nullable();
            $table->string('city')->nullable();
            $table->string('region')->nullable();
            $table->string('tin', 40)->nullable();
            $table->foreignId('price_list_id')->nullable()->constrained('price_lists')->nullOnDelete();
            $table->decimal('credit_limit', 15, 2)->default(0);
            $table->unsignedInteger('payment_terms_days')->default(30);
            $table->decimal('opening_balance', 15, 2)->default(0);
            $table->decimal('current_balance', 15, 2)->default(0);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('quotations', function (Blueprint $table) {
            $table->id();
            $table->string('quotation_no', 30)->unique();
            $table->date('quotation_date')->index();
            $table->date('valid_until')->nullable();
            $table->foreignId('customer_id')->constrained();
            $table->foreignId('price_list_id')->nullable()->constrained('price_lists')->nullOnDelete();
            $table->foreignId('quarry_id')->nullable()->constrained()->nullOnDelete();
            $table->string('subject')->nullable();
            $table->decimal('subtotal', 15, 2)->default(0);
            $table->decimal('discount_amount', 15, 2)->default(0);
            $table->decimal('levy_nhil', 15, 2)->default(0);
            $table->decimal('levy_getfund', 15, 2)->default(0);
            $table->decimal('levy_covid', 15, 2)->default(0);
            $table->decimal('vat_amount', 15, 2)->default(0);
            $table->decimal('grand_total', 15, 2)->default(0);
            $table->string('status', 20)->default('draft')->index();
            $table->text('terms')->nullable();
            $table->foreignId('prepared_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('quotation_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('quotation_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->constrained();
            $table->string('description')->nullable();
            $table->decimal('qty_tonnes', 14, 3)->default(0);
            $table->decimal('rate', 12, 2)->default(0);
            $table->decimal('discount', 12, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
        });
        Schema::create('sales_orders', function (Blueprint $table) {
            $table->id();
            $table->string('order_no', 30)->unique();
            $table->date('order_date')->index();
            $table->foreignId('customer_id')->constrained();
            $table->foreignId('quotation_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('quarry_id')->nullable()->constrained()->nullOnDelete();
            $table->string('delivery_address')->nullable();
            $table->date('required_date')->nullable();
            $table->decimal('subtotal', 15, 2)->default(0);
            $table->decimal('discount_amount', 15, 2)->default(0);
            $table->decimal('tax_amount', 15, 2)->default(0);
            $table->decimal('grand_total', 15, 2)->default(0);
            $table->decimal('advance_received', 15, 2)->default(0);
            $table->string('status', 25)->default('draft')->index();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('sales_order_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('order_id')->constrained('sales_orders')->cascadeOnDelete();
            $table->foreignId('product_id')->constrained();
            $table->decimal('qty_ordered', 14, 3)->default(0);
            $table->decimal('qty_dispatched', 14, 3)->default(0);
            $table->decimal('rate', 12, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
        });
        Schema::create('weighbridge_tickets', function (Blueprint $table) {
            $table->id();
            $table->string('ticket_no', 30)->unique();
            $table->date('ticket_date')->index();
            $table->string('vehicle_no', 30)->index();
            $table->string('driver_name')->nullable();
            $table->foreignId('transporter_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('customer_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedBigInteger('supplier_id')->nullable()->index();
            $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
            $table->string('transaction_type', 25)->default('sales-dispatch');
            $table->decimal('gross_weight', 12, 3)->default(0);
            $table->decimal('tare_weight', 12, 3)->default(0);
            $table->decimal('net_weight', 12, 3)->default(0);
            $table->foreignId('sales_order_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedBigInteger('delivery_note_id')->nullable()->index();
            $table->timestamp('first_weight_time')->nullable();
            $table->timestamp('second_weight_time')->nullable();
            $table->foreignId('operator_id')->nullable()->constrained('users')->nullOnDelete();
            $table->string('status', 15)->default('open')->index();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        Schema::create('delivery_notes', function (Blueprint $table) {
            $table->id();
            $table->string('dn_no', 30)->unique();
            $table->date('dn_date')->index();
            $table->foreignId('customer_id')->constrained();
            $table->foreignId('sales_order_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('transporter_id')->nullable()->constrained()->nullOnDelete();
            $table->string('vehicle_no', 30)->nullable();
            $table->string('driver_name')->nullable();
            $table->foreignId('quarry_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('weighbridge_ticket_id')->nullable()->constrained()->nullOnDelete();
            $table->string('gate_pass_no', 30)->nullable()->unique();
            $table->decimal('subtotal', 15, 2)->default(0);
            $table->decimal('tax_amount', 15, 2)->default(0);
            $table->decimal('grand_total', 15, 2)->default(0);
            $table->string('status', 20)->default('draft')->index();
            $table->string('dispatched_by')->nullable();
            $table->timestamps();
        });
        Schema::create('delivery_note_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('delivery_note_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->constrained();
            $table->foreignId('stockpile_id')->nullable()->constrained()->nullOnDelete();
            $table->decimal('qty_tonnes', 14, 3)->default(0);
            $table->decimal('rate', 12, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
        });
        Schema::create('sales_invoices', function (Blueprint $table) {
            $table->id();
            $table->string('invoice_no', 30)->unique();
            $table->date('invoice_date')->index();
            $table->date('due_date')->nullable()->index();
            $table->foreignId('customer_id')->constrained();
            $table->foreignId('sales_order_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('delivery_note_id')->nullable()->constrained()->nullOnDelete();
            $table->decimal('subtotal', 15, 2)->default(0);
            $table->decimal('discount_amount', 15, 2)->default(0);
            $table->decimal('levy_nhil', 15, 2)->default(0);
            $table->decimal('levy_getfund', 15, 2)->default(0);
            $table->decimal('levy_covid', 15, 2)->default(0);
            $table->decimal('vat_amount', 15, 2)->default(0);
            $table->decimal('grand_total', 15, 2)->default(0);
            $table->decimal('amount_paid', 15, 2)->default(0);
            $table->decimal('balance_due', 15, 2)->default(0);
            $table->string('status', 20)->default('draft')->index();
            $table->unsignedBigInteger('journal_id')->nullable()->index();
            $table->text('terms')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('sales_invoice_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('invoice_id')->constrained('sales_invoices')->cascadeOnDelete();
            $table->foreignId('product_id')->nullable()->constrained()->nullOnDelete();
            $table->string('description');
            $table->decimal('qty', 14, 3)->default(0);
            $table->foreignId('unit_id')->nullable()->constrained('units')->nullOnDelete();
            $table->decimal('rate', 12, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
        });
        Schema::create('customer_payments', function (Blueprint $table) {
            $table->id();
            $table->string('receipt_no', 30)->unique();
            $table->date('payment_date')->index();
            $table->foreignId('customer_id')->constrained();
            $table->foreignId('invoice_id')->nullable()->constrained('sales_invoices')->nullOnDelete();
            $table->decimal('amount', 15, 2)->default(0);
            $table->string('payment_mode', 25)->default('bank-transfer');
            $table->unsignedBigInteger('bank_account_id')->nullable()->index();
            $table->string('cheque_no', 40)->nullable();
            $table->string('reference', 80)->nullable();
            $table->string('received_by')->nullable();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        Schema::create('sales_credit_notes', function (Blueprint $table) {
            $table->id();
            $table->string('cn_no', 30)->unique();
            $table->date('cn_date')->index();
            $table->foreignId('customer_id')->constrained();
            $table->foreignId('invoice_id')->nullable()->constrained('sales_invoices')->nullOnDelete();
            $table->decimal('amount', 15, 2)->default(0);
            $table->string('reason')->nullable();
            $table->string('status', 20)->default('draft')->index();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('sales_credit_note_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('credit_note_id')->constrained('sales_credit_notes')->cascadeOnDelete();
            $table->string('description');
            $table->decimal('qty', 14, 3)->default(0);
            $table->decimal('rate', 12, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
        });
    }
    public function down(): void {
        Schema::dropIfExists('sales_credit_note_lines');
        Schema::dropIfExists('sales_credit_notes');
        Schema::dropIfExists('customer_payments');
        Schema::dropIfExists('sales_invoice_lines');
        Schema::dropIfExists('sales_invoices');
        Schema::dropIfExists('delivery_note_lines');
        Schema::dropIfExists('delivery_notes');
        Schema::dropIfExists('weighbridge_tickets');
        Schema::dropIfExists('sales_order_lines');
        Schema::dropIfExists('sales_orders');
        Schema::dropIfExists('quotation_lines');
        Schema::dropIfExists('quotations');
        Schema::dropIfExists('customers');
        Schema::dropIfExists('price_list_lines');
        Schema::dropIfExists('price_lists');
        Schema::dropIfExists('transporters');
    }
};
