-- -------------------------------------

CREATE TABLE PLZ_TYPES (
	code TINYINT UNSIGNED NOT NULL ,
	description VARCHAR( 90 ) NOT NULL ,
--
	PRIMARY KEY ( code )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

CREATE TABLE PLZ_CANTONS (
	canton CHAR( 2 ) NOT NULL ,
	description VARCHAR( 30 ) NOT NULL ,
--
	PRIMARY KEY ( canton )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

CREATE TABLE PLZ_LANGUAGES (
	code TINYINT UNSIGNED NOT NULL ,
	language VARCHAR( 15 ) NOT NULL ,
--
	PRIMARY KEY ( code )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

CREATE TABLE PLZ_P1 (
	onrp SMALLINT UNSIGNED NOT NULL ,
	plz_type_code TINYINT UNSIGNED NOT NULL ,
	postcode SMALLINT UNSIGNED NOT NULL ,
	postcode_suffix CHAR( 2 ) NOT NULL ,
	location_description_18 VARCHAR( 18 ) NOT NULL ,
	location_description_27 VARCHAR( 27 ) NOT NULL ,
	canton CHAR( 2 ) NOT NULL ,
	language_code TINYINT UNSIGNED NOT NULL ,
	language_code_alternative TINYINT UNSIGNED NULL ,
	available_in_sortierfile TINYINT UNSIGNED NOT NULL ,
	postcode_end SMALLINT UNSIGNED NOT NULL ,
	bfsnr SMALLINT UNSIGNED NOT NULL ,
	validity DATE NOT NULL ,
--
	PRIMARY KEY ( onrp ) ,
	KEY plz_type_code ( plz_type_code ) ,
	KEY canton ( canton ) ,
	KEY language_code ( language_code ) ,
	KEY language_code_alternative ( language_code_alternative ) ,
	KEY postcode_end ( postcode_end ) ,
	KEY bfsnr ( bfsnr )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

CREATE TABLE PLZ_DESCRIPTION_TYPES (
	code TINYINT UNSIGNED NOT NULL ,
	description VARCHAR( 140 ) NOT NULL ,
--
	PRIMARY KEY ( code )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

CREATE TABLE PLZ_P2 (
	onrp SMALLINT UNSIGNED NOT NULL ,
	laufnummer SMALLINT UNSIGNED NOT NULL ,
	description_type TINYINT NOT NULL ,
	language_code TINYINT UNSIGNED NOT NULL ,
	description_18 VARCHAR( 18 ) NOT NULL ,
	description_27 VARCHAR( 27 ) NOT NULL ,
--
	PRIMARY KEY ( onrp ) ,
	KEY description_type ( description_type ) ,
	KEY language_code ( language_code )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

CREATE TABLE PLZ_COMMUNITIES (
	bfsnr SMALLINT UNSIGNED NOT NULL ,
	name VARCHAR( 30 ) NOT NULL ,
	canton CHAR( 2 ) NOT NULL ,
	agglomeration SMALLINT UNSIGNED NULL ,
--
	PRIMARY KEY ( bfsnr ) ,
	KEY canton ( canton )
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_general_ci ;

-- -------------------------------------

INSERT INTO PLZ_TYPES (code, description) VALUES (10, 'Domizil- und Fachadressen');
INSERT INTO PLZ_TYPES (code, description) VALUES (20, 'Domiziladressen');
INSERT INTO PLZ_TYPES (code, description) VALUES (30, 'Fachadressen');
INSERT INTO PLZ_TYPES (code, description) VALUES (40, 'Firmenadressen');
INSERT INTO PLZ_TYPES (code, description) VALUES (80, 'postinterne PLZ (Angabe Zustellpoststelle auf Bundzetteln oder auf Sackanschriften)');

INSERT INTO PLZ_CANTONS (canton, description) VALUES('AG', 'Aargau');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('AI', 'Appenzell Innerrhoden');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('AR', 'Appenzell Ausserrhoden');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('BD', 'Vaud');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('BE', 'Bern');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('BL', 'Basel-Landschaft');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('BS', 'Basel-Stadt');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('DE', 'Deutschland');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('FL', 'Fürstentum Liechtenstein');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('FR', 'Fribourg');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('GE', 'Genève');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('GL', 'Glarus');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('GR', 'Graubünden');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('IT', 'Italien');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('JU', 'Jura');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('LU', 'Luzern');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('NE', 'Neuchâtel');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('NW', 'Nidwalden');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('OW', 'Obwalden');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('SG', 'St. Gallen');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('SH', 'Schaffhausen');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('SO', 'Solothurn');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('SZ', 'Schwyz');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('TG', 'Thurgau');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('TI', 'Ticino');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('UR', 'Uri');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('VD', 'Vaud');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('VS', 'Valais');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('ZG', 'Zug');
INSERT INTO PLZ_CANTONS (canton, description) VALUES('ZH', 'Zürich');

INSERT INTO PLZ_LANGUAGES (code, language) VALUES(1, 'deutsch');
INSERT INTO PLZ_LANGUAGES (code, language) VALUES(2, 'französisch');
INSERT INTO PLZ_LANGUAGES (code, language) VALUES(3, 'italienisch');

INSERT INTO PLZ_DESCRIPTION_TYPES (code, description) VALUES(2, 'für Adressierung erlaubte alternative oder fremdsprachige Ortsbezeichnung');
INSERT INTO PLZ_DESCRIPTION_TYPES (code, description) VALUES(3, 'Gebietsbezeichnung; darf in Adressierzeile verwendet werden. Darf aber nicht zusammen mit der PLZ in der Ortszeile verwendet werden.');