package db

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

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

var ErrFieldValueNotFound = errors.New("field value not found")
var ErrFieldValueInUse = errors.New("field value is in use by cards")

type defaultBoardFieldValue struct {
	Field string
	Value string
	Label string
	Color string
}

var defaultBoardFieldValues = []defaultBoardFieldValue{
	{Field: "status", Value: "todo", Label: "To Do", Color: "#1976d2"},
	{Field: "status", Value: "in_progress", Label: "In Progress", Color: "#f59e0b"},
	{Field: "status", Value: "done", Label: "Done", Color: "#16a34a"},
	{Field: "type", Value: "task", Label: "Task", Color: "#1976d2"},
	{Field: "type", Value: "bug", Label: "Bug", Color: "#d32f2f"},
	{Field: "type", Value: "story", Label: "Story", Color: "#388e3c"},
	{Field: "type", Value: "epic", Label: "Epic", Color: "#7b1fa2"},
	{Field: "priority", Value: "low", Label: "Low", Color: "#4caf50"},
	{Field: "priority", Value: "medium", Label: "Medium", Color: "#2196f3"},
	{Field: "priority", Value: "high", Label: "High", Color: "#ff9800"},
	{Field: "priority", Value: "urgent", Label: "Urgent", Color: "#f44336"},
}

var fieldValueCols = `
	fv.public_id, brd.public_id,
	fv.field, fv.value, fv.label, fv.color, fv.display_order,
	fv.start_date, fv.end_date,
	fv.created_at, fv.updated_at`

var fieldValueFrom = `
	FROM board_field_values fv
	JOIN boards brd ON brd.id = fv.board_id`

func scanFieldValue(row interface{ Scan(...any) error }, fv *models.BoardFieldValue) error {
	return row.Scan(
		&fv.ID, &fv.BoardID,
		&fv.Field, &fv.Value, &fv.Label, &fv.Color, &fv.DisplayOrder,
		&fv.StartDate, &fv.EndDate,
		&fv.CreatedAt, &fv.UpdatedAt,
	)
}

func (s *Store) SeedDefaultBoardFieldValues(ctx context.Context, boardID string) error {
	var i int
	var item defaultBoardFieldValue
	var fv models.BoardFieldValue
	var err error

	for i = 0; i < len(defaultBoardFieldValues); i++ {
		item = defaultBoardFieldValues[i]
		fv = models.BoardFieldValue{
			Field: item.Field,
			Value: item.Value,
			Label: item.Label,
			Color: item.Color,
		}
		_, err = s.CreateBoardFieldValue(ctx, boardID, fv)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *Store) ListBoardFieldValues(boardID, field string) ([]models.BoardFieldValue, error) {
	return listBoardFieldValues(s, boardID, field)
}

func listBoardFieldValues(execer sqlExecutor, boardID string, field string) ([]models.BoardFieldValue, error) {
	var rows *sql.Rows
	var err error
	var result []models.BoardFieldValue
	var fv models.BoardFieldValue

	rows, err = execer.Query(
		`SELECT`+fieldValueCols+fieldValueFrom+`
		WHERE brd.public_id = ? AND fv.field = ?
		ORDER BY fv.display_order ASC, fv.created_at ASC`, boardID, field)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		fv = models.BoardFieldValue{}
		err = scanFieldValue(rows, &fv)
		if err != nil {
			return nil, err
		}
		result = append(result, fv)
	}
	return result, rows.Err()
}

func (s *Store) CreateBoardFieldValue(ctx context.Context, boardID string, fv models.BoardFieldValue) (models.BoardFieldValue, error) {
	var id string
	var err error
	var now int64
	var internalBoardID int64
	var maxOrder int64
	var result models.BoardFieldValue
	var tx txExecutor
	var owned bool

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

	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return result, err
	}
	err = tx.QueryRow(`SELECT id FROM boards WHERE public_id = ? AND delete_at = 0`, boardID).Scan(&internalBoardID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return result, ErrBoardNotFound
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return result, err
	}

	err = tx.QueryRow(
		`SELECT COALESCE(MAX(display_order), -1) FROM board_field_values WHERE board_id = ? AND field = ?`,
		internalBoardID, fv.Field).Scan(&maxOrder)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return result, err
	}

	_, err = tx.Exec(`
		INSERT INTO board_field_values
		  (public_id, board_id, field, value, label, color, display_order, start_date, end_date, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		id, internalBoardID, fv.Field, fv.Value, fv.Label, fv.Color,
		maxOrder+1, fv.StartDate, fv.EndDate, now, now)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return result, err
	}

	err = scanFieldValue(
		tx.QueryRow(`SELECT`+fieldValueCols+fieldValueFrom+` WHERE fv.public_id = ?`, id),
		&result)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return result, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return result, err
	}
	return result, nil
}

func (s *Store) UpdateBoardFieldValue(boardID, valueID string, fv models.BoardFieldValue) (models.BoardFieldValue, error) {
	var now int64
	var res sql.Result
	var n int64
	var err error
	var updated models.BoardFieldValue

	now = time.Now().Unix()
	res, err = s.Exec(`
		UPDATE board_field_values
		SET label = ?, color = ?, start_date = ?, end_date = ?, updated_at = ?
		WHERE public_id = ?
		  AND board_id = (SELECT id FROM boards WHERE public_id = ? AND delete_at = 0)`,
		fv.Label, fv.Color, fv.StartDate, fv.EndDate, now,
		valueID, boardID)
	if err != nil {
		return updated, err
	}
	n, err = res.RowsAffected()
	if err != nil {
		return updated, err
	}
	if n == 0 {
		return updated, ErrFieldValueNotFound
	}

	err = scanFieldValue(
		s.QueryRow(`SELECT`+fieldValueCols+fieldValueFrom+` WHERE fv.public_id = ?`, valueID),
		&updated)
	return updated, err
}

// blockPropsCol maps a field name to the corresponding block_properties column.
// Returns ("", false) for unknown fields.
func blockPropsCol(field string) (string, bool) {
	var cols = map[string]string{
		"status":   "status",
		"type":     "card_type",
		"priority": "priority",
		"sprint":   "sprint",
	}
	var col, ok = cols[field]
	return col, ok
}

func countBoardFieldValueRefs(execer sqlExecutor, boardID string, field string, value string) (int, error) {
	var col string
	var ok bool
	var count int
	var err error

	col, ok = blockPropsCol(field)
	if !ok {
		return 0, nil
	}
	err = execer.QueryRow(
		fmt.Sprintf(`
		SELECT COUNT(*) FROM block_properties bp
		JOIN blocks bl ON bl.id = bp.block_id
		JOIN boards brd ON brd.id = bl.board_id
		WHERE brd.public_id = ? AND bl.delete_at = 0 AND bp.%s = ?`, col),
		boardID, value).Scan(&count)
	return count, err
}

func (s *Store) CountBoardFieldValueRefs(boardID, field, value string) (int, error) {
	return countBoardFieldValueRefs(s, boardID, field, value)
}

func (s *Store) ReorderBoardFieldValues(ctx context.Context, boardID, field string, ids []string) ([]models.BoardFieldValue, error) {
	var internalBoardID int64
	var err error
	var now int64
	var tx txExecutor
	var owned bool
	var i int
	var id string
	var result []models.BoardFieldValue
	var execResult sql.Result
	var n int64

	now = time.Now().Unix()
	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return nil, err
	}
	err = tx.QueryRow(`SELECT id FROM boards WHERE public_id = ? AND delete_at = 0`, boardID).Scan(&internalBoardID)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return nil, ErrBoardNotFound
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return nil, err
	}

	for i = 0; i < len(ids); i++ {
		id = ids[i]
		execResult, err = tx.Exec(`
			UPDATE board_field_values SET display_order = ?, updated_at = ?
			WHERE public_id = ? AND board_id = ? AND field = ?`,
			i, now, id, internalBoardID, field)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return nil, err
		}
		n, err = execResult.RowsAffected()
		if err != nil {
			rollbackIfOwned(tx, owned)
			return nil, err
		}
		if n == 0 {
			rollbackIfOwned(tx, owned)
			return nil, ErrFieldValueNotFound
		}
	}

	result, err = listBoardFieldValues(tx, boardID, field)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return nil, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return nil, err
	}
	return result, nil
}

func (s *Store) DeleteBoardFieldValue(ctx context.Context, boardID, valueID string) error {
	var err error
	var res sql.Result
	var n int64
	var value string
	var field string
	var count int
	var tx txExecutor
	var owned bool

	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil {
		return err
	}
	err = tx.QueryRow(`
		SELECT fv.value, fv.field FROM board_field_values fv
		JOIN boards brd ON brd.id = fv.board_id
		WHERE fv.public_id = ? AND brd.public_id = ?`, valueID, boardID).Scan(&value, &field)
	if err == sql.ErrNoRows {
		rollbackIfOwned(tx, owned)
		return ErrFieldValueNotFound
	}
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}

	count, err = countBoardFieldValueRefs(tx, boardID, field, value)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	if count > 0 {
		rollbackIfOwned(tx, owned)
		return fmt.Errorf("%w: %d card(s)", ErrFieldValueInUse, count)
	}

	res, err = tx.Exec(`
		DELETE FROM board_field_values
		WHERE public_id = ?
		  AND board_id = (SELECT id FROM boards WHERE public_id = ? AND delete_at = 0)`,
		valueID, boardID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	n, err = res.RowsAffected()
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	if n == 0 {
		rollbackIfOwned(tx, owned)
		return ErrFieldValueNotFound
	}
	return commitIfOwned(tx, owned)
}