<?php

namespace App\Models;

use CodeIgniter\Database\ResultInterface;
use Config\OSPOS;

/**
 * Customer class
 */
class Customer extends Person
{
	protected $table = 'customers';
	protected $primaryKey = 'person_id';
	protected $useAutoIncrement = false;
	protected $useSoftDeletes = false;
	protected $allowedFields = [
		'account_number',
		'taxable',
		'tax_id',
		'sales_tax_code_id',
		'deleted',
		'discount',
		'discount_type',
		'company_name',
		'package_id',
		'points',
		'date',
		'employee_id',
		'consent'
	];


	/**
	 * Determines if a given person_id is a customer
	 */
	public function exists(int $person_id): bool
	{
		$builder = $this->db->table('customers');
		$builder->join('people', 'people.person_id = customers.person_id');
		$builder->where('customers.person_id', $person_id);
		return ($builder->get()->getNumRows() == 1);
	}

	/**
	 * Checks if account number exists
	 */
	public function check_account_number_exists(string $account_number, string $person_id = ''): bool
	{
		$builder = $this->db->table('customers');
		$builder->where('account_number', $account_number);

		if(!empty($person_id))
		{
			$builder->where('person_id !=', $person_id);
		}

		return ($builder->get()->getNumRows() == 1);	//TODO: ===
	}

	/**
	 * Gets total of rows
	 */
	public function get_total_rows(): int
	{
		$builder = $this->db->table('customers');
		$builder->where('deleted', 0);

		return $builder->countAllResults();
	}

	/**
	 * Returns all the customers
	 */
	public function get_all(int $limit = 0, int $offset = 0): ResultInterface
	{
		$builder = $this->db->table('customers');
		$builder->join('people', 'customers.person_id = people.person_id');
		$builder->where('deleted', 0);
		$builder->orderBy('last_name', 'asc');

		if($limit > 0)
		{
			$builder->limit($limit, $offset);
		}

		return $builder->get();
	}

	/**
	 * Gets information about a particular customer
	 */
	public function get_info(?int $person_id): object
	{
		$builder = $this->db->table('customers');
		$builder->join('people', 'people.person_id = customers.person_id');
		$builder->where('customers.person_id', $person_id);
		$query = $builder->get();

		return $query->getNumRows() === 1
			? $query->getRow()
			: $this->getEmptyObject('customers');
	}

	/**
	 * Initializes an empty object based on database definitions
	 * @param string $table_name
	 * @return object
	 */
	private function getEmptyObject(string $table_name): object
	{
		// Return an empty base parent object, as $item_id is NOT an item
		$empty_obj = parent::get_info(NEW_ENTRY);

		// Iterate through field definitions to determine how the fields should be initialized
		foreach($this->db->getFieldData($table_name) as $field)
		{
			$field_name = $field->name;

			if(in_array($field->type, ['int', 'tinyint', 'decimal']))
			{
				$empty_obj->$field_name = ($field->primary_key == 1) ? NEW_ENTRY : 0;
			}
			else
			{
				$empty_obj->$field_name = null;
			}
		}

		return $empty_obj;
	}


	/**
	 * Gets stats about a particular customer
	 */
	public function get_stats(int $customer_id)
	{
		$db_prefix = $this->db->getPrefix();
		$totals_decimals = totals_decimals();
		$quantity_decimals = quantity_decimals();

		//Temp Table
		$builder = $this->db->table('sales');
		$builder->select('sales.sale_id AS sale_id, AVG(`' . $db_prefix . 'sales_items`.`discount`) AS avg_discount, SUM(`' . $db_prefix . 'sales_items`.`quantity_purchased`) AS quantity');
		$builder->join('sales_items', 'sales_items.sale_id = sales.sale_id');
		$builder->where('sales.customer_id', $customer_id);
		$builder->groupBy('sale_id');
		$selectQuery = $builder->getCompiledSelect();

		$sql = 'CREATE TEMPORARY TABLE IF NOT EXISTS ' . $this->db->prefixTable('sales_items_temp');
		$sql .= ' (INDEX(sale_id)) ENGINE=MEMORY (' . $selectQuery . ')';
		$this->db->query($sql);

		//Get data
		$builder = $this->db->table('sales');
		$builder->select([
			'SUM(sales_payments.payment_amount - sales_payments.cash_refund) AS total',
			'MIN(sales_payments.payment_amount - sales_payments.cash_refund) AS min',
			'MAX(sales_payments.payment_amount - sales_payments.cash_refund) AS max',
			'AVG(sales_payments.payment_amount - sales_payments.cash_refund) AS average',
			"ROUND(AVG(sales_items_temp.avg_discount), $totals_decimals) AS avg_discount",
			"ROUND(SUM(sales_items_temp.quantity), $quantity_decimals) AS quantity"
		]);
		$builder->join('sales_payments AS sales_payments', 'sales.sale_id = sales_payments.sale_id');
		$builder->join('sales_items_temp AS sales_items_temp', 'sales.sale_id = sales_items_temp.sale_id');
		$builder->where('sales.customer_id', $customer_id);
		$builder->where('sales.sale_status', COMPLETED);
		$builder->groupBy('sales.customer_id');

		$stat = $builder->get()->getRow();

		//Drop Temp Table
		$sql = 'DROP TEMPORARY TABLE IF EXISTS ' . $this->db->prefixTable('sales_items_temp');
		$this->db->query($sql);

		return $stat;
	}

	/**
	 * Gets information about multiple customers
	 */
	public function get_multiple_info(array $person_ids): ResultInterface
	{
		$builder = $this->db->table('customers');
		$builder->join('people', 'people.person_id = customers.person_id');
		$builder->whereIn('customers.person_id', $person_ids);
		$builder->orderBy('last_name', 'asc');

		return $builder->get();
	}

	/**
	 * Checks if customer email exists
	 */
	public function check_email_exists(string $email, string $customer_id = ''): bool
	{
		// if the email is empty return like it is not existing
		if(empty($email))
		{
			return false;
		}

		$builder = $this->db->table('customers');
		$builder->join('people', 'people.person_id = customers.person_id');
		$builder->where('people.email', $email);
		$builder->where('customers.deleted', 0);

		if(!empty($customer_id))
		{
			$builder->where('customers.person_id !=', $customer_id);
		}

		return ($builder->get()->getNumRows() == 1);	//TODO: ===
	}

	/**
	 * Inserts or updates a customer
	 */
	public function save_customer(array &$person_data, array &$customer_data, int $customer_id = NEW_ENTRY): bool
	{
		$success = false;
		$this->db->transStart();

		if(parent::save_value($person_data, $customer_id))
		{
			$builder = $this->db->table('customers');
			if($customer_id == NEW_ENTRY || !$customer_id || !$this->exists($customer_id))
			{
				$customer_data['person_id'] = $person_data['person_id'];
				$success = $builder->insert($customer_data);
			}
			else
			{
				$builder->where('person_id', $customer_id);
				$success = $builder->update($customer_data);
			}
		}

		$this->db->transComplete();

		$success &= $this->db->transStatus();

		return $success;
	}

	/**
	 * Updates reward points value
	 */
	public function update_reward_points_value(int $customer_id, int $value): void
	{
		$builder = $this->db->table('customers');
		$builder->where('person_id', $customer_id);
		$builder->update(['points' => $value]);
	}

	/**
	 * @param $customer_id
	 * @param bool $purge
	 * @return bool
	 */
	public function delete($customer_id = null, bool $purge = false): bool
	{
		$result = true;
		$config = config(OSPOS::class)->settings;

		// if privacy enforcement is selected scramble customer data
		if($config['enforce_privacy'])
		{
			$builder = $this->db->table('people');
			$builder->where('person_id', $customer_id);
			$result &= $builder->update([
					'first_name' => $customer_id,
					'last_name' => $customer_id,
					'phone_number' => '',
					'email' => '',
					'gender' => null,
					'address_1' => '',
					'address_2' => '',
					'city' => '',
					'state' => '',
					'zip' => '',
					'country' => '',
					'comments' => ''
				]);

			$builder = $this->db->table('customers');
			$builder->where('person_id', $customer_id);
			$result &= $builder->update([
					'consent' => 0,
					'company_name' => null,
					'account_number' => null,
					'tax_id' => '',
					'taxable' => 0,
					'discount' => 0.00,
					'discount_type' => 0,
					'package_id' => null,
					'points' => null,
					'sales_tax_code_id' => null,
					'deleted' => 1
				]);
		}
		else
		{
			$builder = $this->db->table('customers');
			$builder->where('person_id', $customer_id);

			$result &= $builder->update(['deleted' => 1]);
		}

		return $result;
	}

	/**
	 * Deletes a list of customers
	 */
	public function delete_list(array $person_ids): bool
	{
		$builder = $this->db->table('customers');
		$builder->whereIn('person_id', $person_ids);

		return $builder->update(['deleted' => 1]);
 	}

 	/**
	 * Get search suggestions to find customers
	 */
	public function get_search_suggestions(string $search, int $limit = 25, bool $unique = true): array
	{
		$suggestions = [];

		$builder = $this->db->table('customers');
		$builder->join('people', 'customers.person_id = people.person_id');
		$builder->groupStart();
			$builder->like('first_name', $search);
			$builder->orLike('last_name', $search);
			$builder->orLike('CONCAT(first_name, " ", last_name)', $search);

			if($unique)
			{
				$builder->orLike('email', $search);
				$builder->orLike('phone_number', $search);
				$builder->orLike('company_name', $search);
			}
		$builder->groupEnd();
		$builder->where('deleted', 0);
		$builder->orderBy('last_name', 'asc');

		foreach($builder->get()->getResult() as $row)
		{
			$suggestions[] = [
				'value' => $row->person_id,
				'label' => $row->first_name . ' ' . $row->last_name . (!empty($row->company_name) ? ' [' . $row->company_name . ']' : ''). (!empty($row->phone_number) ? ' [' . $row->phone_number . ']' : '')
			];
		}

		if(!$unique)
		{
			$builder = $this->db->table('customers');
			$builder->join('people', 'customers.person_id = people.person_id');
			$builder->where('deleted', 0);
			$builder->like('email', $search);
			$builder->orderBy('email', 'asc');

			foreach($builder->get()->getResult() as $row)
			{
				$suggestions[] = ['value' => $row->person_id, 'label' => $row->email];
			}

			$builder = $this->db->table('customers');
			$builder->join('people', 'customers.person_id = people.person_id');
			$builder->where('deleted', 0);
			$builder->like('phone_number', $search);
			$builder->orderBy('phone_number', 'asc');

			foreach($builder->get()->getResult() as $row)
			{
				$suggestions[] = ['value' => $row->person_id, 'label' => $row->phone_number];
			}

			$builder = $this->db->table('customers');
			$builder->join('people', 'customers.person_id = people.person_id');
			$builder->where('deleted', 0);
			$builder->like('account_number', $search);
			$builder->orderBy('account_number', 'asc');

			foreach($builder->get()->getResult() as $row)
			{
				$suggestions[] = ['value' => $row->person_id, 'label' => $row->account_number];
			}

			$builder = $this->db->table('customers');
			$builder->join('people', 'customers.person_id = people.person_id');
			$builder->where('deleted', 0);
			$builder->like('company_name', $search);
			$builder->orderBy('company_name', 'asc');

			foreach($builder->get()->getResult() as $row)
			{
				$suggestions[] = ['value' => $row->person_id, 'label' => $row->company_name];
			}
		}

		//only return $limit suggestions
		if(count($suggestions) > $limit)
		{
			$suggestions = array_slice($suggestions, 0, $limit);
		}

		return $suggestions;
	}

 	/**
	 * Gets rows
	 */
	public function get_found_rows(string $search): int
	{
		return $this->search($search, 0, 0, 'last_name', 'asc', true);
	}

	/**
	 * Performs a search on customers
	 */
	public function search(string $search, ?int $rows = 0, ?int $limit_from = 0, ?string $sort = 'last_name', ?string $order = 'asc', ?bool $count_only = false)
	{
		// Set default values
		if($rows == null) $rows = 0;
		if($limit_from == null) $limit_from = 0;
		if($sort == null) $sort = 'last_name';
		if($order == null) $order = 'asc';
		if($count_only == null) $count_only = false;

		$builder = $this->db->table('customers AS customers');

		// get_found_rows case
		if($count_only)
		{
			$builder->select('COUNT(customers.person_id) as count');
		}

		$builder->join('people', 'customers.person_id = people.person_id');
		$builder->groupStart();
			$builder->like('first_name', $search);
			$builder->orLike('last_name', $search);
			$builder->orLike('email', $search);
			$builder->orLike('phone_number', $search);
			$builder->orLike('account_number', $search);
			$builder->orLike('company_name', $search);
			$builder->orLike('CONCAT(first_name, " ", last_name)', $search);	//TODO: Duplicated code.
		$builder->groupEnd();
		$builder->where('deleted', 0);

		// get_found_rows case
		if($count_only)
		{
			return $builder->get()->getRow()->count;
		}

		$builder->orderBy($sort, $order);

		if($rows > 0)
		{
			$builder->limit($rows, $limit_from);
		}

		return $builder->get();
	}
}
