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

-- Add isDeleted to feed
ALTER TABLE feed ADD COLUMN isDeleted INTEGER AS Boolean NOT NULL DEFAULT 0;

-- Drop bookmark table and its triggers
DROP TABLE IF EXISTS bookmark;
DROP TRIGGER IF EXISTS post_bookmarked;
DROP TRIGGER IF EXISTS post_unbookmarked;
DROP TRIGGER IF EXISTS post_content_update;

-- Drop indexes
DROP INDEX post_date_index;
DROP INDEX post_createdAt_index;
DROP INDEX post_is_hidden_index;
DROP INDEX post_date_read_hidden_index;

-- Drop post table triggers
DROP TRIGGER hide_post_if_blocked_word_is_present_AFTER_INSERT;
DROP TRIGGER hide_post_if_blocked_word_is_present_BEFORE_UPDATE;
DROP TRIGGER unhide_post_if_no_blocked_words_present_and_post_is_hidden_BEFORE_UPDATE;
DROP TRIGGER post_search_fts_BEFORE_DELETE;
DROP TRIGGER post_search_fts_AFTER_UPDATE;
DROP TRIGGER post_search_fts_AFTER_INSERT;
DROP TRIGGER hide_posts_with_blocked_words_AFTER_INSERT;
DROP TRIGGER unhide_posts_with_blocked_words_AFTER_DELETE;

-- Recreate post table without ON DELETE CASCADE
CREATE TABLE post_new (
  id TEXT NOT NULL,
  sourceId TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  imageUrl TEXT,
  postDate INTEGER AS Instant NOT NULL,
  createdAt INTEGER AS Instant NOT NULL,
  updatedAt INTEGER AS Instant NOT NULL,
  syncedAt INTEGER AS Instant NOT NULL,
  link TEXT NOT NULL,
  commentsLink TEXT DEFAULT NULL,
  bookmarked INTEGER AS Boolean NOT NULL DEFAULT 0,
  read INTEGER AS Boolean NOT NULL DEFAULT 0,
  isHidden INTEGER AS Boolean NOT NULL DEFAULT 0,
  PRIMARY KEY (id, sourceId),
  FOREIGN KEY(sourceId) REFERENCES feed(id)
);

INSERT INTO post_new SELECT * FROM post;

DROP TABLE post;

ALTER TABLE post_new RENAME TO post;

-- Recreate indexes
CREATE INDEX post_date_index ON post (postDate);
CREATE INDEX post_createdAt_index ON post (createdAt);
CREATE INDEX post_is_hidden_index ON post(isHidden);
CREATE INDEX post_date_read_hidden_index ON post(postDate, read, isHidden);

-- Recreate triggers
CREATE TRIGGER hide_post_if_blocked_word_is_present_AFTER_INSERT
AFTER INSERT ON post
FOR EACH ROW
WHEN (
  SELECT 1
  FROM blockedWord
  WHERE
    new.title LIKE '%' || blockedWord.content || '%' OR
    new.description LIKE '%' || blockedWord.content || '%'
) IS NOT NULL
BEGIN
  UPDATE post SET isHidden = 1 WHERE id = new.id;
END;

CREATE TRIGGER hide_post_if_blocked_word_is_present_BEFORE_UPDATE
BEFORE UPDATE OF title, description ON post
FOR EACH ROW
WHEN (
  SELECT 1
  FROM blockedWord
  WHERE
    (new.title LIKE '%' || blockedWord.content || '%' OR
    new.description LIKE '%' || blockedWord.content || '%') AND
    new.isHidden == 0
) IS NOT NULL
BEGIN
  UPDATE post SET isHidden = 1 WHERE id = new.id;
END;

CREATE TRIGGER unhide_post_if_no_blocked_words_present_and_post_is_hidden_BEFORE_UPDATE
UPDATE OF title, description ON post
FOR EACH ROW
WHEN (
  SELECT 1
  FROM blockedWord
  WHERE
    (new.title LIKE '%' || blockedWord.content || '%' OR
    new.description LIKE '%' || blockedWord.content || '%')
) IS NULL AND old.isHidden == 1
BEGIN
  UPDATE post SET isHidden = 0 WHERE id = new.id;
END;

CREATE TRIGGER post_search_fts_BEFORE_DELETE
BEFORE DELETE ON post
BEGIN DELETE FROM post_search WHERE id = old.id;
END;

CREATE TRIGGER post_search_fts_AFTER_UPDATE
AFTER UPDATE ON post
BEGIN UPDATE OR IGNORE post_search SET title = new.title, description = new.description WHERE id = new.id;
END;

CREATE TRIGGER post_search_fts_AFTER_INSERT
AFTER INSERT ON post
BEGIN INSERT OR IGNORE INTO post_search(id, title, description, link) VALUES (new.id, new.title, new.description, new.link);
END;

CREATE TRIGGER delete_post_content_AFTER_DELETE
AFTER DELETE ON post
BEGIN
  DELETE FROM postContent WHERE id = old.id;
END;

CREATE TRIGGER hide_posts_with_blocked_words_AFTER_INSERT
AFTER INSERT ON blockedWord
BEGIN
  UPDATE OR IGNORE post
  SET isHidden = 1
  WHERE
    (title LIKE '%' || new.content || '%' OR
    description LIKE '%' || new.content || '%') AND
    isHidden = 0;
END;

CREATE TRIGGER unhide_posts_with_blocked_words_AFTER_DELETE
AFTER DELETE ON blockedWord
BEGIN
  UPDATE OR IGNORE post
  SET isHidden = 0
  WHERE EXISTS (
    SELECT 1
    FROM blockedWord
    WHERE (post.title LIKE '%' || blockedWord.content || '%' OR
           post.description LIKE '%' || blockedWord.content || '%')
  )
  AND isHidden = 1;
END;
