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

class Umkm_model extends CI_Model {

    protected $table = 'umkm';

    // ─── AMBIL SEMUA ──────────────────────────────────────────
    // JOIN ke tabel warga/users untuk nama & kontak pemilik
    public function get_all($kategori = null, $status = null, $q = null) {
        $this->db
            ->select('u.*, 
                      COALESCE(w.nama_lengkap, usr.username) as nama_pemilik,
                      w.nik,
                      usr.id as user_id_owner')
            ->from($this->table.' u')
            ->join('warga w',  'w.id = u.warga_id', 'left')
            ->join('users usr', 'usr.id = u.user_id', 'left');

        if ($kategori) $this->db->where('u.kategori', $kategori);
        if ($status)   $this->db->where('u.status', $status);
        if ($q) {
            $this->db->group_start()
                ->like('u.nama_usaha', $q)
                ->or_like('u.deskripsi', $q)
                ->or_like('u.produk_layanan', $q)
                ->or_like('w.nama_lengkap', $q)
                ->group_end();
        }

        return $this->db
            ->order_by('u.status', 'ASC')       // aktif dulu
            ->order_by('u.nama_usaha', 'ASC')
            ->get()
            ->result();
    }

    // ─── AMBIL SATU ───────────────────────────────────────────
    public function get_by_id($id) {
        return $this->db
            ->select('u.*, 
                     COALESCE(w.nama_lengkap, usr.username) as nama_pemilik,
                      w.nik,
                      usr.id as user_id_owner')
            ->from($this->table.' u')
            ->join('warga w',   'w.id = u.warga_id', 'left')
            ->join('users usr', 'usr.id = u.user_id', 'left')
            ->where('u.id', (int)$id)
            ->get()
            ->row();
    }

    // ─── AMBIL MILIK USER TERTENTU ────────────────────────────
    public function get_by_user($user_id) {
        return $this->db
            ->where('user_id', (int)$user_id)
            ->order_by('created_at', 'DESC')
            ->get($this->table)
            ->result();
    }

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

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

    // ─── HAPUS ────────────────────────────────────────────────
    public function delete($id) {
        $this->db->where('id', (int)$id)->delete($this->table);
        return $this->db->affected_rows();
    }

    // ─── COUNT by status ──────────────────────────────────────
    public function count_by_status($status) {
        return $this->db
            ->where('status', $status)
            ->count_all_results($this->table);
    }

    // ─── COUNT kategori unik yang ada UMKM-nya ────────────────
    public function count_kategori() {
        $result = $this->db
            ->select('COUNT(DISTINCT kategori) as total')
            ->where('status', 'aktif')
            ->get($this->table)
            ->row();
        return $result->total ?? 0;
    }

    // ─── GET pending (untuk notif admin) ──────────────────────
    public function get_pending($limit = 5) {
        return $this->db
            ->select('u.*, COALESCE(w.nama_lengkap, usr.username) as nama_pemilik')
            ->from($this->table.' u')
            ->join('warga w',   'w.id = u.warga_id', 'left')
            ->join('users usr', 'usr.id = u.user_id', 'left')
            ->where('u.status', 'pending')
            ->order_by('u.created_at', 'ASC')
            ->limit($limit)
            ->get()
            ->result();
    }
}