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';

/**
 * Issue Tracking Module Tools - 12-Factor MCP Implementation
 * 
 * Provides comprehensive issue tracking with:
 * - Multiple issue types (bug, feature, enhancement, documentation, question)
 * - Priority management and status tracking
 * - Comments and attachments
 * - Labels and module tagging
 * - Related issue tracking
 */

// Input type interfaces
interface CreateIssueInput {
  type: 'bug' | 'feature' | 'enhancement' | 'documentation' | 'question';
  title: string;
  description: string;
  priority: 'critical' | 'high' | 'medium' | 'low';
  labels?: string[];
  affectedModules?: string[];
  assignedTo?: string;
  relatedIssues?: string[];
}

interface UpdateIssueInput {
  issueId: string;
  type?: 'bug' | 'feature' | 'enhancement' | 'documentation' | 'question';
  title?: string;
  description?: string;
  status?: 'open' | 'in-progress' | 'resolved' | 'closed' | 'wont-fix';
  priority?: 'critical' | 'high' | 'medium' | 'low';
  assignedTo?: string | null;
  labels?: string[];
  affectedModules?: string[];
  relatedIssues?: string[];
  resolution?: string;
}

interface AddCommentInput {
  issueId: string;
  content: string;
  type?: 'comment' | 'status-change' | 'assignment' | 'resolution';
}

interface ListIssuesInput {
  type?: Array<'bug' | 'feature' | 'enhancement' | 'documentation' | 'question'>;
  status?: Array<'open' | 'in-progress' | 'resolved' | 'closed' | 'wont-fix'>;
  priority?: Array<'critical' | 'high' | 'medium' | 'low'>;
  assignedTo?: string;
  createdBy?: string;
  labels?: string[];
  affectedModules?: string[];
  limit?: number;
  offset?: number;
}

interface GetIssueInput {
  issueId: string;
}

interface DeleteIssueInput {
  issueId: string;
}

interface IssueMetricsInput {
  dateRange?: {
    startDate?: string;
    endDate?: string;
  };
  groupBy?: 'type' | 'status' | 'priority' | 'assignee' | 'module';
}

interface SearchIssuesInput {
  query: string;
  includeComments?: boolean;
  limit?: number;
}

/**
 * Create a new issue
 */
const createIssueTool = createTool<CreateIssueInput, any>({
  name: 'create_issue',
  description: 'Create a new issue (bug, feature, enhancement, etc.)',
  category: 'issue-tracking',
  inputSchema: {
    type: 'object',
    properties: {
      type: {
        type: 'string',
        enum: ['bug', 'feature', 'enhancement', 'documentation', 'question'],
        description: 'Type of issue'
      },
      title: {
        type: 'string',
        description: 'Issue title',
        minLength: 1,
        maxLength: 200
      },
      description: {
        type: 'string',
        description: 'Detailed description of the issue'
      },
      priority: {
        type: 'string',
        enum: ['critical', 'high', 'medium', 'low'],
        description: 'Issue priority level'
      },
      labels: {
        type: 'array',
        items: { type: 'string' },
        description: 'Labels for categorization',
        maxItems: 20
      },
      affectedModules: {
        type: 'array',
        items: { type: 'string' },
        description: 'Modules affected by this issue',
        maxItems: 10
      },
      assignedTo: {
        type: 'string',
        description: 'User to assign the issue to'
      },
      relatedIssues: {
        type: 'array',
        items: { 
          type: 'string',
          pattern: '^ISSUE-\\d{4}$'
        },
        description: 'Related issue IDs',
        maxItems: 10
      }
    },
    required: ['type', 'title', 'description', 'priority'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: CreateIssueInput, context: RequestContext) {
    try {
      // Generate issue ID
      const countResult = await context.db.get(
        'SELECT COUNT(*) as count FROM issue_tracker_issues WHERE project_id = ?',
        [context.projectId || 'default']
      );
      
      const issueNumber = (countResult.data?.count || 0) + 1;
      const issueId = `ISSUE-${issueNumber.toString().padStart(4, '0')}`;
      const now = Date.now();

      // Create issue
      const result = await context.db.run(
        `INSERT INTO issue_tracker_issues 
         (id, project_id, type, title, description, status, priority, created_by, 
          assigned_to, labels, affected_modules, related_issues, metadata, 
          created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          issueId,
          context.projectId || 'default',
          input.type,
          input.title,
          input.description,
          'open',
          input.priority,
          context.userId || 'system',
          input.assignedTo || null,
          JSON.stringify(input.labels || []),
          JSON.stringify(input.affectedModules || []),
          JSON.stringify(input.relatedIssues || []),
          JSON.stringify({}),
          now,
          now
        ]
      );

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

      // Add creation comment
      await context.db.run(
        `INSERT INTO issue_tracker_comments 
         (id, issue_id, project_id, author, content, type, created_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          randomUUID(),
          issueId,
          context.projectId || 'default',
          context.userId || 'system',
          `Issue created: ${input.title}`,
          'status-change',
          now
        ]
      );

      return createSuccessResult({
        issue: {
          id: issueId,
          type: input.type,
          title: input.title,
          description: input.description,
          status: 'open',
          priority: input.priority,
          createdBy: context.userId || 'system',
          assignedTo: input.assignedTo || null,
          labels: input.labels || [],
          affectedModules: input.affectedModules || [],
          relatedIssues: input.relatedIssues || [],
          createdAt: new Date(now).toISOString(),
          updatedAt: new Date(now).toISOString()
        },
        message: `Issue ${issueId} created successfully`
      });

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

/**
 * Update an existing issue
 */
const updateIssueTool = createTool<UpdateIssueInput, any>({
  name: 'update_issue',
  description: 'Update an existing issue',
  category: 'issue-tracking',
  inputSchema: {
    type: 'object',
    properties: {
      issueId: {
        type: 'string',
        description: 'Issue ID to update',
        pattern: '^ISSUE-\\d{4}$'
      },
      type: {
        type: 'string',
        enum: ['bug', 'feature', 'enhancement', 'documentation', 'question'],
        description: 'Type of issue'
      },
      title: {
        type: 'string',
        description: 'Issue title',
        minLength: 1,
        maxLength: 200
      },
      description: {
        type: 'string',
        description: 'Detailed description'
      },
      status: {
        type: 'string',
        enum: ['open', 'in-progress', 'resolved', 'closed', 'wont-fix'],
        description: 'Issue status'
      },
      priority: {
        type: 'string',
        enum: ['critical', 'high', 'medium', 'low'],
        description: 'Priority level'
      },
      assignedTo: {
        type: ['string', 'null'],
        description: 'User to assign to (null to unassign)'
      },
      labels: {
        type: 'array',
        items: { type: 'string' },
        description: 'Labels for categorization',
        maxItems: 20
      },
      affectedModules: {
        type: 'array',
        items: { type: 'string' },
        description: 'Affected modules',
        maxItems: 10
      },
      relatedIssues: {
        type: 'array',
        items: { 
          type: 'string',
          pattern: '^ISSUE-\\d{4}$'
        },
        description: 'Related issue IDs',
        maxItems: 10
      },
      resolution: {
        type: 'string',
        description: 'Resolution description'
      }
    },
    required: ['issueId'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: UpdateIssueInput, context: RequestContext) {
    try {
      // Check if issue exists
      const issueResult = await context.db.get(
        'SELECT * FROM issue_tracker_issues WHERE id = ? AND project_id = ?',
        [input.issueId, context.projectId || 'default']
      );

      if (!issueResult.success || !issueResult.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Issue not found',
          category: 'validation'
        });
      }

      const oldIssue = issueResult.data;
      const now = Date.now();
      const updates: any[] = [];
      const values: any[] = [];
      const changes: string[] = [];

      // Build update query
      if (input.type !== undefined) {
        updates.push('type = ?');
        values.push(input.type);
        if (input.type !== oldIssue.type) {
          changes.push(`type: ${oldIssue.type} → ${input.type}`);
        }
      }

      if (input.title !== undefined) {
        updates.push('title = ?');
        values.push(input.title);
        if (input.title !== oldIssue.title) {
          changes.push(`title updated`);
        }
      }

      if (input.description !== undefined) {
        updates.push('description = ?');
        values.push(input.description);
        changes.push('description updated');
      }

      if (input.status !== undefined) {
        updates.push('status = ?');
        values.push(input.status);
        if (input.status !== oldIssue.status) {
          changes.push(`status: ${oldIssue.status} → ${input.status}`);
        }
      }

      if (input.priority !== undefined) {
        updates.push('priority = ?');
        values.push(input.priority);
        if (input.priority !== oldIssue.priority) {
          changes.push(`priority: ${oldIssue.priority} → ${input.priority}`);
        }
      }

      if (input.assignedTo !== undefined) {
        updates.push('assigned_to = ?');
        values.push(input.assignedTo);
        const oldAssignee = oldIssue.assigned_to || 'unassigned';
        const newAssignee = input.assignedTo || 'unassigned';
        if (oldAssignee !== newAssignee) {
          changes.push(`assigned to: ${oldAssignee} → ${newAssignee}`);
        }
      }

      if (input.labels !== undefined) {
        updates.push('labels = ?');
        values.push(JSON.stringify(input.labels));
        changes.push('labels updated');
      }

      if (input.affectedModules !== undefined) {
        updates.push('affected_modules = ?');
        values.push(JSON.stringify(input.affectedModules));
        changes.push('affected modules updated');
      }

      if (input.relatedIssues !== undefined) {
        updates.push('related_issues = ?');
        values.push(JSON.stringify(input.relatedIssues));
        changes.push('related issues updated');
      }

      if (input.resolution !== undefined) {
        updates.push('resolution = ?');
        values.push(input.resolution);
        changes.push('resolution added');
      }

      // Handle closed status
      if (input.status === 'closed' || input.status === 'resolved') {
        updates.push('closed_at = ?');
        values.push(now);
      }

      updates.push('updated_at = ?');
      values.push(now);

      if (updates.length === 1) {
        return createErrorResult({
          code: 'VALIDATION_ERROR',
          message: 'No updates provided',
          category: 'validation'
        });
      }

      // Update issue
      values.push(input.issueId, context.projectId || 'default');
      const updateResult = await context.db.run(
        `UPDATE issue_tracker_issues SET ${updates.join(', ')} WHERE id = ? AND project_id = ?`,
        values
      );

      if (!updateResult.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to update issue',
          category: 'system'
        });
      }

      // Add update comment if changes were made
      if (changes.length > 0) {
        await context.db.run(
          `INSERT INTO issue_tracker_comments 
           (id, issue_id, project_id, author, content, type, created_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            randomUUID(),
            input.issueId,
            context.projectId || 'default',
            context.userId || 'system',
            `Issue updated: ${changes.join(', ')}`,
            'status-change',
            now
          ]
        );
      }

      // Get updated issue
      const updatedResult = await context.db.get(
        'SELECT * FROM issue_tracker_issues WHERE id = ? AND project_id = ?',
        [input.issueId, context.projectId || 'default']
      );

      return createSuccessResult({
        issue: {
          id: updatedResult.data.id,
          type: updatedResult.data.type,
          title: updatedResult.data.title,
          description: updatedResult.data.description,
          status: updatedResult.data.status,
          priority: updatedResult.data.priority,
          createdBy: updatedResult.data.created_by,
          assignedTo: updatedResult.data.assigned_to,
          labels: JSON.parse(updatedResult.data.labels || '[]'),
          affectedModules: JSON.parse(updatedResult.data.affected_modules || '[]'),
          relatedIssues: JSON.parse(updatedResult.data.related_issues || '[]'),
          resolution: updatedResult.data.resolution,
          closedAt: updatedResult.data.closed_at ? new Date(updatedResult.data.closed_at).toISOString() : null,
          createdAt: new Date(updatedResult.data.created_at).toISOString(),
          updatedAt: new Date(updatedResult.data.updated_at).toISOString()
        },
        changes,
        message: `Issue ${input.issueId} updated successfully`
      });

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

/**
 * Add a comment to an issue
 */
const addCommentTool = createTool<AddCommentInput, any>({
  name: 'add_issue_comment',
  description: 'Add a comment to an issue',
  category: 'issue-tracking',
  inputSchema: {
    type: 'object',
    properties: {
      issueId: {
        type: 'string',
        description: 'Issue ID to comment on',
        pattern: '^ISSUE-\\d{4}$'
      },
      content: {
        type: 'string',
        description: 'Comment content',
        minLength: 1,
        maxLength: 5000
      },
      type: {
        type: 'string',
        enum: ['comment', 'status-change', 'assignment', 'resolution'],
        description: 'Type of comment',
        default: 'comment'
      }
    },
    required: ['issueId', 'content'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: AddCommentInput, context: RequestContext) {
    try {
      // Verify issue exists
      const issueResult = await context.db.get(
        'SELECT id FROM issue_tracker_issues WHERE id = ? AND project_id = ?',
        [input.issueId, context.projectId || 'default']
      );

      if (!issueResult.success || !issueResult.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Issue not found',
          category: 'validation'
        });
      }

      const commentId = randomUUID();
      const now = Date.now();

      // Add comment
      const result = await context.db.run(
        `INSERT INTO issue_tracker_comments 
         (id, issue_id, project_id, author, content, type, created_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          commentId,
          input.issueId,
          context.projectId || 'default',
          context.userId || 'system',
          input.content,
          input.type || 'comment',
          now
        ]
      );

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

      // Update issue's updated_at timestamp
      await context.db.run(
        'UPDATE issue_tracker_issues SET updated_at = ? WHERE id = ? AND project_id = ?',
        [now, input.issueId, context.projectId || 'default']
      );

      return createSuccessResult({
        comment: {
          id: commentId,
          issueId: input.issueId,
          author: context.userId || 'system',
          content: input.content,
          type: input.type || 'comment',
          createdAt: new Date(now).toISOString()
        },
        message: 'Comment added successfully'
      });

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

/**
 * List issues with filtering
 */
const listIssuesTool = createTool<ListIssuesInput, any>({
  name: 'list_issues',
  description: 'List issues with optional filtering',
  category: 'issue-tracking',
  readOnly: true,
  inputSchema: {
    type: 'object',
    properties: {
      type: {
        type: 'array',
        items: {
          type: 'string',
          enum: ['bug', 'feature', 'enhancement', 'documentation', 'question']
        },
        description: 'Filter by issue types'
      },
      status: {
        type: 'array',
        items: {
          type: 'string',
          enum: ['open', 'in-progress', 'resolved', 'closed', 'wont-fix']
        },
        description: 'Filter by status'
      },
      priority: {
        type: 'array',
        items: {
          type: 'string',
          enum: ['critical', 'high', 'medium', 'low']
        },
        description: 'Filter by priority'
      },
      assignedTo: {
        type: 'string',
        description: 'Filter by assignee'
      },
      createdBy: {
        type: 'string',
        description: 'Filter by creator'
      },
      labels: {
        type: 'array',
        items: { type: 'string' },
        description: 'Filter by labels (any match)'
      },
      affectedModules: {
        type: 'array',
        items: { type: 'string' },
        description: 'Filter by affected modules (any match)'
      },
      limit: {
        type: 'integer',
        description: 'Maximum number of results',
        minimum: 1,
        maximum: 100,
        default: 20
      },
      offset: {
        type: 'integer',
        description: 'Offset for pagination',
        minimum: 0,
        default: 0
      }
    },
    required: [],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: ListIssuesInput, context: RequestContext) {
    try {
      let query = 'SELECT * FROM issue_tracker_issues WHERE project_id = ?';
      const params: any[] = [context.projectId || 'default'];

      // Build filters
      if (input.type && input.type.length > 0) {
        query += ` AND type IN (${input.type.map(() => '?').join(',')})`;
        params.push(...input.type);
      }

      if (input.status && input.status.length > 0) {
        query += ` AND status IN (${input.status.map(() => '?').join(',')})`;
        params.push(...input.status);
      }

      if (input.priority && input.priority.length > 0) {
        query += ` AND priority IN (${input.priority.map(() => '?').join(',')})`;
        params.push(...input.priority);
      }

      if (input.assignedTo) {
        query += ' AND assigned_to = ?';
        params.push(input.assignedTo);
      }

      if (input.createdBy) {
        query += ' AND created_by = ?';
        params.push(input.createdBy);
      }

      // Order by priority and creation date
      query += ' ORDER BY CASE priority WHEN "critical" THEN 1 WHEN "high" THEN 2 WHEN "medium" THEN 3 ELSE 4 END, created_at DESC';
      
      // Add pagination
      query += ' LIMIT ? OFFSET ?';
      params.push(input.limit || 20, input.offset || 0);

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

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to list issues',
          category: 'system'
        });
      }

      const issues = [];
      for (const row of result.data || []) {
        // Apply label and module filters in memory
        const labels = JSON.parse(row.labels || '[]');
        const affectedModules = JSON.parse(row.affected_modules || '[]');

        if (input.labels && input.labels.length > 0) {
          if (!input.labels.some(label => labels.includes(label))) {
            continue;
          }
        }

        if (input.affectedModules && input.affectedModules.length > 0) {
          if (!input.affectedModules.some(module => affectedModules.includes(module))) {
            continue;
          }
        }

        // Get comment count
        const commentResult = await context.db.get(
          'SELECT COUNT(*) as count FROM issue_tracker_comments WHERE issue_id = ?',
          [row.id]
        );

        issues.push({
          id: row.id,
          type: row.type,
          title: row.title,
          description: row.description,
          status: row.status,
          priority: row.priority,
          createdBy: row.created_by,
          assignedTo: row.assigned_to,
          labels,
          affectedModules,
          relatedIssues: JSON.parse(row.related_issues || '[]'),
          resolution: row.resolution,
          commentCount: commentResult.data?.count || 0,
          closedAt: row.closed_at ? new Date(row.closed_at).toISOString() : null,
          createdAt: new Date(row.created_at).toISOString(),
          updatedAt: new Date(row.updated_at).toISOString()
        });
      }

      // Get total count for pagination
      let countQuery = 'SELECT COUNT(*) as count FROM issue_tracker_issues WHERE project_id = ?';
      const countParams: any[] = [context.projectId || 'default'];
      
      if (input.type && input.type.length > 0) {
        countQuery += ` AND type IN (${input.type.map(() => '?').join(',')})`;
        countParams.push(...input.type);
      }

      if (input.status && input.status.length > 0) {
        countQuery += ` AND status IN (${input.status.map(() => '?').join(',')})`;
        countParams.push(...input.status);
      }

      if (input.priority && input.priority.length > 0) {
        countQuery += ` AND priority IN (${input.priority.map(() => '?').join(',')})`;
        countParams.push(...input.priority);
      }

      if (input.assignedTo) {
        countQuery += ' AND assigned_to = ?';
        countParams.push(input.assignedTo);
      }

      if (input.createdBy) {
        countQuery += ' AND created_by = ?';
        countParams.push(input.createdBy);
      }

      const countResult = await context.db.get(countQuery, countParams);

      return createSuccessResult({
        issues,
        pagination: {
          total: countResult.data?.count || 0,
          limit: input.limit || 20,
          offset: input.offset || 0,
          hasMore: (input.offset || 0) + issues.length < (countResult.data?.count || 0)
        }
      });

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

/**
 * Get a specific issue with all details
 */
const getIssueTool = createTool<GetIssueInput, any>({
  name: 'get_issue',
  description: 'Get detailed information about a specific issue',
  category: 'issue-tracking',
  readOnly: true,
  inputSchema: {
    type: 'object',
    properties: {
      issueId: {
        type: 'string',
        description: 'Issue ID to retrieve',
        pattern: '^ISSUE-\\d{4}$'
      }
    },
    required: ['issueId'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: GetIssueInput, context: RequestContext) {
    try {
      // Get issue
      const issueResult = await context.db.get(
        'SELECT * FROM issue_tracker_issues WHERE id = ? AND project_id = ?',
        [input.issueId, context.projectId || 'default']
      );

      if (!issueResult.success || !issueResult.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Issue not found',
          category: 'validation'
        });
      }

      const issue = issueResult.data;

      // Get comments
      const commentsResult = await context.db.query(
        'SELECT * FROM issue_tracker_comments WHERE issue_id = ? ORDER BY created_at ASC',
        [input.issueId]
      );

      const comments = (commentsResult.data || []).map((comment: any) => ({
        id: comment.id,
        issueId: comment.issue_id,
        author: comment.author,
        content: comment.content,
        type: comment.type,
        editedAt: comment.edited_at ? new Date(comment.edited_at).toISOString() : null,
        createdAt: new Date(comment.created_at).toISOString()
      }));

      // Get attachments
      const attachmentsResult = await context.db.query(
        'SELECT * FROM issue_tracker_attachments WHERE issue_id = ? ORDER BY uploaded_at DESC',
        [input.issueId]
      );

      const attachments = (attachmentsResult.data || []).map((attachment: any) => ({
        id: attachment.id,
        filename: attachment.filename,
        path: attachment.path,
        size: attachment.size,
        mimeType: attachment.mime_type,
        uploadedBy: attachment.uploaded_by,
        uploadedAt: new Date(attachment.uploaded_at).toISOString()
      }));

      return createSuccessResult({
        issue: {
          id: issue.id,
          type: issue.type,
          title: issue.title,
          description: issue.description,
          status: issue.status,
          priority: issue.priority,
          createdBy: issue.created_by,
          assignedTo: issue.assigned_to,
          labels: JSON.parse(issue.labels || '[]'),
          affectedModules: JSON.parse(issue.affected_modules || '[]'),
          relatedIssues: JSON.parse(issue.related_issues || '[]'),
          resolution: issue.resolution,
          closedAt: issue.closed_at ? new Date(issue.closed_at).toISOString() : null,
          metadata: JSON.parse(issue.metadata || '{}'),
          createdAt: new Date(issue.created_at).toISOString(),
          updatedAt: new Date(issue.updated_at).toISOString(),
          comments,
          attachments
        }
      });

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

/**
 * Delete an issue
 */
const deleteIssueTool = createTool<DeleteIssueInput, any>({
  name: 'delete_issue',
  description: 'Delete an issue and all its associated data',
  category: 'issue-tracking',
  inputSchema: {
    type: 'object',
    properties: {
      issueId: {
        type: 'string',
        description: 'Issue ID to delete',
        pattern: '^ISSUE-\\d{4}$'
      }
    },
    required: ['issueId'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: DeleteIssueInput, context: RequestContext) {
    try {
      // Check if issue exists
      const issueResult = await context.db.get(
        'SELECT * FROM issue_tracker_issues WHERE id = ? AND project_id = ?',
        [input.issueId, context.projectId || 'default']
      );

      if (!issueResult.success || !issueResult.data) {
        return createErrorResult({
          code: 'RESOURCE_NOT_FOUND',
          message: 'Issue not found',
          category: 'validation'
        });
      }

      // Delete issue (CASCADE will handle comments and attachments)
      const deleteResult = await context.db.run(
        'DELETE FROM issue_tracker_issues WHERE id = ? AND project_id = ?',
        [input.issueId, context.projectId || 'default']
      );

      if (!deleteResult.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to delete issue',
          category: 'system'
        });
      }

      return createSuccessResult({
        message: `Issue ${input.issueId} deleted successfully`,
        deletedIssue: {
          id: issueResult.data.id,
          title: issueResult.data.title,
          type: issueResult.data.type,
          status: issueResult.data.status
        }
      });

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

/**
 * Get issue metrics and statistics
 */
const issueMetricsTool = createTool<IssueMetricsInput, any>({
  name: 'issue_metrics',
  description: 'Get issue metrics and statistics',
  category: 'issue-tracking',
  readOnly: true,
  inputSchema: {
    type: 'object',
    properties: {
      dateRange: {
        type: 'object',
        properties: {
          startDate: {
            type: 'string',
            format: 'date',
            description: 'Start date (YYYY-MM-DD)'
          },
          endDate: {
            type: 'string',
            format: 'date',
            description: 'End date (YYYY-MM-DD)'
          }
        }
      },
      groupBy: {
        type: 'string',
        enum: ['type', 'status', 'priority', 'assignee', 'module'],
        description: 'Group metrics by field'
      }
    },
    required: [],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: IssueMetricsInput, context: RequestContext) {
    try {
      let baseQuery = 'SELECT * FROM issue_tracker_issues WHERE project_id = ?';
      const params: any[] = [context.projectId || 'default'];

      // Apply date range filter
      if (input.dateRange?.startDate) {
        baseQuery += ' AND created_at >= ?';
        params.push(new Date(input.dateRange.startDate).getTime());
      }

      if (input.dateRange?.endDate) {
        baseQuery += ' AND created_at <= ?';
        params.push(new Date(input.dateRange.endDate).getTime());
      }

      const result = await context.db.query(baseQuery, params);

      if (!result.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to get metrics',
          category: 'system'
        });
      }

      const issues = result.data || [];
      
      // Calculate basic metrics
      const totalIssues = issues.length;
      const openIssues = issues.filter((i: any) => ['open', 'in-progress'].includes(i.status)).length;
      const closedIssues = issues.filter((i: any) => ['resolved', 'closed'].includes(i.status)).length;
      const criticalIssues = issues.filter((i: any) => i.priority === 'critical').length;
      
      // Group by requested field
      let groupedMetrics: any = {};
      if (input.groupBy) {
        switch (input.groupBy) {
          case 'type':
            groupedMetrics = issues.reduce((acc: any, issue: any) => {
              acc[issue.type] = (acc[issue.type] || 0) + 1;
              return acc;
            }, {});
            break;
            
          case 'status':
            groupedMetrics = issues.reduce((acc: any, issue: any) => {
              acc[issue.status] = (acc[issue.status] || 0) + 1;
              return acc;
            }, {});
            break;
            
          case 'priority':
            groupedMetrics = issues.reduce((acc: any, issue: any) => {
              acc[issue.priority] = (acc[issue.priority] || 0) + 1;
              return acc;
            }, {});
            break;
            
          case 'assignee':
            groupedMetrics = issues.reduce((acc: any, issue: any) => {
              const assignee = issue.assigned_to || 'unassigned';
              acc[assignee] = (acc[assignee] || 0) + 1;
              return acc;
            }, {});
            break;
            
          case 'module':
            for (const issue of issues) {
              const modules = JSON.parse(issue.affected_modules || '[]');
              for (const module of modules) {
                groupedMetrics[module] = (groupedMetrics[module] || 0) + 1;
              }
            }
            break;
        }
      }

      // Calculate average resolution time for closed issues
      const resolvedIssues = issues.filter((i: any) => 
        i.closed_at && ['resolved', 'closed'].includes(i.status)
      );
      
      let avgResolutionTime = 0;
      if (resolvedIssues.length > 0) {
        const totalTime = resolvedIssues.reduce((sum: number, issue: any) => {
          return sum + (issue.closed_at - issue.created_at);
        }, 0);
        avgResolutionTime = Math.round(totalTime / resolvedIssues.length / (1000 * 60 * 60)); // in hours
      }

      return createSuccessResult({
        metrics: {
          totalIssues,
          openIssues,
          closedIssues,
          criticalIssues,
          avgResolutionTimeHours: avgResolutionTime,
          issuesByType: {
            bug: issues.filter((i: any) => i.type === 'bug').length,
            feature: issues.filter((i: any) => i.type === 'feature').length,
            enhancement: issues.filter((i: any) => i.type === 'enhancement').length,
            documentation: issues.filter((i: any) => i.type === 'documentation').length,
            question: issues.filter((i: any) => i.type === 'question').length
          },
          issuesByPriority: {
            critical: criticalIssues,
            high: issues.filter((i: any) => i.priority === 'high').length,
            medium: issues.filter((i: any) => i.priority === 'medium').length,
            low: issues.filter((i: any) => i.priority === 'low').length
          }
        },
        groupedMetrics: input.groupBy ? groupedMetrics : null,
        dateRange: input.dateRange || null
      });

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

/**
 * Search issues by text
 */
const searchIssuesTool = createTool<SearchIssuesInput, any>({
  name: 'search_issues',
  description: 'Search issues by text in title, description, and optionally comments',
  category: 'issue-tracking',
  readOnly: true,
  inputSchema: {
    type: 'object',
    properties: {
      query: {
        type: 'string',
        description: 'Search query',
        minLength: 2,
        maxLength: 100
      },
      includeComments: {
        type: 'boolean',
        description: 'Include comment content in search',
        default: false
      },
      limit: {
        type: 'integer',
        description: 'Maximum number of results',
        minimum: 1,
        maximum: 50,
        default: 20
      }
    },
    required: ['query'],
    additionalProperties: false
  } as JSONSchema7,

  async execute(input: SearchIssuesInput, context: RequestContext) {
    try {
      const searchPattern = `%${input.query}%`;
      
      // Search in issues
      const issueQuery = `
        SELECT DISTINCT i.* FROM issue_tracker_issues i
        WHERE i.project_id = ?
        AND (i.title LIKE ? OR i.description LIKE ? OR i.id LIKE ?)
        ORDER BY i.created_at DESC
        LIMIT ?
      `;
      
      const issueResult = await context.db.query(
        issueQuery,
        [context.projectId || 'default', searchPattern, searchPattern, searchPattern, input.limit || 20]
      );

      if (!issueResult.success) {
        return createErrorResult({
          code: 'DATABASE_ERROR',
          message: 'Failed to search issues',
          category: 'system'
        });
      }

      const issueIds = new Set((issueResult.data || []).map((i: any) => i.id));

      // Search in comments if requested
      if (input.includeComments) {
        const commentQuery = `
          SELECT DISTINCT i.* FROM issue_tracker_issues i
          JOIN issue_tracker_comments c ON i.id = c.issue_id
          WHERE i.project_id = ?
          AND c.content LIKE ?
          AND i.id NOT IN (${Array.from(issueIds).map(() => '?').join(',') || "''"})
          ORDER BY i.created_at DESC
          LIMIT ?
        `;
        
        const remainingLimit = (input.limit || 20) - issueIds.size;
        if (remainingLimit > 0) {
          const commentResult = await context.db.query(
            commentQuery,
            [context.projectId || 'default', searchPattern, ...Array.from(issueIds), remainingLimit]
          );

          if (commentResult.success && commentResult.data) {
            issueResult.data.push(...commentResult.data);
          }
        }
      }

      const issues = (issueResult.data || []).map((issue: any) => ({
        id: issue.id,
        type: issue.type,
        title: issue.title,
        description: issue.description.substring(0, 200) + (issue.description.length > 200 ? '...' : ''),
        status: issue.status,
        priority: issue.priority,
        createdBy: issue.created_by,
        assignedTo: issue.assigned_to,
        labels: JSON.parse(issue.labels || '[]'),
        affectedModules: JSON.parse(issue.affected_modules || '[]'),
        createdAt: new Date(issue.created_at).toISOString(),
        updatedAt: new Date(issue.updated_at).toISOString()
      }));

      return createSuccessResult({
        issues,
        query: input.query,
        includeComments: input.includeComments || false,
        resultCount: issues.length
      });

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

/**
 * Setup issue tracking tools
 */
export async function setupIssueTrackingTools(): Promise<ToolRegistration> {
  return {
    module: 'issue-tracking',
    tools: [
      createIssueTool,
      updateIssueTool,
      addCommentTool,
      listIssuesTool,
      getIssueTool,
      deleteIssueTool,
      issueMetricsTool,
      searchIssuesTool
    ]
  };
}