package main

import (
	"database/sql"
	"fmt"

	"github.com/syke99/dynaQ"
	_ "github.com/go-sql-driver/mysql"
)

func dsn(dbName string) string {
	return fmt.Sprintf("%s:%[email protected](%s)/%s", username, password, hostname, dbName)
}

func main() {
	
  // create an instance of your database
    db, err := sql.Open("mysql", dsn(""))
    if err != nil {
	    panic(err)
    }
    defer db.Close()
    
    // create a new dynamic querier with your database instance,
    // you can either pass in your own time format to match the
    // format of time values stored in your database, or if they
    // match the format "2006-01-02 15:04", you can pass in ""
    // and dynaQ will default to that format
    dq := dynaQ.NewDynaQ(db, "")
    
    // after creating a dynamic querier, just pass in whatever
    // query string you want, and whatever variable amouont of query
    // arguments you need.
    // you can query for multiple rows, or just one row at once
    //    
    // testTable:
    // __| id | name | cost | available |   created-date   |__
    // ---------------------------------------------------------
    //   |  1 |  ab  | 2.10 |   true    | 2018-01-18 05:43 |
    //   |  2 |  cd  | 1.55 |   false   | 2018-01-14 06:28 |
    //   |  3 |  ef  | 3.78 |   true    | 2018-06-27 09:59 |
    //   |  4 |  gh  | 2.76 |   true    | 2018-09-04 15:09 |
    //   |  5 |  ij  | 8.13 |   true    | 2019-01-01 23:43 |
    //   |  6 |  kl  | 4.45 |   false   | 2019-01-19 10:14 |
    //   |  7 |  mn  | 2.99 |   false   | 2019-02-11 06:22 |
    //
    // single row:
    row, err := dq.DatabaseQueryRow("select * from testTable where id in (@p1, @p2, @p3, @p4)", 1, 2, 4, 7)
    if err != nil {
	    panic(err)
    }
    
    
    fmt.Println("-----------------")
    // a dynaQ column holds the type in field
    // <columnVariable>.Type for easy type
    // assertion later on
    for _, column := range row {
	     fmt.Println(fmt.Sprintf("column: %s, value: %v (type: %s)", column.Name, fmt.Sprintf("%v", column.Value), column.Type))
    }
    fmt.Println("-----------------")
    //
    // this will output:
    // -----------------
    // column: id, value: 1 (type: int64)
    // column: name, value: ab (type: string)
    // column: cost, value: 2.10 (type: float64)
    // column: available, value: true (type: bool)
    // column: created-date, value: 2018-01-18 05:43 (type: time.Time)
    // -----------------
	
	
    // multiple rows:
    rows, err := dq.DatabaseQuery("select * from testTable where id in (@p1, @p2, @p3, @p4)", 1, 2, 4, 7)
    if err != nil {
        panic(err)
    }
    
    newRow := true
	
    fmt.Println("-----------------")
    for newRow {
    	// the first value returned by rows.NextRow()
	// is a bool signaling whether there is another
	// row following the second value returned by
	// rows.NextRow(), which represents the current
	// row for this loop
    	if ok, row := rows.NextRow(); !ok {
		newRow = false
    	}
    	fmt.Println(fmt.Sprintf("row: %d", row.CurrentRow))
    	fmt.Println("-----------------")
	for _, column := range row {
            	fmt.Println(fmt.Sprintf("column: %s, value: %v (type: %s)", column.Name, fmt.Sprintf("%v", column.Value), column.Type))
        }
	rowNumber++
	fmt.Println("-----------------")
    }
    
    //
    // this will output:
    // -----------------
    // row: 1
    // -----------------
    // column: id, value: 1 (type: int64)
    // column: name, value: ab (type: string)
    // column: cost, value: 2.10 (type: float64)
    // column: available, value: true (type: bool)
    // column: created-date, value: 2018-01-18 05:43 (type: time.Time)
    // -----------------
    // row: 2
    // -----------------
    // column: id, value: 2 (type: int64)
    // column: name, value: cd (type string)
    // column: cost, value: 1.55 (type float64)
    // column: available, value: false (type: bool)
    // column: created-date, value: 2018-01-14 06:28 (type: time.Time)
    // -----------------
    // row: 3
    // -----------------
    // column: id, value: 4 (type: int64)
    // column: name, value: gh (type: string)
    // column: cost, value: 2.76 (type: float64)
    // column: available, value: true (type: bool)
    // column: created-date, value: 2018-09-04 15:09 (type: time.Time)
    // -----------------
    // row: 4
    // -----------------
    // column: id, value: 7 (type: int64)
    // column: name, value: mn (type: string)
    // column: cost, value: 2.99 (type: float64)
    // column: available, value: false (type: bool)
    // column: created-date, value: 2019-02-11 06:22 (type: time.Time)
    // -----------------
}