import sqlite3
import json
from datetime import datetime


SCHEMA = """
CREATE TABLE IF NOT EXISTS orders (
    number TEXT PRIMARY KEY,
    date TEXT,
    status TEXT,
    buyerName TEXT,
    buyerName1 TEXT,
    buyerStreet TEXT,
    buyerPostalCode TEXT,
    buyerCity TEXT,
    buyerCountry TEXT,
    buyerEMail TEXT,
    buyerPhone TEXT,
    buyerTaxNumber TEXT,
    deliveryName TEXT,
    deliveryStreet TEXT,
    deliveryPostalCode TEXT,
    deliveryCity TEXT,
    deliveryCountry TEXT,
    items TEXT,
    deliveryMethod TEXT,
    methodOfPayment TEXT,
    totalAmountInDomCurr REAL,
    documentCurrency TEXT,
    remarks TEXT,
    shopify_id TEXT,
    shopify_order_name TEXT,
    shopify_customer_name TEXT,
    shopify_financial_status TEXT,
    shopify_fulfillment_status TEXT,
    language TEXT,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
    productCode TEXT PRIMARY KEY,
    name TEXT,
    englishName TEXT,
    germanName TEXT,
    barcode TEXT,
    defaultWarehouseLocation TEXT
);

CREATE TABLE IF NOT EXISTS skipped_orders (
    order_number TEXT PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
);
"""


def init_db(db_path):
    con = sqlite3.connect(db_path)
    con.executescript(SCHEMA)
    # Migrations: add columns that may not exist in older DBs
    for col, definition in [
        ('language',               'TEXT'),
        ('deliveryName',           'TEXT'),
        ('deliveryStreet',         'TEXT'),
        ('deliveryPostalCode',     'TEXT'),
        ('deliveryCity',           'TEXT'),
        ('deliveryCountry',        'TEXT'),
        ('shopify_order_name',          'TEXT'),
        ('shopify_customer_name',       'TEXT'),
        ('shopify_financial_status',    'TEXT'),
        ('shopify_fulfillment_status',  'TEXT'),
    ]:
        try:
            con.execute(f'ALTER TABLE orders ADD COLUMN {col} {definition}')
        except Exception:
            pass  # column already exists
    con.commit()
    con.close()


def get_db(db_path):
    con = sqlite3.connect(db_path)
    con.row_factory = sqlite3.Row
    return con


# ── Orders ────────────────────────────────────────────────────────────────────

def upsert_orders(orders: list, db_path: str):
    con = get_db(db_path)
    try:
        for order in orders:
            items = order.get('items')
            if isinstance(items, (dict, list)):
                items = json.dumps(items)
            con.execute(
                """INSERT INTO orders (
                    number, date, status,
                    buyerName, buyerName1,
                    buyerStreet, buyerPostalCode, buyerCity, buyerCountry,
                    buyerEMail, buyerPhone, buyerTaxNumber,
                    deliveryName, deliveryStreet, deliveryPostalCode, deliveryCity, deliveryCountry,
                    items, deliveryMethod, methodOfPayment,
                    totalAmountInDomCurr, documentCurrency,
                    remarks, shopify_id, language, updated_at
                ) VALUES (
                    :number, :date, :status,
                    :buyerName, :buyerName1,
                    :buyerStreet, :buyerPostalCode, :buyerCity, :buyerCountry,
                    :buyerEMail, :buyerPhone, :buyerTaxNumber,
                    :deliveryName, :deliveryStreet, :deliveryPostalCode, :deliveryCity, :deliveryCountry,
                    :items, :deliveryMethod, :methodOfPayment,
                    :totalAmountInDomCurr, :documentCurrency,
                    :remarks, :shopify_id, :language, :updated_at
                )
                ON CONFLICT(number) DO UPDATE SET
                    date=excluded.date, status=excluded.status,
                    buyerName=excluded.buyerName, buyerName1=excluded.buyerName1,
                    buyerStreet=excluded.buyerStreet, buyerPostalCode=excluded.buyerPostalCode,
                    buyerCity=excluded.buyerCity, buyerCountry=excluded.buyerCountry,
                    buyerEMail=excluded.buyerEMail, buyerPhone=excluded.buyerPhone,
                    buyerTaxNumber=excluded.buyerTaxNumber,
                    deliveryName=excluded.deliveryName, deliveryStreet=excluded.deliveryStreet,
                    deliveryPostalCode=excluded.deliveryPostalCode, deliveryCity=excluded.deliveryCity,
                    deliveryCountry=excluded.deliveryCountry,
                    items=excluded.items, deliveryMethod=excluded.deliveryMethod,
                    methodOfPayment=excluded.methodOfPayment,
                    totalAmountInDomCurr=excluded.totalAmountInDomCurr,
                    documentCurrency=excluded.documentCurrency,
                    remarks=excluded.remarks,
                    shopify_id=COALESCE(excluded.shopify_id, orders.shopify_id),
                    language=excluded.language, updated_at=excluded.updated_at""",
                {
                    'number':             order.get('number'),
                    'date':               order.get('date'),
                    'status':             order.get('status'),
                    'buyerName':          order.get('buyerName'),
                    'buyerName1':         order.get('buyerName1'),
                    'buyerStreet':        order.get('buyerStreet'),
                    'buyerPostalCode':    order.get('buyerPostalCode'),
                    'buyerCity':          order.get('buyerCity'),
                    'buyerCountry':       order.get('buyerCountry'),
                    'buyerEMail':         order.get('buyerEMail'),
                    'buyerPhone':         order.get('buyerPhone'),
                    'buyerTaxNumber':     order.get('buyerTaxNumber'),
                    'deliveryName':       order.get('deliveryName', ''),
                    'deliveryStreet':     order.get('deliveryStreet', ''),
                    'deliveryPostalCode': order.get('deliveryPostalCode', ''),
                    'deliveryCity':       order.get('deliveryCity', ''),
                    'deliveryCountry':    order.get('deliveryCountry', ''),
                    'items':              items,
                    'deliveryMethod':     order.get('deliveryMethod'),
                    'methodOfPayment':    order.get('methodOfPayment'),
                    'totalAmountInDomCurr': order.get('totalAmountInDomCurr'),
                    'documentCurrency':   order.get('documentCurrency'),
                    'remarks':            order.get('remarks'),
                    'shopify_id':         order.get('shopify_id'),
                    'language':           order.get('language'),
                    'updated_at':         datetime.utcnow().isoformat(),
                }
            )
        con.commit()
    finally:
        con.close()


def set_order_shopify_id(number: str, shopify_id: str, db_path: str,
                         shopify_order_name: str = None,
                         shopify_customer_name: str = None,
                         shopify_financial_status: str = None,
                         shopify_fulfillment_status: str = None) -> bool:
    """Update shopify_id (and optional Shopify metadata) for an order by its e-računi number.
    Returns True if the order existed."""
    con = get_db(db_path)
    try:
        cur = con.execute(
            """UPDATE orders
               SET shopify_id = ?,
                   shopify_order_name = COALESCE(?, shopify_order_name),
                   shopify_customer_name = COALESCE(?, shopify_customer_name),
                   shopify_financial_status = COALESCE(?, shopify_financial_status),
                   shopify_fulfillment_status = COALESCE(?, shopify_fulfillment_status)
               WHERE number = ?""",
            (shopify_id, shopify_order_name, shopify_customer_name,
             shopify_financial_status, shopify_fulfillment_status, number),
        )
        con.commit()
        return cur.rowcount > 0
    finally:
        con.close()


def get_sync_mappings(db_path: str) -> list:
    """Return all orders (with or without a Shopify link), ordered by date descending."""
    con = get_db(db_path)
    try:
        rows = con.execute(
            """SELECT number, date, buyerName, status, shopify_id,
                      shopify_order_name, shopify_customer_name,
                      shopify_financial_status, shopify_fulfillment_status,
                      totalAmountInDomCurr, documentCurrency
               FROM orders
               WHERE status != 'Draft'
               ORDER BY date DESC, number DESC"""
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        con.close()


def get_all_orders(db_path: str, exclude_statuses=None):
    if exclude_statuses is None:
        exclude_statuses = ['Draft']
    con = get_db(db_path)
    try:
        placeholders = ','.join('?' for _ in exclude_statuses)
        rows = con.execute(
            f"SELECT * FROM orders WHERE status NOT IN ({placeholders}) ORDER BY date DESC, number DESC",
            exclude_statuses
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        con.close()


def get_processing_orders(db_path: str):
    con = get_db(db_path)
    try:
        rows = con.execute(
            """SELECT o.* FROM orders o
               WHERE o.status = 'Processing'
                 AND o.number LIKE '%S%'
                 AND o.number NOT IN (SELECT order_number FROM skipped_orders)
               ORDER BY o.date ASC, o.number ASC""",
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        con.close()


def get_order(number: str, db_path: str):
    con = get_db(db_path)
    try:
        row = con.execute("SELECT * FROM orders WHERE number = ?", (number,)).fetchone()
        return dict(row) if row else None
    finally:
        con.close()


def update_order_shopify_id(number: str, shopify_id: str, db_path: str):
    con = get_db(db_path)
    try:
        con.execute("UPDATE orders SET shopify_id = ? WHERE number = ?", (shopify_id, number))
        con.commit()
    finally:
        con.close()


def update_order_status(number: str, status: str, db_path: str):
    con = get_db(db_path)
    try:
        con.execute(
            "UPDATE orders SET status = ?, updated_at = ? WHERE number = ?",
            (status, datetime.utcnow().isoformat(), number)
        )
        con.commit()
    finally:
        con.close()


def skip_order(number: str, db_path: str):
    con = get_db(db_path)
    try:
        con.execute("INSERT OR IGNORE INTO skipped_orders (order_number) VALUES (?)", (number,))
        con.commit()
    finally:
        con.close()


def unskip_order(number: str, db_path: str):
    con = get_db(db_path)
    try:
        con.execute("DELETE FROM skipped_orders WHERE order_number = ?", (number,))
        con.commit()
    finally:
        con.close()


def get_skipped_orders(db_path: str):
    con = get_db(db_path)
    try:
        rows = con.execute("SELECT order_number FROM skipped_orders").fetchall()
        return [r['order_number'] for r in rows]
    finally:
        con.close()


# ── Products ──────────────────────────────────────────────────────────────────

def upsert_products(products: list, db_path: str):
    con = get_db(db_path)
    try:
        for p in products:
            con.execute(
                """INSERT OR REPLACE INTO products
                   (productCode, name, englishName, germanName, barcode, defaultWarehouseLocation)
                   VALUES (:productCode, :name, :englishName, :germanName, :barcode, :defaultWarehouseLocation)""",
                {
                    'productCode': p.get('productCode'),
                    'name': p.get('name'),
                    'englishName': p.get('englishName'),
                    'germanName': p.get('germanName'),
                    'barcode': p.get('barcode'),
                    'defaultWarehouseLocation': p.get('defaultWarehouseLocation'),
                }
            )
        con.commit()
    finally:
        con.close()


def get_product(product_code: str, db_path: str):
    con = get_db(db_path)
    try:
        row = con.execute("SELECT * FROM products WHERE productCode = ?", (product_code,)).fetchone()
        return dict(row) if row else None
    finally:
        con.close()


def get_all_products(db_path: str) -> list:
    con = get_db(db_path)
    try:
        rows = con.execute("SELECT * FROM products ORDER BY productCode ASC").fetchall()
        return [dict(r) for r in rows]
    finally:
        con.close()


# ── Users ─────────────────────────────────────────────────────────────────────

def get_user_by_id(user_id: int, db_path: str):
    con = get_db(db_path)
    try:
        row = con.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
        return dict(row) if row else None
    finally:
        con.close()


def get_user_by_email(email: str, db_path: str):
    con = get_db(db_path)
    try:
        row = con.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()
        return dict(row) if row else None
    finally:
        con.close()


def get_all_users(db_path: str):
    con = get_db(db_path)
    try:
        rows = con.execute("SELECT id, email, created_at FROM users ORDER BY created_at ASC").fetchall()
        return [dict(r) for r in rows]
    finally:
        con.close()


def create_user(email: str, password_hash: str, db_path: str):
    con = get_db(db_path)
    try:
        con.execute(
            "INSERT OR IGNORE INTO users (email, password_hash) VALUES (?, ?)",
            (email, password_hash)
        )
        con.commit()
    finally:
        con.close()


def delete_user(user_id: int, db_path: str):
    con = get_db(db_path)
    try:
        con.execute("DELETE FROM users WHERE id = ?", (user_id,))
        con.commit()
    finally:
        con.close()


def user_exists(db_path: str) -> bool:
    """Return True if at least one user account exists."""
    con = get_db(db_path)
    try:
        row = con.execute("SELECT COUNT(*) as c FROM users").fetchone()
        return row['c'] > 0
    finally:
        con.close()


# ── Settings ──────────────────────────────────────────────────────────────────

def get_setting(key: str, db_path: str, default=None):
    con = get_db(db_path)
    try:
        row = con.execute("SELECT value FROM settings WHERE key = ?", (key,)).fetchone()
        return row['value'] if row else default
    finally:
        con.close()


def set_setting(key: str, value: str, db_path: str):
    con = get_db(db_path)
    try:
        con.execute(
            "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
            (key, value)
        )
        con.commit()
    finally:
        con.close()


def get_all_settings(db_path: str) -> dict:
    """Return all settings as a plain dict."""
    con = get_db(db_path)
    try:
        rows = con.execute("SELECT key, value FROM settings").fetchall()
        return {r['key']: r['value'] for r in rows}
    finally:
        con.close()


def save_settings(data: dict, db_path: str):
    """Bulk-save a dict of key→value pairs, skipping empty strings."""
    con = get_db(db_path)
    try:
        for key, value in data.items():
            if value != '':
                con.execute(
                    "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
                    (key, value)
                )
        con.commit()
    finally:
        con.close()