import os
import sqlite3
from flask import g
from core.config import DB_PATH


def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH)
        g.db.row_factory = sqlite3.Row
    return g.db


def close_db(e=None):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True) if os.path.dirname(DB_PATH) else None

    db = sqlite3.connect(DB_PATH)
    db.row_factory = sqlite3.Row

    db.executescript("""
        CREATE TABLE IF NOT EXISTS ayarlar (
            anahtar TEXT PRIMARY KEY,
            deger TEXT
        );

        CREATE TABLE IF NOT EXISTS musteriler (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ad_soyad TEXT NOT NULL,
            telefon TEXT,
            tc_no TEXT,
            olusturma_tarihi TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS urunler (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ad TEXT NOT NULL UNIQUE,
            sku TEXT,
            stok INTEGER DEFAULT 0,
            maliyet REAL DEFAULT 0,
            fiyat REAL DEFAULT 0,
            aciklama TEXT,
            etiketler TEXT,
            olusturma_tarihi TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS satislar (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            musteri_id INTEGER,
            musteri_ad TEXT,
            urun_id INTEGER,
            urun_ad TEXT,
            satis_fiyati REAL DEFAULT 0,
            pesinat REAL DEFAULT 0,
            kalan REAL DEFAULT 0,
            notlar TEXT,
            tarih TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (musteri_id) REFERENCES musteriler(id),
            FOREIGN KEY (urun_id) REFERENCES urunler(id)
        );

        CREATE TABLE IF NOT EXISTS stok_hareketleri (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            urun_id INTEGER,
            urun_ad TEXT,
            hareket_turu TEXT,
            miktar INTEGER,
            birim_maliyet REAL DEFAULT 0,
            aciklama TEXT,
            tarih TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (urun_id) REFERENCES urunler(id)
        );

        CREATE TABLE IF NOT EXISTS odemeler (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            satis_id INTEGER NOT NULL,
            miktar REAL NOT NULL,
            notlar TEXT,
            tarih TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (satis_id) REFERENCES satislar(id)
        );
    """)

    ensure_schema(db)

    db.commit()
    db.close()


def ensure_schema(db=None):
    owns_connection = False

    if db is None:
        db = sqlite3.connect(DB_PATH)
        db.row_factory = sqlite3.Row
        owns_connection = True

    try:
        ensure_urunler_columns(db)
        ensure_musteriler_columns(db)
        ensure_satislar_columns(db)
        ensure_stok_hareketleri_columns(db)
        ensure_odemeler_table(db)
        db.commit()
    finally:
        if owns_connection:
            db.close()


def ensure_urunler_columns(db):
    columns = get_table_columns(db, "urunler")

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

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

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

    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")


def ensure_musteriler_columns(db):
    columns = get_table_columns(db, "musteriler")

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

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

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


def ensure_satislar_columns(db):
    columns = get_table_columns(db, "satislar")

    if "musteri_id" not in columns:
        db.execute("ALTER TABLE satislar ADD COLUMN musteri_id INTEGER")

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

    if "urun_id" not in columns:
        db.execute("ALTER TABLE satislar ADD COLUMN urun_id INTEGER")

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

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

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

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

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

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

    if "kaynak" not in columns:
        db.execute("ALTER TABLE satislar ADD COLUMN kaynak TEXT DEFAULT 'normal'")


def ensure_stok_hareketleri_columns(db):
    columns = get_table_columns(db, "stok_hareketleri")

    if "urun_id" not in columns:
        db.execute("ALTER TABLE stok_hareketleri ADD COLUMN urun_id INTEGER")

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

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

    if "miktar" not in columns:
        db.execute("ALTER TABLE stok_hareketleri ADD COLUMN miktar INTEGER")

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

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

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


def get_table_columns(db, table_name):
    rows = db.execute(f"PRAGMA table_info({table_name})").fetchall()
    return [row["name"] for row in rows]

def ensure_odemeler_table(db):
    db.execute("""
        CREATE TABLE IF NOT EXISTS odemeler (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            satis_id INTEGER NOT NULL,
            miktar REAL NOT NULL,
            notlar TEXT,
            tarih TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (satis_id) REFERENCES satislar(id)
        )
    """)
