CREATE TABLE "KanjiVG_Version" (
  "version" VARCHAR(10) PRIMARY KEY NOT NULL,
  "date" DATE NOT NULL,
  "hash" VARCHAR(64) NOT NULL
) WITHOUT ROWID;

CREATE TRIGGER "KanjiVG_Version_SingleRow"
BEFORE INSERT ON "KanjiVG_Version"
WHEN (SELECT COUNT(*) FROM "KanjiVG_Version") >= 1
BEGIN
    SELECT RAISE(FAIL, 'Only one row allowed in KanjiVG_Version');
END;

CREATE TABLE "KanjiVG_Entry" (
  "character" CHAR(1) PRIMARY KEY NOT NULL
) WITHOUT ROWID;

CREATE TABLE "KanjiVG_StrokeNumber" (
  "character" CHAR(1) NOT NULL REFERENCES "KanjiVG_Entry"("character"),
  "strokeNum" INTEGER NOT NULL,
  "x" REAL NOT NULL,
  "y" REAL NOT NULL,
  PRIMARY KEY ("character", "strokeNum"),
  FOREIGN KEY ("character", "strokeNum") REFERENCES "KanjiVG_Path"("character", "pathId")
) WITHOUT ROWID;

CREATE TABLE "KanjiVG_Path" (
  "character" CHAR(1) NOT NULL REFERENCES "KanjiVG_Entry"("character"),
  "pathId" INTEGER NOT NULL,
  "groupId" INTEGER NOT NULL,
  "type" VARCHAR(10),
  "svgPath" TEXT NOT NULL,
  PRIMARY KEY ("character", "pathId"),
  FOREIGN KEY ("character", "groupId") REFERENCES "KanjiVG_PathGroup"("character", "groupId")
) WITHOUT ROWID;

CREATE TABLE "KanjiVG_PathGroup" (
  "character" CHAR(1) NOT NULL REFERENCES "KanjiVG_Entry"("character"),
  "groupId" INTEGER NOT NULL,
  "parentGroupId" INTEGER,
  "element" TEXT,
  "original" TEXT,
  "position" VARCHAR(6),
  "radical" TEXT,
  "part" INTEGER,
  PRIMARY KEY ("character", "groupId"),
  CHECK (
    "position" IN (
      'upperA',
      'upperB',
      'lower1',
      'lower2',
      'bottom',
      'kamae',
      'kamaec',
      'left',
      'middle',
      'nyo',
      'nyoc',
      'right',
      'tare',
      'tarec',
      'top'
    )
    OR
    "position" IS NULL
  ),
  FOREIGN KEY ("character", "parentGroupId") REFERENCES "KanjiVG_PathGroup"("character", "groupId")
) WITHOUT ROWID;
