"""
Tag system API endpoints
"""
from fastapi import APIRouter, HTTPException
from typing import List, Optional
from app.tags.backend.models import (
    Tag, TagCreate, TagUpdate, 
    EntityTag, EntityTagCreate,
    TagWorkflow, TagWorkflowCreate,
    TagType,
    TagGroup, TagGroupCreate
)
from app.core.database import execute_query, execute_query_single, execute_update

router = APIRouter(prefix="/tags")

# ============= TAG GROUPS =============

@router.get("/groups", response_model=List[TagGroup])
async def list_tag_groups():
    results = execute_query("SELECT * FROM tag_groups ORDER BY name")
    return results


@router.post("/groups", response_model=TagGroup)
async def create_tag_group(group: TagGroupCreate):
    query = """
        INSERT INTO tag_groups (name, description, behavior)
        VALUES (%s, %s, %s)
        RETURNING *
    """
    result = execute_query_single(query, (group.name, group.description, group.behavior))
    return result

# ============= TAG CRUD =============

@router.get("", response_model=List[Tag])
async def list_tags(
    type: Optional[TagType] = None,
    is_active: Optional[bool] = None
):
    """List all tags with optional filtering"""
    query = "SELECT * FROM tags WHERE 1=1"
    params = []
    
    if type:
        query += " AND type = %s"
        params.append(type)
    
    if is_active is not None:
        query += " AND is_active = %s"
        params.append(is_active)
    
    query += " ORDER BY type, name"
    
    results = execute_query(query, tuple(params) if params else ())
    return results

@router.get("/{tag_id}", response_model=Tag)
async def get_tag(tag_id: int):
    """Get single tag by ID"""
    result = execute_query_single(
        "SELECT * FROM tags WHERE id = %s",
        (tag_id,)
    )
    if not result:
        raise HTTPException(status_code=404, detail="Tag not found")
    return result

@router.post("", response_model=Tag)
async def create_tag(tag: TagCreate):
    """Create new tag"""
    query = """
        INSERT INTO tags (name, type, description, color, icon, is_active, tag_group_id)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        RETURNING *
    """
    result = execute_query_single(
        query,
        (tag.name, tag.type, tag.description, tag.color, tag.icon, tag.is_active, tag.tag_group_id)
    )
    return result

@router.put("/{tag_id}", response_model=Tag)
async def update_tag(tag_id: int, tag: TagUpdate):
    """Update existing tag"""
    # Build dynamic update query
    updates = []
    params = []
    
    if tag.name is not None:
        updates.append("name = %s")
        params.append(tag.name)
    if tag.description is not None:
        updates.append("description = %s")
        params.append(tag.description)
    if tag.color is not None:
        updates.append("color = %s")
        params.append(tag.color)
    if tag.icon is not None:
        updates.append("icon = %s")
        params.append(tag.icon)
    if tag.is_active is not None:
        updates.append("is_active = %s")
        params.append(tag.is_active)
    if tag.tag_group_id is not None:
        updates.append("tag_group_id = %s")
        params.append(tag.tag_group_id)
    
    if not updates:
        raise HTTPException(status_code=400, detail="No fields to update")
    
    updates.append("updated_at = CURRENT_TIMESTAMP")
    params.append(tag_id)
    
    query = f"""
        UPDATE tags 
        SET {', '.join(updates)}
        WHERE id = %s
        RETURNING *
    """
    
    result = execute_query_single(query, tuple(params))
    if not result:
        raise HTTPException(status_code=404, detail="Tag not found")
    return result

@router.delete("/{tag_id}")
async def delete_tag(tag_id: int):
    """Delete tag (also removes all entity associations)"""
    result = execute_update(
        "DELETE FROM tags WHERE id = %s",
        (tag_id,)
    )
    if result == 0:
        raise HTTPException(status_code=404, detail="Tag not found")
    return {"message": "Tag deleted successfully"}


# ============= ENTITY TAGGING =============

@router.post("/entity", response_model=EntityTag)
async def add_tag_to_entity(entity_tag: EntityTagCreate):
    """Add tag to any entity (ticket, customer, time_entry, etc.)"""
    # Enforce toggle/single groups
    group = execute_query_single(
        """
        SELECT tg.behavior, t.tag_group_id
        FROM tags t
        JOIN tag_groups tg ON t.tag_group_id = tg.id
        WHERE t.id = %s
        """,
        (entity_tag.tag_id,)
    )

    if group and group.get("behavior") in ("single", "toggle"):
        execute_update(
            """
            DELETE FROM entity_tags
            WHERE entity_type = %s
              AND entity_id = %s
              AND tag_id IN (
                  SELECT id FROM tags WHERE tag_group_id = %s
              )
            """,
            (entity_tag.entity_type, entity_tag.entity_id, group["tag_group_id"])
        )

    query = """
        INSERT INTO entity_tags (entity_type, entity_id, tag_id, tagged_by)
        VALUES (%s, %s, %s, %s)
        ON CONFLICT (entity_type, entity_id, tag_id) DO NOTHING
        RETURNING *
    """
    result = execute_query_single(
        query,
        (entity_tag.entity_type, entity_tag.entity_id, entity_tag.tag_id, entity_tag.tagged_by)
    )
    if not result:
        raise HTTPException(status_code=409, detail="Tag already exists on entity")
    return result

@router.delete("/entity")
async def remove_tag_from_entity(
    entity_type: str,
    entity_id: int,
    tag_id: int
):
    """Remove tag from entity"""
    result = execute_update(
        "DELETE FROM entity_tags WHERE entity_type = %s AND entity_id = %s AND tag_id = %s",
        (entity_type, entity_id, tag_id)
    )
    if result == 0:
        raise HTTPException(status_code=404, detail="Tag association not found")
    return {"message": "Tag removed from entity"}

@router.delete("/entity/{entity_type}/{entity_id}/{tag_id}")
async def remove_tag_from_entity_path(
    entity_type: str,
    entity_id: int,
    tag_id: int
):
    """Remove tag from entity (Path param version)"""
    result = execute_update(
        "DELETE FROM entity_tags WHERE entity_type = %s AND entity_id = %s AND tag_id = %s",
        (entity_type, entity_id, tag_id)
    )
    if result == 0:
        raise HTTPException(status_code=404, detail="Tag association not found")
    return {"message": "Tag removed from entity"}

@router.get("/entity/{entity_type}/{entity_id}", response_model=List[Tag])
async def get_entity_tags(entity_type: str, entity_id: int):
    """Get all tags for a specific entity"""
    query = """
        SELECT t.* 
        FROM tags t
        JOIN entity_tags et ON et.tag_id = t.id
        WHERE et.entity_type = %s AND et.entity_id = %s
        ORDER BY t.type, t.name
    """
    results = execute_query(query, (entity_type, entity_id))
    return results

@router.get("/search")
async def search_tags(q: str, type: Optional[TagType] = None):
    """Search tags by name (fuzzy search)"""
    query = """
        SELECT * FROM tags 
        WHERE is_active = true 
        AND LOWER(name) LIKE LOWER(%s)
    """
    params = [f"%{q}%"]
    
    if type:
        query += " AND type = %s"
        params.append(type)
    
    query += " ORDER BY name LIMIT 20"
    
    results = execute_query(query, tuple(params))
    return results


# ============= WORKFLOW MANAGEMENT =============

@router.get("/workflows", response_model=List[TagWorkflow])
async def list_workflows(tag_id: Optional[int] = None):
    """List all workflow configurations"""
    if tag_id:
        results = execute_query(
            "SELECT * FROM tag_workflows WHERE tag_id = %s ORDER BY id",
            (tag_id,)
        )
    else:
        results = execute_query(
            "SELECT * FROM tag_workflows ORDER BY tag_id, id",
            ()
        )
    return results

@router.post("/workflows", response_model=TagWorkflow)
async def create_workflow(workflow: TagWorkflowCreate):
    """Create new workflow trigger"""
    query = """
        INSERT INTO tag_workflows (tag_id, trigger_event, action_type, action_config, is_active)
        VALUES (%s, %s, %s, %s, %s)
        RETURNING *
    """
    result = execute_query_single(
        query,
        (workflow.tag_id, workflow.trigger_event, workflow.action_type, 
         workflow.action_config, workflow.is_active)
    )
    return result

@router.delete("/workflows/{workflow_id}")
async def delete_workflow(workflow_id: int):
    """Delete workflow configuration"""
    result = execute_update(
        "DELETE FROM tag_workflows WHERE id = %s",
        (workflow_id,)
    )
    if result == 0:
        raise HTTPException(status_code=404, detail="Workflow not found")
    return {"message": "Workflow deleted"}
