from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime, timedelta
from sqlalchemy.sql import func


db = SQLAlchemy()


# ==============================
# Bangladesh Time Function
# ==============================

def bangladesh_time():
    return datetime.utcnow() + timedelta(hours=6)



# ---------------- USER ----------------

class User(UserMixin, db.Model):
    __table_args__ = (
        db.Index('idx_user_username', 'username'),
        db.Index('idx_user_shop', 'shop_id'),
        db.Index('idx_user_phone', 'phone'),
        db.Index('idx_user_email', 'email'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )


    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=True
    )


    username = db.Column(
        db.String(100),
        nullable=False
    )


    password_hash = db.Column(
        db.String(255),
        nullable=False
    )


    role = db.Column(
        db.String(50),
        default="owner"
    )


    recovery_question = db.Column(
        db.String(255),
        nullable=True
    )


    recovery_answer_hash = db.Column(
        db.String(255),
        nullable=True
    )


    phone = db.Column(
        db.String(20),
        unique=False,
        nullable=True
    )


    email = db.Column(
        db.String(255),
        unique=False,
        nullable=True
    )


    reset_code_hash = db.Column(
        db.String(255),
        nullable=True
    )


    reset_code_expires = db.Column(
        db.DateTime,
        nullable=True
    )

    must_change_password = db.Column(
        db.Boolean,
        default=False,
        nullable=False,
        server_default="0"
    )

    last_login_at = db.Column(
        db.DateTime,
        nullable=True
    )

    last_login_ip = db.Column(
        db.String(45),
        nullable=True
    )

    last_login_device = db.Column(
        db.String(255),
        nullable=True
    )


    shop = db.relationship(
        "Shop",
        back_populates="users"
    )

    def set_password(self, password):

        self.password_hash = generate_password_hash(password)


    def check_password(self, password):

        return check_password_hash(
            self.password_hash,
            password
        )


    def set_recovery_answer(self, answer):
        # Normalize (trim + lowercase) so the answer isn't case/space sensitive
        normalized = (answer or "").strip().lower()
        self.recovery_answer_hash = generate_password_hash(normalized) if normalized else None


    def check_recovery_answer(self, answer):
        if not self.recovery_answer_hash:
            return False
        normalized = (answer or "").strip().lower()
        return check_password_hash(self.recovery_answer_hash, normalized)


    def set_reset_code(self, code, minutes_valid=10):
        self.reset_code_hash = generate_password_hash(code)
        self.reset_code_expires = bangladesh_time() + timedelta(minutes=minutes_valid)


    def check_reset_code(self, code):
        if not self.reset_code_hash or not self.reset_code_expires:
            return False
        if bangladesh_time() > self.reset_code_expires:
            return False
        return check_password_hash(self.reset_code_hash, (code or "").strip())


    def clear_reset_code(self):
        self.reset_code_hash = None
        self.reset_code_expires = None


    @classmethod
    def find_all_by_login_identifier(cls, identifier):
        if not identifier:
            return []
        val = identifier.strip().lower()
        # Authenticate by Email, Phone, or Username matching the identifier
        return cls.query.filter(
            (func.lower(cls.email) == val) |
            (func.lower(cls.phone) == val) |
            (func.lower(cls.username) == val)
        ).all()





# ---------------- PRODUCT ----------------

class Product(db.Model):
    __table_args__ = (
        db.Index('idx_product_name', 'name'),
        db.Index('idx_product_shop', 'shop_id'),
        db.Index('idx_product_barcode', 'barcode'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )
    
    shop = db.relationship(
        "Shop",
        back_populates="products"
    )

    product_code = db.Column(
        db.String(50),
        unique=True,
        nullable=True
    )


    barcode = db.Column(
        db.String(100),
        unique=True,
        nullable=True
    )


    name = db.Column(
        db.String(200),
        nullable=False
    )


    active = db.Column(
        db.Boolean,
        default=True
    )


    category = db.Column(
        db.String(100),
        nullable=True
    )


    # KG, Litre, Piece etc.

    unit = db.Column(
        db.String(50),
        nullable=False,
        default="Piece"
    )


    buy_price = db.Column(
        db.Float,
        nullable=False,
        default=0
    )


    sell_price = db.Column(
        db.Float,
        nullable=False,
        default=0
    )


    stock = db.Column(
        db.Float,
        nullable=False,
        default=0
    )

    # -------- Extended product fields --------
    image = db.Column(db.String(300), nullable=True)
    brand = db.Column(db.String(150), nullable=True)
    batch_number = db.Column(db.String(100), nullable=True)
    expiry_date = db.Column(db.Date, nullable=True)
    reorder_level = db.Column(db.Float, nullable=False, default=5)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    @property
    def days_until_expiry(self):
        if not self.expiry_date:
            return None
        from datetime import date
        today = date.today()
        exp = self.expiry_date
        if hasattr(exp, "date"):
            exp = exp.date()
        return (exp - today).days

    @property
    def expiry_label(self):
        days = self.days_until_expiry
        if days is None:
            return None
        if days < 0:
            return f"Expired ({abs(days)}d ago)"
        elif days == 0:
            return "Expires Today"
        elif days <= 30:
            return f"{days} days to expire"
        else:
            return f"{days}d left"




# ---------------- LICENSE INSTALLATION ----------------

class LicenseInstallation(db.Model):
    """Local licence state. The signed token itself is verified by the POS."""
    id = db.Column(db.Integer, primary_key=True)
    installation_id = db.Column(db.String(64), unique=True, nullable=False)
    device_hash = db.Column(db.String(128), nullable=False)
    trial_started_at = db.Column(db.DateTime, nullable=False, default=bangladesh_time)
    trial_expires_at = db.Column(db.DateTime, nullable=False)
    license_token = db.Column(db.Text, nullable=True)
    last_validated_at = db.Column(db.DateTime, nullable=True)


# ---------------- CUSTOMER ----------------

class Customer(db.Model):
    __table_args__ = (
        db.Index('idx_customer_name', 'name'),
        db.Index('idx_customer_phone', 'phone'),
        db.Index('idx_customer_shop', 'shop_id'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )
    
    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )


    name = db.Column(
        db.String(200),
        nullable=False
    )


    phone = db.Column(
        db.String(20),
        nullable=True
    )


    address = db.Column(
        db.String(300),
        nullable=True
    )

    shop = db.relationship(
        "Shop",
        back_populates="customers"
    )

    created_at = db.Column(
        db.DateTime,
        default=bangladesh_time
    )

    # Wallet balance built from advance payments (reduces future dues)
    advance_balance = db.Column(db.Float, nullable=False, default=0)

    loyalty_points = db.Column(db.Float, nullable=False, default=0)

    payments = db.relationship(
        "CustomerPayment",
        backref="customer",
        cascade="all, delete"
    )




# ---------------- INVOICE ----------------

class Invoice(db.Model):
    __table_args__ = (
        db.Index('idx_invoice_created_at', 'created_at'),
        db.Index('idx_invoice_shop', 'shop_id'),
        db.Index('idx_invoice_customer', 'customer_id'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )
    
    shop = db.relationship(
        "Shop",
        back_populates="invoices"
    )
    
    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )


    invoice_no = db.Column(
        db.String(100),
        unique=True,
        nullable=False
    )


    customer_id = db.Column(
        db.Integer,
        db.ForeignKey("customer.id"),
        nullable=True
    )


    total_amount = db.Column(
        db.Float,
        default=0
    )
    
    
    
    subtotal = db.Column(
        db.Float,
        default=0
    )

    discount_amount = db.Column(
        db.Float,
        default=0
    )

    tax_amount = db.Column(
        db.Float,
        default=0
    )

    paid_amount = db.Column(
        db.Float,
        default=0
    )

    due_amount = db.Column(
        db.Float,
        default=0
    )

    received_amount = db.Column(
        db.Float,
        default=0
    )

    change_amount = db.Column(
        db.Float,
        default=0
    )

    payment_status = db.Column(
        db.String(20),
        default="Paid"
    )

    payment_method = db.Column(
        db.String(30),
        default="Cash"
    )

    note = db.Column(
        db.Text,
        nullable=True
    )

    coupon_code = db.Column(db.String(50), nullable=True)

    # e.g. "Cash:200,bKash:150" when a sale is split across methods
    payment_split = db.Column(db.String(255), nullable=True)

    created_at = db.Column(
        db.DateTime,
        default=bangladesh_time
    )


    items = db.relationship(
        "InvoiceItem",
        backref="invoice",
        lazy=True
    )


    customer = db.relationship(
        "Customer",
        backref="invoices"
    )




# ---------------- INVOICE ITEM ----------------

class InvoiceItem(db.Model):
    __table_args__ = (
        db.Index('idx_invoice_item_invoice', 'invoice_id'),
        db.Index('idx_invoice_item_product', 'product_id'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )


    invoice_id = db.Column(
        db.Integer,
        db.ForeignKey("invoice.id"),
        nullable=False
    )


    product_id = db.Column(
        db.Integer,
        db.ForeignKey("product.id"),
        nullable=False
    )


    quantity = db.Column(
        db.Float,
        nullable=False
    )


    price = db.Column(
        db.Float,
        nullable=False
    )


    total = db.Column(
        db.Float,
        nullable=False
    )


    product = db.relationship("Product", backref="invoice_items")




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

class Purchase(db.Model):
    __table_args__ = (
        db.Index('idx_purchase_shop', 'shop_id'),
        db.Index('idx_purchase_product', 'product_id'),
        db.Index('idx_purchase_supplier', 'supplier_id'),
        db.Index('idx_purchase_created', 'created_at'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )


    product_id = db.Column(
        db.Integer,
        db.ForeignKey("product.id"),
        nullable=False
    )


    supplier_id = db.Column(
        db.Integer,
        db.ForeignKey("supplier.id"),
        nullable=True
    )


    quantity = db.Column(
        db.Float,
        nullable=False
    )


    buy_price = db.Column(
        db.Float,
        nullable=False
    )


    payment_status = db.Column(
        db.String(20),
        default="Due"
    )


    paid_amount = db.Column(
        db.Float,
        default=0
    )


    due_amount = db.Column(
        db.Float,
        default=0
    )


    created_at = db.Column(
        db.DateTime,
        default=bangladesh_time
    )


    # Relationships

    product = db.relationship(
        "Product",
        backref="purchases"
    )


    shop = db.relationship(
        "Shop",
        back_populates="purchases"
    )


    supplier = db.relationship(
        "Supplier",
        back_populates="purchases"
    )
    

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

class Supplier(db.Model):
    __table_args__ = (
        db.Index('idx_supplier_shop', 'shop_id'),
        db.Index('idx_supplier_phone', 'phone'),
        db.Index('idx_supplier_code', 'supplier_code'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )

    supplier_code = db.Column(
        db.String(30),
        unique=True,
        nullable=False
    )

    name = db.Column(
        db.String(200),
        nullable=False
    )

    company = db.Column(
        db.String(200)
    )

    phone = db.Column(
        db.String(30)
    )

    email = db.Column(
        db.String(120)
    )

    address = db.Column(
        db.String(300)
    )

    opening_due = db.Column(
        db.Float,
        default=0
    )

    advance_balance = db.Column(
        db.Float,
        default=0
    )

    active = db.Column(
        db.Boolean,
        default=True
    )

    created_at = db.Column(
        db.DateTime,
        default=bangladesh_time
    )

    shop = db.relationship(
        "Shop",
        back_populates="suppliers"
    )

    purchases = db.relationship(
        "Purchase",
        back_populates="supplier"
    )
    
    payments = db.relationship(
        "SupplierPayment",
        back_populates="supplier",
        cascade="all, delete"
    )

#--------------Supplier Payment------------

class SupplierPayment(db.Model):
    __table_args__ = (
        db.Index('idx_supplier_payment_shop', 'shop_id'),
        db.Index('idx_supplier_payment_supplier', 'supplier_id'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )


    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )


    supplier_id = db.Column(
        db.Integer,
        db.ForeignKey("supplier.id"),
        nullable=False
    )


    amount = db.Column(
        db.Float,
        nullable=False
    )


    note = db.Column(
        db.String(255),
        nullable=True
    )

    payment_method = db.Column(
        db.String(30),
        default="Cash"
    )

    created_at = db.Column(
        db.DateTime,
        default=bangladesh_time
    )


    supplier = db.relationship(
        "Supplier",
        back_populates="payments"
    )


    shop = db.relationship(
        "Shop"
    )


# ---------------- EXPENSE ----------------

# ---------------- EXPENSE ----------------

class Expense(db.Model):
    __table_args__ = (
        db.Index('idx_expense_shop', 'shop_id'),
        db.Index('idx_expense_date', 'date'),
    )

    id = db.Column(
        db.Integer,
        primary_key=True
    )
    
    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )

    title = db.Column(
        db.String(200),
        nullable=False
    )

    category = db.Column(
        db.String(100)
    )

    amount = db.Column(
        db.Float,
        nullable=False
    )

    note = db.Column(
        db.Text
    )

    payment_method = db.Column(
        db.String(30),
        default="Cash"
    )

    date = db.Column(
        db.DateTime,
        default=bangladesh_time
    )

    is_recurring = db.Column(db.Boolean, default=False)

    recurring_frequency = db.Column(db.String(20), nullable=True)

    last_generated = db.Column(db.DateTime, nullable=True)

    shop = db.relationship(
        "Shop",
        back_populates="expenses"
    )


#------------Shop---------------
class Shop(db.Model):

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    shop_name = db.Column(
        db.String(200),
        nullable=False
    )

    owner_name = db.Column(
        db.String(200)
    )

    phone = db.Column(
        db.String(20)
    )

    address = db.Column(
        db.String(300)
    )

    logo = db.Column(
        db.String(300),
        nullable=True
    )

    created_at = db.Column(
        db.DateTime,
        default=bangladesh_time
    )

    active = db.Column(
        db.Boolean,
        default=True,
        nullable=False,
        server_default="1"
    )

    license_expires_at = db.Column(
        db.DateTime,
        nullable=True
    )

    subscription_plan = db.Column(
        db.String(50),
        default="Monthly (৳500)",
        nullable=False,
        server_default="Monthly (৳500)"
    )



    # RELATIONSHIPS

    users = db.relationship(
        "User",
        back_populates="shop"
    )

    products = db.relationship(
        "Product",
        back_populates="shop"
    )

    customers = db.relationship(
        "Customer",
        back_populates="shop"
    )

    invoices = db.relationship(
        "Invoice",
        back_populates="shop"
    )

    purchases = db.relationship(
        "Purchase",
        back_populates="shop"
    )
    
    suppliers = db.relationship(
        "Supplier",
        back_populates="shop"
    )

    expenses = db.relationship(
        "Expense",
        back_populates="shop"
    )
    sales_returns = db.relationship(
        "SalesReturn",
        back_populates="shop"
    )

    coupons = db.relationship("Coupon", backref="shop")
    held_sales = db.relationship("HeldSale", backref="shop")
    stock_adjustments = db.relationship("StockAdjustment", backref="shop")
    purchase_orders = db.relationship("PurchaseOrder", backref="shop")
    purchase_returns = db.relationship("PurchaseReturn", backref="shop")
    customer_payments = db.relationship("CustomerPayment", backref="shop")
    audit_logs = db.relationship("AuditLog", backref="shop", cascade="all, delete-orphan")

    
# ==========================
# SALES RETURN
# ==========================

class SalesReturn(db.Model):

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    shop_id = db.Column(
        db.Integer,
        db.ForeignKey("shop.id"),
        nullable=False
    )

    invoice_id = db.Column(
        db.Integer,
        db.ForeignKey("invoice.id"),
        nullable=False
    )
    return_no = db.Column(
        db.String(50),
        unique=True,
        nullable=False
    )

    return_date = db.Column(
        db.DateTime,
        default=bangladesh_time
    )

    total_return = db.Column(
        db.Float,
        default=0
    )

    reason = db.Column(
        db.String(300)
    )

    invoice = db.relationship(
        "Invoice",
        backref="returns"
    )
    shop = db.relationship(
        "Shop",
        back_populates="sales_returns"
    )

    

class SalesReturnItem(db.Model):

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    return_id = db.Column(
        db.Integer,
        db.ForeignKey("sales_return.id"),
        nullable=False
    )

    product_id = db.Column(
        db.Integer,
        db.ForeignKey("product.id"),
        nullable=False
    )

    quantity = db.Column(
        db.Float,
        nullable=False
    )

    price = db.Column(
        db.Float,
        nullable=False
    )

    total = db.Column(
        db.Float,
        nullable=False
    )

    product = db.relationship(
        "Product"
    )

    sales_return = db.relationship(
        "SalesReturn",
        backref="items"
    )

# ==========================
# ROLES & PERMISSIONS
# ==========================
ROLE_PERMISSIONS = {
    "owner": {"all"},
    "admin": {"all"},
    "manager": {
        "dashboard", "products", "sell", "sales", "purchase",
        "customers", "suppliers", "expenses", "reports",
        "stock", "returns", "coupons", "backups"
    },
    "cashier": {
        "dashboard", "sell", "sales", "returns", "customers"
    },
    "salesman": {
        "dashboard", "sell", "sales"
    },
}


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

class AuditLog(db.Model):
    __table_args__ = (
        db.Index('idx_audit_shop', 'shop_id'),
        db.Index('idx_audit_created_at', 'created_at'),
    )
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=True)
    action = db.Column(db.String(150), nullable=False)
    details = db.Column(db.Text, nullable=True)
    ip_address = db.Column(db.String(45), nullable=True)
    device_info = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    user = db.relationship("User")


def log_action(shop_id, user_id, action, details="", ip_address=None, device_info=None):
    entry = AuditLog(
        shop_id=shop_id,
        user_id=user_id,
        action=action,
        details=details,
        ip_address=ip_address,
        device_info=device_info
    )
    db.session.add(entry)


# ==========================
# DISCOUNT COUPON
# ==========================

class Coupon(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    code = db.Column(db.String(50), nullable=False)
    discount_type = db.Column(db.String(10), default="percent")  # percent / flat
    value = db.Column(db.Float, nullable=False, default=0)
    active = db.Column(db.Boolean, default=True)
    expiry_date = db.Column(db.Date, nullable=True)
    usage_limit = db.Column(db.Integer, nullable=True)
    used_count = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    __table_args__ = (
        db.UniqueConstraint("shop_id", "code", name="uq_shop_coupon_code"),
    )

    def is_valid(self):
        if not self.active:
            return False
        if self.expiry_date and self.expiry_date < datetime.utcnow().date():
            return False
        if self.usage_limit and self.used_count >= self.usage_limit:
            return False
        return True

    def calculate_discount(self, subtotal):
        if self.discount_type == "flat":
            return min(self.value, subtotal)
        return round(subtotal * (self.value / 100.0), 2)


# ==========================
# HELD / DRAFT SALE
# ==========================

class HeldSale(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=True)
    customer_id = db.Column(db.Integer, db.ForeignKey("customer.id"), nullable=True)
    reference = db.Column(db.String(50), nullable=True)
    note = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    customer = db.relationship("Customer")
    items = db.relationship("HeldSaleItem", backref="held_sale", cascade="all, delete")


class HeldSaleItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    held_sale_id = db.Column(db.Integer, db.ForeignKey("held_sale.id"), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey("product.id"), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    price = db.Column(db.Float, nullable=False)

    product = db.relationship("Product")


# ==========================
# CUSTOMER PAYMENTS (Due collection / Advance)
# ==========================

class CustomerPayment(db.Model):
    __table_args__ = (
        db.Index('idx_customer_payment_shop', 'shop_id'),
        db.Index('idx_customer_payment_created_at', 'created_at'),
    )
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    customer_id = db.Column(db.Integer, db.ForeignKey("customer.id"), nullable=False)
    invoice_id = db.Column(db.Integer, db.ForeignKey("invoice.id"), nullable=True)
    amount = db.Column(db.Float, nullable=False)
    payment_type = db.Column(db.String(20), default="due_collection")  # due_collection / advance
    method = db.Column(db.String(30), default="Cash")
    note = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    invoice = db.relationship("Invoice")


# ==========================
# STOCK ADJUSTMENT
# ==========================

class StockAdjustment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey("product.id"), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=True)
    change_qty = db.Column(db.Float, nullable=False)  # + or -
    reason = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    product = db.relationship("Product")
    user = db.relationship("User")


# ==========================
# PURCHASE ORDER (request to supplier, before goods arrive)
# ==========================

class PurchaseOrder(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    supplier_id = db.Column(db.Integer, db.ForeignKey("supplier.id"), nullable=True)
    po_no = db.Column(db.String(50), unique=True, nullable=False)
    status = db.Column(db.String(20), default="Pending")  # Pending / Received / Cancelled
    note = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    supplier = db.relationship("Supplier")
    items = db.relationship("PurchaseOrderItem", backref="purchase_order", cascade="all, delete")


class PurchaseOrderItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    purchase_order_id = db.Column(db.Integer, db.ForeignKey("purchase_order.id"), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey("product.id"), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    buy_price = db.Column(db.Float, nullable=False, default=0)

    product = db.relationship("Product")


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

class PurchaseReturn(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    purchase_id = db.Column(db.Integer, db.ForeignKey("purchase.id"), nullable=False)
    return_no = db.Column(db.String(50), unique=True, nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    amount = db.Column(db.Float, nullable=False, default=0)
    reason = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, default=bangladesh_time)

    purchase = db.relationship("Purchase", backref="returns")


# ==========================
# SAAS SUBSCRIPTION PAYMENT LEDGER (Owner Portal)
# ==========================

class SubscriptionPayment(db.Model):
    __table_args__ = (
        db.Index('idx_sub_payment_shop', 'shop_id'),
        db.Index('idx_sub_payment_status', 'status'),
    )
    id = db.Column(db.Integer, primary_key=True)
    shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"), nullable=False)
    amount_paid = db.Column(db.Float, nullable=False, default=0.0)
    payment_method = db.Column(db.String(50), default="bKash")
    transaction_id = db.Column(db.String(100), nullable=True)
    duration_days = db.Column(db.Integer, nullable=False, default=30)
    old_expiry_date = db.Column(db.DateTime, nullable=True)
    new_expiry_date = db.Column(db.DateTime, nullable=True)
    payment_date = db.Column(db.DateTime, default=bangladesh_time)
    note = db.Column(db.String(255), nullable=True)
    status = db.Column(db.String(20), default="Approved")  # Pending, Approved, Rejected
    payment_proof = db.Column(db.String(255), nullable=True)

    shop = db.relationship("Shop", backref=db.backref("subscription_payments", lazy=True, cascade="all, delete-orphan"))


# ==========================
# HYBRID OFFLINE/ONLINE SYNC QUEUE
# ==========================

class SyncQueue(db.Model):
    __table_args__ = (
        db.Index('idx_sync_queue_synced', 'synced'),
        db.Index('idx_sync_queue_created', 'created_at'),
    )
    id = db.Column(db.Integer, primary_key=True)
    entity_type = db.Column(db.String(50), nullable=False)  # shop_signup, payment_request, login_ping
    endpoint = db.Column(db.String(100), nullable=False)
    payload_json = db.Column(db.Text, nullable=False)
    synced = db.Column(db.Boolean, default=False)
    attempts = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=bangladesh_time)