package store

import (
	"context"
	"time"
)

type Post struct {
	ID        int64
	PageID    int64
	Slug      string
	Title     string
	BodyMD    string
	BodyHTML  string
	Published bool
	CreatedAt time.Time
	UpdatedAt time.Time
	// joined
	PageSlug  string
	PageTitle string
}

const postCols = `p.id, p.page_id, p.slug, p.title, p.body_md, p.body_html, p.published, p.created_at, p.updated_at, g.slug, g.title`

func scanPost(row interface{ Scan(...any) error }) (*Post, error) {
	var p Post
	err := row.Scan(&p.ID, &p.PageID, &p.Slug, &p.Title, &p.BodyMD, &p.BodyHTML, &p.Published, &p.CreatedAt, &p.UpdatedAt, &p.PageSlug, &p.PageTitle)
	if err != nil {
		return nil, wrap(err)
	}
	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
}

// 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))
}

func (bs *BlogStore) CreatePost(ctx context.Context, p *Post) (*Post, error) {
	var id int64
	err := bs.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`,
		p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published).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, published=$7, updated_at=now() WHERE id=$1`,
		p.ID, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published)
	return wrap(err)
}

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