<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;
use CodeIgniter\Database\Config;

class Ownership extends Migration
{
    public function up()
    {
        
        // $db->query('DROP TABLE IF EXISTS migrations');

        // Define the schema for the 'email_settings' table
        $this->forge->addField([
            'own_uid' => [
                'type'       => 'VARCHAR',
                'constraint' => 50,
                'null'       => false,
                // 'unsigned'       => true,
                // 'auto_increment' => true,
            ],
            'own_inv_uid' => [
                'type'       => 'VARCHAR',
                'constraint' => 30,
                'null'       => false,
            ],
            'own_usr_uid' => [
                'type'       => 'VARCHAR',
                'constraint' => 30,
                'null'       => false,
            ],
            'own_assign_date' => [
                'type'       => 'DATE',
            ],
            'own_assign_user' => [
                'type'       => 'VARCHAR',
                'constraint' => 30,
                'null'       => false,
            ],
            'own_assign_remarks' => [
                'type'       => 'TEXT',
                // 'null'       => false,
            ],
            'own_return_date' => [
                'type'       => 'DATE',
            ],
            'own_return_user' => [
                'type'       => 'VARCHAR',
                'constraint' => 30,
                'null'       => false,
            ],
            'own_return_remarks' => [
                'type'       => 'TEXT',
                // 'null'       => false,
            ],
            'own_update' => [
                'type'    => 'TIMESTAMP',
                'null'    => false,
            ],

        ]);

        // Add a primary key
        $this->forge->addPrimaryKey('own_uid');

        // Set the table's charset and collation
        $this->forge->createTable('tbl_ownership', true, [
            'ENGINE' => 'InnoDB',
            'DEFAULT CHARSET' => 'utf8mb4',
            'COLLATE' => 'utf8mb4_general_ci',
        ]);



        $db = \Config\Database::connect();

        // Step 1: Extract inv_owner from inv_remarks (value starting with '00')
        $db->query("
            UPDATE tbl_inventory
            SET inv_owner = 
              TRIM(
                SUBSTRING_INDEX(
                  SUBSTRING(inv_remarks, LOCATE('00', inv_remarks)),
                  ' ',
                  1
                )
              )
            WHERE inv_remarks LIKE '%00%'
        ");

        // Step 2: Convert inv_owner to usr_uid if match found, or set blank if null
        $db->query("
            UPDATE tbl_inventory AS i
            LEFT JOIN tbl_user AS u ON i.inv_owner = u.usr_emp_id
            SET i.inv_owner = CASE
                WHEN u.usr_uid IS NOT NULL THEN u.usr_uid
                WHEN i.inv_owner IS NULL THEN ''
                ELSE i.inv_owner
            END
        ");

        
    }

    public function down()
    {
        // Optional: Revert the inv_owner changes, e.g., set to NULL
        $db = \Config\Database::connect();
        $db->query("UPDATE tbl_inventory SET inv_owner = NULL");
    }
}
