-- Create column-based notification subscriptions table
-- This replaces the limited lead/admin notification system with a flexible column-based approach

CREATE TABLE IF NOT EXISTS project_column_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    user_id INT NOT NULL,
    column_name VARCHAR(50) NOT NULL, -- 'backlog', 'todo', 'in_progress', 'review', 'testing', 'done', 'cancelled'
    notification_type ENUM('on_enter', 'on_exit') DEFAULT 'on_enter', -- When task enters or exits this column
    is_enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_project_user (project_id, user_id),
    INDEX idx_project_column (project_id, column_name),
    INDEX idx_user_subscriptions (user_id),
    
    -- Foreign key constraints
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    -- Unique constraint to prevent duplicate subscriptions
    UNIQUE KEY unique_subscription (project_id, user_id, column_name, notification_type)
);

-- Add comment for documentation
ALTER TABLE project_column_subscriptions COMMENT = 'Column-based notification subscriptions for project task status changes';

-- Insert some example default subscriptions for common roles
-- These can be customized per organization/project
-- Examples:
-- Developers typically want to know about 'todo' tasks (new work)
-- Testers typically want to know about 'testing' tasks
-- Project leads typically want to know about 'review' and 'done' tasks
-- Everyone might want to know about 'cancelled' tasks

-- Note: These would be inserted by the application logic based on user roles/preferences
-- rather than hardcoded here