// Contains numerous pre-written SQL queries
// to update migration and bulk tables
package queries

import (
	"fmt"

	"github.com/lib/pq"
)

type PostgresQueries struct{}

func (q *PostgresQueries) SetTimeout(milliseconds int) string {
	return fmt.Sprintf("SET statement_timeout=%d", milliseconds)
}

// creates the versions table if it does not exist
func (q *PostgresQueries) CreateVersionsTable() string {
	return `CREATE TABLE IF NOT EXISTS iceman_versions (
		version int NOT NULL PRIMARY KEY
	)`
}

// gets the latest version added to the table
func (q *PostgresQueries) GetDataVersion() string {
	return `SELECT MAX(version) FROM iceman_versions`
}

// inserts a new version into the version table
func (q *PostgresQueries) InsertVersion() string {
	return `INSERT INTO iceman_versions (version) VALUES ($1)`
}

// creates the migration table if it does not exist
func (q *PostgresQueries) CreateMigrationsTable() string {
	return `CREATE TABLE IF NOT EXISTS iceman_migrations (
				id varchar NOT NULL PRIMARY KEY,
				name varchar NOT NULL,
				created_at timestamp with time zone NOT NULL,
				applied_at timestamp with time zone NOT NULL
            )`
}

// add entry to table after applying migration
func (q *PostgresQueries) InsertMigration() string {
	return "INSERT INTO iceman_migrations (id, name, created_at, applied_at) VALUES ($1, $2, $3, $4)"
}

// remove entry from table after rolling back migration
func (q *PostgresQueries) DeleteMigration() string {
	return "DELETE FROM iceman_migrations WHERE id = $1"
}

// creates bulk table if it does not exist
func (q *PostgresQueries) CreateBulkTable() string {
	return `CREATE TABLE IF NOT EXISTS iceman_bulk (
				id varchar NOT NULL PRIMARY KEY,
				name varchar NOT NULL,
				executed_at timestamp with time zone NOT NULL,
				complete boolean NOT NULL,
				next_row int NOT NULL
            )`
}

func (q *PostgresQueries) BulkTableV2() string {
	return `ALTER TABLE iceman_bulk ALTER COLUMN next_row TYPE bigint`
}

// create entry after executing new bulk operation
func (q *PostgresQueries) InsertBulk() string {
	return `INSERT INTO iceman_bulk (id, name, executed_at, complete, next_row) 
			VALUES ($1, $2, $3, $4, $5)
	`
}

// update bulk entry progress data
func (q *PostgresQueries) UpdateBulk() string {
	return `UPDATE iceman_bulk SET executed_at = $1, complete = $2, next_row = $3 WHERE id = $4`
}

func (q *PostgresQueries) GetAllIds() string {
	return "SELECT id FROM iceman_migrations"
}

func (q *PostgresQueries) GetAllMigrations() string {
	return "SELECT * FROM iceman_migrations"
}

func (q *PostgresQueries) GetLastApplied() string {
	return `SELECT id FROM iceman_migrations
				WHERE applied_at = (SELECT MAX(applied_at) FROM iceman_migrations)
				ORDER BY created_at DESC LIMIT 1`
}

func (q *PostgresQueries) GetSingleBulk() string {
	return `SELECT id, executed_at, complete, next_row FROM iceman_bulk WHERE id = $1`
}

func (q *PostgresQueries) GetSingleBulkByFilename() string {
	return `SELECT id, executed_at, complete, next_row FROM iceman_bulk WHERE name = $1`
}

func (q *PostgresQueries) GetAllBulks() string {
	return `SELECT id, executed_at, complete, next_row FROM iceman_bulk`
}

func (q *PostgresQueries) FormatArray(elements []interface{}) []interface{} {
	return []interface{}{pq.Array(elements)}
}

func (q *PostgresQueries) SelectIdBetween() string {
	return "SELECT id FROM %v WHERE id >= $1 AND id < $2"
}

func (q *PostgresQueries) AndIdBetween() string {
	return "AND (%v.id >= $1 AND %v.id < $2)"
}

func (q *PostgresQueries) CreatePlaceholders(count int) string {

	comma := ""
	placeholders := ""

	// Not that this is 1-based
	for i := 1; i <= count; i++ {
		placeholders += fmt.Sprintf("%v$%v", comma, i)
		comma = ", "
	}
	return placeholders
}
