#!/bin/bash

function create_use_database() {
  name=$1
  cat << EOF | cat
CREATE DATABASE $name;
USE $name;

EOF
}

function create_table_fk() {
  num=$1
  cat << EOF | cat
CREATE TABLE Packages_$num (
  pkgID int(10) unsigned NOT NULL AUTO_INCREMENT,
  pkgHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  pkgVersion varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  pkgIsInstalled tinyint(1) NOT NULL,
  pkgAvailableVersion varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  pkgDescription longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  pkgDateInstalled datetime NOT NULL,
  pkgName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (pkgID),
  UNIQUE KEY UNIQ_62C3A2F1F2D49DB1 (pkgHandle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE SiteTypes_$num (
  siteTypeID int(10) unsigned NOT NULL AUTO_INCREMENT,
  siteTypeHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  siteTypeName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  siteTypeThemeID int(11) NOT NULL,
  siteTypeHomePageTemplateID int(11) NOT NULL,
  pkgID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (siteTypeID),
  UNIQUE KEY UNIQ_7CBFE97576D39A3C (siteTypeHandle),
  UNIQUE KEY UNIQ_7CBFE975C7F74FC3 (siteTypeName),
  KEY IDX_7CBFE975CE45CBB0 (pkgID),
  CONSTRAINT FK_P$num FOREIGN KEY (pkgID) REFERENCES Packages_$num (pkgID)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Sites_$num (
  siteID int(10) unsigned NOT NULL AUTO_INCREMENT,
  pThemeID int(10) unsigned NOT NULL,
  siteIsDefault tinyint(1) NOT NULL,
  siteHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  siteTypeID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (siteID),
  UNIQUE KEY UNIQ_7DC18567D84E1976 (siteHandle),
  KEY IDX_7DC18567E9548DF7 (siteTypeID),
  CONSTRAINT FK_S$num FOREIGN KEY (siteTypeID) REFERENCES SiteTypes_$num (siteTypeID)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Calendars_$num (
  caID int(10) unsigned NOT NULL AUTO_INCREMENT,
  caName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  caOverridePermissions tinyint(1) NOT NULL DEFAULT '0',
  eventPageAttributeKeyHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  enableMoreDetails varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  eventPageParentID int(10) unsigned DEFAULT '0',
  eventPageTypeID int(10) unsigned DEFAULT '0',
  eventPageAssociatedID int(10) unsigned DEFAULT '0',
  siteID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (caID),
  KEY IDX_62E00AC521D8435 (siteID),
  CONSTRAINT FK_CV$num FOREIGN KEY (siteID) REFERENCES Sites_$num (siteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE CalendarEvents_$num (
  eventID int(10) unsigned NOT NULL AUTO_INCREMENT,
  caID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (eventID),
  KEY IDX_7F23C55611C85723 (caID),
  CONSTRAINT FK_CE$num FOREIGN KEY (caID) REFERENCES Calendars_$num (caID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE CalendarRelatedEvents_$num (
  relatedEventID int(10) unsigned NOT NULL AUTO_INCREMENT,
  relationID int(10) unsigned NOT NULL,
  relationType varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  eventID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (relatedEventID),
  KEY IDX_310130E910409BA4 (eventID),
  CONSTRAINT FK_CRE$num FOREIGN KEY (eventID) REFERENCES CalendarEvents_$num (eventID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

EOF
}

function create_table() {
  num=$1
  cat << EOF | cat
CREATE TABLE Packages_$num (
  pkgID int(10) unsigned NOT NULL AUTO_INCREMENT,
  pkgHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  pkgVersion varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  pkgIsInstalled tinyint(1) NOT NULL,
  pkgAvailableVersion varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  pkgDescription longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  pkgDateInstalled datetime NOT NULL,
  pkgName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (pkgID),
  UNIQUE KEY UNIQ_62C3A2F1F2D49DB1 (pkgHandle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE SiteTypes_$num (
  siteTypeID int(10) unsigned NOT NULL AUTO_INCREMENT,
  siteTypeHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  siteTypeName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  siteTypeThemeID int(11) NOT NULL,
  siteTypeHomePageTemplateID int(11) NOT NULL,
  pkgID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (siteTypeID),
  UNIQUE KEY UNIQ_7CBFE97576D39A3C (siteTypeHandle),
  UNIQUE KEY UNIQ_7CBFE975C7F74FC3 (siteTypeName),
  KEY IDX_7CBFE975CE45CBB0 (pkgID)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Sites_$num (
  siteID int(10) unsigned NOT NULL AUTO_INCREMENT,
  pThemeID int(10) unsigned NOT NULL,
  siteIsDefault tinyint(1) NOT NULL,
  siteHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  siteTypeID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (siteID),
  UNIQUE KEY UNIQ_7DC18567D84E1976 (siteHandle),
  KEY IDX_7DC18567E9548DF7 (siteTypeID)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE Calendars_$num (
  caID int(10) unsigned NOT NULL AUTO_INCREMENT,
  caName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  caOverridePermissions tinyint(1) NOT NULL DEFAULT '0',
  eventPageAttributeKeyHandle varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  enableMoreDetails varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  eventPageParentID int(10) unsigned DEFAULT '0',
  eventPageTypeID int(10) unsigned DEFAULT '0',
  eventPageAssociatedID int(10) unsigned DEFAULT '0',
  siteID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (caID),
  KEY IDX_62E00AC521D8435 (siteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE CalendarEvents_$num (
  eventID int(10) unsigned NOT NULL AUTO_INCREMENT,
  caID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (eventID),
  KEY IDX_7F23C55611C85723 (caID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE CalendarRelatedEvents_$num (
  relatedEventID int(10) unsigned NOT NULL AUTO_INCREMENT,
  relationID int(10) unsigned NOT NULL,
  relationType varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  eventID int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (relatedEventID),
  KEY IDX_310130E910409BA4 (eventID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

EOF
}

cat << EOF | cat
CREATE USER test@localhost IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON test_fk.* TO test@localhost;

EOF

create_use_database "test_fk"
for i in {0..99}
do
  create_table_fk $i
done

for i in {1..99}
do
  create_use_database "test_$i"
  for j in {0..99}
  do
    create_table $j
  done
done