<?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');
		$this->db->order_by('c.fecha desc, c.id desc');
		$this->db->limit((int)$limit);
		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();
	}

	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 = $this->get_system_payments_by_type($fecha, $caja_person_id);
		foreach ($system_by_payment as $row)
		{
			$this->db->insert($this->table_detalle, array(
				'cierre_id' => $cierre_id,
				'tipo_pago' => $row['payment_type'],
				'moneda' => 'USD',
				'monto_sistema_usd' => $row['amount_usd'],
				'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;
			$moneda = isset($vals['moneda']) && in_array($vals['moneda'], array('USD','BS')) ? $vals['moneda'] : 'USD';

			// 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;
	}

	public function mark_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' => 'Completado',
			'completo' => 1,
			'completed_by' => $employee_id,
			'completed_at' => date('Y-m-d H:i:s'),
		));
	}

	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;
	}
}
