CREATE TABLE `ACCOUNTS` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `NAME` varchar(40),
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

CREATE TABLE `TRANSACTION` (
  `ID` char(64) PRIMARY KEY NOT NULL,
  `TDATE` date NOT NULL,
  `ACCOUNT_ID` integer NOT NULL,
  `MEMO` varchar(64) NOT NULL,
  `COUNTRY` char(2),
  `OUTFLOW` decimal(20,2),
  `INFLOW` decimal(12,2),
  `OWNER_ID` integer,
  `INSTALLMENT_NR` integer,
  `INSTALLMENT_TT` integer,
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

CREATE TABLE `PAYEE` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `NAME` varchar(40),
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

CREATE TABLE `OWNER` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `NAME` varchar(40),
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

CREATE TABLE `CATEGORY` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `NAME` varchar(40),
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

CREATE TABLE `SUBCATEGORY` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `CATEGORY_ID` integer,
  `NAME` varchar(40),
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

CREATE TABLE `CATEGORIZED_TRANSACTIONS` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `ACCOUNT_ID` integer,
  `TRANSACTION_ID` char(64),
  `PAYEE_ID` integer,
  `CATEGORY_ID` integer,
  `SUBCATEGORY_ID` integer,
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

DROP TABLE IF EXISTS `RULES`;

CREATE TABLE `RULES` (
  `ID` integer PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `PATTERN` varchar(50) NOT NULL,
  `CATEGORY_ID` integer,
  `SUBCATEGORY_ID` integer,
  `CREATED` datetime NOT NULL,
  `UPDATED` datetime
);

ALTER TABLE `CATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `RULES` (`CATEGORY_ID`);

ALTER TABLE `SUBCATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `RULES` (`SUBCATEGORY_ID`);

ALTER TABLE `ACCOUNTS` ADD FOREIGN KEY (`ID`) REFERENCES `TRANSACTION` (`ACCOUNT_ID`);

ALTER TABLE `OWNER` ADD FOREIGN KEY (`ID`) REFERENCES `TRANSACTION` (`OWNER_ID`);

ALTER TABLE `CATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `SUBCATEGORY` (`CATEGORY_ID`);

ALTER TABLE `TRANSACTION` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`TRANSACTION_ID`);

ALTER TABLE `CATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`CATEGORY_ID`);

ALTER TABLE `SUBCATEGORY` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`SUBCATEGORY_ID`);

ALTER TABLE `PAYEE` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`PAYEE_ID`);

ALTER TABLE `ACCOUNTS` ADD FOREIGN KEY (`ID`) REFERENCES `CATEGORIZED_TRANSACTIONS` (`ACCOUNT_ID`);