package db

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

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

var ErrParentNotInBoard = errors.New("parent block not found in this board")
var ErrParentCycle = errors.New("parent would create a cycle in the block tree")
var ErrBoardNotFound = errors.New("board not found")
var ErrUserNotFound = errors.New("user not found")

const boardSelectCols = `
	b.public_id,
	pr.public_id, pr.slug, pr.name,
	b.title, b.description, b.icon, b.show_description,
	b.card_properties, b.is_template,
	COALESCE(cu.public_id, ''), COALESCE(uu.public_id, ''),
	COALESCE(cu.username, ''), COALESCE(uu.username, ''),
	b.created_at, b.updated_at, b.delete_at`

const boardSelectFrom = `
	FROM boards b
	JOIN projects pr ON pr.id = b.project_id
	LEFT JOIN users cu ON cu.id = b.created_by
	LEFT JOIN users uu ON uu.id = b.updated_by`

func scanBoard(row interface{ Scan(...any) error }, b *models.Board) error {
	return row.Scan(
		&b.ID,
		&b.ProjectID, &b.ProjectSlug, &b.ProjectName,
		&b.Title, &b.Description, &b.Icon, &b.ShowDescription,
		&b.CardProperties, &b.IsTemplate,
		&b.CreatedBy, &b.UpdatedBy,
		&b.CreatedByName, &b.UpdatedByName,
		&b.CreatedAt, &b.UpdatedAt, &b.DeleteAt,
	)
}

func (s *Store) CreateBoard(ctx context.Context, board models.Board) (models.Board, error) {
	var id string
	var err error
	var nowUnix int64
	var tx txExecutor
	var owned bool

	if board.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return board, err
		}
		board.ID = id
	}
	nowUnix = time.Now().UTC().Unix()
	board.CreatedAt = nowUnix
	board.UpdatedAt = nowUnix
	if board.CardProperties == "" {
		board.CardProperties = "[]"
	}

	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return board, err
	}
	_, err = tx.Exec(`
		INSERT INTO boards (public_id, project_id, title, description, icon, show_description,
			card_properties, is_template, created_by, updated_by, created_at, updated_at, delete_at)
		VALUES (?,
			(SELECT id FROM projects WHERE public_id = ?),
			?, ?, ?, ?, ?, ?,
			(SELECT id FROM users WHERE public_id = ?),
			(SELECT id FROM users WHERE public_id = ?),
			?, ?, 0)`,
		board.ID, board.ProjectID,
		board.Title, board.Description, board.Icon, board.ShowDescription,
		board.CardProperties, board.IsTemplate,
		board.CreatedBy, board.CreatedBy,
		board.CreatedAt, board.UpdatedAt,
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return board, err
	}
	_, err = tx.Exec(`
		INSERT INTO board_members (board_id, user_id, role, created_at)
		VALUES (
			(SELECT id FROM boards WHERE public_id = ?),
			(SELECT id FROM users WHERE public_id = ?),
			'admin', ?)`,
		board.ID, board.CreatedBy, board.CreatedAt)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return board, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return board, err
	}
	return s.GetBoard(board.ID)
}

// GetBoard returns the board only if it is not soft-deleted.
func (s *Store) GetBoard(id string) (models.Board, error) {
	var board models.Board
	var row *sql.Row
	row = s.QueryRow(
		`SELECT`+boardSelectCols+boardSelectFrom+` WHERE b.public_id = ? AND b.delete_at = 0`, id)
	return board, scanBoard(row, &board)
}

// GetBoardProjectID returns the project public_id for a non-deleted board.
// Used by requireBoardRole to fall back to the project role.
func (s *Store) GetBoardProjectID(boardID string) (string, error) {
	var projectID string
	var row *sql.Row
	row = s.QueryRow(`
		SELECT pr.public_id
		FROM boards b JOIN projects pr ON pr.id = b.project_id
		WHERE b.public_id = ? AND b.delete_at = 0`,
		boardID)
	return projectID, row.Scan(&projectID)
}

func (s *Store) ListBoards(projectID string) ([]models.Board, error) {
	var rows *sql.Rows
	var err error
	var boards []models.Board
	var board models.Board

	rows, err = s.Query(
		`SELECT`+boardSelectCols+boardSelectFrom+`
		WHERE pr.public_id = ? AND b.delete_at = 0
		ORDER BY b.created_at ASC`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		board = models.Board{}
		err = scanBoard(rows, &board)
		if err != nil {
			return nil, err
		}
		boards = append(boards, board)
	}
	return boards, rows.Err()
}

// ListBoardsWhereExplicitMember returns non-deleted boards in the given project
// for which the user has an explicit board_members row. Used when the caller
// has no project-level role but may still have per-board access.
func (s *Store) ListBoardsWhereExplicitMember(projectID string, userID string) ([]models.Board, error) {
	var rows *sql.Rows
	var err error
	var boards []models.Board
	var board models.Board

	rows, err = s.Query(
		`SELECT`+boardSelectCols+boardSelectFrom+`
		JOIN board_members bm ON bm.board_id = b.id
		JOIN users mu ON mu.id = bm.user_id
		WHERE pr.public_id = ? AND mu.public_id = ? AND b.delete_at = 0
		ORDER BY b.created_at ASC`, projectID, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		board = models.Board{}
		err = scanBoard(rows, &board)
		if err != nil {
			return nil, err
		}
		boards = append(boards, board)
	}
	return boards, rows.Err()
}

func (s *Store) ListAllBoardsAdmin() ([]models.Board, error) {
	var rows *sql.Rows
	var err error
	var boards []models.Board
	var board models.Board

	rows, err = s.Query(
		`SELECT` + boardSelectCols + boardSelectFrom + `
		WHERE b.delete_at = 0
		ORDER BY pr.name ASC, b.title ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		board = models.Board{}
		err = scanBoard(rows, &board)
		if err != nil {
			return nil, err
		}
		boards = append(boards, board)
	}
	return boards, rows.Err()
}

func (s *Store) ListAllBoardsForUser(userID string) ([]models.Board, error) {
	var rows *sql.Rows
	var err error
	var boards []models.Board
	var board models.Board

	rows, err = s.Query(
		`SELECT`+boardSelectCols+boardSelectFrom+`
		WHERE b.delete_at = 0
		AND (
			EXISTS (
				SELECT 1 FROM project_role_bindings prb
				WHERE prb.project_id = b.project_id
				  AND prb.subject_type = 'user'
				  AND prb.subject_id = (SELECT id FROM users WHERE public_id = ?)
			)
			OR EXISTS (
				SELECT 1 FROM project_role_bindings prb
				JOIN user_groups ug ON ug.id = prb.subject_id
				WHERE prb.project_id = b.project_id
				  AND prb.subject_type = 'group'
				  AND ug.disabled = 0
				  AND (
					ug.scope = 'all_users'
					OR EXISTS (
						SELECT 1 FROM user_group_members ugm
						JOIN users ux ON ux.id = ugm.user_id
						WHERE ugm.group_id = ug.id AND ux.public_id = ?
					)
				  )
			)
			OR EXISTS (
				SELECT 1 FROM board_members bm
				JOIN users u ON u.id = bm.user_id
				WHERE bm.board_id = b.id AND u.public_id = ?
			)
		)
		ORDER BY pr.name ASC, b.title ASC`,
		userID, userID, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		board = models.Board{}
		err = scanBoard(rows, &board)
		if err != nil {
			return nil, err
		}
		boards = append(boards, board)
	}
	return boards, rows.Err()
}

func (s *Store) UpdateBoard(board models.Board) (models.Board, error) {
	var err error
	var nowUnix int64
	nowUnix = time.Now().UTC().Unix()
	_, err = s.Exec(`
		UPDATE boards SET
			title = ?, description = ?, icon = ?, show_description = ?,
			card_properties = ?,
			updated_by = (SELECT id FROM users WHERE public_id = ?),
			updated_at = ?
		WHERE public_id = ? AND delete_at = 0`,
		board.Title, board.Description, board.Icon, board.ShowDescription,
		board.CardProperties,
		board.UpdatedBy, nowUnix,
		board.ID,
	)
	if err != nil {
		return board, err
	}
	return s.GetBoard(board.ID)
}

func (s *Store) DeleteBoard(id string, updatedBy string) error {
	var err error
	var nowUnix int64
	nowUnix = time.Now().UTC().Unix()
	_, err = s.Exec(`
		UPDATE boards SET delete_at = ?, updated_by = (SELECT id FROM users WHERE public_id = ?), updated_at = ?
		WHERE public_id = ? AND delete_at = 0`,
		nowUnix, updatedBy, nowUnix, id)
	return err
}

// ----- blocks -----

const blockSelectCols = `
	bl.public_id,
	b.public_id,
	COALESCE(pb.public_id, ''),
	COALESCE(cu.public_id, ''), COALESCE(uu.public_id, ''),
	bl.type, bl.title, bl.fields,
	bl.created_at, bl.updated_at, bl.completed_at, COALESCE(cbu.public_id, ''), bl.delete_at`

const blockSelectFrom = `
	FROM blocks bl
	JOIN boards b ON b.id = bl.board_id
	LEFT JOIN blocks pb ON pb.id = bl.parent_id AND bl.parent_id != 0
	LEFT JOIN users cu ON cu.id = bl.created_by
	LEFT JOIN users uu ON uu.id = bl.updated_by
	LEFT JOIN users cbu ON cbu.id = bl.completed_by`

func scanBlock(row interface{ Scan(...any) error }, bl *models.Block) error {
	return row.Scan(
		&bl.ID,
		&bl.BoardID,
		&bl.ParentID,
		&bl.CreatedBy, &bl.UpdatedBy,
		&bl.Type, &bl.Title, &bl.Fields,
		&bl.CreatedAt, &bl.UpdatedAt, &bl.CompletedAt, &bl.CompletedBy, &bl.DeleteAt,
	)
}

// resolveParentBlockID returns the internal row ID for a parent block constrained
// to the same board. Returns 0 when parentPublicID is "". Returns sql.ErrNoRows
// when the block doesn't exist, is deleted, or belongs to a different board.
func resolveParentBlockID(tx txExecutor, boardPublicID, parentPublicID string) (int64, error) {
	var parentID int64
	var row *sql.Row
	if parentPublicID == "" {
		return 0, nil
	}
	row = tx.QueryRow(`
		SELECT bl2.id FROM blocks bl2
		JOIN boards b2 ON b2.id = bl2.board_id
		WHERE bl2.public_id = ? AND b2.public_id = ? AND bl2.delete_at = 0 AND b2.delete_at = 0`,
		parentPublicID, boardPublicID)
	return parentID, row.Scan(&parentID)
}

// hasCycleAfterReparent returns true when assigning newParentID as the parent
// of blockID would create a cycle (including the self-parent case).
func hasCycleAfterReparent(tx txExecutor, blockID, newParentID int64) (bool, error) {
	var count int
	var row *sql.Row
	if newParentID == 0 {
		return false, nil
	}
	if newParentID == blockID {
		return true, nil
	}
	// Walk up from newParentID; if blockID appears in that chain there is a cycle.
	var err error
	row = tx.QueryRow(`
		WITH RECURSIVE ancestors(id) AS (
			SELECT ?
			UNION ALL
			SELECT b.parent_id FROM blocks b JOIN ancestors a ON b.id = a.id WHERE b.parent_id != 0
		)
		SELECT COUNT(*) FROM ancestors WHERE id = ?`,
		newParentID, blockID)
	err = row.Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

func (s *Store) insertBlockHistory(tx txExecutor, blockID int64, insertAt int64) error {
	var err error
	_, err = tx.Exec(`
		INSERT INTO blocks_history
			(block_id, public_id, board_id, parent_id, created_by, updated_by,
			 type, title, fields, created_at, updated_at, completed_at, completed_by, delete_at, insert_at)
		SELECT id, public_id, board_id, parent_id, created_by, updated_by,
			   type, title, fields, created_at, updated_at, completed_at, completed_by, delete_at, ?
		FROM blocks WHERE id = ?`,
		insertAt, blockID)
	return err
}

func (s *Store) CreateBlock(ctx context.Context, block models.Block) (models.Block, error) {
	var id string
	var err error
	var nowUnix int64
	var tx txExecutor
	var owned bool
	var result sql.Result
	var blockID int64
	var parentID int64

	if block.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return block, err
		}
		block.ID = id
	}
	nowUnix = time.Now().UTC().Unix()
	block.CreatedAt = nowUnix
	block.UpdatedAt = nowUnix
	if block.Fields == "" {
		block.Fields = "{}"
	}

	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return block, err
	}
	parentID, err = resolveParentBlockID(tx, block.BoardID, block.ParentID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return block, ErrParentNotInBoard
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	result, err = tx.Exec(`
		INSERT INTO blocks (public_id, board_id, parent_id, created_by, updated_by,
			type, title, fields, created_at, updated_at, completed_at, completed_by, delete_at)
		VALUES (?,
			(SELECT id FROM boards WHERE public_id = ? AND delete_at = 0),
			?,
			(SELECT id FROM users WHERE public_id = ?),
			(SELECT id FROM users WHERE public_id = ?),
			?, ?, ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), 0)`,
		block.ID, block.BoardID,
		parentID,
		block.CreatedBy, block.CreatedBy,
		block.Type, block.Title, block.Fields,
		block.CreatedAt, block.UpdatedAt,
		block.CompletedAt, block.CompletedBy,
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	blockID, err = result.LastInsertId()
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	err = s.insertBlockHistory(tx, blockID, nowUnix)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return block, err
	}
	return s.GetBlock(block.BoardID, block.ID)
}

// GetBlock returns a non-deleted block that belongs to the specified non-deleted board.
func (s *Store) GetBlock(boardID string, id string) (models.Block, error) {
	var block models.Block
	var row *sql.Row
	row = s.QueryRow(
		`SELECT`+blockSelectCols+blockSelectFrom+`
		WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
		id, boardID)
	return block, scanBlock(row, &block)
}

func (s *Store) ListBlocks(boardID string) ([]models.Block, error) {
	var rows *sql.Rows
	var err error
	var blocks []models.Block
	var block models.Block

	rows, err = s.Query(
		`SELECT`+blockSelectCols+blockSelectFrom+`
		WHERE b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0
		ORDER BY bl.created_at ASC`, boardID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		block = models.Block{}
		err = scanBlock(rows, &block)
		if err != nil {
			return nil, err
		}
		blocks = append(blocks, block)
	}
	return blocks, rows.Err()
}

func (s *Store) ListBlocksByType(boardID string, blockType string) ([]models.Block, error) {
	var rows *sql.Rows
	var err error
	var blocks []models.Block
	var block models.Block

	rows, err = s.Query(
		`SELECT`+blockSelectCols+blockSelectFrom+`
		WHERE b.public_id = ? AND bl.type = ? AND bl.delete_at = 0 AND b.delete_at = 0
		ORDER BY bl.created_at ASC`, boardID, blockType)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		block = models.Block{}
		err = scanBlock(rows, &block)
		if err != nil {
			return nil, err
		}
		blocks = append(blocks, block)
	}
	return blocks, rows.Err()
}

func (s *Store) UpdateBlock(ctx context.Context, block models.Block) (models.Block, error) {
	var err error
	var nowUnix int64
	var tx txExecutor
	var owned bool
	var row *sql.Row
	var blockID int64
	var parentID int64
	var hasCycle bool

	nowUnix = time.Now().UTC().Unix()
	block.UpdatedAt = nowUnix

	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return block, err
	}

	// Verify block belongs to the specified board and is not deleted.
	row = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards b ON b.id = bl.board_id
		WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
		block.ID, block.BoardID)
	err = row.Scan(&blockID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}

	parentID, err = resolveParentBlockID(tx, block.BoardID, block.ParentID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return block, ErrParentNotInBoard
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	hasCycle, err = hasCycleAfterReparent(tx, blockID, parentID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	if hasCycle {
		rollbackIfOwned(tx, owned)
		return block, ErrParentCycle
	}
	_, err = tx.Exec(`
		UPDATE blocks SET
			parent_id = ?,
			title = ?, fields = ?,
			updated_by = (SELECT id FROM users WHERE public_id = ?),
			updated_at = ?,
			completed_at = ?,
			completed_by = (SELECT id FROM users WHERE public_id = ?)
		WHERE id = ?`,
		parentID,
		block.Title, block.Fields,
		block.UpdatedBy, nowUnix,
		block.CompletedAt, block.CompletedBy,
		blockID,
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	err = s.insertBlockHistory(tx, blockID, nowUnix)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return block, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return block, err
	}
	return s.GetBlock(block.BoardID, block.ID)
}

func (s *Store) PatchBlocks(ctx context.Context, boardID string, patches []models.BlockPatch, updatedBy string) error {
	var err error
	var nowUnix int64
	var tx txExecutor
	var owned bool
	var i int
	var p models.BlockPatch
	var blockID int64
	var newParentID int64
	var hasCycle bool
	var row *sql.Row

	if len(patches) == 0 {
		return nil
	}
	nowUnix = time.Now().UTC().Unix()

	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return err
	}
	for i = 0; i < len(patches); i++ {
		p = patches[i]
		// Verify each block belongs to the specified board and is not deleted.
		row = tx.QueryRow(`
			SELECT bl.id FROM blocks bl
			JOIN boards b ON b.id = bl.board_id
			WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
			p.ID, boardID)
		err = row.Scan(&blockID)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return err
		}
		if p.ParentID != nil {
			newParentID, err = resolveParentBlockID(tx, boardID, *p.ParentID)
			if err == sql.ErrNoRows {
				rollbackIfOwned(tx, owned)
				return ErrParentNotInBoard
			}
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
			hasCycle, err = hasCycleAfterReparent(tx, blockID, newParentID)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
			if hasCycle {
				rollbackIfOwned(tx, owned)
				return ErrParentCycle
			}
			_, err = tx.Exec(`UPDATE blocks SET parent_id = ? WHERE id = ?`, newParentID, blockID)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
		}
		if p.Title != nil {
			_, err = tx.Exec(`UPDATE blocks SET title = ? WHERE id = ?`, *p.Title, blockID)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
		}
		if p.Fields != nil {
			_, err = tx.Exec(`UPDATE blocks SET fields = ? WHERE id = ?`, *p.Fields, blockID)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
		}
		if p.CompletedAt != nil {
			if *p.CompletedAt > 0 {
				_, err = tx.Exec(`UPDATE blocks SET completed_at = ?, completed_by = (SELECT id FROM users WHERE public_id = ?) WHERE id = ?`, *p.CompletedAt, updatedBy, blockID)
			} else {
				_, err = tx.Exec(`UPDATE blocks SET completed_at = 0, completed_by = NULL WHERE id = ?`, blockID)
			}
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
		}
		if p.DeleteAt != nil {
			_, err = tx.Exec(`UPDATE blocks SET delete_at = ? WHERE id = ?`, *p.DeleteAt, blockID)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return err
			}
		}
		_, err = tx.Exec(`
			UPDATE blocks SET
				updated_by = (SELECT id FROM users WHERE public_id = ?),
				updated_at = ?
			WHERE id = ?`,
			updatedBy, nowUnix, blockID)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return err
		}
		err = s.insertBlockHistory(tx, blockID, nowUnix)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return err
		}
	}
	return commitIfOwned(tx, owned)
}

func (s *Store) DeleteBlock(ctx context.Context, boardID string, id string, updatedBy string) error {
	var err error
	var nowUnix int64
	var tx txExecutor
	var owned bool
	var blockID int64
	var row *sql.Row
	var descendantRows *sql.Rows
	var affectedIDs []int64
	var dID int64

	nowUnix = time.Now().UTC().Unix()
	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return err
	}
	// Verify block belongs to the specified board and is not already deleted.
	row = tx.QueryRow(`
		SELECT bl.id FROM blocks bl
		JOIN boards b ON b.id = bl.board_id
		WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
		id, boardID)
	err = row.Scan(&blockID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	// Collect the block and all non-deleted descendants so we can record history for each.
	descendantRows, err = tx.Query(`
		WITH RECURSIVE descendants(id) AS (
			SELECT ? AS id
			UNION ALL
			SELECT b.id FROM blocks b
			JOIN descendants d ON b.parent_id = d.id
			WHERE b.delete_at = 0
		)
		SELECT id FROM descendants`, blockID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	for descendantRows.Next() {
		err = descendantRows.Scan(&dID)
		if err != nil {
			descendantRows.Close()
			rollbackIfOwned(tx, owned)
			return err
		}
		affectedIDs = append(affectedIDs, dID)
	}
	descendantRows.Close()
	err = descendantRows.Err()
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	// Soft-delete the block and all its descendants in one statement.
	_, err = tx.Exec(`
		WITH RECURSIVE descendants(id) AS (
			SELECT ? AS id
			UNION ALL
			SELECT b.id FROM blocks b
			JOIN descendants d ON b.parent_id = d.id
			WHERE b.delete_at = 0
		)
		UPDATE blocks SET
			delete_at = ?,
			updated_by = (SELECT id FROM users WHERE public_id = ?),
			updated_at = ?
		WHERE id IN (SELECT id FROM descendants) AND delete_at = 0`,
		blockID, nowUnix, updatedBy, nowUnix)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	for _, dID = range affectedIDs {
		err = s.insertBlockHistory(tx, dID, nowUnix)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return err
		}
	}
	return commitIfOwned(tx, owned)
}

// ----- board members -----

func (s *Store) GetBoardMemberRole(boardID string, userID string) (string, error) {
	var role string
	var row *sql.Row
	row = s.QueryRow(`
		SELECT bm.role FROM board_members bm
		JOIN boards b ON b.id = bm.board_id
		JOIN users u ON u.id = bm.user_id
		WHERE b.public_id = ? AND u.public_id = ? AND b.delete_at = 0`,
		boardID, userID)
	return role, row.Scan(&role)
}

func (s *Store) ListBoardMembers(boardID string) ([]models.BoardMember, error) {
	var rows *sql.Rows
	var err error
	var members []models.BoardMember
	var m models.BoardMember

	rows, err = s.Query(`
		SELECT b.public_id, u.public_id, u.username, bm.role, bm.created_at
		FROM board_members bm
		JOIN boards b ON b.id = bm.board_id
		JOIN users u ON u.id = bm.user_id
		WHERE b.public_id = ? AND b.delete_at = 0
		ORDER BY bm.created_at ASC`, boardID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		m = models.BoardMember{}
		err = rows.Scan(&m.BoardID, &m.UserID, &m.Username, &m.Role, &m.CreatedAt)
		if err != nil {
			return nil, err
		}
		members = append(members, m)
	}
	return members, rows.Err()
}

func (s *Store) ListBoardAssignableUsers(boardID string) ([]models.BoardAssignableUser, error) {
	var rows *sql.Rows
	var err error
	var users []models.BoardAssignableUser
	var u models.BoardAssignableUser

	rows, err = s.Query(`
		SELECT DISTINCT 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 users u
		WHERE u.disabled = 0
		  AND EXISTS (SELECT 1 FROM boards b WHERE b.public_id = ? AND b.delete_at = 0)
		  AND (
			u.is_admin = 1
			OR EXISTS (
				SELECT 1 FROM board_members bm
				JOIN boards b ON b.id = bm.board_id
				WHERE b.public_id = ? AND b.delete_at = 0 AND bm.user_id = u.id
			)
			OR EXISTS (
				SELECT 1 FROM project_role_bindings prb
				JOIN boards b ON b.project_id = prb.project_id
				WHERE b.public_id = ? AND b.delete_at = 0
				  AND prb.subject_type = 'user'
				  AND prb.subject_id = u.id
			)
			OR EXISTS (
				SELECT 1 FROM project_role_bindings prb
				JOIN boards b ON b.project_id = prb.project_id
				JOIN user_groups ug ON ug.id = prb.subject_id
				WHERE b.public_id = ? AND b.delete_at = 0
				  AND prb.subject_type = 'group'
				  AND ug.disabled = 0
				  AND (
					ug.scope = 'all_users'
					OR EXISTS (
						SELECT 1 FROM user_group_members ugm
						WHERE ugm.group_id = ug.id AND ugm.user_id = u.id
					)
				  )
			)
		  )
		ORDER BY COALESCE(NULLIF(u.display_name, ''), u.username), u.username`, boardID, boardID, boardID, boardID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		u = models.BoardAssignableUser{}
		err = rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.AvatarURL)
		if err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

func (s *Store) UpsertBoardMember(ctx context.Context, member models.BoardMember) error {
	var err error
	var nowUnix int64
	var boardInternalID int64
	var userInternalID int64
	var tx txExecutor
	var owned bool

	nowUnix = time.Now().UTC().Unix()
	if member.CreatedAt == 0 {
		member.CreatedAt = nowUnix
	}
	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil { return err }
	err = tx.QueryRow(`SELECT id FROM boards WHERE public_id = ? AND delete_at = 0`, member.BoardID).Scan(&boardInternalID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return ErrBoardNotFound
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	err = tx.QueryRow(`SELECT id FROM users WHERE public_id = ?`, member.UserID).Scan(&userInternalID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return ErrUserNotFound
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	_, err = tx.Exec(`
		INSERT INTO board_members (board_id, user_id, role, created_at)
		VALUES (?, ?, ?, ?)
		ON CONFLICT(board_id, user_id) DO UPDATE SET role = excluded.role`,
		boardInternalID, userInternalID, member.Role, member.CreatedAt)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	return commitIfOwned(tx, owned)
}

func (s *Store) DeleteBoardMember(boardID string, userID string) error {
	var err error
	_, err = s.Exec(`
		DELETE FROM board_members
		WHERE board_id = (SELECT id FROM boards WHERE public_id = ? AND delete_at = 0)
		  AND user_id  = (SELECT id FROM users  WHERE public_id = ?)`,
		boardID, userID)
	return err
}