from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, session, abort
from translations import translate
from datetime import datetime, date, timedelta, timezone
import json
import secrets
import os
import sys
import time
import shutil
import threading
import smtplib
import ssl
import uuid
from email.mime.text import MIMEText

from apscheduler.schedulers.background import BackgroundScheduler

import os

from paths import DATA_DIR, STATIC_DIR, TEMPLATE_DIR
BASE_DIR = DATA_DIR

# Auto-load .env file if present in any execution location
env_candidates = [
    os.path.join(os.path.dirname(__file__), ".env"),
    os.path.join(getattr(sys, "_MEIPASS", ""), ".env"),
    os.path.join(os.getcwd(), ".env"),
    os.path.join(DATA_DIR, ".env")
]
for env_file in env_candidates:
    if env_file and os.path.exists(env_file):
        try:
            with open(env_file, "r", encoding="utf-8") as f:
                for line in f:
                    line = line.strip()
                    if line and not line.startswith("#") and "=" in line:
                        k, v = line.split("=", 1)
                        if k.strip() not in os.environ:
                            os.environ[k.strip()] = v.strip()
        except Exception:
            pass



from backup_system import (
    start_backup_system,
    create_local_backup,
    upload_to_gdrive,
    connect_gdrive,
    disconnect_gdrive,
    gdrive_status
)

from io import BytesIO

from flask import send_file

from reportlab.lib.units import inch

from reportlab.lib.units import mm

from reportlab.pdfgen import canvas

from sqlalchemy import func, extract

from flask_migrate import Migrate


from flask_login import (
    LoginManager,
    login_user,
    logout_user,
    login_required,
    current_user
)

from config import Config


from models import (
    db,
    User,
    Shop,
    Product,
    Customer,
    Invoice,
    InvoiceItem,
    Purchase,
    Supplier,
    SupplierPayment,
    Expense,
    SalesReturn,
    SalesReturnItem,
    ROLE_PERMISSIONS,
    AuditLog,
    log_action,
    Coupon,
    HeldSale,
    HeldSaleItem,
    CustomerPayment,
    StockAdjustment,
    PurchaseOrder,
    PurchaseOrderItem,
    PurchaseReturn,
    SubscriptionPayment,
    bangladesh_time
)
from licensing import activation_request_code, get_installation, licence_status, verify_license_token

from functools import wraps
import csv
import io as _io

try:
    import openpyxl
    from openpyxl.utils import get_column_letter
    HAS_OPENPYXL = True
except ImportError:
    HAS_OPENPYXL = False

from reportlab.graphics.barcode import code128
from reportlab.lib.pagesizes import A4

# =====================
# =====================
# APP INIT & DB AUTO-HEAL
# =====================
app = Flask(__name__, static_folder=STATIC_DIR, template_folder=TEMPLATE_DIR)
app.config.from_object(Config)

def check_and_auto_heal_database():
    """Checks SQLite database health and automatically repairs/restores if malformed disk image is detected."""
    db_uri = str(app.config.get("SQLALCHEMY_DATABASE_URI", ""))
    if not db_uri.startswith("sqlite:///"):
        return
    
    db_file_path = db_uri.replace("sqlite:///", "")
    if not os.path.exists(db_file_path):
        return

    is_healthy = False
    try:
        import sqlite3
        conn = sqlite3.connect(db_file_path, timeout=5)
        cur = conn.cursor()
        cur.execute("PRAGMA integrity_check")
        res = cur.fetchone()
        conn.close()
        if res and res[0] == "ok":
            is_healthy = True
    except Exception as e:
        print(f"[DB INTEGRITY ERROR] Database check failed: {e}")

    if is_healthy:
        return

    # If corrupted, perform automated healing
    print(f"[AUTO-HEALING] Malformed database disk image detected on '{db_file_path}'. Initiating automatic recovery...")
    try:
        import shutil, glob
        # 1. Clean up locking WAL/SHM files
        wal_file = f"{db_file_path}-wal"
        shm_file = f"{db_file_path}-shm"
        for lock_f in [wal_file, shm_file]:
            if os.path.exists(lock_f):
                try:
                    os.remove(lock_f)
                except Exception:
                    pass

        # 2. Move corrupted file to backup
        now_ts = datetime.now().strftime("%Y%m%d_%H%M%S")
        corrupted_bak = f"{db_file_path}.corrupted_{now_ts}"
        try:
            shutil.move(db_file_path, corrupted_bak)
            print(f"[AUTO-HEALING] Backed up corrupted database to: {corrupted_bak}")
        except Exception:
            pass

        # 3. First attempt: Restore from Supabase Cloud
        cloud_restored = False
        try:
            owner_portal_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "owner_portal"))
            if owner_portal_dir not in sys.path:
                sys.path.insert(0, owner_portal_dir)
            from db_exporter import export_shop_to_sqlite
            export_shop_to_sqlite(1, target_file_path=db_file_path)
            cloud_restored = True
            print("[AUTO-HEALING SUCCESS] Rebuilt fresh SQLite database from Supabase Cloud!")
        except Exception as ce:
            print(f"[AUTO-HEALING] Cloud rebuild fallback: {ce}")

        # 4. Second attempt: Restore from latest healthy local backup
        if not cloud_restored:
            backup_files = sorted(glob.glob(os.path.join(os.path.dirname(db_file_path), "backups", "local", "*.db")), reverse=True)
            for b in backup_files:
                try:
                    conn = sqlite3.connect(b)
                    cur = conn.cursor()
                    cur.execute("PRAGMA integrity_check")
                    if cur.fetchone()[0] == "ok":
                        shutil.copy2(b, db_file_path)
                        conn.close()
                        print(f"[AUTO-HEALING SUCCESS] Restored database from healthy local backup: {b}")
                        break
                    conn.close()
                except Exception:
                    pass
    except Exception as heal_err:
        print(f"[AUTO-HEALING ERROR] {heal_err}")

# Auto-heal database before initializing SQLAlchemy
check_and_auto_heal_database()

db.init_app(app)
migrate = Migrate(app, db)

from sqlalchemy import event, text
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    """Configures high-speed WAL mode, 10s busy timeout, memory cache, and foreign key integrity for SQLite."""
    if "sqlite" in type(dbapi_connection).__module__.lower():
        try:
            cursor = dbapi_connection.cursor()
            cursor.execute("PRAGMA journal_mode = WAL")
            cursor.execute("PRAGMA synchronous = NORMAL")
            cursor.execute("PRAGMA busy_timeout = 10000") # 10s busy timeout
            cursor.execute("PRAGMA wal_autocheckpoint = 1000")
            cursor.execute("PRAGMA cache_size = -64000")  # 64MB In-Memory Cache
            cursor.execute("PRAGMA temp_store = MEMORY")
            cursor.execute("PRAGMA foreign_keys = ON")
            cursor.close()
        except Exception:
            pass

with app.app_context():
    try:
        db.create_all()
        is_postgres = "postgres" in str(app.config.get("SQLALCHEMY_DATABASE_URI", "")).lower()
        
        alter_queries = [
            'ALTER TABLE "user" ADD COLUMN phone VARCHAR(20)' if is_postgres else 'ALTER TABLE user ADD COLUMN phone VARCHAR(20)',
            'ALTER TABLE "user" ADD COLUMN email VARCHAR(120)' if is_postgres else 'ALTER TABLE user ADD COLUMN email VARCHAR(120)',
            'ALTER TABLE "user" ADD COLUMN must_change_password BOOLEAN DEFAULT FALSE' if is_postgres else 'ALTER TABLE user ADD COLUMN must_change_password BOOLEAN DEFAULT 0',
            'ALTER TABLE "user" ADD COLUMN last_login_at TIMESTAMP' if is_postgres else 'ALTER TABLE user ADD COLUMN last_login_at DATETIME',
            'ALTER TABLE "user" ADD COLUMN last_login_ip VARCHAR(50)' if is_postgres else 'ALTER TABLE user ADD COLUMN last_login_ip VARCHAR(50)',
            'ALTER TABLE "user" ADD COLUMN last_login_device VARCHAR(255)' if is_postgres else 'ALTER TABLE user ADD COLUMN last_login_device VARCHAR(255)',
            'ALTER TABLE shop ADD COLUMN subscription_plan VARCHAR(50) DEFAULT \'Monthly (৳500)\'',
            'ALTER TABLE shop ADD COLUMN active BOOLEAN DEFAULT 1',
            'ALTER TABLE shop ADD COLUMN license_expires_at DATETIME',
            'ALTER TABLE invoice ADD COLUMN received_amount FLOAT DEFAULT 0',
            'ALTER TABLE invoice ADD COLUMN change_amount FLOAT DEFAULT 0',
            'ALTER TABLE audit_log ADD COLUMN ip_address VARCHAR(50)',
            'ALTER TABLE audit_log ADD COLUMN device_info VARCHAR(255)'
        ]
        
        for q in alter_queries:
            try:
                db.session.execute(text(q))
                db.session.commit()
            except Exception:
                db.session.rollback()
    except Exception:
        db.session.rollback()

def _start_async_queue_flush():
    try:
        from sync_manager import flush_pending_sync_queue
        from models import SyncQueue
        flush_pending_sync_queue(app, db, SyncQueue)
    except Exception:
        pass




def get_lan_ip():
    """Best-effort detection of this PC's LAN IP so the login page can
    show a phone-friendly address (e.g. http://192.168.0.12:5000)."""
    import socket
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    try:
        s.connect(("8.8.8.8", 80))
        ip = s.getsockname()[0]
    except Exception:
        ip = "127.0.0.1"
    finally:
        s.close()
    return ip


def safe_commit():
    """Safely commits the current database session.
    Automatically executes db.session.rollback() on any error to protect against session contamination."""
    try:
        db.session.commit()
        return True
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"[DB SAFE COMMIT ERROR] Transaction rolled back: {e}")
        return False


# =====================
# LOGIN
# =====================
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = "login"



def get_current_shop():
    if current_user.shop_id:
        return current_user.shop_id

    shop = Shop.query.first()

    if shop:
        return shop.id

    return None

def has_permission(user, section):
    if not user or not user.is_authenticated:
        return False
    user_role = (user.role or "owner").strip().lower()
    perms = ROLE_PERMISSIONS.get(user_role, set())
    if "all" in perms:
        return True
    if isinstance(section, (list, tuple, set)):
        return any((s or "").strip().lower() in perms for s in section)
    return (section or "").strip().lower() in perms


app.jinja_env.globals["can"] = lambda section: has_permission(current_user, section)


def permission_required(section):
    def decorator(view_func):
        @wraps(view_func)
        def wrapped(*args, **kwargs):
            if not has_permission(current_user, section):
                if request.is_json or request.path.startswith("/api/"):
                    return jsonify({
                        "status": "error",
                        "error": "Forbidden",
                        "message": "You do not have permission to access this resource."
                    }), 403
                flash("You do not have permission to access that section.", "danger")
                if has_permission(current_user, "dashboard"):
                    return redirect(url_for("dashboard"))
                elif has_permission(current_user, "sell"):
                    return redirect(url_for("sell"))
                else:
                    return redirect(url_for("account"))
            return view_func(*args, **kwargs)
        return wrapped
    return decorator


def send_email(to_email, subject, body):
    """
    Sends a plain-text email via Gmail SMTP using the App Password
    configured in config.py (POS_GMAIL_ADDRESS / POS_GMAIL_APP_PASSWORD).
    Returns (True, None) on success, or (False, error_message) on failure —
    callers should show a friendly message rather than a raw exception,
    since the most common failure here is simply "no internet right now".
    """
    username = app.config.get("MAIL_USERNAME")
    password = app.config.get("MAIL_PASSWORD")
    sender_name = app.config.get("MAIL_SENDER_NAME", "Shop Manager POS")

    if not username or not password:
        return False, "Email sending is not configured on this installation."

    msg = MIMEText(body)
    msg["Subject"] = subject
    msg["From"] = f"{sender_name} <{username}>"
    msg["To"] = to_email

    try:
        context = ssl.create_default_context()
        with smtplib.SMTP(app.config["MAIL_SERVER"], app.config["MAIL_PORT"], timeout=15) as server:
            server.starttls(context=context)
            server.login(username, password)
            server.sendmail(username, [to_email], msg.as_string())
        return True, None
    except Exception as exc:
        return False, str(exc)


# ==========================================================
# LANGUAGE / TRANSLATION SETUP
# ==========================================================

_db_status_cache = {"status": None, "time": 0}

def check_real_network_online():
    """Fast check if internet is active (0.2s timeout, cached)."""
    import socket
    test_targets = [
        ('1.1.1.1', 80),
        ('8.8.8.8', 53),
        ('google.com', 80)
    ]
    for host, port in test_targets:
        try:
            s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
            s.settimeout(0.3)
            s.connect((host, port))
            s.close()
            return True
        except Exception:
            continue
    return False

def get_db_connection_status():
    global _db_status_cache
    now = time.time()
    if _db_status_cache["status"] and (now - _db_status_cache["time"] < 60):
        return _db_status_cache["status"]

    cloud_url = os.environ.get("DATABASE_URL", "").strip()
    is_cloud = bool(cloud_url and cloud_url.startswith("postgres") and os.environ.get("POS_USE_REMOTE_DB", "0") == "1")

    status = {
        "connected": True,
        "is_cloud": is_cloud,
        "label_en": "Cloud PostgreSQL Connected" if is_cloud else "Database Connected (Online)",
        "label_bn": "ক্লাউড ডাটাবেজ কানেক্টেড" if is_cloud else "ডাটাবেজ কানেক্টেড (অনলাইন)"
    }
    _db_status_cache["status"] = status
    _db_status_cache["time"] = now
    return status


def trigger_cloud_sync_async(shop_id):
    """Triggers background cloud sync to Supabase without blocking local UI execution."""
    if not shop_id:
        return
    try:
        from sync_manager import push_shop_data_to_cloud
        threading.Thread(
            target=push_shop_data_to_cloud,
            args=(shop_id, app, db),
            daemon=True
        ).start()
    except Exception as e:
        print(f"[ASYNC CLOUD SYNC TRIGGER ERROR] {e}")


@app.route("/api/db-status")
def api_db_status():
    """API endpoint to get live database connection & network status."""
    return jsonify(get_db_connection_status())


@app.route("/api/dashboard-growth")
@login_required
@permission_required("dashboard")
def api_dashboard_growth():
    """Returns dynamic business growth trends (daily, monthly, yearly) and distribution breakdown."""
    try:
        shop = current_user.shop if (current_user.is_authenticated and getattr(current_user, 'shop', None)) else Shop.query.first()
        shop_id = shop.id if shop else 1
        now_bd = bangladesh_time()
        
        # 1. Daily trend (Last 30 days)
        daily_labels = []
        daily_sales = []
        daily_expenses = []
        daily_profits = []
        
        for i in range(29, -1, -1):
            day_dt = now_bd.date() - timedelta(days=i)
            daily_labels.append(day_dt.strftime("%d %b"))
            
            invs = Invoice.query.filter_by(shop_id=shop_id).filter(
                db.func.date(Invoice.created_at) == day_dt
            ).all()
            
            day_s = sum(float(inv.total_amount or 0) for inv in invs)
            
            exps = Expense.query.filter_by(shop_id=shop_id).filter(
                db.func.date(Expense.date) == day_dt
            ).all()
            day_e = sum(float(ex.amount or 0) for ex in exps)
            
            daily_sales.append(round(day_s, 2))
            daily_expenses.append(round(day_e, 2))
            daily_profits.append(round(max(0, day_s - day_e), 2))
            
        prev_d = sum(daily_sales[:15])
        curr_d = sum(daily_sales[15:])
        daily_growth = round(((curr_d - prev_d) / prev_d * 100), 1) if prev_d > 0 else (100.0 if curr_d > 0 else 0.0)

        # 2. Monthly trend (Last 12 months)
        monthly_labels = []
        monthly_sales = []
        monthly_expenses = []
        monthly_profits = []
        
        for i in range(11, -1, -1):
            y = now_bd.year
            m = now_bd.month - i
            while m <= 0:
                m += 12
                y -= 1
            
            m_date = date(y, m, 1)
            monthly_labels.append(m_date.strftime("%b %y"))
            
            m_invs = Invoice.query.filter_by(shop_id=shop_id).filter(
                extract('year', Invoice.created_at) == y,
                extract('month', Invoice.created_at) == m
            ).all()
            
            m_s = sum(float(inv.total_amount or 0) for inv in m_invs)
            
            m_exps = Expense.query.filter_by(shop_id=shop_id).filter(
                extract('year', Expense.date) == y,
                extract('month', Expense.date) == m
            ).all()
            m_e = sum(float(ex.amount or 0) for ex in m_exps)
            
            monthly_sales.append(round(m_s, 2))
            monthly_expenses.append(round(m_e, 2))
            monthly_profits.append(round(max(0, m_s - m_e), 2))
            
        prev_m = sum(monthly_sales[:6])
        curr_m = sum(monthly_sales[6:])
        monthly_growth = round(((curr_m - prev_m) / prev_m * 100), 1) if prev_m > 0 else (100.0 if curr_m > 0 else 0.0)

        # 3. Yearly trend (Last 5 years)
        yearly_labels = []
        yearly_sales = []
        yearly_expenses = []
        yearly_profits = []
        
        curr_yr = now_bd.year
        for yr in range(curr_yr - 4, curr_yr + 1):
            yearly_labels.append(str(yr))
            
            y_invs = Invoice.query.filter_by(shop_id=shop_id).filter(
                extract('year', Invoice.created_at) == yr
            ).all()
            y_s = sum(float(inv.total_amount or 0) for inv in y_invs)
            
            y_exps = Expense.query.filter_by(shop_id=shop_id).filter(
                extract('year', Expense.date) == yr
            ).all()
            y_e = sum(float(ex.amount or 0) for ex in y_exps)
            
            yearly_sales.append(round(y_s, 2))
            yearly_expenses.append(round(y_e, 2))
            yearly_profits.append(round(max(0, y_s - y_e), 2))
            
        prev_y = yearly_sales[-2] if len(yearly_sales) > 1 else 0
        curr_y = yearly_sales[-1] if len(yearly_sales) > 0 else 0
        yearly_growth = round(((curr_y - prev_y) / prev_y * 100), 1) if prev_y > 0 else (100.0 if curr_y > 0 else 0.0)

        # 4. Sales Distribution: Category, Payment & Top Products breakdown
        all_invoices = Invoice.query.filter_by(shop_id=shop_id).all()
        cat_map = {}
        pay_map = {}
        prod_map = {}
        
        for inv in all_invoices:
            method = inv.payment_method or "Cash"
            pay_map[method] = pay_map.get(method, 0.0) + float(inv.total_amount or 0)
            
            for item in inv.items:
                cat = (item.product.category if item.product and item.product.category else "General")
                item_amt = float(item.total if getattr(item, 'total', None) is not None else ((item.quantity or 0) * (item.price or 0)))
                cat_map[cat] = cat_map.get(cat, 0.0) + item_amt

                p_name = item.product.name if (item.product and item.product.name) else (f"Product #{item.product_id}" if item.product_id else "Uncategorized")
                prod_map[p_name] = prod_map.get(p_name, 0.0) + item_amt
                
        cat_labels = list(cat_map.keys()) if cat_map else ["General"]
        cat_values = [round(v, 2) for v in cat_map.values()] if cat_map else [0.0]
        
        pay_labels = list(pay_map.keys()) if pay_map else ["Cash"]
        pay_values = [round(v, 2) for v in pay_map.values()] if pay_map else [0.0]

        if prod_map:
            sorted_prods = sorted(prod_map.items(), key=lambda x: x[1], reverse=True)
            top_5 = sorted_prods[:5]
            others_sum = sum(val for _, val in sorted_prods[5:])
            
            top_prod_labels = [p[0] for p in top_5]
            top_prod_values = [round(p[1], 2) for p in top_5]
            
            if others_sum > 0:
                top_prod_labels.append("Others")
                top_prod_values.append(round(others_sum, 2))
        else:
            top_prod_labels = ["No Products"]
            top_prod_values = [0.0]

        return jsonify({
            "daily": {
                "labels": daily_labels,
                "sales": daily_sales,
                "expenses": daily_expenses,
                "profits": daily_profits,
                "growth_percent": daily_growth
            },
            "monthly": {
                "labels": monthly_labels,
                "sales": monthly_sales,
                "expenses": monthly_expenses,
                "profits": monthly_profits,
                "growth_percent": monthly_growth
            },
            "yearly": {
                "labels": yearly_labels,
                "sales": yearly_sales,
                "expenses": yearly_expenses,
                "profits": yearly_profits,
                "growth_percent": yearly_growth
            },
            "categories": {
                "labels": cat_labels,
                "values": cat_values
            },
            "payments": {
                "labels": pay_labels,
                "values": pay_values
            },
            "products": {
                "labels": top_prod_labels,
                "values": top_prod_values
            }
        })
    except Exception as e:
        app.logger.error(f"Error in api_dashboard_growth: {e}")
        return jsonify({
            "daily": {"labels": ["Day 1", "Day 2", "Day 3"], "sales": [0]*3, "expenses": [0]*3, "profits": [0]*3, "growth_percent": 0},
            "monthly": {"labels": ["Jan", "Feb", "Mar"], "sales": [0]*3, "expenses": [0]*3, "profits": [0]*3, "growth_percent": 0},
            "yearly": {"labels": ["2024", "2025", "2026"], "sales": [0]*3, "expenses": [0]*3, "profits": [0]*3, "growth_percent": 0},
            "categories": {"labels": ["General"], "values": [0]},
            "payments": {"labels": ["Cash"], "values": [0]},
            "products": {"labels": ["No Products"], "values": [0]}
        })






@app.context_processor
def inject_language():
    lang = session.get("lang", "en")
    if "_csrf_token" not in session:
        session["_csrf_token"] = os.urandom(32).hex()
    def language_label(en_text, bn_text):
        """Small inline translator for page text not yet in translations.py."""
        if lang == "bn":
            return bn_text
        if lang == "both":
            return f"{en_text} ({bn_text})"
        return en_text
    license_info = None
    try:
        current_shop = current_user.shop if (current_user.is_authenticated and getattr(current_user, 'shop', None)) else Shop.query.first()
        now_bd = bangladesh_time()
        if current_shop and getattr(current_shop, 'license_expires_at', None):
            expires_at = current_shop.license_expires_at
            days_left = max(0, (expires_at.date() - now_bd.date()).days)
            is_active = getattr(current_shop, 'active', True)
            if not is_active or days_left <= 0:
                status = "expired"
            else:
                status = "licensed"
            license_info = {"status": status, "expires_at": expires_at, "days_left": days_left}
        else:
            status, payload, state = licence_status(
                app.config["LICENSE_PUBLIC_KEY_PATH"], app.config["LICENSE_ENFORCEMENT"]
            )
            expires_at = state.trial_expires_at
            if status == "licensed" and payload:
                expires_at = datetime.fromisoformat(payload["expires_at"])
            days_left = max(0, (expires_at.date() - now_bd.date()).days)
            license_info = {"status": status, "expires_at": expires_at, "days_left": days_left}
    except Exception:
        license_info = None

    return {
        "t": lambda key: translate(key, lang),
        "l": language_label,
        "current_lang": lang,
        "csrf_token": session["_csrf_token"],
        "license_info": license_info,
        "db_status": get_db_connection_status(),
    }


@app.before_request
def protect_state_changes_and_licence():
    """Block forged form submissions and enforce the installed POS licence."""
    if request.path.startswith("/api/mobile/"):
        return None

    if request.method in {"POST", "PUT", "PATCH", "DELETE"}:
        submitted_token = request.form.get("_csrf_token") or request.headers.get("X-CSRF-Token")
        if not submitted_token or submitted_token != session.get("_csrf_token"):
            abort(400, "Invalid form security token. Please refresh and try again.")

    # Whitelisted endpoints accessible without login or license check
    if request.endpoint in {"static", "license_activation", "customer_receipt", "setup", "signup", "login", "forgot_password", "reset_password", "set_language", "api_db_status", "logout"}:
        return None

    try:
        has_any_user = bool(User.query.first())
    except Exception:
        try:
            db.session.rollback()
            has_any_user = bool(User.query.first())
        except Exception:
            has_any_user = True

    if not has_any_user:
        return redirect(url_for("setup"))

    # Step 1: Enforce user authentication first (User must login before license check!)
    if not current_user.is_authenticated:
        return redirect(url_for("login"))

    # Step 2: User is authenticated; now check their shop's validity & license expiry
    current_shop = getattr(current_user, 'shop', None)
    if current_shop:
        now_bd = bangladesh_time()
        
        # Auto check Supabase cloud for license update if near expiry or expired
        if current_shop.license_expires_at and current_shop.license_expires_at <= (now_bd + timedelta(days=2)):
            from sync_manager import sync_shop_info_from_cloud
            sync_shop_info_from_cloud(current_shop.id, app, db)

        # Check if shop was suspended/locked by vendor
        if hasattr(current_shop, 'active') and current_shop.active is False:
            return redirect(url_for("license_activation"))

        # Check if shop license has expired
        if current_shop.license_expires_at and current_shop.license_expires_at < now_bd:
            return redirect(url_for("license_activation"))

    status, _, _ = licence_status(
        app.config["LICENSE_PUBLIC_KEY_PATH"],
        app.config["LICENSE_ENFORCEMENT"],
    )
    if status == "expired":
        return redirect(url_for("license_activation"))
    return None


@app.route("/activation", methods=["GET", "POST"])
def license_activation():
    state = get_installation()
    current_shop = current_user.shop if (current_user.is_authenticated and getattr(current_user, 'shop', None)) else Shop.query.first()

    # Sync latest shop status & license expiry from Supabase Cloud
    if current_shop:
        from sync_manager import sync_shop_info_from_cloud
        sync_shop_info_from_cloud(current_shop.id, app, db)

    status, payload, _ = licence_status(
        app.config["LICENSE_PUBLIC_KEY_PATH"],
        app.config["LICENSE_ENFORCEMENT"],
    )
    effective_expiry = current_shop.license_expires_at if (current_shop and getattr(current_shop, 'license_expires_at', None)) else state.trial_expires_at

    now_bd = bangladesh_time()
    is_expired = False
    if effective_expiry and effective_expiry < now_bd:
        is_expired = True
    if status == "expired":
        is_expired = True
    if current_shop and getattr(current_shop, 'active', True) is False:
        is_expired = True

    trial_days_left = max(0, (effective_expiry.date() - now_bd.date()).days) if (effective_expiry and not is_expired) else 0

    if request.method == "POST":
        requested_plan = request.form.get("requested_plan")
        if requested_plan and current_shop:
            try:
                current_shop.subscription_plan = requested_plan
                amount = 500.0
                days = 30
                if "6 Month" in requested_plan or "2,800" in requested_plan or "2800" in requested_plan:
                    amount = 2800.0
                    days = 180
                elif "1 Year" in requested_plan or "5,500" in requested_plan or "5500" in requested_plan:
                    amount = 5500.0
                    days = 365

                trx_id = request.form.get("trx_id", "").strip()
                pay_method = request.form.get("payment_method", "bKash").strip()
                proof = request.form.get("proof_note", "").strip()

                # Handle payment proof screenshot / slip file upload
                proof_file = request.files.get("proof_file")
                proof_file_url = None
                if proof_file and proof_file.filename:
                    from werkzeug.utils import secure_filename
                    orig_name = secure_filename(proof_file.filename) or "slip.png"
                    ext = os.path.splitext(orig_name)[1].lower()
                    if ext in [".png", ".jpg", ".jpeg", ".webp", ".pdf"]:
                        upload_folder = os.path.join(STATIC_DIR, "uploads", "payment_proofs")
                        os.makedirs(upload_folder, exist_ok=True)
                        safe_filename = f"proof_shop{current_shop.id}_{int(time.time())}_{orig_name}"
                        full_save_path = os.path.join(upload_folder, safe_filename)
                        proof_file.save(full_save_path)
                        proof_file_url = f"/static/uploads/payment_proofs/{safe_filename}"

                final_proof = proof_file_url or proof

                sub_pay = SubscriptionPayment(
                    shop_id=current_shop.id,
                    amount_paid=amount,
                    payment_method=pay_method,
                    transaction_id=trx_id,
                    duration_days=days,
                    old_expiry_date=current_shop.license_expires_at,
                    new_expiry_date=effective_expiry or (datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(days=days)),
                    payment_date=datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6),
                    note=f"Requested Package: {requested_plan} | {proof}" if proof else f"Requested Package: {requested_plan}",
                    status="Pending",
                    payment_proof=final_proof
                )
                db.session.add(sub_pay)
                db.session.commit()

                # Sync payment request instantly to central Supabase Cloud & Owner Portal
                sync_with_owner_portal("/api/submit-payment-request", {
                    "shop_id": current_shop.id,
                    "shop_name": current_shop.shop_name,
                    "owner_name": current_shop.owner_name,
                    "phone": current_shop.phone,
                    "requested_plan": requested_plan,
                    "amount_paid": amount,
                    "payment_method": pay_method,
                    "transaction_id": trx_id,
                    "payment_proof": final_proof,
                    "duration_days": days,
                    "old_expiry_date": current_shop.license_expires_at.isoformat() if current_shop.license_expires_at else None
                })

                flash(f"Payment proof for '{requested_plan}' submitted successfully! Pending vendor owner approval.", "success")
            except Exception as ex:
                db.session.rollback()
                app.logger.error(f"Error submitting payment proof: {ex}", exc_info=True)
                flash(f"Payment proof request submitted successfully! Pending vendor owner approval.", "success")
            return redirect(url_for("license_activation"))

        valid, result = verify_license_token(
            request.form.get("activation_key", ""), state.device_hash,
            app.config["LICENSE_PUBLIC_KEY_PATH"],
        )
        if valid:
            state.license_token = request.form["activation_key"].strip()
            state.last_validated_at = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)

            try:
                parts = request.form["activation_key"].strip().split(".")
                raw_json = json.loads(base64.urlsafe_b64decode(parts[0] + "==").decode("utf-8"))
                if "expires_at" in raw_json:
                    token_expiry = datetime.fromisoformat(raw_json["expires_at"])
                    if current_shop:
                        current_shop.license_expires_at = token_expiry
                    effective_expiry = token_expiry
            except Exception:
                pass

            db.session.commit()
            flash("Activation successful. Your POS is ready.", "success")
            return redirect(url_for("login"))
        flash(result, "danger")

    return render_template(
        "activation.html",
        status=status,
        payload=payload,
        state=state,
        request_code=activation_request_code(state),
        trial_days_left=trial_days_left,
        effective_expiry=effective_expiry,
        current_shop=current_shop,
        is_expired=is_expired
    )


@app.route("/customer/receipt/<int:payment_id>", methods=["GET"])
def customer_receipt(payment_id):
    payment = SubscriptionPayment.query.get_or_404(payment_id)
    return render_template("customer_receipt.html", p=payment)


@app.route("/set-language/<lang>")
def set_language(lang):

    if lang not in ("en", "bn", "both"):
        lang = "en"

    session["lang"] = lang
    session.permanent = True

    return redirect(request.referrer or url_for("dashboard"))





def generate_supplier_code():

    last = Supplier.query.order_by(
        Supplier.id.desc()
    ).first()

    if last:

        try:

            number = int(
                last.supplier_code.replace(
                    "SUP",
                    ""
                )
            ) + 1

        except:

            number = 1

    else:

        number = 1

    return f"SUP{number:05d}"


@login_manager.user_loader
def load_user(user_id):
    return db.session.get(User, int(user_id))


# =====================
# BACKUP CONFIG (UNCHANGED AS YOU REQUESTED)
# =====================
DB_PATH = os.path.join(BASE_DIR, "shop.db")
BACKUP_DIR = os.path.join(BASE_DIR, "backups", "local")


# =====================
# START BACKUP SYSTEM & RECURRING EXPENSE SCHEDULER
# =====================
if os.environ.get("START_SCHEDULER") == "1":
    try:
        start_backup_system()
    except Exception:
        pass
    try:
        _recurring_scheduler = BackgroundScheduler()
        _recurring_scheduler.add_job(
            lambda: generate_due_recurring_expenses(),
            trigger="interval",
            hours=24
        )
        if not _recurring_scheduler.running:
            _recurring_scheduler.start()
    except Exception:
        pass



# =====================
# BACKUP ROUTES
# =====================

@app.route("/run-backup", methods=["POST"])
@login_required
@permission_required("backups")
def run_backup():

    try:
        create_local_backup()
        upload_to_gdrive()

        flash("Backup completed successfully!", "success")

    except Exception as e:
        flash(f"Backup failed: {str(e)}", "danger")

    return redirect(url_for("backups"))


@app.route("/gdrive-status-api")
@login_required
@permission_required("backups")
def gdrive_status_api():
    return jsonify(gdrive_status())


@app.route("/gdrive-connect", methods=["POST"])
@login_required
@permission_required("backups")
def gdrive_connect():
    file = request.files.get("credentials_file")

    if not file or file.filename == "":
        flash("Please choose a credentials JSON file.", "danger")
        return redirect(url_for("backups"))

    try:
        client_secrets_json = json.load(file)
        ok = connect_gdrive(client_secrets_json)

        if ok:
            flash("✅ Google Drive connected successfully!", "success")
        else:
            flash("❌ Could not connect Google Drive. Check the credentials file.", "danger")

    except Exception as e:
        flash(f"❌ Connect failed: {str(e)}", "danger")

    return redirect(url_for("backups"))


@app.route("/gdrive-disconnect", methods=["POST"])
@login_required
@permission_required("backups")
def gdrive_disconnect():
    try:
        disconnect_gdrive()
        flash("Google Drive disconnected.", "success")
    except Exception as e:
        flash(f"❌ Disconnect failed: {str(e)}", "danger")

    return redirect(url_for("backups"))


@app.route("/gdrive-backup-now", methods=["POST"])
@login_required
@permission_required("backups")
def gdrive_backup_now():
    try:
        upload_to_gdrive()
        flash("✅ Uploaded to Google Drive successfully!", "success")
    except Exception as e:
        flash(f"❌ Google Drive upload failed: {str(e)}", "danger")

    return redirect(url_for("backups"))


@app.route("/backups")
@login_required
@permission_required("backups")
def backups():
    try:
        os.makedirs(BACKUP_DIR, exist_ok=True)
        files = [f for f in os.listdir(BACKUP_DIR) if f.endswith(".db")]
        files = sorted(files, reverse=True)
    except Exception as ex:
        print(f"[BACKUP LIST WARNING] {ex}")
        files = []

    return render_template("backups.html", backups=files)


@app.route("/restore-backup/<filename>", methods=["POST"])
@login_required
@permission_required("backups")
def restore_backup(filename):
    # Do not allow a crafted filename to point outside the backup folder.
    if os.path.basename(filename) != filename or not filename.endswith(".db"):
        abort(400)
    backup_file = os.path.join(BACKUP_DIR, filename)

    if not os.path.exists(backup_file):
        flash("❌ ব্যাকআপ ফাইলটি খুঁজে পাওয়া যায়নি!", "danger")
        return redirect(url_for("backups"))

    try:
        # Close active connections and dispose SQLAlchemy engine pool to release Windows file locks
        db.session.remove()
        db.engine.dispose()

        shutil.copy2(backup_file, DB_PATH)

        # Re-dispose engine to bind new database file
        db.engine.dispose()

        # Run auto-migration on restored DB so old backup schemas get missing columns automatically
        try:
            db.create_all()
            is_postgres = "postgres" in str(app.config.get("SQLALCHEMY_DATABASE_URI", "")).lower()
            alter_queries = [
                'ALTER TABLE "user" ADD COLUMN phone VARCHAR(20)' if is_postgres else 'ALTER TABLE user ADD COLUMN phone VARCHAR(20)',
                'ALTER TABLE "user" ADD COLUMN email VARCHAR(120)' if is_postgres else 'ALTER TABLE user ADD COLUMN email VARCHAR(120)',
                'ALTER TABLE "user" ADD COLUMN must_change_password BOOLEAN DEFAULT FALSE' if is_postgres else 'ALTER TABLE user ADD COLUMN must_change_password BOOLEAN DEFAULT 0',
                'ALTER TABLE "user" ADD COLUMN last_login_at TIMESTAMP' if is_postgres else 'ALTER TABLE user ADD COLUMN last_login_at DATETIME',
                'ALTER TABLE "user" ADD COLUMN last_login_ip VARCHAR(50)' if is_postgres else 'ALTER TABLE user ADD COLUMN last_login_ip VARCHAR(50)',
                'ALTER TABLE "user" ADD COLUMN last_login_device VARCHAR(255)' if is_postgres else 'ALTER TABLE user ADD COLUMN last_login_device VARCHAR(255)',
                'ALTER TABLE shop ADD COLUMN subscription_plan VARCHAR(50) DEFAULT \'Monthly (৳500)\'',
                'ALTER TABLE shop ADD COLUMN active BOOLEAN DEFAULT 1',
                'ALTER TABLE shop ADD COLUMN license_expires_at DATETIME'
            ]
            for q in alter_queries:
                try:
                    db.session.execute(text(q))
                    db.session.commit()
                except Exception:
                    db.session.rollback()
        except Exception:
            db.session.rollback()

        flash("✅ ডাটাবেজ ব্যাকআপ সফলভাবে রিস্টোর করা হয়েছে!", "success")
    except Exception as e:
        db.session.rollback()
        print(f"[RESTORE ERROR] {e}")
        flash(f"❌ ডাটাবেজ রিস্টোর করতে সমস্যা হয়েছে: {str(e)}", "danger")

    return redirect(url_for("backups"))


@app.route("/upload-restore-backup", methods=["POST"])
@login_required
@permission_required("backups")
def upload_restore_backup():
    if "backup_file" not in request.files:
        flash("❌ কোনো ব্যাকআপ ফাইল সিলেক্ট করা হয়নি!", "danger")
        return redirect(url_for("backups"))

    file = request.files["backup_file"]
    if not file or file.filename == "":
        flash("❌ ব্যাকআপ ফাইল সিলেক্ট করুন!", "danger")
        return redirect(url_for("backups"))

    if not file.filename.endswith(".db"):
        flash("❌ শুধুমাত্র .db ফরম্যাটের ডাটাবেজ ব্যাকআপ ফাইল আপলোড করা যাবে!", "danger")
        return redirect(url_for("backups"))

    try:
        os.makedirs(BACKUP_DIR, exist_ok=True)
        safe_name = f"uploaded_{datetime.now().strftime('%Y-%m-%d_%H-%M-%S')}_{os.path.basename(file.filename)}"
        target_path = os.path.join(BACKUP_DIR, safe_name)
        file.save(target_path)

        # Close active connections and dispose SQLAlchemy engine pool
        db.session.remove()
        db.engine.dispose()

        shutil.copy2(target_path, DB_PATH)
        db.engine.dispose()

        # Run auto-migration on restored DB
        try:
            db.create_all()
        except Exception:
            pass

        flash("✅ ক্লাউড/বাহ্যিক ব্যাকআপ ফাইল সফলভাবে আপলোড ও রিস্টোর করা হয়েছে!", "success")
    except Exception as e:
        db.session.rollback()
        print(f"[UPLOAD RESTORE ERROR] {e}")
        flash(f"❌ ব্যাকআপ রিস্টোর করতে সমস্যা হয়েছে: {str(e)}", "danger")

    return redirect(url_for("backups"))


@app.route("/cloud-backup-now", methods=["POST"])
@login_required
@permission_required("backups")
def cloud_backup_now():
    """Manually triggers immediate upload of all local shop data to Supabase Cloud DB."""
    try:
        from sync_manager import push_shop_data_to_cloud
        ok, msg = push_shop_data_to_cloud(current_user.shop_id, app, db)
        if ok:
            flash("✅ ক্লাউড ডাটাবেজে (Supabase) সমস্ত পণ্য, কাস্টমার, ইনভয়েস ও হিসাব সফলভাবে ব্যাকআপ হয়েছে!", "success")
        else:
            flash(f"❌ ক্লাউড ব্যাকআপ ব্যর্থ হয়েছে: {msg}", "danger")
    except Exception as e:
        flash(f"❌ সমস্যা হয়েছে: {str(e)}", "danger")
    return redirect(url_for("backups"))


@app.route("/cloud-restore-now", methods=["POST"])
@login_required
@permission_required("backups")
def cloud_restore_now():
    """Restores all shop data from Supabase Cloud DB into local SQLite without needing Google Drive."""
    try:
        from sync_manager import restore_shop_data_from_cloud
        ok, msg = restore_shop_data_from_cloud(current_user.shop_id, app, db)
        if ok:
            flash(f"✅ ক্লাউড ডাটাবেজ থেকে ডাটা সফলভাবে রিকভার হয়েছে! ({msg})", "success")
        else:
            flash(f"❌ ক্লাউড থেকে রিস্টোর করতে সমস্যা হয়েছে: {msg}", "danger")
    except Exception as e:
        flash(f"❌ সমস্যা হয়েছে: {str(e)}", "danger")
    return redirect(url_for("backups"))


# ---------------- LICENSE & ACTIVE STATUS ENFORCEMENT ----------------

@app.before_request
def check_shop_license_and_active_status():
    if current_user.is_authenticated:
        # Exempt static files, logout, and activation
        if request.endpoint in ['static', 'logout', 'license_activation', 'customer_receipt', 'set_language', 'api_db_status']:
            return None

        shop = getattr(current_user, 'shop', None)
        if shop:
            now_bd = bangladesh_time()
            # Check 1: Shop active status (Vendor manual lock/suspend)
            if hasattr(shop, 'active') and shop.active is False:
                return redirect(url_for("license_activation"))

            # Check 2: License expiry date check
            if hasattr(shop, 'license_expires_at') and shop.license_expires_at:
                if now_bd > shop.license_expires_at:
                    return redirect(url_for("license_activation"))

# ---------------- HOME ----------------

@app.route("/")
def home():

    if current_user.is_authenticated:

        return redirect(
            url_for("dashboard")
        )

    return redirect(
        url_for("login")
    )



def sync_with_owner_portal(endpoint, payload):
    """Sends background sync HTTP request to central Owner Portal with direct Supabase Cloud DB fallback."""
    import threading
    import urllib.request
    import json
    from sync_manager import sync_remote_shop_to_supabase_db, sync_payment_request_to_supabase_db

    owner_portal_url = os.environ.get("POS_OWNER_PORTAL_URL", "").rstrip("/")

    def _do_sync():
        success = False

        # 1. Try direct Supabase Cloud DB insertion first for instant remote shop registration & payment requests
        if endpoint == "/api/register-remote-shop":
            success = sync_remote_shop_to_supabase_db(payload)
        elif endpoint == "/api/submit-payment-request":
            success = sync_payment_request_to_supabase_db(payload)

        # 2. Try HTTP POST if Owner Portal URL is configured
        if not success and owner_portal_url and not owner_portal_url.startswith("http://127.0.0.1"):
            target_url = f"{owner_portal_url}{endpoint}"
            try:
                raw_data = json.dumps(payload).encode("utf-8")
                req = urllib.request.Request(
                    target_url,
                    data=raw_data,
                    headers={"Content-Type": "application/json", "User-Agent": payload.get("device_info", "Shop POS Client")}
                )
                with urllib.request.urlopen(req, timeout=5) as resp:
                    if resp.status in (200, 201):
                        success = True
            except Exception:
                success = False

        if not success:
            try:
                with app.app_context():
                    q_item = SyncQueue(
                        entity_type=endpoint.strip("/").replace("api/", ""),
                        endpoint=endpoint,
                        payload_json=json.dumps(payload),
                        synced=False,
                        attempts=1
                    )
                    db.session.add(q_item)
                    db.session.commit()
            except Exception:
                db.session.rollback()

    thread = threading.Thread(target=_do_sync, daemon=True)
    thread.start()


# ---------------- SETUP WIZARD ----------------

@app.route("/setup", methods=["GET", "POST"])
def setup():
    if User.query.first():
        return redirect(url_for("login"))

    if request.method == "POST":
        shop_name = request.form.get("shop_name", "").strip()
        owner_name = request.form.get("owner_name", "").strip()
        phone = request.form.get("phone", "").strip()
        email = request.form.get("email", "").strip().lower()
        username = request.form.get("username", "").strip() or email.split("@")[0]
        password = request.form.get("password", "")
        confirm_password = request.form.get("confirm_password", "")

        if not shop_name or not owner_name or not email or not phone or not password:
            flash("দোকানের নাম, মালিকের নাম, ফোন নম্বর, Gmail এবং পাসওয়ার্ড আবশ্যক।", "danger")
            return render_template("setup.html")

        if password != confirm_password:
            flash("পাসওয়ার্ড দুটি মিলছে না।", "danger")
            return render_template("setup.html")

        if len(password) < 6:
            flash("পাসওয়ার্ড অন্তত ৬ অক্ষরের হতে হবে।", "danger")
            return render_template("setup.html")

        existing = User.query.filter(
            (func.lower(User.email) == email) |
            (func.lower(User.phone) == phone) |
            (func.lower(User.username) == username.lower())
        ).first()

        if existing:
            flash("এই Gmail, ফোন নম্বর অথবা ইউজারনেম ইতোমধ্যে ব্যবহৃত হয়েছে।", "danger")
            return render_template("setup.html")

        now_bd = bangladesh_time()
        initial_expiry = now_bd + timedelta(days=7)
        shop = Shop.query.first()
        if not shop:
            shop = Shop(shop_name=shop_name, owner_name=owner_name, phone=phone, active=True, subscription_plan="Free Trial (7 Days)", license_expires_at=initial_expiry)
            db.session.add(shop)
            db.session.commit()
        else:
            shop.shop_name = shop_name
            shop.owner_name = owner_name
            if not getattr(shop, 'license_expires_at', None):
                shop.license_expires_at = initial_expiry
            shop.active = True
            db.session.commit()

        admin = User(
            username=username,
            email=email,
            phone=phone,
            shop_id=shop.id,
            role="owner",
            must_change_password=False
        )
        admin.set_password(password)
        db.session.add(admin)
        db.session.commit()

        login_user(admin)

        # Trigger auto sync to central Owner Portal
        ip_addr = request.headers.get("X-Forwarded-For", request.remote_addr or "").split(",")[0].strip()
        user_agent = request.headers.get("User-Agent", "Unknown Device")
        sync_with_owner_portal("/api/register-remote-shop", {
            "shop_name": shop_name,
            "owner_name": owner_name,
            "phone": phone,
            "email": email,
            "admin_username": username,
            "admin_password": password,
            "ip_address": ip_addr,
            "device_info": user_agent
        })

        flash("অভিনন্দন! আপনার শপ ও অ্যাকাউন্ট সফলভাবে তৈরি করা হয়েছে।", "success")
        return redirect(url_for("dashboard"))

    return render_template("setup.html")


# ---------------- SIGN UP WIZARD ----------------

@app.route("/signup", methods=["GET", "POST"])
def signup():
    if request.method == "POST":
        shop_name = request.form.get("shop_name", "").strip()
        owner_name = request.form.get("owner_name", "").strip()
        phone = request.form.get("phone", "").strip()
        email = request.form.get("email", "").strip().lower()
        password = request.form.get("password", "")
        confirm_password = request.form.get("confirm_password", "")

        # Auto-derive username from email prefix or phone
        username = email.split("@")[0] if (email and "@" in email) else (phone or f"user_{secrets.token_hex(4)}")

        if not shop_name or not owner_name or not email or not phone or not password:
            flash("দোকানের নাম, মালিকের নাম, মোবাইল নম্বর, জিমেইল এবং পাসওয়ার্ড আবশ্যক।", "danger")
            return render_template("signup.html")

        if password != confirm_password:
            flash("পাসওয়ার্ড দুটি মিলছে না।", "danger")
            return render_template("signup.html")

        if len(password) < 6:
            flash("পাসওয়ার্ড অন্তত ৬ অক্ষরের হতে হবে।", "danger")
            return render_template("signup.html")

        existing_shop = Shop.query.filter(func.lower(Shop.shop_name) == shop_name.lower()).first()
        if existing_shop:
            flash(f"'{shop_name}' নামের একটি শপ ইতোমধ্যে খোলা রয়েছে। অনুগ্রহ করে অন্য শপের নাম অথবা ভিন্ন নাম ব্যবহার করুন।", "danger")
            return render_template("signup.html")

        # Handle potential username collision gracefully per shop
        existing_username = User.query.filter(func.lower(User.username) == username.lower()).first()
        if existing_username:
            username = f"{username}_{secrets.token_hex(2)}"

        try:
            # Create new Shop with initial 7-day free trial license
            now_bd = bangladesh_time()
            initial_expiry = now_bd + timedelta(days=7)
            new_shop = Shop(shop_name=shop_name, owner_name=owner_name, phone=phone, active=True, subscription_plan="Free Trial (7 Days)", license_expires_at=initial_expiry)
            db.session.add(new_shop)
            db.session.commit()

            admin = User(
                username=username,
                email=email,
                phone=phone,
                shop_id=new_shop.id,
                role="owner",
                must_change_password=False
            )
            admin.set_password(password)
            db.session.add(admin)
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            print(f"[SIGNUP ERROR] {e}")
            flash(f"অ্যাকাউন্ট তৈরি করতে সমস্যা হয়েছে: {str(e)}", "danger")
            return render_template("signup.html")

        login_user(admin)

        # Trigger auto sync to central Owner Portal
        ip_addr = request.headers.get("X-Forwarded-For", request.remote_addr or "").split(",")[0].strip()
        user_agent = request.headers.get("User-Agent", "Unknown Device")
        sync_with_owner_portal("/api/register-remote-shop", {
            "shop_name": shop_name,
            "owner_name": owner_name,
            "phone": phone,
            "email": email,
            "admin_username": username,
            "admin_password": password,
            "ip_address": ip_addr,
            "device_info": user_agent
        })

        flash("অভিনন্দন! আপনার শপ অ্যাকাউন্ট সফলভাবে সাইন আপ করা হয়েছে।", "success")
        return redirect(url_for("dashboard"))


    return render_template("signup.html")


# ---------------- LOGIN ----------------


@app.route("/login", methods=["GET","POST"])
def login():

    if request.method == "POST":

        login_input = request.form.get("username", "").strip()
        password = request.form.get("password", "")

        users = User.find_all_by_login_identifier(login_input)
        user = next((u for u in users if u.check_password(password)), None)

        if not user:
            from sync_manager import authenticate_and_sync_cloud_user
            user = authenticate_and_sync_cloud_user(login_input, password, app, db, User, Shop)

        if user:

            login_user(user)

            # Record login IP & Device details
            ip_addr = request.headers.get("X-Forwarded-For", request.remote_addr or "").split(",")[0].strip()
            user_agent = request.headers.get("User-Agent", "Unknown Device")
            
            device_str = "Desktop PC"
            if "Windows" in user_agent:
                device_str = "Windows PC"
            elif "Macintosh" in user_agent:
                device_str = "Mac PC"
            elif "Android" in user_agent:
                device_str = "Android Mobile/Tablet"
            elif "iPhone" in user_agent or "iPad" in user_agent:
                device_str = "iOS Device"
            elif "Linux" in user_agent:
                device_str = "Linux Machine"
            
            if user_agent and "Mozilla" in user_agent:
                if "Edg" in user_agent:
                    device_str += " (Edge)"
                elif "Chrome" in user_agent:
                    device_str += " (Chrome)"
                elif "Firefox" in user_agent:
                    device_str += " (Firefox)"
                elif "Safari" in user_agent:
                    device_str += " (Safari)"

            try:
                user.last_login_at = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)
                user.last_login_ip = ip_addr
                user.last_login_device = device_str

                log_action(
                    user.shop_id or 1,
                    user.id,
                    "User Login",
                    f"Logged in from {device_str} (IP: {ip_addr})",
                    ip_address=ip_addr,
                    device_info=device_str
                )
                db.session.commit()

                # Sync login activity ping to central Owner Portal
                sync_with_owner_portal("/api/ping-shop-activity", {
                    "shop_name": user.shop.shop_name if user.shop else "Client Shop",
                    "username": user.username,
                    "phone": user.phone or "",
                    "ip_address": ip_addr,
                    "device_info": device_str,
                    "action": "User Login"
                })

                # Auto-sync latest Company Name & License from Supabase Cloud DB
                if user.shop_id:
                    try:
                        from sync_manager import sync_shop_info_from_cloud
                        threading.Thread(
                            target=sync_shop_info_from_cloud,
                            args=(user.shop_id, app, db),
                            daemon=True
                        ).start()
                    except Exception:
                        pass

                    # Auto-restore data from Supabase Cloud DB if local shop is empty (fresh PC / new install)
                    prod_count = Product.query.filter_by(shop_id=user.shop_id).count()
                    if prod_count == 0:
                        try:
                            from sync_manager import restore_shop_data_from_cloud
                            threading.Thread(
                                target=restore_shop_data_from_cloud,
                                args=(user.shop_id, app, db),
                                daemon=True
                            ).start()
                        except Exception as rex:
                            print(f"[AUTO CLOUD RESTORE WARNING] {rex}")
            except Exception as e:
                db.session.rollback()

            # First-login forced password change
            if user.must_change_password:
                flash(
                    "প্রথমবার লগইনের পর আপনাকে পাসওয়ার্ড পরিবর্তন করতে হবে।",
                    "warning"
                )
                return redirect(url_for("account"))

            return redirect(
                url_for("dashboard")
            )


        flash(
            "ইউজারনেম, জিমেইল বা ফোন নম্বর এবং পাসওয়ার্ড সঠিক নয়।",
            "danger"
        )


    return render_template(
        "login.html",
        lan_ip=get_lan_ip()
    )


# ---------------- FORGOT PASSWORD (self-service, security question) ----------------

@app.route("/forgot-password", methods=["GET", "POST"])
def forgot_password():
    """
    Self-service password recovery, two possible methods depending on
    what the account has set up in My Account:

      1) Email code (primary, needs internet) — a 6-digit code is emailed
         to the address the user saved; valid for 10 minutes.
      2) Security question (offline fallback) — used only if no email
         is on file, so the app still works without internet.

    Nothing here reveals whether a username exists, to avoid leaking
    valid usernames to someone probing the form.
    """

    step = "find"
    username = ""
    question = None
    masked_email = None
    attempts = session.get("recovery_attempts", 0)

    if request.method == "POST":
        stage = request.form.get("stage", "find")

        if stage == "find":
            identifier = request.form.get("username", "").strip()
            users = User.find_all_by_login_identifier(identifier)
            user = users[0] if users else None

            if user and user.email:
                code = f"{secrets.randbelow(1000000):06d}"
                user.set_reset_code(code)
                db.session.commit()

                sent, error = send_email(
                    user.email,
                    "Your Shop Manager password reset code",
                    f"Your password reset code is: {code}\n\n"
                    "This code expires in 10 minutes. If you did not request "
                    "this, you can safely ignore this email.",
                )

                if sent:
                    session["recovery_username"] = user.username
                    session["recovery_attempts"] = 0
                    step = "email_code"
                    local, _, domain = user.email.partition("@")
                    masked_email = f"{local[:2]}***@{domain}" if len(local) > 2 else f"***@{domain}"
                else:
                    # No internet / SMTP not configured — fall back to security question if set.
                    user.clear_reset_code()
                    db.session.commit()
                    if user.recovery_answer_hash:
                        session["recovery_username"] = username
                        session["recovery_attempts"] = 0
                        step = "question"
                        question = user.recovery_question
                        flash(f"Could not send email ({error}). Using your security question instead.", "warning")
                    else:
                        flash(f"Could not send the reset email right now: {error}", "danger")
                        step = "find"

            elif user and user.recovery_answer_hash:
                session["recovery_username"] = username
                session["recovery_attempts"] = 0
                step = "question"
                question = user.recovery_question

            else:
                flash(
                    "No recovery method is set up for this account. "
                    "Please ask another admin/owner to reset your password.",
                    "danger",
                )
                step = "find"

        elif stage in {"email_code", "question"}:
            username = session.get("recovery_username", "")
            user = User.query.filter_by(username=username).first()

            if not user:
                flash("Recovery session expired. Please start again.", "danger")
                session.pop("recovery_username", None)
                step = "find"
            elif attempts >= 5:
                flash("Too many incorrect attempts. Please start again.", "danger")
                session.pop("recovery_username", None)
                session.pop("recovery_attempts", None)
                user.clear_reset_code()
                db.session.commit()
                step = "find"
            else:
                new_password = request.form.get("password", "").strip()
                confirm_password = request.form.get("confirm_password", "").strip()

                if stage == "email_code":
                    code = request.form.get("code", "")
                    verified = user.check_reset_code(code)
                    fail_step, question = "email_code", None
                else:
                    answer = request.form.get("answer", "")
                    verified = user.check_recovery_answer(answer)
                    fail_step, question = "question", user.recovery_question

                if not verified:
                    session["recovery_attempts"] = attempts + 1
                    flash("Incorrect code/answer. Please try again.", "danger")
                    step = fail_step
                elif len(new_password) < 6:
                    flash("New password must be at least 6 characters.", "danger")
                    step = fail_step
                elif new_password != confirm_password:
                    flash("Passwords do not match.", "danger")
                    step = fail_step
                else:
                    user.set_password(new_password)
                    user.clear_reset_code()
                    log_action(user.shop_id, user.id, "Password Reset", f"Self-service password reset via {stage}")
                    db.session.commit()
                    session.pop("recovery_username", None)
                    session.pop("recovery_attempts", None)
                    flash("Password reset successful. Please log in.", "success")
                    return redirect(url_for("login"))

    return render_template(
        "forgot_password.html",
        step=step,
        username=username,
        question=question,
        masked_email=masked_email,
    )



# ---------------- PRODUCT CODE ----------------


def generate_product_code():
    return f"PRD-{uuid.uuid4().hex[:8].upper()}"



# ---------------- PRODUCTS ----------------


@app.route("/products")
@login_required
@permission_required("products")
def products():
    from datetime import date, timedelta
    today = date.today()

    search = (request.args.get("q") or request.args.get("search") or "").strip()
    stock_status = request.args.get("stock_status", "all").strip()

    base_query = Product.query.filter_by(shop_id=current_user.shop_id)
    all_products_raw = base_query.all()

    total_products = len(all_products_raw)
    active_products = sum(1 for p in all_products_raw if p.active)
    low_stock_count = sum(1 for p in all_products_raw if p.stock > 0 and p.stock <= p.reorder_level)
    out_of_stock_count = sum(1 for p in all_products_raw if p.stock <= 0)
    expired_count = sum(1 for p in all_products_raw if p.expiry_date and p.days_until_expiry is not None and p.days_until_expiry <= 30)

    query = base_query
    if search:
        query = query.filter(
            db.or_(
                Product.name.ilike(f"%{search}%"),
                Product.category.ilike(f"%{search}%"),
                Product.product_code.ilike(f"%{search}%"),
                Product.barcode.ilike(f"%{search}%")
            )
        )

    if stock_status == "low":
        query = query.filter(Product.stock > 0, Product.stock <= Product.reorder_level)
    elif stock_status == "out_of_stock":
        query = query.filter(Product.stock <= 0)
    elif stock_status == "expired":
        query = query.filter(Product.expiry_date != None, Product.expiry_date <= today + timedelta(days=30))
    elif stock_status == "low_or_out":
        query = query.filter(Product.stock <= Product.reorder_level)

    filtered_products = query.order_by(Product.id.desc()).all()

    return render_template(
        "products.html",
        products=filtered_products,
        search=search,
        stock_status=stock_status,
        total_products=total_products,
        active_products=active_products,
        low_stock_count=low_stock_count,
        out_of_stock_count=out_of_stock_count,
        expired_count=expired_count
    )

# ---------------- ADD PRODUCT ----------------


def save_product_image(file_storage):
    if not file_storage or not file_storage.filename:
        return None

    ext = os.path.splitext(file_storage.filename)[1].lower()
    if ext not in (".png", ".jpg", ".jpeg", ".webp", ".gif"):
        return None

    filename = f"prod_{datetime.now().strftime('%Y%m%d%H%M%S%f')}{ext}"
    save_path = os.path.join(STATIC_DIR, "uploads", "products", filename)
    file_storage.save(save_path)

    return f"uploads/products/{filename}"


def safe_float(val, default=0.0):
    if not val:
        return default
    try:
        return float(str(val).strip().replace(",", ""))
    except Exception:
        return default


def safe_int(val, default=0):
    if not val:
        return default
    try:
        return int(float(str(val).strip().replace(",", "")))
    except Exception:
        return default


@app.route("/add-product", methods=["POST"])
@login_required
@permission_required("products")
def add_product():
    try:
        name = request.form.get("name", "").strip()
        if not name:
            flash("পণ্যের নাম প্রদান করা আবশ্যক।", "danger")
            return redirect(url_for("products"))

        expiry_raw = request.form.get("expiry_date", "").strip()
        expiry_date = None
        if expiry_raw:
            try:
                expiry_date = datetime.strptime(expiry_raw, "%Y-%m-%d").date()
            except Exception:
                expiry_date = None

        barcode = request.form.get("barcode", "").strip() or None
        if barcode:
            existing_barcode = Product.query.filter_by(shop_id=current_user.shop_id, barcode=barcode).first()
            if existing_barcode:
                flash(f"বারকোড '{barcode}' ইতিমধ্যেই অন্য একটি পণ্যে ({existing_barcode.name}) যুক্ত আছে। অনুগ্রহ করে ভিন্ন বারকোড লিখুন।", "danger")
                return redirect(url_for("products"))

        product_code = generate_product_code()
        product = Product(
            shop_id=current_user.shop_id,
            product_code=product_code,
            name=name,
            category=request.form.get("category", "").strip(),
            unit=request.form.get("unit", "").strip() or "pcs",
            buy_price=safe_float(request.form.get("buy_price"), 0.0),
            sell_price=safe_float(request.form.get("sell_price"), 0.0),
            stock=safe_float(request.form.get("stock"), 0.0),
            barcode=barcode,
            brand=request.form.get("brand", "").strip() or None,
            batch_number=request.form.get("batch_number", "").strip() or None,
            expiry_date=expiry_date,
            reorder_level=safe_float(request.form.get("reorder_level"), 5.0),
            image=save_product_image(request.files.get("image"))
        )

        db.session.add(product)
        log_action(
            current_user.shop_id,
            current_user.id,
            "Add Product",
            f"Name: {product.name} | Category: {product.category or 'General'} | Price: ৳{product.sell_price:.2f} | Buy: ৳{product.buy_price:.2f} | Stock: {product.stock}"
        )
        db.session.commit()
        trigger_cloud_sync_async(current_user.shop_id)
        flash(f"পণ্য '{product.name}' (কোড: {product.product_code}) সফলভাবে যোগ করা হয়েছে।", "success")
    except IntegrityError as ie:
        db.session.rollback()
        print(f"[ADD PRODUCT INTEGRITY ERROR] {ie}")
        err_text = str(ie).lower()
        if "barcode" in err_text:
            flash("এই বারকোডটি (Barcode) ইতিমধ্যেই ডাটাবেজে অন্য একটি পণ্যে ব্যবহৃত হয়েছে।", "danger")
        elif "product_code" in err_text:
            # Fallback auto re-generate product_code and commit
            try:
                product.product_code = f"PRD-{int(datetime.now().timestamp()):06d}"
                db.session.add(product)
                db.session.commit()
                flash(f"পণ্য '{product.name}' (কোড: {product.product_code}) সফলভাবে যোগ করা হয়েছে।", "success")
            except Exception as e2:
                db.session.rollback()
                flash("পণ্যের কোডে দ্বন্দ্ব দেখা দিয়েছে। অনুগ্রহ করে পুনরায় চেষ্টা করুন।", "danger")
        else:
            flash("ডাটাবেজ ইউনিক সীমাবদ্ধতা ভঙ্গের কারণে পণ্য যোগ করা সম্ভব হয়নি।", "danger")
    except Exception as e:
        db.session.rollback()
        print(f"[ADD PRODUCT ERROR] {e}")
        flash("পণ্য যোগ করতে সমস্যা হয়েছে। অনুগ্রহ করে আপনার প্রদানকৃত তথ্যগুলো পরীক্ষা করে পুনরায় চেষ্টা করুন।", "danger")

    return redirect(url_for("products"))


@app.route("/edit-product/<int:id>", methods=["GET", "POST"])
@login_required
@permission_required("products")
def edit_product(id):
    product = Product.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    if request.method == "POST":
        expiry_raw = request.form.get("expiry_date", "").strip()

        old_name = product.name
        old_stock = product.stock or 0
        old_sell_price = product.sell_price or 0
        old_buy_price = product.buy_price or 0

        new_name = request.form.get("name", "").strip() or product.name
        new_buy_price = safe_float(request.form.get("buy_price"), product.buy_price or 0.0)
        new_sell_price = safe_float(request.form.get("sell_price"), product.sell_price or 0.0)
        new_stock = safe_float(request.form.get("stock"), product.stock or 0.0)

        changes = []
        if old_stock != new_stock:
            diff = new_stock - old_stock
            changes.append(f"Stock: {old_stock:g} -> {new_stock:g} ({diff:+g})")
        if old_sell_price != new_sell_price:
            changes.append(f"Sell Price: ৳{old_sell_price:.2f} -> ৳{new_sell_price:.2f}")
        if old_buy_price != new_buy_price:
            changes.append(f"Buy Price: ৳{old_buy_price:.2f} -> ৳{new_buy_price:.2f}")
        if old_name != new_name:
            changes.append(f"Name: '{old_name}' -> '{new_name}'")

        product.name = new_name
        product.category = request.form.get("category", "").strip()
        product.unit = request.form.get("unit", "").strip()
        product.buy_price = new_buy_price
        product.sell_price = new_sell_price
        product.stock = new_stock
        product.barcode = request.form.get("barcode", "").strip() or None
        product.brand = request.form.get("brand", "").strip() or None
        product.batch_number = request.form.get("batch_number", "").strip() or None
        
        expiry_date = None
        if expiry_raw:
            try:
                expiry_date = datetime.strptime(expiry_raw, "%Y-%m-%d").date()
            except Exception:
                expiry_date = None
        product.expiry_date = expiry_date
        product.reorder_level = safe_float(request.form.get("reorder_level"), 5.0)

        new_image = save_product_image(request.files.get("image"))
        if new_image:
            product.image = new_image

        details_str = f"Product '{old_name}': " + (", ".join(changes) if changes else "Updated details (no stock/price change)")
        log_action(
            current_user.shop_id,
            current_user.id,
            "Edit Product",
            details_str
        )
        try:
            db.session.commit()
            flash("পণ্য সফলভাবে হালনাগাদ করা হয়েছে।", "success")
            return redirect(url_for("products"))
        except IntegrityError as ie:
            db.session.rollback()
            print(f"[EDIT PRODUCT INTEGRITY ERROR] {ie}")
            flash("বারকোড বা তথ্যের দ্বৈততার কারণে হালনাগাদ করা সম্ভব হয়নি।", "danger")
        except Exception as e:
            db.session.rollback()
            print(f"[EDIT PRODUCT ERROR] {e}")
            flash("পণ্য হালনাগাদ করতে সমস্যা হয়েছে।", "danger")

    return render_template("edit_product.html", product=product)


# ---------------- BULK PRODUCT IMPORT ----------------

@app.route("/download-product-sample")
@login_required
@permission_required("products")
def download_product_sample():
    output = _io.StringIO()
    writer = csv.writer(output)
    writer.writerow(["Name", "Category", "Unit", "Buy Price", "Sell Price", "Stock", "Barcode", "Brand", "Reorder Level", "Batch Number"])
    writer.writerow(["Basmati Rice 1kg", "Grocery", "pcs", "120", "140", "50", "8901234567891", "Pran", "10", "B101"])
    writer.writerow(["Ruchi Soybean Oil 1L", "Oil", "liter", "180", "195", "30", "8909876543212", "Ruchi", "5", "B102"])
    
    mem = _io.BytesIO()
    mem.write(output.getvalue().encode('utf-8-sig'))
    mem.seek(0)
    return send_file(
        mem,
        mimetype="text/csv",
        as_attachment=True,
        download_name="product_import_sample.csv"
    )


@app.route("/import-products", methods=["POST"])
@login_required
@permission_required("products")
def import_products():
    file = request.files.get("file")
    if not file or not file.filename:
        flash("No file selected for import.", "danger")
        return redirect(url_for("products"))

    filename = file.filename.lower()
    imported_count = 0
    errors_count = 0

    try:
        rows = []
        if filename.endswith(".csv"):
            stream = _io.StringIO(file.stream.read().decode("utf-8-sig", errors="ignore"))
            csv_reader = csv.reader(stream)
            header = next(csv_reader, None)
            for r in csv_reader:
                if r:
                    rows.append(r)
        elif filename.endswith((".xlsx", ".xls")) and HAS_OPENPYXL:
            wb = openpyxl.load_workbook(file)
            sheet = wb.active
            first = True
            for r in sheet.iter_rows(values_only=True):
                if first:
                    first = False
                    continue
                if any(r):
                    rows.append([str(c or "").strip() for c in r])
        else:
            flash("Invalid file format. Please upload a .csv or .xlsx file.", "danger")
            return redirect(url_for("products"))

        for r in rows:
            if not r or len(r) < 6:
                continue
            name = str(r[0]).strip() if len(r) > 0 else ""
            category = str(r[1]).strip() if len(r) > 1 else ""
            unit = str(r[2]).strip() if len(r) > 2 else "pcs"
            try:
                buy_price = float(r[3]) if len(r) > 3 and str(r[3]).strip() != "" else 0.0
                sell_price = float(r[4]) if len(r) > 4 and str(r[4]).strip() != "" else 0.0
                stock = float(r[5]) if len(r) > 5 and str(r[5]).strip() != "" else 0.0
            except (ValueError, TypeError):
                errors_count += 1
                continue

            barcode = str(r[6]).strip() if len(r) > 6 and str(r[6]).strip() else None
            brand = str(r[7]).strip() if len(r) > 7 and str(r[7]).strip() else None
            try:
                reorder_level = float(r[8]) if len(r) > 8 and str(r[8]).strip() != "" else 5.0
            except (ValueError, TypeError):
                reorder_level = 5.0
            batch_number = str(r[9]).strip() if len(r) > 9 and str(r[9]).strip() else None

            if not name:
                errors_count += 1
                continue

            if barcode:
                existing = Product.query.filter_by(shop_id=current_user.shop_id, barcode=barcode).first()
                if existing:
                    existing.stock += stock
                    if buy_price > 0:
                        existing.buy_price = buy_price
                    if sell_price > 0:
                        existing.sell_price = sell_price
                    imported_count += 1
                    continue

            p = Product(
                shop_id=current_user.shop_id,
                product_code=generate_product_code(),
                name=name,
                category=category,
                unit=unit if unit else "pcs",
                buy_price=buy_price,
                sell_price=sell_price,
                stock=stock,
                barcode=barcode,
                brand=brand,
                reorder_level=reorder_level,
                batch_number=batch_number
            )
            db.session.add(p)
            imported_count += 1

        db.session.commit()
        log_action(current_user.shop_id, current_user.id, "Bulk Import Products", f"Imported {imported_count} products")
        flash(f"✅ {imported_count} products imported successfully! ({errors_count} rows skipped)", "success")

    except Exception as e:
        db.session.rollback()
        flash(f"❌ Product import failed: {str(e)}", "danger")

    return redirect(url_for("products"))

#-------------Stock Report---------------

@app.route("/stock-report")
@login_required
@permission_required("stock")
def stock_report():
    from datetime import date, timedelta
    today = date.today()

    search = (request.args.get("q") or request.args.get("search") or "").strip()
    stock_status = request.args.get("stock_status", "all").strip()

    base_query = Product.query.filter_by(shop_id=current_user.shop_id)
    all_products_raw = base_query.all()

    total_products = len(all_products_raw)
    total_stock_value = sum((p.stock or 0) * (p.buy_price or 0) for p in all_products_raw)
    low_stock_count = sum(1 for p in all_products_raw if (p.stock or 0) > 0 and (p.stock or 0) <= (p.reorder_level or 5))
    out_of_stock_count = sum(1 for p in all_products_raw if (p.stock or 0) <= 0)
    expired_count = sum(1 for p in all_products_raw if p.expiry_date and p.days_until_expiry is not None and p.days_until_expiry <= 30)

    query = base_query
    if search:
        query = query.filter(
            db.or_(
                Product.name.ilike(f"%{search}%"),
                Product.category.ilike(f"%{search}%"),
                Product.product_code.ilike(f"%{search}%"),
                Product.barcode.ilike(f"%{search}%")
            )
        )

    if stock_status == "low":
        query = query.filter(Product.stock > 0, Product.stock <= Product.reorder_level)
    elif stock_status == "out_of_stock":
        query = query.filter(Product.stock <= 0)
    elif stock_status == "expired":
        query = query.filter(Product.expiry_date != None, Product.expiry_date <= today + timedelta(days=30))

    filtered_products = query.order_by(Product.id.desc()).all()

    return render_template(
        "stock_report.html",
        products=filtered_products,
        search=search,
        stock_status=stock_status,
        total_products=total_products,
        total_stock_value=total_stock_value,
        low_stock_count=low_stock_count,
        out_of_stock_count=out_of_stock_count,
        expired_count=expired_count
    )
    
    
    
#--------------Product Status Active / Inactive-------

@app.route("/toggle-product/<int:id>", methods=["POST"])
@login_required
@permission_required("products")
def toggle_product(id):

    product = Product.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()

    product.active = not product.active

    db.session.commit()

    flash("Product status updated")

    return redirect(url_for("products"))




# ---------------- DELETE PRODUCT ----------------


@app.route("/delete-product/<int:id>", methods=["GET", "POST"])
@login_required
@permission_required("products")
def delete_product(id):


    product = Product.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()


    log_action(
        current_user.shop_id,
        current_user.id,
        "Delete Product",
        f"Deleted '{product.name}' (Category: {product.category or 'General'})"
    )

    db.session.delete(product)
    db.session.commit()



    return redirect(
        url_for("products")
    )



# ---------------- DASHBOARD ----------------
from datetime import datetime, date, timezone
from sqlalchemy import func, extract
from flask_login import login_required
from flask import render_template, request
@app.route("/dashboard")
@login_required
@permission_required("dashboard")
def dashboard():

    # Bangladesh timezone correction
    
    bd_now = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)
    today = bd_now.date()

    # ===================== FILTER SETUP =====================

    filter_type = request.args.get("filter")

    from_date = request.args.get("from_date")
    to_date = request.args.get("to_date")


    invoice_query = Invoice.query.filter(
    Invoice.shop_id == current_user.shop_id
)



    # ===================== DATE FILTER =====================

    if from_date and to_date:

        from_dt = datetime.strptime(
            from_date,
            "%Y-%m-%d"
        )

        to_dt = datetime.strptime(
            to_date,
            "%Y-%m-%d"
        )

        to_dt = to_dt.replace(
            hour=23,
            minute=59,
            second=59
        )


        #query = query.filter(
            #Invoice.created_at.between(
             #   from_dt,
              #  to_dt
           # )
       # )


    # ===================== SALES BASE QUERY =====================

    
# ===================== DATE FILTER =====================

    if from_date and to_date:

        invoice_query = invoice_query.filter(
            Invoice.created_at.between(from_dt, to_dt)
        )

    elif filter_type == "today":

        bd_start = datetime.combine(today, datetime.min.time())
        bd_end = bd_start + timedelta(days=1)

        invoice_query = invoice_query.filter(
            Invoice.created_at >= bd_start,
            Invoice.created_at < bd_end
    )

    elif filter_type == "month":

        invoice_query = invoice_query.filter(
            extract('month', Invoice.created_at) == today.month,
            extract('year', Invoice.created_at) == today.year
    )

    elif filter_type == "year":

        invoice_query = invoice_query.filter(
            extract('year', Invoice.created_at) == today.year
    )

# ===================== FINAL INVOICES =====================

    invoices = invoice_query.all()


    # all time হলে কোনো filter হবে না


    # FILTERED SALES ITEMS

    #items = InvoiceItem.query.join(Invoice).filter(
      #  Invoice.shop_id == current_user.shop_id,
     #   Invoice.created_at >= bd_start if filter_type == "today" else True
   # ).all()
   
    items = InvoiceItem.query.filter(
        InvoiceItem.invoice_id.in_([inv.id for inv in invoices])
    ).all()



    # ===================== STOCK =====================


    products = Product.query.filter_by(
    shop_id=current_user.shop_id
    ).all()


    customers = Customer.query.filter_by(
        shop_id=current_user.shop_id
    ).all()


    stock_value = sum(
        float(p.buy_price) * float(p.stock)
        for p in products
    )


    query = Invoice.query.filter(
        Invoice.shop_id == current_user.shop_id
    )

    # ===================== SALES =====================


    #total_sales = len(
       # set(
        #    i.id
           # for i in items)
        
    total_sales = len(invoices)

    total_sales_amount = sum(
        float(inv.total_amount or 0)
        for inv in invoices
    )

    total_due_amount = sum(
        float(inv.due_amount or 0)
        for inv in invoices
    )

    avg_order_value = (
        total_sales_amount / total_sales
        if total_sales > 0 else 0
    )



    # ===================== TODAY SALES =====================

    # ===================== TODAY SALES AMOUNT (BANGLADESH TIME) =====================

# Bangladesh timezone (UTC +6) অনুযায়ী আজকের শুরু এবং শেষ সময়
# Bangladesh local date অনুযায়ী আজকের শুরু এবং শেষ সময়

    bd_start = datetime.combine(
    today,
    datetime.min.time()
)

    bd_end = datetime.combine(
        today + timedelta(days=1),
        datetime.min.time()
    )


    today_sales_amount = sum(
        inv.total_amount or 0
        for inv in invoices
    )



    # ===================== PROFIT =====================


    gross_profit = 0


    for i in items:

        if i.product:

            gross_profit += (

                float(i.price)

                -

                float(i.product.buy_price)

            ) * float(i.quantity)



    total_profit = gross_profit



    # ===================== EXPENSE =====================

    expense_query = Expense.query.filter(
    Expense.shop_id == current_user.shop_id
)

    if from_date and to_date:

        expense_query = expense_query.filter(
            Expense.date.between(from_dt, to_dt)
        )

    elif filter_type == "today":

        start_today = datetime.combine(today, datetime.min.time())
        end_today = start_today + timedelta(days=1)

        expense_query = expense_query.filter(
            Expense.date >= start_today,
            Expense.date < end_today
        )

    elif filter_type == "month":

        expense_query = expense_query.filter(
            extract('month', Expense.date) == today.month,
            extract('year', Expense.date) == today.year
        )

    elif filter_type == "year":

        expense_query = expense_query.filter(
            extract('year', Expense.date) == today.year
        )

    monthly_expense = expense_query.with_entities(
        func.sum(Expense.amount)
    ).scalar() or 0


    # ===================== NET PROFIT =====================


    net_profit = (
        float(gross_profit)
        -
        float(monthly_expense)
    )



    # ===================== LOW STOCK =====================


    # Keep "low" and "out of stock" separate. A zero-stock product must not
    # appear in both dashboard alerts.
    low_stock = Product.query.filter(
        Product.shop_id == current_user.shop_id,
        Product.active == True,
        Product.stock > 0,
        Product.stock <= Product.reorder_level
    ).all()
    out_of_stock = Product.query.filter(
        Product.shop_id == current_user.shop_id,
        Product.active == True,
        Product.stock <= 0
    ).all()



    # ===================== RECENT SALES =====================


    recent_sales = Invoice.query.filter_by(shop_id=current_user.shop_id).order_by(
        Invoice.created_at.desc()
    ).limit(10).all()



    return render_template(
        "dashboard.html",


        # STOCK

        stock_value=stock_value,
        total_stock_value=stock_value,


        # SALES

        total_sales=total_sales,
        total_sales_amount=total_sales_amount,
        today_sales_amount=today_sales_amount,
        total_due_amount=total_due_amount,
        avg_order_value=avg_order_value,


        # PROFIT

        gross_profit=gross_profit,
        total_profit=total_profit,
        monthly_expense=monthly_expense,
        net_profit=net_profit,


        # OTHER

        low_stock=low_stock,
        low_stock_count=len(low_stock),
        out_of_stock=out_of_stock,
        out_of_stock_count=len(out_of_stock),
        customers=customers,
        customer_count=len(customers),

        recent_sales=recent_sales
    )


@app.route("/api/sales-growth")
@login_required
@permission_required("dashboard")
def api_sales_growth():
    """
    Returns daily (last 30 days), monthly (last 12 months), and yearly
    (last 5 years) sales totals for the growth chart on the dashboard,
    plus a growth % comparing the latest completed period to the one
    before it.
    """

    shop_id = current_user.shop_id
    bd_now = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)
    today = bd_now.date()

    # ---------------- DAILY (last 30 days, oldest -> newest) ----------------
    daily_labels = []
    daily_sales = []
    daily_expenses = []

    for i in range(29, -1, -1):
        day = today - timedelta(days=i)
        day_start = datetime.combine(day, datetime.min.time())
        day_end = day_start + timedelta(days=1)

        sales_total = db.session.query(func.coalesce(func.sum(Invoice.total_amount), 0)).filter(
            Invoice.shop_id == shop_id,
            Invoice.created_at >= day_start,
            Invoice.created_at < day_end,
        ).scalar() or 0

        expense_total = db.session.query(func.coalesce(func.sum(Expense.amount), 0)).filter(
            Expense.shop_id == shop_id,
            Expense.date >= day_start,
            Expense.date < day_end,
        ).scalar() or 0

        daily_labels.append(day.strftime("%d %b"))
        daily_sales.append(round(float(sales_total), 2))
        daily_expenses.append(round(float(expense_total), 2))

    # gross profit per day = sum((sell_price - buy_price) * qty) for items in that day
    daily_gross = []
    for i in range(29, -1, -1):
        day = today - timedelta(days=i)
        day_start = datetime.combine(day, datetime.min.time())
        day_end = day_start + timedelta(days=1)
        rows = (
            db.session.query(InvoiceItem.price, InvoiceItem.quantity, Product.buy_price)
            .join(Invoice, InvoiceItem.invoice_id == Invoice.id)
            .join(Product, InvoiceItem.product_id == Product.id)
            .filter(
                Invoice.shop_id == shop_id,
                Invoice.created_at >= day_start,
                Invoice.created_at < day_end,
            )
            .all()
        )
        gp = sum((r.price - r.buy_price) * r.quantity for r in rows)
        daily_gross.append(round(float(gp), 2))
    daily_profits = [round(g - e, 2) for g, e in zip(daily_gross, daily_expenses)]
    daily_current = daily_sales[-1]
    daily_previous = daily_sales[-2] if len(daily_sales) > 1 else 0
    if daily_previous > 0:
        daily_growth = round(((daily_current - daily_previous) / daily_previous) * 100, 1)
    else:
        daily_growth = 100.0 if daily_current > 0 else 0.0

    # ---------------- MONTHLY (last 12 months, oldest -> newest) ----------------
    monthly_labels = []
    monthly_sales = []
    monthly_expenses = []

    for i in range(11, -1, -1):
        year = bd_now.year
        month = bd_now.month - i
        while month <= 0:
            month += 12
            year -= 1

        sales_total = db.session.query(func.coalesce(func.sum(Invoice.total_amount), 0)).filter(
            Invoice.shop_id == shop_id,
            extract('year', Invoice.created_at) == year,
            extract('month', Invoice.created_at) == month,
        ).scalar() or 0

        expense_total = db.session.query(func.coalesce(func.sum(Expense.amount), 0)).filter(
            Expense.shop_id == shop_id,
            extract('year', Expense.date) == year,
            extract('month', Expense.date) == month,
        ).scalar() or 0

        monthly_labels.append(datetime(year, month, 1).strftime("%b %Y"))
        monthly_sales.append(round(float(sales_total), 2))
        monthly_expenses.append(round(float(expense_total), 2))

    monthly_gross = []
    for i in range(11, -1, -1):
        yr = bd_now.year
        mo = bd_now.month - i
        while mo <= 0:
            mo += 12
            yr -= 1
        rows = (
            db.session.query(InvoiceItem.price, InvoiceItem.quantity, Product.buy_price)
            .join(Invoice, InvoiceItem.invoice_id == Invoice.id)
            .join(Product, InvoiceItem.product_id == Product.id)
            .filter(
                Invoice.shop_id == shop_id,
                extract('year', Invoice.created_at) == yr,
                extract('month', Invoice.created_at) == mo,
            )
            .all()
        )
        gp = sum((r.price - r.buy_price) * r.quantity for r in rows)
        monthly_gross.append(round(float(gp), 2))
    monthly_profits = [round(g - e, 2) for g, e in zip(monthly_gross, monthly_expenses)]
    monthly_current = monthly_sales[-1]
    monthly_previous = monthly_sales[-2] if len(monthly_sales) > 1 else 0
    if monthly_previous > 0:
        monthly_growth = round(((monthly_current - monthly_previous) / monthly_previous) * 100, 1)
    else:
        monthly_growth = 100.0 if monthly_current > 0 else 0.0

    # ---------------- YEARLY (last 5 years, oldest -> newest) ----------------
    yearly_labels = []
    yearly_sales = []
    yearly_expenses = []

    for i in range(4, -1, -1):
        year = bd_now.year - i

        sales_total = db.session.query(func.coalesce(func.sum(Invoice.total_amount), 0)).filter(
            Invoice.shop_id == shop_id,
            extract('year', Invoice.created_at) == year,
        ).scalar() or 0

        expense_total = db.session.query(func.coalesce(func.sum(Expense.amount), 0)).filter(
            Expense.shop_id == shop_id,
            extract('year', Expense.date) == year,
        ).scalar() or 0

        yearly_labels.append(str(year))
        yearly_sales.append(round(float(sales_total), 2))
        yearly_expenses.append(round(float(expense_total), 2))

    yearly_gross = []
    for i in range(4, -1, -1):
        yr = bd_now.year - i
        rows = (
            db.session.query(InvoiceItem.price, InvoiceItem.quantity, Product.buy_price)
            .join(Invoice, InvoiceItem.invoice_id == Invoice.id)
            .join(Product, InvoiceItem.product_id == Product.id)
            .filter(
                Invoice.shop_id == shop_id,
                extract('year', Invoice.created_at) == yr,
            )
            .all()
        )
        gp = sum((r.price - r.buy_price) * r.quantity for r in rows)
        yearly_gross.append(round(float(gp), 2))
    yearly_profits = [round(g - e, 2) for g, e in zip(yearly_gross, yearly_expenses)]
    yearly_current = yearly_sales[-1]
    yearly_previous = yearly_sales[-2] if len(yearly_sales) > 1 else 0
    if yearly_previous > 0:
        yearly_growth = round(((yearly_current - yearly_previous) / yearly_previous) * 100, 1)
    else:
        yearly_growth = 100.0 if yearly_current > 0 else 0.0

    # ---------------- CATEGORY & PAYMENT BREAKDOWNS (for Pie / Donut Chart) ----------------
    cat_rows = (
        db.session.query(
            func.coalesce(Product.category, 'General'),
            func.coalesce(func.sum(InvoiceItem.total), 0)
        )
        .join(InvoiceItem, InvoiceItem.product_id == Product.id)
        .join(Invoice, InvoiceItem.invoice_id == Invoice.id)
        .filter(Invoice.shop_id == shop_id)
        .group_by(Product.category)
        .order_by(func.sum(InvoiceItem.total).desc())
        .limit(6)
        .all()
    )
    category_labels = [r[0] if r[0] else 'General' for r in cat_rows]
    category_values = [round(float(r[1]), 2) for r in cat_rows]
    if not category_labels:
        total_inv_sales = db.session.query(func.coalesce(func.sum(Invoice.total_amount), 0)).filter(Invoice.shop_id == shop_id).scalar() or 0
        category_labels = ["General Sales"]
        category_values = [round(float(total_inv_sales), 2)]

    pay_rows = (
        db.session.query(
            func.coalesce(Invoice.payment_method, 'Cash'),
            func.coalesce(func.sum(Invoice.paid_amount), 0)
        )
        .filter(Invoice.shop_id == shop_id)
        .group_by(Invoice.payment_method)
        .all()
    )
    payment_labels = [r[0] if r[0] else 'Cash' for r in pay_rows]
    payment_values = [round(float(r[1]), 2) for r in pay_rows]
    if not payment_labels:
        payment_labels = ["Cash"]
        payment_values = [0.0]

    return jsonify({
        "daily": {
            "labels": daily_labels,
            "sales": daily_sales,
            "expenses": daily_expenses,
            "profits": daily_profits,
            "values": daily_sales,  # backward compat
            "current": daily_current,
            "previous": daily_previous,
            "growth_percent": daily_growth,
        },
        "monthly": {
            "labels": monthly_labels,
            "sales": monthly_sales,
            "expenses": monthly_expenses,
            "profits": monthly_profits,
            "values": monthly_sales,  # backward compat
            "current": monthly_current,
            "previous": monthly_previous,
            "growth_percent": monthly_growth,
        },
        "yearly": {
            "labels": yearly_labels,
            "sales": yearly_sales,
            "expenses": yearly_expenses,
            "profits": yearly_profits,
            "values": yearly_sales,  # backward compat
            "current": yearly_current,
            "previous": yearly_previous,
            "growth_percent": yearly_growth,
        },
        "categories": {
            "labels": category_labels,
            "values": category_values,
        },
        "payments": {
            "labels": payment_labels,
            "values": payment_values,
        }
    })

    
# ---------------- CUSTOMERS ----------------


@app.route("/customers")
@login_required
@permission_required("customers")
def customers():

    all_customers = Customer.query.filter_by(
        shop_id=current_user.shop_id
    ).order_by(Customer.id.desc()).all()

    for c in all_customers:
        c.total_due = db.session.query(
            func.coalesce(func.sum(Invoice.due_amount), 0)
        ).filter(
            Invoice.customer_id == c.id,
            Invoice.shop_id == current_user.shop_id
        ).scalar() or 0

    total_due_all = sum(c.total_due for c in all_customers)

    return render_template(
        "customers.html",
        customers=all_customers,
        total_due_all=total_due_all
    )



@app.route("/add-customer", methods=["POST"])
@login_required
@permission_required("customers")
def add_customer():
    name = request.form.get("name", "").strip()
    if not name:
        flash("গ্রাহকের নাম প্রদান করা আবশ্যক।", "danger")
        return redirect(url_for("customers"))

    try:
        customer = Customer(
            shop_id=get_current_shop(),
            name=name,
            phone=request.form.get("phone", "").strip(),
            address=request.form.get("address", "").strip()
        )

        db.session.add(customer)
        log_action(current_user.shop_id, current_user.id, "Add Customer", f"Customer '{customer.name}' ({customer.phone or 'No phone'})")
        db.session.commit()
        flash(f"গ্রাহক '{customer.name}' সফলভাবে যোগ করা হয়েছে।", "success")
    except Exception as e:
        db.session.rollback()
        print(f"[ADD CUSTOMER ERROR] {e}")
        flash("গ্রাহক যোগ করতে সমস্যা হয়েছে। অনুগ্রহ করে আবার চেষ্টা করুন।", "danger")

    return redirect(url_for("customers"))


@app.route("/quick-add-customer", methods=["POST"])
@login_required
@permission_required("sell")
def quick_add_customer():
    name = request.form.get("name", "").strip()
    phone = request.form.get("phone", "").strip()
    address = request.form.get("address", "").strip()

    if not name:
        return jsonify({"success": False, "message": "গ্রাহকের নাম আবশ্যক"}), 400

    try:
        shop_id = get_current_shop()
        customer = Customer(
            shop_id=shop_id,
            name=name,
            phone=phone,
            address=address
        )
        db.session.add(customer)
        log_action(shop_id, current_user.id, "Quick Add Customer", f"Customer '{customer.name}' ({customer.phone or 'No phone'})")
        db.session.commit()
        trigger_cloud_sync_async(shop_id)

        return jsonify({
            "success": True,
            "id": customer.id,
            "name": customer.name,
            "phone": customer.phone or "",
            "customer": {
                "id": customer.id,
                "name": customer.name,
                "phone": customer.phone or "",
                "loyalty_points": getattr(customer, 'loyalty_points', 0) or 0,
                "advance_balance": getattr(customer, 'advance_balance', 0) or 0
            }
        })
    except Exception as e:
        db.session.rollback()
        print(f"[QUICK ADD CUSTOMER ERROR] {e}")
        return jsonify({"success": False, "message": "গ্রাহক যোগ করতে সমস্যা হয়েছে"}), 500




@app.route("/delete-customer/<int:id>", methods=["POST"])
@login_required
@permission_required("customers")
def delete_customer(id):

    customer = Customer.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    log_action(current_user.shop_id, current_user.id, "Delete Customer", f"Deleted customer '{customer.name}' ({customer.phone or 'No phone'})")
    db.session.delete(customer)
    db.session.commit()

    return redirect(
        url_for("customers")
    )




# ---------------- SEARCH PRODUCT ----------------


@app.route("/search-product")
@login_required
@permission_required(["sell", "products", "stock"])
def search_product():

    keyword = request.args.get("q", "")

    products = Product.query.filter(
        
        Product.shop_id == current_user.shop_id,

        Product.active == True,

        db.or_(
            Product.name.ilike(f"%{keyword}%"),
            Product.barcode.ilike(f"%{keyword}%"),
            Product.product_code.ilike(f"%{keyword}%")
        )

    ).all()

    result = []

    for p in products:
        result.append({
            "id": p.id,
            "name": p.name,
            "price": p.sell_price,
            "stock": p.stock
        })

    return jsonify(result)



# ---------------- SELL PAGE ----------------

@app.route("/sell", methods=["GET", "POST"])
@login_required
@permission_required("sell")
def sell():

    products = Product.query.filter_by(
    shop_id=current_user.shop_id
    ).all()


    customers = Customer.query.filter_by(
        shop_id=current_user.shop_id
    ).all()

    if request.method == "POST":

        product_id = safe_int(request.form.get("product_id"))
        quantity = safe_float(request.form.get("quantity"), 0.0)
        customer_id = request.form.get("customer_id")

        product = Product.query.filter_by(
            id=product_id, shop_id=current_user.shop_id, active=True
        ).first()

        if not product:
            flash("Product not found")
            return redirect(url_for("sell"))

        if quantity <= 0 or product.stock < quantity:
            flash("Not enough stock")
            return redirect(url_for("sell"))

        if customer_id and not Customer.query.filter_by(id=customer_id, shop_id=current_user.shop_id).first():
            abort(404)

        price = product.sell_price

        # CREATE INVOICE
        today = datetime.now().strftime("%Y%m%d")

        last = Invoice.query.order_by(Invoice.id.desc()).first()

        if last:
            try:
                new_no = int(last.invoice_no.split("-")[-1]) + 1
            except:
                new_no = 1
        else:
            new_no = 1

        invoice_no = f"INV-{today}-{new_no:04d}"

        invoice = Invoice(

        shop_id=current_user.shop_id,

        invoice_no=invoice_no,
            customer_id=customer_id if customer_id else None,
            total_amount=quantity * price
        )

        db.session.add(invoice)
        db.session.flush()

        item = InvoiceItem(
            invoice_id=invoice.id,
            product_id=product.id,
            quantity=quantity,
            price=price,
            total=quantity * price
        )

        product.stock -= quantity

        db.session.add(item)
        db.session.commit()

        flash("Sale successful")

        return redirect(url_for("sales"))

    return render_template("sell.html", products=products, customers=customers, resume_cart="[]", resume_customer="")



# ---------------- SALES HISTORY ----------------


@app.route("/sales")
@login_required
@permission_required("sales")
def sales():
    keyword = request.args.get("q", "").strip()
    from_date = request.args.get("from_date")
    to_date = request.args.get("to_date")
    status = request.args.get("status", "all").strip().lower()
    page = request.args.get("page", 1, type=int)

    sales_query = Invoice.query.filter_by(shop_id=current_user.shop_id).outerjoin(Customer)

    if keyword:
        sales_query = sales_query.filter(
            db.or_(
                Invoice.invoice_no.ilike(f"%{keyword}%"),
                Customer.name.ilike(f"%{keyword}%"),
                Customer.phone.ilike(f"%{keyword}%")
            )
        )

    if from_date:
        start = datetime.strptime(from_date, "%Y-%m-%d")
        sales_query = sales_query.filter(Invoice.created_at >= start)

    if to_date:
        end = datetime.strptime(to_date, "%Y-%m-%d") + timedelta(days=1)
        sales_query = sales_query.filter(Invoice.created_at < end)

    if status == "paid":
        sales_query = sales_query.filter(db.or_(Invoice.due_amount <= 0, Invoice.due_amount.is_(None)))
    elif status == "due":
        sales_query = sales_query.filter(Invoice.due_amount > 0)

    pagination = sales_query.order_by(Invoice.created_at.desc()).paginate(page=page, per_page=20, error_out=False)
    all_invoices = pagination.items

    # Calculate metrics across all shop invoices
    all_shop_invoices = Invoice.query.filter_by(shop_id=current_user.shop_id).all()
    total_sales_amount = sum(inv.total_amount or 0 for inv in all_shop_invoices)
    total_paid_amount = sum(inv.paid_amount or 0 for inv in all_shop_invoices)
    total_due_amount = sum(inv.due_amount or 0 for inv in all_shop_invoices)

    total_items = sum(len(inv.items) for inv in all_invoices)
    average_invoice = total_sales_amount / len(all_shop_invoices) if all_shop_invoices else 0

    for invoice in all_invoices:
        sold_qty = sum(item.quantity for item in invoice.items)
        returned_qty = db.session.query(func.sum(SalesReturnItem.quantity)).join(SalesReturn).filter(SalesReturn.invoice_id == invoice.id).scalar() or 0
        if returned_qty == 0:
            if invoice.due_amount and invoice.due_amount > 0:
                invoice.return_status = "Due"
            else:
                invoice.return_status = "Paid"
        elif returned_qty >= sold_qty:
            invoice.return_status = "Returned"
        else:
            invoice.return_status = "Partial Return"

    return render_template(
        "sales.html",
        sales=all_invoices,
        keyword=keyword,
        from_date=from_date,
        to_date=to_date,
        status=status,
        total_invoices=len(all_shop_invoices),
        total_sales_amount=total_sales_amount,
        total_paid_amount=total_paid_amount,
        total_due_amount=total_due_amount,
        total_items=total_items,
        pagination=pagination,
        average_invoice=average_invoice
    )


# ---------------- VIEW INVOICE ----------------

#@app.route("/invoice/<int:id>")
#@login_required
#def invoice(id):

    invoice = Invoice.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()

    return render_template(
        "invoice.html",
        invoice=invoice
    )



# ---------------- PURCHASE ----------------

@app.route("/purchase", methods=["GET", "POST"])
@login_required
@permission_required("purchase")
def purchase():

    products = Product.query.filter_by(
        shop_id=current_user.shop_id
    ).all()


    suppliers = Supplier.query.filter_by(
        shop_id=current_user.shop_id,
        active=True
    ).all()



    if request.method == "POST":


        product_id = safe_int(request.form.get("product_id"))
        supplier_id = safe_int(request.form.get("supplier_id")) or None
        quantity = safe_float(request.form.get("quantity"), 0.0)



        buy_price = safe_float(request.form.get("buy_price"), 0.0)
        payment_status = request.form.get("payment_status", "Due")
        paid_amount = safe_float(request.form.get("paid_amount"), 0.0)

        total_amount = quantity * buy_price
        due_amount = total_amount - paid_amount

        # Auto-apply supplier advance balance if any
        if supplier_id and due_amount > 0:
            supp_obj = Supplier.query.filter_by(id=supplier_id, shop_id=current_user.shop_id).first()
            if supp_obj and supp_obj.advance_balance and supp_obj.advance_balance > 0:
                auto_applied = min(due_amount, supp_obj.advance_balance)
                supp_obj.advance_balance -= auto_applied
                paid_amount += auto_applied
                due_amount -= auto_applied
                payment_status = "Paid" if due_amount <= 0 else "Partial"

        product = Product.query.filter_by(
            id=product_id,
            shop_id=current_user.shop_id
        ).first()

        if product:
            old_stock = product.stock or 0
            old_value = old_stock * (product.buy_price or 0)
            new_value = quantity * buy_price
            total_stock = old_stock + quantity

            if total_stock > 0:
                product.buy_price = (old_value + new_value) / total_stock

            product.stock = total_stock

            purchase = Purchase(
                shop_id=current_user.shop_id,
                product_id=product.id,
                supplier_id=supplier_id,
                quantity=quantity,
                buy_price=buy_price,
                payment_status=payment_status,
                paid_amount=paid_amount,
                due_amount=due_amount
            )

            db.session.add(purchase)
            db.session.commit()
            flash("Purchase added successfully", "success")

        return redirect(url_for("purchase"))

    return render_template(
        "purchase.html",
        products=products,
        suppliers=suppliers
    )


#------------Supplier ledger---------------------

@app.route("/supplier-ledger")
@login_required
@permission_required("suppliers")
def supplier_ledger():

    suppliers = Supplier.query.filter_by(
        shop_id=current_user.shop_id
    ).all()

    ledger = []

    for supplier in suppliers:
        total_purchase = db.session.query(
            func.sum(Purchase.quantity * Purchase.buy_price)
        ).filter(Purchase.supplier_id == supplier.id).scalar() or 0

        total_due = db.session.query(
            func.sum(Purchase.due_amount)
        ).filter(Purchase.supplier_id == supplier.id).scalar() or 0

        total_due = max(total_due, 0)
        total_paid = max(total_purchase - total_due, 0)

        ledger.append({
            "supplier": supplier,
            "purchase": total_purchase,
            "paid": total_paid,
            "due": total_due,
            "advance": supplier.advance_balance or 0
        })

    return render_template(
        "supplier_ledger.html",
        ledger=ledger
    )


@app.route("/supplier-payment/<int:supplier_id>", methods=["GET", "POST"])
@login_required
@permission_required("suppliers")
def supplier_payment(supplier_id):

    supplier = Supplier.query.filter_by(
        id=supplier_id, shop_id=current_user.shop_id
    ).first_or_404()

    total_purchase = db.session.query(
        func.sum(Purchase.quantity * Purchase.buy_price)
    ).filter(Purchase.supplier_id == supplier.id).scalar() or 0

    current_due = db.session.query(
        func.sum(Purchase.due_amount)
    ).filter(Purchase.supplier_id == supplier.id).scalar() or 0
    current_due = max(current_due, 0)

    if request.method == "POST":

        try:
            amount = float(request.form.get("amount", "0").strip() or 0)
        except ValueError:
            flash("Enter a valid numeric payment amount.", "danger")
            return redirect(url_for("supplier_payment", supplier_id=supplier.id))

        if amount <= 0:
            flash("Enter a valid payment amount.", "warning")
            return redirect(url_for("supplier_payment", supplier_id=supplier.id))

        payment_method = request.form.get("payment_method", "Cash").strip() or "Cash"
        payment = SupplierPayment(
            shop_id=current_user.shop_id,
            supplier_id=supplier.id,
            amount=amount,
            note=request.form.get("note", ""),
            payment_method=payment_method
        )
        db.session.add(payment)

        # Apply the payment against outstanding purchase dues, oldest first.
        remaining = amount
        open_purchases = Purchase.query.filter(
            Purchase.supplier_id == supplier.id,
            Purchase.due_amount > 0
        ).order_by(Purchase.created_at.asc()).all()

        for p in open_purchases:
            if remaining <= 0:
                break
            applied = min(p.due_amount or 0, remaining)
            p.due_amount = (p.due_amount or 0) - applied
            p.paid_amount = (p.paid_amount or 0) + applied
            p.payment_status = "Paid" if p.due_amount <= 0 else "Partial"
            remaining -= applied

        # Excess payment is automatically saved into supplier's advance balance
        if remaining > 0:
            supplier.advance_balance = (supplier.advance_balance or 0) + remaining

        log_action(current_user.shop_id, current_user.id, "Supplier Payment", f"{supplier.name}: ৳{amount:.2f} ({payment_method})")
        db.session.commit()

        if remaining > 0:
            flash(f"Payment of ৳{amount:.2f} ({payment_method}) recorded for {supplier.name} (৳{remaining:.2f} saved as advance balance).", "success")
        else:
            flash(f"Payment of ৳{amount:.2f} ({payment_method}) recorded for {supplier.name}.", "success")
        return redirect(url_for("supplier_ledger"))

    payments = SupplierPayment.query.filter_by(
        supplier_id=supplier.id
    ).order_by(SupplierPayment.created_at.desc()).all()

    return render_template(
        "supplier_payment.html",
        supplier=supplier,
        current_due=current_due,
        payments=payments
    )


# ---------------- PURCHASE HISTORY ----------------

@app.route("/purchase-history")
@login_required
@permission_required("purchase")
def purchase_history():

    keyword = request.args.get(
        "q",
        ""
    ).strip()


    from_date = request.args.get(
        "from_date"
    )


    to_date = request.args.get(
        "to_date"
    )


    page = request.args.get(
        "page",
        1,
        type=int
    )



    purchase_query = Purchase.query.filter_by(
        shop_id=current_user.shop_id
    ).join(
        Product
    ).outerjoin(
        Supplier
    )



    if keyword:


        purchase_query = purchase_query.filter(

            db.or_(

                Product.name.ilike(
                    f"%{keyword}%"
                ),

                Supplier.name.ilike(
                    f"%{keyword}%"
                )

            )

        )



    if from_date:


        start = datetime.strptime(
            from_date,
            "%Y-%m-%d"
        )


        purchase_query = purchase_query.filter(
            Purchase.created_at >= start
        )



    if to_date:


        end = datetime.strptime(
            to_date,
            "%Y-%m-%d"
        ) + timedelta(days=1)


        purchase_query = purchase_query.filter(
            Purchase.created_at < end
        )



    pagination = purchase_query.order_by(

        Purchase.id.desc()

    ).paginate(

        page=page,

        per_page=20,

        error_out=False

    )



    purchases = pagination.items



    total_purchase_amount = sum(

        (p.quantity * p.buy_price)

        for p in purchases

    )



    total_quantity = sum(

        p.quantity

        for p in purchases

    )



    total_records = pagination.total



    average_purchase = (

        total_purchase_amount / total_records

        if total_records > 0

        else 0

    )



    return render_template(

        "purchase_history.html",

        purchases=purchases,

        pagination=pagination,

        keyword=keyword,

        from_date=from_date,

        to_date=to_date,

        total_purchase_amount=total_purchase_amount,

        total_quantity=total_quantity,

        total_records=total_records,

        average_purchase=average_purchase

    )
    
#--------------Purchase View-------------------

@app.route("/purchase-view/<int:id>")
@login_required
@permission_required("purchase")
def purchase_view(id):

    purchase = Purchase.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()


    total_amount = (
        purchase.quantity *
        purchase.buy_price
    )


    return render_template(

        "purchase_view.html",

        purchase=purchase,

        total_amount=total_amount

    )
    
#-----------------Edit Purchase-----------------

@app.route("/edit-purchase/<int:id>", methods=["GET", "POST"])
@login_required
@permission_required("purchase")
def edit_purchase(id):

    purchase = Purchase.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()


    products = Product.query.filter_by(
        shop_id=current_user.shop_id
    ).all()


    suppliers = Supplier.query.filter_by(
        shop_id=current_user.shop_id,
        active=True
    ).all()



    if request.method == "POST":


        new_product_id = int(
            request.form["product_id"]
        )


        new_supplier_id = request.form.get(
            "supplier_id"
        )


        if new_supplier_id:
            new_supplier_id = int(
                new_supplier_id
            )

        else:
            new_supplier_id = None



        new_quantity = float(
            request.form["quantity"]
        )


        new_buy_price = float(
            request.form["buy_price"]
        )



        old_product = Product.query.get(
            purchase.product_id
        )



        # Remove old stock

        if old_product:

            old_product.stock -= purchase.quantity



        new_product = Product.query.get(
            new_product_id
        )



        if new_product:

            new_product.stock += new_quantity



            new_product.buy_price = (
                new_buy_price
            )



        purchase.product_id = new_product_id

        purchase.supplier_id = new_supplier_id

        purchase.quantity = new_quantity

        purchase.buy_price = new_buy_price



        db.session.commit()



        flash(
            "Purchase updated successfully",
            "success"
        )


        return redirect(
            url_for(
                "purchase_history"
            )
        )




    return render_template(

        "edit_purchase.html",

        purchase=purchase,

        products=products,

        suppliers=suppliers

    )
    
#------------------Delete Purchase------------

@app.route("/delete-purchase/<int:id>", methods=["POST"])
@login_required
@permission_required("purchase")
def delete_purchase(id):

    purchase = Purchase.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()



    product = Product.query.get(
        purchase.product_id
    )



    if product:

        product.stock -= purchase.quantity



    db.session.delete(
        purchase
    )


    db.session.commit()



    flash(
        "Purchase deleted successfully",
        "success"
    )


    return redirect(
        url_for(
            "purchase_history"
        )
    )


# ---------------- DAILY REPORT ----------------

from datetime import datetime, timedelta, timezone

@app.route("/report/daily")
@app.route("/daily-report")
@login_required
@permission_required("reports")
def daily_report():

    # Bangladesh time adjust (UTC +6)
    now = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)
    today = now.date()

    # Optional date filter (?date=YYYY-MM-DD), defaults to today
    date_str = request.args.get("date", "").strip()
    try:
        selected_date = datetime.strptime(date_str, "%Y-%m-%d").date() if date_str else today
    except ValueError:
        selected_date = today

    sales = Invoice.query.filter_by(shop_id=current_user.shop_id).all()

    total_sales_amount = 0
    total_profit = 0

    filtered_sales = []

    for invoice in sales:

        if not invoice.created_at:
            continue

        # created_at is already stored in Bangladesh time (see bangladesh_time() in models.py)
        inv_time = invoice.created_at

        if inv_time.date() != selected_date:
            continue

        filtered_sales.append(invoice)

        total_sales_amount += invoice.total_amount or 0

        for item in invoice.items:
            product = item.product
            if product:
                total_profit += (item.price - product.buy_price) * item.quantity

    today_expense = 0
    cash_expense = 0
    bkash_expense = 0
    nagad_expense = 0
    card_expense = 0

    for exp in Expense.query.filter_by(shop_id=current_user.shop_id).all():
        exp_time = (exp.date or now) + timedelta(hours=0)
        if exp_time.date() == selected_date:
            amt = exp.amount or 0
            today_expense += amt
            exp_method = (getattr(exp, "payment_method", None) or "Cash").strip().lower()
            if "bkash" in exp_method:
                bkash_expense += amt
            elif "nagad" in exp_method:
                nagad_expense += amt
            elif "card" in exp_method or "bank" in exp_method:
                card_expense += amt
            else:
                cash_expense += amt

    net_profit = total_profit - today_expense

    # Cash Register & Payment Method Settlement
    cash_sales = 0
    bkash_sales = 0
    nagad_sales = 0
    card_sales = 0
    due_sales = 0

    for invoice in filtered_sales:
        due_sales += (invoice.due_amount or 0)
        method = (invoice.payment_method or "Cash").strip().lower()
        paid = invoice.paid_amount or 0
        if "bkash" in method or "rocket" in method:
            bkash_sales += paid
        elif "nagad" in method:
            nagad_sales += paid
        elif "card" in method or "bank" in method:
            card_sales += paid
        else:
            cash_sales += paid

    # Include CustomerPayment due collections for selected_date
    due_payments = CustomerPayment.query.filter_by(shop_id=current_user.shop_id).all()
    for pay in due_payments:
        pay_date = pay.created_at.date() if pay.created_at else None
        if pay_date == selected_date:
            amt = pay.amount or 0
            m = (pay.method or "Cash").strip().lower()
            if "bkash" in m or "rocket" in m:
                bkash_sales += amt
            elif "nagad" in m:
                nagad_sales += amt
            elif "card" in m or "bank" in m:
                card_sales += amt
            else:
                cash_sales += amt

    net_cash_in_hand = max(0, cash_sales - cash_expense)
    net_bkash_in_hand = max(0, bkash_sales - bkash_expense)
    net_nagad_in_hand = max(0, nagad_sales - nagad_expense)
    net_card_in_hand = max(0, card_sales - card_expense)

    return render_template(
        "daily_report.html",
        sales=filtered_sales,
        total_sales_amount=total_sales_amount,
        total_profit=total_profit,
        today_expense=today_expense,
        cash_expense=cash_expense,
        bkash_expense=bkash_expense,
        nagad_expense=nagad_expense,
        card_expense=card_expense,
        net_profit=net_profit,
        cash_sales=cash_sales,
        bkash_sales=bkash_sales,
        nagad_sales=nagad_sales,
        card_sales=card_sales,
        due_sales=due_sales,
        net_cash_in_hand=net_cash_in_hand,
        net_bkash_in_hand=net_bkash_in_hand,
        net_nagad_in_hand=net_nagad_in_hand,
        net_card_in_hand=net_card_in_hand,
        timedelta=timedelta,
        selected_date=selected_date,
        today=today
    )
  
  #----------------------------Montly Report-------------------

from sqlalchemy import extract
from datetime import datetime, timedelta, timezone


@app.route("/monthly-report")
@login_required
@permission_required("reports")
def monthly_report():


    # Bangladesh Time
    now = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)

    current_month = now.month
    current_year = now.year

    # Optional month filter (?month=YYYY-MM from <input type="month">), defaults to current month
    month_str = request.args.get("month", "").strip()
    selected_month, selected_year = current_month, current_year
    if month_str:
        try:
            y_str, m_str = month_str.split("-")
            selected_year = int(y_str)
            selected_month = int(m_str)
        except (ValueError, AttributeError):
            selected_month, selected_year = current_month, current_year

    period_label = datetime(selected_year, selected_month, 1).strftime("%B %Y")


    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id).all()


    sales = []

    total_sales_amount = 0
    total_profit = 0



    for invoice in invoices:


        if not invoice.created_at:
            continue


        # created_at is already stored in Bangladesh time (see bangladesh_time() in models.py)
        invoice_time = invoice.created_at


        if (
            invoice_time.month == selected_month
            and invoice_time.year == selected_year
        ):


            sales.append(invoice)


            total_sales_amount += invoice.total_amount or 0



            for item in invoice.items:

                if item.product:

                    total_profit += (
                        item.price - item.product.buy_price
                    ) * item.quantity


    monthly_expense_total = 0
    for exp in Expense.query.filter_by(shop_id=current_user.shop_id).all():
        if not exp.date:
            continue
        exp_time = exp.date  # already stored in Bangladesh time
        if exp_time.month == selected_month and exp_time.year == selected_year:
            monthly_expense_total += exp.amount or 0

    monthly_net_profit = total_profit - monthly_expense_total


    return render_template(

        "monthly_report.html",

        sales=sales,

        total_sales_amount=total_sales_amount,

        total_profit=total_profit,

        monthly_expense_total=monthly_expense_total,

        monthly_net_profit=monthly_net_profit,

        timedelta=timedelta,

        selected_month=selected_month,

        selected_year=selected_year,

        period_label=period_label

    )
    
    
#--------------Customer Sales------------------
from datetime import timedelta, timezone

@app.route("/customer-sales/<int:id>")
@login_required
@permission_required(["customers", "sales"])
def customer_sales(id):

    customer = Customer.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    invoices = Invoice.query.filter_by(
        customer_id=id
    ).order_by(
        Invoice.id.desc()
    ).all()


    total_purchase = sum(
        (invoice.total_amount or 0) 
        for invoice in invoices
    )


    return render_template(
        "customer_sales.html",
        customer=customer,
        invoices=invoices,
        total_purchase=total_purchase
    )


#--------------------Supplier----------------------

@app.route("/suppliers")
@login_required
@permission_required("suppliers")
def suppliers():

    keyword = request.args.get(
        "q",
        ""
    ).strip()

    page = request.args.get(
        "page",
        1,
        type=int
    )

    query = Supplier.query.filter_by(
        shop_id=current_user.shop_id
    )

    if keyword:

        query = query.filter(

            db.or_(

                Supplier.name.ilike(
                    f"%{keyword}%"
                ),

                Supplier.company.ilike(
                    f"%{keyword}%"
                ),

                Supplier.phone.ilike(
                    f"%{keyword}%"
                ),

                Supplier.supplier_code.ilike(
                    f"%{keyword}%"
                )

            )

        )

    pagination = query.order_by(

        Supplier.name.asc()

    ).paginate(

        page=page,

        per_page=15,

        error_out=False

    )

    return render_template(

        "suppliers.html",

        suppliers=pagination.items,

        pagination=pagination,

        keyword=keyword

    )
    
#-------------------Add Supplier-----------------

@app.route(
    "/add-supplier",
    methods=["POST"]
)
@login_required
@permission_required("suppliers")
def add_supplier():

    supplier = Supplier(

        shop_id=current_user.shop_id,

        supplier_code=generate_supplier_code(),

        name=request.form["name"],

        company=request.form.get(
            "company"
        ),

        phone=request.form.get(
            "phone"
        ),

        email=request.form.get(
            "email"
        ),

        address=request.form.get(
            "address"
        ),

        opening_due=float(

            request.form.get(

                "opening_due",

                0

            ) or 0

        ),

        active=True

    )

    db.session.add(

        supplier

    )

    db.session.commit()

    flash(

        "Supplier added successfully.",

        "success"

    )

    return redirect(

        url_for(

            "suppliers"

        )

    )
    
#--------------------Toggle Supplier--------------

@app.route(
    "/toggle-supplier/<int:id>"
)
@login_required
@permission_required("suppliers")
def toggle_supplier(id):

    supplier = Supplier.query.filter_by(

        id=id,

        shop_id=current_user.shop_id

    ).first_or_404()

    supplier.active = (

        not supplier.active

    )

    db.session.commit()

    flash(

        "Supplier status updated.",

        "success"

    )

    return redirect(

        url_for(

            "suppliers"

        )

    )
    
#-----------Edit Supplier-----------------------

@app.route(
    "/edit-supplier/<int:id>",
    methods=["POST"]
)
@login_required
@permission_required("suppliers")
def edit_supplier(id):

    supplier = Supplier.query.filter_by(

        id=id,

        shop_id=current_user.shop_id

    ).first_or_404()

    supplier.name = request.form["name"]

    supplier.company = request.form.get(

        "company"

    )

    supplier.phone = request.form.get(

        "phone"

    )

    supplier.email = request.form.get(

        "email"

    )

    supplier.address = request.form.get(

        "address"

    )

    supplier.opening_due = float(

        request.form.get(

            "opening_due",

            0

        ) or 0

    )

    db.session.commit()

    flash(

        "Supplier updated successfully.",

        "success"

    )

    return redirect(

        url_for(

            "suppliers"

        )

    )
    
#-------------------Delete Supplier---------------

@app.route("/delete-supplier/<int:id>", methods=["GET", "POST"])
@login_required
@permission_required("suppliers")
def delete_supplier(id):

    supplier = Supplier.query.filter_by(

        id=id,

        shop_id=current_user.shop_id

    ).first_or_404()

    if supplier.purchases:

        flash(

            "Supplier has purchase history. Cannot delete.",

            "danger"

        )

        return redirect(

            url_for(

                "suppliers"

            )

        )

    db.session.delete(

        supplier

    )

    db.session.commit()

    flash(

        "Supplier deleted successfully.",

        "success"

    )

    return redirect(

        url_for(

            "suppliers"

        )

    )


# ---------------- CREATE INVOICE ----------------

@app.route("/create-invoice", methods=["POST"])
@login_required
@permission_required("sell")
def create_invoice():
    try:
        raw_cart = (request.form.get("cart_data") or "").strip()
        if not raw_cart:
            flash("কার্ট খালি। অনুগ্রহ করে পণ্য যোগ করুন।" if session.get("lang") == "bn" else "Cart is empty. Please add products first.", "warning")
            return redirect(url_for("sell"))

        try:
            cart_data = json.loads(raw_cart)
        except Exception:
            flash("কার্ট ডাটা সঠিক নয়। পুনরায় চেষ্টা করুন।" if session.get("lang") == "bn" else "Invalid cart data. Please try again.", "warning")
            return redirect(url_for("sell"))

        if not cart_data or not isinstance(cart_data, list) or len(cart_data) == 0:
            flash("কার্ট খালি। অনুগ্রহ করে পণ্য যোগ করুন।" if session.get("lang") == "bn" else "Cart is empty. Please add products first.", "warning")
            return redirect(url_for("sell"))

        customer_id = request.form.get("customer_id", "").strip()
        if customer_id in ("", "None", "null", "undefined"):
            customer_id = None
        else:
            try:
                customer_id = int(customer_id)
            except (TypeError, ValueError):
                customer_id = None

        shop_id = get_current_shop()
        if not shop_id:
            flash("শপ খুঁজে পাওয়া যায়নি।" if session.get("lang") == "bn" else "Shop not found.", "danger")
            return redirect(url_for("sell"))

        if customer_id and not Customer.query.filter_by(id=customer_id, shop_id=shop_id).first():
            customer_id = None

        today = datetime.now().strftime("%Y%m%d")
        last_invoice = Invoice.query.order_by(Invoice.id.desc()).first()

        if last_invoice and last_invoice.invoice_no:
            try:
                last_no = int(last_invoice.invoice_no.split("-")[-1])
                new_no = last_no + 1
            except Exception:
                new_no = (last_invoice.id or 0) + 1
        else:
            new_no = 1

        invoice_no = f"INV-{today}-{new_no:04d}"

        invoice = Invoice(
            shop_id=shop_id,
            invoice_no=invoice_no,
            customer_id=customer_id,
            total_amount=0
        )

        db.session.add(invoice)
        db.session.flush()

        total = 0.0
        valid_items_count = 0

        for item in cart_data:
            try:
                p_id = int(item.get("id", 0))
            except (TypeError, ValueError):
                continue

            product = Product.query.filter_by(
                id=p_id, shop_id=shop_id, active=True
            ).first()

            if not product:
                continue

            try:
                qty = float(item.get("qty", 0))
            except (TypeError, ValueError):
                qty = 0.0

            if qty <= 0:
                continue

            price = float(product.sell_price or 0.0)

            if product.stock is not None and product.stock < qty:
                db.session.rollback()
                flash(f"'{product.name}' পণ্যের পর্যাপ্ত স্টক নেই (বর্তমান স্টক: {product.stock})।" if session.get("lang") == "bn" else f"Not enough stock for {product.name} (Current stock: {product.stock}).", "warning")
                return redirect(url_for("sell"))

            if product.stock is not None:
                product.stock -= qty

            line_total = round(qty * price, 2)
            total += line_total
            valid_items_count += 1

            invoice_item = InvoiceItem(
                invoice_id=invoice.id,
                product_id=product.id,
                quantity=qty,
                price=price,
                total=line_total
            )
            db.session.add(invoice_item)

        if valid_items_count == 0:
            db.session.rollback()
            flash("কার্টে কোনো বৈধ পণ্য পাওয়া যায়নি।" if session.get("lang") == "bn" else "No valid products found in cart.", "warning")
            return redirect(url_for("sell"))

        # -------- Coupon & Manual Discount --------
        subtotal = round(total, 2)
        discount_amount = 0.0
        coupon_code = (request.form.get("coupon_code") or "").strip().upper()

        if coupon_code:
            coupon = Coupon.query.filter_by(shop_id=shop_id, code=coupon_code).first()
            if coupon and coupon.is_valid():
                discount_amount = coupon.calculate_discount(subtotal)
                coupon.used_count = (coupon.used_count or 0) + 1
                invoice.coupon_code = coupon.code
            else:
                flash("কুপন কোডটি অবৈধ বা মেয়াদোত্তীর্ণ।" if session.get("lang") == "bn" else "Coupon code was invalid or expired.", "warning")

        # Manual Discount (Flat ৳ or %)
        manual_discount_raw = (request.form.get("manual_discount") or "0").strip()
        discount_type = request.form.get("discount_type", "flat")
        try:
            manual_discount_val = float(manual_discount_raw) if manual_discount_raw else 0.0
        except ValueError:
            manual_discount_val = 0.0

        if manual_discount_val > 0:
            if discount_type == "percent":
                discount_amount += round(subtotal * (manual_discount_val / 100.0), 2)
            else:
                discount_amount += manual_discount_val

        # -------- Loyalty Points Redemption --------
        customer = Customer.query.filter_by(id=customer_id, shop_id=shop_id).first() if customer_id else None
        use_loyalty = request.form.get("use_loyalty") in ("on", "true", "1")

        if use_loyalty and customer and (customer.loyalty_points or 0) >= 100:
            current_grand = max(subtotal - discount_amount, 0.0)
            pts_available = float(customer.loyalty_points)
            pts_to_redeem = min(pts_available, current_grand)
            customer.loyalty_points -= pts_to_redeem
            discount_amount += pts_to_redeem

        grand_total = max(round(subtotal - discount_amount, 2), 0.0)

        # -------- Payment Method & Paid / Received Amount --------
        payment_method = (request.form.get("payment_method") or "Cash").strip()
        paid_amount_raw = (request.form.get("paid_amount") or "").strip()
        try:
            received_amount = float(paid_amount_raw) if paid_amount_raw else grand_total
        except ValueError:
            received_amount = grand_total

        use_advance = request.form.get("use_advance") in ("on", "true", "1")
        advance_applied = 0.0
        if use_advance and customer and (customer.advance_balance or 0) > 0:
            advance_applied = min(float(customer.advance_balance), grand_total)
            customer.advance_balance -= advance_applied
            payment_method = f"{payment_method} + Advance"

        total_tendered = received_amount + advance_applied
        change_amount = max(round(total_tendered - grand_total, 2), 0.0)
        paid_amount = min(total_tendered, grand_total)
        due_amount = max(round(grand_total - paid_amount, 2), 0.0)

        payment_status = "Paid" if due_amount <= 0 else ("Partial" if paid_amount > 0 else "Due")

        invoice.subtotal = subtotal
        invoice.discount_amount = discount_amount
        invoice.total_amount = grand_total
        invoice.paid_amount = paid_amount
        invoice.due_amount = due_amount
        invoice.received_amount = received_amount
        invoice.change_amount = change_amount
        invoice.payment_status = payment_status
        invoice.payment_method = payment_method

        # -------- Loyalty points: 1 point per 100 Tk spent --------
        if customer:
            customer.loyalty_points = (customer.loyalty_points or 0) + int(grand_total // 100)

        cust_name = customer.name if customer else "Walk-in Customer"
        log_action(
            shop_id,
            current_user.id,
            "Create Invoice",
            f"Invoice #{invoice_no} | Customer: {cust_name} | Total: ৳{grand_total:.2f} | Paid: ৳{paid_amount:.2f} | Method: {payment_method}"
        )

        db.session.commit()
        trigger_cloud_sync_async(shop_id)

        return redirect(url_for("invoice", id=invoice.id))

    except Exception as ex:
        db.session.rollback()
        import traceback
        print("=" * 60)
        print(f"[CREATE INVOICE ERROR] {ex}")
        traceback.print_exc()
        print("=" * 60)
        flash("ইনভয়েস তৈরির সময় সমস্যা হয়েছে। অনুগ্রহ করে পুনরায় চেষ্টা করুন।" if session.get("lang") == "bn" else f"An error occurred while creating the invoice: {str(ex)}", "danger")
        return redirect(url_for("sell"))


#---------Add Expense--------------

# ==========================
# EXPENSE ADD
# ==========================

@app.route("/add-expense", methods=["GET", "POST"])
@login_required
@permission_required("expenses")
def add_expense():

    if request.method == "POST":

        title = request.form.get("title") or request.form.get("category") or "General Expense"
        category = request.form.get("category") or "General"
        amount = safe_float(request.form.get("amount"), 0.0)
        note = request.form.get("note", "")
        payment_method = request.form.get("payment_method", "Cash")

        expense = Expense(
            shop_id=get_current_shop(),
            title=title,
            category=category,
            amount=amount,
            note=note,
            payment_method=payment_method
        )

        db.session.add(expense)

        log_action(
            get_current_shop(),
            current_user.id,
            "Add Expense",
            f"Title: {title} | Amount: ৳{amount:.2f} | Method: {payment_method} | Category: {category}"
        )

        db.session.commit()


        flash("Expense added successfully!", "success")

        return redirect(url_for("expenses"))


    return render_template(
        "add_expense.html"
    )
    
    
#---------------Expense------------------

# ==========================
# EXPENSE LIST
# ==========================

@app.route("/expenses")
@login_required
@permission_required("expenses")
def expenses():

    expense_list = Expense.query.order_by(
        Expense.date.desc()
    ).all()


    return render_template(
        "expenses.html",
        expenses=expense_list
    )

#----------------Shop Settings--------

# ---------------- SHOP SETTINGS ----------------

@app.route("/shop-settings", methods=["GET", "POST"])
@login_required
@permission_required("all")
def shop_settings():

    shop = Shop.query.get(get_current_shop())

    if request.method == "POST":

        shop.shop_name = request.form["shop_name"]
        shop.owner_name = request.form["owner_name"]
        shop.phone = request.form["phone"]
        shop.address = request.form["address"]

        db.session.commit()

        flash("Shop information updated successfully.", "success")

        return redirect(url_for("shop_settings"))

    return render_template(
        "shop_settings.html",
        shop=shop
    )

#-------------Invoice--------------------

# ---------------- VIEW INVOICE ----------------

@app.route("/invoice/<int:id>")
@login_required
@permission_required(["sales", "sell"])
def invoice(id):

    invoice = Invoice.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()

    shop = Shop.query.get(
        current_user.shop_id
    )

    return render_template(
        "invoice.html",
        invoice=invoice,
        shop=shop
    )
#-------------------Generate Return-------------

def generate_return_no():
    today = datetime.now().strftime("%Y%m%d")
    return f"RTN-{today}-{uuid.uuid4().hex[:6].upper()}"



#--------------Invoice return item-----------
@app.route("/return/<int:id>", methods=["GET", "POST"])
@login_required
@permission_required("returns")
def return_invoice(id):

    invoice = Invoice.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()

    if request.method == "POST":

        reason = request.form.get(
            "reason",
            ""
        )

        # প্রথমে check করি আসলেই কিছু Return করা হচ্ছে কিনা
        has_return = False

        for item in invoice.items:

            qty_text = request.form.get(
                f"return_qty_{item.id}",
                ""
            ).strip()

            if qty_text != "" and float(qty_text) > 0:
                has_return = True
                break

        if not has_return:

            flash(
                "Please enter at least one return quantity.",
                "warning"
            )

            return redirect(
                url_for(
                    "return_invoice",
                    id=id
                )
            )

        # এখন Return Header তৈরি হবে
        sales_return = SalesReturn(
            shop_id=current_user.shop_id,
            invoice_id=invoice.id,
            return_no=generate_return_no(),
            reason=reason,
            total_return=0
        )

        db.session.add(sales_return)
        db.session.flush()

        total_return = 0

        for item in invoice.items:

            qty_text = request.form.get(
                f"return_qty_{item.id}",
                ""
            ).strip()

            if qty_text == "":
                continue

            qty = float(qty_text)

            if qty <= 0:
                continue

            # আগে কত Return হয়েছে
            returned_qty = db.session.query(
                func.sum(
                    SalesReturnItem.quantity
                )
            ).join(
                SalesReturn
            ).filter(
                SalesReturn.invoice_id == invoice.id,
                SalesReturnItem.product_id == item.product_id
            ).scalar() or 0

            available_qty = item.quantity - returned_qty

            if qty > available_qty:

                flash(
                    f"{item.product.name} এর সর্বোচ্চ {available_qty} Return করা যাবে।",
                    "danger"
                )

                db.session.rollback()

                return redirect(
                    url_for(
                        "return_invoice",
                        id=id
                    )
                )

            return_item = SalesReturnItem(

                return_id=sales_return.id,

                product_id=item.product_id,

                quantity=qty,

                price=item.price,

                total=qty * item.price

            )

            db.session.add(return_item)

            # Stock ফেরত
            if item.product:

                item.product.stock += qty

            total_return += qty * item.price

        sales_return.total_return = total_return

        # Adjust customer financial ledger
        if total_return > 0:
            if invoice.due_amount > 0:
                if total_return <= invoice.due_amount:
                    invoice.due_amount -= total_return
                else:
                    excess = total_return - invoice.due_amount
                    invoice.due_amount = 0
                    if invoice.customer:
                        invoice.customer.advance_balance = (invoice.customer.advance_balance or 0) + excess
            else:
                if invoice.customer:
                    invoice.customer.advance_balance = (invoice.customer.advance_balance or 0) + total_return

            # Recalculate payment_status
            if invoice.due_amount <= 0:
                invoice.payment_status = "Paid"
            elif invoice.due_amount < invoice.total_amount:
                invoice.payment_status = "Partial"
            else:
                invoice.payment_status = "Unpaid"

        db.session.commit()

        flash(
            "Product returned successfully.",
            "success"
        )

        return redirect(
            url_for("sales")
        )

    return render_template(
        "return_invoice.html",
        invoice=invoice
    )
#-----------------Invoice PDF------------
@app.route("/invoice/<int:id>/pdf")
@login_required
@permission_required(["sales", "sell"])
def invoice_pdf(id):

    invoice = Invoice.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    shop = Shop.query.get(invoice.shop_id)

    buffer = BytesIO()

    # =====================================================
    # 80MM THERMAL RECEIPT
    # =====================================================

    PAGE_WIDTH = 80 * mm

    # Compact height based on number of products
    item_count = len(invoice.items)

    # Short roll-friendly receipt: leaves only the space the items need.
    PAGE_HEIGHT = 68 * mm + (item_count * 5.5 * mm)

    pdf = canvas.Canvas(
        buffer,
        pagesize=(PAGE_WIDTH, PAGE_HEIGHT)
    )

    pdf.setTitle(invoice.invoice_no)

    # =====================================================
    # BASIC SETTINGS
    # =====================================================

    LEFT = 4 * mm
    RIGHT = PAGE_WIDTH - 4 * mm
    CENTER = PAGE_WIDTH / 2

    y = PAGE_HEIGHT - 6 * mm

    # =====================================================
    # SHOP HEADER
    # =====================================================

    pdf.setFont(
        "Helvetica-Bold",
        13
    )

    pdf.drawCentredString(
        CENTER,
        y,
        shop.shop_name or ""
    )

    y -= 5 * mm

    pdf.setFont(
        "Helvetica",
        8
    )

    if shop and shop.address and str(shop.address).strip() not in ("None", ""):
        pdf.drawCentredString(
            CENTER,
            y,
            str(shop.address)
        )
        y -= 3.5 * mm

    if shop and shop.phone and str(shop.phone).strip() not in ("None", ""):
        pdf.drawCentredString(
            CENTER,
            y,
            f"Phone: {shop.phone}"
        )
        y -= 4 * mm

    # Header separator
    pdf.line(
        LEFT,
        y,
        RIGHT,
        y
    )

    y -= 5 * mm

    # =====================================================
    # INVOICE TITLE
    # =====================================================

    pdf.setFont(
        "Helvetica-Bold",
        11
    )

    pdf.drawCentredString(
        CENTER,
        y,
        "INVOICE"
    )

    y -= 4 * mm

    pdf.setFont(
        "Helvetica",
        8
    )

    pdf.drawCentredString(
        CENTER,
        y,
        f"{invoice.invoice_no} | "
        f"{invoice.created_at.strftime('%d-%m-%Y %I:%M %p')}"
    )

    y -= 4 * mm

    # =====================================================
    # CUSTOMER
    # =====================================================

    if invoice.customer:

        pdf.setFont(
            "Helvetica-Bold",
            8
        )

        pdf.drawString(
            LEFT,
            y,
            "Customer:"
        )

        pdf.setFont(
            "Helvetica",
            8
        )

        customer_name = invoice.customer.name or ""

        pdf.drawString(
            LEFT + 17 * mm,
            y,
            customer_name[:28]
        )

        y -= 3.5 * mm

        if invoice.customer.phone:

            pdf.drawString(
                LEFT,
                y,
                f"Phone: {invoice.customer.phone}"
            )

            y -= 3.5 * mm

    else:

        pdf.setFont(
            "Helvetica",
            8
        )

        pdf.drawString(
            LEFT,
            y,
            "Customer: Walk-in Customer"
        )

        y -= 3.5 * mm

    # Customer separator
    pdf.line(
        LEFT,
        y,
        RIGHT,
        y
    )

    y -= 4 * mm

    # =====================================================
    # PRODUCT TABLE HEADER
    # =====================================================

    pdf.setFont(
        "Helvetica-Bold",
        7.5
    )

    # Column positions
    product_x = LEFT
    qty_x = 47 * mm
    price_x = 61 * mm
    total_x = RIGHT

    pdf.drawString(
        product_x,
        y,
        "Product"
    )

    pdf.drawRightString(
        qty_x,
        y,
        "Qty"
    )

    pdf.drawRightString(
        price_x,
        y,
        "Price"
    )

    pdf.drawRightString(
        total_x,
        y,
        "Total"
    )

    y -= 2.5 * mm

    pdf.line(
        LEFT,
        y,
        RIGHT,
        y
    )

    y -= 4 * mm

    # =====================================================
    # PRODUCTS
    # =====================================================

    pdf.setFont(
        "Helvetica",
        7.5
    )

    for item in invoice.items:

        if item.product:
            product_name = item.product.name or "Deleted Product"
        else:
            product_name = "Deleted Product"

        # Keep product name compact
        product_name = product_name[:25]

        # Product
        pdf.drawString(
            product_x,
            y,
            product_name
        )

        # Quantity
        pdf.drawRightString(
            qty_x,
            y,
            f"{item.quantity:g}"
        )

        # Price
        pdf.drawRightString(
            price_x,
            y,
            f"{item.price:.2f}"
        )

        # Total
        pdf.drawRightString(
            total_x,
            y,
            f"{item.total:.2f}"
        )

        # Small vertical spacing
        y -= 3.5 * mm

    # =====================================================
    # PRODUCT / SUMMARY SEPARATOR
    # =====================================================

    y -= 1 * mm

    pdf.line(
        LEFT,
        y,
        RIGHT,
        y
    )

    y -= 3.5 * mm

    # =====================================================
    # SUMMARY - COMPACT SIDE BY SIDE
    # =====================================================

    pdf.setFont(
        "Helvetica",
        8
    )

    subtotal = invoice.subtotal or invoice.total_amount

    # -----------------------------------------------------
    # Subtotal
    # -----------------------------------------------------

    pdf.drawString(
        LEFT,
        y,
        "Subtotal:"
    )

    pdf.drawRightString(
        total_x,
        y,
        f"Tk {subtotal:.2f}"
    )

    y -= 3.5 * mm

    # -----------------------------------------------------
    # Discount
    # -----------------------------------------------------

    if invoice.discount_amount:

        discount_text = "Discount"

        if invoice.coupon_code:
            discount_text += f" ({invoice.coupon_code})"

        pdf.drawString(
            LEFT,
            y,
            discount_text + ":"
        )

        pdf.drawRightString(
            total_x,
            y,
            f"- Tk {invoice.discount_amount:.2f}"
        )

        y -= 3.5 * mm

    # =====================================================
    # GRAND TOTAL
    # =====================================================

    pdf.setFont(
        "Helvetica-Bold",
        10
    )

    pdf.drawString(
        LEFT,
        y,
        "Grand Total:"
    )

    pdf.drawRightString(
        total_x,
        y,
        f"Tk {invoice.total_amount:.2f}"
    )

    y -= 4 * mm

    # =====================================================
    # RECEIVED & CHANGE RETURN & PAID
    # =====================================================

    rec_amt = getattr(invoice, 'received_amount', 0) or 0
    chg_amt = getattr(invoice, 'change_amount', 0) or 0

    if rec_amt > 0:
        pdf.setFont("Helvetica", 8)
        pdf.drawString(LEFT, y, "Received:")
        pdf.drawRightString(total_x, y, f"Tk {rec_amt:.2f}")
        y -= 3.5 * mm

        if chg_amt > 0:
            pdf.setFont("Helvetica-Bold", 8)
            pdf.drawString(LEFT, y, "Change Return:")
            pdf.drawRightString(total_x, y, f"Tk {chg_amt:.2f}")
            y -= 3.5 * mm

    pdf.setFont(
        "Helvetica",
        8
    )

    payment_method = invoice.payment_method or "Cash"

    pdf.drawString(
        LEFT,
        y,
        f"Paid ({payment_method}):"
    )

    pdf.drawRightString(
        total_x,
        y,
        f"Tk {(invoice.paid_amount or 0):.2f}"
    )

    y -= 3.5 * mm

    # =====================================================
    # DUE
    # =====================================================

    if invoice.due_amount and invoice.due_amount > 0:

        pdf.setFont(
            "Helvetica-Bold",
            9
        )

        pdf.drawString(
            LEFT,
            y,
            "DUE:"
        )

        pdf.drawRightString(
            total_x,
            y,
            f"Tk {invoice.due_amount:.2f}"
        )

        y -= 4.5 * mm

    # =====================================================
    # FOOTER SEPARATOR
    # =====================================================

    pdf.line(
        LEFT,
        y,
        RIGHT,
        y
    )

    y -= 4.5 * mm

    # =====================================================
    # FOOTER
    # =====================================================

    pdf.setFont(
        "Helvetica-Bold",
        8
    )

    pdf.drawCentredString(
        CENTER,
        y,
        "Thank You For Shopping!"
    )

    y -= 3.5 * mm

    pdf.setFont(
        "Helvetica",
        7.5
    )

    pdf.drawCentredString(
        CENTER,
        y,
        "Please Visit Again"
    )

    # =====================================================
    # SAVE PDF
    # =====================================================

    pdf.save()

    buffer.seek(0)

    return send_file(
        buffer,
        as_attachment=True,
        download_name=f"{invoice.invoice_no}.pdf",
        mimetype="application/pdf"
    )


@app.route("/due-receipt/<int:payment_id>/pdf")
@login_required
@permission_required(["customers", "sales", "sell"])
def due_receipt_pdf(payment_id):
    payment = CustomerPayment.query.filter_by(id=payment_id, shop_id=current_user.shop_id).first_or_404()
    customer = payment.customer
    shop = current_user.shop
    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id, customer_id=customer.id).all()
    total_due = sum(inv.due_amount or 0 for inv in invoices)

    buffer = BytesIO()
    PAGE_WIDTH = 80 * mm
    PAGE_HEIGHT = 85 * mm

    pdf = canvas.Canvas(buffer, pagesize=(PAGE_WIDTH, PAGE_HEIGHT))
    pdf.setTitle(f"REC-{payment.id:05d}")

    LEFT = 4 * mm
    RIGHT = PAGE_WIDTH - 4 * mm
    CENTER = PAGE_WIDTH / 2
    y = PAGE_HEIGHT - 6 * mm

    # Shop Header
    pdf.setFont("Helvetica-Bold", 12)
    pdf.drawCentredString(CENTER, y, shop.shop_name if shop else "Shop Manager POS")
    y -= 4 * mm
    pdf.setFont("Helvetica", 8)
    if shop and shop.address:
        pdf.drawCentredString(CENTER, y, str(shop.address)[:40])
        y -= 3.5 * mm
    if shop and shop.phone:
        pdf.drawCentredString(CENTER, y, f"Phone: {shop.phone}")
        y -= 3.5 * mm

    y -= 1 * mm
    pdf.setDash(2, 2)
    pdf.line(LEFT, y, RIGHT, y)
    pdf.setDash()
    y -= 4 * mm

    pdf.setFont("Helvetica-Bold", 8)
    pdf.drawString(LEFT, y, f"Inv: REC-{payment.id:05d}")
    date_str = payment.created_at.strftime("%d-%m-%y %I:%M%p") if payment.created_at else ""
    pdf.drawRightString(RIGHT, y, date_str)
    y -= 4 * mm

    if customer:
        pdf.drawString(LEFT, y, customer.name[:25])
        pdf.drawRightString(RIGHT, y, customer.phone or "")
        y -= 4 * mm

    pdf.setDash(2, 2)
    pdf.line(LEFT, y, RIGHT, y)
    pdf.setDash()
    y -= 4 * mm

    # Items Header
    pdf.setFont("Helvetica-Bold", 7.5)
    pdf.drawString(LEFT, y, "Item")
    pdf.drawCentredString(LEFT + 35 * mm, y, "Qty")
    pdf.drawRightString(RIGHT, y, "Total")
    y -= 3.5 * mm

    pdf.setFont("Helvetica", 7.5)
    pdf.drawString(LEFT, y, "Due Collection Payment")
    pdf.drawCentredString(LEFT + 35 * mm, y, "1")
    pdf.drawRightString(RIGHT, y, f"{payment.amount:.2f}")
    y -= 4 * mm

    pdf.setDash(2, 2)
    pdf.line(LEFT, y, RIGHT, y)
    pdf.setDash()
    y -= 4 * mm

    # Totals
    pdf.setFont("Helvetica", 8)
    pdf.drawString(LEFT, y, "Subtotal")
    pdf.drawRightString(RIGHT, y, f"Tk {payment.amount:.2f}")
    y -= 3.5 * mm

    pdf.setFont("Helvetica-Bold", 9)
    pdf.drawString(LEFT, y, "Grand Total")
    pdf.drawRightString(RIGHT, y, f"Tk {payment.amount:.2f}")
    y -= 4 * mm

    pdf.setFont("Helvetica", 8)
    pdf.drawString(LEFT, y, f"Paid ({payment.method or 'Cash'})")
    pdf.drawRightString(RIGHT, y, f"Tk {payment.amount:.2f}")
    y -= 3.5 * mm

    pdf.setFont("Helvetica-Bold", 8)
    pdf.drawString(LEFT, y, "Remaining Due")
    pdf.drawRightString(RIGHT, y, f"Tk {total_due:.2f}")
    y -= 4 * mm

    pdf.setDash(2, 2)
    pdf.line(LEFT, y, RIGHT, y)
    pdf.setDash()
    y -= 4 * mm

    pdf.setFont("Helvetica", 7.5)
    pdf.drawCentredString(CENTER, y, "Thank You For Payment")
    y -= 3.5 * mm
    pdf.drawCentredString(CENTER, y, "Please Visit Again")

    pdf.showPage()
    pdf.save()

    buffer.seek(0)
    return send_file(
        buffer,
        as_attachment=True,
        download_name=f"REC-{payment.id:05d}.pdf",
        mimetype="application/pdf"
    )

#--------------Returns------------------

@app.route("/returns")
@login_required
@permission_required("returns")
def returns():

    returns = SalesReturn.query.filter_by(
        shop_id=current_user.shop_id
    ).order_by(
        SalesReturn.return_date.desc()
    ).all()

    return render_template(
        "returns.html",
        returns=returns
    )
    
#------------ Return Details-------------
@app.route("/return-details/<int:id>")
@login_required
@permission_required("returns")
def return_details(id):

    sales_return = SalesReturn.query.filter_by(
        id=id,
        shop_id=current_user.shop_id
    ).first_or_404()

    return render_template(
        "return_details.html",
        sales_return=sales_return
    )

# ==========================================================
# NUMBER GENERATORS (new modules)
# ==========================================================

def generate_po_no():
    today = datetime.now().strftime("%Y%m%d")
    return f"PO-{today}-{uuid.uuid4().hex[:6].upper()}"


def generate_purchase_return_no():
    today = datetime.now().strftime("%Y%m%d")
    return f"PRT-{today}-{uuid.uuid4().hex[:6].upper()}"


def low_stock_products(shop_id):
    return Product.query.filter(
        Product.shop_id == shop_id,
        Product.active == True,
        Product.stock > 0,
        Product.stock <= Product.reorder_level
    ).all()


# ==========================================================
# USER MANAGEMENT (roles & permissions)
# ==========================================================

@app.route("/users")
@login_required
@permission_required("all")
def users():
    all_users = User.query.filter_by(shop_id=current_user.shop_id).all()
    return render_template("users.html", users=all_users, roles=list(ROLE_PERMISSIONS.keys()))


@app.route("/add-user", methods=["POST"])
@login_required
@permission_required("all")
def add_user():
    username = request.form["username"].strip()
    email = request.form.get("email", "").strip() or None
    phone = request.form.get("phone", "").strip() or None
    password = request.form["password"]
    role = request.form.get("role", "cashier")

    # Smart fallback: if username looks like a Gmail/Email and email field was empty
    if '@' in username and not email:
        email = username.lower()

    if email and User.query.filter_by(email=email).first():
        flash("এই Gmail দিয়ে ইতোমধ্যে একটি ইউজার অ্যাকাউন্ট রয়েছে।", "danger")
        return redirect(url_for("users"))

    if phone and User.query.filter_by(phone=phone).first():
        flash("এই ফোন নম্বর দিয়ে ইতোমধ্যে একটি ইউজার অ্যাকাউন্ট রয়েছে।", "danger")
        return redirect(url_for("users"))

    user = User(username=username, email=email, phone=phone, shop_id=current_user.shop_id, role=role)
    user.set_password(password)
    db.session.add(user)
    log_action(current_user.shop_id, current_user.id, "Create User", f"Created {username} ({role})")
    db.session.commit()
    trigger_cloud_sync_async(current_user.shop_id)

    flash("ইউজার অ্যাকাউন্ট সফলভাবে যোগ করা হয়েছে।", "success")
    return redirect(url_for("users"))


@app.route("/account", methods=["GET", "POST"])
@login_required
def account():
    """
    Every logged-in user (owner/admin especially) sets up their own
    password-recovery method here — an email address (preferred, needs
    internet) and/or a security question (works fully offline). This is
    what powers the self-service /forgot-password flow — without at
    least one of these, an admin locked out has no one above them to
    reset the password for them.
    """

    if request.method == "POST":
        current_password = request.form.get("current_password", "")
        form_type = request.form.get("form_type", "")

        if not current_user.check_password(current_password):
            flash("Current password is incorrect.", "danger")
            return redirect(url_for("account"))

        if form_type == "email":
            email = request.form.get("email", "").strip()

            if not email or "@" not in email or "." not in email.split("@")[-1]:
                flash("Please enter a valid email address.", "danger")
                return redirect(url_for("account"))

            current_user.email = email
            log_action(current_user.shop_id, current_user.id, "Update Account", "Recovery email updated")
            db.session.commit()
            trigger_cloud_sync_async(current_user.shop_id)
            flash("Recovery email saved.", "success")
            return redirect(url_for("account"))

        elif form_type == "question":
            question = request.form.get("recovery_question", "").strip()
            answer = request.form.get("recovery_answer", "").strip()

            if not question or not answer:
                flash("Please provide both a recovery question and an answer.", "danger")
                return redirect(url_for("account"))

            current_user.recovery_question = question
            current_user.set_recovery_answer(answer)
            log_action(current_user.shop_id, current_user.id, "Update Account", "Recovery question updated")
            db.session.commit()
            trigger_cloud_sync_async(current_user.shop_id)
            flash("Recovery question saved. Keep the answer somewhere safe.", "success")
            return redirect(url_for("account"))

        elif form_type == "password":
            new_password = request.form.get("new_password", "").strip()
            confirm_password = request.form.get("confirm_password", "").strip()

            if not new_password:
                flash("নতুন পাসওয়ার্ড দিন।", "danger")
                return redirect(url_for("account"))
            if len(new_password) < 6:
                flash("পাসওয়ার্ড কমপক্ষে ৬ অক্ষরের হতে হবে।", "danger")
                return redirect(url_for("account"))
            if new_password != confirm_password:
                flash("পাসওয়ার্ড মিলছে না।", "danger")
                return redirect(url_for("account"))

            current_user.set_password(new_password)
            current_user.must_change_password = False
            log_action(current_user.shop_id, current_user.id, "Update Account", "Password changed")
            db.session.commit()
            trigger_cloud_sync_async(current_user.shop_id)
            flash("পাসওয়ার্ড সফলভাবে পরিবর্তন হয়েছে।", "success")
            return redirect(url_for("dashboard"))

    return render_template("account.html", must_change=current_user.must_change_password)


@app.route("/edit-user/<int:id>", methods=["POST"])
@login_required
@permission_required("all")
def edit_user(id):
    user = User.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    user.role = request.form.get("role", user.role)

    new_email = request.form.get("email", "").strip() or None
    new_phone = request.form.get("phone", "").strip() or None

    if new_email and new_email != user.email:
        existing = User.query.filter(User.email == new_email, User.id != user.id).first()
        if existing:
            flash("এই Gmail ইতোমধ্যে অন্য অ্যাকাউন্টে ব্যবহৃত।", "danger")
            return redirect(url_for("users"))
        user.email = new_email

    if new_phone and new_phone != user.phone:
        existing = User.query.filter(User.phone == new_phone, User.id != user.id).first()
        if existing:
            flash("এই ফোন নম্বর ইতোমধ্যে অন্য অ্যাকাউন্টে ব্যবহৃত।", "danger")
            return redirect(url_for("users"))
        user.phone = new_phone

    new_password = request.form.get("password", "").strip()
    if new_password:
        user.set_password(new_password)

    log_action(current_user.shop_id, current_user.id, "Edit User", f"Updated {user.username}")
    db.session.commit()
    trigger_cloud_sync_async(current_user.shop_id)

    flash("ইউজার তথ্য পরিবর্তন করা হয়েছে।", "success")
    return redirect(url_for("users"))


@app.route("/delete-user/<int:id>", methods=["GET", "POST"])
@login_required
@permission_required("all")
def delete_user(id):
    user = User.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    if user.id == current_user.id:
        flash("You cannot delete your own account.", "danger")
        return redirect(url_for("users"))

    db.session.delete(user)
    log_action(current_user.shop_id, current_user.id, "Delete User", f"Deleted {user.username}")
    db.session.commit()
    trigger_cloud_sync_async(current_user.shop_id)

    flash("User deleted.", "success")
    return redirect(url_for("users"))


# ==========================================================
# AUDIT LOG
# ==========================================================

@app.route("/audit-log")
@login_required
@permission_required("all")
def audit_log():
    logs = AuditLog.query.filter_by(
        shop_id=current_user.shop_id
    ).order_by(AuditLog.created_at.desc()).limit(500).all()

    return render_template("audit_log.html", logs=logs)


# ==========================================================
# DISCOUNT COUPONS
# ==========================================================

@app.route("/coupons")
@login_required
@permission_required("coupons")
def coupons():
    all_coupons = Coupon.query.filter_by(shop_id=current_user.shop_id).order_by(Coupon.id.desc()).all()
    return render_template("coupons.html", coupons=all_coupons)


@app.route("/add-coupon", methods=["POST"])
@login_required
@permission_required("coupons")
def add_coupon():
    code = request.form["code"].strip().upper()
    expiry = request.form.get("expiry_date") or None
    usage_limit = request.form.get("usage_limit") or None

    coupon = Coupon(
        shop_id=current_user.shop_id,
        code=code,
        discount_type=request.form.get("discount_type", "percent"),
        value=float(request.form.get("value", 0)),
        expiry_date=datetime.strptime(expiry, "%Y-%m-%d").date() if expiry else None,
        usage_limit=int(usage_limit) if usage_limit else None
    )
    db.session.add(coupon)

    try:
        db.session.commit()
        flash("Coupon created.", "success")
    except Exception:
        db.session.rollback()
        flash("A coupon with that code already exists.", "danger")

    return redirect(url_for("coupons"))


@app.route("/toggle-coupon/<int:id>", methods=["POST"])
@login_required
@permission_required("coupons")
def toggle_coupon(id):
    coupon = Coupon.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    coupon.active = not coupon.active
    db.session.commit()
    return redirect(url_for("coupons"))


@app.route("/delete-coupon/<int:id>", methods=["POST"])
@login_required
@permission_required("coupons")
def delete_coupon(id):
    coupon = Coupon.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    db.session.delete(coupon)
    db.session.commit()
    return redirect(url_for("coupons"))


@app.route("/api/check-coupon")
@login_required
@permission_required("sell")
def api_check_coupon():
    code = request.args.get("code", "").strip().upper()
    subtotal = float(request.args.get("subtotal", 0) or 0)

    coupon = Coupon.query.filter_by(shop_id=current_user.shop_id, code=code).first()

    if not coupon or not coupon.is_valid():
        return jsonify({"valid": False, "message": "Invalid or expired coupon."})

    discount = coupon.calculate_discount(subtotal)
    return jsonify({"valid": True, "discount": discount, "code": coupon.code})


# ==========================================================
# HOLD / DRAFT SALE
# ==========================================================

@app.route("/hold-sale", methods=["POST"])
@login_required
@permission_required("sell")
def hold_sale():
    items_json = request.form.get("cart_data", "[]")
    try:
        cart = json.loads(items_json)
    except Exception:
        cart = []

    if not cart:
        flash("Cart is empty, nothing to hold.", "warning")
        return redirect(url_for("sell"))

    customer_id = request.form.get("customer_id") or None

    held = HeldSale(
        shop_id=current_user.shop_id,
        user_id=current_user.id,
        customer_id=int(customer_id) if customer_id else None,
        reference=f"HOLD-{datetime.now().strftime('%H%M%S')}",
        note=request.form.get("note", "")
    )
    db.session.add(held)
    db.session.flush()

    for row in cart:
        db.session.add(HeldSaleItem(
            held_sale_id=held.id,
            product_id=int(row["id"]),
            quantity=float(row["qty"]),
            price=float(row["price"])
        ))

    log_action(current_user.shop_id, current_user.id, "Hold Sale", held.reference)
    db.session.commit()

    flash(f"Sale held as {held.reference}.", "success")
    return redirect(url_for("sell"))


@app.route("/held-sales")
@login_required
@permission_required("sell")
def held_sales():
    held = HeldSale.query.filter_by(shop_id=current_user.shop_id).order_by(HeldSale.created_at.desc()).all()
    return render_template("held_sales.html", held_sales=held)


@app.route("/resume-hold/<int:id>")
@login_required
@permission_required("sell")
def resume_hold(id):
    held = HeldSale.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    cart = [
        {
            "id": item.product_id,
            "name": item.product.name if item.product else "",
            "qty": item.quantity,
            "price": item.price
        }
        for item in held.items
    ]

    products = Product.query.filter_by(shop_id=current_user.shop_id).all()
    customers = Customer.query.filter_by(shop_id=current_user.shop_id).all()
    resume_customer_id = held.customer_id or ""

    db.session.delete(held)
    db.session.commit()

    return render_template(
        "sell.html",
        products=products,
        customers=customers,
        resume_cart=json.dumps(cart),
        resume_customer=resume_customer_id
    )


@app.route("/delete-hold/<int:id>", methods=["POST"])
@login_required
@permission_required("sell")
def delete_hold(id):
    held = HeldSale.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    db.session.delete(held)
    db.session.commit()
    flash("Held sale removed.", "success")
    return redirect(url_for("held_sales"))


# ==========================================================
# CUSTOMER LEDGER, DUE COLLECTION & ADVANCE PAYMENT
# ==========================================================

@app.route("/customer-ledger/<int:id>")
@login_required
@permission_required("customers")
def customer_ledger(id):
    customer = Customer.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    invoices = Invoice.query.filter_by(customer_id=customer.id).order_by(Invoice.created_at.desc()).all()
    payments = CustomerPayment.query.filter_by(customer_id=customer.id).order_by(CustomerPayment.created_at.desc()).all()

    total_due = sum((inv.due_amount or 0) for inv in invoices)

    return render_template(
        "customer_ledger.html",
        customer=customer,
        invoices=invoices,
        payments=payments,
        total_due=total_due
    )


@app.route("/collect-due/<int:customer_id>", methods=["POST"])
@login_required
@permission_required("customers")
def collect_due(customer_id):
    customer = Customer.query.filter_by(id=customer_id, shop_id=current_user.shop_id).first_or_404()
    amount = safe_float(request.form.get("amount"), 0.0)
    invoice_id = request.form.get("invoice_id") or None

    if amount <= 0:
        flash("Enter a valid amount.", "warning")
        return redirect(url_for("customer_ledger", id=customer_id))

    payment = CustomerPayment(
        shop_id=current_user.shop_id,
        customer_id=customer.id,
        invoice_id=invoice_id,
        amount=amount,
        payment_type="due_collection",
        method=request.form.get("method", "Cash"),
        note=request.form.get("note", "")
    )
    db.session.add(payment)

    # Auto-allocate payment to customer's due invoices
    remaining = amount
    if invoice_id:
        target_inv = Invoice.query.filter_by(
            id=invoice_id, shop_id=current_user.shop_id, customer_id=customer.id
        ).first()
        if target_inv and target_inv.due_amount and target_inv.due_amount > 0:
            to_pay = min(remaining, target_inv.due_amount)
            target_inv.paid_amount = (target_inv.paid_amount or 0) + to_pay
            target_inv.due_amount = max((target_inv.due_amount or 0) - to_pay, 0.0)
            target_inv.payment_status = "Paid" if target_inv.due_amount <= 0 else "Partial"
            remaining -= to_pay

    if remaining > 0:
        due_invoices = Invoice.query.filter(
            Invoice.shop_id == current_user.shop_id,
            Invoice.customer_id == customer.id,
            Invoice.due_amount > 0
        ).order_by(Invoice.created_at.asc()).all()

        for inv in due_invoices:
            if remaining <= 0:
                break
            to_pay = min(remaining, inv.due_amount)
            inv.paid_amount = (inv.paid_amount or 0) + to_pay
            inv.due_amount = max((inv.due_amount or 0) - to_pay, 0.0)
            inv.payment_status = "Paid" if inv.due_amount <= 0 else "Partial"
            remaining -= to_pay

    log_action(current_user.shop_id, current_user.id, "Collect Due", f"{customer.name}: {amount}")
    db.session.commit()
    trigger_cloud_sync_async(current_user.shop_id)

    flash("Due collected successfully. Money receipt ready to print.", "success")
    return redirect(url_for("customer_ledger", id=customer_id, print_receipt_id=payment.id))


@app.route("/due-receipt/<int:payment_id>")
@login_required
@permission_required(["customers", "sales", "sell"])
def due_receipt(payment_id):
    payment = CustomerPayment.query.filter_by(id=payment_id, shop_id=current_user.shop_id).first_or_404()
    customer = payment.customer
    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id, customer_id=customer.id).all()
    total_due = sum(inv.due_amount or 0 for inv in invoices)
    shop = current_user.shop
    return render_template("due_receipt.html", p=payment, customer=customer, total_due=total_due, shop=shop)


@app.route("/advance-payment/<int:customer_id>", methods=["POST"])
@login_required
@permission_required("customers")
def advance_payment(customer_id):
    customer = Customer.query.filter_by(id=customer_id, shop_id=current_user.shop_id).first_or_404()
    amount = safe_float(request.form.get("amount"), 0.0)

    if amount <= 0:
        flash("Enter a valid amount.", "warning")
        return redirect(url_for("customer_ledger", id=customer_id))

    payment = CustomerPayment(
        shop_id=current_user.shop_id,
        customer_id=customer.id,
        amount=amount,
        payment_type="advance",
        method=request.form.get("method", "Cash"),
        note=request.form.get("note", "")
    )
    db.session.add(payment)

    customer.advance_balance = (customer.advance_balance or 0) + amount

    log_action(current_user.shop_id, current_user.id, "Advance Payment", f"{customer.name}: {amount}")
    db.session.commit()

    flash("Advance payment recorded.", "success")
    return redirect(url_for("customer_ledger", id=customer_id))


# ==========================================================
# STOCK ADJUSTMENT & LOW STOCK ALERT
# ==========================================================

@app.route("/stock-adjustment", methods=["GET", "POST"])
@login_required
@permission_required("stock")
def stock_adjustment():
    if request.method == "POST":
        product_id = safe_int(request.form.get("product_id"))
        product = Product.query.filter_by(
            id=product_id, shop_id=current_user.shop_id
        ).first()

        if not product:
            flash("পণ্যটি খুঁজে পাওয়া যায়নি।", "danger")
            return redirect(url_for("stock_adjustment"))

        old_stock = product.stock or 0
        change_qty = safe_float(request.form.get("change_qty"), 0.0)
        new_stock = old_stock + change_qty
        product.stock = new_stock
        reason = request.form.get("reason", "").strip()

        db.session.add(StockAdjustment(
            shop_id=current_user.shop_id,
            product_id=product.id,
            user_id=current_user.id,
            change_qty=change_qty,
            reason=reason
        ))

        log_action(
            current_user.shop_id,
            current_user.id,
            "Stock Adjustment",
            f"Product '{product.name}': Stock {old_stock:g} -> {new_stock:g} ({change_qty:+g})" + (f" | Reason: {reason}" if reason else "")
        )
        db.session.commit()

        flash("Stock adjusted.", "success")
        return redirect(url_for("stock_adjustment"))

    products = Product.query.filter_by(shop_id=current_user.shop_id, active=True).order_by(Product.name).all()
    history = StockAdjustment.query.filter_by(shop_id=current_user.shop_id).order_by(
        StockAdjustment.created_at.desc()
    ).limit(100).all()

    return render_template("stock_adjustment.html", products=products, history=history)


@app.route("/low-stock-alert")
@login_required
@permission_required("stock")
def low_stock_alert():
    products = low_stock_products(current_user.shop_id)
    out_of_stock = Product.query.filter(
        Product.shop_id == current_user.shop_id,
        Product.active == True,
        Product.stock <= 0
    ).order_by(Product.name).all()
    return render_template("low_stock_alert.html", products=products, out_of_stock=out_of_stock)


# ==========================================================
# PURCHASE ORDER
# ==========================================================

@app.route("/purchase-orders")
@login_required
@permission_required("purchase")
def purchase_orders():
    orders = PurchaseOrder.query.filter_by(shop_id=current_user.shop_id).order_by(
        PurchaseOrder.created_at.desc()
    ).all()
    return render_template("purchase_orders.html", orders=orders)


@app.route("/create-purchase-order", methods=["GET", "POST"])
@login_required
@permission_required("purchase")
def create_purchase_order():
    if request.method == "POST":
        po = PurchaseOrder(
            shop_id=current_user.shop_id,
            supplier_id=request.form.get("supplier_id") or None,
            po_no=generate_po_no(),
            note=request.form.get("note", "")
        )
        db.session.add(po)
        db.session.flush()

        product_ids = request.form.getlist("product_id[]")
        quantities = request.form.getlist("quantity[]")
        prices = request.form.getlist("buy_price[]")

        for pid, qty, price in zip(product_ids, quantities, prices):
            if not pid or not qty:
                continue
            db.session.add(PurchaseOrderItem(
                purchase_order_id=po.id,
                product_id=int(pid),
                quantity=float(qty),
                buy_price=float(price or 0)
            ))

        log_action(current_user.shop_id, current_user.id, "Create Purchase Order", po.po_no)
        db.session.commit()

        flash(f"Purchase order {po.po_no} created.", "success")
        return redirect(url_for("purchase_orders"))

    products = Product.query.filter_by(shop_id=current_user.shop_id, active=True).order_by(Product.name).all()
    suppliers = Supplier.query.filter_by(shop_id=current_user.shop_id, active=True).order_by(Supplier.name).all()
    return render_template("create_purchase_order.html", products=products, suppliers=suppliers)


@app.route("/receive-purchase-order/<int:id>", methods=["POST"])
@login_required
@permission_required("purchase")
def receive_purchase_order(id):
    po = PurchaseOrder.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()

    if po.status == "Received":
        flash("This purchase order was already received.", "warning")
        return redirect(url_for("purchase_orders"))

    for item in po.items:
        purchase = Purchase(
            shop_id=current_user.shop_id,
            product_id=item.product_id,
            supplier_id=po.supplier_id,
            quantity=item.quantity,
            buy_price=item.buy_price,
            payment_status="Due",
            due_amount=item.quantity * item.buy_price
        )
        db.session.add(purchase)

        if item.product:
            item.product.stock = (item.product.stock or 0) + item.quantity

    po.status = "Received"
    log_action(current_user.shop_id, current_user.id, "Receive Purchase Order", po.po_no)
    db.session.commit()

    flash(f"Purchase order {po.po_no} received into stock.", "success")
    return redirect(url_for("purchase_orders"))


@app.route("/cancel-purchase-order/<int:id>", methods=["POST"])
@login_required
@permission_required("purchase")
def cancel_purchase_order(id):
    po = PurchaseOrder.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    po.status = "Cancelled"
    db.session.commit()
    flash("Purchase order cancelled.", "success")
    return redirect(url_for("purchase_orders"))


# ==========================================================
# PURCHASE RETURN (goods sent back to supplier)
# ==========================================================

@app.route("/purchase-return/<int:purchase_id>", methods=["GET", "POST"])
@login_required
@permission_required("purchase")
def purchase_return(purchase_id):
    purchase = Purchase.query.filter_by(id=purchase_id, shop_id=current_user.shop_id).first_or_404()

    if request.method == "POST":
        qty = float(request.form.get("quantity", 0) or 0)

        if qty <= 0 or qty > purchase.quantity:
            flash("Enter a valid return quantity.", "danger")
            return redirect(url_for("purchase_return", purchase_id=purchase.id))

        amount = qty * purchase.buy_price

        pr = PurchaseReturn(
            shop_id=current_user.shop_id,
            purchase_id=purchase.id,
            return_no=generate_purchase_return_no(),
            quantity=qty,
            amount=amount,
            reason=request.form.get("reason", "")
        )
        db.session.add(pr)

        if purchase.product:
            purchase.product.stock = max((purchase.product.stock or 0) - qty, 0)

        log_action(current_user.shop_id, current_user.id, "Purchase Return", pr.return_no)
        db.session.commit()

        flash("Purchase return recorded.", "success")
        return redirect(url_for("purchase_history"))

    return render_template("purchase_return.html", purchase=purchase)


# ==========================================================
# RECURRING EXPENSES
# ==========================================================

@app.route("/toggle-recurring-expense/<int:id>", methods=["POST"])
@login_required
@permission_required("expenses")
def toggle_recurring_expense(id):
    expense = Expense.query.filter_by(id=id, shop_id=current_user.shop_id).first_or_404()
    expense.is_recurring = not expense.is_recurring
    if expense.is_recurring and not expense.recurring_frequency:
        expense.recurring_frequency = "monthly"
    db.session.commit()
    flash("Recurring status updated.", "success")
    return redirect(url_for("expenses"))


def generate_due_recurring_expenses():
    """Runs on a schedule: regenerates monthly recurring expenses."""
    with app.app_context():
        today = datetime.now()
        templates = Expense.query.filter_by(is_recurring=True).all()

        for tmpl in templates:
            last = tmpl.last_generated or tmpl.date
            if (today.year, today.month) == (last.year, last.month):
                continue

            db.session.add(Expense(
                shop_id=tmpl.shop_id,
                title=tmpl.title,
                category=tmpl.category,
                amount=tmpl.amount,
                note=f"Auto-generated recurring expense from '{tmpl.title}'"
            ))
            tmpl.last_generated = today

        db.session.commit()


# ==========================================================
# BARCODE PRINTING
# ==========================================================

@app.route("/barcode-print")
@login_required
@permission_required("products")
def barcode_print_page():
    products = Product.query.filter_by(shop_id=current_user.shop_id, active=True).order_by(Product.name).all()
    return render_template("barcode_print.html", products=products)

#########
@app.route("/barcode-pdf")
@login_required
@permission_required("products")
def barcode_pdf():
    ids = request.args.getlist("ids")
    try:
        copies = max(1, min(50, int(request.args.get("copies", 1) or 1)))
    except ValueError:
        copies = 1
    label_size = request.args.get("label_size", "80x35")

    # -----------------------------------------------------
    # Get selected products
    # -----------------------------------------------------
    products = Product.query.filter(
        Product.id.in_(ids),
        Product.shop_id == current_user.shop_id
    ).all()

    buffer = BytesIO()

    # -----------------------------------------------------
    # Compact labels sized for common USB/Bluetooth thermal printers.
    # 80x35 is the default; 58x30 helps save paper on smaller labels.
    # -----------------------------------------------------
    if label_size == "58x30":
        label_w, label_h = 58 * mm, 30 * mm
    else:
        label_w, label_h = 80 * mm, 35 * mm

    pdf = canvas.Canvas(
        buffer,
        pagesize=(label_w, label_h)
    )

    # -----------------------------------------------------
    # Generate labels
    # -----------------------------------------------------
    for product in products:

        code_value = (
            product.barcode
            or product.product_code
            or str(product.id)
        )

        for _ in range(copies):

            # -------------------------------------------------
            # Label center
            # -------------------------------------------------
            center_x = label_w / 2

            # -------------------------------------------------
            # Barcode
            # -------------------------------------------------
            barcode_widget = code128.Code128(
                code_value,
                barHeight=10 * mm,
                barWidth=0.55
            )

            barcode_width = barcode_widget.width

            # Maximum barcode width
            max_barcode_width = label_w - (6 * mm)

            # If barcode is too wide, reduce it
            if barcode_width > max_barcode_width:

                barcode_widget = code128.Code128(
                    code_value,
                    barHeight=10 * mm,
                    barWidth=0.42
                )

                barcode_width = barcode_widget.width

            # Center barcode horizontally
            barcode_x = center_x - (barcode_width / 2)

            # Barcode vertical position
            barcode_y = label_h - (14 * mm)

            barcode_widget.drawOn(
                pdf,
                barcode_x,
                barcode_y
            )

            # -------------------------------------------------
            # Product Name
            # -------------------------------------------------
            pdf.setFont("Helvetica", 8)

            product_name = product.name[:30]

            # Very small gap below barcode
            name_y = barcode_y - 10

            pdf.drawCentredString(
                center_x,
                name_y,
                product_name
            )

            # -------------------------------------------------
            # Price
            # -------------------------------------------------
            pdf.setFont("Helvetica-Bold", 8)

            # Small gap below product name
            price_y = name_y - 10

            pdf.drawCentredString(
                center_x,
                price_y,
                f"Tk {product.sell_price:.2f}"
            )

            # -------------------------------------------------
            # Finish this label
            # -------------------------------------------------
            pdf.showPage()

    # -----------------------------------------------------
    # Save PDF
    # -----------------------------------------------------
    pdf.save()

    buffer.seek(0)

    # -----------------------------------------------------
    # Return PDF
    # -----------------------------------------------------
    mode = request.args.get("mode", "download")
    as_attach = True if mode != "inline" else False

    return send_file(
        buffer,
        as_attachment=as_attach,
        download_name=f"barcodes_{label_size}.pdf",
        mimetype="application/pdf"
    )

# ==========================================================
# EXPORTS: PDF / EXCEL / CSV for reports
# ==========================================================

def _rows_to_csv_response(headers, rows, filename):
    output = _io.StringIO()
    writer = csv.writer(output)
    writer.writerow(headers)
    writer.writerows(rows)

    mem = BytesIO()
    mem.write(output.getvalue().encode("utf-8-sig"))
    mem.seek(0)

    return send_file(mem, as_attachment=True, download_name=filename, mimetype="text/csv")


def _rows_to_excel_response(headers, rows, filename, sheet_title="Report"):
    if not HAS_OPENPYXL:
        flash("Excel export isn't available on this server (openpyxl missing).", "danger")
        return redirect(url_for("dashboard"))

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = sheet_title
    ws.append(headers)

    for row in rows:
        ws.append(row)

    for i, header in enumerate(headers, start=1):
        ws.column_dimensions[get_column_letter(i)].width = max(14, len(str(header)) + 2)

    mem = BytesIO()
    wb.save(mem)
    mem.seek(0)

    return send_file(
        mem,
        as_attachment=True,
        download_name=filename,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )


def _rows_to_pdf_response(title, headers, rows, filename):
    buffer = BytesIO()
    pdf = canvas.Canvas(buffer, pagesize=A4)
    page_w, page_h = A4

    y = page_h - 50
    pdf.setFont("Helvetica-Bold", 14)
    pdf.drawString(40, y, title)
    y -= 25

    col_x = [40 + i * (page_w - 80) / len(headers) for i in range(len(headers))]

    pdf.setFont("Helvetica-Bold", 9)
    for x, h in zip(col_x, headers):
        pdf.drawString(x, y, str(h))
    y -= 12
    pdf.line(40, y, page_w - 40, y)
    y -= 14

    pdf.setFont("Helvetica", 9)
    for row in rows:
        if y < 50:
            pdf.showPage()
            y = page_h - 50
            pdf.setFont("Helvetica", 9)
        for x, val in zip(col_x, row):
            pdf.drawString(x, y, str(val))
        y -= 14

    pdf.save()
    buffer.seek(0)

    return send_file(buffer, as_attachment=True, download_name=filename, mimetype="application/pdf")


@app.route("/export/sales/<fmt>")
@login_required
@permission_required("reports")
def export_sales(fmt):
    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id).order_by(Invoice.created_at.desc()).all()

    headers = ["Invoice No", "Date", "Customer", "Subtotal", "Discount", "Total", "Paid", "Due", "Status"]
    rows = [
        [
            inv.invoice_no,
            inv.created_at.strftime("%Y-%m-%d %H:%M"),
            inv.customer.name if inv.customer else "Walk-in",
            f"{inv.subtotal or 0:.2f}",
            f"{inv.discount_amount or 0:.2f}",
            f"{inv.total_amount or 0:.2f}",
            f"{inv.paid_amount or 0:.2f}",
            f"{inv.due_amount or 0:.2f}",
            inv.payment_status
        ]
        for inv in invoices
    ]

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "sales_report.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "sales_report.xlsx", "Sales")
    if fmt == "pdf":
        return _rows_to_pdf_response("Sales Report", headers, rows, "sales_report.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("sales"))


@app.route("/export/stock/<fmt>")
@login_required
@permission_required("reports")
def export_stock(fmt):
    products = Product.query.filter_by(shop_id=current_user.shop_id).order_by(Product.name).all()

    headers = ["Code", "Name", "Category", "Stock", "Buy Price", "Sell Price", "Stock Value"]
    rows = [
        [
            p.product_code,
            p.name,
            p.category or "",
            p.stock,
            f"{p.buy_price:.2f}",
            f"{p.sell_price:.2f}",
            f"{(p.stock or 0) * (p.buy_price or 0):.2f}"
        ]
        for p in products
    ]

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "stock_report.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "stock_report.xlsx", "Stock")
    if fmt == "pdf":
        return _rows_to_pdf_response("Stock Report", headers, rows, "stock_report.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("stock_report"))


@app.route("/export/expenses/<fmt>")
@login_required
@permission_required("reports")
def export_expenses(fmt):
    items = Expense.query.filter_by(shop_id=current_user.shop_id).order_by(Expense.date.desc()).all()

    headers = ["Title", "Category", "Amount", "Recurring", "Date", "Note"]
    rows = [
        [
            e.title,
            e.category or "",
            f"{e.amount:.2f}",
            "Yes" if e.is_recurring else "No",
            e.date.strftime("%Y-%m-%d"),
            e.note or ""
        ]
        for e in items
    ]

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "expenses_report.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "expenses_report.xlsx", "Expenses")
    if fmt == "pdf":
        return _rows_to_pdf_response("Expense Report", headers, rows, "expenses_report.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("expenses"))


@app.route("/export/customers/<fmt>")
@login_required
@permission_required("reports")
def export_customers(fmt):
    items = Customer.query.filter_by(shop_id=current_user.shop_id).order_by(Customer.name).all()

    headers = ["Name", "Phone", "Address", "Advance Balance"]
    rows = [[c.name, c.phone or "", c.address or "", f"{c.advance_balance or 0:.2f}"] for c in items]

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "customers_report.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "customers_report.xlsx", "Customers")
    if fmt == "pdf":
        return _rows_to_pdf_response("Customer Report", headers, rows, "customers_report.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("customers"))


@app.route("/export/suppliers/<fmt>")
@login_required
@permission_required("reports")
def export_suppliers(fmt):
    items = Supplier.query.filter_by(shop_id=current_user.shop_id).order_by(Supplier.name).all()

    headers = ["Code", "Name", "Company", "Phone", "Opening Due"]
    rows = [[s.supplier_code, s.name, s.company or "", s.phone or "", f"{s.opening_due or 0:.2f}"] for s in items]

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "suppliers_report.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "suppliers_report.xlsx", "Suppliers")
    if fmt == "pdf":
        return _rows_to_pdf_response("Supplier Report", headers, rows, "suppliers_report.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("suppliers"))


@app.route("/export/profit/<fmt>")
@login_required
@permission_required("reports")
def export_profit(fmt):
    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id).all()

    headers = ["Invoice No", "Date", "Sales Total", "Est. Cost", "Gross Profit"]
    rows = []

    for inv in invoices:
        cost = sum((item.product.buy_price or 0) * item.quantity for item in inv.items if item.product)
        profit = (inv.total_amount or 0) - cost
        rows.append([
            inv.invoice_no,
            inv.created_at.strftime("%Y-%m-%d"),
            f"{inv.total_amount or 0:.2f}",
            f"{cost:.2f}",
            f"{profit:.2f}"
        ])

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "profit_report.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "profit_report.xlsx", "Profit")
    if fmt == "pdf":
        return _rows_to_pdf_response("Profit Report", headers, rows, "profit_report.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("dashboard"))


@app.route("/export/daily-report/<fmt>")
@login_required
@permission_required("reports")
def export_daily_report(fmt):
    now = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)
    today = now.date()

    # Optional date filter (?date=YYYY-MM-DD), defaults to today — mirrors daily_report()
    date_str = request.args.get("date", "").strip()
    try:
        selected_date = datetime.strptime(date_str, "%Y-%m-%d").date() if date_str else today
    except ValueError:
        selected_date = today

    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id).all()

    headers = ["Invoice No", "Product", "Qty", "Price", "Total", "Date"]
    rows = []

    for inv in invoices:
        if not inv.created_at:
            continue
        if inv.created_at.date() != selected_date:  # already stored in Bangladesh time
            continue
        for item in inv.items:
            rows.append([
                inv.invoice_no,
                item.product.name if item.product else "Deleted Product",
                f"{item.quantity:.2f}",
                f"{item.price:.2f}",
                f"{item.total:.2f}",
                inv.created_at.strftime("%Y-%m-%d %H:%M")
            ])

    fname = f"daily_report_{selected_date}"
    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, fname + ".csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, fname + ".xlsx", "Daily Report")
    if fmt == "pdf":
        return _rows_to_pdf_response(f"Daily Sales Report - {selected_date}", headers, rows, fname + ".pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("daily_report"))


@app.route("/export/monthly-report/<fmt>")
@login_required
@permission_required("reports")
def export_monthly_report(fmt):
    now = datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(hours=6)
    current_month, current_year = now.month, now.year

    # Optional month filter (?month=YYYY-MM), defaults to current month — mirrors monthly_report()
    month_str = request.args.get("month", "").strip()
    selected_month, selected_year = current_month, current_year
    if month_str:
        try:
            y_str, m_str = month_str.split("-")
            selected_year = int(y_str)
            selected_month = int(m_str)
        except (ValueError, AttributeError):
            selected_month, selected_year = current_month, current_year

    invoices = Invoice.query.filter_by(shop_id=current_user.shop_id).all()

    headers = ["Invoice No", "Product", "Qty", "Price", "Total", "Date"]
    rows = []

    for inv in invoices:
        if not inv.created_at:
            continue
        inv_time = inv.created_at  # already stored in Bangladesh time
        if inv_time.month != selected_month or inv_time.year != selected_year:
            continue
        for item in inv.items:
            rows.append([
                inv.invoice_no,
                item.product.name if item.product else "Deleted Product",
                f"{item.quantity:.2f}",
                f"{item.price:.2f}",
                f"{item.total:.2f}",
                inv_time.strftime("%Y-%m-%d %H:%M")
            ])

    fname = f"monthly_report_{selected_year}_{selected_month:02d}"
    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, fname + ".csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, fname + ".xlsx", "Monthly Report")
    if fmt == "pdf":
        return _rows_to_pdf_response(f"Monthly Sales Report - {selected_year}-{selected_month:02d}", headers, rows, fname + ".pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("monthly_report"))


@app.route("/export/audit-log/<fmt>")
@login_required
@permission_required("all")
def export_audit_log(fmt):
    logs = AuditLog.query.filter_by(shop_id=current_user.shop_id).order_by(AuditLog.created_at.desc()).limit(2000).all()

    headers = ["Date/Time", "User", "Role", "Action", "Details"]
    rows = [
        [
            log.created_at.strftime("%Y-%m-%d %I:%M %p"),
            log.user.username if log.user else "System",
            (log.user.role if log.user and log.user.role else "System").title(),
            log.action,
            log.details or ""
        ]
        for log in logs
    ]

    if fmt == "csv":
        return _rows_to_csv_response(headers, rows, "audit_log.csv")
    if fmt == "excel":
        return _rows_to_excel_response(headers, rows, "audit_log.xlsx", "Audit Log")
    if fmt == "pdf":
        return _rows_to_pdf_response("Audit Log", headers, rows, "audit_log.pdf")

    flash("Unknown export format.", "danger")
    return redirect(url_for("audit_log"))


# ---------------- LOGOUT ----------------


@app.route("/logout")
@login_required
def logout():


    logout_user()


    return redirect(url_for("login"))




# ---------------- APP RUN ----------------


from sqlalchemy import text

def initialize_app():
    """Run DB creation, migrations, and default shop/admin seeding.
    Called both from `python app.py` and from launcher.py (the packaged
    desktop build) so both paths share the exact same startup logic."""

    with app.app_context():

        db.create_all()

        # ==========================
        # SALES RETURN MIGRATION
        # ==========================
        try:
            from sqlalchemy import inspect
            inspector = inspect(db.engine)
            existing_tables = inspector.get_table_names()
            if "sales_return" in existing_tables:
                existing_cols = [c['name'] for c in inspector.get_columns("sales_return")]
                if "return_no" not in existing_cols:
                    db.session.execute(text("ALTER TABLE sales_return ADD COLUMN return_no TEXT"))
                    db.session.commit()
        except Exception:
            db.session.rollback()

        # ==========================
        # NEW-COLUMN MIGRATION (safe, additive only)
        # ==========================
        from sqlalchemy import inspect
        try:
            inspector = inspect(db.engine)
            existing_tables = inspector.get_table_names()
            NEW_COLUMNS = [
                ("product", "image", "TEXT"),
                ("product", "brand", "TEXT"),
                ("product", "batch_number", "TEXT"),
                ("product", "expiry_date", "DATE"),
                ("product", "reorder_level", "FLOAT DEFAULT 5"),
                ("product", "created_at", "DATETIME"),
                ("customer", "advance_balance", "FLOAT DEFAULT 0"),
                ("customer", "loyalty_points", "FLOAT DEFAULT 0"),
                ("invoice", "coupon_code", "TEXT"),
                ("invoice", "payment_split", "TEXT"),
                ("invoice", "received_amount", "FLOAT DEFAULT 0"),
                ("invoice", "change_amount", "FLOAT DEFAULT 0"),
                ("expense", "is_recurring", "BOOLEAN DEFAULT 0"),
                ("expense", "recurring_frequency", "TEXT"),
                ("expense", "last_generated", "DATETIME"),
                ("expense", "payment_method", "TEXT DEFAULT 'Cash'"),
                ("user", "recovery_question", "TEXT"),
                ("user", "recovery_answer_hash", "TEXT"),
                ("user", "phone", "TEXT"),
                ("user", "email", "TEXT"),
                ("user", "reset_code_hash", "TEXT"),
                ("user", "reset_code_expires", "DATETIME"),
                ("user", "must_change_password", "BOOLEAN DEFAULT 0"),
                ("supplier_payment", "payment_method", "TEXT DEFAULT 'Cash'"),
                ("supplier", "advance_balance", "FLOAT DEFAULT 0"),
                ("shop", "active", "BOOLEAN DEFAULT 1"),
                ("shop", "license_expires_at", "DATETIME"),
                ("user", "last_login_at", "DATETIME"),
                ("user", "last_login_ip", "TEXT"),
                ("user", "last_login_device", "TEXT"),
                ("audit_log", "ip_address", "TEXT"),
                ("audit_log", "device_info", "TEXT"),
                ("subscription_payment", "status", "TEXT DEFAULT 'Approved'"),
                ("subscription_payment", "payment_proof", "TEXT"),
            ]

            for table, column, coltype in NEW_COLUMNS:
                if table in existing_tables:
                    existing_cols = [c['name'] for c in inspector.get_columns(table)]
                    if column not in existing_cols:
                        try:
                            db.session.execute(text(f"ALTER TABLE {table} ADD COLUMN {column} {coltype}"))
                            db.session.commit()
                        except Exception:
                            db.session.rollback()
        except Exception as e:
            db.session.rollback()

        # ==========================
        # DEFAULT SHOP
        # Ensure database tables exist
        db.create_all()

        try:
            from mobile_api import register_mobile_api
            import models as m
            register_mobile_api(
                app, db,
                models={
                    'User': m.User, 'Shop': m.Shop, 'Product': m.Product,
                    'Customer': m.Customer, 'Supplier': m.Supplier, 'Invoice': m.Invoice,
                    'InvoiceItem': m.InvoiceItem, 'CustomerPayment': m.CustomerPayment, 'Purchase': m.Purchase, 'Expense': m.Expense, 'Coupon': m.Coupon
                },
                helpers={
                    'bangladesh_time': bangladesh_time,
                    'safe_float': safe_float,
                    'safe_int': safe_int
                }
            )
        except Exception as ex_m:
            print(f"[MOBILE API REGISTRATION WARNING] {ex_m}")


@app.errorhandler(500)
def handle_500_error(e):
    import traceback
    print("=" * 60)
    print("[INTERNAL SERVER ERROR 500 CAUGHT]")
    traceback.print_exc()
    print("=" * 60)
    try:
        db.session.rollback()
    except Exception:
        pass
    
    # If API call, return JSON error
    if request.path.startswith("/api/"):
        return jsonify({"error": "Internal Server Error", "message": str(e)}), 500

    # User friendly error page / redirect with message
    flash("⚠️ সার্ভারে সাময়িক ত্রুটি হয়েছে। পুনরায় চেষ্টা করুন বা ড্যাশবোর্ডে ফিরে যান।", "danger")
    return redirect(url_for("dashboard"))


@app.errorhandler(404)
def handle_404_error(e):
    if request.path.startswith("/api/"):
        return jsonify({"error": "Not Found"}), 404
    flash("❌ কাঙ্ক্ষিত পেজটি খুঁজে পাওয়া যায়নি।", "warning")
    return redirect(url_for("dashboard"))


if __name__ == "__main__":
    initialize_app()
    print("SERVER STARTING...")

    app.run(
        debug=os.environ.get("POS_DEBUG", "0") == "1",
        host="0.0.0.0",
        port=5000
    )