package db

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

	"github.com/lerko/nib/internal/link"
)

type Backlink struct {
	EntityID string
	Title    *string
	Body     string
	LinkText string
}

func escapeLike(s string) string {
	r := strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`)
	return r.Replace(s)
}

func (s *Store) resolveLink(ctx context.Context, tx *sql.Tx, linkText string, excludeID string) *string {
	lower := strings.ToLower(linkText)

	var id string
	err := tx.QueryRowContext(ctx, `
		SELECT id FROM entities
		WHERE LOWER(title) = ? AND id != ? AND deleted_at IS NULL
		ORDER BY created_at DESC LIMIT 1`, lower, excludeID).Scan(&id)
	if err == nil {
		return &id
	}

	err = tx.QueryRowContext(ctx, `
		SELECT id FROM entities
		WHERE LOWER(body) LIKE ? ESCAPE '\' AND id != ? AND deleted_at IS NULL
		ORDER BY created_at DESC LIMIT 1`, "%"+escapeLike(lower)+"%", excludeID).Scan(&id)
	if err == nil {
		return &id
	}

	return nil
}

func syncLinks(ctx context.Context, tx *sql.Tx, s *Store, entityID string, body string) error {
	if _, err := tx.ExecContext(ctx, "DELETE FROM entity_links WHERE from_id = ?", entityID); err != nil {
		return err
	}

	linkTexts := link.ExtractLinks(body)
	for _, lt := range linkTexts {
		toID := s.resolveLink(ctx, tx, lt, entityID)
		if _, err := tx.ExecContext(ctx,
			"INSERT OR IGNORE INTO entity_links (from_id, to_id, link_text) VALUES (?, ?, ?)",
			entityID, toID, lt); err != nil {
			return err
		}
	}
	return nil
}

func (s *Store) LoadBacklinks(ctx context.Context, entityID string) ([]Backlink, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT e.id, e.title, e.body, el.link_text
		FROM entity_links el
		JOIN entities e ON e.id = el.from_id
		WHERE el.to_id = ? AND e.deleted_at IS NULL
		ORDER BY e.created_at DESC`, entityID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var backlinks []Backlink
	for rows.Next() {
		var bl Backlink
		var title sql.NullString
		if err := rows.Scan(&bl.EntityID, &title, &bl.Body, &bl.LinkText); err != nil {
			return nil, err
		}
		if title.Valid {
			bl.Title = &title.String
		}
		backlinks = append(backlinks, bl)
	}
	return backlinks, rows.Err()
}