1import os
  2from datetime import datetime
  3
  4import sqlalchemy.sql
  5from sqlalchemy import (
  6    BigInteger,
  7    Boolean,
  8    Column,
  9    DateTime,
 10    Enum,
 11    Float,
 12    ForeignKey,
 13    Integer,
 14    String,
 15    Table,
 16    UniqueConstraint,
 17    JSON,
 18    and_,
 19    func,
 20)
 21from sqlalchemy.ext.hybrid import hybrid_property
 22from sqlalchemy.orm import relationship
 23from sqlalchemy.sql.expression import text
 24
 25from app.db.base import Base
 26from app.models.node import NodeStatus
 27from app.models.proxy import (
 28    InboundHostALPN,
 29    InboundHostFingerprint,
 30    InboundHostSecurity,
 31    ProxyTypes,
 32)
 33from app.models.user import (
 34    ReminderType,
 35    UserDataUsageResetStrategy,
 36    UserStatus,
 37    UserExpireStrategy,
 38)
 39
 40
 41class Admin(Base):
 42    __tablename__ = "admins"
 43
 44    id = Column(Integer, primary_key=True)
 45    username = Column(String(32), unique=True, index=True)
 46    hashed_password = Column(String(128))
 47    users = relationship("User", back_populates="admin")
 48    created_at = Column(DateTime, default=datetime.utcnow)
 49    is_sudo = Column(Boolean, default=False)
 50    password_reset_at = Column(DateTime)
 51
 52
 53inbounds_services = Table(
 54    "inbounds_services",
 55    Base.metadata,
 56    Column("inbound_id", ForeignKey("inbounds.id"), primary_key=True),
 57    Column("service_id", ForeignKey("services.id"), primary_key=True),
 58)
 59
 60users_services = Table(
 61    "users_services",
 62    Base.metadata,
 63    Column("user_id", ForeignKey("users.id"), primary_key=True),
 64    Column("service_id", ForeignKey("services.id"), primary_key=True),
 65)
 66
 67
 68class Service(Base):
 69    __tablename__ = "services"
 70    id = Column(Integer, primary_key=True)
 71    name = Column(String(64))
 72    users = relationship(
 73        "User", secondary=users_services, back_populates="services"
 74    )
 75    inbounds = relationship(
 76        "Inbound", secondary=inbounds_services, back_populates="services"
 77    )
 78
 79    @property
 80    def inbound_ids(self):
 81        return [inbound.id for inbound in self.inbounds]
 82
 83    @property
 84    def user_ids(self):
 85        return [user.id for user in self.users]
 86
 87
 88class User(Base):
 89    __tablename__ = "users"
 90
 91    id = Column(Integer, primary_key=True)
 92    username = Column(String(32), unique=True, index=True)
 93    key = Column(String(64), unique=True)
 94    activated = Column(Boolean, nullable=False, default=True)
 95    enabled = Column(
 96        Boolean,
 97        nullable=False,
 98        default=True,
 99        server_default=sqlalchemy.sql.true(),
100    )
101    services = relationship(
102        "Service",
103        secondary=users_services,
104        back_populates="users",
105        lazy="joined",
106    )
107    inbounds = relationship(
108        "Inbound",
109        secondary="join(users_services, inbounds_services, inbounds_services.c.service_id == users_services.c.service_id)"
110        ".join(Inbound, Inbound.id == inbounds_services.c.inbound_id)",
111        viewonly=True,
112        distinct_target_key=True,
113    )
114    used_traffic = Column(BigInteger, default=0)
115    lifetime_used_traffic = Column(
116        BigInteger, default=0, server_default="0", nullable=False
117    )
118    traffic_reset_at = Column(DateTime)
119    node_usages = relationship(
120        "NodeUserUsage",
121        back_populates="user",
122        cascade="all,delete,delete-orphan",
123    )
124    notification_reminders = relationship(
125        "NotificationReminder",
126        back_populates="user",
127        cascade="all,delete,delete-orphan",
128    )
129    data_limit = Column(BigInteger)
130    data_limit_reset_strategy = Column(
131        Enum(UserDataUsageResetStrategy),
132        nullable=False,
133        default=UserDataUsageResetStrategy.no_reset,
134    )
135    ip_limit = Column(Integer, nullable=False, default=-1)
136    settings = Column(String(1024))
137    expire_strategy = Column(
138        Enum(UserExpireStrategy),
139        nullable=False,
140        default=UserExpireStrategy.NEVER,
141    )
142    expire_date = Column(DateTime)
143    usage_duration = Column(BigInteger)
144    activation_deadline = Column(DateTime)
145    admin_id = Column(Integer, ForeignKey("admins.id"))
146    admin = relationship("Admin", back_populates="users")
147    sub_updated_at = Column(DateTime)
148    sub_last_user_agent = Column(String(512))
149    created_at = Column(DateTime, default=datetime.utcnow)
150    note = Column(String(500))
151    online_at = Column(DateTime)
152    edit_at = Column(DateTime)
153
154    @property
155    def service_ids(self):
156        return [service.id for service in self.services]
157
158    @hybrid_property
159    def expired(self):
160        if self.expire_strategy == "fixed_date":
161            return self.expire_date < datetime.utcnow()
162        return False
163
164    @expired.expression
165    def expired(cls):
166        return and_(
167            cls.expire_strategy == "fixed_date", cls.expire_date < func.now()
168        )
169
170    @hybrid_property
171    def data_limit_reached(self):
172        if self.data_limit is not None:
173            return self.used_traffic >= self.data_limit
174        return False
175
176    @data_limit_reached.expression
177    def data_limit_reached(cls):
178        return and_(
179            cls.data_limit.isnot(None), cls.used_traffic >= cls.data_limit
180        )
181
182    @hybrid_property
183    def is_active(self):
184        return (
185            self.enabled and not self.expired and not self.data_limit_reached
186        )
187
188    @is_active.expression
189    def is_active(cls):
190        return and_(cls.enabled == True, ~cls.expired, ~cls.data_limit_reached)
191
192    @property
193    def status(self):
194        return UserStatus.ACTIVE if self.is_active else UserStatus.INACTIVE
195
196
197class Inbound(Base):
198    __tablename__ = "inbounds"
199    __table_args__ = (UniqueConstraint("node_id", "tag"),)
200
201    id = Column(Integer, primary_key=True)
202    protocol = Column(Enum(ProxyTypes))
203    tag = Column(String(256), nullable=False)
204    config = Column(String(512), nullable=False)
205    node_id = Column(Integer, ForeignKey("nodes.id"), index=True)
206    node = relationship("Node", back_populates="inbounds")
207    services = relationship(
208        "Service", secondary=inbounds_services, back_populates="inbounds"
209    )
210    hosts = relationship(
211        "InboundHost",
212        back_populates="inbound",
213        cascade="all, delete, delete-orphan",
214    )
215
216    @property
217    def service_ids(self):
218        return [service.id for service in self.services]
219
220
221class InboundHost(Base):
222    __tablename__ = "hosts"
223
224    id = Column(Integer, primary_key=True)
225    remark = Column(String(256), nullable=False)
226    address = Column(String(256), nullable=False)
227    port = Column(Integer)
228    path = Column(String(256))
229    sni = Column(String(1024))
230    host = Column(String(1024))
231    security = Column(
232        Enum(InboundHostSecurity),
233        nullable=False,
234        default=InboundHostSecurity.inbound_default,
235    )
236    alpn = Column(
237        Enum(InboundHostALPN),
238        nullable=False,
239        default=InboundHostSecurity.none,
240        server_default=InboundHostSecurity.none.name,
241    )
242    fingerprint = Column(
243        Enum(InboundHostFingerprint),
244        nullable=False,
245        default=InboundHostSecurity.none,
246        server_default=InboundHostSecurity.none.name,
247    )
248
249    mux = Column(
250        Boolean,
251        default=False,
252        nullable=False,
253        server_default=sqlalchemy.sql.true(),
254    )
255    fragment = Column(JSON())
256
257    inbound_id = Column(Integer, ForeignKey("inbounds.id"), nullable=False)
258    inbound = relationship("Inbound", back_populates="hosts")
259    allowinsecure = Column(Boolean, default=False)
260    is_disabled = Column(Boolean, default=False)
261
262
263class System(Base):
264    __tablename__ = "system"
265
266    id = Column(Integer, primary_key=True)
267    uplink = Column(BigInteger, default=0)
268    downlink = Column(BigInteger, default=0)
269
270
271class JWT(Base):
272    __tablename__ = "jwt"
273
274    id = Column(Integer, primary_key=True)
275    secret_key = Column(
276        String(64), nullable=False, default=lambda: os.urandom(32).hex()
277    )
278
279
280class TLS(Base):
281    __tablename__ = "tls"
282
283    id = Column(Integer, primary_key=True)
284    key = Column(String(4096), nullable=False)
285    certificate = Column(String(2048), nullable=False)
286
287
288class Node(Base):
289    __tablename__ = "nodes"
290    __table_args__ = (UniqueConstraint("address", "port"),)
291    id = Column(Integer, primary_key=True)
292    name = Column(String(256), unique=True)
293    connection_backend = Column(String(32))
294    address = Column(String(256))
295    port = Column(Integer)
296    xray_version = Column(String(32))
297    inbounds = relationship(
298        "Inbound", back_populates="node", cascade="all, delete"
299    )
300    status = Column(
301        Enum(NodeStatus), nullable=False, default=NodeStatus.unhealthy
302    )
303    last_status_change = Column(DateTime, default=datetime.utcnow)
304    message = Column(String(1024))
305    created_at = Column(DateTime, default=datetime.utcnow)
306    uplink = Column(BigInteger, default=0)
307    downlink = Column(BigInteger, default=0)
308    user_usages = relationship(
309        "NodeUserUsage",
310        back_populates="node",
311        cascade="all, delete, delete-orphan",
312    )
313    usages = relationship(
314        "NodeUsage",
315        back_populates="node",
316        cascade="all, delete, delete-orphan",
317    )
318    usage_coefficient = Column(
319        Float, nullable=False, server_default=text("1.0"), default=1
320    )
321
322    @property
323    def inbound_ids(self):
324        return [inbound.id for inbound in self.inbounds]
325
326
327class NodeUserUsage(Base):
328    __tablename__ = "node_user_usages"
329    __table_args__ = (UniqueConstraint("created_at", "user_id", "node_id"),)
330
331    id = Column(Integer, primary_key=True)
332    created_at = Column(DateTime, nullable=False)  # one hour per record
333    user_id = Column(Integer, ForeignKey("users.id"))
334    user = relationship("User", back_populates="node_usages")
335    node_id = Column(Integer, ForeignKey("nodes.id"))
336    node = relationship("Node", back_populates="user_usages")
337    used_traffic = Column(BigInteger, default=0)
338
339
340class NodeUsage(Base):
341    __tablename__ = "node_usages"
342    __table_args__ = (UniqueConstraint("created_at", "node_id"),)
343
344    id = Column(Integer, primary_key=True)
345    created_at = Column(DateTime, nullable=False)  # one hour per record
346    node_id = Column(Integer, ForeignKey("nodes.id"))
347    node = relationship("Node", back_populates="usages")
348    uplink = Column(BigInteger, default=0)
349    downlink = Column(BigInteger, default=0)
350
351
352class NotificationReminder(Base):
353    __tablename__ = "notification_reminders"
354
355    id = Column(Integer, primary_key=True)
356    user_id = Column(Integer, ForeignKey("users.id"))
357    user = relationship("User", back_populates="notification_reminders")
358    type = Column(Enum(ReminderType), nullable=False)
359    expires_at = Column(DateTime)
360    created_at = Column(DateTime, default=datetime.utcnow)
361
362
363class Settings(Base):
364    __tablename__ = "settings"
365
366    id = Column(Integer, primary_key=True, server_default=text("0"))
367    subscription = Column(JSON, nullable=False)
368    telegram = Column(JSON)