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),
"Document_Description" VARCHAR(255),
PRIMARY KEY ("Document_ID")
);

CREATE TABLE "Paragraphs" (
"Paragraph_ID" INT,
"Document_ID" INT,
"Paragraph_Text" VARCHAR(255),
PRIMARY KEY ("Paragraph_ID"),
FOREIGN KEY ("Document_ID") REFERENCES "Documents"("Document_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,
"Template_Details" VARCHAR(255),
PRIMARY KEY ("Template_ID")
);





INSERT INTO "Documents" VALUES ("1", "Customer reviews", "Compilation of customer feedback and testimonials");
INSERT INTO "Documents" VALUES ("2", "Product specs", "Detailed specifications of products");
INSERT INTO "Documents" VALUES ("3", "Customer reviews 2023", "Customer reviews collected in 2023");
INSERT INTO "Documents" VALUES ("4", "Empty document", "Document with no paragraphs");
INSERT INTO "Documents" VALUES ("5", "Customer reviews", "Another document with the same name but different content");
INSERT INTO "Paragraphs" VALUES ("101", "1", "The product quality exceeded my expectations.");
INSERT INTO "Paragraphs" VALUES ("102", "1", "Customer service was prompt and helpful.");
INSERT INTO "Paragraphs" VALUES ("103", "1", "");
INSERT INTO "Paragraphs" VALUES ("104", "1", "Delivery was delayed but the packaging was excellent.");
INSERT INTO "Paragraphs" VALUES ("105", "3", "2023 reviews show increased satisfaction.");
INSERT INTO "Paragraphs" VALUES ("106", "5", "This is a different 'Customer reviews' document with unique feedback.");
INSERT INTO "Paragraphs" VALUES ("107", "5", "Some paragraphs have very long text to test system limits. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.");
INSERT INTO "Paragraphs" VALUES ("108", "2", "Product dimensions: 10x20x30 cm.");
INSERT INTO "Paragraphs" VALUES ("109", "2", "Weight: 2.5 kg.");
INSERT INTO "Paragraphs" VALUES ("110", "5", "");
INSERT INTO "Ref_Template_Types" VALUES ("TT001", "Review Templates");
INSERT INTO "Ref_Template_Types" VALUES ("TT002", "Specification Templates");
INSERT INTO "Templates" VALUES ("201", "Standard review template with rating and comments");
INSERT INTO "Templates" VALUES ("202", "Detailed product specification template");