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

try {
    $db = \App\Config\Database::getInstance()->getConnection();
    
    $sql = "CREATE TABLE IF NOT EXISTS `returned_items` (
        `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `tenant_id` BIGINT UNSIGNED NOT NULL,
        `branch_id` BIGINT UNSIGNED NOT NULL,
        `medicine_id` BIGINT UNSIGNED NOT NULL,
        `batch_number` VARCHAR(100) NOT NULL,
        `quantity` INT NOT NULL,
        `reason` TEXT NOT NULL,
        `returned_by` INT NOT NULL,
        `returned_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
        FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE,
        FOREIGN KEY (`medicine_id`) REFERENCES `medicines`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $db->exec($sql);
    echo "Table returned_items created successfully.";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
