Begin TRANSACTION;

-- The folowing tables will be created (in order of creation):
-- (1) FederalStates 
-- (2) AdministrativeDistricts 
-- (3) Counties 
-- (4) CityNames 
-- (5) PostCodes   
-- (6) Streets
-- (7) Housenumbers
-- (8) PostBox
-- (9) CityNamesToPostBox

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Delete the first row as it contains only column-names and must not be interpreted as a record
DELETE FROM Postcodes_import WHERE rowid = 1;
ALTER TABLE Postcodes_import ADD COLUMN i_ID INTEGER NULL;
UPDATE Postcodes_import SET i_ID = rowid-1 WHERE true;

.mode column
.nullvalue "NULL"
.header on
-- SELECT * FROM Postcodes_import;
.width 5 15 15 20 10 20 20 20
SELECT i_ID, i_FederalStates, i_AdministrativeDistricts, i_Counties, i_PostCodes, i_CityNames_Official, i_Streets_StreetName, i_Housenumbers_Housenumber FROM Postcodes_import;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Convert empty strings (i.e. strings with length = 0) in the CSV-file to NULL
-- UPDATE Postcodes_import SET i_FederalStates            = NULL WHERE length(i_FederalStates) = 0;
-- UPDATE Postcodes_import SET i_AdministrativeDistricts  = NULL WHERE length(i_AdministrativeDistricts) = 0;
-- UPDATE Postcodes_import SET i_Counties                 = NULL WHERE length(i_Counties) = 0;
-- UPDATE Postcodes_import SET i_PostCodes                = NULL WHERE length(i_PostCodes) = 0;
-- UPDATE Postcodes_import SET i_CityNames_Official       = NULL WHERE length(i_CityNames_Official) = 0;
-- UPDATE Postcodes_import SET i_CityNames_DE             = NULL WHERE length(i_CityNames_DE) = 0;
-- UPDATE Postcodes_import SET i_CityNames_EN             = NULL WHERE length(i_CityNames_EN) = 0;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Prepare the fields with geo-coordinate information:
UPDATE Postcodes_import SET i_PostCodes_GC_latitude      = NULL WHERE length(i_PostCodes_GC_latitude) = 0;
UPDATE Postcodes_import SET i_PostCodes_GC_longitude     = NULL WHERE length(i_PostCodes_GC_longitude)= 0;
UPDATE Postcodes_import SET i_Streets_GC_latitude        = NULL WHERE length(i_Streets_GC_latitude) = 0;
UPDATE Postcodes_import SET i_Streets_GC_longitude       = NULL WHERE length(i_Streets_GC_longitude)= 0;
UPDATE Postcodes_import SET i_Housenumbers_GC_latitude   = NULL WHERE length(i_Housenumbers_GC_latitude) = 0;
UPDATE Postcodes_import SET i_Housenumbers_GC_longitude  = NULL WHERE length(i_Housenumbers_GC_longitude) = 0;
-- DOC: It is assumed, that "," is used as decimal-separator in the Excel sheet instead of ".". Hence, "," will be replaced by "."
UPDATE Postcodes_import SET i_PostCodes_GC_latitude      = REPLACE(i_PostCodes_GC_latitude,',','.')     WHERE i_PostCodes_GC_latitude IS NOT NULL;
UPDATE Postcodes_import SET i_PostCodes_GC_longitude     = REPLACE(i_PostCodes_GC_longitude,',','.')    WHERE i_PostCodes_GC_longitude IS NOT NULL;
UPDATE Postcodes_import SET i_Streets_GC_latitude        = REPLACE(i_Streets_GC_latitude,',','.')       WHERE i_Streets_GC_latitude IS NOT NULL;
UPDATE Postcodes_import SET i_Streets_GC_longitude       = REPLACE(i_Streets_GC_longitude,',','.')      WHERE i_Streets_GC_longitude IS NOT NULL;
UPDATE Postcodes_import SET i_Housenumbers_GC_latitude   = REPLACE(i_Housenumbers_GC_latitude,',','.')  WHERE i_Housenumbers_GC_latitude IS NOT NULL;
UPDATE Postcodes_import SET i_Housenumbers_GC_longitude  = REPLACE(i_Housenumbers_GC_longitude,',','.') WHERE i_Housenumbers_GC_longitude IS NOT NULL;
-- DOC: Create additional CenterPoint-fields in the table "Postcodes_import", combining latitude- and corresponding longitude-information:
ALTER TABLE Postcodes_import ADD COLUMN i_PostCode_CenterPoint      TEXT NULL;
ALTER TABLE Postcodes_import ADD COLUMN i_Streets_CenterPoint       TEXT NULL;
ALTER TABLE Postcodes_import ADD COLUMN i_Housenumbers_CenterPoint  TEXT NULL;
UPDATE Postcodes_import SET i_PostCode_CenterPoint      = 'POINT(' || "i_PostCodes_GC_longitude" || ' ' || "i_PostCodes_GC_latitude" || ')'         WHERE i_PostCodes_GC_latitude IS NOT NULL AND i_PostCodes_GC_longitude IS NOT NULL;
UPDATE Postcodes_import SET i_Streets_CenterPoint       = 'POINT(' || "i_Streets_GC_longitude" || ' ' || "i_Streets_GC_latitude" || ')'             WHERE i_Streets_GC_latitude IS NOT NULL AND i_Streets_GC_longitude IS NOT NULL;
UPDATE Postcodes_import SET i_Housenumbers_CenterPoint  = 'POINT(' || "i_Housenumbers_GC_longitude" || ' ' || "i_Housenumbers_GC_latitude" || ')'   WHERE i_Housenumbers_GC_latitude IS NOT NULL AND i_Housenumbers_GC_longitude IS NOT NULL;
-- DOC: The added CenterPoint-fields shall remain NULL if and only if corresponding latitude or longitude IS NULL (or both)
UPDATE Postcodes_import SET i_PostCode_CenterPoint     = NULL WHERE i_PostCodes_GC_latitude IS NULL OR i_PostCodes_GC_longitude IS NULL;
UPDATE Postcodes_import SET i_Streets_CenterPoint      = NULL WHERE i_Streets_GC_latitude IS NULL OR i_Streets_GC_longitude IS NULL;
UPDATE Postcodes_import SET i_Housenumbers_CenterPoint = NULL WHERE i_Housenumbers_GC_latitude IS NULL OR i_Housenumbers_GC_longitude IS NULL;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: The table "FederalStates" must contain at least one record (which will be the tuple record (1, 'UNKNOWN') in case it is the only one),
-- since the FK-field "FederalStateid" of the the table "AdministrativeDistricts" carries the constraint NOT NULL (so that each AdministrativeDistrict must be assigned to a FederalState).
-- In order to guarantee this, the field "i_FederalStates" (which is used for inserting into "FederalStates") is updated to be 'UNKNOWN' in case no value exists.
UPDATE Postcodes_import SET i_FederalStates = 'UNKNOWN'           WHERE length(i_FederalStates) = 0;
UPDATE Postcodes_import SET i_AdministrativeDistricts = 'UNKNOWN' WHERE length(i_AdministrativeDistricts) = 0;
UPDATE Postcodes_import SET i_Counties = 'UNKNOWN'                WHERE length(i_Counties) = 0;
-- Attention: Generally, values in the field "i_FederalStates" will not be unique
-- Attention: Possibly there are already records with value 'UNKNOWN' in the CSV-file
-- Attention: It is assumed that a FederalState can be uniquely identified by its name.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE Postcodes_import ADD COLUMN i_AdministrativeDistricts_unique TEXT NULL;
UPDATE Postcodes_import SET i_AdministrativeDistricts_unique = i_AdministrativeDistricts || ' (' || i_FederalStates || ')' WHERE length(i_AdministrativeDistricts) != 0;
-- "i_AdministrativeDistricts_unique" will be unique in terms of "unique within the respective FederalState" (becuase of appending)
ALTER TABLE Postcodes_import ADD COLUMN i_Counties_unique TEXT NULL;
UPDATE Postcodes_import SET i_Counties_unique = i_Counties || ' (' || i_AdministrativeDistricts_unique || ')' WHERE length(i_Counties) != 0;
-- "i_Counties_unique" will be unique in terms of "unique within the respective AdministrativeDistrict" (becuase of appending)

-- DEBUG Begin
-- .width 5 15 30 45 10 20 
-- SELECT i_ID, i_FederalStates, i_AdministrativeDistricts_unique, i_Counties_unique, i_PostCodes, i_CityNames_Official FROM Postcodes_import;
-- DEBUG End








--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the required tables according to the database-model
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "FederalStates", insert records and add the field "i_FederalStateid" to "Postcodes_import"
CREATE TABLE FederalStates
(
    id      INTEGER NOT NULL CONSTRAINT "PK_FederalStates" PRIMARY KEY AUTOINCREMENT,
    Name    TEXT NULL
);
INSERT INTO FederalStates (Name) SELECT DISTINCT i_FederalStates FROM Postcodes_import WHERE true;
-- DOC: Assertion: Due to construction the table "FederalStates" will contain at least one record with value 'UNKNOWN' in the field "Name"
-- DEBUG Begin
-- SELECT * FROM FederalStates;
-- .width 20
-- SELECT COUNT(*) FROM FederalStates;
-- DEBUG End

-- DOC: Add the field "i_FederalStateid" to "Postcodes_import", so that FederalStates can be identified by their id within the table "Postcodes_import".
-- This field will be used for correct assignment of the "FederalStateid" to the AdministrativeDistrict in the table "AdministrativeDistricts".
ALTER TABLE Postcodes_import ADD COLUMN i_FederalStateid INTEGER NULL;
UPDATE Postcodes_import SET i_FederalStateid = (SELECT "FederalStates"."id" FROM FederalStates WHERE "FederalStates"."Name" = "Postcodes_import"."i_FederalStates");
-- DEBUG Begin
-- .width 5 7 20 30 45 10 20 
-- SELECT i_ID, i_FederalStateid AS i_FS_id, i_FederalStates, i_AdministrativeDistricts_unique, i_Counties_unique, i_PostCodes, i_CityNames_Official FROM Postcodes_import;
-- DEBUG End

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "AdministrativeDistricts", insert records and add the field "i_AdministrativeDistrictid" to "Postcodes_import"
CREATE TABLE AdministrativeDistricts
(
    id                INTEGER NOT NULL CONSTRAINT "PK_AdministrativeDistricts" PRIMARY KEY AUTOINCREMENT,
    Name              TEXT NULL,
    CenterPoint       TEXT NULL,
    FederalStateid    INTEGER NOT NULL,
    CONSTRAINT "FK_AdministrativeDistricts_FederalStates_FederalStateid" FOREIGN KEY (FederalStateid) REFERENCES FederalStates (id) ON DELETE CASCADE
);
INSERT INTO AdministrativeDistricts (Name, FederalStateid) SELECT DISTINCT i_AdministrativeDistricts_unique , i_FederalStateid FROM Postcodes_import WHERE true;
-- DOC: values of "CenterPoint" will remain "NULL"

-- DOC: Add the field "i_AdministrativeDistrictid" to "Postcodes_import", so that AdministrativeDistricts can be identified by their id within the table "Postcodes_import".
-- This field will be used for correct assignment of the "AdministrativeDistrictid" to the County in the table "Counties".
ALTER TABLE Postcodes_import ADD COLUMN i_AdministrativeDistrictid INTEGER NULL;
UPDATE Postcodes_import SET i_AdministrativeDistrictid = (SELECT "AdministrativeDistricts"."id" FROM AdministrativeDistricts WHERE "AdministrativeDistricts"."Name" = "Postcodes_import"."i_AdministrativeDistricts_unique");
-- DOC: From now on, the information to which FederalState an AdministrativeDistrict belongs, is given by "AdministrativeDistricts"."FederalStateid".
-- Hence, the compound Name "i_AdministrativeDistricts_unique" can (and should) be replaced by the original one "i_AdministrativeDistricts".
UPDATE AdministrativeDistricts SET Name =
(SELECT i_AdministrativeDistricts FROM Postcodes_import WHERE "Postcodes_import"."i_AdministrativeDistricts_unique" = "AdministrativeDistricts"."Name");

-- DEBUG Begin
-- .width 5 25 11 20
-- SELECT * FROM AdministrativeDistricts;
-- .width 20
-- SELECT COUNT(*) FROM AdministrativeDistricts;
-- .width 5 7 20 7 30 45 10 20 
-- SELECT i_ID, i_FederalStateid AS i_FS_id, i_FederalStates, i_AdministrativeDistrictid AS i_AD_id, i_AdministrativeDistricts_unique, i_Counties_unique, i_PostCodes, i_CityNames_Official FROM Postcodes_import;
-- DEBUG End

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "Counties", insert records and add the field "i_Countyid" to "Postcodes_import"
CREATE TABLE Counties
(
    id                           INTEGER NOT NULL CONSTRAINT "PK_Counties" PRIMARY KEY AUTOINCREMENT,
    Name                         TEXT NULL,
    AdministrativeDistrictid     INTEGER NOT NULL,
    CONSTRAINT "FK_Counties_AdministrativeDistricts_AdministrativeDistrictid" FOREIGN KEY (AdministrativeDistrictid) REFERENCES AdministrativeDistricts (id) ON DELETE CASCADE
);
INSERT INTO Counties (Name, AdministrativeDistrictid) SELECT DISTINCT i_Counties_unique , i_AdministrativeDistrictid FROM Postcodes_import WHERE true;

ALTER TABLE Postcodes_import ADD COLUMN i_Countyid INTEGER NULL;
UPDATE Postcodes_import SET i_Countyid = (SELECT "Counties"."id" FROM Counties WHERE "Counties"."Name" = "Postcodes_import"."i_Counties_unique");
UPDATE Counties SET Name = (SELECT i_Counties FROM Postcodes_import WHERE "Postcodes_import"."i_Counties_unique" = "Counties"."Name");   

-- DEBUG Begin
-- .width 5 30 30
-- SELECT * FROM Counties;
-- .width 20
-- SELECT COUNT(*) FROM Counties;
-- .width 5 7 20 7 30 7 45 10 20 
-- SELECT i_ID, i_FederalStateid AS i_FS_id, i_FederalStates, i_AdministrativeDistrictid AS i_AD_id, i_AdministrativeDistricts_unique, i_Countyid AS i_C_id, i_Counties_unique, i_PostCodes, i_CityNames_Official FROM Postcodes_import;
-- DEBUG End

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "CityNames" and insert records 
CREATE TABLE CityNames
(
    id                  INTEGER NOT NULL CONSTRAINT "PK_CityNames" PRIMARY KEY AUTOINCREMENT,
    CityName_Official   TEXT COLLATE NOCASE NULL,
    CityName_DE         TEXT COLLATE NOCASE NULL,
    CityName_EN         TEXT COLLATE NOCASE NULL
);
INSERT INTO CityNames (CityName_Official, CityName_DE, CityName_EN) SELECT i_CityNames_Official, i_CityNames_DE, i_CityNames_EN FROM Postcodes_import WHERE true;
-- DOC: Since records are inserted into "CityNames" via simple "SELECT" instead of "SELECT DISTINCT", "CityNames"."id" has the same values as "Postcodes_import"."i_ID" 
-- DEBUG Begin
-- .width 3 30 30 30
-- SELECT * FROM CityNames;
-- .width 20
-- SELECT COUNT(*) FROM CityNames;
-- DEBUG End

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "PostCodes" and insert records   
CREATE TABLE PostCodes
(
    id              INTEGER NOT NULL CONSTRAINT "PK_PostCodes" PRIMARY KEY AUTOINCREMENT,
    PostCode        TEXT NULL,
    CenterPoint     TEXT NULL,
    DialCode        TEXT NULL,
    CityNamesID     INTEGER NOT NULL,
    Countyid        INTEGER NOT NULL,
    CONSTRAINT "FK_PostCodes_CityNames_CityNamesID" FOREIGN KEY (CityNamesID) REFERENCES CityNames (id) ON DELETE CASCADE,
    CONSTRAINT "FK_PostCodes_Counties_Countyid" FOREIGN KEY (Countyid) REFERENCES Counties (id) ON DELETE CASCADE
);
INSERT INTO PostCodes (PostCode, CenterPoint, DialCode, CityNamesID, Countyid) SELECT i_PostCodes, i_PostCode_CenterPoint, i_PostCodes_DialCode, i_ID, i_Countyid FROM Postcodes_import WHERE true;
-- DOC: Since records are inserted into "PostCodes" via simple "SELECT" instead of "SELECT DISTINCT", "Postcodes"."id" has the same values as "Postcodes_import"."i_ID" 
-- DEBUG Begin
-- .width 5 8 11 8 20 15 
-- SELECT * FROM Postcodes;
-- .width 20
-- SELECT COUNT(*) FROM Postcodes;
-- DEBUG End

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "Streets" and insert records 
CREATE TABLE Streets
(
    id                 INTEGER NOT NULL CONSTRAINT "PK_Streets" PRIMARY KEY AUTOINCREMENT,
    StreetName         TEXT COLLATE NOCASE NULL,
    PostcodeEntryid    INTEGER NOT NULL,
    CenterPoint        TEXT NULL,
    CONSTRAINT "FK_Streets_PostCodes_PostcodeEntryid" FOREIGN KEY (PostcodeEntryid) REFERENCES PostCodes (id) ON DELETE CASCADE
);
INSERT INTO Streets (StreetName, PostcodeEntryid, CenterPoint) SELECT i_Streets_StreetName, i_ID, i_Streets_CenterPoint FROM Postcodes_import WHERE true;
-- DOC:Since records are inserted via simple "SELECT" instead of "SELECT DISTINCT", "Streets"."id" has the same values as "Postcodes_import"."i_ID" 
-- DEBUG Begin
-- .width 5 30 20 11 
-- SELECT * FROM Streets;
-- DEBUG End

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "Housenumbers" and insert records 
CREATE TABLE Housenumbers
(
    id              INTEGER NOT NULL CONSTRAINT "PK_Housenumbers" PRIMARY KEY AUTOINCREMENT,
    Housenumber     TEXT NULL,
    CenterPoint     TEXT NULL,
    StreetEntryid   INTEGER NOT NULL,
    CONSTRAINT "FK_Housenumbers_Streets_StreetEntryid" FOREIGN KEY (StreetEntryid) REFERENCES Streets (id) ON DELETE CASCADE
);
INSERT INTO Housenumbers (Housenumber, CenterPoint, StreetEntryid) SELECT i_Housenumbers_Housenumber, i_Housenumbers_CenterPoint, i_ID FROM Postcodes_import WHERE true;
-- DOC: Since records are inserted via simple "SELECT" instead of "SELECT DISTINCT", "Housenumbers"."id" has the same values as "Postcodes_import"."i_ID" 
-- DEBUG Begin
-- .width 5 20 11 20 
-- SELECT * FROM Housenumbers;
-- DEBUG End

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "PostBox" and insert records   
CREATE TABLE PostBox
(
    id                 INTEGER NOT NULL CONSTRAINT "PK_PostBox" PRIMARY KEY AUTOINCREMENT,
    PostboxNrFrom      TEXT NULL,
    PostboxNrTo        TEXT NULL,
    CityPostCodeFrom   TEXT NULL,
    CityPostCodeTo     TEXT NULL,
    PostboxPostCode    TEXT NULL
);
INSERT INTO PostBox (PostboxNrFrom, PostboxNrTo, CityPostCodeFrom, CityPostCodeTo, PostboxPostCode) SELECT i_PostBox_PostboxNrFrom, i_PostBox_PostboxNrTo, i_PostBox_CityPostCodeFrom, i_PostBox_CityPostCodeTo, i_PostBox_PostboxPostCode FROM Postcodes_import WHERE true;
-- DOC: Since records are inserted via simple "SELECT" instead of "SELECT DISTINCT", "PostBox"."id" has the same values as "Postcodes_import"."i_ID" 
-- DEBUG Begin
-- .width 5 30 30 30 30 30 
-- SELECT * FROM PostBox;
-- DEBUG End

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the table "CityNamesToPostBox" and insert records 
CREATE TABLE CityNamesToPostBox
(
    id            INTEGER NOT NULL CONSTRAINT "PK_CityNamesToPostBox" PRIMARY KEY AUTOINCREMENT,
    CityNamesID   INTEGER NOT NULL,
    Postboxid     INTEGER NOT NULL,
    CONSTRAINT "FK_CityNamesToPostBox_CityNames_CityNamesid" FOREIGN KEY (CityNamesID) REFERENCES CityNames (id) ON DELETE CASCADE,
    CONSTRAINT "FK_CityNamesToPostBox_PostBox_Postboxid" FOREIGN KEY (Postboxid) REFERENCES PostBox (id) ON DELETE CASCADE
);
INSERT INTO CityNamesToPostBox (CityNamesID, Postboxid) SELECT i_ID, i_ID FROM Postcodes_import WHERE true;
-- DOC: Since records are inserted via simple "SELECT" instead of "SELECT DISTINCT", "CityNamesToPostBox"."id" has the same values as "Postcodes_import"."i_ID" 
-- DEBUG Begin
-- .width 5 20 20 
-- SELECT * FROM CityNamesToPostBox;
-- DEBUG End

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DEBUG Begin
-- .width 5 7 20 7 30 7 45 10 20 20 20 
-- SELECT i_ID, i_FederalStateid AS i_FS_id, i_FederalStates, i_AdministrativeDistrictid AS i_AD_id, i_AdministrativeDistricts_unique, i_Countyid AS i_C_id, i_Counties_unique, i_PostCodes, i_CityNames_Official, i_Streets_StreetName, i_Housenumbers_Housenumber FROM Postcodes_import;
-- DEBUG End






-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOC: Create the views required by the model for the Postcode-database
CREATE VIEW zip AS
SELECT PostCodes.id, PostCodes.PostCode, CityNames.CityName_Official, CityNames.CityName_DE, CityNames.CityName_EN, PostCodes.CenterPoint, Counties.Name AS County, AdministrativeDistricts.Name AS AdministrativeDistrict, FederalStates.Name AS FederalStateName
FROM PostCodes
INNER JOIN Counties ON Counties.id = PostCodes.Countyid
INNER JOIN CityNames ON CityNames.id = PostCodes.CityNamesID
INNER JOIN AdministrativeDistricts ON AdministrativeDistricts.id = Counties.AdministrativeDistrictid
INNER JOIN FederalStates ON FederalStates.id = AdministrativeDistricts.FederalStateid;
-- DEBUG Begin
-- .width 5 8 15 15 11 20 20 20
-- SELECT * FROM zip;
-- DEBUG End

CREATE VIEW street AS
SELECT Streets.id, Streets.StreetName, Streets.CenterPoint, PostCodes.PostCode, CityNames.CityName_Official, CityNames.CityName_DE, CityNames.CityName_EN, Counties.Name AS County, AdministrativeDistricts.Name AS AdministrativeDistrict, FederalStates.Name AS FederalStateName
FROM Streets
INNER JOIN PostCodes ON Streets.PostcodeEntryid = PostCodes.id
INNER JOIN Counties ON PostCodes.Countyid = Counties.id
INNER JOIN CityNames ON CityNames.id = PostCodes.CityNamesid
INNER JOIN AdministrativeDistricts ON Counties.AdministrativeDistrictid = AdministrativeDistricts.id
INNER JOIN FederalStates ON AdministrativeDistricts.FederalStateid = FederalStates.id;

CREATE VIEW housenumber AS
SELECT Housenumbers.id, Housenumbers.Housenumber, Housenumbers.CenterPoint, Streets.StreetName, PostCodes.PostCode, CityNames.CityName_Official, CityNames.CityName_DE, CityNames.CityName_EN, Counties.Name AS County, AdministrativeDistricts.Name AS AdministrativeDistrict, FederalStates.Name AS FederalStateName
FROM Housenumbers
INNER JOIN Streets ON Housenumbers.StreetEntryid = Streets.id
INNER JOIN PostCodes ON Streets.PostcodeEntryid = PostCodes.id
INNER JOIN Counties ON PostCodes.Countyid = Counties.id
INNER JOIN CityNames ON CityNames.id = PostCodes.CityNamesid
INNER JOIN AdministrativeDistricts ON Counties.AdministrativeDistrictid = AdministrativeDistricts.id
INNER JOIN FederalStates ON AdministrativeDistricts.FederalStateid = FederalStates.id;

CREATE VIEW postboxes AS
SELECT PostBox.PostboxNrFrom || ' .. ' || PostBox.PostboxNrTo AS FromTo, PostBox.PostboxPostCode, CityNames.CityName_Official, CityNames.CityName_DE, CityName_EN
FROM CityNamesToPostBox
INNER JOIN PostBox ON CityNamesToPostBox.PostBoxid = PostBox.id
INNER JOIN CityNames ON CityNamesToPostBox.CityNamesid = CityNames.id
ORDER BY PostBox.PostboxNrFrom;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
.width 20
SELECT COUNT(*) AS amount_of_records FROM Postcodes_import;
DROP TABLE Postcodes_import;

END;