tinyqc/app/Database/Migrations/2026-01-17-000001_QualityControlSystem.php

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class QualityControlSystem extends Migration
{
    public function up()
    {
        // master_depts
        $this->forge->addField([
            'dept_id' => ['type' => 'INT', 'unsigned' => true, 'auto_increment' => true],
            'dept_name' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('dept_id', true);
        $this->forge->createTable('master_depts');

        // master_controls
        $this->forge->addField([
            'control_id' => ['type' => 'INT', 'unsigned' => true, 'auto_increment' => true],
            'dept_id' => ['type' => 'INT', 'unsigned' => true, 'null' => true],
            'control_name' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'lot' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'producer' => ['type' => 'TEXT', 'null' => true],
            'exp_date' => ['type' => 'DATE', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('control_id', true);
        $this->forge->addForeignKey('dept_id', 'master_depts', 'dept_id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('master_controls');

        // master_tests
        $this->forge->addField([
            'test_id' => ['type' => 'INT', 'unsigned' => true, 'auto_increment' => true],
            'dept_id' => ['type' => 'INT', 'unsigned' => true, 'null' => true],
            'test_code' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'test_name' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'test_unit' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'test_method' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'cva' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'ba' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'tea' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('test_id', true);
        $this->forge->addUniqueKey('test_code');
        $this->forge->addForeignKey('dept_id', 'master_depts', 'dept_id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('master_tests');

        // control_tests
        $this->forge->addField([
            'control_test_id' => ['type' => 'INT', 'unsigned' => true, 'auto_increment' => true],
            'control_id' => ['type' => 'INT', 'unsigned' => true, 'null' => true],
            'test_id' => ['type' => 'INT', 'unsigned' => true, 'null' => true],
            'mean' => ['type' => 'FLOAT', 'null' => true],
            'sd' => ['type' => 'FLOAT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('control_test_id', true);
        $this->forge->addUniqueKey(['control_id', 'test_id']);
        $this->forge->addForeignKey('control_id', 'master_controls', 'control_id', 'SET NULL', 'CASCADE');
        $this->forge->addForeignKey('test_id', 'master_tests', 'test_id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('control_tests');

        // results
        $this->forge->addField([
            'result_id' => ['type' => 'INT', 'unsigned' => true, 'auto_increment' => true],
            'control_id' => ['type' => 'INT', 'unsigned' => true, 'null' => true],
            'test_id' => ['type' => 'INT', 'unsigned' => true, 'null' => true],
            'res_date' => ['type' => 'DATE', 'null' => true],
            'res_value' => ['type' => 'DECIMAL', 'constraint' => '18,4', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('result_id', true);
        $this->forge->addForeignKey('control_id', 'master_controls', 'control_id', 'SET NULL', 'CASCADE');
        $this->forge->addForeignKey('test_id', 'master_tests', 'test_id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('results');

        // test_comments
        $this->forge->addField([
            'test_comment_id' => ['type' => 'INT', 'unsigned' => true, 'auto_increment' => true],
            'test_id' => ['type' => 'INT', 'unsigned' => true],
            'comment_date' => ['type' => 'DATE', 'null' => true],
            'comment_text' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
            'deleted_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('test_comment_id', true);
        $this->forge->addUniqueKey(['test_id', 'comment_date']);
        $this->forge->addForeignKey('test_id', 'master_tests', 'test_id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('test_comments');
    }

    public function down()
    {
        $this->forge->dropTable('test_comments', true);
        $this->forge->dropTable('results', true);
        $this->forge->dropTable('control_tests', true);
        $this->forge->dropTable('master_tests', true);
        $this->forge->dropTable('master_controls', true);
        $this->forge->dropTable('master_depts', true);
    }
}