<?php
// Nowills SQC ERP — PROJECTS (BOQ, WBS, site diary, subcontracts, IPCs, costing) & HR (staff, attendance, leave, payroll)
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('projects', function (Blueprint $table) {
            $table->id();
            $table->string('code', 30)->unique();
            $table->string('name');
            $table->string('client_name')->nullable();
            $table->foreignId('client_id')->nullable()->constrained('customers')->nullOnDelete();
            $table->string('location')->nullable();
            $table->string('region')->nullable();
            $table->string('contract_no', 60)->nullable();
            $table->decimal('contract_value', 15, 2)->default(0);
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->date('actual_end_date')->nullable();
            $table->string('project_manager')->nullable();
            $table->string('status', 20)->default('active')->index();
            $table->decimal('progress_percent', 5, 2)->default(0);
            $table->decimal('retention_percent', 5, 2)->default(5);
            $table->text('description')->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('boq_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained()->cascadeOnDelete();
            $table->string('item_no', 20);
            $table->text('description');
            $table->string('unit', 20)->default('lot');
            $table->decimal('qty', 14, 3)->default(0);
            $table->decimal('rate', 12, 2)->default(0);
            $table->decimal('amount', 15, 2)->default(0);
            $table->decimal('qty_executed', 14, 3)->default(0);
            $table->decimal('amount_executed', 15, 2)->default(0);
            $table->timestamps();
        });
        Schema::create('project_tasks', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained()->cascadeOnDelete();
            $table->foreignId('parent_id')->nullable()->constrained('project_tasks')->nullOnDelete();
            $table->string('wbs_code', 20)->nullable();
            $table->string('task_name');
            $table->string('assigned_to')->nullable();
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->decimal('progress_percent', 5, 2)->default(0);
            $table->decimal('weight_factor', 8, 2)->default(1);
            $table->string('status', 20)->default('not-started')->index();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        Schema::create('site_diaries', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained()->cascadeOnDelete();
            $table->date('diary_date')->index();
            $table->string('weather', 40)->nullable();
            $table->unsignedInteger('labour_count')->default(0);
            $table->text('staff_on_site')->nullable();
            $table->text('equipment_on_site')->nullable();
            $table->text('work_progress')->nullable();
            $table->text('materials_received')->nullable();
            $table->text('visitors')->nullable();
            $table->text('issues_delays')->nullable();
            $table->string('prepared_by')->nullable();
            $table->timestamps();
            $table->unique(['project_id', 'diary_date']);
        });
        Schema::create('subcontracts', function (Blueprint $table) {
            $table->id();
            $table->string('subcontract_no', 30)->unique();
            $table->foreignId('project_id')->constrained()->cascadeOnDelete();
            $table->string('subcontractor_name');
            $table->foreignId('supplier_id')->nullable()->constrained()->nullOnDelete();
            $table->text('scope_of_work')->nullable();
            $table->decimal('contract_amount', 15, 2)->default(0);
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->decimal('retention_percent', 5, 2)->default(5);
            $table->string('status', 20)->default('active')->index();
            $table->timestamps();
        });
        Schema::create('subcontractor_claims', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained();
            $table->foreignId('subcontract_id')->constrained();
            $table->string('claim_no', 30)->unique();
            $table->date('claim_date')->index();
            $table->date('period_from')->nullable();
            $table->date('period_to')->nullable();
            $table->decimal('amount_claimed', 15, 2)->default(0);
            $table->decimal('amount_certified', 15, 2)->default(0);
            $table->decimal('retention_deducted', 15, 2)->default(0);
            $table->decimal('tax_withheld', 15, 2)->default(0);
            $table->decimal('net_payable', 15, 2)->default(0);
            $table->string('status', 20)->default('submitted')->index();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        Schema::create('client_ipcs', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained()->cascadeOnDelete();
            $table->string('ipc_no', 30)->unique();
            $table->date('ipc_date')->index();
            $table->date('period_from')->nullable();
            $table->date('period_to')->nullable();
            $table->decimal('gross_valuation', 15, 2)->default(0);
            $table->decimal('retention_deducted', 15, 2)->default(0);
            $table->decimal('advance_repayment', 15, 2)->default(0);
            $table->decimal('tax_withheld', 15, 2)->default(0);
            $table->decimal('net_certified', 15, 2)->default(0);
            $table->string('status', 20)->default('draft')->index();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        Schema::create('project_expenses', function (Blueprint $table) {
            $table->id();
            $table->foreignId('project_id')->constrained()->cascadeOnDelete();
            $table->date('expense_date')->index();
            $table->string('category', 30)->default('other');
            $table->string('description');
            $table->decimal('amount', 15, 2)->default(0);
            $table->string('ref_type', 60)->nullable();
            $table->unsignedBigInteger('ref_id')->nullable();
            $table->string('incurred_by')->nullable();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('equipment_deployments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('equipment_id')->constrained();
            $table->foreignId('project_id')->nullable()->constrained()->nullOnDelete();
            $table->foreignId('quarry_id')->nullable()->constrained()->nullOnDelete();
            $table->date('deployed_date')->index();
            $table->date('released_date')->nullable();
            $table->decimal('meter_start', 12, 1)->nullable();
            $table->decimal('meter_end', 12, 1)->nullable();
            $table->string('status', 15)->default('deployed')->index();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        // ── HR ──
        Schema::create('departments', function (Blueprint $table) {
            $table->id();
            $table->string('code', 20)->unique();
            $table->string('name')->unique();
            $table->string('head_of_dept')->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
        Schema::create('designations', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->foreignId('department_id')->nullable()->constrained()->nullOnDelete();
            $table->string('grade', 20)->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
        Schema::create('employees', function (Blueprint $table) {
            $table->id();
            $table->string('employee_no', 30)->unique();
            $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('other_names')->nullable();
            $table->string('gender', 10)->nullable();
            $table->date('date_of_birth')->nullable();
            $table->string('national_id', 40)->nullable();
            $table->string('ssnit_no', 40)->nullable();
            $table->string('tin', 40)->nullable();
            $table->string('phone', 30)->nullable();
            $table->string('phone2', 30)->nullable();
            $table->string('email')->nullable();
            $table->string('address')->nullable();
            $table->string('city')->nullable();
            $table->foreignId('department_id')->constrained('departments');
            $table->foreignId('designation_id')->constrained('designations');
            $table->string('employment_type', 20)->default('permanent');
            $table->date('date_employed')->nullable();
            $table->date('date_confirmed')->nullable();
            $table->date('date_exited')->nullable();
            $table->string('status', 20)->default('active')->index();
            $table->decimal('basic_salary', 12, 2)->default(0);
            $table->json('allowances')->nullable();
            $table->string('bank_name')->nullable();
            $table->string('bank_branch')->nullable();
            $table->string('account_no', 40)->nullable();
            $table->string('next_of_kin')->nullable();
            $table->string('nok_phone', 30)->nullable();
            $table->string('photo_path')->nullable();
            $table->foreignId('quarry_id')->nullable()->constrained()->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();
        });
        Schema::create('employee_documents', function (Blueprint $table) {
            $table->id();
            $table->foreignId('employee_id')->constrained()->cascadeOnDelete();
            $table->string('doc_type', 40);
            $table->string('doc_name')->nullable();
            $table->string('file_path')->nullable();
            $table->date('issue_date')->nullable();
            $table->date('expiry_date')->nullable();
            $table->timestamps();
        });
        Schema::create('attendances', function (Blueprint $table) {
            $table->id();
            $table->foreignId('employee_id')->constrained()->cascadeOnDelete();
            $table->date('attendance_date')->index();
            $table->string('shift', 10)->default('day');
            $table->time('check_in')->nullable();
            $table->time('check_out')->nullable();
            $table->decimal('hours_worked', 6, 2)->default(0);
            $table->decimal('overtime_hours', 6, 2)->default(0);
            $table->string('status', 20)->default('present')->index();
            $table->string('remarks')->nullable();
            $table->timestamps();
            $table->unique(['employee_id', 'attendance_date']);
        });
        Schema::create('leave_types', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->unsignedInteger('days_allowed')->default(0);
            $table->boolean('is_paid')->default(true);
            $table->timestamps();
        });
        Schema::create('leave_applications', function (Blueprint $table) {
            $table->id();
            $table->string('leave_no', 30)->unique();
            $table->foreignId('employee_id')->constrained()->cascadeOnDelete();
            $table->foreignId('leave_type_id')->constrained();
            $table->date('from_date');
            $table->date('to_date');
            $table->unsignedInteger('days_requested')->default(0);
            $table->text('reason')->nullable();
            $table->string('covering_officer')->nullable();
            $table->string('status', 20)->default('pending')->index();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('staff_advances', function (Blueprint $table) {
            $table->id();
            $table->string('advance_no', 30)->unique();
            $table->foreignId('employee_id')->constrained()->cascadeOnDelete();
            $table->date('advance_date')->index();
            $table->decimal('amount', 12, 2)->default(0);
            $table->string('purpose')->nullable();
            $table->unsignedInteger('repayment_months')->default(1);
            $table->decimal('monthly_deduction', 12, 2)->default(0);
            $table->decimal('amount_repaid', 12, 2)->default(0);
            $table->decimal('balance', 12, 2)->default(0);
            $table->string('status', 20)->default('pending')->index();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
        });
        Schema::create('payroll_runs', function (Blueprint $table) {
            $table->id();
            $table->string('payroll_no', 30)->unique();
            $table->date('payroll_month')->unique();
            $table->string('status', 20)->default('draft')->index();
            $table->decimal('total_gross', 15, 2)->default(0);
            $table->decimal('total_payee', 15, 2)->default(0);
            $table->decimal('total_ssnit_employee', 15, 2)->default(0);
            $table->decimal('total_ssnit_employer', 15, 2)->default(0);
            $table->decimal('total_deductions', 15, 2)->default(0);
            $table->decimal('total_net', 15, 2)->default(0);
            $table->foreignId('prepared_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('approved_by')->nullable()->constrained('users')->nullOnDelete();
            $table->date('paid_date')->nullable();
            $table->text('remarks')->nullable();
            $table->timestamps();
        });
        Schema::create('payroll_lines', function (Blueprint $table) {
            $table->id();
            $table->foreignId('payroll_run_id')->constrained()->cascadeOnDelete();
            $table->foreignId('employee_id')->constrained();
            $table->decimal('basic_salary', 12, 2)->default(0);
            $table->decimal('allowances_total', 12, 2)->default(0);
            $table->decimal('overtime_pay', 12, 2)->default(0);
            $table->decimal('gross_pay', 12, 2)->default(0);
            $table->decimal('ssnit_employee', 12, 2)->default(0);
            $table->decimal('paye', 12, 2)->default(0);
            $table->decimal('advances_deduction', 12, 2)->default(0);
            $table->decimal('other_deductions', 12, 2)->default(0);
            $table->decimal('total_deductions', 12, 2)->default(0);
            $table->decimal('net_pay', 12, 2)->default(0);
            $table->decimal('ssnit_employer', 12, 2)->default(0);
            $table->timestamps();
        });
    }
    public function down(): void {
        Schema::dropIfExists('payroll_lines');
        Schema::dropIfExists('payroll_runs');
        Schema::dropIfExists('staff_advances');
        Schema::dropIfExists('leave_applications');
        Schema::dropIfExists('leave_types');
        Schema::dropIfExists('attendances');
        Schema::dropIfExists('employee_documents');
        Schema::dropIfExists('employees');
        Schema::dropIfExists('designations');
        Schema::dropIfExists('departments');
        Schema::dropIfExists('equipment_deployments');
        Schema::dropIfExists('project_expenses');
        Schema::dropIfExists('client_ipcs');
        Schema::dropIfExists('subcontractor_claims');
        Schema::dropIfExists('subcontracts');
        Schema::dropIfExists('site_diaries');
        Schema::dropIfExists('project_tasks');
        Schema::dropIfExists('boq_items');
        Schema::dropIfExists('projects');
    }
};
