1import os
  2from datetime import datetime
  3
  4from sqlalchemy import (
  5    BigInteger,
  6    Boolean,
  7    Column,
  8    DateTime,
  9    Enum,
 10    Float,
 11    ForeignKey,
 12    Integer,
 13    String,
 14    Table,
 15    UniqueConstraint,
 16)
 17from sqlalchemy.orm import relationship
 18from sqlalchemy.sql.expression import text
 19
 20from app.db.base import Base
 21from app.models.node import NodeStatus
 22from app.models.proxy import (
 23    InboundHostALPN,
 24    InboundHostFingerprint,
 25    InboundHostSecurity,
 26    ProxyTypes,
 27)
 28from app.models.user import ReminderType, UserDataLimitResetStrategy, UserStatus
 29
 30
 31class Admin(Base):
 32    __tablename__ = "admins"
 33
 34    id = Column(Integer, primary_key=True)
 35    username = Column(String(32), unique=True, index=True)
 36    hashed_password = Column(String(128))
 37    users = relationship("User", back_populates="admin")
 38    created_at = Column(DateTime, default=datetime.utcnow)
 39    is_sudo = Column(Boolean, default=False)
 40    password_reset_at = Column(DateTime)
 41
 42
 43inbounds_services = Table(
 44    "inbounds_services",
 45    Base.metadata,
 46    Column("inbound_id", ForeignKey("inbounds.id"), primary_key=True),
 47    Column("service_id", ForeignKey("services.id"), primary_key=True),
 48)
 49
 50users_services = Table(
 51    "users_services",
 52    Base.metadata,
 53    Column("user_id", ForeignKey("users.id"), primary_key=True),
 54    Column("service_id", ForeignKey("services.id"), primary_key=True),
 55)
 56
 57
 58class Service(Base):
 59    __tablename__ = "services"
 60    id = Column(Integer, primary_key=True)
 61    name = Column(String(64))
 62    # modifications = Column(String(1024))
 63    users = relationship("User", secondary=users_services, back_populates="services")
 64    inbounds = relationship(
 65        "Inbound", secondary=inbounds_services, back_populates="services"
 66    )
 67
 68
 69class User(Base):
 70    __tablename__ = "users"
 71
 72    id = Column(Integer, primary_key=True)
 73    username = Column(String(32), unique=True, index=True)
 74    key = Column(String(64), unique=True)
 75    services = relationship(
 76        "Service", secondary=users_services, back_populates="users", lazy="joined"
 77    )
 78    inbounds = relationship(
 79        "Inbound",
 80        secondary="join(users_services, inbounds_services, inbounds_services.c.service_id == users_services.c.service_id)"
 81        ".join(Inbound, Inbound.id == inbounds_services.c.inbound_id)",
 82        viewonly=True,
 83        distinct_target_key=True,
 84        lazy="joined",
 85    )
 86    # proxies = relationship("Proxy", back_populates="user", cascade="all, delete-orphan")
 87    status = Column(Enum(UserStatus), nullable=False, default=UserStatus.active)
 88    used_traffic = Column(BigInteger, default=0)
 89    node_usages = relationship(
 90        "NodeUserUsage",
 91        back_populates="user",
 92        cascade="all,delete,delete-orphan",
 93        lazy="joined",
 94    )
 95    notification_reminders = relationship(
 96        "NotificationReminder",
 97        back_populates="user",
 98        cascade="all,delete,delete-orphan",
 99    )
100    data_limit = Column(BigInteger)
101    data_limit_reset_strategy = Column(
102        Enum(UserDataLimitResetStrategy),
103        nullable=False,
104        default=UserDataLimitResetStrategy.no_reset,
105    )
106    ip_limit = Column(Integer, nullable=False, default=-1)
107    settings = Column(String)
108    usage_logs = relationship(
109        "UserUsageResetLogs", back_populates="user", lazy="joined"
110    )
111    expire = Column(DateTime)
112    admin_id = Column(Integer, ForeignKey("admins.id"))
113    admin = relationship("Admin", back_populates="users")
114    sub_updated_at = Column(DateTime)
115    sub_last_user_agent = Column(String(512))
116    created_at = Column(DateTime, default=datetime.utcnow)
117    note = Column(String(500))
118    online_at = Column(DateTime)
119    on_hold_expire_duration = Column(BigInteger)
120    on_hold_timeout = Column(DateTime)
121    edit_at = Column(DateTime)
122
123    @property
124    def lifetime_used_traffic(self):
125        return (
126            sum([log.used_traffic_at_reset for log in self.usage_logs])
127            + self.used_traffic
128        )
129
130    @property
131    def last_traffic_reset_time(self):
132        return self.usage_logs[-1].reset_at if self.usage_logs else self.created_at
133
134
135class UserUsageResetLogs(Base):
136    __tablename__ = "user_usage_logs"
137
138    id = Column(Integer, primary_key=True)
139    user_id = Column(Integer, ForeignKey("users.id"))
140    user = relationship("User", back_populates="usage_logs")
141    used_traffic_at_reset = Column(BigInteger, nullable=False)
142    reset_at = Column(DateTime, default=datetime.utcnow)
143
144
145class Inbound(Base):
146    __tablename__ = "inbounds"
147    __table_args__ = (UniqueConstraint("node_id", "tag"),)
148
149    id = Column(Integer, primary_key=True)
150    protocol = Column(Enum(ProxyTypes))
151    tag = Column(String(256), nullable=False)
152    config = Column(String(), nullable=False)
153    node_id = Column(Integer, ForeignKey("nodes.id"), index=True)
154    node = relationship("Node", back_populates="inbounds")
155    services = relationship(
156        "Service", secondary=inbounds_services, back_populates="inbounds"
157    )
158    hosts = relationship(
159        "InboundHost", back_populates="inbound", cascade="all, delete, delete-orphan"
160    )
161
162
163class InboundHost(Base):
164    __tablename__ = "hosts"
165
166    id = Column(Integer, primary_key=True)
167    remark = Column(String(256), nullable=False)
168    address = Column(String(256), nullable=False)
169    port = Column(Integer)
170    path = Column(String(256))
171    sni = Column(String(256))
172    host = Column(String(256))
173    security = Column(
174        Enum(InboundHostSecurity),
175        nullable=False,
176        default=InboundHostSecurity.inbound_default,
177    )
178    alpn = Column(
179        Enum(InboundHostALPN),
180        nullable=False,
181        default=InboundHostSecurity.none,
182        server_default=InboundHostSecurity.none.name,
183    )
184    fingerprint = Column(
185        Enum(InboundHostFingerprint),
186        nullable=False,
187        default=InboundHostSecurity.none,
188        server_default=InboundHostSecurity.none.name,
189    )
190
191    inbound_id = Column(Integer, ForeignKey("inbounds.id"), nullable=False)
192    inbound = relationship("Inbound", back_populates="hosts")
193    allowinsecure = Column(Boolean, default=False)
194    is_disabled = Column(Boolean, default=False)
195
196
197class System(Base):
198    __tablename__ = "system"
199
200    id = Column(Integer, primary_key=True)
201    uplink = Column(BigInteger, default=0)
202    downlink = Column(BigInteger, default=0)
203
204
205class JWT(Base):
206    __tablename__ = "jwt"
207
208    id = Column(Integer, primary_key=True)
209    secret_key = Column(
210        String(64), nullable=False, default=lambda: os.urandom(32).hex()
211    )
212
213
214class TLS(Base):
215    __tablename__ = "tls"
216
217    id = Column(Integer, primary_key=True)
218    key = Column(String(4096), nullable=False)
219    certificate = Column(String(2048), nullable=False)
220
221
222class Node(Base):
223    __tablename__ = "nodes"
224    __table_args__ = (UniqueConstraint("address", "port"),)
225    id = Column(Integer, primary_key=True)
226    name = Column(String(256), unique=True)
227    connection_backend = Column(String())
228    address = Column(String(256))
229    port = Column(Integer)
230    xray_version = Column(String(32))
231    inbounds = relationship("Inbound", back_populates="node", cascade="all, delete")
232    status = Column(Enum(NodeStatus), nullable=False, default=NodeStatus.unhealthy)
233    last_status_change = Column(DateTime, default=datetime.utcnow)
234    message = Column(String(1024))
235    created_at = Column(DateTime, default=datetime.utcnow)
236    uplink = Column(BigInteger, default=0)
237    downlink = Column(BigInteger, default=0)
238    user_usages = relationship(
239        "NodeUserUsage", back_populates="node", cascade="all, delete, delete-orphan"
240    )
241    usages = relationship(
242        "NodeUsage", back_populates="node", cascade="all, delete, delete-orphan"
243    )
244    usage_coefficient = Column(
245        Float, nullable=False, server_default=text("1.0"), default=1
246    )
247
248
249class NodeUserUsage(Base):
250    __tablename__ = "node_user_usages"
251    __table_args__ = (UniqueConstraint("created_at", "user_id", "node_id"),)
252
253    id = Column(Integer, primary_key=True)
254    created_at = Column(DateTime, nullable=False)  # one hour per record
255    user_id = Column(Integer, ForeignKey("users.id"))
256    user = relationship("User", back_populates="node_usages")
257    node_id = Column(Integer, ForeignKey("nodes.id"))
258    node = relationship("Node", back_populates="user_usages")
259    used_traffic = Column(BigInteger, default=0)
260
261
262class NodeUsage(Base):
263    __tablename__ = "node_usages"
264    __table_args__ = (UniqueConstraint("created_at", "node_id"),)
265
266    id = Column(Integer, primary_key=True)
267    created_at = Column(DateTime, nullable=False)  # one hour per record
268    node_id = Column(Integer, ForeignKey("nodes.id"))
269    node = relationship("Node", back_populates="usages")
270    uplink = Column(BigInteger, default=0)
271    downlink = Column(BigInteger, default=0)
272
273
274class NotificationReminder(Base):
275    __tablename__ = "notification_reminders"
276
277    id = Column(Integer, primary_key=True)
278    user_id = Column(Integer, ForeignKey("users.id"))
279    user = relationship("User", back_populates="notification_reminders")
280    type = Column(Enum(ReminderType), nullable=False)
281    expires_at = Column(DateTime)
282    created_at = Column(DateTime, default=datetime.utcnow)