<?php
require_once 'config/database.php';

echo "<h2>Creating Previous Balance Table...</h2>";

try {
    // Create previous_balances table
    $sql = "
    CREATE TABLE IF NOT EXISTS previous_balances (
        id INT AUTO_INCREMENT PRIMARY KEY,
        account_name VARCHAR(100) NOT NULL,
        account_type ENUM('cash', 'bank', 'mobile', 'other') DEFAULT 'cash',
        balance_date DATE NOT NULL,
        opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        description TEXT,
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        INDEX idx_account_name (account_name),
        INDEX idx_balance_date (balance_date),
        INDEX idx_account_type (account_type),
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
    )";
    
    $pdo->exec($sql);
    echo "<p style='color: green;'>✓ Previous balances table created successfully</p>";
    
    // Check if table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'previous_balances'");
    $exists = $stmt->fetch();
    
    if ($exists) {
        echo "<p>Table 'previous_balances' exists in database</p>";
        
        // Show table structure
        $stmt = $pdo->query("DESCRIBE previous_balances");
        $columns = $stmt->fetchAll();
        
        echo "<h3>Table Structure:</h3>";
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
        foreach ($columns as $column) {
            echo "<tr>";
            echo "<td>" . $column['Field'] . "</td>";
            echo "<td>" . $column['Type'] . "</td>";
            echo "<td>" . $column['Null'] . "</td>";
            echo "<td>" . $column['Key'] . "</td>";
            echo "<td>" . $column['Default'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        // Insert sample data if table is empty
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM previous_balances");
        $count = $stmt->fetch()['count'];
        
        if ($count == 0) {
            echo "<h3>Inserting sample data...</h3>";
            
            $sample_data = [
                ['Cash Account', 'cash', date('Y-m-01'), 50000.00, 'Opening cash balance for current month', 1],
                ['Bank Account', 'bank', date('Y-m-01'), 150000.00, 'Opening bank balance for current month', 1],
                ['Mobile Banking', 'mobile', date('Y-m-01'), 25000.00, 'Opening mobile banking balance', 1]
            ];
            
            $stmt = $pdo->prepare("INSERT INTO previous_balances (account_name, account_type, balance_date, opening_balance, description, created_by) VALUES (?, ?, ?, ?, ?, ?)");
            
            foreach ($sample_data as $data) {
                $stmt->execute($data);
                echo "<p>✓ Inserted: " . $data[0] . " - ৳" . number_format($data[3], 2) . "</p>";
            }
        } else {
            echo "<p>Existing records found: " . $count . " entries</p>";
        }
        
        // Show current data
        echo "<h3>Current Previous Balances:</h3>";
        $stmt = $pdo->query("
            SELECT pb.*, u.username as created_by_name 
            FROM previous_balances pb 
            LEFT JOIN users u ON pb.created_by = u.id 
            ORDER BY pb.balance_date DESC, pb.account_name ASC
        ");
        $balances = $stmt->fetchAll();
        
        if (!empty($balances)) {
            echo "<table border='1' cellpadding='5'>";
            echo "<tr><th>Account Name</th><th>Type</th><th>Date</th><th>Balance</th><th>Description</th><th>Created By</th></tr>";
            foreach ($balances as $balance) {
                echo "<tr>";
                echo "<td>" . htmlspecialchars($balance['account_name']) . "</td>";
                echo "<td>" . ucfirst($balance['account_type']) . "</td>";
                echo "<td>" . $balance['balance_date'] . "</td>";
                echo "<td>৳" . number_format($balance['opening_balance'], 2) . "</td>";
                echo "<td>" . htmlspecialchars($balance['description'] ?? '') . "</td>";
                echo "<td>" . htmlspecialchars($balance['created_by_name'] ?? 'System') . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        } else {
            echo "<p>No previous balances found</p>";
        }
        
    } else {
        echo "<p style='color: red;'>✗ Failed to create table</p>";
    }
    
    echo "<h3>Setup Complete!</h3>";
    echo "<p>You can now access the Previous Balance management system.</p>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}
?>