-- Mathe-App Datenbankschema

CREATE TABLE IF NOT EXISTS users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) UNIQUE NOT NULL,
    password    VARCHAR(255) NOT NULL,
    username    VARCHAR(50) UNIQUE NOT NULL,
    xp          INTEGER DEFAULT 0,
    level       INTEGER DEFAULT 1,
    streak      INTEGER DEFAULT 0,
    streak_last DATE,
    is_premium  BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS categories (
    id          SERIAL PRIMARY KEY,
    slug        VARCHAR(50) UNIQUE NOT NULL,
    title       VARCHAR(100) NOT NULL,
    icon        VARCHAR(10),
    sort_order  INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS topics (
    id          SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES categories(id),
    slug        VARCHAR(50) UNIQUE NOT NULL,
    title       VARCHAR(100) NOT NULL,
    description TEXT,
    difficulty  SMALLINT CHECK (difficulty BETWEEN 1 AND 5),
    is_premium  BOOLEAN DEFAULT FALSE,
    sort_order  INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS tasks (
    id          SERIAL PRIMARY KEY,
    topic_id    INTEGER REFERENCES topics(id),
    title       VARCHAR(200) NOT NULL,
    question    TEXT NOT NULL,
    answer_type VARCHAR(20) NOT NULL CHECK (answer_type IN ('number', 'multiple_choice', 'text')),
    correct     TEXT NOT NULL,
    tolerance   NUMERIC DEFAULT 0,
    unit        VARCHAR(20),
    choices     JSONB,
    explanation TEXT,
    xp_reward   INTEGER DEFAULT 10,
    difficulty  SMALLINT CHECK (difficulty BETWEEN 1 AND 5)
);

CREATE TABLE IF NOT EXISTS user_progress (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER REFERENCES users(id),
    task_id     INTEGER REFERENCES tasks(id),
    solved_at   TIMESTAMPTZ DEFAULT NOW(),
    correct     BOOLEAN NOT NULL,
    UNIQUE(user_id, task_id)
);

-- ============================================================
-- Kategorien
-- ============================================================
INSERT INTO categories (slug, title, icon, sort_order) VALUES
  ('finanzen',    'Finanzen',            '💰', 1),
  ('geometrie',   'Geometrie',           '📐', 2),
  ('verhaeltnisse','Verhältnisse & Zeit', '⚖️',  3),
  ('physik',      'Physik',              '⚡', 4),
  ('informatik',  'Informatik',          '💻', 5),
  ('verkehr',     'Verkehr',             '🚗', 6),
  ('shopping',    'Shopping',            '🛒', 7)
ON CONFLICT (slug) DO NOTHING;

-- ============================================================
-- Topics
-- ============================================================
INSERT INTO topics (category_id, slug, title, description, difficulty, sort_order) VALUES
  -- Finanzen
  ((SELECT id FROM categories WHERE slug='finanzen'), 'zinsen',    'Zinsen & Sparen',    'Tages- und Festgeld, Zinseszins', 2, 1),
  ((SELECT id FROM categories WHERE slug='finanzen'), 'kredite',   'Kredite & Raten',    'Ratenkredite, Gesamtkosten, effektiver Jahreszins', 3, 2),
  ((SELECT id FROM categories WHERE slug='finanzen'), 'rabatte',   'Rabatte & Steuern',  'Prozentrechnung, MwSt, Netto/Brutto', 1, 3),
  -- Geometrie
  ((SELECT id FROM categories WHERE slug='geometrie'), 'flaechen',  'Flächen',            'Zimmer, Garten, Grundstück berechnen', 2, 1),
  ((SELECT id FROM categories WHERE slug='geometrie'), 'volumina',  'Volumina',           'Aquarium, Pool, Tank, Behälter', 2, 2),
  ((SELECT id FROM categories WHERE slug='geometrie'), 'winkel',    'Winkel & Trigonometrie', 'Dachneigung, Rampen, GPS-Peilung', 3, 3),
  -- Verhältnisse & Zeit
  ((SELECT id FROM categories WHERE slug='verhaeltnisse'), 'dreisatz',  'Dreisatz',           'Rezepte, Mischungen, Maßstäbe', 1, 1),
  ((SELECT id FROM categories WHERE slug='verhaeltnisse'), 'zeiten',    'Zeitrechnung',       'Fahrtzeiten, Zeitzonen, Zeitplanung', 2, 2),
  ((SELECT id FROM categories WHERE slug='verhaeltnisse'), 'prozente',  'Prozente',           'Wachstum, Abnahme, Inflation', 2, 3),
  -- Physik
  ((SELECT id FROM categories WHERE slug='physik'), 'mechanik',  'Mechanik',           'Geschwindigkeit, Kraft, Arbeit, Energie', 3, 1),
  ((SELECT id FROM categories WHERE slug='physik'), 'elektrik',  'Elektrizität',       'Stromverbrauch, Kosten, Ohmsches Gesetz', 3, 2),
  ((SELECT id FROM categories WHERE slug='physik'), 'thermik',   'Wärme & Energie',    'Heizkosten, Isolierung, Wärmebedarf', 3, 3),
  -- Informatik
  ((SELECT id FROM categories WHERE slug='informatik'), 'binaer',    'Binär & Hexadezimal','Umrechnung, Bitoperationen', 3, 1),
  ((SELECT id FROM categories WHERE slug='informatik'), 'daten',     'Dateigrößen',        'Speicher, Übertragungszeit, Kompression', 2, 2),
  ((SELECT id FROM categories WHERE slug='informatik'), 'algorithmen','Algorithmen',        'Komplexität, Laufzeit, Sortierung', 4, 3),
  -- Verkehr
  ((SELECT id FROM categories WHERE slug='verkehr'), 'fahrzeit',  'Fahrtzeit & Tempo',  'Autobahn, Stau, Durchschnittsgeschwindigkeit', 2, 1),
  ((SELECT id FROM categories WHERE slug='verkehr'), 'sprit',     'Spritverbrauch',     'Tankkosten, Verbrauch, E-Auto vs. Benziner', 2, 2),
  ((SELECT id FROM categories WHERE slug='verkehr'), 'bremsen',   'Bremswege',          'Reaktionsweg, Bremsweg, Sicherheitsabstand', 3, 3),
  -- Shopping
  ((SELECT id FROM categories WHERE slug='shopping'), 'preisvergleich','Preisvergleich',  'Grundpreis, Mengenrabatt, Sonderangebote', 1, 1),
  ((SELECT id FROM categories WHERE slug='shopping'), 'einheitspreis', 'Einheitspreis',   'Preis pro kg, Liter, Stück — was lohnt sich?', 1, 2),
  ((SELECT id FROM categories WHERE slug='shopping'), 'ratenkauf',     'Ratenkauf',       'Gesamtkosten, Zinsfalle, Barkauf vs. Raten', 2, 3)
ON CONFLICT (slug) DO NOTHING;

-- ============================================================
-- Aufgaben: Zinsen
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='zinsen'),
  'Tagesgeld-Zinsen',
  'Du legst 5.000 € auf ein Tagesgeldkonto mit 3,5 % Zinsen pro Jahr an. Wie viel Zinsen bekommst du nach einem Jahr (in €)?',
  'number', '175', 0.01, '€',
  'Zinsen = Kapital × Zinssatz = 5000 × 0,035 = 175 €',
  10, 1
),
(
  (SELECT id FROM topics WHERE slug='zinsen'),
  'Zinseszins nach 3 Jahren',
  'Du legst 2.000 € für 3 Jahre zu 4 % Zinsen p.a. an (Zinseszins). Auf welchen Betrag wächst dein Guthaben (gerundet auf Cent)?',
  'number', '2249.73', 0.02, '€',
  'K₃ = 2000 × 1,04³ = 2000 × 1,124864 = 2249,73 €',
  20, 2
),
(
  (SELECT id FROM topics WHERE slug='zinsen'),
  'Wie lange bis zur Verdopplung?',
  'Bei welchem Zinssatz verdoppelt sich ein Kapital in ca. 10 Jahren? Nutze die 72er-Regel.',
  'number', '7.2', 0.1, '%',
  'Die 72er-Regel: Zinssatz ≈ 72 ÷ Jahre = 72 ÷ 10 = 7,2 %',
  15, 2
);

-- ============================================================
-- Aufgaben: Kredite
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='kredite'),
  'Gesamtkosten Ratenkredit',
  'Du nimmst einen Kredit über 12.000 € auf, Laufzeit 3 Jahre, monatliche Rate 380 €. Wie viel zahlst du insgesamt zurück (in €)?',
  'number', '13680', 1, '€',
  '380 € × 36 Monate = 13.680 € → Zinskosten: 1.680 €',
  15, 2
),
(
  (SELECT id FROM topics WHERE slug='kredite'),
  'Welcher Kredit ist günstiger?',
  'Kredit A: 10.000 € in 24 Monaten à 450 €. Kredit B: 10.000 € in 36 Monaten à 310 €. Welcher kostet weniger Zinsen?',
  'multiple_choice', 'A',
  0, NULL,
  'A: 24×450 = 10.800 € (800 € Zinsen). B: 36×310 = 11.160 € (1.160 € Zinsen). Kredit A ist günstiger.',
  20, 2
),
(
  (SELECT id FROM topics WHERE slug='kredite'),
  'Effektiver Jahreszins',
  'Du leihst dir 500 € und zahlst nach einem Jahr 530 € zurück. Wie hoch ist der effektive Jahreszins in %?',
  'number', '6', 0.1, '%',
  '(530 - 500) / 500 × 100 = 30/500 × 100 = 6 %',
  10, 1
);

-- ============================================================
-- Aufgaben: Rabatte & Steuern
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='rabatte'),
  '20 % Rabatt',
  'Ein Jacke kostet 89,95 €. Im Sale gibt es 20 % Rabatt. Was zahlst du?',
  'number', '71.96', 0.02, '€',
  '89,95 × 0,80 = 71,96 €',
  10, 1
),
(
  (SELECT id FROM topics WHERE slug='rabatte'),
  'Netto aus Brutto',
  'Ein Laptop kostet brutto 1.190 € (19 % MwSt). Wie hoch ist der Nettopreis in €?',
  'number', '1000', 0.5, '€',
  'Netto = Brutto ÷ 1,19 = 1190 ÷ 1,19 = 1000 €',
  15, 2
);

-- ============================================================
-- Aufgaben: Flächen
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='flaechen'),
  'Wohnzimmer streichen',
  'Dein Wohnzimmer ist 5,5 m lang und 4,2 m breit. Ein Eimer Farbe deckt 12 m². Wie viele Eimer brauchst du (aufrunden)?',
  'number', '2', 0, 'Eimer',
  '5,5 × 4,2 = 23,1 m² ÷ 12 m² = 1,925 → 2 Eimer',
  10, 1
),
(
  (SELECT id FROM topics WHERE slug='flaechen'),
  'Kreisförmiger Pool',
  'Ein runder Pool hat einen Durchmesser von 4,8 m. Wie groß ist die Wasseroberfläche in m² (auf 2 Stellen gerundet)?',
  'number', '18.10', 0.05, 'm²',
  'r = 2,4 m → A = π × r² = π × 5,76 ≈ 18,10 m²',
  15, 2
);

-- ============================================================
-- Aufgaben: Volumina
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='volumina'),
  'Aquarium befüllen',
  'Ein Aquarium ist 80 cm lang, 35 cm breit und 40 cm hoch. Wie viele Liter fasst es (max. 90 % füllen)?',
  'number', '100.8', 1, 'Liter',
  '80×35×40 = 112.000 cm³ = 112 l × 0,9 = 100,8 l',
  15, 2
),
(
  (SELECT id FROM topics WHERE slug='volumina'),
  'Heizöltank',
  'Ein zylindrischer Heizöltank hat einen Radius von 0,6 m und eine Höhe von 2 m. Wie viele Liter fasst er (gerundet)?',
  'number', '2262', 5, 'Liter',
  'V = π × r² × h = π × 0,36 × 2 ≈ 2,262 m³ = 2262 l',
  20, 3
);

-- ============================================================
-- Aufgaben: Dreisatz
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='dreisatz'),
  'Rezept skalieren',
  'Ein Kuchenrezept für 4 Personen braucht 320 g Mehl. Wie viel Mehl brauchst du für 7 Personen?',
  'number', '560', 1, 'g',
  '320 g ÷ 4 × 7 = 560 g',
  10, 1
),
(
  (SELECT id FROM topics WHERE slug='dreisatz'),
  'Kartenlesen',
  'Auf einer Karte im Maßstab 1:50.000 misst eine Strecke 4,6 cm. Wie lang ist die echte Strecke in km?',
  'number', '2.3', 0.05, 'km',
  '4,6 cm × 50.000 = 230.000 cm = 2,3 km',
  15, 2
);

-- ============================================================
-- Aufgaben: Zeiten
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='zeiten'),
  'Zeitzone New York',
  'Es ist in Berlin 15:30 Uhr (MEZ, UTC+1). Wie spät ist es gleichzeitig in New York (EST, UTC-5)?',
  'number', '9', 0, 'Uhr',
  '15:30 - 6 Stunden = 09:30 → 9 Uhr',
  10, 1
),
(
  (SELECT id FROM topics WHERE slug='zeiten'),
  'Projektplanung',
  'Ein Projekt startet am 15. März. Es dauert 11 Wochen. An welchem Datum endet es (Tag im Monat im Juni)?',
  'number', '2', 0, '',
  '11 Wochen = 77 Tage. 15. März + 77 Tage = 31. März + 30 April + 16 Tage = 31. Mai + 2 Tage = 2. Juni',
  20, 2
);

-- ============================================================
-- Aufgaben: Physik – Elektrizität
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='elektrik'),
  'Stromkosten Backofen',
  'Dein Backofen hat 2.200 W. Du backst 3 mal pro Woche je 45 Minuten. Wie viel kWh verbrauchst du pro Monat (4 Wochen)?',
  'number', '9.9', 0.1, 'kWh',
  '2,2 kW × 0,75 h × 3 × 4 = 19,8 kWh ÷ 2… Nein: 2,2 × 0,75 = 1,65 kWh/Mal × 12 = 19,8 kWh → Sorry, 2,2 × 0,75 × 12 = 19,8. Korrekte Antwort: 19,8',
  15, 2
),
(
  (SELECT id FROM topics WHERE slug='elektrik'),
  'Ohmsches Gesetz',
  'Eine Glühbirne hat einen Widerstand von 240 Ω und liegt an 230 V. Welche Stromstärke fließt durch sie (in Ampere, auf 2 Stellen gerundet)?',
  'number', '0.96', 0.01, 'A',
  'I = U ÷ R = 230 ÷ 240 ≈ 0,96 A',
  20, 3
);

-- ============================================================
-- Aufgaben: Informatik – Dateigrößen
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='daten'),
  'Download-Zeit',
  'Du lädst eine Datei mit 2,4 GB bei 50 Mbit/s herunter. Wie lange dauert das in Minuten (gerundet)?',
  'number', '6.4', 0.1, 'min',
  '2,4 GB = 2400 MB = 19200 Mbit. 19200 ÷ 50 = 384 Sekunden = 6,4 Minuten',
  20, 3
),
(
  (SELECT id FROM topics WHERE slug='binaer'),
  'Binär zu Dezimal',
  'Was ist 1011 in Dezimaldarstellung?',
  'number', '11', 0, '',
  '1×8 + 0×4 + 1×2 + 1×1 = 8+2+1 = 11',
  15, 2
);

-- ============================================================
-- Aufgaben: Verkehr
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='fahrzeit'),
  'Durchschnittsgeschwindigkeit',
  'Du fährst 240 km in 2 Stunden 40 Minuten. Wie hoch ist deine Durchschnittsgeschwindigkeit in km/h?',
  'number', '90', 0.5, 'km/h',
  '2h40min = 2,667 h → 240 ÷ 2,667 ≈ 90 km/h',
  15, 2
),
(
  (SELECT id FROM topics WHERE slug='sprit'),
  'Tankkosten Vergleich',
  'Auto A verbraucht 7 l/100 km, Auto B 4,5 l/100 km. Benzin kostet 1,80 €/l. Wie viel sparst du mit Auto B auf 15.000 km im Jahr (in €)?',
  'number', '675', 1, '€',
  'A: 1050 l × 1,80 = 1890 €. B: 675 l × 1,80 = 1215 €. Ersparnis: 675 €',
  25, 2
),
(
  (SELECT id FROM topics WHERE slug='bremsen'),
  'Bremsweg bei 100 km/h',
  'Reaktionszeit 1 s, Geschwindigkeit 100 km/h. Wie lang ist der Reaktionsweg in Metern?',
  'number', '27.8', 0.2, 'm',
  '100 km/h = 27,78 m/s → in 1 s = 27,8 m',
  20, 3
);

-- ============================================================
-- Aufgaben: Shopping
-- ============================================================
INSERT INTO tasks (topic_id, title, question, answer_type, correct, tolerance, unit, explanation, xp_reward, difficulty) VALUES
(
  (SELECT id FROM topics WHERE slug='einheitspreis'),
  'Welches Angebot lohnt sich?',
  '500 g Kaffee für 4,99 € oder 750 g für 6,99 €. Welche Packung hat den günstigeren Kilopreis?',
  'multiple_choice', '750g',
  0, NULL,
  '500 g: 9,98 €/kg. 750 g: 9,32 €/kg → 750 g ist günstiger.',
  10, 1
),
(
  (SELECT id FROM topics WHERE slug='ratenkauf'),
  'Ratenkauf-Falle',
  'Ein TV kostet bar 799 €. Per Ratenkauf: 24 × 39,90 €. Wie viel Aufpreis zahlst du in %?',
  'number', '19.7', 0.5, '%',
  '24 × 39,90 = 957,60 €. Aufpreis: 158,60 €. 158,60/799 × 100 ≈ 19,9 %',
  20, 2
);