CREATE TABLE "Map" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL
);

CREATE TABLE "Category" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT UNIQUE,
  "valid" INTEGER DEFAULT 0,
  "checked" INTEGER,
  "externaldata" INTEGER,
  "form" TEXT NOT NULL,
  "iconurl" TEXT,
  "owner" TEXT NOT NULL,
  "map_id" INTEGER NOT NULL REFERENCES "Map" ("id") ON DELETE CASCADE
);

CREATE INDEX "idx_category__map_id" ON "Category" ("map_id");

CREATE TABLE "Marker" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "key" TEXT,
  "name" TEXT NOT NULL,
  "type" INTEGER NOT NULL,
  "description" TEXT,
  "lat" DECIMAL(12, 2) NOT NULL,
  "lon" DECIMAL(12, 2) NOT NULL,
  "alt" DECIMAL(12, 2),
  "url" TEXT,
  "iconurl" TEXT,
  "photoprefix" TEXT,
  "datemaj" TEXT,
  "category_id" INTEGER NOT NULL REFERENCES "Category" ("id") ON DELETE CASCADE,
  "datecreated" DATETIME
);

CREATE INDEX "idx_marker__category_id" ON "Marker" ("category_id");

CREATE TABLE "Propertyref" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "category_id" INTEGER NOT NULL REFERENCES "Category" ("id") ON DELETE CASCADE
);

CREATE INDEX "idx_propertyref__category_id" ON "Propertyref" ("category_id");

CREATE TABLE "Propertylib" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "propertyref_id" INTEGER NOT NULL REFERENCES "Propertyref" ("id"),
  "libid" INTEGER,
  "Lib" TEXT
);

CREATE INDEX "idx_propertylib__propertyref_id" ON "Propertylib" ("propertyref_id");

CREATE TABLE "Propertyval" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "marker_id" INTEGER NOT NULL REFERENCES "Marker" ("id"),
  "propertyref_id" INTEGER NOT NULL REFERENCES "Propertyref" ("id") ON DELETE CASCADE,
  "value" TEXT NOT NULL,
  "datecreated" DATETIME
);

CREATE INDEX "idx_propertyval__marker_id" ON "Propertyval" ("marker_id");

CREATE INDEX "idx_propertyval__propertyref_id" ON "Propertyval" ("propertyref_id")