package main

import (
  "database/sql"
  "fmt"
  _ "github.com/lib/pq"
//  "json"
)

type Book struct {
  Id             int
  Age            string  // recommended age, e.g. "beginner", "junior", "ya" (Young Adult), "adult"
  AuthorGrouping string  // unique rendering of the author's name, used for internal grouping 
  AuthorReading  string	 // reading order of author's name, e.g. "Charles Dickens"
  AuthorSort     string  // sort order of author's name, e.g. "Dickens, Charles"
  DDC            string  // Dewey Decimal Classification
  Description    string  // Back cover / inside flap blurb, describing the book
  Genre          string  // e.g. "adventure", "historical", "mystery", "romance", "sf" (Science Fiction)
  LCC            string  // Library of Congress Classification
  SeriesName     string  
  Title          string
  Volume         string
}

func main() {
  // TODO:  protect DB with a real password, and read DB config from an external file
  db := openDb("quanlib", "quanlib", "quanlib")
  if nil == db {
    return
  }
  defer db.Close()

  query := "SELECT COUNT(1) FROM Books"
  ps, err := db.Prepare(query)
  if nil != err {
    fmt.Println("Error:  failed to prepare statement:  " + query)
    return
  }

  var count int
  err = ps.QueryRow().Scan(&count)
  if sql.ErrNoRows == err {
    fmt.Println("Error:  No rows returned by statement:  " + query)
  } else {
    fmt.Println("Found ", count, " books.")
  }

  ids := []int {1041, 1951, 2148, 103}
  books := queryBooksByIds(db, ids)

  fmt.Println("Found books:", books)
}

func nsVal(ns sql.NullString) string {
  if ns.Valid {
    return ns.String
  }
  return ""
}

func openDb(user, pass, dbName string) (*sql.DB) {
  db, err := sql.Open("postgres","user=" + user + " password=" + pass + " dbname=" + dbName + " sslmode=disable")
  if nil != err {
    fmt.Println("Error:  DB arguments incorrect?", err)
    return nil
  }

  err = db.Ping()
  if nil != err {
    fmt.Println("Error:  could not connect to DB.", err)
    db.Close()
    return nil
  }

  return db
}

func queryBooksByIds(db *sql.DB, ids []int) []Book {
  query := `SELECT s.age,a.grouping,a.reading,a.sort,c.ddc,b.description,s.genre,c.lcc,s.descr,b.title,b.volume
            FROM Authors a 
            INNER JOIN Books b ON a.id=b.author
            LEFT OUTER JOIN Classifications c ON c.id=b.classification
            LEFT OUTER JOIN Series s ON s.id=b.series
            WHERE b.id=$1`

  ps, err := db.Prepare(query)
  if nil != err {
    fmt.Println("Error:  failed to prepare statement:  " + query)
    return nil
  }
  defer ps.Close()

  res := make([]Book, len(ids))
  
  for n, id := range ids {
    row := ps.QueryRow(id)

    var age, grouping, reading, sort, ddc, description, genre, lcc, name, title, volume sql.NullString

    err = row.Scan(&age, &grouping, &reading, &sort, &ddc, &description, &genre, &lcc, &name, &title, &volume)
    if err != nil {
      fmt.Println("Error:  Failed to read book:", id, ":", err)
    } else {
      var b Book
      b.Id = id
      b.Age = nsVal(age)
      b.AuthorGrouping = nsVal(grouping)
      b.AuthorReading = nsVal(reading)
      b.AuthorSort = nsVal(sort)
      b.DDC = nsVal(ddc)
      b.Description = nsVal(description)
      b.Genre = nsVal(genre)
      b.LCC = nsVal(lcc)
      b.SeriesName = nsVal(name)
      b.Title = nsVal(title)
      b.Volume = nsVal(volume)

      res[n] = b
    }
  }

  return res
}