#!/usr/bin/env python3
"""ÜBERDeck Starter Pack Generator — 300 Core German Words
Outputs:
  - uberdeck-starter-pack.html  (interactive flashcard app)
  - uberdeck-starter-pack.apkg  (Anki import package)
"""

import json, sqlite3, zipfile, time, hashlib, os, random, struct, shutil, tempfile

# ============================================================
# WORD LIST — 300 Core German Words
# ============================================================
WORDS = [
    # Greetings & Phrases (20)
    {"de":"Hallo","en":"Hello","cat":"Phrases","lv":"A1"},
    {"de":"Guten Morgen","en":"Good morning","cat":"Phrases","lv":"A1"},
    {"de":"Guten Tag","en":"Good day","cat":"Phrases","lv":"A1"},
    {"de":"Guten Abend","en":"Good evening","cat":"Phrases","lv":"A1"},
    {"de":"Auf Wiedersehen","en":"Goodbye","cat":"Phrases","lv":"A1"},
    {"de":"Tschüss","en":"Bye","cat":"Phrases","lv":"A1"},
    {"de":"Bitte","en":"Please / You're welcome","cat":"Phrases","lv":"A1"},
    {"de":"Danke","en":"Thank you","cat":"Phrases","lv":"A1"},
    {"de":"Entschuldigung","en":"Excuse me / Sorry","cat":"Phrases","lv":"A1"},
    {"de":"Ja","en":"Yes","cat":"Phrases","lv":"A1"},
    {"de":"Nein","en":"No","cat":"Phrases","lv":"A1"},
    {"de":"Wie bitte?","en":"Pardon? / Come again?","cat":"Phrases","lv":"A1"},
    {"de":"Ich verstehe nicht","en":"I don't understand","cat":"Phrases","lv":"A1"},
    {"de":"Sprechen Sie Deutsch?","en":"Do you speak German?","cat":"Phrases","lv":"A1"},
    {"de":"Wie heißen Sie?","en":"What is your name?","cat":"Phrases","lv":"A1"},
    {"de":"Ich heiße…","en":"My name is…","cat":"Phrases","lv":"A1"},
    {"de":"Woher kommen Sie?","en":"Where are you from?","cat":"Phrases","lv":"A1"},
    {"de":"Wie geht es Ihnen?","en":"How are you? (formal)","cat":"Phrases","lv":"A1"},
    {"de":"Mir geht es gut","en":"I am fine","cat":"Phrases","lv":"A1"},
    {"de":"Willkommen","en":"Welcome","cat":"Phrases","lv":"A1"},
    # Numbers (20)
    {"de":"eins","en":"one","cat":"Numbers","lv":"A1"},
    {"de":"zwei","en":"two","cat":"Numbers","lv":"A1"},
    {"de":"drei","en":"three","cat":"Numbers","lv":"A1"},
    {"de":"vier","en":"four","cat":"Numbers","lv":"A1"},
    {"de":"fünf","en":"five","cat":"Numbers","lv":"A1"},
    {"de":"sechs","en":"six","cat":"Numbers","lv":"A1"},
    {"de":"sieben","en":"seven","cat":"Numbers","lv":"A1"},
    {"de":"acht","en":"eight","cat":"Numbers","lv":"A1"},
    {"de":"neun","en":"nine","cat":"Numbers","lv":"A1"},
    {"de":"zehn","en":"ten","cat":"Numbers","lv":"A1"},
    {"de":"zwanzig","en":"twenty","cat":"Numbers","lv":"A1"},
    {"de":"dreißig","en":"thirty","cat":"Numbers","lv":"A1"},
    {"de":"vierzig","en":"forty","cat":"Numbers","lv":"A1"},
    {"de":"fünfzig","en":"fifty","cat":"Numbers","lv":"A1"},
    {"de":"hundert","en":"hundred","cat":"Numbers","lv":"A1"},
    {"de":"tausend","en":"thousand","cat":"Numbers","lv":"A1"},
    {"de":"erste","en":"first","cat":"Numbers","lv":"A1"},
    {"de":"zweite","en":"second","cat":"Numbers","lv":"A1"},
    {"de":"dritte","en":"third","cat":"Numbers","lv":"A1"},
    {"de":"letzte","en":"last","cat":"Numbers","lv":"A1"},
    # Colors (15)
    {"de":"rot","en":"red","cat":"Colors","lv":"A1"},
    {"de":"blau","en":"blue","cat":"Colors","lv":"A1"},
    {"de":"grün","en":"green","cat":"Colors","lv":"A1"},
    {"de":"gelb","en":"yellow","cat":"Colors","lv":"A1"},
    {"de":"schwarz","en":"black","cat":"Colors","lv":"A1"},
    {"de":"weiß","en":"white","cat":"Colors","lv":"A1"},
    {"de":"grau","en":"grey","cat":"Colors","lv":"A1"},
    {"de":"braun","en":"brown","cat":"Colors","lv":"A1"},
    {"de":"orange","en":"orange","cat":"Colors","lv":"A1"},
    {"de":"lila","en":"purple","cat":"Colors","lv":"A1"},
    {"de":"rosa","en":"pink","cat":"Colors","lv":"A1"},
    {"de":"silber","en":"silver","cat":"Colors","lv":"A1"},
    {"de":"gold","en":"gold","cat":"Colors","lv":"A1"},
    {"de":"dunkel","en":"dark","cat":"Colors","lv":"A1"},
    {"de":"hell","en":"light / bright","cat":"Colors","lv":"A1"},
    # Family (20)
    {"de":"die Familie","en":"the family","cat":"Family","lv":"A1"},
    {"de":"die Mutter","en":"the mother","cat":"Family","lv":"A1"},
    {"de":"der Vater","en":"the father","cat":"Family","lv":"A1"},
    {"de":"die Eltern","en":"the parents","cat":"Family","lv":"A1"},
    {"de":"der Sohn","en":"the son","cat":"Family","lv":"A1"},
    {"de":"die Tochter","en":"the daughter","cat":"Family","lv":"A1"},
    {"de":"die Kinder","en":"the children","cat":"Family","lv":"A1"},
    {"de":"der Bruder","en":"the brother","cat":"Family","lv":"A1"},
    {"de":"die Schwester","en":"the sister","cat":"Family","lv":"A1"},
    {"de":"der Großvater","en":"the grandfather","cat":"Family","lv":"A1"},
    {"de":"die Großmutter","en":"the grandmother","cat":"Family","lv":"A1"},
    {"de":"der Onkel","en":"the uncle","cat":"Family","lv":"A1"},
    {"de":"die Tante","en":"the aunt","cat":"Family","lv":"A1"},
    {"de":"der Cousin","en":"the cousin (m)","cat":"Family","lv":"A1"},
    {"de":"die Cousine","en":"the cousin (f)","cat":"Family","lv":"A1"},
    {"de":"der Mann","en":"the husband / man","cat":"Family","lv":"A1"},
    {"de":"die Frau","en":"the wife / woman","cat":"Family","lv":"A1"},
    {"de":"der Freund","en":"the friend / boyfriend","cat":"Family","lv":"A1"},
    {"de":"die Freundin","en":"the friend / girlfriend","cat":"Family","lv":"A1"},
    {"de":"das Baby","en":"the baby","cat":"Family","lv":"A1"},
    # Food & Drink (25)
    {"de":"das Brot","en":"the bread","cat":"Food & Drink","lv":"A1"},
    {"de":"die Butter","en":"the butter","cat":"Food & Drink","lv":"A1"},
    {"de":"der Käse","en":"the cheese","cat":"Food & Drink","lv":"A1"},
    {"de":"das Ei","en":"the egg","cat":"Food & Drink","lv":"A1"},
    {"de":"das Fleisch","en":"the meat","cat":"Food & Drink","lv":"A1"},
    {"de":"der Fisch","en":"the fish","cat":"Food & Drink","lv":"A1"},
    {"de":"das Gemüse","en":"the vegetables","cat":"Food & Drink","lv":"A1"},
    {"de":"das Obst","en":"the fruit","cat":"Food & Drink","lv":"A1"},
    {"de":"der Apfel","en":"the apple","cat":"Food & Drink","lv":"A1"},
    {"de":"die Banane","en":"the banana","cat":"Food & Drink","lv":"A1"},
    {"de":"die Kartoffel","en":"the potato","cat":"Food & Drink","lv":"A1"},
    {"de":"die Tomate","en":"the tomato","cat":"Food & Drink","lv":"A1"},
    {"de":"das Wasser","en":"the water","cat":"Food & Drink","lv":"A1"},
    {"de":"der Kaffee","en":"the coffee","cat":"Food & Drink","lv":"A1"},
    {"de":"der Tee","en":"the tea","cat":"Food & Drink","lv":"A1"},
    {"de":"das Bier","en":"the beer","cat":"Food & Drink","lv":"A1"},
    {"de":"der Wein","en":"the wine","cat":"Food & Drink","lv":"A1"},
    {"de":"die Milch","en":"the milk","cat":"Food & Drink","lv":"A1"},
    {"de":"der Saft","en":"the juice","cat":"Food & Drink","lv":"A1"},
    {"de":"der Zucker","en":"the sugar","cat":"Food & Drink","lv":"A1"},
    {"de":"das Salz","en":"the salt","cat":"Food & Drink","lv":"A1"},
    {"de":"der Kuchen","en":"the cake","cat":"Food & Drink","lv":"A1"},
    {"de":"die Suppe","en":"the soup","cat":"Food & Drink","lv":"A1"},
    {"de":"das Frühstück","en":"the breakfast","cat":"Food & Drink","lv":"A1"},
    {"de":"das Abendessen","en":"the dinner","cat":"Food & Drink","lv":"A1"},
    # Animals (20)
    {"de":"der Hund","en":"the dog","cat":"Animals","lv":"A1"},
    {"de":"die Katze","en":"the cat","cat":"Animals","lv":"A1"},
    {"de":"das Pferd","en":"the horse","cat":"Animals","lv":"A1"},
    {"de":"die Kuh","en":"the cow","cat":"Animals","lv":"A1"},
    {"de":"das Schwein","en":"the pig","cat":"Animals","lv":"A1"},
    {"de":"das Huhn","en":"the chicken","cat":"Animals","lv":"A1"},
    {"de":"der Vogel","en":"the bird","cat":"Animals","lv":"A1"},
    {"de":"der Fisch","en":"the fish","cat":"Animals","lv":"A1"},
    {"de":"die Maus","en":"the mouse","cat":"Animals","lv":"A1"},
    {"de":"der Bär","en":"the bear","cat":"Animals","lv":"A1"},
    {"de":"der Löwe","en":"the lion","cat":"Animals","lv":"A1"},
    {"de":"der Tiger","en":"the tiger","cat":"Animals","lv":"A1"},
    {"de":"der Elefant","en":"the elephant","cat":"Animals","lv":"A1"},
    {"de":"die Schlange","en":"the snake","cat":"Animals","lv":"A1"},
    {"de":"der Schmetterling","en":"the butterfly","cat":"Animals","lv":"A1"},
    {"de":"die Biene","en":"the bee","cat":"Animals","lv":"A1"},
    {"de":"der Wolf","en":"the wolf","cat":"Animals","lv":"A1"},
    {"de":"der Fuchs","en":"the fox","cat":"Animals","lv":"A1"},
    {"de":"die Ente","en":"the duck","cat":"Animals","lv":"A1"},
    {"de":"das Kaninchen","en":"the rabbit","cat":"Animals","lv":"A1"},
    # Verbs (40)
    {"de":"sein","en":"to be","cat":"Verbs","lv":"A1"},
    {"de":"haben","en":"to have","cat":"Verbs","lv":"A1"},
    {"de":"werden","en":"to become / will","cat":"Verbs","lv":"A1"},
    {"de":"machen","en":"to do / make","cat":"Verbs","lv":"A1"},
    {"de":"gehen","en":"to go","cat":"Verbs","lv":"A1"},
    {"de":"kommen","en":"to come","cat":"Verbs","lv":"A1"},
    {"de":"sehen","en":"to see","cat":"Verbs","lv":"A1"},
    {"de":"sagen","en":"to say","cat":"Verbs","lv":"A1"},
    {"de":"wissen","en":"to know (fact)","cat":"Verbs","lv":"A1"},
    {"de":"kennen","en":"to know (person)","cat":"Verbs","lv":"A1"},
    {"de":"denken","en":"to think","cat":"Verbs","lv":"A1"},
    {"de":"sprechen","en":"to speak","cat":"Verbs","lv":"A1"},
    {"de":"essen","en":"to eat","cat":"Verbs","lv":"A1"},
    {"de":"trinken","en":"to drink","cat":"Verbs","lv":"A1"},
    {"de":"schlafen","en":"to sleep","cat":"Verbs","lv":"A1"},
    {"de":"arbeiten","en":"to work","cat":"Verbs","lv":"A1"},
    {"de":"lernen","en":"to learn / study","cat":"Verbs","lv":"A1"},
    {"de":"lesen","en":"to read","cat":"Verbs","lv":"A1"},
    {"de":"schreiben","en":"to write","cat":"Verbs","lv":"A1"},
    {"de":"hören","en":"to hear / listen","cat":"Verbs","lv":"A1"},
    {"de":"kaufen","en":"to buy","cat":"Verbs","lv":"A1"},
    {"de":"verkaufen","en":"to sell","cat":"Verbs","lv":"A1"},
    {"de":"spielen","en":"to play","cat":"Verbs","lv":"A1"},
    {"de":"laufen","en":"to run / walk","cat":"Verbs","lv":"A1"},
    {"de":"fahren","en":"to drive / travel","cat":"Verbs","lv":"A1"},
    {"de":"fliegen","en":"to fly","cat":"Verbs","lv":"A1"},
    {"de":"helfen","en":"to help","cat":"Verbs","lv":"A1"},
    {"de":"brauchen","en":"to need","cat":"Verbs","lv":"A1"},
    {"de":"wollen","en":"to want","cat":"Verbs","lv":"A1"},
    {"de":"können","en":"to be able to / can","cat":"Verbs","lv":"A1"},
    {"de":"müssen","en":"to have to / must","cat":"Verbs","lv":"A1"},
    {"de":"dürfen","en":"to be allowed to / may","cat":"Verbs","lv":"A1"},
    {"de":"öffnen","en":"to open","cat":"Verbs","lv":"A1"},
    {"de":"schließen","en":"to close","cat":"Verbs","lv":"A1"},
    {"de":"geben","en":"to give","cat":"Verbs","lv":"A1"},
    {"de":"nehmen","en":"to take","cat":"Verbs","lv":"A1"},
    {"de":"fragen","en":"to ask","cat":"Verbs","lv":"A1"},
    {"de":"antworten","en":"to answer","cat":"Verbs","lv":"A1"},
    {"de":"verstehen","en":"to understand","cat":"Verbs","lv":"A1"},
    {"de":"finden","en":"to find","cat":"Verbs","lv":"A1"},
    # Adjectives (25)
    {"de":"groß","en":"big / tall","cat":"Adjectives","lv":"A1"},
    {"de":"klein","en":"small / little","cat":"Adjectives","lv":"A1"},
    {"de":"gut","en":"good","cat":"Adjectives","lv":"A1"},
    {"de":"schlecht","en":"bad","cat":"Adjectives","lv":"A1"},
    {"de":"schön","en":"beautiful / nice","cat":"Adjectives","lv":"A1"},
    {"de":"hässlich","en":"ugly","cat":"Adjectives","lv":"A1"},
    {"de":"schnell","en":"fast / quick","cat":"Adjectives","lv":"A1"},
    {"de":"langsam","en":"slow","cat":"Adjectives","lv":"A1"},
    {"de":"alt","en":"old","cat":"Adjectives","lv":"A1"},
    {"de":"jung","en":"young","cat":"Adjectives","lv":"A1"},
    {"de":"neu","en":"new","cat":"Adjectives","lv":"A1"},
    {"de":"warm","en":"warm","cat":"Adjectives","lv":"A1"},
    {"de":"kalt","en":"cold","cat":"Adjectives","lv":"A1"},
    {"de":"heiß","en":"hot","cat":"Adjectives","lv":"A1"},
    {"de":"billig","en":"cheap","cat":"Adjectives","lv":"A1"},
    {"de":"teuer","en":"expensive","cat":"Adjectives","lv":"A1"},
    {"de":"leicht","en":"easy / light","cat":"Adjectives","lv":"A1"},
    {"de":"schwer","en":"heavy / difficult","cat":"Adjectives","lv":"A1"},
    {"de":"lang","en":"long","cat":"Adjectives","lv":"A1"},
    {"de":"kurz","en":"short","cat":"Adjectives","lv":"A1"},
    {"de":"richtig","en":"correct / right","cat":"Adjectives","lv":"A1"},
    {"de":"falsch","en":"wrong / false","cat":"Adjectives","lv":"A1"},
    {"de":"müde","en":"tired","cat":"Adjectives","lv":"A1"},
    {"de":"glücklich","en":"happy","cat":"Adjectives","lv":"A1"},
    {"de":"traurig","en":"sad","cat":"Adjectives","lv":"A1"},
    # Time & Days (20)
    {"de":"die Zeit","en":"the time","cat":"Time & Days","lv":"A1"},
    {"de":"der Tag","en":"the day","cat":"Time & Days","lv":"A1"},
    {"de":"die Woche","en":"the week","cat":"Time & Days","lv":"A1"},
    {"de":"der Monat","en":"the month","cat":"Time & Days","lv":"A1"},
    {"de":"das Jahr","en":"the year","cat":"Time & Days","lv":"A1"},
    {"de":"heute","en":"today","cat":"Time & Days","lv":"A1"},
    {"de":"gestern","en":"yesterday","cat":"Time & Days","lv":"A1"},
    {"de":"morgen","en":"tomorrow","cat":"Time & Days","lv":"A1"},
    {"de":"jetzt","en":"now","cat":"Time & Days","lv":"A1"},
    {"de":"später","en":"later","cat":"Time & Days","lv":"A1"},
    {"de":"Montag","en":"Monday","cat":"Time & Days","lv":"A1"},
    {"de":"Dienstag","en":"Tuesday","cat":"Time & Days","lv":"A1"},
    {"de":"Mittwoch","en":"Wednesday","cat":"Time & Days","lv":"A1"},
    {"de":"Donnerstag","en":"Thursday","cat":"Time & Days","lv":"A1"},
    {"de":"Freitag","en":"Friday","cat":"Time & Days","lv":"A1"},
    {"de":"Samstag","en":"Saturday","cat":"Time & Days","lv":"A1"},
    {"de":"Sonntag","en":"Sunday","cat":"Time & Days","lv":"A1"},
    {"de":"das Wochenende","en":"the weekend","cat":"Time & Days","lv":"A1"},
    {"de":"die Stunde","en":"the hour","cat":"Time & Days","lv":"A1"},
    {"de":"die Minute","en":"the minute","cat":"Time & Days","lv":"A1"},
    # House & Home (20)
    {"de":"das Haus","en":"the house","cat":"House & Home","lv":"A1"},
    {"de":"die Wohnung","en":"the apartment","cat":"House & Home","lv":"A1"},
    {"de":"das Zimmer","en":"the room","cat":"House & Home","lv":"A1"},
    {"de":"die Küche","en":"the kitchen","cat":"House & Home","lv":"A1"},
    {"de":"das Badezimmer","en":"the bathroom","cat":"House & Home","lv":"A1"},
    {"de":"das Schlafzimmer","en":"the bedroom","cat":"House & Home","lv":"A1"},
    {"de":"das Wohnzimmer","en":"the living room","cat":"House & Home","lv":"A1"},
    {"de":"die Tür","en":"the door","cat":"House & Home","lv":"A1"},
    {"de":"das Fenster","en":"the window","cat":"House & Home","lv":"A1"},
    {"de":"der Tisch","en":"the table","cat":"House & Home","lv":"A1"},
    {"de":"der Stuhl","en":"the chair","cat":"House & Home","lv":"A1"},
    {"de":"das Bett","en":"the bed","cat":"House & Home","lv":"A1"},
    {"de":"der Schrank","en":"the wardrobe / cupboard","cat":"House & Home","lv":"A1"},
    {"de":"die Lampe","en":"the lamp","cat":"House & Home","lv":"A1"},
    {"de":"der Boden","en":"the floor / ground","cat":"House & Home","lv":"A1"},
    {"de":"die Wand","en":"the wall","cat":"House & Home","lv":"A1"},
    {"de":"die Treppe","en":"the stairs","cat":"House & Home","lv":"A1"},
    {"de":"der Garten","en":"the garden","cat":"House & Home","lv":"A1"},
    {"de":"der Schlüssel","en":"the key","cat":"House & Home","lv":"A1"},
    {"de":"das Dach","en":"the roof","cat":"House & Home","lv":"A1"},
    # Nature & Weather (20)
    {"de":"die Sonne","en":"the sun","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Mond","en":"the moon","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Stern","en":"the star","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Regen","en":"the rain","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Schnee","en":"the snow","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Wind","en":"the wind","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Himmel","en":"the sky / heaven","cat":"Nature & Weather","lv":"A1"},
    {"de":"die Wolke","en":"the cloud","cat":"Nature & Weather","lv":"A1"},
    {"de":"das Wetter","en":"the weather","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Baum","en":"the tree","cat":"Nature & Weather","lv":"A1"},
    {"de":"die Blume","en":"the flower","cat":"Nature & Weather","lv":"A1"},
    {"de":"das Gras","en":"the grass","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Berg","en":"the mountain","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Fluss","en":"the river","cat":"Nature & Weather","lv":"A1"},
    {"de":"das Meer","en":"the sea / ocean","cat":"Nature & Weather","lv":"A1"},
    {"de":"der See","en":"the lake","cat":"Nature & Weather","lv":"A1"},
    {"de":"der Wald","en":"the forest","cat":"Nature & Weather","lv":"A1"},
    {"de":"die Erde","en":"the earth / ground","cat":"Nature & Weather","lv":"A1"},
    {"de":"das Feuer","en":"the fire","cat":"Nature & Weather","lv":"A1"},
    {"de":"das Eis","en":"the ice","cat":"Nature & Weather","lv":"A1"},
    # Travel & Transport (20)
    {"de":"das Auto","en":"the car","cat":"Travel & Transport","lv":"A1"},
    {"de":"der Bus","en":"the bus","cat":"Travel & Transport","lv":"A1"},
    {"de":"der Zug","en":"the train","cat":"Travel & Transport","lv":"A1"},
    {"de":"das Flugzeug","en":"the airplane","cat":"Travel & Transport","lv":"A1"},
    {"de":"das Schiff","en":"the ship","cat":"Travel & Transport","lv":"A1"},
    {"de":"das Fahrrad","en":"the bicycle","cat":"Travel & Transport","lv":"A1"},
    {"de":"die Straße","en":"the street / road","cat":"Travel & Transport","lv":"A1"},
    {"de":"der Bahnhof","en":"the train station","cat":"Travel & Transport","lv":"A1"},
    {"de":"der Flughafen","en":"the airport","cat":"Travel & Transport","lv":"A1"},
    {"de":"das Hotel","en":"the hotel","cat":"Travel & Transport","lv":"A1"},
    {"de":"die Stadt","en":"the city / town","cat":"Travel & Transport","lv":"A1"},
    {"de":"das Land","en":"the country","cat":"Travel & Transport","lv":"A1"},
    {"de":"die Reise","en":"the journey / trip","cat":"Travel & Transport","lv":"A1"},
    {"de":"die Karte","en":"the map / card","cat":"Travel & Transport","lv":"A1"},
    {"de":"links","en":"left","cat":"Travel & Transport","lv":"A1"},
    {"de":"rechts","en":"right","cat":"Travel & Transport","lv":"A1"},
    {"de":"geradeaus","en":"straight ahead","cat":"Travel & Transport","lv":"A1"},
    {"de":"der Parkplatz","en":"the parking lot","cat":"Travel & Transport","lv":"A1"},
    {"de":"die Ampel","en":"the traffic light","cat":"Travel & Transport","lv":"A1"},
    {"de":"die Brücke","en":"the bridge","cat":"Travel & Transport","lv":"A1"},
    # Body (15)
    {"de":"der Kopf","en":"the head","cat":"Body","lv":"A1"},
    {"de":"das Gesicht","en":"the face","cat":"Body","lv":"A1"},
    {"de":"das Auge","en":"the eye","cat":"Body","lv":"A1"},
    {"de":"das Ohr","en":"the ear","cat":"Body","lv":"A1"},
    {"de":"die Nase","en":"the nose","cat":"Body","lv":"A1"},
    {"de":"der Mund","en":"the mouth","cat":"Body","lv":"A1"},
    {"de":"die Hand","en":"the hand","cat":"Body","lv":"A1"},
    {"de":"der Arm","en":"the arm","cat":"Body","lv":"A1"},
    {"de":"das Bein","en":"the leg","cat":"Body","lv":"A1"},
    {"de":"der Fuß","en":"the foot","cat":"Body","lv":"A1"},
    {"de":"der Rücken","en":"the back","cat":"Body","lv":"A1"},
    {"de":"der Bauch","en":"the belly / stomach","cat":"Body","lv":"A1"},
    {"de":"das Haar","en":"the hair","cat":"Body","lv":"A1"},
    {"de":"der Finger","en":"the finger","cat":"Body","lv":"A1"},
    {"de":"das Herz","en":"the heart","cat":"Body","lv":"A1"},
    # Clothes (15)
    {"de":"das Hemd","en":"the shirt","cat":"Clothes","lv":"A1"},
    {"de":"die Hose","en":"the trousers / pants","cat":"Clothes","lv":"A1"},
    {"de":"das Kleid","en":"the dress","cat":"Clothes","lv":"A1"},
    {"de":"der Rock","en":"the skirt","cat":"Clothes","lv":"A1"},
    {"de":"die Jacke","en":"the jacket","cat":"Clothes","lv":"A1"},
    {"de":"der Mantel","en":"the coat","cat":"Clothes","lv":"A1"},
    {"de":"der Schuh","en":"the shoe","cat":"Clothes","lv":"A1"},
    {"de":"die Socke","en":"the sock","cat":"Clothes","lv":"A1"},
    {"de":"der Hut","en":"the hat","cat":"Clothes","lv":"A1"},
    {"de":"der Schal","en":"the scarf","cat":"Clothes","lv":"A1"},
    {"de":"die Handschuhe","en":"the gloves","cat":"Clothes","lv":"A1"},
    {"de":"das T-Shirt","en":"the t-shirt","cat":"Clothes","lv":"A1"},
    {"de":"die Unterwäsche","en":"the underwear","cat":"Clothes","lv":"A1"},
    {"de":"der Gürtel","en":"the belt","cat":"Clothes","lv":"A1"},
    {"de":"die Brille","en":"the glasses","cat":"Clothes","lv":"A1"},
    # Essentials — bring total to exactly 300 (5 more)
    {"de":"das Geld","en":"the money","cat":"Essentials","lv":"A1"},
    {"de":"der Preis","en":"the price","cat":"Essentials","lv":"A1"},
    {"de":"die Sprache","en":"the language","cat":"Essentials","lv":"A1"},
    {"de":"das Buch","en":"the book","cat":"Essentials","lv":"A1"},
    {"de":"der Name","en":"the name","cat":"Essentials","lv":"A1"},
]

assert len(WORDS) == 300, f"Expected 300 words, got {len(WORDS)}"
print(f"✅ Loaded {len(WORDS)} words across {len(set(w['cat'] for w in WORDS))} categories")

OUT_DIR = os.path.dirname(os.path.abspath(__file__))

# ============================================================
# 1. HTML FLASHCARD APP
# ============================================================

CATEGORY_COLORS = {
    "Phrases":          "#FF2D78",
    "Numbers":          "#B44AFF",
    "Colors":           "#FF6B35",
    "Family":           "#FF2D78",
    "Food & Drink":     "#4AFF91",
    "Animals":          "#FFD700",
    "Verbs":            "#00BFFF",
    "Adjectives":       "#B44AFF",
    "Time & Days":      "#FF6B35",
    "House & Home":     "#4AFF91",
    "Nature & Weather": "#00BFFF",
    "Travel & Transport":"#FFD700",
    "Body":             "#FF2D78",
    "Clothes":          "#B44AFF",
    "Essentials":       "#4AFF91",
}

words_json = json.dumps(WORDS, ensure_ascii=False)
cats_json = json.dumps(list(dict.fromkeys(w["cat"] for w in WORDS)), ensure_ascii=False)
cat_colors_json = json.dumps(CATEGORY_COLORS, ensure_ascii=False)

HTML = f"""<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>ÜBERDeck — 300 Core German Words</title>
<style>
  :root {{
    --bg: #0C0A1A;
    --surface: #13102A;
    --surface2: #1A163A;
    --pink: #FF2D78;
    --violet: #B44AFF;
    --green: #4AFF91;
    --gold: #FFD700;
    --sky: #00BFFF;
    --orange: #FF6B35;
    --text: #F0EEF8;
    --muted: #7B78A8;
    --border: rgba(180,74,255,0.2);
  }}
  * {{ margin:0; padding:0; box-sizing:border-box; }}
  body {{ background:var(--bg); color:var(--text); font-family:'Segoe UI',system-ui,sans-serif; min-height:100vh; }}

  /* Header */
  header {{ padding:24px 32px 16px; border-bottom:1px solid var(--border); display:flex; align-items:center; justify-content:space-between; flex-wrap:wrap; gap:12px; }}
  .logo {{ font-size:1.8rem; font-weight:800; letter-spacing:-1px; }}
  .logo span:first-child {{ color:var(--pink); }}
  .logo span:last-child {{ color:var(--violet); }}
  .stats {{ display:flex; gap:20px; font-size:.85rem; color:var(--muted); }}
  .stats strong {{ color:var(--text); }}

  /* Controls */
  .controls {{ padding:16px 32px; display:flex; gap:10px; flex-wrap:wrap; align-items:center; }}
  .search-wrap {{ position:relative; flex:1; min-width:200px; max-width:320px; }}
  .search-wrap input {{
    width:100%; padding:8px 12px 8px 36px;
    background:var(--surface); border:1px solid var(--border);
    border-radius:8px; color:var(--text); font-size:.9rem; outline:none;
  }}
  .search-wrap input:focus {{ border-color:var(--violet); }}
  .search-icon {{ position:absolute; left:10px; top:50%; transform:translateY(-50%); color:var(--muted); font-size:1rem; }}
  .filter-btn {{
    padding:8px 14px; border-radius:8px; border:1px solid var(--border);
    background:var(--surface); color:var(--muted); cursor:pointer; font-size:.82rem;
    transition:all .2s;
  }}
  .filter-btn:hover {{ border-color:var(--violet); color:var(--text); }}
  .filter-btn.active {{ background:var(--violet); border-color:var(--violet); color:#fff; font-weight:600; }}
  .mode-btn {{
    margin-left:auto; padding:8px 18px; border-radius:8px;
    background:var(--pink); border:none; color:#fff; font-weight:700;
    cursor:pointer; font-size:.9rem; transition:opacity .2s;
  }}
  .mode-btn:hover {{ opacity:.85; }}

  /* Progress */
  .progress-bar {{ height:3px; background:var(--surface2); }}
  .progress-fill {{ height:3px; background:linear-gradient(90deg,var(--pink),var(--violet)); width:0%; transition:width .4s; }}

  /* Main */
  main {{ padding:24px 32px; }}

  /* Flashcard Mode */
  #card-mode {{ display:none; max-width:600px; margin:0 auto; }}
  .card-nav {{ display:flex; justify-content:space-between; align-items:center; margin-bottom:20px; }}
  .nav-btn {{ background:var(--surface2); border:1px solid var(--border); color:var(--text); padding:10px 20px; border-radius:8px; cursor:pointer; font-size:1rem; transition:all .2s; }}
  .nav-btn:hover {{ border-color:var(--pink); }}
  .card-counter {{ color:var(--muted); font-size:.9rem; }}
  
  .flip-card {{ width:100%; height:280px; perspective:1000px; cursor:pointer; margin-bottom:16px; }}
  .flip-inner {{ position:relative; width:100%; height:100%; transition:transform .5s; transform-style:preserve-3d; }}
  .flip-card.flipped .flip-inner {{ transform:rotateY(180deg); }}
  .flip-front, .flip-back {{
    position:absolute; width:100%; height:100%; backface-visibility:hidden;
    border-radius:16px; display:flex; flex-direction:column; align-items:center; justify-content:center;
    border:1px solid var(--border);
  }}
  .flip-front {{ background:var(--surface); }}
  .flip-back {{ background:var(--surface2); transform:rotateY(180deg); }}
  .card-de {{ font-size:2.4rem; font-weight:800; letter-spacing:-1px; margin-bottom:8px; }}
  .card-cat-badge {{
    font-size:.75rem; padding:4px 10px; border-radius:20px; font-weight:600;
    background:rgba(180,74,255,.15); color:var(--violet);
    position:absolute; top:16px; right:16px;
  }}
  .card-hint {{ color:var(--muted); font-size:.85rem; margin-top:8px; }}
  .card-en {{ font-size:1.8rem; font-weight:700; }}
  .card-level {{ position:absolute; top:16px; left:16px; font-size:.7rem; color:var(--muted); font-weight:600; }}
  
  .flip-tip {{ text-align:center; color:var(--muted); font-size:.8rem; margin-bottom:16px; }}
  
  .card-actions {{ display:flex; gap:10px; justify-content:center; }}
  .action-btn {{ padding:10px 24px; border-radius:8px; border:none; font-weight:700; cursor:pointer; font-size:.9rem; transition:all .2s; }}
  .btn-known {{ background:var(--green); color:#0C0A1A; }}
  .btn-again {{ background:var(--surface2); color:var(--text); border:1px solid var(--border); }}
  .btn-known:hover {{ opacity:.85; }}
  .btn-again:hover {{ border-color:var(--pink); }}
  
  .session-stats {{ display:flex; gap:16px; justify-content:center; margin-top:16px; font-size:.85rem; color:var(--muted); }}
  .session-stats span strong {{ color:var(--green); }}
  .session-stats span.wrong strong {{ color:var(--pink); }}

  /* Grid Mode */
  #grid-mode {{ display:block; }}
  .cat-section {{ margin-bottom:40px; }}
  .cat-title {{
    font-size:1rem; font-weight:700; margin-bottom:16px;
    display:flex; align-items:center; gap:10px;
  }}
  .cat-dot {{ width:10px; height:10px; border-radius:50%; flex-shrink:0; }}
  .cat-count {{ color:var(--muted); font-weight:400; font-size:.85rem; }}
  
  .word-grid {{ display:grid; grid-template-columns:repeat(auto-fill,minmax(200px,1fr)); gap:10px; }}
  .word-card {{
    background:var(--surface); border:1px solid var(--border);
    border-radius:10px; padding:14px 16px; cursor:pointer;
    transition:all .2s; position:relative; overflow:hidden;
  }}
  .word-card::before {{
    content:''; position:absolute; top:0; left:0; width:3px; height:100%;
  }}
  .word-card:hover {{ transform:translateY(-2px); border-color:var(--violet); }}
  .word-card:hover .word-en {{ opacity:1; max-height:40px; }}
  .word-de {{ font-weight:700; font-size:1rem; margin-bottom:4px; }}
  .word-en {{ color:var(--muted); font-size:.82rem; opacity:0; max-height:0; transition:all .3s; overflow:hidden; }}
  .word-card:hover .word-en {{ opacity:1; max-height:40px; }}
  .word-lv {{ position:absolute; top:8px; right:10px; font-size:.65rem; color:var(--muted); }}

  /* Empty state */
  .empty {{ text-align:center; padding:60px; color:var(--muted); }}
  .empty h3 {{ font-size:1.4rem; margin-bottom:8px; color:var(--text); }}

  /* Keyboard hint */
  .kbd-hints {{ text-align:center; color:var(--muted); font-size:.75rem; margin-top:8px; }}
  .kbd {{ background:var(--surface2); border:1px solid var(--border); border-radius:4px; padding:2px 6px; font-family:monospace; }}

  /* Footer */
  footer {{ text-align:center; padding:40px 24px 24px; color:var(--muted); font-size:.8rem; }}

  @media(max-width:600px){{
    header,main,.controls{{ padding-left:16px; padding-right:16px; }}
    .card-de{{ font-size:1.8rem; }}
    .card-en{{ font-size:1.4rem; }}
    .flip-card{{ height:240px; }}
  }}
</style>
</head>
<body>

<header>
  <div class="logo"><span>ÜBER</span><span>Deck</span> <span style="font-size:1rem;font-weight:400;color:var(--muted)">Starter Pack</span></div>
  <div class="stats">
    <span>🇩🇪 <strong id="total-count">300</strong> words</span>
    <span>📚 <strong id="cat-count">14</strong> categories</span>
    <span>✅ <strong id="known-count">0</strong> known</span>
  </div>
</header>

<div class="progress-bar"><div class="progress-fill" id="progress-fill"></div></div>

<div class="controls">
  <div class="search-wrap">
    <span class="search-icon">🔍</span>
    <input type="text" id="search-input" placeholder="Search words…">
  </div>
  <div id="cat-filters" style="display:flex;gap:8px;flex-wrap:wrap;"></div>
  <button class="mode-btn" id="mode-toggle" onclick="toggleMode()">🃏 Flashcard Mode</button>
</div>

<main>
  <!-- Grid Mode -->
  <div id="grid-mode"></div>

  <!-- Flashcard Mode -->
  <div id="card-mode">
    <div class="card-nav">
      <button class="nav-btn" onclick="prevCard()">← Prev</button>
      <span class="card-counter"><span id="card-idx">1</span> / <span id="card-total">300</span></span>
      <button class="nav-btn" onclick="nextCard()">Next →</button>
    </div>
    <div class="flip-card" id="flip-card" onclick="flipCard()">
      <div class="flip-inner">
        <div class="flip-front">
          <span class="card-level" id="fc-level">A1</span>
          <span class="card-cat-badge" id="fc-cat">Phrases</span>
          <div class="card-de" id="fc-de">Hallo</div>
          <div class="card-hint">Tap to reveal →</div>
        </div>
        <div class="flip-back">
          <span class="card-level" id="fc-level-b">A1</span>
          <div class="card-en" id="fc-en">Hello</div>
          <div class="card-hint" id="fc-de-b" style="margin-top:4px;"></div>
        </div>
      </div>
    </div>
    <div class="flip-tip">
      <span class="kbd">Space</span> flip &nbsp;
      <span class="kbd">←</span><span class="kbd">→</span> navigate &nbsp;
      <span class="kbd">K</span> mark known &nbsp;
      <span class="kbd">R</span> shuffle
    </div>
    <div class="card-actions">
      <button class="action-btn btn-again" onclick="markAgain()">↺ Again</button>
      <button class="action-btn btn-known" onclick="markKnown()">✓ Known</button>
    </div>
    <div class="session-stats">
      <span>Known: <strong id="sess-known">0</strong></span>
      <span class="wrong">Again: <strong id="sess-again">0</strong></span>
      <span>Remaining: <strong id="sess-remain">0</strong></span>
    </div>
  </div>
</main>

<footer>ÜBERDeck Starter Pack · 300 Core German Words · A1 Level · Made with ⚡ by MelAI</footer>

<script>
const WORDS = {words_json};
const CATEGORIES = {cats_json};
const CAT_COLORS = {cat_colors_json};

let filteredWords = [...WORDS];
let currentCat = 'All';
let searchTerm = '';
let isCardMode = false;
let cardIndex = 0;
let flipped = false;
let known = new Set();
let sessionKnown = 0;
let sessionAgain = 0;
let cardQueue = [];

// ── Category dot color ──────────────────────────────────────
function catColor(cat) {{ return CAT_COLORS[cat] || '#B44AFF'; }}

// ── Build filter buttons ────────────────────────────────────
function buildFilters() {{
  const el = document.getElementById('cat-filters');
  const all = document.createElement('button');
  all.className = 'filter-btn active'; all.textContent = 'All';
  all.onclick = () => setFilter('All');
  el.appendChild(all);
  CATEGORIES.forEach(cat => {{
    const btn = document.createElement('button');
    btn.className = 'filter-btn';
    btn.textContent = cat;
    btn.onclick = () => setFilter(cat);
    el.appendChild(btn);
  }});
}}

function setFilter(cat) {{
  currentCat = cat;
  document.querySelectorAll('.filter-btn').forEach(b => {{
    b.classList.toggle('active', b.textContent === cat);
  }});
  applyFilters();
}}

function applyFilters() {{
  filteredWords = WORDS.filter(w => {{
    const catOk = currentCat === 'All' || w.cat === currentCat;
    const srchOk = !searchTerm || w.de.toLowerCase().includes(searchTerm) || w.en.toLowerCase().includes(searchTerm);
    return catOk && srchOk;
  }});
  cardQueue = [...filteredWords];
  cardIndex = 0;
  renderGrid();
  if (isCardMode) renderCard();
}}

// ── Grid ────────────────────────────────────────────────────
function renderGrid() {{
  const el = document.getElementById('grid-mode');
  el.innerHTML = '';
  if (!filteredWords.length) {{
    el.innerHTML = '<div class="empty"><h3>No words found</h3><p>Try a different search or filter.</p></div>';
    return;
  }}
  const bycat = {{}};
  filteredWords.forEach(w => {{
    if (!bycat[w.cat]) bycat[w.cat] = [];
    bycat[w.cat].push(w);
  }});
  Object.entries(bycat).forEach(([cat, words]) => {{
    const sec = document.createElement('div');
    sec.className = 'cat-section';
    sec.innerHTML = `
      <div class="cat-title">
        <span class="cat-dot" style="background:${{catColor(cat)}}"></span>
        ${{cat}} <span class="cat-count">${{words.length}} words</span>
      </div>
      <div class="word-grid" id="grid-${{cat.replace(/[^a-z]/gi,'_')}}"></div>`;
    el.appendChild(sec);
    const grid = sec.querySelector('.word-grid');
    words.forEach(w => {{
      const card = document.createElement('div');
      card.className = 'word-card' + (known.has(w.de) ? ' known' : '');
      card.style.setProperty('--accent', catColor(cat));
      card.innerHTML = `
        <span class="word-lv">${{w.lv}}</span>
        <div class="word-de">${{w.de}}</div>
        <div class="word-en">${{w.en}}</div>`;
      card.querySelector('.word-card\\.before, .word-card').style.cssText +=
        '--clr:' + catColor(cat);
      card.style.cssText += `--clr:${{catColor(cat)}};`;
      // color the left border
      card.style.setProperty('border-left', `3px solid ${{catColor(cat)}}`);
      if (known.has(w.de)) card.style.opacity = '.5';
      card.onclick = () => {{ card.querySelector('.word-en').style.opacity = 1; }};
      grid.appendChild(card);
    }});
  }});
  updateStats();
}}

// ── Flashcard ───────────────────────────────────────────────
function renderCard() {{
  if (!cardQueue.length) return;
  const w = cardQueue[cardIndex];
  document.getElementById('fc-de').textContent = w.de;
  document.getElementById('fc-en').textContent = w.en;
  document.getElementById('fc-de-b').textContent = w.de;
  document.getElementById('fc-cat').textContent = w.cat;
  document.getElementById('fc-cat').style.color = catColor(w.cat);
  document.getElementById('fc-level').textContent = w.lv;
  document.getElementById('fc-level-b').textContent = w.lv;
  document.getElementById('card-idx').textContent = cardIndex + 1;
  document.getElementById('card-total').textContent = cardQueue.length;
  document.getElementById('sess-remain').textContent = cardQueue.length - cardIndex;
  const fc = document.getElementById('flip-card');
  fc.classList.remove('flipped');
  flipped = false;
}}

function flipCard() {{
  document.getElementById('flip-card').classList.toggle('flipped');
  flipped = !flipped;
}}

function nextCard() {{ if (cardIndex < cardQueue.length-1) {{ cardIndex++; renderCard(); }} }}
function prevCard() {{ if (cardIndex > 0) {{ cardIndex--; renderCard(); }} }}

function markKnown() {{
  if (!cardQueue.length) return;
  const w = cardQueue[cardIndex];
  known.add(w.de);
  sessionKnown++;
  document.getElementById('sess-known').textContent = sessionKnown;
  updateStats();
  if (cardIndex < cardQueue.length-1) {{ cardIndex++; renderCard(); }}
}}

function markAgain() {{
  sessionAgain++;
  document.getElementById('sess-again').textContent = sessionAgain;
  if (cardIndex < cardQueue.length-1) {{ cardIndex++; renderCard(); }}
}}

function shuffleCards() {{
  cardQueue = [...filteredWords].sort(() => Math.random() - .5);
  cardIndex = 0;
  renderCard();
}}

// ── Mode toggle ─────────────────────────────────────────────
function toggleMode() {{
  isCardMode = !isCardMode;
  document.getElementById('grid-mode').style.display = isCardMode ? 'none' : 'block';
  document.getElementById('card-mode').style.display = isCardMode ? 'block' : 'none';
  document.getElementById('mode-toggle').textContent = isCardMode ? '🗂️ Browse Mode' : '🃏 Flashcard Mode';
  if (isCardMode) {{ cardQueue = [...filteredWords]; cardIndex = 0; renderCard(); }}
}}

// ── Stats ───────────────────────────────────────────────────
function updateStats() {{
  document.getElementById('known-count').textContent = known.size;
  document.getElementById('total-count').textContent = filteredWords.length;
  document.getElementById('cat-count').textContent = new Set(filteredWords.map(w=>w.cat)).size;
  const pct = WORDS.length ? (known.size / WORDS.length * 100) : 0;
  document.getElementById('progress-fill').style.width = pct + '%';
}}

// ── Keyboard ────────────────────────────────────────────────
document.addEventListener('keydown', e => {{
  if (!isCardMode) return;
  if (e.code === 'Space') {{ e.preventDefault(); flipCard(); }}
  if (e.code === 'ArrowRight') nextCard();
  if (e.code === 'ArrowLeft') prevCard();
  if (e.key === 'k' || e.key === 'K') markKnown();
  if (e.key === 'r' || e.key === 'R') shuffleCards();
}});

// ── Search ──────────────────────────────────────────────────
document.getElementById('search-input').addEventListener('input', e => {{
  searchTerm = e.target.value.toLowerCase();
  applyFilters();
}});

// ── Init ────────────────────────────────────────────────────
buildFilters();
renderGrid();
</script>
</body>
</html>"""

html_path = os.path.join(OUT_DIR, "uberdeck-starter-pack.html")
with open(html_path, "w", encoding="utf-8") as f:
    f.write(HTML)
print(f"✅ HTML written → {html_path}")

# ============================================================
# 2. APKG (Anki Package)
# ============================================================

def make_guid(seed):
    """Generate a short, stable guid."""
    h = hashlib.sha1(seed.encode()).digest()
    chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!#"
    result = ""
    n = int.from_bytes(h[:8], "big")
    while n > 0:
        result += chars[n % 64]
        n //= 64
    return result[:10].ljust(10, "a")

def field_checksum(sfld):
    h = hashlib.sha1(sfld.encode("utf-8")).digest()
    return struct.unpack(">I", h[:4])[0]

def make_apkg(words, out_path):
    tmpdir = tempfile.mkdtemp()
    db_path = os.path.join(tmpdir, "collection.anki2")
    media_path = os.path.join(tmpdir, "media")

    con = sqlite3.connect(db_path)
    cur = con.cursor()

    # Schema
    cur.executescript("""
        CREATE TABLE col (
            id integer PRIMARY KEY,
            crt integer NOT NULL,
            mod integer NOT NULL,
            scm integer NOT NULL,
            ver integer NOT NULL,
            dty integer NOT NULL,
            usn integer NOT NULL,
            ls integer NOT NULL,
            conf text NOT NULL,
            models text NOT NULL,
            decks text NOT NULL,
            dconf text NOT NULL,
            tags text NOT NULL
        );
        CREATE TABLE notes (
            id integer PRIMARY KEY,
            guid text NOT NULL,
            mid integer NOT NULL,
            mod integer NOT NULL,
            usn integer NOT NULL,
            tags text NOT NULL,
            flds text NOT NULL,
            sfld text NOT NULL,
            csum integer NOT NULL,
            flags integer NOT NULL,
            data text NOT NULL
        );
        CREATE TABLE cards (
            id integer PRIMARY KEY,
            nid integer NOT NULL,
            did integer NOT NULL,
            ord integer NOT NULL,
            mod integer NOT NULL,
            usn integer NOT NULL,
            type integer NOT NULL,
            queue integer NOT NULL,
            due integer NOT NULL,
            ivl integer NOT NULL,
            factor integer NOT NULL,
            reps integer NOT NULL,
            lapses integer NOT NULL,
            left integer NOT NULL,
            odue integer NOT NULL,
            odid integer NOT NULL,
            flags integer NOT NULL,
            data text NOT NULL
        );
        CREATE TABLE revlog (
            id integer PRIMARY KEY,
            cid integer NOT NULL,
            usn integer NOT NULL,
            ease integer NOT NULL,
            ivl integer NOT NULL,
            lastIvl integer NOT NULL,
            factor integer NOT NULL,
            time integer NOT NULL,
            type integer NOT NULL
        );
        CREATE TABLE graves (
            usn integer NOT NULL,
            oid integer NOT NULL,
            type integer NOT NULL
        );
        CREATE INDEX ix_notes_usn on notes (usn);
        CREATE INDEX ix_cards_usn on cards (usn);
        CREATE INDEX ix_cards_nid on cards (nid);
        CREATE INDEX ix_cards_sched on cards (did, queue, due);
        CREATE INDEX ix_revlog_usn on revlog (usn);
        CREATE INDEX ix_revlog_cid on revlog (cid);
    """)

    now = int(time.time())
    deck_id = 1700000001
    model_id = 1700000002

    # Model (note type)
    model = {
        str(model_id): {
            "id": model_id,
            "name": "ÜBERDeck German",
            "type": 0,
            "mod": now,
            "usn": -1,
            "sortf": 0,
            "did": deck_id,
            "tmpls": [{
                "name": "Card 1",
                "ord": 0,
                "qfmt": "<div class='german'>{{German}}</div><div class='cat'>{{Category}}</div>",
                "afmt": "{{FrontSide}}<hr id=answer><div class='english'>{{English}}</div>",
                "bqfmt": "",
                "bafmt": "",
                "did": None,
                "bfont": "",
                "bsize": 0
            }],
            "flds": [
                {"name":"German","ord":0,"sticky":False,"rtl":False,"font":"Arial","size":24,"media":[]},
                {"name":"English","ord":1,"sticky":False,"rtl":False,"font":"Arial","size":20,"media":[]},
                {"name":"Category","ord":2,"sticky":False,"rtl":False,"font":"Arial","size":14,"media":[]},
                {"name":"Level","ord":3,"sticky":False,"rtl":False,"font":"Arial","size":12,"media":[]},
            ],
            "css": """
.card {
  font-family: 'Segoe UI', Arial, sans-serif;
  background: #0C0A1A;
  color: #F0EEF8;
  text-align: center;
  padding: 30px 20px;
  min-height: 200px;
  display: flex;
  flex-direction: column;
  align-items: center;
  justify-content: center;
}
.german {
  font-size: 2.2rem;
  font-weight: 800;
  color: #FF2D78;
  margin-bottom: 10px;
}
.english {
  font-size: 1.6rem;
  font-weight: 600;
  color: #4AFF91;
  margin-top: 10px;
}
.cat {
  font-size: 0.75rem;
  color: #7B78A8;
  background: rgba(180,74,255,0.15);
  padding: 4px 12px;
  border-radius: 20px;
  margin-top: 8px;
}
hr#answer {
  border-color: rgba(180,74,255,0.3);
  margin: 20px 0;
}
""",
            "latexPre": "\\documentclass[12pt]{article}\n\\special{papersize=3in,5in}\n\\usepackage{amssymb,amsmath}\n\\pagestyle{empty}\n\\setlength{\\parindent}{0in}\n\\begin{document}\n",
            "latexPost": "\\end{document}",
            "tags": [],
            "vers": []
        }
    }

    # Deck
    decks = {
        "1": {
            "id": 1, "name": "Default", "extendRev": 50,
            "usn": 0, "collapsed": False, "browserCollapsed": False,
            "newToday": [0,0], "timeToday": [0,0], "revToday": [0,0], "lrnToday": [0,0],
            "dyn": 0, "extendNew": 10, "conf": 1, "desc": "", "mod": now
        },
        str(deck_id): {
            "id": deck_id,
            "name": "ÜBERDeck Starter Pack — 300 Core German Words",
            "extendRev": 50, "usn": -1, "collapsed": False, "browserCollapsed": False,
            "newToday": [0,0], "timeToday": [0,0], "revToday": [0,0], "lrnToday": [0,0],
            "dyn": 0, "extendNew": 10, "conf": 1,
            "desc": "300 core A1 German words across 14 categories. Made with ⚡ by MelAI.",
            "mod": now
        }
    }

    # Deck config
    dconf = {
        "1": {
            "id": 1, "name": "Default", "mod": now, "usn": 0,
            "maxTaken": 60, "timer": 0, "autoplay": True, "replayq": True,
            "new": {"bury": True, "delays": [1,10], "initialFactor": 2500, "ints": [1,4,7], "order": 1, "perDay": 20},
            "rev": {"bury": True, "ease4": 1.3, "fuzz": 0.05, "ivlFct": 1, "maxIvl": 36500, "minSpace": 1, "perDay": 200},
            "lapse": {"delays": [10], "leechAction": 0, "leechFails": 8, "minInt": 1, "mult": 0}
        }
    }

    conf = {
        "activeDecks": [deck_id],
        "addToCur": True,
        "collapseTime": 1200,
        "curDeck": deck_id,
        "curModel": str(model_id),
        "dueCounts": True,
        "estTimes": True,
        "newBury": True,
        "newSpread": 0,
        "nextPos": 1,
        "notetype": "ÜBERDeck German",
        "schedVer": 1,
        "sortBackwards": False,
        "sortType": "noteFld",
        "timeLim": 0
    }

    cur.execute(
        "INSERT INTO col VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (1, now, now, now, 11, 0, -1, 0,
         json.dumps(conf), json.dumps(model), json.dumps(decks), json.dumps(dconf), "{}")
    )

    # Notes & Cards
    for i, w in enumerate(words):
        note_id = now * 1000 + i
        card_id = note_id + 500000
        guid = make_guid(f"uberdeck_{i}_{w['de']}")
        flds = "\x1f".join([w["de"], w["en"], w["cat"], w["lv"]])
        sfld = w["de"]
        csum = field_checksum(sfld)
        tags = f" {w['cat'].replace(' ','_')} A1 "

        cur.execute(
            "INSERT INTO notes VALUES (?,?,?,?,?,?,?,?,?,?,?)",
            (note_id, guid, model_id, now, -1, tags, flds, sfld, csum, 0, "")
        )
        cur.execute(
            "INSERT INTO cards VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (card_id, note_id, deck_id, 0, now, -1, 0, 0, i+1, 0, 0, 0, 0, 0, 0, 0, 0, "")
        )

    con.commit()
    con.close()

    # media file (empty mapping)
    with open(media_path, "w") as f:
        json.dump({}, f)

    # zip into .apkg
    with zipfile.ZipFile(out_path, "w", zipfile.ZIP_DEFLATED) as zf:
        zf.write(db_path, "collection.anki2")
        zf.write(media_path, "media")

    shutil.rmtree(tmpdir)
    print(f"✅ APKG written → {out_path}")

apkg_path = os.path.join(OUT_DIR, "uberdeck-starter-pack.apkg")
make_apkg(WORDS, apkg_path)

# Final summary
html_size = os.path.getsize(html_path) / 1024
apkg_size = os.path.getsize(apkg_path) / 1024
print(f"\n🎉 ÜBERDeck Starter Pack DONE!")
print(f"   HTML : {html_path} ({html_size:.1f} KB)")
print(f"   APKG : {apkg_path} ({apkg_size:.1f} KB)")
print(f"   Words: {len(WORDS)}")
print(f"   Cats : {len(set(w['cat'] for w in WORDS))}")