package db

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

// Store wraps the SQLite connection and domain queries.
type Store struct {
	db *sql.DB
}

// User is a Reddit account reference.
type User struct {
	ID           int64  `json:"id"`
	RedditUserID string `json:"reddit_user_id"`
	Username     string `json:"username"`
}

// Post is a Reddit submission ("top post").
type Post struct {
	ID           int64     `json:"id"`
	RedditPostID string    `json:"reddit_post_id"`
	Title        string    `json:"title"`
	Body         string    `json:"body"`
	AuthorID     int64     `json:"author_id"`
	AuthorName   string    `json:"author_name"`
	Permalink    string    `json:"permalink"`
	Subreddit    string    `json:"subreddit"`
	CreatedAt    time.Time `json:"created_at"`
}

// Comment is a saved Reddit comment with optional screenshot/Imgur URL.
type Comment struct {
	ID              int64     `json:"id"`
	RedditCommentID string    `json:"reddit_comment_id"`
	Body            string    `json:"body"`
	AuthorID        int64     `json:"author_id"`
	AuthorName      string    `json:"author_name"`
	PostID          int64     `json:"post_id"`
	RedditPostID    string    `json:"reddit_post_id"`
	Permalink       string    `json:"permalink"`
	ScreenshotPath  string    `json:"screenshot_path"`
	ImgurURL        string    `json:"imgur_url"`
	CapturedAt      time.Time `json:"captured_at"`
	PostTitle       string    `json:"post_title"`
}

// SearchResult is a comment row returned by search.
type SearchResult struct {
	Comment
}

// Open opens (or creates) the SQLite database and runs migrations.
func Open(path string) (*Store, error) {
	database, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}
	database.SetMaxOpenConns(1)

	store := &Store{db: database}
	if err := store.migrate(); err != nil {
		_ = database.Close()
		return nil, err
	}
	return store, nil
}

// Close closes the database.
func (s *Store) Close() error {
	return s.db.Close()
}

func (s *Store) migrate() error {
	const schema = `
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reddit_user_id TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reddit_post_id TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL DEFAULT '',
    body TEXT NOT NULL DEFAULT '',
    author_id INTEGER NOT NULL REFERENCES users(id),
    permalink TEXT NOT NULL DEFAULT '',
    subreddit TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reddit_comment_id TEXT NOT NULL UNIQUE,
    body TEXT NOT NULL DEFAULT '',
    author_id INTEGER NOT NULL REFERENCES users(id),
    post_id INTEGER NOT NULL REFERENCES posts(id),
    permalink TEXT NOT NULL DEFAULT '',
    screenshot_path TEXT NOT NULL DEFAULT '',
    imgur_url TEXT NOT NULL DEFAULT '',
    captured_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_comments_reddit_id ON comments(reddit_comment_id);
CREATE INDEX IF NOT EXISTS idx_posts_reddit_id ON posts(reddit_post_id);

CREATE TABLE IF NOT EXISTS blocked_usernames (
    username TEXT PRIMARY KEY COLLATE NOCASE,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`
	_, err := s.db.Exec(schema)
	if err != nil {
		return fmt.Errorf("migrate: %w", err)
	}
	return s.seedBlocklistIfEmpty()
}

func (s *Store) seedBlocklistIfEmpty() error {
	var count int
	if err := s.db.QueryRow(`SELECT COUNT(1) FROM blocked_usernames`).Scan(&count); err != nil {
		return err
	}
	if count > 0 {
		return nil
	}
	_, err := s.db.Exec(`INSERT INTO blocked_usernames (username) VALUES (?)`, NormalizeUsername("AutoModerator"))
	return err
}

// NormalizeUsername trims, strips u/, and lower-cases a Reddit username.
func NormalizeUsername(name string) string {
	name = strings.TrimSpace(name)
	name = strings.TrimPrefix(name, "u/")
	name = strings.TrimPrefix(name, "U/")
	name = strings.TrimPrefix(name, "/u/")
	return strings.ToLower(strings.TrimSpace(name))
}

// ListBlockedUsernames returns all blocked usernames (normalized).
func (s *Store) ListBlockedUsernames(ctx context.Context) ([]string, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT username FROM blocked_usernames ORDER BY username COLLATE NOCASE`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		out = append(out, name)
	}
	if out == nil {
		out = []string{}
	}
	return out, rows.Err()
}

// IsUsernameBlocked reports whether the author is on the blocklist.
func (s *Store) IsUsernameBlocked(ctx context.Context, username string) (bool, error) {
	normalized := NormalizeUsername(username)
	if normalized == "" {
		return false, nil
	}
	var count int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(1) FROM blocked_usernames WHERE username = ?`, normalized).Scan(&count)
	return count > 0, err
}

// AddBlockedUsername inserts one username (idempotent).
func (s *Store) AddBlockedUsername(ctx context.Context, username string) (string, error) {
	normalized := NormalizeUsername(username)
	if normalized == "" {
		return "", fmt.Errorf("username required")
	}
	_, err := s.db.ExecContext(ctx, `
INSERT INTO blocked_usernames (username) VALUES (?)
ON CONFLICT(username) DO NOTHING
`, normalized)
	return normalized, err
}

// RemoveBlockedUsername deletes one username.
func (s *Store) RemoveBlockedUsername(ctx context.Context, username string) error {
	normalized := NormalizeUsername(username)
	if normalized == "" {
		return fmt.Errorf("username required")
	}
	_, err := s.db.ExecContext(ctx, `DELETE FROM blocked_usernames WHERE username = ?`, normalized)
	return err
}

// ReplaceBlockedUsernames replaces the entire blocklist.
func (s *Store) ReplaceBlockedUsernames(ctx context.Context, usernames []string) ([]string, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.ExecContext(ctx, `DELETE FROM blocked_usernames`); err != nil {
		return nil, err
	}

	seen := map[string]struct{}{}
	var normalized []string
	for _, name := range usernames {
		n := NormalizeUsername(name)
		if n == "" {
			continue
		}
		if _, ok := seen[n]; ok {
			continue
		}
		seen[n] = struct{}{}
		if _, err := tx.ExecContext(ctx, `INSERT INTO blocked_usernames (username) VALUES (?)`, n); err != nil {
			return nil, err
		}
		normalized = append(normalized, n)
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}
	if normalized == nil {
		normalized = []string{}
	}
	return normalized, nil
}

// CommentExists reports whether a comment with the given Reddit id is stored.
func (s *Store) CommentExists(ctx context.Context, redditCommentID string) (bool, error) {
	var count int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(1) FROM comments WHERE reddit_comment_id = ?`, redditCommentID).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// CommentCaptureStatus reports existence and whether a screenshot is already stored.
type CommentCaptureStatus struct {
	Exists         bool   `json:"exists"`
	HasScreenshot  bool   `json:"has_screenshot"`
	ScreenshotPath string `json:"screenshot_path,omitempty"`
}

// GetCommentCaptureStatus loads capture status for a Reddit comment id.
func (s *Store) GetCommentCaptureStatus(ctx context.Context, redditCommentID string) (CommentCaptureStatus, error) {
	var path string
	err := s.db.QueryRowContext(ctx, `SELECT screenshot_path FROM comments WHERE reddit_comment_id = ?`, redditCommentID).Scan(&path)
	if err == sql.ErrNoRows {
		return CommentCaptureStatus{Exists: false, HasScreenshot: false}, nil
	}
	if err != nil {
		return CommentCaptureStatus{}, err
	}
	return CommentCaptureStatus{
		Exists:         true,
		HasScreenshot:  strings.TrimSpace(path) != "",
		ScreenshotPath: path,
	}, nil
}

// SetCommentScreenshot sets screenshot_path only when currently empty.
func (s *Store) SetCommentScreenshot(ctx context.Context, redditCommentID, screenshotPath string) (bool, error) {
	result, err := s.db.ExecContext(ctx, `
UPDATE comments
SET screenshot_path = ?
WHERE reddit_comment_id = ?
  AND (screenshot_path IS NULL OR screenshot_path = '')
`, screenshotPath, redditCommentID)
	if err != nil {
		return false, err
	}
	n, err := result.RowsAffected()
	return n > 0, err
}

// PostExists reports whether a post with the given Reddit id is stored.
func (s *Store) PostExists(ctx context.Context, redditPostID string) (bool, error) {
	var count int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(1) FROM posts WHERE reddit_post_id = ?`, redditPostID).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// UpsertUser inserts or updates a user by Reddit user id.
func (s *Store) UpsertUser(ctx context.Context, redditUserID, username string) (int64, error) {
	if redditUserID == "" {
		redditUserID = "unknown:" + username
	}
	if username == "" {
		username = "[deleted]"
	}

	_, err := s.db.ExecContext(ctx, `
INSERT INTO users (reddit_user_id, username) VALUES (?, ?)
ON CONFLICT(reddit_user_id) DO UPDATE SET username = excluded.username
`, redditUserID, username)
	if err != nil {
		return 0, err
	}

	var id int64
	err = s.db.QueryRowContext(ctx, `SELECT id FROM users WHERE reddit_user_id = ?`, redditUserID).Scan(&id)
	return id, err
}

// SavePostInput is the payload for saving a submission.
type SavePostInput struct {
	RedditPostID string
	Title        string
	Body         string
	AuthorID     string
	AuthorName   string
	Permalink    string
	Subreddit    string
}

// SavePost upserts a post and its author.
func (s *Store) SavePost(ctx context.Context, input SavePostInput) (int64, error) {
	authorID, err := s.UpsertUser(ctx, input.AuthorID, input.AuthorName)
	if err != nil {
		return 0, err
	}

	_, err = s.db.ExecContext(ctx, `
INSERT INTO posts (reddit_post_id, title, body, author_id, permalink, subreddit, created_at)
VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
ON CONFLICT(reddit_post_id) DO UPDATE SET
    title = excluded.title,
    body = excluded.body,
    author_id = excluded.author_id,
    permalink = excluded.permalink,
    subreddit = excluded.subreddit
`, input.RedditPostID, input.Title, input.Body, authorID, input.Permalink, input.Subreddit)
	if err != nil {
		return 0, err
	}

	var id int64
	err = s.db.QueryRowContext(ctx, `SELECT id FROM posts WHERE reddit_post_id = ?`, input.RedditPostID).Scan(&id)
	return id, err
}

// SaveCommentInput is the payload for saving a comment.
type SaveCommentInput struct {
	RedditCommentID string
	Body            string
	AuthorID        string
	AuthorName      string
	RedditPostID    string
	PostTitle       string
	PostBody        string
	PostAuthorID    string
	PostAuthorName  string
	PostPermalink   string
	Subreddit       string
	Permalink       string
	ScreenshotPath  string
}

// SaveComment ensures the parent post exists, then inserts the comment (no overwrite of existing).
func (s *Store) SaveComment(ctx context.Context, input SaveCommentInput) (int64, error) {
	exists, err := s.CommentExists(ctx, input.RedditCommentID)
	if err != nil {
		return 0, err
	}
	if exists {
		var id int64
		err = s.db.QueryRowContext(ctx, `SELECT id FROM comments WHERE reddit_comment_id = ?`, input.RedditCommentID).Scan(&id)
		return id, err
	}

	postID, err := s.SavePost(ctx, SavePostInput{
		RedditPostID: input.RedditPostID,
		Title:        input.PostTitle,
		Body:         input.PostBody,
		AuthorID:     input.PostAuthorID,
		AuthorName:   input.PostAuthorName,
		Permalink:    input.PostPermalink,
		Subreddit:    input.Subreddit,
	})
	if err != nil {
		return 0, err
	}

	authorID, err := s.UpsertUser(ctx, input.AuthorID, input.AuthorName)
	if err != nil {
		return 0, err
	}

	result, err := s.db.ExecContext(ctx, `
INSERT INTO comments (reddit_comment_id, body, author_id, post_id, permalink, screenshot_path, captured_at)
VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
`, input.RedditCommentID, input.Body, authorID, postID, input.Permalink, input.ScreenshotPath)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

// GetCommentByRedditID loads a comment by Reddit id.
func (s *Store) GetCommentByRedditID(ctx context.Context, redditCommentID string) (*Comment, error) {
	row := s.db.QueryRowContext(ctx, `
SELECT c.id, c.reddit_comment_id, c.body, c.author_id, u.username, c.post_id, p.reddit_post_id,
       c.permalink, c.screenshot_path, c.imgur_url, c.captured_at, p.title
FROM comments c
JOIN users u ON u.id = c.author_id
JOIN posts p ON p.id = c.post_id
WHERE c.reddit_comment_id = ?
`, redditCommentID)

	var comment Comment
	var capturedAt string
	err := row.Scan(
		&comment.ID, &comment.RedditCommentID, &comment.Body, &comment.AuthorID, &comment.AuthorName,
		&comment.PostID, &comment.RedditPostID, &comment.Permalink, &comment.ScreenshotPath,
		&comment.ImgurURL, &capturedAt, &comment.PostTitle,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	comment.CapturedAt = parseTime(capturedAt)
	return &comment, nil
}

// SetImgurURL stores the Imgur link for a comment.
func (s *Store) SetImgurURL(ctx context.Context, redditCommentID, imgurURL string) error {
	_, err := s.db.ExecContext(ctx, `UPDATE comments SET imgur_url = ? WHERE reddit_comment_id = ?`, imgurURL, redditCommentID)
	return err
}

func commentSearchFilter(query, username string) (where string, args []any) {
	var clauses []string
	if username != "" {
		clauses = append(clauses, `u.username LIKE ?`)
		args = append(args, username+"%")
	}
	if query != "" {
		pattern := normalizeGlob(query)
		clauses = append(clauses, `(c.body GLOB ? OR p.title GLOB ?)`)
		args = append(args, pattern, pattern)
	}
	if len(clauses) > 0 {
		where = "WHERE " + strings.Join(clauses, " AND ")
	}
	return where, args
}

// CountComments returns how many comments match the optional username / GLOB filters.
func (s *Store) CountComments(ctx context.Context, query, username string) (int, error) {
	where, args := commentSearchFilter(query, username)
	sqlQuery := fmt.Sprintf(`
SELECT COUNT(1)
FROM comments c
JOIN users u ON u.id = c.author_id
JOIN posts p ON p.id = c.post_id
%s
`, where)
	var total int
	err := s.db.QueryRowContext(ctx, sqlQuery, args...).Scan(&total)
	return total, err
}

// SearchComments finds comments by optional username and GLOB text pattern (newest first).
func (s *Store) SearchComments(ctx context.Context, query, username string, limit, offset int) ([]SearchResult, error) {
	if limit <= 0 || limit > 500 {
		limit = 100
	}
	if offset < 0 {
		offset = 0
	}

	where, args := commentSearchFilter(query, username)
	sqlQuery := fmt.Sprintf(`
SELECT c.id, c.reddit_comment_id, c.body, c.author_id, u.username, c.post_id, p.reddit_post_id,
       c.permalink, c.screenshot_path, c.imgur_url, c.captured_at, p.title
FROM comments c
JOIN users u ON u.id = c.author_id
JOIN posts p ON p.id = c.post_id
%s
ORDER BY c.captured_at DESC, c.id DESC
LIMIT ? OFFSET ?
`, where)
	args = append(args, limit, offset)

	rows, err := s.db.QueryContext(ctx, sqlQuery, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []SearchResult
	for rows.Next() {
		var item SearchResult
		var capturedAt string
		if err := rows.Scan(
			&item.ID, &item.RedditCommentID, &item.Body, &item.AuthorID, &item.AuthorName,
			&item.PostID, &item.RedditPostID, &item.Permalink, &item.ScreenshotPath,
			&item.ImgurURL, &capturedAt, &item.PostTitle,
		); err != nil {
			return nil, err
		}
		item.CapturedAt = parseTime(capturedAt)
		results = append(results, item)
	}
	return results, rows.Err()
}

func normalizeGlob(query string) string {
	query = strings.TrimSpace(query)
	if query == "" {
		return "*"
	}
	// If the user did not include wildcards, wrap for substring match.
	if !strings.ContainsAny(query, "*?") {
		return "*" + query + "*"
	}
	return query
}

func parseTime(value string) time.Time {
	layouts := []string{
		time.RFC3339,
		"2006-01-02 15:04:05",
		"2006-01-02T15:04:05Z",
	}
	for _, layout := range layouts {
		if parsed, err := time.Parse(layout, value); err == nil {
			return parsed
		}
	}
	return time.Time{}
}