<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Statement_model extends CI_Model
{
    protected $table = 'partner_transactions';

    public function __construct()
    {
        parent::__construct();
    }

    // ===============================================================
    // LECTURE
    // ===============================================================

    /**
     * Récupérer une transaction par ID
     */
    public function get($id)
    {
        return $this->db
            ->select('partner_transactions.*, users.full_name AS created_by_name')
            ->join('users', 'users.id = partner_transactions.created_by')
            ->where('partner_transactions.id', $id)
            ->get($this->table)
            ->row();
    }

    /**
     * Relevé de compte avec filtres et calcul du solde courant
     */
    public function get_statement($filters = [], $limit = 50, $offset = 0)
    {
        $this->db
            ->select('partner_transactions.*, users.full_name AS created_by_name')
            ->join('users', 'users.id = partner_transactions.created_by');

        if ( ! empty($filters['type'])) {
            $this->db->where('partner_transactions.type', $filters['type']);
        }
        if ( ! empty($filters['date_from'])) {
            $this->db->where('partner_transactions.transaction_date >=', $filters['date_from']);
        }
        if ( ! empty($filters['date_to'])) {
            $this->db->where('partner_transactions.transaction_date <=', $filters['date_to']);
        }
        if ( ! empty($filters['search'])) {
            $this->db->group_start()
                ->like('partner_transactions.reference', $filters['search'])
                ->or_like('partner_transactions.description', $filters['search'])
            ->group_end();
        }

        return $this->db
            ->order_by('partner_transactions.transaction_date', 'ASC')
            ->order_by('partner_transactions.id', 'ASC')
            ->get($this->table, $limit, $offset)
            ->result();
    }

    /**
     * Nombre total de transactions (pour pagination)
     */
    public function count_statement($filters = [])
    {
        if ( ! empty($filters['type'])) {
            $this->db->where('type', $filters['type']);
        }
        if ( ! empty($filters['date_from'])) {
            $this->db->where('transaction_date >=', $filters['date_from']);
        }
        if ( ! empty($filters['date_to'])) {
            $this->db->where('transaction_date <=', $filters['date_to']);
        }
        return $this->db->count_all_results($this->table);
    }

    /**
     * Solde courant du compte partenaire
     * = Total crédits (avances) - Total débits (dépenses)
     */
    public function get_current_balance()
    {
        $row = $this->db
            ->select('COALESCE(SUM(credit), 0) - COALESCE(SUM(debit), 0) AS balance', FALSE)
            ->get($this->table)
            ->row();
        return $row ? (float) $row->balance : 0;
    }

    /**
     * Totaux : avances reçues, dépenses imputées, ajustements
     */
    public function get_totals($filters = [])
    {
        $this->db->select('
            COALESCE(SUM(credit), 0) AS total_credits,
            COALESCE(SUM(debit), 0) AS total_debits,
            COALESCE(SUM(credit), 0) - COALESCE(SUM(debit), 0) AS balance
        ', FALSE);

        if ( ! empty($filters['date_from'])) {
            $this->db->where('transaction_date >=', $filters['date_from']);
        }
        if ( ! empty($filters['date_to'])) {
            $this->db->where('transaction_date <=', $filters['date_to']);
        }

        return $this->db->get($this->table)->row();
    }

    /**
     * Solde cumulé avant une date (pour calculer le running balance)
     */
    public function get_balance_before($date)
    {
        $row = $this->db
            ->select('COALESCE(SUM(credit), 0) - COALESCE(SUM(debit), 0) AS balance', FALSE)
            ->where('transaction_date <', $date)
            ->get($this->table)
            ->row();
        return $row ? (float) $row->balance : 0;
    }

    // ===============================================================
    // ÉCRITURE
    // ===============================================================

    /**
     * Créer une transaction
     */
    public function create($data)
    {
        // Calculer le solde courant après cette opération
        $current = $this->get_current_balance();
        $data['balance'] = $current + (float)($data['credit'] ?? 0) - (float)($data['debit'] ?? 0);

        $this->db->insert($this->table, $data);
        return $this->db->insert_id();
    }

    /**
     * Enregistrer un cash advance (crédit au compte partenaire + crédit au compte bancaire)
     */
    public function record_cash_advance($advance_data, $bank_cashbox_id, $user_id)
    {
        $this->db->trans_start();

        // 1. Écriture dans le compte partenaire
        $transaction = [
            'transaction_date' => $advance_data['date'],
            'reference'        => $advance_data['reference'],
            'description'      => $advance_data['description'],
            'debit'            => 0,
            'credit'           => $advance_data['amount'],
            'type'             => 'advance',
            'related_id'       => null,
            'created_by'       => $user_id,
        ];

        $tx_id = $this->create($transaction);

        // 2. Créditer le compte bancaire Wassim
        $this->load->model('Cashbox_model');
        $this->Cashbox_model->update_balance($bank_cashbox_id, $advance_data['amount'], 'credit');

        $this->db->trans_complete();

        return $this->db->trans_status() ? $tx_id : false;
    }

    /**
     * Enregistrer une dépense validée dans le compte partenaire (débit)
     * Appelé automatiquement quand un batch est envoyé
     */
    public function record_expense($expense_id, $reference, $description, $amount, $user_id)
    {
        $transaction = [
            'transaction_date' => date('Y-m-d'),
            'reference'        => $reference,
            'description'      => $description,
            'debit'            => $amount,
            'credit'           => 0,
            'type'             => 'expense',
            'related_id'       => $expense_id,
            'created_by'       => $user_id,
        ];

        return $this->create($transaction);
    }

    /**
     * Enregistrer un ajustement (correction manuelle)
     */
    public function record_adjustment($data, $user_id)
    {
        $transaction = [
            'transaction_date' => $data['date'],
            'reference'        => $data['reference'],
            'description'      => $data['description'],
            'debit'            => (float) ($data['debit'] ?? 0),
            'credit'           => (float) ($data['credit'] ?? 0),
            'type'             => 'adjustment',
            'related_id'       => null,
            'created_by'       => $user_id,
        ];

        return $this->create($transaction);
    }

    /**
     * Générer la prochaine référence d'avance
     * Format: ADV-2026-001
     */
    public function next_advance_reference()
    {
        $year = date('Y');
        $last = $this->db
            ->select('reference')
            ->like('reference', 'ADV-' . $year, 'after')
            ->order_by('id', 'DESC')
            ->get($this->table, 1)
            ->row();

        if ($last && preg_match('/ADV-\d{4}-(\d+)/', $last->reference, $m)) {
            $next = (int) $m[1] + 1;
        } else {
            $next = 1;
        }

        return 'ADV-' . $year . '-' . str_pad($next, 3, '0', STR_PAD_LEFT);
    }

    /**
     * Recalculer tous les soldes (running balance) — utile après correction
     */
    public function recalculate_balances()
    {
        $rows = $this->db
            ->order_by('transaction_date', 'ASC')
            ->order_by('id', 'ASC')
            ->get($this->table)
            ->result();

        $balance = 0;
        foreach ($rows as $row) {
            $balance = $balance + (float) $row->credit - (float) $row->debit;
            $this->db->where('id', $row->id)->update($this->table, ['balance' => $balance]);
        }

        return $balance;
    }
}
