CREATE TABLE `telegram_bot_user_card_contacts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `eu_mobile_number` VARCHAR(30) NOT NULL,
  `country_code` VARCHAR(10) DEFAULT NULL,
  `is_verified_for_3ds` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_id` (`user_id`),
  KEY `idx_eu_mobile_number` (`eu_mobile_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `telegram_bot_virtual_card_inventory` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `reference_code` VARCHAR(50) NOT NULL,
  `cardholder_name` VARCHAR(150) DEFAULT NULL,
  `card_brand` VARCHAR(50) DEFAULT 'Visa',
  `card_number` VARCHAR(32) NOT NULL,
  `masked_card_number` VARCHAR(32) DEFAULT NULL,
  `exp_month` VARCHAR(2) NOT NULL,
  `exp_year` VARCHAR(4) NOT NULL,
  `cvv` VARCHAR(10) NOT NULL,
  `billing_currency` VARCHAR(10) DEFAULT 'EUR',
  `card_label` VARCHAR(100) DEFAULT 'Virtual Card',
  `card_status` ENUM('available','assigned','blocked','expired','cancelled') NOT NULL DEFAULT 'available',
  `assigned_at` DATETIME DEFAULT NULL,
  `admin_notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_reference_code` (`reference_code`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_card_status` (`card_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `telegram_bot_virtual_card_requests` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `virtual_card_id` INT(11) DEFAULT NULL,
  `request_reference` VARCHAR(50) NOT NULL,
  `request_status` ENUM('pending_mobile','submitted','completed','rejected','cancelled') NOT NULL DEFAULT 'submitted',
  `eu_mobile_number` VARCHAR(30) DEFAULT NULL,
  `request_notes` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_request_reference` (`request_reference`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_virtual_card_id` (`virtual_card_id`),
  KEY `idx_request_status` (`request_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;