<?php
require_once __DIR__ . '/config/database.php';
use App\Config\Database;

try {
    $db = Database::getInstance()->getConnection();
    
    // Create units_of_measure table
    $db->exec("
        CREATE TABLE IF NOT EXISTS units_of_measure (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
    
    // Insert some default units
    $defaultUnits = ['Bottle', 'Card', 'Pack', 'Vial', 'Strip', 'Box', 'Tablet', 'Capsule', 'Tube', 'Ampoule'];
    $stmt = $db->prepare("INSERT IGNORE INTO units_of_measure (name) VALUES (?)");
    foreach ($defaultUnits as $unit) {
        $stmt->execute([$unit]);
    }
    
    // Add uom_id and volume to medicines table
    $columns = $db->query("SHOW COLUMNS FROM medicines")->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('uom_id', $columns)) {
        $db->exec("ALTER TABLE medicines ADD COLUMN uom_id INT NULL");
        $db->exec("ALTER TABLE medicines ADD CONSTRAINT fk_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id) ON DELETE SET NULL");
    }
    
    if (!in_array('volume', $columns)) {
        $db->exec("ALTER TABLE medicines ADD COLUMN volume DECIMAL(10,2) NULL");
    }
    
    echo "Migration completed successfully.\n";
} catch (Exception $e) {
    echo "Migration failed: " . $e->getMessage() . "\n";
}
