<?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_division_detail($division_id) {
        $id = (int)$division_id;

        $basic = $this->db->query("
            SELECT d.id, d.name, d.code, d.description,
                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,
                COALESCE(SUM(h.male_members), 0)      AS male_members,
                COALESCE(SUM(h.female_members), 0)    AS female_members
            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
            WHERE d.id = ?
            GROUP BY d.id
        ", [$id])->row_array();

        if (!$basic) return null;

        // Ward-level stats
        $basic['ward_stats'] = $this->db->query("
            SELECT w.id, w.name, w.code,
                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
            LEFT JOIN cells      c ON c.ward_id = w.id
            LEFT JOIN households h ON h.cell_id = c.id
            WHERE w.division_id = ?
            GROUP BY w.id ORDER BY w.name ASC
        ", [$id])->result_array();

        // Nationality breakdown from population_statistics (reference population)
        $basic['nationality'] = $this->db->query("
            SELECT nationality, refugee_status,
                SUM(total_households)  AS total_households,
                SUM(total_population)  AS total_population,
                SUM(male_population)   AS male_population,
                SUM(female_population) AS female_population
            FROM population_statistics
            WHERE division_id = ? AND ward_id IS NULL AND cell_id IS NULL
            GROUP BY nationality, refugee_status
            ORDER BY total_population DESC
        ", [$id])->result_array();

        // Household condition breakdowns (from actual collected data)
        $cond_join = "FROM households h JOIN cells c ON c.id=h.cell_id JOIN wards w ON w.id=c.ward_id WHERE w.division_id=?";

        $basic['water_sources'] = $this->db->query(
            "SELECT h.water_source AS label, COUNT(*) AS count $cond_join AND h.water_source IS NOT NULL GROUP BY h.water_source ORDER BY count DESC", [$id]
        )->result_array();

        $basic['energy_lighting'] = $this->db->query(
            "SELECT h.energy_lighting AS label, COUNT(*) AS count $cond_join AND h.energy_lighting IS NOT NULL GROUP BY h.energy_lighting ORDER BY count DESC", [$id]
        )->result_array();

        $basic['toilet_facilities'] = $this->db->query(
            "SELECT h.toilet_facilities AS label, COUNT(*) AS count $cond_join AND h.toilet_facilities IS NOT NULL GROUP BY h.toilet_facilities ORDER BY count DESC", [$id]
        )->result_array();

        $basic['household_types'] = $this->db->query(
            "SELECT h.household_type AS label, COUNT(*) AS count $cond_join GROUP BY h.household_type ORDER BY count DESC", [$id]
        )->result_array();

        $basic['documentation'] = $this->db->query(
            "SELECT h.documentation_status AS label, COUNT(*) AS count $cond_join AND h.documentation_status IS NOT NULL GROUP BY h.documentation_status ORDER BY count DESC", [$id]
        )->result_array();

        // Monthly registration trend
        $basic['monthly_trend'] = $this->db->query("
            SELECT DATE_FORMAT(h.registration_date,'%b') AS month,
                   DATE_FORMAT(h.registration_date,'%Y-%m') AS month_key,
                   COUNT(*) AS count
            $cond_join AND h.registration_date IS NOT NULL
            GROUP BY month_key ORDER BY month_key ASC LIMIT 12
        ", [$id])->result_array();

        return $basic;
    }

    public function get_ward_detail($ward_id) {
        $id = (int)$ward_id;

        $basic = $this->db->query("
            SELECT w.id, w.name, w.code, w.description,
                d.id AS division_id, d.name AS division_name, d.code AS division_code,
                COUNT(DISTINCT c.id)               AS total_cells,
                COALESCE(SUM(c.population), 0)     AS total_population,
                COUNT(DISTINCT h.id)               AS total_households,
                COALESCE(SUM(h.male_members), 0)   AS male_members,
                COALESCE(SUM(h.female_members), 0) AS female_members
            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 w.id = ?
            GROUP BY w.id
        ", [$id])->row_array();

        if (!$basic) return null;

        // Cell breakdown
        $basic['cell_stats'] = $this->db->query("
            SELECT c.id, c.name, c.code, c.population AS ref_population,
                COUNT(DISTINCT h.id) AS total_households
            FROM cells c
            LEFT JOIN households h ON h.cell_id = c.id
            WHERE c.ward_id = ?
            GROUP BY c.id ORDER BY c.population DESC
        ", [$id])->result_array();

        // Nationality from population_statistics (ward level)
        $basic['nationality'] = $this->db->query("
            SELECT nationality, refugee_status,
                SUM(total_households)  AS total_households,
                SUM(total_population)  AS total_population,
                SUM(male_population)   AS male_population,
                SUM(female_population) AS female_population
            FROM population_statistics
            WHERE ward_id = ? AND cell_id IS NULL
            GROUP BY nationality, refugee_status
            ORDER BY total_population DESC
        ", [$id])->result_array();

        // Household condition breakdowns
        $join = "FROM households h JOIN cells c ON c.id=h.cell_id WHERE c.ward_id=?";

        $basic['water_sources'] = $this->db->query(
            "SELECT h.water_source AS label, COUNT(*) AS count $join AND h.water_source IS NOT NULL GROUP BY h.water_source ORDER BY count DESC", [$id]
        )->result_array();

        $basic['energy_lighting'] = $this->db->query(
            "SELECT h.energy_lighting AS label, COUNT(*) AS count $join AND h.energy_lighting IS NOT NULL GROUP BY h.energy_lighting ORDER BY count DESC", [$id]
        )->result_array();

        $basic['toilet_facilities'] = $this->db->query(
            "SELECT h.toilet_facilities AS label, COUNT(*) AS count $join AND h.toilet_facilities IS NOT NULL GROUP BY h.toilet_facilities ORDER BY count DESC", [$id]
        )->result_array();

        $basic['household_types'] = $this->db->query(
            "SELECT h.household_type AS label, COUNT(*) AS count $join GROUP BY h.household_type ORDER BY count DESC", [$id]
        )->result_array();

        $basic['documentation'] = $this->db->query(
            "SELECT h.documentation_status AS label, COUNT(*) AS count $join AND h.documentation_status IS NOT NULL GROUP BY h.documentation_status ORDER BY count DESC", [$id]
        )->result_array();

        $basic['monthly_trend'] = $this->db->query("
            SELECT DATE_FORMAT(h.registration_date,'%b') AS month,
                   DATE_FORMAT(h.registration_date,'%Y-%m') AS month_key,
                   COUNT(*) AS count
            $join AND h.registration_date IS NOT NULL
            GROUP BY month_key ORDER BY month_key ASC LIMIT 12
        ", [$id])->result_array();

        return $basic;
    }

    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();
    }
}