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

DROP INDEX feed_link_index;
DROP INDEX post_feed_link_index;

DROP TRIGGER post_bookmarked;
DROP TRIGGER post_unbookmarked;
DROP TRIGGER post_content_update;

DROP TRIGGER post_search_fts_BEFORE_DELETE;
DROP TRIGGER post_search_fts_AFTER_UPDATE;
DROP TRIGGER post_search_fts_AFTER_INSERT;

DROP TRIGGER feed_search_fts_BEFORE_DELETE;
DROP TRIGGER feed_search_fts_AFTER_UPDATE;
DROP TRIGGER feed_search_fts_AFTER_INSERT;

-- Start: Migrate feed table to include id column --
ALTER TABLE feed ADD COLUMN id TEXT NOT NULL DEFAULT '';
UPDATE feed SET id = link;

CREATE TABLE feed_temp(
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  icon TEXT NOT NULL,
  description TEXT NOT NULL,
  link TEXT NOT NULL,
  homepageLink TEXT NOT NULL,
  createdAt INTEGER AS Instant NOT NULL,
  pinnedAt INTEGER AS Instant,
  lastCleanUpAt INTEGER AS Instant,
  alwaysFetchSourceArticle INTEGER AS Boolean NOT NULL DEFAULT 0
);

INSERT INTO feed_temp
SELECT
  id,
  name,
  icon,
  description,
  link,
  homepageLink,
  createdAt,
  pinnedAt,
  lastCleanUpAt,
  alwaysFetchSourceArticle
FROM feed;

DROP TABLE feed;

ALTER TABLE feed_temp RENAME TO feed;

CREATE INDEX feed_link_index ON feed(link);
-- End: Migrate feed table to include id column --

-- Start: Migrate feed search table to include id column --
DROP TABLE feed_search;

CREATE VIRTUAL TABLE IF NOT EXISTS feed_search USING FTS5(
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  tokenize="trigram"
);

INSERT INTO feed_search SELECT id, name, link FROM feed;

CREATE TRIGGER IF NOT EXISTS
feed_search_fts_BEFORE_DELETE
BEFORE DELETE ON feed
BEGIN DELETE FROM feed_search WHERE id = old.id;
END;

CREATE TRIGGER IF NOT EXISTS
feed_search_fts_AFTER_UPDATE
AFTER UPDATE ON feed
BEGIN UPDATE OR IGNORE feed_search SET name = new.name WHERE id = new.id;
END;

CREATE TRIGGER IF NOT EXISTS
feed_search_fts_AFTER_INSERT
AFTER INSERT ON feed
BEGIN INSERT OR IGNORE INTO feed_search(id, name, link) VALUES (new.id, new.name, new.link);
END;
-- End: Migrate feed search table to include id column --

-- Start: Migrate post table to include source id column --
CREATE TABLE post_temp(
  id TEXT NOT NULL PRIMARY KEY,
  sourceId TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  rawContent TEXT,
  imageUrl TEXT,
  date 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,
  FOREIGN KEY(sourceId) REFERENCES feed(id) ON DELETE CASCADE
);

INSERT INTO post_temp
SELECT
  id,
  feedLink,
  title,
  description,
  rawContent,
  imageUrl,
  date,
  link,
  commentsLink,
  bookmarked,
  read
FROM post;

DROP TABLE post;

ALTER TABLE post_temp RENAME TO post;

CREATE INDEX post_source_id_index ON post(sourceId);
-- End: Migrate post table to include source id column --

-- Start: Migrate post search table triggers --
CREATE TRIGGER IF NOT EXISTS
post_search_fts_BEFORE_DELETE
BEFORE DELETE ON post
BEGIN DELETE FROM post_search WHERE id = old.id;
END;

CREATE TRIGGER IF NOT EXISTS
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 IF NOT EXISTS
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;
-- End: Migrate post search table triggers --

-- Start: Migrate bookmark table to include source id column --
CREATE TABLE bookmark_temp(
  id TEXT NOT NULL PRIMARY KEY,
  sourceId TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  imageUrl TEXT,
  date 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,
  feedName TEXT NOT NULL,
  feedIcon TEXT NOT NULL
);

INSERT INTO bookmark_temp
SELECT
  id,
  feedLink,
  title,
  description,
  imageUrl,
  date,
  link,
  commentsLink,
  bookmarked,
  read,
  feedName,
  feedIcon
FROM bookmark;

DROP TABLE bookmark;

ALTER TABLE bookmark_temp RENAME TO bookmark;

CREATE TRIGGER IF NOT EXISTS
post_bookmarked
AFTER UPDATE OF bookmarked ON post WHEN new.bookmarked == 1
BEGIN
  INSERT OR REPLACE INTO bookmark(id, sourceId, title, description, imageUrl, date, link, commentsLink, bookmarked, read, feedName, feedIcon)
  SELECT new.id, new.sourceId, new.title, new.description, new.imageUrl, new.date, new.link, new.commentsLink, new.bookmarked, new.read, feed.name, feed.icon
  FROM feed WHERE feed.id == new.sourceId;
END;

CREATE TRIGGER IF NOT EXISTS
post_unbookmarked
AFTER UPDATE OF bookmarked ON post WHEN new.bookmarked == 0
BEGIN DELETE FROM bookmark WHERE id = new.id;
END;

CREATE TRIGGER IF NOT EXISTS
post_content_update
AFTER UPDATE OF title, description, imageUrl, date, read ON post WHEN new.bookmarked == 1
BEGIN
  UPDATE OR IGNORE bookmark SET title = new.title, description = new.description, imageUrl = new.imageUrl, date = new.date, commentsLink = new.commentsLink, read = new.read
  WHERE id = new.id;
END;
-- End: Migrate bookmark table to include id column --
