// GetRaw runs the provided postgres query and args against this sqlquery's db.
func (q SqlQuery) GetRaw(ctx context.Context, query string, args []interface{}, dest interface{}) error {
	db := sqlx.NewDb(q.DB, "postgres")
	log.WithField(ctx, "sql", query).Info("query sql")
	log.WithField(ctx, "args", args).Debug("query args")

	return db.Get(dest, query, args...)
}

func (d *DB) Open() error {
	conf := pgx.ConnPoolConfig{
		ConnConfig: pgx.ConnConfig{
			Host:     d.config.Host,
			Port:     uint16(d.config.Port),
			User:     d.config.Username,
			Password: d.config.Password,
			Database: d.config.Database,
			Logger:   d.config.Logger,
		},
		MaxConnections: d.config.MaxConnections,
	}

	p, err := pgx.NewConnPool(conf)

	if err != nil {
		return err
	}

	db, err := stdlib.OpenFromConnPool(p)

	if err != nil {
		return err
	}

	dbx := sqlx.NewDb(db, "pgx")
	d.queryer = queryer{impl: dbx}

	return nil
}

func init() {
	var err error
	db, err = sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	db.Exec("DROP TABLE users;")
	dbx = sqlx.NewDb(db, "sqlite3")

	ddl := []string{CreateUserStmt}
	for _, stmt := range ddl {
		_, err = db.Exec(stmt)
		if err != nil {
			panic(err)
		}
	}

	for i := 0; i < 100; i++ {
		user := &User{}
		user.Name = randomdata.FullName(randomdata.RandomGender)
		user.Email = randomdata.Email()
		user.Pass = "pa55word"
		user.Created = time.Now().Unix()
		user.Updated = time.Now().Unix()

		err := InsertUser(db, InsertUserStmt, user)
		if err != nil {
			panic(err)
		}
	}
}

func server(config config, db *sql.DB) *echo.Echo {
	dbx := sqlx.NewDb(db, "postgres")
	exec := echo.New()
	if !config.Production {
		exec.Debug()
	}
	exec.Use(mw.Logger())
	exec.Use(mw.Recover())
	exec.Use(mw.Gzip())

	exec.Get("/status", func(c *echo.Context) error {
		return c.NoContent(http.StatusOK)
	})

	leadHandler := lead.NewHandler(datastores.NewLead(dbx))

	exec.Post("/lead", leadHandler.Create)
	exec.Get("/lead/:hashCode", leadHandler.CountByInvites)

	assetHandler := http.FileServer(rice.MustFindBox("static").HTTPBox())
	exec.Get("/", func(c *echo.Context) error {
		assetHandler.ServeHTTP(c.Response().Writer(), c.Request())
		return nil
	})
	exec.Get("/static/*", func(c *echo.Context) error {
		http.StripPrefix("/static/", assetHandler).
			ServeHTTP(c.Response().Writer(), c.Request())
		return nil
	})

	return exec
}

//Connect connec to the db
func (db *DB) Connect(sqlType, conn string, maxOpenConn int) (err error) {
	db.sqlType = sqlType
	db.connectionString = conn
	rawDB, err := sql.Open(db.sqlType, db.connectionString)
	if err != nil {
		return err
	}
	rawDB.SetMaxOpenConns(maxOpenConn)
	rawDB.SetMaxIdleConns(maxOpenConn)
	db.DB = sqlx.NewDb(rawDB, db.sqlType)

	if db.sqlType == "sqlite3" {
		db.DB.Exec("PRAGMA foreign_keys = ON;")
	}

	for i := 0; i < retryDB; i++ {
		err = db.DB.Ping()
		if err == nil {
			return nil
		}
		time.Sleep(retryDBWait * time.Second)
		log.Info("Retrying db connection... (%s)", err)
	}

	return fmt.Errorf("Failed to connect db")
}

func TestMain(m *testing.M) {
	var err error

	test.db, err = sql.Open("sqlite3", ":memory:")
	panicIfErr(err)

	_, err = test.db.Exec("CREATE TABLE users(a, b UNIQUE)")
	panicIfErr(err)

	_, err = test.db.Exec("INSERT INTO users VALUES(1, 2)")
	panicIfErr(err)

	test.dbx = sqlx.NewDb(test.db, "sqlite3")

	test.dbgorm, err = gorm.Open("sqlite3", ":memory:")
	panicIfErr(err)

	user := &User{"1", "2"}
	test.dbgorm.CreateTable(user)
	test.dbgorm.Create(user)

	err = orm.RunSyncdb("default", true, false)
	panicIfErr(err)
	test.dbbeegoorm = orm.NewOrm()
	beeuser := &BeeUser{A: "1", B: "2"}
	test.dbbeegoorm.Insert(beeuser)

	os.Exit(m.Run())
}

// NewTestDB connects to the default DBMS, creates a new database using testdb,
// and loads the application's schema.
func NewTestDB() (*testdb.TestDB, *sqlx.DB, error) {
	stmts, err := readSQL("../schema.sql")
	if err != nil {
		return nil, nil, err
	}

	tdb, err := testdb.Open(DriverName, DefaultDataSource+" dbname=postgres")
	if err != nil {
		return nil, nil, err
	}

	db, err := sql.Open(DriverName, DefaultDataSource+" dbname="+tdb.Name())
	if err != nil {
		tdb.Close()
		return nil, nil, err
	}

	for _, stmt := range stmts {
		if _, err := db.Exec(stmt); err != nil {
			db.Close()
			tdb.Close()
			return nil, nil, fmt.Errorf("%#v\nExecuting: %s", err, stmt)
		}
	}

	dbx := sqlx.NewDb(db, DriverName)
	dbx.MapperFunc(MapperFunc())

	return tdb, dbx, nil
}

func TestCatalogueServiceTags(t *testing.T) {
	logger = log.NewLogfmtLogger(os.Stderr)
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("an error '%s' was not expected when opening stub database connection", err)
	}
	defer db.Close()
	sqlxDB := sqlx.NewDb(db, "sqlmock")

	var cols []string = []string{"name"}

	mock.ExpectQuery("SELECT name FROM tag").WillReturnRows(sqlmock.NewRows(cols).
		AddRow(tags[0]).
		AddRow(tags[1]).
		AddRow(tags[2]))

	s := NewCatalogueService(sqlxDB, logger)

	have, err := s.Tags()
	if err != nil {
		t.Errorf("Tags(): %v", err)
	}
	if !reflect.DeepEqual(tags, have) {
		t.Errorf("Tags(): want %v, have %v", tags, have)
	}
}

func Server(db *sql.DB) *echo.Echo {
	dbx := sqlx.NewDb(db, "postgres")
	e := echo.New()
	e.Post("/books/:name", createBook(dbx))
	e.Get("/books", listBooks(dbx))
	return e
}

// NewDbMap returns a new DbMap using the db connection and dialect.
func NewDbMap(db *sql.DB, dialect Dialect) *DbMap {
	return &DbMap{
		Db:      db,
		Dialect: dialect,
		Dbx:     sqlx.NewDb(db, dialect.DriverName()),
		mapper:  reflectx.NewMapperFunc("db", sqlx.NameMapper),
	}
}

// Setup the database to be shared across the app.
func (c *DatabaseConfig) InitDB() error {
	vdb, err := c.NewVersionedDB()
	if err != nil {
		return err
	}
	DB = sqlx.NewDb(vdb.DB, database.DEFAULT_DRIVER)
	return nil
}

func NewDB(dsn string) *DB {
	if dsn == "" {
		dsn = "testing:[email protected](localhost:3306)/testing?charset=utf8&parseTime=True"
	}

	sqlxDb := sqlx.NewDb(connect("mysql", dsn), "mysql")
	return &DB{*sqlxDb, newStmtCache()}
}

// NewDatastore returns a new Datastore
func NewDatastore(db *sql.DB) datastore.Datastore {
	dbx := sqlx.NewDb(db, "postgres")
	return struct {
		*Cepstore
	}{
		NewCepstore(dbx),
	}
}

func (q SqlQuery) Get(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
	db := sqlx.NewDb(q.DB, "postgres")
	sql = sql.PlaceholderFormat(sq.Dollar)
	query, args, err := sql.ToSql()

	if err != nil {
		return err
	}
	return db.Get(dest, query, args...)
}

// SelectRaw runs the provided postgres query and args against this sqlquery's db.
func (q SqlQuery) SelectRaw(ctx context.Context, query string, args []interface{}, dest interface{}) error {
	db := sqlx.NewDb(q.DB, "postgres")
	log.WithField(ctx, "sql", query).Info("query sql")
	log.WithField(ctx, "args", args).Debug("query args")

	err := db.Select(dest, query, args...)
	if err != nil {
		err = errors.Wrap(err, 1)
	}
	return err
}

func init() {
	var err error

	dbNative, err = sql.Open("postgres", os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}

	dbSqlx = sqlx.NewDb(dbNative, "postgres")

	fmt.Printf("Connected to database at %s", os.Getenv("DATABASE_URL"))
}

// NewDB instantiates a Connection for a given database/sql connection
func NewDB(db *sql.DB, driverName string) *DB {
	database := sqlx.NewDb(db, driverName)
	conn := &DB{database, &Queryable{database}}
	if driverName == "postgres" {
		pgMustNotAllowEscapeSequence(conn)
		if dat.Strict {
			conn.SQL("SET client_min_messages to 'DEBUG';")
		}
	} else {
		panic("Unsupported driver: " + driverName)
	}
	return conn
}

func (q SqlQuery) Select(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
	db := sqlx.NewDb(q.DB, "postgres")
	sql = sql.PlaceholderFormat(sq.Dollar)
	query, args, err := sql.ToSql()

	if err != nil {
		return err
	}

	log.WithField(ctx, "sql", query).Info("Executing query")

	return db.Select(dest, query, args...)
}

func TestCatalogueServiceGet(t *testing.T) {
	logger = log.NewLogfmtLogger(os.Stderr)
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("an error '%s' was not expected when opening stub database connection", err)
	}
	defer db.Close()
	sqlxDB := sqlx.NewDb(db, "sqlmock")

	var cols []string = []string{"id", "name", "description", "price", "count", "image_url_1", "image_url_2", "tag_name"}

	// (Error) Test Cases 1
	mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols))

	// Test Case 2
	mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols).
		AddRow(s3.ID, s3.Name, s3.Description, s3.Price, s3.Count, s3.ImageURL[0], s3.ImageURL[1], strings.Join(s3.Tags, ",")))

	s := NewCatalogueService(sqlxDB, logger)
	{
		// Error case
		for _, id := range []string{
			"0",
		} {
			want := ErrNotFound
			if _, have := s.Get(id); want != have {
				t.Errorf("Get(%s): want %v, have %v", id, want, have)
			}
		}
	}
	{
		// Success case
		for id, want := range map[string]Sock{
			"3": s3,
		} {
			have, err := s.Get(id)
			if err != nil {
				t.Errorf("Get(%s): %v", id, err)
				continue
			}
			if !reflect.DeepEqual(want, have) {
				t.Errorf("Get(%s): want %s, have %s", id, want.ID, have.ID)
				continue
			}
		}
	}
}

// NewSQLiteDB returns a NewtonDB instance that is backed by an SQLiteDB stored
// in a file.
func NewSQLiteDB(dbPath string) (NewtonDB, error) {
	if dbPath == "" {
		return nil, errors.New("dbPath is empty")
	}

	sdb := &SQLiteNewtonDB{}
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, err
	}
	sdb.db = sqlx.NewDb(db, "sqlite3")

	err = updateSQLiteDBVersion(sdb)
	if err != nil {
		return nil, err
	}

	return sdb, nil
}