<?php
require_once __DIR__ . '/config.php';
$db = getDB();

try {
    // 1. Create dealer_ledgers table
    $db->exec("
        CREATE TABLE IF NOT EXISTS dealer_ledgers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            dealer_id INT NOT NULL,
            date DATE NOT NULL,
            description VARCHAR(255) NOT NULL,
            type ENUM('debit', 'credit') NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            balance DECIMAL(10,2) NOT NULL,
            reference_id INT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (dealer_id) REFERENCES dealers(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    // 2. Add columns to orders for tracking if they don't exist
    $db->exec("
        ALTER TABLE orders 
        ADD COLUMN IF NOT EXISTS tracking_number VARCHAR(100) DEFAULT NULL,
        ADD COLUMN IF NOT EXISTS logistics_partner VARCHAR(100) DEFAULT NULL,
        ADD COLUMN IF NOT EXISTS shipped_at DATETIME DEFAULT NULL,
        ADD COLUMN IF NOT EXISTS delivered_at DATETIME DEFAULT NULL
    ");

    // 3. Add columns to dealers for outstanding balance
    $db->exec("
        ALTER TABLE dealers 
        ADD COLUMN IF NOT EXISTS credit_limit DECIMAL(10,2) DEFAULT 0.00,
        ADD COLUMN IF NOT EXISTS outstanding_balance DECIMAL(10,2) DEFAULT 0.00
    ");

    echo "Migration V3 Successful!";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
