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)

	CreateUser(username, password string) (User, error)
	AuthenticateUser(username, password string) (User, error)
	GetById(id string) (User, error)
	AllUsers() ([]User, error)

	CreateSession(user User) (Session, error)
	DeleteSession(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
	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 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
}