<?php

namespace App\Libraries;

use CodeIgniter\Database\BaseConnection;

class PartnerPayoutGridRetention
{
    /**
     * Fetch PRR rows with vehicle_type AND segment joined.
     */
    private static function fetchPrrPvtRows(BaseConnection $db, int $agentId): array
    {
        if ($agentId <= 0) {
            return [];
        }

        return $db->table('partner_retention_rate prr')
            ->select('prr.vehicle_type_id, prr.segment_id, prr.retention_rate, 
                      vt.vehicle_type, seg.segment')
            ->join('vehicle_type vt', 'vt.id = prr.vehicle_type_id', 'left')
            ->join('partner_segment seg', 'seg.id = prr.segment_id', 'left')
            ->where('prr.agent_id', $agentId)
            ->where('prr.is_active', 1)
            ->get()
            ->getResultArray();
    }

    public static function normalizeVehicleTypeLabel(?string $s): string
    {
        if ($s === null || $s === '') {
            return '';
        }
        return strtolower(trim(preg_replace('/\s+/u', ' ', $s)));
    }

    public static function normalizeSegmentLabel(?string $s): string
    {
        if ($s === null || $s === '') {
            return '';
        }
        return strtolower(trim(preg_replace('/\s+/u', ' ', $s)));
    }

    /**
     * Build meta map keyed by vt+segment combinations for resolution.
     */
    private static function partnerMetaMapFromPrrRows(array $prrRows): array
    {
        $meta = [];
        foreach ($prrRows as $r) {
            if (!array_key_exists('retention_rate', $r)
                || $r['retention_rate'] === null
                || $r['retention_rate'] === '') {
                continue;
            }

            $partnerRr = (float) $r['retention_rate'];
            $vtRaw     = isset($r['vehicle_type']) ? trim((string) $r['vehicle_type']) : '';
            $segRaw    = isset($r['segment'])       ? trim((string) $r['segment'])      : '';
            $partnerVt = $vtRaw !== '' ? $vtRaw : 'N/A';

            $entry = [
                'partner_VT' => $partnerVt,
                'partner_RR' => $partnerRr,
            ];

            $normVt  = self::normalizeVehicleTypeLabel($vtRaw);
            $normSeg = self::normalizeSegmentLabel($segRaw);
            $vid     = isset($r['vehicle_type_id']) ? (int) $r['vehicle_type_id'] : 0;
            $sid     = isset($r['segment_id'])      ? (int) $r['segment_id']      : 0;

            // Key 1: most specific — vt + segment (text)
            if ($normVt !== '' && $normSeg !== '') {
                $meta["vt::{$normVt}::seg::{$normSeg}"] = $entry;
            }

            // Key 2: vt only (text fallback)
            if ($normVt !== '') {
                $meta["vt::{$normVt}"] = $meta["vt::{$normVt}"] ?? $entry;
            }

            // Key 3: by IDs — most specific
            if ($vid > 0 && $sid > 0) {
                $meta["id::{$vid}::seg::{$sid}"] = $entry;
            }

            // Key 4: by VT id only (fallback)
            if ($vid > 0) {
                $meta["id::{$vid}"] = $meta["id::{$vid}"] ?? $entry;
            }
        }

        return $meta;
    }

    public static function buildPartnerMetaMap(BaseConnection $db, int $agentId): array
    {
        return self::partnerMetaMapFromPrrRows(self::fetchPrrPvtRows($db, $agentId));
    }

    public static function buildMasterNormLabelToVehicleTypeIdMap(BaseConnection $db): array
    {
        $rows = $db->table('vehicle_type')
            ->select('id, vehicle_type')
            ->where('is_active', 1)
            ->get()
            ->getResultArray();

        $out = [];
        foreach ($rows as $r) {
            $k = self::normalizeVehicleTypeLabel($r['vehicle_type'] ?? '');
            if ($k === '') continue;
            $out[$k] = (int) $r['id'];
        }

        return $out;
    }

    public static function buildMasterNormSegmentToIdMap(BaseConnection $db): array
    {
        $rows = $db->table('partner_segment')
            ->select('id, segment')
            ->where('is_active', 1)
            ->get()
            ->getResultArray();

        $out = [];
        foreach ($rows as $r) {
            $k = self::normalizeSegmentLabel($r['segment'] ?? '');
            if ($k === '') continue;
            $out[$k] = (int) $r['id'];
        }

        return $out;
    }

    /**
     * Resolve partner meta for a grid row.
     * Priority:
     *   1. vt text + segment text  (most specific)
     *   2. vt_id + segment_id
     *   3. vt text only
     *   4. vt_id only
     */
    public static function resolvePartnerMetaForGridRow(
        array $row,
        array $metaMap,
        ?array $masterNormLabelToVtId = null,
        ?array $masterNormSegToId = null
    ): ?array {
        $normVt  = self::normalizeVehicleTypeLabel(isset($row['vehicle_type']) ? (string) $row['vehicle_type'] : '');
        $normSeg = self::normalizeSegmentLabel(isset($row['segment']) ? (string) $row['segment'] : '');

        // 1. Most specific: vt text + segment text
        if ($normVt !== '' && $normSeg !== '') {
            $k = "vt::{$normVt}::seg::{$normSeg}";
            if (isset($metaMap[$k])) return $metaMap[$k];
        }

        // 2. By IDs: vt_id + segment_id
        $vid = isset($row['vehicle_type_id']) ? (int) $row['vehicle_type_id'] : 0;
        $sid = isset($row['segment_id'])      ? (int) $row['segment_id']      : 0;

        if ($vid <= 0 && $masterNormLabelToVtId !== null && $normVt !== '') {
            $vid = $masterNormLabelToVtId[$normVt] ?? 0;
        }
        if ($sid <= 0 && $masterNormSegToId !== null && $normSeg !== '') {
            $sid = $masterNormSegToId[$normSeg] ?? 0;
        }

        if ($vid > 0 && $sid > 0) {
            $k = "id::{$vid}::seg::{$sid}";
            if (isset($metaMap[$k])) return $metaMap[$k];
        }

        // 3. Fallback: vt text only
        if ($normVt !== '') {
            $k = "vt::{$normVt}";
            if (isset($metaMap[$k])) return $metaMap[$k];
        }

        // 4. Fallback: vt id only
        if ($vid > 0) {
            $k = "id::{$vid}";
            if (isset($metaMap[$k])) return $metaMap[$k];
        }

        return null;
    }

    public static function gridPayoutDisplayManagerAccounts($raw): string
    {
        if ($raw === null || $raw === '' || $raw === '-') return '-';
        $v = self::toFloat($raw);
        if ($v === null || $v <= 0) return '-';
        return self::formatGridNumericDisplay($v);
    }

    /**
     * Agent payout column: partner_RR − original base value.
     * oa = original comp, ob = original tp, oc = original od
     * comp = partner_RR - oa
     * tp   = partner_RR - ob
     * od   = partner_RR - oc
     */
    public static function agentPayoutColumnFromPartnerRr($raw, float $partnerRr): string
    {
        if ($raw === null || $raw === '' || $raw === '-') return '-';
        $base = self::toFloat($raw);
        if ($base === null) return '-';

        // Formula: partner_RR − base
        $result = $partnerRr - $base;

        return self::formatGridNumericDisplay($result);
    }

    private static function formatGridNumericDisplay(float $v): string
    {
        if (abs($v - round($v)) < 0.00001) {
            return (string) (int) round($v);
        }
        return rtrim(rtrim(number_format($v, 2, '.', ''), '0'), '.');
    }

    public static function applyManagerAccountsDisplayToRows(array $rows): array
    {
        foreach ($rows as &$row) {
            if (array_key_exists('comp', $row)) $row['comp'] = self::gridPayoutDisplayManagerAccounts($row['comp'] ?? null);
            if (array_key_exists('tp', $row))   $row['tp']   = self::gridPayoutDisplayManagerAccounts($row['tp']   ?? null);
            if (array_key_exists('od', $row))    $row['od']   = self::gridPayoutDisplayManagerAccounts($row['od']   ?? null);
        }
        unset($row);
        return $rows;
    }

    private static function retentionMapFromPrrRows(array $prrRows): array
    {
        $map = [];
        foreach ($prrRows as $r) {
            if (!array_key_exists('retention_rate', $r)
                || $r['retention_rate'] === null
                || $r['retention_rate'] === '') continue;

            $rate    = (float) $r['retention_rate'];
            $name    = isset($r['vehicle_type']) ? trim((string) $r['vehicle_type']) : '';
            $segName = isset($r['segment'])      ? trim((string) $r['segment'])      : '';
            $vid     = isset($r['vehicle_type_id']) ? (int) $r['vehicle_type_id'] : 0;
            $sid     = isset($r['segment_id'])      ? (int) $r['segment_id']      : 0;

            $normVt  = self::normalizeVehicleTypeLabel($name);
            $normSeg = self::normalizeSegmentLabel($segName);

            if ($normVt !== '' && $normSeg !== '') {
                $map["vt::{$normVt}::seg::{$normSeg}"] = $rate;
            }
            if ($normVt !== '') {
                $map["vt::{$normVt}"] = $map["vt::{$normVt}"] ?? $rate;
            }
            if ($vid > 0 && $sid > 0) {
                $map["id::{$vid}::seg::{$sid}"] = $rate;
            }
            if ($vid > 0) {
                $map["id::{$vid}"] = $map["id::{$vid}"] ?? $rate;
            }
        }
        return $map;
    }

    public static function buildRetentionMap(BaseConnection $db, int $agentId): array
    {
        return self::retentionMapFromPrrRows(self::fetchPrrPvtRows($db, $agentId));
    }

    /**
     * Main apply for Agent role:
     *
     * oa = original comp  (raw DB value)
     * ob = original tp    (raw DB value)
     * oc = original od    (raw DB value)
     *
     * comp = partner_RR - oa
     * tp   = partner_RR - ob
     * od   = partner_RR - oc
     *
     * Also sets: partner_VT, partner_RR, partner_segment,
     *            partner_comp, partner_tp, partner_od
     */
    public static function applyToRows(array $rows, int $agentId, BaseConnection $db): array
    {
        if ($agentId <= 0 || $rows === []) return $rows;

        $prrRows   = self::fetchPrrPvtRows($db, $agentId);
        $metaMap   = self::partnerMetaMapFromPrrRows($prrRows);
        $masterVt  = self::buildMasterNormLabelToVehicleTypeIdMap($db);
        $masterSeg = self::buildMasterNormSegmentToIdMap($db);

        foreach ($rows as &$row) {

            // ── Store originals as oa / ob / oc ──────────────────────────────────
            $row['oa'] = $row['comp'] ?? null;   // original comp
            $row['ob'] = $row['tp']   ?? null;   // original tp
            $row['oc'] = $row['od']   ?? null;   // original od

            $pm = self::resolvePartnerMetaForGridRow($row, $metaMap, $masterVt, $masterSeg);

            if ($pm === null) {
                // No retention rate found for this row
                $row['partner_VT']      = '-';
                $row['partner_RR']      = '-';
                $row['partner_segment'] = '-';
                $row['partner_comp']    = '-';
                $row['partner_tp']      = '-';
                $row['partner_od']      = '-';
                $row['comp']            = '-';
                $row['tp']              = '-';
                $row['od']              = '-';
                continue;
            }

            $rr = (float) $pm['partner_RR'];

            // ── Partner meta columns ──────────────────────────────────────────────
            $row['partner_VT']      = $pm['partner_VT'];
            $row['partner_RR']      = $pm['partner_RR'];
            $row['partner_segment'] = $row['segment'] ?? '-';

            // partner_* = original − RR  (display only, same as before)
            $row['partner_comp'] = self::agentPayoutColumnFromPartnerRr($row['oa'], $rr);
            $row['partner_tp']   = self::agentPayoutColumnFromPartnerRr($row['ob'], $rr);
            $row['partner_od']   = self::agentPayoutColumnFromPartnerRr($row['oc'], $rr);

            // ── Overwrite comp / tp / od = partner_RR − original ─────────────────
            // $row['comp'] = self::agentPayoutColumnFromPartnerRr($row['oa'], $rr);
            // $row['tp']   = self::agentPayoutColumnFromPartnerRr($row['ob'], $rr);
            // $row['od']   = self::agentPayoutColumnFromPartnerRr($row['oc'], $rr);

            $row['comp'] = (($x = self::agentPayoutColumnFromPartnerRr($row['oa'], $rr)) > 0) ? $x : '-';
            $row['tp']   = (($y = self::agentPayoutColumnFromPartnerRr($row['ob'], $rr)) > 0) ? $y : '-';
            $row['od']   = (($z = self::agentPayoutColumnFromPartnerRr($row['oc'], $rr)) > 0) ? $z : '-';
        }
        unset($row);

        return $rows;
    }

    public static function retentionForRow(array $row, array $map): ?float
    {
        $key = self::normalizeVehicleTypeLabel(isset($row['vehicle_type']) ? (string) $row['vehicle_type'] : '');
        if ($key !== '' && isset($map[$key])) return $map[$key];
        $vid = isset($row['vehicle_type_id']) ? (int) $row['vehicle_type_id'] : 0;
        if ($vid > 0 && isset($map['id:' . $vid])) return $map['id:' . $vid];
        return null;
    }

    public static function adjustPayoutValue($raw, float $rate)
    {
        if ($raw === null || $raw === '' || $raw === '-') return $raw;
        $base = self::toFloat($raw);
        if ($base === null) return $raw;
        $adj = $base - $rate;
        if ($adj <= 0) return '-';
        if (abs($adj - round($adj)) < 0.00001) return (int) round($adj);
        return rtrim(rtrim(number_format($adj, 2, '.', ''), '0'), '.');
    }

    private static function toFloat($raw): ?float
    {
        if (is_numeric($raw)) return (float) $raw;
        $clean = preg_replace('/[^0-9.\-]/', '', (string) $raw);
        if ($clean === '' || $clean === '-' || $clean === '.') return null;
        return (float) $clean;
    }
}