graph TB
    subgraph "应用代码 (Python)"
        A["class User(db.Model):<br/>id = Column(Integer)<br/>name = Column(String)"]
        B["user = User(name='Alice')<br/>db.session.add(user)<br/>db.session.commit()"]
        C["User.query.filter_by(name='Alice')<br/>.order_by(User.id.desc())<br/>.all()"]
    end
    
    subgraph "SQLAlchemy ORM"
        D["ORM 层<br/>对象 ↔ 关系映射<br/>会话管理<br/>变更跟踪"]
    end
    
    subgraph "SQLAlchemy Core"
        E["SQL 表达式语言<br/>schema/types/sql<br/>engine/connection"]
    end
    
    subgraph "数据库驱动 (Dialect)"
        F["SQLite<br/>Dialect"]
        G["PostgreSQL<br/>Dialect"]
        H["MySQL<br/>Dialect"]
    end
    
    subgraph "数据库"
        I["📁 SQLite<br/>app.db"]
        J["🐘 PostgreSQL"]
        K["🐬 MySQL"]
    end
    
    A --> D
    B --> D
    C --> D
    D --> E
    E --> F
    E --> G
    E --> H
    F --> I
    G --> J
    H --> K
    
    style D fill:#4A90D9,color:#fff
    style E fill:#7B68EE,color:#fff

stateDiagram-v2
    [*] --> Transient: User()
    
    Transient --> Pending: db.session.add(user)
    
    Pending --> Flushed: db.session.flush()
    Pending --> Detached: db.session.expunge(user)
    
    Flushed --> Committed: db.session.commit()
    Flushed --> Pending: 事务回滚
    
    Committed --> Loaded: user.todos
    
    Loaded --> Expired: db.session.expire(user)
    Expired --> Loaded: user.name (触发重新查询)
    
    Loaded --> Deleted: db.session.delete(user)
    Deleted --> Detached: db.session.commit()
    
    Loaded --> Detached: 会话关闭
    Detached --> Loaded: db.session.merge(user)

graph LR
    subgraph "JWT = Header.Payload.Signature"
        H["Header<br/>{<br/>&nbsp;"alg": "HS256",<br/>&nbsp;"typ": "JWT"<br/>}"]
        P["Payload<br/>{<br/>&nbsp;"sub": 1,<br/>&nbsp;"iat": 1700000000,<br/>&nbsp;"exp": 1700086400<br/>}"]
        S["Signature<br/>HMACSHA256(<br/>&nbsp;base64(Header) + '.' +<br/>&nbsp;base64(Payload),<br/>&nbsp;SECRET_KEY<br/>)"]
    end
    
    H -->|Base64URL| H64["eyJhbGciOiJIUzI1NiIs..."]
    P -->|Base64URL| P64["eyJzdWIiOjEsImlhdCI6MTcw..."]
    S --> S64["dGhpcyBpcyB0aGUgc2lnbmF0..."]
    
    H64 --> Token["eyJhbGciOiJIUzI1NiIs...<br/>.<br/>eyJzdWIiOjEsImlhdCI6MTcw...<br/>.<br/>dGhpcyBpcyB0aGUgc2lnbmF0..."]
    P64 --> Token
    S64 --> Token
    
    style Token fill:#f0f0f0,stroke:#333,stroke-width:2px

sequenceDiagram
    participant C as 客户端
    participant API as Flask API
    participant JWT as JWT 模块
    participant DB as SQLAlchemy
    
    Note over C,DB: ─── 注册流程 ───
    
    C->>API: POST /api/auth/register<br/>{username, password}
    API->>API: 参数校验
    API->>DB: 检查用户名是否已存在
    DB-->>API: 不存在
    API->>API: generate_password_hash(password)
    API->>DB: INSERT user
    DB-->>API: 用户已创建
    API->>JWT: create_token(user_id)
    JWT-->>API: "eyJhbGciOiJIUzI1NiIs..."
    API-->>C: 201 Created<br/>{token, user}
    
    Note over C,DB: ─── 登录流程 ───
    
    C->>API: POST /api/auth/login<br/>{username, password}
    API->>DB: SELECT user WHERE username=?
    DB-->>API: User 对象
    API->>API: check_password_hash(hash, password)
    Note over API: 验证通过 ✅
    API->>JWT: create_token(user_id)
    JWT-->>API: "eyJhbGciOiJIUzI1NiIs..."
    API-->>C: 200 OK<br/>{token, user}
    
    Note over C,DB: ─── 受保护请求 ───
    
    C->>API: GET /api/todos<br/>Authorization: Bearer xxx
    API->>JWT: decode_token(token)
    JWT-->>API: {sub: 1, exp: ...}
    API->>DB: SELECT user WHERE id=1
    DB-->>API: User 对象
    API->>DB: SELECT todos WHERE user_id=1
    DB-->>API: [Todo, Todo, Todo]
    API-->>C: 200 OK<br/>{data: [...]}

graph LR
    subgraph "HTTP 方法"
        GET[GET<br/>读取]
        POST[POST<br/>创建]
        PUT[PUT<br/>全量更新]
        PATCH[PATCH<br/>局部更新]
        DELETE[DELETE<br/>删除]
    end
    
    subgraph "URL 路径"
        COLL["/todos<br/>资源集合"]
        ITEM["/todos/:id<br/>单个资源"]
        ACT["/todos/:id/toggle<br/>动作"]
    end
    
    GET --> COLL
    POST --> COLL
    GET --> ITEM
    PUT --> ITEM
    PATCH --> ITEM
    DELETE --> ITEM
    POST --> ACT
    
    COLL -->|返回列表| R1["200 + 数组"]
    COLL -->|创建| R2["201 + 新资源"]
    ITEM -->|读取| R3["200 + 资源"]
    ITEM -->|更新| R4["200 + 资源"]
    ITEM -->|删除| R5["204 无内容"]
    ACT -->|操作| R6["200 + 结果"]

flowchart TB
    Start["HTTP 请求<br/>POST /api/v1/todos"] --> Auth{"token_required<br/>检查 Authorization"}
    
    Auth -->|"无 Token"| Err401["401 Unauthorized<br/>{code: AUTH_REQUIRED}"]
    Auth -->|"Token 无效/过期"| Err401
    
    Auth -->|Token 有效| UserCheck["查找用户<br/>User.query.get(sub)"]
    
    UserCheck -->|用户不存在| Err401_2["401 Unauthorized<br/>{code: USER_NOT_FOUND}"]
    UserCheck -->|用户存在| Validate["参数校验<br/>validate_todo_data()"]
    
    Validate -->|校验失败| Err422["422 Unprocessable<br/>{code: VALIDATION_ERROR,<br/>details: {...}}"]
    
    Validate -->|校验通过| Create["创建 Todo 对象<br/>todo = Todo(**cleaned)"]
    
    Create --> Add["db.session.add(todo)"]
    Add --> Commit["db.session.commit()"]
    Commit --> Response["201 Created<br/>{message, data}"]
    
    style Err401 fill:#ff9999
    style Err401_2 fill:#ff9999
    style Err422 fill:#ffcc99
    style Response fill:#90EE90

graph TD
    subgraph "❌ N+1 查询 (lazy='select')"
        Q1["1 次查询: SELECT * FROM users"]
        Q1 --> U1["用户 1"]
        Q1 --> U2["用户 2"]
        Q1 --> U3["用户 3"]
        
        U1 --> T1["1 次查询: SELECT * FROM todos WHERE user_id=1"]
        U2 --> T2["1 次查询: SELECT * FROM todos WHERE user_id=2"]
        U3 --> T3["1 次查询: SELECT * FROM todos WHERE user_id=3"]
        
        Total1["总共: 1 (用户) + N (用户数) 次查询"]
    end
    
    subgraph "✅ 预加载 (lazy='joined')"
        Q2["1 次查询: SELECT * FROM users<br/>LEFT JOIN todos ON user.id=todos.user_id"]
        Q2 --> R1["用户 1 + TODO 列表  ← 一次查询全部获取"]
        Q2 --> R2["用户 2 + TODO 列表"]
        Q2 --> R3["用户 3 + TODO 列表"]
        
        Total2["总共: 1 次查询！"]
    end
    
    style Total1 fill:#ff9999
    style Total2 fill:#90EE90