feat(migrations): add database schema init scripts

2 files changed, 91 insertions, 0 deletions

diff --git a/src/main/sql/1_create-database.down.sql b/src/main/sql/1_create-database.down.sql
new file mode 100644
index 0000000..f43aba0
--- /dev/null
+++ b/src/main/sql/1_create-database.down.sql
@@ -0,0 +1,12 @@
+DROP TABLE IF EXISTS operation_products;
+DROP TABLE IF EXISTS operations;
+DROP TABLE IF EXISTS operation_statuses;
+DROP TYPE IF EXISTS OPERATION_TYPE;
+DROP TABLE IF EXISTS product_slots;
+DROP TABLE IF EXISTS product_storage_statues;
+DROP TABLE IF EXISTS storage_slots;
+DROP TABLE IF EXISTS partner_contacts;
+DROP TABLE IF EXISTS partners;
+DROP TYPE IF EXISTS PARTNER_TYPE;
+DROP TABLE IF EXISTS products;
+DROP TABLE IF EXISTS product_types;
\ No newline at end of file
diff --git a/src/main/sql/1_create-database.up.sql b/src/main/sql/1_create-database.up.sql
new file mode 100644
index 0000000..d4566fc
--- /dev/null
+++ b/src/main/sql/1_create-database.up.sql
@@ -0,0 +1,79 @@
+CREATE TABLE product_types(
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL
+);
+
+CREATE TABLE products(
+    id INTEGER PRIMARY KEY,
+    type_id INTEGER NOT NULL REFERENCES product_types(id),
+    height INTEGER NOT NULL,
+    width INTEGER NOT NULL,
+    depth INTEGER NOT NULL,
+    max_storage_duration INTERVAL NULL
+);
+
+CREATE TYPE PARTNER_TYPE AS ENUM('SUPPLIER', 'ISSUER');
+
+CREATE TABLE partners(
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL,
+    tax_number TEXT NOT NULL,
+    type PARTNER_TYPE NOT NULL,
+    address TEXT NOT NULL
+);
+
+CREATE TABLE partner_contacts(
+    id INTEGER PRIMARY KEY,
+    partner_id INTEGER NOT NULL REFERENCES partners(id),
+    surname TEXT NOT NULL,
+    name TEXT NOT NULL,
+    position TEXT NOT NULL,
+    phone TEXT NOT NULL,
+    email TEXT NOT NULL
+);
+
+CREATE TABLE storage_slots(
+    id INTEGER PRIMARY KEY,
+    location TEXT NOT NULL,
+    height INTEGER NOT NULL,
+    width INTEGER NOT NULL,
+    depth INTEGER NOT NULL
+);
+
+CREATE TABLE product_storage_statues(
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL
+);
+
+CREATE TABLE product_slots(
+    id INTEGER PRIMARY KEY,
+    product_id INTEGER NOT NULL REFERENCES products(id),
+    slot_id INTEGER NOT NULL REFERENCES storage_slots(id),
+    amount INTEGER NOT NULL CHECK (amount > 0),
+    status_id INTEGER NOT NULL REFERENCES product_storage_statues(id),
+    placement_time TIMESTAMP NOT NULL
+);
+
+CREATE TYPE OPERATION_TYPE AS ENUM('SUPPLY', 'ISSUE');
+
+CREATE TABLE operation_statues(
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL
+);
+
+CREATE TABLE operations(
+    id INTEGER PRIMARY KEY,
+    partner_id INTEGER NOT NULL REFERENCES partners(id),
+    responsible_id INTEGER NOT NULL REFERENCES partner_contacts(id),
+    type OPERATION_TYPE NOT NULL,
+    status_id INTEGER NOT NULL REFERENCES operation_statues(id),
+    date_created TIMESTAMP NOT NULL,
+    date_finished TIMESTAMP NULL
+);
+
+CREATE TABLE operation_products(
+    id INTEGER PRIMARY KEY,
+    operation_id INTEGER NOT NULL REFERENCES operations(id),
+    product_id INTEGER NOT NULL REFERENCES products(id),
+    amount INTEGER NOT NULL CHECK (amount > 0)
+);
\ No newline at end of file