/* ========================================
 * Tabellen
 * ======================================== */
CREATE TABLE IF NOT EXISTS "Land" (
	"ID"	TEXT NOT NULL UNIQUE,
	"Name"	TEXT NOT NULL,
	PRIMARY KEY("ID")
);

CREATE TABLE IF NOT EXISTS "Kreis" (
	"ID"	INTEGER NOT NULL UNIQUE,
	"Land"	TEXT NOT NULL,
	"Name"	TEXT NOT NULL,
	"Kfz"	TEXT NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT),
	CONSTRAINT "Land" FOREIGN KEY("Land") REFERENCES "Land"("ID")
);

CREATE TABLE IF NOT EXISTS "Gemeinde" (
	"ID"	INTEGER NOT NULL,
	"KreisID"	INTEGER NOT NULL,
	"Name"	TEXT NOT NULL,
	"Gemeindeschluessel"	TEXT NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT),
	CONSTRAINT "Kreis" FOREIGN KEY("KreisID") REFERENCES "Kreis"("ID")
);

CREATE TABLE IF NOT EXISTS "Strasse" (
	"ID"	INTEGER NOT NULL UNIQUE,
	"GemeindeID"	INTEGER NOT NULL,
	"Ortsteil"	TEXT,
	"Name"	TEXT NOT NULL,
	"Strassenschluessel"	TEXT NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT),
	CONSTRAINT "GemeindeID" FOREIGN KEY("GemeindeID") REFERENCES "Gemeinde"("ID")
);

CREATE TABLE IF NOT EXISTS "DataUpdate" (
	"ID"	INTEGER NOT NULL UNIQUE,
	"Type"	INTEGER NOT NULL,
	"GemeindeID"	INTEGER NOT NULL,
	"StrasseID"	INTEGER NOT NULL,
	"Ortsteil"	TEXT NOT NULL,
	"Strasse"	TEXT NOT NULL,
	"Strassenschluessel"	TEXT NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT)
);

CREATE TABLE IF NOT EXISTS "DBVersion" (
	"Typ"	TEXT,
	"Major"	INTEGER,
	"Minor"	INTEGER
, "Datum"	TEXT);

INSERT INTO DBVersion VALUES ('Schema', 1, 1, '2025-05-26');
INSERT INTO DBVersion VALUES ('Data', 1, 1, '2025-05-26');

/* ========================================
 * Indizes
 * ======================================== */
CREATE INDEX idxStrasse
ON Strasse (GemeindeID, Name);

/* ========================================
 * Views
 * ======================================== */
CREATE VIEW Strassenliste
AS
SELECT Name AS Strasse,
      CASE WHEN Ortsteil = "" THEN "" ELSE "(" || Ortsteil || ")" END AS Ortsteil,  
	  ID,
	  GemeindeID
	  FROM Strasse ORDER BY Strasse, Ortsteil
/* Strassenliste(Strasse,Ortsteil,ID,GemeindeID) */;
CREATE VIEW Gemeindeliste
AS
SELECT Gemeinde.ID AS ID, Gemeinde.Name AS Gemeinde, 
CASE
    WHEN Gemeinde.Name =  Kreis.Name THEN " (" || Kreis.Land || ")"
    ELSE " (" || Kreis.Name || ", " || Kreis.Land || ")"
END AS Text 
FROM Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID ORDER BY Gemeinde
/* Gemeindeliste(ID,Gemeinde,Text) */;
CREATE VIEW getLand
AS
SELECT Land.Name AS Name, Kreis.Kfz AS Kfz
FROM Land INNER JOIN Kreis ON Land.ID = Kreis.Land
/* getLand(Name,Kfz) */;
CREATE VIEW getGemeinde
AS
SELECT Gemeinde.Name AS Name, Gemeinde.Gemeindeschluessel AS GemSchl, Kreis.Kfz AS Kfz
FROM (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID)
/* getGemeinde(Name,GemSchl,Kfz) */;

CREATE VIEW getKreis
AS
SELECT Kreis.Kfz, Kreis.Name AS Kreis, Land.Name AS Land, Gemeinde.ID AS GemID, Gemeinde.Gemeindeschluessel AS Schluessel
FROM Land INNER JOIN (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID) ON Land.ID = Kreis.Land
/* getKreis(Kfz,Kreis,Land,GemID,Schluessel) */;
CREATE VIEW getSchemaVersion
AS
SELECT Major, Minor, Major || "." || Minor AS Name, Datum 
FROM "DBVersion" WHERE Typ = "Schema"
/* getSchemaVersion(Major,Minor,Name) */;
CREATE VIEW getDataVersion
AS
SELECT Major, Minor, Major || "." || Minor AS Name, Datum 
FROM "DBVersion" WHERE Typ = "Data"
/* getDataVersion(Major,Minor,Name) */;
CREATE VIEW getStrasse
AS
SELECT Strasse.Name || CASE WHEN Strasse.Ortsteil = "" THEN "" ELSE " (" || Ortsteil || ")" END  AS Name, 
     Strasse.Strassenschluessel AS StrSchl, Gemeinde.ID AS GemID, Gemeinde.Gemeindeschluessel AS GemSchl, Kreis.Kfz AS Kfz
FROM (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID) INNER JOIN Strasse ON Gemeinde.ID = Strasse.GemeindeID
/* getStrasse(Name,StrSchl,GemID,GemSchl,Kfz) */;
CREATE VIEW Schlüssel AS 
SELECT Land.Name AS Land, Kreis.Name AS Kreis, Kreis.Kfz AS Kfz, Gemeinde.Name AS Gemeinde, Gemeinde.Gemeindeschluessel, Strasse.Name AS Strasse, Strasse.Ortsteil AS Ortsteil, Strasse.Strassenschluessel
FROM (Land INNER JOIN (Kreis INNER JOIN Gemeinde ON Kreis.ID = Gemeinde.KreisID) ON Land.ID = Kreis.Land) INNER JOIN Strasse ON Gemeinde.ID = Strasse.GemeindeID
/* "Schlüssel"(Land,Kreis,Kfz,Gemeinde,Gemeindeschluessel,Strasse,Ortsteil,Strassenschluessel) */;

/* ========================================
 * Trigger
 * ======================================== */
CREATE TRIGGER UpdateStrasse 
   AFTER UPDATE 
   ON Strasse
BEGIN
 INSERT INTO DataUpdate (Type, GemeindeID, StrasseID, Ortsteil, Strasse, Strassenschluessel)
        VALUES (2, NEW.GemeindeID, NEW.ID, NEW.Ortsteil, NEW.Name, NEW.Strassenschluessel);
END;

CREATE TRIGGER InsertStrasse 
   AFTER INSERT 
   ON Strasse
BEGIN
 INSERT INTO DataUpdate (Type, GemeindeID, StrasseID, Ortsteil, Strasse, Strassenschluessel)
        VALUES (1, NEW.GemeindeID, NEW.ID, NEW.Ortsteil, NEW.Name, NEW.Strassenschluessel);
END;