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

class Kegiatan_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    // ─── GET ALL (admin) ─────────────────────────────────────
    public function get_all($kategori = null, $bulan = null, $tahun = null, $cari = null) {
        $this->db->select('kegiatan.*, users.nama_lengkap as pembuat,
            (SELECT COUNT(*) FROM kegiatan_galeri kg WHERE kg.kegiatan_id = kegiatan.id) as jumlah_foto');
        $this->db->from('kegiatan');
        $this->db->join('users', 'users.id = kegiatan.dibuat_oleh', 'left');
        $this->_apply_filters($kategori, $bulan, $tahun, $cari);
        $this->db->order_by('kegiatan.tanggal_mulai', 'DESC');
        return $this->db->get()->result();
    }

    // ─── GET PUBLISHED (warga) ───────────────────────────────
    public function get_published($kategori = null, $bulan = null, $tahun = null, $cari = null) {
        $this->db->select('kegiatan.*, users.nama_lengkap as pembuat,
            (SELECT COUNT(*) FROM kegiatan_galeri kg WHERE kg.kegiatan_id = kegiatan.id) as jumlah_foto');
        $this->db->from('kegiatan');
        $this->db->join('users', 'users.id = kegiatan.dibuat_oleh', 'left');
        $this->db->where('kegiatan.status', 'publish');
        $this->_apply_filters($kategori, $bulan, $tahun, $cari);
        $this->db->order_by('kegiatan.tanggal_mulai', 'DESC');
        return $this->db->get()->result();
    }

    private function _apply_filters($kategori, $bulan, $tahun, $cari) {
        if($kategori && $kategori != 'semua') {
            $this->db->where('kegiatan.kategori', $kategori);
        }
        if($bulan && $tahun) {
            $this->db->where('MONTH(kegiatan.tanggal_mulai)', $bulan);
            $this->db->where('YEAR(kegiatan.tanggal_mulai)', $tahun);
        } elseif($tahun) {
            $this->db->where('YEAR(kegiatan.tanggal_mulai)', $tahun);
        }
        if($cari) {
            $this->db->like('kegiatan.judul', $cari);
        }
    }

    // ─── GET BY ID ───────────────────────────────────────────
    public function get_by_id($id) {
        $this->db->select('kegiatan.*, users.nama_lengkap as pembuat,
            (SELECT COUNT(*) FROM kegiatan_galeri kg WHERE kg.kegiatan_id = kegiatan.id) as jumlah_foto');
        $this->db->from('kegiatan');
        $this->db->join('users', 'users.id = kegiatan.dibuat_oleh', 'left');
        $this->db->where('kegiatan.id', $id);
        return $this->db->get()->row();
    }

    // ─── LATEST (dashboard) ──────────────────────────────────
    public function get_latest($limit = 4) {
        $this->db->select('kegiatan.*, users.nama_lengkap as pembuat,
            (SELECT COUNT(*) FROM kegiatan_galeri kg WHERE kg.kegiatan_id = kegiatan.id) as jumlah_foto');
        $this->db->from('kegiatan');
        $this->db->join('users', 'users.id = kegiatan.dibuat_oleh', 'left');
        $this->db->where('kegiatan.status', 'publish');
        $this->db->order_by('kegiatan.tanggal_mulai', 'DESC');
        $this->db->limit($limit);
        return $this->db->get()->result();
    }

    // ─── CRUD ────────────────────────────────────────────────
    public function insert($data) {
        $this->db->insert('kegiatan', $data);
        return $this->db->insert_id();
    }

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

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

    // ─── GALERI ──────────────────────────────────────────────
    public function get_galeri($kegiatan_id) {
        $this->db->where('kegiatan_id', $kegiatan_id);
        $this->db->order_by('urutan', 'ASC');
        return $this->db->get('kegiatan_galeri')->result();
    }

    public function get_galeri_by_id($id) {
        return $this->db->get_where('kegiatan_galeri', array('id' => $id))->row();
    }

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

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

    public function delete_galeri_by_kegiatan($kegiatan_id) {
        $this->db->where('kegiatan_id', $kegiatan_id);
        return $this->db->delete('kegiatan_galeri');
    }

    // ─── AVAILABLE YEARS ─────────────────────────────────────
    public function get_available_years() {
        $this->db->select('DISTINCT YEAR(tanggal_mulai) as tahun');
        $this->db->from('kegiatan');
        $this->db->where('tanggal_mulai IS NOT NULL');
        $this->db->order_by('tahun', 'DESC');
        $years = array();
        foreach($this->db->get()->result() as $row) {
            if($row->tahun) $years[] = $row->tahun;
        }
        if(empty($years)) $years[] = date('Y');
        return $years;
    }

    // ─── COUNT ───────────────────────────────────────────────
    public function count_active() {
        $this->db->where('status', 'publish');
        $this->db->where('tanggal_mulai <=', date('Y-m-d'));
        return $this->db->count_all_results('kegiatan');
    }
}