<?php

class Peminjaman_model extends CI_Model {

    public function get($q=null,$status=null,$filter=null,$tanggal=null,$ganti=null)

{
   $this->db->select('
p.id_peminjaman,
p.tanggal_pinjam,
p.tanggal_kembali,
dp.id_detail,
dp.jumlah,
dp.status,
dp.status_ganti,
dp.tanggal_dikembalikan,
b.judul,
a.nama
');


    $this->db->from('detail_peminjaman dp');
    $this->db->join('peminjaman p','p.id_peminjaman=dp.id_peminjaman');
    $this->db->join('buku b','b.id_buku=dp.id_buku');
    $this->db->join('anggota a','a.id_anggota=p.id_anggota');


    // 🔎 SEARCH
    if(!empty($q)){
        $this->db->group_start();
        $this->db->like('a.nama',$q);
        $this->db->or_like('b.judul',$q);
        $this->db->group_end();
    }


    // 📌 STATUS
    if(!empty($status)){
        $this->db->where('dp.status',$status);
    }


    // 📅 FILTER WAKTU
    if(!empty($filter)){

        if($filter=='harian'){
            $this->db->where('DATE(p.tanggal_pinjam)',date('Y-m-d'));
        }

        elseif($filter=='mingguan'){
            $this->db->where('YEARWEEK(p.tanggal_pinjam,1)=YEARWEEK(CURDATE(),1)');
        }

        elseif($filter=='bulanan'){
            $this->db->where('MONTH(p.tanggal_pinjam)',date('m'));
            $this->db->where('YEAR(p.tanggal_pinjam)',date('Y'));
        }

        elseif($filter=='tahunan'){
            $this->db->where('YEAR(p.tanggal_pinjam)',date('Y'));
        }

    }

    // 📆 FILTER TANGGAL MANUAL
    if(!empty($tanggal)){
        $this->db->where('DATE(p.tanggal_pinjam)',$tanggal);
    }
if(!empty($ganti)){
    if($ganti=='tidak_perlu'){
        $this->db->where_not_in('dp.status',['rusak','hilang']);
    }else{
        $this->db->where('dp.status_ganti',$ganti);
    }
}


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



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

    public function buku()
    {
        return $this->db->where('stok >', 0)
                        ->get('buku')
                        ->result();
    }

    public function insert($data, $buku)
    {
        $this->db->insert('peminjaman', $data);
        $id = $this->db->insert_id();

        $this->db->insert('detail_peminjaman', [
            'id_peminjaman' => $id,
            'id_buku'       => $buku,
            'jumlah'        => 1
        ]);

        $this->db->set('stok', 'stok-1', false)
                 ->where('id_buku', $buku)
                 ->update('buku');
    }

    public function kembali($id)
    {
        $this->db->where('id_peminjaman', $id);
        $p = $this->db->get('peminjaman')->row();

        $today = date('Y-m-d');
        $telat = max(0, (strtotime($today) - strtotime($p->tanggal_kembali)) / 86400);

        $this->db->where('id_peminjaman', $id)
                 ->update('peminjaman', ['status' => 'dikembalikan']);

        $detail = $this->db->where('id_peminjaman', $id)
                           ->get('detail_peminjaman')
                           ->row();

        $this->db->set('stok', 'stok+1', false)
                 ->where('id_buku', $detail->id_buku)
                 ->update('buku');
    }

    public function jumlah_pinjam_aktif($id_anggota)
    {
        $this->db->select_sum('dp.jumlah');
        $this->db->from('detail_peminjaman dp');
        $this->db->join('peminjaman p', 'p.id_peminjaman = dp.id_peminjaman');
        $this->db->where('p.id_anggota', $id_anggota);
        $this->db->where('dp.status', 'dipinjam');

        $row = $this->db->get()->row();
        return $row->jumlah ?? 0;
    }

    public function hapus($id)
    {
        $detail = $this->db->where('id_peminjaman', $id)
                           ->get('detail_peminjaman')
                           ->row();

        if ($detail) {
            $this->db->set('stok', 'stok+' . $detail->jumlah, false)
                     ->where('id_buku', $detail->id_buku)
                     ->update('buku');
        }

        $this->db->where('id_peminjaman', $id)
                 ->delete('detail_peminjaman');

        $this->db->where('id_peminjaman', $id)
                 ->delete('peminjaman');
    }

    public function notifikasi_h3()
    {
        $sql = "
            SELECT peminjaman.*, anggota.nama, anggota.no_hp, buku.judul,
            DATEDIFF(peminjaman.tanggal_kembali, DATE(NOW())) AS sisa_hari
            FROM peminjaman
            JOIN anggota ON anggota.id_anggota = peminjaman.id_anggota
            JOIN detail_peminjaman ON detail_peminjaman.id_peminjaman = peminjaman.id_peminjaman
            JOIN buku ON buku.id_buku = detail_peminjaman.id_buku
            WHERE peminjaman.status = 'dipinjam'
            AND DATEDIFF(peminjaman.tanggal_kembali, DATE(NOW())) <= 3
            ORDER BY peminjaman.tanggal_kembali ASC
        ";

        return $this->db->query($sql)->result();
    }

    public function count_notifikasi()
    {
        $sql = "
            SELECT COUNT(*) as total
            FROM peminjaman
            WHERE status = 'dipinjam'
            AND DATEDIFF(tanggal_kembali, CURDATE()) <= 3
        ";

        return $this->db->query($sql)->row()->total;
    }

    public function kembali_buku($id_detail)
    {
        $detail = $this->db->get_where('detail_peminjaman', [
            'id_detail' => $id_detail
        ])->row();

        if (!$detail) return;

        $this->db->set('stok', 'stok+' . $detail->jumlah, false)
                 ->where('id_buku', $detail->id_buku)
                 ->update('buku');

        $this->db->where('id_detail', $id_detail)
                 ->update('detail_peminjaman', [
                     'status'               => 'dikembalikan',
                     'tanggal_dikembalikan' => date('Y-m-d')
                 ]);

        $sisa = $this->db
            ->where('id_peminjaman', $detail->id_peminjaman)
            ->where('status', 'dipinjam')
            ->count_all_results('detail_peminjaman');

        if ($sisa == 0) {
            $this->db->where('id_peminjaman', $detail->id_peminjaman)
                     ->update('peminjaman', [
                         'status' => 'dikembalikan'
                     ]);
        }
    }
    public function buku_perlu_ganti(){

$this->db->select('
dp.id_detail,
a.nama,
b.judul,
dp.jumlah,
p.tanggal_pinjam,
dp.status,
dp.status_ganti
');

$this->db->from('detail_peminjaman dp');
$this->db->join('peminjaman p','p.id_peminjaman=dp.id_peminjaman');
$this->db->join('anggota a','a.id_anggota=p.id_anggota');
$this->db->join('buku b','b.id_buku=dp.id_buku');

$this->db->where_in('dp.status',['rusak','hilang']);
$this->db->where('(dp.status_ganti IS NULL OR dp.status_ganti="belum")');

return $this->db->get()->result();
}
public function jumlah_belum_kembali(){
return $this->db
->where('status','dipinjam')
->count_all_results('detail_peminjaman');
}

}
