CREATE TABLE IF NOT EXISTS flashcard (
    id INTEGER PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    deck_id INTEGER NOT NULL,
    UNIQUE (question, deck_id),
    FOREIGN KEY (deck_id) REFERENCES deck (id)
    ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deck (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE IF NOT EXISTS review (
    id INTEGER PRIMARY KEY,
    reviewed DATETIME NOT NULL,
    scheduled DATETIME NOT NULL,
    rating TEXT NOT NULL,
    stability REAL NOT NULL,
    difficulty REAL NOT NULL,
    flashcard_id INTEGER NOT NULL,
    FOREIGN KEY (flashcard_id) REFERENCES flashcard (id)
    ON DELETE CASCADE
);