"""
Dashboard Routes (Async)
"""
import ast
import json
import time
from datetime import datetime
from typing import Optional, List, Dict, Any, Union

from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy import func, or_, select, update, delete
from sqlalchemy.exc import OperationalError
from pydantic import BaseModel, ConfigDict

from app.core.database import get_async_db_session
from app.models.dashboard import Dashboard, Section
from app.models.question import Question

# Request Schemas (Unchanged)
class DashboardCreateRequest(BaseModel):
    name: str
    user_id: str
    agent_id: Union[str, List[str]]


class DashboardListRequest(BaseModel):
    user_id: str
    agent_id: Union[str, List[str]]


class DashboardSectionsRequest(BaseModel):
    dashboard_id: str
    user_id: str
    agent_id: Union[str, List[str]]


class SectionAddRequest(BaseModel):
    dashboard_id: str
    name: str
    user_id: str
    agent_id:  Union[str, List[str]]
    question_id: int  # Question ID from saved prompts (myprompts)
    height: Optional[int] = 400
    width: Optional[int] = 800
    position_x: Optional[int] = 0
    position_y: Optional[int] = 0


class SectionUpdateRequest(BaseModel):
    section_id: str
    dashboard_id: str
    name: str
    user_id: str
    agent_id:  Union[str, List[str]]
    question_id: int  # Question ID from saved prompts (myprompts)
    height: Optional[int] = 400
    width: Optional[int] = 800
    position_x: Optional[int] = 0
    position_y: Optional[int] = 0


class SectionDeleteRequest(BaseModel):
    section_id: str
    dashboard_id: str
    user_id: str
    agent_id: Union[str, List[str]]


class DashboardDeleteRequest(BaseModel):
    dashboard_id: str
    user_id: str
    agent_id: Union[str, List[str]]


class DashboardUpdateRequest(BaseModel):
    dashboard_id: str
    name: str
    user_id: str
    agent_id: Union[str, List[str]]


# Response Schemas (Unchanged)
class DashboardResponse(BaseModel):
    id: int
    name: str
    user_id: str
    agent_id: Union[str, List[str]]
    created_at: str
    updated_at: str
    section_count: Optional[int] = 0
    
    model_config = ConfigDict(from_attributes=True)


router = APIRouter(prefix="/dashboards", tags=["Dashboards"])


def _match_dashboard_agent_sql(request_agent_id: Union[str, List[str]]):
    """
    Same matching as questions.multi_agent_get_my_prompts: stored agent_id is a JSON list
    in a Text column — use equality OR LIKE per id.
    """
    if isinstance(request_agent_id, list):
        agent_ids = request_agent_id
    else:
        agent_ids = [request_agent_id]
    return or_(
        *[
            or_(
                Dashboard.agent_id == str(aid).strip(),
                Dashboard.agent_id.like(f'%"{str(aid).strip()}"%'),
            )
            for aid in agent_ids
        ]
    )


def _parse_stored_agent_ids(stored_agent_id: str) -> set:
    """Parse stored Text agent_id into a set of id strings."""
    if not stored_agent_id:
        return set()
    s = str(stored_agent_id).strip()
    try:
        parsed = json.loads(s)
        if isinstance(parsed, list):
            return {str(x) for x in parsed}
        return {str(parsed)}
    except (json.JSONDecodeError, TypeError):
        pass
    try:
        parsed = ast.literal_eval(s)
        if isinstance(parsed, (list, tuple)):
            return {str(x) for x in parsed}
        return {str(parsed)}
    except (ValueError, SyntaxError):
        pass
    return {s}


def _parse_request_agent_ids(agent_id: Union[str, List[str]]) -> set:
    """Normalize client agent_id (single id, JSON list string, or list) to a set of strings."""
    if isinstance(agent_id, list):
        return {str(x).strip() for x in agent_id if str(x).strip()}
    return _parse_stored_agent_ids(str(agent_id))


def _section_dashboard_question_agent_scope(
    dashboard_agent_id: str,
    question_agent_id: str,
    request_agent_id: Union[str, List[str]],
) -> tuple[bool, Optional[str]]:
    """
    Decide if a saved question can be attached to this dashboard (multi-agent / main-screen flow).

    - Allow if dashboard and question share at least one agent (stored JSON lists or plain ids).
    - Else allow if the client session (request.agent_id) includes at least one agent from the
      question — so a dashboard created with agent [7] can still attach a prompt saved under [1]
      when the user passes request.agent_id [1,7] (or equivalent).
    - Else allow if the client session includes at least one of the dashboard's agents (e.g. user
      is on main-screen agent 7 and the dashboard is [7]) so they can attach a question saved
      under another id without resending every agent.
    - Second/third rules do not change dashboard.agent_id: scope stays as set at /create, even
      if attached questions list multiple agents in sections.
    """
    _da = _parse_stored_agent_ids(dashboard_agent_id)
    _qa = _parse_stored_agent_ids(question_agent_id)
    _req = _parse_request_agent_ids(request_agent_id)
    if not _qa:
        return False, None
    if _da and (_da & _qa):
        return True, None
    if _req and (_qa & _req):
        return True, None
    if _da and _req and (_da & _req):
        return True, None
    return False, None


def _agent_id_matches(stored_agent_id: str, request_agent_id: Union[str, List[str]]) -> bool:
    """Check if request_agent_id matches stored_agent_id. stored can be single id or JSON list."""
    stored_ids = _parse_stored_agent_ids(stored_agent_id)
    if not stored_ids:
        return False
    request_ids = (
        [str(request_agent_id)]
        if isinstance(request_agent_id, str)
        else [str(x) for x in request_agent_id]
    )
    return bool(stored_ids & set(request_ids))


def _coerce_dashboard_pk(dashboard_id: Union[str, int]) -> int:
    """Dashboard.id is Integer; clients may send string ids."""
    if isinstance(dashboard_id, int):
        return dashboard_id
    return int(str(dashboard_id).strip())


@router.post("/create", status_code=201)
async def create_dashboard(request: DashboardCreateRequest):
    """
    Create a new dashboard (Async).
    """
    try:
        async with get_async_db_session() as db:
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            # Same as questions.multi_agent_save_prompt: always store JSON list string (multi-agent supported)
            if isinstance(request.agent_id, list):
                agent_ids = [str(x).strip() for x in request.agent_id if str(x).strip()]
            else:
                agent_ids = [str(request.agent_id).strip()]
            stored_agent_id = json.dumps(agent_ids)
            dashboard = Dashboard(
                name=request.name,
                user_id=str(request.user_id).strip(),
                agent_id=stored_agent_id,
                created_at=now,
                updated_at=now
            )
            db.add(dashboard)
            await db.commit()
            await db.refresh(dashboard)
            try:
                aid_out: Any = json.loads(dashboard.agent_id)
            except (json.JSONDecodeError, TypeError):
                aid_out = dashboard.agent_id
            return {
                "success": True,
                "message": "Dashboard created successfully",
                "details": {
                    "dashboard": {
                        "id": dashboard.id,
                        "name": dashboard.name,
                        "user_id": dashboard.user_id,
                        "agent_id": aid_out,
                        "created_at": dashboard.created_at,
                        "updated_at": dashboard.updated_at
                    }
                }
            }
            
    except OperationalError as e:
        return {
            "success": False,
            "message": f"Error creating dashboard: Database is locked or busy. Please try again. {str(e)}",
            "details": {}
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error creating dashboard: {str(e)}",
            "details": {}
        }


@router.post("/list")
async def get_all_dashboards(request: DashboardListRequest):
    """
    Get all dashboards for a user and agent (Async).
    Returns dashboards with section count
    """
    try:
        async with get_async_db_session() as db:
            # Async Query with generic select()
            stmt = (
                select(Dashboard, func.count(Section.id).label('section_count'))
                .outerjoin(Section, Dashboard.id == Section.dashboard_id)
                .where(
                    Dashboard.user_id == str(request.user_id).strip(),
                    _match_dashboard_agent_sql(request.agent_id),
                )
                .group_by(Dashboard.id)
                .order_by(Dashboard.updated_at.desc())
            )
            result = await db.execute(stmt)
            dashboards = result.all()
            
            output = []
            for dashboard, section_count in dashboards:
                try:
                    aid_row: Any = json.loads(dashboard.agent_id)
                except (json.JSONDecodeError, TypeError):
                    aid_row = dashboard.agent_id
                output.append({
                    "id": dashboard.id,
                    "name": dashboard.name,
                    "user_id": dashboard.user_id,
                    "agent_id": aid_row,
                    "created_at": dashboard.created_at,
                    "updated_at": dashboard.updated_at,
                    "metadata": dashboard.metadata_,
                    "section_count": section_count or 0
                })
            
            return {
                "success": True,
                "message": "Dashboards retrieved successfully",
                "details": {"dashboards": output}
            }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving dashboards: {str(e)}",
            "details": {}
        }


@router.post("/edit-dashboard")
async def update_dashboard_name(request: DashboardUpdateRequest):
    """
    Update a dashboard name (Async).
    """
    try:
        async with get_async_db_session() as db:
            # Verify dashboard exists
            stmt = select(Dashboard).where(
                Dashboard.id == _coerce_dashboard_pk(request.dashboard_id),
                Dashboard.user_id == str(request.user_id).strip(),
                _match_dashboard_agent_sql(request.agent_id),
            )
            result = await db.execute(stmt)
            dashboard = result.scalar_one_or_none()
            
            if not dashboard:
                return {
                    "success": False,
                    "message": "Dashboard not found or access denied",
                    "details": {
                        "dashboard_id": request.dashboard_id,
                        "user_id": request.user_id,
                        "agent_id": request.agent_id
                    }
                }
            
            # Update dashboard name and timestamp
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            dashboard.name = request.name
            dashboard.updated_at = now
            
            await db.commit()
            await db.refresh(dashboard)
            try:
                aid_up: Any = json.loads(dashboard.agent_id)
            except (json.JSONDecodeError, TypeError):
                aid_up = dashboard.agent_id
            return {
                "success": True,
                "message": "Dashboard updated successfully",
                "details": {
                    "dashboard": {
                        "id": dashboard.id,
                        "name": dashboard.name,
                        "user_id": dashboard.user_id,
                        "agent_id": aid_up,
                        "created_at": dashboard.created_at,
                        "updated_at": dashboard.updated_at,
                        "metadata": dashboard.metadata_
                    }
                }
            }
            
    except OperationalError as e:
        return {
            "success": False,
            "message": f"Error updating dashboard: Database is locked. Please try again. {str(e)}",
            "details": {}
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error updating dashboard: {str(e)}",
            "details": {}
        }


@router.post("/sections/list")
async def get_dashboard_sections(request: DashboardSectionsRequest):
    """
    Get all sections for a specific dashboard (Async).
    """
    try:
        async with get_async_db_session() as db:
            # Verify dashboard exists
            stmt = select(Dashboard).where(
                Dashboard.id == _coerce_dashboard_pk(request.dashboard_id),
                Dashboard.user_id == str(request.user_id).strip(),
                _match_dashboard_agent_sql(request.agent_id),
            )
            result = await db.execute(stmt)
            dashboard = result.scalar_one_or_none()
            
            if not dashboard:
                return {
                    "success": False,
                    "message": "Dashboard not found or access denied",
                    "details": {
                        "dashboard_id": request.dashboard_id,
                        "user_id": request.user_id,
                        "agent_id": request.agent_id
                    }
                }
            
            # Get sections
            stmt_sections = select(Section).where(
                Section.dashboard_id == str(_coerce_dashboard_pk(request.dashboard_id))
            ).order_by(Section.created_at.asc())
            
            result_sections = await db.execute(stmt_sections)
            sections = result_sections.scalars().all()
            
            output = []
            for section in sections:
                output.append({
                    "id": section.id,
                    "dashboard_id": section.dashboard_id,
                    "name": section.name,
                    "height": section.height,
                    "width": section.width,
                    "url": section.url,
                    "position_x": section.position_x,
                    "position_y": section.position_y,
                    "created_at": section.created_at,
                    "updated_at": section.updated_at,
                    "metadata": section.metadata_
                })
            
            return {
                "success": True,
                "message": "Sections retrieved successfully",
                "details": {
                    "dashboard_id": request.dashboard_id,
                    "dashboard_name": dashboard.name,
                    "sections": output
                }
            }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving sections: {str(e)}",
            "details": {}
        }


@router.post("/sections/add", status_code=201)
async def add_section(request: SectionAddRequest):
    """
    Add a section to a dashboard (Async).
    """
    try:
        async with get_async_db_session() as db:
            # Verify dashboard exists (agent_id in DB can be single id or JSON list)
            try:
                dash_pk = _coerce_dashboard_pk(request.dashboard_id)
            except (TypeError, ValueError):
                return {
                    "success": False,
                    "message": "Invalid dashboard_id",
                    "details": {"dashboard_id": request.dashboard_id},
                }
            stmt = select(Dashboard).where(
                Dashboard.id == dash_pk,
                Dashboard.user_id == str(request.user_id).strip(),
            )
            result = await db.execute(stmt)
            dashboard = result.scalar_one_or_none()
            if not dashboard:
                return {
                    "success": False,
                    "message": "Dashboard not found or access denied",
                    "details": {
                        "reason": "no_dashboard_for_user",
                        "dashboard_id": dash_pk,
                        "user_id": request.user_id,
                    },
                }
            
            # Verify question exists
            stmt_q = select(Question).where(Question.id == request.question_id)
            if str(request.user_id).strip() != "1":
                stmt_q = stmt_q.where(Question.user_id == str(request.user_id).strip())
            
            result_q = await db.execute(stmt_q)
            question = result_q.scalar_one_or_none()
            if not question:
                return {
                    "success": False,
                    "message": "Question not found or access denied",
                    "details": {
                        "question_id": request.question_id,
                        "dashboard_id": dash_pk
                    }
                }
            # Authorize: dashboard ∩ question, OR question ∩ request, OR request ∩ dashboard.
            # dashboard.agent_id is only set at /create; section attach does not expand it.
            ok_scope, _ = _section_dashboard_question_agent_scope(
                dashboard.agent_id, question.agent_id, request.agent_id
            )
            if not ok_scope:
                return {
                    "success": False,
                    "message": "Question not found or access denied",
                    "details": {
                        "reason": "dashboard_question_agent_mismatch",
                        "question_id": request.question_id,
                        "dashboard_id": dash_pk,
                        "dashboard_agent_id": dashboard.agent_id,
                        "question_agent_id": question.agent_id,
                        "request_agent_id": request.agent_id,
                    },
                }
            
            # Use question_id as the URL
            url = str(request.question_id)
            
            # Create section
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            section = Section(
                dashboard_id=str(dash_pk),
                name=request.name,
                height=request.height,
                width=request.width,
                url=url,
                position_x=request.position_x,
                position_y=request.position_y,
                created_at=now,
                updated_at=now
            )
            db.add(section)
            
            # Update dashboard's updated_at timestamp
            dashboard.updated_at = now
            
            await db.commit()
            await db.refresh(section)
            
            return {
                "success": True,
                "message": "Section added successfully",
                "details": {
                    "section": {
                        "id": section.id,
                        "dashboard_id": section.dashboard_id,
                        "name": section.name,
                        "height": section.height,
                        "width": section.width,
                        "url": section.url,
                        "position_x": section.position_x,
                        "position_y": section.position_y,
                        "created_at": section.created_at,
                        "updated_at": section.updated_at,
                    }
                }
            }
            
    except OperationalError as e:
        return {
            "success": False,
            "message": f"Error adding section: Database is locked. Please try again. {str(e)}",
            "details": {}
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error adding section: {str(e)}",
            "details": {}
        }


@router.post("/sections/update")
async def update_section(request: SectionUpdateRequest):
    """
    Update a section in a dashboard (Async).
    """
    try:
        async with get_async_db_session() as db:
            # Verify dashboard exists (same as sections/add: scope agent via question vs dashboard, not request.agent_id)
            _did_up = _coerce_dashboard_pk(request.dashboard_id)
            stmt = select(Dashboard).where(
                Dashboard.id == _did_up,
                Dashboard.user_id == str(request.user_id).strip(),
            )
            result = await db.execute(stmt)
            dashboard = result.scalar_one_or_none()
            
            if not dashboard:
                return {
                    "success": False,
                    "message": "Dashboard not found or access denied",
                    "details": {
                        "dashboard_id": request.dashboard_id,
                        "user_id": request.user_id,
                        "agent_id": request.agent_id
                    }
                }
            
            # Verify section exists
            stmt_s = select(Section).where(
                Section.id == request.section_id,
                Section.dashboard_id == str(_did_up)
            )
            result_s = await db.execute(stmt_s)
            section = result_s.scalar_one_or_none()
            
            if not section:
                return {
                    "success": False,
                    "message": "Section not found or access denied",
                    "details": {
                        "section_id": request.section_id,
                        "dashboard_id": request.dashboard_id
                    }
                }
            
            # Verify question exists
            stmt_q = select(Question).where(Question.id == request.question_id)
            if str(request.user_id).strip() != "1":
                stmt_q = stmt_q.where(Question.user_id == str(request.user_id).strip())
            
            result_q = await db.execute(stmt_q)
            question = result_q.scalar_one_or_none()
            if not question:
                return {
                    "success": False,
                    "message": "Question not found or access denied",
                    "details": {
                        "question_id": request.question_id,
                        "dashboard_id": request.dashboard_id
                    }
                }
            ok_scope_u, _ = _section_dashboard_question_agent_scope(
                dashboard.agent_id, question.agent_id, request.agent_id
            )
            if not ok_scope_u:
                return {
                    "success": False,
                    "message": "Question not found or access denied",
                    "details": {
                        "reason": "dashboard_question_agent_mismatch",
                        "question_id": request.question_id,
                        "dashboard_id": request.dashboard_id,
                        "dashboard_agent_id": dashboard.agent_id,
                        "question_agent_id": question.agent_id,
                        "request_agent_id": request.agent_id,
                    },
                }
            
            # Use question_id as the URL
            url = str(request.question_id)
            
            # Update section
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            section.name = request.name
            section.height = request.height
            section.width = request.width
            section.url = url
            section.position_x = request.position_x
            section.position_y = request.position_y
            section.updated_at = now
            
            # Update dashboard's updated_at timestamp
            dashboard.updated_at = now
            
            await db.commit()
            await db.refresh(section)
            
            return {
                "success": True,
                "message": "Section updated successfully",
                "details": {
                    "section": {
                        "id": section.id,
                        "dashboard_id": section.dashboard_id,
                        "name": section.name,
                        "height": section.height,
                        "width": section.width,
                        "url": section.url,
                        "position_x": section.position_x,
                        "position_y": section.position_y,
                        "created_at": section.created_at,
                        "updated_at": section.updated_at
                    }
                }
            }
            
    except OperationalError as e:
        return {
            "success": False,
            "message": f"Error updating section: Database is locked. Please try again. {str(e)}",
            "details": {}
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error updating section: {str(e)}",
            "details": {}
        }


@router.post("/sections/delete")
async def delete_section(request: SectionDeleteRequest):
    """
    Delete a section from a dashboard (Async).
    """
    try:
        async with get_async_db_session() as db:
            # Verify dashboard exists
            _did_del = _coerce_dashboard_pk(request.dashboard_id)
            stmt = select(Dashboard).where(
                Dashboard.id == _did_del,
                Dashboard.user_id == str(request.user_id).strip(),
                _match_dashboard_agent_sql(request.agent_id),
            )
            result = await db.execute(stmt)
            dashboard = result.scalar_one_or_none()
            
            if not dashboard:
                return {
                    "success": False,
                    "message": "Dashboard not found or access denied",
                    "details": {
                        "dashboard_id": request.dashboard_id,
                        "user_id": request.user_id,
                        "agent_id": request.agent_id
                    }
                }
            
            # Verify section exists
            stmt_s = select(Section).where(
                Section.id == request.section_id,
                Section.dashboard_id == str(_did_del)
            )
            result_s = await db.execute(stmt_s)
            section = result_s.scalar_one_or_none()
            
            if not section:
                return {
                    "success": False,
                    "message": "Section not found or access denied",
                    "details": {
                        "section_id": request.section_id,
                        "dashboard_id": request.dashboard_id
                    }
                }
            
            # Delete section
            section_id = section.id
            await db.delete(section)
            
            # Update dashboard's updated_at timestamp
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            dashboard.updated_at = now
            
            await db.commit()
            
            return {
                "success": True,
                "message": "Section deleted successfully",
                "details": {
                    "section_id": section_id,
                    "dashboard_id": request.dashboard_id
                }
            }
            
    except OperationalError as e:
        return {
            "success": False,
            "message": f"Error deleting section: Database is locked. Please try again. {str(e)}",
            "details": {}
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error deleting section: {str(e)}",
            "details": {}
        }


@router.post("/delete")
async def delete_dashboard(request: DashboardDeleteRequest):
    """
    Delete a dashboard and all its sections (Async).
    """
    try:
        async with get_async_db_session() as db:
            # Verify dashboard exists
            _did_rm = _coerce_dashboard_pk(request.dashboard_id)
            stmt = select(Dashboard).where(
                Dashboard.id == _did_rm,
                Dashboard.user_id == str(request.user_id).strip(),
                _match_dashboard_agent_sql(request.agent_id),
            )
            result = await db.execute(stmt)
            dashboard = result.scalar_one_or_none()
            
            if not dashboard:
                return {
                    "success": False,
                    "message": "Dashboard not found or access denied",
                    "details": {
                        "dashboard_id": request.dashboard_id,
                        "user_id": request.user_id,
                        "agent_id": request.agent_id
                    }
                }
            
            dashboard_id = dashboard.id
            dashboard_name = dashboard.name
            
            # Delete all sections first
            await db.execute(delete(Section).where(Section.dashboard_id == str(_did_rm)))
            
            # Delete dashboard
            await db.delete(dashboard)
            
            await db.commit()
            
            return {
                "success": True,
                "message": "Dashboard deleted successfully",
                "details": {
                    "dashboard_id": dashboard_id,
                    "dashboard_name": dashboard_name
                }
            }
            
    except OperationalError as e:
        return {
            "success": False,
            "message": f"Error deleting dashboard: Database is locked. Please try again. {str(e)}",
            "details": {}
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error deleting dashboard: {str(e)}",
            "details": {}
        }
