9 files changed, 309 insertions, 0 deletions
diff --git a/pkg/database/database.go b/pkg/database/database.go
new file mode 100644
index 0000000..b236d2d
--- /dev/null
+++ b/pkg/database/database.go
@@ -0,0 +1,31 @@
+package database
+
+import (
+	"database/sql"
+	"embed"
+	"fmt"
+
+	_ "github.com/mattn/go-sqlite3"
+	"github.com/pressly/goose/v3"
+)
+
+//go:embed migrations/*.sql
+var embedMigrations embed.FS
+
+func Connect(path string) (*sql.DB, error) {
+	return sql.Open("sqlite3", path+"?_foreign_keys=on")
+}
+
+func Migrate(db *sql.DB) error {
+	goose.SetBaseFS(embedMigrations)
+
+	if err := goose.SetDialect("sqlite3"); err != nil {
+		return fmt.Errorf("could not set dialect: %w", err)
+	}
+
+	if err := goose.Up(db, "migrations"); err != nil {
+		return fmt.Errorf("could not apply migrations: %w", err)
+	}
+
+	return nil
+}
diff --git a/pkg/database/migrations/0001_initial.sql b/pkg/database/migrations/0001_initial.sql
new file mode 100644
index 0000000..5e69229
--- /dev/null
+++ b/pkg/database/migrations/0001_initial.sql
@@ -0,0 +1,19 @@
+-- +goose Up
+
+CREATE TABLE entries (
+  id          integer PRIMARY KEY,
+  title       text    NOT NULL,
+  kind        integer NOT NULL,
+  url         text    NOT NULL,
+  description text    NOT NULL,
+  timestamp   text    NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  FOREIGN KEY(kind) REFERENCES kinds(id)
+) STRICT;
+
+CREATE TABLE kinds (
+  id    integer PRIMARY KEY,
+  name  text    NOT NULL,
+  emoji text    NOT NULL
+) STRICT;
+
+CREATE INDEX idx_kinds_name ON kinds(name);
diff --git a/pkg/database/migrations/0002_seed.sql b/pkg/database/migrations/0002_seed.sql
new file mode 100644
index 0000000..8be0bba
--- /dev/null
+++ b/pkg/database/migrations/0002_seed.sql
@@ -0,0 +1,6 @@
+-- +goose Up
+
+INSERT INTO kinds (name, emoji)
+VALUES
+  ("read", "👀"),
+  ("starred", "⭐");
diff --git a/pkg/database/query/entries.sql b/pkg/database/query/entries.sql
new file mode 100644
index 0000000..1927468
--- /dev/null
+++ b/pkg/database/query/entries.sql
@@ -0,0 +1,11 @@
+-- name: CreateEntryWithKindName :one
+INSERT INTO entries (title, kind, url, description) 
+SELECT ?, kinds.id, ?, ?
+FROM kinds
+WHERE kinds.name = ?
+RETURNING *;
+
+-- name: GetEntries :many
+SELECT title, url, description, timestamp, kinds.name as kind_name, kinds.emoji as kind_emoji FROM entries
+JOIN kinds ON entries.id == kinds.id
+ORDER BY timestamp DESC;
diff --git a/pkg/database/sqlc/db.go b/pkg/database/sqlc/db.go
new file mode 100644
index 0000000..e4d7828
--- /dev/null
+++ b/pkg/database/sqlc/db.go
@@ -0,0 +1,31 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package sqlc
+
+import (
+	"context"
+	"database/sql"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/pkg/database/sqlc/entries.sql.go b/pkg/database/sqlc/entries.sql.go
new file mode 100644
index 0000000..f412811
--- /dev/null
+++ b/pkg/database/sqlc/entries.sql.go
@@ -0,0 +1,89 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: entries.sql
+
+package sqlc
+
+import (
+	"context"
+)
+
+const createEntryWithKindName = `-- name: CreateEntryWithKindName :one
+INSERT INTO entries (title, kind, url, description) 
+SELECT ?, kinds.id, ?, ?
+FROM kinds
+WHERE kinds.name = ?
+RETURNING id, title, kind, url, description, timestamp
+`
+
+type CreateEntryWithKindNameParams struct {
+	Title       string `json:"title"`
+	Url         string `json:"url"`
+	Description string `json:"description"`
+	Name        string `json:"name"`
+}
+
+func (q *Queries) CreateEntryWithKindName(ctx context.Context, arg CreateEntryWithKindNameParams) (Entry, error) {
+	row := q.db.QueryRowContext(ctx, createEntryWithKindName,
+		arg.Title,
+		arg.Url,
+		arg.Description,
+		arg.Name,
+	)
+	var i Entry
+	err := row.Scan(
+		&i.ID,
+		&i.Title,
+		&i.Kind,
+		&i.Url,
+		&i.Description,
+		&i.Timestamp,
+	)
+	return i, err
+}
+
+const getEntries = `-- name: GetEntries :many
+SELECT title, url, description, timestamp, kinds.name as kind_name, kinds.emoji as kind_emoji FROM entries
+JOIN kinds ON entries.id == kinds.id
+ORDER BY timestamp DESC
+`
+
+type GetEntriesRow struct {
+	Title       string `json:"title"`
+	Url         string `json:"url"`
+	Description string `json:"description"`
+	Timestamp   string `json:"timestamp"`
+	KindName    string `json:"kind_name"`
+	KindEmoji   string `json:"kind_emoji"`
+}
+
+func (q *Queries) GetEntries(ctx context.Context) ([]GetEntriesRow, error) {
+	rows, err := q.db.QueryContext(ctx, getEntries)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetEntriesRow
+	for rows.Next() {
+		var i GetEntriesRow
+		if err := rows.Scan(
+			&i.Title,
+			&i.Url,
+			&i.Description,
+			&i.Timestamp,
+			&i.KindName,
+			&i.KindEmoji,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
diff --git a/pkg/database/sqlc/models.go b/pkg/database/sqlc/models.go
new file mode 100644
index 0000000..2e74c4b
--- /dev/null
+++ b/pkg/database/sqlc/models.go
@@ -0,0 +1,20 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package sqlc
+
+type Entry struct {
+	ID          int64  `json:"id"`
+	Title       string `json:"title"`
+	Kind        int64  `json:"kind"`
+	Url         string `json:"url"`
+	Description string `json:"description"`
+	Timestamp   string `json:"timestamp"`
+}
+
+type Kind struct {
+	ID    int64  `json:"id"`
+	Name  string `json:"name"`
+	Emoji string `json:"emoji"`
+}
diff --git a/pkg/entries/entries.go b/pkg/entries/entries.go
new file mode 100644
index 0000000..2b0967b
--- /dev/null
+++ b/pkg/entries/entries.go
@@ -0,0 +1,52 @@
+package entries
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+
+	"git.leonardobishop.net/history/pkg/database/sqlc"
+)
+
+type Service interface {
+	CreateEntry(title, kind, url, description string) (*sqlc.Entry, error)
+	GetEntries() ([]sqlc.GetEntriesRow, error)
+}
+
+type service struct {
+	db *sql.DB
+}
+
+func NewService(db *sql.DB) Service {
+	return &service{
+		db: db,
+	}
+}
+
+func (s *service) CreateEntry(title, kind, url, description string) (*sqlc.Entry, error) {
+	queries := sqlc.New(s.db)
+
+	entry, err := queries.CreateEntryWithKindName(context.Background(), sqlc.CreateEntryWithKindNameParams{
+		Title:       title,
+		Url:         url,
+		Description: description,
+		Name:        kind,
+	})
+
+	if err != nil {
+		return nil, fmt.Errorf("could not create entry: %w", err)
+	}
+
+	return &entry, nil
+}
+
+func (s *service) GetEntries() ([]sqlc.GetEntriesRow, error) {
+	queries := sqlc.New(s.db)
+
+	entries, err := queries.GetEntries(context.Background())
+	if err != nil {
+		return make([]sqlc.GetEntriesRow, 0), fmt.Errorf("could not get entries: %w", err)
+	}
+
+	return entries, nil
+}
diff --git a/pkg/html/service.go b/pkg/html/service.go
new file mode 100644
index 0000000..cba3a6c
--- /dev/null
+++ b/pkg/html/service.go
@@ -0,0 +1,50 @@
+package html
+
+import (
+	"time"
+
+	"git.leonardobishop.net/history/pkg/database/sqlc"
+)
+
+type Service interface {
+	GenerateHtml([]sqlc.GetEntriesRow) (string, error)
+}
+
+type service struct{}
+
+func NewService() Service {
+	return &service{}
+}
+
+func (s *service) GenerateHtml(entries []sqlc.GetEntriesRow) (string, error) {
+	var str string
+
+	var currentDate time.Time
+
+	for _, entry := range entries {
+		date, err := time.Parse(time.DateTime, entry.Timestamp)
+		if err != nil {
+			return "", err
+		}
+
+		if currentDate.Year() != date.Year() || currentDate.Month() != date.Month() {
+			str = str + "<h2>" + date.Format("January 2006") + "</h2>"
+		}
+
+		currentDate = date
+
+		str += "<span class=\"entry\">"
+		if entry.KindName == "starred" {
+			str += "<b>"
+		}
+		str += "<a class=\"entry-title\" href=" + entry.Url + " target=\"_blank\">" + entry.KindEmoji + " " + entry.Title + "</a>"
+		if entry.KindName == "starred" {
+			str += "</b>"
+		}
+		str += " - <span class=\"entry-description\">" + entry.Description + "</span>"
+		str += " - <span class=\"entry-date\">" + date.Format(time.DateOnly) + "</span>"
+		str += "</span><br>"
+	}
+
+	return str, nil
+}