CREATE TABLE order_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    provider_code VARCHAR(50) NOT NULL,
    attempt_no INT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('pending', 'requires_action', 'paid', 'failed', 'cancelled', 'expired') NOT NULL DEFAULT 'pending',
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    amount_expected_ttc DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount_paid_ttc DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    provider_checkout_session_id VARCHAR(190) NULL,
    provider_payment_intent_id VARCHAR(190) NULL,
    provider_payment_status VARCHAR(80) NULL,
    provider_customer_email VARCHAR(190) NULL,
    checkout_url VARCHAR(500) NULL,
    failure_code VARCHAR(120) NULL,
    failure_message TEXT NULL,
    metadata_json JSON NULL,
    raw_last_payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    paid_at DATETIME NULL,
    failed_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    expired_at DATETIME NULL,
    UNIQUE KEY uq_order_payments_provider_session (provider_checkout_session_id),
    UNIQUE KEY uq_order_payments_order_attempt (order_id, attempt_no),
    INDEX idx_order_payments_order (order_id),
    INDEX idx_order_payments_status (status),
    CONSTRAINT fk_order_payments_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
