accounting_dev_v2/application/migrations/20260911000100_finalize_technician_equipment_operations.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

/**
 * Subledger operasional Peralatan Teknisi. Tidak membuat jurnal dan tidak
 * mengubah qty/cost inventory. Barang perusahaan tetap menunjuk item_barcodes;
 * barang customer disimpan pada register non-accounting yang terpisah.
 */
class Migration_Finalize_technician_equipment_operations extends CI_Migration
{
    public function up()
    {
        $this->db->query("CREATE TABLE IF NOT EXISTS customer_equipment_registry(
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,
            owner_customer_id BIGINT UNSIGNED NULL,current_customer_id BIGINT UNSIGNED NULL,
            equipment_name VARCHAR(150) NOT NULL,category VARCHAR(100) NULL,brand VARCHAR(100) NULL,model VARCHAR(100) NULL,
            external_barcode VARCHAR(120) NOT NULL,serial_number VARCHAR(120) NULL,description TEXT NULL,
            ownership_type VARCHAR(40) NOT NULL DEFAULT 'customer',deployment_allowed TINYINT(1) NOT NULL DEFAULT 0,
            authorization_reference VARCHAR(150) NULL,tracking_type VARCHAR(10) NOT NULL DEFAULT 'UNIT',total_qty DECIMAL(18,4) NOT NULL DEFAULT 1,
            custody_type VARCHAR(30) NOT NULL DEFAULT 'customer',custody_id BIGINT UNSIGNED NULL,usage_status VARCHAR(30) NOT NULL DEFAULT 'installed',
            condition_status VARCHAR(30) NOT NULL DEFAULT 'good',lifecycle_status VARCHAR(30) NOT NULL DEFAULT 'active',version INT NOT NULL DEFAULT 1,
            registered_by BIGINT UNSIGNED NULL,registered_at DATETIME NOT NULL,updated_by BIGINT UNSIGNED NULL,updated_at DATETIME NULL,
            UNIQUE KEY uq_customer_equipment_barcode(company_id,external_barcode),KEY idx_customer_equipment_owner(company_id,owner_customer_id,lifecycle_status),
            KEY idx_customer_equipment_location(company_id,custody_type,custody_id,condition_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $this->db->query("CREATE TABLE IF NOT EXISTS technician_equipment_documents(
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,document_no VARCHAR(70) NOT NULL,
            document_type VARCHAR(40) NOT NULL,document_date DATE NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'posted',
            customer_id BIGINT UNSIGNED NULL,technician_id BIGINT UNSIGNED NULL,target_technician_id BIGINT UNSIGNED NULL,warehouse_id BIGINT UNSIGNED NULL,
            work_order_no VARCHAR(100) NULL,reason VARCHAR(255) NULL,notes TEXT NULL,reversal_of_id BIGINT UNSIGNED NULL,
            idempotency_key VARCHAR(120) NOT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,reversed_by BIGINT UNSIGNED NULL,reversed_at DATETIME NULL,
            UNIQUE KEY uq_technician_document_no(company_id,document_no),UNIQUE KEY uq_technician_document_idem(company_id,idempotency_key),
            KEY idx_technician_document(company_id,document_date,document_type,status),KEY idx_technician_document_people(company_id,technician_id,customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $this->db->query("CREATE TABLE IF NOT EXISTS technician_custody_allocations(
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,
            subject_type VARCHAR(30) NOT NULL,subject_id BIGINT UNSIGNED NOT NULL,item_id BIGINT UNSIGNED NULL,barcode_id BIGINT UNSIGNED NULL,
            customer_equipment_id BIGINT UNSIGNED NULL,barcode_snapshot VARCHAR(191) NOT NULL,ownership_type VARCHAR(40) NOT NULL,
            custodian_type VARCHAR(30) NOT NULL,custodian_id BIGINT UNSIGNED NULL,customer_id BIGINT UNSIGNED NULL,
            usage_status VARCHAR(30) NOT NULL,condition_status VARCHAR(30) NOT NULL DEFAULT 'good',qty DECIMAL(18,4) NOT NULL,
            balance_key CHAR(64) NOT NULL,version INT NOT NULL DEFAULT 1,last_document_id BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
            UNIQUE KEY uq_technician_allocation(balance_key),KEY idx_technician_allocation_subject(company_id,subject_type,subject_id),
            KEY idx_technician_allocation_holder(company_id,custodian_type,custodian_id,usage_status),
            KEY idx_technician_allocation_customer(company_id,customer_id,condition_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $this->db->query("CREATE TABLE IF NOT EXISTS technician_equipment_document_lines(
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,document_id BIGINT UNSIGNED NOT NULL,company_id BIGINT UNSIGNED NOT NULL,
            subject_type VARCHAR(30) NOT NULL,subject_id BIGINT UNSIGNED NOT NULL,item_id BIGINT UNSIGNED NULL,barcode_id BIGINT UNSIGNED NULL,
            customer_equipment_id BIGINT UNSIGNED NULL,barcode_snapshot VARCHAR(191) NOT NULL,ownership_type VARCHAR(40) NOT NULL,tracking_type VARCHAR(10) NOT NULL,
            action_type VARCHAR(40) NOT NULL,from_custodian_type VARCHAR(30) NULL,from_custodian_id BIGINT UNSIGNED NULL,
            to_custodian_type VARCHAR(30) NULL,to_custodian_id BIGINT UNSIGNED NULL,customer_id BIGINT UNSIGNED NULL,
            from_usage_status VARCHAR(30) NULL,to_usage_status VARCHAR(30) NULL,from_condition_status VARCHAR(30) NULL,to_condition_status VARCHAR(30) NULL,
            qty DECIMAL(18,4) NOT NULL,item_movement_id BIGINT UNSIGNED NULL,notes TEXT NULL,created_at DATETIME NOT NULL,
            KEY idx_technician_line_document(document_id),KEY idx_technician_line_subject(company_id,subject_type,subject_id),
            KEY idx_technician_line_movement(item_movement_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $this->db->query("CREATE TABLE IF NOT EXISTS technician_equipment_inspections(
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,document_id BIGINT UNSIGNED NOT NULL,
            source_allocation_id BIGINT UNSIGNED NULL,subject_type VARCHAR(30) NOT NULL,subject_id BIGINT UNSIGNED NOT NULL,
            result_status VARCHAR(30) NOT NULL,qty DECIMAL(18,4) NOT NULL,inspection_date DATE NOT NULL,notes TEXT NOT NULL,
            inspected_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,
            KEY idx_technician_inspection_queue(company_id,result_status,inspection_date),KEY idx_technician_inspection_subject(company_id,subject_type,subject_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        if ($this->db->table_exists('item_technician') && $this->db->table_exists('item_barcodes')) {
            $this->db->query("INSERT IGNORE INTO technician_custody_allocations
                (company_id,subject_type,subject_id,item_id,barcode_id,customer_equipment_id,barcode_snapshot,ownership_type,custodian_type,custodian_id,customer_id,usage_status,condition_status,qty,balance_key,version,created_at,updated_at)
                SELECT COALESCE(it.company_id,i.company_id,1),'company_barcode',ib.id,it.item_id,ib.id,NULL,it.barcode,'company','technician',it.user_id,NULL,'carried','good',it.carried_qty,
                       SHA2(CONCAT_WS('|',COALESCE(it.company_id,i.company_id,1),'company_barcode',ib.id,'technician',it.user_id,0,'carried','good'),256),1,COALESCE(it.created_at,NOW()),NOW()
                  FROM item_technician it JOIN item_barcodes ib ON ib.barcode=it.barcode JOIN items i ON i.id=it.item_id
                 WHERE it.status='active' AND it.carried_qty>0");
            $this->db->query("INSERT IGNORE INTO technician_custody_allocations
                (company_id,subject_type,subject_id,item_id,barcode_id,customer_equipment_id,barcode_snapshot,ownership_type,custodian_type,custodian_id,customer_id,usage_status,condition_status,qty,balance_key,version,created_at,updated_at)
                SELECT COALESCE(it.company_id,i.company_id,1),'company_barcode',ib.id,it.item_id,ib.id,NULL,it.barcode,'company','customer',it.customer_id,it.customer_id,'installed','good',it.installed_qty,
                       SHA2(CONCAT_WS('|',COALESCE(it.company_id,i.company_id,1),'company_barcode',ib.id,'customer',COALESCE(it.customer_id,0),COALESCE(it.customer_id,0),'installed','good'),256),1,COALESCE(it.created_at,NOW()),NOW()
                  FROM item_technician it JOIN item_barcodes ib ON ib.barcode=it.barcode JOIN items i ON i.id=it.item_id
                 WHERE it.status='active' AND it.installed_qty>0 AND it.customer_id IS NOT NULL");
            $this->db->query("UPDATE item_barcodes ib JOIN(SELECT barcode_id,SUM(qty) qty FROM technician_custody_allocations WHERE subject_type='company_barcode' GROUP BY barcode_id)x ON x.barcode_id=ib.id SET ib.reserved_qty=LEAST(ib.qty_sisa,GREATEST(ib.reserved_qty,x.qty)),ib.status=CASE WHEN ib.qty_sisa-GREATEST(ib.reserved_qty,x.qty)>.0001 THEN 'available' ELSE 'installed' END,ib.version=ib.version+1 WHERE ib.qty_sisa>0");
        }

        if ($this->db->table_exists('roles')) {
            $roles=$this->db->get('roles')->result();
            $features=array('technician_equipment','technician_handover','technician_installation','technician_transfer','technician_return','technician_inspection','technician_correction');
            foreach($roles as$role){$permissions=json_decode((string)$role->permissions,true);if(!is_array($permissions)||!isset($permissions['items']))continue;foreach($features as$feature)if(!isset($permissions[$feature]))$permissions[$feature]=$permissions['items'];$this->db->where('id',$role->id)->update('roles',array('permissions'=>json_encode($permissions),'updated_at'=>date('Y-m-d H:i:s')));}
        }
    }

    public function down()
    {
        throw new RuntimeException('Gunakan backup untuk rollback subledger Peralatan Teknisi.');
    }
}