database/models.py

import uuid
from datetime import datetime
from sqlalchemy import Column, String, Boolean, DateTime, DECIMAL, ForeignKey, Integer, Text, BigInteger
from sqlalchemy.dialects.postgresql import UUID, JSONB, INET, ARRAY
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    password_hash = Column(String(255))
    name = Column(String(100))
    avatar_url = Column(Text)
    balance = Column(DECIMAL(15, 4), default=0.0000)
    status = Column(String(50), default="active")
    spent = Column(DECIMAL(15, 4), default=0.0000)
    role = Column(String(20), default="USER")
    api_key = Column(String(100), unique=True)
    referral_code = Column(String(20), unique=True)
    referred_by = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    timezone = Column(String(50), default="UTC")
    currency = Column(String(3), default="USD")
    locale = Column(String(5), default="en")
    is_active = Column(Boolean, default=True)
    login_attempts = Column(Integer, default=0)
    locked_until = Column(DateTime(timezone=True))
    oauth_provider = Column(String(20))
    oauth_provider_id = Column(String(255))
    last_login_at = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    orders = relationship("Order", back_populates="user")
    transactions = relationship("Transaction", back_populates="user")
    tickets = relationship("Ticket", back_populates="user")

class Category(Base):
    __tablename__ = "categories"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(150), nullable=False)
    slug = Column(String(150), unique=True, nullable=False)
    description = Column(Text)
    icon = Column(String(50))
    sort_order = Column(Integer, default=0)
    is_active = Column(Boolean, default=True)
    parent_id = Column(Integer, ForeignKey("categories.id"))
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    
    services = relationship("Service", back_populates="category")

class ApiProvider(Base):
    __tablename__ = "api_providers"

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    url = Column(String(255), nullable=False)
    api_key = Column(String(255), nullable=False)
    balance = Column(DECIMAL(15, 4), default=0.0000)
    currency = Column(String(3), default="USD")
    is_active = Column(Boolean, default=True)
    last_sync_at = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)

    services = relationship("Service", back_populates="api_provider")
    orders = relationship("Order", back_populates="api_provider")

class Service(Base):
    __tablename__ = "services"
    
    id = Column(Integer, primary_key=True, index=True)
    category_id = Column(Integer, ForeignKey("categories.id"))
    name = Column(String(500), nullable=False)
    slug = Column(String(500))
    type = Column(String(50), default="Default")
    price = Column(DECIMAL(15, 6), nullable=False)
    min_quantity = Column(Integer, nullable=False)
    max_quantity = Column(Integer, nullable=False)
    refill_enabled = Column(Boolean, default=False)
    refill_days = Column(Integer, default=0)
    speed = Column(String(100))
    average_time = Column(String(100))
    description = Column(Text)
    api_provider_id = Column(Integer, ForeignKey("api_providers.id"))
    api_service_id = Column(String(100))
    is_drip_feed = Column(Boolean, default=False)
    is_active = Column(Boolean, default=True)
    sort_order = Column(Integer, default=0)
    total_orders = Column(Integer, default=0)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)
    
    category = relationship("Category", back_populates="services")
    api_provider = relationship("ApiProvider", back_populates="services")
    orders = relationship("Order", back_populates="service")

class Order(Base):
    __tablename__ = "orders"
    
    id = Column(Integer, primary_key=True)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    service_id = Column(Integer, ForeignKey("services.id"))
    link = Column(String(2000), nullable=False)
    quantity = Column(Integer, nullable=False)
    charge = Column(DECIMAL(15, 4), nullable=False)
    status = Column(String(50), default="Pending")
    start_count = Column(Integer, default=0)
    remains = Column(Integer, default=0)
    api_provider_id = Column(Integer, ForeignKey("api_providers.id"))
    api_order_id = Column(String(100))
    is_drip_feed = Column(Boolean, default=False)
    drip_feed_interval = Column(Integer)
    drip_feed_quantity = Column(Integer)
    refill_count = Column(Integer, default=0)
    last_refill_at = Column(DateTime(timezone=True))
    ip_address = Column(INET)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)

    user = relationship("User", back_populates="orders")
    service = relationship("Service", back_populates="orders")
    api_provider = relationship("ApiProvider", back_populates="orders")
    tickets = relationship("Ticket", back_populates="order")

class Transaction(Base):
    __tablename__ = "transactions"
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    amount = Column(DECIMAL(15, 4), nullable=False)
    type = Column(String(30), nullable=False)
    payment_method = Column(String(50), nullable=False)
    payment_gateway_id = Column(String(255))
    status = Column(String(20), default="Pending")
    balance_before = Column(DECIMAL(15, 4))
    balance_after = Column(DECIMAL(15, 4))
    note = Column(Text)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)

    user = relationship("User", back_populates="transactions")

class Ticket(Base):
    __tablename__ = "tickets"
    
    id = Column(Integer, primary_key=True)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    subject = Column(String(200), nullable=False)
    status = Column(String(20), default="Pending")
    priority = Column(String(10), default="Normal")
    order_id = Column(Integer, ForeignKey("orders.id"))
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)

    user = relationship("User", back_populates="tickets")
    order = relationship("Order", back_populates="tickets")
    messages = relationship("TicketMessage", back_populates="ticket")

class TicketMessage(Base):
    __tablename__ = "ticket_messages"
    
    id = Column(Integer, primary_key=True)
    ticket_id = Column(Integer, ForeignKey("tickets.id"))
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    message = Column(Text, nullable=False)
    is_admin = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)

    ticket = relationship("Ticket", back_populates="messages")
    user = relationship("User")

class Referral(Base):
    __tablename__ = "referrals"
    
    id = Column(Integer, primary_key=True)
    referrer_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    referred_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    commission_rate = Column(DECIMAL(5, 2), default=5.00)
    total_earned = Column(DECIMAL(15, 4), default=0.0000)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)

class BlogPost(Base):
    __tablename__ = "blog_posts"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    slug = Column(String(300), unique=True, nullable=False)
    content = Column(Text, nullable=False)
    excerpt = Column(Text)
    featured_image_url = Column(Text)
    author_name = Column(String(100))
    tags = Column(ARRAY(String))
    meta_title = Column(String(300))
    meta_description = Column(String(500))
    is_published = Column(Boolean, default=False)
    published_at = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)

class SystemSetting(Base):
    __tablename__ = "system_settings"
    
    key = Column(String(100), primary_key=True)
    value = Column(Text, nullable=False)
    description = Column(Text)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)

class AuditLog(Base):
    __tablename__ = "audit_logs"
    
    id = Column(BigInteger, primary_key=True)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    action = Column(String(100), nullable=False)
    entity_type = Column(String(50))
    entity_id = Column(String(100))
    old_value = Column(JSONB)
    new_value = Column(JSONB)
    ip_address = Column(INET)
    user_agent = Column(Text)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)

    user = relationship("User")