`
-- FP Task 01 Data import
DROP SCHEMA IF EXISTS pandemic;
CREATE SCHEMA IF NOT EXISTS pandemic;
USE pandemic;

-- FP Task 02 Normalized forms
DROP TABLE IF EXISTS entities;
CREATE TABLE IF NOT EXISTS entities (
  entity_ID INT PRIMARY KEY AUTO_INCREMENT,
  entity_Code VARCHAR(8),
  Entity VARCHAR(255)
);

INSERT INTO entities (entity_Code, Entity)
(SELECT DISTINCT Code, Entity
FROM infectious_cases);

DROP TABLE IF EXISTS infect_types;
CREATE TABLE IF NOT EXISTS infect_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entity_ID INT,
    Year YEAR,
    Number_yaws VARCHAR(45),
    polio_cases INT,
    cases_guinea_worm INT,
    Number_rabies VARCHAR(45),
    Number_malaria VARCHAR(45),
    Number_hiv VARCHAR(45),
    Number_tuberculosis VARCHAR(45),
    Number_smallpox VARCHAR(45),
    Number_cholera_cases VARCHAR(45),
    FOREIGN KEY (entity_ID)
        REFERENCES entities (entity_ID)
);

INSERT INTO infect_types (entity_ID, Year, Number_yaws, polio_cases, cases_guinea_worm, Number_rabies, Number_malaria, Number_hiv, Number_tuberculosis, Number_smallpox, Number_cholera_cases)
SELECT
    e.entity_ID,
    ic.Year,
    ic.Number_yaws,
    ic.polio_cases,
    ic.cases_guinea_worm,
    ic.Number_rabies,
    ic.Number_malaria,
    ic.Number_hiv,
    ic.Number_tuberculosis,
    ic.Number_smallpox,
    ic.Number_cholera_cases
FROM
    infectious_cases AS ic
    JOIN entities AS e ON ic.Entity = e.Entity AND ic.Code = e.entity_Code;

DESCRIBE infect_types;
    
-- FP Task 03 Data analysis
SELECT
    DISTINCT entity_ID,
    AVG(CAST(Number_rabies AS FLOAT)) AS avgCases_rabies,
    MIN(CAST(Number_rabies AS FLOAT)) AS minCases_rabies,
    MAX(CAST(Number_rabies AS FLOAT)) AS maxCases_rabies,
    SUM(CAST(Number_rabies AS FLOAT)) AS sumCases_rabies
FROM (
        SELECT *
        FROM infect_types
        WHERE Number_rabies <> ''
    ) AS filtered_data
GROUP BY entity_ID;

-- FP Task 03.1, 03.2 Sort by AVG DESC, LIMIT 10
SELECT
    e.entity_ID,
    e.entity_Code,
    AVG(CAST(t.Number_rabies AS DECIMAL(7,2))) AS avgCases_rabies,
    MIN(CAST(t.Number_rabies AS DECIMAL(7,2))) AS minCases_rabies,
    MAX(CAST(t.Number_rabies AS DECIMAL(7,2))) AS maxCases_rabies,
    SUM(CAST(t.Number_rabies AS DECIMAL(7,2))) AS sumCases_rabies
FROM infect_types AS t
JOIN entities AS e ON t.entity_ID = e.entity_ID
WHERE t.Number_rabies <> ''
GROUP BY e.entity_ID, e.entity_Code
ORDER BY avgCases_rabies DESC
LIMIT 10;

-- FP Task 04 Date functions
SELECT
    t.entity_ID,
    MAKEDATE(t.Year, 1) AS Date_case,
    TIMESTAMPDIFF(YEAR, MAKEDATE(t.Year, 1), CURDATE()) AS Years_from_now,
    SUM(CAST(t.Number_rabies AS DECIMAL(7,2))) AS Rabies_case_per_year
FROM infect_types t
WHERE t.Number_rabies <> ''
GROUP BY t.entity_ID, Date_case, Years_from_now;
    
-- FP Task 05 Create the function
DELIMITER //
CREATE FUNCTION calculate_years(Input_year INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE years_diff INT;
    SET years_diff = TIMESTAMPDIFF(YEAR, MAKEDATE(Input_year, 1), CURDATE());
    RETURN years_diff;
END//
DELIMITER ;

SELECT
    t.entity_ID,
    MAKEDATE(t.Year, 1) AS Date_case,
    calculate_years(t.Year) AS Years_from_now,
    SUM(CAST(t.Number_rabies AS DECIMAL(7,2))) AS Rabies_case_per_year
FROM infect_types t
WHERE t.Number_rabies <> ''
GROUP BY t.entity_ID, Date_case, Years_from_now;

-- FP Task 05.1    
DELIMITER //
CREATE FUNCTION calculate_cases(year_case INT, divisor INT)
RETURNS decimal(7,2)
DETERMINISTIC
BEGIN
    DECLARE cases_per_period FLOAT;
    SET cases_per_period = CAST(year_case / divisor AS FLOAT);
    RETURN cases_per_period;
END//
DELIMITER ;

SELECT 
    t.entity_ID,
    MAKEDATE(t.Year, 1) AS Date,
    calculate_cases(CAST(t.Number_rabies AS FLOAT),
            12) AS CasesPerMonth,
    calculate_cases(CAST(t.Number_rabies AS FLOAT),
            4) AS CasesPerQuarter,
    calculate_cases(CAST(t.Number_rabies AS FLOAT),
            2) AS CasesPerSemester
FROM
    infect_types t
WHERE
        CAST(t.Number_rabies AS FLOAT) IS NOT NULL;
 `