-- =================================================================
-- FlowChief Intelligence System Database Migration
-- Creates event-driven intelligence infrastructure
-- =================================================================

-- Work Events: Capture all work activities as immutable events
CREATE TABLE IF NOT EXISTS work_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    
    -- Event Classification
    event_type ENUM(
        'task_start',           -- User starts working on a task
        'task_stop',            -- User stops working on a task
        'task_move',            -- Task moved between columns/statuses
        'task_assign',          -- Task assigned to someone
        'task_comment',         -- Comment added to task
        'heartbeat',            -- User activity heartbeat
        'context_switch',       -- User switches between tasks
        'session_break',        -- Explicit break/pause
        'manual_entry'          -- Manual time entry
    ) NOT NULL,
    
    -- Core Entities
    user_id INT,
    task_id INT,
    project_id INT,
    
    -- Status Changes
    from_status VARCHAR(100),
    to_status VARCHAR(100),
    
    -- Flexible Metadata
    metadata JSON, -- {session_id, confidence, source, duration_hint, etc}
    
    -- Timing
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Performance Indexes
    INDEX idx_org_time (organization_id, timestamp),
    INDEX idx_user_time (user_id, timestamp),
    INDEX idx_task_events (task_id, timestamp),
    INDEX idx_project_events (project_id, timestamp),
    INDEX idx_event_type (event_type, timestamp),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE SET NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- Work Sessions: Processed view of user work patterns
CREATE TABLE IF NOT EXISTS work_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    
    -- Session Identity  
    session_uuid VARCHAR(36) UNIQUE, -- For deduplication
    user_id INT NOT NULL,
    task_id INT NOT NULL,
    project_id INT NOT NULL,
    
    -- Session Classification
    session_type ENUM(
        'development',          -- Active development work
        'review',              -- Code/design review
        'testing',             -- Testing and QA
        'planning',            -- Planning and analysis
        'meeting',             -- Meetings and discussions
        'research',            -- Research and investigation
        'deployment',          -- Deployment activities
        'maintenance'          -- Bug fixes and maintenance
    ) DEFAULT 'development',
    
    -- Timing
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NULL, -- NULL = still active
    duration_minutes INT GENERATED ALWAYS AS (
        CASE 
            WHEN end_time IS NULL THEN NULL
            ELSE TIMESTAMPDIFF(MINUTE, start_time, end_time)
        END
    ) STORED,
    
    -- Quality Metrics
    confidence DECIMAL(3,2) DEFAULT 1.00, -- 0.00 to 1.00
    context_switches INT DEFAULT 0,
    interruption_count INT DEFAULT 0,
    
    -- Derived Insights
    productivity_score DECIMAL(3,2), -- Calculated productivity metric
    focus_score DECIMAL(3,2),        -- Measure of sustained focus
    
    -- Metadata
    source_events JSON, -- Array of event IDs that created this session
    notes TEXT,         -- Manual notes/context
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Performance Indexes
    INDEX idx_user_sessions (user_id, start_time DESC),
    INDEX idx_active_sessions (user_id, end_time), -- NULL end_time for active
    INDEX idx_project_sessions (project_id, start_time DESC),
    INDEX idx_task_sessions (task_id, start_time DESC),
    INDEX idx_session_duration (duration_minutes),
    INDEX idx_session_type (session_type, start_time),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Workflow Mappings: Map project columns to canonical workflow phases
CREATE TABLE IF NOT EXISTS workflow_mappings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    project_id INT, -- NULL = organization default
    
    -- Column Mapping
    column_name VARCHAR(100) NOT NULL,
    canonical_phase ENUM(
        'backlog',             -- Ideas and planning
        'development',         -- Active development
        'review',             -- Code review, design review
        'testing',            -- QA, testing, validation
        'deployment',         -- Deploy, staging, release prep
        'done',               -- Completed work
        'blocked',            -- Blocked/waiting
        'cancelled'           -- Cancelled/rejected
    ) NOT NULL,
    
    -- Role Assignment
    primary_role ENUM(
        'developer',          -- Software developers
        'reviewer',           -- Code/design reviewers
        'tester',             -- QA engineers, testers
        'deployer',           -- DevOps, deployment engineers
        'manager',            -- Project managers
        'designer',           -- UI/UX designers
        'analyst'             -- Business analysts
    ),
    
    -- Configuration
    is_active BOOLEAN DEFAULT TRUE,
    weight DECIMAL(3,2) DEFAULT 1.00, -- For priority calculations
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_org_mappings (organization_id),
    INDEX idx_project_mappings (project_id),
    INDEX idx_column_mappings (column_name),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    
    -- Constraints
    UNIQUE KEY unique_column_mapping (organization_id, project_id, column_name)
);

-- Intelligence Queries: Store and learn from user queries
CREATE TABLE IF NOT EXISTS intelligence_queries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    
    -- Query Details
    query_text TEXT NOT NULL,
    query_intent VARCHAR(100), -- Parsed intent category
    query_type ENUM(
        'status',              -- "What's John working on?"
        'workload',            -- "Who's overloaded?"
        'progress',            -- "How is project X going?"
        'risk',                -- "What are the risks?"
        'analytics',           -- "Show me productivity trends"
        'forecast',            -- "When will this be done?"
        'assignment',          -- "Who should work on this?"
        'general'              -- Other queries
    ),
    
    -- Response Tracking
    response_data JSON,        -- Full response content
    response_time_ms INT,      -- Query processing time
    confidence_score DECIMAL(3,2), -- AI confidence in response
    
    -- User Feedback
    user_rating ENUM('helpful', 'neutral', 'unhelpful') NULL,
    user_feedback TEXT,
    
    -- Context
    context JSON,              -- User role, project context, etc.
    session_id VARCHAR(100),   -- Group related queries
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_user_queries (user_id, created_at DESC),
    INDEX idx_org_queries (organization_id, created_at DESC),
    INDEX idx_query_type (query_type, created_at DESC),
    INDEX idx_query_rating (user_rating),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Intelligence Insights: Proactive insights and recommendations
CREATE TABLE IF NOT EXISTS intelligence_insights (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    
    -- Insight Classification
    insight_type ENUM(
        'workload_warning',    -- User overloaded
        'project_risk',        -- Project at risk
        'blocker_detected',    -- Blocking dependencies
        'deadline_alert',      -- Approaching deadlines
        'quality_concern',     -- Quality metrics declining
        'resource_opportunity', -- Available capacity
        'performance_trend',   -- Performance patterns
        'collaboration_issue', -- Team communication problems
        'efficiency_gain'      -- Optimization opportunities
    ) NOT NULL,
    
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    
    -- Content
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    recommendation TEXT,       -- Suggested actions
    
    -- Targeting
    target_users JSON,         -- Array of user IDs who should see this
    target_roles JSON,         -- Array of roles who should see this
    
    -- Lifecycle
    is_active BOOLEAN DEFAULT TRUE,
    action_required BOOLEAN DEFAULT FALSE,
    dismissed_by JSON,         -- Array of user IDs who dismissed this
    
    -- Context
    related_entities JSON,     -- {projects: [], tasks: [], users: []}
    evidence JSON,            -- Supporting data and metrics
    confidence DECIMAL(3,2) DEFAULT 0.80,
    
    -- Timing
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL, -- When insight becomes stale
    first_shown_at TIMESTAMP NULL,
    last_shown_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_org_insights (organization_id, priority, is_active),
    INDEX idx_insight_type (insight_type, detected_at),
    INDEX idx_active_insights (is_active, expires_at),
    INDEX idx_priority_insights (priority, detected_at),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

-- Task Priority Cache: Pre-calculated priority scores for performance
CREATE TABLE IF NOT EXISTS task_priorities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    task_id INT NOT NULL,
    
    -- Priority Scores (0.00 to 1.00)
    operational_priority DECIMAL(5,4) NOT NULL,  -- Rule-based priority
    predictive_priority DECIMAL(5,4),           -- ML-enhanced priority (optional)
    final_priority DECIMAL(5,4) NOT NULL,       -- Weighted combination
    
    -- Score Components (for explainability)
    urgency_score DECIMAL(3,2),     -- Due date proximity
    impact_score DECIMAL(3,2),      -- Business value/story points
    blockage_score DECIMAL(3,2),    -- Blocking other tasks
    risk_score DECIMAL(3,2),        -- Technical/quality risk
    breach_score DECIMAL(3,2),      -- Already overdue
    
    -- Reasoning
    priority_reason JSON,           -- Explanation of score components
    calculation_metadata JSON,     -- Detailed calculation data
    
    -- Freshness
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL, -- When to recalculate
    
    -- Indexes
    INDEX idx_task_priority (task_id),
    INDEX idx_org_priorities (organization_id, final_priority DESC),
    INDEX idx_priority_expiry (expires_at),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    
    -- Constraints
    UNIQUE KEY unique_task_priority (task_id)
);

-- Insert default workflow mappings for existing organizations
INSERT INTO workflow_mappings (organization_id, column_name, canonical_phase, primary_role)
SELECT DISTINCT o.id, 'backlog', 'backlog', 'manager' FROM organizations o
WHERE NOT EXISTS (SELECT 1 FROM workflow_mappings WHERE organization_id = o.id AND column_name = 'backlog');

INSERT INTO workflow_mappings (organization_id, column_name, canonical_phase, primary_role)
SELECT DISTINCT o.id, 'todo', 'development', 'developer' FROM organizations o
WHERE NOT EXISTS (SELECT 1 FROM workflow_mappings WHERE organization_id = o.id AND column_name = 'todo');

INSERT INTO workflow_mappings (organization_id, column_name, canonical_phase, primary_role)
SELECT DISTINCT o.id, 'in_progress', 'development', 'developer' FROM organizations o
WHERE NOT EXISTS (SELECT 1 FROM workflow_mappings WHERE organization_id = o.id AND column_name = 'in_progress');

INSERT INTO workflow_mappings (organization_id, column_name, canonical_phase, primary_role)
SELECT DISTINCT o.id, 'review', 'review', 'reviewer' FROM organizations o
WHERE NOT EXISTS (SELECT 1 FROM workflow_mappings WHERE organization_id = o.id AND column_name = 'review');

INSERT INTO workflow_mappings (organization_id, column_name, canonical_phase, primary_role)
SELECT DISTINCT o.id, 'testing', 'testing', 'tester' FROM organizations o
WHERE NOT EXISTS (SELECT 1 FROM workflow_mappings WHERE organization_id = o.id AND column_name = 'testing');

INSERT INTO workflow_mappings (organization_id, column_name, canonical_phase, primary_role)
SELECT DISTINCT o.id, 'done', 'done', NULL FROM organizations o
WHERE NOT EXISTS (SELECT 1 FROM workflow_mappings WHERE organization_id = o.id AND column_name = 'done');

-- Success confirmation
SELECT 'Intelligence System database migration completed successfully!' as status;
SELECT 'Created tables: work_events, work_sessions, workflow_mappings, intelligence_queries, intelligence_insights, task_priorities' as tables_created;
SELECT COUNT(*) as workflow_mappings_created FROM workflow_mappings;