import time
import requests
import json
import pymysql
from datetime import datetime

# Function to fetch data from BLS API
def fetch_data(series_ids):
    headers = {'Content-type': 'application/json'}
    data = json.dumps({"seriesid": series_ids, "startyear": "2024", "endyear": "2024"})
    try:
        response = requests.post('https://api.bls.gov/publicAPI/v2/timeseries/data/', data=data, headers=headers)
        response.raise_for_status()  # Raise exception for HTTP errors
        return json.loads(response.text)
    except requests.exceptions.RequestException as e:
        return None

# Function to convert BLS period format to datetime
def convert_date(year, period):
    date_string = f"{year}/{period.replace('M', '')}/01"
    return datetime.strptime(date_string, '%Y/%m/%d')

# Function to insert data into MySQL database
def insert_data(cursor, table_name, date, name, value):
    cursor.execute(
        f"INSERT INTO {table_name}(date, name, value) VALUES (%s, %s, %s)",
        (date, name, value)
    )

# Function to process series data and insert into the database
def process_series_data(json_data, table_name, names):
    db = pymysql.connect(host="127.0.0.1",user="root",password="2GS@bPYcgiMyL14A",database="Macroeconomics",port=4423)

    cursor = db.cursor()

    for i, series in enumerate(json_data['Results']['series']):
        for data_point in sorted(series['data'], key=lambda x: (x['year'], x['period'])):
            year = data_point['year']
            period = data_point['period']
            value = data_point['value']
            date = convert_date(year, period)
            name = names[i] if i < len(names) else f"Unknown {i}"

            cursor.execute(f"SELECT COUNT(*) FROM {table_name} WHERE date = %s AND name = %s", (date, name))
            if cursor.fetchone()[0] == 0:
                insert_data(cursor, table_name, date, name, value)
                db.commit()

    db.close()

# Function to merge JSON data
def merge_json_data(json_data_list):
    merged_series = []
    for json_data in json_data_list:
        if json_data and 'Results' in json_data and 'series' in json_data['Results']:
            merged_series.extend(json_data['Results']['series'])
    return {'Results': {'series': merged_series}}

# Main script logic
while True:
    series_ids1 = [
        'CUUR0000SA0', 'CUUR0000SAF1', 'CUUR0000SAF11', 'CUUR0000SAF111', 'CUUR0000SAF112', 'CUUR0000SEFJ',
        'CUUR0000SAF113', 'CUUR0000SAF114', 'CUUR0000SEFV', 'CUUR0000SA0E', 'CUUR0000SACE', 'CUUR0000SEHE01',
        'CUUR0000SETB', 'CUUR0000SETB01', 'CUUR0000SEHF', 'CUUR0000SEHF01', 'CUUR0000SEHF02'
    ]
    series_ids2 = [
        'CUUR0000SA0L1E', 'CUUR0000SACL1E', 'CUUR0000SAA', 'CUUR0000SETA01', 'CUUR0000SETA02', 'CUUR0000SAM1',
        'CUUR0000SAF116', 'CUUR0000SEGA', 'CUUR0000SASLE', 'CUUR0000SAH1', 'CUUR0000SEHA', 'CUUR0000SEHC',
        'CUUR0000SAM2', 'CUUR0000SEMC01', 'CUUR0000SEMD01', 'CUUR0000SAS4', 'CUUR0000SETD', 'CUUR0000SETE',
        'CUUR0000SETG01'
    ]
    series_ids3 = [s.replace('CUUR', 'CUSR') for s in series_ids1]
    series_ids4 = [s.replace('CUUR', 'CUSR') for s in series_ids2]

    json_data1 = fetch_data(series_ids1)
    json_data2 = fetch_data(series_ids2)
    json_data3 = fetch_data(series_ids3)
    json_data4 = fetch_data(series_ids4)

    combined_json_data_NSA = merge_json_data([json_data1, json_data2])
    combined_json_data_SA = merge_json_data([json_data3, json_data4])

    names = [
        'All items', 'Food', 'Food at home', 'Cereals and bakery products', 'Meats, poultry, fish, and eggs',
        'Dairy and related products', 'Fruits and vegetables', 'Nonalcoholic beverages and beverage materials',
        'Food away from home', 'Energy', 'Energy commodities', 'Fuel oil', 'Motor fuel', 'Gasoline (all types)',
        'Energy services', 'Electricity', 'Utility (piped) gas service', 'All items less food and energy',
        'Commodities less food and energy commodities', 'Apparel', 'New vehicles', 'Used cars and trucks',
        'Medical care commodities', 'Alcoholic beverages', 'Tobacco and smoking products',
        'Services less energy services', 'Shelter', 'Rent of primary residence', "Owners equivalent rent of residences",
        'Medical care services', "Physicians services", 'Hospital services', 'Transportation services',
        'Motor vehicle maintenance and repair', 'Motor vehicle insurance', 'Airline fares'
    ]

    if combined_json_data_NSA and 'Results' in combined_json_data_NSA and 'series' in combined_json_data_NSA['Results']:
        process_series_data(combined_json_data_NSA, 'CPI_NSA', names)

    if combined_json_data_SA and 'Results' in combined_json_data_SA and 'series' in combined_json_data_SA['Results']:
        process_series_data(combined_json_data_SA, 'CPI_SA', names)

    time.sleep(86400)