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

class Jadwal_model extends CI_Model {

    private $table = 'jadwal_kegiatan';
    private $table_petugas = 'jadwal_petugas';

    public function get_all($filters = []) {
        $this->db->select('jk.*, w.nama_lengkap as penanggung_jawab, u.nama_lengkap as created_by_name');
        $this->db->from($this->table . ' jk');
        $this->db->join('warga w', 'w.id = jk.penanggung_jawab_id', 'left');
        $this->db->join('users u', 'u.id = jk.created_by', 'left');
        
        if (!empty($filters['kategori'])) {
            $this->db->where('jk.kategori', $filters['kategori']);
        }
        if (!empty($filters['status'])) {
            $this->db->where('jk.status', $filters['status']);
        }
        if (!empty($filters['jenis_jadwal'])) {
            $this->db->where('jk.jenis_jadwal', $filters['jenis_jadwal']);
        }
        
        $this->db->order_by('jk.created_at', 'DESC');
        return $this->db->get()->result();
    }

    public function get_by_id($id) {
        $this->db->select('jk.*, w.nama_lengkap as penanggung_jawab, w.nik, u.nama_lengkap as created_by_name');
        $this->db->from($this->table . ' jk');
        $this->db->join('warga w', 'w.id = jk.penanggung_jawab_id', 'left');
        $this->db->join('users u', 'u.id = jk.created_by', 'left');
        $this->db->where('jk.id', $id);
        return $this->db->get()->row();
    }

    public function get_petugas_by_jadwal($jadwal_id) {
        // FIX: Ambil no_hp dari tabel users, bukan dari warga
        $this->db->select('jp.*, w.nama_lengkap, w.nik, u.no_hp');
        $this->db->from($this->table_petugas . ' jp');
        $this->db->join('warga w', 'w.id = jp.warga_id', 'left');
        $this->db->join('users u', 'u.warga_id = w.id', 'left'); // Join ke users melalui warga_id
        $this->db->where('jp.jadwal_id', $jadwal_id);
        $this->db->order_by('jp.urutan', 'ASC');
        $this->db->order_by('jp.kelompok', 'ASC');
        return $this->db->get()->result();
    }

    public function get_jadwal_hari_ini() {
        $hari_ini = date('l');
        $hari_map = [
            'Monday' => 'senin', 'Tuesday' => 'selasa', 'Wednesday' => 'rabu',
            'Thursday' => 'kamis', 'Friday' => 'jumat', 'Saturday' => 'sabtu', 'Sunday' => 'minggu'
        ];
        $hari = $hari_map[$hari_ini];
        $tanggal = date('Y-m-d');

        $this->db->select('jk.*, w.nama_lengkap as penanggung_jawab');
        $this->db->from($this->table . ' jk');
        $this->db->join('warga w', 'w.id = jk.penanggung_jawab_id', 'left');
        $this->db->where('jk.status', 'aktif');
        $this->db->group_start();
            // Jadwal harian
            $this->db->or_where('jk.jenis_jadwal', 'harian');
            // Jadwal mingguan dengan hari cocok
            $this->db->or_group_start()
                ->where('jk.jenis_jadwal', 'mingguan')
                ->where('jk.hari', $hari)
            ->group_end();
            // Jadwal dengan tanggal spesifik hari ini
            $this->db->or_group_start()
                ->where('jk.jenis_jadwal', 'tanggal_tentu')
                ->where('jk.tanggal_mulai', $tanggal)
            ->group_end();
            // Jadwal dalam range tanggal
            $this->db->or_group_start()
                ->where('jk.jenis_jadwal', 'range_tanggal')
                ->where('jk.tanggal_mulai <=', $tanggal)
                ->where('jk.tanggal_selesai >=', $tanggal)
            ->group_end();
        $this->db->group_end();

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

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

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

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

    public function insert_petugas($data) {
        return $this->db->insert($this->table_petugas, $data);
    }

    public function delete_petugas($jadwal_id) {
        $this->db->where('jadwal_id', $jadwal_id);
        return $this->db->delete($this->table_petugas);
    }

    public function get_kelompok_by_jadwal($jadwal_id) {
        $this->db->select('DISTINCT(kelompok) as nama_kelompok', false);
        $this->db->from($this->table_petugas);
        $this->db->where('jadwal_id', $jadwal_id);
        $this->db->where('kelompok IS NOT NULL');
        return $this->db->get()->result();
    }

    public function get_statistik() {
        $stats = [];
        $stats['total'] = $this->db->where('status !=', 'selesai')->count_all_results($this->table);
        $stats['harian'] = $this->db->where('jenis_jadwal', 'harian')->where('status', 'aktif')->count_all_results($this->table);
        $stats['mingguan'] = $this->db->where('jenis_jadwal', 'mingguan')->where('status', 'aktif')->count_all_results($this->table);
        $stats['hari_ini'] = count($this->get_jadwal_hari_ini());
        return $stats;
    }
}