<?php

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

/**
 * Controlled-drug stocktake / reconciliation.
 *
 * One row per (medicine, batch) physically counted. If variance != 0 the
 * controller inserts a paired `adjust` row into clc_controlled_stock_movements
 * carrying `stocktake_id` so the register always reconciles to physical reality
 * and the daily-seal chain stays unbroken.
 */
class CreateClcControlledReconciliationsTable extends Migration
{
    public function up()
    {
        if (!Schema::hasTable('clc_controlled_reconciliations')) {
            Schema::create('clc_controlled_reconciliations', function (Blueprint $table) {
                $table->increments('id');
                $table->unsignedInteger('business_id')->index();
                $table->string('reference', 30)->unique();          // CR-RC-{biz}-{0000001}
                $table->unsignedInteger('medicine_id')->index();
                $table->string('batch_no', 60)->nullable()->index();
                $table->decimal('expected_qty', 14, 3);
                $table->decimal('counted_qty', 14, 3);
                $table->decimal('variance', 14, 3);                 // counted - expected
                $table->string('unit', 30)->nullable();
                $table->text('notes')->nullable();
                $table->dateTime('stocktake_at');
                $table->unsignedInteger('witnessed_by')->nullable();
                $table->unsignedInteger('movement_id')->nullable(); // back-ref to the adjust movement (if any)
                $table->unsignedInteger('created_by')->nullable();
                $table->timestamps();

                $table->index(['business_id', 'medicine_id']);
            });
        }

        // Back-ref column on movements so an `adjust` row can point to the
        // stocktake that generated it.
        if (Schema::hasTable('clc_controlled_stock_movements')
            && !Schema::hasColumn('clc_controlled_stock_movements', 'stocktake_id'))
        {
            Schema::table('clc_controlled_stock_movements', function (Blueprint $table) {
                $table->unsignedInteger('stocktake_id')->nullable()->index()->after('prescription_id');
            });
        }
    }

    public function down()
    {
        Schema::dropIfExists('clc_controlled_reconciliations');

        if (Schema::hasTable('clc_controlled_stock_movements')
            && Schema::hasColumn('clc_controlled_stock_movements', 'stocktake_id'))
        {
            Schema::table('clc_controlled_stock_movements', function (Blueprint $table) {
                $table->dropColumn('stocktake_id');
            });
        }
    }
}
