git.stevedylan.dev

apps/posts/db.go 15.4 K raw

package main

import (
	"database/sql"
	"errors"
	"strings"
	"time"
	"fmt"

	"github.com/stevedylandev/andromeda/pkg/auth"
)

const postsSchema = `
CREATE TABLE IF NOT EXISTS posts (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    short_id        TEXT NOT NULL UNIQUE,
    title           TEXT,
    slug            TEXT NOT NULL UNIQUE,
    alias           TEXT,
    canonical_url   TEXT,
    published_date  TEXT,
    meta_description TEXT,
    meta_image      TEXT,
    lang            TEXT NOT NULL DEFAULT 'en',
    tags            TEXT,
		weather					TEXT,
    content         TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'draft',
    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
    updated_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE IF NOT EXISTS pages (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    short_id        TEXT NOT NULL UNIQUE,
    title           TEXT NOT NULL,
    slug            TEXT NOT NULL UNIQUE,
    content         TEXT NOT NULL,
    is_published    INTEGER NOT NULL DEFAULT 0,
    nav_order       INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
    updated_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS files (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    short_id        TEXT NOT NULL UNIQUE,
    filename        TEXT NOT NULL UNIQUE,
    original_name   TEXT NOT NULL,
    content_type    TEXT NOT NULL DEFAULT 'application/octet-stream',
    size            INTEGER NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
    storage_backend TEXT NOT NULL DEFAULT 'local'
);
`

var defaultSettings = [][2]string{
	{"blog_title", "My Blog"},
	{"blog_description", "A simple blog"},
	{"intro_content", ""},
	{"nav_links", "[blog](/) [posts](/posts)"},
	{"custom_css", ""},
	{"favicon_url", ""},
	{"og_image_url", ""},
	{"custom_header", ""},
	{"custom_footer", `<div>
<a href="/feed.xml" class="rss-link" title="RSS Feed"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" fill="currentColor" viewBox="0 0 256 256"><path fill="currentColor" d="M104.08 151.92A67.52 67.52 0 0 1 124 200a4 4 0 0 1-8 0a60 60 0 0 0-60-60a4 4 0 0 1 0-8a67.52 67.52 0 0 1 48.08 19.92M56 84a4 4 0 0 0 0 8a108 108 0 0 1 108 108a4 4 0 0 0 8 0A116 116 0 0 0 56 84m116 0A162.92 162.92 0 0 0 56 36a4 4 0 0 0 0 8a155 155 0 0 1 110.31 45.69A155 155 0 0 1 212 200a4 4 0 0 0 8 0a162.92 162.92 0 0 0-48-116M60 188a8 8 0 1 0 8 8a8 8 0 0 0-8-8"/></svg></a>
</div>`},
	{"default_location", ""},
}

func seedDefaultSettings(db *sql.DB) {
	for _, kv := range defaultSettings {
		_, _ = db.Exec(`INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)`, kv[0], kv[1])
	}
}

const postCols = `id, short_id, title, slug, alias, canonical_url, published_date, meta_description, meta_image, lang, tags, weather, content, status, created_at, updated_at`

func scanPost(s interface{ Scan(...any) error }) (*Post, error) {
	var p Post
	var title, alias, canonicalURL, publishedDate, metaDesc, metaImage, tags, weather sql.NullString
	err := s.Scan(&p.ID, &p.ShortID, &title, &p.Slug, &alias, &canonicalURL,
		&publishedDate, &metaDesc, &metaImage, &p.Lang, &tags, &weather, &p.Content,
		&p.Status, &p.CreatedAt, &p.UpdatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	if title.Valid {
		v := title.String
		p.Title = &v
	}
	if alias.Valid {
		v := alias.String
		p.Alias = &v
	}
	if canonicalURL.Valid {
		v := canonicalURL.String
		p.CanonicalURL = &v
	}
	if publishedDate.Valid {
		v := publishedDate.String
		p.PublishedDate = &v
	}
	if metaDesc.Valid {
		v := metaDesc.String
		p.MetaDescription = &v
	}
	if metaImage.Valid {
		v := metaImage.String
		p.MetaImage = &v
	}
	if tags.Valid {
		v := tags.String
		p.Tags = &v
	}
	if weather.Valid {
		v := weather.String
		p.Weather = &v
	}
	return &p, nil
}

type PostInput struct {
	Title           *string
	Slug            string
	Content         string
	Status          string
	Alias           *string
	CanonicalURL    *string
	PublishedDate   *string
	MetaDescription *string
	MetaImage       *string
	Lang            string
	Tags            *string
	Weather         *string
}

func nullable(p *string) any {
	if p == nil {
		return nil
	}
	return *p
}

func createPost(db *sql.DB, in PostInput) (*Post, error) {
	shortID, err := auth.GenerateShortID(10)
	if err != nil {
		return nil, err
	}
	in.PublishedDate = normalizePubDatePtr(in.PublishedDate)
	res, err := db.Exec(
		`INSERT INTO posts (short_id, title, slug, content, status, alias, canonical_url, published_date, meta_description, meta_image, lang, tags, weather)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		shortID, nullable(in.Title), in.Slug, in.Content, in.Status,
		nullable(in.Alias), nullable(in.CanonicalURL), nullable(in.PublishedDate),
		nullable(in.MetaDescription), nullable(in.MetaImage), in.Lang, nullable(in.Tags), nullable(in.Weather),
	)
	if err != nil {
		return nil, err
	}
	id, _ := res.LastInsertId()
	return scanPost(db.QueryRow(`SELECT `+postCols+` FROM posts WHERE id = ?`, id))
}

func getPostByShortID(db *sql.DB, shortID string) (*Post, error) {
	return scanPost(db.QueryRow(`SELECT `+postCols+` FROM posts WHERE short_id = ?`, shortID))
}

func getPostBySlug(db *sql.DB, slug string) (*Post, error) {
	return scanPost(db.QueryRow(`SELECT `+postCols+` FROM posts WHERE slug = ?`, slug))
}

func getAllPosts(db *sql.DB) ([]Post, error) {
	rows, err := db.Query(`SELECT ` + postCols + ` FROM posts ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Post
	for rows.Next() {
		p, err := scanPost(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *p)
	}
	return out, rows.Err()
}

func getPublishedPosts(db *sql.DB, limit int64) ([]Post, error) {
	if limit <= 0 {
		limit = -1
	}
	rows, err := db.Query(
		`SELECT `+postCols+` FROM posts WHERE status = 'published' ORDER BY published_date DESC, id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Post
	for rows.Next() {
		p, err := scanPost(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *p)
	}
	return out, rows.Err()
}

func updatePost(db *sql.DB, shortID string, in PostInput) (*Post, error) {
	in.PublishedDate = normalizePubDatePtr(in.PublishedDate)
	res, err := db.Exec(
		`UPDATE posts SET title = ?, slug = ?, content = ?, status = ?, alias = ?, canonical_url = ?,
		 published_date = CASE WHEN ? = 'published' THEN COALESCE(?, published_date, strftime('%Y-%m-%dT%H:%M:%SZ','now')) ELSE ? END,
		 meta_description = ?, meta_image = ?, lang = ?, tags = ?, weather = ?,
		 updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
		nullable(in.Title), in.Slug, in.Content, in.Status, nullable(in.Alias), nullable(in.CanonicalURL),
		in.Status, nullable(in.PublishedDate), nullable(in.PublishedDate),
		nullable(in.MetaDescription), nullable(in.MetaImage), in.Lang, nullable(in.Tags), nullable(in.Weather), shortID,
	)
	if err != nil {
		return nil, err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return nil, nil
	}
	return getPostByShortID(db, shortID)
}

func deletePost(db *sql.DB, shortID string) (bool, error) {
	res, err := db.Exec(`DELETE FROM posts WHERE short_id = ?`, shortID)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}

func togglePostStatus(db *sql.DB, shortID string) (string, error) {
	var current string
	err := db.QueryRow(`SELECT status FROM posts WHERE short_id = ?`, shortID).Scan(&current)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	newStatus := "published"
	if current == "published" {
		newStatus = "draft"
	}
	if newStatus == "published" {
		_, err = db.Exec(
			`UPDATE posts SET status = ?, published_date = COALESCE(published_date, strftime('%Y-%m-%dT%H:%M:%SZ','now')), updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
			newStatus, shortID)
	} else {
		_, err = db.Exec(`UPDATE posts SET status = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
			newStatus, shortID)
	}
	return newStatus, err
}

func findAliasRedirect(db *sql.DB, alias string) (string, error) {
	var slug string
	err := db.QueryRow(`SELECT slug FROM posts WHERE alias = ? AND status = 'published'`, alias).Scan(&slug)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return "/posts/" + slug, nil
}

const pageCols = `id, short_id, title, slug, content, is_published, nav_order, created_at, updated_at`

func scanPage(s interface{ Scan(...any) error }) (*Page, error) {
	var p Page
	var pub int
	err := s.Scan(&p.ID, &p.ShortID, &p.Title, &p.Slug, &p.Content, &pub, &p.NavOrder, &p.CreatedAt, &p.UpdatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	p.IsPublished = pub != 0
	return &p, nil
}

func createPage(db *sql.DB, title, slug, content string, isPublished bool, navOrder int64) (*Page, error) {
	shortID, err := auth.GenerateShortID(10)
	if err != nil {
		return nil, err
	}
	pub := 0
	if isPublished {
		pub = 1
	}
	res, err := db.Exec(
		`INSERT INTO pages (short_id, title, slug, content, is_published, nav_order) VALUES (?, ?, ?, ?, ?, ?)`,
		shortID, title, slug, content, pub, navOrder)
	if err != nil {
		return nil, err
	}
	id, _ := res.LastInsertId()
	return scanPage(db.QueryRow(`SELECT `+pageCols+` FROM pages WHERE id = ?`, id))
}

func getPageByShortID(db *sql.DB, shortID string) (*Page, error) {
	return scanPage(db.QueryRow(`SELECT `+pageCols+` FROM pages WHERE short_id = ?`, shortID))
}

func getPageBySlug(db *sql.DB, slug string) (*Page, error) {
	return scanPage(db.QueryRow(`SELECT `+pageCols+` FROM pages WHERE slug = ?`, slug))
}

func getAllPages(db *sql.DB) ([]Page, error) {
	rows, err := db.Query(`SELECT ` + pageCols + ` FROM pages ORDER BY nav_order ASC, id ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Page
	for rows.Next() {
		p, err := scanPage(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *p)
	}
	return out, rows.Err()
}

func updatePage(db *sql.DB, shortID, title, slug, content string, isPublished bool, navOrder int64) (*Page, error) {
	pub := 0
	if isPublished {
		pub = 1
	}
	res, err := db.Exec(
		`UPDATE pages SET title = ?, slug = ?, content = ?, is_published = ?, nav_order = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
		title, slug, content, pub, navOrder, shortID)
	if err != nil {
		return nil, err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return nil, nil
	}
	return getPageByShortID(db, shortID)
}

func deletePage(db *sql.DB, shortID string) error {
	_, err := db.Exec(`DELETE FROM pages WHERE short_id = ?`, shortID)
	return err
}

func getSetting(db *sql.DB, key string) (string, error) {
	var v string
	err := db.QueryRow(`SELECT value FROM settings WHERE key = ?`, key).Scan(&v)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return v, nil
}

func setSetting(db *sql.DB, key, value string) error {
	_, err := db.Exec(`INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
		key, value)
	return err
}

const fileCols = `id, short_id, filename, original_name, content_type, size, created_at, storage_backend`

func scanFile(s interface{ Scan(...any) error }) (*UploadedFile, error) {
	var f UploadedFile
	err := s.Scan(&f.ID, &f.ShortID, &f.Filename, &f.OriginalName, &f.ContentType, &f.Size, &f.CreatedAt, &f.StorageBackend)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &f, nil
}

func createFile(db *sql.DB, filename, originalName, contentType string, size int64, backend string) (*UploadedFile, error) {
	shortID, err := auth.GenerateShortID(10)
	if err != nil {
		return nil, err
	}
	if backend == "" {
		backend = "local"
	}
	res, err := db.Exec(
		`INSERT INTO files (short_id, filename, original_name, content_type, size, storage_backend) VALUES (?, ?, ?, ?, ?, ?)`,
		shortID, filename, originalName, contentType, size, backend)
	if err != nil {
		return nil, err
	}
	id, _ := res.LastInsertId()
	return scanFile(db.QueryRow(`SELECT `+fileCols+` FROM files WHERE id = ?`, id))
}

func getFileByFilename(db *sql.DB, filename string) (*UploadedFile, error) {
	return scanFile(db.QueryRow(`SELECT `+fileCols+` FROM files WHERE filename = ?`, filename))
}

func getAllFiles(db *sql.DB) ([]UploadedFile, error) {
	rows, err := db.Query(`SELECT ` + fileCols + ` FROM files ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []UploadedFile
	for rows.Next() {
		f, err := scanFile(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *f)
	}
	return out, rows.Err()
}

func deleteFile(db *sql.DB, shortID string) (*UploadedFile, error) {
	f, err := scanFile(db.QueryRow(`SELECT `+fileCols+` FROM files WHERE short_id = ?`, shortID))
	if err != nil || f == nil {
		return f, err
	}
	if _, err := db.Exec(`DELETE FROM files WHERE short_id = ?`, shortID); err != nil {
		return nil, err
	}
	return f, nil
}

func nowDatetime() string {
	return time.Now().UTC().Format(time.RFC3339)
}

// migrateTimestamps rewrites legacy "YYYY-MM-DD HH:MM:SS" and date-only values
// to RFC3339 UTC. Idempotent: skips values already in RFC3339 form.
func migrateTimestamps(db *sql.DB) error {
	stmts := []string{
		`UPDATE posts SET published_date = REPLACE(published_date, ' ', 'T') || 'Z'
		 WHERE published_date IS NOT NULL AND length(published_date) = 19
		   AND published_date LIKE '____-__-__ __:__:__'`,
		`UPDATE posts SET published_date = published_date || 'T00:00:00Z'
		 WHERE published_date IS NOT NULL AND length(published_date) = 10
		   AND published_date LIKE '____-__-__'`,
		`UPDATE posts SET created_at = REPLACE(created_at, ' ', 'T') || 'Z'
		 WHERE length(created_at) = 19 AND created_at LIKE '____-__-__ __:__:__'`,
		`UPDATE posts SET updated_at = REPLACE(updated_at, ' ', 'T') || 'Z'
		 WHERE length(updated_at) = 19 AND updated_at LIKE '____-__-__ __:__:__'`,
		`UPDATE pages SET created_at = REPLACE(created_at, ' ', 'T') || 'Z'
		 WHERE length(created_at) = 19 AND created_at LIKE '____-__-__ __:__:__'`,
		`UPDATE pages SET updated_at = REPLACE(updated_at, ' ', 'T') || 'Z'
		 WHERE length(updated_at) = 19 AND updated_at LIKE '____-__-__ __:__:__'`,
		`UPDATE files SET created_at = REPLACE(created_at, ' ', 'T') || 'Z'
		 WHERE length(created_at) = 19 AND created_at LIKE '____-__-__ __:__:__'`,
	}
	for _, s := range stmts {
		if _, err := db.Exec(s); err != nil {
			return err
		}
	}
	return nil
}

// migrateWeather updates tables to have new optional weather column
func migrateWeather(db *sql.DB) error {
	var count int
	if err := db.QueryRow(
		`SELECT COUNT(*) FROM pragma_table_info('posts') WHERE name = 'weather'`,
	).Scan(&count); err != nil {
		return err
	}
	if count == 0 {
		_, err := db.Exec(`ALTER TABLE posts ADD COLUMN weather TEXT`)
		return err
	}
	return nil
}

func normalizePubDatePtr(p *string) *string {
	if p == nil {
		return nil
	}
	if v, ok := parsePubDate(*p); ok {
		return &v
	}
	return p
}

func runMigrations(db *sql.DB) error {
    if err := migrateTimestamps(db); err != nil {
        return fmt.Errorf("migrate timestamps: %w", err)
    }
    if err := migrateWeather(db); err != nil {
        return fmt.Errorf("add weather column: %w", err)
    }
    return nil
}

func _useStrings() {
	_ = strings.TrimSpace
}

1	package main
2
3	import (
4	"database/sql"
5	"errors"
6	"strings"
7	"time"
8	"fmt"
9
10	"github.com/stevedylandev/andromeda/pkg/auth"
11	)
12
13	const postsSchema = `
14	CREATE TABLE IF NOT EXISTS posts (
15	id INTEGER PRIMARY KEY AUTOINCREMENT,
16	short_id TEXT NOT NULL UNIQUE,
17	title TEXT,
18	slug TEXT NOT NULL UNIQUE,
19	alias TEXT,
20	canonical_url TEXT,
21	published_date TEXT,
22	meta_description TEXT,
23	meta_image TEXT,
24	lang TEXT NOT NULL DEFAULT 'en',
25	tags TEXT,
26	weather TEXT,
27	content TEXT NOT NULL,
28	status TEXT NOT NULL DEFAULT 'draft',
29	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
30	updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
31	);
32
33	CREATE TABLE IF NOT EXISTS pages (
34	id INTEGER PRIMARY KEY AUTOINCREMENT,
35	short_id TEXT NOT NULL UNIQUE,
36	title TEXT NOT NULL,
37	slug TEXT NOT NULL UNIQUE,
38	content TEXT NOT NULL,
39	is_published INTEGER NOT NULL DEFAULT 0,
40	nav_order INTEGER NOT NULL DEFAULT 0,
41	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
42	updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
43	);
44
45	CREATE TABLE IF NOT EXISTS settings (
46	key TEXT PRIMARY KEY,
47	value TEXT NOT NULL
48	);
49
50	CREATE TABLE IF NOT EXISTS files (
51	id INTEGER PRIMARY KEY AUTOINCREMENT,
52	short_id TEXT NOT NULL UNIQUE,
53	filename TEXT NOT NULL UNIQUE,
54	original_name TEXT NOT NULL,
55	content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
56	size INTEGER NOT NULL,
57	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
58	storage_backend TEXT NOT NULL DEFAULT 'local'
59	);
60	`
61
62	var defaultSettings = [][2]string{
63	{"blog_title", "My Blog"},
64	{"blog_description", "A simple blog"},
65	{"intro_content", ""},
66	{"nav_links", "[blog](/) [posts](/posts)"},
67	{"custom_css", ""},
68	{"favicon_url", ""},
69	{"og_image_url", ""},
70	{"custom_header", ""},
71	{"custom_footer", `<div>
72	<a href="/feed.xml" class="rss-link" title="RSS Feed"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" fill="currentColor" viewBox="0 0 256 256"><path fill="currentColor" d="M104.08 151.92A67.52 67.52 0 0 1 124 200a4 4 0 0 1-8 0a60 60 0 0 0-60-60a4 4 0 0 1 0-8a67.52 67.52 0 0 1 48.08 19.92M56 84a4 4 0 0 0 0 8a108 108 0 0 1 108 108a4 4 0 0 0 8 0A116 116 0 0 0 56 84m116 0A162.92 162.92 0 0 0 56 36a4 4 0 0 0 0 8a155 155 0 0 1 110.31 45.69A155 155 0 0 1 212 200a4 4 0 0 0 8 0a162.92 162.92 0 0 0-48-116M60 188a8 8 0 1 0 8 8a8 8 0 0 0-8-8"/></svg></a>
73	</div>`},
74	{"default_location", ""},
75	}
76
77	func seedDefaultSettings(db *sql.DB) {
78	for _, kv := range defaultSettings {
79	_, _ = db.Exec(`INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)`, kv[0], kv[1])
80	}
81	}
82
83	const postCols = `id, short_id, title, slug, alias, canonical_url, published_date, meta_description, meta_image, lang, tags, weather, content, status, created_at, updated_at`
84
85	func scanPost(s interface{ Scan(...any) error }) (*Post, error) {
86	var p Post
87	var title, alias, canonicalURL, publishedDate, metaDesc, metaImage, tags, weather sql.NullString
88	err := s.Scan(&p.ID, &p.ShortID, &title, &p.Slug, &alias, &canonicalURL,
89	&publishedDate, &metaDesc, &metaImage, &p.Lang, &tags, &weather, &p.Content,
90	&p.Status, &p.CreatedAt, &p.UpdatedAt)
91	if errors.Is(err, sql.ErrNoRows) {
92	return nil, nil
93	}
94	if err != nil {
95	return nil, err
96	}
97	if title.Valid {
98	v := title.String
99	p.Title = &v
100	}
101	if alias.Valid {
102	v := alias.String
103	p.Alias = &v
104	}
105	if canonicalURL.Valid {
106	v := canonicalURL.String
107	p.CanonicalURL = &v
108	}
109	if publishedDate.Valid {
110	v := publishedDate.String
111	p.PublishedDate = &v
112	}
113	if metaDesc.Valid {
114	v := metaDesc.String
115	p.MetaDescription = &v
116	}
117	if metaImage.Valid {
118	v := metaImage.String
119	p.MetaImage = &v
120	}
121	if tags.Valid {
122	v := tags.String
123	p.Tags = &v
124	}
125	if weather.Valid {
126	v := weather.String
127	p.Weather = &v
128	}
129	return &p, nil
130	}
131
132	type PostInput struct {
133	Title *string
134	Slug string
135	Content string
136	Status string
137	Alias *string
138	CanonicalURL *string
139	PublishedDate *string
140	MetaDescription *string
141	MetaImage *string
142	Lang string
143	Tags *string
144	Weather *string
145	}
146
147	func nullable(p *string) any {
148	if p == nil {
149	return nil
150	}
151	return *p
152	}
153
154	func createPost(db sql.DB, in PostInput) (Post, error) {
155	shortID, err := auth.GenerateShortID(10)
156	if err != nil {
157	return nil, err
158	}
159	in.PublishedDate = normalizePubDatePtr(in.PublishedDate)
160	res, err := db.Exec(
161	`INSERT INTO posts (short_id, title, slug, content, status, alias, canonical_url, published_date, meta_description, meta_image, lang, tags, weather)
162	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
163	shortID, nullable(in.Title), in.Slug, in.Content, in.Status,
164	nullable(in.Alias), nullable(in.CanonicalURL), nullable(in.PublishedDate),
165	nullable(in.MetaDescription), nullable(in.MetaImage), in.Lang, nullable(in.Tags), nullable(in.Weather),
166	)
167	if err != nil {
168	return nil, err
169	}
170	id, _ := res.LastInsertId()
171	return scanPost(db.QueryRow(`SELECT `+postCols+` FROM posts WHERE id = ?`, id))
172	}
173
174	func getPostByShortID(db sql.DB, shortID string) (Post, error) {
175	return scanPost(db.QueryRow(`SELECT `+postCols+` FROM posts WHERE short_id = ?`, shortID))
176	}
177
178	func getPostBySlug(db sql.DB, slug string) (Post, error) {
179	return scanPost(db.QueryRow(`SELECT `+postCols+` FROM posts WHERE slug = ?`, slug))
180	}
181
182	func getAllPosts(db *sql.DB) ([]Post, error) {
183	rows, err := db.Query(`SELECT ` + postCols + ` FROM posts ORDER BY id DESC`)
184	if err != nil {
185	return nil, err
186	}
187	defer rows.Close()
188	var out []Post
189	for rows.Next() {
190	p, err := scanPost(rows)
191	if err != nil {
192	return nil, err
193	}
194	out = append(out, *p)
195	}
196	return out, rows.Err()
197	}
198
199	func getPublishedPosts(db *sql.DB, limit int64) ([]Post, error) {
200	if limit <= 0 {
201	limit = -1
202	}
203	rows, err := db.Query(
204	`SELECT `+postCols+` FROM posts WHERE status = 'published' ORDER BY published_date DESC, id DESC LIMIT ?`, limit)
205	if err != nil {
206	return nil, err
207	}
208	defer rows.Close()
209	var out []Post
210	for rows.Next() {
211	p, err := scanPost(rows)
212	if err != nil {
213	return nil, err
214	}
215	out = append(out, *p)
216	}
217	return out, rows.Err()
218	}
219
220	func updatePost(db sql.DB, shortID string, in PostInput) (Post, error) {
221	in.PublishedDate = normalizePubDatePtr(in.PublishedDate)
222	res, err := db.Exec(
223	`UPDATE posts SET title = ?, slug = ?, content = ?, status = ?, alias = ?, canonical_url = ?,
224	published_date = CASE WHEN ? = 'published' THEN COALESCE(?, published_date, strftime('%Y-%m-%dT%H:%M:%SZ','now')) ELSE ? END,
225	meta_description = ?, meta_image = ?, lang = ?, tags = ?, weather = ?,
226	updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
227	nullable(in.Title), in.Slug, in.Content, in.Status, nullable(in.Alias), nullable(in.CanonicalURL),
228	in.Status, nullable(in.PublishedDate), nullable(in.PublishedDate),
229	nullable(in.MetaDescription), nullable(in.MetaImage), in.Lang, nullable(in.Tags), nullable(in.Weather), shortID,
230	)
231	if err != nil {
232	return nil, err
233	}
234	if n, _ := res.RowsAffected(); n == 0 {
235	return nil, nil
236	}
237	return getPostByShortID(db, shortID)
238	}
239
240	func deletePost(db *sql.DB, shortID string) (bool, error) {
241	res, err := db.Exec(`DELETE FROM posts WHERE short_id = ?`, shortID)
242	if err != nil {
243	return false, err
244	}
245	n, _ := res.RowsAffected()
246	return n > 0, nil
247	}
248
249	func togglePostStatus(db *sql.DB, shortID string) (string, error) {
250	var current string
251	err := db.QueryRow(`SELECT status FROM posts WHERE short_id = ?`, shortID).Scan(&current)
252	if errors.Is(err, sql.ErrNoRows) {
253	return "", nil
254	}
255	if err != nil {
256	return "", err
257	}
258	newStatus := "published"
259	if current == "published" {
260	newStatus = "draft"
261	}
262	if newStatus == "published" {
263	_, err = db.Exec(
264	`UPDATE posts SET status = ?, published_date = COALESCE(published_date, strftime('%Y-%m-%dT%H:%M:%SZ','now')), updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
265	newStatus, shortID)
266	} else {
267	_, err = db.Exec(`UPDATE posts SET status = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
268	newStatus, shortID)
269	}
270	return newStatus, err
271	}
272
273	func findAliasRedirect(db *sql.DB, alias string) (string, error) {
274	var slug string
275	err := db.QueryRow(`SELECT slug FROM posts WHERE alias = ? AND status = 'published'`, alias).Scan(&slug)
276	if errors.Is(err, sql.ErrNoRows) {
277	return "", nil
278	}
279	if err != nil {
280	return "", err
281	}
282	return "/posts/" + slug, nil
283	}
284
285	const pageCols = `id, short_id, title, slug, content, is_published, nav_order, created_at, updated_at`
286
287	func scanPage(s interface{ Scan(...any) error }) (*Page, error) {
288	var p Page
289	var pub int
290	err := s.Scan(&p.ID, &p.ShortID, &p.Title, &p.Slug, &p.Content, &pub, &p.NavOrder, &p.CreatedAt, &p.UpdatedAt)
291	if errors.Is(err, sql.ErrNoRows) {
292	return nil, nil
293	}
294	if err != nil {
295	return nil, err
296	}
297	p.IsPublished = pub != 0
298	return &p, nil
299	}
300
301	func createPage(db sql.DB, title, slug, content string, isPublished bool, navOrder int64) (Page, error) {
302	shortID, err := auth.GenerateShortID(10)
303	if err != nil {
304	return nil, err
305	}
306	pub := 0
307	if isPublished {
308	pub = 1
309	}
310	res, err := db.Exec(
311	`INSERT INTO pages (short_id, title, slug, content, is_published, nav_order) VALUES (?, ?, ?, ?, ?, ?)`,
312	shortID, title, slug, content, pub, navOrder)
313	if err != nil {
314	return nil, err
315	}
316	id, _ := res.LastInsertId()
317	return scanPage(db.QueryRow(`SELECT `+pageCols+` FROM pages WHERE id = ?`, id))
318	}
319
320	func getPageByShortID(db sql.DB, shortID string) (Page, error) {
321	return scanPage(db.QueryRow(`SELECT `+pageCols+` FROM pages WHERE short_id = ?`, shortID))
322	}
323
324	func getPageBySlug(db sql.DB, slug string) (Page, error) {
325	return scanPage(db.QueryRow(`SELECT `+pageCols+` FROM pages WHERE slug = ?`, slug))
326	}
327
328	func getAllPages(db *sql.DB) ([]Page, error) {
329	rows, err := db.Query(`SELECT ` + pageCols + ` FROM pages ORDER BY nav_order ASC, id ASC`)
330	if err != nil {
331	return nil, err
332	}
333	defer rows.Close()
334	var out []Page
335	for rows.Next() {
336	p, err := scanPage(rows)
337	if err != nil {
338	return nil, err
339	}
340	out = append(out, *p)
341	}
342	return out, rows.Err()
343	}
344
345	func updatePage(db sql.DB, shortID, title, slug, content string, isPublished bool, navOrder int64) (Page, error) {
346	pub := 0
347	if isPublished {
348	pub = 1
349	}
350	res, err := db.Exec(
351	`UPDATE pages SET title = ?, slug = ?, content = ?, is_published = ?, nav_order = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE short_id = ?`,
352	title, slug, content, pub, navOrder, shortID)
353	if err != nil {
354	return nil, err
355	}
356	if n, _ := res.RowsAffected(); n == 0 {
357	return nil, nil
358	}
359	return getPageByShortID(db, shortID)
360	}
361
362	func deletePage(db *sql.DB, shortID string) error {
363	_, err := db.Exec(`DELETE FROM pages WHERE short_id = ?`, shortID)
364	return err
365	}
366
367	func getSetting(db *sql.DB, key string) (string, error) {
368	var v string
369	err := db.QueryRow(`SELECT value FROM settings WHERE key = ?`, key).Scan(&v)
370	if errors.Is(err, sql.ErrNoRows) {
371	return "", nil
372	}
373	if err != nil {
374	return "", err
375	}
376	return v, nil
377	}
378
379	func setSetting(db *sql.DB, key, value string) error {
380	_, err := db.Exec(`INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
381	key, value)
382	return err
383	}
384
385	const fileCols = `id, short_id, filename, original_name, content_type, size, created_at, storage_backend`
386
387	func scanFile(s interface{ Scan(...any) error }) (*UploadedFile, error) {
388	var f UploadedFile
389	err := s.Scan(&f.ID, &f.ShortID, &f.Filename, &f.OriginalName, &f.ContentType, &f.Size, &f.CreatedAt, &f.StorageBackend)
390	if errors.Is(err, sql.ErrNoRows) {
391	return nil, nil
392	}
393	if err != nil {
394	return nil, err
395	}
396	return &f, nil
397	}
398
399	func createFile(db sql.DB, filename, originalName, contentType string, size int64, backend string) (UploadedFile, error) {
400	shortID, err := auth.GenerateShortID(10)
401	if err != nil {
402	return nil, err
403	}
404	if backend == "" {
405	backend = "local"
406	}
407	res, err := db.Exec(
408	`INSERT INTO files (short_id, filename, original_name, content_type, size, storage_backend) VALUES (?, ?, ?, ?, ?, ?)`,
409	shortID, filename, originalName, contentType, size, backend)
410	if err != nil {
411	return nil, err
412	}
413	id, _ := res.LastInsertId()
414	return scanFile(db.QueryRow(`SELECT `+fileCols+` FROM files WHERE id = ?`, id))
415	}
416
417	func getFileByFilename(db sql.DB, filename string) (UploadedFile, error) {
418	return scanFile(db.QueryRow(`SELECT `+fileCols+` FROM files WHERE filename = ?`, filename))
419	}
420
421	func getAllFiles(db *sql.DB) ([]UploadedFile, error) {
422	rows, err := db.Query(`SELECT ` + fileCols + ` FROM files ORDER BY id DESC`)
423	if err != nil {
424	return nil, err
425	}
426	defer rows.Close()
427	var out []UploadedFile
428	for rows.Next() {
429	f, err := scanFile(rows)
430	if err != nil {
431	return nil, err
432	}
433	out = append(out, *f)
434	}
435	return out, rows.Err()
436	}
437
438	func deleteFile(db sql.DB, shortID string) (UploadedFile, error) {
439	f, err := scanFile(db.QueryRow(`SELECT `+fileCols+` FROM files WHERE short_id = ?`, shortID))
440	if err != nil \|\| f == nil {
441	return f, err
442	}
443	if _, err := db.Exec(`DELETE FROM files WHERE short_id = ?`, shortID); err != nil {
444	return nil, err
445	}
446	return f, nil
447	}
448
449	func nowDatetime() string {
450	return time.Now().UTC().Format(time.RFC3339)
451	}
452
453	// migrateTimestamps rewrites legacy "YYYY-MM-DD HH:MM:SS" and date-only values
454	// to RFC3339 UTC. Idempotent: skips values already in RFC3339 form.
455	func migrateTimestamps(db *sql.DB) error {
456	stmts := []string{
457	`UPDATE posts SET published_date = REPLACE(published_date, ' ', 'T') \|\| 'Z'
458	WHERE published_date IS NOT NULL AND length(published_date) = 19
459	AND published_date LIKE '____-__-__ __:__:__'`,
460	`UPDATE posts SET published_date = published_date \|\| 'T00:00:00Z'
461	WHERE published_date IS NOT NULL AND length(published_date) = 10
462	AND published_date LIKE '____-__-__'`,
463	`UPDATE posts SET created_at = REPLACE(created_at, ' ', 'T') \|\| 'Z'
464	WHERE length(created_at) = 19 AND created_at LIKE '____-__-__ __:__:__'`,
465	`UPDATE posts SET updated_at = REPLACE(updated_at, ' ', 'T') \|\| 'Z'
466	WHERE length(updated_at) = 19 AND updated_at LIKE '____-__-__ __:__:__'`,
467	`UPDATE pages SET created_at = REPLACE(created_at, ' ', 'T') \|\| 'Z'
468	WHERE length(created_at) = 19 AND created_at LIKE '____-__-__ __:__:__'`,
469	`UPDATE pages SET updated_at = REPLACE(updated_at, ' ', 'T') \|\| 'Z'
470	WHERE length(updated_at) = 19 AND updated_at LIKE '____-__-__ __:__:__'`,
471	`UPDATE files SET created_at = REPLACE(created_at, ' ', 'T') \|\| 'Z'
472	WHERE length(created_at) = 19 AND created_at LIKE '____-__-__ __:__:__'`,
473	}
474	for _, s := range stmts {
475	if _, err := db.Exec(s); err != nil {
476	return err
477	}
478	}
479	return nil
480	}
481
482	// migrateWeather updates tables to have new optional weather column
483	func migrateWeather(db *sql.DB) error {
484	var count int
485	if err := db.QueryRow(
486	`SELECT COUNT(*) FROM pragma_table_info('posts') WHERE name = 'weather'`,
487	).Scan(&count); err != nil {
488	return err
489	}
490	if count == 0 {
491	_, err := db.Exec(`ALTER TABLE posts ADD COLUMN weather TEXT`)
492	return err
493	}
494	return nil
495	}
496
497	func normalizePubDatePtr(p string) string {
498	if p == nil {
499	return nil
500	}
501	if v, ok := parsePubDate(*p); ok {
502	return &v
503	}
504	return p
505	}
506
507	func runMigrations(db *sql.DB) error {
508	if err := migrateTimestamps(db); err != nil {
509	return fmt.Errorf("migrate timestamps: %w", err)
510	}
511	if err := migrateWeather(db); err != nil {
512	return fmt.Errorf("add weather column: %w", err)
513	}
514	return nil
515	}
516
517	func _useStrings() {
518	_ = strings.TrimSpace
519	}