influence/backend/app/Models/VendorInfluencerPartnershipModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class VendorInfluencerPartnershipModel extends Model
{
    protected $table = 'VENDOR_INFLUENCER_PARTNERSHIP';
    protected $primaryKey = 'SEQ';
    protected $useAutoIncrement = true;
    protected $returnType = 'array';
    protected $useSoftDeletes = false;
    protected $protectFields = true;
    protected $allowedFields = [
        'VENDOR_SEQ',
        'INFLUENCER_SEQ',
        'STATUS',
        'REQUEST_TYPE',
        'REQUEST_MESSAGE',
        'RESPONSE_MESSAGE',
        'COMMISSION_RATE',
        'SPECIAL_CONDITIONS',
        'REQUESTED_BY',
        'PROCESSED_BY',
        'REQUEST_DATE',
        'RESPONSE_DATE',
        'PARTNERSHIP_START_DATE',
        'PARTNERSHIP_END_DATE',
        'IS_ACTIVE',
        'PARTNERSHIP_CYCLE',
        'PREVIOUS_STATUS',
        'PREVIOUS_END_DATE',
        'CREATED_AT',
        'UPDATED_AT'
    ];

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

    // Validation
    protected $validationRules = [
        'VENDOR_SEQ' => 'required|integer',
        'INFLUENCER_SEQ' => 'required|integer',
        'STATUS' => 'required|in_list[PENDING,APPROVED,REJECTED,TERMINATED]',
        'REQUEST_TYPE' => 'required|in_list[NEW,REAPPLY]',
        'REQUESTED_BY' => 'required|integer',
        'IS_ACTIVE' => 'required|in_list[Y,N]'
    ];

    protected $validationMessages = [
        'VENDOR_SEQ' => [
            'required' => '벤더사 SEQ는 필수입니다.',
            'integer' => '벤더사 SEQ는 정수여야 합니다.'
        ],
        'INFLUENCER_SEQ' => [
            'required' => '인플루언서 SEQ는 필수입니다.',
            'integer' => '인플루언서 SEQ는 정수여야 합니다.'
        ],
        'STATUS' => [
            'required' => '상태는 필수입니다.',
            'in_list' => '유효하지 않은 상태입니다.'
        ],
        'REQUEST_TYPE' => [
            'required' => '요청 타입은 필수입니다.',
            'in_list' => '유효하지 않은 요청 타입입니다.'
        ],
        'REQUESTED_BY' => [
            'required' => '요청자 SEQ는 필수입니다.',
            'integer' => '요청자 SEQ는 정수여야 합니다.'
        ],
        'IS_ACTIVE' => [
            'required' => 'IS_ACTIVE는 필수입니다.',
            'in_list' => 'IS_ACTIVE는 Y 또는 N이어야 합니다.'
        ]
    ];

    protected $skipValidation = true;  // 임시로 validation 비활성화
    protected $cleanValidationRules = true;

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

    /**
     * 삽입 전 처리
     */
    protected function beforeInsert(array $data)
    {
        if (!isset($data['data']['CREATED_AT'])) {
            $data['data']['CREATED_AT'] = date('Y-m-d H:i:s');
        }
        if (!isset($data['data']['UPDATED_AT'])) {
            $data['data']['UPDATED_AT'] = date('Y-m-d H:i:s');
        }
        if (!isset($data['data']['REQUEST_DATE'])) {
            $data['data']['REQUEST_DATE'] = date('Y-m-d H:i:s');
        }
        return $data;
    }

    /**
     * 업데이트 전 처리
     */
    protected function beforeUpdate(array $data)
    {
        $data['data']['UPDATED_AT'] = date('Y-m-d H:i:s');
        return $data;
    }

    /**
     * 벤더사의 인플루언서 요청 목록 조회 (히스토리 포함)
     */
    public function getInfluencerRequestsForVendor($vendorSeq, $filters = [])
    {
        $builder = $this->select('
            VENDOR_INFLUENCER_PARTNERSHIP.*,
            VENDOR_INFLUENCER_PARTNERSHIP.STATUS as CURRENT_STATUS,
            VENDOR_INFLUENCER_PARTNERSHIP.REQUEST_TYPE as ADD_INFO1,
            VENDOR_INFLUENCER_PARTNERSHIP.PARTNERSHIP_END_DATE as ADD_INFO3,
            USER_LIST.NAME as INFLUENCER_NAME,
            USER_LIST.NICK_NAME as INFLUENCER_NICKNAME,
            USER_LIST.EMAIL as INFLUENCER_EMAIL,
            USER_LIST.PHONE as INFLUENCER_PHONE,
            USER_LIST.SNS_TYPE as SNS_TYPE,
            USER_LIST.SNS_LINK_ID as SNS_LINK_ID
        ')
        ->join('USER_LIST', 'USER_LIST.SEQ = VENDOR_INFLUENCER_PARTNERSHIP.INFLUENCER_SEQ', 'left')
        ->where('VENDOR_INFLUENCER_PARTNERSHIP.VENDOR_SEQ', $vendorSeq);

        // 상태별 필터링
        if (!empty($filters['status'])) {
            if ($filters['status'] === 'TERMINATED') {
                $builder->where('VENDOR_INFLUENCER_PARTNERSHIP.STATUS', 'TERMINATED')
                        ->where('VENDOR_INFLUENCER_PARTNERSHIP.IS_ACTIVE', 'N');
            } else {
                $builder->where('VENDOR_INFLUENCER_PARTNERSHIP.STATUS', $filters['status'])
                        ->where('VENDOR_INFLUENCER_PARTNERSHIP.IS_ACTIVE', 'Y');
            }
        } else {
            $builder->where('VENDOR_INFLUENCER_PARTNERSHIP.IS_ACTIVE', 'Y');
        }

        if (!empty($filters['keyword'])) {
            $builder->groupStart()
                   ->like('USER_LIST.NAME', $filters['keyword'])
                   ->orLike('USER_LIST.NICK_NAME', $filters['keyword'])
                   ->orLike('USER_LIST.EMAIL', $filters['keyword'])
                   ->groupEnd();
        }

        $results = $builder->orderBy('VENDOR_INFLUENCER_PARTNERSHIP.CREATED_AT', 'DESC')
                          ->get()
                          ->getResultArray();

        // 각 인플루언서의 파트너십 히스토리 추가
        foreach ($results as &$result) {
            $history = $this->where('VENDOR_SEQ', $vendorSeq)
                ->where('INFLUENCER_SEQ', $result['INFLUENCER_SEQ'])
                ->orderBy('CREATED_AT', 'DESC')
                ->get()
                ->getResultArray();

            $result['partnership_history'] = $history;

            // SNS 채널 정보를 JSON 배열로 변환
            $snsChannels = [];
            if (!empty($result['SNS_TYPE']) && !empty($result['SNS_LINK_ID'])) {
                $snsChannels[] = [
                    'platform' => strtolower($result['SNS_TYPE']),
                    'handle' => $result['SNS_LINK_ID']
                ];
            }
            $result['influencerSnsChannels'] = json_encode($snsChannels);
        }

        return $results;
    }

    /**
     * 인플루언서의 벤더사 검색 (히스토리 포함)
     */
    public function searchVendorsForInfluencer($influencerSeq, $filters = [])
    {
        $builder = $this->db->table('VENDOR_LIST')
            ->select('
                VENDOR_LIST.*,
                VIP.STATUS as PARTNERSHIP_STATUS,
                VIP.REQUEST_TYPE as PARTNERSHIP_REQUEST_TYPE,
                VIP.COMMISSION_RATE as CURRENT_COMMISSION_RATE,
                VIP.SPECIAL_CONDITIONS as CURRENT_SPECIAL_CONDITIONS,
                VIP.CREATED_AT as PARTNERSHIP_DATE,
                VIP.IS_ACTIVE,
                VIP.SEQ as PARTNERSHIP_SEQ
            ')
            ->join('(
                SELECT * FROM VENDOR_INFLUENCER_PARTNERSHIP p1
                WHERE p1.CREATED_AT = (
                    SELECT MAX(p2.CREATED_AT)
                    FROM VENDOR_INFLUENCER_PARTNERSHIP p2
                    WHERE p2.VENDOR_SEQ = p1.VENDOR_SEQ
                    AND p2.INFLUENCER_SEQ = p1.INFLUENCER_SEQ
                )
            ) VIP', 
                "VIP.VENDOR_SEQ = VENDOR_LIST.SEQ AND VIP.INFLUENCER_SEQ = {$influencerSeq}",
                'left'
            )
            ->where('VENDOR_LIST.IS_ACT', 'Y');

        // 필터 적용
        if (!empty($filters['keyword'])) {
            $builder->like('VENDOR_LIST.COMPANY_NAME', $filters['keyword']);
        }

        if (!empty($filters['category'])) {
            $builder->where('VENDOR_LIST.CATEGORY', $filters['category']);
        }

        // 파트너십 상태별 필터링
        if (!empty($filters['status'])) {
            if ($filters['status'] === 'TERMINATED') {
                $builder->where('VIP.STATUS', 'TERMINATED')
                        ->where('VIP.IS_ACTIVE', 'N');
            } else {
                $builder->where('VIP.STATUS', $filters['status'])
                        ->where('VIP.IS_ACTIVE', 'Y');
            }
        }

        $results = $builder->orderBy('VIP.CREATED_AT', 'DESC')
                          ->get()
                          ->getResultArray();

        // 각 벤더사의 파트너십 히스토리 추가
        foreach ($results as &$result) {
            $history = $this->db->table($this->table)
                ->where('VENDOR_SEQ', $result['SEQ'])
                ->where('INFLUENCER_SEQ', $influencerSeq)
                ->orderBy('CREATED_AT', 'DESC')
                ->get()
                ->getResultArray();

            $result['partnership_history'] = $history;
        }

        return $results;
    }

    /**
     * 파트너십 요청 생성
     */
    public function createPartnershipRequest($data)
    {
        $db = \Config\Database::connect();
        $db->transStart();

        $this->deactivateExistingPartnership($data['VENDOR_SEQ'], $data['INFLUENCER_SEQ']);
        $result = $this->insert($data);

        $dbError = $db->error();
        $db->transComplete();

        if (!$result || !empty($dbError['message'])) {
            return [
                'success' => false,
                'db_error' => $dbError,
                'model_errors' => $this->errors()
            ];
        }

        return $result;
    }

    /**
     * 파트너십 승인 처리
     */
    public function approvePartnership($partnershipSeq, $processedBy, $responseMessage = '')
    {
        $updateData = [
            'STATUS' => 'APPROVED',
            'PROCESSED_BY' => $processedBy,
            'RESPONSE_MESSAGE' => $responseMessage,
            'RESPONSE_DATE' => date('Y-m-d H:i:s'),
            'PARTNERSHIP_START_DATE' => date('Y-m-d H:i:s')
        ];

        return $this->update($partnershipSeq, $updateData);
    }

    /**
     * 파트너십 거부 처리
     */
    public function rejectPartnership($partnershipSeq, $processedBy, $responseMessage = '')
    {
        $updateData = [
            'STATUS' => 'REJECTED',
            'PROCESSED_BY' => $processedBy,
            'RESPONSE_MESSAGE' => $responseMessage,
            'RESPONSE_DATE' => date('Y-m-d H:i:s')
        ];

        return $this->update($partnershipSeq, $updateData);
    }

    /**
     * 파트너십 해지 처리
     */
    public function terminatePartnership($mappingSeq, $terminatedBy, $responseMessage = null)
    {
        $db = \Config\Database::connect();
        
        // 현재 상태 확인
        $current = $db->table($this->table)
            ->where('SEQ', $mappingSeq)
            ->get()
            ->getRowArray();

        if (!$current) {
            log_message('error', "Partnership not found: {$mappingSeq}");
            return ['success' => false, 'error' => 'Partnership not found'];
        }

        // APPROVED 상태이고 활성 상태인 경우에만 해지 가능
        if ($current['STATUS'] !== 'APPROVED' || $current['IS_ACTIVE'] !== 'Y') {
            log_message('error', "Invalid status for termination. Current status: {$current['STATUS']}, IS_ACTIVE: {$current['IS_ACTIVE']}");
            return ['success' => false, 'error' => 'Invalid status for termination'];
        }

        $db->transStart();

        try {
            // 해지 처리 실행
            $updateData = [
                'STATUS' => 'TERMINATED',
                'IS_ACTIVE' => 'N',
                'PARTNERSHIP_END_DATE' => date('Y-m-d H:i:s'),
                'RESPONSE_MESSAGE' => $responseMessage,
                'PROCESSED_BY' => $terminatedBy,
                'RESPONSE_DATE' => date('Y-m-d H:i:s'),
                'UPDATED_AT' => date('Y-m-d H:i:s')
            ];

            $success = $db->table($this->table)
                ->where('SEQ', $mappingSeq)
                ->update($updateData);

            if (!$success || $db->affectedRows() !== 1) {
                throw new \Exception('Failed to terminate partnership');
            }

            // 최종 상태 확인
            $final = $db->table($this->table)
                ->where('SEQ', $mappingSeq)
                ->get()
                ->getRowArray();

            if (!$final || $final['STATUS'] !== 'TERMINATED' || $final['IS_ACTIVE'] !== 'N') {
                throw new \Exception('Final state verification failed');
            }

            $db->transComplete();

            return ['success' => true, 'data' => $final];

        } catch (\Exception $e) {
            $db->transRollback();
            log_message('error', "Termination failed: " . $e->getMessage());
            return [
                'success' => false, 
                'error' => $e->getMessage(),
                'debug' => [
                    'expectedStatus' => 'TERMINATED',
                    'expectedIsActive' => 'N',
                    'actualStatus' => $current['STATUS'],
                    'actualIsActive' => $current['IS_ACTIVE'],
                    'error' => $e->getMessage()
                ]
            ];
        }
    }

    /**
     * 기존 파트너십 비활성화 (재신청 시 사용)
     */
    protected function deactivateExistingPartnership($vendorSeq, $influencerSeq)
    {
        $db = \Config\Database::connect();
        
        // 1. 현재 활성 파트너십 찾기
        $activePartnership = $db->table($this->table)
            ->where('VENDOR_SEQ', $vendorSeq)
            ->where('INFLUENCER_SEQ', $influencerSeq)
            ->where('IS_ACTIVE', 'Y')
            ->get()
            ->getRowArray();

        if ($activePartnership) {
            // 2. 상태를 TERMINATED로 변경하고 IS_ACTIVE='N'으로 설정
            $db->table($this->table)
                ->where('SEQ', $activePartnership['SEQ'])
                ->update([
                    'STATUS' => 'TERMINATED',
                    'IS_ACTIVE' => 'N',
                    'PARTNERSHIP_END_DATE' => date('Y-m-d H:i:s')
                ]);
        }

        // 3. 혹시 모를 다른 IS_ACTIVE='Y' 레코드도 'N'으로 변경
        $db->table($this->table)
            ->where('VENDOR_SEQ', $vendorSeq)
            ->where('INFLUENCER_SEQ', $influencerSeq)
            ->where('IS_ACTIVE', 'Y')
            ->update(['IS_ACTIVE' => 'N']);

        // 4. 검증 및 로깅
        $finalCheck = $db->table($this->table)
            ->where('VENDOR_SEQ', $vendorSeq)
            ->where('INFLUENCER_SEQ', $influencerSeq)
            ->where('IS_ACTIVE', 'Y')
            ->countAllResults();

        if ($finalCheck > 0) {
            log_message('error', "Found unexpected active partnerships: vendor={$vendorSeq}, influencer={$influencerSeq}, count={$finalCheck}");
        }
    }

    /**
     * 현재 활성 파트너십 조회
     */
    public function getActivePartnership($vendorSeq, $influencerSeq)
    {
        return $this->where('VENDOR_SEQ', $vendorSeq)
                   ->where('INFLUENCER_SEQ', $influencerSeq)
                   ->where('IS_ACTIVE', 'Y')
                   ->first();
    }

    /**
     * 벤더사 통계 조회
     */
    public function getVendorStats($vendorSeq)
    {
        $result = $this->select('STATUS, COUNT(*) as count')
                      ->where('VENDOR_SEQ', $vendorSeq)
                      ->where('IS_ACTIVE', 'Y')
                      ->groupBy('STATUS')
                      ->get()
                      ->getResultArray();

        $stats = [
            'pending' => 0,
            'approved' => 0,
            'rejected' => 0,
            'terminated' => 0,
            'total' => 0
        ];

        foreach ($result as $row) {
            $status = strtolower($row['STATUS']);
            $stats[$status] = (int)$row['count'];
            $stats['total'] += (int)$row['count'];
        }

        return $stats;
    }

    /**
     * 재승인 요청 생성
     */
    public function createReapplyRequest($vendorSeq, $influencerSeq, $requestMessage, $requestedBy)
    {
        $db = \Config\Database::connect();
        $db->transStart();

        try {
            // 기존 활성 파트너십 비활성화
            $this->deactivateExistingPartnership($vendorSeq, $influencerSeq);

            // 이전 파트너십들의 최대 사이클 번호 조회
            $maxCycle = $db->table($this->table)
                ->selectMax('PARTNERSHIP_CYCLE', 'max_cycle')
                ->where('VENDOR_SEQ', $vendorSeq)
                ->where('INFLUENCER_SEQ', $influencerSeq)
                ->get()
                ->getRowArray();

            $newCycle = ($maxCycle && $maxCycle['max_cycle']) ? $maxCycle['max_cycle'] + 1 : 1;

            // 최근 TERMINATED 파트너십 정보 조회 (이전 상태 정보용)
            $previousPartnership = $db->table($this->table)
                ->where('VENDOR_SEQ', $vendorSeq)
                ->where('INFLUENCER_SEQ', $influencerSeq)
                ->where('STATUS', 'TERMINATED')
                ->where('IS_ACTIVE', 'N')
                ->orderBy('CREATED_AT', 'DESC')
                ->get()
                ->getRowArray();

            // 새로운 재승인 요청 생성
            $insertData = [
                'VENDOR_SEQ' => $vendorSeq,
                'INFLUENCER_SEQ' => $influencerSeq,
                'STATUS' => 'PENDING',
                'REQUEST_TYPE' => 'REAPPLY',
                'REQUEST_MESSAGE' => $requestMessage,
                'REQUESTED_BY' => $requestedBy,
                'REQUEST_DATE' => date('Y-m-d H:i:s'),
                'IS_ACTIVE' => 'Y',
                'PARTNERSHIP_CYCLE' => $newCycle,
                'CREATED_AT' => date('Y-m-d H:i:s'),
                'UPDATED_AT' => date('Y-m-d H:i:s')
            ];

            // 이전 파트너십 정보가 있으면 추가
            if ($previousPartnership) {
                $insertData['PREVIOUS_STATUS'] = $previousPartnership['STATUS'];
                $insertData['PREVIOUS_END_DATE'] = $previousPartnership['PARTNERSHIP_END_DATE'];
            }

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

            if (!$result) {
                throw new \Exception('Failed to create reapply request');
            }

            $db->transComplete();

            return [
                'success' => true,
                'data' => array_merge(['SEQ' => $result], $insertData)
            ];

        } catch (\Exception $e) {
            $db->transRollback();
            log_message('error', "Create reapply request failed: " . $e->getMessage());
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }
}