CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(128) primary key);
CREATE TABLE users
(
    id            uuid PRIMARY KEY NOT NULL UNIQUE,
    email         TEXT             NOT NULL UNIQUE,
    password_hash BLOB             NOT NULL,
    is_admin      TINYINT          NOT NULL
);
CREATE TABLE subscriptions
(
    user_id uuid             NOT NULL,
    feed_id uuid             NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(feed_id) REFERENCES feeds(id) ON DELETE CASCADE,
    UNIQUE(user_id, feed_id)
);
CREATE TABLE feeds
(
    id                uuid PRIMARY KEY NOT NULL,
    link              TEXT             NOT NULL UNIQUE,
    next_check        timestamp        NOT NULL DEFAULT (unixepoch('now')),
    repeated_failures INT              NOT NULL DEFAULT 0
);
CREATE TABLE feed_updates
(
    user_id      uuid             NOT NULL,
    link_to_post TEXT             NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    UNIQUE(user_id, link_to_post)
);
-- Dbmate schema migrations
INSERT INTO "schema_migrations" (version) VALUES
  ('20260331190734');