<?php
require_once __DIR__ . '/../config/Database.php';

class Transaction {
    private $db;
    private $conn;
    
    public function __construct() {
        $this->db = new Database();
        $this->conn = $this->db->getConnection();
    }
    
    public function createTransaction($data) {
        $this->conn->begin_transaction();
        
        try {
            // Insert main transaction
            $query = "INSERT INTO transactions (id, transaction_date, customer_id, cashier_id, 
                      total_amount, discount_amount, final_amount, payment_method, status, notes) 
                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->conn->prepare($query);
            $stmt->bind_param("ssisdddsss", $data['id'], $data['date'], $data['customer_id'],
                              $data['cashier_id'], $data['total_amount'], $data['discount_amount'],
                              $data['final_amount'], $data['payment_method'], $data['status'], $data['notes']);
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to create transaction");
            }
            
            // Insert transaction items
            foreach ($data['items'] as $item) {
                $itemQuery = "INSERT INTO transaction_items (transaction_id, product_id, variation_id, 
                              quantity, unit_price, discount_amount, line_total) 
                              VALUES (?, ?, ?, ?, ?, ?, ?)";
                $itemStmt = $this->conn->prepare($itemQuery);
                $itemStmt->bind_param("sisiddd", $data['id'], $item['product_id'], $item['variation_id'],
                                      $item['quantity'], $item['unit_price'], $item['discount_amount'],
                                      $item['line_total']);
                
                if (!$itemStmt->execute()) {
                    throw new Exception("Failed to insert transaction item");
                }
            }
            
            // Update customer loyalty points if applicable
            if ($data['customer_id']) {
                $pointsEarned = floor($data['final_amount'] / 100);
                $this->updateCustomerPoints($data['customer_id'], $pointsEarned);
                
                // Update customer last visit and total spent
                $updateQuery = "UPDATE customers SET last_visit = NOW(), total_spent = total_spent + ? WHERE id = ?";
                $updateStmt = $this->conn->prepare($updateQuery);
                $updateStmt->bind_param("di", $data['final_amount'], $data['customer_id']);
                $updateStmt->execute();
            }
            
            // Update stock for each item
            foreach ($data['items'] as $item) {
                if (!empty($item['variation_id'])) {
                    $stockQuery = "UPDATE products_variations SET stock = stock - ? WHERE id = ?";
                    $stockStmt = $this->conn->prepare($stockQuery);
                    $stockStmt->bind_param("is", $item['quantity'], $item['variation_id']);
                    $stockStmt->execute();
                }
            }
            
            $this->conn->commit();
            return ['success' => true, 'transaction_id' => $data['id']];
        } catch (Exception $e) {
            $this->conn->rollback();
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
    
    public function getTransaction($transactionId) {
        $query = "SELECT * FROM transactions WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("s", $transactionId);
        $stmt->execute();
        
        $transaction = $stmt->get_result()->fetch_assoc();
        if ($transaction) {
            $transaction['items'] = $this->getTransactionItems($transactionId);
        }
        return $transaction;
    }
    
    public function getTransactionItems($transactionId) {
        $query = "SELECT ti.*, p.name as product_name, pv.sku, pv.barcode 
                  FROM transaction_items ti 
                  LEFT JOIN products p ON ti.product_id = p.id 
                  LEFT JOIN products_variations pv ON ti.variation_id = pv.id 
                  WHERE ti.transaction_id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("s", $transactionId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getAllTransactions($filters = []) {
        $query = "SELECT t.*, c.name as customer_name FROM transactions t 
                  LEFT JOIN customers c ON t.customer_id = c.id WHERE 1=1";
        
        if (isset($filters['start_date'])) {
            $query .= " AND DATE(t.transaction_date) >= '" . $this->db->escape($filters['start_date']) . "'";
        }
        if (isset($filters['end_date'])) {
            $query .= " AND DATE(t.transaction_date) <= '" . $this->db->escape($filters['end_date']) . "'";
        }
        if (isset($filters['status'])) {
            $query .= " AND t.status = '" . $this->db->escape($filters['status']) . "'";
        }
        if (isset($filters['customer_id'])) {
            $query .= " AND t.customer_id = " . intval($filters['customer_id']);
        }
        
        $query .= " ORDER BY t.transaction_date DESC LIMIT 1000";
        
        $result = $this->conn->query($query);
        return $result->fetch_all(MYSQLI_ASSOC);
    }
    
    private function updateCustomerPoints($customerId, $points) {
        $query = "UPDATE customers SET loyalty_points = loyalty_points + ? WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("ii", $points, $customerId);
        $stmt->execute();
    }
    
    public function getSalesStats($startDate = null, $endDate = null) {
        if (!$startDate) $startDate = date('Y-m-d');
        if (!$endDate) $endDate = date('Y-m-d');
        
        $query = "SELECT 
                    COUNT(*) as total_transactions,
                    SUM(final_amount) as total_sales,
                    AVG(final_amount) as average_sale,
                    COUNT(CASE WHEN status = 'returned' THEN 1 END) as returned_orders
                  FROM transactions 
                  WHERE DATE(transaction_date) BETWEEN ? AND ? AND status != 'cancelled'";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("ss", $startDate, $endDate);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
}
?>
