import {Connection} from 'sqlcmd-pg'

interface Package {
  id: number
  name: string
}

interface Statistic {
  package_id?: number
  day: Date
  downloads: number
}

export const db = new Connection({
  host: '127.0.0.1',
  port: 5432,
  database: 'npm-history',
})

// connect db log events to local logger
db.on('log', ev => {
  const args = [ev.format].concat(ev.args)
  logger[ev.level].apply(logger, args)
})

const NPM_EPOCH = new Date('2009-09-29')

/**
Custom-built 'INSERT INTO <table> (<columns>) VALUES (<row1>), (<row2>), ...;'
SQL query for inserting statistic rows. I don't think there are any limits on
the number of parameters you can have in a prepared query.

 .execute((error: Error, rows: Package[]) => {
   if (error) return callback(error)
   if (rows.length > 0) return callback(null, rows[0])
   db.Insert('package')
   .set({name}).returning('*')
   .execute((insertError: Error, insertRows: Package[]) => {
     if (insertError) return callback(insertError)
     return callback(null, insertRows[0])
   })
 })

      getRangeStatistics(name, start, end, (statsError, statistics) => {
        if (statsError) return callback(statsError)

        // 5. save the values we just fetched
        const [sql, args] = buildMultirowInsert(package_row.id, statistics)
        db.executeSQL(sql, args, (sqlError: Error) => {
          if (sqlError) return callback(sqlError)

          // 6. merge local and new statistics for the response
          const total_statistics = statistics.concat(local_statistics).sort((a, b) => a.day.getTime() - b.day.getTime())
          callback(null, total_statistics)
        })
      })

/**
Given a package name, return the full package row from the database, creating
one if needed.
*/
function findOrCreatePackage(name: string, callback: (error: Error, package_row?: Package) => void) {
  db.Select('package')
  .whereEqual({name})
  .execute((error: Error, rows: Package[]) => {
    if (error) return callback(error)
    if (rows.length > 0) return callback(null, rows[0])
    db.Insert('package')
    .set({name}).returning('*')
    .execute((insertError: Error, insertRows: Package[]) => {
      if (insertError) return callback(insertError)
      return callback(null, insertRows[0])
    })
  })
}

export function queryAverageDownloads(start: moment.Moment,
                                      end: moment.Moment,
                                      callback: (error: Error, packages?: {[index: string]: number}) => void) {
  db.Select('package_statistic')
  .add('name', 'AVG(downloads)::int AS average')
  .where('downloads > -1')
  .where('day >= ?', start.toDate())
  .where('day < ?', end.toDate())
  .groupBy('name')
  .orderBy('name')
  .execute((error: Error, rows: {name: string, average: number}[]) => {
    if (error) return callback(error)
    logger.info('averaged downloads for %d packages', rows.length)
    const packages: {[index: string]: number} = {}
    rows.forEach(row => packages[row.name] = row.average)
    callback(null, packages)
  })
}