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 (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) {
	rows, err := s.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 a blog for the dashboard, optionally filtered by page.
func (s *Store) ListPosts(ctx context.Context, blogID int64, pageID int64) ([]Post, error) {
	return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
		WHERE g.blog_id=$1 AND ($2=0 OR p.page_id=$2) ORDER BY p.created_at DESC, p.id DESC`, blogID, pageID)
}

// PublishedPosts returns a page of published posts for the public site.
func (s *Store) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) {
	posts, err := s.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 = s.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total)
	return posts, total, err
}

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

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

func (s *Store) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) {
	return scanPost(s.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 (s *Store) CreatePost(ctx context.Context, p *Post) (*Post, error) {
	var id int64
	err := s.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 scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id))
}

// UpdatePost updates a post; the page must belong to the same blog (checked by handler).
func (s *Store) UpdatePost(ctx context.Context, p *Post) error {
	_, err := s.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 (s *Store) DeletePost(ctx context.Context, blogID, id int64) error {
	_, err := s.db.Exec(ctx, `DELETE FROM posts p USING pages g WHERE g.id=p.page_id AND g.blog_id=$1 AND p.id=$2`, blogID, id)
	return err
}