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,
"Template_ID" INT,
"Document_Name" VARCHAR(255),
"Document_Description" VARCHAR(255),
PRIMARY KEY ("Document_ID"),
FOREIGN KEY ("Template_ID") REFERENCES "Templates"("Template_ID")
);

CREATE TABLE "Paragraphs" (
"Paragraph_ID" INT,
"Paragraph_Text" VARCHAR(255),
PRIMARY KEY ("Paragraph_ID")
);

CREATE TABLE "Ref_Template_Types" (
"Template_Type_Code" CHAR(15),
"Template_Type_Description" VARCHAR(255),
PRIMARY KEY ("Template_Type_Code")
);

CREATE TABLE "Templates" (
"Template_ID" INT UNIQUE,
"Template_Details" VARCHAR(255),
PRIMARY KEY ("Template_ID")
);





INSERT INTO "Templates" VALUES ("1", "Standard Template");
INSERT INTO "Templates" VALUES ("2", "Warranty Template");
INSERT INTO "Templates" VALUES ("3", "Weekly Report Template");
INSERT INTO "Templates" VALUES ("4", "Workflow Template");
INSERT INTO "Templates" VALUES ("5", "Warranty and Service Template");
INSERT INTO "Templates" VALUES ("6", "Special Template with W");
INSERT INTO "Templates" VALUES ("7", "Template for New Workflows");
INSERT INTO "Documents" VALUES ("101", "1", "Doc Alpha", "This document covers the workflow process.");
INSERT INTO "Documents" VALUES ("102", "2", "Doc Beta", "Warranty details are included here.");
INSERT INTO "Documents" VALUES ("103", "3", "Doc Gamma", "Weekly summary and updates.");
INSERT INTO "Documents" VALUES ("104", "4", "Doc Delta", "Overview of the system.");
INSERT INTO "Documents" VALUES ("105", "5", "Doc Epsilon", "Warranty service and support.");
INSERT INTO "Documents" VALUES ("106", "6", "Doc Zeta", "Special W-case testing.");
INSERT INTO "Documents" VALUES ("107", "7", "Doc Eta", "New workflows: guidelines.");
INSERT INTO "Documents" VALUES ("108", "1", "Doc Theta", "No relevant letter here.");
INSERT INTO "Documents" VALUES ("109", "2", "Doc Iota", "w is at the start.");
INSERT INTO "Documents" VALUES ("110", "3", "Doc Kappa", "Ends with w.");
INSERT INTO "Documents" VALUES ("111", "4", "Doc Lambda", "Punctuation around w, like w!");
INSERT INTO "Documents" VALUES ("112", "5", "Doc Mu", "Multiple Ws in this W document.");
INSERT INTO "Documents" VALUES ("113", "6", "Doc Nu", "Mixed case W and w in description.");
INSERT INTO "Documents" VALUES ("114", "7", "Doc Xi", "No letter of interest.");
INSERT INTO "Documents" VALUES ("115", "1", "Doc Omicron", "Contains a W, surrounded by commas, w, and periods.");
INSERT INTO "Documents" VALUES ("116", "2", "Doc Pi", "Contains no relevant letter.");
INSERT INTO "Documents" VALUES ("117", "3", "Doc Rho", "w@orkflow and W@orking cases.");
INSERT INTO "Documents" VALUES ("118", "4", "Doc Sigma", "Whitespace and w placement.");
INSERT INTO "Documents" VALUES ("119", "5", "Doc Tau", "No w here.");
INSERT INTO "Documents" VALUES ("120", "6", "Doc Upsilon", "W is here, and w is there.");
INSERT INTO "Documents" VALUES ("121", "7", "Doc Phi", "Document without the letter.");
INSERT INTO "Documents" VALUES ("122", "1", "Doc Chi", "w-w-w hyphenated.");
INSERT INTO "Documents" VALUES ("123", "2", "Doc Psi", "W? Yes, W!");
INSERT INTO "Documents" VALUES ("124", "3", "Doc Omega", "Ends with a W.");
INSERT INTO "Paragraphs" VALUES ("201", "Paragraph text not relevant for this query.");
INSERT INTO "Paragraphs" VALUES ("202", "Another paragraph text.");
INSERT INTO "Paragraphs" VALUES ("203", "Yet another paragraph.");
INSERT INTO "Paragraphs" VALUES ("204", "Paragraphs are not needed here.");
INSERT INTO "Paragraphs" VALUES ("205", "Just filler text.");
INSERT INTO "Ref_Template_Types" VALUES ("TT01", "Standard");
INSERT INTO "Ref_Template_Types" VALUES ("TT02", "Warranty");
INSERT INTO "Ref_Template_Types" VALUES ("TT03", "Weekly");
INSERT INTO "Ref_Template_Types" VALUES ("TT04", "Workflow");
INSERT INTO "Ref_Template_Types" VALUES ("TT05", "Special");