from core.db import get_db

def _normalize_number(value, default=0, allow_null=False):
    if value is None or value == "":
        return None if allow_null else default

    try:
        num = float(value)
        return num
    except (TypeError, ValueError):
        return None if allow_null else default

def _ensure_columns():
    db = get_db()
    columns = [row["name"] for row in db.execute("PRAGMA table_info(urunler)").fetchall()]

    if "fiyat" not in columns:
      db.execute("ALTER TABLE urunler ADD COLUMN fiyat REAL DEFAULT 0")

    if "aciklama" not in columns:
      db.execute("ALTER TABLE urunler ADD COLUMN aciklama TEXT")

    if "etiketler" not in columns:
      db.execute("ALTER TABLE urunler ADD COLUMN etiketler TEXT")

    if "olusturma_tarihi" not in columns:
      db.execute("ALTER TABLE urunler ADD COLUMN olusturma_tarihi TEXT DEFAULT (datetime('now','localtime'))")

    db.commit()

def search_products(q="", stokta_olan=False):
    _ensure_columns()
    db = get_db()

    sql = """
        SELECT id, ad, sku, stok, maliyet, fiyat, aciklama, etiketler, olusturma_tarihi
        FROM urunler
        WHERE 1=1
    """
    params = []

    if q:
        sql += " AND (ad LIKE ? OR COALESCE(sku,'') LIKE ? OR COALESCE(aciklama,'') LIKE ? OR COALESCE(etiketler,'') LIKE ?)"
        like = f"%{q}%"
        params.extend([like, like, like, like])

    if stokta_olan:
        sql += " AND COALESCE(stok,0) > 0"

    sql += " ORDER BY ad LIMIT 10"

    rows = db.execute(sql, params).fetchall()
    return [dict(row) for row in rows]

def list_products():
    _ensure_columns()
    db = get_db()

    rows = db.execute("""
        SELECT id, ad, sku, stok, maliyet, fiyat, aciklama, etiketler, olusturma_tarihi
        FROM urunler
        ORDER BY ad
    """).fetchall()

    return [dict(row) for row in rows]

def create_product(data):
    _ensure_columns()
    db = get_db()

    ad = (data.get("ad") or "").strip()
    stok = _normalize_number(data.get("stok"), default=0)
    fiyat = _normalize_number(data.get("fiyat"), default=0)
    aciklama = (data.get("aciklama") or "").strip()
    etiketler = (data.get("etiketler") or "").strip()
    maliyet = _normalize_number(data.get("maliyet"), allow_null=True)

    if not ad:
        return {"ok": False, "hata": "Ürün adı zorunlu"}

    if stok is None or stok < 0:
        return {"ok": False, "hata": "Stok hatalı"}

    if fiyat is None or fiyat < 0:
        return {"ok": False, "hata": "Fiyat hatalı"}

    if maliyet is not None and maliyet < 0:
        return {"ok": False, "hata": "Maliyet hatalı"}

    exists = db.execute("SELECT id FROM urunler WHERE lower(ad)=lower(?)", (ad,)).fetchone()
    if exists:
        return {"ok": False, "hata": "Bu isimde ürün zaten var"}

    db.execute("""
        INSERT INTO urunler (ad, stok, maliyet, fiyat, aciklama, etiketler)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (ad, int(stok), maliyet, float(fiyat), aciklama, etiketler))
    db.commit()

    return {"ok": True}

def update_product(urun_id, data):
    _ensure_columns()
    db = get_db()

    current = db.execute("SELECT id FROM urunler WHERE id = ?", (urun_id,)).fetchone()
    if not current:
        return {"ok": False, "hata": "Ürün bulunamadı"}

    ad = (data.get("ad") or "").strip()
    stok = _normalize_number(data.get("stok"), default=0)
    fiyat = _normalize_number(data.get("fiyat"), default=0)
    aciklama = (data.get("aciklama") or "").strip()
    etiketler = (data.get("etiketler") or "").strip()
    maliyet = _normalize_number(data.get("maliyet"), allow_null=True)

    if not ad:
        return {"ok": False, "hata": "Ürün adı zorunlu"}

    if stok is None or stok < 0:
        return {"ok": False, "hata": "Stok hatalı"}

    if fiyat is None or fiyat < 0:
        return {"ok": False, "hata": "Fiyat hatalı"}

    if maliyet is not None and maliyet < 0:
        return {"ok": False, "hata": "Maliyet hatalı"}

    exists = db.execute(
        "SELECT id FROM urunler WHERE lower(ad)=lower(?) AND id <> ?",
        (ad, urun_id)
    ).fetchone()

    if exists:
        return {"ok": False, "hata": "Bu isimde başka ürün zaten var"}

    db.execute("""
        UPDATE urunler
        SET ad = ?, stok = ?, maliyet = ?, fiyat = ?, aciklama = ?, etiketler = ?
        WHERE id = ?
    """, (ad, int(stok), maliyet, float(fiyat), aciklama, etiketler, urun_id))
    db.commit()

    return {"ok": True}

def delete_product(urun_id):
    db = get_db()

    current = db.execute("SELECT id FROM urunler WHERE id = ?", (urun_id,)).fetchone()
    if not current:
        return {"ok": False, "hata": "Ürün bulunamadı"}

    db.execute("DELETE FROM urunler WHERE id = ?", (urun_id,))
    db.commit()

    return {"ok": True}