DROP TABLE IF EXISTS personal_account_operations;
DROP TABLE IF EXISTS employee_personal_accounts;
DROP TABLE IF EXISTS employees;
DROP SEQUENCE IF EXISTS seq_employees cascade;

CREATE SEQUENCE seq_employees START 5000;

CREATE TABLE employees
(
    id    BIGINT PRIMARY KEY DEFAULT nextval('seq_employees'),
    name  VARCHAR NOT NULL,
    email VARCHAR NOT NULL
);
CREATE UNIQUE INDEX employees_unique_email_idx ON employees (email);

CREATE TABLE employee_personal_accounts (
                                            id        BIGINT PRIMARY KEY DEFAULT nextval('seq_employees'),
                                            employee_id BIGINT   NOT NULL,
                                            personal_account VARCHAR  NOT NULL,
                                            FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX employee_personal_accounts_unique_employee_account_idx
    ON employee_personal_accounts (employee_id, personal_account);

CREATE TABLE personal_account_operations (
                                             id                  BIGINT PRIMARY KEY DEFAULT nextval('seq_employees'),
                                             personal_account_id     BIGINT   NOT NULL,
                                             operation_date_time           TIMESTAMP NOT NULL,
                                             operation_type      VARCHAR  NOT NULL,
    -- Money data on PostgreSQL using Java https://stackoverflow.com/a/18170030
                                             operation_value     DECIMAL NOT NULL,
                                             FOREIGN KEY (personal_account_id) REFERENCES employee_personal_accounts (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX personal_account_operations_unique_account_datetime_idx
    ON personal_account_operations (personal_account_id, operation_date_time);