influence/backend/app/Models/VendorPartnershipModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class VendorPartnershipModel 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 getVendorRequestsWithPagination($vendorSeq, $page = 1, $size = 20, $status = null)
    {
        $filters = [];
        if ($status) {
            $filters['status'] = $status;
        }

        $builder = $this->getVendorRequests($vendorSeq, $filters);
        
        // 전체 개수 계산
        $totalBuilder = clone $builder;
        $total = $totalBuilder->countAllResults();
        
        // 페이지네이션 적용
        $offset = ($page - 1) * $size;
        $builder->limit($size, $offset);
        
        $data = $builder->get()->getResultArray();
        
        return [
            'data' => $data,
            'pagination' => [
                'total' => $total,
                'currentPage' => $page,
                'totalPages' => ceil($total / $size),
                'limit' => $size,
                'offset' => $offset
            ]
        ];
    }

    /**
     * 벤더사의 인플루언서 요청 목록 조회
     */
    public function getVendorRequests($vendorSeq, $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,
            u.NICK_NAME as INFLUENCER_NAME,
            u.NAME as INFLUENCER_REAL_NAME,
            u.EMAIL as INFLUENCER_EMAIL,
            u.PHONE as INFLUENCER_PHONE,
            u.PROFILE_IMAGE,
            u.FOLLOWER_COUNT,
            u.ENGAGEMENT_RATE,
            u.PRIMARY_CATEGORY,
            u.INFLUENCER_TYPE,
            u.REGION as INFLUENCER_REGION,
            u.DESCRIPTION as INFLUENCER_DESCRIPTION,
            u.RATING as INFLUENCER_RATING,
            u.VERIFICATION_STATUS
        ');
        $builder->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                      'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"', 'left');
        $builder->join('USER_LIST u', 'u.SEQ = vim.INFLUENCER_SEQ', 'left');
        $builder->where('vim.VENDOR_SEQ', $vendorSeq);
        $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['influencer_type'])) {
            $builder->where('u.INFLUENCER_TYPE', $filters['influencer_type']);
        }
        
        // 카테고리 필터
        if (isset($filters['category'])) {
            $builder->where('u.PRIMARY_CATEGORY', $filters['category']);
        }
        
        // 팔로워 수 필터
        if (isset($filters['min_followers'])) {
            $builder->where('u.FOLLOWER_COUNT >=', $filters['min_followers']);
        }
        if (isset($filters['max_followers'])) {
            $builder->where('u.FOLLOWER_COUNT <=', $filters['max_followers']);
        }
        
        // 기간 필터
        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['verification_status'])) {
            $builder->where('u.VERIFICATION_STATUS', $filters['verification_status']);
        }
        
        // 재승인 요청 필터
        if (isset($filters['is_reapply'])) {
            $builder->where('vim.ADD_INFO1', 'REAPPLY');
        }
        
        $builder->orderBy('vim.REG_DATE', 'DESC');
        
        return $builder;
    }
    
    /**
     * 요청 승인/거부 처리
     */
    public function processRequest($mappingSeq, $action, $processedBy, $responseMessage = '')
    {
        $partnership = $this->mappingModel->getBasicMapping($mappingSeq);
        
        if (!$partnership) {
            throw new \Exception('요청을 찾을 수 없습니다.');
        }
        
        // 현재 상태 확인
        $currentStatus = $this->statusHistoryModel->getCurrentStatus($mappingSeq);
        if (!$currentStatus || $currentStatus['STATUS'] !== 'PENDING') {
            throw new \Exception('이미 처리된 요청입니다.');
        }
        
        $newStatus = ($action === 'approve') ? 'APPROVED' : 'REJECTED';
        
        // 상태 변경
        $statusResult = $this->statusHistoryModel->changeStatus(
            $mappingSeq,
            $newStatus,
            $responseMessage,
            $processedBy
        );
        
        $updateData = [
            'RESPONSE_MESSAGE' => $responseMessage,
            'APPROVED_BY' => $processedBy,
            'RESPONSE_DATE' => date('Y-m-d H:i:s')
        ];
        
        // 승인인 경우 파트너십 시작일 설정
        if ($action === 'approve') {
            $updateData['PARTNERSHIP_START_DATE'] = date('Y-m-d H:i:s');
        }
        
        $this->update($mappingSeq, $updateData);
        
        return $statusResult;
    }
    
    /**
     * 파트너십 해지 (벤더사가 해지)
     */
    public function terminateByVendor($mappingSeq, $vendorSeq, $reason = '')
    {
        $partnership = $this->mappingModel->getBasicMapping($mappingSeq);
        
        if (!$partnership) {
            throw new \Exception('파트너십을 찾을 수 없습니다.');
        }
        
        if ($partnership['VENDOR_SEQ'] != $vendorSeq) {
            throw new \Exception('본인의 파트너십만 해지할 수 있습니다.');
        }
        
        // 현재 상태 확인
        $currentStatus = $this->statusHistoryModel->getCurrentStatus($mappingSeq);
        if (!$currentStatus || $currentStatus['STATUS'] !== 'APPROVED') {
            throw new \Exception('승인된 파트너십만 해지할 수 있습니다.');
        }
        
        // 상태를 TERMINATED로 변경
        $statusResult = $this->statusHistoryModel->changeStatus(
            $mappingSeq,
            'TERMINATED',
            $reason,
            $vendorSeq
        );
        
        // 파트너십 종료일 설정
        $this->update($mappingSeq, [
            'PARTNERSHIP_END_DATE' => date('Y-m-d H:i:s'),
            'ADD_INFO1' => $reason, // 해지 사유
            'ADD_INFO2' => $vendorSeq // 해지 처리자
        ]);
        
        return $statusResult;
    }
    
    /**
     * 벤더사 통계 조회
     */
    public function getVendorStats($vendorSeq)
    {
        $stats = [];
        
        // 전체 파트너십 수
        $stats['total_partnerships'] = $this->where('VENDOR_SEQ', $vendorSeq)
            ->where('IS_ACT', 'Y')
            ->countAllResults();
        
        // 상태별 통계는 히스토리 모델에서 조회
        $statusStats = $this->statusHistoryModel->getStatusStatsByVendor($vendorSeq);
        $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;
        
        // 재승인 요청 수
        $stats['reapply_requests'] = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->where('vim.VENDOR_SEQ', $vendorSeq)
            ->where('vsh.STATUS', 'PENDING')
            ->where('vim.ADD_INFO1', 'REAPPLY')
            ->where('vim.IS_ACT', 'Y')
            ->countAllResults();
        
        // 평균 커미션율
        $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.VENDOR_SEQ', $vendorSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.IS_ACT', 'Y')
            ->get()
            ->getRowArray();
        $stats['avg_commission_rate'] = round($avgCommission['avg_rate'] ?? 0, 2);
        
        // 인플루언서 타입별 분포
        $stats['influencer_type_distribution'] = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('u.INFLUENCER_TYPE, COUNT(*) as count')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->join('USER_LIST u', 'u.SEQ = vim.INFLUENCER_SEQ', 'left')
            ->where('vim.VENDOR_SEQ', $vendorSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.IS_ACT', 'Y')
            ->groupBy('u.INFLUENCER_TYPE')
            ->get()
            ->getResultArray();
        
        // 카테고리별 인플루언서 분포
        $stats['category_distribution'] = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('u.PRIMARY_CATEGORY, COUNT(*) as count')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->join('USER_LIST u', 'u.SEQ = vim.INFLUENCER_SEQ', 'left')
            ->where('vim.VENDOR_SEQ', $vendorSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.IS_ACT', 'Y')
            ->groupBy('u.PRIMARY_CATEGORY')
            ->get()
            ->getResultArray();
        
        // 월별 파트너십 생성 추이 (최근 12개월)
        $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.VENDOR_SEQ', $vendorSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.PARTNERSHIP_START_DATE >=', date('Y-m-d', strtotime('-12 months')))
            ->where('vim.IS_ACT', 'Y')
            ->groupBy('month')
            ->orderBy('month', 'ASC')
            ->get()
            ->getResultArray();
        
        // 인플루언서별 성과 상위 10명
        $stats['top_influencers'] = $this->db->table('VENDOR_INFLUENCER_MAPPING vim')
            ->select('
                u.SEQ, u.NICK_NAME, u.PROFILE_IMAGE, u.FOLLOWER_COUNT,
                u.ENGAGEMENT_RATE, vim.COMMISSION_RATE, vim.PARTNERSHIP_START_DATE
            ')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"')
            ->join('USER_LIST u', 'u.SEQ = vim.INFLUENCER_SEQ', 'left')
            ->where('vim.VENDOR_SEQ', $vendorSeq)
            ->where('vsh.STATUS', 'APPROVED')
            ->where('vim.IS_ACT', 'Y')
            ->orderBy('u.FOLLOWER_COUNT', 'DESC')
            ->orderBy('u.ENGAGEMENT_RATE', 'DESC')
            ->limit(10)
            ->get()
            ->getResultArray();
        
        return $stats;
    }
    
    /**
     * 벤더사의 현재 활성 파트너십 조회
     */
    public function getActivePartnerships($vendorSeq)
    {
        return $this->getVendorRequests($vendorSeq, [
            'status' => 'APPROVED'
        ])->get()->getResultArray();
    }
    
    /**
     * 새로운 요청 알림 조회
     */
    public function getNewRequests($vendorSeq, $days = 7)
    {
        $fromDate = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        
        return $this->getVendorRequests($vendorSeq, [
            'status' => 'PENDING',
            'start_date' => $fromDate
        ])->get()->getResultArray();
    }
    
    /**
     * 재승인 요청 목록 조회
     */
    public function getReapplyRequests($vendorSeq)
    {
        return $this->getVendorRequests($vendorSeq, [
            'status' => 'PENDING',
            'is_reapply' => true
        ])->get()->getResultArray();
    }
    
    /**
     * 요청 상세 정보 조회
     */
    public function getRequestDetail($mappingSeq, $vendorSeq)
    {
        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,
                u.NICK_NAME, u.NAME, u.EMAIL, u.PHONE, u.PROFILE_IMAGE,
                u.FOLLOWER_COUNT, u.ENGAGEMENT_RATE, u.PRIMARY_CATEGORY,
                u.INFLUENCER_TYPE, u.REGION, u.DESCRIPTION,
                u.RATING as INFLUENCER_RATING, u.VERIFICATION_STATUS,
                u.SNS_CHANNELS, u.PORTFOLIO_URL,
                requester.NICK_NAME as REQUESTED_BY_NAME
            ')
            ->join('VENDOR_INFLUENCER_STATUS_HISTORY vsh', 
                   'vsh.MAPPING_SEQ = vim.SEQ AND vsh.IS_CURRENT = "Y"', 'left')
            ->join('USER_LIST u', 'u.SEQ = vim.INFLUENCER_SEQ', 'left')
            ->join('USER_LIST requester', 'requester.SEQ = vim.REQUESTED_BY', 'left')
            ->where('vim.SEQ', $mappingSeq)
            ->where('vim.VENDOR_SEQ', $vendorSeq)
            ->where('vim.IS_ACT', 'Y')
            ->first();
    }
    
    /**
     * 인플루언서 제안 생성 (벤더사가 먼저 제안)
     */
    public function createVendorProposal($data)
    {
        // 중복 제안 확인
        $existing = $this->mappingModel->getExistingMapping(
            $data['VENDOR_SEQ'],
            $data['INFLUENCER_SEQ'],
            ['TERMINATED', 'REJECTED', 'CANCELLED']
        );
        
        if (!empty($existing)) {
            throw new \Exception('이미 진행 중인 파트너십이나 제안이 있습니다.');
        }
        
        $insertData = array_merge($data, [
            'REQUEST_TYPE' => 'VENDOR_PROPOSAL',
            'REQUEST_DATE' => date('Y-m-d H:i:s'),
            'IS_ACT' => 'Y'
        ]);
        
        return $this->insert($insertData);
    }
    
    /**
     * 만료 예정 파트너십 조회
     */
    public function getExpiringPartnerships($vendorSeq, $days = 30)
    {
        $expireDate = date('Y-m-d H:i:s', strtotime("+{$days} days"));
        
        return $this->getVendorRequests($vendorSeq, [
            'status' => 'APPROVED'
        ])
            ->where('vim.EXPIRED_DATE <=', $expireDate)
            ->where('vim.EXPIRED_DATE IS NOT NULL')
            ->get()
            ->getResultArray();
    }
    
    /**
     * 벤더사별 인플루언서 추천 점수 계산
     */
    public function getInfluencerRecommendationScore($vendorSeq, $influencerSeq)
    {
        // 벤더사와 인플루언서 정보 조회는 각각의 모델에서 처리
        $vendorModel = new \App\Models\VendorModel();
        $influencerModel = new \App\Models\InfluencerModel();
        
        $vendor = $vendorModel->find($vendorSeq);
        $influencer = $influencerModel->getProfile($influencerSeq);
        
        if (!$vendor || !$influencer) {
            return 0;
        }
        
        $score = 0;
        
        // 카테고리 일치도 (40점)
        if ($vendor['CATEGORY'] === $influencer['PRIMARY_CATEGORY']) {
            $score += 40;
        } elseif ($vendor['CATEGORY'] === $influencer['SECONDARY_CATEGORY']) {
            $score += 20;
        }
        
        // 지역 일치도 (20점)
        if ($vendor['REGION'] === $influencer['REGION']) {
            $score += 20;
        }
        
        // 인플루언서 등급 (20점)
        switch ($influencer['INFLUENCER_TYPE']) {
            case 'MEGA':
                $score += 20;
                break;
            case 'MACRO':
                $score += 15;
                break;
            case 'MICRO':
                $score += 10;
                break;
            case 'NANO':
                $score += 5;
                break;
        }
        
        // 인플루언서 평점 (10점)
        $score += ($influencer['RATING'] ?? 0) * 2;
        
        // 검증 상태 (10점)
        if ($influencer['VERIFICATION_STATUS'] === 'VERIFIED') {
            $score += 10;
        }
        
        return min(100, $score); // 최대 100점
    }
}