<?php

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

class Finance_report_model extends MY_Model
{
    public $_table_name;
    public $_order_by;
    public $_primary_key;

    private $rollup_table = 'tbl_finance_rollups';
    private $rollup_ttl = 300;
    private $project_summary_ttl = 300;

    public function get_global_finance_rollup($force_refresh = false)
    {
        return $this->get_finance_rollup('global', 'all', $force_refresh);
    }

    public function get_client_finance_rollup($client_id, $force_refresh = false)
    {
        $client_id = (int) $client_id;
        if (empty($client_id)) {
            return $this->empty_rollup();
        }

        return $this->get_finance_rollup('client', (string) $client_id, $force_refresh);
    }

    public function refresh_global_finance_rollup()
    {
        return $this->refresh_finance_rollup('global', 'all');
    }

    public function refresh_client_finance_rollup($client_id)
    {
        $client_id = (int) $client_id;
        if (empty($client_id)) {
            return $this->empty_rollup();
        }

        return $this->refresh_finance_rollup('client', (string) $client_id);
    }

    public function refresh_finance_rollup_from_transaction(array $transaction)
    {
        $client_id = !empty($transaction['paid_by']) ? (int) $transaction['paid_by'] : 0;
        if (!empty($client_id)) {
            $this->refresh_client_finance_rollup($client_id);
        }
        $this->refresh_global_finance_rollup();
    }

    public function sum_table_amount($table, $column, array $where = array(), array $where_in = array())
    {
        $this->db->select_sum($column);
        foreach ($where as $key => $value) {
            $this->db->where($key, $value);
        }
        foreach ($where_in as $field => $values) {
            $this->db->where_in($field, $values);
        }
        $row = $this->db->get($table)->row();

        return (float) (!empty($row->{$column}) ? $row->{$column} : 0);
    }

    public function sum_transaction_amount($column, array $where = array())
    {
        return $this->sum_table_amount('tbl_transactions', $column, $where);
    }

    public function sum_payment_amount(array $where = array(), array $where_in = array())
    {
        if (empty($where) && empty($where_in)) {
            $rollup = $this->get_global_finance_rollup();
            return !empty($rollup['payment_total']) ? (float) $rollup['payment_total'] : 0;
        }

        if (isset($where['paid_by']) && count($where) === 1 && empty($where_in)) {
            $rollup = $this->get_client_finance_rollup((int) $where['paid_by']);
            return !empty($rollup['payment_total']) ? (float) $rollup['payment_total'] : 0;
        }

        return $this->sum_table_amount('tbl_payments', 'amount', $where, $where_in);
    }

    public function build_transaction_summary($column, $today, $first_day_month, $this_week_start, $before_30_days, array $where = array())
    {
        return array(
            'all_time' => $this->sum_transaction_amount($column, $where),
            'this_month' => $this->sum_transaction_amount($column, array_merge($where, array('date >=' => $first_day_month, 'date <=' => $today))),
            'this_week' => $this->sum_transaction_amount($column, array_merge($where, array('date >=' => $this_week_start, 'date <=' => $today))),
            'last_30_days' => $this->sum_transaction_amount($column, array_merge($where, array('date >=' => $before_30_days, 'date <=' => $today))),
        );
    }

    public function get_transaction_daily_series($type, $start_date, $end_date, $amount_column = 'amount')
    {
        $rows = $this->db
            ->select('date, COALESCE(SUM(' . $this->db->escape_identifiers($amount_column) . '),0) AS total_amount', false)
            ->where('type', $type)
            ->where('date >=', $start_date)
            ->where('date <=', $end_date)
            ->group_by('date')
            ->order_by('date', 'ASC')
            ->get('tbl_transactions')
            ->result();

        $series = array();
        $cursor = strtotime($start_date);
        $end = strtotime($end_date);
        while ($cursor !== false && $cursor <= $end) {
            $series[date('Y-m-d', $cursor)] = 0;
            $cursor = strtotime('+1 day', $cursor);
        }

        if (!empty($rows)) {
            foreach ($rows as $row) {
                $series[$row->date] = (float) $row->total_amount;
            }
        }

        return $series;
    }

    public function get_transaction_category_totals($type)
    {
        $rows = $this->db
            ->select('category_id, COALESCE(SUM(amount),0) AS total_amount', false)
            ->where('type', $type)
            ->group_by('category_id')
            ->get('tbl_transactions')
            ->result();

        $totals = array();
        if (!empty($rows)) {
            foreach ($rows as $row) {
                $totals[(int) $row->category_id] = (float) $row->total_amount;
            }
        }

        return $totals;
    }

    public function get_payment_daily_series($start_date, $end_date)
    {
        $rows = $this->db
            ->select('payment_date, COALESCE(SUM(amount),0) AS total_amount', false)
            ->where('payment_date >=', $start_date)
            ->where('payment_date <=', $end_date)
            ->group_by('payment_date')
            ->order_by('payment_date', 'ASC')
            ->get('tbl_payments')
            ->result();

        $series = array();
        $cursor = strtotime($start_date);
        $end = strtotime($end_date);
        while ($cursor !== false && $cursor <= $end) {
            $series[date('Y-m-d', $cursor)] = 0;
            $cursor = strtotime('+1 day', $cursor);
        }

        if (!empty($rows)) {
            foreach ($rows as $row) {
                $series[$row->payment_date] = (float) $row->total_amount;
            }
        }

        return $series;
    }

    public function get_project_expense_summary($project_id)
    {
        $project_id = (int) $project_id;
        if (empty($project_id)) {
            return array(
                'total_amount' => 0,
                'billable_amount' => 0,
                'not_billable_amount' => 0,
            );
        }

        $cache_key = $this->project_expense_summary_cache_key($project_id);
        $cached = function_exists('helper_cache_get') ? helper_cache_get($cache_key) : false;
        if ($cached !== false) {
            return $cached;
        }

        $row = $this->db
            ->select('COALESCE(SUM(amount),0) AS total_amount, COALESCE(SUM(CASE WHEN billable = "Yes" THEN amount ELSE 0 END),0) AS billable_amount, COALESCE(SUM(CASE WHEN billable = "No" THEN amount ELSE 0 END),0) AS not_billable_amount', false)
            ->where(array('project_id' => $project_id, 'type' => 'Expense'))
            ->get('tbl_transactions')
            ->row();

        $summary = array(
            'total_amount' => !empty($row->total_amount) ? (float) $row->total_amount : 0,
            'billable_amount' => !empty($row->billable_amount) ? (float) $row->billable_amount : 0,
            'not_billable_amount' => !empty($row->not_billable_amount) ? (float) $row->not_billable_amount : 0,
        );

        if (function_exists('helper_cache_save')) {
            helper_cache_save($cache_key, $summary, $this->project_summary_ttl);
        }

        return $summary;
    }

    public function get_project_billed_expense_total($project_id, $force_refresh = false)
    {
        $project_id = (int) $project_id;
        if (empty($project_id)) {
            return 0;
        }

        $cache_key = $this->project_billed_expense_total_cache_key($project_id);
        $cached = (!$force_refresh && function_exists('helper_cache_get')) ? helper_cache_get($cache_key) : false;
        if ($cached !== false) {
            return (float) $cached;
        }

        $invoice_ids = array();
        $expense_rows = $this->db
            ->select('DISTINCT invoices_id', false)
            ->from('tbl_transactions')
            ->where('project_id', $project_id)
            ->where('type', 'Expense')
            ->where('invoices_id !=', 0)
            ->get()
            ->result();
        if (!empty($expense_rows)) {
            foreach ($expense_rows as $expense_row) {
                if (!empty($expense_row->invoices_id)) {
                    $invoice_ids[] = (int) $expense_row->invoices_id;
                }
            }
        }

        $total = 0;
        if (!empty($invoice_ids)) {
            $row = $this->db
                ->select('COALESCE(SUM(amount),0) AS billed_total', false)
                ->where_in('invoices_id', array_values(array_unique($invoice_ids)))
                ->get('tbl_payments')
                ->row();
            $total = !empty($row->billed_total) ? (float) $row->billed_total : 0;
        }

        if (function_exists('helper_cache_save')) {
            helper_cache_save($cache_key, $total, $this->project_summary_ttl);
        }

        return $total;
    }

    public function get_project_invoice_summary($project_id, $client_id = null, $force_refresh = false)
    {
        $project_id = (int) $project_id;
        if (empty($project_id)) {
            return array(
                'rows' => array(),
                'currency_symbol' => '$',
                'total_amount' => 0,
                'total_due' => 0,
                'paid_amount' => 0,
                'overdue_count' => 0,
                'invoice_count' => 0,
            );
        }

        $cache_key = $this->project_invoice_summary_cache_key($project_id);
        $cached = (!$force_refresh && function_exists('helper_cache_get')) ? helper_cache_get($cache_key) : false;
        if ($cached !== false) {
            return $cached;
        }

        $rows = $this->db
            ->where(array('project_id' => $project_id))
            ->where_not_in('status', array('draft', 'Draft', 'cancelled', 'Cancelled'))
            ->order_by('invoices_id', 'DESC')
            ->get('tbl_invoices')
            ->result();

        $financial_map = array();
        if (!empty($rows)) {
            $CI = &get_instance();
            if (!isset($CI->invoice_model)) {
                $CI->load->model('invoice_model');
            }
            $financial_map = $CI->invoice_model->get_invoice_financial_map($rows);
        }

        $today = date('Y-m-d');
        $total_amount = 0;
        $total_due = 0;
        $paid_amount = 0;
        $overdue_count = 0;
        $enriched_rows = array();

        if (!empty($rows)) {
            foreach ($rows as $invoice_row) {
                $invoice_id = (int) $invoice_row->invoices_id;
                $financial = isset($financial_map[$invoice_id]) ? $financial_map[$invoice_id] : array(
                    'total' => 0,
                    'due' => 0,
                    'paid_amount' => 0,
                    'status' => lang('not_paid'),
                );

                $invoice_row->display_total = (float) $financial['total'];
                $invoice_row->display_due = (float) $financial['due'];
                $invoice_row->paid_amount = (float) $financial['paid_amount'];
                $invoice_row->payment_status = $financial['status'];
                $enriched_rows[] = $invoice_row;

                $total_amount += (float) $financial['total'];
                $total_due += (float) $financial['due'];
                $paid_amount += (float) $financial['paid_amount'];
                if (!empty($invoice_row->due_date) && $invoice_row->due_date < $today && (float) $financial['due'] > 0) {
                    $overdue_count++;
                }
            }
        }

        $currency_symbol = '$';
        if (!empty($client_id)) {
            $CI = &get_instance();
            if (!isset($CI->items_model)) {
                $CI->load->model('items_model');
            }
            $currency = $CI->items_model->client_currency_symbol($client_id);
            if (!empty($currency->symbol)) {
                $currency_symbol = $currency->symbol;
            }
        }

        $summary = array(
            'rows' => $enriched_rows,
            'currency_symbol' => $currency_symbol,
            'total_amount' => $total_amount,
            'total_due' => $total_due,
            'paid_amount' => $paid_amount,
            'overdue_count' => $overdue_count,
            'invoice_count' => !empty($enriched_rows) ? count($enriched_rows) : 0,
        );

        if (function_exists('helper_cache_save')) {
            helper_cache_save($cache_key, $summary, $this->project_summary_ttl);
        }

        return $summary;
    }

    public function refresh_project_finance_summary($project_id)
    {
        $project_id = (int) $project_id;
        if (empty($project_id)) {
            return array();
        }

        if (function_exists('finance_cache_delete')) {
            finance_cache_delete($this->project_expense_summary_cache_key($project_id));
            finance_cache_delete($this->project_billed_expense_total_cache_key($project_id));
            finance_cache_delete($this->project_invoice_summary_cache_key($project_id));
        }

        return array(
            'expense_summary' => $this->get_project_expense_summary($project_id),
            'billed_expense_total' => $this->get_project_billed_expense_total($project_id, true),
            'invoice_summary' => $this->get_project_invoice_summary($project_id, null, true),
        );
    }

    public function refresh_project_finance_summary_from_transaction(array $transaction)
    {
        if (!empty($transaction['project_id'])) {
            $this->refresh_project_finance_summary((int) $transaction['project_id']);
        }
    }

    public function refresh_project_finance_summary_from_invoice($project_id)
    {
        if (!empty($project_id)) {
            $this->refresh_project_finance_summary((int) $project_id);
        }
    }

    private function project_expense_summary_cache_key($project_id)
    {
        return 'project_expense_summary_' . (int) $project_id;
    }

    private function project_billed_expense_total_cache_key($project_id)
    {
        return 'project_billed_expense_total_' . (int) $project_id;
    }

    private function project_invoice_summary_cache_key($project_id)
    {
        return 'project_invoice_summary_' . (int) $project_id;
    }

    private function get_finance_rollup($summary_type, $summary_key, $force_refresh = false)
    {
        if (empty($summary_type) || empty($summary_key)) {
            return $this->empty_rollup();
        }

        if (!$this->db->table_exists($this->rollup_table)) {
            return $this->build_rollup_row($summary_type, $summary_key);
        }

        $row = $this->db
            ->where('summary_type', $summary_type)
            ->where('summary_key', (string) $summary_key)
            ->get($this->rollup_table)
            ->row_array();

        if (empty($row)) {
            return $this->refresh_finance_rollup($summary_type, $summary_key);
        }

        if ($force_refresh || $this->rollup_is_stale($row)) {
            return $this->refresh_finance_rollup($summary_type, $summary_key);
        }

        return $this->normalize_rollup_row($row);
    }

    private function refresh_finance_rollup($summary_type, $summary_key)
    {
        if (!$this->db->table_exists($this->rollup_table)) {
            return $this->build_rollup_row($summary_type, $summary_key);
        }

        $row = $this->build_rollup_row($summary_type, $summary_key);
        $this->db->replace($this->rollup_table, $row);
        return $row;
    }

    private function build_rollup_row($summary_type, $summary_key)
    {
        $row = array(
            'summary_type' => (string) $summary_type,
            'summary_key' => (string) $summary_key,
            'income_total' => 0,
            'expense_total' => 0,
            'payment_total' => 0,
            'updated_at' => date('Y-m-d H:i:s'),
        );

        if ($summary_type === 'global' && $summary_key === 'all') {
            $txn = $this->db
                ->select('COALESCE(SUM(credit),0) AS income_total, COALESCE(SUM(debit),0) AS expense_total', false)
                ->get('tbl_transactions')
                ->row_array();
            $pay = $this->db
                ->select('COALESCE(SUM(amount),0) AS payment_total', false)
                ->get('tbl_payments')
                ->row_array();
            $row['income_total'] = !empty($txn['income_total']) ? (float) $txn['income_total'] : 0;
            $row['expense_total'] = !empty($txn['expense_total']) ? (float) $txn['expense_total'] : 0;
            $row['payment_total'] = !empty($pay['payment_total']) ? (float) $pay['payment_total'] : 0;
            return $row;
        }

        if ($summary_type === 'client') {
            $client_id = (int) $summary_key;
            $txn = $this->db
                ->select('COALESCE(SUM(credit),0) AS income_total, COALESCE(SUM(debit),0) AS expense_total', false)
                ->where('paid_by', $client_id)
                ->get('tbl_transactions')
                ->row_array();
            $pay = $this->db
                ->select('COALESCE(SUM(amount),0) AS payment_total', false)
                ->where('paid_by', $client_id)
                ->get('tbl_payments')
                ->row_array();
            $row['income_total'] = !empty($txn['income_total']) ? (float) $txn['income_total'] : 0;
            $row['expense_total'] = !empty($txn['expense_total']) ? (float) $txn['expense_total'] : 0;
            $row['payment_total'] = !empty($pay['payment_total']) ? (float) $pay['payment_total'] : 0;
            return $row;
        }

        return $row;
    }

    private function normalize_rollup_row(array $row)
    {
        return array(
            'summary_type' => !empty($row['summary_type']) ? $row['summary_type'] : '',
            'summary_key' => !empty($row['summary_key']) ? $row['summary_key'] : '',
            'income_total' => !empty($row['income_total']) ? (float) $row['income_total'] : 0,
            'expense_total' => !empty($row['expense_total']) ? (float) $row['expense_total'] : 0,
            'payment_total' => !empty($row['payment_total']) ? (float) $row['payment_total'] : 0,
            'updated_at' => !empty($row['updated_at']) ? $row['updated_at'] : null,
        );
    }

    private function empty_rollup()
    {
        return array(
            'summary_type' => '',
            'summary_key' => '',
            'income_total' => 0,
            'expense_total' => 0,
            'payment_total' => 0,
            'updated_at' => null,
        );
    }

    private function rollup_is_stale(array $row)
    {
        if (empty($row['updated_at'])) {
            return true;
        }

        $updated_at = strtotime($row['updated_at']);
        if ($updated_at === false) {
            return true;
        }

        return (time() - $updated_at) > $this->rollup_ttl;
    }
}
