# --- Data handling & processing ---
import pandas as pd
import base64
import requests
import os
import json
import http.client
from io import StringIO
import yaml  # To set up credentials in a YAML file

# --- Display utilities ---
from IPython.display import Image, display, HTML

# --- Visualization libraries ---
import matplotlib.pyplot as plt
from matplotlib.colors import LinearSegmentedColormap
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go

# --- External process handling ---
import subprocess

# --- Dataset access (UCI ML Repository) Library to download UCI Machine Learning Laboratory's Air Quality Dataset ---
import ucimlrepo
from ucimlrepo import fetch_ucirepo

config = {
    "cloud_url": "https://[host]:[port]/griddb/v2/[clustername]/dbs/[database_name]/",
    "cloud_username": "XXXXX",
    "cloud_pass": "XXXXX"
}

yaml_path = os.path.expanduser("~/.griddb.yaml")
with open(yaml_path, 'w') as file:
    yaml.dump(config, file)

# fetch dataset
dataset = fetch_ucirepo(id=360)
df = pd.concat([dataset.data.features, dataset.data.targets], axis=1)
#print(df.head())

#Creating a datetime column for use in a container
df['datetime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'])


# Renaming columns 
df = df.rename(columns={
    'CO(GT)': 'co_gt',
    'PT08.S1(CO)': 'pt08_s1_co',
    'NMHC(GT)': 'nmhc_gt',
    'C6H6(GT)': 'c6h6_gt',
    'PT08.S2(NMHC)': 'pt08_s2_nmhc',
    'NOx(GT)': 'nox_gt',
    'PT08.S3(NOx)': 'pt08_s3_nox',
    'NO2(GT)': 'no2_gt',
    'PT08.S4(NO2)': 'pt08_s4_no2',
    'PT08.S5(O3)': 'pt08_s5_o3',
    'T': 't',
    'RH': 'rh',
    'AH': 'ah'
})

# Formatting the datetime column as per GridDB requirements
df['datetime'] = pd.to_datetime(df['Date'] + ' ' + df['Time'], errors='coerce')
#df['datetime'] = df['datetime'].dt.strftime('%Y-%m-%dT%H:%M:%S')
df['datetime'] = pd.to_datetime(df['datetime']).dt.strftime('%Y-%m-%dT%H:%M:%S.%f').str[:-3] + 'Z'

cli_path = "C:\\Users\\...\\griddb-cloud-cli.exe"

def run_select(query):
    cmd = f"{cli_path} sql query -s \"{query}\" --pretty"
    result = subprocess.run(
        cmd,
        shell=True,
        capture_output=True,
        text=True
    )
    if result.returncode != 0:
        print("Query failed")
        print("STDERR:", result.stderr)
    else:
        print("Query succeeded")
        print(result.stdout)

cli_path = "C:\\Users\\...\\griddb-cloud-cli.exe"

# Single-line SQL query string
query = "SELECT EXTRACT(HOUR, datetime) AS hour, AVG(co_gt) AS avg_co FROM air_quality_data GROUP BY hour ORDER BY hour"

result = subprocess.run(
    [cli_path, 'sql', 'query', '-s', query, '--pretty'],
    capture_output=True,
    text=True
)

# Debug outputs
#print("STDOUT:\n", result.stdout)
#print("STDERR:\n", result.stderr)

# Parse if there's valid JSON
try:
    parts = result.stdout.split('\n', 1)
    if len(parts) == 2:
        data_json = json.loads(parts[1])
        rows = []
        for record in data_json:
            row = {field["Name"]: field["Value"] for field in record}
            rows.append(row)
        avg_co_levels = pd.DataFrame(rows)
        #print(avg_co_levels.head())
    else:
        print("Unexpected format in stdout.")
except json.JSONDecodeError as e:
    print("JSON parsing failed:", str(e))

cli_path = "C:\\Users\\...\\griddb-cloud-cli.exe"

query = (
    "SELECT "
    "EXTRACT(YEAR, datetime) AS year, "
    "EXTRACT(MONTH, datetime) AS month, "
    "EXTRACT(DAY, datetime) AS day, "
    "AVG(no2_gt) AS avg_no2, "
    "AVG(nox_gt) AS avg_nox "
    "FROM air_quality_data "
    "GROUP BY year, month, day "
    "ORDER BY year, month, day"
)

result = subprocess.run(
    [cli_path, 'sql', 'query', '-s', query, '--pretty'],
    capture_output=True,
    text=True
)

# Debug outputs
#print("STDOUT:\n", result.stdout)
#print("STDERR:\n", result.stderr)

# Parse if there's valid JSON
try:
    parts = result.stdout.split('\n', 1)
    if len(parts) == 2:
        data_json = json.loads(parts[1])
        rows = []
        for record in data_json:
            row = {field["Name"]: field["Value"] for field in record}
            rows.append(row)
        daily_no_levels = pd.DataFrame(rows)
        #print(avg_co_levels.head())
    else:
        print("Unexpected format in stdout.")
except json.JSONDecodeError as e:
    print("JSON parsing failed:", str(e))

daily_no_levels["act_date"] = pd.to_datetime(daily_no_levels[["year", "month", "day"]])

cli_path = "C:\\Users\\...\\griddb-cloud-cli.exe"

query = (
    "SELECT "
    "EXTRACT(YEAR, datetime) AS year, "
    "EXTRACT(MONTH, datetime) AS month, "
    "EXTRACT(DAY, datetime) AS day, "
    "AVG(CAST(c6h6_gt AS DOUBLE)) AS avg_benzene, "
    "AVG(CAST(nmhc_gt AS DOUBLE)) AS avg_nmhc "
    "FROM air_quality_data "
    "WHERE c6h6_gt > 10 AND nmhc_gt > 250 "
    "AND c6h6_gt IS NOT NULL " 
    "AND nmhc_gt IS NOT NULL "
    "GROUP BY year, month, day "
    "ORDER BY avg_benzene DESC "
    "LIMIT 10"
)


result = subprocess.run(
    [cli_path, 'sql', 'query', '-s', query, '--pretty'],
    capture_output=True,
    text=True
)

#print(result)

# Debug outputs
#print("STDOUT:\n", result.stdout)
#print("STDERR:\n", result.stderr)

# Parse if there's valid JSON
try:
    parts = result.stdout.split('\n', 1)
    if len(parts) == 2:
        data_json = json.loads(parts[1])
        rows = []
        for record in data_json:
            row = {field["Name"]: field["Value"] for field in record}
            rows.append(row)
        pollution_spikes = pd.DataFrame(rows)
        #print(pollution_spikes.head())
    else:
        print("Unexpected format in stdout.")
except json.JSONDecodeError as e:
    print("JSON parsing failed:", str(e))

pollution_spikes["act_date"] = pd.to_datetime(pollution_spikes[["year", "month", "day"]])

cli_path = "C:\\Users\\...\\griddb-cloud-cli.exe"

query = ("SELECT "
        "EXTRACT(YEAR, datetime) AS year, "
        "EXTRACT(MONTH, datetime) AS month, "
        "CASE " 
        "WHEN EXTRACT(MONTH, datetime) IN (6, 7, 8) THEN 'Summer' "
        "WHEN EXTRACT(MONTH, datetime) IN (12, 1, 2) THEN 'Winter' "
        "ELSE 'Other' "
        "END AS season, "
        "AVG(CAST(co_gt AS DOUBLE)) AS avg_co, "
        "AVG(CAST(nox_gt AS DOUBLE)) AS avg_nox, "
        "AVG(CAST(no2_gt AS DOUBLE)) AS avg_no2, "        
        "AVG(CAST(pt08_s1_co AS DOUBLE)) AS avg_s1_co, "
        "AVG(CAST(nmhc_gt AS DOUBLE)) AS avg_nmhc, "
        "AVG(CAST(pt08_s2_nmhc AS DOUBLE)) AS avg_s2_nmhc, "
        "AVG(CAST(pt08_s3_nox AS DOUBLE)) AS avg_s3_nox, "
        "AVG(CAST(pt08_s4_no2 AS DOUBLE)) AS avg_s4_no2, "
        "AVG(CAST(pt08_s5_o3 AS DOUBLE)) AS avg_s5_o3 "
        "FROM air_quality_data "
        "WHERE EXTRACT(MONTH, datetime) IN (6, 7, 8, 12, 1, 2) "
        "GROUP BY year, month, season "
        "ORDER BY year, month; "
)

result = subprocess.run(
    [cli_path, 'sql', 'query', '-s', query, '--pretty'],
    capture_output=True,
    text=True
)

#print(result)

# Debug outputs
#print("STDOUT:\n", result.stdout)
#print("STDERR:\n", result.stderr)

# Parse if there's valid JSON
try:
    parts = result.stdout.split('\n', 1)
    if len(parts) == 2:
        data_json = json.loads(parts[1])
        rows = []
        for record in data_json:
            row = {field["Name"]: field["Value"] for field in record}
            rows.append(row)
        sensor_calibration = pd.DataFrame(rows)
        #print(sensor_calibration.head())
    else:
        print("Unexpected format in stdout.")
except json.JSONDecodeError as e:
    print("JSON parsing failed:", str(e))