package storage

import (
	"database/sql"
	"log"
	"time"

	"go.rctt.net/solec/core"
)

type Database struct {
	*sql.DB
}

const initSql = `
	CREATE TABLE IF NOT EXISTS messages
		(id INTEGER NOT NULL PRIMARY KEY, source STRING, target STRING, timestamp INT, content STRING);

	CREATE TABLE IF NOT EXISTS users
		(addr STRING NOT NULL PRIMARY KEY, pass STRING);

	CREATE TABLE IF NOT EXISTS permissions
		(user STRING, channel STRING, read INT, write INT, PRIMARY KEY (user, channel));
`

func InitDb(path string) (*Database, error) {
	db, err := sql.Open("sqlite3", path)
	if err != nil {
		return nil, err
	}

	_, err = db.Exec(initSql)
	return &Database{db}, nil
}

func (db *Database) AddMessage(msg core.Message) (err error) {
	_, err = db.Exec(
		"INSERT INTO messages (source, target, timestamp, content) VALUES (?, ?, ?, ?);",
		msg.Source, msg.Target, msg.Timestamp.Unix(), msg.Content,
	)

	return err
}

func (db *Database) GetHistory(target string, since time.Time, num, offset int) (history []core.Message, err error) {
	rows, err := db.Query(
		`SELECT source, target, timestamp, content FROM messages
			WHERE target = ? AND timestamp > ? LIMIT ? OFFSET ?`,
		target, since.Unix(), num, offset,
	)

	return db.getHistory(rows, err)
}

func (db *Database) GetHistoryUser(user1, user2 string, since time.Time, num int, offset int) (history []core.Message, err error) {
	rows, err := db.Query(
		`SELECT source, target, timestamp, content FROM messages
			WHERE (source = ? OR source = ? ) AND (target = ? OR target = ?) AND timestamp > ? LIMIT ? OFFSET ?`,
		user1, user2, user1, user2, since.Unix(), num, offset,
	)

	return db.getHistory(rows, err)
}

func (db *Database) getHistory(rows *sql.Rows, err error) ([]core.Message, error) {
	var history []core.Message

	defer func() {
		if rows == nil {
			return
		}
		if err = rows.Close(); err != nil {
			log.Println("cannot close database row:", err)
		}
	}()

	if err != nil {
		return history, err
	}

	for rows.Next() {
		var msg core.Message
		var timestamp int64
		if err = rows.Scan(&msg.Source, &msg.Target, &timestamp, &msg.Content); err != nil {
			return history, err
		}
		msg.Timestamp = time.Unix(timestamp, 0)

		history = append(history, msg)
	}

	return history, nil
}

func (db *Database) SetUser(user core.UserData) error {
	_, err := db.Exec(
		"INSERT OR REPLACE INTO users (addr, pass) VALUES (?, ?);",
		user.Name, user.Pass,
	)

	return err
}

func (db *Database) DelUser(addr string) error {
	_, err := db.Exec("DELETE FROM users WHERE addr = ?", addr)
	return err
}

func (db *Database) GetUserPass(addr string) (string, error) {
	var pass string

	err := db.QueryRow("SELECT pass FROM users WHERE addr = ?", addr).Scan(&pass)
	if err != nil {
		return "", err
	}

	return pass, nil
}

func (db *Database) SetPermission(perm core.PermissionData) error {
	_, err := db.Exec(
		"INSERT OR REPLACE INTO permissions (user, channel, read, write) VALUES (?, ?, ?, ?);",
		perm.User, perm.Channel, perm.Read, perm.Write, perm.User, perm.Channel,
	)

	return err
}

func (db *Database) GetPermission(user, channel string) (core.PermissionData, error) {
	var read, write int

	err := db.QueryRow(
		"SELECT read, write FROM permissions WHERE user = ? AND channel = ?", user, channel).
		Scan(&read, &write)
	if err != nil {
		return core.PermissionData{}, err
	}

	return core.PermissionData{
		User:    user,
		Channel: channel,
		Read:    itob(read),
		Write:   itob(write),
	}, nil
}

func (db *Database) GetChannelUsers(channel string) (users []string, err error) {
	rows, err := db.Query("SELECT user FROM permissions WHERE channel = ? AND write = 1;", channel)
	defer func() {
		if rows == nil {
			return
		}
		if err := rows.Close(); err != nil {
			log.Println("cannot close database row:", err)
		}
	}()
	if err != nil {
		return users, err
	}

	for rows.Next() {
		var user string
		if err := rows.Scan(&user); err != nil {
			return users, err
		}
		users = append(users, user)
	}

	return users, nil
}

func (db *Database) GetUserChannels(user string, count, offset int) (channels []string, err error) {
	rows, err := db.Query("SELECT channel FROM permissions WHERE user = ? AND write = 1;", user)
	defer func() {
		if rows == nil {
			return
		}
		if err := rows.Close(); err != nil {
			log.Println("cannot close database row:", err)
		}
	}()
	if err != nil {
		return channels, err
	}

	for rows.Next() {
		var channel string
		if err := rows.Scan(&channel); err != nil {
			return channels, err
		}
		channels = append(channels, channel)
	}

	return channels, nil
}

func itob(v int) bool {
	if v == 1 {
		return true
	}
	return false
}