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,
"Template_ID" INT,
"Document_Name" 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) UNIQUE,
"Template_Type_Description" VARCHAR(255),
PRIMARY KEY ("Template_Type_Code")
);

CREATE TABLE "Templates" (
"Template_ID" INT UNIQUE,
"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 "Ref_Template_Types" VALUES ("LEGAL_DOC", "Legal Document");
INSERT INTO "Ref_Template_Types" VALUES ("HR_POLICY", "Human Resources Policy");
INSERT INTO "Ref_Template_Types" VALUES ("FIN_REPORT", "Financial Report");
INSERT INTO "Ref_Template_Types" VALUES ("TECH_SPEC", "Technical Specification");
INSERT INTO "Ref_Template_Types" VALUES ("MKT_PLAN", "Marketing Plan");
INSERT INTO "Templates" VALUES ("101", "LEGAL_DOC", "Contract agreement template for clients");
INSERT INTO "Templates" VALUES ("102", "LEGAL_DOC", "Non-disclosure agreement template");
INSERT INTO "Templates" VALUES ("201", "HR_POLICY", "Employee leave policy template");
INSERT INTO "Templates" VALUES ("202", "HR_POLICY", "Code of conduct template");
INSERT INTO "Templates" VALUES ("301", "FIN_REPORT", "Quarterly financial summary template");
INSERT INTO "Templates" VALUES ("302", "FIN_REPORT", "Annual budget report template");
INSERT INTO "Templates" VALUES ("401", "TECH_SPEC", "Software requirements specification");
INSERT INTO "Templates" VALUES ("402", "TECH_SPEC", "Hardware installation guide");
INSERT INTO "Templates" VALUES ("501", "MKT_PLAN", "Social media marketing plan");
INSERT INTO "Templates" VALUES ("502", "MKT_PLAN", "Product launch marketing plan");
INSERT INTO "Documents" VALUES ("1", "101", "Client Contract 2023");
INSERT INTO "Documents" VALUES ("2", "101", "Vendor Contract 2023");
INSERT INTO "Documents" VALUES ("3", "102", "NDA for Project X");
INSERT INTO "Documents" VALUES ("4", "201", "Employee Leave Policy 2023");
INSERT INTO "Documents" VALUES ("5", "202", "Code of Conduct 2023");
INSERT INTO "Documents" VALUES ("6", "202", "Updated Code of Conduct 2024");
INSERT INTO "Documents" VALUES ("7", "301", "Q1 Financial Summary 2023");
INSERT INTO "Documents" VALUES ("8", "301", "Q2 Financial Summary 2023");
INSERT INTO "Documents" VALUES ("9", "302", "Annual Budget 2023");
INSERT INTO "Documents" VALUES ("10", "401", "Software Spec v1.0");
INSERT INTO "Documents" VALUES ("11", "401", "Software Spec v1.1");
INSERT INTO "Documents" VALUES ("12", "402", "Hardware Guide Rev A");
INSERT INTO "Documents" VALUES ("13", "501", "Social Media Plan Q3");
INSERT INTO "Documents" VALUES ("14", "502", "Product Launch Plan 2023");
INSERT INTO "Documents" VALUES ("15", "502", "Product Launch Plan 2024");
INSERT INTO "Documents" VALUES ("16", "502", "Product Launch Plan 2025");
INSERT INTO "Paragraphs" VALUES ("1", "This document outlines the terms and conditions of the contract.");
INSERT INTO "Paragraphs" VALUES ("2", "Employees are entitled to 20 days of paid leave annually.");
INSERT INTO "Paragraphs" VALUES ("3", "The financial report summarizes the quarterly earnings.");
INSERT INTO "Paragraphs" VALUES ("4", "This template is used for marketing campaign planning.");
INSERT INTO "Paragraphs" VALUES ("5", "Technical specifications include system requirements and design.");
INSERT INTO "Paragraphs" VALUES ("6", "Confidentiality clauses are included in this NDA template.");
INSERT INTO "Paragraphs" VALUES ("7", "The code of conduct defines acceptable workplace behavior.");
INSERT INTO "Paragraphs" VALUES ("8", "Installation instructions for hardware components.");
INSERT INTO "Paragraphs" VALUES ("9", "Social media strategy focuses on engagement and growth.");
INSERT INTO "Paragraphs" VALUES ("10", "Budget allocations for different departments are detailed here.");