//
// SQLite3 storage backend for Snippets system.  This is quite
// inefficient since it does locking on all database access, but it is
// good enough for prototyping.  I'm not even sure if I need the
// locking, perhaps SQLite3 is more robustly built than I think, but
// for now I'll just leave it this way.
//
// To speed up builds I had to install go-sqlite3:
//
//   go install github.com/mattn/go-sqlite3
//
//
package store

import (
	"database/sql"
	"errors"
	"log"
	"os"
	"sync"
	"time"

	"github.com/borud/snippets/model"
	_ "github.com/mattn/go-sqlite3"
)

// Database schema using SQLite3 DDL.
//
const schema = `
PRAGMA foreign_keys = ON;
PRAGMA defer_foreign_keys = FALSE;

CREATE TABLE IF NOT EXISTS organizations (
       name         VARCHAR(80) PRIMARY KEY NOT NULL,
       description  VARCHAR(255),
       imageurl	    VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS users (
       username     VARCHAR(128) PRIMARY KEY NOT NULL,
       password     VARCHAR(128) NOT NULL,
       fullname     VARCHAR(128) NOT NULL,
       email	    VARCHAR(128) NOT NULL,
       organization VARCHAR(128) NOT NULL,
       imageurl	    VARCHAR(255) NOT NULL,

	   FOREIGN KEY(organization) REFERENCES organizations(name)
);
CREATE INDEX organization_idx ON users (organization);

CREATE TABLE IF NOT EXISTS snippets (
	   id         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	   username   VARCHAR(255) NOT NULL,
	   ts		  timestamp NOT NULL,
	   content	  TEXT NOT NULL,

	   FOREIGN KEY(username) REFERENCES users(username)
);
CREATE INDEX snippets_user_idx ON snippets (username);

CREATE TABLE IF NOT EXISTS follows (
	   username		 VARCHAR(255) NOT NULL,
	   follows		 VARCHAR(255) NOT NULL,
	   since		 INTEGER,

 	   FOREIGN KEY(username) REFERENCES users(username),
	   FOREIGN KEY(follows) REFERENCES users(username)
);

INSERT INTO organizations (name, description, imageurl) VALUES('Telenor', 'Telenor is a digital company', '/static/img/c.png');
INSERT INTO organizations (name, description, imageurl) VALUES('Other', 'Telenor is a digital company', '/static/img/c.png');

/* password is 'secretive' */
INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('borud', '$2a$08$xHrcnewvS.icboGjrLqH3e/6/vZgPAJp9/fOW1weaXZmreiGQlAfq', 'Bjørn Borud', 'borud@telenordigital.com', 'Telenor', 'https://avatars3.githubusercontent.com/u/1595');
INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('stalehd', '', 'Ståle Holberg Dahl', 'stalehd@telenordigital.com', 'Telenor', 'https://media.licdn.com/mpr/mpr/shrinknp_200_200/p/3/000/090/163/20f1209.jpg');
INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('tlan', '', 'Thomas Langås', 'tlan@telenordigital.com', 'Telenor', 'https://yt3.ggpht.com/-FtFMyyKtpiM/AAAAAAAAAAI/AAAAAAAAAAA/y-tBQ0qv4gE/s900-c-k-no-mo-rj-c0xffffff/photo.jpg');
INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('hansj', '', 'Hans Jørgen Grimstad', 'hansj@telenordigital.com', 'Telenor', 'https://avatars3.githubusercontent.com/u/483862');
INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('pkk', '', 'Per Kristian Kummermo', 'pkk@telenordigital.com', 'Telenor', 'https://media.licdn.com/mpr/mpr/shrinknp_200_200/p/2/005/030/2b7/164fcba.jpg');

INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('a', '', 'Testuser A', 'a@example.com', 'Other', '/static/img/u.jpg');
INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('b', '', 'Testuser B', 'b@example.com', 'Other', '/static/img/u.jpg');

INSERT INTO snippets (username, ts, content) VALUES ('borud', date('now'), 'This is a test snippet');
INSERT INTO snippets (username, ts, content) VALUES ('borud', date('now', '-1 day'), 'This is another test snippet');
INSERT INTO snippets (username, ts, content) VALUES ('stalehd', date('now'), 'Foo bar baz');
INSERT INTO snippets (username, ts, content) VALUES ('stalehd', date('now','-2 day'), 'The dingo ate my baby');
INSERT INTO snippets (username, ts, content) VALUES ('pkk', date('now'), 'Some stuff');
INSERT INTO snippets (username, ts, content) VALUES ('pkk', date('now', '-5 day'), 'Some more stuff');
INSERT INTO snippets (username, ts, content) VALUES ('tlan', date('now'), 'Spent mucho dineros');
INSERT INTO snippets (username, ts, content) VALUES ('tlan', date('now', '-9 day'), 'Blah di blah');

INSERT INTO follows (username, follows, since) VALUES ('borud', 'stalehd', date('now'));
INSERT INTO follows (username, follows, since) VALUES ('stalehd', 'borud', date('now'));
INSERT INTO follows (username, follows, since) VALUES ('tlan', 'borud', date('now'));
INSERT INTO follows (username, follows, since) VALUES ('pkk', 'borud', date('now'));
`

// Struct containing the storage state information.
//
type SqliteStore struct {
	mutex    *sync.Mutex
	fileName string
	db       *sql.DB
	isOpen   bool
}

// Create an SqliteStore instance.
//
func CreateSqliteStore(dbFile string) Store {
	ss := SqliteStore{
		mutex:    &sync.Mutex{},
		fileName: dbFile,
		isOpen:   false,
	}

	return &ss
}

// Convert time to milliseconds since epoch.
//
func unixMilli(t time.Time) int64 {
	return t.Round(time.Millisecond).UnixNano() / (int64(time.Millisecond) / int64(time.Nanosecond))
}

// Raise panic of the database is not open when we assume it is.
//
func (st *SqliteStore) ensureDbOpen() {
	if !st.isOpen {
		panic("Database not open")
	}
}

// Initialize database instance.
//
func (st *SqliteStore) Init() (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	// Record if database existed prior to opening it.
	var databaseFileExisted = false
	if _, err := os.Stat(st.fileName); err == nil {
		databaseFileExisted = true
	}

	db, err := sql.Open("sqlite3", st.fileName)
	if err != nil {
		return errors.New("Unable to open database in " + st.fileName)
	}

	st.db = db

	// Ping the database to make sure it is there.
	if err = st.db.Ping(); err != nil {
		return errors.New("Failed to ping database right after opening")
	}

	// If the database did not exist and we just created it the schema has to be initialized.
	if !databaseFileExisted {
		_ = st.createSchema()
	}

	st.isOpen = true

	return nil
}

// Create database schema
//
func (st *SqliteStore) createSchema() error {
	log.Printf("Creating database schema")
	if _, err := st.db.Exec(schema); err != nil {
		panic("Unable to create schema: " + err.Error())
	}

	return nil
}

func (st *SqliteStore) CreateSnippet(snippet model.Snippet) (id int64, err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()
	st.ensureDbOpen()

	res, err := st.db.Exec("INSERT INTO snippets (username, ts, content) values(?, date('now'), ?)",
		snippet.Username,
		snippet.Content)

	if err != nil {
		return
	}

	id, err = res.LastInsertId()

	return
}

func (st *SqliteStore) ReadSnippet(snippetId int64) (snippet model.Snippet, err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	err = st.db.QueryRow("SELECT id, username, ts, strftime('%W', ts), content FROM snippets WHERE id = ?", snippetId).
		Scan(&snippet.SnippetId,
			&snippet.Username,
			&snippet.Timestamp,
			&snippet.Week,
			&snippet.Content)
	return
}

func (st *SqliteStore) UpdateSnippet(snippet model.Snippet) (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	_, err = st.db.Exec("UPDATE snippets SET username = ?, content = ? WHERE id = ?", snippet.Username, snippet.Content, snippet.SnippetId)

	return
}

func (st *SqliteStore) DeleteSnippet(snippetId int64) (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	_, err = st.db.Exec("DELETE FROM snippets WHERE id = ?", snippetId)

	return
}

func (st *SqliteStore) ListSnippetsMain(username string, offset int32, limit int32) (snippets []model.Snippet, err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()
	st.ensureDbOpen()

	//	rows, err := st.db.Query("SELECT id, username, ts, strftime('%W', ts), content FROM snippets ORDER BY ts DESC LIMIT ?,?", offset, limit)

	rows, err := st.db.Query("SELECT users.username, users.fullname, users.imageurl, snippets.id, snippets.ts, strftime('%W', snippets.ts), snippets.content "+
		"FROM users, snippets "+
		"WHERE snippets.username = users.username "+
		"ORDER BY snippets.id DESC "+
		"LIMIT ?,?",
		offset,
		limit)

	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		snippet := model.Snippet{}
		user := model.User{}
		err = rows.Scan(
			&user.Username,
			&user.Fullname,
			&user.Imageurl,
			&snippet.SnippetId,
			&snippet.Timestamp,
			&snippet.Week,
			&snippet.Content,
		)

		snippet.Username = user.Username
		snippet.User = &user
		snippets = append(snippets, snippet)
	}
	return
}

func (st *SqliteStore) ListUserSnippets(username string, offset int32, limit int32) (snippets []model.Snippet, err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()
	st.ensureDbOpen()

	// rows, err := st.db.Query("SELECT id, username, ts, strftime('%W', ts), content FROM snippets WHERE username = ? ORDER BY ts DESC LIMIT ?,?", username, offset, limit)
	rows, err := st.db.Query("SELECT users.username, users.fullname, users.imageurl, snippets.id, snippets.ts, strftime('%W', snippets.ts), snippets.content "+
		"FROM users, snippets "+
		"WHERE snippets.username = ? AND snippets.username = users.username "+
		"ORDER BY snippets.id DESC "+
		"LIMIT ?,?",
		username,
		offset,
		limit)

	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		snippet := model.Snippet{}
		user := model.User{}

		err = rows.Scan(
			&user.Username,
			&user.Fullname,
			&user.Imageurl,
			&snippet.SnippetId,
			&snippet.Timestamp,
			&snippet.Week,
			&snippet.Content,
		)

		snippet.Username = user.Username
		snippet.User = &user
		snippets = append(snippets, snippet)
	}
	return
}

func (st *SqliteStore) CreateUser(user model.User) (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	_, err = st.db.Exec("INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES (?,?,?,?,?,?)",
		&user.Username,
		&user.Password,
		&user.Fullname,
		&user.Email,
		&user.Organization,
		&user.Imageurl)

	return
}

func (st *SqliteStore) ReadUser(username string) (user model.User, err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	err = st.db.QueryRow("SELECT username, password, fullname, email, organization, imageurl FROM users WHERE username = ?", username).
		Scan(&user.Username,
			&user.Password,
			&user.Fullname,
			&user.Email,
			&user.Organization,
			&user.Imageurl)

	return
}

func (st *SqliteStore) UpdateUser(user model.User) (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	// Note that organization, once set, cannot be changed via the normal API
	_, err = st.db.Exec("UPDATE users SET password = ?, fullname = ?, email = ?, imageurl = ? WHERE username = ?",
		user.Password,
		user.Fullname,
		user.Email,
		user.Imageurl,
		user.Username)

	return
}

func (st *SqliteStore) DeleteUser(username string) (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

	_, err = st.db.Exec("DELETE FROM users WHERE username = ?", username)

	return
}

func (st *SqliteStore) FollowUser(username string, follow string) (err error) {
	if username == follow {
		return errors.New("Cannot follow self")
	}

	st.mutex.Lock()
	defer st.mutex.Unlock()
	st.ensureDbOpen()

	_, err = st.db.Exec("INSERT INTO follows (username, follows, since) VALUES(?,?,date('now'))",
		&username,
		&follow)
	return
}

func (st *SqliteStore) UnfollowUser(username string, follows string) (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()
	st.ensureDbOpen()

	_, err = st.db.Exec("DELETE FROM follows WHERE username = ? AND follows = ?", username, follows)
	return
}

func (st *SqliteStore) ListFollow(username string) (follows []string, followers []string, err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()
	st.ensureDbOpen()

	rows, err := st.db.Query("SELECT username, follows FROM follows WHERE username = ? OR follows = ?", username, username)
	if err != nil {
		return
	}
	defer rows.Close()

	// Since we are querying both followers and whom the user follows,
	// we need to sort out what's what and split the two cases into
	// two different result arays.
	for rows.Next() {
		var first string
		var second string
		err = rows.Scan(&first, &second)
		if err != nil {
			return
		}

		if first == username {
			follows = append(follows, first)
		} else {
			followers = append(followers, first)
		}
	}

	return
}

func (st *SqliteStore) Close() (err error) {
	st.mutex.Lock()
	defer st.mutex.Unlock()

	st.ensureDbOpen()

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

	st.isOpen = false
	return
}