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

class Migration_Version_617 extends CI_Migration
{
    public function __construct()
    {
        parent::__construct();
    }

    public function up()
    {
        if (!$this->db->table_exists('tbl_finance_rollups')) {
            $this->db->query(
                "CREATE TABLE `tbl_finance_rollups` (
                    `rollup_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                    `summary_type` varchar(32) NOT NULL,
                    `summary_key` varchar(64) NOT NULL,
                    `income_total` decimal(20,2) NOT NULL DEFAULT '0.00',
                    `expense_total` decimal(20,2) NOT NULL DEFAULT '0.00',
                    `payment_total` decimal(20,2) NOT NULL DEFAULT '0.00',
                    `updated_at` datetime DEFAULT NULL,
                    PRIMARY KEY (`rollup_id`),
                    UNIQUE KEY `uq_tbl_finance_rollups_summary` (`summary_type`,`summary_key`),
                    KEY `idx_tbl_finance_rollups_summary_type` (`summary_type`),
                    KEY `idx_tbl_finance_rollups_summary_key` (`summary_key`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
            );
        }

        $this->backfill_rollups();

        if ($this->db->table_exists('tbl_config')) {
            $this->db->where('config_key', 'version')->update('tbl_config', array('value' => '6.1.7'));
        }
    }

    public function down()
    {
        if ($this->db->table_exists('tbl_finance_rollups')) {
            $this->db->query("DROP TABLE `tbl_finance_rollups`");
        }

        if ($this->db->table_exists('tbl_config')) {
            $this->db->where('config_key', 'version')->update('tbl_config', array('value' => '6.1.6'));
        }
    }

    private function backfill_rollups()
    {
        if (!$this->db->table_exists('tbl_finance_rollups')) {
            return;
        }

        $this->db->truncate('tbl_finance_rollups');

        $global_txn = $this->db
            ->select('COALESCE(SUM(credit),0) AS income_total, COALESCE(SUM(debit),0) AS expense_total', false)
            ->get('tbl_transactions')
            ->row_array();
        $global_pay = $this->db
            ->select('COALESCE(SUM(amount),0) AS payment_total', false)
            ->get('tbl_payments')
            ->row_array();
        $this->db->insert('tbl_finance_rollups', array(
            'summary_type' => 'global',
            'summary_key' => 'all',
            'income_total' => !empty($global_txn['income_total']) ? (float) $global_txn['income_total'] : 0,
            'expense_total' => !empty($global_txn['expense_total']) ? (float) $global_txn['expense_total'] : 0,
            'payment_total' => !empty($global_pay['payment_total']) ? (float) $global_pay['payment_total'] : 0,
            'updated_at' => date('Y-m-d H:i:s'),
        ));

        $transaction_clients = $this->db
            ->select('paid_by AS client_id, COALESCE(SUM(credit),0) AS income_total, COALESCE(SUM(debit),0) AS expense_total', false)
            ->where('paid_by >', 0)
            ->group_by('paid_by')
            ->get('tbl_transactions')
            ->result_array();
        $payment_clients = $this->db
            ->select('paid_by AS client_id, COALESCE(SUM(amount),0) AS payment_total', false)
            ->where('paid_by >', 0)
            ->group_by('paid_by')
            ->get('tbl_payments')
            ->result_array();

        $client_map = array();
        foreach ($transaction_clients as $row) {
            $client_id = (int) $row['client_id'];
            if (empty($client_id)) {
                continue;
            }
            if (!isset($client_map[$client_id])) {
                $client_map[$client_id] = array('income_total' => 0, 'expense_total' => 0, 'payment_total' => 0);
            }
            $client_map[$client_id]['income_total'] = !empty($row['income_total']) ? (float) $row['income_total'] : 0;
            $client_map[$client_id]['expense_total'] = !empty($row['expense_total']) ? (float) $row['expense_total'] : 0;
        }
        foreach ($payment_clients as $row) {
            $client_id = (int) $row['client_id'];
            if (empty($client_id)) {
                continue;
            }
            if (!isset($client_map[$client_id])) {
                $client_map[$client_id] = array('income_total' => 0, 'expense_total' => 0, 'payment_total' => 0);
            }
            $client_map[$client_id]['payment_total'] = !empty($row['payment_total']) ? (float) $row['payment_total'] : 0;
        }

        foreach ($client_map as $client_id => $totals) {
            $this->db->insert('tbl_finance_rollups', array(
                'summary_type' => 'client',
                'summary_key' => (string) $client_id,
                'income_total' => $totals['income_total'],
                'expense_total' => $totals['expense_total'],
                'payment_total' => $totals['payment_total'],
                'updated_at' => date('Y-m-d H:i:s'),
            ));
        }
    }
}
