import requests
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt

# Fetch data from the non-SSL API (example API URL)
def fetch_data_from_api():
    api_url = "http://example.com/api"  # Replace with your actual non-SSL API URL
    response = requests.get(api_url)
    data = response.json()  # Assumes the response is in JSON format
    return data

# Store data in SQLite
def store_data_in_sqlite(data):
    conn = sqlite3.connect('data.db')
    cursor = conn.cursor()

    # Create table if it does not exist
    cursor.execute('''CREATE TABLE IF NOT EXISTS api_data (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT,
                        value INTEGER)''')

    # Insert data into the database
    for item in data:
        cursor.execute("INSERT INTO api_data (name, value) VALUES (?, ?)", (item['name'], item['value']))
    
    conn.commit()
    conn.close()

# Function to retrieve data for the chart
def get_data_for_chart():
    conn = sqlite3.connect('data.db')
    df = pd.read_sql_query("SELECT name, value FROM api_data", conn)
    conn.close()
    return df

# Function to plot a chart
def plot_chart(df):
    plt.figure(figsize=(8,6))
    plt.bar(df['name'], df['value'])
    plt.xlabel('Name')
    plt.ylabel('Value')
    plt.title('API Data Visualization')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.show()

if __name__ == "__main__":
    # Fetch, store data and visualize
    data = fetch_data_from_api()
    store_data_in_sqlite(data)
    df = get_data_for_chart()
    plot_chart(df)