import { Hono } from 'hono'
import { requireIdentity } from '../middleware/auth.js'
import { DB } from '../db.js'

const app = new Hono()

function num(value) {
  const n = Number(value ?? 0)
  return Number.isFinite(n) ? n : 0
}

function round(value, digits = 1) {
  const n = Number(value)
  if (!Number.isFinite(n)) return 0
  const m = 10 ** digits
  return Math.round(n * m) / m
}

function rowDomainSql() {
  return `CASE
    WHEN s.node_path IS NULL OR s.node_path = '' THEN '未归类'
    WHEN instr(s.node_path, ' / ') > 0 THEN substr(s.node_path, 1, instr(s.node_path, ' / ') - 1)
    ELSE s.node_path
  END`
}

const SKILL_METRICS_CTE = `
WITH skill_metrics AS (
  SELECT
    s.id,
    s.name,
    s.description,
    s.node_id,
    s.node_path,
    s.status,
    s.creator_name,
    s.package_key,
    COALESCE(s.download_count, 0) AS downloads,
    COALESCE(s.like_count, 0) AS likes,
    COALESCE(s.dislike_count, 0) AS dislikes,
    ${rowDomainSql()} AS domain,
    COUNT(i.id) AS api_calls,
    SUM(CASE WHEN i.response_status = 'success' THEN 1 ELSE 0 END) AS success_calls,
    SUM(CASE WHEN i.id IS NOT NULL AND i.response_status != 'success' THEN 1 ELSE 0 END) AS failed_calls,
    AVG(i.latency_ms) AS avg_latency_ms,
    (
      COALESCE(s.download_count, 0) * 3 +
      COALESCE(s.like_count, 0) * 2 -
      COALESCE(s.dislike_count, 0) +
      COUNT(i.id) * 5 +
      SUM(CASE WHEN i.response_status = 'success' THEN 1 ELSE 0 END) * 2
    ) AS hot_score
  FROM skills s
  LEFT JOIN skill_invocations i ON i.skill_id = s.id
  GROUP BY s.id
)
`

function normalizeDomain(row) {
  const apiCalls = num(row.api_calls)
  const successCalls = num(row.success_calls)
  return {
    domain: row.domain || '未归类',
    totalSkills: num(row.total_skills),
    publishedSkills: num(row.published_skills),
    downloads: num(row.downloads),
    likes: num(row.likes),
    dislikes: num(row.dislikes),
    apiCalls,
    successCalls,
    failedCalls: num(row.failed_calls),
    successRate: apiCalls ? round((successCalls / apiCalls) * 100, 1) : 100,
    avgLatencyMs: round(row.avg_latency_ms, 0),
    hotScore: num(row.hot_score),
  }
}

function normalizeSkill(row) {
  const apiCalls = num(row.api_calls)
  const successCalls = num(row.success_calls)
  return {
    id: row.id,
    name: row.name,
    description: row.description || '',
    domain: row.domain || '未归类',
    nodePath: row.node_path || '',
    status: row.status || 'draft',
    creatorName: row.creator_name || '',
    hasPackage: !!row.package_key,
    downloads: num(row.downloads),
    likes: num(row.likes),
    dislikes: num(row.dislikes),
    apiCalls,
    successCalls,
    failedCalls: num(row.failed_calls),
    successRate: apiCalls ? round((successCalls / apiCalls) * 100, 1) : 100,
    avgLatencyMs: round(row.avg_latency_ms, 0),
    hotScore: num(row.hot_score),
  }
}

app.get('/rankings', async (c) => {
  const unauth = requireIdentity(c)
  if (unauth) return unauth

  const limit = Math.min(50, Math.max(5, Number(c.req.query('limit') || 20)))

  const summary = await DB.prepare(`
    SELECT
      COUNT(*) AS total_skills,
      SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published_skills,
      SUM(COALESCE(download_count, 0)) AS downloads,
      SUM(COALESCE(like_count, 0)) AS likes,
      SUM(COALESCE(dislike_count, 0)) AS dislikes
    FROM skills
  `).first()

  const invSummary = await DB.prepare(`
    SELECT
      COUNT(*) AS api_calls,
      SUM(CASE WHEN response_status = 'success' THEN 1 ELSE 0 END) AS success_calls,
      SUM(CASE WHEN response_status != 'success' THEN 1 ELSE 0 END) AS failed_calls,
      AVG(latency_ms) AS avg_latency_ms
    FROM skill_invocations
  `).first()

  const { results: domains } = await DB.prepare(`${SKILL_METRICS_CTE}
    SELECT
      domain,
      COUNT(*) AS total_skills,
      SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published_skills,
      SUM(downloads) AS downloads,
      SUM(likes) AS likes,
      SUM(dislikes) AS dislikes,
      SUM(api_calls) AS api_calls,
      SUM(success_calls) AS success_calls,
      SUM(failed_calls) AS failed_calls,
      AVG(avg_latency_ms) AS avg_latency_ms,
      SUM(hot_score) AS hot_score
    FROM skill_metrics
    GROUP BY domain
    ORDER BY hot_score DESC, api_calls DESC, downloads DESC, total_skills DESC
    LIMIT ?
  `).bind(limit).all()

  const { results: topSkills } = await DB.prepare(`${SKILL_METRICS_CTE}
    SELECT *
    FROM skill_metrics
    ORDER BY hot_score DESC, api_calls DESC, downloads DESC, likes DESC
    LIMIT ?
  `).bind(limit).all()

  const apiCalls = num(invSummary?.api_calls)
  const successCalls = num(invSummary?.success_calls)
  return c.json({
    summary: {
      totalSkills: num(summary?.total_skills),
      publishedSkills: num(summary?.published_skills),
      downloads: num(summary?.downloads),
      likes: num(summary?.likes),
      dislikes: num(summary?.dislikes),
      apiCalls,
      successCalls,
      failedCalls: num(invSummary?.failed_calls),
      successRate: apiCalls ? round((successCalls / apiCalls) * 100, 1) : 100,
      avgLatencyMs: round(invSummary?.avg_latency_ms, 0),
    },
    domains: (domains || []).map(normalizeDomain),
    topSkills: (topSkills || []).map(normalizeSkill),
  })
})

export default app
