package db

import (
	"database/sql"
	"errors"
	"fmt"
	"slices"
	"strings"
	"time"

	"github.com/bluesky-social/indigo/atproto/syntax"
	lexutil "github.com/bluesky-social/indigo/lex/util"
	"github.com/ipfs/go-cid"
	"tangled.org/core/appview/models"
	"tangled.org/core/orm"
)

func AddString(d *DB, s models.String) error {
	tx, err := d.Begin()
	if err != nil {
		return fmt.Errorf("starting transaction: %w", err)
	}
	defer tx.Rollback()
	res, err := tx.Exec(
		`insert into strings (
			did,
			rkey,
			cid,
			title,
			description,
			file_name,
			file_content,
			created
		)
		values (?, ?, ?, ?, ?, ?, ?, ?)
		on conflict(did, rkey) do update set
			cid         = excluded.cid,
			title       = excluded.title,
			description = excluded.description,
			file_name   = excluded.file_name,
			file_content= excluded.file_content,
			created     = excluded.created,
			edited      = case when strings.cid is not null then ? else strings.edited end
		where strings.cid is not excluded.cid`,
		s.Did,
		s.Rkey,
		s.Cid,
		s.Title,
		s.Description,
		s.FileName,
		s.FileContent,
		s.Created.Format(time.RFC3339),
		time.Now().Format(time.RFC3339),
	)
	if err != nil {
		return fmt.Errorf("inserting string: %w", err)
	}

	num, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("calculating affected rows: %w", err)
	}
	if num == 0 {
		return nil
	}

	_, err = tx.Exec(`delete from string_files where at_uri = ?`, s.AtUri())
	if err != nil {
		return fmt.Errorf("deleting old files: %w", err)
	}

	// legacy single-file strings carry their content in file_name/file_content
	// and have no rows in string_files; nothing more to insert.
	if len(s.Files) == 0 {
		if err := tx.Commit(); err != nil {
			return fmt.Errorf("commiting transaction: %w", err)
		}
		return nil
	}

	vals := make([]string, len(s.Files))
	args := make([]any, 0, len(s.Files)*8)
	for i, file := range s.Files {
		vals[i] = "(?, ?, ?, ?, ?)"
		args = append(args,
			s.AtUri(),
			file.Name,
			file.Content.Ref.String(),
			file.Content.Size,
			file.Content.MimeType,
		)
	}
	_, err = tx.Exec(
		fmt.Sprintf(
			`insert into string_files (
				at_uri,
				name,
				content_ref,
				content_size,
				content_mimetype
			)
			values %s`,
			strings.Join(vals, ","),
		),
		args...,
	)
	if err != nil {
		return fmt.Errorf("inserting files: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commiting transaction: %w", err)
	}
	return nil
}

func GetString(e Execer, filters ...orm.Filter) (models.String, error) {
	strings, err := GetStrings(e, 0, filters...)
	if err != nil {
		return models.String{}, err
	}
	if len(strings) != 1 {
		return models.String{}, sql.ErrNoRows
	}
	return strings[0], nil
}

func GetStrings(e Execer, limit int, filters ...orm.Filter) ([]models.String, error) {
	stringMap := make(map[syntax.ATURI]*models.String)

	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 ")
	}

	limitClause := ""
	if limit != 0 {
		limitClause = fmt.Sprintf(" limit %d ", limit)
	}

	query := fmt.Sprintf(
		`select
			did,
			rkey,
			cid,
			title,
			description,
			file_name,
			file_content,
			created,
			edited
		from strings
		%s
		order by created desc
		%s`,
		whereClause,
		limitClause,
	)

	rows, err := e.Query(query, args...)

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

	for rows.Next() {
		var s models.String
		var createdAt string
		var cid, title, description, editedAt sql.Null[string]

		if err := rows.Scan(
			&s.Did,
			&s.Rkey,
			&cid,
			&title,
			&description,
			&s.FileName,
			&s.FileContent,
			&createdAt,
			&editedAt,
		); err != nil {
			return nil, err
		}

		if cid.Valid {
			s.Cid = new(syntax.CID)
			*s.Cid = syntax.CID(cid.V)
		}

		if title.Valid {
			s.Title = new(string)
			*s.Title = title.V
		}

		if description.Valid {
			s.Description = new(string)
			*s.Description = description.V
		}

		s.Created, err = time.Parse(time.RFC3339, createdAt)
		if err != nil {
			s.Created = time.Now()
		}

		if editedAt.Valid {
			e, err := time.Parse(time.RFC3339, editedAt.V)
			if err != nil {
				e = time.Now()
			}
			s.Edited = &e
		}

		s.Stats = &models.StringStats{}
		stringMap[s.AtUri()] = &s
	}

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

	// if no strings, return early
	if len(stringMap) == 0 {
		return nil, nil
	}

	// build IN clause for related queries
	inClause := strings.TrimSuffix(strings.Repeat("?, ", len(stringMap)), ", ")
	args = make([]any, len(stringMap))
	i := 0
	for _, s := range stringMap {
		args[i] = s.AtUri()
		i++
	}

	// get files
	{
		rows, err := e.Query(
			fmt.Sprintf(
				`select
					at_uri,
					name,
					content_ref,
					content_size,
					content_mimetype
				from string_files
				where at_uri in (%s) order by at_uri, id`,
				inClause,
			),
			args...,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to execute string_files query: %w", err)
		}
		defer rows.Close()

		for rows.Next() {
			var stringAt syntax.ATURI
			var file models.String_File

			var contentRef string
			if err := rows.Scan(
				&stringAt,
				&file.Name,
				&contentRef,
				&file.Content.Size,
				&file.Content.MimeType,
			); err != nil {
				return nil, fmt.Errorf("failed to execute string_files query: %w", err)
			}

			file.Content.Ref = lexutil.LexLink(cid.MustParse(contentRef))

			if s, ok := stringMap[stringAt]; ok {
				s.Files = append(s.Files, file)
			}
		}
		if err = rows.Err(); err != nil {
			return nil, fmt.Errorf("failed to execute string_files query: %w", err)
		}
	}

	// get star counts
	{
		rows, err := e.Query(
			fmt.Sprintf(
				`select subject, count(1) from stars where subject_type = 'string' and subject in (%s) group by subject`,
				inClause,
			),
			args...,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to execute star-count query: %w", err)
		}
		defer rows.Close()

		for rows.Next() {
			var stringAt syntax.ATURI
			var count int
			if err := rows.Scan(&stringAt, &count); err != nil {
				continue
			}
			if s, ok := stringMap[stringAt]; ok {
				s.Stats.StarCount = count
			}
		}
		if err = rows.Err(); err != nil {
			return nil, fmt.Errorf("failed to execute star-count query: %w", err)
		}
	}

	var all []models.String
	for _, s := range stringMap {
		all = append(all, *s)
	}

	// sort by created timestamp (desc)
	slices.SortFunc(all, func(a, b models.String) int {
		if a.Created.After(b.Created) {
			return -1
		}
		return 1
	})

	return all, nil
}

func CountStrings(e Execer, filters ...orm.Filter) (int64, 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 ")
	}

	repoQuery := fmt.Sprintf(`select count(1) from strings %s`, whereClause)
	var count int64
	err := e.QueryRow(repoQuery, args...).Scan(&count)

	if !errors.Is(err, sql.ErrNoRows) && err != nil {
		return 0, err
	}

	return count, nil
}

func DeleteString(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(`delete from strings %s`, whereClause)

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