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

class Request_model extends CI_Model
{
    protected $table = 'expense_requests';

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

    public function get($id)
    {
        return $this->db
            ->select('expense_requests.*, 
                      expense_categories.label AS category_label,
                      locations.name AS location_name,
                      requester.full_name AS requested_by_name,
                      approver.full_name AS approved_by_name')
            ->join('expense_categories', 'expense_categories.id = expense_requests.category_id')
            ->join('locations', 'locations.id = expense_requests.location_id')
            ->join('users requester', 'requester.id = expense_requests.requested_by')
            ->join('users approver', 'approver.id = expense_requests.approved_by', 'left')
            ->where('expense_requests.id', $id)
            ->get($this->table)
            ->row();
    }

    public function get_list($filters = [], $limit = 20, $offset = 0)
    {
        $this->db
            ->select('expense_requests.*, 
                      expense_categories.label AS category_label,
                      locations.name AS location_name,
                      requester.full_name AS requested_by_name,
                      expenses.id AS expense_pk, expenses.expense_id AS expense_ref, 
                      expenses.status AS expense_status, expenses.amount AS expense_amount,
                      expenses.invoice_path AS expense_invoice_path')
            ->join('expense_categories', 'expense_categories.id = expense_requests.category_id')
            ->join('locations', 'locations.id = expense_requests.location_id')
            ->join('users requester', 'requester.id = expense_requests.requested_by')
            ->join('expenses', 'expenses.request_id = expense_requests.id', 'left');

        if ( ! empty($filters['status'])) {
            $this->db->where('expense_requests.status', $filters['status']);
        }
        if ( ! empty($filters['location_id'])) {
            $this->db->where('expense_requests.location_id', $filters['location_id']);
        }
        if ( ! empty($filters['user_location_id'])) {
            $this->db->where('expense_requests.location_id', $filters['user_location_id']);
        }
        // Filtre : demandes approuvées sans dépense créée
        if ( ! empty($filters['missing_expense'])) {
            $this->db->where('expense_requests.status', 'approved');
            $this->db->where('expenses.id IS NULL', null, FALSE);
        }
        // Filtre : dépenses sans justificatif
        if ( ! empty($filters['missing_invoice'])) {
            $this->db->where('expenses.id IS NOT NULL', null, FALSE);
            $this->db->group_start()
                ->where('expenses.invoice_path IS NULL', null, FALSE)
                ->or_where('expenses.invoice_path', '')
            ->group_end();
        }

        return $this->db
            ->order_by('expense_requests.created_at', '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['location_id'])) {
            $this->db->where('location_id', $filters['location_id']);
        }
        return $this->db->count_all_results($this->table);
    }

    public function create($data)
    {
        $this->db->insert($this->table, $data);
        return $this->db->insert_id();
    }

    public function update($id, $data)
    {
        return $this->db->where('id', $id)->update($this->table, $data);
    }

    public function next_reference()
    {
        $year = date('Y');
        $prefix = 'REQ-' . $year . '-';

        $last = $this->db
            ->select('reference')
            ->like('reference', $prefix, 'after')
            ->order_by('id', 'DESC')
            ->get($this->table, 1)
            ->row();

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

        return $prefix . str_pad($next, 3, '0', STR_PAD_LEFT);
    }

    public function count_pending($location_id = null)
    {
        $this->db->where('status', 'pending');
        if ($location_id) {
            $this->db->where('location_id', $location_id);
        }
        return $this->db->count_all_results($this->table);
    }

    public function get_approved_unlinked($location_id = null)
    {
        $this->db
            ->select('expense_requests.*, expense_categories.label AS category_label, locations.name AS location_name')
            ->join('expense_categories', 'expense_categories.id = expense_requests.category_id')
            ->join('locations', 'locations.id = expense_requests.location_id')
            ->where('expense_requests.status', 'approved');

        $this->db->where('expense_requests.id NOT IN (SELECT COALESCE(request_id, 0) FROM expenses)', null, FALSE);

        if ($location_id) {
            $this->db->where('expense_requests.location_id', $location_id);
        }

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