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

class Cash_register_model extends CI_Model {

	/**
	 * Check if table exists (for installations that haven't run migration)
	 */
	public function table_exists() {
		return $this->db->table_exists('db_cash_register_sessions');
	}

	/**
	 * Get current open session for this user in this store
	 */
	public function get_open_session($store_id, $user_id) {
		if (!$this->table_exists()) {
			return null;
		}
		$this->db->where('store_id', (int)$store_id);
		$this->db->where('user_id', (int)$user_id);
		$this->db->where('status', 'open');
		return $this->db->get('db_cash_register_sessions')->row();
	}

	/**
	 * Open a new cash register session.
	 * If account_id is provided and opening_amount > 0, posts a CASH REGISTER OPENING transaction to credit that account.
	 *
	 * @param int    $store_id
	 * @param int    $user_id
	 * @param float  $opening_amount
	 * @param string $opening_note
	 * @param int|null $currency_id
	 * @param int|null $account_id  Cash account to credit with opening amount (optional; if set, balance is updated)
	 * @param float|null $drawer_total_at_open  Total cash in drawer at start (for expected); if null, opening_amount is used
	 */
	public function open_session($store_id, $user_id, $opening_amount, $opening_note = '', $currency_id = null, $account_id = null, $drawer_total_at_open = null) {
		if (!$this->table_exists()) {
			return array('success' => false, 'message' => 'Cash register feature is not installed. Run setup/install/cash_register_sessions.sql');
		}
		$open = $this->get_open_session($store_id, $user_id);
		if ($open) {
			return array('success' => false, 'message' => 'You already have an open register session.');
		}
		$amount = (float)$opening_amount;
		$account_id = $account_id ? (int)$account_id : null;
		$can_post = $this->db->field_exists('ref_cash_register_session_id', 'ac_transactions');
		// If user expects cash account to be updated but DB is not ready, fail with clear message
		if ($amount > 0 && $account_id && !$can_post) {
			return array('success' => false, 'message' => 'Cash account cannot be updated: database update required. Please run setup/install/cash_register_account_and_transaction.sql');
		}
		$data = array(
			'store_id'       => (int)$store_id,
			'user_id'        => (int)$user_id,
			'opened_at'      => date('Y-m-d H:i:s'),
			'opening_amount' => $amount,
			'opening_note'   => $opening_note,
			'status'        => 'open',
			'currency_id'    => $currency_id ? (int)$currency_id : null
		);
		if ($account_id && $this->db->field_exists('account_id', 'db_cash_register_sessions')) {
			$data['account_id'] = $account_id;
		}
		if ($this->db->field_exists('drawer_total_at_open', 'db_cash_register_sessions')) {
			$data['drawer_total_at_open'] = ($drawer_total_at_open !== null && $drawer_total_at_open !== '') ? (float)$drawer_total_at_open : null;
		}
		$this->db->insert('db_cash_register_sessions', $data);
		if (!$this->db->affected_rows()) {
			return array('success' => false, 'message' => 'Failed to open register.');
		}
		$session_id = $this->db->insert_id();

		// Post opening to cash account so balance is updated
		if ($amount > 0 && $account_id && $can_post) {
			$posted = insert_account_transaction(array(
				'transaction_type'       => 'CASH REGISTER OPENING',
				'reference_table_id'     => $session_id,
				'credit_account_id'      => $account_id,
				'credit_amt'             => $amount,
				'debit_account_id'       => null,
				'debit_amt'              => 0,
				'process'                => 'SAVE',
				'note'                   => $opening_note ?: 'Cash register opening',
				'transaction_date'       => date('Y-m-d'),
				'payment_code'           => '',
				'customer_id'            => null,
				'supplier_id'            => null,
			));
			if (!$posted) {
				log_message('error', 'Cash register opening: failed to post transaction for session ' . $session_id);
				return array('success' => false, 'message' => 'Register opened but cash account was not updated. Please edit opening from Cash Register page or check logs.');
			}
		}

		return array('success' => true, 'session_id' => $session_id);
	}

	/**
	 * Get session by id (must be open and belong to this store/user)
	 */
	public function get_session_by_id($session_id, $store_id, $user_id) {
		if (!$this->table_exists()) {
			return null;
		}
		$this->db->where('id', (int)$session_id);
		$this->db->where('store_id', (int)$store_id);
		$this->db->where('user_id', (int)$user_id);
		$this->db->where('status', 'open');
		return $this->db->get('db_cash_register_sessions')->row();
	}

	/**
	 * Update opening amount/account/note/drawer total for current open session.
	 * Reverses existing CASH REGISTER OPENING transaction (if any), updates session row, then posts new transaction.
	 */
	public function update_opening($session_id, $store_id, $user_id, $opening_amount, $account_id, $opening_note = '', $drawer_total_at_open = null) {
		if (!$this->table_exists()) {
			return array('success' => false, 'message' => 'Cash register feature is not installed.');
		}
		$session = $this->get_session_by_id($session_id, $store_id, $user_id);
		if (!$session) {
			return array('success' => false, 'message' => 'Session not found or not open.');
		}
		$amount = (float)$opening_amount;
		$account_id = $account_id ? (int)$account_id : null;

		// Remove existing opening transaction so balance is recalculated
		if ($this->db->field_exists('ref_cash_register_session_id', 'ac_transactions')) {
			$old = $this->db->select('id, credit_account_id')->where('ref_cash_register_session_id', $session_id)->where('transaction_type', 'CASH REGISTER OPENING')->get('ac_transactions')->row();
			if ($old) {
				$this->db->where('id', $old->id)->delete('ac_transactions');
				if ($old->credit_account_id) {
					update_account_balance($old->credit_account_id);
				}
			}
		}

		// Update session row
		$update = array(
			'opening_amount' => $amount,
			'opening_note'   => $opening_note
		);
		if ($this->db->field_exists('account_id', 'db_cash_register_sessions')) {
			$update['account_id'] = $account_id;
		}
		if ($this->db->field_exists('drawer_total_at_open', 'db_cash_register_sessions')) {
			$update['drawer_total_at_open'] = ($drawer_total_at_open !== null && $drawer_total_at_open !== '') ? (float)$drawer_total_at_open : null;
		}
		$this->db->where('id', $session_id);
		$this->db->update('db_cash_register_sessions', $update);

		// Post new opening transaction if amount > 0 and account selected
		if ($amount > 0 && $account_id && $this->db->field_exists('ref_cash_register_session_id', 'ac_transactions')) {
			$posted = insert_account_transaction(array(
				'transaction_type'       => 'CASH REGISTER OPENING',
				'reference_table_id'     => $session_id,
				'credit_account_id'      => $account_id,
				'credit_amt'             => $amount,
				'debit_account_id'       => null,
				'debit_amt'              => 0,
				'process'                => 'SAVE',
				'note'                   => $opening_note ?: 'Cash register opening (updated)',
				'transaction_date'       => date('Y-m-d'),
				'payment_code'           => '',
				'customer_id'            => null,
				'supplier_id'            => null,
			));
			if (!$posted) {
				log_message('error', 'Cash register update opening: failed to post transaction for session ' . $session_id);
			}
		}

		return array('success' => true);
	}

	/**
	 * Get total cash received from sales during this register session (for expected closing).
	 * $end_date: optional Y-m-d (e.g. session closed_at date); if null, uses today (for open sessions).
	 */
	public function get_cash_sales_for_session($session, $end_date = null) {
		$store_id = (int)$session->store_id;
		$open_date = date('Y-m-d', strtotime($session->opened_at));
		if ($end_date === null) {
			$end_date = date('Y-m-d');
		}
		$this->db->select('COALESCE(SUM(sp.payment), 0) as total', false);
		$this->db->from('db_salespayments sp');
		$this->db->join('db_sales s', 's.id = sp.sales_id', 'inner');
		$this->db->where('s.store_id', $store_id);
		$this->db->where('LOWER(TRIM(sp.payment_type))', 'cash');
		$this->db->where('s.created_date >=', $open_date);
		$this->db->where('s.created_date <=', $end_date);
		$row = $this->db->get()->row();
		return $row ? (float)$row->total : 0;
	}

	/** Base for expected = total in drawer at start (or opening amount) */
	public function get_drawer_base_for_expected($session) {
		if ($this->db->field_exists('drawer_total_at_open', 'db_cash_register_sessions') && isset($session->drawer_total_at_open) && $session->drawer_total_at_open !== null && $session->drawer_total_at_open !== '') {
			return (float)$session->drawer_total_at_open;
		}
		return (float)$session->opening_amount;
	}

	/**
	 * Close current open session.
	 * Does NOT post to the cash account on close – opening and sales already updated the account.
	 * Closing amount and variance are for reporting only (see variance report).
	 */
	public function close_session($store_id, $user_id, $closing_amount, $closing_note = '') {
		if (!$this->table_exists()) {
			return array('success' => false, 'message' => 'Cash register feature is not installed.');
		}
		$open = $this->get_open_session($store_id, $user_id);
		if (!$open) {
			return array('success' => false, 'message' => 'No open register session found.');
		}
		$closing_amount = (float)$closing_amount;
		$this->db->where('id', $open->id);
		$this->db->update('db_cash_register_sessions', array(
			'closed_at'      => date('Y-m-d H:i:s'),
			'closing_amount' => $closing_amount,
			'closing_note'   => $closing_note,
			'status'         => 'closed'
		));
		if ($this->db->affected_rows() < 0) {
			return array('success' => false, 'message' => 'Failed to close register.');
		}
		return array('success' => true);
	}

	/**
	 * Get closed sessions with expected and variance for report.
	 * Variance = closing_amount - expected; negative = short, positive = over.
	 */
	public function get_closed_sessions_with_variance($store_id, $from_date = null, $to_date = null) {
		if (!$this->table_exists()) {
			return array();
		}
		$this->db->select('s.id, s.opened_at, s.closed_at, s.opening_amount, s.drawer_total_at_open, s.closing_amount, s.closing_note, s.user_id');
		$this->db->from('db_cash_register_sessions s');
		$this->db->where('s.store_id', (int)$store_id);
		$this->db->where('s.status', 'closed');
		if ($from_date) {
			$this->db->where('DATE(s.closed_at) >=', $from_date);
		}
		if ($to_date) {
			$this->db->where('DATE(s.closed_at) <=', $to_date);
		}
		$this->db->order_by('s.closed_at', 'DESC');
		$rows = $this->db->get()->result();
		$out = array();
		foreach ($rows as $s) {
			$end_date = $s->closed_at ? date('Y-m-d', strtotime($s->closed_at)) : null;
			$cash_sales = $this->get_cash_sales_for_session($s, $end_date);
			$base = $this->get_drawer_base_for_expected($s);
			$expected = $base + $cash_sales;
			$variance = (float)$s->closing_amount - $expected;
			$out[] = (object)array(
				'id'             => $s->id,
				'opened_at'      => $s->opened_at,
				'closed_at'      => $s->closed_at,
				'opening_amount' => (float)$s->opening_amount,
				'drawer_base'    => $base,
				'cash_from_sales'=> $cash_sales,
				'expected'       => $expected,
				'closing_amount' => (float)$s->closing_amount,
				'variance'        => $variance,
				'closing_note'   => $s->closing_note,
				'user_id'        => $s->user_id,
			);
		}
		return $out;
	}
}
