
CREATE TABLE IF NOT EXISTS `user_opportunity_investments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `opportunity_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `units` decimal(15,8) DEFAULT 0,
  `status` enum('active','sold') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`opportunity_id`) REFERENCES `investment_opportunities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add units column to investment_opportunities if not exists for potential simulated stock price
-- (Note: Not strictly enforced here, handled in logic)
