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

class Atk_Pengambilan_model extends CI_Model
{
    private $table = "atk_pengambilan";

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->load->model('Atk_Pengambilan_model');
        $this->load->model('ATK_Product_model');
    }

    public function getById($id) {
    return $this->db
        ->select('atk_pengambilan.*, atk_product.atk_product_nama, divisi_categories.divisi_categories_nama')
        ->from('atk_pengambilan')
        ->join('atk_product', 'atk_pengambilan.atk_pengambilan_product = atk_product.atk_product_id', 'left')
        ->join('divisi_categories', 'atk_pengambilan.atk_pengambilan_divisi = divisi_categories.divisi_categories_id', 'left')
        ->where('atk_pengambilan.id', $id) 
        ->get()
        ->row();
}


    public function getAll() {
    $this->db->select('p.*, pr.atk_product_id, pr.atk_product_nama, d.divisi_categories_nama');
    $this->db->from('atk_pengambilan p');
    $this->db->join('atk_product pr', 'p.atk_pengambilan_product = pr.atk_product_id', 'left');
    $this->db->join('divisi_categories d', 'p.atk_pengambilan_divisi = d.divisi_categories_id', 'left');
    $this->db->where('p.atk_pengambilan_status', 1);
    $this->db->order_by('p.tanggal_pengambilan', 'DESC');
    return $this->db->get()->result();
}


    public function jumlah_inventory(){
        $query =  $this->db->query("SELECT COUNT(inventory_id) AS JumlahInventory FROM inventory WHERE inventory_status = '1'");
        return $query->result();      
    }

    public function save()
{
    $peminjam = $this->session->userdata('username');
    if (empty($peminjam)) {
        show_error("Session 'username' tidak ditemukan. Pastikan user login dulu.");
        return;
    }

    $products = $this->input->post('atk_pengambilan_product');  // array
    $jumlahs  = $this->input->post('atk_pengambilan_jumlah');   // array
    $divisi   = $this->input->post('atk_pengambilan_divisi');

    if (empty($products) || empty($jumlahs)) {
        $this->session->set_flashdata('error', 'Tidak ada produk yang dipilih.');
        redirect('admin/ATK/Atk_Pengambilan_Controller/add');
        return;
    }

    foreach ($products as $i => $product_id) {
        $jumlah = (int) $jumlahs[$i];

        // cek stok
        $stok = $this->ATK_Product_model->getStock($product_id);
        if ($jumlah > $stok) {
            $this->session->set_flashdata('error', "Stok produk ID $product_id tidak mencukupi");
            redirect('admin/ATK/Atk_Pengambilan_Controller/add');
            return;
        }

        // simpan data per item
        $data = [
            'atk_pengambilan_peminjam' => $peminjam,
            'atk_pengambilan_divisi'   => $divisi,
            'atk_pengambilan_product'  => $product_id,
            'atk_pengambilan_jumlah'   => $jumlah,
            'atk_pengambilan_status'   => 1,
            'created_at'               => date('Y-m-d H:i:s') // pakai sesuai field tabel
        ];
        $this->Atk_Pengambilan_model->save($data);

        // kurangi stok
        $this->ATK_Product_model->reduceStock($product_id, $jumlah);
    }

    $this->session->set_flashdata('success', 'Pengambilan berhasil disimpan');
    redirect('admin/ATK/Atk_Pengambilan_Controller');
}



    public function view_update($where, $table){
        return $this->db->get_where($table, $where);
    }

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

    public function delete($where, $table){
        $this->db->where($where);
        $this->db->delete($table);
    }

    public function get_pengambilan_with_product()
    {
        return $this->db->select('atk_pengambilan.*, atk_product.atk_product_id, atk_product.atk_product_nama, divisi_categories.divisi_categories_nama')
            ->from('atk_pengambilan')
            ->join('atk_product', 'atk_pengambilan.atk_pengambilan_product = atk_product.atk_product_id', 'left')
            ->join('divisi_categories', 'atk_pengambilan.atk_pengambilan_divisi = divisi_categories.divisi_categories_id', 'left')
            ->where('atk_pengambilan.atk_pengambilan_status', 1)
            ->get()
            ->result();
    }

    public function insert_pengambilan($data) {
    return $this->db->insert('atk_pengambilan', $data);
}

public function getStock($product_id) {
    $this->db->select('atk_product_stock');
    $this->db->from('atk_product');
    $this->db->where('atk_product_id', $product_id);
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return (int) $query->row()->atk_product_stock;
    } else {
        return 0;
    }
}

public function getDivisi(){
    $query = $this->db->get('divisi_categories');
    return $query->result();
}

public function getKategori(){
    $query = $this->db->get('atk_categories');
    return $query->result();
}

public function get_products()
{
    return $this->db->get('atk_product')->result();
}

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

    
}

public function get_all_pengambilan()
{
    $this->db->select('p.*, d.divisi_categories_nama, a.atk_product_nama');
    $this->db->from('atk_pengambilan p');
    $this->db->join('divisi_categories d', 'p.atk_pengambilan_divisi = d.divisi_categories_id', 'left');
    $this->db->join('atk_product a', 'p.atk_pengambilan_product = a.atk_product_id', 'left');
    $this->db->order_by('p.tanggal_pengambilan', 'DESC');
    return $this->db->get()->result();
}

public function get_filtered_pengambilan($produk_id = null, $divisi = null, $lokasi = null)
{
    $this->db->select('
        p.*,
        d.divisi_categories_nama,
        pr.atk_product_nama,
        l.inventory_location_nama
    ');
    $this->db->from('atk_pengambilan p');
    $this->db->join('divisi_categories d', 'p.atk_pengambilan_divisi = d.divisi_categories_id', 'left');
    $this->db->join('atk_product pr', 'p.atk_pengambilan_product = pr.atk_product_id', 'left');
    $this->db->join('inventory_location l', 'pr.atk_product_location = l.inventory_location_id', 'left');

    if (!empty($produk_id)) {
        $this->db->where('p.atk_pengambilan_product', $produk_id);
    }
    if (!empty($divisi)) {
        $this->db->where('p.atk_pengambilan_divisi', $divisi);
    }
    if (!empty($lokasi)) {
        $this->db->where('pr.atk_product_location', $lokasi);
    }

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




public function get_all_produk()
{
    return $this->db->get('atk_product')->result();
}

public function get_all_divisi()
{
    $this->db->distinct();
    $this->db->select('d.divisi_categories_id as divisi_id, d.divisi_categories_nama');
    $this->db->from('atk_pengambilan p');
    $this->db->join('divisi_categories d', 'p.atk_pengambilan_divisi = d.divisi_categories_id', 'left');
    return $this->db->get()->result();
}


public function get_pengambilan_perbulan() {
    $this->db->select("DATE_FORMAT(tanggal_pengambilan, '%Y-%m') as bulan, COUNT(*) as total");
    $this->db->from("atk_pengambilan");
    $this->db->group_by("DATE_FORMAT(tanggal_pengambilan, '%Y-%m')");
    $this->db->order_by("bulan", "ASC");
    return $this->db->get()->result();
}

// ✅ Rekap per karyawan, urut terbaru
    public function rekapKaryawan($bulan, $tahun) {
        $this->db->select('
            atk_pengambilan.atk_pengambilan_peminjam, 
            divisi_categories.divisi_categories_nama AS atk_pengambilan_divisi,
            COUNT(DISTINCT atk_pengambilan.id) AS total_transaksi, 
            SUM(atk_pengambilan.atk_pengambilan_jumlah) AS total_item,
            MAX(atk_pengambilan.tanggal_pengambilan) AS last_pengambilan
        ');
        $this->db->from('atk_pengambilan');
        $this->db->join('divisi_categories', 'atk_pengambilan.atk_pengambilan_divisi = divisi_categories.divisi_categories_id', 'left');
        if ($bulan != 'all') {
            $this->db->where('MONTH(atk_pengambilan.tanggal_pengambilan)', $bulan);
        }
        $this->db->where('YEAR(atk_pengambilan.tanggal_pengambilan)', $tahun);
        $this->db->group_by(['atk_pengambilan_peminjam', 'divisi_categories.divisi_categories_nama']);
        $this->db->order_by('last_pengambilan', 'DESC'); // urut terbaru
        return $this->db->get()->result();
    }

    // ✅ Rekap per divisi, urut terbaru
    public function rekapDivisi($bulan, $tahun) {
        $this->db->select('
            divisi_categories.divisi_categories_nama AS atk_pengambilan_divisi,
            COUNT(DISTINCT atk_pengambilan.atk_pengambilan_peminjam) AS total_karyawan,
            COUNT(DISTINCT atk_pengambilan.id) AS total_transaksi,
            SUM(atk_pengambilan.atk_pengambilan_jumlah) AS total_item,
            MAX(atk_pengambilan.tanggal_pengambilan) AS last_pengambilan
        ');
        $this->db->from('atk_pengambilan');
        $this->db->join('divisi_categories', 'atk_pengambilan.atk_pengambilan_divisi = divisi_categories.divisi_categories_id', 'left');
        if ($bulan != 'all') {
            $this->db->where('MONTH(atk_pengambilan.tanggal_pengambilan)', $bulan);
        }
        $this->db->where('YEAR(atk_pengambilan.tanggal_pengambilan)', $tahun);
        $this->db->group_by('divisi_categories.divisi_categories_nama');
        $this->db->order_by('last_pengambilan', 'DESC'); // urut terbaru
        return $this->db->get()->result();
    }



    // Data chart per bulan
    public function chartPerBulan($tahun = null) {
        if (!$tahun) $tahun = date('Y');

        $this->db->select('MONTH(pickup_date) as bulan, COUNT(*) as jumlah');
        $this->db->from('atk_pengambilan');
        $this->db->where('YEAR(pickup_date)', $tahun);
        $this->db->group_by('MONTH(pickup_date)');
        $this->db->order_by('bulan', 'ASC');
        return $this->db->get()->result();
    }

    // Data chart per minggu
    public function chartPerMinggu($tahun = null, $bulan = null) {
        if (!$tahun) $tahun = date('Y');
        if (!$bulan) $bulan = date('m');

        $this->db->select('WEEK(pickup_date,1) as minggu, COUNT(*) as jumlah');
        $this->db->from('atk_pengambilan');
        $this->db->where('YEAR(pickup_date)', $tahun);
        $this->db->where('MONTH(pickup_date)', $bulan);
        $this->db->group_by('WEEK(pickup_date,1)');
        $this->db->order_by('minggu','ASC');
        return $this->db->get()->result();
    }


        public function getRekapPerBulan($bulan = 'all', $tahun = null) {
        $this->db->select("
            MONTH(tanggal_pengambilan) as bulan, 
            COUNT(*) as total_pengambilan,
            GROUP_CONCAT(atk_product.atk_product_nama SEPARATOR ', ') as atk_list
        ");
        $this->db->from('atk_pengambilan');
        $this->db->join('atk_product', 'atk_product.atk_product_id = atk_pengambilan.atk_pengambilan_product', 'left');

        if($bulan != 'all'){
            $this->db->where('MONTH(tanggal_pengambilan)', $bulan);
        }
        if($tahun){
            $this->db->where('YEAR(tanggal_pengambilan)', $tahun);
        }

        $this->db->group_by('MONTH(tanggal_pengambilan)');
        $query = $this->db->get();
        return $query->result();
    }
    public function getFiltered($filter_kategori = null, $filter_lokasi = null)
{
    $this->db->select('p.*, 
        b.brand_categories_nama, 
        c.atk_categories_nama, 
        u.uom_categories_nama, 
        l.inventory_location_nama');
    $this->db->from('atk_product p');
    $this->db->join('brand_categories b', 'p.brand_categories_id = b.brand_categories_id', 'left');
    $this->db->join('atk_categories c', 'p.atk_categories_id = c.atk_categories_id', 'left');
    $this->db->join('uom_categories u', 'p.uom_categories_id = u.uom_categories_id', 'left');
    $this->db->join('inventory_location l', 'p.inventory_location_id = l.inventory_location_id', 'left');

    // Filter kategori kalau ada
    if (!empty($filter_kategori)) {
        $this->db->where('p.atk_categories_id', $filter_kategori);
    }

    // Filter lokasi kalau ada
    if (!empty($filter_lokasi)) {
        $this->db->where('p.inventory_location_id', $filter_lokasi);
    }

    $this->db->order_by('p.update_date', 'DESC');
    return $this->db->get()->result();
}


}
