import Database from 'better-sqlite3';
const db = new Database('./code-reasoning/.swarm/memory.db');

// Find SOLID principle patterns
const results = db.prepare(`
  SELECT id, type, pattern_data, confidence
  FROM patterns
  WHERE json_extract(pattern_data, '$.tags') LIKE '%solid%'
  ORDER BY confidence DESC
  LIMIT 5
`).all();

results.forEach(pattern => {
  const data = JSON.parse(pattern.pattern_data);
  console.log(`${data.description}`);
  console.log(`Solution: ${data.solution}`);
  console.log(`Success Rate: ${data.success_rate * 100}%`);
  console.log(`Tags: ${data.tags.join(', ')}`);
  console.log('---');
});

// Find algorithm optimization patterns
const optimizations = db.prepare(`
  SELECT id, type, pattern_data
  FROM patterns
  WHERE type = 'algorithm-optimization'
    AND json_extract(pattern_data, '$.metadata.improvement') IS NOT NULL
  ORDER BY json_extract(pattern_data, '$.success_rate') DESC
  LIMIT 10
`).all();

optimizations.forEach(opt => {
  const data = JSON.parse(opt.pattern_data);
  console.log(`${data.description}`);
  console.log(`Improvement: ${data.metadata.improvement}`);
  console.log(`Before: ${data.metadata.before}`);
  console.log(`After: ${data.metadata.after}`);
});

// Find all patterns that enhance or are enabled by a pattern
function findRelatedPatterns(patternId) {
  const related = db.prepare(`
    SELECT pl.relation, p.pattern_data
    FROM pattern_links pl
    JOIN patterns p ON pl.dst_id = p.id
    WHERE pl.src_id = ?
    ORDER BY pl.weight DESC
  `).all(patternId);

  return related.map(r => ({
    relationship: r.relation,
    pattern: JSON.parse(r.pattern_data)
  }));
}

const related = findRelatedPatterns('pattern-100');
console.log('Related patterns:', related);

// Find JavaScript-specific patterns
const jsPatterns = db.prepare(`
  SELECT pattern_data
  FROM patterns
  WHERE json_extract(pattern_data, '$.tags') LIKE '%javascript%'
  ORDER BY json_extract(pattern_data, '$.success_rate') DESC
`).all();

// Find anti-patterns (low success rate or marked as antiPattern)
const antiPatterns = db.prepare(`
  SELECT pattern_data
  FROM patterns
  WHERE json_extract(pattern_data, '$.success_rate') < 0.8
     OR json_extract(pattern_data, '$.metadata.antiPattern') = 1
  ORDER BY json_extract(pattern_data, '$.success_rate') ASC
`).all();

// Example: Using code-reasoning model with agentic-flow coder agent
import { AgenticFlow } from 'agentic-flow';
import Database from 'better-sqlite3';

const reasoningDB = new Database('./code-reasoning/.swarm/memory.db');
const agent = new AgenticFlow('coder');

async function generateOptimizedCode(task) {
  // 1. Query relevant patterns
  const patterns = reasoningDB.prepare(`
    SELECT pattern_data FROM patterns
    WHERE json_extract(pattern_data, '$.description') LIKE ?
    ORDER BY confidence DESC LIMIT 3
  `).all(`%${task}%`);

  // 2. Build context with best practices
  const context = patterns.map(p => {
    const data = JSON.parse(p.pattern_data);
    return `Pattern: ${data.description}\nSolution: ${data.solution}\nExample: ${JSON.stringify(data.metadata.after)}`;
  }).join('\n\n');

  // 3. Generate code with pattern guidance
  const result = await agent.execute({
    task: `${task}\n\nBest Practices to follow:\n${context}`,
    temperature: 0.7
  });

  return result;
}

// Example usage
const code = await generateOptimizedCode('Create a user authentication API');

async function reviewCode(code, language) {
  // Find relevant anti-patterns
  const antiPatterns = reasoningDB.prepare(`
    SELECT pattern_data FROM patterns
    WHERE json_extract(pattern_data, '$.tags') LIKE ?
      AND json_extract(pattern_data, '$.metadata.antiPattern') = 1
    ORDER BY confidence DESC
  `).all(`%${language}%`);

  const reviewer = new AgenticFlow('reviewer');
  const issues = [];

  for (const pattern of antiPatterns) {
    const data = JSON.parse(pattern.pattern_data);
    if (code.includes(data.metadata.before)) {
      issues.push({
        pattern: data.description,
        suggestion: data.solution,
        example: data.metadata.after
      });
    }
  }

  return {
    issues,
    review: await reviewer.execute({
      task: `Review this ${language} code:\n${code}\n\nKnown issues to check:\n${JSON.stringify(issues)}`
    })
  };
}

{
  "domain": "algorithm-optimization",
  "description": "Database query spam: Same query executed multiple times per request",
  "solution": "Implement request-scoped caching and batch queries with DataLoader",
  "success_rate": 0.95,
  "complexity": "low",
  "tags": ["caching", "database", "n+1", "graphql", "optimization"],
  "metadata": {
    "example": "const DataLoader = require('dataloader');\nconst userLoader = new DataLoader(async (ids) => {\n  const users = await db.query('SELECT * FROM users WHERE id IN (?)', [ids]);\n  return ids.map(id => users.find(u => u.id === id));\n});",
    "frameworks": ["GraphQL", "Apollo", "NestJS"],
    "before": "Multiple individual queries",
    "after": "Single batched query"
  }
}

{
  "domain": "language-specific",
  "description": "Callback hell: Deeply nested async callbacks creating pyramid of doom",
  "solution": "Convert to async/await or Promise chains for linear flow",
  "success_rate": 0.96,
  "complexity": "medium",
  "tags": ["javascript", "async", "promises", "async-await", "refactoring"],
  "metadata": {
    "before": "getUser(userId, (user) => {\n  getOrders(user.id, (orders) => {\n    getOrderDetails(orders[0].id, (details) => {\n      console.log(details);\n    });\n  });\n});",
    "after": "async function fetchOrderDetails(userId) {\n  const user = await getUser(userId);\n  const orders = await getOrders(user.id);\n  const details = await getOrderDetails(orders[0].id);\n  console.log(details);\n}",
    "benefits": ["Linear flow", "Error handling with try/catch", "Readable"]
  }
}

{
  "domain": "design-patterns",
  "description": "Single Responsibility Principle violation: God class handling multiple concerns",
  "solution": "Split class into focused, single-purpose classes with clear responsibilities",
  "success_rate": 0.92,
  "complexity": "medium",
  "tags": ["solid", "srp", "refactoring", "java", "oop"],
  "metadata": {
    "before": "class UserManager {\n  validateUser() {}\n  saveToDatabase() {}\n  sendEmail() {}\n  generateReport() {}\n  logActivity() {}\n}",
    "after": "class UserValidator { validate() {} }\nclass UserRepository { save() {} }\nclass EmailService { send() {} }\nclass ReportGenerator { generate() {} }\nclass ActivityLogger { log() {} }",
    "antiPattern": true
  }
}

-- Find patterns with specific tags
SELECT * FROM patterns
WHERE json_extract(pattern_data, '$.tags') LIKE '%async%'
  AND json_extract(pattern_data, '$.success_rate') > 0.9;

-- Find patterns by complexity
SELECT * FROM patterns
WHERE json_extract(pattern_data, '$.complexity') = 'low'
ORDER BY confidence DESC;

-- Find patterns with code examples
SELECT * FROM patterns
WHERE json_extract(pattern_data, '$.metadata.before') IS NOT NULL
  AND json_extract(pattern_data, '$.metadata.after') IS NOT NULL;

-- Find all patterns that prevent a specific anti-pattern
SELECT p2.*
FROM pattern_links pl
JOIN patterns p2 ON pl.dst_id = p2.id
WHERE pl.src_id = 'pattern-123'
  AND pl.relation = 'prevents';

-- Find pattern chains (A → B → C)
WITH RECURSIVE pattern_chain AS (
  SELECT dst_id, src_id, relation, 1 as depth
  FROM pattern_links
  WHERE src_id = 'pattern-start'
  UNION ALL
  SELECT pl.dst_id, pl.src_id, pl.relation, pc.depth + 1
  FROM pattern_links pl
  JOIN pattern_chain pc ON pl.src_id = pc.dst_id
  WHERE pc.depth < 5
)
SELECT * FROM pattern_chain;