import { JSONSchema7 } from 'json-schema';
import { randomUUID } from 'crypto';
import { createTool, createSuccessResult, createErrorResult } from '../../core/tool-framework.js';
import { ToolRegistration, RequestContext } from '../../core/types.js';

/**
 * Product Roadmap Tools - 12-Factor MCP Implementation
 * 
 * Implements Factor 2: Deterministic Execution with structured outputs
 * Implements Factor 3: Stateless Processes with RequestContext
 * Implements Factor 4: Structured Outputs for LLM consumption
 */

// Input type interfaces
interface CreateRoadmapInput {
  name: string;
  vision: string;
  timeHorizon: 'quarterly' | 'annual' | 'multi-year';
  owner: string;
  stakeholders?: string[];
}

interface AddRoadmapThemeInput {
  roadmapId: string;
  name: string;
  description: string;
  objectives: string[];
  priority: 'must-have' | 'should-have' | 'nice-to-have';
  startQuarter: string;
  endQuarter: string;
}

interface CreateInitiativeInput {
  roadmapId: string;
  themeId: string;
  title: string;
  description: string;
  estimatedValue: {
    userImpact: 'low' | 'medium' | 'high' | 'critical';
    revenueImpact: number;
    costSavings: number;
    strategicValue: number;
    customerSatisfaction: number;
  };
  estimatedEffort: {
    developmentWeeks: number;
    designWeeks: number;
    qaWeeks: number;
    confidence: 'low' | 'medium' | 'high';
  };
  risks?: Array<{
    description: string;
    likelihood: 'low' | 'medium' | 'high';
    impact: 'low' | 'medium' | 'high';
    mitigation: string;
  }>;
}

interface AddFeatureInput {
  roadmapId: string;
  initiativeId: string;
  name: string;
  description: string;
  businessValue: {
    score: number;
    rationale: string;
    metrics: string[];
  };
  technicalComplexity: 'low' | 'medium' | 'high' | 'very-high';
  targetRelease?: string;
}

interface UpdateFeatureStatusInput {
  featureId: string;
  status: 'proposed' | 'approved' | 'in-progress' | 'completed' | 'cancelled';
}

interface UpdateInitiativeStatusInput {
  initiativeId: string;
  status: 'ideation' | 'validated' | 'scheduled' | 'in-development' | 'launched';
}

interface CreateMilestoneInput {
  roadmapId: string;
  name: string;
  date: string;
  type: 'release' | 'business' | 'technical' | 'regulatory';
  description: string;
  deliverables: string[];
  dependencies?: string[];
}

interface PlanReleaseInput {
  roadmapId: string;
  version: string;
  name: string;
  date: string;
  features: string[];
  themes?: string[];
  goals: string[];
  notes?: string;
}

interface PrioritizeFeaturesInput {
  roadmapId: string;
  method: 'rice' | 'value-effort' | 'moscow' | 'kano' | 'custom';
  weights?: {
    businessValue: number;
    userImpact: number;
    strategicAlignment: number;
    technicalFeasibility: number;
    risk: number;
  };
  scope?: 'all' | 'theme' | 'initiative' | 'unscheduled';
  scopeId?: string;
}

interface GenerateTimelineInput {
  roadmapId: string;
  viewType: 'quarterly' | 'monthly' | 'release' | 'now-next-later';
  startPeriod?: string;
  endPeriod?: string;
  months?: number;
}

/**
 * Create a new product roadmap
 */
const createRoadmapTool = createTool<CreateRoadmapInput, any>({
  name: 'create_roadmap',
  description: 'Create a new product roadmap with vision and strategic timeline',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      name: {
        type: 'string',
        description: 'Name of the product roadmap',
        minLength: 1,
        maxLength: 200
      },
      vision: {
        type: 'string',
        description: 'Product vision statement',
        minLength: 1,
        maxLength: 1000
      },
      timeHorizon: {
        type: 'string',
        enum: ['quarterly', 'annual', 'multi-year'],
        description: 'Planning time horizon'
      },
      owner: {
        type: 'string',
        description: 'Product owner name',
        minLength: 1,
        maxLength: 100
      },
      stakeholders: {
        type: 'array',
        items: { type: 'string', maxLength: 100 },
        description: 'List of stakeholder names',
        maxItems: 20
      }
    },
    required: ['name', 'vision', 'timeHorizon', 'owner'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: CreateRoadmapInput, context: RequestContext) {
    try {
      const roadmapId = `roadmap-${randomUUID()}`;
      const now = Date.now();

      // Check for duplicate roadmap names
      const existingCheck = await context.db.get(
        'SELECT id FROM product_roadmaps WHERE name = ? AND project_id = ?',
        [input.name, context.projectId || 'default']
      );

      if (existingCheck.success && existingCheck.data) {
        return createErrorResult({
          code: 'DUPLICATE_RESOURCE',
          message: 'A roadmap with this name already exists',
          category: 'validation'
        });
      }

      // Insert roadmap into database
      const result = await context.db.run(
        `INSERT INTO product_roadmaps 
         (id, project_id, name, vision, time_horizon, status, owner, stakeholders, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          roadmapId,
          context.projectId || 'default',
          input.name,
          input.vision,
          input.timeHorizon,
          'draft',
          input.owner,
          JSON.stringify(input.stakeholders || []),
          now,
          now
        ]
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to create roadmap',
          details: { error: result.error },
          category: 'system'
        });
      }

      return createSuccessResult({
        roadmap: {
          id: roadmapId,
          name: input.name,
          vision: input.vision,
          timeHorizon: input.timeHorizon,
          status: 'draft',
          owner: input.owner,
          stakeholders: input.stakeholders || []
        },
        message: `Product roadmap "${input.name}" created successfully`,
        nextSteps: [
          'Add strategic themes using add_roadmap_theme',
          'Create initiatives within themes',
          'Add features to initiatives',
          'Set up milestones and releases',
          'Prioritize features using prioritize_features'
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to create roadmap: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Add a strategic theme to a roadmap
 */
const addRoadmapThemeTool = createTool<AddRoadmapThemeInput, any>({
  name: 'add_roadmap_theme',
  description: 'Add a strategic theme to a product roadmap',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      name: {
        type: 'string',
        description: 'Theme name',
        minLength: 1,
        maxLength: 200
      },
      description: {
        type: 'string',
        description: 'Theme description',
        minLength: 1,
        maxLength: 1000
      },
      objectives: {
        type: 'array',
        items: { type: 'string', maxLength: 500 },
        description: 'Strategic objectives',
        minItems: 1,
        maxItems: 10
      },
      priority: {
        type: 'string',
        enum: ['must-have', 'should-have', 'nice-to-have'],
        description: 'Theme priority'
      },
      startQuarter: {
        type: 'string',
        description: 'Start quarter (e.g., Q1 2024)',
        pattern: '^Q[1-4] \\d{4}$'
      },
      endQuarter: {
        type: 'string',
        description: 'End quarter',
        pattern: '^Q[1-4] \\d{4}$'
      }
    },
    required: ['roadmapId', 'name', 'description', 'objectives', 'priority', 'startQuarter', 'endQuarter'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: AddRoadmapThemeInput, context: RequestContext) {
    try {
      // Verify roadmap exists
      const roadmapCheck = await context.db.get(
        'SELECT id FROM product_roadmaps WHERE id = ? AND project_id = ?',
        [input.roadmapId, context.projectId || 'default']
      );

      if (!roadmapCheck.success || !roadmapCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Roadmap not found',
          details: { roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      const themeId = `theme-${randomUUID()}`;
      const now = Date.now();

      // Insert theme
      const result = await context.db.run(
        `INSERT INTO roadmap_themes 
         (id, roadmap_id, name, description, objectives, priority, start_quarter, end_quarter, status, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          themeId,
          input.roadmapId,
          input.name,
          input.description,
          JSON.stringify(input.objectives),
          input.priority,
          input.startQuarter,
          input.endQuarter,
          'planned',
          now,
          now
        ]
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to create theme',
          details: { error: result.error },
          category: 'system'
        });
      }

      return createSuccessResult({
        theme: {
          id: themeId,
          name: input.name,
          description: input.description,
          objectives: input.objectives,
          priority: input.priority,
          timeframe: {
            startQuarter: input.startQuarter,
            endQuarter: input.endQuarter
          },
          status: 'planned'
        },
        message: `Theme "${input.name}" added to roadmap`,
        nextSteps: [
          'Create initiatives within this theme using create_initiative',
          'Track progress through the theme lifecycle'
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to add theme: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Create a product initiative
 */
const createInitiativeTool = createTool<CreateInitiativeInput, any>({
  name: 'create_initiative',
  description: 'Create a product initiative within a theme',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      themeId: {
        type: 'string',
        description: 'ID of the theme',
        pattern: '^theme-[a-f0-9-]+$'
      },
      title: {
        type: 'string',
        description: 'Initiative title',
        minLength: 1,
        maxLength: 200
      },
      description: {
        type: 'string',
        description: 'Initiative description',
        minLength: 1,
        maxLength: 2000
      },
      estimatedValue: {
        type: 'object',
        properties: {
          userImpact: {
            type: 'string',
            enum: ['low', 'medium', 'high', 'critical']
          },
          revenueImpact: {
            type: 'number',
            description: 'Estimated revenue impact',
            minimum: 0
          },
          costSavings: {
            type: 'number',
            description: 'Estimated cost savings',
            minimum: 0
          },
          strategicValue: {
            type: 'integer',
            description: 'Strategic value (1-10)',
            minimum: 1,
            maximum: 10
          },
          customerSatisfaction: {
            type: 'integer',
            description: 'Projected NPS impact',
            minimum: -100,
            maximum: 100
          }
        },
        required: ['userImpact', 'revenueImpact', 'costSavings', 'strategicValue', 'customerSatisfaction'],
        additionalProperties: false
      },
      estimatedEffort: {
        type: 'object',
        properties: {
          developmentWeeks: {
            type: 'number',
            minimum: 0
          },
          designWeeks: {
            type: 'number',
            minimum: 0
          },
          qaWeeks: {
            type: 'number',
            minimum: 0
          },
          confidence: {
            type: 'string',
            enum: ['low', 'medium', 'high']
          }
        },
        required: ['developmentWeeks', 'designWeeks', 'qaWeeks', 'confidence'],
        additionalProperties: false
      },
      risks: {
        type: 'array',
        items: {
          type: 'object',
          properties: {
            description: {
              type: 'string',
              maxLength: 500
            },
            likelihood: {
              type: 'string',
              enum: ['low', 'medium', 'high']
            },
            impact: {
              type: 'string',
              enum: ['low', 'medium', 'high']
            },
            mitigation: {
              type: 'string',
              maxLength: 500
            }
          },
          required: ['description', 'likelihood', 'impact', 'mitigation'],
          additionalProperties: false
        },
        maxItems: 10
      }
    },
    required: ['roadmapId', 'themeId', 'title', 'description', 'estimatedValue', 'estimatedEffort'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: CreateInitiativeInput, context: RequestContext) {
    try {
      // Verify theme exists
      const themeCheck = await context.db.get(
        `SELECT t.id FROM roadmap_themes t 
         JOIN product_roadmaps r ON t.roadmap_id = r.id 
         WHERE t.id = ? AND t.roadmap_id = ? AND r.project_id = ?`,
        [input.themeId, input.roadmapId, context.projectId || 'default']
      );

      if (!themeCheck.success || !themeCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Theme not found in specified roadmap',
          details: { themeId: input.themeId, roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      const initiativeId = `initiative-${randomUUID()}`;
      const now = Date.now();

      // Insert initiative
      const result = await context.db.run(
        `INSERT INTO roadmap_initiatives 
         (id, theme_id, roadmap_id, title, description, status,
          user_impact, revenue_impact, cost_savings, strategic_value, customer_satisfaction,
          development_weeks, design_weeks, qa_weeks, effort_confidence,
          risks, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          initiativeId,
          input.themeId,
          input.roadmapId,
          input.title,
          input.description,
          'ideation',
          input.estimatedValue.userImpact,
          input.estimatedValue.revenueImpact,
          input.estimatedValue.costSavings,
          input.estimatedValue.strategicValue,
          input.estimatedValue.customerSatisfaction,
          input.estimatedEffort.developmentWeeks,
          input.estimatedEffort.designWeeks,
          input.estimatedEffort.qaWeeks,
          input.estimatedEffort.confidence,
          JSON.stringify(input.risks || []),
          now,
          now
        ]
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to create initiative',
          details: { error: result.error },
          category: 'system'
        });
      }

      const totalEffort = input.estimatedEffort.developmentWeeks + 
                         input.estimatedEffort.designWeeks + 
                         input.estimatedEffort.qaWeeks;

      return createSuccessResult({
        initiative: {
          id: initiativeId,
          title: input.title,
          description: input.description,
          status: 'ideation',
          value: input.estimatedValue,
          effort: input.estimatedEffort,
          totalEffortWeeks: totalEffort,
          risks: input.risks || []
        },
        message: `Initiative "${input.title}" created`,
        nextSteps: [
          'Add features to this initiative using add_feature',
          'Validate the initiative with stakeholders',
          'Link to agile epics for implementation'
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to create initiative: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Add a feature to an initiative
 */
const addFeatureTool = createTool<AddFeatureInput, any>({
  name: 'add_feature',
  description: 'Add a feature to an initiative',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      initiativeId: {
        type: 'string',
        description: 'ID of the initiative',
        pattern: '^initiative-[a-f0-9-]+$'
      },
      name: {
        type: 'string',
        description: 'Feature name',
        minLength: 1,
        maxLength: 200
      },
      description: {
        type: 'string',
        description: 'Feature description',
        minLength: 1,
        maxLength: 1000
      },
      businessValue: {
        type: 'object',
        properties: {
          score: {
            type: 'integer',
            description: 'Business value score (1-100)',
            minimum: 1,
            maximum: 100
          },
          rationale: {
            type: 'string',
            description: 'Value rationale',
            maxLength: 1000
          },
          metrics: {
            type: 'array',
            items: { type: 'string', maxLength: 200 },
            description: 'Success metrics',
            minItems: 1,
            maxItems: 10
          }
        },
        required: ['score', 'rationale', 'metrics'],
        additionalProperties: false
      },
      technicalComplexity: {
        type: 'string',
        enum: ['low', 'medium', 'high', 'very-high'],
        description: 'Technical complexity'
      },
      targetRelease: {
        type: 'string',
        description: 'Target release ID (optional)',
        pattern: '^release-[a-f0-9-]+$'
      }
    },
    required: ['roadmapId', 'initiativeId', 'name', 'description', 'businessValue', 'technicalComplexity'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: AddFeatureInput, context: RequestContext) {
    try {
      // Verify initiative exists
      const initiativeCheck = await context.db.get(
        `SELECT i.id FROM roadmap_initiatives i 
         JOIN product_roadmaps r ON i.roadmap_id = r.id 
         WHERE i.id = ? AND i.roadmap_id = ? AND r.project_id = ?`,
        [input.initiativeId, input.roadmapId, context.projectId || 'default']
      );

      if (!initiativeCheck.success || !initiativeCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Initiative not found in specified roadmap',
          details: { initiativeId: input.initiativeId, roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      const featureId = `feature-${randomUUID()}`;
      const now = Date.now();

      // Insert feature
      const result = await context.db.run(
        `INSERT INTO roadmap_features 
         (id, initiative_id, roadmap_id, name, description, status, priority,
          business_value_score, business_value_rationale, business_value_metrics,
          technical_complexity, target_release, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          featureId,
          input.initiativeId,
          input.roadmapId,
          input.name,
          input.description,
          'proposed',
          input.businessValue.score, // Using score as priority
          input.businessValue.score,
          input.businessValue.rationale,
          JSON.stringify(input.businessValue.metrics),
          input.technicalComplexity,
          input.targetRelease || null,
          now,
          now
        ]
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to add feature',
          details: { error: result.error },
          category: 'system'
        });
      }

      return createSuccessResult({
        feature: {
          id: featureId,
          name: input.name,
          description: input.description,
          status: 'proposed',
          businessValue: input.businessValue,
          technicalComplexity: input.technicalComplexity,
          targetRelease: input.targetRelease
        },
        message: `Feature "${input.name}" added`,
        nextSteps: [
          'Prioritize features using prioritize_features',
          'Assign to a release using plan_release',
          'Link to user stories for implementation'
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to add feature: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Update feature status
 */
const updateFeatureStatusTool = createTool<UpdateFeatureStatusInput, any>({
  name: 'update_feature_status',
  description: 'Update the status of a feature',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      featureId: {
        type: 'string',
        description: 'ID of the feature',
        pattern: '^feature-[a-f0-9-]+$'
      },
      status: {
        type: 'string',
        enum: ['proposed', 'approved', 'in-progress', 'completed', 'cancelled'],
        description: 'New status'
      }
    },
    required: ['featureId', 'status'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: UpdateFeatureStatusInput, context: RequestContext) {
    try {
      const result = await context.db.run(
        `UPDATE roadmap_features 
         SET status = ?, updated_at = ? 
         WHERE id = ? AND roadmap_id IN (
           SELECT id FROM product_roadmaps WHERE project_id = ?
         )`,
        [input.status, Date.now(), input.featureId, context.projectId || 'default']
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to update feature status',
          details: { error: result.error },
          category: 'system'
        });
      }

      if (!result.data || result.data.changes === 0) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Feature not found',
          details: { featureId: input.featureId },
          category: 'validation'
        });
      }

      // Get feature details
      const feature = await context.db.get(
        'SELECT name FROM roadmap_features WHERE id = ?',
        [input.featureId]
      );

      return createSuccessResult({
        featureId: input.featureId,
        featureName: feature.data?.name || 'Unknown',
        status: input.status,
        message: `Feature status updated to: ${input.status}`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to update feature status: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Update initiative status
 */
const updateInitiativeStatusTool = createTool<UpdateInitiativeStatusInput, any>({
  name: 'update_initiative_status',
  description: 'Update the status of an initiative',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      initiativeId: {
        type: 'string',
        description: 'ID of the initiative',
        pattern: '^initiative-[a-f0-9-]+$'
      },
      status: {
        type: 'string',
        enum: ['ideation', 'validated', 'scheduled', 'in-development', 'launched'],
        description: 'New status'
      }
    },
    required: ['initiativeId', 'status'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: UpdateInitiativeStatusInput, context: RequestContext) {
    try {
      const result = await context.db.run(
        `UPDATE roadmap_initiatives 
         SET status = ?, updated_at = ? 
         WHERE id = ? AND roadmap_id IN (
           SELECT id FROM product_roadmaps WHERE project_id = ?
         )`,
        [input.status, Date.now(), input.initiativeId, context.projectId || 'default']
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to update initiative status',
          details: { error: result.error },
          category: 'system'
        });
      }

      if (!result.data || result.data.changes === 0) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Initiative not found',
          details: { initiativeId: input.initiativeId },
          category: 'validation'
        });
      }

      // Get initiative details
      const initiative = await context.db.get(
        'SELECT title FROM roadmap_initiatives WHERE id = ?',
        [input.initiativeId]
      );

      return createSuccessResult({
        initiativeId: input.initiativeId,
        initiativeTitle: initiative.data?.title || 'Unknown',
        status: input.status,
        message: `Initiative status updated to: ${input.status}`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to update initiative status: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Create a milestone
 */
const createMilestoneTool = createTool<CreateMilestoneInput, any>({
  name: 'create_milestone',
  description: 'Create a milestone in the roadmap',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      name: {
        type: 'string',
        description: 'Milestone name',
        minLength: 1,
        maxLength: 200
      },
      date: {
        type: 'string',
        format: 'date',
        description: 'Milestone date (ISO format)'
      },
      type: {
        type: 'string',
        enum: ['release', 'business', 'technical', 'regulatory'],
        description: 'Type of milestone'
      },
      description: {
        type: 'string',
        description: 'Milestone description',
        minLength: 1,
        maxLength: 1000
      },
      deliverables: {
        type: 'array',
        items: { type: 'string', maxLength: 200 },
        description: 'List of deliverables',
        minItems: 1,
        maxItems: 20
      },
      dependencies: {
        type: 'array',
        items: { 
          type: 'string',
          pattern: '^milestone-[a-f0-9-]+$'
        },
        description: 'List of dependency IDs',
        maxItems: 10
      }
    },
    required: ['roadmapId', 'name', 'date', 'type', 'description', 'deliverables'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: CreateMilestoneInput, context: RequestContext) {
    try {
      // Verify roadmap exists
      const roadmapCheck = await context.db.get(
        'SELECT id FROM product_roadmaps WHERE id = ? AND project_id = ?',
        [input.roadmapId, context.projectId || 'default']
      );

      if (!roadmapCheck.success || !roadmapCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Roadmap not found',
          details: { roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      const milestoneId = `milestone-${randomUUID()}`;
      const milestoneDate = new Date(input.date);
      const now = Date.now();

      // Insert milestone
      const result = await context.db.run(
        `INSERT INTO roadmap_milestones 
         (id, roadmap_id, name, date, type, description, deliverables, dependencies, status, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          milestoneId,
          input.roadmapId,
          input.name,
          milestoneDate.getTime(),
          input.type,
          input.description,
          JSON.stringify(input.deliverables),
          JSON.stringify(input.dependencies || []),
          'upcoming',
          now,
          now
        ]
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to create milestone',
          details: { error: result.error },
          category: 'system'
        });
      }

      return createSuccessResult({
        milestone: {
          id: milestoneId,
          name: input.name,
          date: input.date,
          type: input.type,
          description: input.description,
          deliverables: input.deliverables,
          dependencies: input.dependencies || [],
          status: 'upcoming'
        },
        message: `Milestone "${input.name}" created`,
        nextSteps: [
          'Track milestone progress',
          'Update status as work progresses',
          'Link features to milestone deliverables'
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to create milestone: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Plan a release
 */
const planReleaseTool = createTool<PlanReleaseInput, any>({
  name: 'plan_release',
  description: 'Plan a product release',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      version: {
        type: 'string',
        description: 'Release version',
        minLength: 1,
        maxLength: 50
      },
      name: {
        type: 'string',
        description: 'Release name',
        minLength: 1,
        maxLength: 200
      },
      date: {
        type: 'string',
        format: 'date',
        description: 'Release date (ISO format)'
      },
      features: {
        type: 'array',
        items: { 
          type: 'string',
          pattern: '^feature-[a-f0-9-]+$'
        },
        description: 'Feature IDs to include',
        minItems: 1,
        maxItems: 100
      },
      themes: {
        type: 'array',
        items: { 
          type: 'string',
          pattern: '^theme-[a-f0-9-]+$'
        },
        description: 'Theme IDs addressed',
        maxItems: 20
      },
      goals: {
        type: 'array',
        items: { type: 'string', maxLength: 500 },
        description: 'Release goals',
        minItems: 1,
        maxItems: 10
      },
      notes: {
        type: 'string',
        description: 'Release notes',
        maxLength: 5000
      }
    },
    required: ['roadmapId', 'version', 'name', 'date', 'features', 'goals'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: PlanReleaseInput, context: RequestContext) {
    try {
      // Verify roadmap exists
      const roadmapCheck = await context.db.get(
        'SELECT id FROM product_roadmaps WHERE id = ? AND project_id = ?',
        [input.roadmapId, context.projectId || 'default']
      );

      if (!roadmapCheck.success || !roadmapCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Roadmap not found',
          details: { roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      // Verify all features exist
      const featurePlaceholders = input.features.map(() => '?').join(',');
      const featureCheck = await context.db.all(
        `SELECT id FROM roadmap_features 
         WHERE id IN (${featurePlaceholders}) AND roadmap_id = ?`,
        [...input.features, input.roadmapId]
      );

      if (!featureCheck.success || featureCheck.data.length !== input.features.length) {
        return createErrorResult({
          code: 'VALIDATION_ERROR',
          message: 'One or more features not found in this roadmap',
          category: 'validation'
        });
      }

      const releaseId = `release-${randomUUID()}`;
      const releaseDate = new Date(input.date);
      const now = Date.now();

      // Insert release
      const result = await context.db.run(
        `INSERT INTO roadmap_releases 
         (id, roadmap_id, version, name, date, features, themes, goals, status, notes, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          releaseId,
          input.roadmapId,
          input.version,
          input.name,
          releaseDate.getTime(),
          JSON.stringify(input.features),
          JSON.stringify(input.themes || []),
          JSON.stringify(input.goals),
          'planning',
          input.notes || null,
          now,
          now
        ]
      );

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to plan release',
          details: { error: result.error },
          category: 'system'
        });
      }

      return createSuccessResult({
        release: {
          id: releaseId,
          version: input.version,
          name: input.name,
          date: input.date,
          featureCount: input.features.length,
          themeCount: (input.themes || []).length,
          goals: input.goals,
          status: 'planning'
        },
        message: `Release v${input.version} planned`,
        nextSteps: [
          'Review release scope with stakeholders',
          'Update feature statuses as development progresses',
          'Prepare release notes and documentation'
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to plan release: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Prioritize features
 */
const prioritizeFeaturesTool = createTool<PrioritizeFeaturesInput, any>({
  name: 'prioritize_features',
  description: 'Prioritize features using various methods (RICE, MoSCoW, Value-Effort, etc.)',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      method: {
        type: 'string',
        enum: ['rice', 'value-effort', 'moscow', 'kano', 'custom'],
        description: 'Prioritization method'
      },
      weights: {
        type: 'object',
        properties: {
          businessValue: { type: 'number', minimum: 0, maximum: 1 },
          userImpact: { type: 'number', minimum: 0, maximum: 1 },
          strategicAlignment: { type: 'number', minimum: 0, maximum: 1 },
          technicalFeasibility: { type: 'number', minimum: 0, maximum: 1 },
          risk: { type: 'number', minimum: 0, maximum: 1 }
        },
        description: 'Custom weights (only for custom method)',
        additionalProperties: false
      },
      scope: {
        type: 'string',
        enum: ['all', 'theme', 'initiative', 'unscheduled'],
        description: 'Scope of prioritization',
        default: 'all'
      },
      scopeId: {
        type: 'string',
        description: 'ID of theme/initiative if scope is specific'
      }
    },
    required: ['roadmapId', 'method'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: PrioritizeFeaturesInput, context: RequestContext) {
    try {
      // Build query based on scope
      let query = `
        SELECT f.*, i.user_impact, i.strategic_value, i.development_weeks
        FROM roadmap_features f
        JOIN roadmap_initiatives i ON f.initiative_id = i.id
        WHERE f.roadmap_id = ?
      `;
      const params: any[] = [input.roadmapId];

      if (input.scope === 'theme' && input.scopeId) {
        query += ' AND i.theme_id = ?';
        params.push(input.scopeId);
      } else if (input.scope === 'initiative' && input.scopeId) {
        query += ' AND f.initiative_id = ?';
        params.push(input.scopeId);
      } else if (input.scope === 'unscheduled') {
        query += ' AND f.target_release IS NULL';
      }

      const features = await context.db.all(query, params);

      if (!features.success || features.data.length === 0) {
        return createErrorResult({
          code: 'NO_DATA',
          message: 'No features found for prioritization',
          category: 'validation'
        });
      }

      // Calculate scores based on method
      const scoredFeatures = features.data.map((feature: any) => {
        let score = 0;
        let rationale = '';

        switch (input.method) {
          case 'rice':
            // RICE = (Reach * Impact * Confidence) / Effort
            const reach = 100; // Default reach
            const impact = feature.user_impact === 'critical' ? 3 : 
                          feature.user_impact === 'high' ? 2 : 
                          feature.user_impact === 'medium' ? 1 : 0.5;
            const confidence = 0.8; // Default confidence
            const effort = feature.development_weeks || 1;
            score = (reach * impact * confidence) / effort;
            rationale = `RICE Score: Reach(${reach}) × Impact(${impact}) × Confidence(${confidence}) ÷ Effort(${effort}w)`;
            break;

          case 'value-effort':
            const value = feature.business_value_score || 50;
            const effortScore = feature.technical_complexity === 'very-high' ? 4 :
                               feature.technical_complexity === 'high' ? 3 :
                               feature.technical_complexity === 'medium' ? 2 : 1;
            score = value / effortScore;
            rationale = `Value(${value}) / Effort(${effortScore})`;
            break;

          case 'moscow':
            // Assign scores based on business value ranges
            if (feature.business_value_score >= 80) {
              score = 4;
              rationale = 'Must Have (80+ value)';
            } else if (feature.business_value_score >= 60) {
              score = 3;
              rationale = 'Should Have (60-79 value)';
            } else if (feature.business_value_score >= 40) {
              score = 2;
              rationale = 'Could Have (40-59 value)';
            } else {
              score = 1;
              rationale = 'Won\'t Have (<40 value)';
            }
            break;

          case 'custom':
            if (!input.weights) {
              score = feature.priority || 50;
              rationale = 'Using default priority';
            } else {
              // Apply custom weights
              const bv = (feature.business_value_score || 50) * (input.weights.businessValue || 0);
              const ui = (feature.user_impact === 'critical' ? 100 : 
                         feature.user_impact === 'high' ? 75 : 
                         feature.user_impact === 'medium' ? 50 : 25) * (input.weights.userImpact || 0);
              const sa = (feature.strategic_value || 5) * 10 * (input.weights.strategicAlignment || 0);
              const tf = (feature.technical_complexity === 'low' ? 100 :
                         feature.technical_complexity === 'medium' ? 75 :
                         feature.technical_complexity === 'high' ? 50 : 25) * (input.weights.technicalFeasibility || 0);
              score = bv + ui + sa + tf;
              rationale = `Custom: BV(${bv.toFixed(1)}) + UI(${ui.toFixed(1)}) + SA(${sa.toFixed(1)}) + TF(${tf.toFixed(1)})`;
            }
            break;

          default:
            score = feature.priority || 50;
            rationale = 'Default priority';
        }

        return {
          featureId: feature.id,
          name: feature.name,
          score,
          rationale,
          currentStatus: feature.status,
          complexity: feature.technical_complexity
        };
      });

      // Sort by score descending
      scoredFeatures.sort((a: any, b: any) => b.score - a.score);

      // Add rank
      scoredFeatures.forEach((f: any, index: number) => {
        f.rank = index + 1;
      });

      // Return top 10 for display
      const topFeatures = scoredFeatures.slice(0, 10);

      return createSuccessResult({
        method: input.method,
        scope: input.scope,
        totalFeatures: scoredFeatures.length,
        topFeatures,
        message: `Prioritized ${scoredFeatures.length} features using ${input.method.toUpperCase()} method`,
        insights: [
          `Highest priority: "${topFeatures[0].name}" (score: ${topFeatures[0].score.toFixed(2)})`,
          `${scoredFeatures.filter((f: any) => f.currentStatus === 'proposed').length} features awaiting approval`,
          `Consider reviewing low-complexity, high-value features for quick wins`
        ]
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to prioritize features: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Generate roadmap timeline
 */
const generateTimelineTool = createTool<GenerateTimelineInput, any>({
  name: 'generate_roadmap_timeline',
  description: 'Generate timeline views of the roadmap',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      },
      viewType: {
        type: 'string',
        enum: ['quarterly', 'monthly', 'release', 'now-next-later'],
        description: 'Type of timeline view'
      },
      startPeriod: {
        type: 'string',
        description: 'Start quarter/month (for quarterly/monthly views)'
      },
      endPeriod: {
        type: 'string',
        description: 'End quarter/month (for quarterly view)'
      },
      months: {
        type: 'integer',
        description: 'Number of months (for monthly view)',
        minimum: 1,
        maximum: 24,
        default: 6
      }
    },
    required: ['roadmapId', 'viewType'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: GenerateTimelineInput, context: RequestContext) {
    try {
      // Verify roadmap exists
      const roadmapCheck = await context.db.get(
        'SELECT * FROM product_roadmaps WHERE id = ? AND project_id = ?',
        [input.roadmapId, context.projectId || 'default']
      );

      if (!roadmapCheck.success || !roadmapCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Roadmap not found',
          details: { roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      const roadmap = roadmapCheck.data;
      let timelineData: any = {};

      switch (input.viewType) {
        case 'quarterly':
          // Get themes for quarterly view
          const themes = await context.db.all(
            `SELECT * FROM roadmap_themes 
             WHERE roadmap_id = ? 
             ORDER BY start_quarter`,
            [input.roadmapId]
          );

          if (themes.success && themes.data) {
            const quarters = new Set<string>();
            themes.data.forEach((theme: any) => {
              quarters.add(theme.start_quarter);
              quarters.add(theme.end_quarter);
            });

            timelineData = {
              type: 'quarterly',
              quarters: Array.from(quarters).sort(),
              items: themes.data.map((theme: any) => ({
                id: theme.id,
                type: 'theme',
                name: theme.name,
                startQuarter: theme.start_quarter,
                endQuarter: theme.end_quarter,
                priority: theme.priority,
                status: theme.status
              }))
            };
          }
          break;

        case 'release':
          // Get releases
          const releases = await context.db.all(
            `SELECT * FROM roadmap_releases 
             WHERE roadmap_id = ? 
             ORDER BY date`,
            [input.roadmapId]
          );

          if (releases.success && releases.data) {
            // Get feature counts for each release
            const releaseFeatures = await Promise.all(
              releases.data.map(async (release: any) => {
                const features = JSON.parse(release.features);
                return {
                  ...release,
                  featureCount: features.length,
                  date: new Date(release.date).toISOString()
                };
              })
            );

            timelineData = {
              type: 'release',
              releases: releaseFeatures.map((r: any) => ({
                id: r.id,
                version: r.version,
                name: r.name,
                date: r.date,
                status: r.status,
                featureCount: r.featureCount,
                goals: JSON.parse(r.goals)
              }))
            };
          }
          break;

        case 'now-next-later':
          // Get all initiatives with their status
          const initiatives = await context.db.all(
            `SELECT i.*, t.name as theme_name, t.priority as theme_priority
             FROM roadmap_initiatives i
             JOIN roadmap_themes t ON i.theme_id = t.id
             WHERE i.roadmap_id = ?`,
            [input.roadmapId]
          );

          if (initiatives.success && initiatives.data) {
            const now = initiatives.data.filter((i: any) => 
              i.status === 'in-development' || i.status === 'validated'
            );
            const next = initiatives.data.filter((i: any) => 
              i.status === 'scheduled'
            );
            const later = initiatives.data.filter((i: any) => 
              i.status === 'ideation'
            );

            timelineData = {
              type: 'now-next-later',
              now: now.map((i: any) => ({
                id: i.id,
                title: i.title,
                theme: i.theme_name,
                priority: i.theme_priority,
                status: i.status,
                userImpact: i.user_impact
              })),
              next: next.map((i: any) => ({
                id: i.id,
                title: i.title,
                theme: i.theme_name,
                priority: i.theme_priority,
                status: i.status,
                userImpact: i.user_impact
              })),
              later: later.map((i: any) => ({
                id: i.id,
                title: i.title,
                theme: i.theme_name,
                priority: i.theme_priority,
                status: i.status,
                userImpact: i.user_impact
              }))
            };
          }
          break;

        default:
          return createErrorResult({
            code: 'NOT_IMPLEMENTED',
            message: `Timeline view '${input.viewType}' not implemented`,
            category: 'validation'
          });
      }

      return createSuccessResult({
        roadmap: {
          id: roadmap.id,
          name: roadmap.name,
          timeHorizon: roadmap.time_horizon
        },
        timeline: timelineData,
        message: `Generated ${input.viewType} timeline view`,
        visualization: `Timeline view ready for visualization`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to generate timeline: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * List roadmaps
 */
const listRoadmapsTool = createTool<{}, any>({
  name: 'list_roadmaps',
  description: 'List all product roadmaps',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {},
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: {}, context: RequestContext) {
    try {
      const roadmaps = await context.db.all(
        `SELECT r.*, 
         (SELECT COUNT(*) FROM roadmap_themes WHERE roadmap_id = r.id) as theme_count,
         (SELECT COUNT(*) FROM roadmap_milestones WHERE roadmap_id = r.id) as milestone_count,
         (SELECT COUNT(*) FROM roadmap_releases WHERE roadmap_id = r.id) as release_count
         FROM product_roadmaps r
         WHERE r.project_id = ?
         ORDER BY r.updated_at DESC`,
        [context.projectId || 'default']
      );

      if (!roadmaps.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to fetch roadmaps',
          details: { error: roadmaps.error },
          category: 'system'
        });
      }

      if (roadmaps.data.length === 0) {
        return createSuccessResult({
          roadmaps: [],
          message: 'No product roadmaps found. Create one using create_roadmap.'
        });
      }

      const roadmapList = roadmaps.data.map((r: any) => ({
        id: r.id,
        name: r.name,
        vision: r.vision,
        timeHorizon: r.time_horizon,
        status: r.status,
        owner: r.owner,
        themes: r.theme_count,
        milestones: r.milestone_count,
        releases: r.release_count,
        updatedAt: new Date(r.updated_at).toISOString()
      }));

      return createSuccessResult({
        roadmaps: roadmapList,
        totalCount: roadmapList.length,
        message: `Found ${roadmapList.length} product roadmap${roadmapList.length !== 1 ? 's' : ''}`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to list roadmaps: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Get roadmap details
 */
const getRoadmapTool = createTool<{ roadmapId: string }, any>({
  name: 'get_roadmap',
  description: 'Get detailed information about a specific roadmap',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      }
    },
    required: ['roadmapId'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: { roadmapId: string }, context: RequestContext) {
    try {
      // Get roadmap
      const roadmap = await context.db.get(
        'SELECT * FROM product_roadmaps WHERE id = ? AND project_id = ?',
        [input.roadmapId, context.projectId || 'default']
      );

      if (!roadmap.success || !roadmap.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Roadmap not found',
          details: { roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      // Get summary statistics
      const stats = await context.db.get(
        `SELECT 
         (SELECT COUNT(*) FROM roadmap_themes WHERE roadmap_id = ?) as themes,
         (SELECT COUNT(*) FROM roadmap_initiatives WHERE roadmap_id = ?) as initiatives,
         (SELECT COUNT(*) FROM roadmap_features WHERE roadmap_id = ?) as features,
         (SELECT COUNT(*) FROM roadmap_milestones WHERE roadmap_id = ?) as milestones,
         (SELECT COUNT(*) FROM roadmap_releases WHERE roadmap_id = ?) as releases`,
        [input.roadmapId, input.roadmapId, input.roadmapId, input.roadmapId, input.roadmapId]
      );

      // Get themes with counts
      const themes = await context.db.all(
        `SELECT t.*,
         (SELECT COUNT(*) FROM roadmap_initiatives WHERE theme_id = t.id) as initiative_count,
         (SELECT COUNT(*) FROM roadmap_features f 
          JOIN roadmap_initiatives i ON f.initiative_id = i.id 
          WHERE i.theme_id = t.id) as feature_count
         FROM roadmap_themes t
         WHERE t.roadmap_id = ?
         ORDER BY t.start_quarter`,
        [input.roadmapId]
      );

      // Get upcoming milestones
      const milestones = await context.db.all(
        `SELECT * FROM roadmap_milestones 
         WHERE roadmap_id = ? AND status = 'upcoming'
         ORDER BY date
         LIMIT 5`,
        [input.roadmapId]
      );

      const roadmapData = {
        id: roadmap.data.id,
        name: roadmap.data.name,
        vision: roadmap.data.vision,
        timeHorizon: roadmap.data.time_horizon,
        status: roadmap.data.status,
        owner: roadmap.data.owner,
        stakeholders: JSON.parse(roadmap.data.stakeholders || '[]'),
        createdAt: new Date(roadmap.data.created_at).toISOString(),
        updatedAt: new Date(roadmap.data.updated_at).toISOString(),
        statistics: stats.data || {},
        themes: themes.success ? themes.data.map((t: any) => ({
          id: t.id,
          name: t.name,
          priority: t.priority,
          status: t.status,
          timeframe: `${t.start_quarter} - ${t.end_quarter}`,
          initiatives: t.initiative_count,
          features: t.feature_count
        })) : [],
        upcomingMilestones: milestones.success ? milestones.data.map((m: any) => ({
          id: m.id,
          name: m.name,
          date: new Date(m.date).toISOString(),
          type: m.type
        })) : []
      };

      return createSuccessResult({
        roadmap: roadmapData,
        message: `Retrieved details for roadmap "${roadmapData.name}"`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to get roadmap: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Get theme details
 */
const getThemeDetailsTool = createTool<{ themeId: string }, any>({
  name: 'get_theme_details',
  description: 'Get detailed information about a theme including initiatives and features',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      themeId: {
        type: 'string',
        description: 'ID of the theme',
        pattern: '^theme-[a-f0-9-]+$'
      }
    },
    required: ['themeId'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: { themeId: string }, context: RequestContext) {
    try {
      // Get theme
      const theme = await context.db.get(
        `SELECT t.* FROM roadmap_themes t
         JOIN product_roadmaps r ON t.roadmap_id = r.id
         WHERE t.id = ? AND r.project_id = ?`,
        [input.themeId, context.projectId || 'default']
      );

      if (!theme.success || !theme.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Theme not found',
          details: { themeId: input.themeId },
          category: 'validation'
        });
      }

      // Get initiatives
      const initiatives = await context.db.all(
        `SELECT i.*,
         (SELECT COUNT(*) FROM roadmap_features WHERE initiative_id = i.id) as feature_count
         FROM roadmap_initiatives i
         WHERE i.theme_id = ?
         ORDER BY i.strategic_value DESC`,
        [input.themeId]
      );

      // Get all features for this theme
      const features = await context.db.all(
        `SELECT f.* FROM roadmap_features f
         JOIN roadmap_initiatives i ON f.initiative_id = i.id
         WHERE i.theme_id = ?
         ORDER BY f.priority DESC`,
        [input.themeId]
      );

      // Calculate metrics
      const metrics = {
        initiativesTotal: initiatives.data?.length || 0,
        initiativesCompleted: initiatives.data?.filter((i: any) => i.status === 'launched').length || 0,
        featuresTotal: features.data?.length || 0,
        featuresCompleted: features.data?.filter((f: any) => f.status === 'completed').length || 0,
        progressPercentage: 0
      };

      if (metrics.featuresTotal > 0) {
        metrics.progressPercentage = Math.round((metrics.featuresCompleted / metrics.featuresTotal) * 100);
      }

      const themeData = {
        theme: {
          id: theme.data.id,
          name: theme.data.name,
          description: theme.data.description,
          objectives: JSON.parse(theme.data.objectives || '[]'),
          priority: theme.data.priority,
          timeframe: {
            startQuarter: theme.data.start_quarter,
            endQuarter: theme.data.end_quarter
          },
          status: theme.data.status,
          metrics
        },
        initiatives: initiatives.data?.map((i: any) => ({
          id: i.id,
          title: i.title,
          status: i.status,
          features: i.feature_count,
          userImpact: i.user_impact,
          revenueImpact: i.revenue_impact,
          strategicValue: i.strategic_value
        })) || [],
        features: features.data?.slice(0, 10).map((f: any) => ({
          id: f.id,
          name: f.name,
          status: f.status,
          businessValue: f.business_value_score,
          complexity: f.technical_complexity
        })) || []
      };

      return createSuccessResult({
        ...themeData,
        message: `Retrieved details for theme "${themeData.theme.name}"`,
        summary: `${metrics.initiativesTotal} initiatives, ${metrics.featuresTotal} features, ${metrics.progressPercentage}% complete`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to get theme details: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Get roadmap health
 */
const getRoadmapHealthTool = createTool<{ roadmapId: string }, any>({
  name: 'get_roadmap_health',
  description: 'Get health assessment and metrics for a roadmap',
  category: 'product-roadmap',
  inputSchema: {
    type: 'object',
    properties: {
      roadmapId: {
        type: 'string',
        description: 'ID of the roadmap',
        pattern: '^roadmap-[a-f0-9-]+$'
      }
    },
    required: ['roadmapId'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: { roadmapId: string }, context: RequestContext) {
    try {
      // Verify roadmap exists
      const roadmapCheck = await context.db.get(
        'SELECT * FROM product_roadmaps WHERE id = ? AND project_id = ?',
        [input.roadmapId, context.projectId || 'default']
      );

      if (!roadmapCheck.success || !roadmapCheck.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Roadmap not found',
          details: { roadmapId: input.roadmapId },
          category: 'validation'
        });
      }

      // Get comprehensive metrics
      const metrics = await context.db.get(
        `SELECT 
         (SELECT COUNT(*) FROM roadmap_features WHERE roadmap_id = ?) as features_planned,
         (SELECT COUNT(*) FROM roadmap_features WHERE roadmap_id = ? AND status = 'completed') as features_completed,
         (SELECT COUNT(*) FROM roadmap_initiatives WHERE roadmap_id = ? AND status IN ('in-development', 'validated')) as initiatives_active,
         (SELECT COUNT(*) FROM roadmap_milestones WHERE roadmap_id = ? AND status = 'completed' AND date <= ?) as milestones_on_time,
         (SELECT COUNT(*) FROM roadmap_milestones WHERE roadmap_id = ? AND date <= ?) as milestones_due`,
        [input.roadmapId, input.roadmapId, input.roadmapId, input.roadmapId, Date.now(), input.roadmapId, Date.now()]
      );

      const metricsData = metrics.data || {};
      
      // Calculate on-time delivery
      const onTimeDelivery = metricsData.milestones_due > 0 
        ? Math.round((metricsData.milestones_on_time / metricsData.milestones_due) * 100)
        : 100;

      // Calculate value delivered (simplified)
      const valueDelivered = metricsData.features_planned > 0
        ? Math.round((metricsData.features_completed / metricsData.features_planned) * 100)
        : 0;

      // Determine velocity trend (simplified - would need historical data)
      const velocityTrend = valueDelivered > 50 ? 'increasing' : 
                           valueDelivered > 25 ? 'stable' : 'decreasing';

      // Assess health
      let health: 'excellent' | 'good' | 'at-risk' | 'critical' = 'good';
      const risks: string[] = [];
      const recommendations: string[] = [];

      if (onTimeDelivery < 50) {
        health = 'critical';
        risks.push('Milestone delivery rate below 50%');
        recommendations.push('Review milestone planning and resource allocation');
      } else if (onTimeDelivery < 75) {
        health = 'at-risk';
        risks.push('Milestone delivery rate below target');
      }

      if (metricsData.initiatives_active === 0) {
        risks.push('No active initiatives');
        recommendations.push('Validate and schedule initiatives for development');
      }

      if (valueDelivered < 25 && metricsData.features_planned > 10) {
        health = health === 'critical' ? 'critical' : 'at-risk';
        risks.push('Low feature completion rate');
        recommendations.push('Review feature scope and prioritization');
      }

      if (velocityTrend === 'decreasing') {
        recommendations.push('Investigate causes of declining velocity');
      }

      // Check for stale themes
      const staleThemes = await context.db.all(
        `SELECT name FROM roadmap_themes 
         WHERE roadmap_id = ? AND status = 'planned' 
         AND created_at < ?`,
        [input.roadmapId, Date.now() - 90 * 24 * 60 * 60 * 1000] // 90 days old
      );

      if (staleThemes.success && staleThemes.data.length > 0) {
        risks.push(`${staleThemes.data.length} themes in planning for over 90 days`);
        recommendations.push('Review and update theme statuses');
      }

      if (risks.length === 0 && onTimeDelivery >= 90 && valueDelivered >= 75) {
        health = 'excellent';
      }

      return createSuccessResult({
        health,
        metrics: {
          featuresPlanned: metricsData.features_planned || 0,
          featuresCompleted: metricsData.features_completed || 0,
          initiativesActive: metricsData.initiatives_active || 0,
          velocityTrend,
          onTimeDelivery,
          valueDelivered
        },
        risks,
        recommendations,
        message: `Roadmap health: ${health.toUpperCase()}`
      });

    } catch (error) {
      return createErrorResult({
        code: 'EXECUTION_ERROR',
        message: `Failed to assess roadmap health: ${error instanceof Error ? error.message : 'Unknown error'}`,
        category: 'execution'
      });
    }
  }
});

/**
 * Setup all product roadmap tools
 */
export async function setupProductRoadmapTools(): Promise<ToolRegistration> {
  return {
    module: 'product-roadmap',
    tools: [
      createRoadmapTool,
      addRoadmapThemeTool,
      createInitiativeTool,
      addFeatureTool,
      updateFeatureStatusTool,
      updateInitiativeStatusTool,
      createMilestoneTool,
      planReleaseTool,
      prioritizeFeaturesTool,
      generateTimelineTool,
      listRoadmapsTool,
      getRoadmapTool,
      getThemeDetailsTool,
      getRoadmapHealthTool
    ]
  };
}