-- v1.7: apply after v1.6; dedicated gateway ledgers. Backup first.
CREATE TABLE IF NOT EXISTS saas_gateway_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 org_id BIGINT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NOT NULL,
 provider VARCHAR(20) NOT NULL DEFAULT 'razorpay',
 gateway_order_id VARCHAR(120) NOT NULL UNIQUE,
 amount_paise BIGINT UNSIGNED NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'INR',
 state ENUM('created','paid','failed') NOT NULL DEFAULT 'created',
 created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_gateway_org_invoice(org_id,invoice_id),
 FOREIGN KEY(org_id) REFERENCES organizations(id),
 FOREIGN KEY(invoice_id) REFERENCES saas_billing_invoices(id),
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS saas_gateway_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_id VARCHAR(150) NOT NULL UNIQUE,
 provider VARCHAR(20) NOT NULL,
 event_type VARCHAR(100) NOT NULL,
 gateway_order_id VARCHAR(120) NULL,
 gateway_payment_id VARCHAR(120) NULL,
 raw_sha256 CHAR(64) NOT NULL,
 received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_gateway_event_order(gateway_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS saas_gateway_payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 gateway_payment_id VARCHAR(120) NOT NULL UNIQUE,
 gateway_order_id VARCHAR(120) NOT NULL,
 amount_paise BIGINT UNSIGNED NOT NULL,
 status VARCHAR(30) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_gateway_pay_order(gateway_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;