-- Import the csv into a table of strings.
-- So it can be examined, converted, and cleaned using the power of SQL if needed.

CREATE TABLE [world_data].[dbo].[owid_covid_raw] (
	[iso_code] [nvarchar](50) NULL,
	[continent] [nvarchar](50) NULL,
	[location] [nvarchar](50) NULL,
	[date] [nvarchar](50) NULL,
	[total_cases] [nvarchar](50) NULL,
	[new_cases] [nvarchar](50) NULL,
	[new_cases_smoothed] [nvarchar](50) NULL,
	[total_deaths] [nvarchar](50) NULL,
	[new_deaths] [nvarchar](50) NULL,
	[new_deaths_smoothed] [nvarchar](50) NULL,
	[total_cases_per_million] [nvarchar](50) NULL,
	[new_cases_per_million] [nvarchar](50) NULL,
	[new_cases_smoothed_per_million] [nvarchar](50) NULL,
	[total_deaths_per_million] [nvarchar](50) NULL,
	[new_deaths_per_million] [nvarchar](50) NULL,
	[new_deaths_smoothed_per_million] [nvarchar](50) NULL,
	[reproduction_rate] [nvarchar](50) NULL,
	[icu_patients] [nvarchar](50) NULL,
	[icu_patients_per_million] [nvarchar](50) NULL,
	[hosp_patients] [nvarchar](50) NULL,
	[hosp_patients_per_million] [nvarchar](50) NULL,
	[weekly_icu_admissions] [nvarchar](50) NULL,
	[weekly_icu_admissions_per_million] [nvarchar](50) NULL,
	[weekly_hosp_admissions] [nvarchar](50) NULL,
	[weekly_hosp_admissions_per_million] [nvarchar](50) NULL,
	[total_tests] [nvarchar](50) NULL,
	[new_tests] [nvarchar](50) NULL,
	[total_tests_per_thousand] [nvarchar](50) NULL,
	[new_tests_per_thousand] [nvarchar](50) NULL,
	[new_tests_smoothed] [nvarchar](50) NULL,
	[new_tests_smoothed_per_thousand] [nvarchar](50) NULL,
	[positive_rate] [nvarchar](50) NULL,
	[tests_per_case] [nvarchar](50) NULL,
	[tests_units] [nvarchar](50) NULL,
	[total_vaccinations] [nvarchar](50) NULL,
	[people_vaccinated] [nvarchar](50) NULL,
	[people_fully_vaccinated] [nvarchar](50) NULL,
	[total_boosters] [nvarchar](50) NULL,
	[new_vaccinations] [nvarchar](50) NULL,
	[new_vaccinations_smoothed] [nvarchar](50) NULL,
	[total_vaccinations_per_hundred] [nvarchar](50) NULL,
	[people_vaccinated_per_hundred] [nvarchar](50) NULL,
	[people_fully_vaccinated_per_hundred] [nvarchar](50) NULL,
	[total_boosters_per_hundred] [nvarchar](50) NULL,
	[new_vaccinations_smoothed_per_million] [nvarchar](50) NULL,
	[new_people_vaccinated_smoothed] [nvarchar](50) NULL,
	[new_people_vaccinated_smoothed_per_hundred] [nvarchar](50) NULL,
	[stringency_index] [nvarchar](50) NULL,
	[population] [nvarchar](50) NULL,
	[population_density] [nvarchar](50) NULL,
	[median_age] [nvarchar](50) NULL,
	[aged_65_older] [nvarchar](50) NULL,
	[aged_70_older] [nvarchar](50) NULL,
	[gdp_per_capita] [nvarchar](50) NULL,
	[extreme_poverty] [nvarchar](50) NULL,
	[cardiovasc_death_rate] [nvarchar](50) NULL,
	[diabetes_prevalence] [nvarchar](50) NULL,
	[female_smokers] [nvarchar](50) NULL,
	[male_smokers] [nvarchar](50) NULL,
	[handwashing_facilities] [nvarchar](50) NULL,
	[hospital_beds_per_thousand] [nvarchar](50) NULL,
	[life_expectancy] [nvarchar](50) NULL,
	[human_development_index] [nvarchar](50) NULL,
	[excess_mortality_cumulative_absolute] [nvarchar](50) NULL,
	[excess_mortality_cumulative] [nvarchar](50) NULL,
	[excess_mortality] [nvarchar](50) NULL,
	[excess_mortality_cumulative_per_million] [nvarchar](50) NULL
);
BULK INSERT [world_data].[dbo].[owid_covid_raw]
FROM 'C:\github\world_data\owid_covid\owid-covid-data.csv'  -- <<<<< YOU WILL HAVE TO CHANGE THIS PATH <<<<<
WITH
(
		ROWTERMINATOR = '0x0a',  -- \n DID NOT WORK!!!
		FIELDTERMINATOR = ',',
        FIRSTROW=2,
		MAXERRORS=2
);



-- Create a table with the types we ultimately want

create table [world_data].[dbo].[owid_covid] (
iso_code nvarchar(100),
continent nvarchar(100),
location nvarchar(100),
date date,
total_cases float,
new_cases float,
new_cases_smoothed float,
total_deaths float,
new_deaths float,
new_deaths_smoothed float,
total_cases_per_million float,
new_cases_per_million float,
new_cases_smoothed_per_million float,
total_deaths_per_million float,
new_deaths_per_million float,
new_deaths_smoothed_per_million float,
reproduction_rate float,
icu_patients float,
icu_patients_per_million float,
hosp_patients float,
hosp_patients_per_million float,
weekly_icu_admissions float,
weekly_icu_admissions_per_million float,
weekly_hosp_admissions float,
weekly_hosp_admissions_per_million float,
total_tests float,
new_tests float,
total_tests_per_thousand float,
new_tests_per_thousand float,
new_tests_smoothed float,
new_tests_smoothed_per_thousand float,
positive_rate float,
tests_per_case float,
tests_units nvarchar(100),  -- THIS IS NOT A FLOAT
total_vaccinations float,
people_vaccinated float,
people_fully_vaccinated float,
total_boosters float,
new_vaccinations float,
new_vaccinations_smoothed float,
total_vaccinations_per_hundred float,
people_vaccinated_per_hundred float,
people_fully_vaccinated_per_hundred float,
total_boosters_per_hundred float,
new_vaccinations_smoothed_per_million float,
new_people_vaccinated_smoothed float,
new_people_vaccinated_smoothed_per_hundred float,
stringency_index float,
population float,
population_density float,
median_age float,
aged_65_older float,
aged_70_older float,
gdp_per_capita float,
extreme_poverty float,
cardiovasc_death_rate float,
diabetes_prevalence float,
female_smokers float,
male_smokers float,
handwashing_facilities float,
hospital_beds_per_thousand float,
life_expectancy float,
human_development_index float,
excess_mortality_cumulative_absolute float,
excess_mortality_cumulative float,
excess_mortality float,
excess_mortality_cumulative_per_million float
);

-- Convert strings into the expected types and insert into final table
insert into [world_data].[dbo].[owid_covid]
select 
iso_code,
continent,
location,
convert(date, [date]),
convert(float, total_cases),
convert(float, new_cases),
convert(float, new_cases_smoothed),
convert(float, total_deaths),
convert(float, new_deaths),
convert(float, new_deaths_smoothed),
convert(float, total_cases_per_million),
convert(float, new_cases_per_million),
convert(float, new_cases_smoothed_per_million),
convert(float, total_deaths_per_million),
convert(float, new_deaths_per_million),
convert(float, new_deaths_smoothed_per_million),
convert(float, reproduction_rate),
convert(float, icu_patients),
convert(float, icu_patients_per_million),
convert(float, hosp_patients),
convert(float, hosp_patients_per_million),
convert(float, weekly_icu_admissions),
convert(float, weekly_icu_admissions_per_million),
convert(float, weekly_hosp_admissions),
convert(float, weekly_hosp_admissions_per_million),
convert(float, total_tests),
convert(float, new_tests),
convert(float, total_tests_per_thousand),
convert(float, new_tests_per_thousand),
convert(float, new_tests_smoothed),
convert(float, new_tests_smoothed_per_thousand),
convert(float, positive_rate),
convert(float, tests_per_case),
tests_units,  -- THIS IS NOT A FLOAT
convert(float, total_vaccinations),
convert(float, people_vaccinated),
convert(float, people_fully_vaccinated),
convert(float, total_boosters),
convert(float, new_vaccinations),
convert(float, new_vaccinations_smoothed),
convert(float, total_vaccinations_per_hundred),
convert(float, people_vaccinated_per_hundred),
convert(float, people_fully_vaccinated_per_hundred),
convert(float, total_boosters_per_hundred),
convert(float, new_vaccinations_smoothed_per_million),
convert(float, new_people_vaccinated_smoothed),
convert(float, new_people_vaccinated_smoothed_per_hundred),
convert(float, stringency_index),
convert(float, population),
convert(float, population_density),
convert(float, median_age),
convert(float, aged_65_older),
convert(float, aged_70_older),
convert(float, gdp_per_capita),
convert(float, extreme_poverty),
convert(float, cardiovasc_death_rate),
convert(float, diabetes_prevalence),
convert(float, female_smokers),
convert(float, male_smokers),
convert(float, handwashing_facilities),
convert(float, hospital_beds_per_thousand),
convert(float, life_expectancy),
convert(float, human_development_index),
convert(float, excess_mortality_cumulative_absolute),
convert(float, excess_mortality_cumulative),
convert(float, excess_mortality),
convert(float, excess_mortality_cumulative_per_million)
from [world_data].[dbo].[owid_covid_raw];


-- Check our final data

select * from [world_data].[dbo].[owid_covid];
