#Tortoise-ORM Quick Start: Write asynchronous database operations like writing django

from tortoise import Tortoise

TORTOISE_ORM = {
    "connections": {
        "default": {
            "engine": "tortoise.backends.sqlite",       # 开发用 SQLite
            # "engine": "tortoise.backends.asyncpg",   # 生产用 PostgreSQL
            "credentials": {
                "file_path": "./dev.db",                # SQLite 文件路径
                # "host": "localhost",
                # "port": 5432,
                # "user": "dev_user",
                # "password": "dev_pwd",
                # "database": "tortoise_demo",
            }
        }
    },
    "apps": {
        # 自定义 app 名称，后续模型引用时使用 `app名.模型类名`
        "demo": {
            "models": ["__main__", "demo_models"],      # 模型所在的模块路径
            "default_connection": "default",
        }
    },
    "use_tz": False,          # 国内项目一般无需 UTC 转换
    "timezone": "Asia/Shanghai",
}

from fastapi import FastAPI
from tortoise.contrib.fastapi import register_tortoise
from tortoise_config import TORTOISE_ORM

app = FastAPI(title="Tortoise + FastAPI CRUD 演示")

register_tortoise(
    app,
    config=TORTOISE_ORM,
    generate_schemas=True,             # 开发时自动建表（生产环境建议使用 Aerich 迁移工具）
    add_exception_handlers=True,       # 自动捕获模型验证、不存在等异常
)

from tortoise import fields
from tortoise.models import Model

class Author(Model):
    id = fields.IntField(pk=True, autoincrement=True)
    name = fields.CharField(max_length=50, description="作者姓名")
    email = fields.CharField(max_length=100, unique=True, description="唯一邮箱")
    # 反向关联提示（非必需，但能提供 IDE 自动补全）
    articles: fields.ReverseRelation["Article"]

    class Meta:
        table = "authors"
        ordering = ["name"]

    def __str__(self):
        return self.name


class Tag(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=30, unique=True, description="标签名")
    articles: fields.ReverseRelation["Article"]

    def __str__(self):
        return self.name


class Article(Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=200, index=True, description="文章标题（加索引加速搜索）")
    content = fields.TextField(description="文章正文")
    views = fields.IntField(default=0, description="阅读量")
    is_published = fields.BooleanField(default=False, description="是否发布")
    created_at = fields.DatetimeField(auto_now_add=True, description="创建时间")
    updated_at = fields.DatetimeField(auto_now=True, description="更新时间")

    # 外键：删除作者时级联删除其所有文章
    author = fields.ForeignKeyField(
        "demo.Author",            # 格式：app名.模型类名
        related_name="articles",
        on_delete=fields.CASCADE,
    )
    # 多对多：通过 article_tag 中间表关联标签
    tags = fields.ManyToManyField(
        "demo.Tag",
        related_name="articles",
        through="article_tag",
    )

    class Meta:
        table = "articles"
        ordering = ["-created_at"]   # 最新文章排在最前

# 单条创建
new_author = await Author.create(
    name="张三",
    email="zhangsan@example.com"
)

# 批量创建（比循环单个创建快 10 倍以上）
await Tag.bulk_create([
    Tag(name="Python"),
    Tag(name="FastAPI"),
    Tag(name="Tortoise-ORM"),
])

# 创建文章并关联作者和标签
new_article = await Article.create(
    title="Tortoise-ORM 入门第一弹",
    content="今天我们来学习 Tortoise-ORM ...",
    author=new_author   # 也可以直接传 author_id=1
)

python_tag = await Tag.get(name="Python")
tortoise_tag = await Tag.get(name="Tortoise-ORM")
await new_article.tags.add(python_tag, tortoise_tag)

# 查询单条：若不存在则抛出 DoesNotExist 异常
author = await Author.get(id=1)

# 安全查询：找不到返回 None
article = await Article.get_or_none(id=999)

# 过滤查询
published_articles = await Article.filter(is_published=True)

# 跨表过滤（使用双下划线语法）
python_articles = await Article.filter(tags__name="Python").prefetch_related("author", "tags")

# 分页与排序
page1 = await Article.all().offset(0).limit(10).order_by("-views")

# 只查询需要的字段（减少数据传输）
titles = await Article.all().only("id", "title")

# 聚合统计：计算每个作者的文章数
from tortoise.functions import Count

author_stats = await Author.annotate(article_count=Count("articles"))
for stat in author_stats:
    print(f"{stat.name} 写了 {stat.article_count} 篇文章")

# 先查再改，适合需要业务逻辑判断的场景
article = await Article.get(id=1)
article.title = "修改后的标题"
article.is_published = True
await article.save()

# 批量更新（性能更好）
await Article.filter(views__gt=1000).update(is_published=True)

# 单条删除
article = await Article.get(id=1)
await article.delete()

# 批量删除
await Article.filter(is_published=False, created_at__lt="2024-01-01").delete()

from fastapi import FastAPI, HTTPException, Query
from pydantic import BaseModel, EmailStr
from typing import List
from demo_models import Author, Article, Tag

app = FastAPI(title="Tortoise + FastAPI CRUD 演示")

# ------------------------------ Pydantic 模型 ------------------------------
class AuthorIn(BaseModel):
    name: str
    email: EmailStr

class AuthorOut(BaseModel):
    id: int
    name: str
    email: EmailStr

    class Config:
        from_attributes = True   # 允许直接从 Tortoise 模型转换为 Pydantic

# ------------------------------ 接口实现 ----------------------------------
@app.post("/authors", response_model=AuthorOut, status_code=201)
async def create_author(data: AuthorIn):
    # 尽管模型有 unique 约束，显式检查可以返回更友好的错误信息
    exists = await Author.exists(email=data.email)
    if exists:
        raise HTTPException(status_code=400, detail="该邮箱已被注册")
    return await Author.create(**data.model_dump())

@app.get("/authors", response_model=List[AuthorOut])
async def list_authors(
    skip: int = Query(0, ge=0),
    limit: int = Query(10, ge=1, le=50)
):
    return await Author.all().offset(skip).limit(limit)

from tortoise import Tortoise

# 初始化（主程序入口）
await Tortoise.init(config=TORTOISE_ORM)
await Tortoise.generate_schemas()          # 首次建表（生产环境用 Aerich）

# 创建
obj = await Model.create(**kwargs)
await Model.bulk_create([Model(...), Model(...)])

# 查询
obj = await Model.get_or_none(id=1)        # 安全版，找不到返回 None
objs = await Model.filter(...)
    .prefetch_related("关联字段")
    .order_by("-时间字段")
    .offset(skip)
    .limit(limit)

# 更新
obj.field = "新值"
await obj.save()
await Model.filter(...).update(field="新值")

# 删除
await obj.delete()
await Model.filter(...).delete()