feat(database): Added initial database schema

1 files changed, 93 insertions, 0 deletions

diff --git a/migrations/001_initial_schema.sql b/migrations/001_initial_schema.sql
new file mode 100644
index 0000000..77ab893
--- /dev/null
+++ b/migrations/001_initial_schema.sql
@@ -0,0 +1,93 @@
+CREATE TABLE calendars (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    description TEXT,
+    is_archived INTEGER NOT NULL DEFAULT 0,
+    created_at TEXT NOT NULL,
+    updated_at TEXT NOT NULL
+);
+
+CREATE INDEX idx_calendars_archived
+    ON calendars (is_archived);
+
+
+/* Only for nonrecurrent events */
+CREATE TABLE events (
+    id TEXT PRIMARY KEY,
+    calendar_id TEXT NOT NULL,
+    title TEXT NOT NULL,
+    description TEXT,
+    starts_at TEXT NOT NULL,
+    ends_at TEXT NOT NULL,
+    color INTEGER NOT NULL DEFAULT 0,
+    is_all_day INTEGER NOT NULL DEFAULT 0,
+    is_cancelled INTEGER NOT NULL DEFAULT 0,
+    created_at TEXT NOT NULL,
+    updated_at TEXT NOT NULL,
+    FOREIGN KEY (calendar_id)
+        REFERENCES calendars(id)
+        ON DELETE CASCADE,
+    CHECK (starts_at < ends_at),
+    CHECK (color BETWEEN 0 AND 255)
+);
+
+CREATE TABLE recurrences (
+    id TEXT PRIMARY KEY,
+    calendar_id TEXT NOT NULL,
+    title TEXT NOT NULL,
+    description TEXT,
+    starts_at TEXT NOT NULL,
+    ends_at TEXT NOT NULL,
+    frequency TEXT NOT NULL,
+    interval INTEGER NOT NULL,
+    until TEXT,
+    color INTEGER NOT NULL DEFAULT 0,
+    is_all_day INTEGER NOT NULL DEFAULT 0,
+    is_cancelled INTEGER NOT NULL DEFAULT 0,
+    created_at TEXT NOT NULL,
+    updated_at TEXT NOT NULL,
+    FOREIGN KEY (calendar_id)
+        REFERENCES calendars(id)
+        ON DELETE CASCADE,
+    CHECK (starts_at < ends_at),
+    CHECK (interval > 0),
+    CHECK (color BETWEEN 0 AND 255)
+);
+
+CREATE TABLE recurrence_exceptions (
+    recurrence_id TEXT NOT NULL,
+    original_starts_at TEXT NOT NULL,
+    new_starts_at TEXT,
+    new_ends_at TEXT,
+    is_cancelled INTEGER NOT NULL DEFAULT 0,
+    PRIMARY KEY (recurrence_id, original_starts_at)
+    FOREIGN KEY (recurrence_id)
+        REFERENCES recurrences(id)
+        ON DELETE CASCADE
+);
+
+CREATE INDEX idx_events_overlap
+    ON events (calendar_id, ends_at, starts_at)
+    WHERE is_cancelled = 0;
+
+CREATE INDEX idx_events_active
+    ON events (calendar_id, is_cancelled, starts_at);
+
+CREATE INDEX idx_events_calendar_dates
+    ON events (calendar_id, starts_at, ends_at)
+    WHERE is_cancelled = 0;
+
+CREATE INDEX idx_recurrences_calendar
+    ON recurrences (calendar_id);
+
+CREATE INDEX idx_recurrences_start_end
+    ON recurrences (starts_at, ends_at);
+
+CREATE INDEX idx_recurrences_active
+    ON recurrences (is_cancelled, until);
+
+CREATE INDEX idx_recurrence_exceptions_recurrence
+    ON recurrence_exceptions (recurrence_id);
+
+CREATE INDEX idx_recurrence_exceptions_original_date
+    ON recurrence_exceptions (recurrence_id, original_starts_at);