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

class Administrative_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    // ===================== DIVISIONS =====================
    
    public function get_all_divisions() {
        $this->db->select('*');
        $this->db->from('divisions');
        $this->db->order_by('name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_division_by_id($division_id) {
        $this->db->where('id', $division_id);
        $query = $this->db->get('divisions');
        return $query->row_array();
    }

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

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

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

    // ===================== WARDS =====================
    
    public function get_all_wards() {
        $this->db->select('wards.*, divisions.name as division_name');
        $this->db->from('wards');
        $this->db->join('divisions', 'divisions.id = wards.division_id');
        $this->db->order_by('divisions.name, wards.name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_wards_by_division($division_id) {
        $this->db->select('*');
        $this->db->from('wards');
        $this->db->where('division_id', $division_id);
        $this->db->order_by('name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_ward_by_id($ward_id) {
        $this->db->select('wards.*, divisions.name as division_name');
        $this->db->from('wards');
        $this->db->join('divisions', 'divisions.id = wards.division_id');
        $this->db->where('wards.id', $ward_id);
        $query = $this->db->get();
        return $query->row_array();
    }

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

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

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

    // ===================== CELLS =====================
    
    public function get_all_cells() {
        $this->db->select('cells.*, wards.name as ward_name, divisions.name as division_name');
        $this->db->from('cells');
        $this->db->join('wards', 'wards.id = cells.ward_id');
        $this->db->join('divisions', 'divisions.id = wards.division_id');
        $this->db->order_by('divisions.name, wards.name, cells.name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_cells_by_ward($ward_id) {
        $this->db->select('*');
        $this->db->from('cells');
        $this->db->where('ward_id', $ward_id);
        $this->db->order_by('name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_cells_by_division($division_id) {
        $this->db->select('cells.*, wards.name as ward_name');
        $this->db->from('cells');
        $this->db->join('wards', 'wards.id = cells.ward_id');
        $this->db->where('wards.division_id', $division_id);
        $this->db->order_by('wards.name, cells.name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_cell_by_id($cell_id) {
        $this->db->select('cells.*, wards.name as ward_name, wards.division_id, divisions.name as division_name');
        $this->db->from('cells');
        $this->db->join('wards', 'wards.id = cells.ward_id');
        $this->db->join('divisions', 'divisions.id = wards.division_id');
        $this->db->where('cells.id', $cell_id);
        $query = $this->db->get();
        return $query->row_array();
    }

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

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

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

    // ===================== STATISTICS =====================

    public function get_division_statistics() {
        $sql = "
            SELECT
                d.id,
                d.name,
                d.code,
                COUNT(DISTINCT w.id)  AS total_wards,
                COUNT(DISTINCT c.id)  AS total_cells,
                COALESCE(SUM(c.population), 0)   AS total_population,
                COUNT(DISTINCT h.id)  AS total_households
            FROM divisions d
            LEFT JOIN wards      w ON w.division_id = d.id
            LEFT JOIN cells      c ON c.ward_id     = w.id
            LEFT JOIN households h ON h.cell_id     = c.id
            GROUP BY d.id
            ORDER BY d.name ASC
        ";
        return $this->db->query($sql)->result_array();
    }

    public function get_ward_statistics($division_id = null) {
        $where = $division_id ? "WHERE w.division_id = " . (int)$division_id : '';
        $sql = "
            SELECT
                w.id,
                w.name,
                w.division_id,
                d.name AS division_name,
                COUNT(DISTINCT c.id)  AS total_cells,
                COALESCE(SUM(c.population), 0)   AS total_population,
                COUNT(DISTINCT h.id)  AS total_households
            FROM wards w
            JOIN  divisions  d ON d.id          = w.division_id
            LEFT JOIN cells  c ON c.ward_id     = w.id
            LEFT JOIN households h ON h.cell_id = c.id
            {$where}
            GROUP BY w.id
            ORDER BY d.name ASC, w.name ASC
        ";
        return $this->db->query($sql)->result_array();
    }
}