BEGIN;

USE aj_test_db_1;
/**
if the query fails in workbench, the second time it runs, it will say that the tables already exists ...
- ahhh .. mysql DDL statements cause implicit commit (data definition language: create, alter structures ... )
 */
DROP TABLE IF EXISTS backup_users, backup_pool, backup_item_categories, backup_item_types,  backup_pool_items, backup_item_config, backup_history, backup_source_file_hashes ;

CREATE TABLE backup_users(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email TEXT not null,
  first_name TEXT not null,
  last_name TEXT not null,

  PRIMARY KEY (id)
);

CREATE TABLE backup_pool(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) not null UNIQUE,
  user_id INT UNSIGNED NOT NULL REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  disabled tinyint(1) NOT NULL,
  PRIMARY KEY (id)
);

/*
  database, filesystem, etc
 */
CREATE TABLE backup_item_categories(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) not null UNIQUE,
  description TEXT,

  PRIMARY KEY (id)
);
/*
  database (mysql, postgres, ..., other-handlers), filesystem, ... , other-handlers
 */
CREATE TABLE backup_item_types(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  backup_item_category_id INT UNSIGNED NOT NULL REFERENCES backup_item_categories(id) ON DELETE CASCADE ON UPDATE CASCADE,
  name VARCHAR(200) NOT NULL,
  description TEXT,

  PRIMARY KEY (id),
  UNIQUE KEY (backup_item_category_id, name)
);


CREATE TABLE backup_pool_items(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name TEXT NOT NULL,
  backup_pool_id INT UNSIGNED NOT NULL REFERENCES backup_pool(id) ON DELETE CASCADE ON UPDATE CASCADE,
  backup_item_type_id INT UNSIGNED NOT NULL REFERENCES backup_item_types(id) ON DELETE CASCADE ON UPDATE CASCADE,
  schedule TEXT NOT NULL, /*cron format?*/
  next_date DATETIME,
  PRIMARY KEY (id)
);

/**
- vertical table
source and target (default to path on backup APP server) information ...
  - eventually could ftp/forward backup to separate server ...
e.g.
  - ftp: login, base path,
  - mysql login, ... full db for now
 */
CREATE TABLE backup_item_config(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  backup_pool_item_id INT UNSIGNED NOT NULL REFERENCES backup_pool_items(id) ON DELETE CASCADE ON UPDATE CASCADE,
  name VARCHAR(200) NOT NULL,
  value TEXT NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY (backup_pool_item_id, name)
);


CREATE TABLE backup_history(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  backup_pool_item_id INT UNSIGNED NOT NULL REFERENCES backup_pool_items(id) ON DELETE CASCADE ON UPDATE CASCADE,
  name VARCHAR(200) NOT NULL,
  path TEXT NOT NULL,
  hash TEXT,
  date_added DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  success tinyint NOT NULL DEFAULT '1', /*may have to use "boolean" in the doctrine comment*/
  message text,
  PRIMARY KEY (id),
  /*UNIQUE KEY (backup_pool_item_id, name)*/
  INDEX (backup_pool_item_id, name)
);

CREATE TABLE backup_source_file_hashes(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  backup_pool_item_id INT UNSIGNED NOT NULL REFERENCES backup_pool_items(id) ON DELETE CASCADE ON UPDATE CASCADE,
  name VARCHAR(200) NOT NULL,
  path TEXT NOT NULL,
  hash TEXT,
  date_added DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY (backup_pool_item_id, name)
);

INSERT INTO backup_users(email, first_name, last_name) VALUES ('aj1@example.com', 'User1', 'User1');
INSERT INTO backup_item_categories (name, description) VALUES ('database', 'Backup a database');
INSERT INTO backup_item_categories (name, description) VALUES ('file-system', 'Backup a file system');

INSERT INTO backup_item_types (backup_item_category_id, name, description) VALUES (1, 'MySql', 'MySql Database');
INSERT INTO backup_item_types (backup_item_category_id, name, description) VALUES (1, 'Postgres', 'Postgres Database');
INSERT INTO backup_item_types (backup_item_category_id, name, description) VALUES (2, 'FTP', 'Using FTP');
INSERT INTO backup_item_types (backup_item_category_id, name, description) VALUES (2, 'SFTP', 'Using SFTP');

COMMIT ;