-- Migration: Add opportunity competitors table
-- Description: Track competitors for each opportunity with pricing and analysis
-- Date: 2025-01-13

CREATE TABLE IF NOT EXISTS `opportunity_competitors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `opportunity_id` int(10) unsigned NOT NULL,
  `competitor_name` varchar(255) NOT NULL,
  `product_service` varchar(255) DEFAULT NULL COMMENT 'What product/service they are offering',
  `pricing` decimal(15,2) DEFAULT NULL COMMENT 'Their pricing',
  `strengths` text COMMENT 'Competitor strengths',
  `weaknesses` text COMMENT 'Competitor weaknesses',
  `notes` text COMMENT 'Additional notes',
  `created_by` int(10) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_opportunity_id` (`opportunity_id`),
  KEY `idx_created_by` (`created_by`),
  CONSTRAINT `fk_opp_comp_opportunity` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_opp_comp_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;