from .extensions import db
from datetime import datetime

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(200), nullable=False)
    role = db.Column(db.String(20), default='customer') # 'admin' for POS, 'customer' for Web
    phone = db.Column(db.String(20), nullable=True)
    address = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)


class VerificationCode(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    channel = db.Column(db.String(20), nullable=False)  # email | phone
    destination = db.Column(db.String(255), nullable=False, index=True)
    purpose = db.Column(db.String(50), nullable=False, default='registration')
    code_hash = db.Column(db.String(64), nullable=False)
    expires_at = db.Column(db.DateTime, nullable=False)
    attempt_count = db.Column(db.Integer, default=0)
    verified_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    products = db.relationship('Product', backref='category', lazy=True)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)
    
    purchase_price = db.Column(db.Float, default=0.0)
    price = db.Column(db.Float, nullable=False)
    stock = db.Column(db.Integer, default=0)
    min_stock = db.Column(db.Integer, default=5)
    
    barcode = db.Column(db.String(100), unique=True, nullable=True)
    supplier = db.Column(db.String(200), nullable=True)
    expiry_date = db.Column(db.Date, nullable=True)
    
    # 📸 NEW: Image save karne ke liye column (Default image ka naam 'default.png' rakha hai)
    image_file = db.Column(db.String(255), nullable=True, default='default.png')
    
    # 🏷️ Advanced Filtering Columns
    brand = db.Column(db.String(100), nullable=True)
    discount_price = db.Column(db.Float, nullable=True, default=0.0) # If > 0, item is on sale

    # 🏷️ Website Section Controls
    show_in_recent = db.Column(db.Boolean, default=False)
    show_in_gifts = db.Column(db.Boolean, default=False)
    show_in_deals = db.Column(db.Boolean, default=False)
    show_in_video = db.Column(db.Boolean, default=False)
    
    # ⚡ Flash Sale Features
    flash_sale_end = db.Column(db.DateTime, nullable=True)
    flash_sale_video = db.Column(db.String(500), nullable=True)
    physical_sale_video = db.Column(db.String(500), nullable=True)
    
    last_updated = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Order(db.Model):
    id = db.Column(db.String(50), primary_key=True) # e.g. INV-1001
    date = db.Column(db.DateTime, default=datetime.utcnow)
    
    customer_name = db.Column(db.String(150), default="Walk-in")
    customer_phone = db.Column(db.String(50), default="")
    customer_email = db.Column(db.String(100), default="")
    
    subtotal = db.Column(db.Float, nullable=False, default=0.0)
    discount = db.Column(db.Float, default=0.0)
    tax = db.Column(db.Float, default=0.0)
    final_total = db.Column(db.Float, nullable=False)
    
    payment_method = db.Column(db.String(50), default="Cash")
    status = db.Column(db.String(50), default="Completed") # Completed, Refunded, Online
    cashier = db.Column(db.String(100), nullable=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
    
    source = db.Column(db.String(50), default="POS") # Identify POS vs Web
    
    # 📱 SMS Tracking
    sms_status = db.Column(db.String(100), default="None") # None, Placed_Sent, Approved_Sent, Error
    last_sms_date = db.Column(db.DateTime, nullable=True)
    
    items = db.relationship('OrderItem', backref='order', lazy=True)

class OrderItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # FIX: order_id ab String hai kyunke Order.id 'INV-100X' format mein hai
    order_id = db.Column(db.String(50), db.ForeignKey('order.id'), nullable=False)
    product_name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Float, nullable=False)
    quantity = db.Column(db.Integer, nullable=False)

class Expense(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    category = db.Column(db.String(100))
    date = db.Column(db.Date, nullable=False)
    method = db.Column(db.String(50))
    notes = db.Column(db.Text, nullable=True)

class ShopSettings(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shop_name = db.Column(db.String(100), default="SwiftCart Store")
    address = db.Column(db.String(200), default="Main Market, City")
    phone = db.Column(db.String(50), default="0300-0000000")
    receipt_footer = db.Column(db.String(100), default="Thank you for shopping with us!")

class KhataAccount(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    customer_name = db.Column(db.String(150), nullable=False)
    phone = db.Column(db.String(50), unique=True, nullable=False) # Will link with User.phone
    balance = db.Column(db.Float, default=0.0) # +ve means customer owes store
    contact_person = db.Column(db.String(150), nullable=True)
    whatsapp_number = db.Column(db.String(50), nullable=True)
    address = db.Column(db.Text, nullable=True)
    credit_limit = db.Column(db.Float, default=0.0)
    category = db.Column(db.String(100), nullable=True)

class KhataTransaction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    khata_id = db.Column(db.Integer, db.ForeignKey('khata_account.id'))
    amount = db.Column(db.Float, nullable=False)
    t_type = db.Column(db.String(50)) # 'DEBIT' (Udhaar taken), 'CREDIT' (Payment given)
    description = db.Column(db.String(255))
    date = db.Column(db.DateTime, default=datetime.utcnow)
    
    khata = db.relationship('KhataAccount', backref='transactions', lazy=True)

class Coupon(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(50), unique=True, nullable=False)
    discount = db.Column(db.Float, nullable=False)
    active = db.Column(db.Boolean, default=True)

class Review(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    rating = db.Column(db.Integer, nullable=False, default=5)
    comment = db.Column(db.Text, nullable=True)
    date_posted = db.Column(db.DateTime, default=datetime.utcnow)
    
    product = db.relationship('Product', backref='reviews', lazy=True)
    user = db.relationship('User', backref='reviews', lazy=True)

class Wishlist(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    date_added = db.Column(db.DateTime, default=datetime.utcnow)
    
    product = db.relationship('Product', backref='wishlisted_by', lazy=True)
    user = db.relationship('User', backref='wishlist', lazy=True)

# ================= FINANCES MODULES =================
class BankAccount(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150), nullable=False)
    contact_person = db.Column(db.String(150))
    phone = db.Column(db.String(50))
    address = db.Column(db.Text)
    current_credit = db.Column(db.Float, default=0.0)
    
class Company(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150), nullable=False)
    contact_person = db.Column(db.String(150))
    email = db.Column(db.String(150))
    phone = db.Column(db.String(50))

class FinancePayment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    party_type = db.Column(db.String(50)) # 'Bank', 'Customer'
    party_name = db.Column(db.String(150)) # the selected bank or customer name
    txn_type = db.Column(db.String(50)) # 'Receive Payment', 'Pay Payment'
    amount = db.Column(db.Float, nullable=False)
    method = db.Column(db.String(50)) # 'Cash', 'Bank Transfer', etc
    bank_name = db.Column(db.String(150), nullable=True) # if via bank
    description = db.Column(db.Text)
    date = db.Column(db.DateTime, default=datetime.utcnow)

class DailyProfit(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    purchase_amount = db.Column(db.Float, default=0.0)
    sale_amount = db.Column(db.Float, default=0.0)
    profit = db.Column(db.Float, default=0.0)
    description = db.Column(db.Text)
    date = db.Column(db.DateTime, default=datetime.utcnow)

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150), nullable=False)
    phone = db.Column(db.String(50))
    salary = db.Column(db.Float, default=0.0)
    hire_date = db.Column(db.String(50))
    attendances = db.relationship('Attendance', backref='employee', lazy=True)

class Attendance(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False)
    date = db.Column(db.String(50))
    status = db.Column(db.String(20), default='Present')  # Present, Absent, Half-Day, Leave
    earning_amount = db.Column(db.Float, default=0.0)
    description = db.Column(db.Text, default='')

class BackupSetting(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    enable_daily = db.Column(db.Boolean, default=False)
    backup_time = db.Column(db.String(10), default="02:00")
    backup_dir = db.Column(db.String(300), default="C:\\Backups")
    instant_crit = db.Column(db.Boolean, default=False)
    on_close = db.Column(db.Boolean, default=True)
    last_backup = db.Column(db.String(100), default="Never")

class PurchaseOrder(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    vendor_name = db.Column(db.String(150))
    product_name = db.Column(db.String(150))
    payment_method = db.Column(db.String(50), default="Cash")
    order_date = db.Column(db.String(50))
    total_cost = db.Column(db.Float, default=0.0)


class PosSyncLog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    synced_at = db.Column(db.DateTime, default=datetime.utcnow)
    sales_uploaded = db.Column(db.Integer, default=0)
    expenses_uploaded = db.Column(db.Integer, default=0)
    source_terminal = db.Column(db.String(100), default="Swift_Cart")
    details = db.Column(db.Text, nullable=True)
    status = db.Column(db.String(50), default="Pending")
    is_received = db.Column(db.Boolean, default=False)