
CREATE TABLE "Claim_Headers" (
	"Claim_Header_ID" INTEGER NOT NULL, 
	"Claim_Status_Code" CHAR(15) NOT NULL, 
	"Claim_Type_Code" CHAR(15) NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_of_Claim" DATETIME, 
	"Date_of_Settlement" DATETIME, 
	"Amount_Claimed" DECIMAL(20, 4), 
	"Amount_Piad" DECIMAL(20, 4), 
	PRIMARY KEY ("Claim_Header_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Policies" ("Policy_ID")
)


CREATE TABLE "Claims_Documents" (
	"Claim_ID" INTEGER NOT NULL, 
	"Document_Type_Code" CHAR(15) NOT NULL, 
	"Created_by_Staff_ID" INTEGER, 
	"Created_Date" INTEGER, 
	PRIMARY KEY ("Claim_ID", "Document_Type_Code"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claim_Headers" ("Claim_Header_ID"), 
	FOREIGN KEY("Created_by_Staff_ID") REFERENCES "Staff" ("Staff_ID")
)


CREATE TABLE "Claims_Processing" (
	"Claim_Processing_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER NOT NULL, 
	"Claim_Outcome_Code" CHAR(15) NOT NULL, 
	"Claim_Stage_ID" INTEGER NOT NULL, 
	"Staff_ID" INTEGER, 
	PRIMARY KEY ("Claim_Processing_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claim_Headers" ("Claim_Header_ID"), 
	FOREIGN KEY("Staff_ID") REFERENCES "Staff" ("Staff_ID")
)


CREATE TABLE "Claims_Processing_Stages" (
	"Claim_Stage_ID" INTEGER NOT NULL, 
	"Next_Claim_Stage_ID" INTEGER, 
	"Claim_Status_Name" VARCHAR(255) NOT NULL, 
	"Claim_Status_Description" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Claim_Stage_ID")
)


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Customer_ID")
)


CREATE TABLE "Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_Type_Code" CHAR(15) NOT NULL, 
	"Start_Date" DATETIME, 
	"End_Date" DATETIME, 
	PRIMARY KEY ("Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID")
)


CREATE TABLE "Staff" (
	"Staff_ID" INTEGER NOT NULL, 
	"Staff_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Staff_ID")
)

CREATE TABLE "Claim_Headers" (
	"Claim_Header_ID" INTEGER NOT NULL, 
	"Claim_Status_Code" CHAR(15) NOT NULL, 
	"Claim_Type_Code" CHAR(15) NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_of_Claim" DATETIME, 
	"Date_of_Settlement" DATETIME, 
	"Amount_Claimed" DECIMAL(20, 4), 
	"Amount_Piad" DECIMAL(20, 4), 
	PRIMARY KEY ("Claim_Header_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Policies" ("Policy_ID")
)

/*
2 rows from Claim_Headers table:
Claim_Header_ID	Claim_Status_Code	Claim_Type_Code	Policy_ID	Date_of_Claim	Date_of_Settlement	Amount_Claimed	Amount_Piad
15	Settled	Handphone Subsidy	518	2016-05-31 06:07:11	2018-02-23 03:46:38	349.1500	582.0300
24	Disputed	Child Birth	518	2016-12-30 21:43:21	2017-10-08 21:43:14	318.1600	309.2000
*/


CREATE TABLE "Claims_Documents" (
	"Claim_ID" INTEGER NOT NULL, 
	"Document_Type_Code" CHAR(15) NOT NULL, 
	"Created_by_Staff_ID" INTEGER, 
	"Created_Date" INTEGER, 
	PRIMARY KEY ("Claim_ID", "Document_Type_Code"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claim_Headers" ("Claim_Header_ID"), 
	FOREIGN KEY("Created_by_Staff_ID") REFERENCES "Staff" ("Staff_ID")
)

/*
2 rows from Claims_Documents table:
Claim_ID	Document_Type_Code	Created_by_Staff_ID	Created_Date
24	Document	718	8
27	Document	986	6
*/


CREATE TABLE "Claims_Processing" (
	"Claim_Processing_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER NOT NULL, 
	"Claim_Outcome_Code" CHAR(15) NOT NULL, 
	"Claim_Stage_ID" INTEGER NOT NULL, 
	"Staff_ID" INTEGER, 
	PRIMARY KEY ("Claim_Processing_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claim_Headers" ("Claim_Header_ID"), 
	FOREIGN KEY("Staff_ID") REFERENCES "Staff" ("Staff_ID")
)

/*
2 rows from Claims_Processing table:
Claim_Processing_ID	Claim_ID	Claim_Outcome_Code	Claim_Stage_ID	Staff_ID
118	28	In progress	1	771
145	62	In progress	1	589
*/


CREATE TABLE "Claims_Processing_Stages" (
	"Claim_Stage_ID" INTEGER NOT NULL, 
	"Next_Claim_Stage_ID" INTEGER, 
	"Claim_Status_Name" VARCHAR(255) NOT NULL, 
	"Claim_Status_Description" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Claim_Stage_ID")
)

/*
2 rows from Claims_Processing_Stages table:
Claim_Stage_ID	Next_Claim_Stage_ID	Claim_Status_Name	Claim_Status_Description
1	1	Open	Open a new claim
3	1	Close	Close a claim
*/


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Customer_ID")
)

/*
2 rows from Customers table:
Customer_ID	Customer_Details
252	America Jaskolski
263	Ellsworth Paucek
*/


CREATE TABLE "Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_Type_Code" CHAR(15) NOT NULL, 
	"Start_Date" DATETIME, 
	"End_Date" DATETIME, 
	PRIMARY KEY ("Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID")
)

/*
2 rows from Policies table:
Policy_ID	Customer_ID	Policy_Type_Code	Start_Date	End_Date
125	808	Deputy	2018-02-10 08:56:30	2018-03-18 09:17:26
151	380	Jurisdiction	2017-12-20 06:02:31	2017-09-16 22:04:13
*/


CREATE TABLE "Staff" (
	"Staff_ID" INTEGER NOT NULL, 
	"Staff_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Staff_ID")
)

/*
2 rows from Staff table:
Staff_ID	Staff_Details
406	Clifton
427	Cathryn
*/
Find the description of the claim status "Open".
SELECT claim_status_description FROM claims_processing_stages WHERE claim_status_name  =  "Open"
[{'Claim_Status_Description': 'Open a new claim'}]