<?php
/**
 * ============================================================================
 * classes/Price.php - Market Price Management Class
 * ============================================================================
 */

namespace App;

use PDO;
use PDOException;

class Price {
    private $db;
    private $table = 'market_prices';
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    /**
     * Get current prices for a product type
     */
    public function getCurrentPrices($productType = null, $location = null) {
        $sql = "SELECT * FROM {$this->table} WHERE 1=1";
        $params = [];
        
        if ($productType) {
            $sql .= " AND product_type = ?";
            $params[] = $productType;
        }
        
        if ($location) {
            $sql .= " AND market_location LIKE ?";
            $params[] = "%{$location}%";
        }
        
        $sql .= " AND price_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
        $sql .= " ORDER BY price_date DESC, market_location";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get price trends for a product
     */
    public function getPriceTrends($productType, $days = 30) {
        $sql = "SELECT price_date, AVG(price) as average_price, MIN(price) as min_price, MAX(price) as max_price
                FROM {$this->table}
                WHERE product_type = ?
                AND price_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
                GROUP BY price_date
                ORDER BY price_date ASC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$productType, $days]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Compare prices across markets
     */
    public function comparePrices($productType) {
        // First, get all markets data
        $sql = "SELECT 
                    market_location as location,
                    price,
                    unit,
                    price_date as recorded_date
                FROM {$this->table}
                WHERE product_type = ?
                AND price_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                ORDER BY price_date DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$productType]);
        $markets = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (empty($markets)) {
            return [];
        }
        
        // Calculate statistics
        $prices = array_column($markets, 'price');
        $unit = $markets[0]['unit'] ?? 'kg';
        
        $result = [
            'min_price' => !empty($prices) ? min($prices) : 0,
            'max_price' => !empty($prices) ? max($prices) : 0,
            'avg_price' => !empty($prices) ? array_sum($prices) / count($prices) : 0,
            'unit' => $unit,
            'markets' => []
        ];
        
        // Group by location and get latest price for each
        $locationPrices = [];
        foreach ($markets as $market) {
            $loc = $market['location'];
            if (!isset($locationPrices[$loc]) || 
                strtotime($market['recorded_date']) > strtotime($locationPrices[$loc]['recorded_date'])) {
                $locationPrices[$loc] = $market;
            }
        }
        
        $result['markets'] = array_values($locationPrices);
        
        return $result;
    }
    
    /**
     * Get all product types with prices
     */
    public function getProductTypes() {
        $sql = "SELECT DISTINCT product_type 
                FROM {$this->table}
                ORDER BY product_type";
        
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get all market locations
     */
    public function getMarketLocations() {
        $sql = "SELECT DISTINCT market_location 
                FROM {$this->table}
                ORDER BY market_location";
        
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
    
    /**
     * Add price entry (admin only)
     */
    public function addPrice($data) {
        try {
            $sql = "INSERT INTO {$this->table} 
                    (product_type, market_location, price, unit, source, price_date)
                    VALUES (?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute([
                $data['product_type'],
                $data['market_location'],
                $data['price'],
                $data['unit'],
                $data['source'] ?? 'Manual Entry',
                $data['price_date'] ?? date('Y-m-d')
            ]);
            
            if ($result) {
                // Check price alerts
                $this->checkPriceAlerts($data['product_type'], $data['price']);
                
                return ['success' => true, 'message' => 'Price added successfully'];
            }
            
            return ['success' => false, 'message' => 'Failed to add price'];
            
        } catch (PDOException $e) {
            error_log("Price addition error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Database error occurred'];
        }
    }
    
    /**
     * Check and trigger price alerts
     */
    private function checkPriceAlerts($productType, $currentPrice) {
        try {
            // Get active alerts for this product
            $sql = "SELECT pa.*, u.phone_number 
                    FROM price_alerts pa
                    JOIN users u ON pa.user_id = u.id
                    WHERE pa.product_type = ? 
                    AND pa.is_active = 1";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$productType]);
            $alerts = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            foreach ($alerts as $alert) {
                $shouldAlert = false;
                
                if ($alert['alert_condition'] === 'above' && $currentPrice >= $alert['target_price']) {
                    $shouldAlert = true;
                } elseif ($alert['alert_condition'] === 'below' && $currentPrice <= $alert['target_price']) {
                    $shouldAlert = true;
                }
                
                if ($shouldAlert) {
                    // Create notification
                    $message = "Price alert: {$productType} is now " . formatCurrency($currentPrice);
                    
                    $notifSql = "INSERT INTO notifications (user_id, title, message, type, icon, link)
                                VALUES (?, ?, ?, 'info', 'tag', '/prices/')";
                    $notifStmt = $this->db->prepare($notifSql);
                    $notifStmt->execute([
                        $alert['user_id'],
                        'Price Alert Triggered',
                        $message
                    ]);
                    
                    // Update last triggered
                    $updateSql = "UPDATE price_alerts SET last_triggered = NOW() WHERE id = ?";
                    $updateStmt = $this->db->prepare($updateSql);
                    $updateStmt->execute([$alert['id']]);
                }
            }
        } catch (PDOException $e) {
            error_log("Price alert check error: " . $e->getMessage());
        }
    }
    
    /**
     * Get user's price alerts
     */
    public function getUserAlerts($userId) {
        $sql = "SELECT * FROM price_alerts 
                WHERE user_id = ? 
                ORDER BY created_at DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Create price alert
     */
    public function createAlert($userId, $productType, $targetPrice, $condition) {
        try {
            $sql = "INSERT INTO price_alerts 
                    (user_id, product_type, target_price, alert_condition, is_active)
                    VALUES (?, ?, ?, ?, 1)";
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute([$userId, $productType, $targetPrice, $condition]);
            
            if ($result) {
                return ['success' => true, 'message' => 'Price alert created successfully'];
            }
            
            return ['success' => false, 'message' => 'Failed to create alert'];
            
        } catch (PDOException $e) {
            error_log("Alert creation error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Database error occurred'];
        }
    }
    
    /**
     * Delete price alert
     */
    public function deleteAlert($alertId, $userId) {
        $sql = "DELETE FROM price_alerts WHERE id = ? AND user_id = ?";
        $stmt = $this->db->prepare($sql);
        
        if ($stmt->execute([$alertId, $userId])) {
            return ['success' => true, 'message' => 'Alert deleted successfully'];
        }
        
        return ['success' => false, 'message' => 'Failed to delete alert'];
    }
    
    /**
     * Toggle alert status
     */
    public function toggleAlert($alertId, $userId) {
        $sql = "UPDATE price_alerts 
                SET is_active = NOT is_active 
                WHERE id = ? AND user_id = ?";
        
        $stmt = $this->db->prepare($sql);
        
        if ($stmt->execute([$alertId, $userId])) {
            return ['success' => true, 'message' => 'Alert status updated'];
        }
        
        return ['success' => false, 'message' => 'Failed to update alert'];
    }
    
    /**
     * Get price statistics
     */
    public function getStatistics($productType = null) {
        $sql = "SELECT 
                    product_type,
                    COUNT(*) as total_entries,
                    AVG(price) as average_price,
                    MIN(price) as lowest_price,
                    MAX(price) as highest_price,
                    MAX(price_date) as last_update
                FROM {$this->table}
                WHERE price_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
        
        if ($productType) {
            $sql .= " AND product_type = ?";
        }
        
        $sql .= " GROUP BY product_type ORDER BY product_type";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($productType ? [$productType] : []);
        
        return $productType ? $stmt->fetch(PDO::FETCH_ASSOC) : $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>