22 lines
918 B
SQL
22 lines
918 B
SQL
-- SMS Bank Integration Tables
|
|
|
|
CREATE TABLE IF NOT EXISTS raw_sms_log (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
sender VARCHAR(100) NOT NULL,
|
|
message_body TEXT NOT NULL,
|
|
status ENUM('pending', 'matched', 'unmatched', 'error') DEFAULT 'pending',
|
|
payment_request_id VARCHAR(36) DEFAULT NULL,
|
|
extracted_ref VARCHAR(50) DEFAULT NULL,
|
|
extracted_amount DECIMAL(12,3) DEFAULT NULL,
|
|
received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
processed_at DATETIME DEFAULT NULL,
|
|
INDEX idx_sms_status (status),
|
|
INDEX idx_sms_payment (payment_request_id),
|
|
INDEX idx_sms_date (received_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Add columns to payment_requests
|
|
-- Run these one at a time. If column already exists, just skip the error.
|
|
ALTER TABLE payment_requests ADD COLUMN sms_log_id VARCHAR(36) DEFAULT NULL;
|
|
ALTER TABLE payment_requests ADD COLUMN confirmed_at DATETIME DEFAULT NULL;
|