CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  username VARCHAR(64) NULL,
  first_name VARCHAR(128) NULL,
  kyc_status VARCHAR(32) NOT NULL DEFAULT 'unverified',
  is_blocked TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_states (
  telegram_id BIGINT PRIMARY KEY,
  state VARCHAR(64) NOT NULL,
  data_json LONGTEXT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kyc_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  full_name_enc LONGTEXT NOT NULL,
  national_id_enc LONGTEXT NOT NULL,
  phone_enc LONGTEXT NOT NULL,
  card_enc LONGTEXT NOT NULL,
  card_masked VARCHAR(32) NOT NULL,
  id_doc_file_id TEXT NOT NULL,
  bank_card_file_id TEXT NOT NULL,
  submitted_at DATETIME NOT NULL,
  reviewed_at DATETIME NULL,
  reviewed_by BIGINT NULL,
  CONSTRAINT fk_kyc_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  symbol VARCHAR(32) NOT NULL UNIQUE,
  market_symbol VARCHAR(64) NOT NULL UNIQUE,
  asset_precision TINYINT NOT NULL DEFAULT 8,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  custom_markup_percent DECIMAL(10,4) NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS treasury_wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  asset VARCHAR(32) NOT NULL,
  network VARCHAR(64) NOT NULL,
  provider VARCHAR(32) NOT NULL DEFAULT 'trustwallet',
  address TEXT NOT NULL,
  memo TEXT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  min_reserve DECIMAL(36,18) NOT NULL DEFAULT 0,
  max_single_send DECIMAL(36,18) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_treasury_asset_network(asset,network),
  KEY idx_treasury_provider(provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id VARCHAR(32) NOT NULL UNIQUE,
  client_order_id VARCHAR(64) NOT NULL UNIQUE,
  user_id BIGINT UNSIGNED NOT NULL,
  asset VARCHAR(32) NOT NULL,
  market_symbol VARCHAR(64) NOT NULL,
  network VARCHAR(64) NOT NULL,
  quantity DECIMAL(36,18) NOT NULL,
  wallet_address TEXT NOT NULL,
  wallet_memo TEXT NULL,
  market_price_irt DECIMAL(36,8) NOT NULL,
  markup_percent DECIMAL(10,4) NOT NULL,
  final_amount_irt DECIMAL(36,8) NOT NULL,
  quote_expires_at DATETIME NOT NULL,
  status VARCHAR(64) NOT NULL,
  receipt_file_id TEXT NULL,
  approved_by BIGINT NULL,
  tabdeal_order_id VARCHAR(128) NULL,
  tabdeal_withdrawal_id VARCHAR(128) NULL,
  executed_qty DECIMAL(36,18) NULL,
  treasury_wallet_id BIGINT UNSIGNED NULL,
  delivery_approved_by BIGINT NULL,
  txid TEXT NULL,
  failure_reason TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_order_user(user_id),
  KEY idx_order_status(status),
  CONSTRAINT fk_order_user FOREIGN KEY(user_id) REFERENCES users(id),
  CONSTRAINT fk_order_treasury FOREIGN KEY(treasury_wallet_id) REFERENCES treasury_wallets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_telegram_id BIGINT NULL,
  action VARCHAR(128) NOT NULL,
  object_type VARCHAR(64) NOT NULL,
  object_id VARCHAR(128) NOT NULL,
  details LONGTEXT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_audit_action(action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS wallet_connections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(32) NOT NULL,
  network VARCHAR(32) NOT NULL,
  address VARCHAR(255) NOT NULL,
  wallet_name VARCHAR(128) NULL,
  is_admin_wallet TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json LONGTEXT NULL,
  connected_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_wallet_provider_network_address(provider, network, address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS processed_updates (
  update_id BIGINT PRIMARY KEY,
  status VARCHAR(16) NOT NULL,
  error_text TEXT NULL,
  received_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_processed_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_errors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope VARCHAR(128) NOT NULL,
  message TEXT NOT NULL,
  context_json LONGTEXT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_errors_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
