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

try {
    $db = \App\Config\Database::getInstance()->getConnection();
    
    // Disable FK checks to drop tables cleanly
    $db->exec("SET FOREIGN_KEY_CHECKS = 0");
    
    $db->exec("DROP TABLE IF EXISTS `sales_items`");
    $db->exec("DROP TABLE IF EXISTS `purchase_items`");
    $db->exec("DROP TABLE IF EXISTS `batches`");
    $db->exec("DROP TABLE IF EXISTS `inventory_batches`");
    $db->exec("DROP TABLE IF EXISTS `medicines`");
    $db->exec("DROP TABLE IF EXISTS `smtp_settings`");

    $db->exec("SET FOREIGN_KEY_CHECKS = 1");

    // Create medicines table
    $db->exec("CREATE TABLE `medicines` (
        `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `tenant_id` BIGINT UNSIGNED NOT NULL,
        `name` VARCHAR(255) NOT NULL,
        `generic_name` VARCHAR(255) NULL,
        `status` ENUM('active', 'inactive') DEFAULT 'active',
        `reorder_level` INT DEFAULT 10,
        `expiry_alert_days` INT DEFAULT 30,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // Create inventory_batches table
    $db->exec("CREATE TABLE `inventory_batches` (
        `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,
        `supplier` VARCHAR(255) NULL,
        `unit_price` DECIMAL(10, 2) NULL,
        `selling_price` DECIMAL(10, 2) NULL,
        `expiry_date` DATE NOT NULL,
        `quantity` INT NOT NULL DEFAULT 0,
        `status` ENUM('active', 'inactive') DEFAULT 'active',
        `stock_level` ENUM('In-stock', 'low-stock', 'out-of stock') DEFAULT 'In-stock',
        `withdrawal_comment` TEXT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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");

    // Create returned_items table
    $db->exec("CREATE TABLE `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");



    // Create and seed smtp_settings table
    $db->exec("CREATE TABLE `smtp_settings` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `smtp_host` VARCHAR(255) NOT NULL,
        `smtp_port` INT NOT NULL,
        `smtp_username` VARCHAR(255) NOT NULL,
        `smtp_password` VARCHAR(255) NOT NULL,
        `from_email` VARCHAR(255) NULL,
        `from_name` VARCHAR(255) NOT NULL,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $db->exec("INSERT INTO `smtp_settings` (`id`, `smtp_host`, `smtp_port`, `smtp_username`, `smtp_password`, `from_email`, `from_name`, `updated_at`)
        VALUES (1, 'mail.nexstoreapps.com', 465, 'shs@nexstoreapps.com', 'SHS@nexova', NULL, 'SHSApperal', '2026-05-22 16:57:26')");

    echo "Tables dropped and recreated successfully!\n";
} catch (\Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
