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

class Komplain_model extends CI_Model
{
    const STATUSES = ['OPEN','VERIFICATION','APPROVED','REWORK','QC','READY','DELIVERED','CLOSED','REJECTED','CANCELLED'];
    const PROCESSES = ['design'=>'Design','customer_approval'=>'Customer Approval','prepress'=>'Prepress','printing'=>'Printing','lamination'=>'Lamination','cutting'=>'Cutting','die_cut'=>'Die Cut / Pond','gluing'=>'Gluing','finishing'=>'Finishing','qc'=>'QC','packing'=>'Packing','delivery'=>'Delivery'];

    public function reworkProcessMap()
    {
        $processes=[];
        foreach(job_statuses(true) as $status)$processes['job_status_'.(int)$status['code']]=$status['label'];
        return $processes;
    }

    public function installed() { return $this->db->table_exists('complaints') && $this->db->table_exists('complaint_logs'); }

    public function can($permission)
    {
        if (!$this->session->userdata('idu')) return false;
        if (!$this->db->table_exists('complaint_permissions')) return false;
        $user = function_exists('cekUser') ? cekUser((int)$this->session->idu) : [];
        $levelId = isset($user['lv']) ? (int)$user['lv'] : 0;
        return $this->db->where('id_level',$levelId)->where_in('permission',[$permission,'complaint.*'])->count_all_results('complaint_permissions') > 0;
    }

    public function menuAllowed()
    {
        $user = function_exists('cekUser') ? cekUser((int)$this->session->idu) : [];
        $menu = function_exists('sidebar_menu_data') ? sidebar_menu_data($user) : ['links'=>[]];
        return isset($menu['links']['komplain']);
    }

    public function order($invoiceId)
    {
        return $this->db->select("i.id_invoice,i.id_transaksi,i.id_konsumen,i.tgl_trx,COALESCE(NULLIF(k.perusahaan,''),k.nama,'-') customer",false)
            ->from('invoice i')->join('konsumen k','k.id=i.id_konsumen','left')->where('i.id_invoice',(int)$invoiceId)
            ->where('i.status !=','batal')->limit(1)->get()->row_array();
    }

    public function orderItems($invoiceId)
    {
        return $this->db->select("d.id_rincianinvoice,d.id_produk,d.no_spk,d.jumlah,COALESCE(NULLIF(TRIM(b.title),''),NULLIF(TRIM(p.title),''),'Bahan') product_name",false)
            ->from('invoice_detail d')->join('produk p','p.id=d.id_produk','left')->join('bahan b','b.id=d.id_bahan','left')->where('d.id_invoice',(int)$invoiceId)
            ->where('d.item_type','product')->order_by('d.id_rincianinvoice')->get()->result_array();
    }

    public function searchOrders($term)
    {
        $this->db->select("i.id_invoice,i.id_transaksi,DATE_FORMAT(TIMESTAMP(DATE(i.tgl_trx),COALESCE(NULLIF(i.jam_order,'00:00:00'),TIME(i.tgl_trx))),'%d/%m/%Y %H:%i:%s') AS tgl_trx,COALESCE(NULLIF(k.perusahaan,''),k.nama,'-') customer",false)
            ->from('invoice i')->join('konsumen k','k.id=i.id_konsumen','left')->where('i.status !=','batal');
        $this->db->group_start()->like('i.id_transaksi',$term)->or_like('k.nama',$term)->or_like('k.perusahaan',$term)->group_end();
        return $this->db->order_by('i.id_invoice','DESC')->limit(15)->get()->result_array();
    }

    public function nextNumber($table,$field,$prefix)
    {
        $monthPrefix=$prefix.'-'.date('ym');
        $row=$this->db->select($field)->like($field,$monthPrefix,'after')->order_by('id','DESC')->limit(1)->get($table)->row_array();
        $seq=$row ? (int)substr($row[$field],-5)+1 : 1;
        return $monthPrefix.str_pad($seq,5,'0',STR_PAD_LEFT);
    }

    public function createComplaint(array $head,array $items,$userId,array $files=[])
    {
        $now=date('Y-m-d H:i:s'); $this->db->trans_begin();
        $head += ['complaint_no'=>$this->nextNumber('complaints','complaint_no','CMP'),'status'=>'OPEN','created_by'=>(int)$userId,'created_at'=>$now,'updated_at'=>$now];
        $ok=$this->db->insert('complaints',$head); $id=(int)$this->db->insert_id();
        foreach($items as $item){$item['complaint_id']=$id;$ok=$ok&&$this->db->insert('complaint_items',$item);}
        foreach($files as $file){$file['complaint_id']=$id;$file['uploaded_by']=(int)$userId;$file['created_at']=$now;$ok=$ok&&$this->db->insert('complaint_files',$file);}
        $ok=$ok&&$this->log($id,$userId,'CREATED',null,'OPEN','Komplain dibuat.');
        if($ok&&$this->db->trans_status()){$this->db->trans_commit();return $id;}$this->db->trans_rollback();return 0;
    }

    public function find($id)
    {
        return $this->db->select("c.*,i.id_transaksi,i.tgl_trx,COALESCE(NULLIF(k.perusahaan,''),k.nama,'-') customer,u.nama_lengkap pic_name,a.nama_lengkap approved_name",false)
            ->from('complaints c')->join('invoice i','i.id_invoice=c.transaction_id')->join('konsumen k','k.id=c.customer_id','left')
            ->join('tb_users u','u.id_user=c.pic_user_id','left')->join('tb_users a','a.id_user=c.approved_by','left')->where('c.id',(int)$id)->limit(1)->get()->row_array();
    }

    public function detailData($id)
    {
        return ['items'=>$this->db->where('complaint_id',(int)$id)->get('complaint_items')->result_array(),
            'files'=>$this->db->where('complaint_id',(int)$id)->get('complaint_files')->result_array(),
            'reworks'=>$this->db->where('complaint_id',(int)$id)->order_by('id','DESC')->get('complaint_reworks')->result_array(),
            'costs'=>$this->db->where('complaint_id',(int)$id)->order_by('id','DESC')->get('complaint_costs')->result_array(),
            'logs'=>$this->db->select('l.*,u.nama_lengkap')->from('complaint_logs l')->join('tb_users u','u.id_user=l.user_id','left')->where('l.complaint_id',(int)$id)->order_by('l.id','DESC')->get()->result_array()];
    }

    private function buildRowsQuery(array $f=[])
    {
        $this->db->select("c.*,i.id_transaksi,COALESCE(NULLIF(k.perusahaan,''),k.nama,'-') customer,(SELECT GROUP_CONCAT(ci.product_name SEPARATOR ', ') FROM complaint_items ci WHERE ci.complaint_id=c.id) products,COALESCE(u.nama_lengkap,'-') pic,COALESCE((SELECT SUM(cc.total_cost) FROM complaint_costs cc WHERE cc.complaint_id=c.id),0) total_cost",false)
            ->from('complaints c')->join('invoice i','i.id_invoice=c.transaction_id')->join('konsumen k','k.id=c.customer_id','left')
            ->join('tb_users u','u.id_user=c.pic_user_id','left');
        foreach(['status','priority','complaint_type','root_cause_department'] as $key)if(!empty($f[$key]))$this->db->where('c.'.$key,$f[$key]);
        if(!empty($f['q']))$this->db->group_start()->like('c.complaint_no',$f['q'])->or_like('i.id_transaksi',$f['q'])->or_like('k.nama',$f['q'])->group_end();
        if(!empty($f['from']))$this->db->where('c.complaint_date >=',$f['from']);if(!empty($f['to']))$this->db->where('c.complaint_date <=',$f['to']);
    }

    public function countRows(array $f=[])
    {
        $this->buildRowsQuery($f);
        return (int)$this->db->count_all_results();
    }

    public function rows(array $f=[],$limit=10,$offset=0)
    {
        $this->buildRowsQuery($f);
        return $this->db->order_by('c.id','DESC')->limit((int)$limit,(int)$offset)->get()->result_array();
    }

    public function summary()
    {
        $row=$this->db->select("SUM(status='OPEN') open_count,SUM(status='APPROVED') approved_count,SUM(status='REWORK') rework_count,SUM(status='REJECTED') rejected_count",false)->get('complaints')->row_array();
        $row['total_cost']=(float)$this->db->select_sum('total_cost')->get('complaint_costs')->row()->total_cost;return $row;
    }

    public function updateDecision($id,array $data,$userId)
    {
        $old=$this->find($id);if(!$old)return false;$data['updated_at']=date('Y-m-d H:i:s');
        $this->db->trans_begin();$ok=$this->db->where('id',(int)$id)->update('complaints',$data);
        $ok=$ok&&$this->log($id,$userId,'DECISION',$old['status'],$data['status'],$data['resolution_note']);
        if($ok&&$this->db->trans_status()){$this->db->trans_commit();return true;}$this->db->trans_rollback();return false;
    }

    public function createRework($complaint,array $data,array $selected,$userId)
    {
        $now=date('Y-m-d H:i:s');$this->db->trans_begin();$rw=$this->nextNumber('complaint_reworks','rework_no','RW');
        $count=$this->db->where('complaint_id',$complaint['id'])->count_all_results('complaint_reworks')+1;
        $spk='RW-SPK-'.preg_replace('/[^A-Za-z0-9-]/','',$data['original_spk']).'-'.str_pad($count,2,'0',STR_PAD_LEFT);
        $ok=$this->db->insert('complaint_reworks',['complaint_id'=>$complaint['id'],'original_spk_detail_id'=>$data['detail_id'],'rework_no'=>$rw,'rework_spk_no'=>$spk,'qty'=>$data['qty'],'status'=>'PENDING','created_by'=>$userId,'created_at'=>$now,'updated_at'=>$now]);$id=(int)$this->db->insert_id();$seq=1;
        foreach($this->reworkProcessMap() as $type=>$name){$status=in_array($type,$selected,true)?'PENDING':'SKIPPED';$ok=$ok&&$this->db->insert('complaint_rework_processes',['rework_id'=>$id,'process_type'=>$type,'process_name'=>$name,'sequence_no'=>$seq++,'status'=>$status,'created_at'=>$now,'updated_at'=>$now]);}
        $ok=$ok&&$this->createReworkJobDetail($complaint,$data,$selected,$rw,$spk,$now);
        $ok=$ok&&$this->createOrderBasedReworkCost($complaint,$data,$id,$rw,$userId,$now);
        $ok=$ok&&$this->db->where('id',$complaint['id'])->update('complaints',['status'=>'REWORK','rework_qty'=>$complaint['rework_qty']+$data['qty'],'updated_at'=>$now]);
        $ok=$ok&&$this->db->set('rework_qty','rework_qty+'.$this->db->escape($data['qty']),false)->where(['complaint_id'=>$complaint['id'],'transaction_detail_id'=>$data['detail_id']])->update('complaint_items');
        $ok=$ok&&$this->log($complaint['id'],$userId,'REWORK_CREATED',$complaint['status'],'REWORK',$rw.' / '.$spk);
        if($ok&&$this->db->trans_status()){$this->db->trans_commit();return $id;}$this->db->trans_rollback();return 0;
    }

    private function createOrderBasedReworkCost(array $complaint,array $data,$reworkId,$reworkNo,$userId,$now)
    {
        $item=$this->db->select("d.harga,COALESCE(NULLIF(TRIM(s.satuan),''),NULLIF(TRIM(d.satuan),''),'unit') unit_name,COALESCE(NULLIF(TRIM(b.title),''),NULLIF(TRIM(p.title),''),'Item') item_name",false)->from('invoice_detail d')->join('satuan s','s.id=d.id_satuan','left')->join('bahan b','b.id=d.id_bahan','left')->join('produk p','p.id=d.id_produk','left')->where('d.id_rincianinvoice',(int)$data['detail_id'])->limit(1)->get()->row_array();
        if(!$item)return false;
        $qty=(float)$data['qty'];$unitCost=max(0,(float)$item['harga']);
        return $this->db->insert('complaint_costs',['complaint_id'=>(int)$complaint['id'],'rework_id'=>(int)$reworkId,'cost_type'=>'Harga Order','description'=>'Biaya '.$item['item_name'].' sesuai harga order '.$complaint['id_transaksi'].' / '.$reworkNo,'qty'=>$qty,'unit'=>'Pcs','unit_cost'=>$unitCost,'total_cost'=>$qty*$unitCost,'created_by'=>(int)$userId,'created_at'=>$now]);
    }

    private function createReworkJobDetail(array $complaint,array $data,array $selected,$reworkNo,$spk,$now)
    {
        $original=$this->db->where('id_rincianinvoice',(int)$data['detail_id'])->limit(1)->get('invoice_detail')->row_array();
        if(!$original||!$selected)return false;
        $reworkInvoiceId=$this->getOrCreateReworkInvoice((int)$original['id_invoice'],$complaint,$now);
        if(!$reworkInvoiceId)return false;
        $firstProcess=(string)reset($selected);
        if(strpos($firstProcess,'job_status_')!==0)return false;
        $statusCode=(int)substr($firstProcess,11);
        unset($original['id_rincianinvoice']);
        $note='Rework komplain '.$complaint['complaint_no'].' / '.$reworkNo;
        $original['no_spk']=$spk;
        $original['id_invoice']=$reworkInvoiceId;
        $original['jumlah']=$data['qty'];
        $original['harga_satuan']=0;
        $original['harga']=0;
        $original['diskon']=0;
        $original['hpp']=0;
        $original['status']=$statusCode;
        $original['ambil']='N';
        $original['rak']='N';
        $original['id_pengirim']=0;
        $original['jumlah_kirim']=0;
        $original['token']=null;
        $original['catatan']=trim((string)$original['catatan'].($original['catatan']?"\n":'').$note);
        $original['update_date']=$now;
        $inserted=$this->db->insert('invoice_detail',$original);
        if($inserted)$this->db->where('id_invoice',$reworkInvoiceId)->update('invoice',['hide_status_board'=>0]);
        return $inserted;
    }

    private function getOrCreateReworkInvoice($originalInvoiceId,array $complaint,$now)
    {
        $source=$this->db->where('id_invoice',$originalInvoiceId)->limit(1)->get('invoice')->row_array();
        if(!$source)return 0;
        $originalOrderNo=(string)$source['id_transaksi'];
        $orderNo='CMP-'.preg_replace('/^CMP-/i','',$originalOrderNo);
        $existing=$this->db->select('id_invoice')->where('id_transaksi',$orderNo)->where('status !=','batal')->order_by('id_invoice','DESC')->limit(1)->get('invoice')->row_array();
        if($existing)return (int)$existing['id_invoice'];
        unset($source['id_invoice']);
        $source['id_transaksi']=$orderNo;
        foreach(['total_bayar','jumlah_bayar','kembalian','potongan_harga','cashback','pajak','lunas','oto','cetak','komisi_persen','komisi_nominal','total_pajak','komisi_penjualan_nominal'] as $field)$source[$field]=0;
        $source['pos']='N';
        $source['tgl_trx']=date('Y-m-d 00:00:00',strtotime($now));
        $source['jam_order']=date('H:i:s',strtotime($now));
        $source['tgl_update']=$now;
        $source['status']='simpan';
        $source['hide_status_board']=0;
        $source['history']='Pekerjaan rework '.$complaint['complaint_no'].' dari '.$originalOrderNo;
        $source['data_json']=null;
        $source['sesi_cart']=null;
        $source['external_order_id']=null;
        $source['channel']='complaint';
        if(!$this->db->insert('invoice',$source))return 0;
        return (int)$this->db->insert_id();
    }

    public function processes($reworkId){return $this->db->where('rework_id',(int)$reworkId)->order_by('sequence_no')->get('complaint_rework_processes')->result_array();}
    public function syncReworkProgressFromJobDetail($detailId,$jobStatus,$userId)
    {
        if(!$this->db->table_exists('complaint_reworks')||!$this->db->table_exists('complaint_rework_processes'))return true;
        $rework=$this->db->select('r.*')->from('invoice_detail d')->join('invoice i','i.id_invoice=d.id_invoice')->join('complaint_reworks r','r.rework_spk_no=d.no_spk')->where('d.id_rincianinvoice',(int)$detailId)->like('i.id_transaksi','CMP-','after')->limit(1)->get()->row_array();
        if(!$rework)return true;
        $currentRank=job_status_rank((int)$jobStatus);$now=date('Y-m-d H:i:s');$changed=false;
        foreach($this->processes($rework['id']) as $process){
            if($process['status']==='SKIPPED'||strpos($process['process_type'],'job_status_')!==0)continue;
            $processCode=(int)substr($process['process_type'],11);$processRank=job_status_rank($processCode);
            $target=$processRank<$currentRank?'DONE':($processCode===(int)$jobStatus?'IN_PROGRESS':'PENDING');
            if($process['status']===$target)continue;
            $data=['status'=>$target,'updated_at'=>$now];
            if($target==='DONE'){$data['started_at']=$process['started_at']?:$now;$data['finished_at']=$now;}
            elseif($target==='IN_PROGRESS'){$data['started_at']=$process['started_at']?:$now;$data['finished_at']=null;}
            else{$data['started_at']=null;$data['finished_at']=null;}
            if(!$this->db->where('id',(int)$process['id'])->update('complaint_rework_processes',$data))return false;
            $changed=true;
        }
        if($changed){
            if(!$this->db->where('id',(int)$rework['id'])->update('complaint_reworks',['status'=>'IN_PROGRESS','started_at'=>$rework['started_at']?:$now,'updated_at'=>$now]))return false;
            $meta=job_status_meta((int)$jobStatus);
            if(!$this->log((int)$rework['complaint_id'],(int)$userId,'REWORK_STATUS_SYNC',null,(string)$meta['label'],'Progress rework disinkronkan dari List Pekerjaan.'))return false;
        }
        return true;
    }
    public function updateProcess($complaintId,$processId,$status,$assigned,$notes,$userId)
    {
        $p=$this->db->select('p.*')->from('complaint_rework_processes p')->join('complaint_reworks r','r.id=p.rework_id')->where('p.id',(int)$processId)->where('r.complaint_id',(int)$complaintId)->get()->row_array();if(!$p)return false;$now=date('Y-m-d H:i:s');$data=['status'=>$status,'assigned_to'=>$assigned?:null,'notes'=>$notes,'updated_at'=>$now];if($status==='IN_PROGRESS'&&!$p['started_at'])$data['started_at']=$now;if($status==='DONE')$data['finished_at']=$now;
        $this->db->trans_begin();$ok=$this->db->where('id',$processId)->update('complaint_rework_processes',$data);$ok=$ok&&$this->log($complaintId,$userId,'PROCESS_UPDATED',null,$status,$p['process_name'].': '.$notes);if($ok&&$this->db->trans_status()){$this->db->trans_commit();return true;}$this->db->trans_rollback();return false;
    }
    public function addCost($complaintId,array $data,$userId){$data+=['complaint_id'=>$complaintId,'created_by'=>$userId,'created_at'=>date('Y-m-d H:i:s')];$this->db->trans_begin();$ok=$this->db->insert('complaint_costs',$data)&&$this->log($complaintId,$userId,'COST_ADDED',null,null,$data['description']);if($ok&&$this->db->trans_status()){$this->db->trans_commit();return true;}$this->db->trans_rollback();return false;}
    public function log($id,$user,$action,$old,$new,$desc){return $this->db->insert('complaint_logs',['complaint_id'=>$id,'user_id'=>$user,'action'=>$action,'old_status'=>$old,'new_status'=>$new,'description'=>$desc,'created_at'=>date('Y-m-d H:i:s')]);}
}
