<?php
require_once __DIR__ . '/../api/config/database.php';
$db = Database::connect();

try {
    $db->beginTransaction();

    echo "1. Adding columns to purchase_lots...\n";
    $cols = [
        'bori_qty INT DEFAULT 0 AFTER packing',
        'tara DECIMAL(10,2) DEFAULT 0.00 AFTER bori_qty',
        'gross_weight DECIMAL(12,2) DEFAULT 0.00 AFTER tara',
        'kat DECIMAL(10,2) DEFAULT 0.00 AFTER gross_weight',
        'rate_basis INT DEFAULT 40 AFTER net_weight',
        'rate DECIMAL(12,2) DEFAULT 0.00 AFTER rate_basis',
        'gross_amount DECIMAL(15,2) DEFAULT 0.00 AFTER rate'
    ];

    foreach ($cols as $col) {
        // Try to add, ignore if already exists
        try {
            $db->exec("ALTER TABLE purchase_lots ADD COLUMN $col");
        } catch (Exception $e) {
            echo "   (Column already exists or error: " . $e->getMessage() . ")\n";
        }
    }

    echo "2. Syncing existing single-item data from purchase_master to purchase_lots...\n";
    // We update purchase_lots by joining with purchase_master
    $sql = "
        UPDATE purchase_lots pl
        JOIN purchase_master pm ON pl.purchase_id = pm.id
        SET 
            pl.bori_qty = pm.bori_qty,
            pl.tara = pm.tara,
            pl.gross_weight = pm.gross_weight,
            pl.kat = pm.kat,
            pl.rate_basis = pm.rate_basis,
            pl.rate = pm.rate,
            pl.gross_amount = pm.gross_amount
        WHERE (pl.gross_amount = 0 OR pl.gross_amount IS NULL)
    ";
    $count = $db->exec($sql);
    echo "   ($count records synced)\n";

    echo "3. Updating purchase_master to reflect multi-item capability...\n";
    // We'll keep the columns in purchase_master for now to avoid breaking existing queries that don't join yet
    // But we'll add a 'total_gross_amount' to master to explicitly sum items
    try {
        $db->exec("ALTER TABLE purchase_master ADD COLUMN total_gross_amount DECIMAL(15,2) DEFAULT 0.00 AFTER item_id");
        $db->exec("UPDATE purchase_master SET total_gross_amount = gross_amount");
    } catch (Exception $e) {}

    $db->commit();
    echo "Migration completed successfully.\n";

} catch (Exception $e) {
    if ($db->inTransaction()) $db->rollBack();
    echo "Migration FAILED: " . $e->getMessage() . "\n";
}
