<?php
/**
 * JUSTSALE POS - Database Migration Script
 * Migrates local database to v3.0.0
 * Adds: Product Batches & Expiry Dates
 */

require_once 'api/db.php';

try {
    echo "<h2>JUSTSALE Database Migration Tool (v3.0.0)</h2>";
    echo "<p>Connected to database: <strong>$dbname</strong></p>";
    echo "<hr>";

    // 1. Update products table
    echo "Updating 'products' table... ";
    $pdo->exec("ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `track_batches` tinyint(1) DEFAULT 0 AFTER `min_stock_level` ");
    echo "<span style='color:green'>Success</span><br>";

    // 2. Create product_batches table
    echo "Creating 'product_batches' table... ";
    $sql_pb = "CREATE TABLE IF NOT EXISTS `product_batches` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `product_id` int(11) NOT NULL,
      `batch_number` varchar(100) DEFAULT NULL,
      `expiry_date` date DEFAULT NULL,
      `cost_price` decimal(12,2) DEFAULT NULL,
      `selling_price` decimal(12,2) DEFAULT NULL,
      `initial_quantity` int(11) NOT NULL DEFAULT 0,
      `stock_quantity` int(11) NOT NULL DEFAULT 0,
      `created_at` timestamp DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      KEY `product_id` (`product_id`),
      CONSTRAINT `product_batches_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    $pdo->exec($sql_pb);
    echo "<span style='color:green'>Success</span><br>";

    // 3. Update sale_items table
    echo "Updating 'sale_items' table... ";
    $pdo->exec("ALTER TABLE `sale_items` ADD COLUMN IF NOT EXISTS `batch_id` int(11) DEFAULT NULL AFTER `subtotal` ");
    $pdo->exec("ALTER TABLE `sale_items` ADD INDEX IF NOT EXISTS `batch_id` (`batch_id`) ");
    // Note: Foreign key addition might fail if column already exists or index missing, so we use try/catch or just exec
    try {
        $pdo->exec("ALTER TABLE `sale_items` ADD CONSTRAINT `sale_items_ibfk_3` FOREIGN KEY IF NOT EXISTS (`batch_id`) REFERENCES `product_batches` (`id`) ON DELETE SET NULL");
    } catch (Exception $e) {}
    echo "<span style='color:green'>Success</span><br>";

    // 4. Update reception_items table
    echo "Updating 'reception_items' table... ";
    $pdo->exec("ALTER TABLE `reception_items` ADD COLUMN IF NOT EXISTS `batch_id` int(11) DEFAULT NULL AFTER `quantity_received` ");
    $pdo->exec("ALTER TABLE `reception_items` ADD INDEX IF NOT EXISTS `batch_id` (`batch_id`) ");
    try {
        $pdo->exec("ALTER TABLE `reception_items` ADD CONSTRAINT `reception_items_ibfk_4` FOREIGN KEY IF NOT EXISTS (`batch_id`) REFERENCES `product_batches` (`id`) ON DELETE SET NULL");
    } catch (Exception $e) {}
    echo "<span style='color:green'>Success</span><br>";

    // 5. Update stock_adjustments table
    echo "Updating 'stock_adjustments' table... ";
    $pdo->exec("ALTER TABLE `stock_adjustments` ADD COLUMN IF NOT EXISTS `batch_id` int(11) DEFAULT NULL AFTER `user_id` ");
    $pdo->exec("ALTER TABLE `stock_adjustments` ADD INDEX IF NOT EXISTS `batch_id` (`batch_id`) ");
    try {
        $pdo->exec("ALTER TABLE `stock_adjustments` ADD CONSTRAINT `stock_adjustments_ibfk_3` FOREIGN KEY IF NOT EXISTS (`batch_id`) REFERENCES `product_batches` (`id`) ON DELETE SET NULL");
    } catch (Exception $e) {}
    echo "<span style='color:green'>Success</span><br>";

    // 6. Update System Version
    $pdo->prepare("INSERT INTO settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)")
        ->execute(['system_version', '3.0.0']);

    echo "<hr>";
    echo "<h3 style='color:blue'>Migration Complete!</h3>";
    echo "<p>Your database is now fully compatible with JUSTSALE v3.0.0 (Batches & Expiry Edition).</p>";
    echo "<p><a href='index.html'>Click here to return to the Dashboard</a></p>";

} catch (Exception $e) {
    echo "<hr>";
    echo "<h3 style='color:red'>Migration Failed!</h3>";
    echo "<p>Error: " . $e->getMessage() . "</p>";
}
