import os import json import urllib.request import threading import time from datetime import datetime, timedelta, timezone def sync_remote_shop_to_supabase_db(payload): """Directly insert/update Shop & User into central Supabase PostgreSQL DB if DATABASE_URL is set.""" cloud_url = get_cloud_db_url() try: import psycopg2 conn = psycopg2.connect(cloud_url, connect_timeout=2) conn.autocommit = True cur = conn.cursor() shop_name = payload.get("shop_name", "Shop") owner_name = payload.get("owner_name", "Owner") phone = payload.get("phone") email = payload.get("email") admin_username = payload.get("admin_username") or email or phone or "admin" admin_password = payload.get("admin_password", "123456") existing_shop = None if phone: cur.execute("SELECT id FROM shop WHERE phone = %s", (phone,)) res = cur.fetchone() if res: existing_shop = res[0] if not existing_shop and email: cur.execute("SELECT s.id FROM shop s JOIN \"user\" u ON s.id = u.shop_id WHERE u.email = %s", (email,)) res = cur.fetchone() if res: existing_shop = res[0] now_bd = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6) trial_expiry = now_bd + timedelta(days=7) if not existing_shop: cur.execute(""" INSERT INTO shop (shop_name, owner_name, phone, active, license_expires_at, subscription_plan, created_at) VALUES (%s, %s, %s, TRUE, %s, 'Free Trial (7 Days)', %s) RETURNING id """, (shop_name, owner_name, phone, trial_expiry, now_bd)) shop_id = cur.fetchone()[0] else: shop_id = existing_shop cur.execute("SELECT id FROM \"user\" WHERE username = %s OR (email IS NOT NULL AND email = %s)", (admin_username, email if email else "__none__")) u_res = cur.fetchone() if not u_res: from werkzeug.security import generate_password_hash pw_hash = generate_password_hash(admin_password) cur.execute(""" INSERT INTO "user" (shop_id, username, password_hash, role, email, phone, must_change_password) VALUES (%s, %s, %s, 'owner', %s, %s, FALSE) ON CONFLICT (username) DO UPDATE SET password_hash = EXCLUDED.password_hash, email = EXCLUDED.email, phone = EXCLUDED.phone; """, (shop_id, admin_username, pw_hash, email, phone)) conn.close() print(f"[SUPABASE DIRECT SYNC SUCCESS] Shop '{shop_name}' registered directly to Supabase cloud DB!") return True except Exception as ex: print(f"[SUPABASE DIRECT SYNC WARNING] {ex}") return False def sync_payment_request_to_supabase_db(payload): """Directly insert a SubscriptionPayment into central Supabase PostgreSQL DB.""" cloud_url = get_cloud_db_url() try: import psycopg2 conn = psycopg2.connect(cloud_url, connect_timeout=6) conn.autocommit = True cur = conn.cursor() shop_id = payload.get("shop_id") phone = payload.get("phone") shop_name = payload.get("shop_name") cloud_shop_id = None if shop_id: cur.execute("SELECT id FROM shop WHERE id = %s", (shop_id,)) res = cur.fetchone() if res: cloud_shop_id = res[0] if not cloud_shop_id and phone: cur.execute("SELECT id FROM shop WHERE phone = %s", (phone,)) res = cur.fetchone() if res: cloud_shop_id = res[0] if not cloud_shop_id and shop_name: cur.execute("SELECT id FROM shop WHERE shop_name = %s", (shop_name,)) res = cur.fetchone() if res: cloud_shop_id = res[0] if not cloud_shop_id: cur.execute(""" INSERT INTO shop (shop_name, owner_name, phone, active, subscription_plan, created_at) VALUES (%s, %s, %s, TRUE, %s, %s) RETURNING id """, (shop_name or "Shop", payload.get("owner_name", "Owner"), phone, payload.get("requested_plan", "Monthly (৳500)"), datetime.now())) cloud_shop_id = cur.fetchone()[0] amount = float(payload.get("amount_paid") or 500.0) pay_method = payload.get("payment_method", "bKash") or "bKash" trx_id = payload.get("transaction_id", "") or "" duration_days = int(payload.get("duration_days") or 30) old_expiry = payload.get("old_expiry_date") proof = payload.get("payment_proof", "") or "" plan = payload.get("requested_plan", "") or "" note = f"Requested Package: {plan} | {proof}" if plan else proof cur.execute(""" INSERT INTO subscription_payment (shop_id, amount_paid, payment_method, transaction_id, duration_days, old_expiry_date, payment_date, note, status, payment_proof) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'Pending', %s) """, ( cloud_shop_id, amount, pay_method, trx_id, duration_days, old_expiry, datetime.now(), note, proof )) if plan: cur.execute("UPDATE shop SET subscription_plan = %s WHERE id = %s", (plan, cloud_shop_id)) conn.close() print(f"[SUPABASE PAYMENT SYNC SUCCESS] Payment request for shop {cloud_shop_id} ({shop_name}) synced to Supabase Cloud DB!") return True except Exception as ex: print(f"[SUPABASE PAYMENT SYNC WARNING] {ex}") return False def get_cloud_db_url(): url = os.environ.get("DATABASE_URL", "").strip() if not url or not url.startswith("postgres"): url = "postgresql://postgres.rjpbrsqtligqoxwowemy:%40Gronext2024@aws-0-ap-northeast-1.pooler.supabase.com:5432/postgres?sslmode=require" elif "sslmode" not in url: sep = "&" if "?" in url else "?" url = f"{url}{sep}sslmode=require" return url def authenticate_and_sync_cloud_user(login_input, password, app, db, User, Shop): """ Checks Supabase Cloud DB for user credentials if not found locally, and automatically syncs the Shop and all staff Users down to local SQLite. """ if not login_input or not password: return None val = login_input.strip().lower() cloud_url = get_cloud_db_url() try: import psycopg2 from psycopg2.extras import RealDictCursor from werkzeug.security import check_password_hash from sqlalchemy import func conn = psycopg2.connect(cloud_url, connect_timeout=2) conn.autocommit = True cur = conn.cursor(cursor_factory=RealDictCursor) # Look for user in Supabase cur.execute(""" SELECT * FROM "user" WHERE lower(email) = %s OR lower(username) = %s OR lower(phone) = %s """, (val, val, val)) cloud_users = cur.fetchall() matched_cu = None for cu in cloud_users: pw_hash = cu.get("password_hash") if pw_hash and check_password_hash(pw_hash, password): matched_cu = cu break if not matched_cu: conn.close() return None # Fetch associated shop and all staff users from Supabase cloud_shop = None cloud_all_users = [] if matched_cu.get("shop_id"): cur.execute("SELECT * FROM shop WHERE id = %s", (matched_cu["shop_id"],)) cloud_shop = cur.fetchone() cur.execute("SELECT * FROM \"user\" WHERE shop_id = %s", (matched_cu["shop_id"],)) cloud_all_users = cur.fetchall() conn.close() # Now sync down to local SQLite with app.app_context(): local_shop = None if cloud_shop: sname = (cloud_shop.get("shop_name") or "").strip() sphone = (cloud_shop.get("phone") or "").strip() if cloud_shop.get("phone") else None if sphone: local_shop = Shop.query.filter(Shop.phone == sphone).first() if not local_shop and sname: local_shop = Shop.query.filter(Shop.shop_name == sname).first() if not local_shop: local_shop = Shop( id=cloud_shop.get("id"), shop_name=sname or "Shop", owner_name=cloud_shop.get("owner_name", "Owner"), phone=sphone, active=bool(cloud_shop.get("active", True)), subscription_plan=cloud_shop.get("subscription_plan", "Free Trial (7 Days)"), license_expires_at=cloud_shop.get("license_expires_at"), created_at=cloud_shop.get("created_at") or datetime.now() ) db.session.add(local_shop) db.session.flush() else: local_shop.active = bool(cloud_shop.get("active", True)) local_shop.subscription_plan = cloud_shop.get("subscription_plan", local_shop.subscription_plan) if cloud_shop.get("license_expires_at"): local_shop.license_expires_at = cloud_shop.get("license_expires_at") db.session.flush() local_sid = local_shop.id if local_shop else 1 target_user = None # Sync all users from cloud shop down to local SQLite users_to_sync = cloud_all_users if cloud_all_users else [matched_cu] for cu in users_to_sync: u_email = cu.get("email") u_uname = cu.get("username") u_phone = cu.get("phone") lu = None if u_email: lu = User.query.filter(func.lower(User.email) == u_email.lower()).first() if not lu and u_phone: lu = User.query.filter(User.phone == u_phone).first() if not lu and u_uname: lu = User.query.filter(func.lower(User.username) == u_uname.lower()).first() if not lu: lu = User( shop_id=local_sid, username=u_uname or f"user_{cu.get('id', 1)}", email=u_email, phone=u_phone, password_hash=cu.get("password_hash"), role=cu.get("role", "cashier"), must_change_password=bool(cu.get("must_change_password", False)) ) db.session.add(lu) else: lu.shop_id = local_sid lu.password_hash = cu.get("password_hash", lu.password_hash) lu.role = cu.get("role", lu.role) if u_email: lu.email = u_email if u_phone: lu.phone = u_phone if cu.get("id") == matched_cu.get("id") or (u_phone and u_phone == matched_cu.get("phone")) or (u_email and u_email == matched_cu.get("email")) or (u_uname and u_uname.lower() == val): target_user = lu db.session.commit() print(f"[CLOUD AUTH SUCCESS] User '{target_user.username if target_user else 'user'}' and shop accounts synced from Supabase Cloud!") return target_user or lu except Exception as ex: print(f"[CLOUD AUTH WARNING] {ex}") return None def sync_shop_info_from_cloud(shop_id, app, db): """ Pulls the latest shop metadata (shop_name, owner_name, phone, address, active, license_expires_at) from central Supabase Cloud DB / Owner Portal into local SQLite Shop record. """ cloud_url = get_cloud_db_url() try: import psycopg2 from psycopg2.extras import RealDictCursor from models import Shop with app.app_context(): local_shop = db.session.get(Shop, shop_id) if not local_shop: return False, "Local shop not found" conn = psycopg2.connect(cloud_url, connect_timeout=5) conn.autocommit = True cur = conn.cursor(cursor_factory=RealDictCursor) cur.execute("SELECT * FROM shop WHERE id = %s", (shop_id,)) res = cur.fetchone() if not res and local_shop.phone: cur.execute("SELECT * FROM shop WHERE phone = %s", (local_shop.phone,)) res = cur.fetchone() conn.close() if res: changed = False cloud_sname = (res.get("shop_name") or "").strip() if cloud_sname and cloud_sname != local_shop.shop_name: print(f"[AUTO SYNC] Updating local shop name: '{local_shop.shop_name}' -> '{cloud_sname}'") local_shop.shop_name = cloud_sname changed = True cloud_oname = (res.get("owner_name") or "").strip() if cloud_oname and cloud_oname != (local_shop.owner_name or ""): local_shop.owner_name = cloud_oname changed = True cloud_phone = (res.get("phone") or "").strip() if cloud_phone and cloud_phone != (local_shop.phone or ""): local_shop.phone = cloud_phone changed = True if res.get("active") is not None and bool(res.get("active")) != bool(local_shop.active): local_shop.active = bool(res.get("active")) changed = True if res.get("license_expires_at") and res.get("license_expires_at") != local_shop.license_expires_at: local_shop.license_expires_at = res.get("license_expires_at") changed = True if changed: db.session.commit() print(f"[AUTO SYNC SUCCESS] Local shop {shop_id} metadata updated successfully!") return True, "Shop synced" return True, "No changes" except Exception as e: return False, str(e) def flush_pending_sync_queue(app, db, SyncQueue): """ Background worker that flushes unsynced items from SyncQueue and periodically synchronizes shop metadata (e.g. company name, license) from central Owner Portal / Supabase Cloud DB whenever internet connection is available. """ def _worker(): last_pull_time = 0 while True: try: with app.app_context(): # 1. Flush pending sync queue pending_items = SyncQueue.query.filter_by(synced=False).order_by(SyncQueue.id.asc()).limit(10).all() if pending_items: owner_portal_url = os.environ.get("POS_OWNER_PORTAL_URL", "").rstrip("/") for item in pending_items: synced = False payload = json.loads(item.payload_json or "{}") # Direct Supabase cloud database sync attempt if item.endpoint == "/api/register-remote-shop": synced = sync_remote_shop_to_supabase_db(payload) # HTTP POST attempt if Owner Portal URL is configured if not synced and owner_portal_url and not owner_portal_url.startswith("http://127.0.0.1"): target_url = f"{owner_portal_url}{item.endpoint}" try: payload_bytes = item.payload_json.encode("utf-8") req = urllib.request.Request( target_url, data=payload_bytes, headers={"Content-Type": "application/json", "User-Agent": "Shop POS Sync Manager"} ) with urllib.request.urlopen(req, timeout=5) as resp: if resp.status in (200, 201): synced = True except Exception: pass if synced: item.synced = True else: item.attempts += 1 db.session.commit() # 2. Periodically pull latest Shop Company Name & License from Cloud every 30 seconds now_ts = time.time() if now_ts - last_pull_time >= 30: last_pull_time = now_ts try: from models import Shop shops = Shop.query.all() for s in shops: sync_shop_info_from_cloud(s.id, app, db) except Exception: pass except Exception: pass time.sleep(15) thread = threading.Thread(target=_worker, daemon=True) thread.start() def push_shop_data_to_cloud(shop_id, app, db): """Pushes all local Products, Customers, Invoices, Payments, Expenses, Suppliers, and Users to Supabase.""" cloud_url = get_cloud_db_url() try: import psycopg2 from models import Shop, User, Product, Customer, Invoice, InvoiceItem, Expense, Supplier with app.app_context(): local_shop = db.session.get(Shop, shop_id) if not local_shop: return False, "Shop not found locally" local_users = User.query.filter_by(shop_id=shop_id).all() products = Product.query.filter_by(shop_id=shop_id).all() customers = Customer.query.filter_by(shop_id=shop_id).all() invoices = Invoice.query.filter_by(shop_id=shop_id).all() expenses = Expense.query.filter_by(shop_id=shop_id).all() suppliers = Supplier.query.filter_by(shop_id=shop_id).all() conn = psycopg2.connect(cloud_url, connect_timeout=8) conn.autocommit = True cur = conn.cursor() # 1. Upsert Shop cur.execute(""" INSERT INTO shop (id, shop_name, owner_name, phone, active, license_expires_at, subscription_plan, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET shop_name = EXCLUDED.shop_name, owner_name = EXCLUDED.owner_name, phone = EXCLUDED.phone, active = EXCLUDED.active, license_expires_at = EXCLUDED.license_expires_at, subscription_plan = EXCLUDED.subscription_plan; """, ( local_shop.id, local_shop.shop_name, local_shop.owner_name or "Owner", local_shop.phone, bool(local_shop.active), local_shop.license_expires_at, local_shop.subscription_plan or "Monthly (৳500)", local_shop.created_at or datetime.now() )) # 2. Upsert Users (Owner, Managers, Cashiers, Salesmen) for u in local_users: cur.execute(""" INSERT INTO "user" (shop_id, username, email, phone, password_hash, role, must_change_password) VALUES (%s, %s, %s, %s, %s, %s, %s) ON CONFLICT (username) DO UPDATE SET shop_id = EXCLUDED.shop_id, email = EXCLUDED.email, phone = EXCLUDED.phone, password_hash = EXCLUDED.password_hash, role = EXCLUDED.role, must_change_password = EXCLUDED.must_change_password; """, ( u.shop_id, u.username, u.email, u.phone, u.password_hash, u.role or "owner", bool(getattr(u, "must_change_password", False)) )) # 3. Upsert Products for p in products: cur.execute(""" INSERT INTO product (id, shop_id, name, category, buy_price, sell_price, stock, reorder_level, barcode, unit, active, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, category = EXCLUDED.category, buy_price = EXCLUDED.buy_price, sell_price = EXCLUDED.sell_price, stock = EXCLUDED.stock, reorder_level = EXCLUDED.reorder_level, barcode = EXCLUDED.barcode, unit = EXCLUDED.unit, active = EXCLUDED.active; """, ( p.id, p.shop_id, p.name, getattr(p, 'category', 'General') or 'General', float(p.buy_price or 0), float(p.sell_price or 0), float(p.stock or 0), float(getattr(p, 'alert_quantity', 5) or 5), getattr(p, 'barcode', None), getattr(p, 'unit', 'Pcs') or 'Pcs', getattr(p, 'is_active', True) if getattr(p, 'is_active', True) is not None else True, getattr(p, 'created_at', None) or datetime.now() )) # 4. Upsert Customers for c in customers: cur.execute(""" INSERT INTO customer (id, shop_id, name, phone, address, advance_balance, loyalty_points, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, phone = EXCLUDED.phone, address = EXCLUDED.address, advance_balance = EXCLUDED.advance_balance, loyalty_points = EXCLUDED.loyalty_points; """, ( c.id, c.shop_id, c.name, c.phone, getattr(c, 'address', None), float(getattr(c, 'advance_balance', 0) or 0), float(getattr(c, 'loyalty_points', 0) or 0), getattr(c, 'created_at', None) or datetime.now() )) # 5. Upsert Invoices & Items for inv in invoices: inv_num = getattr(inv, 'invoice_no', None) or getattr(inv, 'invoice_number', None) or f"INV-{inv.id}" cur.execute(""" INSERT INTO invoice (id, shop_id, customer_id, invoice_no, total_amount, subtotal, paid_amount, due_amount, discount_amount, payment_method, payment_status, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET invoice_no = EXCLUDED.invoice_no, total_amount = EXCLUDED.total_amount, paid_amount = EXCLUDED.paid_amount, due_amount = EXCLUDED.due_amount, payment_status = EXCLUDED.payment_status; """, ( inv.id, inv.shop_id, inv.customer_id, inv_num, float(inv.total_amount or 0), float(getattr(inv, 'subtotal', inv.total_amount) or inv.total_amount or 0), float(inv.paid_amount or 0), float(inv.due_amount or 0), float(getattr(inv, 'discount_amount', 0) or 0), getattr(inv, 'payment_method', getattr(inv, 'payment_type', 'Cash')) or 'Cash', getattr(inv, 'payment_status', 'Paid') or 'Paid', inv.created_at or datetime.now() )) inv_items = InvoiceItem.query.filter_by(invoice_id=inv.id).all() for item in inv_items: cur.execute(""" INSERT INTO invoice_item (id, invoice_id, product_id, quantity, price, total) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET quantity = EXCLUDED.quantity, price = EXCLUDED.price, total = EXCLUDED.total; """, ( item.id, item.invoice_id, item.product_id, float(item.quantity or 0), float(item.price or 0), float(item.total or 0) )) # 6. Upsert Expenses for exp in expenses: cur.execute(""" INSERT INTO expense (id, shop_id, title, category, amount, note, payment_method, date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title, category = EXCLUDED.category, amount = EXCLUDED.amount, payment_method = EXCLUDED.payment_method; """, ( exp.id, exp.shop_id, getattr(exp, 'title', getattr(exp, 'description', 'Expense')) or 'Expense', getattr(exp, 'category', 'General') or 'General', float(exp.amount or 0), getattr(exp, 'note', getattr(exp, 'description', '')) or '', getattr(exp, 'payment_method', 'Cash') or 'Cash', getattr(exp, 'date', getattr(exp, 'created_at', None)) or datetime.now() )) # 7. Upsert Suppliers for sup in suppliers: cur.execute(""" INSERT INTO supplier (id, shop_id, supplier_code, name, company, phone, opening_due, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (id) DO UPDATE SET supplier_code = EXCLUDED.supplier_code, name = EXCLUDED.name, company = EXCLUDED.company, phone = EXCLUDED.phone, opening_due = EXCLUDED.opening_due; """, ( sup.id, sup.shop_id, getattr(sup, 'supplier_code', f"SUP-{sup.id}") or f"SUP-{sup.id}", sup.name, getattr(sup, 'company', '') or '', getattr(sup, 'phone', '') or '', float(getattr(sup, 'opening_due', getattr(sup, 'due', 0)) or 0), getattr(sup, 'created_at', None) or datetime.now() )) conn.close() print(f"[CLOUD BACKUP SUCCESS] All local data for shop {shop_id} backed up to Supabase Cloud!") return True, "Cloud backup completed successfully" except Exception as e: print(f"[CLOUD BACKUP ERROR] {e}") return False, str(e) def restore_shop_data_from_cloud(shop_id, app, db): """Restores all Users, Products, Customers, Invoices, Payments, Expenses from Supabase into local SQLite.""" cloud_url = get_cloud_db_url() try: import psycopg2 from psycopg2.extras import RealDictCursor from models import Shop, User, Product, Customer, Invoice, InvoiceItem, Expense, Supplier, CustomerPayment, Purchase from sqlalchemy import func conn = psycopg2.connect(cloud_url, connect_timeout=8) conn.autocommit = True cur = conn.cursor(cursor_factory=RealDictCursor) cur.execute("SELECT * FROM shop WHERE id = %s", (shop_id,)) cloud_shop = cur.fetchone() cur.execute("SELECT * FROM \"user\" WHERE shop_id = %s", (shop_id,)) cloud_users = cur.fetchall() cur.execute("SELECT * FROM product WHERE shop_id = %s", (shop_id,)) cloud_products = cur.fetchall() cur.execute("SELECT * FROM customer WHERE shop_id = %s", (shop_id,)) cloud_customers = cur.fetchall() cur.execute("SELECT * FROM invoice WHERE shop_id = %s", (shop_id,)) cloud_invoices = cur.fetchall() cur.execute(""" SELECT ii.* FROM invoice_item ii JOIN invoice i ON ii.invoice_id = i.id WHERE i.shop_id = %s """, (shop_id,)) cloud_inv_items = cur.fetchall() cur.execute("SELECT * FROM expense WHERE shop_id = %s", (shop_id,)) cloud_expenses = cur.fetchall() cur.execute("SELECT * FROM supplier WHERE shop_id = %s", (shop_id,)) cloud_suppliers = cur.fetchall() cur.execute("SELECT * FROM customer_payment WHERE shop_id = %s", (shop_id,)) cloud_cust_payments = cur.fetchall() conn.close() with app.app_context(): # 1. Restore Shop if cloud_shop: s = db.session.get(Shop, shop_id) if not s: s = Shop(id=shop_id, shop_name=cloud_shop.get("shop_name", "Shop")) db.session.add(s) s.shop_name = cloud_shop.get("shop_name", s.shop_name) s.owner_name = cloud_shop.get("owner_name", s.owner_name) s.phone = cloud_shop.get("phone", s.phone) s.active = bool(cloud_shop.get("active", True)) s.subscription_plan = cloud_shop.get("subscription_plan", s.subscription_plan) if cloud_shop.get("license_expires_at"): s.license_expires_at = cloud_shop.get("license_expires_at") db.session.flush() # 1b. Restore Users (Staff, Cashiers, Managers, Owner) for cu in cloud_users: uname = cu.get("username") u = None if uname: u = User.query.filter(func.lower(User.username) == uname.lower()).first() if not u and cu.get("phone"): u = User.query.filter_by(phone=cu.get("phone")).first() if not u and cu.get("email"): u = User.query.filter(func.lower(User.email) == cu.get("email").lower()).first() if not u: u = User(shop_id=shop_id, username=uname or f"user_{cu.get('id', 1)}") db.session.add(u) u.shop_id = shop_id u.username = uname or u.username u.email = cu.get("email") u.phone = cu.get("phone") u.password_hash = cu.get("password_hash", u.password_hash) u.role = cu.get("role", "cashier") u.must_change_password = bool(cu.get("must_change_password", False)) db.session.flush() # 2. Restore Products for cp in cloud_products: p = db.session.get(Product, cp["id"]) if not p: p = Product(id=cp["id"], shop_id=shop_id, name=cp.get("name", "Product")) db.session.add(p) p.name = cp.get("name", p.name) p.category = cp.get("category", getattr(p, 'category', 'General')) p.buy_price = float(cp.get("buy_price") or 0) p.sell_price = float(cp.get("sell_price") or 0) p.stock = float(cp.get("stock") or 0) p.barcode = cp.get("barcode") p.reorder_level = float(cp.get("reorder_level") or 5) p.unit = cp.get("unit") or 'Piece' db.session.flush() # 3. Restore Customers for cc in cloud_customers: c = db.session.get(Customer, cc["id"]) if not c: c = Customer(id=cc["id"], shop_id=shop_id, name=cc.get("name", "Customer")) db.session.add(c) c.name = cc.get("name", c.name) c.phone = cc.get("phone", c.phone) c.address = cc.get("address", getattr(c, 'address', None)) c.advance_balance = float(cc.get("advance_balance") or 0) c.loyalty_points = float(cc.get("loyalty_points") or 0) db.session.flush() # 4. Restore Invoices for ci in cloud_invoices: inv = db.session.get(Invoice, ci["id"]) if not inv: inv = Invoice(id=ci["id"], shop_id=shop_id) db.session.add(inv) inv.customer_id = ci.get("customer_id") # Fix: explicitly set invoice_no (required NOT NULL) inv.invoice_no = ci.get("invoice_no") or ci.get("invoice_number") or f"INV-{shop_id}-{ci['id']:04d}" inv.total_amount = float(ci.get("total_amount") or 0) inv.subtotal = float(ci.get("subtotal") or ci.get("total_amount") or 0) inv.paid_amount = float(ci.get("paid_amount") or 0) inv.due_amount = float(ci.get("due_amount") or 0) inv.discount_amount = float(ci.get("discount_amount") or 0) inv.tax_amount = float(ci.get("tax_amount") or 0) inv.payment_method = ci.get("payment_method") or ci.get("payment_type") or "Cash" inv.payment_status = ci.get("payment_status") or "Paid" inv.note = ci.get("note") inv.coupon_code = ci.get("coupon_code") inv.payment_split = ci.get("payment_split") inv.created_at = ci.get("created_at") or datetime.now() db.session.flush() # 5. Restore Invoice Items for cii in cloud_inv_items: item = db.session.get(InvoiceItem, cii["id"]) if not item: item = InvoiceItem(id=cii["id"], invoice_id=cii["invoice_id"], product_id=cii["product_id"]) db.session.add(item) item.invoice_id = cii["invoice_id"] item.product_id = cii["product_id"] item.quantity = float(cii.get("quantity") or 0) item.price = float(cii.get("price") or 0) item.total = float(cii.get("total") or 0) db.session.flush() # 6. Restore Expenses for ce in cloud_expenses: exp = db.session.get(Expense, ce["id"]) if not exp: exp = Expense(id=ce["id"], shop_id=shop_id) db.session.add(exp) exp.title = ce.get("title") or ce.get("description") or "Expense" exp.category = ce.get("category", "General") exp.amount = float(ce.get("amount") or 0) exp.note = ce.get("note") or ce.get("description") or "" exp.payment_method = ce.get("payment_method", "Cash") exp.date = ce.get("date") or ce.get("created_at") or datetime.now() db.session.flush() # 7. Restore Suppliers for cs in cloud_suppliers: sup = db.session.get(Supplier, cs["id"]) if not sup: sup = Supplier(id=cs["id"], shop_id=shop_id, name=cs.get("name", "Supplier")) db.session.add(sup) sup.supplier_code = cs.get("supplier_code") or f"SUP-{cs['id']}" sup.name = cs.get("name", sup.name) sup.company = cs.get("company", getattr(sup, 'company', '')) sup.phone = cs.get("phone", getattr(sup, 'phone', '')) sup.opening_due = float(cs.get("opening_due") or 0) sup.advance_balance = float(cs.get("advance_balance") or 0) sup.active = bool(cs.get("active", True)) db.session.flush() # 8. Restore Customer Payments for cp in cloud_cust_payments: pmt = db.session.get(CustomerPayment, cp["id"]) if not pmt: pmt = CustomerPayment(id=cp["id"], shop_id=shop_id, customer_id=cp["customer_id"]) db.session.add(pmt) pmt.customer_id = cp["customer_id"] pmt.amount = float(cp.get("amount") or 0) pmt.payment_type = cp.get("payment_type") or "due_payment" pmt.method = cp.get("method") or "Cash" pmt.note = cp.get("note") or "" pmt.created_at = cp.get("created_at") or datetime.now() db.session.flush() db.session.commit() print(f"[CLOUD RESTORE SUCCESS] Restored all shop {shop_id} data into local SQLite!") return True, f"Successfully restored {len(cloud_products)} products, {len(cloud_customers)} customers, {len(cloud_invoices)} invoices from Cloud Database!" except Exception as e: print(f"[CLOUD RESTORE ERROR] {e}") return False, str(e)