package message

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

func (store PinStore) UpsertActiveTopNotice(ctx context.Context, record ScopeMessageTopNoticeRecord) (ScopeMessageTopNoticeMutationResult, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeMutationResult{}, err
	}
	record = normalizeScopeMessageTopNoticeRecord(record)
	if record.ID == "" || record.WorkspaceID == "" || record.ScopeID == "" || record.MessageID == "" || record.SetByMemberID == "" {
		return ScopeMessageTopNoticeMutationResult{}, invalidArgument("message top notice id, workspace, scope, message, and setter are required")
	}
	existing, found, err := store.LoadActiveTopNoticeForScope(ctx, record.WorkspaceID, record.ScopeID)
	if err != nil {
		return ScopeMessageTopNoticeMutationResult{}, err
	}
	if found {
		if strings.TrimSpace(existing.MessageID) == record.MessageID {
			return ScopeMessageTopNoticeMutationResult{Changed: false, TopNotice: existing}, nil
		}
		if _, err := store.execer.Exec(ctx, `
DELETE FROM scope_message_top_notices
WHERE workspace_id = $1
  AND scope_id = $2
`, record.WorkspaceID, record.ScopeID); err != nil {
			return ScopeMessageTopNoticeMutationResult{}, fmt.Errorf("replace scope message top notice: %w", err)
		}
	}
	if _, err := store.execer.Exec(ctx, `
INSERT INTO scope_message_top_notices (id, workspace_id, scope_id, message_id, set_by_member_id, set_at, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
`, record.ID, record.WorkspaceID, record.ScopeID, record.MessageID, record.SetByMemberID, record.SetAt, record.CreatedAt, record.UpdatedAt); err != nil {
		if uniqueViolationMatches(err, "scope_message_top_notices_scope_key") || uniqueViolationMatches(err, "scope_message_top_notices_scope_message_key") {
			existing, found, loadErr := store.LoadActiveTopNoticeForScope(ctx, record.WorkspaceID, record.ScopeID)
			if loadErr != nil {
				return ScopeMessageTopNoticeMutationResult{}, loadErr
			}
			if found {
				return ScopeMessageTopNoticeMutationResult{Changed: false, TopNotice: existing}, nil
			}
		}
		return ScopeMessageTopNoticeMutationResult{}, fmt.Errorf("insert scope message top notice: %w", err)
	}
	return ScopeMessageTopNoticeMutationResult{Changed: true, TopNotice: record}, nil
}

func (store PinStore) LoadActiveTopNotice(ctx context.Context, workspaceID, scopeID, messageID string) (ScopeMessageTopNoticeRecord, bool, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeRecord{}, false, err
	}
	return store.loadActiveTopNoticeByWhere(ctx, `
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID))
}

func (store PinStore) LoadActiveTopNoticeForScope(ctx context.Context, workspaceID, scopeID string) (ScopeMessageTopNoticeRecord, bool, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeRecord{}, false, err
	}
	return store.loadActiveTopNoticeByWhere(ctx, `
WHERE workspace_id = $1
  AND scope_id = $2
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID))
}

func (store PinStore) DeleteActiveTopNotice(ctx context.Context, workspaceID, scopeID, messageID string) (ScopeMessageTopNoticeMutationResult, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeMutationResult{}, err
	}
	existing, found, err := store.LoadActiveTopNotice(ctx, workspaceID, scopeID, messageID)
	if err != nil || !found {
		return ScopeMessageTopNoticeMutationResult{Changed: false}, err
	}
	result, err := store.execer.Exec(ctx, `
DELETE FROM scope_message_top_notices
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID))
	if err != nil {
		return ScopeMessageTopNoticeMutationResult{}, fmt.Errorf("delete scope message top notice: %w", err)
	}
	return ScopeMessageTopNoticeMutationResult{Changed: result.RowsAffected() > 0, TopNotice: existing}, nil
}

func (store PinStore) GetActiveTopNotice(ctx context.Context, workspaceID, scopeID, viewerMemberID string) (ScopeMessageTopNoticeRow, bool, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeRow{}, false, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	scopeID = strings.TrimSpace(scopeID)
	viewerMemberID = strings.TrimSpace(viewerMemberID)
	if workspaceID == "" || scopeID == "" || viewerMemberID == "" {
		return ScopeMessageTopNoticeRow{}, false, invalidArgument("message top notice workspace, scope, and viewer are required")
	}
	var row ScopeMessageTopNoticeRow
	var metadata []byte
	var hideID, hideWorkspaceID, hideScopeID, hideTopNoticeID, hideMessageID, hideMemberID sql.NullString
	var editedAt sql.NullTime
	var hideHiddenAt, hideCreatedAt, hideUpdatedAt sql.NullTime
	err := store.execer.QueryRow(ctx, `
SELECT
    p.id, p.workspace_id, p.scope_id, p.message_id, p.set_by_member_id, p.set_at, p.created_at, p.updated_at,
    m.id, m.workspace_id, m.scope_id, m.seq, m.workspace_global_seq, m.author_member_id,
    m.author_type,
    COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id),
    COALESCE(NULLIF(m.idempotency_subject_member_id, ''), m.author_member_id),
    COALESCE(m.client_message_id, ''), COALESCE(m.idempotency_payload_digest, ''),
    m.message_type, m.content, m.metadata, m.created_at, m.updated_at,
    m.edited_at, COALESCE(m.edited_by_member_id, ''), m.edit_revision,
    h.id, h.workspace_id, h.scope_id, h.top_notice_id, h.message_id, h.hidden_by_member_id, h.hidden_at, h.created_at, h.updated_at
FROM scope_message_top_notices p
JOIN scope_messages m
  ON m.id = p.message_id
 AND m.workspace_id = p.workspace_id
 AND m.scope_id = p.scope_id
LEFT JOIN scope_message_top_notice_hides h
  ON h.top_notice_id = p.id
 AND h.workspace_id = p.workspace_id
 AND h.scope_id = p.scope_id
 AND h.hidden_by_member_id = $3
WHERE p.workspace_id = $1
  AND p.scope_id = $2
LIMIT 1
`, workspaceID, scopeID, viewerMemberID).Scan(
		&row.TopNotice.ID,
		&row.TopNotice.WorkspaceID,
		&row.TopNotice.ScopeID,
		&row.TopNotice.MessageID,
		&row.TopNotice.SetByMemberID,
		&row.TopNotice.SetAt,
		&row.TopNotice.CreatedAt,
		&row.TopNotice.UpdatedAt,
		&row.Message.ID,
		&row.Message.WorkspaceID,
		&row.Message.ScopeID,
		&row.Message.Seq,
		&row.Message.WorkspaceGlobalSeq,
		&row.Message.AuthorMemberID,
		&row.Message.AuthorType,
		&row.Message.NotifyPrincipalMemberID,
		&row.Message.IdempotencySubjectMemberID,
		&row.Message.ClientMessageID,
		&row.Message.IdempotencyPayloadDigest,
		&row.Message.MessageType,
		&row.Message.Content,
		&metadata,
		&row.Message.CreatedAt,
		&row.Message.UpdatedAt,
		&editedAt,
		&row.Message.EditedByMemberID,
		&row.Message.EditRevision,
		&hideID,
		&hideWorkspaceID,
		&hideScopeID,
		&hideTopNoticeID,
		&hideMessageID,
		&hideMemberID,
		&hideHiddenAt,
		&hideCreatedAt,
		&hideUpdatedAt,
	)
	if errorsIsNoRows(err) {
		return ScopeMessageTopNoticeRow{}, false, nil
	}
	if err != nil {
		return ScopeMessageTopNoticeRow{}, false, fmt.Errorf("get scope message top notice: %w", err)
	}
	row.Message.Metadata = metadata
	row.Message.EditedAt = nullableTimePtr(editedAt)
	if hideID.Valid {
		row.HasHide = true
		row.Hide = &ScopeMessageTopNoticeHideRecord{
			ID:               hideID.String,
			WorkspaceID:      hideWorkspaceID.String,
			ScopeID:          hideScopeID.String,
			TopNoticeID:      hideTopNoticeID.String,
			MessageID:        hideMessageID.String,
			HiddenByMemberID: hideMemberID.String,
		}
		if hideHiddenAt.Valid {
			row.Hide.HiddenAt = hideHiddenAt.Time
		}
		if hideCreatedAt.Valid {
			row.Hide.CreatedAt = hideCreatedAt.Time
		}
		if hideUpdatedAt.Valid {
			row.Hide.UpdatedAt = hideUpdatedAt.Time
		}
	}
	return row, true, nil
}

func (store PinStore) InsertTopNoticeHide(ctx context.Context, record ScopeMessageTopNoticeHideRecord) (ScopeMessageTopNoticeHideMutationResult, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeHideMutationResult{}, err
	}
	record = normalizeScopeMessageTopNoticeHideRecord(record)
	if record.ID == "" || record.WorkspaceID == "" || record.ScopeID == "" || record.TopNoticeID == "" || record.MessageID == "" || record.HiddenByMemberID == "" {
		return ScopeMessageTopNoticeHideMutationResult{}, invalidArgument("message top notice banner hide id, workspace, scope, top notice, message, and viewer are required")
	}
	if _, err := store.execer.Exec(ctx, `
INSERT INTO scope_message_top_notice_hides (id, workspace_id, scope_id, top_notice_id, message_id, hidden_by_member_id, hidden_at, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
`, record.ID, record.WorkspaceID, record.ScopeID, record.TopNoticeID, record.MessageID, record.HiddenByMemberID, record.HiddenAt, record.CreatedAt, record.UpdatedAt); err != nil {
		if uniqueViolationMatches(err, "scope_message_top_notice_hides_instance_key") {
			existing, found, loadErr := store.LoadTopNoticeHide(ctx, record.WorkspaceID, record.ScopeID, record.MessageID, record.TopNoticeID, record.HiddenByMemberID)
			if loadErr != nil {
				return ScopeMessageTopNoticeHideMutationResult{}, loadErr
			}
			if found {
				return ScopeMessageTopNoticeHideMutationResult{Changed: false, Hide: existing}, nil
			}
		}
		return ScopeMessageTopNoticeHideMutationResult{}, fmt.Errorf("insert scope message top notice banner hide: %w", err)
	}
	return ScopeMessageTopNoticeHideMutationResult{Changed: true, Hide: record}, nil
}

func (store PinStore) LoadTopNoticeHide(ctx context.Context, workspaceID, scopeID, messageID, topNoticeID, hiddenByMemberID string) (ScopeMessageTopNoticeHideRecord, bool, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeHideRecord{}, false, err
	}
	var record ScopeMessageTopNoticeHideRecord
	err := store.execer.QueryRow(ctx, `
SELECT id, workspace_id, scope_id, top_notice_id, message_id, hidden_by_member_id, hidden_at, created_at, updated_at
FROM scope_message_top_notice_hides
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
  AND top_notice_id = $4
  AND hidden_by_member_id = $5
LIMIT 1
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID), strings.TrimSpace(topNoticeID), strings.TrimSpace(hiddenByMemberID)).Scan(
		&record.ID,
		&record.WorkspaceID,
		&record.ScopeID,
		&record.TopNoticeID,
		&record.MessageID,
		&record.HiddenByMemberID,
		&record.HiddenAt,
		&record.CreatedAt,
		&record.UpdatedAt,
	)
	if errorsIsNoRows(err) {
		return ScopeMessageTopNoticeHideRecord{}, false, nil
	}
	if err != nil {
		return ScopeMessageTopNoticeHideRecord{}, false, fmt.Errorf("load scope message top notice banner hide: %w", err)
	}
	return record, true, nil
}

func (store PinStore) DeleteTopNoticeHide(ctx context.Context, workspaceID, scopeID, messageID, topNoticeID, hiddenByMemberID string) (ScopeMessageTopNoticeHideMutationResult, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessageTopNoticeHideMutationResult{}, err
	}
	existing, found, err := store.LoadTopNoticeHide(ctx, workspaceID, scopeID, messageID, topNoticeID, hiddenByMemberID)
	if err != nil || !found {
		return ScopeMessageTopNoticeHideMutationResult{Changed: false}, err
	}
	result, err := store.execer.Exec(ctx, `
DELETE FROM scope_message_top_notice_hides
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
  AND top_notice_id = $4
  AND hidden_by_member_id = $5
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID), strings.TrimSpace(topNoticeID), strings.TrimSpace(hiddenByMemberID))
	if err != nil {
		return ScopeMessageTopNoticeHideMutationResult{}, fmt.Errorf("delete scope message top notice banner hide: %w", err)
	}
	return ScopeMessageTopNoticeHideMutationResult{Changed: result.RowsAffected() > 0, Hide: existing}, nil
}

func (store PinStore) DeleteTopNoticeHidesForViewerScope(ctx context.Context, workspaceID, scopeID, hiddenByMemberID string) (int64, error) {
	if err := store.requireExecer(); err != nil {
		return 0, err
	}
	result, err := store.execer.Exec(ctx, `
DELETE FROM scope_message_top_notice_hides
WHERE workspace_id = $1
  AND scope_id = $2
  AND hidden_by_member_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(hiddenByMemberID))
	if err != nil {
		return 0, fmt.Errorf("delete scope message top notice banner hides: %w", err)
	}
	return result.RowsAffected(), nil
}

func (store PinStore) loadActiveTopNoticeByWhere(ctx context.Context, where string, args ...any) (ScopeMessageTopNoticeRecord, bool, error) {
	var record ScopeMessageTopNoticeRecord
	err := store.execer.QueryRow(ctx, `
SELECT id, workspace_id, scope_id, message_id, set_by_member_id, set_at, created_at, updated_at
FROM scope_message_top_notices
`+where+`
LIMIT 1
`, args...).Scan(
		&record.ID,
		&record.WorkspaceID,
		&record.ScopeID,
		&record.MessageID,
		&record.SetByMemberID,
		&record.SetAt,
		&record.CreatedAt,
		&record.UpdatedAt,
	)
	if errorsIsNoRows(err) {
		return ScopeMessageTopNoticeRecord{}, false, nil
	}
	if err != nil {
		return ScopeMessageTopNoticeRecord{}, false, fmt.Errorf("load scope message top notice: %w", err)
	}
	return record, true, nil
}