Initial commit

2 files changed, 31 insertions, 0 deletions

diff --git a/migrations/0000_initial.sql b/migrations/0000_initial.sql
new file mode 100644
index 0000000..8e9f1ae
--- /dev/null
+++ b/migrations/0000_initial.sql
@@ -0,0 +1,28 @@
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
+
+CREATE TABLE IF NOT EXISTS polls (
+    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
+    title TEXT NOT NULL,
+    description TEXT,
+    pub_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    author_name TEXT NOT NULL,
+    author_email TEXT,
+    manage_code uuid DEFAULT uuid_generate_v4()
+);
+
+CREATE TABLE IF NOT EXISTS choices (
+    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
+    poll_id uuid NOT NULL,
+    start_datetime TIMESTAMP NOT NULL,
+    end_datetime TIMESTAMP NOT NULL,
+    FOREIGN KEY (poll_id) REFERENCES polls (id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS votes (
+    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
+    poll_id uuid NOT NULL,
+    choice_id uuid NOT NULL,
+    voter_name TEXT NOT NULL,
+    FOREIGN KEY (choice_id) REFERENCES choices (id) ON DELETE CASCADE,
+    FOREIGN KEY (poll_id) REFERENCES polls (id) ON DELETE CASCADE
+);
diff --git a/migrations/0001_indices.sql b/migrations/0001_indices.sql
new file mode 100644
index 0000000..42007ee
--- /dev/null
+++ b/migrations/0001_indices.sql
@@ -0,0 +1,3 @@
+CREATE INDEX IF NOT EXISTS idx_votes_poll_id_voter_name ON votes (poll_id, voter_name);
+CREATE INDEX IF NOT EXISTS idx_choices_poll_id_start_datetime ON choices (poll_id, start_datetime);
+CREATE INDEX IF NOT EXISTS idx_votes_choice_id ON votes (choice_id);