CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'admin',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  tax_number VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  default_crypto_asset VARCHAR(80) DEFAULT 'USDT Liquid',
  liquid_address VARCHAR(255) NULL,
  pix_key VARCHAR(190) NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_clients_tax_number (tax_number),
  INDEX idx_clients_name (name),
  INDEX idx_clients_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_reference VARCHAR(80) NOT NULL,
  client_id INT NULL,
  customer_name VARCHAR(150) NOT NULL,
  customer_tax_number VARCHAR(20) NOT NULL,
  amount_cents INT NOT NULL,
  depix_address VARCHAR(255) NOT NULL,
  provider_id VARCHAR(100) NULL,
  qr_copy_paste TEXT NULL,
  qr_image_url TEXT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'created',
  public_token VARCHAR(64) NULL,
  blockchain_txid VARCHAR(120) NULL,
  bank_txid VARCHAR(120) NULL,
  raw_response JSON NULL,
  webhook_payload JSON NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_provider_id (provider_id),
  INDEX idx_status (status),
  UNIQUE KEY uniq_payments_public_token (public_token),
  INDEX idx_payments_client_id (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS swaps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  from_asset VARCHAR(80) NOT NULL DEFAULT 'DePix',
  to_asset VARCHAR(80) NOT NULL DEFAULT 'USDT Liquid',
  amount DECIMAL(20,8) NOT NULL,
  wallet_address VARCHAR(255) NOT NULL,
  provider VARCHAR(80) NOT NULL DEFAULT 'SideSwap',
  status VARCHAR(50) NOT NULL DEFAULT 'pending_manual_execution',
  provider_quote_id VARCHAR(120) NULL,
  blockchain_txid VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_swaps_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS crypto_receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  asset VARCHAR(80) NOT NULL DEFAULT 'USDT Liquid',
  amount DECIMAL(20,8) NULL,
  receiving_address VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'waiting_payment',
  reference VARCHAR(100) NULL,
  blockchain_txid VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_crypto_receipts_client_id (client_id),
  INDEX idx_crypto_receipts_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS crypto_payouts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  asset VARCHAR(80) NOT NULL DEFAULT 'USDT Liquid',
  amount DECIMAL(20,8) NOT NULL,
  destination_address VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending_manual_send',
  reference VARCHAR(100) NULL,
  blockchain_txid VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_crypto_payouts_client_id (client_id),
  INDEX idx_crypto_payouts_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;