package db

import (
	"database/sql"
	"fmt"
	"maps"
	"slices"
	"sort"
	"strings"
	"time"

	"github.com/bluesky-social/indigo/atproto/syntax"
	"tangled.org/core/appview/models"
	"tangled.org/core/orm"
)

func PutComment(tx *sql.Tx, c *models.Comment) error {
	result, err := tx.Exec(
		`insert into comments (
			did,
			rkey,
			subject_at,
			reply_to,
			body,
			pull_submission_id,
			created
		)
		values (?, ?, ?, ?, ?, ?, ?)
		on conflict(did, rkey) do update set
			subject_at = excluded.subject_at,
			reply_to = excluded.reply_to,
			body = excluded.body,
			edited = case
				when
					comments.subject_at != excluded.subject_at
					or comments.body != excluded.body
					or comments.reply_to != excluded.reply_to
				then ?
				else comments.edited
			end`,
		c.Did,
		c.Rkey,
		c.Subject,
		c.ReplyTo,
		c.Body,
		c.PullSubmissionId,
		c.Created.Format(time.RFC3339),
		time.Now().Format(time.RFC3339),
	)
	if err != nil {
		return err
	}

	c.Id, err = result.LastInsertId()
	if err != nil {
		return err
	}

	if err := putReferences(tx, c.AtUri(), c.References); err != nil {
		return fmt.Errorf("put reference_links: %w", err)
	}

	return nil
}

func DeleteComments(e Execer, filters ...orm.Filter) error {
	var conditions []string
	var args []any
	for _, filter := range filters {
		conditions = append(conditions, filter.Condition())
		args = append(args, filter.Arg()...)
	}

	whereClause := ""
	if conditions != nil {
		whereClause = " where " + strings.Join(conditions, " and ")
	}

	query := fmt.Sprintf(`update comments set body = "", deleted = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)

	_, err := e.Exec(query, args...)
	return err
}

func GetComments(e Execer, filters ...orm.Filter) ([]models.Comment, error) {
	commentMap := make(map[string]*models.Comment)

	var conditions []string
	var args []any
	for _, filter := range filters {
		conditions = append(conditions, filter.Condition())
		args = append(args, filter.Arg()...)
	}

	whereClause := ""
	if conditions != nil {
		whereClause = " where " + strings.Join(conditions, " and ")
	}

	query := fmt.Sprintf(`
		select
			id,
			did,
			rkey,
			subject_at,
			reply_to,
			body,
			pull_submission_id,
			created,
			edited,
			deleted
		from
			comments
		%s
		`, whereClause)

	rows, err := e.Query(query, args...)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var comment models.Comment
		var created string
		var rkey, edited, deleted, replyTo sql.Null[string]
		err := rows.Scan(
			&comment.Id,
			&comment.Did,
			&rkey,
			&comment.Subject,
			&replyTo,
			&comment.Body,
			&comment.PullSubmissionId,
			&created,
			&edited,
			&deleted,
		)
		if err != nil {
			return nil, err
		}

		// this is a remnant from old times, newer comments always have rkey
		if rkey.Valid {
			comment.Rkey = rkey.V
		}

		if t, err := time.Parse(time.RFC3339, created); err == nil {
			comment.Created = t
		}

		if edited.Valid {
			if t, err := time.Parse(time.RFC3339, edited.V); err == nil {
				comment.Edited = &t
			}
		}

		if deleted.Valid {
			if t, err := time.Parse(time.RFC3339, deleted.V); err == nil {
				comment.Deleted = &t
			}
		}

		if replyTo.Valid {
			rt := syntax.ATURI(replyTo.V)
			comment.ReplyTo = &rt
		}

		atUri := comment.AtUri().String()
		commentMap[atUri] = &comment
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}
	defer rows.Close()

	// collect references from each comments
	commentAts := slices.Collect(maps.Keys(commentMap))
	allReferencs, err := GetReferencesAll(e, orm.FilterIn("from_at", commentAts))
	if err != nil {
		return nil, fmt.Errorf("failed to query reference_links: %w", err)
	}
	for commentAt, references := range allReferencs {
		if comment, ok := commentMap[commentAt.String()]; ok {
			comment.References = references
		}
	}

	var comments []models.Comment
	for _, c := range commentMap {
		comments = append(comments, *c)
	}

	sort.Slice(comments, func(i, j int) bool {
		return comments[i].Created.After(comments[j].Created)
	})

	return comments, nil
}