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

class Cashbox_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    // ===============================================================
    // CAISSES
    // ===============================================================

    /**
     * Récupérer une caisse par ID
     */
    public function get($id)
    {
        return $this->db
            ->select('cashboxes.*, locations.name AS location_name, locations.code AS location_code')
            ->join('locations', 'locations.id = cashboxes.location_id', 'left')
            ->where('cashboxes.id', $id)
            ->get('cashboxes')
            ->row();
    }

    /**
     * Liste de toutes les caisses (avec filtres optionnels)
     */
    public function get_all($filters = [])
    {
        $this->db
            ->select('cashboxes.*, locations.name AS location_name')
            ->join('locations', 'locations.id = cashboxes.location_id', 'left');

        if ( ! empty($filters['type'])) {
            $this->db->where('cashboxes.type', $filters['type']);
        }
        if ( ! empty($filters['location_id'])) {
            $this->db->where('cashboxes.location_id', $filters['location_id']);
        }
        if (isset($filters['is_active'])) {
            $this->db->where('cashboxes.is_active', $filters['is_active']);
        }

        return $this->db->order_by('cashboxes.type', 'ASC')
                        ->order_by('cashboxes.name', 'ASC')
                        ->get('cashboxes')
                        ->result();
    }

    /**
     * Caisses accessibles par l'utilisateur (selon localisation)
     */
    /*public function get_accessible($user_location_id = null, $is_manager = false)
    {
        $this->db
            ->select('cashboxes.*, locations.name AS location_name')
            ->join('locations', 'locations.id = cashboxes.location_id', 'left')
            ->where('cashboxes.is_active', 1);

        // Les agents locaux ne voient que les caisses de leur localisation
        if ( ! $is_manager && $user_location_id) {
            $this->db->group_start()
                ->where('cashboxes.location_id', $user_location_id)
                ->or_where('cashboxes.type', 'central')
                ->or_where('cashboxes.type', 'bank')
            ->group_end();
        }

        return $this->db->order_by('cashboxes.type', 'ASC')->get('cashboxes')->result();
    }*/
    public function get_accessible($user_location_id = null, $is_manager = false)
    {
        $this->db
            ->select('cashboxes.*, locations.name AS location_name')
            ->join('locations', 'locations.id = cashboxes.location_id', 'left')
            ->where('cashboxes.is_active', 1);

        // Les agents locaux ne voient que les petites caisses de leur localisation
        if ( ! $is_manager && $user_location_id) {
            $this->db->where('cashboxes.location_id', $user_location_id);
        }

        return $this->db->order_by('cashboxes.type', 'ASC')->get('cashboxes')->result();
    }

    /**
     * Mettre à jour le solde d'une caisse (débit ou crédit relatif)
     */
    public function update_balance($cashbox_id, $amount, $operation = 'debit')
    {
        if ($operation === 'debit') {
            $this->db->set('current_balance', 'current_balance - ' . (float) $amount, FALSE);
        } else {
            $this->db->set('current_balance', 'current_balance + ' . (float) $amount, FALSE);
        }
        return $this->db->where('id', $cashbox_id)->update('cashboxes');
    }

    /**
     * Forcer le solde d'une caisse (initialisation / ajustement)
     */
    public function set_balance($cashbox_id, $new_balance)
    {
        return $this->db->where('id', $cashbox_id)->update('cashboxes', [
            'current_balance' => $new_balance,
        ]);
    }

    /**
     * Position de trésorerie globale
     */
    public function get_treasury_position()
    {
        return $this->db
            ->select('cashboxes.id, cashboxes.code, cashboxes.name, cashboxes.type, 
                      cashboxes.current_balance, cashboxes.currency, locations.name AS location_name')
            ->join('locations', 'locations.id = cashboxes.location_id', 'left')
            ->where('cashboxes.is_active', 1)
            ->order_by('FIELD(cashboxes.type, "bank", "central", "petty")', '', FALSE)
            ->get('cashboxes')
            ->result();
    }

    /**
     * Total trésorerie
     */
    public function get_total_balance()
    {
        $row = $this->db
            ->select_sum('current_balance', 'total')
            ->where('is_active', 1)
            ->get('cashboxes')
            ->row();
        return $row ? (float) $row->total : 0;
    }

    // ===============================================================
    // CLÔTURES DE CAISSE
    // ===============================================================

    /**
     * Récupérer une clôture par ID
     */
    public function get_closing($id)
    {
        return $this->db
            ->select('cash_closings.*, cashboxes.name AS cashbox_name, cashboxes.code AS cashbox_code,
                      users.full_name AS closed_by_name')
            ->join('cashboxes', 'cashboxes.id = cash_closings.cashbox_id')
            ->join('users', 'users.id = cash_closings.closed_by')
            ->where('cash_closings.id', $id)
            ->get('cash_closings')
            ->row();
    }

    /**
     * Liste des clôtures avec filtres
     */
    public function get_closings($filters = [], $limit = 20, $offset = 0)
    {
        $this->db
            ->select('cash_closings.*, cashboxes.name AS cashbox_name, cashboxes.code AS cashbox_code,
                      users.full_name AS closed_by_name')
            ->join('cashboxes', 'cashboxes.id = cash_closings.cashbox_id')
            ->join('users', 'users.id = cash_closings.closed_by');

        if ( ! empty($filters['cashbox_id'])) {
            $this->db->where('cash_closings.cashbox_id', $filters['cashbox_id']);
        }
        if ( ! empty($filters['status'])) {
            $this->db->where('cash_closings.status', $filters['status']);
        }
        if ( ! empty($filters['date_from'])) {
            $this->db->where('cash_closings.period_end >=', $filters['date_from']);
        }
        if ( ! empty($filters['date_to'])) {
            $this->db->where('cash_closings.period_end <=', $filters['date_to']);
        }

        return $this->db
            ->order_by('cash_closings.created_at', 'DESC')
            ->get('cash_closings', $limit, $offset)
            ->result();
    }

    /**
     * Nombre de clôtures (pagination)
     */
    public function count_closings($filters = [])
    {
        if ( ! empty($filters['cashbox_id'])) {
            $this->db->where('cashbox_id', $filters['cashbox_id']);
        }
        if ( ! empty($filters['status'])) {
            $this->db->where('status', $filters['status']);
        }
        return $this->db->count_all_results('cash_closings');
    }

    /**
     * Créer une clôture
     */
    public function create_closing($data)
    {
        $this->db->insert('cash_closings', $data);
        return $this->db->insert_id();
    }

    /**
     * Mettre à jour une clôture
     */
    public function update_closing($id, $data)
    {
        return $this->db->where('id', $id)->update('cash_closings', $data);
    }

    /**
     * Calculer les mouvements d'une caisse sur une période
     */
    public function get_period_movements($cashbox_id, $date_start, $date_end)
    {
        // Total des dépenses sur la période
        $expenses = $this->db
            ->select_sum('amount', 'total')
            ->where('cashbox_id', $cashbox_id)
            ->where('expense_date >=', $date_start)
            ->where('expense_date <=', $date_end)
            ->where_in('status', ['validated', 'batched'])
            ->get('expenses')
            ->row();

        // Total des transferts reçus
        $transfers_in = $this->db
            ->select_sum('amount', 'total')
            ->where('dest_cashbox_id', $cashbox_id)
            ->where('transfer_date >=', $date_start)
            ->where('transfer_date <=', $date_end)
            ->where('status', 'validated')
            ->get('transfers')
            ->row();

        // Total des transferts émis
        $transfers_out = $this->db
            ->select_sum('amount', 'total')
            ->where('source_cashbox_id', $cashbox_id)
            ->where('transfer_date >=', $date_start)
            ->where('transfer_date <=', $date_end)
            ->where('status', 'validated')
            ->get('transfers')
            ->row();

        return [
            'expenses'      => (float) ($expenses->total ?? 0),
            'transfers_in'  => (float) ($transfers_in->total ?? 0),
            'transfers_out' => (float) ($transfers_out->total ?? 0),
        ];
    }

    /**
     * Dernière clôture confirmée d'une caisse
     */
    public function get_last_closing($cashbox_id)
    {
        return $this->db
            ->where('cashbox_id', $cashbox_id)
            ->where('status', 'confirmed')
            ->order_by('period_end', 'DESC')
            ->get('cash_closings', 1)
            ->row();
    }

    // ===============================================================
    // TRANSFERTS INTERNES
    // ===============================================================

    /**
     * Récupérer un transfert par ID
     */
    public function get_transfer($id)
    {
        return $this->db
            ->select('transfers.*, 
                      src.name AS source_name, src.code AS source_code,
                      dst.name AS dest_name, dst.code AS dest_code,
                      creator.full_name AS created_by_name,
                      validator.full_name AS validated_by_name')
            ->join('cashboxes src', 'src.id = transfers.source_cashbox_id')
            ->join('cashboxes dst', 'dst.id = transfers.dest_cashbox_id')
            ->join('users creator', 'creator.id = transfers.created_by')
            ->join('users validator', 'validator.id = transfers.validated_by', 'left')
            ->where('transfers.id', $id)
            ->get('transfers')
            ->row();
    }

    /**
     * Liste des transferts avec filtres
     */
    public function get_transfers($filters = [], $limit = 20, $offset = 0)
    {
        $this->db
            ->select('transfers.*, 
                      src.name AS source_name, src.code AS source_code,
                      dst.name AS dest_name, dst.code AS dest_code,
                      creator.full_name AS created_by_name')
            ->join('cashboxes src', 'src.id = transfers.source_cashbox_id')
            ->join('cashboxes dst', 'dst.id = transfers.dest_cashbox_id')
            ->join('users creator', 'creator.id = transfers.created_by');

        if ( ! empty($filters['status'])) {
            $this->db->where('transfers.status', $filters['status']);
        }
        if ( ! empty($filters['source_cashbox_id'])) {
            $this->db->where('transfers.source_cashbox_id', $filters['source_cashbox_id']);
        }
        if ( ! empty($filters['dest_cashbox_id'])) {
            $this->db->where('transfers.dest_cashbox_id', $filters['dest_cashbox_id']);
        }
        if ( ! empty($filters['date_from'])) {
            $this->db->where('transfers.transfer_date >=', $filters['date_from']);
        }
        if ( ! empty($filters['date_to'])) {
            $this->db->where('transfers.transfer_date <=', $filters['date_to']);
        }

        return $this->db
            ->order_by('transfers.created_at', 'DESC')
            ->get('transfers', $limit, $offset)
            ->result();
    }

    /**
     * Nombre de transferts (pagination)
     */
    public function count_transfers($filters = [])
    {
        if ( ! empty($filters['status'])) {
            $this->db->where('status', $filters['status']);
        }
        if ( ! empty($filters['source_cashbox_id'])) {
            $this->db->where('source_cashbox_id', $filters['source_cashbox_id']);
        }
        return $this->db->count_all_results('transfers');
    }

    /**
     * Créer un transfert
     */
    public function create_transfer($data)
    {
        $this->db->insert('transfers', $data);
        return $this->db->insert_id();
    }

    /**
     * Mettre à jour un transfert
     */
    public function update_transfer($id, $data)
    {
        return $this->db->where('id', $id)->update('transfers', $data);
    }

    /**
     * Générer la prochaine référence de transfert
     * Format: TRF-2026-0001
     */
    public function next_transfer_reference()
    {
        $year = date('Y');
        $last = $this->db
            ->select('reference')
            ->like('reference', 'TRF-' . $year, 'after')
            ->order_by('id', 'DESC')
            ->get('transfers', 1)
            ->row();

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

        return 'TRF-' . $year . '-' . str_pad($next, 4, '0', STR_PAD_LEFT);
    }

    /**
     * Valider un transfert (avec mise à jour des soldes)
     */
    public function validate_transfer($id, $validator_id)
    {
        $transfer = $this->get_transfer($id);
        if ( ! $transfer || $transfer->status !== 'pending') {
            return false;
        }

        $this->db->trans_start();

        // Mettre à jour le transfert
        $this->update_transfer($id, [
            'status'       => 'validated',
            'validated_by' => $validator_id,
            'validated_at' => date('Y-m-d H:i:s'),
        ]);

        // Débiter la caisse source
        $this->update_balance($transfer->source_cashbox_id, $transfer->amount, 'debit');

        // Créditer la caisse destination
        $this->update_balance($transfer->dest_cashbox_id, $transfer->amount, 'credit');

        $this->db->trans_complete();

        return $this->db->trans_status();
    }

    /**
     * Rejeter un transfert
     */
    public function reject_transfer($id, $validator_id)
    {
        return $this->update_transfer($id, [
            'status'       => 'rejected',
            'validated_by' => $validator_id,
            'validated_at' => date('Y-m-d H:i:s'),
        ]);
    }

    // ===============================================================
    // HISTORIQUE DES MOUVEMENTS D'UNE CAISSE
    // ===============================================================

    /**
     * Récupérer tous les mouvements d'une caisse (dépenses + transferts)
     */
    public function get_movements($cashbox_id, $date_from = null, $date_to = null, $limit = 50)
    {
        $movements = [];

        // Dépenses
        $this->db
            ->select("expense_date AS move_date, expense_id AS reference, 
                      description, amount, 'expense' AS move_type, 'debit' AS direction")
            ->where('cashbox_id', $cashbox_id)
            ->where_in('status', ['validated', 'batched']);

        if ($date_from) $this->db->where('expense_date >=', $date_from);
        if ($date_to)   $this->db->where('expense_date <=', $date_to);

        $expenses = $this->db->get('expenses')->result_array();
        $movements = array_merge($movements, $expenses);

        // Transferts reçus
        $this->db
            ->select("transfer_date AS move_date, reference, 
                      CONCAT('Transfert de ', src.name) AS description, 
                      transfers.amount, 'transfer_in' AS move_type, 'credit' AS direction")
            ->join('cashboxes src', 'src.id = transfers.source_cashbox_id')
            ->where('transfers.dest_cashbox_id', $cashbox_id)
            ->where('transfers.status', 'validated');

        if ($date_from) $this->db->where('transfer_date >=', $date_from);
        if ($date_to)   $this->db->where('transfer_date <=', $date_to);

        $in = $this->db->get('transfers')->result_array();
        $movements = array_merge($movements, $in);

        // Transferts émis
        $this->db
            ->select("transfer_date AS move_date, reference, 
                      CONCAT('Transfert vers ', dst.name) AS description, 
                      transfers.amount, 'transfer_out' AS move_type, 'debit' AS direction")
            ->join('cashboxes dst', 'dst.id = transfers.dest_cashbox_id')
            ->where('transfers.source_cashbox_id', $cashbox_id)
            ->where('transfers.status', 'validated');

        if ($date_from) $this->db->where('transfer_date >=', $date_from);
        if ($date_to)   $this->db->where('transfer_date <=', $date_to);

        $out = $this->db->get('transfers')->result_array();
        $movements = array_merge($movements, $out);

        // Trier par date décroissante
        usort($movements, function($a, $b) {
            return strcmp($b['move_date'], $a['move_date']);
        });

        return array_slice($movements, 0, $limit);
    }
}
