influence/backend/app/Models/VendorInfluencerStatusHistoryModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class VendorInfluencerStatusHistoryModel extends Model
{
    protected $table = 'VENDOR_INFLUENCER_STATUS_HISTORY';
    protected $primaryKey = 'SEQ';
    protected $useAutoIncrement = true;
    protected $returnType = 'array';
    protected $useSoftDeletes = false;
    protected $protectFields = true;
    protected $allowedFields = [
        'MAPPING_SEQ',
        'STATUS',
        'PREVIOUS_STATUS',
        'STATUS_MESSAGE',
        'CHANGED_BY',
        'CHANGED_DATE',
        'IS_CURRENT',
        'REG_DATE'
    ];

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat = 'datetime';
    protected $createdField = 'REG_DATE';
    protected $updatedField = '';
    protected $deletedField = '';

    // Validation
    protected $validationRules = [
        'MAPPING_SEQ' => 'required|integer',
        'STATUS' => 'required|in_list[PENDING,APPROVED,REJECTED,CANCELLED,EXPIRED,TERMINATED]',
        'CHANGED_BY' => 'required|integer',
        'IS_CURRENT' => 'required|in_list[Y,N]'
    ];

    protected $validationMessages = [
        'MAPPING_SEQ' => [
            'required' => '매핑 SEQ는 필수입니다.',
            'integer' => '매핑 SEQ는 정수여야 합니다.'
        ],
        'STATUS' => [
            'required' => '상태는 필수입니다.',
            'in_list' => '유효하지 않은 상태입니다.'
        ],
        'CHANGED_BY' => [
            'required' => '변경자는 필수입니다.',
            'integer' => '변경자 SEQ는 정수여야 합니다.'
        ]
    ];

    protected $skipValidation = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert = ['beforeInsert'];
    protected $afterInsert = [];
    protected $beforeUpdate = [];
    protected $afterUpdate = [];
    protected $beforeFind = [];
    protected $afterFind = [];
    protected $beforeDelete = [];
    protected $afterDelete = [];

    /**
     * 상태 변경 전 처리
     */
    protected function beforeInsert(array $data)
    {
        // REG_DATE 자동 설정
        if (!isset($data['data']['REG_DATE'])) {
            $data['data']['REG_DATE'] = date('Y-m-d H:i:s');
        }
        
        // CHANGED_DATE 자동 설정
        if (!isset($data['data']['CHANGED_DATE'])) {
            $data['data']['CHANGED_DATE'] = date('Y-m-d H:i:s');
        }

        return $data;
    }

    /**
     * 특정 매핑의 현재 상태 조회
     */
    public function getCurrentStatus($mappingSeq)
    {
        return $this->where('MAPPING_SEQ', $mappingSeq)
                   ->where('IS_CURRENT', 'Y')
                   ->first();
    }

    /**
     * 특정 매핑의 상태 히스토리 조회
     */
    public function getStatusHistory($mappingSeq, $limit = 10)
    {
        return $this->where('MAPPING_SEQ', $mappingSeq)
                   ->orderBy('CHANGED_DATE', 'DESC')
                   ->limit($limit)
                   ->findAll();
    }

    /**
     * 상태 변경 (트랜잭션 포함)
     */
    public function changeStatus($mappingSeq, $newStatus, $statusMessage = '', $changedBy = null)
    {
        $db = \Config\Database::connect();
        $db->transStart();

        try {
            // 1. 현재 상태 조회
            $currentStatus = $this->getCurrentStatus($mappingSeq);
            $previousStatus = $currentStatus ? $currentStatus['STATUS'] : null;

            // 2. 기존 현재 상태를 이전 상태로 변경
            if ($currentStatus) {
                $this->update($currentStatus['SEQ'], ['IS_CURRENT' => 'N']);
            }

            // 3. 새로운 상태 히스토리 추가
            $historyData = [
                'MAPPING_SEQ' => $mappingSeq,
                'STATUS' => $newStatus,
                'PREVIOUS_STATUS' => $previousStatus,
                'STATUS_MESSAGE' => $statusMessage,
                'CHANGED_BY' => $changedBy,
                'IS_CURRENT' => 'Y'
            ];

            $result = $this->insert($historyData);

            // 4. 메인 테이블의 MOD_DATE 업데이트
            $mappingModel = new VendorInfluencerMappingModel();
            $mappingModel->update($mappingSeq, ['MOD_DATE' => date('Y-m-d H:i:s')]);

            $db->transComplete();

            if ($db->transStatus() === false) {
                throw new \Exception('상태 변경 트랜잭션 실패');
            }

            return $result;

        } catch (\Exception $e) {
            $db->transRollback();
            log_message('error', '상태 변경 실패: ' . $e->getMessage());
            throw $e;
        }
    }

    /**
     * 특정 상태의 매핑 목록 조회
     */
    public function getMappingsByStatus($status, $isActive = true)
    {
        $builder = $this->builder();
        $builder->select('VENDOR_INFLUENCER_STATUS_HISTORY.*, VENDOR_INFLUENCER_MAPPING.*')
                ->join('VENDOR_INFLUENCER_MAPPING', 
                       'VENDOR_INFLUENCER_MAPPING.SEQ = VENDOR_INFLUENCER_STATUS_HISTORY.MAPPING_SEQ')
                ->where('VENDOR_INFLUENCER_STATUS_HISTORY.STATUS', $status)
                ->where('VENDOR_INFLUENCER_STATUS_HISTORY.IS_CURRENT', 'Y');
        
        if ($isActive) {
            $builder->where('VENDOR_INFLUENCER_MAPPING.IS_ACT', 'Y');
        }

        return $builder->get()->getResultArray();
    }

    /**
     * 벤더사별 상태 통계
     */
    public function getStatusStatsByVendor($vendorSeq)
    {
        $builder = $this->builder();
        return $builder->select('VENDOR_INFLUENCER_STATUS_HISTORY.STATUS, COUNT(*) as count')
                      ->join('VENDOR_INFLUENCER_MAPPING', 
                             'VENDOR_INFLUENCER_MAPPING.SEQ = VENDOR_INFLUENCER_STATUS_HISTORY.MAPPING_SEQ')
                      ->where('VENDOR_INFLUENCER_MAPPING.VENDOR_SEQ', $vendorSeq)
                      ->where('VENDOR_INFLUENCER_STATUS_HISTORY.IS_CURRENT', 'Y')
                      ->where('VENDOR_INFLUENCER_MAPPING.IS_ACT', 'Y')
                      ->groupBy('VENDOR_INFLUENCER_STATUS_HISTORY.STATUS')
                      ->get()
                      ->getResultArray();
    }

    /**
     * 인플루언서별 상태 통계
     */
    public function getStatusStatsByInfluencer($influencerSeq)
    {
        $builder = $this->builder();
        return $builder->select('VENDOR_INFLUENCER_STATUS_HISTORY.STATUS, COUNT(*) as count')
                      ->join('VENDOR_INFLUENCER_MAPPING', 
                             'VENDOR_INFLUENCER_MAPPING.SEQ = VENDOR_INFLUENCER_STATUS_HISTORY.MAPPING_SEQ')
                      ->where('VENDOR_INFLUENCER_MAPPING.INFLUENCER_SEQ', $influencerSeq)
                      ->where('VENDOR_INFLUENCER_STATUS_HISTORY.IS_CURRENT', 'Y')
                      ->where('VENDOR_INFLUENCER_MAPPING.IS_ACT', 'Y')
                      ->groupBy('VENDOR_INFLUENCER_STATUS_HISTORY.STATUS')
                      ->get()
                      ->getResultArray();
    }
}