<?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() {
        $this->db->select('
            divisions.id,
            divisions.name,
            COUNT(DISTINCT wards.id) as total_wards,
            COUNT(DISTINCT cells.id) as total_cells,
            COALESCE(SUM(cells.population), 0) as total_population,
            COALESCE(SUM(cells.households), 0) as total_households
        ');
        $this->db->from('divisions');
        $this->db->join('wards', 'wards.division_id = divisions.id', 'left');
        $this->db->join('cells', 'cells.ward_id = wards.id', 'left');
        $this->db->group_by('divisions.id');
        $this->db->order_by('divisions.name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_ward_statistics($division_id = null) {
        $this->db->select('
            wards.id,
            wards.name,
            wards.division_id,
            divisions.name as division_name,
            COUNT(DISTINCT cells.id) as total_cells,
            COALESCE(SUM(cells.population), 0) as total_population,
            COALESCE(SUM(cells.households), 0) as total_households
        ');
        $this->db->from('wards');
        $this->db->join('divisions', 'divisions.id = wards.division_id');
        $this->db->join('cells', 'cells.ward_id = wards.id', 'left');
        
        if ($division_id) {
            $this->db->where('wards.division_id', $division_id);
        }
        
        $this->db->group_by('wards.id');
        $this->db->order_by('divisions.name, wards.name', 'ASC');
        $query = $this->db->get();
        return $query->result_array();
    }
}
