<?php
// Nowills SQC ERP — QUARRY & PRODUCTION: sites, benches, blasting, extraction, crushing, stockpiles, QC lab
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('units', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('symbol', 20)->unique();
            $table->string('description')->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
        Schema::create('quarries', function (Blueprint $table) {
            $table->id();
            $table->string('code', 30)->unique();
            $table->string('name');
            $table->string('location')->nullable();
            $table->string('region')->default('Greater Accra');
            $table->string('gps_coordinates', 60)->nullable();
            $table->string('licence_no', 60)->nullable();
            $table->date('licence_expiry')->nullable();
            $table->string('epa_permit_no', 60)->nullable();
            $table->date('epa_expiry')->nullable();
            $table->unsignedBigInteger('manager_id')->nullable()->index();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('pit_benches', function (Blueprint $table) {
            $table->id();
            $table->foreignId('quarry_id')->constrained()->cascadeOnDelete();
            $table->string('code', 30);
            $table->string('name');
            $table->decimal('bench_height', 8, 2)->nullable();
            $table->string('status', 20)->default('active');
            $table->timestamps();
            $table->unique(['quarry_id', 'code']);
        });
        Schema::create('product_categories', function (Blueprint $table) {
            $table->id();
            $table->string('code', 20)->unique();
            $table->string('name');
            $table->string('description')->nullable();
            $table->timestamps();
        });
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->foreignId('category_id')->constrained('product_categories');
            $table->string('code', 30)->unique();
            $table->string('name');
            $table->string('description')->nullable();
            $table->foreignId('unit_id')->constrained('units');
            $table->decimal('standard_rate', 12, 2)->default(0);
            $table->decimal('cost_rate', 12, 2)->default(0);
            $table->boolean('taxable')->default(true);
            $table->boolean('is_sellable')->default(true);
            $table->decimal('min_stock_level', 14, 3)->default(0);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('crusher_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('quarry_id')->constrained()->cascadeOnDelete();
            $table->string('code', 30)->unique();
            $table->string('name');
            $table->string('crusher_type', 30)->default('jaw');
            $table->decimal('rated_capacity_tph', 10, 2)->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
        Schema::create('blast_logs', function (Blueprint $table) {
            $table->id();
            $table->string('blast_no', 30)->unique();
            $table->foreignId('quarry_id')->constrained();
            $table->foreignId('bench_id')->nullable()->constrained('pit_benches')->nullOnDelete();
            $table->date('blast_date')->index();
            $table->time('blast_time')->nullable();
            $table->unsignedInteger('holes_drilled')->default(0);
            $table->decimal('total_depth_m', 10, 2)->default(0);
            $table->decimal('explosive_used_kg', 12, 3)->default(0);
            $table->unsignedInteger('detonators_used')->default(0);
            $table->string('blaster_name')->nullable();
            $table->decimal('volume_broken_m3', 12, 3)->default(0);
            $table->decimal('tonnage_broken', 14, 3)->default(0);
            $table->string('weather', 40)->nullable();
            $table->text('remarks')->nullable();
            $table->string('status', 20)->default('planned')->index();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('extraction_logs', function (Blueprint $table) {
            $table->id();
            $table->foreignId('quarry_id')->constrained();
            $table->foreignId('bench_id')->nullable()->constrained('pit_benches')->nullOnDelete();
            $table->date('log_date')->index();
            $table->string('shift', 10)->default('day');
            $table->unsignedBigInteger('equipment_id')->nullable()->index();
            $table->unsignedInteger('dumper_trips')->default(0);
            $table->decimal('tonnage_extracted', 14, 3)->default(0);
            $table->decimal('overburden_m3', 12, 3)->default(0);
            $table->string('operator_name')->nullable();
            $table->text('remarks')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('production_entries', function (Blueprint $table) {
            $table->id();
            $table->string('entry_no', 30)->unique();
            $table->date('entry_date')->index();
            $table->string('shift', 10)->default('day');
            $table->foreignId('quarry_id')->constrained();
            $table->foreignId('crusher_line_id')->constrained();
            $table->foreignId('product_id')->constrained();
            $table->decimal('produced_tonnes', 14, 3)->default(0);
            $table->decimal('operating_hours', 8, 2)->default(0);
            $table->decimal('downtime_hours', 8, 2)->default(0);
            $table->decimal('fuel_used_litres', 12, 3)->default(0);
            $table->string('operator_name')->nullable();
            $table->text('remarks')->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('downtime_logs', function (Blueprint $table) {
            $table->id();
            $table->foreignId('crusher_line_id')->nullable()->constrained()->nullOnDelete();
            $table->unsignedBigInteger('equipment_id')->nullable()->index();
            $table->date('log_date')->index();
            $table->time('start_time')->nullable();
            $table->time('end_time')->nullable();
            $table->decimal('duration_hours', 8, 2)->default(0);
            $table->string('reason_category', 40)->default('mechanical');
            $table->text('description')->nullable();
            $table->string('resolved_by')->nullable();
            $table->timestamps();
        });
        Schema::create('stockpiles', function (Blueprint $table) {
            $table->id();
            $table->foreignId('quarry_id')->constrained();
            $table->foreignId('product_id')->constrained();
            $table->string('code', 30)->unique();
            $table->string('location')->nullable();
            $table->decimal('capacity_tonnes', 14, 3)->nullable();
            $table->decimal('current_qty', 14, 3)->default(0);
            $table->timestamps();
        });
        Schema::create('stockpile_movements', function (Blueprint $table) {
            $table->id();
            $table->date('movement_date')->index();
            $table->foreignId('stockpile_id')->constrained();
            $table->string('movement_type', 30)->index();
            $table->string('ref_type', 60)->nullable();
            $table->unsignedBigInteger('ref_id')->nullable();
            $table->decimal('qty_in', 14, 3)->default(0);
            $table->decimal('qty_out', 14, 3)->default(0);
            $table->decimal('balance_after', 14, 3)->default(0);
            $table->text('remarks')->nullable();
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->index(['ref_type', 'ref_id']);
        });
        Schema::create('qc_tests', function (Blueprint $table) {
            $table->id();
            $table->string('test_no', 30)->unique();
            $table->date('test_date')->index();
            $table->foreignId('quarry_id')->constrained();
            $table->foreignId('product_id')->constrained();
            $table->foreignId('stockpile_id')->nullable()->constrained()->nullOnDelete();
            $table->string('sample_source', 30)->default('stockpile');
            $table->boolean('gradation_pass')->nullable();
            $table->decimal('flakiness_index', 8, 2)->nullable();
            $table->decimal('los_angeles_abrasion', 8, 2)->nullable();
            $table->decimal('aggregate_crushing_value', 8, 2)->nullable();
            $table->decimal('water_absorption', 8, 2)->nullable();
            $table->decimal('silt_content', 8, 2)->nullable();
            $table->string('tested_by')->nullable();
            $table->string('verdict', 20)->default('pending')->index();
            $table->text('remarks')->nullable();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
    }
    public function down(): void {
        Schema::dropIfExists('qc_tests');
        Schema::dropIfExists('stockpile_movements');
        Schema::dropIfExists('stockpiles');
        Schema::dropIfExists('downtime_logs');
        Schema::dropIfExists('production_entries');
        Schema::dropIfExists('extraction_logs');
        Schema::dropIfExists('blast_logs');
        Schema::dropIfExists('crusher_lines');
        Schema::dropIfExists('products');
        Schema::dropIfExists('product_categories');
        Schema::dropIfExists('pit_benches');
        Schema::dropIfExists('quarries');
        Schema::dropIfExists('units');
    }
};
