package main

import (
  "database/sql"
  "fmt"
  _ "github.com/lib/pq"
  "strings"
  "strconv"
  "sync"
)

// ---------------------------------------------------------------------------
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"
  CoverId        int     // index into EFS table for cover, if there is one
  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)
  Language       string
  LCC            string  // Library of Congress Classification
  SeriesName     string  
  Title          string
  Volume         string
}

// ---------------------------------------------------------------------------
type Field string
const (
  Author, Language, List, Series, Sort, Title Field = "aut", "lan", "lst", "ser", "srt", "tit"
)

func (f Field) String() string {
  return string(f)
}

// ---------------------------------------------------------------------------
type SearchTerm struct {
  Attribute Field
  Text      string
}

// ---------------------------------------------------------------------------
type SortOrder string
const (
  ByArrival, ByAuthor, ByPublication, ByTitle SortOrder = "arr", "aut", "pub", "tit"
)

func (so SortOrder) String() string {
  return string(so)
}

// ---------------------------------------------------------------------------
var g_db *sql.DB = nil
var g_mutex = &sync.Mutex{}

// ============================================================================
func conditional(count int) (string, int) {
  if (count == 0) {
    return "WHERE", 1
  } else {
    return "AND", (count + 1)
  }
}

func dbShutdown() {
  if nil != g_db {
    g_db.Close()
  }
}

func getDb() (*sql.DB) {
  if nil == g_db {
    g_mutex.Lock()
    defer g_mutex.Unlock()
    if nil == g_db {
      config := GetConfig()
      g_db = openDb(config.user, config.pass, config.dbName)
    }
  }

  return g_db
}

func niVal(ni sql.NullInt64) int {
  if ni.Valid {
    return int(ni.Int64)
  }
  return 0
}

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 {
    report("Error:  DB arguments incorrect?", err)
    return nil
  }

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

  return db
}

func queryBooksByIds(ids []int) []Book {
  query := `SELECT s.age,a.grouping,a.reading,a.sort,b.cover,c.ddc,b.description,s.genre,b.language,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 := getDb().Prepare(query)
  if nil != err {
    report("Error:  failed to prepare statement:  " + query, err)
    return nil
  }
  defer ps.Close()

  var count int = 0
  for _, id := range ids {
    if 0 != id {
      count++
    }
  }
    
  res := make([]Book, count)
  
  count = 0
  for _, id := range ids {
    if 0 == id {
      continue
    }

    row := ps.QueryRow(id)

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

    err = row.Scan(&age, &grouping, &reading, &sort, &cover, &ddc, &description, &genre, &language, &lcc, &name, &title, &volume)
    if err != nil {
      report("Error:  Failed to read book:" + strconv.Itoa(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.CoverId = niVal(cover)
      b.DDC = nsVal(ddc)
      b.Description = nsVal(description)
      b.Genre = nsVal(genre)
      b.Language = nsVal(language)
      b.LCC = nsVal(lcc)
      b.SeriesName = nsVal(name)
      b.Title = nsVal(title)
      b.Volume = nsVal(volume)

      res[count] = b
      count++
    }
  }

  if count < len(res) {
    res = res[:count]
  }

  return res
}

func queryBookPathById(id int) (string) {
  query := "SELECT b.path FROM Books b WHERE b.id=$1"

  ps, err := getDb().Prepare(query)
  if nil != err {
    report("Failed to Prepare query:  " + query, err)
    return ""
  }
  defer ps.Close()

  row := ps.QueryRow(id)
  var path sql.NullString
  err = row.Scan(&path)
  if nil != err {
    report(fmt.Sprintf("Failed to retrieve path for book id %v: ", id), err)
    return ""
  }

  return nsVal(path)
}

func queryIds(criteria []SearchTerm) []int {
  query := "SELECT b.id FROM Books b" +
           " INNER JOIN Authors a ON a.id=b.author" +
           " LEFT OUTER JOIN Series s ON s.id=b.series" +
           " LEFT OUTER JOIN Lists_Books lb ON b.id=lb.book" +
           " LEFT OUTER JOIN Lists l ON l.id=lb.list" +
           " "

  args := make([]interface{}, 0)
  conjunction := "WHERE"
  count := 0
  sort := ByAuthor

  for _, criterion := range criteria {
    switch criterion.Attribute {
    case Author:
      conjunction, count = conditional(count)
      query += conjunction + " UPPER(a.grouping) LIKE UPPER($" + strconv.Itoa(count) + ")"
      args = append(args, strings.Replace(criterion.Text, " ", "", -1))
    case Language:
      conjunction, count = conditional(count)
      query += conjunction + " UPPER(b.language) LIKE UPPER($" + strconv.Itoa(count) + ")"
      args = append(args, criterion.Text)
    case List:
      conjunction, count = conditional(count)
      codes := strings.Split(criterion.Text, ",")
      query += conjunction + " UPPER(l.code) IN ("

      for j, code := range codes {
        if j > 0 {
          query += ","
        }
        query += "UPPER($" + strconv.Itoa(count + j) + ")"
        args = append(args, code)
      }
      query += ")"
      count += len(codes) - 1
    case Series:
      conjunction, count = conditional(count)
      query += conjunction + " UPPER(s.descr) LIKE UPPER($" + strconv.Itoa(count) + ")"
      args = append(args, criterion.Text)
    case Sort:
      sort = SortOrder(criterion.Text)
    case Title:
      conjunction, count = conditional(count)
      query += conjunction + " UPPER(b.title) LIKE UPPER($" + strconv.Itoa(count) + ")"
      args = append(args, criterion.Text)
    default:
      report("Error:  unrecognized search field in queryIds():  " + criterion.Attribute.String(), nil)
      return nil
    }
  }

  switch sort {
  case ByArrival:
    query += " ORDER BY b.arrived DESC,a.grouping,s.descr,b.volume,b.title,b.path"
  case ByAuthor:
    query += " ORDER BY a.grouping,s.descr,b.volume,b.title,b.path,b.arrived DESC"
  case ByPublication:
    report("Error: cannot sort by publication (not yet implemented)", nil)
    return nil
  case ByTitle:
    query += " ORDER BY b.title,a.grouping,s.descr,b.volume,b.path,b.arrived DESC"
  default:
    report("Error: unrecognized sort order in queryIds(): " + sort.String(), nil)
    return nil
  }

  res := []int{}

  ps, err := getDb().Prepare(query)
  if nil != err {
    report("Failed to Prepare query:  " + query, err)
    return nil
  }
  defer ps.Close()

  var rows *sql.Rows
  rows, err = ps.Query(args...)
  if nil != err {
    report("Failed to execute query:  " + query, err)
    return nil
  }
  defer rows.Close()

  for rows.Next() {
    var id int
    rows.Scan(&id)
    res = append(res, id)
  }

  return res
}

func queryMimeTypeByEfsId(efsId int) string {
  const query = "SELECT mimeType FROM Efs WHERE id=$1"

  ps, err := getDb().Prepare(query)
  if nil != err {
    report("Failed to Prepare query:  " + query, err)
    return ""
  }
  defer ps.Close()

  row := ps.QueryRow(efsId)
  var mimeType sql.NullString
  err = row.Scan(&mimeType)
  if nil != err {
    report(fmt.Sprintf("Failed to retrieve mimeType for id %v: ", efsId), err)
    return ""
  }
  
  return nsVal(mimeType)
}

func report(msg string, err error) {
  fmt.Println("Error:  " + msg, err)
}