PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "Documents" (
"Document_ID" INT UNIQUE,
"Document_Name" VARCHAR(255),
PRIMARY KEY ("Document_ID")
);

CREATE TABLE "Paragraphs" (
"Paragraph_ID" INT,
"Document_ID" INT,
"Paragraph_Text" VARCHAR(255),
"Other_Details" VARCHAR(255),
PRIMARY KEY ("Paragraph_ID"),
FOREIGN KEY ("Document_ID") REFERENCES "Documents"("Document_ID")
);

CREATE TABLE "Templates" (
"Template_ID" INT,
"Template_Type_Code" CHAR(15),
"Template_Details" VARCHAR(255),
PRIMARY KEY ("Template_ID")
);





INSERT INTO "Documents" VALUES ("1", "Global Affairs Report");
INSERT INTO "Documents" VALUES ("2", "Asia Economic Review");
INSERT INTO "Documents" VALUES ("3", "Technology Insights");
INSERT INTO "Documents" VALUES ("4", "Travel Guide");
INSERT INTO "Documents" VALUES ("5", "Historical Archives");
INSERT INTO "Paragraphs" VALUES ("101", "1", "Korea", "Summary of Korea's geopolitical status");
INSERT INTO "Paragraphs" VALUES ("102", "1", "Korea", "Note missing trailing space");
INSERT INTO "Paragraphs" VALUES ("103", "2", "Korea", "Economic growth analysis");
INSERT INTO "Paragraphs" VALUES ("104", "2", "Korea.", "With punctuation");
INSERT INTO "Paragraphs" VALUES ("105", "3", "South Korea", "Technology sector overview");
INSERT INTO "Paragraphs" VALUES ("106", "3", "Korea", "Duplicate exact text for edge case");
INSERT INTO "Paragraphs" VALUES ("107", "4", "", "Empty paragraph text");
INSERT INTO "Paragraphs" VALUES ("108", "4", "NULL", "Null paragraph text");
INSERT INTO "Paragraphs" VALUES ("109", "5", "Korea - historical context", "Similar but not exact text");
INSERT INTO "Paragraphs" VALUES ("110", "5", "Korea", "Another exact match with trailing space");
INSERT INTO "Templates" VALUES ("201", "TYPE_A", "Template for geopolitical reports");
INSERT INTO "Templates" VALUES ("202", "TYPE_B", "Template for economic reviews");
INSERT INTO "Templates" VALUES ("203", "TYPE_C", "Template for technology articles");
INSERT INTO "Templates" VALUES ("204", "TYPE_D", "Template for travel guides");
INSERT INTO "Templates" VALUES ("205", "TYPE_E", "Template for historical archives");