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

class Batch_model extends CI_Model
{
    protected $table = 'batches';

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

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

    public function get($id)
    {
        return $this->db
            ->select('batches.*, users.full_name AS created_by_name')
            ->join('users', 'users.id = batches.created_by')
            ->where('batches.id', $id)
            ->get($this->table)
            ->row();
    }

    public function get_list($filters = [], $limit = 20, $offset = 0)
    {
        $this->db
            ->select('batches.*, users.full_name AS created_by_name')
            ->join('users', 'users.id = batches.created_by');

        if ( ! empty($filters['status'])) {
            $this->db->where('batches.status', $filters['status']);
        }
        if ( ! empty($filters['year'])) {
            $this->db->where('batches.year', $filters['year']);
        }

        return $this->db
            ->order_by('batches.year', 'DESC')
            ->order_by('batches.week_number', 'DESC')
            ->get($this->table, $limit, $offset)
            ->result();
    }

    public function count_list($filters = [])
    {
        if ( ! empty($filters['status'])) {
            $this->db->where('status', $filters['status']);
        }
        if ( ! empty($filters['year'])) {
            $this->db->where('year', $filters['year']);
        }
        return $this->db->count_all_results($this->table);
    }

    /**
     * Récupérer les dépenses d'un batch
     */
    public function get_batch_expenses($batch_id)
    {
        return $this->db
            ->select('expenses.*, 
                      expense_categories.label AS category_label,
                      locations.name AS location_name, locations.code AS location_code,
                      cashboxes.name AS cashbox_name,
                      creator.full_name AS created_by_name,
                      validator.full_name AS validated_by_name')
            ->join('expense_categories', 'expense_categories.id = expenses.category_id')
            ->join('locations', 'locations.id = expenses.location_id')
            ->join('cashboxes', 'cashboxes.id = expenses.cashbox_id', 'left')
            ->join('users creator', 'creator.id = expenses.created_by')
            ->join('users validator', 'validator.id = expenses.validated_by', 'left')
            ->where('expenses.batch_id', $batch_id)
            ->order_by('expenses.expense_date', 'ASC')
            ->order_by('expenses.expense_id', 'ASC')
            ->get('expenses')
            ->result();
    }

    /**
     * Récupérer les dépenses validées non encore batchées
     */
    public function get_unbatched_expenses()
    {
        return $this->db
            ->select('expenses.*, 
                      expense_categories.label AS category_label,
                      locations.name AS location_name, locations.code AS location_code')
            ->join('expense_categories', 'expense_categories.id = expenses.category_id')
            ->join('locations', 'locations.id = expenses.location_id')
            ->where('expenses.status', 'validated')
            ->where('expenses.batch_id IS NULL', null, FALSE)
            ->order_by('expenses.expense_date', 'ASC')
            ->get('expenses')
            ->result();
    }

    public function count_unbatched()
    {
        return $this->db
            ->where('status', 'validated')
            ->where('batch_id IS NULL', null, FALSE)
            ->count_all_results('expenses');
    }

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

    /**
     * Créer un batch et y affecter les dépenses sélectionnées
     */
    public function create_batch($expense_ids, $user_id)
    {
        if (empty($expense_ids)) return false;

        $year = date('Y');
        $week = date('W');

        // Vérifier si un batch existe déjà pour cette semaine
        $existing = $this->db
            ->where('year', $year)
            ->where('week_number', $week)
            ->get($this->table)
            ->row();

        if ($existing) {
            // Utiliser la semaine suivante ou un suffixe
            $week = $week . 'b';
        }

        $batch_id_str = 'BATCH-' . $year . '-' . str_pad($week, 2, '0', STR_PAD_LEFT);

        // Calculer le total
        $expenses = $this->db
            ->select('SUM(amount) AS total, COUNT(*) AS count')
            ->where_in('id', $expense_ids)
            ->where('status', 'validated')
            ->where('batch_id IS NULL', null, FALSE)
            ->get('expenses')
            ->row();

        if ( ! $expenses || $expenses->count == 0) return false;

        // Déterminer la période
        $period = $this->db
            ->select('MIN(expense_date) AS start_date, MAX(expense_date) AS end_date')
            ->where_in('id', $expense_ids)
            ->get('expenses')
            ->row();

        $this->db->trans_start();

        // Créer le batch
        $this->db->insert($this->table, [
            'batch_id'      => $batch_id_str,
            'year'          => $year,
            'week_number'   => (int) $week,
            'period_start'  => $period->start_date,
            'period_end'    => $period->end_date,
            'total_amount'  => $expenses->total,
            'currency'      => 'KMF',
            'expense_count' => $expenses->count,
            'status'        => 'draft',
            'created_by'    => $user_id,
        ]);

        $new_id = $this->db->insert_id();

        // Affecter les dépenses au batch
        $this->db
            ->where_in('id', $expense_ids)
            ->where('status', 'validated')
            ->where('batch_id IS NULL', null, FALSE)
            ->update('expenses', [
                'batch_id' => $new_id,
                'status'   => 'batched',
            ]);

        $this->db->trans_complete();

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

    /**
     * Finaliser un batch
     */
    public function finalize($id, $zip_path = null)
    {
        return $this->db->where('id', $id)->update($this->table, [
            'status'       => 'finalized',
            'zip_path'     => $zip_path,
            'finalized_at' => date('Y-m-d H:i:s'),
        ]);
    }

    /**
     * Marquer comme envoyé
     */
    public function mark_sent($id)
    {
        return $this->db->where('id', $id)->update($this->table, [
            'status'  => 'sent',
            'sent_at' => date('Y-m-d H:i:s'),
        ]);
    }

    /**
     * Marquer comme accusé
     */
    public function mark_acknowledged($id)
    {
        return $this->db->where('id', $id)->update($this->table, [
            'status' => 'acknowledged',
        ]);
    }

    /**
     * Générer le batch_id pour affichage
     */
    public function next_batch_id()
    {
        return 'BATCH-' . date('Y') . '-' . str_pad(date('W'), 2, '0', STR_PAD_LEFT);
    }
}
