-- -*- sql-product: sqlite; -*-

-- SPDX-FileCopyrightText: 2023 Vasilij Schneidermann <mail@vasilij.de>
--
-- SPDX-License-Identifier: GPL-3.0-or-later

BEGIN;

CREATE TABLE IF NOT EXISTS todos(
  id INTEGER PRIMARY KEY,
  sort_id INTEGER,
  list_id INTEGER,
  content TEXT,
  done BOOLEAN DEFAULT(FALSE),
  repeat_type TEXT,
  repeat_spec TEXT,
  created TEXT,
  updated TEXT,
  FOREIGN KEY(list_id) REFERENCES lists(id)
);

CREATE TABLE IF NOT EXISTS lists(
  id INTEGER PRIMARY KEY,
  sort_id INTEGER,
  content TEXT,
  created TEXT,
  updated TEXT
);

CREATE TABLE IF NOT EXISTS users(
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT,
  created TEXT,
  updated TEXT,
  UNIQUE(username)
);

CREATE TABLE IF NOT EXISTS sessions(
  id INTEGER PRIMARY KEY,
  identifier TEXT,
  user_id INTEGER,
  created TEXT,
  updated TEXT,
  FOREIGN KEY(user_id) REFERENCES users(id),
  UNIQUE(identifier)
);

COMMIT;