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

try {
    $db = getDB();
    
    // Create cash_fund table
    $db->exec('BEGIN TRANSACTION');
    
    // Drop table if exists
    $db->exec('DROP TABLE IF EXISTS cash_fund');
    
    // Create the table with all necessary columns
    $db->exec('CREATE TABLE cash_fund (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        initial_balance DECIMAL(10,2) NOT NULL,
        current_balance DECIMAL(10,2) NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )');
    
    // Add balance_after column to financial_transactions if it doesn't exist
    $result = $db->query("PRAGMA table_info(financial_transactions)");
    $columns = [];
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $columns[] = $row['name'];
    }
    
    if (!in_array('balance_after', $columns)) {
        $db->exec('ALTER TABLE financial_transactions ADD COLUMN balance_after DECIMAL(10,2)');
    }
    
    $db->exec('COMMIT');
    echo "Migration completed successfully: Created cash_fund table\n";
    
} catch (Exception $e) {
    $db->exec('ROLLBACK');
    echo "Migration failed: " . $e->getMessage() . "\n";
}
