<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Propertyreports_model extends App_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function getPropertySummaryData()
    {
        $this->db->select('property_purpose, COUNT(*) as total');
        $this->db->from(db_prefix() . 'propertybase_properties');
        $this->db->group_by('property_purpose');

        $query = $this->db->get();
        $properties = $query->result_array();

        $totalForSale = 0;
        $totalForRent = 0;
        $totalForSaleAndRent = 0;
        $totalProperties = 0;

        foreach ($properties as $property) {
            $totalProperties += $property['total'];

            $this->db->select_sum('total');
            $this->db->from(db_prefix() . 'invoices');
            $this->db->where('property_id > 0');
            $this->db->where('status', 2);
            $invoiceQuery = $this->db->get();
            $invoiceTotal = $invoiceQuery->row()->total;

            $this->db->select_sum('amount');
            $this->db->from(db_prefix() . 'expenses');
            $this->db->where('property_id > 0');
            $expenseQuery = $this->db->get();
            $expenseTotal = $expenseQuery->row()->amount;

            $earnings = $invoiceTotal - $expenseTotal;

            switch ($property['property_purpose']) {
                case 1:
                    $totalForSale = $property['total'];
                    break;
                case 2:
                    $totalForRent = $property['total'];
                    break;
                case 3:
                    $totalForSaleAndRent = $property['total'];
                    break;
            }

        }

        return [
            'total_properties' => $totalProperties,
            'total_for_sale' => $totalForSale,
            'total_for_rent' => $totalForRent,
            'total_for_sale_and_rent' => $totalForSaleAndRent,
            'total_invoices_paid' => app_format_money($invoiceTotal ?? 0, get_base_currency()->id),
            'total_expenses' => app_format_money($expenseTotal ?? 0, get_base_currency()->id),
            'final_balance' => app_format_money($earnings ?? 0, get_base_currency()->id)
        ];
    }

    public function propertyLocationOnMapChart()
    {
        $this->db->select('p.id, c.short_name as country_name, p.lat, p.lng, COUNT(p.id) as property_count');
        $this->db->from(db_prefix() . 'propertybase_properties p');
        $this->db->join(db_prefix() . 'countries c', 'p.country = c.country_id', 'left');
        $this->db->where('p.lat IS NOT NULL');
        $this->db->where('p.lng IS NOT NULL');
        $this->db->group_by('p.lat, p.lng, p.id');

        $properties = $this->db->get()->result_array();

        foreach ($properties as &$property) {
            $property_id = (int)$property['id'];

            $property['property_name'] = propertybase_formatted_property_name($property_id, true);
            $property['property_address'] = propertybase_formatted_property_location($property_id);
        }

        return $properties;
    }

    public function getPropertyCategoryDistribution()
    {
        $this->db->select('c.name as category_name, COUNT(p.id) as property_count');
        $this->db->from(db_prefix() . 'propertybase_properties p');
        $this->db->join(db_prefix() . 'propertybase_categories c', 'p.category = c.id', 'left');
        $this->db->group_by('p.category');

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

    public function getPropertyLeadsByMonth()
    {
        $currentYear = date('Y');

        $this->db->select("YEAR(pl.created_at) as year, MONTH(pl.created_at) as month, 
                       COUNT(pl.lead_id) as lead_count, 
                       p.id as property_id, p.name");

        $this->db->from(db_prefix() . 'propertybase_properties_leads pl');
        $this->db->join(db_prefix() . 'propertybase_properties p', 'pl.property_id = p.id', 'left');
        $this->db->where('YEAR(pl.created_at)', $currentYear);
        $this->db->group_by('YEAR(pl.created_at), MONTH(pl.created_at), p.id');
        $this->db->order_by('YEAR(pl.created_at) DESC, MONTH(pl.created_at) DESC');

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

    public function getLeadsPerMonthCurrentYear()
    {
        $currentYear = date('Y');

        $this->db->select('MONTH(created_at) AS month, COUNT(lead_id) AS lead_count');
        $this->db->from(db_prefix() . 'propertybase_properties_leads');
        $this->db->where('YEAR(created_at)', $currentYear);
        $this->db->group_by('MONTH(created_at)');
        $this->db->order_by('month', 'ASC');

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

    public function getPropertyOwnershipDistribution()
    {
        $this->db->select('ot.name AS ownership_type, COUNT(p.id) AS property_count');
        $this->db->from(db_prefix() . 'propertybase_properties p');
        $this->db->join(db_prefix() . 'propertybase_ownership_types ot', 'p.ownership = ot.id', 'left');
        $this->db->group_by('p.ownership');
        $this->db->order_by('property_count', 'DESC');

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

    public function getMaintenanceRequestsByProperty()
    {
        $currentYear = date('Y');

        $this->db->select('p.id AS property_id, p.name AS property_name, 
                        COUNT(m.id) AS maintenance_count, 
                        MONTH(m.created_at) AS month');

        $this->db->from(db_prefix() . 'propertybase_properties_maintenance m');
        $this->db->join(db_prefix() . 'propertybase_properties p', 'm.property_id = p.id', 'left');
        $this->db->where('YEAR(m.created_at)', $currentYear);
        $this->db->group_by('p.id, MONTH(m.created_at)');
        $this->db->order_by('p.name, MONTH(m.created_at)', 'ASC');

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

}