CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('superadmin','admin','merchant') NOT NULL,
 permissions TEXT NULL,
 active TINYINT NOT NULL DEFAULT 1,
 client_id BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS clients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 contact_phone VARCHAR(50) NOT NULL DEFAULT '',
 address VARCHAR(1000) NOT NULL DEFAULT '',
 personal_provider VARCHAR(30) NOT NULL DEFAULT 'bkash',
 merchant_provider VARCHAR(30) NOT NULL DEFAULT 'bkash',
 personal_enabled TINYINT NOT NULL DEFAULT 1,
 merchant_enabled TINYINT NOT NULL DEFAULT 1,
 payment_provider VARCHAR(30) NOT NULL DEFAULT 'bkash',
 website_url VARCHAR(500) DEFAULT '',
 personal_number VARCHAR(50) DEFAULT '',
 merchant_number VARCHAR(50) DEFAULT '',
 qr_url VARCHAR(500) DEFAULT '',
 status ENUM('active','suspended') DEFAULT 'active',
 api_key_hash CHAR(64) NOT NULL UNIQUE,
 fee_per_1000 DECIMAL(12,2) NOT NULL DEFAULT 0,
 fee_per_success DECIMAL(12,2) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 token CHAR(48) NOT NULL UNIQUE,
 order_ref VARCHAR(120) NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 method ENUM('personal','merchant') NOT NULL,
 provider_snapshot VARCHAR(30) NOT NULL,
 expires_at DATETIME NOT NULL,
 verification_source VARCHAR(30) NOT NULL DEFAULT 'manual',
 receiver_snapshot VARCHAR(50) NOT NULL,
 qr_snapshot VARCHAR(500) DEFAULT '',
 status ENUM('pending','review','verified','rejected','expired','cancelled') NOT NULL DEFAULT 'pending',
 trx_id VARCHAR(150) DEFAULT NULL,
 fee_rate_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0,
 fee_flat_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0,
 fee_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
 fee_flat DECIMAL(14,2) NOT NULL DEFAULT 0,
 fee_total DECIMAL(14,2) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 verified_at TIMESTAMP NULL DEFAULT NULL,
 UNIQUE KEY uniq_order (client_id,order_ref),
 UNIQUE KEY uniq_trx (client_id,trx_id),
 INDEX ix_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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,
 provider VARCHAR(30) NOT NULL DEFAULT '',
 receiver VARCHAR(50) NOT NULL DEFAULT '',
 method ENUM('personal','merchant') NOT NULL DEFAULT 'personal',
 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,
 sms_at DATETIME NULL,
 validated_sms TINYINT NOT NULL DEFAULT 0,
 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;
CREATE TABLE IF NOT EXISTS sms_claims (
 provider VARCHAR(30) NOT NULL, trx_id VARCHAR(150) NOT NULL,
 payment_id BIGINT UNSIGNED NOT NULL UNIQUE, event_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(provider,trx_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS qr_assets (
 id CHAR(48) PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY ix_qr_client(client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;