influence/backend/app/Models/InfluencerPartnershipModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class InfluencerPartnershipModel extends Model
{
    protected $table = 'VENDOR_INFLUENCER_MAPPING';
    protected $primaryKey = 'SEQ';
    protected $useAutoIncrement = true;
    protected $returnType = 'array';
    protected $useSoftDeletes = false;
    
    protected $allowedFields = [
        'VENDOR_SEQ',
        'INFLUENCER_SEQ', 
        'REQUEST_TYPE',
        'REQUEST_MESSAGE',
        'RESPONSE_MESSAGE',
        'REQUESTED_BY',
        'APPROVED_BY',
        'COMMISSION_RATE',
        'SPECIAL_CONDITIONS',
        'EXPIRED_DATE',
        'REQUEST_DATE',
        'RESPONSE_DATE',
        'PARTNERSHIP_START_DATE',
        'PARTNERSHIP_END_DATE',
        'ADD_INFO1',
        'ADD_INFO2',
        'ADD_INFO3',
        'IS_ACT'
    ];
    
    protected $useTimestamps = true;
    protected $createdField = 'REG_DATE';
    protected $updatedField = 'MOD_DATE';
    protected $dateFormat = 'datetime';
    
    protected $validationRules = [
        'VENDOR_SEQ' => 'required|integer',
        'INFLUENCER_SEQ' => 'required|integer',
        'REQUEST_TYPE' => 'required|in_list[INFLUENCER_REQUEST,VENDOR_PROPOSAL,INFLUENCER_REAPPLY]',
        'REQUESTED_BY' => 'required|integer',
        'COMMISSION_RATE' => 'permit_empty|decimal|greater_than_equal_to[0]|less_than_equal_to[100]',
        'IS_ACT' => 'required|in_list[Y,N]'
    ];

    // 히스토리 모델
    protected $statusHistoryModel;
    protected $mappingModel;

    public function __construct()
    {
        parent::__construct();
        $this->statusHistoryModel = new VendorInfluencerStatusHistoryModel();
        $this->mappingModel = new VendorInfluencerMappingModel();
    }
    
    /**
     * 인플루언서의 파트너십 목록 조회
     */
    public function getInfluencerPartnerships($influencerSeq, $filters = [])
    {
        $builder = $this->db->table('VENDOR_INFLUENCER_MAPPING vim');
        $builder->select('
            vim.*,
            vsh.STATUS as CURRENT_STATUS,
            vsh.STATUS_MESSAGE as CURRENT_STATUS_MESSAGE,
            vsh.CHANGED_DATE as STATUS_CHANGED_DATE,
            v.COMPANY_NAME as VENDOR_NAME,
            v.COMPANY_EMAIL as VENDOR_EMAIL,
            v.COMPANY_PHONE as VENDOR_PHONE,
            v.LOGO_IMAGE as VENDOR_LOGO,
            v.CATEGORY as VENDOR_CATEGORY,
            v.REGION as VENDOR_REGION,
            v.DESCRIPTION as VENDOR_DESCRIPTION,
            v.RATING as VENDOR_RATING
        ');
        $builder->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                      'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"', 'left');
        $builder->join('VENDOR_LIST v', 'v.SEQ = vim.VENDOR_SEQ', 'left');
        $builder->where('vim.INFLUENCER_SEQ', $influencerSeq);
        $builder->where('vim.IS_ACT', 'Y');
        
        // 상태 필터
        if (isset($filters['status'])) {
            if (is_array($filters['status'])) {
                $builder->whereIn('vsh.STATUS', $filters['status']);
            } else {
                $builder->where('vsh.STATUS', $filters['status']);
            }
        }
        
        // 요청 타입 필터
        if (isset($filters['request_type'])) {
            $builder->where('vim.REQUEST_TYPE', $filters['request_type']);
        }
        
        // 기간 필터
        if (isset($filters['start_date'])) {
            $builder->where('vim.REG_DATE >=', $filters['start_date']);
        }
        if (isset($filters['end_date'])) {
            $builder->where('vim.REG_DATE <=', $filters['end_date']);
        }
        
        // 벤더사 카테고리 필터
        if (isset($filters['vendor_category'])) {
            $builder->where('v.CATEGORY', $filters['vendor_category']);
        }
        
        // 재승인 요청 필터
        if (isset($filters['is_reapply'])) {
            $builder->where('vim.ADD_INFO1', 'REAPPLY');
        }
        
        $builder->orderBy('vim.REG_DATE', 'DESC');
        
        return $builder;
    }
    
    /**
     * 승인 요청 생성
     */
    public function createApprovalRequest($data)
    {
        // 중복 요청 확인
        $existing = $this->mappingModel->checkExistingPendingRequest(
            $data['VENDOR_SEQ'], 
            $data['INFLUENCER_SEQ']
        );
        
        if ($existing) {
            throw new \Exception('이미 처리 중인 요청이 있습니다.');
        }
        
        $insertData = array_merge($data, [
            'REQUEST_TYPE' => 'INFLUENCER_REQUEST',
            'REQUEST_DATE' => date('Y-m-d H:i:s'),
            'IS_ACT' => 'Y'
        ]);
        
        // mappingModel을 사용하여 insert (콜백 자동 실행)
        return $this->mappingModel->insert($insertData);
    }
    
    /**
     * 재승인 요청 생성
     */
    public function createReapplyRequest($data)
    {
        // 재승인 가능한 파트너십 확인
        $terminated = $this->mappingModel->checkReapplyEligiblePartnership(
            $data['VENDOR_SEQ'],
            $data['INFLUENCER_SEQ']
        );
        
        if (!$terminated) {
            throw new \Exception('해지된 파트너십이 없어 재승인을 요청할 수 없습니다.');
        }
        
        // 이미 재승인 요청 중인지 확인
        $existingReapply = $this->mappingModel->checkExistingPendingRequest(
            $data['VENDOR_SEQ'],
            $data['INFLUENCER_SEQ']
        );
        
        if ($existingReapply) {
            throw new \Exception('이미 재승인 요청이 진행 중입니다.');
        }
        
        $insertData = array_merge($data, [
            'REQUEST_TYPE' => 'INFLUENCER_REAPPLY',
            'REQUEST_DATE' => date('Y-m-d H:i:s'),
            'ADD_INFO1' => 'REAPPLY',
            'ADD_INFO2' => $terminated['SEQ'], // 이전 파트너십 SEQ
            'ADD_INFO3' => date('Y-m-d H:i:s'), // 재신청 일시
            'COMMISSION_RATE' => $data['COMMISSION_RATE'] ?? $terminated['COMMISSION_RATE'],
            'SPECIAL_CONDITIONS' => $data['SPECIAL_CONDITIONS'] ?? $terminated['SPECIAL_CONDITIONS'],
            'IS_ACT' => 'Y'
        ]);
        
        // mappingModel을 사용하여 insert (콜백 자동 실행)
        return $this->mappingModel->insert($insertData);
    }
    
    /**
     * 파트너십 해지 (인플루언서가 해지)
     */
    public function terminateByInfluencer($mappingSeq, $influencerSeq, $reason = '')
    {
        $partnership = $this->mappingModel->getBasicMapping($mappingSeq);
        
        if (!$partnership) {
            throw new \Exception('파트너십을 찾을 수 없습니다.');
        }
        
        if ($partnership['INFLUENCER_SEQ'] != $influencerSeq) {
            throw new \Exception('본인의 파트너십만 해지할 수 있습니다.');
        }
        
        // 현재 상태 확인
        $currentStatus = $this->statusHistoryModel->getCurrentStatus($mappingSeq);
        if (!$currentStatus || $currentStatus['STATUS'] !== 'APPROVED') {
            throw new \Exception('승인된 파트너십만 해지할 수 있습니다.');
        }
        
        // 상태를 TERMINATED로 변경
        $statusResult = $this->statusHistoryModel->changeStatus(
            $mappingSeq, 
            'TERMINATED', 
            $reason, 
            $influencerSeq
        );
        
        // 파트너십 종료일 설정
        $this->update($mappingSeq, [
            'PARTNERSHIP_END_DATE' => date('Y-m-d H:i:s'),
            'ADD_INFO1' => $reason, // 해지 사유
            'ADD_INFO2' => $influencerSeq // 해지 처리자
        ]);
        
        return $statusResult;
    }
    
    /**
     * 인플루언서 통계 조회
     */
    public function getInfluencerStats($influencerSeq)
    {
        $stats = [];
        
        // 전체 파트너십 수
        $stats['total_partnerships'] = $this->where('INFLUENCER_SEQ', $influencerSeq)
            ->where('IS_ACT', 'Y')
            ->countAllResults();
        
        // 상태별 통계는 히스토리 모델에서 조회
        $statusStats = $this->statusHistoryModel->getStatusStatsByInfluencer($influencerSeq);
        $statusCounts = [];
        foreach ($statusStats as $stat) {
            $statusCounts[$stat['STATUS']] = $stat['count'];
        }
        
        $stats['approved_partnerships'] = $statusCounts['APPROVED'] ?? 0;
        $stats['active_partnerships'] = $statusCounts['APPROVED'] ?? 0;
        $stats['terminated_partnerships'] = $statusCounts['TERMINATED'] ?? 0;
        $stats['pending_requests'] = $statusCounts['PENDING'] ?? 0;
        $stats['rejected_requests'] = $statusCounts['REJECTED'] ?? 0;
        
        // 평균 커미션율
        $avgCommission = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('AVG(vim.COMMISSION_RATE) as avg_rate')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->where('vim.INFLUENCER_SEQ', $influencerSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.IS_ACT', 'Y')
            ->get()
            ->getRowArray();
        $stats['avg_commission_rate'] = round($avgCommission['avg_rate'] ?? 0, 2);
        
        // 카테고리별 파트너십 분포
        $stats['category_distribution'] = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('v.CATEGORY, COUNT(*) as count')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->join('VENDOR_LIST v', 'v.SEQ = vim.VENDOR_SEQ', 'left')
            ->where('vim.INFLUENCER_SEQ', $influencerSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.IS_ACT', 'Y')
            ->groupBy('v.CATEGORY')
            ->get()
            ->getResultArray();
        
        // 최근 6개월 월별 파트너십 생성 수
        $stats['monthly_partnerships'] = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('DATE_FORMAT(vim.PARTNERSHIP_START_DATE, "%Y-%m") as month, COUNT(*) as count')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->where('vim.INFLUENCER_SEQ', $influencerSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.PARTNERSHIP_START_DATE >=', date('Y-m-d', strtotime('-6 months')))
            ->where('vim.IS_ACT', 'Y')
            ->groupBy('month')
            ->orderBy('month', 'ASC')
            ->get()
            ->getResultArray();
        
        return $stats;
    }
    
    /**
     * 인플루언서의 현재 활성 파트너십 조회
     */
    public function getActivePartnerships($influencerSeq)
    {
        return $this->getInfluencerPartnerships($influencerSeq, [
            'status' => 'APPROVED'
        ])->get()->getResultArray();
    }
    
    /**
     * 인플루언서의 요청 이력 조회
     */
    public function getRequestHistory($influencerSeq, $limit = 10)
    {
        return $this->getInfluencerPartnerships($influencerSeq)
            ->limit($limit)
            ->get()
            ->getResultArray();
    }
    
    /**
     * 재승인 가능한 벤더사 목록 조회
     */
    public function getReapplyableVendors($influencerSeq)
    {
        return $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('
                DISTINCT v.SEQ, v.COMPANY_NAME, v.LOGO_IMAGE, v.CATEGORY,
                vim.COMMISSION_RATE, vim.SPECIAL_CONDITIONS, vim.PARTNERSHIP_END_DATE
            ')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->join('VENDOR_LIST v', 'v.SEQ = vim.VENDOR_SEQ', 'left')
            ->where('vim.INFLUENCER_SEQ', $influencerSeq)
            ->where('vsh.STATUS', 'TERMINATED')
            ->where('vim.IS_ACT', 'Y')
            ->where('v.IS_ACT', 'Y')
            ->whereNotIn('vim.VENDOR_SEQ', function($builder) use ($influencerSeq) {
                // 현재 재승인 요청 중인 벤더사 제외
                return $builder->select('vim2.VENDOR_SEQ')
                    ->from('VENDOR_INFLUENCER_MAPPING vim2')
                    ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh2', 
                           'vsh2.MAPPING_SEQ = vim2.SEQ AND vsh2.IS_CURRENT = "Y"')
                    ->where('vim2.INFLUENCER_SEQ', $influencerSeq)
                    ->where('vsh2.STATUS', 'PENDING')
                    ->where('vim2.ADD_INFO1', 'REAPPLY')
                    ->where('vim2.IS_ACT', 'Y');
            })
            ->orderBy('vim.PARTNERSHIP_END_DATE', 'DESC')
            ->get()
            ->getResultArray();
    }
    
    /**
     * 파트너십 상세 정보 조회
     */
    public function getPartnershipDetail($mappingSeq, $influencerSeq)
    {
        return $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('
                vim.*,
                vsh.STATUS as CURRENT_STATUS,
                vsh.STATUS_MESSAGE as CURRENT_STATUS_MESSAGE,
                vsh.CHANGED_DATE as STATUS_CHANGED_DATE,
                v.COMPANY_NAME, v.COMPANY_EMAIL, v.COMPANY_PHONE,
                v.LOGO_IMAGE, v.CATEGORY, v.REGION, v.DESCRIPTION,
                v.RATING as VENDOR_RATING,
                u.NICK_NAME as REQUESTED_BY_NAME
            ')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"', 'left')
            ->join('VENDOR_LIST v', 'v.SEQ = vim.VENDOR_SEQ', 'left')
            ->join('USER_LIST u', 'u.SEQ = vim.REQUESTED_BY', 'left')
            ->where('vim.SEQ', $mappingSeq)
            ->where('vim.INFLUENCER_SEQ', $influencerSeq)
            ->where('vim.IS_ACT', 'Y')
            ->first();
    }
}