CREATE DATABASE STORKTRANSFER;

USE STORKTRANSFER;

CREATE TABLE `document` (
  `DOCID` varchar(255) NOT NULL,
  `DOCUMENT` longblob,
  `FILENAME` varchar(255) DEFAULT NULL,
  `MIMETYPE` longtext,
  `CREATED` datetime DEFAULT NULL,
  `UPDATED` datetime DEFAULT NULL,
  `DELETED` datetime DEFAULT NULL,
  `RECEIVERCERT` longtext,
  `ENCKEY` longtext,
  `ENCIV` longtext,
  PRIMARY KEY (`DOCID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `request` (
  `REQUESTID` varchar(255) NOT NULL,
  `DOCID` varchar(255) DEFAULT NULL,
  `DESTCOUNTRY` varchar(10) NOT NULL,
  `SPCOUNTRY` varchar(10) NOT NULL,
  `SPID` varchar(255) NOT NULL,
  `XMLREQUEST` longtext,
  `XMLRESPONSE` longtext,
  `REQTIMESTAMP` datetime DEFAULT NULL,
  `RESTIMESTAMP` datetime DEFAULT NULL,
  PRIMARY KEY (`REQUESTID`),
  KEY `docid_idx` (`DOCID`),
  CONSTRAINT `docidindex` FOREIGN KEY (`DOCID`) REFERENCES `document` (`DOCID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `spid` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SPID` varchar(45) NOT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  `DESC` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
SELECT * FROM STORKTRANSFER.spid;

CREATE TABLE `temp_document` (
  `DOCID` varchar(255) NOT NULL,
  `DOCUMENT` longblob,
  `MIMETYPE` longtext,
  `CREATED` datetime DEFAULT NULL,
  `FETCHED` datetime DEFAULT NULL,
  `DELETED` datetime DEFAULT NULL,
  `RECEIVERCERT` longtext,
  `ENCKEY` longtext,
  `ENCIV` longtext,
  `SPID` int(11) DEFAULT NULL,
  PRIMARY KEY (`DOCID`),
  KEY `SPconstraint_idx` (`SPID`),
  CONSTRAINT `SPconstraint` FOREIGN KEY (`SPID`) REFERENCES `spid` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Temp table for SP documents';