package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    qb "github.com/didi/gendry/builder"
)

func main() {
    db,err := sql.Open("mysql", "xxxxxxxxxxx")
    if nil != err {
        panic(err)
    }
    mp := map[string]interface{}{
    	"country": "China",
    	"role": "driver",
    	"age >": 45,
        "_groupby": "name",
        "_having": map[string]interface{}{
            "total >": 1000,
            "total <=": 50000,
        },
    	"_orderby": "age desc",
    }
    cond,vals,err := qb.BuildSelect("tableName", where, []string{"name", "count(price) as total", "age"})
    
    //cond: SELECT name,count(price) as total,age FROM tableName WHERE (age>? AND country=? AND role=?) GROUP BY name HAVING (total>? AND total<=?) ORDER BY age DESC
    //vals: []interface{}{45, "China", "driver", 1000, 50000}

	if nil != err {
		panic(err)
	}	
	
    rows,err := db.Query(cond, vals...)
    if nil != err {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name,phone string
        rows.Scan(&id, &name, &phone)
        fmt.Println(id, name, phone)
    }

    //have fun !!
}

where := map[string]interface{}{
	"foo <>": "aha",
	"bar <=": 45,
	"sex in": []interface{}{"girl", "boy"},
	"name like": "%James",
}

where := map[string]interface{}{
	"age >": 100,
	"_orderby": "fieldName asc",
	"_groupby": "fieldName",
	"_having": map[string]interface{}{"foo":"bar",},
	"_limit": []uint{begin,end},
}

where := map[string]interface{}{
    "score > ": 100,
    "city in": []interface{}{"Beijing", "Shijiazhuang",}
}
// supported: AggregateSum,AggregateMax,AggregateMin,AggregateCount,AggregateAvg
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))
sumAge := result.Int64()

result,err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 
numberOfRecords := result.Int64()

result,err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))
averageScore := result.Float64()

where := map[string]interface{}{
	"foo <>": "aha",
	"bar <=": 45,
	"sex in": []interface{}{"girl", "boy"},
}
update := map[string]interface{}{
	"role": "primaryschoolstudent",
	"rank": 5,
}
cond,vals,err := qb.BuildUpdate("table_name", where, update)

db.Exec(cond, vals...)

var data []map[string]interface{}
data = append(data, map[string]interface{}{
    "name": "deen",
    "age":  23,
})
data = append(data, map[string]interface{}{
    "name": "Tony",
    "age":  30,
})
cond, vals, err := qb.BuildInsert(table, data)
db.Exec(cond, vals...)

cond, vals, err := builder.NamedQuery("select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})", map[string]interface{}{
	"name": "caibirdme",
	"m_score": []float64{3.0, 5.8, 7.9},
})

assert.Equal("select * from tb where name=? and id in (select uid from anothertable where score in (?,?,?))", cond)
assert.Equal([]interface{}{"caibirdme", 3.0, 5.8, 7.9}, vals)