-- Virtual Cards Table for TrustBeam Credit
-- This table stores virtual debit cards for customers

CREATE TABLE IF NOT EXISTS `virtual_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `card_number` varchar(19) NOT NULL,
  `card_type` varchar(50) NOT NULL DEFAULT 'Virtual Debit',
  `expiry_date` varchar(7) NOT NULL,
  `cvv` varchar(3) NOT NULL,
  `daily_limit` decimal(10,2) NOT NULL DEFAULT 1000.00,
  `monthly_limit` decimal(10,2) NOT NULL DEFAULT 5000.00,
  `status` enum('Active','Blocked','Expired','Suspended') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `card_number` (`card_number`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `virtual_cards_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Sample data (optional)
INSERT INTO `virtual_cards` (`user_id`, `card_number`, `card_type`, `expiry_date`, `cvv`, `daily_limit`, `monthly_limit`, `status`) VALUES
(1, '4111111111111111', 'Virtual Debit', '12/25', '123', 1000.00, 5000.00, 'Active'),
(1, '4222222222222222', 'Virtual Debit', '06/26', '456', 1000.00, 5000.00, 'Active'),
(2, '4333333333333333', 'Virtual Debit', '09/26', '789', 1000.00, 5000.00, 'Active');
