<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        $sql = <<<'SQL'
            CREATE TABLE pump_testing_results (
                id                      BIGINT GENERATED always AS IDENTITY PRIMARY KEY,

                plant_id                BIGINT NOT NULL,
                pump_testing_master_id  BIGINT NOT NULL,

                tested_date             DATE NOT NULL DEFAULT CURRENT_DATE,
                tested_type             TEXT DEFAULT NULL,
                pump_serial_number      TEXT DEFAULT NULL,
                correction_head         TEXT DEFAULT NULL,
                sl_no                   TEXT DEFAULT NULL,
                voltage                 TEXT DEFAULT NULL,
                current                 TEXT DEFAULT NULL,
                watt                    TEXT DEFAULT NULL,
                frequency               TEXT DEFAULT NULL,
                speed                   TEXT DEFAULT NULL,
                discharge               TEXT DEFAULT NULL,
                head                    TEXT DEFAULT NULL,
                pump_output             TEXT DEFAULT NULL,
                power_p2                TEXT DEFAULT NULL,
                overall_efficiency      TEXT DEFAULT NULL,
                pump_efficiency         TEXT DEFAULT NULL,

                created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
                updated_at              TIMESTAMP NOT NULL DEFAULT NOW(),
                deleted_at              TIMESTAMP,

                created_by              TEXT NOT NULL,
                updated_by              TEXT NOT NULL,

                UNIQUE (plant_id, pump_testing_master_id, tested_type, pump_serial_number, sl_no),

                FOREIGN KEY (pump_testing_master_id) REFERENCES pump_testing_masters (id),
                FOREIGN KEY (plant_id)  REFERENCES  plants (id)
            );
        SQL;

        DB::statement($sql);
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('pump_testing_results');
    }
};
