package db

import (
	"database/sql"
	"encoding/json"
	"strings"
	"time"
)

type PageMeta struct {
	Path      string
	HTMLPath  string
	Title     string
	Date      string
	Tags      []string
	UpdatedAt time.Time
}

func (d *DB) UpsertPage(p PageMeta) error {
	tags, _ := json.Marshal(p.Tags)
	_, err := d.db.Exec(`
		INSERT INTO pages (path, html_path, title, date, tags, updated_at)
		VALUES (?, ?, ?, ?, ?, ?)
		ON CONFLICT(path) DO UPDATE SET
			html_path  = excluded.html_path,
			title      = excluded.title,
			date       = excluded.date,
			tags       = excluded.tags,
			updated_at = excluded.updated_at
	`, p.Path, p.HTMLPath, p.Title, p.Date, string(tags), p.UpdatedAt.UTC())
	return err
}

func (d *DB) DeletePage(path string) error {
	_, err := d.db.Exec(`DELETE FROM pages WHERE path = ?`, path)
	return err
}

func (d *DB) GetPage(path string) (*PageMeta, error) {
	row := d.db.QueryRow(
		`SELECT path, html_path, title, date, tags FROM pages WHERE path = ?`, path)
	var p PageMeta
	var tagsJSON string
	if err := row.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
		return nil, err
	}
	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
	return &p, nil
}

func (d *DB) ListPages() ([]PageMeta, error) {
	rows, err := d.db.Query(
		`SELECT path, html_path, title, date, tags FROM pages ORDER BY date DESC, path`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanPages(rows)
}

func (d *DB) ListByTag(tag string) ([]PageMeta, error) {
	needle := `%"` + strings.ReplaceAll(tag, `"`, `\"`) + `"%`
	rows, err := d.db.Query(
		`SELECT path, html_path, title, date, tags FROM pages WHERE tags LIKE ? ORDER BY date DESC`, needle)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanPages(rows)
}

func scanPages(rows *sql.Rows) ([]PageMeta, error) {
	var pages []PageMeta
	for rows.Next() {
		var p PageMeta
		var tagsJSON string
		if err := rows.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
			return nil, err
		}
		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
		pages = append(pages, p)
	}
	return pages, rows.Err()
}