<?php
/**
 * FuelEntries — captures refuel events and immediately posts a balanced
 * journal to the ledger. Payment method → credit-account map is the same
 * shape as Expenses; kept duplicated here for now to avoid coupling.
 *
 * Two mutating actions:
 *   create(...)   — inserts the row, posts a JE, links je_id back
 *   void(...)     — posts a reversing JE, marks status = voided
 * Editing a posted entry is disallowed (accounting practice: post an
 * adjustment or void + re-enter).
 */
class FuelEntries extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function f($v): float  { return (float)str_replace(',', '', (string)$v); }
    private function n($v): ?int   { $v = preg_replace('/[^0-9\-]/', '', (string)$v); return $v === '' ? null : (int)$v; }
    private function nn(int $v): ?int { return $v > 0 ? $v : null; }

    /**
     * Rewired to the client's COA on 2026-08-27 (Turn 1.5 · slice 21).
     * Fuel debit lands in 7009 (Fuel); payment method maps to the same
     * bank/cash/mobile/card accounts as the Expenses posting.
     */
    private const PAYMENT_METHOD_TO_CREDIT = [
        'cash'          => '3003',   // Cash
        'petty_cash'    => '3003',   // Cash (lumped)
        'mobile_money'  => '3006',   // Mobile Money Float
        'bank_transfer' => '3001',   // Bank - CRDB
        'cheque'        => '3001',   // Bank - CRDB
        'fuel_card'     => '3009',   // Fuel Card Account
    ];
    private const FUEL_EXPENSE_CODE = '7009';   // Fuel (was 5010)

    /* =========================================================
     *  Read
     * ========================================================= */
    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['f.deleted = 0', 'f.tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];

        if (!empty($filters['q'])) {
            $where[] = '(f.entry_no LIKE :q OR f.station_name LIKE :q OR f.receipt_no LIKE :q OR t.reg_no LIKE :q OR d.full_name LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['truck_id']))        { $where[] = 'f.truck_id = :tid';         $params[':tid']  = (int)$filters['truck_id']; }
        if (!empty($filters['driver_id']))       { $where[] = 'f.driver_id = :did';        $params[':did']  = (int)$filters['driver_id']; }
        if (!empty($filters['status']))          { $where[] = 'f.status = :st';            $params[':st']   = $this->s($filters['status']); }
        if (!empty($filters['variance_status'])) { $where[] = 'f.variance_status = :vst';  $params[':vst']  = $this->s($filters['variance_status']); }
        if (!empty($filters['from_date']))       { $where[] = 'f.entry_date >= :fd';       $params[':fd']   = $this->s($filters['from_date']); }
        if (!empty($filters['to_date']))         { $where[] = 'f.entry_date <= :td';       $params[':td']   = $this->s($filters['to_date']); }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect(
            "SELECT COUNT(*) AS n
               FROM fuel_entries f
          LEFT JOIN trucks   t ON t.c0d = f.truck_id
          LEFT JOIN drivers  d ON d.c0d = f.driver_id
              WHERE $whereSql", $params
        );
        $total = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT f.c0d, f.entry_no, f.entry_date, f.entry_time,
                    f.truck_id, f.driver_id, f.trip_id,
                    f.station_name, f.fuel_type,
                    f.liters, f.price_per_liter, f.gross_amount, f.currency,
                    f.odometer_reading, f.payment_method, f.status,
                    f.sensor_variance_l, f.variance_status,
                    t.reg_no    AS truck_reg,
                    d.full_name AS driver_name,
                    trp.trip_no AS trip_no,
                    je.je_no    AS je_no
               FROM fuel_entries f
          LEFT JOIN trucks   t   ON t.c0d   = f.truck_id
          LEFT JOIN drivers  d   ON d.c0d   = f.driver_id
          LEFT JOIN trips    trp ON trp.c0d = f.trip_id
          LEFT JOIN journal_entries je ON je.c0d = f.je_id
              WHERE $whereSql
           ORDER BY f.entry_date DESC, f.entry_time DESC, f.c0d DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect(
            "SELECT f.*,
                    t.reg_no AS truck_reg, t.make, t.model, t.capacity_kg, t.fuel_tank_liters,
                    d.full_name AS driver_name, d.phone_1 AS driver_phone,
                    trp.trip_no AS trip_no,
                    je.je_no AS je_no, void_je.je_no AS void_je_no
               FROM fuel_entries f
          LEFT JOIN trucks   t       ON t.c0d = f.truck_id
          LEFT JOIN drivers  d       ON d.c0d = f.driver_id
          LEFT JOIN trips    trp     ON trp.c0d = f.trip_id
          LEFT JOIN journal_entries je      ON je.c0d = f.je_id
          LEFT JOIN journal_entries void_je ON void_je.c0d = f.void_je_id
              WHERE f.c0d = :id AND f.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function nextEntryNo(): string
    {
        $rs = (new DB())->mySelect(
            "SELECT entry_no FROM fuel_entries WHERE entry_no LIKE 'FUEL-%' ORDER BY c0d DESC LIMIT 1", []
        );
        $last = (!empty($rs) && preg_match('/(\d+)$/', $rs[0]['entry_no'], $m)) ? (int)$m[1] : 0;
        return 'FUEL-' . str_pad((string)($last + 1), 5, '0', STR_PAD_LEFT);
    }

    /* =========================================================
     *  Create — inserts entry + posts JE atomically. If JE post
     *  fails we do NOT commit the entry (caller sees ok=false).
     * ========================================================= */
    public function create(array $d, int $uid, string $uname): array
    {
        $truckId = (int)($d['truck_id'] ?? 0);
        if (!$truckId) return ['ok' => false, 'msg' => 'Truck is required.', 'id' => 0];

        $liters = round($this->f($d['liters']          ?? 0), 3);
        $pplRaw = $this->f($d['price_per_liter'] ?? 0);
        $gross  = round($this->f($d['gross_amount']    ?? 0), 2);

        // Compute defaults: if gross missing, gross = liters * ppl; if ppl missing, ppl = gross / liters
        if ($gross <= 0 && $liters > 0 && $pplRaw > 0) $gross = round($liters * $pplRaw, 2);
        if ($pplRaw <= 0 && $liters > 0 && $gross > 0) $pplRaw = round($gross / $liters, 2);

        if ($liters <= 0)     return ['ok' => false, 'msg' => 'Litres must be greater than zero.', 'id' => 0];
        if ($gross  <= 0)     return ['ok' => false, 'msg' => 'Amount must be greater than zero.', 'id' => 0];

        $method    = $this->s($d['payment_method'] ?? 'cash') ?: 'cash';
        $creditCode = self::PAYMENT_METHOD_TO_CREDIT[$method] ?? '1010';
        $entryDate  = $this->s($d['entry_date'] ?? '') ?: date('Y-m-d');
        $entryTime  = $this->s($d['entry_time'] ?? '') ?: null;

        $db     = new DB();
        $entryNo = $this->nextEntryNo();

        // Insert entry FIRST (status='pending' until JE posts, then update to 'posted')
        $r = $db->insertUpdate(
            "INSERT INTO fuel_entries
                (tenant_id, entry_no, entry_date, entry_time,
                 truck_id, driver_id, trip_id,
                 station_name, station_location, fuel_type,
                 liters, price_per_liter, gross_amount, currency, odometer_reading,
                 payment_method, payment_reference, receipt_no, receipt_url, receipt_file_name,
                 status, notes, created_by, created_on)
             VALUES
                (:tenant, :en, :ed, :et,
                 :tid, :did, :trip,
                 :sn, :sl, :ft,
                 :lit, :ppl, :gross, :cur, :odo,
                 :pm, :pref, :rn, :rurl, :rname,
                 'pending', :notes, :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':en'     => $entryNo,
                ':ed'     => $entryDate, ':et' => $entryTime,
                ':tid'    => $truckId,
                ':did'    => $this->nn((int)($d['driver_id'] ?? 0)),
                ':trip'   => $this->nn((int)($d['trip_id']   ?? 0)),
                ':sn'     => $this->s($d['station_name']     ?? ''),
                ':sl'     => $this->s($d['station_location'] ?? ''),
                ':ft'     => $this->s(($d['fuel_type'] ?? '') ?: 'diesel'),
                ':lit'    => $liters, ':ppl' => round($pplRaw, 2), ':gross' => $gross,
                ':cur'    => $this->s(($d['currency'] ?? '') ?: (defined('DEFAULT_CURRENCY') ? DEFAULT_CURRENCY : 'TZS')),
                ':odo'    => $this->n($d['odometer_reading'] ?? ''),
                ':pm'     => $method,
                ':pref'   => $this->s($d['payment_reference'] ?? ''),
                ':rn'     => $this->s($d['receipt_no']        ?? ''),
                ':rurl'   => $this->s($d['receipt_url']       ?? ''),
                ':rname'  => $this->s($d['receipt_file_name'] ?? ''),
                ':notes'  => $this->s($d['notes'] ?? ''),
                ':uid'    => $uid,
            ]
        );
        if ($r['success'] < 1) return ['ok' => false, 'msg' => 'Could not save fuel entry.', 'id' => 0];
        $id = (int)$r['last_id'];

        // Now post the JE. Tags include fuel_entry_id so reports can drill straight to source.
        $tags = [
            'truck_id'      => $truckId,
            'driver_id'     => (int)($d['driver_id'] ?? 0) ?: 0,
            'trip_id'       => (int)($d['trip_id']   ?? 0) ?: 0,
            'fuel_entry_id' => $id,
        ];

        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => $entryDate,
            'source_type'    => 'FUEL',
            'source_id'      => $id,
            'source_ref'     => $entryNo,
            'narrative'      => 'Fuel · ' . number_format($liters, 2) . ' L @ ' . $this->s($d['station_name'] ?? 'station') . ' · ' . ($d['currency'] ?? 'TZS'),
            'currency'       => $d['currency'] ?? 'TZS',
            'fx_rate'        => 1.0,
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines' => [
                array_merge([
                    'account_code' => self::FUEL_EXPENSE_CODE,
                    'debit'        => $gross,
                    'credit'       => 0,
                    'narrative'    => 'Fuel expense · ' . $entryNo,
                ], $tags),
                array_merge([
                    'account_code' => $creditCode,
                    'debit'        => 0,
                    'credit'       => $gross,
                    'narrative'    => 'Paid via ' . $method,
                ], $tags),
            ],
        ]);

        if (!$res['ok']) {
            // Journal failed — mark entry as failed so it stays in the list but flagged.
            $db->insertUpdate(
                "UPDATE fuel_entries SET status = 'je_failed', notes = CONCAT(COALESCE(notes,''), '\n\n[JE FAILED: ', :err, ']')
                  WHERE c0d = :id",
                [':err' => $res['msg'], ':id' => $id]
            );
            return ['ok' => false, 'msg' => 'Entry saved but JE post failed: ' . $res['msg'], 'id' => $id];
        }

        // Success — link JE and update status
        $db->insertUpdate(
            "UPDATE fuel_entries SET status = 'posted', je_id = :jeid,
                                     edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':jeid' => $res['id'], ':uid' => $uid, ':id' => $id]
        );

        // Bump the truck's current_odometer if this reading is higher.
        // Route through $this->n() to strip commas from Cleave-formatted input;
        // a direct (int) cast of "45,200" gives 45, silently failing the WHERE.
        $odoInt = $this->n($d['odometer_reading'] ?? '');
        if ($odoInt !== null && $odoInt > 0) {
            $db->insertUpdate(
                "UPDATE trucks SET current_odometer = :odo, edited_by = :uid, edited_on = NOW()
                  WHERE c0d = :tid AND current_odometer < :odo",
                [':odo' => $odoInt, ':uid' => $uid, ':tid' => $truckId]
            );
        }

        return ['ok' => true, 'msg' => 'Fuel entry saved · JE ' . $res['je_no'] . ' posted.', 'id' => $id, 'entry_no' => $entryNo];
    }

    /**
     * Void a posted fuel entry. Posts a reversing JE (Dr the original credit
     * account / Cr Fuel Expense) and marks the entry voided. Original JE
     * stays in the ledger (never delete accounting records).
     */
    public function voidEntry(int $id, string $reason, int $uid, string $uname): array
    {
        $e = $this->byId($id);
        if (!$e) return ['ok' => false, 'msg' => 'Not found.'];
        if ($e['status'] === 'voided') return ['ok' => false, 'msg' => 'Already voided.'];
        if (empty($e['je_id']))         return ['ok' => false, 'msg' => 'No JE to reverse — set to voided manually if needed.'];

        $method     = $e['payment_method'] ?: 'cash';
        $creditCode = self::PAYMENT_METHOD_TO_CREDIT[$method] ?? '1010';
        $gross      = (float)$e['gross_amount'];

        $tags = [
            'truck_id'      => (int)$e['truck_id'],
            'driver_id'     => (int)($e['driver_id'] ?? 0) ?: 0,
            'trip_id'       => (int)($e['trip_id']   ?? 0) ?: 0,
            'fuel_entry_id' => $id,
        ];

        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => date('Y-m-d'),
            'source_type'    => 'FUEL_REVERSAL',
            'source_id'      => $id,
            'source_ref'     => $e['entry_no'] . '/VOID',
            'narrative'      => 'Void of ' . $e['entry_no'] . ' — ' . ($this->s($reason) ?: 'no reason given'),
            'currency'       => $e['currency'],
            'fx_rate'        => 1.0,
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines' => [
                array_merge([
                    'account_code' => $creditCode,
                    'debit'        => $gross,
                    'credit'       => 0,
                    'narrative'    => 'Reverse payment · ' . $e['entry_no'],
                ], $tags),
                array_merge([
                    'account_code' => self::FUEL_EXPENSE_CODE,
                    'debit'        => 0,
                    'credit'       => $gross,
                    'narrative'    => 'Reverse fuel expense · ' . $e['entry_no'],
                ], $tags),
            ],
        ]);
        if (!$res['ok']) return ['ok' => false, 'msg' => 'Reversal JE failed: ' . $res['msg']];

        (new DB())->insertUpdate(
            "UPDATE fuel_entries SET status = 'voided', void_je_id = :jeid,
                                     void_reason = :rr, voided_by = :uid, voided_at = NOW(),
                                     edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':jeid' => $res['id'], ':rr' => $this->s($reason), ':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Voided · reversing JE ' . $res['je_no'] . ' posted.'];
    }

    public function softDelete(int $id, int $uid): array
    {
        (new DB())->insertUpdate(
            "UPDATE fuel_entries SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true];
    }

    /* =========================================================
     *  Analytics helpers (used by Truck view + Dashboard later)
     * ========================================================= */
    public function statsForTruck(int $truckId, int $days = 30): array
    {
        $days = max(1, min(365, $days));
        $rs = (new DB())->mySelect(
            "SELECT COUNT(*) AS entries, COALESCE(SUM(liters),0) AS total_liters,
                    COALESCE(SUM(gross_amount),0) AS total_cost,
                    MIN(odometer_reading) AS min_odo, MAX(odometer_reading) AS max_odo
               FROM fuel_entries
              WHERE truck_id = :tid AND deleted = 0 AND status = 'posted'
                AND entry_date >= DATE_SUB(CURDATE(), INTERVAL $days DAY)",
            [':tid' => $truckId]
        );
        $r = $rs[0] ?? ['entries'=>0,'total_liters'=>0,'total_cost'=>0,'min_odo'=>null,'max_odo'=>null];
        $km = ($r['max_odo'] && $r['min_odo']) ? ((int)$r['max_odo'] - (int)$r['min_odo']) : 0;
        $r['km_travelled']  = $km;
        $r['l_per_100km']   = ($km > 0 && $r['total_liters'] > 0) ? round(((float)$r['total_liters'] / $km) * 100, 2) : null;
        $r['cost_per_km']   = ($km > 0 && $r['total_cost']   > 0) ? round((float)$r['total_cost']   / $km,       2) : null;
        return $r;
    }
}
