
CREATE TABLE "Available_Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	policy_type_code CHAR(15), 
	"Customer_Phone" VARCHAR(255), 
	PRIMARY KEY ("Policy_ID"), 
	UNIQUE ("Policy_ID")
)


CREATE TABLE "Claims" (
	"Claim_ID" INTEGER NOT NULL, 
	"FNOL_ID" INTEGER NOT NULL, 
	"Effective_Date" DATE, 
	PRIMARY KEY ("Claim_ID"), 
	FOREIGN KEY("FNOL_ID") REFERENCES "First_Notification_of_Loss" ("FNOL_ID"), 
	UNIQUE ("Claim_ID")
)


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_name" VARCHAR(40), 
	PRIMARY KEY ("Customer_ID")
)


CREATE TABLE "Customers_Policies" (
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_Opened" DATE, 
	"Date_Closed" DATE, 
	PRIMARY KEY ("Customer_ID", "Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Available_Policies" ("Policy_ID")
)


CREATE TABLE "First_Notification_of_Loss" (
	"FNOL_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Service_ID" INTEGER NOT NULL, 
	PRIMARY KEY ("FNOL_ID"), 
	FOREIGN KEY("Service_ID") REFERENCES "Services" ("Service_ID"), 
	FOREIGN KEY("Customer_ID", "Policy_ID") REFERENCES "Customers_Policies" ("Customer_ID", "Policy_ID"), 
	UNIQUE ("FNOL_ID")
)


CREATE TABLE "Services" (
	"Service_ID" INTEGER NOT NULL, 
	"Service_name" VARCHAR(40), 
	PRIMARY KEY ("Service_ID")
)


CREATE TABLE "Settlements" (
	"Settlement_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER, 
	"Effective_Date" DATE, 
	"Settlement_Amount" REAL, 
	PRIMARY KEY ("Settlement_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claims" ("Claim_ID"), 
	UNIQUE ("Settlement_ID")
)

CREATE TABLE "Available_Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	policy_type_code CHAR(15), 
	"Customer_Phone" VARCHAR(255), 
	PRIMARY KEY ("Policy_ID"), 
	UNIQUE ("Policy_ID")
)

/*
2 rows from Available_Policies table:
Policy_ID	policy_type_code	Customer_Phone
246	Life Insurance	+16(2)5838999222
257	Property Insurance	242.763.9214
*/


CREATE TABLE "Claims" (
	"Claim_ID" INTEGER NOT NULL, 
	"FNOL_ID" INTEGER NOT NULL, 
	"Effective_Date" DATE, 
	PRIMARY KEY ("Claim_ID"), 
	FOREIGN KEY("FNOL_ID") REFERENCES "First_Notification_of_Loss" ("FNOL_ID"), 
	UNIQUE ("Claim_ID")
)

/*
2 rows from Claims table:
Claim_ID	FNOL_ID	Effective_Date
134	1722	1973-08-18
145	1611	2014-10-19
*/


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_name" VARCHAR(40), 
	PRIMARY KEY ("Customer_ID")
)

/*
2 rows from Customers table:
Customer_ID	Customer_name
194	America Jaskolski
214	Ellsworth Paucek
*/


CREATE TABLE "Customers_Policies" (
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_Opened" DATE, 
	"Date_Closed" DATE, 
	PRIMARY KEY ("Customer_ID", "Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Available_Policies" ("Policy_ID")
)

/*
2 rows from Customers_Policies table:
Customer_ID	Policy_ID	Date_Opened	Date_Closed
214	257	2016-11-19	2018-03-04
214	301	2016-04-12	2018-02-07
*/


CREATE TABLE "First_Notification_of_Loss" (
	"FNOL_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Service_ID" INTEGER NOT NULL, 
	PRIMARY KEY ("FNOL_ID"), 
	FOREIGN KEY("Service_ID") REFERENCES "Services" ("Service_ID"), 
	FOREIGN KEY("Customer_ID", "Policy_ID") REFERENCES "Customers_Policies" ("Customer_ID", "Policy_ID"), 
	UNIQUE ("FNOL_ID")
)

/*
2 rows from First_Notification_of_Loss table:
FNOL_ID	Customer_ID	Policy_ID	Service_ID
532	214	257	6
1611	996	993	9
*/


CREATE TABLE "Services" (
	"Service_ID" INTEGER NOT NULL, 
	"Service_name" VARCHAR(40), 
	PRIMARY KEY ("Service_ID")
)

/*
2 rows from Services table:
Service_ID	Service_name
1	New policy application
4	Close a policy
*/


CREATE TABLE "Settlements" (
	"Settlement_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER, 
	"Effective_Date" DATE, 
	"Settlement_Amount" REAL, 
	PRIMARY KEY ("Settlement_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claims" ("Claim_ID"), 
	UNIQUE ("Settlement_ID")
)

/*
2 rows from Settlements table:
Settlement_ID	Claim_ID	Effective_Date	Settlement_Amount
161	717	2009-11-20	6451.65
176	641	1971-06-29	1588.45
*/
List all the policy types used by the customer enrolled in the most policies.
SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id JOIN available_policies AS t3 ON t2.policy_id  =  t3.policy_id WHERE t1.customer_name  =  (SELECT t1.customer_name FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_name ORDER BY count(*) DESC LIMIT 1)
[{'policy_type_code': 'Mortgage Insurance'}, {'policy_type_code': 'Property Insurance'}]