-- Migration: Add support for multiple user assignments to opportunities
-- Created: 2025-12-03

-- Create opportunity_assignments junction table
CREATE TABLE IF NOT EXISTS opportunity_assignments (
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    opportunity_id INT(10) UNSIGNED NOT NULL,
    user_id INT(10) UNSIGNED NOT NULL,
    role VARCHAR(50) DEFAULT 'contributor' COMMENT 'owner, contributor, viewer',
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT(10) UNSIGNED,
    FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_assignment (opportunity_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add index for faster lookups
CREATE INDEX idx_opportunity_assignments_opportunity ON opportunity_assignments(opportunity_id);
CREATE INDEX idx_opportunity_assignments_user ON opportunity_assignments(user_id);

-- Migrate existing single assignments to the new table
INSERT INTO opportunity_assignments (opportunity_id, user_id, role, assigned_by)
SELECT 
    id as opportunity_id,
    assigned_to as user_id,
    'owner' as role,
    created_by as assigned_by
FROM crm_opportunities
WHERE assigned_to IS NOT NULL
ON DUPLICATE KEY UPDATE opportunity_id = opportunity_id; -- Ignore duplicates if already migrated

-- Note: We're keeping the assigned_to column for backward compatibility
-- It will now represent the "primary" or "owner" assignment
-- The opportunity_assignments table provides full multi-user support

-- Add comment to assigned_to column
ALTER TABLE crm_opportunities 
MODIFY COLUMN assigned_to INT(10) UNSIGNED DEFAULT NULL 
COMMENT 'Primary owner (for backward compatibility, use opportunity_assignments for full list)';