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),
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_Type_Code" CHAR(15),
"Template_Details" VARCHAR(255),
PRIMARY KEY ("Template_ID"),
FOREIGN KEY ("Template_Type_Code") REFERENCES "Ref_Template_Types"("Template_Type_Code")
);





INSERT INTO "Documents" VALUES ("1", "Welcome to NY");
INSERT INTO "Documents" VALUES ("2", "Introduction to LA");
INSERT INTO "Documents" VALUES ("3", "Welcome to SF");
INSERT INTO "Documents" VALUES ("4", "NYC Travel Guide");
INSERT INTO "Documents" VALUES ("5", "Welcome to NY - Extended");
INSERT INTO "Paragraphs" VALUES ("101", "1", "Welcome to New York City, the city that never sleeps.");
INSERT INTO "Paragraphs" VALUES ("102", "1", "Explore the iconic landmarks like the Statue of Liberty and Central Park.");
INSERT INTO "Paragraphs" VALUES ("103", "1", "Enjoy the diverse food scene and vibrant nightlife.");
INSERT INTO "Paragraphs" VALUES ("104", "2", "Los Angeles is known for its entertainment industry.");
INSERT INTO "Paragraphs" VALUES ("105", "3", "San Francisco offers beautiful views and tech innovation.");
INSERT INTO "Paragraphs" VALUES ("106", "4", "NYC Travel Guide covers transportation and accommodation.");
INSERT INTO "Paragraphs" VALUES ("107", "5", "Welcome to NY extended edition with more tips.");
INSERT INTO "Paragraphs" VALUES ("108", "5", "Discover hidden gems and local favorites.");
INSERT INTO "Paragraphs" VALUES ("109", "1", "Don't miss Broadway shows and museums.");
INSERT INTO "Ref_Template_Types" VALUES ("TPL001", "Welcome Letter");
INSERT INTO "Ref_Template_Types" VALUES ("TPL002", "Travel Guide");
INSERT INTO "Ref_Template_Types" VALUES ("TPL003", "Information Sheet");
INSERT INTO "Templates" VALUES ("201", "TPL001", "Standard welcome letter template");
INSERT INTO "Templates" VALUES ("202", "TPL002", "Detailed travel guide template");
INSERT INTO "Templates" VALUES ("203", "TPL003", "General information sheet template");