import { getDb } from '../db'
import { Context } from '../types/context'
import { GraphQLError } from 'graphql'

interface TimeRange {
  start: Date
  end: Date
}

export async function getAnalyticsRevenue(context: Context, timeRange: TimeRange) {
  if (!context.user) {
    throw new GraphQLError('Authentication required', {
      extensions: { code: 'UNAUTHENTICATED' },
    })
  }

  const db = getDb()
  
  // Get total revenue
  const totalResult = await db.query(`
    SELECT 
      SUM(total) as total,
      currency
    FROM invoices
    WHERE billing_period_start >= $1 AND billing_period_start <= $2
      AND status = 'PAID'
    GROUP BY currency
    ORDER BY total DESC
    LIMIT 1
  `, [timeRange.start, timeRange.end])

  const total = totalResult.rows[0]?.total || 0
  const currency = totalResult.rows[0]?.currency || 'USD'

  // Get revenue by period (daily)
  const periodResult = await db.query(`
    SELECT 
      DATE(billing_period_start) as period,
      SUM(total) as revenue
    FROM invoices
    WHERE billing_period_start >= $1 AND billing_period_start <= $2
      AND status = 'PAID'
    GROUP BY DATE(billing_period_start)
    ORDER BY period ASC
  `, [timeRange.start, timeRange.end])

  // Get revenue by service
  const serviceResult = await db.query(`
    SELECT 
      li.description as service,
      SUM(li.total) as revenue
    FROM invoice_line_items li
    JOIN invoices i ON i.id = li.invoice_id
    WHERE i.billing_period_start >= $1 AND i.billing_period_start <= $2
      AND i.status = 'PAID'
    GROUP BY li.description
    ORDER BY revenue DESC
  `, [timeRange.start, timeRange.end])

  // Calculate growth (compare to previous period)
  const previousStart = new Date(timeRange.start)
  const previousEnd = new Date(timeRange.end)
  const periodDays = Math.ceil((timeRange.end.getTime() - timeRange.start.getTime()) / (1000 * 60 * 60 * 24))
  previousStart.setDate(previousStart.getDate() - periodDays)
  previousEnd.setTime(timeRange.start.getTime())

  const previousResult = await db.query(`
    SELECT SUM(total) as total
    FROM invoices
    WHERE billing_period_start >= $1 AND billing_period_start <= $2
      AND status = 'PAID'
  `, [previousStart, previousEnd])

  const previousTotal = previousResult.rows[0]?.total || 0
  const growth = previousTotal > 0 ? ((total - previousTotal) / previousTotal) * 100 : 0

  const totalByService = serviceResult.rows.reduce((sum, row) => sum + parseFloat(row.revenue), 0)

  return {
    total: parseFloat(total) || 0,
    currency,
    byPeriod: periodResult.rows.map(row => ({
      period: row.period.toISOString().split('T')[0],
      revenue: parseFloat(row.revenue) || 0,
    })),
    byService: serviceResult.rows.map(row => ({
      service: row.service,
      revenue: parseFloat(row.revenue) || 0,
      percentage: totalByService > 0 ? (parseFloat(row.revenue) / totalByService) * 100 : 0,
    })),
    growth,
  }
}

export async function getAnalyticsUsers(context: Context, timeRange: TimeRange) {
  if (!context.user) {
    throw new GraphQLError('Authentication required', {
      extensions: { code: 'UNAUTHENTICATED' },
    })
  }

  const db = getDb()
  
  // Get total users
  const totalResult = await db.query('SELECT COUNT(*) as count FROM users')
  const total = parseInt(totalResult.rows[0]?.count) || 0

  // Get active users (logged in within last 30 days)
  const activeResult = await db.query(`
    SELECT COUNT(DISTINCT user_id) as count
    FROM sessions
    WHERE created_at >= NOW() - INTERVAL '30 days'
  `)
  const active = parseInt(activeResult.rows[0]?.count) || 0

  // Get new users in period
  const newResult = await db.query(`
    SELECT COUNT(*) as count
    FROM users
    WHERE created_at >= $1 AND created_at <= $2
  `, [timeRange.start, timeRange.end])
  const newUsers = parseInt(newResult.rows[0]?.count) || 0

  // Get users by period (daily)
  const periodResult = await db.query(`
    SELECT 
      DATE(created_at) as period,
      COUNT(*) as count
    FROM users
    WHERE created_at >= $1 AND created_at <= $2
    GROUP BY DATE(created_at)
    ORDER BY period ASC
  `, [timeRange.start, timeRange.end])

  // Calculate growth
  const previousStart = new Date(timeRange.start)
  const previousEnd = new Date(timeRange.end)
  const periodDays = Math.ceil((timeRange.end.getTime() - timeRange.start.getTime()) / (1000 * 60 * 60 * 24))
  previousStart.setDate(previousStart.getDate() - periodDays)
  previousEnd.setTime(timeRange.start.getTime())

  const previousResult = await db.query(`
    SELECT COUNT(*) as count
    FROM users
    WHERE created_at >= $1 AND created_at <= $2
  `, [previousStart, previousEnd])

  const previousCount = parseInt(previousResult.rows[0]?.count) || 0
  const growth = previousCount > 0 ? ((newUsers - previousCount) / previousCount) * 100 : 0

  return {
    total,
    active,
    new: newUsers,
    byPeriod: periodResult.rows.map(row => ({
      period: row.period.toISOString().split('T')[0],
      count: parseInt(row.count) || 0,
    })),
    growth,
  }
}

export async function getAnalyticsAPIUsage(context: Context, timeRange: TimeRange) {
  if (!context.user) {
    throw new GraphQLError('Authentication required', {
      extensions: { code: 'UNAUTHENTICATED' },
    })
  }

  const db = getDb()
  
  // Get total requests (from audit logs or API usage table)
  const totalResult = await db.query(`
    SELECT COUNT(*) as count
    FROM audit_logs
    WHERE created_at >= $1 AND created_at <= $2
      AND action LIKE 'api.%'
  `, [timeRange.start, timeRange.end])

  const totalRequests = parseInt(totalResult.rows[0]?.count) || 0

  // Get requests by endpoint
  const endpointResult = await db.query(`
    SELECT 
      SUBSTRING(action FROM 5) as endpoint,
      COUNT(*) as requests,
      SUM(CASE WHEN level = 'ERROR' THEN 1 ELSE 0 END) as errors
    FROM audit_logs
    WHERE created_at >= $1 AND created_at <= $2
      AND action LIKE 'api.%'
    GROUP BY endpoint
    ORDER BY requests DESC
  `, [timeRange.start, timeRange.end])

  // Get requests by period (hourly)
  const periodResult = await db.query(`
    SELECT 
      DATE_TRUNC('hour', created_at) as period,
      COUNT(*) as requests,
      SUM(CASE WHEN level = 'ERROR' THEN 1 ELSE 0 END) as errors
    FROM audit_logs
    WHERE created_at >= $1 AND created_at <= $2
      AND action LIKE 'api.%'
    GROUP BY DATE_TRUNC('hour', created_at)
    ORDER BY period ASC
  `, [timeRange.start, timeRange.end])

  const totalErrors = endpointResult.rows.reduce((sum, row) => sum + parseInt(row.errors), 0)
  const errorRate = totalRequests > 0 ? (totalErrors / totalRequests) * 100 : 0

  return {
    totalRequests,
    byEndpoint: endpointResult.rows.map(row => ({
      endpoint: row.endpoint,
      requests: parseInt(row.requests) || 0,
      errors: parseInt(row.errors) || 0,
    })),
    byPeriod: periodResult.rows.map(row => ({
      period: row.period.toISOString(),
      requests: parseInt(row.requests) || 0,
      errors: parseInt(row.errors) || 0,
    })),
    errorRate,
  }
}

export async function getAnalyticsGrowth(context: Context) {
  if (!context.user) {
    throw new GraphQLError('Authentication required', {
      extensions: { code: 'UNAUTHENTICATED' },
    })
  }

  const db = getDb()
  
  const now = new Date()
  const lastMonth = new Date(now)
  lastMonth.setMonth(lastMonth.getMonth() - 1)
  const twoMonthsAgo = new Date(now)
  twoMonthsAgo.setMonth(twoMonthsAgo.getMonth() - 2)

  // Revenue growth
  const revenueCurrent = await db.query(`
    SELECT SUM(total) as total
    FROM invoices
    WHERE billing_period_start >= $1 AND status = 'PAID'
  `, [lastMonth])

  const revenuePrevious = await db.query(`
    SELECT SUM(total) as total
    FROM invoices
    WHERE billing_period_start >= $2 AND billing_period_start < $1 AND status = 'PAID'
  `, [lastMonth, twoMonthsAgo])

  const revenueCurrentVal = parseFloat(revenueCurrent.rows[0]?.total) || 0
  const revenuePreviousVal = parseFloat(revenuePrevious.rows[0]?.total) || 0
  const revenueGrowth = revenuePreviousVal > 0 
    ? ((revenueCurrentVal - revenuePreviousVal) / revenuePreviousVal) * 100 
    : 0

  // User growth
  const usersCurrent = await db.query(`
    SELECT COUNT(*) as count
    FROM users
    WHERE created_at >= $1
  `, [lastMonth])

  const usersPrevious = await db.query(`
    SELECT COUNT(*) as count
    FROM users
    WHERE created_at >= $2 AND created_at < $1
  `, [lastMonth, twoMonthsAgo])

  const usersCurrentVal = parseInt(usersCurrent.rows[0]?.count) || 0
  const usersPreviousVal = parseInt(usersPrevious.rows[0]?.count) || 0
  const usersGrowth = usersPreviousVal > 0 
    ? ((usersCurrentVal - usersPreviousVal) / usersPreviousVal) * 100 
    : 0

  // API usage growth
  const apiCurrent = await db.query(`
    SELECT COUNT(*) as count
    FROM audit_logs
    WHERE created_at >= $1 AND action LIKE 'api.%'
  `, [lastMonth])

  const apiPrevious = await db.query(`
    SELECT COUNT(*) as count
    FROM audit_logs
    WHERE created_at >= $2 AND created_at < $1 AND action LIKE 'api.%'
  `, [lastMonth, twoMonthsAgo])

  const apiCurrentVal = parseInt(apiCurrent.rows[0]?.count) || 0
  const apiPreviousVal = parseInt(apiPrevious.rows[0]?.count) || 0
  const apiGrowth = apiPreviousVal > 0 
    ? ((apiCurrentVal - apiPreviousVal) / apiPreviousVal) * 100 
    : 0

  return {
    revenue: revenueGrowth,
    users: usersGrowth,
    apiUsage: apiGrowth,
    period: 'month',
  }
}

