

-- Payment Transactions Log (audit trail of all payment attempts)
CREATE TABLE IF NOT EXISTS payment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'ZAR',
    gateway_name VARCHAR(50) NOT NULL,
    gateway_transaction_id VARCHAR(255) UNIQUE,
    status ENUM('pending', 'completed', 'failed', 'cancelled', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),
    description TEXT,
    metadata JSON,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES billing_plans(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_gateway_transaction (gateway_transaction_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- Webhook Logs (debugging and auditing webhooks from payment providers)
CREATE TABLE IF NOT EXISTS webhook_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    gateway VARCHAR(50) NOT NULL,
    data LONGTEXT,
    response LONGTEXT,
    http_code INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gateway (gateway),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Passkey Challenges (for authentication flow - expires after 10 minutes)
CREATE TABLE IF NOT EXISTS passkey_challenges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    challenge VARBINARY(512) NOT NULL UNIQUE,
    challenge_hash VARCHAR(64) NOT NULL,
    purpose ENUM('registration', 'authentication') DEFAULT 'authentication',
    expires_at TIMESTAMP,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_challenge_hash (challenge_hash),
    INDEX idx_expires (expires_at)
);
