<?php
/**
 * Create Sub-Agent Top-up Tracking Table
 * SECURITY: Tracks all sub-agent top-ups for audit and security purposes
 * This table stores information BEFORE the account is credited
 */

require_once 'config.php';

$sql = "
CREATE TABLE IF NOT EXISTS subagent_topup_tracking (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT(11) NOT NULL COMMENT 'Sub-agent user ID',
    store_id INT(11) NOT NULL COMMENT 'Store ID the top-up is for',
    store_owner_id INT(11) NOT NULL COMMENT 'Store owner ID (for reference)',
    payment_reference VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique payment reference from Paystack',
    base_amount DECIMAL(10, 2) NOT NULL COMMENT 'Amount to credit (excluding charges)',
    charge_amount DECIMAL(10, 2) DEFAULT 0.00 COMMENT 'Paystack charge amount',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT 'Total amount paid (including charges)',
    payment_status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending' COMMENT 'Payment status',
    payment_method VARCHAR(50) DEFAULT 'paystack' COMMENT 'Payment method used',
    external_transaction_id VARCHAR(255) DEFAULT NULL COMMENT 'Paystack transaction ID',
    ip_address VARCHAR(45) DEFAULT NULL COMMENT 'IP address of the user making the top-up',
    user_agent TEXT DEFAULT NULL COMMENT 'User agent string',
    raw_response_data TEXT DEFAULT NULL COMMENT 'Raw Paystack response data (JSON)',
    notes TEXT DEFAULT NULL COMMENT 'Additional notes or error messages',
    credited_at DATETIME DEFAULT NULL COMMENT 'Timestamp when account was credited',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When top-up was initiated',
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp',
    INDEX idx_user_id (user_id),
    INDEX idx_store_id (store_id),
    INDEX idx_store_owner_id (store_owner_id),
    INDEX idx_payment_reference (payment_reference),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES agents(id) ON DELETE CASCADE,
    FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
    FOREIGN KEY (store_owner_id) REFERENCES agents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks all sub-agent top-ups for security and audit purposes';
";

try {
    if ($conn->query($sql)) {
        echo "✅ Table 'subagent_topup_tracking' created successfully!\n";
        echo "\nTable structure:\n";
        echo "- id: Primary key\n";
        echo "- user_id: Sub-agent making the top-up\n";
        echo "- store_id: Store the top-up is for\n";
        echo "- store_owner_id: Store owner (for reference)\n";
        echo "- payment_reference: Unique payment reference\n";
        echo "- base_amount: Amount to credit\n";
        echo "- charge_amount: Paystack charges\n";
        echo "- total_amount: Total paid\n";
        echo "- payment_status: pending/processing/completed/failed/cancelled\n";
        echo "- ip_address: User's IP address\n";
        echo "- credited_at: When account was credited\n";
        echo "- created_at: When top-up was initiated\n";
    } else {
        echo "❌ Error creating table: " . $conn->error . "\n";
    }
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}

$conn->close();
?>

