-- create post activities table
-- tracks whether a post has been read or not
CREATE TABLE IF NOT EXISTS `post_activities` (
    `id` INTEGER NOT NULL UNIQUE,

    `post_id` INTEGER NOT NULL,
    `post_type` TEXT NOT NULL,
    `site` TEXT NOT NULL,

    `has_read` INTEGER NOT NULL,

    `created_at` TEXT NOT NULL,
    `updated_at` TEXT NOT NULL,
    PRIMARY KEY(`id` AUTOINCREMENT)
);

-- add index on `has_read`
CREATE INDEX IF NOT EXISTS `post_activities_has_read_index` ON `post_activities` (`has_read`);

-- add index on `post_id` and `post_type` and `site`
CREATE UNIQUE INDEX IF NOT EXISTS `post_activities_post_id_post_type_site_unique` ON `post_activities` (`post_id`, `post_type`, `site`);

