專案發包期程管理功能實作

class DeliveryScheduleBase(BaseModel):
    """發包/交貨期程基礎欄位"""
    vendor: str                                    # 廠商名稱
    item: str                                      # 料件名稱
    quantity: str | None = None                    # 數量（含單位）
    order_date: date | None = None                 # 發包日期
    expected_delivery_date: date | None = None     # 預計交貨日期
    actual_delivery_date: date | None = None       # 實際到貨日期
    status: str = "pending"                        # 狀態
    notes: str | None = None                       # 備註

@mcp.tool()
async def add_delivery_schedule(
    project_id: str,
    vendor: str,
    item: str,
    quantity: str | None = None,
    order_date: str | None = None,
    expected_delivery_date: str | None = None,
    status: str = "pending",
    notes: str | None = None,
) -> str:
    """
    新增專案發包/交貨記錄

    Args:
        project_id: 專案 UUID
        vendor: 廠商名稱（必填）
        item: 料件名稱（必填）
        quantity: 數量（含單位，如「2 台」）
        order_date: 發包日期（格式: YYYY-MM-DD）
        expected_delivery_date: 預計交貨日期（格式: YYYY-MM-DD）
        status: 狀態（pending/ordered/delivered/completed）
        notes: 備註
    """
    await ensure_db_connection()

    async with get_connection() as conn:
        # 驗證專案存在
        project = await conn.fetchrow(
            "SELECT id, name FROM projects WHERE id = $1",
            project_id,
        )
        if not project:
            return f"錯誤：找不到專案 {project_id}"

        # 解析日期
        parsed_order_date = None
        parsed_expected_date = None

        if order_date:
            try:
                parsed_order_date = date.fromisoformat(order_date)
            except ValueError:
                return "錯誤：發包日期格式錯誤，請使用 YYYY-MM-DD 格式"

        if expected_delivery_date:
            try:
                parsed_expected_date = date.fromisoformat(expected_delivery_date)
            except ValueError:
                return "錯誤：預計交貨日期格式錯誤"

        # 驗證狀態
        valid_statuses = ["pending", "ordered", "delivered", "completed"]
        if status not in valid_statuses:
            return f"錯誤：狀態必須是 {', '.join(valid_statuses)} 其中之一"

        # 新增記錄
        row = await conn.fetchrow(
            """
            INSERT INTO project_delivery_schedules
                (project_id, vendor, item, quantity, order_date,
                 expected_delivery_date, status, notes, created_by)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'AI')
            RETURNING id, vendor, item
            """,
            project_id, vendor, item, quantity,
            parsed_order_date, parsed_expected_date, status, notes,
        )

        return f"✅ 已新增發包記錄：【{vendor}】{item}"

@mcp.tool()
async def update_delivery_schedule(
    project_id: str,
    delivery_id: str | None = None,
    vendor: str | None = None,
    item: str | None = None,
    new_vendor: str | None = None,
    new_item: str | None = None,
    new_quantity: str | None = None,
    new_status: str | None = None,
    order_date: str | None = None,
    actual_delivery_date: str | None = None,
    expected_delivery_date: str | None = None,
    new_notes: str | None = None,
) -> str:
    """
    更新專案發包/交貨記錄

    Args:
        project_id: 專案 UUID
        delivery_id: 發包記錄 UUID（直接指定）
        vendor: 廠商名稱（用於匹配記錄）
        item: 料件名稱（用於匹配記錄）
        new_status: 新狀態
        actual_delivery_date: 實際到貨日期
        ... 其他更新欄位
    """
    await ensure_db_connection()

    async with get_connection() as conn:
        # 驗證專案存在
        project = await conn.fetchrow(
            "SELECT id, name FROM projects WHERE id = $1", project_id
        )
        if not project:
            return f"錯誤：找不到專案 {project_id}"

        # 找到目標記錄
        if delivery_id:
            # 直接用 ID
            row = await conn.fetchrow(
                "SELECT * FROM project_delivery_schedules WHERE id = $1 AND project_id = $2",
                delivery_id, project_id,
            )
            if not row:
                return f"錯誤：找不到發包記錄 {delivery_id}"
            matches = [row]

        elif vendor and item:
            # 用廠商 + 料件模糊匹配
            matches = await conn.fetch(
                """
                SELECT * FROM project_delivery_schedules
                WHERE project_id = $1 AND vendor ILIKE $2 AND item ILIKE $3
                """,
                project_id, f"%{vendor}%", f"%{item}%",
            )
            if not matches:
                return f"錯誤：找不到匹配的發包記錄（廠商：{vendor}，料件：{item}）"

            if len(matches) > 1:
                # 多筆匹配，列出供選擇
                result = f"找到 {len(matches)} 筆匹配記錄，請更精確指定：\n"
                for m in matches:
                    result += f"- {m['vendor']} - {m['item']}（ID: {m['id']}）\n"
                return result
        else:
            return "錯誤：請提供 delivery_id 或 vendor + item"

        # 執行更新...
        target = matches[0]
        # 建立 UPDATE SQL 動態更新有值的欄位
        # ...

        return f"✅ 已更新發包記錄：【{target['vendor']}】{target['item']}"

@mcp.tool()
async def get_delivery_schedules(
    project_id: str,
    status: str | None = None,
    vendor: str | None = None,
    limit: int = 20,
) -> str:
    """
    取得專案的發包/交貨記錄

    Args:
        project_id: 專案 UUID
        status: 狀態過濾（pending/ordered/delivered/completed）
        vendor: 廠商過濾
        limit: 最大數量，預設 20
    """
    await ensure_db_connection()

    async with get_connection() as conn:
        # 驗證專案存在
        project = await conn.fetchrow(
            "SELECT id, name FROM projects WHERE id = $1", project_id
        )
        if not project:
            return f"錯誤：找不到專案 {project_id}"

        # 建立查詢
        sql = "SELECT * FROM project_delivery_schedules WHERE project_id = $1"
        params = [project_id]

        if status:
            sql += " AND status = $2"
            params.append(status)

        if vendor:
            sql += f" AND vendor ILIKE ${len(params) + 1}"
            params.append(f"%{vendor}%")

        sql += " ORDER BY COALESCE(expected_delivery_date, '9999-12-31'), created_at"
        sql += f" LIMIT ${len(params) + 1}"
        params.append(limit)

        rows = await conn.fetch(sql, *params)

        if not rows:
            return f"專案「{project['name']}」目前沒有發包記錄"

        # 格式化輸出
        status_names = {
            "pending": "待發包",
            "ordered": "已發包",
            "delivered": "已到貨",
            "completed": "已完成",
        }

        result = f"📦 {project['name']} 的發包記錄（共 {len(rows)} 筆）：\n\n"

        for r in rows:
            status_display = status_names.get(r["status"], r["status"])
            result += f"【{r['vendor']}】{r['item']}\n"
            if r["quantity"]:
                result += f"  數量：{r['quantity']}\n"
            if r["order_date"]:
                result += f"  發包日：{r['order_date']}\n"
            if r["expected_delivery_date"]:
                result += f"  預計交貨：{r['expected_delivery_date']}\n"
            if r["actual_delivery_date"]:
                result += f"  實際到貨：{r['actual_delivery_date']}\n"
            result += f"  狀態：{status_display}\n\n"

        return result