package db

import (
	"os"
	"time"
	"io/fs"
	"errors"
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
)


type User struct {
	Id int
	Name string
	PasswordHash []byte
	Salt []byte
}


type Session struct {
	Id string
	UserId int
	Created time.Time
	Modified time.Time
}

type Endpoint struct {
	Id int
	Name string
	Path string
	Address string
}


type Model interface {
	Create(filename string) error
	Open(filename string) error
	Close() error

	GetSchemaVersion() (int, error)
	SetHostAddress(addr string) error
	GetHostAddress() (string, error)
	SetLoginPage(path string) error
	GetLoginPage() (string, error)

	CreateUser(username, password string) (User, error)
	DeleteUser(user User) error
	SetPassword(user User, password string) error
	AuthenticateUser(username, password string) (User, error)
	GetByUsername(username string) (User, error)
	GetById(id string) (User, error)
	AllUsers() ([]User, error)

	CreateSession(user User) (Session, error)
	DeleteSession(session Session) error
	TouchSession(session Session) error
	CheckSession(session Session) (bool, error)
	CleanSessions(maxIdle time.Duration) error
	AllSessions() ([]Session, error)

	CreateEndpoint(name, path, address string) (Endpoint, error)
	DeleteEndpoint(endpoint Endpoint) error
	SetEndpointPath(endpoint Endpoint, path string) error
	SetEndpointAddress(endpoint Endpoint, address string) error
	GetEndpointByName(name string) (Endpoint, error)
	GetEndpointByPath(path string) (Endpoint, error)
	GetEndpointByAddress(address string) (Endpoint, error)
	AllEndpoints() ([]Endpoint, error)
}


// interface to wrap sql.Row and sql.Rows
type Scanner interface {
	Scan(dest ...any) error
}


type Phlox struct {
	db *sql.DB
}


func fileExists(filename string) (bool, error) {
	_, err := os.Stat(filename)
	if err == nil {
		return true, nil
	} else if errors.Is(err, fs.ErrNotExist) {
		return false, nil
	} else {
		// unknown error
		return false, err
	}
}


func (p *Phlox) Create(filename string) error {
	exist, err := fileExists(filename)
	if err != nil {
		return err
	}

	if exist {
		// file already exists
		return fs.ErrExist
	}

	p.db, err = sql.Open("sqlite3", filename)
	if err != nil {
		return err
	}

	_, err = p.db.Exec(`
		create table schema(version integer);
		insert into schema values(0);

		create table config(key string primary key, value string);
		insert into config values("hostaddress", "localhost:3333");
		insert into config values("loginpage", "");

		create table users (userid integer not null primary key, username string, passwordhash string, salt string);
		create table sessions (sessionid string not null primary key, userid integer, created string, modified string, foreign key(userid) references users(userid));
		create table endpoints (endpointid integer not null primary key, name string, path string, address string);
	`)
	if err != nil {
		return err
	}

	return nil
}


func (p *Phlox) Open(filename string) error {
	exist, err := fileExists(filename)
	if err != nil {
		return err
	}

	if !exist {
		// no such file!
		return fs.ErrNotExist
	}

	p.db, err = sql.Open("sqlite3", filename)
	if err != nil {
		return err
	}

	return nil
}


func (p *Phlox) GetSchemaVersion() (int, error) {
	row := p.db.QueryRow("select max(version) from schema;")
	if row.Err() != nil {
		return -1, row.Err()
	}

	var version int
	err := row.Scan(&version)
	if err != nil {
		return -1, err
	}

	return version, nil
}


func (p *Phlox) SetHostAddress(addr string) error {
	_, err := p.db.Exec("update config set value=? where key='hostaddress';", addr)
	return err
}


func (p *Phlox) GetHostAddress() (string, error) {
	row := p.db.QueryRow("select value from config where key='hostaddress';")
	var addr string
	err := row.Scan(&addr)
	if err != nil {
		return "", err
	}
	return addr, nil
}


func (p *Phlox) SetLoginPage(path string) error {
	_, err := p.db.Exec("update config set value=? where key='loginpage';", path)
	return err
}


func (p *Phlox) GetLoginPage() (string, error) {
	row := p.db.QueryRow("select value from config where key='loginpage';")
	var path string
	err := row.Scan(&path)
	if err != nil {
		return "", err
	}
	return path, nil
}