//
//  rhrd-go
//
//  The Radio Helsinki Rivendell Go Package
//
//
//  Copyright (C) 2016 Christian Pointner <equinox@helsinki.at>
//
//  This file is part of rhrd-go.
//
//  rhrd-go is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  any later version.
//
//  rhrd-go is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License
//  along with rhrd-go. If not, see <http://www.gnu.org/licenses/>.
//

package rddb

import (
	"database/sql"
	"fmt"
	"regexp"
	"strconv"
	"strings"
	"time"

	"github.com/ziutek/mymysql/godrv"
)

var (
	showMacroRe      = regexp.MustCompile(`^LL 1 ([^ ]+) 0\!$`)
	mysqlTableNameRe = regexp.MustCompile(`^[_0-9a-zA-Z-]+$`)
)

const (
	DB_VERSION           = 250
	dropboxPseudoStation = "import-dropbox"
)

type getPasswordResult struct {
	password string
	err      error
}

type getPasswordRequest struct {
	username string
	cached   bool
	response chan<- getPasswordResult
}

type getGroupOfCartResult struct {
	group string
	err   error
}

type getGroupOfCartRequest struct {
	cart     uint
	response chan<- getGroupOfCartResult
}

type getShowInfoResult struct {
	title   string
	group   string
	carts   []uint
	normLvl int
	trimLvl int
	err     error
}

type getShowInfoRequest struct {
	showid   uint
	response chan<- getShowInfoResult
}

type checkMusicGroupResult struct {
	ismusic bool
	err     error
}

type checkMusicGroupRequest struct {
	group    string
	response chan<- checkMusicGroupResult
}

type getMusicInfoResult struct {
	normLvl int
	trimLvl int
	err     error
}

type getMusicInfoRequest struct {
	group    string
	response chan<- getMusicInfoResult
}

type ShowListEntry struct {
	ID       uint
	Title    string
	log      string
	lowCart  int
	highCart int
}

type ShowList map[uint]ShowListEntry

type getShowListResult struct {
	shows ShowList
	err   error
}

type getShowListRequest struct {
	username string
	response chan<- getShowListResult
}

type CutListEntry struct {
	Number      uint
	Evergreen   bool
	Description string
	Duration    time.Duration
	Imported    NullTime
	NumPlayed   uint
	LastPlayed  NullTime
}

type CartListEntry struct {
	Number uint
	Exists bool
	Title  string
	Cuts   []CutListEntry
}

type getShowCartListResult struct {
	carts []CartListEntry
	err   error
}

type getShowCartListRequest struct {
	show     ShowListEntry
	response chan<- getShowCartListResult
}

type JingleListEntry struct {
	Group        string
	Description  string
	ActiveCart   int
	InactiveCart int
}

type JingleList map[string]JingleListEntry

type getJingleListResult struct {
	jingles JingleList
	err     error
}

type getJingleListRequest struct {
	username string
	response chan<- getJingleListResult
}

type getJingleCutListResult struct {
	active   CartListEntry
	inactive CartListEntry
	err      error
}

type getJingleCutListRequest struct {
	jingle   JingleListEntry
	response chan<- getJingleCutListResult
}

type DB struct {
	conf                 *config
	dbh                  *sql.DB
	passwordCache        map[string]string
	getPasswordChan      chan getPasswordRequest
	getPasswordStmt      *sql.Stmt
	getGroupOfCartChan   chan getGroupOfCartRequest
	getGroupOfCartStmt   *sql.Stmt
	getShowInfoChan      chan getShowInfoRequest
	getShowInfoStmt      *sql.Stmt
	checkMusicGroupChan  chan checkMusicGroupRequest
	checkMusicGroupStmt  *sql.Stmt
	getMusicInfoChan     chan getMusicInfoRequest
	getMusicInfoStmt     *sql.Stmt
	getShowListChan      chan getShowListRequest
	getShowListStmt      *sql.Stmt
	getShowCartListChan  chan getShowCartListRequest
	getCartInfoStmt      *sql.Stmt
	getCutInfoStmt       *sql.Stmt
	getJingleListChan    chan getJingleListRequest
	getJingleListStmt    *sql.Stmt
	getJingleCutListChan chan getJingleCutListRequest
	quit                 chan bool
	done                 chan bool
}

func (db *DB) init() (err error) {
	godrv.Register("SET CHARACTER SET utf8;")

	dsn := fmt.Sprintf("tcp:%s:3306*%s/%s/%s", db.conf.dbHost, db.conf.dbDb, db.conf.dbUser, db.conf.dbPasswd)
	if db.dbh, err = sql.Open("mymysql", dsn); err != nil {
		return
	}

	var dbver int
	err = db.dbh.QueryRow("select DB from VERSION;").Scan(&dbver)
	if err != nil {
		err = fmt.Errorf("fetching version: %s", err)
		return
	}
	if dbver != DB_VERSION {
		err = fmt.Errorf("version mismatch is %d, should be %d", dbver, DB_VERSION)
		return
	}

	if db.getPasswordStmt, err = db.dbh.Prepare("select PASSWORD from USERS where LOGIN_NAME = ?;"); err != nil {
		return
	}
	if db.getGroupOfCartStmt, err = db.dbh.Prepare("select NAME,DEFAULT_LOW_CART,DEFAULT_HIGH_CART from GROUPS where DEFAULT_LOW_CART <= ? and DEFAULT_HIGH_CART >= ?;"); err != nil {
		return
	}
	if db.getShowInfoStmt, err = db.dbh.Prepare("select CART.TITLE,CART.MACROS,DROPBOXES.GROUP_NAME,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART from CART, DROPBOXES, GROUPS where CART.NUMBER = DROPBOXES.TO_CART and GROUPS.NAME = DROPBOXES.GROUP_NAME and CART.NUMBER = ?;"); err != nil {
		return
	}
	if db.checkMusicGroupStmt, err = db.dbh.Prepare("select count(*) from DROPBOXES where GROUP_NAME = ? and SET_USER_DEFINED like \"M;%\";"); err != nil {
		return
	}
	if db.getMusicInfoStmt, err = db.dbh.Prepare("select NORMALIZATION_LEVEL,AUTOTRIM_LEVEL from DROPBOXES where DROPBOXES.GROUP_NAME = ?;"); err != nil {
		return
	}
	if db.getShowListStmt, err = db.dbh.Prepare("select CART.TITLE,CART.MACROS,DROPBOXES.TO_CART,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART from CART, USER_PERMS, DROPBOXES, GROUPS where CART.NUMBER = DROPBOXES.TO_CART and USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME = USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME = GROUPS.NAME and DROPBOXES.STATION_NAME = ?;"); err != nil {
		return
	}
	if db.getCartInfoStmt, err = db.dbh.Prepare("select TITLE from CART where TYPE=1 and NUMBER = ?"); err != nil {
		return
	}
	if db.getCutInfoStmt, err = db.dbh.Prepare("select CUT_NAME, EVERGREEN, DESCRIPTION, LENGTH, ORIGIN_DATETIME, PLAY_COUNTER, LAST_PLAY_DATETIME from CUTS where CART_NUMBER = ?"); err != nil {
		return
	}
	if db.getJingleListStmt, err = db.dbh.Prepare("select GROUPS.NAME,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME = USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME = GROUPS.NAME and DROPBOXES.STATION_NAME = ?;"); err != nil {
		return
	}

	return
}

func (db *DB) getPassword(username string, cached bool) (result getPasswordResult) {
	if cached {
		result.password = db.passwordCache[username]
	}

	if result.password == "" {
		if result.err = db.getPasswordStmt.QueryRow(username).Scan(&result.password); result.err != nil {
			if result.err == sql.ErrNoRows {
				result.err = fmt.Errorf("user '%s' not known by rivendell", username)
			}
			return
		}
		db.passwordCache[username] = result.password
	}

	return
}

func (db *DB) getGroupOfCart(cart uint) (result getGroupOfCartResult) {
	var rows *sql.Rows
	if rows, result.err = db.getGroupOfCartStmt.Query(cart, cart); result.err != nil {
		return
	}
	defer rows.Close()
	sizeMin := ^uint(0)
	for rows.Next() {
		var name string
		var lowCart, highCart uint
		if result.err = rows.Scan(&name, &lowCart, &highCart); result.err != nil {
			return
		}
		if highCart >= lowCart {
			size := (highCart - lowCart) + 1
			if sizeMin > size {
				result.group = name
				sizeMin = size
			}
		}
	}
	if result.err = rows.Err(); result.err != nil {
		return
	}
	if result.group == "" {
		result.err = fmt.Errorf("cart is outside of all group cart ranges")
	}
	return
}

func (db *DB) getLogTableName(log string) (logtable string, err error) {
	logtable = strings.Replace(log, " ", "_", -1) + "_LOG"
	if !mysqlTableNameRe.MatchString(logtable) {
		return "", fmt.Errorf("the log table name contains illegal charecters: %s", logtable)
	}
	return
}

func (db *DB) getShowCarts(log string, lowCart, highCart int) (carts []uint, err error) {
	var logtable string
	if logtable, err = db.getLogTableName(log); err != nil {
		return
	}
	q := fmt.Sprintf("select CART_NUMBER from %s where CART_NUMBER >= %d and CART_NUMBER <= %d order by COUNT;", logtable, lowCart, highCart)
	var rows *sql.Rows
	if rows, err = db.dbh.Query(q); err != nil {
		return
	}
	defer rows.Close()
	for rows.Next() {
		var cart uint
		if err = rows.Scan(&cart); err != nil {
			return
		}
		carts = append(carts, cart)
	}
	err = rows.Err()
	return
}

func (db *DB) getShowInfo(showid uint) (result getShowInfoResult) {
	var macros string
	var lowCart, highCart int
	result.err = db.getShowInfoStmt.QueryRow(showid).Scan(&result.title, &macros, &result.group, &result.normLvl, &result.trimLvl, &lowCart, &highCart)
	if result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = fmt.Errorf("show '%d' not found", showid)
		}
		return
	}
	result.normLvl /= 100
	result.trimLvl /= 100
	result.carts, result.err = db.getShowCarts(showMacroRe.FindStringSubmatch(macros)[1], lowCart, highCart)
	return
}

func (db *DB) checkMusicGroup(group string) (result checkMusicGroupResult) {
	var cnt int
	if result.err = db.checkMusicGroupStmt.QueryRow(group).Scan(&cnt); result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = nil
			result.ismusic = false
		}
		return
	}
	result.ismusic = cnt > 0
	return
}

func (db *DB) getMusicInfo(group string) (result getMusicInfoResult) {
	result.err = db.getMusicInfoStmt.QueryRow(group).Scan(&result.normLvl, &result.trimLvl)
	if result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = fmt.Errorf("music pool '%s' not found", group)
		}
		return
	}
	return
}

func (db *DB) getShowList(username string) (result getShowListResult) {
	var rows *sql.Rows
	if rows, result.err = db.getShowListStmt.Query(username, dropboxPseudoStation); result.err != nil {
		return
	}
	defer rows.Close()

	result.shows = make(map[uint]ShowListEntry)
	for rows.Next() {
		var show ShowListEntry
		var macros, params string

		if result.err = rows.Scan(&show.Title, &macros, &show.ID, &params, &show.lowCart, &show.highCart); result.err != nil {
			return
		}
		if params[0] != 'S' {
			continue
		}
		show.log = showMacroRe.FindStringSubmatch(macros)[1]
		result.shows[show.ID] = show
	}
	result.err = rows.Err()
	return
}

func (db *DB) getCutInfo(cart uint) (cuts []CutListEntry, err error) {
	var rows *sql.Rows
	if rows, err = db.getCutInfoStmt.Query(cart); err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var cut = CutListEntry{}
		var cutName, evergreen string
		var length uint

		if err = rows.Scan(&cutName, &evergreen, &cut.Description, &length, &cut.Imported, &cut.NumPlayed, &cut.LastPlayed); err != nil {
			return
		}

		parts := strings.Split(cutName, "_")
		if len(parts) == 2 {
			if cn, converr := strconv.ParseUint(parts[1], 10, 32); converr != nil {
				continue
			} else {
				cut.Number = uint(cn)
			}
		} else {
			continue
		}
		switch evergreen {
		case "Y":
			cut.Evergreen = true
		default:
			cut.Evergreen = false
		}
		cut.Duration = time.Duration(length) * time.Millisecond

		cuts = append(cuts, cut)
	}
	err = rows.Err()
	return
}

func (db *DB) getCartInfo(cart uint) (c CartListEntry, err error) {
	c = CartListEntry{}
	c.Number = cart
	c.Exists = true
	err = db.getCartInfoStmt.QueryRow(cart).Scan(&c.Title)
	if err != nil {
		if err == sql.ErrNoRows {
			err = nil
			c.Exists = false
		}
		return
	}
	if c.Cuts, err = db.getCutInfo(cart); err != nil {
		return
	}
	return
}

func (db *DB) getShowCartList(show ShowListEntry) (result getShowCartListResult) {
	var carts []uint
	if carts, result.err = db.getShowCarts(show.log, show.lowCart, show.highCart); result.err != nil {
		return
	}

	for _, cart := range carts {
		var c CartListEntry
		if c, result.err = db.getCartInfo(cart); result.err != nil {
			return
		}
		result.carts = append(result.carts, c)
	}
	return
}

func (db *DB) getJingleList(username string) (result getJingleListResult) {
	var rows *sql.Rows
	if rows, result.err = db.getJingleListStmt.Query(username, dropboxPseudoStation); result.err != nil {
		return
	}
	defer rows.Close()

	result.jingles = make(map[string]JingleListEntry)
	for rows.Next() {
		var jingle JingleListEntry
		var params string

		if result.err = rows.Scan(&jingle.Group, &params, &jingle.ActiveCart, &jingle.InactiveCart, &jingle.Description); result.err != nil {
			return
		}
		if params[0] != 'J' {
			continue
		}
		result.jingles[jingle.Group] = jingle
	}
	result.err = rows.Err()
	return
}

func (db *DB) getJingleCutList(jingle JingleListEntry) (result getJingleCutListResult) {
	if result.active, result.err = db.getCartInfo(uint(jingle.ActiveCart)); result.err != nil {
		return
	}
	if result.inactive, result.err = db.getCartInfo(uint(jingle.InactiveCart)); result.err != nil {
		return
	}
	return
}

func (db *DB) dispatchRequests() {
	defer func() { db.done <- true }()
	for {
		select {
		case <-db.quit:
			return
		case req := <-db.getPasswordChan:
			req.response <- db.getPassword(req.username, req.cached)
		case req := <-db.getGroupOfCartChan:
			req.response <- db.getGroupOfCart(req.cart)
		case req := <-db.getShowInfoChan:
			req.response <- db.getShowInfo(req.showid)
		case req := <-db.checkMusicGroupChan:
			req.response <- db.checkMusicGroup(req.group)
		case req := <-db.getMusicInfoChan:
			req.response <- db.getMusicInfo(req.group)
		case req := <-db.getShowListChan:
			req.response <- db.getShowList(req.username)
		case req := <-db.getShowCartListChan:
			req.response <- db.getShowCartList(req.show)
		case req := <-db.getJingleListChan:
			req.response <- db.getJingleList(req.username)
		case req := <-db.getJingleCutListChan:
			req.response <- db.getJingleCutList(req.jingle)
		}
	}
}

// *********************************************************
// Public Interface

type DBChan struct {
	getPasswordChan      chan<- getPasswordRequest
	getGroupOfCartChan   chan<- getGroupOfCartRequest
	getShowInfoChan      chan<- getShowInfoRequest
	checkMusicGroupChan  chan<- checkMusicGroupRequest
	getMusicInfoChan     chan<- getMusicInfoRequest
	getShowListChan      chan<- getShowListRequest
	getShowCartListChan  chan<- getShowCartListRequest
	getJingleListChan    chan<- getJingleListRequest
	getJingleCutListChan chan<- getJingleCutListRequest
}

func (db *DBChan) GetPassword(username string, cached bool) (string, error) {
	resCh := make(chan getPasswordResult)
	req := getPasswordRequest{}
	req.username = username
	req.cached = cached
	req.response = resCh
	db.getPasswordChan <- req

	res := <-resCh
	if res.err != nil {
		return "", res.err
	}
	return res.password, nil
}

func (db *DBChan) CheckPassword(username, password string) (bool, error) {
	cached := true

	for {
		resCh := make(chan getPasswordResult)
		req := getPasswordRequest{}
		req.username = username
		req.cached = cached
		req.response = resCh
		db.getPasswordChan <- req

		res := <-resCh
		if res.err != nil {
			return false, res.err
		}
		if password == res.password {
			return true, nil
		}
		if cached {
			cached = false
		} else {
			break
		}
	}
	return false, nil
}

func (db *DBChan) GetGroupOfCart(cart uint) (string, error) {
	resCh := make(chan getGroupOfCartResult)
	req := getGroupOfCartRequest{}
	req.cart = cart
	req.response = resCh
	db.getGroupOfCartChan <- req

	res := <-resCh
	if res.err != nil {
		return "", res.err
	}
	return res.group, nil
}

func (db *DBChan) GetShowInfo(showid uint) (string, int, int, []uint, error) {
	resCh := make(chan getShowInfoResult)
	req := getShowInfoRequest{}
	req.showid = showid
	req.response = resCh
	db.getShowInfoChan <- req

	res := <-resCh
	if res.err != nil {
		return "", 0, 0, nil, res.err
	}
	return res.group, res.normLvl, res.trimLvl, res.carts, nil
}

func (db *DBChan) CheckMusicGroup(groupname string) (bool, error) {
	resCh := make(chan checkMusicGroupResult)
	req := checkMusicGroupRequest{}
	req.group = groupname
	req.response = resCh
	db.checkMusicGroupChan <- req

	res := <-resCh
	if res.err != nil {
		return false, res.err
	}
	return res.ismusic, nil
}

func (db *DBChan) GetMusicInfo(groupname string) (int, int, error) {
	resCh := make(chan getMusicInfoResult)
	req := getMusicInfoRequest{}
	req.group = groupname
	req.response = resCh
	db.getMusicInfoChan <- req

	res := <-resCh
	if res.err != nil {
		return 0, 0, res.err
	}
	return res.normLvl, res.trimLvl, nil
}

func (db *DBChan) GetShowList(username string) (ShowList, error) {
	resCh := make(chan getShowListResult)
	req := getShowListRequest{}
	req.username = username
	req.response = resCh
	db.getShowListChan <- req

	res := <-resCh
	if res.err != nil {
		return nil, res.err
	}
	return res.shows, nil
}

func (db *DBChan) GetShowCartList(show ShowListEntry) ([]CartListEntry, error) {
	resCh := make(chan getShowCartListResult)
	req := getShowCartListRequest{}
	req.show = show
	req.response = resCh
	db.getShowCartListChan <- req

	res := <-resCh
	if res.err != nil {
		return nil, res.err
	}
	return res.carts, nil
}

func (db *DBChan) GetJingleList(username string) (JingleList, error) {
	resCh := make(chan getJingleListResult)
	req := getJingleListRequest{}
	req.username = username
	req.response = resCh
	db.getJingleListChan <- req

	res := <-resCh
	if res.err != nil {
		return nil, res.err
	}
	return res.jingles, nil
}

func (db *DBChan) GetJingleCutList(jingle JingleListEntry) (CartListEntry, CartListEntry, error) {
	resCh := make(chan getJingleCutListResult)
	req := getJingleCutListRequest{}
	req.jingle = jingle
	req.response = resCh
	db.getJingleCutListChan <- req

	res := <-resCh
	return res.active, res.inactive, res.err
}

func (db *DB) GetInterface() *DBChan {
	ch := &DBChan{}
	ch.getPasswordChan = db.getPasswordChan
	ch.getGroupOfCartChan = db.getGroupOfCartChan
	ch.getShowInfoChan = db.getShowInfoChan
	ch.checkMusicGroupChan = db.checkMusicGroupChan
	ch.getMusicInfoChan = db.getMusicInfoChan
	ch.getShowListChan = db.getShowListChan
	ch.getShowCartListChan = db.getShowCartListChan
	ch.getJingleListChan = db.getJingleListChan
	ch.getJingleCutListChan = db.getJingleCutListChan
	return ch
}

func (db *DB) Cleanup() {
	db.quit <- true
	<-db.done
	close(db.quit)
	close(db.done)
	close(db.getPasswordChan)
	if db.dbh != nil {
		db.dbh.Close()
	}
	if db.getPasswordStmt != nil {
		db.getPasswordStmt.Close()
	}
	if db.getGroupOfCartStmt != nil {
		db.getGroupOfCartStmt.Close()
	}
	if db.getShowInfoStmt != nil {
		db.getShowInfoStmt.Close()
	}
	if db.checkMusicGroupStmt != nil {
		db.checkMusicGroupStmt.Close()
	}
	if db.getMusicInfoStmt != nil {
		db.getMusicInfoStmt.Close()
	}
	if db.getShowListStmt != nil {
		db.getShowListStmt.Close()
	}
	if db.getCartInfoStmt != nil {
		db.getCartInfoStmt.Close()
	}
	if db.getCutInfoStmt != nil {
		db.getCutInfoStmt.Close()
	}
	if db.getJingleListStmt != nil {
		db.getJingleListStmt.Close()
	}
}

func NewDB(configfile string) (db *DB, err error) {
	db = new(DB)
	if db.conf, err = newConfig(configfile); err != nil {
		return
	}
	db.quit = make(chan bool)
	db.done = make(chan bool)
	db.passwordCache = make(map[string]string)
	db.getPasswordChan = make(chan getPasswordRequest, 10)
	db.getGroupOfCartChan = make(chan getGroupOfCartRequest, 10)
	db.getShowInfoChan = make(chan getShowInfoRequest, 10)
	db.checkMusicGroupChan = make(chan checkMusicGroupRequest, 10)
	db.getMusicInfoChan = make(chan getMusicInfoRequest, 10)
	db.getShowListChan = make(chan getShowListRequest, 10)
	db.getShowCartListChan = make(chan getShowCartListRequest, 10)
	db.getJingleListChan = make(chan getJingleListRequest, 10)
	db.getJingleCutListChan = make(chan getJingleCutListRequest, 10)

	if err = db.init(); err != nil {
		return
	}

	go db.dispatchRequests()
	return
}