-- Run ONCE after backing up an existing v0.7 database. Do not also import schema.sql.
ALTER TABLE payments ADD COLUMN fee_rate_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0,
 ADD COLUMN fee_flat_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0;
-- Old links have no historical fee rates. Keep their snapshot zero; do not invent historical charges.
CREATE TABLE IF NOT EXISTS payment_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 payment_id BIGINT UNSIGNED NOT NULL,
 admin_id BIGINT UNSIGNED NOT NULL,
 decision ENUM('verified','rejected') NOT NULL,
 note VARCHAR(1000) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_review_payment(payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 label VARCHAR(100) NOT NULL,
 key_hash CHAR(64) NOT NULL UNIQUE,
 active TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_device_client(client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS device_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 device_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 event_id VARCHAR(120) NOT NULL,
 trx_id VARCHAR(150) NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 receiver VARCHAR(50) NOT NULL,
 provider VARCHAR(50) NOT NULL,
 order_ref VARCHAR(120) NOT NULL DEFAULT '',
 method ENUM('personal','merchant') NOT NULL,
 payload_hash CHAR(64) NOT NULL,
 received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_device_event(device_id,event_id),
 INDEX ix_event_client(client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;