import csv
from pathlib import Path

# Read the CSV file
csv_file = Path(r'c:\Users\Dr Deviil\Downloads\nepali_food_database_improved.csv')
foods = []

with open(csv_file, 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        foods.append(row)

print(f"Parsed {len(foods)} food items from CSV")

# Create SQL file for live database upload
sql_file = Path(r'a:/merobody/backend/fitnep_api/nutrition/nutrition_database.sql')
sql_file.parent.mkdir(parents=True, exist_ok=True)

with open(sql_file, 'w', encoding='utf-8') as f:
    f.write('-- Nutrition Database for Fitnep (Nepali Foods)\n')
    f.write('-- Upload to live cPanel database: drsubashpandeynp_fitnep\n')
    f.write('-- Upload via: mysql -u drsubashpandeynp_fitnep -p < nutrition_database.sql\n\n')
    f.write('USE drsubashpandeynp_fitnep;\n\n')
    
    # Create table if it doesn't exist
    f.write('''CREATE TABLE IF NOT EXISTS nutrition_database (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  serving_size VARCHAR(100) NOT NULL,
  calories INT NOT NULL,
  carbs DECIMAL(6,2) NOT NULL,
  protein DECIMAL(6,2) NOT NULL,
  fat DECIMAL(6,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_food_name (name, serving_size)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

''')
    
    # Insert statements
    f.write('-- Insert or update nutrition data\n')
    for food in foods:
        name = food['Food'].replace("'", "''")
        serving_size = food['Serving Size'].replace("'", "''")
        try:
            calories = int(food['Calories'])
            carbs = float(food['Carbs (g)'])
            protein = float(food['Protein (g)'])
            fat = float(food['Fat (g)'])
        except (ValueError, KeyError):
            continue
        
        f.write(f"INSERT INTO nutrition_database (name, serving_size, calories, carbs, protein, fat) VALUES ('{name}', '{serving_size}', {calories}, {carbs}, {protein}, {fat}) ON DUPLICATE KEY UPDATE calories=VALUES(calories), carbs=VALUES(carbs), protein=VALUES(protein), fat=VALUES(fat);\n")

print(f"Created SQL file with {len(foods)} records at {sql_file}")