import numpy as np
import pandas as pd
import sqlite3   # Library required to read db file
import matplotlib.pyplot as plt

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
"""
## Step 1: Download the data

We first need to download the latest version of the database from the dropbox folder into the local directory. We will run all our queries against this local db file
"""
!curl -L -o "/kaggle/working/covid-india.db" "https://www.dropbox.com/s/hbe04q6vtzapdam/covid-india.db?dl=1"
"""
## Step 2: Access the data

Having downloaded a local copy of the remote data, we can now run SQL queries against it. 

The Wiki here [https://github.com/IBM/covid19-india-data/wiki/States](https://github.com/IBM/covid19-india-data/wiki/States) provides the schema (tables, and columns) of the database
"""
def run_query(querystr: str) -> list:
    con = sqlite3.connect('/kaggle/working/covid-india.db')
    cursor = con.cursor()
    cursor.execute(querystr)
    data = cursor.fetchall()
    con.close()
    return data
"""
## Analysis 1: Daily new cases
"""
querystr = 'SELECT D1.date AS date, D1.cases_positive as DL, K1.positive_cases AS KL, K2.cases_new AS KA ' + \
            'FROM DL_case_info D1 JOIN KL_daily_summary K1 on D1.date == K1.date ' + \
            'JOIN KA_case_info K2 ON D1.date == K2.date ORDER BY date'

data = run_query(querystr)   # the returned data is a list of tuples (date, DL_occupancy, WB_occupancy)

data = pd.DataFrame(data, columns=['Date', 'DL', 'KL', 'KA'])
data.head()
data.plot(x='Date', title='Daily new confirmed cases')
"""
## Analysis 2: COVID-19 Bed Occupancy
"""
querystr = 'SELECT D1.date, D2.hospital_beds_occupied * 100.0 / D2.hospital_beds_total AS DL_occupancy, ' + \
            'W1.covid19_bed_occupancy AS WB_occupancy FROM DL_case_info D1 JOIN DL_patient_mgmt D2 ON D1.date == D2.date ' + \
            'JOIN WB_hospital W1 ON D1.date == W1.date ORDER BY D1.date ASC'

data = run_query(querystr)   # the returned data is a list of tuples (date, DL_occupancy, WB_occupancy)

data = pd.DataFrame(data, columns=['Date', 'DL_occupancy', 'WB_occupancy'])
data.head()
data.plot(x='Date', title='COVID-19 Bed Occupancy in DL and WB')
"""
**This notebook demonstrated a simple workflow of loading the database and executing queries against it to extract the data. You can use this notebook as reference to run more complex queries against the database and model/analyse/visualize various aspects of the data**
"""