<?php

class Cierre_model extends CI_Model
{
	private $table = 'phppos_cierres';
	private $table_detalle = 'phppos_cierre_detalle';

	public function get_recent($limit = 50)
	{
		$this->db->select('c.*, CONCAT(p.first_name, " ", p.last_name) as caja_nombre');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		if ($this->db->field_exists('deleted', $this->table))
		{
			$this->db->where('c.deleted', 0);
		}
		$this->db->order_by('c.fecha desc, c.id desc');
		$this->db->limit((int)$limit);
		return $this->db->get()->result();
	}

	public function get_filtered($limit, $q = '', $status = 'all', $cashier_id = 0)
	{
		$limit = (int)$limit;
		$q = trim((string)$q);
		$status = strtolower(trim((string)$status));
		$cashier_id = (int)$cashier_id;

		$this->db->select('c.*, CONCAT(p.first_name, " ", p.last_name) as caja_nombre');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		if ($this->db->field_exists('deleted', $this->table))
		{
			$this->db->where('c.deleted', 0);
		}

		if ($status !== '' && $status !== 'all')
		{
			$map = array(
				'pendiente' => 'Pendiente',
				'completado' => 'Completado',
				// Estado real en DB (enum): CompletadoRevisado
				'revisado' => 'CompletadoRevisado',
				'revisado_y_completado' => 'CompletadoRevisado',
			);
			if (isset($map[$status]))
			{
				$this->db->where('c.estado', $map[$status]);
			}
		}

		// Optional filter by cashier/person id
		if ($cashier_id > 0)
		{
			$this->db->where('c.caja_person_id', $cashier_id);
		}

		if ($q !== '')
		{
			$this->db->group_start();
				$this->db->like('c.bolsa_manual', $q);
				$this->db->or_like('p.first_name', $q);
				$this->db->or_like('p.last_name', $q);
				$this->db->or_like('c.fecha', $q);
				if (is_numeric($q))
				{
					$this->db->or_where('c.id', (int)$q);
				}
			$this->db->group_end();
		}

		$this->db->order_by('c.fecha desc, c.id desc');
		$this->db->limit($limit);
		return $this->db->get()->result();
	}

	/**
	 * Cashier dropdown options for the list filter.
	 * We return distinct cashiers that exist in closures (not deleted).
	 */
	public function get_cashiers_for_filter()
	{
		$this->db->select('c.caja_person_id as person_id, CONCAT(p.first_name, " ", p.last_name) as name');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		if ($this->db->field_exists('deleted', $this->table))
		{
			$this->db->where('c.deleted', 0);
		}
		$this->db->group_by('c.caja_person_id');
		$this->db->order_by('name asc');
		return $this->db->get()->result();
	}

	public function get($id)
	{
		$this->db->select('c.*, CONCAT(p.first_name, " ", p.last_name) as caja_nombre');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		$this->db->where('c.id', (int)$id);
		return $this->db->get()->row();
	}

	public function get_detalle($cierre_id)
	{
		$this->db->from($this->table_detalle);
		$this->db->where('cierre_id', (int)$cierre_id);
		$this->db->order_by('id asc');
		return $this->db->get()->result();
	}

	/**
	 * Detalle para la vista (solo lectura):
	 *  - Mostrar solo filas con movimiento (sistema o contado != 0)
	 *  - Siempre incluir filas adicionales (es_adicional = 1)
	 */
	public function get_detalle_for_view($cierre_id)
	{
		$this->db->from($this->table_detalle);
		$this->db->where('cierre_id', (int)$cierre_id);
		$this->db->group_start();
			$this->db->where('monto_sistema_usd !=', 0);
			$this->db->or_where('monto_contado_usd !=', 0);
			$this->db->or_where('es_adicional', 1);
		$this->db->group_end();
		$this->db->order_by('es_adicional asc, id asc');
		return $this->db->get()->result();
	}

	/**
	 * Detalle para el Paso 2:
	 *  - Mostrar solo metodos que existieron ese dia (monto_sistema_usd > 0)
	 *  - Siempre incluir filas adicionales (es_adicional = 1)
	 */
	public function get_detalle_step2($cierre_id)
	{
		$this->db->from($this->table_detalle);
		$this->db->where('cierre_id', (int)$cierre_id);
		$this->db->group_start();
			$this->db->where('monto_sistema_usd >', 0);
			$this->db->or_where('es_adicional', 1);
		$this->db->group_end();
		$this->db->order_by('es_adicional asc, id asc');
		return $this->db->get()->result();
	}

	/**
	 * Detalle "usado" (para Step 3 y Edit):
	 *  - Mostrar solo filas con movimiento (sistema > 0) o donde el usuario haya contado algo (contado_usd != 0)
	 *  - Siempre incluir filas adicionales (es_adicional = 1)
	 */
	public function get_detalle_used($cierre_id)
	{
		$this->db->from($this->table_detalle);
		$this->db->where('cierre_id', (int)$cierre_id);
		$this->db->group_start();
			$this->db->where('monto_sistema_usd >', 0);
			$this->db->or_where('monto_contado_usd !=', 0);
			$this->db->or_where('es_adicional', 1);
		$this->db->group_end();
		$this->db->order_by('es_adicional asc, id asc');
		return $this->db->get()->result();
	}

	public function create_from_system($fecha, $caja_person_id, $bolsa_manual, $cambio_tasa, $notas, $created_by)
	{
		$fecha = date('Y-m-d', strtotime($fecha));
		$caja_person_id = (int)$caja_person_id;
		$bolsa_manual = trim($bolsa_manual);
		if (!$fecha || !$caja_person_id || $bolsa_manual === '')
		{
			return false;
		}

		// Prevent duplicates
		$existing = $this->db->get_where($this->table, array(
			'fecha' => $fecha,
			'caja_person_id' => $caja_person_id,
			'bolsa_manual' => $bolsa_manual,
		))->row();
		if ($existing)
		{
			return (int)$existing->id;
		}

		$insert = array(
			'fecha' => $fecha,
			'caja_person_id' => $caja_person_id,
			'bolsa_manual' => $bolsa_manual,
			'cambio_tasa' => $cambio_tasa > 0 ? $cambio_tasa : 1,
			'notas' => $notas,
			'estado' => 'Pendiente',
			'created_by' => (int)$created_by,
			'total_sistema_usd' => 0,
			'total_contado_usd' => 0,
			'diferencia_total_usd' => 0,
			'completo' => 0,
		);

		$this->db->insert($this->table, $insert);
		$cierre_id = (int)$this->db->insert_id();
		if (!$cierre_id)
		{
			return false;
		}

		// Build detail rows from system totals by payment type
		$system_by_payment_rows = $this->get_system_payments_by_type($fecha, $caja_person_id);
		$system_map = array();
		foreach ($system_by_payment_rows as $row)
		{
			$system_map[$row['payment_type']] = (float)$row['amount_usd'];
		}

		// Always create detail rows from active payment configuration so the form never renders empty
		$active_pagos = $this->db
			->where('activo', 1)
			->order_by('orden asc, id asc')
			->get('phppos_cierre_config_pago')
			->result();

		foreach ($active_pagos as $pago)
		{
			$payment_name = $pago->nombre;
			$monto_sistema = isset($system_map[$payment_name]) ? (float)$system_map[$payment_name] : 0;
			$this->db->insert($this->table_detalle, array(
				'cierre_id' => $cierre_id,
				'tipo_pago' => $payment_name,
				'moneda' => $pago->moneda_base ? $pago->moneda_base : 'USD',
				'monto_sistema_usd' => $monto_sistema,
				'monto_contado' => 0,
				'monto_contado_usd' => 0,
				'diferencia_usd' => 0,
				'es_adicional' => 0,
			));
		}

		// Sync header totals
		$this->recalc_totals($cierre_id);

		return $cierre_id;
	}

	public function update_contado($cierre_id, $detalle_post, $cambio_tasa, $notas)
	{
		$cierre_id = (int)$cierre_id;
		$cambio_tasa = $cambio_tasa > 0 ? $cambio_tasa : 1;

		// Update header fields
		$this->db->where('id', $cierre_id);
		$this->db->update($this->table, array(
			'cambio_tasa' => $cambio_tasa,
			'notas' => $notas,
		));

		foreach ($detalle_post as $detalle_id => $vals)
		{
			$detalle_id = (int)$detalle_id;
			$monto_contado = isset($vals['monto_contado']) ? (float)$vals['monto_contado'] : 0;

			// Lock currency for existing rows. Currency can only be changed on additional/custom rows.
			$row = $this->db->get_where($this->table_detalle, array('id' => $detalle_id, 'cierre_id' => $cierre_id))->row();
			if (!$row)
			{
				continue;
			}
			$moneda = $row->moneda;
			if ((int)$row->es_adicional === 1)
			{
				$moneda = isset($vals['moneda']) && in_array($vals['moneda'], array('USD','BS')) ? $vals['moneda'] : $moneda;
			}

			// Convert to USD if needed
			$monto_contado_usd = $moneda === 'BS' ? ($cambio_tasa > 0 ? $monto_contado / $cambio_tasa : 0) : $monto_contado;

			$this->db->where('id', $detalle_id);
			$this->db->where('cierre_id', $cierre_id);
			$this->db->update($this->table_detalle, array(
				'moneda' => $moneda,
				'monto_contado' => $monto_contado,
				'monto_contado_usd' => $monto_contado_usd,
			));
		}

		$this->recalc_totals($cierre_id);
		return true;
	}

	/**
	 * Create additional/custom payment rows added by the user in Step 2.
	 * Each row: ['tipo_pago' => string, 'moneda' => 'USD'|'BS', 'monto_contado' => float]
	 */
	public function add_custom_rows($cierre_id, $custom_rows, $cambio_tasa)
	{
		$cierre_id = (int)$cierre_id;
		$cambio_tasa = $cambio_tasa > 0 ? $cambio_tasa : 1;
		if (!is_array($custom_rows))
		{
			return;
		}

		foreach ($custom_rows as $row)
		{
			$tipo_pago = isset($row['tipo_pago']) ? trim($row['tipo_pago']) : '';
			if ($tipo_pago === '')
			{
				continue;
			}
			$moneda = isset($row['moneda']) && in_array($row['moneda'], array('USD','BS')) ? $row['moneda'] : 'USD';
			$monto_contado = isset($row['monto_contado']) ? (float)$row['monto_contado'] : 0;
			$monto_contado_usd = $moneda === 'BS' ? ($cambio_tasa > 0 ? $monto_contado / $cambio_tasa : 0) : $monto_contado;

			$this->db->insert($this->table_detalle, array(
				'cierre_id' => $cierre_id,
				'tipo_pago' => $tipo_pago,
				'moneda' => $moneda,
				'monto_sistema_usd' => 0,
				'monto_contado' => $monto_contado,
				'monto_contado_usd' => $monto_contado_usd,
				'diferencia_usd' => $monto_contado_usd,
				'es_adicional' => 1,
			));
		}
	}

	public function mark_completed_with_margin($cierre_id, $employee_id, $within_margin)
	{
		$cierre_id = (int)$cierre_id;
		$employee_id = (int)$employee_id;

		$this->recalc_totals($cierre_id);

		$this->db->where('id', $cierre_id);
		return $this->db->update($this->table, array(
			// Business rule: if outside margin, keep it as pending
			'estado' => $within_margin ? 'Completado' : 'Pendiente',
			'completo' => $within_margin ? 1 : 0,
			'completed_by' => $employee_id,
			'completed_at' => date('Y-m-d H:i:s'),
		));
	}

	public function mark_reviewed_completed($cierre_id, $employee_id)
	{
		$cierre_id = (int)$cierre_id;
		$employee_id = (int)$employee_id;
		$this->recalc_totals($cierre_id);
		$this->db->where('id', $cierre_id);
		return $this->db->update($this->table, array(
			'estado' => 'Revisado y completado',
			'completo' => 1,
			'reviewed_by' => $employee_id,
			'reviewed_at' => date('Y-m-d H:i:s'),
		));
	}

	public function delete_cierre($cierre_id)
	{
		$cierre_id = (int)$cierre_id;
		// Soft delete if column exists; otherwise hard delete
		if ($this->db->field_exists('deleted', $this->table))
		{
			$this->db->where('id', $cierre_id);
			return $this->db->update($this->table, array('deleted' => 1));
		}
		$this->db->where('cierre_id', $cierre_id);
		$this->db->delete($this->table_detalle);
		$this->db->where('id', $cierre_id);
		return $this->db->delete($this->table);
	}

	private function recalc_totals($cierre_id)
	{
		$cierre_id = (int)$cierre_id;
		$rows = $this->get_detalle($cierre_id);
		$total_sistema = 0;
		$total_contado = 0;
		foreach ($rows as $r)
		{
			$total_sistema += (float)$r->monto_sistema_usd;
			$total_contado += (float)$r->monto_contado_usd;

			$diff = (float)$r->monto_contado_usd - (float)$r->monto_sistema_usd;
			$this->db->where('id', (int)$r->id);
			$this->db->update($this->table_detalle, array(
				'diferencia_usd' => $diff,
			));
		}

		$this->db->where('id', $cierre_id);
		$this->db->update($this->table, array(
			'total_sistema_usd' => $total_sistema,
			'total_contado_usd' => $total_contado,
			'diferencia_total_usd' => $total_contado - $total_sistema,
		));
	}

	private function get_system_payments_by_type($fecha, $employee_id)
	{
		$fecha = date('Y-m-d', strtotime($fecha));
		$employee_id = (int)$employee_id;

		$sql = "
			SELECT sp.payment_type, SUM(sp.payment_amount) as amount
			FROM phppos_sales_payments sp
			JOIN phppos_sales s ON s.sale_id = sp.sale_id
			WHERE DATE(s.sale_time) = ?
				AND s.deleted = 0
				AND s.suspended = 0
				AND s.employee_id = ?
			GROUP BY sp.payment_type
			ORDER BY sp.payment_type
		";

		$q = $this->db->query($sql, array($fecha, $employee_id));
		$out = array();
		foreach ($q->result() as $r)
		{
			$out[] = array(
				'payment_type' => $r->payment_type,
				'amount_usd' => (float)$r->amount,
			);
		}
		return $out;
	}


	/* ============================
	 * Estadísticas
	 * ============================ */

	private function _apply_stats_filters($date_start, $date_end, $cashier_id)
	{
		if ($this->db->field_exists('deleted', $this->table))
		{
			$this->db->where('c.deleted', 0);
		}

		if ($cashier_id > 0)
		{
			$this->db->where('c.caja_person_id', (int)$cashier_id);
		}

		if ($date_start)
		{
			$this->db->where('c.fecha >=', $date_start);
		}
		if ($date_end)
		{
			$this->db->where('c.fecha <=', $date_end);
		}
	}

	public function stats_kpis($date_start, $date_end, $cashier_id, $margen_error)
	{
		$margen_error = (float)$margen_error;

		$this->db->select('COUNT(*) as total_cierres,
			SUM(c.diferencia_total_usd) as total_diff,
			AVG(c.diferencia_total_usd) as avg_diff,
			SUM(CASE WHEN ABS(c.diferencia_total_usd) > '.$this->db->escape_str($margen_error).' THEN 1 ELSE 0 END) as fuera_margen');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		$this->_apply_stats_filters($date_start, $date_end, (int)$cashier_id);

		$row = $this->db->get()->row();
		if (!$row)
		{
			return array('total_cierres'=>0,'total_diff'=>0,'avg_diff'=>0,'fuera_margen'=>0);
		}
		return array(
			'total_cierres' => (int)$row->total_cierres,
			'total_diff' => (float)$row->total_diff,
			'avg_diff' => (float)$row->avg_diff,
			'fuera_margen' => (int)$row->fuera_margen,
		);
	}

	public function stats_summary_by_cashier($date_start, $date_end, $cashier_id, $margen_error)
	{
		$margen_error = (float)$margen_error;

		$this->db->select('c.caja_person_id, CONCAT(p.first_name, " ", p.last_name) as caja_nombre,
			COUNT(*) as cierres,
			SUM(c.total_sistema_usd) as total_sistema_usd,
			SUM(c.total_contado_usd) as total_contado_usd,
			SUM(c.diferencia_total_usd) as diferencia_total_usd,
			SUM(CASE WHEN ABS(c.diferencia_total_usd) > '.$this->db->escape_str($margen_error).' THEN 1 ELSE 0 END) as fuera_margen');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		$this->_apply_stats_filters($date_start, $date_end, (int)$cashier_id);
		$this->db->group_by('c.caja_person_id');
		$this->db->order_by('diferencia_total_usd desc');
		return $this->db->get()->result();
	}

	public function stats_ranking_out_of_margin($date_start, $date_end, $cashier_id, $margen_error, $limit = 10)
	{
		$margen_error = (float)$margen_error;
		$limit = (int)$limit;

		$this->db->select('c.*, CONCAT(p.first_name, " ", p.last_name) as caja_nombre, ABS(c.diferencia_total_usd) as abs_diff');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		$this->_apply_stats_filters($date_start, $date_end, (int)$cashier_id);
		$this->db->where('ABS(c.diferencia_total_usd) >', $margen_error);
		$this->db->order_by('abs_diff desc');
		$this->db->limit($limit);
		return $this->db->get()->result();
	}

	public function stats_summary_by_status($date_start, $date_end, $cashier_id)
	{
		$this->db->select('c.estado, COUNT(*) as qty');
		$this->db->from($this->table.' c');
		$this->_apply_stats_filters($date_start, $date_end, (int)$cashier_id);
		$this->db->group_by('c.estado');
		return $this->db->get()->result();
	}

	public function stats_error_by_payment_type($date_start, $date_end, $cashier_id)
	{
		// Sum of absolute differences by payment type
		$this->db->select('d.tipo_pago, SUM(ABS(d.diferencia_usd)) as abs_error');
		$this->db->from($this->table_detalle.' d');
		$this->db->join($this->table.' c', 'c.id = d.cierre_id', 'inner');
		if ($this->db->field_exists('deleted', $this->table))
		{
			$this->db->where('c.deleted', 0);
		}
		if ((int)$cashier_id > 0)
		{
			$this->db->where('c.caja_person_id', (int)$cashier_id);
		}
		if ($date_start)
		{
			$this->db->where('c.fecha >=', $date_start);
		}
		if ($date_end)
		{
			$this->db->where('c.fecha <=', $date_end);
		}
		$this->db->group_by('d.tipo_pago');
		$this->db->order_by('abs_error desc');
		$rows = $this->db->get()->result();

		$total = 0.0;
		foreach ($rows as $r)
		{
			$total += (float)$r->abs_error;
		}

		$out = array();
		foreach ($rows as $r)
		{
			$abs_error = (float)$r->abs_error;
			$out[] = (object)array(
				'tipo_pago' => $r->tipo_pago,
				'abs_error' => $abs_error,
				'percent' => $total > 0 ? ($abs_error / $total) * 100 : 0,
			);
		}
		return $out;
	}

	public function stats_export_rows($date_start, $date_end, $cashier_id, $margen_error)
	{
		$margen_error = (float)$margen_error;

		$this->db->select('c.*, CONCAT(p.first_name, " ", p.last_name) as caja_nombre,
			(CASE WHEN ABS(c.diferencia_total_usd) > '.$this->db->escape_str($margen_error).' THEN 1 ELSE 0 END) as fuera_margen');
		$this->db->from($this->table.' c');
		$this->db->join('phppos_people p', 'p.person_id = c.caja_person_id', 'left');
		$this->_apply_stats_filters($date_start, $date_end, (int)$cashier_id);
		$this->db->order_by('c.fecha desc, c.id desc');
		return $this->db->get()->result();
	}


}
