import mysql.connector
import time
import random
from datetime import datetime
from tgtg import TgtgClient

# Database connection details
db_config = {
    'user': 'mwfmhkkx_sveis',
    'password': 'sF*!uN9H986w',
    'host': 'localhost:3306',
    'database': 'mwfmhkkx_tgtg'
}

# Initialize TgtgClient
client = TgtgClient(email="sveinung@hartberg.no")



def create_connection():
    """Create a connection to the MySQL database."""
    return mysql.connector.connect(**db_config)

def save_items_to_db(items):
    """Insert new items into the database if they have changed since last fetch."""
    conn = create_connection()
    cursor = conn.cursor()

    for result in items:
        item_data = result['item']
        store_data = result['store']
        
        # Extract relevant fields
        item_id = item_data['item_id']
        item_name = item_data.get('name', '')
        item_description = item_data.get('description', '')
        item_category = item_data.get('item_category', '')
        price_including_taxes = item_data['price_including_taxes']['minor_units'] / (10 ** item_data['price_including_taxes']['decimals'])
        currency_code = item_data['price_including_taxes']['code']
        value_excluding_taxes = item_data['value_excluding_taxes']['minor_units'] / (10 ** item_data['value_excluding_taxes']['decimals'])
        value_including_taxes = item_data['value_including_taxes']['minor_units'] / (10 ** item_data['value_including_taxes']['decimals'])
        items_available = result['items_available']
        
        # Store details
        store_id = store_data['store_id']
        store_name = store_data['store_name']
        store_address = store_data['store_location']['address']['address_line']
        store_latitude = store_data['store_location']['location']['latitude']
        store_longitude = store_data['store_location']['location']['longitude']
        distance = result['distance']
        favorite = result['favorite']

        # Check if the item already exists and has the same available quantity
        cursor.execute("""
            SELECT items_available FROM items
            WHERE item_id = %s
            ORDER BY last_fetched DESC LIMIT 1
        """, (item_id,))
        
        last_entry = cursor.fetchone()

        # Insert new data only if items_available has changed
        if not last_entry or last_entry[0] != items_available:
            cursor.execute("""
                INSERT INTO items (
                    item_id, item_name, item_description, item_category,
                    price_including_taxes, currency_code, value_excluding_taxes,
                    value_including_taxes, items_available, store_id, store_name,
                    store_address, store_latitude, store_longitude, distance,
                    favorite, last_fetched
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                item_id, item_name, item_description, item_category,
                price_including_taxes, currency_code, value_excluding_taxes,
                value_including_taxes, items_available, store_id, store_name,
                store_address, store_latitude, store_longitude, distance,
                favorite, datetime.now()
            ))

    conn.commit()
    cursor.close()
    conn.close()

def fetch_items():
    """Fetch items from TGTG API using TgtgClient."""
    return client.get_items()

def run_app():
    """Main loop to fetch items and save to DB at intervals."""
    while True:
        # Fetch items from API
        items = fetch_items()
        save_items_to_db(items)
        
        # Wait 3 minutes ± 2 minutes
        interval = 180 + random.randint(-120, 120)
        time.sleep(interval)

if __name__ == "__main__":
    run_app()