import sqlite3
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
DB_PATH = BASE_DIR / "pflegebudget.db"

def get_db_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db_connection()
    cur = conn.cursor()

    cur.execute("""
        CREATE TABLE IF NOT EXISTS kunden (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kundennummer TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
    """)

    cur.execute("""
        CREATE TABLE IF NOT EXISTS budget_monatsstaende (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kunden_id INTEGER NOT NULL,
            jahr INTEGER NOT NULL,
            monat INTEGER NOT NULL,

            pflegegrad INTEGER NOT NULL,
            stundensatz REAL NOT NULL,
            fahrtpauschale REAL NOT NULL,

            uebertrag_entlastung REAL DEFAULT 0,
            verbrauch_entlastung_bisher_jahr REAL DEFAULT 0,
            verbrauch_entlastung_monat REAL DEFAULT 0,
            verbrauch_sachleistung_monat REAL DEFAULT 0,
            verbrauch_verhinderung_monat REAL DEFAULT 0,
            verbrauch_verhinderung_bisher_jahr REAL DEFAULT 0,

            berechnung_json TEXT,
            form_data_json TEXT,

            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,

            UNIQUE(kunden_id, jahr, monat),
            FOREIGN KEY (kunden_id) REFERENCES kunden(id)
        )
    """)

    cur.execute("""
        CREATE TABLE IF NOT EXISTS budget_einsaetze (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kunden_id INTEGER NOT NULL,
            jahr INTEGER NOT NULL,
            monat INTEGER NOT NULL,

            leistung TEXT,
            anbieter TEXT,
            topf TEXT,
            betrag REAL DEFAULT 0,

            status TEXT DEFAULT 'geplant',
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,

            FOREIGN KEY (kunden_id) REFERENCES kunden(id)
        )
    """)

    conn.commit()
    conn.close()