-- INSERT SQL DB DATA

-- USERS TABLE (DUMMY DATA)
INSERT INTO users (ufname,ulname,udisplay,uemail,uphone,uposition,urole,upassword,ualtuser,uavailable) VALUES 
	('Carlos','Giménez','Carlos A. Giménez','mayor@miamidade.gov','305-375-5071','Mayor','admin','password',2,true),
	('Edward','Marquez','Edward Marquez','deputymayor@miamidade.gov','305-375-6182','Deputy Mayor','manager','password',1,true),
	('Jose','Lopez','Jose Lopez','JLopez@miamidade.gov','305-375-7293','ITD Director','user','password',2,true);	

-- TYPE TABLE (DEFAULT VALUES, these are pre-defined values provided by MDC ITD)
INSERT INTO type VALUES 
	('procurement','ITB','{"Recommendation","Scope","Fiscal Impact/Funding Source","Track Record/Monitor", 
									"Delegated Authority", "Vendors Recommended for Award", "Vendors Not Recommended for Award", 
									"Due Diligence", "Applicable Ordinances and Contract Measures"}'),
	('procurement','RFP','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
									"Delegated Authority", "Vendors Recommended for Award", "Vendors Not Recommended for Award", 
									"Due Diligence", "Applicable Ordinances and Contract Measures"}'),
	('procurement','RTQ','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
									"Delegated Authority", "Vendors Recommended for Award", "Vendors Not Recommended for Award", 
									"Due Diligence", "Applicable Ordinances and Contract Measures"}'),
	('procurement','BW','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
									"Delegated Authority", "Vendors Recommended for Award", "Due Diligence", 
									"Applicable Ordinances and Contract Measures"}'),
	('procurement','SS','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
									"Delegated Authority", "Vendors Recommended for Award", "Due Diligence", 
									"Applicable Ordinances and Contract Measures"}'),
	('procurement','Extensions','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
											"Delegated Authority", "Vendors Recommended for Award", "Due Diligence", 
											"Applicable Ordinances and Contract Measures"}'),
	('procurement','Legacy','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
										"Delegated Authority", "Vendors Recommended for Award", "Due Diligence", 
										"Applicable Ordinances and Contract Measures"}'),
	('procurement','Access','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
										"Delegated Authority", "Vendors Recommended for Award", "Due Diligence", 
										"Applicable Ordinances and Contract Measures"}'),
	('procurement','Contract Modification','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
													  "Delegated Authority", "Awarded Vendor(s)", "Due Diligence", 
													  "Applicable Ordinances and Contract Measures"}'),
	('procurement','Five-year Pool Extensions','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
														  "Delegated Authority", "Pre-qualified Vendors", "Due Diligence", 
														  "Applicable Ordinances and Contract Measures"}'),
	('procurement','OTR','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
									"Delegated Authority", "Awarded Vendor(s)", "Due Diligence", "Applicable Ordinances and Contract Measures"}'),
	('procurement','Emergency','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
										  "Delegated Authority", "Awarded Vendor(s)", "Due Diligence", "Applicable Ordinances and Contract Measures"}'),
	('procurement','Rejection','{"Recommendation", "Scope", "Fiscal Impact/Funding Source", "Track Record/Monitor", 
										  "Delegated Authority", "Awarded Vendor(s)", "Due Diligence", "Applicable Ordinances and Contract Measures"}'),
	('procurement','Supplement','{"body"}'), --Has only a body section
	('procurement','Amendment at Committee','{"note"}'); --A previous item but with an added note detailing what was amended

-- MATTER TABLE (DUMMY DATA, I RECOMMEND USING POSTMAN INSTEAD)
INSERT INTO matter (mttype, mtsubtype, mtname, mttitle, mtuser, mtassignee, mtcreatedate, mtlastedited, mtcontract, mtpriority, mtdeadline, mtsections) VALUES 
    ('procurement','ITB','itb_item1','Title of 1st ITB matter',1,2,'2016-01-01 00:00','2016-01-01 00:00','1000a','LOW','2020-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Vendors Recommended for Award": "content", "Vendors Not Recommended for Award": "content", 
				  "Due Diligence": "content", "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','RFP','rfp_item1','Title of RFP matter',1,3,'2016-01-02 00:00','2016-01-02 00:00','1001a','LOW','2019-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Vendors Recommended for Award": "content", "Vendors Not Recommended for Award": "content", 
				  "Due Diligence": "content", "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','BW','bw_item1','Title of BW matter',2,3,'2016-01-03 00:00','2016-01-03 00:00','1002a','MED','2020-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Vendors Recommended for Award": "content", "Due Diligence": "content", 
	              "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','Legacy','leg_item1','Title of LEGACY matter',2,3,'2016-02-01 00:00','2016-02-01 00:00','1003a','MED','2019-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Vendors Recommended for Award": "content", "Due Diligence": "content", 
	              "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','Contract Modification','cmod_item1','Title of CONTRACT MOD matter',2,1,'2016-02-02 00:00','2016-02-02 00:00','1004a','HIGH','2018-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Awarded Vendor(s)": "content", "Due Diligence": "content", 
	              "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','OTR','otr_item1','Title of OTR matter',3,2,'2016-02-03 00:00','2016-02-03 00:00','1005a','HIGH','2018-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Awarded Vendor(s)": "content", "Due Diligence": "content", 
	              "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','Emergency','emg_item1','Title of EMERGENCY matter',3,1,'2016-03-01 00:00','2016-03-01 00:00','1006a','VERY HIGH','2018-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Awarded Vendor(s)": "content", "Due Diligence": "content", 
	              "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','Rejection','rej_item1','Title of REJECTION matter',1,2,'2016-03-02 00:00','2016-03-02 00:00','1007a','LOW','2020-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Awarded Vendor(s)": "content", "Due Diligence": "content", 
	              "Applicable Ordinances and Contract Measures": "content"}'),
	('procurement','ITB','itb_item2','Title of 2nd ITB matter',3,2,'2016-03-03 00:00','2016-03-03 00:00','1008a','MED','2019-12-31',
				'{"Recommendation": "content", "Scope": "content", "Fiscal Impact/Funding Source": "content", 
				  "Track Record/Monitor": "content", "Delegated Authority": "content", 
				  "Vendors Recommended for Award": "content", "Vendors Not Recommended for Award": "content", 
				  "Due Diligence": "content", "Applicable Ordinances and Contract Measures": "content"}');
	
--- [POST SUBMIT NEW ITEM] INSERT MATTER WITH POSTMAN USING JSON SO THAT IT CREATES PDF, UPLOADS, AND ADDS TO HISTORY TABLE TOO
{
	"mttype": "procurement",
	"mtsubtype":"ITB",
	"mtname":"itb0",
	"mttitle":"Recommendation for Approval to Award: LED Lights",
	"mtcontract":"10000",
	"mtuser":1,
	"mtassignee":2,
	"mtlastuser":1,
	"mtpriority":"HIGH",
	"mtdeadline":"2018-12-31",
	"mtsections": {
		"Recommendation": "It is recommended that the Board of County Commissioners (Board) approve award of Contract No. FB-00000, LED Lights, to purchase light-emitting diode (LED) lights for the Department of Transportation and Public Works. The lights will be installed in exterior light ficutres, ceiling and wall mounts at 15 Metrorail stations with the most need throughout the County (Attachment A), ensuring welll-lit stations for patrons. A comprehensive project for the remaining stations, as well as other transit facilities, will be solicited separately. LED lights last longer and are more energy-efficient than traditional lighting sources. The lights are UL-certified (i.e. meet specific safety standards). The new lights will replace outdated mercury vaporlights that have exceeded their useful life of five (5) years. The useful life for the new LED lights is estimated to be 10 years.", 
		"Scope": "The impact of this item is countrywide in nature.", 
		"Fiscal Impact/Funding Source": "The fiscal impact for the one-year term is $2,000,000. <Insert table here>", 
		"Track Record/Monitor": "Jesus Lee of the Interval Services Department is the Procurement Contracting Officer.", 
		"Delegated Authority": "If this item is approved, the County Mayor or County Mayors designee will have the authority to exervcice all provisions of the contract pursuant to Section 2-8.1 of the County Code and Implementing Order 3-38", 
		"Vendors Recommended for Award": "An invitation to Bid was issued under full and open competition on March 24, 2016. There were 13 responses to the solicitation, including four (4) No Bids. The method of award was to the single lowest-priced responsive, responsible bidder in the aggregate. Seven (7) of the bids were deemed non-responsive by the Country Attorneys Office for failure to comply with Federal Transit Administration Buy America requirements. <Insert table here>", 
		"Vendors Not Recommended for Award": "<Insert table here>", 
		"Due Diligence": "Pursuant to Resolution No. R-187-12, due diligence was conducted in accordance with the Internal Services Departments Procurement Guidelines to determine contractor responsibility, including verifying corporate status and that there are no performacnce or compliance issues with the recommended vendor. The lists that were refverenced include: convicted records and federal excluded parties. There was no adverse findings relating to contractor responsibility.", 
		"Applicable Ordinances and Contract Measures": "* The two (2) percent User Access Program provision does not apply due to the federal funding source. * The Small Business Enterprise Bid Preference and Local Preference ordinances do not apply due to the federal funding source. * The Living Wage ordinance does not apply."
	}
}
{
	"mttype": "procurement",
	"mtsubtype":"Emergency",
	"mtname":"emg1",
	"mttitle":"Recommendation to Ratify Emergency Contract Award: Riot Control",
	"mtcontract":"10013",
	"mtuser":1,
	"mtassignee":2,
	"mtlastuser":1,
	"mtpriority":"HIGH",
	"mtdeadline":"2019-11-29",
	"mtsections": {
		"Recommendation": "It is recommended that the Board of County Commissioners (Board) approve award of Contract No. FB-00000, LED Lights, to purchase light-emitting diode (LED) lights for the Department of Transportation and Public Works. The lights will be installed in exterior light ficutres, ceiling and wall mounts at 15 Metrorail stations with the most need throughout the County (Attachment A), ensuring welll-lit stations for patrons. A comprehensive project for the remaining stations, as well as other transit facilities, will be solicited separately. LED lights last longer and are more energy-efficient than traditional lighting sources. The lights are UL-certified (i.e. meet specific safety standards). The new lights will replace outdated mercury vaporlights that have exceeded their useful life of five (5) years. The useful life for the new LED lights is estimated to be 10 years.", 
		"Scope": "The impact of this item is countrywide in nature.", 
		"Fiscal Impact/Funding Source": "The fiscal impact for the one-year term is $2,000,000. <Insert table here>", 
		"Track Record/Monitor": "Jesus Lee of the Interval Services Department is the Procurement Contracting Officer.", 
		"Delegated Authority": "If this item is approved, the County Mayor or County Mayors designee will have the authority to exervcice all provisions of the contract pursuant to Section 2-8.1 of the County Code and Implementing Order 3-38", 
		"Awarded Vendor(s)": "An invitation to Bid was issued under full and open competition on March 24, 2016. There were 13 responses to the solicitation, including four (4) No Bids. The method of award was to the single lowest-priced responsive, responsible bidder in the aggregate. Seven (7) of the bids were deemed non-responsive by the Country Attorneys Office for failure to comply with Federal Transit Administration Buy America requirements. <Insert table here>", 
		"Due Diligence": "Pursuant to Resolution No. R-187-12, due diligence was conducted in accordance with the Internal Services Departments Procurement Guidelines to determine contractor responsibility, including verifying corporate status and that there are no performacnce or compliance issues with the recommended vendor. The lists that were refverenced include: convicted records and federal excluded parties. There was no adverse findings relating to contractor responsibility.", 
		"Applicable Ordinances and Contract Measures": "* The two (2) percent User Access Program provision does not apply due to the federal funding source. * The Small Business Enterprise Bid Preference and Local Preference ordinances do not apply due to the federal funding source. * The Living Wage ordinance does not apply."
	}
}

