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

class Daily_expense_model extends CI_Model {

    public function create($options)
    {
        $this->db->insert('kns1_expenses', $options);
        return $this->db->insert_id();
    }

    public function get_all($limit = NULL, $offset = NULL)
    {
        $this->db->order_by('id', 'DESC');
        $query = $this->db->get('kns1_expenses', $limit, $offset);
        return $query->result();
    }

    public function get_monthly_expense_by_year($year)
    {
        $this->db->select('MONTH(date) AS month, SUM(amount) AS total');
        $this->db->from('kns1_expenses');
        $this->db->where('YEAR(date)', $year);
        $this->db->group_by('MONTH(date)');
        $query = $this->db->get();

        $monthly_totals = array_fill(1, 12, 0);
        foreach ($query->result() as $row) {
            $monthly_totals[(int)$row->month] = (float)$row->total;
        }
        return $monthly_totals;
    }

    public function get_all_by_month_year($month, $year)
    {
        $this->db->select('*');
        $this->db->from('kns1_expenses');
        $this->db->where('MONTH(date)', $month);
        $this->db->where('YEAR(date)', $year);
        $this->db->order_by('date', 'DESC');
        return $this->db->get()->result();
    }

    public function get_by($id)
    {
        $this->db->where('id', $id);
        $query = $this->db->get('kns1_expenses');
        return $query->row();
    }

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


    public function get_expense_type_summary($month, $year)
{
    $this->db->select('et.expense_type, SUM(e.amount) as total');
    $this->db->from('kns1_expenses e');
    $this->db->join('kns1_expense_types et', 'et.id = e.expense_type_id', 'left');
    $this->db->where('MONTH(e.date)', $month);
    $this->db->where('YEAR(e.date)', $year);
    $this->db->group_by('e.expense_type_id');
    $query = $this->db->get();
    return $query->result();
}




    public function destroy($id)
    {
        $this->db->where('id', $id);
        $this->db->delete('kns1_expenses');
        return $this->db->affected_rows();
    }

    public function get_expense_type_name($id)
    {
        $this->db->select('expense_type');
        $this->db->where('id', $id);
        $query = $this->db->get('kns1_expense_types');
        return $query->num_rows() > 0 ? $query->row()->expense_type : false;
    }

    public function get_sub_expense_type_name($id)
    {
        $this->db->select('sub_expense_type');
        $this->db->where('id', $id);
        $query = $this->db->get('kns1_sub_expense_types');
        return $query->num_rows() > 0 ? $query->row()->sub_expense_type : false;
    }
}
