// Get executes the query, returning any found results
func (q LedgerStateQuery) Select(ctx context.Context, dest interface{}) error {
	hSql := sq.
		Select("MAX(sequence) as horizonsequence").
		From("history_ledgers")

	scSql := sq.
		Select("MAX(ledgerseq) as stellarcoresequence").
		From("ledgerheaders")

	var result LedgerState

	err := q.Horizon.Get(ctx, hSql, &result)

	if err != nil {
		return err
	}

	err = q.Core.Get(ctx, scSql, &result)

	if err != nil {
		return err
	}

	setOn([]LedgerState{result}, dest)
	return nil
}

func Query(src interface{}, tableName string, values url.Values) (sql string, args []interface{}, err error) {
	builder := squirrel.Select("*").From(tableName)

	// add where clause
	for key, value := range values {
		nBuilder, err := whereValueForKey(builder, src, key, value[0])
		if err != nil {
			return "", []interface{}{}, err
		}
		builder = nBuilder
	}

	// add order if it exists
	if oVal := values.Get(KeyOrder); oVal != "" {
		field, order, err := orderFromValue(src, oVal)
		if err != nil {
			return "", []interface{}{}, err
		}
		builder = builder.OrderBy(field + " " + string(order))
	}

	// add limit and offset
	builder = builder.Limit(uintFromKey(values, KeyLimit, 1000))
	builder = builder.Offset(uintFromKey(values, KeyOffset, 0))

	return builder.ToSql()
}

func SelectAllEquipmentInfo(dbm *gorp.DbMap) ([]EquipmentInfo, error) {
	var results []EquipmentInfo

	q := sq.Select(
		`"EquipmentInfo"."ID"`,
		`"EquipmentInfo"."CertifiedName"`,
		`"RadioAccessTechnology"."Description" AS EquipmentType`,
		`"EquipmentInfo"."Model"`,
		`"EquipmentInfo"."AuthNumber"`,
		`"EquipmentInfo"."RadioType"`,
		`"EquipmentInfo"."IsApplied1421"`,
		`"EquipmentInfo"."AuthDate"`,
		`"EquipmentInfo"."Note"`,
		`"EquipmentInfo"."File"`,
	).From(
		"EquipmentInfo",
	).LeftJoin(
		`"RadioAccessTechnology" ON "EquipmentInfo"."EquipmentType" = "RadioAccessTechnology"."EquipmentType"`,
	)

	sql, _, err := q.ToSql()
	if err != nil {
		return results, err
	}

	if _, err := dbm.Select(&results, sql); err != nil {
		return nil, err
	}

	return results, nil
}

func (p *Place) Read(tx *sql.Tx) error {
	row := sq.Select("*").Where(sq.Eq{"id": p.ID}).RunWith(tx).QueryRow()
	if err := row.Scan(p.Row()...); err != nil {
		return err
	}
	return nil
}

func BenchmarkSquirrelBuilderComplex(b *testing.B) {
	arg_eq1 := squirrel.Eq{"f": 2, "x": "hi"}
	arg_eq2 := map[string]interface{}{"g": 3}
	arg_eq3 := squirrel.Eq{"h": []int{1, 2, 3}}

	b.ResetTimer()
	for n := 0; n < b.N; n++ {
		squirrel.Select("a", "b", "z", "y", "x").
			Distinct().
			From("c").
			Where("d = ? OR e = ?", 1, "wat").
			Where(arg_eq1).
			Where(arg_eq2).
			Where(arg_eq3).
			GroupBy("i").
			GroupBy("ii").
			GroupBy("iii").
			Having("j = k").
			Having("jj = ?", 1).
			Having("jjj = ?", 2).
			OrderBy("l").
			OrderBy("l").
			OrderBy("l").
			Limit(7).
			Offset(8).
			ToSql()
	}
}

//StateFetch fetches the state of the specified resource
func (tx *Transaction) StateFetch(s *schema.Schema, filter transaction.Filter) (state transaction.ResourceState, err error) {
	if !s.StateVersioning() {
		err = fmt.Errorf("Schema %s does not support state versioning.", s.ID)
		return
	}
	cols := makeStateColumns(s)
	q := sq.Select(cols...).From(quote(s.GetDbTableName()))
	q, _ = addFilterToQuery(s, q, filter, true)
	sql, args, err := q.ToSql()
	if err != nil {
		return
	}
	logQuery(sql, args...)
	rows, err := tx.transaction.Queryx(sql, args...)
	if err != nil {
		return
	}
	defer rows.Close()
	if !rows.Next() {
		err = fmt.Errorf("No resource found")
		return
	}
	data := map[string]interface{}{}
	rows.MapScan(data)
	err = decodeState(data, &state)
	return
}

func QueryQuestions(db *sqlx.DB, authorId int, query string, offset int) ([]Question, error) {
	qs := []Question{}
	qry := sq.Select("*").From("questions")

	if authorId != 0 {
		qry = qry.Where("author_id = ?", authorId)
	}
	if query != "" {
		word := fmt.Sprint("%", query, "%")
		qry = qry.Where("(title LIKE ? OR question LIKE ?)", word, word)
	}
	if offset > 0 {
		qry = qry.Offset(uint64(offset))
	}

	qry = qry.OrderBy("created_at DESC")
	qry = qry.PlaceholderFormat(sq.Dollar)
	sql, params, err := qry.ToSql()

	if err != nil {
		return qs, err
	} else {
		err := db.Select(&qs, sql, params...)
		dbErr := dbError(err)
		return qs, dbErr
	}
}

// TransactionsByLedger is a query that loads all rows from `txhistory` where
// ledgerseq matches `Sequence.`
func (q *Q) TransactionsByLedger(dest interface{}, seq int32) error {
	sql := sq.Select("ctxh.*").
		From("txhistory ctxh").
		OrderBy("ctxh.txindex ASC").
		Where("ctxh.ledgerseq = ?", seq)

	return q.Select(dest, sql)
}

func (q SequenceByAddressQuery) Select(ctx context.Context, dest interface{}) error {
	sql := sq.
		Select("seqnum as sequence", "accountid as address").
		From("accounts").
		Where(sq.Eq{"accountid": q.Addresses})

	return q.SqlQuery.Select(ctx, sql, dest)
}

// LedgerHeaderBySequence is a query that loads a single row from the
// `ledgerheaders` table.
func (q *Q) LedgerHeaderBySequence(dest interface{}, seq int32) error {
	sql := sq.Select("clh.*").
		From("ledgerheaders clh").
		Limit(1).
		Where("clh.ledgerseq = ?", seq)

	return q.Get(dest, sql)
}

func (r *DaoResource) builderApplication() *sq.SelectBuilder {
	builder := sq.Select(
		"id", "token", "provider_id", "platform", "provider",
		"name", "attributes", "created_at", "updated_at").
		From(model.TableApplications)

	return &builder
}

// SequencesForAddresses loads the current sequence number for every accountid
// specified in `addys`
func (q *Q) SequencesForAddresses(dest interface{}, addys []string) error {
	sql := sq.
		Select("seqnum as sequence", "accountid as address").
		From("accounts").
		Where(sq.Eq{"accountid": addys})

	return q.Select(dest, sql)
}

// TransactionByHash is a query that loads a single row from the `txhistory`.
func (q *Q) TransactionByHash(dest interface{}, hash string) error {
	sql := sq.Select("ctxh.*").
		From("txhistory ctxh").
		Limit(1).
		Where("ctxh.txid = ?", hash)

	return q.Get(dest, sql)
}

func ValidateToken(s gorp.SqlExecutor, memberID int64, token string) error {
	query := squirrel.Select("*").From(TableNameToken).
		Where(squirrel.Eq{"MemberID": memberID, "Value": token})
	tokens := []*Token{}
	sqlutil.Select(s, query, &tokens)
	if len(tokens) == 0 {
		return fmt.Errorf("token not found")
	}
	return nil
}

func GetID(dbmap *gorp.DbMap, m CrudResource, id interface{}) error {
	query := squirrel.Select("*").
		From(m.TableName()).
		Where(squirrel.Eq{"ID": id})
	if err := sqlutil.SelectOne(dbmap, query, m); err != nil {
		message := fmt.Sprintf("Could not find %s.", m.TableName())
		return httperr.New(http.StatusNotFound, message, err)
	}
	return nil
}

func (r *DaoResource) GetEndpointByToken(token string) (*model.Endpoint, error) {
	var entity model.Endpoint

	q := sq.Select("id", "token", "provider_id", "application_id", "attributes", "created_at", "updated_at", "enabled").
		From(model.TableEndpoints).
		Where("token = ?", token)

	err := r.Storage.SelectOne(&entity, &q)
	return &entity, err
}

func GetAllProfiles(user_id int, timestamp int64) []profile.Profile {
	query := squirrel.Select(`
		id, user_id, timestamp,
		agent, level, ap,
		unique_portals_visited, portals_discovered, xm_collected,
		hacks, resonators_deployed, links_created, control_fields_created, mind_units_captured,
		longest_link_ever_created, largest_control_field, xm_recharged, portals_captured,
		unique_portals_captured, mods_deployed,
		resonators_destroyed, portals_neutralized, enemy_links_destroyed, enemy_control_fields_destroyed,
		distance_walked,
		max_time_portal_held, max_time_link_maintained, max_link_length_x_days, max_time_field_held,
		largest_field_mus_x_days,
		glyph_hack_points, consecutive_days_hacking,
		unique_missions_completed,
		agents_successfully_recruited,
		innovator
	`).From("tiers_profiles").OrderBy("timestamp ASC")

	query = query.Where(
		squirrel.And{
			squirrel.Eq{"user_id": user_id},
			squirrel.Expr("timestamp >= ?", timestamp),
		},
	)

	// XXX: Handle errors.
	rows, _ := query.RunWith(db).Query()
	defer rows.Close()

	var profiles []profile.Profile
	for rows.Next() {
		var p profile.Profile

		rows.Scan(
			&p.Id, &p.UserId, &p.Timestamp,
			&p.Nick, &p.Level, &p.AP,
			&p.UniquePortalsVisited, &p.PortalsDiscovered, &p.XMCollected,
			&p.Hacks, &p.ResonatorsDeployed, &p.LinksCreated, &p.ControlFieldsCreated, &p.MindUnitsCaptured,
			&p.LongestLinkEverCreated, &p.LargestControlField, &p.XMRecharged, &p.PortalsCaptured,
			&p.UniquePortalsCaptured, &p.ModsDeployed,
			&p.ResonatorsDestroyed, &p.PortalsNeutralized, &p.EnemyLinksDestroyed, &p.EnemyControlFieldsDestroyed,
			&p.DistanceWalked,
			&p.MaxTimePortalHeld, &p.MaxTimeLinkMaintained, &p.MaxLinkLengthXDays, &p.MaxTimeFieldHeld,
			&p.LargestFieldMUsXDays,
			&p.GlyphHackPoints, &p.ConsecutiveDaysHacking,
			&p.UniqueMissionsCompleted,
			&p.AgentsSuccessfullyRecruited,
			&p.InnovatorLevel,
		)

		profiles = append(profiles, p)
	}

	return profiles
}

func (q ConnectedAssetsQuery) Select(ctx context.Context, dest interface{}) error {

	assets, ok := dest.(*[]xdr.Asset)
	if !ok {
		return errors.New("dest is not *[]xdr.Asset")
	}

	var (
		t xdr.AssetType
		c string
		i string
	)

	err := q.SellingAsset.Extract(&t, &c, &i)
	if err != nil {
		return err
	}

	sql := sq.Select(
		"buyingassettype AS type",
		"coalesce(buyingassetcode, '') AS code",
		"coalesce(buyingissuer, '') AS issuer").
		From("offers").
		Where(sq.Eq{"sellingassettype": t}).
		GroupBy("buyingassettype", "buyingassetcode", "buyingissuer")

	if t != xdr.AssetTypeAssetTypeNative {
		sql = sql.Where(sq.Eq{"sellingassetcode": c, "sellingissuer": i})
	}

	var rows []struct {
		Type   xdr.AssetType
		Code   string
		Issuer string
	}

	err = q.SqlQuery.Select(ctx, sql, &rows)

	if err != nil {
		return err
	}

	results := make([]xdr.Asset, len(rows))
	*assets = results

	for i, r := range rows {
		results[i], err = assetFromDB(r.Type, r.Code, r.Issuer)
		if err != nil {
			return err
		}
	}

	return nil
}

func FindUsers() ([]User, error) {
	qb := squirrel.Select("*").From("user")

	var users []User
	err := db.LoadCollection(&qb, &users)
	if err != nil {
		return nil, err
	}

	return users, nil
}

func (*Loader) FindByIdInTable(tableName string, id int, buf interface{}) error {
	qb := squirrel.Select("*").
		From(tableName).
		Where("id = ?", id)

	err := LoadStruct(&qb, buf)
	if err != nil {
		return err
	}

	return nil
}