<?php

namespace App\Models;

use Dcat\Admin\Traits\HasDateTimeFormatter;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\SoftDeletes;
use App\Command\Log;

class Lawyer extends Model
{
    use HasDateTimeFormatter;
    use SoftDeletes;
    protected $table = 'lawyer';

    //律师汇总表 总基本工资
    public static function getTotalBasiSalary($params, $received_at, $invoiced_at)
    {
        // 获取筛选条件
        $lnum = $params['lnum'] ?? '';
        $lname = $params['lname'] ?? '';
        $year = $params['year'] ?? date('Y');

        DB::enableQueryLog();
        $list = [];
        $note_monitor = DB::table('covenant_receive_payment AS rp')
            ->select(['rp.*', 'l.name as lname', 'l.number as lnumber', 'l.commission_rate'])
            ->leftJoin('lawyer as l', 'rp.lawyer_id', '=', 'l.id');
        if ($lnum) {
            $note_monitor = $note_monitor->where("l.number", $lnum);
        }
        if ($lname) {
            $note_monitor = $note_monitor->where("l.name", $lname);
        }
        if ($year) {
            $note_monitor = $note_monitor->where("rp.year", $year);
        }
        if ($invoiced_at['start'] && $received_at['start']) {
            $in_start = $invoiced_at['start'] ?? '';
            $in_end = $invoiced_at['end'] ?? '';
            $re_start = $received_at['start'] ?? '';
            $re_end = $received_at['end'] ?? '';

            if ($in_start && $re_start) {
                if ($in_end && $re_end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end])
                        ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
                } else {
                    if ($in_end) {
                        $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start)
                            ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
                    } elseif ($re_end) {
                        $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                            ->whereBetween('rp.received_at', [$re_start, $re_end]);
                    } else {
                        $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                            ->whereDate('rp.received_at', '>', $re_start);
                    }
                }
            }
        } elseif ($invoiced_at['start'] && !$received_at['start']) { //开票日期
            $start = $invoiced_at['start'] ?? '';
            $end = $invoiced_at['end'] ?? '';

            if ($start) {
                if ($end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$start, $end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $start);
                }
            }
            //
        } elseif ($received_at['start'] && !$invoiced_at['start']) { //收款日期
            $start = $received_at['start'] ?? '';
            $end = $received_at['end'] ?? '';

            if ($start) {
                if ($end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$start, $end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $start);
                }
            }
        }

        // 获取列表
        $total = 0;
        $list = $note_monitor->limit(1000)->groupBy('rp.lawyer_id')->get()->toArray();
        foreach ($list as $item) {
            $salary = LawyerCost::getBasiSalary($item->lawyer_id, $year);
            $total += $salary;
        }

        //$queries = DB::getQueryLog();
        return $total;
    }

    //律师汇总表 律师基本信息统计
    /**
     * 获取律师汇总信息
     * @param array $params 查询参数
     * @param array $received_at 收款日期范围
     * @param array $invoiced_at 开票日期范围
     * @return array 返回包含各项统计数据的数组
     */
    public static function getTotalInfo($params, $received_at, $invoiced_at)
    {
        // 获取筛选条件
        $lnum = $params['number'] ?? ''; // 律师编号
        $lname = $params['name'] ?? '';  // 律师姓名
        $year = $params['year'] ?? date('Y'); // 年份，默认为当前年

        DB::enableQueryLog(); // 开启SQL日志记录
        $list = [];
        
        // 构建基础查询：关联律师表和合同收款表
        $note_monitor = DB::table('lawyer as l')
            ->select(DB::raw('rp.lawyer_id,rp.year,l.number as lnumber,l.name as lname,l.commission_rate,sum(invoiced_money) as invoiced_money,sum(received_amount) as received_amount'))
            ->leftJoin('covenant_receive_payment AS rp', 'rp.lawyer_id', '=', 'l.id')
            ->whereNull('rp.deleted_at'); // 只查询未删除的记录

        // 打印SQL日志
            

        // 添加筛选条件
        if ($lnum) {
            $note_monitor = $note_monitor->where("l.number", $lnum); // 按律师编号筛选
        }
        if ($lname) {
            $note_monitor = $note_monitor->where("l.name", $lname); // 按律师姓名筛选
        }
        if ($year) {
            $note_monitor = $note_monitor->where("rp.year", $year); // 按年份筛选
        }

        // 处理日期范围筛选条件
        // 同时有开票日期和收款日期筛选
        if ($invoiced_at['start'] && $received_at['start']) {
            $in_start = $invoiced_at['start'] ?? '';
            $in_end = $invoiced_at['end'] ?? '';
            $re_start = $received_at['start'] ?? '';
            $re_end = $received_at['end'] ?? '';

            if ($in_start && $re_start) {
                if ($in_end && $re_end) {
                    // 完整的日期范围筛选
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end])
                        ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
                } else {
                    // 部分日期范围筛选
                    if ($in_end) {
                        $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start)
                            ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
                    } elseif ($re_end) {
                        $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                            ->whereBetween('rp.received_at', [$re_start, $re_end]);
                    } else {
                        $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                            ->whereDate('rp.received_at', '>', $re_start);
                    }
                }
            }
        } 
        // 只有开票日期筛选
        elseif ($invoiced_at['start'] && !$received_at['start']) { 
            $start = $invoiced_at['start'] ?? '';
            $end = $invoiced_at['end'] ?? '';

            if ($start) {
                if ($end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$start, $end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $start);
                }
            }
        } 
        // 只有收款日期筛选
        elseif ($received_at['start'] && !$invoiced_at['start']) { 
            $start = $received_at['start'] ?? '';
            $end = $received_at['end'] ?? '';

            if ($start) {
                if ($end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$start, $end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $start);
                }
            }
        }

        // 初始化返回结果数组
        $total = [
            'receipt_money' => 0,         // 已收款总额
            'invoiced_money' => 0,        // 已开票总额
            'commission_amount' => 0,     // 提成金额总额
            'cost' => 0,                  // 成本合计
            'salary' => 0,                // 基本工资总额
            'special_additional' => 0,    // 专项附加总额
            'social' => 0,                // 社保总额
            'accumulation_fund' => 0,      // 公积金总额
            'advance_fee' => 0,           // 预支款总额
            'tickets_money' => 0,         // 贴票金额总额
            'payable_commission' => 0,    // 可结算提成总额
        ];

        // 执行查询并获取结果
        $list = $note_monitor->limit(1000)->groupBy('rp.lawyer_id')->get()->toArray();

        
        $sql = $note_monitor->toSql();
        $bindings = $note_monitor->getBindings();
        Log::add('debug', 'SQL查询: '.$sql.' 参数: '.json_encode($bindings));

        // 处理查询结果
        foreach ($list as $item) {
            // 获取律师信息
            $lawyerObj = Lawyer::find($item->lawyer_id);
            //Log::add('debug', $item); // 记录调试日志
            
            // 检查律师是否存在
            if (!$lawyerObj) {
                Log::add('error', '未找到律师ID: ' . $item->lawyer_id);
                continue;
            }

            // 计算各项金额并累加
            $receipt_money = $item->received_amount; // 已收款金额
            $total['receipt_money'] = round($total['receipt_money'] + $receipt_money, 2);
            
            $invoiced_money = $item->invoiced_money; // 已开票金额
            $total['invoiced_money'] += $invoiced_money;
            
            $commission_amount = self::getCommissionAmount($receipt_money, $lawyerObj->commission_rate); // 提成金额
            $total['commission_amount'] += $commission_amount;
            
            $cost = LawyerCost::getTotalCost($item->lawyer_id, $year); // 总成本
            $total['cost'] += $cost;
            
            //基本工资
            $salary = LawyerCost::getBasiSalary($item->lawyer_id, $year);
            $total['salary'] += $salary;
            //专项附加
            $additional = LawyerCost::getSpecialAdditional($item->lawyer_id, $year);
            $total['special_additional'] += $additional;
            //社保
            $social = LawyerCost::getSocial($item->lawyer_id, $year);
            $total['social'] += $social;
            //公积金
            $accumulation_fund = LawyerCost::getAccumulationFund($item->lawyer_id, $year);
            $total['accumulation_fund'] += $accumulation_fund;
            //预支款
            $advance_fee = LawyerCost::getAdvanceFee($item->lawyer_id, $year);
            $total['advance_fee'] += $advance_fee;
            //贴票金额
            $lawyerObj = Lawyer::find($item->lawyer_id); // 第2次
            // 应该缓存查询结果
            $tickets_money = LawyerCost::getPostingTicketsMoney($item->lawyer_id, $year, $lawyerObj->commission_rate, $lawyerObj->ticket_ratio);
            $total['tickets_money'] += $tickets_money;
            //可结算提成
            $payable_commission = self::getPayableAmount($item->lawyer_id, $year, $lawyerObj->commission_rate, $receipt_money);
            $total['payable_commission'] += $payable_commission;
        }
        
        return $total;
    }

    //提成金额
    public static function getCommissionAmount($receipt_money, $commission_rate)
    {
        return $receipt_money * ($commission_rate / 100);
    }
    //可结算提成
    public static function getPayableAmount($lawyer_id, $year, $commission_rate, $receipt_money)
    {
        $paid_amount =  LawyerCost::getPaidAmount($lawyer_id, $year); //已支付款项
        $result = $receipt_money * ($commission_rate / 100) - $paid_amount;
        return $result;
    }

    //不存在就新增
    public static function getIdByName($title = '')
    {
        $id = 0;
        $row = self::where("name", $title)->first();
        if ($row) {
            $id = $row->id;
        } else {
            $id = self::insertGetId([
                'name' => $title,
                'wtype' => 2,
                'number' => mt_rand(10000, 99999),
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ]);
        }
        return $id;
    }
}