package db

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

type PostRecord struct {
	Slug      string
	Title     string
	Date      string
	Tags      []string
	Draft     bool
	Blocks    string // raw EditorJS JSON
	UpdatedAt int64  // Unix microseconds
}

// UpsertPost inserts or updates a post record.
func (m *MetaDB) UpsertPost(p PostRecord) error {
	tags, _ := json.Marshal(p.Tags)
	draft := 0
	if p.Draft {
		draft = 1
	}
	_, err := m.db.Exec(`
		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(slug) DO UPDATE SET
			title      = excluded.title,
			date       = excluded.date,
			tags       = excluded.tags,
			draft      = excluded.draft,
			blocks     = excluded.blocks,
			updated_at = excluded.updated_at
	`, p.Slug, p.Title, p.Date, string(tags), draft, p.Blocks, p.UpdatedAt)
	return err
}

// GetPost retrieves a single post by slug.
func (m *MetaDB) GetPost(slug string) (*PostRecord, error) {
	row := m.db.QueryRow(
		`SELECT slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE slug = ?`, slug)
	var p PostRecord
	var tagsJSON string
	var draft int
	if err := row.Scan(&p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
		return nil, err
	}
	p.Draft = draft != 0
	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
	if p.Tags == nil {
		p.Tags = []string{}
	}
	return &p, nil
}

// ListPosts returns all posts, optionally including drafts.
func (m *MetaDB) ListPosts(includeDrafts bool) ([]PostRecord, error) {
	query := `SELECT slug, title, date, tags, draft, blocks, updated_at FROM posts`
	if !includeDrafts {
		query += ` WHERE draft = 0`
	}
	query += ` ORDER BY date DESC, slug`
	rows, err := m.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanPosts(rows)
}

// DeletePost removes a post by slug.
func (m *MetaDB) DeletePost(slug string) error {
	_, err := m.db.Exec(`DELETE FROM posts WHERE slug = ?`, slug)
	return err
}

func scanPosts(rows *sql.Rows) ([]PostRecord, error) {
	var posts []PostRecord
	for rows.Next() {
		var p PostRecord
		var tagsJSON string
		var draft int
		if err := rows.Scan(&p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
			return nil, err
		}
		p.Draft = draft != 0
		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
		if p.Tags == nil {
			p.Tags = []string{}
		}
		posts = append(posts, p)
	}
	return posts, rows.Err()
}

// DB returns the underlying *sql.DB for advanced queries (e.g., transaction handling).
func (m *MetaDB) DB() *sql.DB {
	return m.db
}

// GetPostRawPath returns the path for indexing in the search database.
func (p *PostRecord) GetPostRawPath() string {
	return "/" + p.Slug
}

// GetUpdatedTime returns the updated_at as a time.Time for comparison.
func (p *PostRecord) GetUpdatedTime() time.Time {
	return time.UnixMicro(p.UpdatedAt).UTC()
}

// AddRedirect inserts or replaces a redirect from fromSlug to toSlug.
func (m *MetaDB) AddRedirect(fromSlug, toSlug string) error {
	_, err := m.db.Exec(
		`INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
		 ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
		fromSlug, toSlug,
	)
	return err
}

// GetRedirect returns the slug that fromSlug redirects to, or sql.ErrNoRows if none.
func (m *MetaDB) GetRedirect(fromSlug string) (string, error) {
	var toSlug string
	err := m.db.QueryRow(
		`SELECT to_slug FROM redirects WHERE from_slug = ?`, fromSlug,
	).Scan(&toSlug)
	return toSlug, err
}

// CollapseRedirects updates all redirects pointing to oldSlug so they point to newSlug instead,
// preventing redirect chains when a slug is renamed again.
func (m *MetaDB) CollapseRedirects(oldSlug, newSlug string) error {
	_, err := m.db.Exec(
		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
	)
	return err
}

// RenamePost atomically renames a post from oldSlug to newSlug,
// updates any existing redirect chains, and adds a redirect from oldSlug to newSlug.
func (m *MetaDB) RenamePost(oldSlug, newSlug string) error {
	tx, err := m.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Fetch existing record
	var p PostRecord
	var tagsJSON string
	var draft int
	row := tx.QueryRow(
		`SELECT slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE slug = ?`, oldSlug)
	if err := row.Scan(&p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
		return err
	}
	p.Draft = draft != 0
	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)

	// Insert with new slug
	tags, _ := json.Marshal(p.Tags)
	draftInt := 0
	if p.Draft {
		draftInt = 1
	}
	_, err = tx.Exec(`
		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		newSlug, p.Title, p.Date, string(tags), draftInt, p.Blocks, p.UpdatedAt,
	)
	if err != nil {
		return err
	}

	// Delete old record
	if _, err = tx.Exec(`DELETE FROM posts WHERE slug = ?`, oldSlug); err != nil {
		return err
	}

	// Collapse existing redirect chains: anything pointing to oldSlug now points to newSlug
	if _, err = tx.Exec(
		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
	); err != nil {
		return err
	}

	// Add redirect from oldSlug to newSlug
	if _, err = tx.Exec(`
		INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
		ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
		oldSlug, newSlug,
	); err != nil {
		return err
	}

	return tx.Commit()
}