// +build go1.12

package oci8_test

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	"github.com/mattn/go-oci8"
)

func Example_sqlCursor() {
	// Example shows how to do a cursor select

	// For testing, check if database tests are disabled
	if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
		fmt.Println(3)
		return
	}

	oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

	var openString string
	// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
	if len(oci8.TestUsername) > 0 {
		if len(oci8.TestPassword) > 0 {
			openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
		} else {
			openString = oci8.TestUsername + "@"
		}
	}
	openString += oci8.TestHostValid

	// A normal simple Open to localhost would look like:
	// db, err := sql.Open("oci8", "127.0.0.1")
	// For testing, need to use additional variables
	db, err := sql.Open("oci8", openString)
	if err != nil {
		fmt.Printf("Open error is not nil: %v", err)
		return
	}
	if db == nil {
		fmt.Println("db is nil")
		return
	}

	// defer close database
	defer func() {
		err = db.Close()
		if err != nil {
			fmt.Println("Close error is not nil:", err)
		}
	}()

	ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
	err = db.PingContext(ctx)
	cancel()
	if err != nil {
		fmt.Println("PingContext error is not nil:", err)
		return
	}

	var rows *sql.Rows
	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	defer cancel()
	rows, err = db.QueryContext(ctx, "select 1, cursor(select 2 from dual union select 3 from dual) from dual")
	if err != nil {
		fmt.Println("QueryContext error is not nil:", err)
		return
	}

	// defer close rows
	defer func() {
		err = rows.Close()
		if err != nil {
			fmt.Println("Close error is not nil:", err)
		}
	}()

	if !rows.Next() {
		fmt.Println("no Next rows")
		return
	}

	var aInt int64
	var subRows *sql.Rows
	err = rows.Scan(&aInt, &subRows)
	if err != nil {
		fmt.Println("Scan error is not nil:", err)
		return
	}

	if aInt != 1 {
		fmt.Println("aInt != 1")
		return
	}
	if subRows == nil {
		fmt.Println("subRows is nil")
		return
	}

	if !subRows.Next() {
		fmt.Println("no Next subRows")
		return
	}

	err = subRows.Scan(&aInt)
	if err != nil {
		fmt.Println("Scan error is not nil:", err)
		return
	}

	if aInt != 2 {
		fmt.Println("aInt != 2")
		return
	}

	if !subRows.Next() {
		fmt.Println("no Next subRows")
		return
	}

	err = subRows.Scan(&aInt)
	if err != nil {
		fmt.Println("Scan error is not nil:", err)
		return
	}

	if aInt != 3 {
		fmt.Println("aInt != 3")
		return
	}

	if subRows.Next() {
		fmt.Println("has Next rows")
		return
	}

	err = subRows.Err()
	if err != nil {
		fmt.Println("Err error is not nil:", err)
		return
	}

	if rows.Next() {
		fmt.Println("has Next rows")
		return
	}

	err = rows.Err()
	if err != nil {
		fmt.Println("Err error is not nil:", err)
		return
	}

	fmt.Println(aInt)

	// output: 3
}