func main() {
	db, err := ql.OpenMem()
	if err != nil {
		panic(err)
	}
	_, _, err = db.Run(ql.NewRWCtx(), `
        BEGIN TRANSACTION;
        CREATE TABLE data (t time, h uint, bps float);
        COMMIT;
        `)
	if err != nil {
		panic(err)
	}
	for _, date := range listDates() {
		addToDB(date, db)
	}
	rss, _, err := db.Run(ql.NewRWCtx(), "SELECT h, min(bps),avg(bps),max(bps), count(t) FROM data GROUP BY h;")
	if err != nil {
		panic(err)
	}

	for _, rs := range rss {
		if err := rs.Do(false, func(data []interface{}) (bool, error) {
			fmt.Printf("hour %2d, min %4.1f, avg %4.1f, max %4.1f, count %3d\n", data[0], data[1], data[2], data[3], data[4])
			return true, nil
		}); err != nil {
			panic(err)
		}
		fmt.Println("----")
	}
}

func (mb *Mailbox) GetMessage() (*Message, error) {
	db, err := OpenDB()
	if db != nil {
		defer db.Close()
	}
	if err != nil {
		return nil, err
	}

	rss, _, err := db.Run(ql.NewRWCtx(), `
		SELECT id, receiveCount, body, mailbox, createdAt
		FROM message
		WHERE mailbox == $1
		LIMIT 1;
		`, mb.Id)
	var msg Message
	if len(rss) > 0 {
		r, _ := rss[0].FirstRow()
		if r == nil {
			return nil, nil
		}
		rss[0].Do(false, func(data []interface{}) (bool, error) {
			msg = Message{
				Id:           data[0].(string),
				ReceiveCount: data[1].(int64),
				Body:         data[2].(string),
				MailboxId:    data[3].(string),
				CreatedAt:    data[4].(time.Time),
			}
			return false, nil
		})
	} else {
		return nil, nil
	}

	if err != nil {
		return nil, err
	}

	msg.ReceiveCount++

	_, _, err = db.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		UPDATE message
		SET receiveCount = $1
		WHERE id == $2;
		COMMIT;
	`, msg.ReceiveCount, msg.Id)
	if err != nil {
		return nil, err
	}
	return &msg, err
}

func (key *AccessKey) Create() error {
	if key.FullAccess == false {
		mb, err := Find(key.MailboxId)
		if err != nil {
			return err
		}
		if mb == nil {
			return errors.New("Can't generate key. Mailbox not found.")
		}
		key.Name = key.MailboxId
	} else {
		if key.Name == "" {
			key.Name = GenerateIdentifier()
		}
	}
	if KeyExists(key.Name) {
		return errors.New("Key already exists")
	}
	key.Secret = GenerateIdentifier()
	_, _, err := DB.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		INSERT INTO accessToken (
			mailbox, token, fullAccess, name
		) VALUES (
			$1, $2, $3, $4
		);
		COMMIT;
	`, key.MailboxId, key.Secret, key.FullAccess, key.Name)
	return err
}

// All returns a slice of all mailboxes.
func All() ([]Mailbox, error) {
	var mbxs []Mailbox
	rss, _, err := DB.Run(ql.NewRWCtx(), `
		SELECT id, lastCheckedInAt, version, host FROM mailbox`)
	if err != nil {
		return nil, err
	}
	rss[0].Do(false, func(data []interface{}) (bool, error) {
		mb := Mailbox{
			Id: data[0].(string),
		}
		if data[1] != nil {
			mb.LastSeen = data[1].(time.Time)
		}
		if data[2] != nil {
			mb.Version = data[2].(string)
		}
		if data[3] != nil {
			mb.Host = data[3].(string)
		}
		mbxs = append(mbxs, mb)
		return true, nil
	})
	return mbxs, nil
}

// DeployMessage accepts a deployment identifier and adds this mailbox to its
// deployment. A new message will be available for this deployment in the
// mailbox.
func (mb *Mailbox) DeployMessage(depId string) (*Message, error) {
	deployment, err := FindDeployment(depId)
	if err != nil {
		return nil, err
	}

	if deployment == nil {
		return nil, errors.New(fmt.Sprintf("Deployment %s not found", depId))
	}

	msg := &Message{
		Id:         GenerateIdentifier(),
		Mailbox:    mb.Id,
		CreatedAt:  time.Now(),
		Deployment: deployment.Id,
		Body:       deployment.MessageBody,
	}

	_, _, err = DB.Run(ql.NewRWCtx(), `
			BEGIN TRANSACTION;
			INSERT INTO message (
				id, receiveCount, mailbox, createdAt, deployment, deleted
			) VALUES (
				$1, $2, $3, $4, $5, false
			);
			UPDATE deployment
			SET totalMessages = totalMessages + 1
			WHERE id == $5;
			COMMIT;
		`, msg.Id, msg.ReceiveCount, msg.Mailbox, msg.CreatedAt,
		deployment.Id)
	return msg, err
}

func addToDB(date string, db *ql.DB) {
	csvfile, err := os.Open(DATA_DIR + date + "-iperf.csv")
	checkErr(err)

	defer csvfile.Close()

	reader := csv.NewReader(csvfile)

	reader.FieldsPerRecord = -1 // see the Reader struct information below

	rawCSVdata, err := reader.ReadAll()
	checkErr(err)

	// sanity check, display to standard output
	for _, each := range rawCSVdata {
		bps, err := strconv.ParseUint(each[len(each)-1], 10, 64)
		if err != nil {
			fmt.Printf("err parsing bsb: %v in line %v\n", err, each)
			bps = 0
		}
		bpsf := float64(bps) / 1000000.0
		t, err := strconv.ParseInt(each[len(each)-2], 10, 64)
		if err != nil {
			fmt.Printf("err parsing time: %v in line %v\n", err, each)
			continue
		}
		tm := time.Unix(t, 0)
		_, _, err = db.Run(ql.NewRWCtx(), fmt.Sprintf("BEGIN TRANSACTION;INSERT INTO data VALUES(parseTime(\"2006-01-02 15:04:05.999999999 -0700 MST\",\"%v\"), %d, %v);COMMIT;", tm, tm.Hour(), bpsf))
		if err != nil {
			panic(err)
		}
	}
}

func FindDeployment(id string) (*Deployment, error) {
	resp, _, err := DB.Run(ql.NewRWCtx(), `
		SELECT 		id, name, deployedAt, deployedBy, totalMessages, messageBody, asset
		FROM 		deployment
		WHERE 	id == $1;
		SELECT 	count(*) 
		FROM 		message
		WHERE 	deployment == $1;
	`, id)
	if err != nil {
		return nil, err
	}
	var deployment *Deployment
	resp[0].Do(false, func(data []interface{}) (bool, error) {
		d := readDeploymentData(data)
		deployment = &d
		return false, nil
	})
	if deployment != nil {
		resp[1].Do(false, func(data []interface{}) (bool, error) {
			deployment.TotalMessages = data[0].(int64)
			return false, nil
		})
	}
	return deployment, nil
}

// GetMessage returns a message from the mailbox. Once the message is processed
// it should be removed from the queue with Delete.
func (mb *Mailbox) GetMessage() (*Message, error) {
	rss, _, err := DB.Run(ql.NewRWCtx(), `
		SELECT  message.id, deployment.messageBody, message.mailbox,
		message.deployment, message.receiveCount, message.lastReceivedAt,
		message.createdAt, message.deleted
		FROM message, deployment
		WHERE 
			message.mailbox == $1
			AND message.deleted == false
			AND deployment.id == message.deployment
		LIMIT 1;
		`, mb.Id)
	if err != nil {
		return nil, err
	}
	var msg *Message
	if len(rss) > 0 {
		r, _ := rss[0].FirstRow()
		if r == nil {
			return nil, nil
		}
		rss[0].Do(false, func(data []interface{}) (bool, error) {
			msg = readMessageStruct(data)
			return false, nil
		})
	} else {
		return nil, nil
	}

	if err != nil {
		return nil, err
	}

	msg.ReceiveCount++

	_, _, err = DB.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		UPDATE message
		SET receiveCount = $1
		WHERE id == $2;
		COMMIT;
	`, msg.ReceiveCount, msg.Id)
	if err != nil {
		return nil, err
	}
	return msg, err
}

func do() (err error) {
	oDB := flag.String("db", "ql.db", "The DB file to open. It'll be created if missing")
	oFlds := flag.Bool("fld", false, "Show recordset's field names.")
	flag.Parse()

	var src string
	switch n := flag.NArg(); n {
	case 0:
		b, err := ioutil.ReadAll(bufio.NewReader(os.Stdin))
		if err != nil {
			return err
		}

		src = string(b)
	default:
		a := make([]string, n)
		for i := range a {
			a[i] = flag.Arg(i)
		}
		src = strings.Join(a, " ")
	}

	db, err := ql.OpenFile(*oDB, &ql.Options{CanCreate: true})
	if err != nil {
		return err
	}

	defer func() {
		ec := db.Close()
		switch {
		case ec != nil && err != nil:
			log.Println(ec)
		case ec != nil:
			err = ec
		}
	}()

	src = "BEGIN TRANSACTION; " + src + "; COMMIT;"
	l, err := ql.Compile(src)
	if err != nil {
		log.Println(src)
		return err
	}

	rs, i, err := db.Execute(ql.NewRWCtx(), l)
	if err != nil {
		a := strings.Split(strings.TrimSpace(fmt.Sprint(l)), "\n")
		return fmt.Errorf("%v: %s", err, a[i])
	}

	if len(rs) == 0 {
		return
	}

	return rs[len(rs)-1].Do(*oFlds, func(data []interface{}) (bool, error) {
		fmt.Println(str(data))
		return true, nil
	})
}

// qlQuery provides a wrapper to compile a ql query
func qlQuery(db *qlw, key string, wraptx bool, arg ...interface{}) ([]ql.Recordset, int, error) {
	var err error
	if list, err := qlCompile(key, wraptx); err == nil {
		return db.Execute(ql.NewRWCtx(), list, arg...)
	}

	return []ql.Recordset(nil), 0, err
}

func CreateDB() error {
	_, _, err := DB.Run(ql.NewRWCtx(), `
			BEGIN TRANSACTION;
			CREATE TABLE properties (
				key string,
				value string
			);

			INSERT INTO 	properties
			VALUES 				("dbversion", "2");

			CREATE TABLE message (
				id 							string,
				receiveCount 		int,
				mailbox 				string,
				createdAt 			time,
				lastReceivedAt 	time,
				deployment 			string,
				deleted 				bool
			);

			CREATE TABLE mailbox (
				id  								string,
				createdAt 					time,
				version 						string,
				host 								string,
				lastCheckedInAt 		time
			);

			CREATE TABLE accessToken (
				mailbox 		string,
				token 			string,
				name 				string,
				fullAccess 	bool
			);

			CREATE TABLE deployment (
				id 						string,
				messageBody 	string,
				name 					string,
				deployedAt 		time,
				deployedBy 		string,
				totalMessages int,
				open 					bool,
				asset 				string
			);

			CREATE TABLE deploymentResponse (
				deployment 		string,
				mailbox 			string,
				response 			string,
				respondedAt 	time,
				isError 			bool
			);
			COMMIT;`)
	return err
}

func (m *Message) Save() error {
	_, _, err := DB.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		UPDATE message
		SET receiveCount = $2, mailbox = $3, createdAt = $4, deployment = $5,
			deleted = $6
		WHERE id = $1`, m.Id, m.Mailbox, m.CreatedAt, m.Deployment, m.Deleted)
	return err
}

// Marks a message as deleted in the mailbox.
func DeleteMessage(msgId string) error {
	_, _, err := DB.Run(ql.NewRWCtx(), `
	BEGIN TRANSACTION;
	UPDATE message
	SET deleted = true
	WHERE id == $1;
	COMMIT;
	`, msgId)
	return err
}

func (dp *Deployment) AddResponse(mailbox, response string, isErr bool) error {
	_, _, err := DB.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		INSERT INTO 	deploymentResponse 
									(deployment, mailbox, response, respondedAt, isError)
		VALUES 				($1,$2,$3,$4,$5);
		COMMIT;
	`, dp.Id, mailbox, response, time.Now(), isErr)
	return err
}

func runMigration(version, sql string) error {
	log.Infof("Upgrading database to version %s", version)
	fullSql := `
		BEGIN TRANSACTION;
		` + sql + `
		UPDATE properties SET value = $1 WHERE key == "dbversion";
		COMMIT;`
	_, _, err := DB.Run(ql.NewRWCtx(), fullSql, version)
	return err
}

func (mb *Mailbox) Checkin(host, version string) error {
	_, _, err := DB.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		UPDATE 	mailbox
		SET 		lastCheckedInAt = $1, host = $2, version = $3
		WHERE 	id == $4;
		COMMIT;
		`, time.Now(), host, version, mb.Id)
	return err
}

func (dp *Deployment) Save() error {
	_, _, err := DB.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		UPDATE 	deployment
		SET 		name = $2, deployedAt = $3, deployedBy = $4, totalMessages = $5,
						messageBody = $6
		WHERE 	id == $1;
		COMMIT;
	`, dp.Id, dp.Name, dp.DeployedAt, dp.DeployedBy, dp.TotalMessages, dp.MessageBody)
	return err
}

// Purge will mark all messages in the mailbox as deleted. They will no longer
// be availble when polling for messages.
func (mb *Mailbox) Purge() (int64, error) {
	ctx := ql.NewRWCtx()
	_, _, err := DB.Run(ctx, `
	BEGIN TRANSACTION;
	UPDATE message
	SET deleted = true
	WHERE mailbox == $1;
	COMMIT;
	`, mb.Id)
	return ctx.RowsAffected, err
}

func (msg *Message) Completed() error {
	db, err := OpenDB()
	if err != nil {
		return err
	}
	_, _, err = db.Run(ql.NewRWCtx(), `
		BEGIN TRANSACTION;
		DELETE FROM message
		WHERE id = $2;
		COMMIT
		`, msg.ReceiveCount+1)
	return err

}

// Search will return a slice of mailboxes whos identifiers match a given
// pattern. This pattern can be any valid regex. However it will automatically
// convert '*' to '.*'. This allows * to be used as a simple wildcard when
// searching by pattern.
func Search(rawPattern string) ([]Mailbox, error) {
	mbxs := []Mailbox{}
	pattern := strings.ToLower(strings.Replace(rawPattern, "*", ".*", -1))
	rss, _, err := DB.Run(ql.NewRWCtx(),
		` SELECT id FROM mailbox WHERE id LIKE $1`, pattern)
	if err != nil {
		return nil, err
	}
	rss[0].Do(false, func(data []interface{}) (bool, error) {
		mb := Mailbox{Id: data[0].(string)}
		mbxs = append(mbxs, mb)
		return true, nil
	})
	return mbxs, nil
}