CREATE TABLE "Depot" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "code" TEXT,
  "description" TEXT,
  "icon" TEXT,
  "url" TEXT,
  "adress_1" TEXT,
  "adress_2" TEXT,
  "city" TEXT,
  "zip" TEXT,
  "country" TEXT
);

CREATE TABLE "Documenttype" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT UNIQUE NOT NULL,
  "code" TEXT UNIQUE,
  "description" TEXT,
  "icon" TEXT
);

CREATE TABLE "Document" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "private" BOOLEAN,
  "icon" TEXT,
  "url" TEXT,
  "cote" TEXT,
  "notesearch" TEXT,
  "sortorder" INTEGER,
  "depot_id" INTEGER NOT NULL REFERENCES "Depot" ("id"),
  "documenttype_id" INTEGER NOT NULL REFERENCES "Documenttype" ("id"),
  "datecreated" DATETIME
);

CREATE INDEX "idx_document__depot_id" ON "Document" ("depot_id");

CREATE INDEX "idx_document__documenttype_id" ON "Document" ("documenttype_id");

CREATE TABLE "Eventtype" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT UNIQUE NOT NULL,
  "code" TEXT UNIQUE,
  "category" TEXT NOT NULL,
  "description" TEXT,
  "icon" TEXT
);

CREATE TABLE "Metadata" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "code" TEXT UNIQUE,
  "value" TEXT NOT NULL,
  "type" TEXT,
  "description" TEXT
);

CREATE TABLE "Person" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "key" TEXT UNIQUE,
  "description" TEXT,
  "firstname" TEXT NOT NULL,
  "lastname" TEXT NOT NULL,
  "private" BOOLEAN,
  "icon" TEXT,
  "sex" TEXT NOT NULL,
  "notesearch" TEXT,
  "firstnameprefix" TEXT,
  "firtsnameothers" TEXT,
  "lastnamesuffix" TEXT,
  "surname" TEXT,
  "datecreated" DATETIME
);

CREATE TABLE "Place" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "private" BOOLEAN,
  "icon" TEXT,
  "adress1" TEXT,
  "adress2" TEXT,
  "zipcode" TEXT,
  "city" TEXT,
  "cityinsee" INTEGER,
  "country" TEXT,
  "url" TEXT,
  "accurate" TEXT,
  "gpslat" DECIMAL(16, 16),
  "gpslon" DECIMAL(16, 16),
  "zoom" INTEGER,
  "datecreated" DATETIME
);

CREATE TABLE "Event" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "private" BOOLEAN,
  "icon" TEXT,
  "date_begin" DATETIME,
  "date_begin_format" TEXT,
  "date_begin_accurate" TEXT,
  "notesearch" TEXT,
  "sortorder" INTEGER,
  "person_1_id" INTEGER NOT NULL REFERENCES "Person" ("id"),
  "person_2_id" INTEGER REFERENCES "Person" ("id") ON DELETE SET NULL,
  "person_3_id" INTEGER REFERENCES "Person" ("id") ON DELETE SET NULL,
  "eventtype_id" INTEGER NOT NULL REFERENCES "Eventtype" ("id"),
  "place_id" INTEGER NOT NULL REFERENCES "Place" ("id"),
  "datecreated" DATETIME
);

CREATE INDEX "idx_event__eventtype_id" ON "Event" ("eventtype_id");

CREATE INDEX "idx_event__person_1_id" ON "Event" ("person_1_id");

CREATE INDEX "idx_event__person_2_id" ON "Event" ("person_2_id");

CREATE INDEX "idx_event__person_3_id" ON "Event" ("person_3_id");

CREATE INDEX "idx_event__place_id" ON "Event" ("place_id");

CREATE TABLE "Media" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "url" TEXT NOT NULL,
  "datemedia" DATETIME,
  "datemedia_format" TEXT,
  "datemedia_accurate" TEXT,
  "description" TEXT,
  "dimension" TEXT,
  "origin" TEXT,
  "private" BOOLEAN,
  "notesearch" TEXT,
  "sortorder" INTEGER,
  "keyword" TEXT,
  "documenttype_id" INTEGER NOT NULL REFERENCES "Documenttype" ("id") ON DELETE CASCADE,
  "depot_id" INTEGER NOT NULL REFERENCES "Depot" ("id") ON DELETE CASCADE,
  "event_id" INTEGER REFERENCES "Event" ("id") ON DELETE SET NULL,
  "place_id" INTEGER REFERENCES "Place" ("id") ON DELETE SET NULL,
  "datecreated" DATETIME
);

CREATE INDEX "idx_media__depot_id" ON "Media" ("depot_id");

CREATE INDEX "idx_media__documenttype_id" ON "Media" ("documenttype_id");

CREATE INDEX "idx_media__event_id" ON "Media" ("event_id");

CREATE INDEX "idx_media__place_id" ON "Media" ("place_id");

CREATE TABLE "Media_person" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "media_id" INTEGER NOT NULL REFERENCES "Media" ("id") ON DELETE CASCADE,
  "person_id" INTEGER NOT NULL REFERENCES "Person" ("id") ON DELETE CASCADE,
  "datecreated" DATETIME
);

CREATE INDEX "idx_media_person__media_id" ON "Media_person" ("media_id");

CREATE INDEX "idx_media_person__person_id" ON "Media_person" ("person_id");

CREATE TABLE "Source" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "description" TEXT,
  "private" BOOLEAN,
  "icon" TEXT,
  "quality" INTEGER,
  "localurl" TEXT,
  "sourceurl" TEXT,
  "sourcetext" TEXT,
  "page" TEXT,
  "citation" TEXT,
  "textdraduction" TEXT,
  "notesearch" TEXT,
  "sortorder" INTEGER,
  "datecreated" DATETIME
);

CREATE TABLE "Event_source" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "event_id" INTEGER NOT NULL REFERENCES "Event" ("id") ON DELETE CASCADE,
  "source_id" INTEGER NOT NULL REFERENCES "Source" ("id") ON DELETE CASCADE,
  "document_id" INTEGER REFERENCES "Document" ("id") ON DELETE SET NULL,
  "datecreated" DATETIME
);

CREATE INDEX "idx_event_source__document_id" ON "Event_source" ("document_id");

CREATE INDEX "idx_event_source__event_id" ON "Event_source" ("event_id");

CREATE INDEX "idx_event_source__source_id" ON "Event_source" ("source_id")