package db

import "context"
import "database/sql"
import "errors"
import "strings"
import "time"

import "codit/internal/models"
import "codit/internal/util"

var ErrCommentNotFound = errors.New("comment not found")
var ErrChecklistItemNotFound = errors.New("checklist item not found")
var ErrBlockAttachmentNotFound = errors.New("block attachment not found")

const blockCommentSelectCols = `
	c.public_id, bl.public_id,
	c.content,
	COALESCE(u.public_id, ''), COALESCE(u.username, ''),
	c.created_at, c.updated_at, c.delete_at`

const blockCommentSelectFrom = `
	FROM block_comments c
	JOIN blocks bl ON bl.id = c.block_id
	LEFT JOIN users u ON u.id = c.created_by`

func scanBlockComment(row interface{ Scan(...any) error }, bc *models.BlockComment) error {
	return row.Scan(
		&bc.ID,
		&bc.BlockID,
		&bc.Content,
		&bc.CreatedBy, &bc.CreatedByName,
		&bc.CreatedAt, &bc.UpdatedAt, &bc.DeleteAt,
	)
}

func (s *Store) ListBlockComments(boardID, blockID string) ([]models.BlockComment, error) {
	var rows *sql.Rows
	var err error
	var comments []models.BlockComment
	var bc models.BlockComment

	rows, err = s.Query(
		`SELECT`+blockCommentSelectCols+blockCommentSelectFrom+`
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND c.delete_at = 0
		ORDER BY c.created_at ASC`, boardID, blockID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		bc = models.BlockComment{}
		err = scanBlockComment(rows, &bc)
		if err != nil {
			return nil, err
		}
		comments = append(comments, bc)
	}
	return comments, rows.Err()
}

func (s *Store) CreateBlockComment(ctx context.Context, boardID, blockID, content, userID string) (models.BlockComment, error) {
	var id string
	var err error
	var now int64
	var internalBlockID int64
	var internalUserID int64
	var bc models.BlockComment
	var tx txExecutor
	var owned bool

	id, err = util.NewID()
	if err != nil {
		return bc, err
	}
	now = time.Now().Unix()

	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return bc, err
	}
	err = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0`,
		boardID, blockID).Scan(&internalBlockID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return bc, sql.ErrNoRows
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return bc, err
	}

	err = tx.QueryRow(`SELECT id FROM users WHERE public_id = ?`, userID).Scan(&internalUserID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return bc, err
	}

	_, err = tx.Exec(`
		INSERT INTO block_comments (public_id, block_id, content, created_by, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?)`,
		id, internalBlockID, content, internalUserID, now, now)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return bc, err
	}

	err = tx.QueryRow(
		`SELECT`+blockCommentSelectCols+blockCommentSelectFrom+`
		WHERE c.public_id = ?`, id).Scan(
		&bc.ID, &bc.BlockID, &bc.Content,
		&bc.CreatedBy, &bc.CreatedByName,
		&bc.CreatedAt, &bc.UpdatedAt, &bc.DeleteAt)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return bc, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return bc, err
	}
	return bc, nil
}

func (s *Store) DeleteBlockComment(boardID, blockID, commentID string) error {
	var now int64
	var result sql.Result
	var n int64
	var err error

	now = time.Now().Unix()
	result, err = s.Exec(`
		UPDATE block_comments SET delete_at = ?
		WHERE public_id = ?
		  AND delete_at = 0
		  AND block_id = (
		    SELECT bl.id FROM blocks bl
		    JOIN boards brd ON brd.id = bl.board_id
		    WHERE brd.public_id = ? AND bl.public_id = ?
		  )`, now, commentID, boardID, blockID)
	if err != nil {
		return err
	}
	n, err = result.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrCommentNotFound
	}
	return nil
}

const blockChecklistItemSelectCols = `
	ci.public_id, bl.public_id,
	ci.title, ci.done, ci.display_order,
	COALESCE(cu.public_id, ''), COALESCE(uu.public_id, ''),
	ci.created_at, ci.updated_at, ci.delete_at`

const blockChecklistItemSelectFrom = `
	FROM block_checklist_items ci
	JOIN blocks bl ON bl.id = ci.block_id
	LEFT JOIN users cu ON cu.id = ci.created_by
	LEFT JOIN users uu ON uu.id = ci.updated_by`

func scanBlockChecklistItem(row interface{ Scan(...any) error }, item *models.BlockChecklistItem) error {
	return row.Scan(
		&item.ID,
		&item.BlockID,
		&item.Title,
		&item.Done,
		&item.DisplayOrder,
		&item.CreatedBy, &item.UpdatedBy,
		&item.CreatedAt, &item.UpdatedAt, &item.DeleteAt,
	)
}

func (s *Store) ListBlockChecklistItems(boardID, blockID string) ([]models.BlockChecklistItem, error) {
	var rows *sql.Rows
	var err error
	var items []models.BlockChecklistItem
	var item models.BlockChecklistItem

	rows, err = s.Query(
		`SELECT`+blockChecklistItemSelectCols+blockChecklistItemSelectFrom+`
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND ci.delete_at = 0
		ORDER BY ci.display_order ASC, ci.created_at ASC`, boardID, blockID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		item = models.BlockChecklistItem{}
		err = scanBlockChecklistItem(rows, &item)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (s *Store) CreateBlockChecklistItem(ctx context.Context, boardID, blockID, title, userID string) (models.BlockChecklistItem, error) {
	var id string
	var err error
	var now int64
	var internalBlockID int64
	var internalUserID int64
	var maxOrder int64
	var item models.BlockChecklistItem
	var tx txExecutor
	var owned bool

	id, err = util.NewID()
	if err != nil {
		return item, err
	}
	now = time.Now().Unix()

	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return item, err
	}
	err = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0`,
		boardID, blockID).Scan(&internalBlockID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return item, sql.ErrNoRows
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	err = tx.QueryRow(`SELECT id FROM users WHERE public_id = ?`, userID).Scan(&internalUserID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	err = tx.QueryRow(`SELECT COALESCE(MAX(display_order), -1) FROM block_checklist_items WHERE block_id = ? AND delete_at = 0`, internalBlockID).Scan(&maxOrder)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	_, err = tx.Exec(`
		INSERT INTO block_checklist_items (public_id, block_id, title, done, display_order, created_by, updated_by, created_at, updated_at)
		VALUES (?, ?, ?, 0, ?, ?, ?, ?, ?)`,
		id, internalBlockID, title, maxOrder+1, internalUserID, internalUserID, now, now)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	err = scanBlockChecklistItem(
		tx.QueryRow(`SELECT`+blockChecklistItemSelectCols+blockChecklistItemSelectFrom+` WHERE ci.public_id = ?`, id),
		&item)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateBlockChecklistItem(boardID, blockID, itemID, title string, done bool, userID string) (models.BlockChecklistItem, error) {
	var now int64
	var result sql.Result
	var n int64
	var err error
	var item models.BlockChecklistItem

	now = time.Now().Unix()
	result, err = s.Exec(`
		UPDATE block_checklist_items SET title = ?, done = ?, updated_by = (SELECT id FROM users WHERE public_id = ?), updated_at = ?
		WHERE public_id = ?
		  AND delete_at = 0
		  AND block_id = (
		    SELECT bl.id FROM blocks bl
		    JOIN boards brd ON brd.id = bl.board_id
		    WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0
		  )`, title, done, userID, now, itemID, boardID, blockID)
	if err != nil {
		return item, err
	}
	n, err = result.RowsAffected()
	if err != nil {
		return item, err
	}
	if n == 0 {
		return item, ErrChecklistItemNotFound
	}

	err = scanBlockChecklistItem(
		s.QueryRow(`SELECT`+blockChecklistItemSelectCols+blockChecklistItemSelectFrom+` WHERE ci.public_id = ?`, itemID),
		&item)
	return item, err
}

func (s *Store) DeleteBlockChecklistItem(boardID, blockID, itemID string) error {
	var now int64
	var result sql.Result
	var n int64
	var err error

	now = time.Now().Unix()
	result, err = s.Exec(`
		UPDATE block_checklist_items SET delete_at = ?
		WHERE public_id = ?
		  AND delete_at = 0
		  AND block_id = (
		    SELECT bl.id FROM blocks bl
		    JOIN boards brd ON brd.id = bl.board_id
		    WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0
		  )`, now, itemID, boardID, blockID)
	if err != nil {
		return err
	}
	n, err = result.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrChecklistItemNotFound
	}
	return nil
}

func (s *Store) ReorderBlockChecklistItems(ctx context.Context, boardID, blockID string, ids []string) ([]models.BlockChecklistItem, error) {
	var internalBlockID int64
	var err error
	var now int64
	var tx txExecutor
	var owned bool
	var i int
	var id string
	var result sql.Result
	var n int64

	now = time.Now().Unix()
	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return nil, err
	}
	err = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0`,
		boardID, blockID).Scan(&internalBlockID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return nil, sql.ErrNoRows
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return nil, err
	}

	for i = 0; i < len(ids); i++ {
		id = ids[i]
		result, err = tx.Exec(`
			UPDATE block_checklist_items SET display_order = ?, updated_at = ?
			WHERE public_id = ? AND block_id = ? AND delete_at = 0`,
			i, now, id, internalBlockID)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return nil, err
		}
		n, err = result.RowsAffected()
		if err != nil {
			rollbackIfOwned(tx, owned)
			return nil, err
		}
		if n == 0 {
			rollbackIfOwned(tx, owned)
			return nil, ErrChecklistItemNotFound
		}
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return nil, err
	}
	return s.ListBlockChecklistItems(boardID, blockID)
}

const blockAttachmentSelectCols = `
	ba.public_id, bl.public_id,
	ba.filename, ba.content_type, ba.size, ba.storage_path,
	COALESCE(u.public_id, ''),
	ba.created_at, ba.delete_at`

const blockAttachmentSelectFrom = `
	FROM block_attachments ba
	JOIN blocks bl ON bl.id = ba.block_id
	LEFT JOIN users u ON u.id = ba.created_by`

func scanBlockAttachment(row interface{ Scan(...any) error }, item *models.BlockAttachment) error {
	return row.Scan(
		&item.ID,
		&item.BlockID,
		&item.Filename,
		&item.ContentType,
		&item.Size,
		&item.StoragePath,
		&item.CreatedBy,
		&item.CreatedAt,
		&item.DeleteAt,
	)
}

func (s *Store) ListBlockAttachments(boardID, blockID string) ([]models.BlockAttachment, error) {
	var rows *sql.Rows
	var err error
	var items []models.BlockAttachment
	var item models.BlockAttachment

	rows, err = s.Query(
		`SELECT`+blockAttachmentSelectCols+blockAttachmentSelectFrom+`
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND ba.delete_at = 0
		ORDER BY ba.created_at DESC`, boardID, blockID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		item = models.BlockAttachment{}
		err = scanBlockAttachment(rows, &item)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (s *Store) CreateBlockAttachment(ctx context.Context, boardID, blockID string, item models.BlockAttachment) (models.BlockAttachment, error) {
	var err error
	var internalBlockID int64
	var internalUserID int64
	var now int64
	var tx txExecutor
	var owned bool

	now = time.Now().Unix()
	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return item, err
	}
	err = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0`,
		boardID, blockID).Scan(&internalBlockID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return item, sql.ErrNoRows
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	err = tx.QueryRow(`SELECT id FROM users WHERE public_id = ?`, item.CreatedBy).Scan(&internalUserID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	item.BlockID = blockID
	item.CreatedAt = now
	_, err = tx.Exec(`
		INSERT INTO block_attachments (public_id, block_id, filename, content_type, size, storage_path, created_by, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID, internalBlockID, item.Filename, item.ContentType, item.Size, item.StoragePath, internalUserID, now)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	err = scanBlockAttachment(
		tx.QueryRow(`SELECT`+blockAttachmentSelectCols+blockAttachmentSelectFrom+` WHERE ba.public_id = ?`, item.ID),
		&item)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil { return item, err }
	return item, nil
}

func (s *Store) GetBlockAttachment(boardID, blockID, attachmentID string) (models.BlockAttachment, error) {
	var item models.BlockAttachment
	var err error

	err = scanBlockAttachment(
		s.QueryRow(`SELECT`+blockAttachmentSelectCols+blockAttachmentSelectFrom+`
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND ba.public_id = ? AND ba.delete_at = 0`,
			boardID, blockID, attachmentID),
		&item)
	if err == sql.ErrNoRows {
		return item, ErrBlockAttachmentNotFound
	}
	return item, err
}

func (s *Store) DeleteBlockAttachment(ctx context.Context, boardID, blockID, attachmentID string) (models.BlockAttachment, error) {
	var item models.BlockAttachment
	var now int64
	var result sql.Result
	var n int64
	var err error
	var tx txExecutor
	var owned bool

	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return item, err
	}
	err = scanBlockAttachment(
		tx.QueryRow(`SELECT`+blockAttachmentSelectCols+blockAttachmentSelectFrom+`
			JOIN boards brd ON brd.id = bl.board_id
			WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0 AND ba.public_id = ? AND ba.delete_at = 0`,
			boardID, blockID, attachmentID),
		&item)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return item, ErrBlockAttachmentNotFound
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	now = time.Now().Unix()
	result, err = tx.Exec(`
		UPDATE block_attachments SET delete_at = ?
		WHERE public_id = ?
		  AND delete_at = 0
		  AND block_id = (
		    SELECT bl.id FROM blocks bl
		    JOIN boards brd ON brd.id = bl.board_id
		    WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0
		  )`, now, attachmentID, boardID, blockID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	n, err = result.RowsAffected()
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	if n == 0 {
		rollbackIfOwned(tx, owned)
		return item, ErrBlockAttachmentNotFound
	}
	item.DeleteAt = now

	err = commitIfOwned(tx, owned)
	if err != nil { return item, err }
	return item, nil
}

func (s *Store) GetBlockProperties(boardID, blockID string) (models.BlockProperties, error) {
	var bp models.BlockProperties
	var err error

	err = s.QueryRow(`
		SELECT bl.public_id,
		  COALESCE(p.status, ''), COALESCE(p.card_type, ''),
		  COALESCE(p.priority, ''), COALESCE(p.due_date, ''),
		  COALESCE(p.sprint, ''), COALESCE(p.description, ''),
		  COALESCE(ci.total, 0), COALESCE(ci.done, 0),
		  COALESCE(ba.total, 0), COALESCE(ba.images, 0)
		FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		LEFT JOIN block_properties p ON p.block_id = bl.id
		LEFT JOIN (
		  SELECT block_id, COUNT(*) AS total, SUM(CASE WHEN done THEN 1 ELSE 0 END) AS done
		  FROM block_checklist_items
		  WHERE delete_at = 0
		  GROUP BY block_id
		) ci ON ci.block_id = bl.id
		LEFT JOIN (
		  SELECT block_id, COUNT(*) AS total, SUM(CASE WHEN content_type LIKE 'image/%' THEN 1 ELSE 0 END) AS images
		  FROM block_attachments
		  WHERE delete_at = 0
		  GROUP BY block_id
		) ba ON ba.block_id = bl.id
		WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0`,
		boardID, blockID).Scan(
		&bp.BlockID,
		&bp.Status, &bp.CardType,
		&bp.Priority, &bp.DueDate,
		&bp.Sprint, &bp.Description,
		&bp.ChecklistTotal, &bp.ChecklistDone,
		&bp.AttachmentTotal, &bp.ImageTotal)
	if err != nil {
		return bp, err
	}
	err = s.populateBlockPropertiesAssignees(boardID, &bp)
	return bp, err
}

func (s *Store) ListAllBlockProperties(boardID string) ([]models.BlockProperties, error) {
	var rows *sql.Rows
	var err error
	var result []models.BlockProperties
	var bp models.BlockProperties
	var assignees map[string][]models.BoardAssignableUser
	var i int

	rows, err = s.Query(`
		SELECT bl.public_id,
		  COALESCE(p.status, ''), COALESCE(p.card_type, ''),
		  COALESCE(p.priority, ''), COALESCE(p.due_date, ''),
		  COALESCE(p.sprint, ''), COALESCE(p.description, ''),
		  COALESCE(ci.total, 0), COALESCE(ci.done, 0),
		  COALESCE(ba.total, 0), COALESCE(ba.images, 0)
		FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		LEFT JOIN block_properties p ON p.block_id = bl.id
		LEFT JOIN (
		  SELECT block_id, COUNT(*) AS total, SUM(CASE WHEN done THEN 1 ELSE 0 END) AS done
		  FROM block_checklist_items
		  WHERE delete_at = 0
		  GROUP BY block_id
		) ci ON ci.block_id = bl.id
		LEFT JOIN (
		  SELECT block_id, COUNT(*) AS total, SUM(CASE WHEN content_type LIKE 'image/%' THEN 1 ELSE 0 END) AS images
		  FROM block_attachments
		  WHERE delete_at = 0
		  GROUP BY block_id
		) ba ON ba.block_id = bl.id
		WHERE brd.public_id = ? AND bl.type = 'card' AND bl.delete_at = 0
		ORDER BY COALESCE(p.sprint, ''), bl.created_at ASC`, boardID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		bp = models.BlockProperties{}
		err = rows.Scan(
			&bp.BlockID,
			&bp.Status, &bp.CardType,
			&bp.Priority, &bp.DueDate,
			&bp.Sprint, &bp.Description,
			&bp.ChecklistTotal, &bp.ChecklistDone,
			&bp.AttachmentTotal, &bp.ImageTotal)
		if err != nil {
			return nil, err
		}
		result = append(result, bp)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	assignees, err = s.listBlockAssigneesForBoard(boardID)
	if err != nil {
		return nil, err
	}
	for i = 0; i < len(result); i++ {
		setBlockPropertiesAssignees(&result[i], assignees[result[i].BlockID])
	}
	return result, nil
}

func normalizeBlockAssigneeIDs(props models.BlockProperties) []string {
	var seen map[string]bool
	var ids []string
	var id string
	var i int

	seen = map[string]bool{}
	for i = 0; i < len(props.AssigneeIDs); i++ {
		id = strings.TrimSpace(props.AssigneeIDs[i])
		if id == "" || seen[id] {
			continue
		}
		seen[id] = true
		ids = append(ids, id)
	}
	for i = 0; i < len(props.Assignees); i++ {
		id = strings.TrimSpace(props.Assignees[i].ID)
		if id == "" || seen[id] {
			continue
		}
		seen[id] = true
		ids = append(ids, id)
	}
	id = strings.TrimSpace(props.AssigneeID)
	if id != "" && !seen[id] {
		ids = append(ids, id)
	}
	return ids
}

func setBlockPropertiesAssignees(bp *models.BlockProperties, assignees []models.BoardAssignableUser) {
	var i int

	bp.Assignees = assignees
	bp.AssigneeIDs = []string{}
	bp.AssigneeID = ""
	bp.AssigneeName = ""
	for i = 0; i < len(assignees); i++ {
		bp.AssigneeIDs = append(bp.AssigneeIDs, assignees[i].ID)
	}
	if len(assignees) > 0 {
		bp.AssigneeID = assignees[0].ID
		bp.AssigneeName = assignees[0].DisplayName
		if bp.AssigneeName == "" {
			bp.AssigneeName = assignees[0].Username
		}
	}
}

func (s *Store) populateBlockPropertiesAssignees(boardID string, bp *models.BlockProperties) error {
	var all map[string][]models.BoardAssignableUser
	var err error

	all, err = s.listBlockAssigneesForBoard(boardID)
	if err != nil {
		return err
	}
	setBlockPropertiesAssignees(bp, all[bp.BlockID])
	return nil
}

func (s *Store) listBlockAssigneesForBoard(boardID string) (map[string][]models.BoardAssignableUser, error) {
	var rows *sql.Rows
	var err error
	var result map[string][]models.BoardAssignableUser
	var blockID string
	var user models.BoardAssignableUser

	result = map[string][]models.BoardAssignableUser{}
	rows, err = s.Query(`
		SELECT bl.public_id, u.public_id, u.username, u.display_name,
		  CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END
		FROM block_assignees ba
		JOIN blocks bl ON bl.id = ba.block_id
		JOIN boards brd ON brd.id = bl.board_id
		JOIN users u ON u.id = ba.user_id
		WHERE brd.public_id = ? AND brd.delete_at = 0 AND bl.delete_at = 0 AND u.disabled = 0
		ORDER BY ba.assigned_at ASC, u.username ASC`, boardID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		user = models.BoardAssignableUser{}
		err = rows.Scan(&blockID, &user.ID, &user.Username, &user.DisplayName, &user.AvatarURL)
		if err != nil {
			return nil, err
		}
		result[blockID] = append(result[blockID], user)
	}
	return result, rows.Err()
}

func (s *Store) UpsertBlockProperties(ctx context.Context, boardID, blockID string, props models.BlockProperties) (models.BlockProperties, error) {
	var internalBlockID int64
	var err error
	var tx txExecutor
	var owned bool
	var assigneeIDs []string
	var i int
	var result sql.Result
	var affected int64

	assigneeIDs = normalizeBlockAssigneeIDs(props)
	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return props, err
	}
	err = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.public_id = ? AND bl.delete_at = 0`,
		boardID, blockID).Scan(&internalBlockID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return props, sql.ErrNoRows
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return props, err
	}
	_, err = tx.Exec(`
		INSERT INTO block_properties (block_id, status, card_type, priority, due_date, assignee_id, sprint, description)
		VALUES (?, ?, ?, ?, ?, NULL, ?, ?)
		ON CONFLICT(block_id) DO UPDATE SET
		  status = excluded.status,
		  card_type = excluded.card_type,
		  priority = excluded.priority,
		  due_date = excluded.due_date,
		  assignee_id = excluded.assignee_id,
		  sprint = excluded.sprint,
		  description = excluded.description`,
		internalBlockID, props.Status, props.CardType,
		props.Priority, props.DueDate,
		props.Sprint, props.Description)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return props, err
	}
	_, err = tx.Exec(`DELETE FROM block_assignees WHERE block_id = ?`, internalBlockID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return props, err
	}
	for i = 0; i < len(assigneeIDs); i++ {
		result, err = tx.Exec(`
			INSERT INTO block_assignees (block_id, user_id, assigned_at)
			SELECT ?, u.id, ?
			FROM users u
			WHERE u.public_id = ? AND u.disabled = 0`,
			internalBlockID, time.Now().Unix(), assigneeIDs[i])
		if err != nil {
			rollbackIfOwned(tx, owned)
			return props, err
		}
		affected, err = result.RowsAffected()
		if err != nil {
			rollbackIfOwned(tx, owned)
			return props, err
		}
		if affected == 0 {
			rollbackIfOwned(tx, owned)
			return props, ErrUserNotFound
		}
	}

	// Keep blocks.fields status in sync so the kanban view can group without extra queries.
	_, err = tx.Exec(`
		UPDATE blocks SET fields = json_set(COALESCE(fields, '{}'), '$.status', ?), updated_at = ?
		WHERE id = ?`, props.Status, time.Now().Unix(), internalBlockID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return props, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return props, err
	}

	return s.GetBlockProperties(boardID, blockID)
}