import kotlin.Boolean;
import kotlin.time.Instant;

CREATE TABLE blockedWord (
  id TEXT NOT NULL PRIMARY KEY,
  content TEXT NOT NULL,
  isDeleted INTEGER AS Boolean NOT NULL DEFAULT 0,
  updatedAt INTEGER AS Instant NOT NULL DEFAULT 0
);

CREATE INDEX blocked_word_value_index ON blockedWord(content);
CREATE INDEX blocked_word_isDeleted_index ON blockedWord(isDeleted);


insert:
INSERT INTO blockedWord(id, content, updatedAt, isDeleted)
VALUES (?, ?, ?, 0)
ON CONFLICT(id) DO
UPDATE SET isDeleted = 0, updatedAt = excluded.updatedAt;

remove:
UPDATE blockedWord SET isDeleted = 1, updatedAt = :updatedAt WHERE id = :id;

words:
SELECT * FROM blockedWord WHERE isDeleted = 0 ORDER BY rowid DESC;

allBlockedWords:
SELECT * FROM blockedWord;

deleteAll:
DELETE FROM blockedWord;

upsertSyncBlockedWord:
INSERT INTO blockedWord(id, content, isDeleted, updatedAt)
VALUES (:id, :content, :isDeleted, :updatedAt)
ON CONFLICT(id) DO
UPDATE SET
    content = excluded.content,
    isDeleted = excluded.isDeleted,
    updatedAt = excluded.updatedAt
WHERE excluded.updatedAt > blockedWord.updatedAt;
