<?php
/**
 * Medicine Model
 * Handle medicine database operations
 */

namespace Models;

use Core\DB;

class Medicine {
    private $db;
    
    public function __construct() {
        $this->db = DB::getInstance();
    }
    
    /**
     * Search medicines by name
     */
    public function search($query, $limit = 10) {
        return $this->db->fetchAll(
            "SELECT * FROM medicines WHERE name LIKE ? ORDER BY name ASC LIMIT {$limit}",
            ["%{$query}%"]
        );
    }
    
    /**
     * Find medicine by exact name
     */
    public function findByName($name) {
        return $this->db->fetchOne(
            "SELECT * FROM medicines WHERE name = ?",
            [$name]
        );
    }
    
    /**
     * Find medicine by ID
     */
    public function findById($id) {
        return $this->db->fetchOne(
            "SELECT * FROM medicines WHERE id = ?",
            [$id]
        );
    }
    
    /**
     * Create new medicine
     */
    public function create($name, $atcCode = null) {
        $medicine = [
            'id' => $this->generateUuid(),
            'name' => $name,
            'atc_code' => $atcCode
        ];
        
        $this->db->insert('medicines', $medicine);
        
        return $medicine['id'];
    }
    
    /**
     * Get all medicines
     */
    public function getAll() {
        return $this->db->fetchAll("SELECT * FROM medicines ORDER BY name ASC");
    }
    
    /**
     * Get medicines with stock information for a pharmacy
     */
    public function getWithStockForPharmacy($pharmacyId) {
        return $this->db->fetchAll("
            SELECT m.*, ps.stock, ps.unit_price
            FROM medicines m
            LEFT JOIN pharmacy_stock ps ON m.id = ps.medicine_id AND ps.pharmacy_id = ?
            ORDER BY m.name ASC
        ", [$pharmacyId]);
    }
    
    /**
     * Get popular medicines (most ordered)
     */
    public function getPopular($limit = 10) {
        return $this->db->fetchAll("
            SELECT m.*, COUNT(o.id) as order_count
            FROM medicines m
            INNER JOIN orders o ON m.id = o.medicine_id
            GROUP BY m.id
            ORDER BY order_count DESC
            LIMIT {$limit}
        ");
    }
    
    /**
     * Find or create medicine by name
     */
    public function findOrCreate($name, $atcCode = null) {
        $medicine = $this->findByName($name);
        
        if (!$medicine) {
            $id = $this->create($name, $atcCode);
            $medicine = $this->findById($id);
        }
        
        return $medicine;
    }
    
    /**
     * Update medicine
     */
    public function update($id, $data) {
        return $this->db->update('medicines', $data, 'id = ?', [$id]);
    }
    
    /**
     * Delete medicine
     */
    public function delete($id) {
        // Check if medicine is used in any orders or stock
        $inUse = $this->db->count('orders', 'medicine_id = ?', [$id]) > 0 ||
                 $this->db->count('pharmacy_stock', 'medicine_id = ?', [$id]) > 0;
        
        if ($inUse) {
            throw new \Exception('Cannot delete medicine that is in use');
        }
        
        return $this->db->delete('medicines', 'id = ?', [$id]);
    }
    
    /**
     * Get medicine statistics
     */
    public function getStats() {
        $totalMedicines = $this->db->count('medicines');
        
        $mostOrdered = $this->db->fetchOne("
            SELECT m.name, COUNT(o.id) as order_count
            FROM medicines m
            INNER JOIN orders o ON m.id = o.medicine_id
            GROUP BY m.id
            ORDER BY order_count DESC
            LIMIT 1
        ");
        
        $averagePrice = $this->db->fetchOne("
            SELECT AVG(ps.unit_price) as avg_price
            FROM pharmacy_stock ps
            WHERE ps.unit_price > 0
        ");
        
        return [
            'total_medicines' => $totalMedicines,
            'most_ordered' => $mostOrdered['name'] ?? 'N/A',
            'most_ordered_count' => $mostOrdered['order_count'] ?? 0,
            'average_price' => round($averagePrice['avg_price'] ?? 0)
        ];
    }
    
    /**
     * Generate UUID v4
     */
    private function generateUuid() {
        return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0, 0xffff), mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0x0fff) | 0x4000,
            mt_rand(0, 0x3fff) | 0x8000,
            mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
        );
    }
}
?>
