import ssl
import sqlite3
import urllib.request
import os
import csv

DB_PATH = os.path.join(os.path.dirname(__file__), "localfood.db")

def get_db_connection():
    conn = sqlite3.connect(DB_PATH, timeout=20.0, check_same_thread=False)
    conn.execute('pragma journal_mode=wal')
    return conn

def fetch_and_seed():
    ctx = ssl.create_default_context()
    ctx.check_hostname = False
    ctx.verify_mode = ssl.CERT_NONE

    print("Loading fundamental internal CSV dataset...")
    csv_path = os.path.join(os.path.dirname(__file__), "nutrition.csv")
    
    try:
        with open(csv_path, 'r', encoding='utf-8') as f:
            lines = f.readlines()
    except Exception as e:
        print(f"Failed to read local dataset: {e}")
        return
    
    print(f"Dataset downloaded. Mapping data into localfood.db...")
    conn = get_db_connection()
    cursor = conn.cursor()

    reader = csv.reader(lines)
    try:
        headers = next(reader)
    except StopIteration:
        print("Empty dataset.")
        return
    
    def get_val(row, target_names, default="0"):
        for t_name in target_names:
            for i, h in enumerate(headers):
                if t_name.lower() in h.lower():
                    # Remove non-numeric characters like "g" or "mg" but keep decimals
                    val = ''.join(c for c in row[i] if c.isdigit() or c == '.')
                    return val if val else default
        return default

    count = 0
    for row in reader:
        if len(row) < 3: continue
        
        # In this dataset, index 0 is always the food name
        name = row[0]
        
        try:
            calories = float(get_val(row, ['calorie', 'energy']))
            protein = float(get_val(row, ['protein']))
            fat = float(get_val(row, ['total_fat', 'fat_g', 'fat']))
            carbs = float(get_val(row, ['carbohydrate', 'carb']))
            fiber = float(get_val(row, ['fiber']))
            sugar = float(get_val(row, ['sugar']))
            sodium_mg = float(get_val(row, ['sodium']))
            
            vitamin_a = float(get_val(row, ['vitamin_a', 'vita']))
            vitamin_c = float(get_val(row, ['vitamin_c', 'vitc']))
            calcium = float(get_val(row, ['calcium']))
            iron = float(get_val(row, ['iron']))
            potassium = float(get_val(row, ['potassium']))
            cholesterol = float(get_val(row, ['cholesterol']))
        except ValueError:
            continue
        
        cursor.execute("SELECT id FROM foods WHERE name = ?", (name[:100],))
        if cursor.fetchone():
            continue

        cursor.execute('''
            INSERT INTO foods (name, category, calories, protein_g, fat_g, carbs_g, fiber_g, sugar_g, sodium_mg, vitamin_a_iu, vitamin_c_mg, calcium_mg, iron_mg, potassium_mg, cholesterol_mg, source)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            name[:100],
            "Sourced Ingredient",
            calories,
            protein,
            fat,
            carbs,
            fiber,
            sugar,
            sodium_mg,
            vitamin_a,
            vitamin_c,
            calcium,
            iron,
            potassium,
            cholesterol,
            'Sourced_CSV'
        ))
        count += 1
        if count >= 8000:  
            break

    conn.commit()
    conn.close()
    print(f"Successfully seeded {count} foundational macro tracking items into localfood.db!")

if __name__ == '__main__':
    fetch_and_seed()