<?php
require 'includes/db.php';

try {
    // 1. Create Commissions Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS commissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        company_id INT NOT NULL,
        agent_id INT NOT NULL,
        property_id INT NOT NULL,
        allocation_id INT NULL,
        commission_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        commission_percentage DECIMAL(5,2) NULL,
        status ENUM('pending', 'approved', 'paid', 'rejected') DEFAULT 'pending',
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
        FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
        FOREIGN KEY (allocation_id) REFERENCES allocations(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    echo "Table 'commissions' created/checked.\n";

    echo "Commission Schema update completed successfully.\n";

} catch (PDOException $e) {
    die("Schema Update Failed: " . $e->getMessage() . "\n");
}
?>