# crud_router.py

from fastapi import APIRouter, Depends, Query, status 
from pydantic import BaseModel
from typing import Dict, Any, List, Optional 
import asyncpg
from datetime import datetime

# database_conn.py에 정의된 get_db_connection 함수를 임포트합니다.
from database_conn import get_db_connection 

# ----------------------------------------------------
# Pydantic 모델 정의
# ----------------------------------------------------
class ItemCreate(BaseModel):
    name: str
    price: float

class ItemUpdate(BaseModel):
    name: str | None = None
    price: float | None = None

class ItemInDB(BaseModel):
    id: int
    name: str
    price: float
    created_at: datetime

# API 응답 표준화 모델 
class APIResponse(BaseModel):
    success: bool
    msg: str = ""
    data: Any = None 
    
# ----------------------------------------------------
# APIRouter 인스턴스 생성
# ----------------------------------------------------
router = APIRouter(
    tags=["crud_t_test1"],
)

# ----------------------------------------------------
# 1. CREATE (데이터 생성) - POST /crud/t_test1/
# ----------------------------------------------------
@router.post("/", response_model=APIResponse)
async def create_t_test1_item(
    item: ItemCreate,
    conn: asyncpg.Connection = Depends(get_db_connection)
):
    """
    새로운 아이템을 't_test1' 테이블에 삽입합니다 (INSERT).
    """
    response_data = {"success": True, "msg": "", "data": None}
    
    query = """
        INSERT INTO t_test1 (name, price) 
        VALUES ($1, $2) 
        RETURNING id, name, price, created_at;
    """
    
    try:
        record = await conn.fetchrow(query, item.name, item.price)
        
        if record is None:
            response_data["success"] = False
            response_data["msg"] = "Item creation failed unexpectedly: No record returned."
            return response_data
            
        response_data["msg"] = "Item created successfully."
        response_data["data"] = ItemInDB(**dict(record))
        return response_data

    except Exception as e:
        response_data["success"] = False
        response_data["msg"] = f"Database INSERT error: {e!r}"
        return response_data


# ----------------------------------------------------
# 2. READ (전체 조회 및 단일 조회) - GET /crud/t_test1/?item_id={id} 또는 /crud/t_test1/
# ----------------------------------------------------
@router.get("/", response_model=APIResponse)
async def read_t_test1_items(
    # item_id가 쿼리 파라미터로 처리됩니다.
    item_id: Optional[int] = None, 
    conn: asyncpg.Connection = Depends(get_db_connection)
):
    """
    item_id가 있으면 단일 아이템을, 없으면 전체 리스트를 조회합니다 (SELECT).
    """
    response_data = {"success": True, "msg": "", "data": None}
    
    try:
        if item_id:
            # 단일 조회 (WHERE ID)
            query = "SELECT id, name, price, created_at FROM t_test1 WHERE id = $1;"
            record = await conn.fetchrow(query, item_id)
            
            if record is None:
                response_data["success"] = False
                response_data["msg"] = f"Read failed: Item with ID {item_id} not found."
                return response_data
                
            response_data["msg"] = "Item read successfully."
            response_data["data"] = ItemInDB(**dict(record))
        else:
            # 전체 조회 (SELECT *)
            query = "SELECT id, name, price, created_at FROM t_test1 ORDER BY id;"
            records = await conn.fetch(query)
            
            response_data["data"] = [ItemInDB(**dict(record)) for record in records]
            response_data["msg"] = f"Found {len(records)} items."
            
        return response_data
        
    except Exception as e:
        response_data["success"] = False
        response_data["msg"] = f"Database SELECT error: {e!r}"
        return response_data

# ----------------------------------------------------
# 3. UPDATE (데이터 업데이트) - PUT /crud/t_test1/?item_id={id}
# ----------------------------------------------------
@router.put("/", response_model=APIResponse)
async def update_t_test1_item(
    # item_id가 필수 쿼리 파라미터로 처리됩니다.
    item_id: int, 
    item: ItemUpdate,
    conn: asyncpg.Connection = Depends(get_db_connection)
):
    """
    특정 ID의 아이템 정보를 업데이트합니다 (UPDATE).
    """
    response_data = {"success": True, "msg": "", "data": None}
    
    # 1. 현재 아이템 존재 여부 확인 (업데이트 전 필수)
    select_query = "SELECT id FROM t_test1 WHERE id = $1;"
    current_record = await conn.fetchrow(select_query, item_id)
    
    if current_record is None:
        response_data["success"] = False
        response_data["msg"] = f"Update failed: Item with ID {item_id} not found."
        return response_data

    # 2. 동적 SQL 쿼리 생성
    updates = []
    params = []
    param_count = 1
    
    if item.name is not None:
        updates.append(f"name = ${param_count}")
        params.append(item.name)
        param_count += 1
        
    if item.price is not None:
        updates.append(f"price = ${param_count}")
        params.append(item.price)
        param_count += 1
        
    if not updates:
        response_data["msg"] = "No updates provided. Returning current item data."
        # 업데이트할 내용이 없는 경우, 현재 아이템을 조회하여 반환
        return await read_t_test1_items(item_id=item_id, conn=conn)

    # 3. 최종 UPDATE 쿼리 실행
    set_clause = ", ".join(updates)
    where_param_index = param_count
    
    update_query = f"""
        UPDATE t_test1 
        SET {set_clause}
        WHERE id = ${where_param_index}
        RETURNING id, name, price, created_at;
    """
    
    params.append(item_id) # WHERE 절에 사용할 ID 추가
    
    try:
        updated_record = await conn.fetchrow(update_query, *params)
        
        if updated_record is None:
            response_data["success"] = False
            response_data["msg"] = "Update failed: No record was modified."
            return response_data
            
        response_data["msg"] = "Item updated successfully."
        response_data["data"] = ItemInDB(**dict(updated_record))
        return response_data

    except Exception as e:
        response_data["success"] = False
        response_data["msg"] = f"Database UPDATE error: {e!r}"
        return response_data


# ----------------------------------------------------
# 4. DELETE (데이터 삭제) - DELETE /crud/t_test1/?item_id={id}
# ----------------------------------------------------
@router.delete("/", response_model=APIResponse)
async def delete_t_test1_item(
    # item_id가 필수 쿼리 파라미터로 처리됩니다.
    item_id: int,
    conn: asyncpg.Connection = Depends(get_db_connection)
):
    """
    특정 ID의 아이템을 삭제합니다 (DELETE).
    """
    response_data = {"success": True, "msg": "", "data": None}
    
    query = "DELETE FROM t_test1 WHERE id = $1;"
    
    try:
        command_tag = await conn.execute(query, item_id)
        
        deleted_rows = int(command_tag.split()[-1])
        
        if deleted_rows == 0:
            response_data["success"] = False
            response_data["msg"] = f"Delete failed: Item with ID {item_id} not found."
            return response_data
            
        response_data["msg"] = f"Item with ID {item_id} deleted successfully."
        return response_data

    except Exception as e:
        response_data["success"] = False
        response_data["msg"] = f"Database DELETE error: {e!r}"
        return response_data