init_db.py

#!/usr/bin/env python3
"""Create the local database and a small, credible demo dataset."""

from app import create_app, db
from app.models import Demand, Item, Message, Review, Skill, Transaction, User
from sqlalchemy import inspect, text


DEMO_PASSWORD = "demo123"


def upgrade_legacy_sqlite_schema():
    """Add columns introduced by the rebuild without deleting existing data."""
    if db.engine.dialect.name != "sqlite":
        return

    expected_columns = {
        "skill": {
            "requirements": "TEXT",
        },
        "demand": {
            "requirements": "TEXT",
            "urgency": "VARCHAR(16) NOT NULL DEFAULT 'medium'",
            "deadline": "DATETIME",
        },
        "review": {
            "reply": "TEXT",
            "replied_at": "DATETIME",
        },
    }

    inspector = inspect(db.engine)
    table_names = set(inspector.get_table_names())
    statements = []
    for table_name, columns in expected_columns.items():
        if table_name not in table_names:
            continue
        existing = {column["name"] for column in inspector.get_columns(table_name)}
        for column_name, definition in columns.items():
            if column_name not in existing:
                statements.append(
                    f'ALTER TABLE "{table_name}" ADD COLUMN "{column_name}" {definition}'
                )

    if not statements:
        return

    with db.engine.begin() as connection:
        for statement in statements:
            connection.execute(text(statement))
    print(f"已兼容升级旧版 SQLite 数据库（新增 {len(statements)} 个字段）。")


def add_user(email, nickname, major, grade):
    user = User(email=email, nickname=nickname, major=major, grade=grade)
    user.set_password(DEMO_PASSWORD)
    db.session.add(user)
    return user


def init_database():
    app = create_app()
    with app.app_context():
        db.create_all()
        upgrade_legacy_sqlite_schema()
        if User.query.first():
            print("数据库已有数据，跳过演示数据初始化。")
            return

        users = [
            add_user("linan@example.com", "林安", "计算机科学", "大三"),
            add_user("suyue@example.com", "苏悦", "视觉传达", "大二"),
            add_user("chenmo@example.com", "陈默", "新闻传播", "大四"),
            add_user("jianghe@example.com", "江禾", "外国语言文学", "研一"),
            add_user("zhouxing@example.com", "周行", "电子工程", "大三"),
        ]
        db.session.flush()

        skills = [
            Skill(
                user=users[0],
                title="Python 数据分析入门",
                description="从真实 CSV 数据出发，带你完成清洗、透视分析和一张可读的可视化图表。",
                category="编程",
                service_mode="线上",
                price_type="交换技能",
                available_times="周二、周四 19:00 后",
            ),
            Skill(
                user=users[1],
                title="作品集排版诊所",
                description="30 分钟快速诊断信息层级、字体与版式问题，适合设计和产品方向求职作品集。",
                category="设计",
                service_mode="线下",
                price_type="付费",
                price_amount=39,
                available_times="周三下午，仙林校区",
            ),
            Skill(
                user=users[3],
                title="英语口语搭子",
                description="围绕校园生活与面试话题进行情境对话，结束后提供高频表达和发音反馈。",
                category="语言",
                service_mode="均可",
                price_type="免费",
                available_times="工作日 20:00-21:00",
            ),
            Skill(
                user=users[2],
                title="三分钟路演稿打磨",
                description="帮你把项目经历改成结构清楚、有开场钩子且能在三分钟内讲完的路演稿。",
                category="咨询",
                service_mode="线上",
                price_type="交换技能",
                available_times="周末",
            ),
            Skill(
                user=users[4],
                title="单片机焊接与调试",
                description="提供基础焊接工具和现场指导，协助定位常见的供电、连线和串口问题。",
                category="教学",
                service_mode="线下",
                price_type="付费",
                price_amount=25,
                available_times="周六，电子楼",
            ),
        ]
        db.session.add_all(skills)

        items = [
            Item(
                user=users[0],
                title="机械键盘 87 键",
                description="青轴，功能正常，已清洁消毒，适合宿舍外接电脑使用。",
                category="电子产品",
                condition="八成新",
                exchange_for="降噪耳机或显示器支架",
                transaction_type="交换或出售",
                sale_price=120,
                location_campus="仙林校区",
                location_building="四组团",
            ),
            Item(
                user=users[1],
                title="设计类经典书 6 本",
                description="包含《设计心理学》《写给大家看的设计书》等，少量划线。",
                category="书籍",
                condition="九成新",
                transaction_type="出售",
                sale_price=68,
                location_campus="鼓楼校区",
                location_building="南园",
            ),
            Item(
                user=users[2],
                title="富士拍立得 Mini 11",
                description="毕业季闲置，机器功能正常，附保护套，可按天租借。",
                category="电子产品",
                condition="九成新",
                transaction_type="租赁",
                rental_price_day=12,
                location_campus="仙林校区",
                location_building="新闻传播学院",
            ),
            Item(
                user=users[3],
                title="瑜伽垫与弹力带",
                description="瑜伽垫厚 8mm，附三档弹力带，适合宿舍日常训练。",
                category="运动器材",
                condition="九成新",
                transaction_type="出售",
                sale_price=35,
                location_campus="仙林校区",
                location_building="五组团",
            ),
        ]
        db.session.add_all(items)

        demands = [
            Demand(
                user=users[1],
                type="skill",
                title="想学会用 Notion 搭作品集资料库",
                description="资料散落在网盘和聊天记录里，希望有人带我搭一套简单可维护的资料库。",
                requirements="最好能提供一个可复用模板，并讲清楚标签和视图的设计思路。",
                category="技术开发",
                budget_type="交换",
                urgency="medium",
            ),
            Demand(
                user=users[2],
                type="item",
                title="借一支领夹麦完成周末采访",
                description="周六下午在仙林校区做人物采访，预计使用 4 小时，当天归还。",
                requirements="Type-C 接口或附转接头，收音功能正常。",
                category="物品需求",
                budget_type="付费",
                budget_amount_min=15,
                budget_amount_max=30,
                urgency="high",
            ),
            Demand(
                user=users[4],
                type="skill",
                title="寻找一位产品同学帮忙看竞赛方案",
                description="已经有项目背景和功能清单，希望一起梳理用户流程与答辩叙事。",
                requirements="有产品或创业比赛经验优先，预计沟通 45 分钟。",
                category="咨询服务",
                budget_type="交换",
                urgency="medium",
            ),
            Demand(
                user=users[3],
                type="item",
                title="求购二手显示器支架",
                description="桌面空间比较小，需要一个单臂气压支架，能支持 24 英寸显示器。",
                category="物品需求",
                budget_type="付费",
                budget_amount_min=50,
                budget_amount_max=90,
                urgency="low",
            ),
        ]
        db.session.add_all(demands)
        db.session.flush()

        completed = Transaction(
            requester=users[1],
            provider=users[0],
            skill=skills[0],
            transaction_type="skill_service",
            status="completed",
            agreed_terms="用一小时完成 pandas 入门练习。",
        )
        accepted = Transaction(
            requester=users[4],
            provider=users[2],
            item=items[2],
            transaction_type="item_rental",
            status="accepted",
            agreed_terms='{"message":"周六下午借用，傍晚归还。"}',
        )
        response = Transaction(
            requester=users[0],
            provider=users[4],
            demand=demands[2],
            transaction_type="demand_response",
            status="pending_approval",
            agreed_terms='{"title":"可以帮你做一次方案走查","description":"会按用户、场景、流程和答辩叙事四部分反馈。","price_type":"negotiable","price":0}',
        )
        db.session.add_all([completed, accepted, response])
        db.session.flush()

        db.session.add(Review(
            transaction=completed,
            reviewer=users[1],
            reviewee=users[0],
            rating=5,
            comment="讲得很清楚，还给了可以继续练习的小作业。",
            review_type="to_provider",
        ))
        db.session.add_all([
            Message(
                sender=users[4],
                receiver=users[2],
                content="你好，拍立得周六下午还方便租借吗？",
                related_item_id=items[2].id,
            ),
            Message(
                sender=users[2],
                receiver=users[4],
                content="可以，下午两点在新传院门口交接。",
                related_item_id=items[2].id,
            ),
        ])

        db.session.commit()
        print("演示数据库初始化完成。")
        print(f"演示账号：linan@example.com / {DEMO_PASSWORD}")
        print(f"其他账号使用相同密码：{DEMO_PASSWORD}")


if __name__ == "__main__":
    init_database()