<?php

namespace App\Models;

use CodeIgniter\Model;

class ItemModel extends Model
{
    protected $table            = 'items';
    protected $primaryKey       = 'id';
    protected $allowedFields    = [
        'barcode', 'nama', 'id_category', 'id_unit', 'harga', 'stok', 'id_gudang', 'diskon', 'total_harga', 'is_active'
    ];

    // Ambil semua item AKTIF lengkap dengan relasi
    public function getActiveItemsWithRelations()
    {
        return $this->db->table('items i')
            ->select('i.*, c.nama as kategori, u.nama as unit, g.nama as gudang')
            ->join('categories c', 'c.id = i.id_category', 'left')
            ->join('units u', 'u.id = i.id_unit', 'left')
            ->join('gudang g', 'g.id = i.id_gudang', 'left')
            ->where('i.is_active', 1)
            ->orderBy('i.id', 'DESC')
            ->get()
            ->getResultArray();
    }

    // Ambil SEMUA item (aktif & nonaktif) untuk admin
    public function getItemsWithRelations()
    {
        return $this->db->table('items i')
            ->select('i.*, c.nama as kategori, u.nama as unit, g.nama as gudang')
            ->join('categories c', 'c.id = i.id_category', 'left')
            ->join('units u', 'u.id = i.id_unit', 'left')
            ->join('gudang g', 'g.id = i.id_gudang', 'left')
            ->orderBy('i.id', 'DESC')
            ->get()
            ->getResultArray();
    }

    // Ambil item berdasarkan gudang (hanya yang aktif)
    public function getByGudang($gudangId)
    {
        return $this->where([
                'id_gudang' => $gudangId,
                'is_active' => 1
            ])->findAll();
    }

    // Hitung total stok per gudang dari item aktif
    public function getTotalStokPerGudang()
    {
        return $this->select('id_gudang, SUM(stok) as total_stok')
            ->where('is_active', 1)
            ->groupBy('id_gudang')
            ->findAll();
    }

    // Ambil detail item (semua relasi)
    public function getDetailWithRelations($id)
    {
        return $this->db->table('items i')
            ->select('i.*, c.nama as kategori, u.nama as unit, g.nama as gudang')
            ->join('categories c', 'c.id = i.id_category', 'left')
            ->join('units u', 'u.id = i.id_unit', 'left')
            ->join('gudang g', 'g.id = i.id_gudang', 'left')
            ->where('i.id', $id)
            ->get()
            ->getRowArray();
    }

    // Soft delete item (ubah status menjadi tidak aktif)
    public function softDelete($id)
    {
        return $this->update($id, ['is_active' => 0]);
    }

    // Aktifkan kembali item
    public function restore($id)
    {
        return $this->update($id, ['is_active' => 1]);
    }


public function searchItems($keyword = null)
{
    $builder = $this->db->table('items i')
        ->select('i.*, c.nama as kategori, u.nama as unit, g.nama as gudang')
        ->join('categories c', 'c.id = i.id_category', 'left')
        ->join('units u', 'u.id = i.id_unit', 'left')
        ->join('gudang g', 'g.id = i.id_gudang', 'left')
        ->orderBy('i.id', 'DESC');

    if ($keyword) {
        $builder->groupStart()
            ->like('i.barcode', $keyword)
            ->orLike('i.nama', $keyword)
            ->groupEnd();
    }

    return $builder->get()->getResultArray();
}

public function getPaginatedItems($limit, $offset, $keyword = null)
{
    $builder = $this->db->table('items i')
        ->select('i.*, c.nama as kategori, u.nama as unit, g.nama as gudang')
        ->join('categories c', 'c.id = i.id_category', 'left')
        ->join('units u', 'u.id = i.id_unit', 'left')
        ->join('gudang g', 'g.id = i.id_gudang', 'left')
        ->orderBy('i.id', 'DESC');

    if ($keyword) {
        $builder->groupStart()
            ->like('i.barcode', $keyword)
            ->orLike('i.nama', $keyword)
            ->groupEnd();
    }

    return $builder->limit($limit, $offset)->get()->getResultArray();
}

public function countItems($keyword = null)
{
    $builder = $this->db->table('items i');

    if ($keyword) {
        $builder->groupStart()
            ->like('i.barcode', $keyword)
            ->orLike('i.nama', $keyword)
            ->groupEnd();
    }

    return $builder->countAllResults();
}


}
