<?php

use app\libraries\CommonHelper;
use app\libraries\TenantHelper;
use app\libraries\Tenants;
use app\libraries\Userstamp;

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

class Invoice_model extends MY_Model {

    use Tenants;

//    public $database;
    public $short = "i";
    public $table;
    public $tbl;

    // TODO: place this value to setting_center table
    const days_before_payment_next_month = 7;

    public function __construct() {
        parent::__construct();
		$this->database = $this->getTenantNo();
        $this->tbl = 'invoicing_invoice';
        $this->table = $this->tbl . ' ' . $this->short;
        $this->load->model('Setting_center_model');
    }

    public function listing() {
        $this->tenant();
        //public function getAll($database, $table, $fields, $where = null) {
        return $this->getAll($this->database, $this->tbl);
    }

    public function detail($id) {
        $this->tenant();
        return $this->getOne("default", $this->tbl, [], $id);
    }

    public function multiple_invoices($ids) {
		$this->tenant();
		$db = $this->load->database($this->database, TRUE);
		$db->from($this->table_prefix . $this->tbl);
		$db->where_in('id', $ids);
		return $db->get()->result_array();
	}

    public function listing_in_datatables($startYear, $endYear, $startMonth, $endMonth) {
        $this->tenant();
        $db = $this->load->database($this->database, TRUE);
        /**
         * @var CI_DB_query_builder
         */
        $q = $db->from($this->table)
                ->join('mas_invoicing_invoice_item', 'mas_invoicing_invoice.id = mas_invoicing_invoice_item.invoice_id')
                // Student
                ->join('mas_student', 'mas_invoicing_invoice.user_id = mas_student.id');

        // Start Year, Start Month
        $q = $q->group_start()
                ->group_start()
                ->where('mas_invoicing_invoice.year', $startYear)
                ->where('mas_invoicing_invoice.month >=', $startMonth, false)
                ->group_end()
                ->or_where('mas_invoicing_invoice.year >', $startYear, false)
                ->group_end();

        // End Year, End Month
        $q = $q->group_start()
                ->group_start()
                ->where('mas_invoicing_invoice.year', $endYear)
                ->where('mas_invoicing_invoice.month <=', $endMonth, false)
                ->group_end()
                ->or_where('mas_invoicing_invoice.year <', $endYear, false)
                ->group_end();

        return $q->select('
            mas_invoicing_invoice.*,
            mas_invoicing_invoice_item.date_range,
            mas_invoicing_invoice_item.item_name AS course_name,
            mas_student.chi_name AS student_chi_name
        ')->get()->result_array();
    }

    public function create($params) {
        $this->tenant();
        $params['invoice_status'] = $this->getInvoiceStatus($params['year'], $params['month']);
        $params['id'] = $this->insertDB($this->database, $this->table, Userstamp::create($params, 'created_by', 'create_time'));
        return $params;
    }

    public function update($params, $id) {
        $this->tenant();
        $this->saveDB("default", $this->table, Userstamp::update($params, 'updated_by', 'update_time'), $id);
        $params['id'] = $id;
        return $params;
    }

    // Compare only the year and month, provided that we can use the method: Carbon::firstOfMonth()
    public function getInvoiceStatus($year, $month) {
        $hk = new DateTimeZone('Asia/Hong_Kong');

        $invoiceDate = DateTime::createFromFormat('Y-m-d', sprintf('%s-%s-1', $year, $month), $hk)->getTimestamp();

        $today = new DateTime('now', $hk);
        $_year = $today->format('y');
        $_month = $today->format('m');

        $d = DateTime::createFromFormat('Y-m-d', sprintf('%s-%s-1', $_year, $_month), $hk)->getTimestamp();
        $d1 = strtotime('+1 month', $d);

        switch (true) {
            case $d > $invoiceDate:
            case $d1 > $invoiceDate:
                return 'ready';
                break;
            default:
                $_d2 = strtotime('+7 days', $d1);
                $d2 = DateTime::createFromFormat('Y-m-d', date('Y-m-1', $_d2), $hk)->getTimestamp();

                if ($d2 > $invoiceDate) {
                    return 'ready';
                } else {
                    return 'pending';
                }
                break;
        }
    }

    public function notPrinted($invoice) {
        return empty($invoice['pathname']);
    }

    public function printed($invoice) {
       // $tenantNo = TenantHelper::getTenant();
		$issuedname = empty($invoice['issued_name'])? CommonHelper::GUID() : $invoice['issued_name'];
        return $this->update([
                    'issued_date' => time(),
                    'pathname' => sprintf('/invoice/%s.html',  $issuedname),
                        ], $invoice['id']);
    }

    public function void($invoiceId):array {
    	$invoice = $this->detail($invoiceId);
        $this->update([
            'invoice_status' => InvoiceStatus::void
                ], $invoiceId);
    	return $invoice;//for refund only
    }

    public function yearlyIncome ($year)
	{
		$firstday = sprintf("%04d", $year) . '-01-01 00:00:00';
		$start = strtotime($firstday);
		$end = strtotime($firstday . ' +1 year - 1 second');
		$this->tenant();
		$db = $this->load->database($this->database, TRUE);
		$db->select_sum('amount_to_pay', 'amount');
		$db->from($this->table_prefix . $this->tbl);
		$db->where('create_time >=', $start);
		$db->where('create_time <=', $end);
		return $db->get()->row_array()['amount'];
	}

	public function monthlyIncome ($year, $month)
	{
		$firstday = sprintf("%04d-%02d", $year, $month) . '-01 00:00:00';
		$start = strtotime($firstday);
		$end = strtotime($firstday . ' +1 month - 1 second');
		$this->tenant();
		$db = $this->load->database($this->database, TRUE);
		$db->select_sum('amount_to_pay', 'amount');
		$db->from($this->table_prefix . $this->tbl);
		$db->where('create_time >=', $start);
		$db->where('create_time <=', $end);
		return $db->get()->row_array()['amount'];
	}

	public function dailyIncome ($fromDate, $toDate)
	{
		$this->tenant();
		$db = $this->load->database($this->database, TRUE);
		$sql = "SELECT DATE(FROM_UNIXTIME(create_time)) AS `amount_date`, SUM(amount_to_pay) AS `amount` ";
		$sql .= "FROM " . $this->table_prefix . $this->tbl . " ";
		$sql .= "WHERE DATE(FROM_UNIXTIME(create_time)) BETWEEN '" . $fromDate . "' AND '" . $toDate . "' ";
		$sql .= "GROUP BY `amount_date`";
		$result = $db->query($sql)->result_array();
		$data = [];
		foreach ($result as $row) {
			$data[$row['amount_date']] = $row['amount'];
		}
		return $data;
	}

	public function currentYearIncome ($year)
	{
		$start = sprintf("%04d", $year) . "-01-01";
		$end = sprintf("%04d", $year) . "-12-31";
		$this->tenant();
		$db = $this->load->database($this->database, TRUE);
		$sql = "SELECT YEAR(FROM_UNIXTIME(create_time)) AS `amount_year`, MONTH(FROM_UNIXTIME(create_time)) AS `amount_month`, SUM(amount_to_pay) AS `amount` ";
		$sql .= "FROM " . $this->table_prefix . $this->tbl . " ";
		$sql .= "WHERE DATE(FROM_UNIXTIME(create_time)) BETWEEN '" . $start . "' AND '" . $end . "' ";
		$sql .= "GROUP BY `amount_year`, `amount_month`";
		$result = $db->query($sql)->result_array();
		$data = [];
		foreach ($result as $row) {
			$month = sprintf("%02d",$row['amount_month']);
			$data[$row['amount_year'] . '-' . $month] = $row['amount'];
		}
		return $data;
	}

	public function getCancelMergedInvoice($mergedcode){
    	return $this->getOneRow($this->database,$this->tbl,[],['mergedcode'=>$mergedcode,'invoice_status'=>InvoiceStatus::cancelmerged]);
	}
}
