<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\MenuModel;
use App\Models\DealCommissionsModel;
use App\Models\CommissionOverrideModel;
use App\Models\OrdersProcessedModel;
use App\Models\UpsellsProcessedModel; // <-- Make sure to import the UpsellsProcessedModel

class DealCommissions extends Controller
{
    public function index()
    {
        // 1. Must be logged in
        if (! service('auth')->loggedIn()) {
            return redirect()->to('/login');
        }

        // 2. Must be admin
      //  $user = service('auth')->user();
       // if ((int) $user->is_admin !== 1) {
       //     return redirect()->to('/')->with('error', 'Access denied: Admins //only.');
       // }

        // 3. Nav menu
        $menuModel = new MenuModel();
        $menuItems = $menuModel->orderBy('order', 'ASC')->findAll();

        // 4. Pagination limit (default "ALL")
        $limitOptions = [10, 25, 50, 100, 500, 'ALL'];
        $limit = $this->request->getVar('limit') ?? 'ALL';
        $limit = in_array($limit, array_map('strval', $limitOptions), true) ? $limit : 'ALL';

        $currentPage = (int) ($this->request->getVar('page') ?? 1);
        if ($currentPage < 1) {
            $currentPage = 1;
        }

        // 5. Filters
        //$oTypeFilter        = $this->request->getVar('o_type')        ?? '0';
        $oTypeFilter        = $this->request->getVar('o_type')        ?? '0,1';
        $updateStatusFilter = $this->request->getVar('update_status') ?? '0';
        $paidStatusFilter   = $this->request->getVar('paid_status')   ?? 'ALL';

        // 6. Date Range
        $today        = date('Y-m-d');
        $firstOfMonth = date('Y-m-01');
        $fromDate     = $this->request->getVar('from_date') ?: $firstOfMonth;
        $toDate       = $this->request->getVar('to_date')   ?: $today;

        // Toggles (show/hide columns)
        $showCogsParam        = $this->request->getVar('show_cogs')         ?? '0';
        $showRevenueParam     = $this->request->getVar('show_revenue')      ?? '0';
        $showGrossProfitParam = $this->request->getVar('show_gross_profit') ?? '1';

        // Convert them to booleans
        $showCogs        = ($showCogsParam === '1');
        $showRevenue     = ($showRevenueParam === '1');
        $showGrossProfit = ($showGrossProfitParam === '1');

        // 7. Build params array to pass to our library
        $params = [
            'fromDate'           => $fromDate,
            'toDate'             => $toDate,
            'oTypeFilter'        => $oTypeFilter, // could be '0', '1', or '0,1'
            'updateStatusFilter' => $updateStatusFilter,
            'paidStatusFilter'   => $paidStatusFilter,
            'showCogs'           => $showCogs,
            'showRevenue'        => $showRevenue,
            'showGrossProfit'    => $showGrossProfit,
            'limit'              => $limit,
            'currentPage'        => $currentPage,
        ];

        // We'll use the DealCommissionLib to fetch data
        $dealLib = new \App\Libraries\DealCommissionLib();

        /**
         * -------------
         * DEALS vs ORDERS vs BOTH
         * -------------
         * If oTypeFilter == '0,1', do two separate queries (for 0 and 1) and merge.
         */
        if ($oTypeFilter === '0,1') {
            // ========== 1) Deals ==========
            $dealParams = $params;
            $dealParams['oTypeFilter'] = '0';
            $dealDataArr = $dealLib->getDealCommissions($dealParams);

            // ========== 2) Orders ==========
            $orderParams = $params;
            $orderParams['oTypeFilter'] = '1';
            $orderDataArr = $dealLib->getDealCommissions($orderParams);

            // Merge main "results"
            $combinedResults = array_merge($dealDataArr['results'], $orderDataArr['results']);

            // Merge fieldNames
            $combinedFieldNames = array_values(
                array_unique(array_merge($dealDataArr['fieldNames'], $orderDataArr['fieldNames']))
            );

            // Combine totalRows
            $combinedTotalRows  = $dealDataArr['totalRows'] + $orderDataArr['totalRows'];
            $combinedTotalPages = 1; // or more advanced logic

            // Merge commissionSummary
            $mergedSummary = $dealDataArr['commissionSummary'];
            foreach ($orderDataArr['commissionSummary'] as $user => $amt) {
                if (! isset($mergedSummary[$user])) {
                    $mergedSummary[$user] = 0;
                }
                $mergedSummary[$user] += $amt;
            }

            $dataArr = [
                'results'           => $combinedResults,
                'fieldNames'        => $combinedFieldNames,
                'commissionSummary' => $mergedSummary,
                'totalRows'         => $combinedTotalRows,
                'totalPages'        => $combinedTotalPages,
                'currentPage'       => 1,
                'limit'             => $limit,
            ];
        } else {
            // Single call
            $dataArr = $dealLib->getDealCommissions($params);
        }

        // 8b. Upsell Commissions
        if ($oTypeFilter === '0,1') {
            // Do two calls, then merge
            $dealUpsellParams = $params;
            $dealUpsellParams['oTypeFilter'] = '0';
            $upsellArrDeals = $dealLib->getUpsellCommissions($dealUpsellParams);

            $orderUpsellParams = $params;
            $orderUpsellParams['oTypeFilter'] = '1';
            $upsellArrOrders = $dealLib->getUpsellCommissions($orderUpsellParams);

            // Merge results
            $mergedUpsellResults = array_merge($upsellArrDeals['results'], $upsellArrOrders['results']);
            $mergedUpsellFields = array_values(
                array_unique(array_merge($upsellArrDeals['fieldNames'], $upsellArrOrders['fieldNames']))
            );

            // Merge summary
            $mergedUpsellSummary = $upsellArrDeals['upsellCommissionSummary'];
            foreach ($upsellArrOrders['upsellCommissionSummary'] as $uName => $amt) {
                if (! isset($mergedUpsellSummary[$uName])) {
                    $mergedUpsellSummary[$uName] = 0;
                }
                $mergedUpsellSummary[$uName] += $amt;
            }

            $mergedUpsellRows  = $upsellArrDeals['totalRows'] + $upsellArrOrders['totalRows'];
            $mergedUpsellPages = 1;

            $upsellArr = [
                'results'                 => $mergedUpsellResults,
                'fieldNames'              => $mergedUpsellFields,
                'totalRows'               => $mergedUpsellRows,
                'totalPages'              => $mergedUpsellPages,
                'currentPage'             => 1,
                'limit'                   => $limit,
                'upsellCommissionSummary' => $mergedUpsellSummary,
            ];
        } else {
            // Single call
            $upsellArr = $dealLib->getUpsellCommissions($params);
        }

        // 9. Prepare data for the View
        $viewData = [
            'menuItems'            => $menuItems,
            'limitOptions'         => $limitOptions,
            'oTypeFilter'          => $oTypeFilter,
            'updateStatusFilter'   => $updateStatusFilter,
            'paidStatusFilter'     => $paidStatusFilter,
            'fromDate'             => $fromDate,
            'toDate'               => $toDate,
            'showCogs'             => $showCogs,
            'showRevenue'          => $showRevenue,
            'showGrossProfit'      => $showGrossProfit,

            // Deal commissions data
            'results'              => $dataArr['results'],
            'fieldNames'           => $dataArr['fieldNames'],
            'commissionSummary'    => $dataArr['commissionSummary'],
            'currentPage'          => $dataArr['currentPage'],
            'limit'                => $dataArr['limit'],
            'totalRows'            => $dataArr['totalRows'],
            'totalPages'           => $dataArr['totalPages'],

            // Upsell commissions data
            'upsellResults'          => $upsellArr['results'],
            'upsellFieldNames'       => $upsellArr['fieldNames'],
            'upsellTotalRows'        => $upsellArr['totalRows'],
            'upsellTotalPages'       => $upsellArr['totalPages'],
            'upsellCommissionSummary'=> $upsellArr['upsellCommissionSummary'] ?? [],
        ];

        // 10. Render our view
        return view('dealcommissions/index', $viewData);
    }

    /**
     * Update a single field via AJAX (inline edit).
     */
    public function updateField()
    {
        if (! service('auth')->loggedIn()) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Not logged in.',
            ]);
        }
        $user = service('auth')->user();
        if ((int) $user->is_admin !== 1) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Access denied.',
            ]);
        }

        $tableName = 'bbt_qb_sales_receipts';
        $rowId     = $this->request->getPost('id');
        $field     = $this->request->getPost('field');
        $value     = $this->request->getPost('value');

        if (empty($rowId) || empty($field)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Missing rowId or field name.',
            ]);
        }

        // Validate column
        $db = db_connect();
        $validDBColumns = $db->getFieldNames($tableName);
        if (! in_array($field, $validDBColumns)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Field "' . $field . '" does not exist in database. (Computed/Joined fields cannot be edited.)',
            ]);
        }

        // Update
        $builder = $db->table($tableName);
        $builder->where('id', $rowId);

        try {
            $builder->update([$field => $value]);
            if ($db->affectedRows() >= 0) {
                return $this->response->setJSON([
                    'success' => true,
                    'message' => 'Field updated successfully.',
                ]);
            } else {
                return $this->response->setJSON([
                    'success' => false,
                    'message' => 'No rows were updated.',
                ]);
            }
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Exception: ' . $e->getMessage(),
            ]);
        }
    }

    /**
     * Process Kits
     */
    public function processKits()
    {
        // Must be logged in & admin
        if (! service('auth')->loggedIn()) {
            return redirect()->to('/login');
        }
        $user = service('auth')->user();
        if ((int) $user->is_admin !== 1) {
            return redirect()->to('/')->with('error', 'Access denied: Admins only.');
        }

        // 2. Instantiate the new model
        $model = new \App\Models\SpecialKitProcessedModel();

        // 3. Run the logic from the model
        $resultMessage = $model->runSpecialKitProcess();

        // 4. Redirect back
        return redirect()->to('/commission-settings')
            ->with('message', $resultMessage);
    }

    /**
     * Handle "Process Commissions" action.
     * Now includes logic for:
     *   1) deals -> commissions_paid
     *   2) orders_processed -> commissions_paid
     *   3) upsells_processed -> commissions_paid
     */
    public function processCommissions()
    {
        // Must be logged in & admin
        if (! service('auth')->loggedIn()) {
            return redirect()->to('/login');
        }
        $user = service('auth')->user();
        if ((int) $user->is_admin !== 1) {
            return redirect()->to('/')->with('error', 'Access denied: Admins only.');
        }

        // Same filters as index()
        //$oTypeFilter        = $this->request->getVar('o_type')        ?? '0';
        $oTypeFilter        = $this->request->getVar('o_type')        ?? '0,1';
        $updateStatusFilter = $this->request->getVar('update_status') ?? '0';
        $fromDate           = $this->request->getVar('from_date')     ?? date('Y-m-01');
        $toDate             = $this->request->getVar('to_date')       ?? date('Y-m-d');

        // Query matching bbt_qb_sales_receipts
        $dealCommModel = new DealCommissionsModel();
        $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
            ")
            ->join('bbt_deals', 'bbt_deals.id = bbt_qb_sales_receipts.deal_id', 'left');

        // Handle o_type
        if ($oTypeFilter === '0,1') {
            $queryBuilder->whereIn('o_type', [0, 1]);
        } else {
            $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);
        }

        $results = $queryBuilder->findAll();
        if (empty($results)) {
            return redirect()->to('/dealcommissions?o_type=' . $oTypeFilter)
                ->with('error', 'No deals found to process commissions on.');
        }

        // Check if any deals are already in commissions_paid
        $dealIds = array_column($results, 'deal_id');
        $dealIds = array_unique($dealIds);

        $db = db_connect();
        $alreadyProcessed = $db->table('commissions_paid')
            ->whereIn('deal_id', $dealIds)
            ->countAllResults();

        if ($alreadyProcessed > 0) {
            // find which deals
            $duplicatesQuery = $db->table('commissions_paid')
                ->select('deal_id')
                ->whereIn('deal_id', $dealIds)
                ->groupBy('deal_id')
                ->get();
            $duplicateRows = $duplicatesQuery->getResultArray();
            $duplicatesList = array_column($duplicateRows, 'deal_id');

            return redirect()->to('/dealcommissions?o_type=' . $oTypeFilter)
                ->with(
                    'error',
                    'Cannot process commission because these deals already exist in "commissions_paid": '
                    . implode(', ', $duplicatesList)
                );
        }

        // Next payout batch
        $maxBatchRow = $db->table('commissions_paid')
            ->selectMax('payout_batch')
            ->get()
            ->getRowArray();
        $nextBatch = (int) ($maxBatchRow['payout_batch'] ?? 0) + 1;

        // Gather user overrides
        $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);

        // Build overrideMap, also storing 'upsells' for orders/upsells
        $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),
                    'upsells'            => (float) ($rowOverride['upsells']            ?? 0),
                ];
            }
        }

        // (1) Insert data for deals
        $insertData = [];
        $todayDate  = date('Y-m-d');

        foreach ($results as $row) {
            $revenue = (float) $row['model_price']
                     + (float) $row['parts_total']
                     + (float) $row['fee_total']
                     + (float) $row['custom_option_total']
                     - (float) $row['discount'];

            $totalCogs = (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'];

            $grossProfit = $revenue - $totalCogs;

            // deposit
            $depositId = (int) ($row['deposit_collected_by'] ?? 0);
            if ($depositId > 0) {
                $pct = $overrideMap[$depositId]['deposit'] ?? 0;
                $amt = $grossProfit * ($pct / 100.0);

                $insertData[] = [
                    'from_date'      => $fromDate,
                    'to_date'        => $toDate,
                    'payout_batch'   => $nextBatch,
                    'payout_date'    => $todayDate,
                    'user_id'        => $depositId,
                    'deal_id'        => $row['deal_id'],
                    'order_id'       => 0, // or null
                    'job_fuction_id' => 1, // deposit
                    'amount'         => number_format($amt, 2, '.', ''),
                    'revoke_status'  => 0,
                ];
            }

            // customer care
            $careId = (int) ($row['customer_care_by'] ?? 0);
            if ($careId > 0) {
                $pct = $overrideMap[$careId]['customer_care'] ?? 0;
                $amt = $grossProfit * ($pct / 100.0);

                $insertData[] = [
                    'from_date'      => $fromDate,
                    'to_date'        => $toDate,
                    'payout_batch'   => $nextBatch,
                    'payout_date'    => $todayDate,
                    'user_id'        => $careId,
                    'deal_id'        => $row['deal_id'],
                    'order_id'       => 0, // or null
                    'job_fuction_id' => 2, // customer care
                    'amount'         => number_format($amt, 2, '.', ''),
                    'revoke_status'  => 0,
                ];
            }

            // title work
            $titleId = (int) ($row['titlework_completed_by'] ?? 0);
            if ($titleId > 0) {
                $pct = $overrideMap[$titleId]['title_work'] ?? 0;
                $amt = $grossProfit * ($pct / 100.0);

                $insertData[] = [
                    'from_date'      => $fromDate,
                    'to_date'        => $toDate,
                    'payout_batch'   => $nextBatch,
                    'payout_date'    => $todayDate,
                    'user_id'        => $titleId,
                    'deal_id'        => $row['deal_id'],
                    'order_id'       => 0, // or null
                    'job_fuction_id' => 3, // title work
                    'amount'         => number_format($amt, 2, '.', ''),
                    'revoke_status'  => 0,
                ];
            }

            // checkout
            $checkoutId = (int) ($row['checkout_completed_by'] ?? 0);
            if ($checkoutId > 0) {
                $pct = $overrideMap[$checkoutId]['checkout_completed'] ?? 0;
                $amt = $grossProfit * ($pct / 100.0);

                $insertData[] = [
                    'from_date'      => $fromDate,
                    'to_date'        => $toDate,
                    'payout_batch'   => $nextBatch,
                    'payout_date'    => $todayDate,
                    'user_id'        => $checkoutId,
                    'deal_id'        => $row['deal_id'],
                    'order_id'       => 0, // or null
                    'job_fuction_id' => 4, // checkout
                    'amount'         => number_format($amt, 2, '.', ''),
                    'revoke_status'  => 0,
                ];
            }
        }

        if (empty($insertData)) {
            return redirect()->to('/dealcommissions?o_type=' . $oTypeFilter)
                ->with('error', 'No valid commission rows found (perhaps missing user IDs?).');
        }

        // Insert for deals
        $db->table('commissions_paid')->insertBatch($insertData);

        // Mark deals => commission_paid=1
        $db->table('bbt_deals')
            ->whereIn('id', $dealIds)
            ->update(['commission_paid' => 1]);

        // (2) Also process orders_processed rows in the same date range + is_paid=0
        $ordersModel = new OrdersProcessedModel();
        $ordersRows = $ordersModel
            ->where('createdat >=', $fromDate)
            ->where('createdat <=', $toDate . ' 23:59:59')
            ->where('is_paid', 0)
            ->findAll();

        if (! empty($ordersRows)) {
            $insertOps     = [];
            $ordersIdsPaid = [];

            foreach ($ordersRows as $oRow) {
                $salesmanId  = (int) $oRow['salesman'];
                $grossProfit = (float) $oRow['gross_profit'];

                // Only insert if user has an override row
                if (isset($overrideMap[$salesmanId])) {
                    $upsellPct  = $overrideMap[$salesmanId]['upsells'] ?? 0;
                    $commission = $grossProfit * ($upsellPct / 100.0);
                    $commission = number_format($commission, 2, '.', '');

                    $insertOps[] = [
                        'from_date'      => $fromDate,
                        'to_date'        => $toDate,
                        'payout_batch'   => $nextBatch,
                        'payout_date'    => $todayDate,
                        'user_id'        => $salesmanId,
                        'deal_id'        => null,              // per instructions
                        'order_id'       => $oRow['deal_id'],  // store orders_processed.deal_id
                        'job_fuction_id' => 5,                 // upsell commissions (orders)
                        'amount'         => $commission,
                        'revoke_status'  => 0,
                    ];

                    // Mark is_paid=1 since we did pay them
                    $ordersIdsPaid[] = $oRow['id'];
                }
            }

            // Insert new lines
            if (! empty($insertOps)) {
                $db->table('commissions_paid')->insertBatch($insertOps);
            }

            // Update is_paid=1 for these rows
            if (! empty($ordersIdsPaid)) {
                $db->table('orders_processed')
                    ->whereIn('id', $ordersIdsPaid)
                    ->update(['is_paid' => 1]);
            }
        }

        // (3) Process upsells_processed rows in the same date range + is_paid=0
        $upsellsModel = new UpsellsProcessedModel();
        $upsellsRows = $upsellsModel
            ->where('createdat >=', $fromDate)
            ->where('createdat <=', $toDate . ' 23:59:59')
            ->where('is_paid', 0)
            ->findAll();

        if (! empty($upsellsRows)) {
            $insertUpsellOps = [];
            $upsellIdsPaid   = [];

            foreach ($upsellsRows as $uRow) {
                $salesmanId  = (int) $uRow['salesman'];
                $grossProfit = (float) $uRow['gross_profit'];

                // Only insert if user has an override row
                if (isset($overrideMap[$salesmanId])) {
                    $upsellPct  = $overrideMap[$salesmanId]['upsells'] ?? 0;
                    $commission = $grossProfit * ($upsellPct / 100.0);
                    $commission = number_format($commission, 2, '.', '');

                    $insertUpsellOps[] = [
                        'from_date'      => $fromDate,
                        'to_date'        => $toDate,
                        'payout_batch'   => $nextBatch,
                        'payout_date'    => $todayDate,
                        'user_id'        => $salesmanId,
                        'deal_id'        => $uRow['deal_id'], // store upsell's deal_id
                        'order_id'       => null,             // keep null
                        'job_fuction_id' => 6,                // new job function ID for upsells
                        'amount'         => $commission,
                        'revoke_status'  => 0,
                    ];

                    // Mark them as paid
                    $upsellIdsPaid[] = $uRow['id'];
                }
            }

            // Insert new lines if found
            if (! empty($insertUpsellOps)) {
                $db->table('commissions_paid')->insertBatch($insertUpsellOps);
            }

            // Update is_paid=1 for these upsell rows
            if (! empty($upsellIdsPaid)) {
                $db->table('upsells_processed')
                    ->whereIn('id', $upsellIdsPaid)
                    ->update(['is_paid' => 1]);
            }
        }

        // (4) Redirect with success
        return redirect()->to('/dealcommissions?o_type=' . $oTypeFilter)
            ->with('message', 'Commissions processed successfully! Batch #' . $nextBatch);
    }
}
