2 files changed, 84 insertions, 0 deletions
diff --git a/postgresql/Dockerfile b/postgresql/Dockerfile
new file mode 100644
index 0000000..efe7c5f
--- /dev/null
+++ b/postgresql/Dockerfile
@@ -0,0 +1,2 @@
+FROM postgres:16-alpine
+COPY postgresql/populate.sql /docker-entrypoint-initdb.d/
diff --git a/postgresql/populate.sql b/postgresql/populate.sql
new file mode 100644
index 0000000..7fa09f4
--- /dev/null
+++ b/postgresql/populate.sql
@@ -0,0 +1,82 @@
+CREATE DATABASE ded;
+
+-- Install uuid-ossp extension
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
+
+-- Create the Organisations table.
+CREATE TABLE organisations (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(100) NOT NULL,
+    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Add index to created column.
+CREATE INDEX idx_organisations_created ON organisations(created);
+
+-- Let's add some placeholder data to organisations table.
+INSERT INTO organisations (name, created) VALUES ('Random Organisation 1', CURRENT_TIMESTAMP);
+INSERT INTO organisations (name, created) VALUES ('Random Organisation 2', CURRENT_TIMESTAMP);
+INSERT INTO organisations (name, created) VALUES ('Random Organisation 3', CURRENT_TIMESTAMP);
+
+-- Persons!
+CREATE TABLE persons (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    first_name VARCHAR(56) NOT NULL,
+    last_name VARCHAR(56) NOT NULL,
+    organisation_id INT NOT NULL,
+    role_name VARCHAR(56) NOT NULL,
+    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_person_organisation
+        FOREIGN KEY(organisation_id)
+            REFERENCES organisations(id)
+            ON DELETE CASCADE
+);
+
+-- Add index to created column.
+CREATE INDEX idx_persons_created ON persons(created);
+
+-- Add some people
+INSERT INTO persons (first_name, last_name, organisation_id, role_name, created) VALUES ('Larry', 'Cluno', 1, 'Lead Inspector', CURRENT_TIMESTAMP);
+INSERT INTO persons (first_name, last_name, organisation_id, role_name, created) VALUES ('Olivia', 'Capstan-Melville', 1, 'Lead Inspector', CURRENT_TIMESTAMP);
+
+-- Operations!
+CREATE TABLE operations (
+    id SERIAL PRIMARY KEY,
+    name VARCHAR(100) NOT NULL,
+    description VARCHAR(255) NOT NULL,
+    organisation_id INT NOT NULL,
+    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_operation_organisation
+        FOREIGN KEY(organisation_id)
+            REFERENCES organisations(id)
+            ON DELETE CASCADE
+);
+
+-- Add index to created column.
+CREATE INDEX idx_operations_created ON operations(created);
+
+-- Engagement Strategies
+CREATE TABLE engagement_strategies (
+    id SERIAL PRIMARY KEY,
+    valid_from TIMESTAMP NOT NULL,
+    valid_to TIMESTAMP NOT NULL,
+    description VARCHAR(255),
+    operation_id INT NOT NULL,
+    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    CONSTRAINT fk_es_operation
+        FOREIGN KEY(operation_id)
+            REFERENCES operations(id)
+            ON DELETE CASCADE
+);
+
+CREATE INDEX idx_engagement_strategy_created ON engagement_strategies(created);
+
+-- Create some operations
+INSERT INTO operations (name, created, description, organisation_id) VALUES ('Operation 1', CURRENT_TIMESTAMP, 'Operation 1 Description', 1);
+INSERT INTO operations (name, created, description, organisation_id) VALUES ('Operation 2', CURRENT_TIMESTAMP, 'Operation 2 Description', 1);
+INSERT INTO operations (name, created, description, organisation_id) VALUES ('Operation 3', CURRENT_TIMESTAMP, 'Operation 3 Description', 2);
+
+-- Create some ESs
+INSERT INTO engagement_strategies (valid_from, valid_to, operation_id, created) VALUES ('2021-01-01', '2023-01-01', 1, CURRENT_TIMESTAMP);
+INSERT INTO engagement_strategies (valid_from, valid_to, operation_id, created) VALUES ('2023-01-01', '2025-01-01', 1, CURRENT_TIMESTAMP);
+