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

CREATE TABLE IF NOT EXISTS feedGroup(
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  createdAt INTEGER AS Instant NOT NULL,
  updatedAt INTEGER AS Instant NOT NULL,
  pinnedAt INTEGER AS Instant,
  pinnedPosition REAL NOT NULL DEFAULT 0.0,
  isDeleted INTEGER AS Boolean NOT NULL DEFAULT 0,
  remoteId TEXT
);

CREATE INDEX feed_group_pinned_at ON feedGroup(pinnedAt);
CREATE INDEX feed_group_pinned_position ON feedGroup(pinnedPosition);
CREATE INDEX feed_group_pinned_name ON feedGroup(pinnedAt DESC, name);
CREATE INDEX feed_group_remoteId_index ON feedGroup(remoteId) WHERE remoteId IS NOT NULL;
CREATE INDEX feed_group_isDeleted_index ON feedGroup(isDeleted);

count:
SELECT COUNT(*) FROM feedGroup WHERE isDeleted = 0;

groups:
WITH ranked_feeds AS (
  SELECT
    gf.feedGroupId,
    gf.feedId,
    f.homepageLink,
    f.icon,
    CASE
      WHEN f.showFeedFavIcon IS NULL THEN 'null'
      WHEN f.showFeedFavIcon = 1 THEN 'true'
      ELSE 'false'
    END AS showFeedFavIconStr,
    ROW_NUMBER() OVER (PARTITION BY gf.feedGroupId ORDER BY gf.rowid) AS rn
  FROM feedGroupFeed gf
  JOIN feed f ON gf.feedId = f.id
  WHERE f.isDeleted = 0
),
feed_group_metadata AS (
  SELECT
    feedGroupId,
    GROUP_CONCAT(feedId, '::') AS feedIds,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN homepageLink END, '::'), '') AS feedHomepageLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN icon END, '::'), '') AS feedIconLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN showFeedFavIconStr END, '::'), '') AS feedShowFavIconSettings
  FROM ranked_feeds
  GROUP BY feedGroupId
)
SELECT
  fg.id,
  fg.name,
  fgm.feedIds,
  COALESCE(fgm.feedHomepageLinks, '') AS feedHomepageLinks,
  COALESCE(fgm.feedIconLinks, '') AS feedIconLinks,
  COALESCE(fgm.feedShowFavIconSettings, '') AS feedShowFavIconSettings,
  fg.createdAt,
  fg.updatedAt,
  fg.pinnedAt,
  fg.pinnedPosition,
  fg.remoteId
FROM feedGroup fg
LEFT JOIN feed_group_metadata fgm ON fg.id = fgm.feedGroupId
WHERE fg.isDeleted = 0
LIMIT :limit OFFSET :offset;

groupsBlocking:
WITH ranked_feeds AS (
  SELECT
    gf.feedGroupId,
    gf.feedId,
    f.homepageLink,
    f.icon,
    CASE
      WHEN f.showFeedFavIcon IS NULL THEN 'null'
      WHEN f.showFeedFavIcon = 1 THEN 'true'
      ELSE 'false'
    END AS showFeedFavIconStr,
    ROW_NUMBER() OVER (PARTITION BY gf.feedGroupId ORDER BY gf.rowid) AS rn
  FROM feedGroupFeed gf
  JOIN feed f ON gf.feedId = f.id
  WHERE f.isDeleted = 0
),
feed_group_metadata AS (
  SELECT
    feedGroupId,
    GROUP_CONCAT(feedId, '::') AS feedIds,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN homepageLink END, '::'), '') AS feedHomepageLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN icon END, '::'), '') AS feedIconLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN showFeedFavIconStr END, '::'), '') AS feedShowFavIconSettings
  FROM ranked_feeds
  GROUP BY feedGroupId
)
SELECT
  fg.id,
  fg.name,
  fgm.feedIds,
  COALESCE(fgm.feedHomepageLinks, '') AS feedHomepageLinks,
  COALESCE(fgm.feedIconLinks, '') AS feedIconLinks,
  COALESCE(fgm.feedShowFavIconSettings, '') AS feedShowFavIconSettings,
  fg.createdAt,
  fg.updatedAt,
  fg.pinnedAt,
  fg.pinnedPosition,
  fg.remoteId
FROM feedGroup fg
LEFT JOIN feed_group_metadata fgm ON fg.id = fgm.feedGroupId
WHERE fg.isDeleted = 0;

createGroup:
INSERT INTO feedGroup(id, name, createdAt, updatedAt)
VALUES (:id, :name, :createdAt, :updatedAt)
ON CONFLICT(id) DO UPDATE SET
  name = excluded.name,
  updatedAt = excluded.updatedAt,
  isDeleted = 0;

updateGroupName:
UPDATE feedGroup SET name = :name, updatedAt = :updatedAt WHERE id = :id;

updateUpdatedAt:
UPDATE feedGroup SET updatedAt = :updatedAt WHERE id = :id;

markAsDeleted:
UPDATE feedGroup SET isDeleted = 1, updatedAt = :updatedAt WHERE id = :id;

remove:
DELETE FROM feedGroup WHERE id = :id;

updatePinnedAt:
UPDATE feedGroup SET pinnedAt = :pinnedAt, pinnedPosition = 0.0, updatedAt = :updatedAt WHERE id = :id;

group:
WITH ranked_feeds AS (
  SELECT
    gf.feedGroupId,
    gf.feedId,
    f.homepageLink,
    f.icon,
    CASE
      WHEN f.showFeedFavIcon IS NULL THEN 'null'
      WHEN f.showFeedFavIcon = 1 THEN 'true'
      ELSE 'false'
    END AS showFeedFavIconStr,
    ROW_NUMBER() OVER (PARTITION BY gf.feedGroupId ORDER BY gf.rowid) AS rn
  FROM feedGroupFeed gf
  JOIN feed f ON gf.feedId = f.id
  WHERE f.isDeleted = 0
),
feed_group_metadata AS (
  SELECT
    feedGroupId,
    GROUP_CONCAT(feedId, '::') AS feedIds,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN homepageLink END, '::'), '') AS feedHomepageLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN icon END, '::'), '') AS feedIconLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN showFeedFavIconStr END, '::'), '') AS feedShowFavIconSettings
  FROM ranked_feeds
  GROUP BY feedGroupId
)
SELECT
  fg.id,
  fg.name,
  fgm.feedIds,
  COALESCE(fgm.feedHomepageLinks, '') AS feedHomepageLinks,
  COALESCE(fgm.feedIconLinks, '') AS feedIconLinks,
  COALESCE(fgm.feedShowFavIconSettings, '') AS feedShowFavIconSettings,
  fg.createdAt,
  fg.updatedAt,
  fg.pinnedAt,
  fg.pinnedPosition,
  fg.isDeleted,
  fg.remoteId
FROM feedGroup fg
LEFT JOIN feed_group_metadata fgm ON fg.id = fgm.feedGroupId
WHERE fg.id = :id AND fg.isDeleted = 0;

groupsByIds:
WITH ranked_feeds AS (
  SELECT
    gf.feedGroupId,
    gf.feedId,
    f.homepageLink,
    f.icon,
    CASE
      WHEN f.showFeedFavIcon IS NULL THEN 'null'
      WHEN f.showFeedFavIcon = 1 THEN 'true'
      ELSE 'false'
    END AS showFeedFavIconStr,
    ROW_NUMBER() OVER (PARTITION BY gf.feedGroupId ORDER BY gf.rowid) AS rn
  FROM feedGroupFeed gf
  JOIN feed f ON gf.feedId = f.id
  WHERE f.isDeleted = 0
),
feed_group_metadata AS (
  SELECT
    feedGroupId,
    GROUP_CONCAT(feedId, '::') AS feedIds,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN homepageLink END, '::'), '') AS feedHomepageLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN icon END, '::'), '') AS feedIconLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN showFeedFavIconStr END, '::'), '') AS feedShowFavIconSettings
  FROM ranked_feeds
  GROUP BY feedGroupId
)
SELECT
  fg.id,
  fg.name,
  fgm.feedIds,
  COALESCE(fgm.feedHomepageLinks, '') AS feedHomepageLinks,
  COALESCE(fgm.feedIconLinks, '') AS feedIconLinks,
  COALESCE(fgm.feedShowFavIconSettings, '') AS feedShowFavIconSettings,
  fg.createdAt,
  fg.updatedAt,
  fg.pinnedAt,
  fg.remoteId
FROM feedGroup fg
LEFT JOIN feed_group_metadata fgm ON fg.id = fgm.feedGroupId
WHERE fg.id IN :ids AND fg.isDeleted = 0;

updatedPinnedPosition:
UPDATE feedGroup SET pinnedPosition = :pinnedPosition, updatedAt = :updatedAt WHERE id = :id;

allGroupsBlocking:
WITH ranked_feeds AS (
  SELECT
    gf.feedGroupId,
    gf.feedId,
    f.homepageLink,
    f.icon,
    CASE
      WHEN f.showFeedFavIcon IS NULL THEN 'null'
      WHEN f.showFeedFavIcon = 1 THEN 'true'
      ELSE 'false'
    END AS showFeedFavIconStr,
    ROW_NUMBER() OVER (PARTITION BY gf.feedGroupId ORDER BY gf.rowid) AS rn
  FROM feedGroupFeed gf
  JOIN feed f ON gf.feedId = f.id
  WHERE f.isDeleted = 0
),
feed_group_metadata AS (
  SELECT
    feedGroupId,
    GROUP_CONCAT(feedId, '::') AS feedIds,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN homepageLink END, '::'), '') AS feedHomepageLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN icon END, '::'), '') AS feedIconLinks,
    COALESCE(GROUP_CONCAT(CASE WHEN rn <= 4 THEN showFeedFavIconStr END, '::'), '') AS feedShowFavIconSettings
  FROM ranked_feeds
  GROUP BY feedGroupId
)
SELECT
  fg.id,
  fg.name,
  fgm.feedIds,
  COALESCE(fgm.feedHomepageLinks, '') AS feedHomepageLinks,
  COALESCE(fgm.feedIconLinks, '') AS feedIconLinks,
  COALESCE(fgm.feedShowFavIconSettings, '') AS feedShowFavIconSettings,
  fg.createdAt,
  fg.updatedAt,
  fg.pinnedAt,
  fg.pinnedPosition,
  fg.isDeleted,
  fg.remoteId
FROM feedGroup fg
LEFT JOIN feed_group_metadata fgm ON fg.id = fgm.feedGroupId;

deleteAll:
DELETE FROM feedGroup;

upsertSyncGroup:
INSERT INTO feedGroup(id, name, createdAt, updatedAt, pinnedAt, isDeleted, remoteId)
VALUES (:id, :name, :createdAt, :updatedAt, :pinnedAt, :isDeleted, :remoteId)
ON CONFLICT(id) DO
UPDATE SET
    name = excluded.name,
    pinnedAt = excluded.pinnedAt,
    updatedAt = excluded.updatedAt,
    isDeleted = excluded.isDeleted,
    remoteId = excluded.remoteId
WHERE excluded.updatedAt > feedGroup.updatedAt OR feedGroup.updatedAt IS NULL;

updateFeedGroupRemoteId:
UPDATE feedGroup SET remoteId = :remoteId, updatedAt = :updatedAt WHERE id = :id;

feedGroupByRemoteId:
SELECT * FROM feedGroup WHERE remoteId = :remoteId AND isDeleted = 0;
