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

class Transaction_ledger_model extends CI_Model {

	public function get_transaction_ledger_data($customer_id = '', $from_date = '', $to_date = '', $store_id = '') {
		
		try {
			// Simple test query first
			$test_query = $this->db->query("SELECT COUNT(*) as count FROM db_sales WHERE 1=1");
			if (!$test_query) {
				return array('error' => 'Database connection failed');
			}
			
			$store_condition = '';
			if(!empty($store_id)) {
				$store_condition = " AND store_id = " . intval($store_id);
			}
			
			$customer_condition = '';
			if(!empty($customer_id)) {
				$customer_condition = " AND customer_id = " . intval($customer_id);
			}
			
			// Get opening balance for the customer if specified
			$opening_balance = 0;
			if(!empty($customer_id)) {
				$opening_balance = $this->get_opening_balance($customer_id, $from_date, $store_id);
			}
			
			$transactions = array();
			$running_balance = $opening_balance;
			
			// Complete query with all transaction types (UNION)
			$sql = "
			SELECT 
				sales_date as transaction_date,
				customer_id,
				'SALE' as transaction_type,
				sales_code as reference,
				CONCAT('Sale Invoice #', sales_code) as description,
				grand_total as debit_amount,
				0 as credit_amount,
				created_date as sort_date,
				created_time as sort_time
			FROM db_sales 
			WHERE status = 1 $store_condition $customer_condition 
			AND DATE(sales_date) BETWEEN '$from_date' AND '$to_date'
			
			UNION ALL
			
			SELECT 
				payment_date as transaction_date,
				customer_id,
				'PAYMENT' as transaction_type,
				payment_code as reference,
				CONCAT('Payment - ', payment_type, 
					CASE WHEN sales_id IS NOT NULL THEN CONCAT(' for Invoice #', 
						(SELECT sales_code FROM db_sales WHERE id = db_salespayments.sales_id)) 
					ELSE ' (Advance Payment)' END
				) as description,
				0 as debit_amount,
				payment as credit_amount,
				created_date as sort_date,
				created_time as sort_time
			FROM db_salespayments 
			WHERE status = 1 $store_condition $customer_condition 
			AND DATE(payment_date) BETWEEN '$from_date' AND '$to_date'
			
			UNION ALL
			
			SELECT 
				return_date as transaction_date,
				customer_id,
				'RETURN' as transaction_type,
				return_code as reference,
				CONCAT('Sales Return #', return_code) as description,
				0 as debit_amount,
				grand_total as credit_amount,
				created_date as sort_date,
				created_time as sort_time
			FROM db_salesreturn 
			WHERE status = 1 $store_condition $customer_condition 
			AND DATE(return_date) BETWEEN '$from_date' AND '$to_date'
			
			ORDER BY transaction_date ASC, sort_date ASC, sort_time ASC
			LIMIT 200
			";
			
			$query = $this->db->query($sql);
			if (!$query) {
				return array('error' => 'Query failed: ' . $this->db->error()['message']);
			}
			
			$transactions = $query->result_array();
			
			// Calculate running balance and format data
			$formatted_transactions = array();
			
			foreach($transactions as $transaction) {
				// Get customer name
				$customer_name = $this->get_customer_name($transaction['customer_id']);
				
				$debit = floatval($transaction['debit_amount']);
				$credit = floatval($transaction['credit_amount']);
				
				// Update running balance (debit increases balance, credit decreases it)
				$running_balance += ($debit - $credit);
				
				$formatted_transactions[] = array(
					'date' => date('d-m-Y', strtotime($transaction['transaction_date'])),
					'customer_id' => $transaction['customer_id'],
					'customer_name' => $customer_name,
					'transaction_type' => $transaction['transaction_type'],
					'reference' => $transaction['reference'],
					'description' => $transaction['description'],
					'debit' => $debit > 0 ? number_format($debit, 2) : '',
					'credit' => $credit > 0 ? number_format($credit, 2) : '',
					'running_balance' => number_format($running_balance, 2),
					'debit_raw' => $debit,
					'credit_raw' => $credit,
					'balance_raw' => $running_balance
				);
			}
			
			// Calculate summary
			$total_debit = array_sum(array_column($formatted_transactions, 'debit_raw'));
			$total_credit = array_sum(array_column($formatted_transactions, 'credit_raw'));
			$closing_balance = $opening_balance + $total_debit - $total_credit;
			
			return array(
				'transactions' => $formatted_transactions,
				'opening_balance' => $opening_balance,
				'closing_balance' => $closing_balance,
				'total_debit' => $total_debit,
				'total_credit' => $total_credit,
				'transaction_count' => count($formatted_transactions)
			);
			
		} catch (Exception $e) {
			return array('error' => 'Model error: ' . $e->getMessage());
		}
		$closing_balance = $running_balance;
		
		return array(
			'opening_balance' => $opening_balance,
			'total_debit' => $total_debit,
			'total_credit' => $total_credit,
			'closing_balance' => $closing_balance,
			'transactions' => $formatted_transactions,
			'transaction_count' => count($formatted_transactions)
		);
	}
	
	private function get_opening_balance($customer_id, $from_date, $store_id) {
		try {
			// Get customer's opening balance
			$customer = $this->db->select('opening_balance')
							   ->where('id', $customer_id)
							   ->where('store_id', $store_id)
							   ->get('db_customers')
							   ->row();
			
			$opening_balance = $customer ? floatval($customer->opening_balance) : 0;
			
			// For now, just return the customer's opening balance
			// We can add prior transactions calculation later if needed
			return $opening_balance;
			
		} catch (Exception $e) {
			return 0;
		}
	}
	
	private function get_customer_name($customer_id) {
		try {
			$customer = $this->db->select('customer_name')
							   ->where('id', $customer_id)
							   ->get('db_customers')
							   ->row();
			
			return $customer ? $customer->customer_name : 'Customer #' . $customer_id;
		} catch (Exception $e) {
			return 'Customer #' . $customer_id;
		}
	}
	
	public function get_customer_summary($customer_id, $store_id) {
		// Get customer details
		$customer = $this->db->select('customer_name, mobile, opening_balance, current_balance, tot_advance, credit_limit, last_transaction_date')
						   ->where('id', $customer_id)
						   ->where('store_id', $store_id)
						   ->get('db_customers')
						   ->row();
		
		if(!$customer) {
			return array('error' => 'Customer not found');
		}
		
		// Get transaction counts and totals
		$sales_summary = $this->db->select('COUNT(*) as count, COALESCE(SUM(grand_total), 0) as total')
								  ->where('customer_id', $customer_id)
								  ->where('store_id', $store_id)
								  ->where('status', 1)
								  ->get('db_sales')
								  ->row();
		
		$payments_summary = $this->db->select('COUNT(*) as count, COALESCE(SUM(payment), 0) as total')
								    ->where('customer_id', $customer_id)
								    ->where('status', 1)
								    ->get('db_salespayments')
								    ->row();
		
		$returns_summary = $this->db->select('COUNT(*) as count, COALESCE(SUM(grand_total), 0) as total')
								   ->where('customer_id', $customer_id)
								   ->where('store_id', $store_id)
								   ->where('status', 1)
								   ->get('db_salesreturn')
								   ->row();
		
		// Get last few transactions
		$recent_transactions = $this->get_transaction_ledger_data($customer_id, '', '', $store_id);
		$last_5_transactions = array_slice($recent_transactions['transactions'], -5, 5);
		
		return array(
			'customer' => array(
				'name' => $customer->customer_name,
				'mobile' => $customer->mobile,
				'opening_balance' => store_number_format($customer->opening_balance),
				'current_balance' => store_number_format($customer->current_balance),
				'advance_balance' => store_number_format($customer->tot_advance),
				'credit_limit' => store_number_format($customer->credit_limit),
				'last_transaction_date' => $customer->last_transaction_date ? show_date($customer->last_transaction_date) : 'No transactions'
			),
			'summary' => array(
				'total_sales' => array(
					'count' => $sales_summary->count,
					'amount' => store_number_format($sales_summary->total)
				),
				'total_payments' => array(
					'count' => $payments_summary->count,
					'amount' => store_number_format($payments_summary->total)
				),
				'total_returns' => array(
					'count' => $returns_summary->count,
					'amount' => store_number_format($returns_summary->total)
				)
			),
			'recent_transactions' => $last_5_transactions
		);
	}
}
