/* CREATE OR REPLACE DATABASE ded CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; */

/* CREATE USER 'web'@'localhost'; */
/* GRANT SELECT, INDEX, INSERT, UPDATE, DELETE, CREATE, DROP ON ded.* TO 'web'@'localhost'; */
/* ALTER USER 'web'@'localhost' IDENTIFIED BY 'dedpassword'; */

/* Switch to using it. */
USE ded;

/* Create the Operations table */
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS organisations;
DROP TABLE IF EXISTS operations;
DROP TABLE IF EXISTS engagement_strategies;
SET FOREIGN_KEY_CHECKS = 1;

/* Create the Organisations table. */
CREATE TABLE organisations (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created DATETIME NOT NULL
);

/* Add index to created column. */
CREATE INDEX idx_organsations_created ON organisations(created);

-- Let's add some placeholder data to organisations table.
INSERT INTO organisations (name, created) VALUES ("Random Organisation 1", UTC_TIMESTAMP());
INSERT INTO organisations (name, created) VALUES ("Random Organisation 2", UTC_TIMESTAMP());
INSERT INTO organisations (name, created) VALUES ("Random Organisation 3", UTC_TIMESTAMP());


CREATE TABLE operations (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NOT NULL,
    organisation_id INT NOT NULL,
    created DATETIME NOT NULL,
    CONSTRAINT fk_operation_organisation
        FOREIGN KEY(organisation_id)
            REFERENCES organisations(id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

/* Add index to created column. */
CREATE INDEX idx_operations_created ON operations(created);

CREATE TABLE engagement_strategies (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    valid_from DATETIME NOT NULL,
    valid_to DATETIME NOT NULL,
    description VARCHAR(255) NULL,
    operation_id INT NOT NULL,
    created DATETIME NOT NULL,
    CONSTRAINT fk_es_operation
        FOREIGN KEY(operation_id)
            REFERENCES operations(id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

CREATE INDEX idx_engagement_strategy_created ON engagement_strategies(created);


/* Create some operations */
INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 1", UTC_TIMESTAMP(), "Operation 1 Description", 1);
INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 2", UTC_TIMESTAMP(), "Operation 2 Description", 1);
INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 3", UTC_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, UTC_TIMESTAMP());
INSERT INTO engagement_strategies (valid_from, valid_to, operation_id, created) VALUES ("2023-01-01", "2025-01-01", 1, UTC_TIMESTAMP());



/* /1* The following should be carried out on the database server *1/ */
/* -- CREATE USER 'web'@'localhost'; */
/* -- GRANT SELECT, INSERT, UPDATE, DELETE ON ded.* TO 'web'@'localhost'; */
/* /1* Important: Make sure to swap 'pass' with a password of your own choosing. *1/ */
/* -- ALTER USER 'web'@'localhost' IDENTIFIED BY 'dedpassword'; */