Feat: auth; login; signup; migrate to sqlite

4 files changed, 26 insertions, 68 deletions

diff --git a/internal/database/migrations/001_init.up.sql b/internal/database/migrations/001_init.up.sql
index 80ebcad..f7e41c1 100644
--- a/internal/database/migrations/001_init.up.sql
+++ b/internal/database/migrations/001_init.up.sql
@@ -1,21 +1,22 @@
 BEGIN;
 CREATE TABLE user_score (
-    id INT GENERATED BY DEFAULT AS IDENTITY,
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
     username TEXT UNIQUE NOT NULL,
-    burn_time TIMESTAMP NOT NULL DEFAULT NOW() + interval '1 day',
+    password TEXT NOT NULL,
+    burn_time TIMESTAMP NOT NULL,
     score SMALLINT NOT NULL,
-    created_at TIMESTAMP NOT NULL DEFAULT NOW()
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 
 CREATE TABLE action (
-    id INT GENERATED BY DEFAULT AS IDENTITY,
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
     name TEXT NOT NULL,
     magnitude SMALLINT NOT NULL DEFAULT 1,
     repeatable BOOLEAN NOT NULL DEFAULT FALSE,
     type TEXT NOT NULL,
     done BOOLEAN NOT NULL DEFAULT FALSE,
     username TEXT NOT NULL,
-    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     UNIQUE(username, name),
     CONSTRAINT fk_user_score
       FOREIGN KEY(username) 
diff --git a/internal/database/repos/action.go b/internal/database/repos/action.go
index 6024dd5..49ad95e 100644
--- a/internal/database/repos/action.go
+++ b/internal/database/repos/action.go
@@ -5,6 +5,7 @@ import "apjournal/internal/models"
 type ActionRepo interface {
 	DBActionCreate(req *models.Action) error
 	DBActionList(username string) ([]models.Action, error)
+	DBActionGetByName(name string) (*models.Action, error)
 	DBActionDone(name string) error
 	DBActionsToReset() error
 }
@@ -25,6 +26,15 @@ func (p *Provider) DBActionList(username string) ([]models.Action, error) {
 	return resp, nil
 }
 
+func (p *Provider) DBActionGetByName(name string) (*models.Action, error) {
+	resp := models.Action{}
+	query := "SELECT * FROM action WHERE name=$1;"
+	if err := p.db.Get(&resp, query, name); err != nil {
+		return nil, err
+	}
+	return &resp, nil
+}
+
 func (p *Provider) DBActionDone(name string) error {
 	// should reset at burn time
 	stmt := "UPDATE action SET done=true WHERE name=$1;"
diff --git a/internal/database/repos/userscore.go b/internal/database/repos/userscore.go
index 5c09004..2baf99f 100644
--- a/internal/database/repos/userscore.go
+++ b/internal/database/repos/userscore.go
@@ -10,8 +10,8 @@ type UserScoreRepo interface {
 
 func (p *Provider) DBUserScoreCreate(req *models.UserScore) error {
 	_, err := p.db.NamedExec(`
-    INSERT INTO user_score(username, burn_time, score) 
-    VALUES (:username, :burn_time, :score);`, req)
+    INSERT INTO user_score(username, burn_time, score, password) 
+    VALUES (:username, :burn_time, :score, :password);`, req)
 	return err
 }
 
diff --git a/internal/database/sql/main.go b/internal/database/sql/main.go
index 80d5f5c..5a523f6 100644
--- a/internal/database/sql/main.go
+++ b/internal/database/sql/main.go
@@ -1,23 +1,20 @@
 package database
 
 import (
-	"apjournal/internal/database/migrations"
 	"os"
 	"time"
 
-	"github.com/jmoiron/sqlx"
-
-	// driver postgres for migrations
 	"log/slog"
 
-	"github.com/golang-migrate/migrate"
-	_ "github.com/golang-migrate/migrate/database/postgres"
-	bindata "github.com/golang-migrate/migrate/source/go_bindata"
-	_ "github.com/jackc/pgx/v5/stdlib" // register pgx driver
+	"github.com/jmoiron/sqlx"
+	_ "github.com/mattn/go-sqlite3"
 	"github.com/pkg/errors"
 )
 
-var log = slog.New(slog.NewJSONHandler(os.Stdout, nil))
+var (
+	log      = slog.New(slog.NewJSONHandler(os.Stdout, nil))
+	dbDriver = "sqlite3"
+)
 
 type DB struct {
 	Conn *sqlx.DB
@@ -40,38 +37,17 @@ func closeConn(conn *sqlx.DB) error {
 func Init(DBURI string) (*DB, error) {
 	var result DB
 	var err error
-	result.Conn, err = openDBConnection(DBURI, "pgx")
+	result.Conn, err = openDBConnection(DBURI, dbDriver)
 	if err != nil {
 		return nil, err
 	}
 	result.URI = DBURI
-
 	if err := testConnection(result.Conn); err != nil {
 		return nil, err
 	}
 	return &result, nil
 }
 
-func InitWithMigrate(DBURI string, up bool) (*DB, error) {
-	var (
-		result DB
-		err    error
-	)
-	result.Conn, err = openDBConnection(DBURI, "pgx")
-	if err != nil {
-		return nil, err
-	}
-	result.URI = DBURI
-
-	if err = testConnection(result.Conn); err != nil {
-		return nil, err
-	}
-	if err = result.Migrate(DBURI, up); err != nil {
-		return nil, err
-	}
-	return &result, nil
-}
-
 func openDBConnection(dbURI, driver string) (*sqlx.DB, error) {
 	conn, err := sqlx.Open(driver, dbURI)
 	if err != nil {
@@ -88,38 +64,9 @@ func testConnection(conn *sqlx.DB) error {
 	return nil
 }
 
-func (db *DB) Migrate(url string, up bool) error {
-	source := bindata.Resource(migrations.AssetNames(), migrations.Asset)
-	driver, err := bindata.WithInstance(source)
-	if err != nil {
-		return errors.WithStack(errors.WithMessage(err,
-			"unable to instantiate driver from bindata"))
-	}
-	migration, err := migrate.NewWithSourceInstance("go-bindata",
-		driver, url)
-	if err != nil {
-		return errors.WithStack(errors.WithMessage(err,
-			"unable to start migration"))
-	}
-	if up {
-		if err = migration.Up(); err != nil && err.Error() != "no change" {
-			return errors.WithStack(errors.WithMessage(err,
-				"unable to migrate up"))
-		}
-	} else {
-		if err = migration.Down(); err != nil &&
-			err.Error() != "no change" {
-			return errors.WithStack(errors.WithMessage(err,
-				"unable to migrate down"))
-		}
-	}
-	return nil
-}
-
 func (d *DB) PingRoutine(interval time.Duration) {
 	ticker := time.NewTicker(interval)
 	done := make(chan bool)
-
 	for {
 		select {
 		case <-done:
@@ -131,7 +78,7 @@ func (d *DB) PingRoutine(interval time.Duration) {
 				if err := closeConn(d.Conn); err != nil {
 					log.Error("failed to close db connection", "error", err, "ping_at", t)
 				}
-				d.Conn, err = openDBConnection(d.URI, "pgx")
+				d.Conn, err = openDBConnection(d.URI, dbDriver)
 				if err != nil {
 					log.Error("failed to reconnect", "error", err, "ping_at", t)
 				}