<?php

namespace App\Models;

use CodeIgniter\Model;

/**
 * This model focuses on retrieving only the *earliest* deposits (o_type=0, d_type=1)
 * that fall within a given date range, joined to subdeals so we can display one row per subdeal.
 */
class SourceToSaleDepositsModel extends Model
{
    /**
     * Gets the earliest deposit per deal_id (o_type=0, d_type=1),
     * ensures that earliest deposit date is within [from_date, to_date],
     * and then returns one row per subdeal for those deals.
     *
     * Now also retrieves the customer's phone from bbt_customers,
     * and the lead_source_name by joining the deals->journey->lead_source chain.
     */
    public function getEarliestDepositsWithinRange(array $params): array
    {
        // Extract parameters
        $fromDate    = $params['from_date']    ?? null;
        $toDate      = $params['to_date']      ?? null;
        $limit       = $params['limit']        ?? 'ALL';
        $currentPage = (int)($params['current_page'] ?? 1);

        /**
         * 1) Subquery: find earliest deposit per deal_id (o_type=0, d_type=1).
         * 2) Join subquery to bbt_deal_payments so we only get the "first deposit" for each deal.
         * 3) Join deals -> customers -> subdeals -> journey -> lead_source.
         * 4) Filter by the chosen date range [fromDate, toDate].
         */

        // Step 1: Build subquery
        $subQueryBuilder = $this->db->table('bbt_deal_payments')
            ->select('deal_id, MIN(createdat) as earliest')
            ->where('o_type', '0')
            ->where('d_type', '1')
            ->groupBy('deal_id');

        $earliestSubQuery = $subQueryBuilder->getCompiledSelect();

        // Step 2/3: Main builder
        $builder = $this->db->table('bbt_deal_payments p');

        // Select columns including phone from bbt_customers (aliased c), subdeals (aliased s),
        // plus lead source name (aliased ls)
        $builder->select("
            p.deal_id,
            d.cbo                                     AS cob_number,
            DATE_FORMAT(p.createdat, '%Y-%m-%d')      AS deposit_date,
            IFNULL(c.phone, 'N/A')                    AS phone_number,
            s.model_price                             AS unit_value,
            IFNULL(ls.lead_source_name, '')           AS lead_source_name
        ", false);

        // Inner join subquery on matching deal_id & earliest date
        $builder->join("($earliestSubQuery) earliest", "earliest.deal_id = p.deal_id AND earliest.earliest = p.createdat", 'INNER');

        // Left join deals -> customers -> subdeals
        $builder->join('bbt_deals d', 'd.id = p.deal_id', 'left');
        $builder->join('bbt_customers c', 'c.id = d.customer_id', 'left');
        $builder->join('bbt_subdeals s', 's.deal_id = p.deal_id', 'left');

        // Additional left joins for lead source:
        //   deals -> journey -> lead_source
        $builder->join('bbt_journey j', 'j.id = d.journey_id', 'left');
        $builder->join('bbt_lead_source ls', 'ls.id = j.lead_source', 'left');

        // Step 4: Restrict earliest deposit to the selected date range
        if (!empty($fromDate)) {
            $builder->where('p.createdat >=', $fromDate);
        }
        if (!empty($toDate)) {
            $builder->where('p.createdat <=', $toDate . ' 23:59:59');
        }

        // Also ensure it’s a deposit (o_type=0, d_type=1)
        $builder->where('p.o_type', '0');
        $builder->where('p.d_type', '1');

        // Count total rows (before applying limit) for pagination
        $countBuilder = clone $builder;
        $totalRows = $countBuilder->countAllResults(false);

        // Pagination: apply limit + offset if not ALL
        if ($limit !== 'ALL') {
            $limit = (int)$limit;
            $offset = ($currentPage - 1) * $limit;
            $builder->limit($limit, $offset);
        }

        // Sort by deal_id ascending
        $builder->orderBy('p.deal_id', 'ASC');

        // Execute
        $query   = $builder->get();
        $results = $query->getResultArray();

        // Figure out total pages
        $totalPages = 1;
        if ($limit !== 'ALL') {
            $totalPages = (int)ceil($totalRows / $limit);
            if ($totalPages < 1) {
                $totalPages = 1;
            }
        }

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