package store

import (
	"context"
	"time"
)

type Post struct {
	ID        int64
	PageID    int64
	Slug      string
	Title     string
	BodyMD    string
	BodyHTML  string
	Format    string // FormatMarkdown | FormatHTML: how BodyMD is read
	Published bool
	CreatedAt time.Time
	UpdatedAt time.Time
	// joined
	PageSlug  string
	PageTitle string
	Tags      []Tag // by name; IDs are not loaded
}

// The tags come along as two parallel arrays (names and slugs, both by name)
// so every post query stays a single round trip; the alias p is the posts row.
const postCols = `p.id, p.page_id, p.slug, p.title, p.body_md, p.body_html, p.format, p.published, p.created_at, p.updated_at, g.slug, g.title,
	coalesce((SELECT array_agg(t.name ORDER BY t.name) FROM post_tags pt JOIN tags t ON t.id=pt.tag_id WHERE pt.post_id=p.id), '{}'),
	coalesce((SELECT array_agg(t.slug ORDER BY t.name) FROM post_tags pt JOIN tags t ON t.id=pt.tag_id WHERE pt.post_id=p.id), '{}')`

func scanPost(row interface{ Scan(...any) error }) (*Post, error) {
	var p Post
	var names, slugs []string
	err := row.Scan(&p.ID, &p.PageID, &p.Slug, &p.Title, &p.BodyMD, &p.BodyHTML, &p.Format, &p.Published, &p.CreatedAt, &p.UpdatedAt, &p.PageSlug, &p.PageTitle, &names, &slugs)
	if err != nil {
		return nil, wrap(err)
	}
	for i := range names {
		p.Tags = append(p.Tags, Tag{Name: names[i], Slug: slugs[i]})
	}
	return &p, nil
}

func (bs *BlogStore) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) {
	rows, err := bs.db.Query(ctx, q, args...)
	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()
}

// ListPosts returns all posts of the blog for the dashboard, optionally filtered by page.
func (bs *BlogStore) ListPosts(ctx context.Context, pageID int64) ([]Post, error) {
	return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE ($1=0 OR p.page_id=$1) ORDER BY p.created_at DESC, p.id DESC`, pageID)
}

// PublishedPosts returns a page of published posts for the public site.
func (bs *BlogStore) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) {
	posts, err := bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE p.page_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, pageID, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	var total int
	err = bs.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total)
	return posts, total, err
}

// SearchPublishedPosts returns a page of the published posts, from every page
// of the blog, whose title or Markdown body matches the case-insensitive
// regular expression; the caller builds it from the reader's words. Title and
// body are searched as one text so a query can span them.
func (bs *BlogStore) SearchPublishedPosts(ctx context.Context, pattern string, limit, offset int) ([]Post, int, error) {
	posts, err := bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE p.published AND (p.title || E'\n' || p.body_md) ~* $1 ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, pattern, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	var total int
	err = bs.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE published AND (title || E'\n' || body_md) ~* $1`, pattern).Scan(&total)
	return posts, total, err
}

// PostRef is the light-weight row the archive module lists.
type PostRef struct {
	Title     string
	Slug      string
	PageSlug  string
	CreatedAt time.Time
}

// PublishedPostIndex lists every published post of the blog, newest first, without bodies.
func (bs *BlogStore) PublishedPostIndex(ctx context.Context) ([]PostRef, error) {
	rows, err := bs.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE p.published ORDER BY p.created_at DESC, p.id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []PostRef
	for rows.Next() {
		var r PostRef
		if err := rows.Scan(&r.Title, &r.Slug, &r.PageSlug, &r.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// RecentPublishedPosts returns the newest published posts across the whole blog (for feeds).
func (bs *BlogStore) RecentPublishedPosts(ctx context.Context, limit int) ([]Post, error) {
	return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $1`, limit)
}

func (bs *BlogStore) PostByID(ctx context.Context, id int64) (*Post, error) {
	return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id))
}

func (bs *BlogStore) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) {
	return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE p.page_id=$1 AND p.slug=$2 AND p.published`, pageID, slug))
}

// CreatePost inserts the post; a zero CreatedAt means now. The post date is
// the blogger's to choose, so it is a plain column rather than a fixed stamp.
func (bs *BlogStore) CreatePost(ctx context.Context, p *Post) (*Post, error) {
	if p.CreatedAt.IsZero() {
		p.CreatedAt = time.Now()
	}
	var id int64
	err := bs.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, format, published, created_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING id`,
		p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, formatOrMD(p.Format), p.Published, p.CreatedAt).Scan(&id)
	if err != nil {
		return nil, wrap(err)
	}
	return bs.PostByID(ctx, id)
}

func (bs *BlogStore) UpdatePost(ctx context.Context, p *Post) error {
	_, err := bs.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, format=$7, published=$8, created_at=$9, updated_at=now() WHERE id=$1`,
		p.ID, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, formatOrMD(p.Format), p.Published, p.CreatedAt)
	return wrap(err)
}

func (bs *BlogStore) DeletePost(ctx context.Context, id int64) error {
	if _, err := bs.db.Exec(ctx, `DELETE FROM posts WHERE id=$1`, id); err != nil {
		return err
	}
	return deleteOrphanTags(ctx, bs.db)
}