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);