DROP TABLE IF EXISTS dictionary;
DROP TABLE IF EXISTS synchro_type;
DROP TABLE IF EXISTS local_synchro_element;
DROP TABLE IF EXISTS synchro_element;
DROP TABLE IF EXISTS period;
DROP TABLE IF EXISTS source;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS keyword;
DROP TABLE IF EXISTS fragment_hierarchy;
DROP TABLE IF EXISTS fragment;
DROP TABLE IF EXISTS discovery_info;
DROP TABLE IF EXISTS directory_entry;
DROP TABLE IF EXISTS directory;
DROP TABLE IF EXISTS synchro_issue;
DROP TABLE IF EXISTS node_version;
DROP TABLE IF EXISTS domain;
DROP TABLE IF EXISTS domain_info;
DROP TABLE IF EXISTS credential;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS user_role;

CREATE TABLE synchro_issue (
       originator INT NOT NULL
     , catpart    INT NOT NULL 
     , type       INT NOT NULL 
     , cpt        INT NOT NULL 
     , PRIMARY KEY (originator,catpart)
) ENGINE=InnoDB;
CREATE INDEX ind_issue_catpart ON synchro_issue (originator ASC);

CREATE TABLE directory (
       id INT NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , parent_id INT
     , PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8 ;
CREATE INDEX ind_dir_name ON directory (name ASC);
CREATE INDEX ind_dir_parent_id ON directory (parent_id ASC);

CREATE TABLE directory_entry (
       fragment_id BIGINT NOT NULL DEFAULT 0
     , directory_id INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE INDEX ind_dire_frag_id ON directory_entry (fragment_id ASC);
CREATE INDEX ind_dire_dir_id ON directory_entry (directory_id ASC);
CREATE INDEX ind_dire_frag_id_2 ON directory_entry (fragment_id ASC, directory_id ASC);

CREATE TABLE fragment (
       id BIGINT NOT NULL
     , uri VARCHAR(255) NOT NULL
     , xml MEDIUMTEXT
     , data_source VARCHAR(255) NOT NULL
     , is_a_leaf BOOL NOT NULL DEFAULT 0
     , is_discoverable BOOL NOT NULL DEFAULT 0
     , is_archived BOOL NOT NULL DEFAULT 0
     , is_indexed BOOL NOT NULL DEFAULT 0
     , owner_id INT
	 , ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
     , PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

ALTER TABLE fragment COMMENT='This table contains all fragments (fragments and instances)
.The fragment unique id is "uri"
. The field "id" is there to speed up the joins';
CREATE INDEX ind_frag_uri ON fragment (uri ASC);
CREATE INDEX ind_frag_ownerid ON fragment (owner_id ASC);
CREATE INDEX ind_frag_is_indexed ON fragment (is_indexed ASC);


CREATE TABLE fragment_hierarchy (
       child_id BIGINT NOT NULL DEFAULT 0
     , parent_id BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE INDEX ind_fragh_child_id ON fragment_hierarchy (child_id ASC);
CREATE INDEX ind_fragh_par_id ON fragment_hierarchy (parent_id ASC);

CREATE TABLE location (
       id INT NOT NULL AUTO_INCREMENT
     , geometry GEOMETRY NOT NULL
     , PRIMARY KEY (id)

	 # Create a spatial index
	 ,SPATIAL KEY(geometry)
) ;

CREATE TABLE discovery_info (
      id BIGINT NOT NULL
	, keywords TEXT NOT NULL
	, abstract TEXT NOT NULL
	, title TEXT NOT NULL
	, directories TEXT NOT NULL
	, location_id INT
    , start_date DATE
    , end_date DATE
) CHARSET=utf8;

CREATE FULLTEXT INDEX ind_full_text_global ON discovery_info (keywords,abstract,title,directories);
CREATE FULLTEXT INDEX ind_full_text_keywords ON discovery_info (keywords);
CREATE FULLTEXT INDEX ind_full_text_abstract ON discovery_info (abstract);
CREATE FULLTEXT INDEX ind_full_text_title ON discovery_info (title);
CREATE FULLTEXT INDEX ind_full_text_directory ON discovery_info (directories);
CREATE INDEX ind_discovery_id ON discovery_info (id ASC);
CREATE INDEX ind_discovery_location_id ON discovery_info (location_id ASC);
CREATE INDEX ind_per_start_date_2 ON discovery_info (start_date ASC, end_date ASC);

CREATE TABLE source (
       id INT NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , type VARCHAR(20)
     , PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX ind_src_name ON source (name ASC);

CREATE TABLE node_version (
       id INT NOT NULL AUTO_INCREMENT
     , name VARCHAR(150) NOT NULL
     , PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE UNIQUE INDEX ind_node_version_name ON source (name ASC);

CREATE TABLE synchro_element (
       sync_id BIGINT NOT NULL
     , fragment_id BIGINT 
     , owner_id INT
     , type_id INT
     , owner_version BIGINT
     , timestamp_version BIGINT
     , node_version BIGINT
     , PRIMARY KEY (sync_id)
) ENGINE=InnoDB;

CREATE INDEX ind_syncelem_own_id ON synchro_element (owner_id ASC);
CREATE INDEX ind_syncelem_ver ON synchro_element (owner_version ASC);
CREATE INDEX ind_syncelem_node_ver ON synchro_element (node_version ASC);
CREATE INDEX ind_fragment_id ON synchro_element (fragment_id ASC);

CREATE TABLE local_synchro_element (
       sync_id BIGINT NOT NULL
	 , fragment_id BIGINT
     , source_id INT
     , source_version BIGINT
     , timestamp_version BIGINT
     , PRIMARY KEY (sync_id)
) ENGINE=InnoDB;

CREATE TABLE synchro_type (
       id INT NOT NULL AUTO_INCREMENT
     , type VARCHAR(255) NOT NULL
     , PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX ind_own_name ON synchro_type (type ASC);

CREATE TABLE dictionary (
       id INT NOT NULL AUTO_INCREMENT
     , keyword VARCHAR(255) NOT NULL
     , UNIQUE UQ_dictionary_1 (id)
     , PRIMARY KEY (keyword, id)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX ind_dic_keyword ON dictionary (keyword ASC);


## ADD Domain Information
CREATE TABLE domain (
        id INT NOT NULL AUTO_INCREMENT
      , name VARCHAR(255) NOT NULL
      , description TEXT
      , type VARCHAR(255) NOT NULL
      , PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX ind_dom_name ON domain (name ASC);

CREATE TABLE domain_info (
      id INT NOT NULL AUTO_INCREMENT
      , organisation VARCHAR(255) NOT NULL
      , domain_id INT NOT NULL
      , credential_id INT NOT NULL
      , PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX ind_dom_info_domain_id ON domain_info (domain_id ASC);
CREATE INDEX ind_dom_info_organisation ON domain_info (organisation ASC);

CREATE TABLE credential (
    id INT NOT NULL AUTO_INCREMENT
    , credential MEDIUMBLOB NOT NULL
    , type VARCHAR(20)
    , PRIMARY KEY (id)
) ENGINE=InnoDB;


# Users ================================================================

CREATE TABLE user (
	id INT NOT NULL AUTO_INCREMENT
    , full_name VARCHAR(255)
    , user_name VARCHAR(255) UNIQUE NOT NULL
    , email     VARCHAR(255)
    , info      TEXT
    , password  VARCHAR(80)
    , PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE role (
	id INT NOT NULL AUTO_INCREMENT
	, domain_id INT NOT NULL
    , name VARCHAR(255) UNIQUE NOT NULL
    , description TEXT
    , PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE TABLE user_role (
	user_id INT NOT NULL
	,role_id INT NOT NULL
) ENGINE=InnoDB;
CREATE UNIQUE INDEX ind_user_role ON user_role (user_id,role_id);

INSERT INTO `domain` (name,description,type) VALUES ('simdat_demo','SIMDAT_DEMO_DOMAIN','RestrictedSAML');
INSERT INTO role   (name,domain_id,description) VALUES("essential",1,"Essential data");
INSERT INTO role   (name,domain_id,description) VALUES("additional",1,"Additional data");

INSERT INTO `domain_info` VALUES (1,'int.ecmwf.simdat-cn1',1,1);

INSERT INTO `credential` VALUES (1,0x308202043082016DA00302010202087408E663C62EE2B5300D06092A864886F70D0101050500303C310F300D0603550403130653494D444154310C300A060355040B13034E5353310E300C060355040A130545434D5746310B3009060355040613024555301E170D3037313030353131353930305A170D3132313030333132303930305A304D311D301B06035504031314696E742E65636D77662E73696D6461742D636E31310F300D060355040B130653494D444154310E300C060355040A130545434D5746310B300906035504061302455530819F300D06092A864886F70D010101050003818D0030818902818100A5C32A87D7EFC8D1AD30872AE7B07B48175C625A1BFD965CC54825B42003D6D0B556688FFDA0934D59BC3E82CC55131C797914A93F7D056BF89090E1A68020F01042F2CC125FD2E1F200A6CC6AC926C5F52DACEE3D2E4406A98A8233337AF5D536A3D1BC8DFC772064DD79945DD599C6C68B828A2438B9675C6621D21C4604CB0203010001300D06092A864886F70D0101050500038181004EC870CDB378BAA6C9CE3A39FDD1A4BD385A684022CF95B93D91906B1F681EBFA15B857B09C9437B28E12FB657E15D50C9C940BDB3268D8684034A236F680E2A97BDCE332B8998EA5F659F9DFDCD061AB7C108F3C8720CB4A1EE8D3BF5B51B954974ED4E3EE6331D09FC5CEEB7B5D9CE6F2BAEA671563A80D8B2E2DA373354F2,NULL);

# Create user guest, password "guest" with no roles
INSERT INTO user   (full_name,user_name,email,info,password) 
VALUES("Guest user","guest","guest@ecmwf.int","Generic guest user","YBqNY1OZIJ29Q");

# create user vmc_test, password=test_vmc
INSERT INTO user   (full_name,user_name,email,info,password) 
VALUES("Test user","vmc_test","vmc@ecmwf.int","This is a test user","dNPwGW01juqvM");

# associate role ecmwf_countries to user test_vmc for test purpose
INSERT INTO user_role (user_id,role_id) VALUES (2,1);

# Users ================================================================

# Add WMO-CORE

# insert synchro_type
INSERT into synchro_type (type) values ("create");
INSERT into synchro_type (type) values ("delete");
INSERT into synchro_type (type) values ("reset");
INSERT into synchro_type (type) values ("update");
INSERT into synchro_type (type) values ("clean");


# insert 3 sources
INSERT into source (name,type) values("int.wmo","Inv");

INSERT into synchro_element (sync_id,fragment_id,owner_id,type_id,owner_version,timestamp_version) values(1,1,1,1,1,1);

#insert wmo.core
INSERT into fragment (id,is_indexed,uri,xml) values(1,1,"int.wmo.core","<metaData xmlns:v=\"http://www.vgisc.org/\">
	<v:vgisc><v:id>int.wmo.core</v:id></v:vgisc>
	<metadataLanguage>en</metadataLanguage>
	<metadataCharacterSet>ucs2</metadataCharacterSet>
	<metadataDateStamp>2004-02-11</metadataDateStamp>
	<metadataStandardName>WMO19115 http://www.WMO.int/web/www/metadata/WMO-core-metadata-toc.html</metadataStandardName>
	<metadataStandardVersion>Version 0.19</metadataStandardVersion>
	<referenceSystemInfo>
		<referenceSystemIdentifier>WMO Publication 9, Volume A. Observing stations. http://www.WMO.int/web/www/ois/volume-a/vola-home.htm</referenceSystemIdentifier>
		<referenceAuthority>
			<organisationName>World Meteorological Organisation</organisationName>
			<role>originator</role>
			<positionName>Observing Systems</positionName>
			<contactInfo>
				<phone>
					<voice>+41 22 730 8111</voice>
					<facsimile>+41 22 730 8181</facsimile>
				</phone>
				<address>
					<deliveryPoint>7 bis Avenue de la Paix, CP2300 - 1200 </deliveryPoint>
					<city>Geneva 2</city>
					<postalCode/>
					<country>Switzerland</country>
					<electronicMailAddress>ipa@WMO.int</electronicMailAddress>
				</address>
				<onlineAddress>http://www.WMO.int</onlineAddress>
			</contactInfo>
		</referenceAuthority>
		<referenceDescription>This system of observing stations is managed by member states of the World Meteorological Organisation under formal rules and protocols</referenceDescription>
	</referenceSystemInfo>
	<identificationInfo>
		<citation>
			<resourceIdentifierType>WMO GTS AHL and station identifier</resourceIdentifierType>
		</citation>
		<resourceMaintenance>
			<maintenanceAndUpdateFrequency>continual</maintenanceAndUpdateFrequency>
			<maintenanceNote>WMO Volume 306 Manual on codes http://www.WMO.int/web/www/DPS/NewCodesTables/WMO306vol-I-1PartA.pdf</maintenanceNote>
		</resourceMaintenance>
		<resourceConstraints>
			<legalConstraints>
				<otherConstraints>WMO Resolution 40 http://www.WMO.ch/web/pla/Res40Cg-XII.doc</otherConstraints>
			</legalConstraints>
		</resourceConstraints>
		<spatialRepresentationType>irregularPoints</spatialRepresentationType>
		<spatialResolution>1</spatialResolution>
		<dataLanguage>en</dataLanguage>
		<dataCharacterSet>ucs2</dataCharacterSet>
	</identificationInfo>
</metaData>");