1package postgres
 2
 3import (
 4	"database/sql"
 5	"fmt"
 6	"time"
 7
 8	"github.com/picosh/pico/pkg/db"
 9	"github.com/picosh/pico/pkg/shared"
10)
11
12func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
13	if !shared.IsValidSubdomain(name) {
14		return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
15	}
16
17	var id string
18	err := me.Db.QueryRow(`INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`, userID, name, projectDir).Scan(&id)
19	if err != nil {
20		return "", err
21	}
22	return id, nil
23}
24
25func (me *PsqlDB) UpdateProject(userID, name string) error {
26	_, err := me.Db.Exec(`UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`, userID, name, time.Now())
27	return err
28}
29
30func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
31	project := &db.Project{}
32	err := me.Db.Get(project, `SELECT * FROM projects WHERE user_id = $1 AND name = $2;`, userID, name)
33	if err != nil {
34		return nil, err
35	}
36	return project, nil
37}
38
39func (me *PsqlDB) UpsertProject(userID, projectName, projectDir string) (*db.Project, error) {
40	project, err := me.FindProjectByName(userID, projectName)
41	if err == nil {
42		// this just updates the `createdAt` timestamp, useful for book-keeping
43		err = me.UpdateProject(userID, projectName)
44		if err != nil {
45			me.Logger.Error("could not update project", "err", err)
46			return nil, err
47		}
48		return project, nil
49	}
50
51	_, err = me.InsertProject(userID, projectName, projectName)
52	if err != nil {
53		me.Logger.Error("could not create project", "err", err)
54		return nil, err
55	}
56	return me.FindProjectByName(userID, projectName)
57}
58
59func (me *PsqlDB) findPagesStats(userID string) (*db.UserServiceStats, error) {
60	stats := db.UserServiceStats{
61		Service: "pgs",
62	}
63	// the aggregates are NULL when the user has no projects
64	var first, latestCreated, latestUpdated sql.NullTime
65	err := me.Db.QueryRow(
66		`SELECT count(id), min(created_at), max(created_at), max(updated_at) FROM projects WHERE user_id=$1`,
67		userID,
68	).Scan(&stats.Num, &first, &latestCreated, &latestUpdated)
69	if err != nil {
70		return nil, err
71	}
72	stats.FirstCreatedAt = first.Time
73	stats.LastestCreatedAt = latestCreated.Time
74	stats.LatestUpdatedAt = latestUpdated.Time
75
76	return &stats, nil
77}