<?php

namespace App\Libraries;

use App\Models\DealCommissionsModel;
use App\Models\CommissionOverrideModel;
use App\Models\UpsellsProcessedModel;    // <-- Updated import
use App\Models\OrdersProcessedModel;

class DealCommissionLib
{
    /**
     * Single-query version for "Deal Commissions" data.
     * Expects oTypeFilter = '0' OR '1' OR '2', etc. (not '0,1')
     * The controller handles merging if needed.
     */
    public function getDealCommissions(array $params): array
    {
        $fromDate           = $params['fromDate'];
        $toDate             = $params['toDate'];
        $oTypeFilter        = $params['oTypeFilter'];   // e.g. '0' or '1'
        $updateStatusFilter = $params['updateStatusFilter'];
        $paidStatusFilter   = $params['paidStatusFilter'];

        $showCogs        = $params['showCogs'];
        $showRevenue     = $params['showRevenue'];
        $showGrossProfit = $params['showGrossProfit'];

        $limit       = $params['limit'];
        $currentPage = $params['currentPage'];

        // Our model points to bbt_qb_sales_receipts
        $dealCommModel = new DealCommissionsModel();

        // Build the base query
        $queryBuilder = $dealCommModel
            ->select("
                bbt_qb_sales_receipts.*,
                bbt_deals.deposit_collected_by,
                bbt_deals.customer_care_by,
                bbt_deals.titlework_completed_by,
                bbt_deals.checkout_completed_by,

                depositUser.first_name AS deposit_first_name, depositUser.last_name AS deposit_last_name,
                careUser.first_name    AS care_first_name,    careUser.last_name    AS care_last_name,
                titleUser.first_name   AS title_first_name,   titleUser.last_name   AS title_last_name,
                completionUser.first_name AS completion_first_name, completionUser.last_name AS completion_last_name
            ")
            ->join('bbt_deals', 'bbt_deals.id = bbt_qb_sales_receipts.deal_id', 'left')
            ->join('bbt_users depositUser',    'depositUser.id    = bbt_deals.deposit_collected_by',    'left')
            ->join('bbt_users careUser',       'careUser.id       = bbt_deals.customer_care_by',        'left')
            ->join('bbt_users titleUser',      'titleUser.id      = bbt_deals.titlework_completed_by',  'left')
            ->join('bbt_users completionUser', 'completionUser.id = bbt_deals.checkout_completed_by',   'left');

        // Single o_type (e.g. '0', '1') – just do a direct match
        $queryBuilder->where('o_type', $oTypeFilter);

        // Date range
        $queryBuilder
            ->where('bbt_qb_sales_receipts.createdat >=', $fromDate)
            ->where('bbt_qb_sales_receipts.createdat <=', $toDate . ' 23:59:59');

        // Update_status filter
        if ($updateStatusFilter !== 'ALL') {
            $queryBuilder->where('update_status', $updateStatusFilter);
        }

        // Paid_status filter
        if ($paidStatusFilter === 'Unpaid Only') {
            $queryBuilder->where('bbt_deals.commission_paid', 0);
        } elseif ($paidStatusFilter === 'Paid Only') {
            $queryBuilder->where('bbt_deals.commission_paid', 1);
        }

        // Count total for pagination
        $totalRows = $queryBuilder->countAllResults(false);

        // Handle pagination
        if ($limit === 'ALL') {
            $results    = $queryBuilder->findAll();
            $totalPages = 1;
        } else {
            $limitInt = (int) $limit;
            if ($limitInt <= 0) {
                $limitInt = 25;
            }
            $totalPages = max(1, (int) ceil($totalRows / $limitInt));
            if ($currentPage > $totalPages) {
                $currentPage = $totalPages;
            }
            $offset  = ($currentPage - 1) * $limitInt;
            $results = $queryBuilder->findAll($limitInt, $offset);
        }

        // Build the final list of db fields we show
        $db          = db_connect();
        $allDbFields = $db->getFieldNames('bbt_qb_sales_receipts');

        // Excluded columns from the final table
        $excludedFields = [
            'id','pdf','deal_id','o_type','createdat','updatedat','update_status','qb_id','is_sync',
            'sales_data','customer_id','total_price','is_charge','qb_customer_id','qb_journal_id',
            'is_sync_journal','cogs','profit','createdby','is_web','model_cnt',
            'labor_total','hours_billed','parts_cost','parts_profit','hours_total','hours_cnt',
            'tax','unit_tax','parts_tax','county_tax','tire_tax','reg_fee','doc_fee','lien_fee',
            'title_fee','shipping','model_cost','amazon_fee','labor_cost','fee_cost',
            'custom_option_cost','shop_supplies','unit_duty','unit_shipping','is_update',
            'option_total','parts_cnt','promotion_cost','profit','order_processed',
        ];

        // Optionally show/hide cogs, revenue, profit
        if ($showCogs) {
            $idx = array_search('cogs', $excludedFields);
            if ($idx !== false) {
                unset($excludedFields[$idx]);
            }
        }
        if ($showRevenue) {
            $idx = array_search('revenue', $excludedFields);
            if ($idx !== false) {
                unset($excludedFields[$idx]);
            }
        }
        if ($showGrossProfit) {
            $idx = array_search('profit', $excludedFields);
            if ($idx !== false) {
                unset($excludedFields[$idx]);
            }
        }

        // Filter out excluded fields
        $fieldNames = array_filter($allDbFields, function ($f) use ($excludedFields) {
            return ! in_array($f, $excludedFields, true);
        });

        // We'll inject computed columns as needed
        $computedToInject = [];
        if ($showRevenue) {
            $computedToInject[] = 'Revenue';
        }
        if ($showCogs) {
            $computedToInject[] = 'Total COGS';
        }
        if ($showGrossProfit) {
            $computedToInject[] = 'Gross Profit';
        }

        // Insert these computed columns before 'id' if it exists
        $idIndex = array_search('id', $fieldNames);
        if ($idIndex !== false) {
            array_splice($fieldNames, $idIndex, 0, $computedToInject);
        } else {
            $fieldNames = array_merge($computedToInject, $fieldNames);
        }

        // Insert deposit/care/title/checkout columns after 'inv_no'
        $invNoIndex = array_search('inv_no', $fieldNames);
        if ($invNoIndex !== false) {
            array_splice($fieldNames, $invNoIndex + 1, 0, [
                'Deposit','Customer Care','Title Work','Checkout Completed'
            ]);
        } else {
            array_push($fieldNames, 'Deposit','Customer Care','Title Work','Checkout Completed');
        }

        // Prepare an overall commission summary
        $commissionSummary = [];

        // Gather override data for the 4 user roles (deposit/care/title/checkout)
        $uniqueUserIds = [];
        foreach ($results as $r) {
            if (! empty($r['deposit_collected_by'])) {
                $uniqueUserIds[] = (int) $r['deposit_collected_by'];
            }
            if (! empty($r['customer_care_by'])) {
                $uniqueUserIds[] = (int) $r['customer_care_by'];
            }
            if (! empty($r['titlework_completed_by'])) {
                $uniqueUserIds[] = (int) $r['titlework_completed_by'];
            }
            if (! empty($r['checkout_completed_by'])) {
                $uniqueUserIds[] = (int) $r['checkout_completed_by'];
            }
        }
        $uniqueUserIds = array_unique($uniqueUserIds);

        $overrideMap = [];
        if (! empty($uniqueUserIds)) {
            $overrideModel = new CommissionOverrideModel();
            $overrideRows  = $overrideModel->whereIn('user_id', $uniqueUserIds)->findAll();

            foreach ($overrideRows as $rowOverride) {
                $overrideMap[$rowOverride['user_id']] = [
                    'deposit'            => (float) ($rowOverride['deposit']            ?? 0),
                    'customer_care'      => (float) ($rowOverride['customer_care']      ?? 0),
                    'title_work'         => (float) ($rowOverride['title_work']         ?? 0),
                    'checkout_completed' => (float) ($rowOverride['checkout_completed'] ?? 0),
                ];
            }
        }

        // Compute final columns for each row
        foreach ($results as $k => $row) {
            // Basic revenue/cogs/grossProfit
            $revenueValue = (float) $row['model_price']
                          + (float) $row['parts_total']
                          + (float) $row['fee_total']
                          + (float) $row['custom_option_total']
                          - (float) $row['discount'];

            $cogsValue = (float) $row['model_cost']
                       + (float) $row['parts_cost']
                       + (float) $row['fee_cost']
                       + (float) $row['custom_option_cost']
                       + (float) $row['unit_shipping']
                       + (float) $row['unit_duty']
                       + (float) $row['promotion_total'];

            $grossProfitValue = $revenueValue - $cogsValue;

            // Insert toggled columns
            if ($showRevenue) {
                $row['Revenue'] = $revenueValue;
            }
            if ($showCogs) {
                $row['Total COGS'] = $cogsValue;
            }
            if ($showGrossProfit) {
                $row['Gross Profit'] = $grossProfitValue;
            }

            // deposit
            $depId   = (int) ($row['deposit_collected_by'] ?? 0);
            $depName = trim(($row['deposit_first_name'] ?? '') . ' ' . ($row['deposit_last_name'] ?? ''));
            $depPct  = ($depId && isset($overrideMap[$depId])) ? $overrideMap[$depId]['deposit'] : 0;
            $depAmt  = $grossProfitValue * ($depPct / 100.0);
            $row['Deposit'] = ($depName ? $depName . ' - $' . number_format($depAmt, 2) : '');
            if ($depName) {
                if (! isset($commissionSummary[$depName])) {
                    $commissionSummary[$depName] = 0.0;
                }
                $commissionSummary[$depName] += $depAmt;
            }

            // customer care
            $careId   = (int) ($row['customer_care_by'] ?? 0);
            $careName = trim(($row['care_first_name'] ?? '') . ' ' . ($row['care_last_name'] ?? ''));
            $carePct  = ($careId && isset($overrideMap[$careId])) ? $overrideMap[$careId]['customer_care'] : 0;
            $careAmt  = $grossProfitValue * ($carePct / 100.0);
            $row['Customer Care'] = ($careName ? $careName . ' - $' . number_format($careAmt, 2) : '');
            if ($careName) {
                if (! isset($commissionSummary[$careName])) {
                    $commissionSummary[$careName] = 0.0;
                }
                $commissionSummary[$careName] += $careAmt;
            }

            // title work
            $titleId   = (int) ($row['titlework_completed_by'] ?? 0);
            $titleName = trim(($row['title_first_name'] ?? '') . ' ' . ($row['title_last_name'] ?? ''));
            $titlePct  = ($titleId && isset($overrideMap[$titleId])) ? $overrideMap[$titleId]['title_work'] : 0;
            $titleAmt  = $grossProfitValue * ($titlePct / 100.0);
            $row['Title Work'] = ($titleName ? $titleName . ' - $' . number_format($titleAmt, 2) : '');
            if ($titleName) {
                if (! isset($commissionSummary[$titleName])) {
                    $commissionSummary[$titleName] = 0.0;
                }
                $commissionSummary[$titleName] += $titleAmt;
            }

            // checkout
            $chkId   = (int) ($row['checkout_completed_by'] ?? 0);
            $chkName = trim(($row['completion_first_name'] ?? '') . ' ' . ($row['completion_last_name'] ?? ''));
            $chkPct  = ($chkId && isset($overrideMap[$chkId])) ? $overrideMap[$chkId]['checkout_completed'] : 0;
            $chkAmt  = $grossProfitValue * ($chkPct / 100.0);
            $row['Checkout Completed'] = ($chkName ? $chkName . ' - $' . number_format($chkAmt, 2) : '');
            if ($chkName) {
                if (! isset($commissionSummary[$chkName])) {
                    $commissionSummary[$chkName] = 0.0;
                }
                $commissionSummary[$chkName] += $chkAmt;
            }

            // Store updated row
            $results[$k] = $row;
        }

        return [
            'results'           => $results,
            'fieldNames'        => array_values($fieldNames),
            'totalRows'         => $totalRows,
            'totalPages'        => $totalPages,
            'currentPage'       => $currentPage,
            'limit'             => $limit,
            'commissionSummary' => $commissionSummary,
        ];
    }

    /**
     * Single-query version for "Upsell Commissions".
     * Expects oTypeFilter = '0' or '1' (or any single value).
     * The controller merges data if '0,1' is needed.
     *
     * Now points to "upsells_processed" instead of "bbt_upsell_tracking".
     * Excludes user IDs NOT found in commission_override.
     */
    public function getUpsellCommissions(array $params): array
    {
        $fromDate           = $params['fromDate'];
        $toDate             = $params['toDate'];
        $oTypeFilter        = $params['oTypeFilter'];   // single, e.g. '0' or '1'
        $updateStatusFilter = $params['updateStatusFilter'];
        $paidStatusFilter   = $params['paidStatusFilter'];

        $limit       = $params['limit'];
        $currentPage = $params['currentPage'];

        // 1) Base rows from bbt_qb_sales_receipts
        $dealCommModel = new DealCommissionsModel();

        $queryBuilder = $dealCommModel
            ->select('
                bbt_qb_sales_receipts.id AS sr_id,
                bbt_qb_sales_receipts.inv_no,
                bbt_qb_sales_receipts.deal_id,
                bbt_qb_sales_receipts.update_status,
                bbt_qb_sales_receipts.createdat
            ')
            ->join('bbt_deals', 'bbt_deals.id = bbt_qb_sales_receipts.deal_id', 'left')
            ->where('o_type', $oTypeFilter)
            ->where('bbt_qb_sales_receipts.createdat >=', $fromDate)
            ->where('bbt_qb_sales_receipts.createdat <=', $toDate . ' 23:59:59');

        // Apply update_status filter
        if ($updateStatusFilter !== 'ALL') {
            $queryBuilder->where('update_status', $updateStatusFilter);
        }
        // Apply paid_status filter
        if ($paidStatusFilter === 'Unpaid Only') {
            $queryBuilder->where('bbt_deals.commission_paid', 0);
        } elseif ($paidStatusFilter === 'Paid Only') {
            $queryBuilder->where('bbt_deals.commission_paid', 1);
        }

        // 2) Count total
        $totalRows = $queryBuilder->countAllResults(false);

        // 3) Pagination
        if ($limit === 'ALL') {
            $rows       = $queryBuilder->findAll();
            $totalPages = 1;
        } else {
            $limitInt = (int) $limit;
            if ($limitInt <= 0) {
                $limitInt = 25;
            }
            $totalPages = max(1, (int) ceil($totalRows / $limitInt));
            if ($currentPage > $totalPages) {
                $currentPage = $totalPages;
            }
            $offset = ($currentPage - 1) * $limitInt;
            $rows   = $queryBuilder->findAll($limitInt, $offset);
        }

        if (empty($rows)) {
            // No matching receipts
            return [
                'results'                => [],
                'fieldNames'             => ['inv_no','Revenue','Gross Profit'],
                'totalRows'              => 0,
                'totalPages'             => 1,
                'currentPage'            => 1,
                'limit'                  => $limit,
                'upsellCommissionSummary'=> [],
            ];
        }

        // 4) Collect all deal_ids
        $dealIds = array_column($rows, 'deal_id');
        $dealIds = array_unique($dealIds);

        // 5) Pull from upsells_processed (instead of bbt_upsell_tracking)
        $upsellModel = new UpsellsProcessedModel();
        // Optionally filter by date range or is_paid here if desired
        $upsellRows = $upsellModel
            ->whereIn('deal_id', $dealIds)
            ->findAll();

        // Build upsellByDeal structure
        // Each deal_id will have a sum of gross profit and also a sum of retail (for revenue)
        $upsellByDeal   = []; // [deal_id => [ 'total_gp'=>..., 'total_retail'=>..., 'users'=> [ userId => sumGp ]]]
        $allSalesmanIds = [];

        foreach ($upsellRows as $u) {
            $dId    = $u['deal_id'];
            $smId   = (int) $u['salesman'];
            $gp     = (float) $u['gross_profit'];
            $retail = (float) $u['retail'];
            if (! isset($upsellByDeal[$dId])) {
                $upsellByDeal[$dId] = [
                    'total_gp' => 0.0,
                    'total_retail' => 0.0,
                    'users'    => [],
                ];
            }
            $upsellByDeal[$dId]['total_gp'] += $gp;
            $upsellByDeal[$dId]['total_retail'] += $retail;
            if (! isset($upsellByDeal[$dId]['users'][$smId])) {
                $upsellByDeal[$dId]['users'][$smId] = 0.0;
            }
            $upsellByDeal[$dId]['users'][$smId] += $gp;
            $allSalesmanIds[] = $smId;
        }

        // 6) Pull from orders_processed for the same date range
        $ordersModel = new OrdersProcessedModel();
        $ordersRows  = $ordersModel
            ->where('createdat >=', $fromDate)
            ->where('createdat <=', $toDate . ' 23:59:59')
            ->findAll();

        // Build srIdMap => to get inv_no & update_status quickly
        $srIdMap = [];
        foreach ($rows as $r) {
            $srIdMap[$r['sr_id']] = [
                'inv_no'        => $r['inv_no'],
                'update_status' => (int) $r['update_status'],
            ];
        }

        // Collect user IDs from orders_processed as well
        foreach ($ordersRows as $oRow) {
            $allSalesmanIds[] = (int) $oRow['salesman'];
        }
        $allSalesmanIds = array_unique($allSalesmanIds);

        // 7) Build user info & override map (for upsells)
        $salesmanInfo  = []; // userId => "John Doe"
        $overrideMap   = []; // userId => upsells% float

        if (! empty($allSalesmanIds)) {
            $db = db_connect();
            // user info
            $userRows = $db->table('bbt_users')
                ->select('id, first_name, last_name')
                ->whereIn('id', $allSalesmanIds)
                ->get()
                ->getResultArray();

            foreach ($userRows as $uRow) {
                $fullName = trim($uRow['first_name'] . ' ' . $uRow['last_name']);
                $salesmanInfo[$uRow['id']] = $fullName;
            }

            // commission overrides
            $overrideModel = new CommissionOverrideModel();
            $ovRows        = $overrideModel->whereIn('user_id', $allSalesmanIds)->findAll();

            // Filter out users who do NOT appear in commission_override
            $validUserIds = [];
            foreach ($ovRows as $ov) {
                $validUserIds[] = (int) $ov['user_id'];
            }

            $allSalesmanIds = array_intersect($allSalesmanIds, $validUserIds);
            $allSalesmanIds = array_unique($allSalesmanIds);

            // Build $overrideMap ONLY for these valid user IDs
            foreach ($ovRows as $ov) {
                $uId = (int) $ov['user_id'];
                if (in_array($uId, $allSalesmanIds)) {
                    $overrideMap[$uId] = (float) ($ov['upsells'] ?? 0);
                }
            }
        }

        // 8) Build table columns: inv_no, Revenue, "Gross Profit", plus one column per unique user
        //    (But now $allSalesmanIds is filtered to only those in overrides)
        $fieldNames  = ['inv_no', 'Revenue', 'Gross Profit'];
        $userNameMap = []; // userId => column name

        foreach ($allSalesmanIds as $uid) {
            $colName = isset($salesmanInfo[$uid]) ? $salesmanInfo[$uid] : ('User#'.$uid);
            $fieldNames[]      = $colName;
            $userNameMap[$uid] = $colName;
        }

        // 9) Build the final results
        $results                 = [];
        $upsellCommissionSummary = []; // per user column

        // Initialize summary with zero for each user column
        foreach ($userNameMap as $col) {
            $upsellCommissionSummary[$col] = 0.0;
        }

        // 9a) One row per bbt_qb_sales_receipts
        foreach ($rows as $r) {
            $invNo  = $r['inv_no'];
            $dealId = $r['deal_id'];

            $grossProfitSum = 0.0;
            $userSums       = []; // colName => commission

            if (isset($upsellByDeal[$dealId])) {
                $grossProfitSum = $upsellByDeal[$dealId]['total_gp'];
            }
            $revenueValueForUpsell = 0.0;
            if (isset($upsellByDeal[$dealId])) {
                $revenueValueForUpsell = $upsellByDeal[$dealId]['total_retail'];
            }

            // Build row data with inv_no, Revenue, and Gross Profit
            $rowData = [
                'inv_no'       => $invNo,
                'Revenue'      => number_format($revenueValueForUpsell, 2),
                'Gross Profit' => number_format($grossProfitSum, 2),
            ];

            // Fill user columns (if upsell data exists, distribute commissions)
            if (isset($upsellByDeal[$dealId])) {
                foreach ($upsellByDeal[$dealId]['users'] as $smId => $rawGp) {
                    if (isset($overrideMap[$smId]) && $overrideMap[$smId] > 0) {
                        $pct        = $overrideMap[$smId];
                        $commission = $rawGp * ($pct / 100.0);
                        $colName    = $userNameMap[$smId] ?? ('User#'.$smId);

                        if (! isset($userSums[$colName])) {
                            $userSums[$colName] = 0.0;
                        }
                        $userSums[$colName] += $commission;
                    }
                }
            }

            foreach ($userNameMap as $uid => $colN) {
                $amt            = isset($userSums[$colN]) ? $userSums[$colN] : 0.0;
                $rowData[$colN] = number_format($amt, 2);
                $upsellCommissionSummary[$colN] += $amt;
            }

            $results[] = $rowData;
        }

        // 9b) Append rows from orders_processed IF update_status=0, and user has override>0
        foreach ($ordersRows as $oRow) {
            $srId       = (int) $oRow['qb_sales_receipt_id'];
            $salesmanId = (int) $oRow['salesman'];
            $gp         = (float) $oRow['gross_profit'];

            if (! isset($srIdMap[$srId])) {
                continue;
            }
            if ($srIdMap[$srId]['update_status'] !== 0) {
                continue;
            }
            if (! isset($overrideMap[$salesmanId]) || $overrideMap[$salesmanId] <= 0) {
                continue;
            }

            $invNo = $srIdMap[$srId]['inv_no'];
            $pct   = $overrideMap[$salesmanId];
            $commissionAmount = $gp * ($pct / 100.0);
            $revenueValueForOrder = (float) $oRow['retail'];

            $rowData = [
                'inv_no'       => $invNo,
                'Revenue'      => number_format($revenueValueForOrder, 2),
                'Gross Profit' => number_format($gp, 2),
            ];

            foreach ($userNameMap as $uid => $colN) {
                if ($uid === $salesmanId) {
                    $rowData[$colN] = number_format($commissionAmount, 2);
                    $upsellCommissionSummary[$colN] += $commissionAmount;
                } else {
                    $rowData[$colN] = '0.00';
                }
            }

            $results[] = $rowData;
        }

        return [
            'results'                => $results,
            'fieldNames'             => $fieldNames,
            'totalRows'              => $totalRows,
            'totalPages'             => $totalPages,
            'currentPage'            => $currentPage,
            'limit'                  => $limit,
            'upsellCommissionSummary'=> $upsellCommissionSummary,
        ];
    }
}
