use ai::error::{AiError, AiResult};
use ai::tool::tools::FunctionCall;
use async_trait::async_trait;
use db::sqlx;
use serde_json::{Value, json};

use super::helpers::{arg_str, git_ctx, require_workspace_member};
use crate::agent::run::AppAgentContext;

pub struct IssueListTool;

impl IssueListTool {
    pub fn new() -> Self {
        Self
    }
}

impl Default for IssueListTool {
    fn default() -> Self {
        Self::new()
    }
}

#[async_trait]
impl FunctionCall for IssueListTool {
    type Context = AppAgentContext;

    fn name(&self) -> &'static str {
        "issue_list"
    }

    fn description(&self) -> &'static str {
        "List issues in a workspace with optional filters: state (open/closed), priority, label, milestone, assignee."
    }

    fn schema(&self) -> Value {
        json!({
            "type": "object",
            "properties": {
                "workspace": { "type": "string", "description": "Workspace name" },
                "state": { "type": "string", "description": "Filter by state: 'open' or 'closed'" },
                "priority": { "type": "string", "description": "Filter by priority" },
                "label": { "type": "string", "description": "Filter by label name" },
                "milestone": { "type": "string", "description": "Filter by milestone title" },
                "assignee": { "type": "string", "description": "Filter by assignee username" },
                "limit": { "type": "integer", "description": "Max results (default 20, max 100)" }
            },
            "required": ["workspace"]
        })
    }

    async fn call(
        &self,
        ctx: &mut AppAgentContext,
        args: Value,
    ) -> AiResult<Value> {
        let git = git_ctx(ctx)?;
        let workspace = arg_str(&args, "workspace")?;
        let wk_id =
            require_workspace_member(git, ctx.user_id, workspace).await?;
        let limit = args
            .get("limit")
            .and_then(|v| v.as_i64())
            .unwrap_or(20)
            .min(100);

        let mut conditions =
            vec!["i.wk = $1".to_string(), "i.deleted_at IS NULL".to_string()];
        let mut params: Vec<String> = vec![wk_id.to_string()];
        let mut idx = 2i32;

        for (arg, col) in [("state", "i.state"), ("priority", "i.priority")] {
            if let Some(v) = args
                .get(arg)
                .and_then(|v| v.as_str())
                .filter(|s| !s.is_empty())
            {
                conditions.push(format!("{col} = ${idx}"));
                params.push(v.to_string());
                idx += 1;
            }
        }

        if let Some(v) = args
            .get("label")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
        {
            conditions.push(format!("EXISTS(SELECT 1 FROM issue_label il INNER JOIN label l ON l.id = il.label WHERE il.issue = i.id AND l.name = ${idx})"));
            params.push(v.to_string());
            idx += 1;
        }

        if let Some(v) = args
            .get("milestone")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
        {
            conditions.push(format!("EXISTS(SELECT 1 FROM issue_milestone im INNER JOIN milestone m ON m.id = im.milestone WHERE im.issue = i.id AND m.title = ${idx})"));
            params.push(v.to_string());
            idx += 1;
        }

        if let Some(v) = args
            .get("assignee")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
        {
            conditions.push(format!("EXISTS(SELECT 1 FROM issue_assignee ia INNER JOIN \"user\" u ON u.id = ia.\"user\" WHERE ia.issue = i.id AND u.username = ${idx})"));
            params.push(v.to_string());
            idx += 1;
        }

        let where_clause = conditions.join(" AND ");
        let query = format!(
            "SELECT i.number, i.title, i.body, i.state, i.priority, \
             i.closed_at, i.due_at, i.created_at \
             FROM issue i WHERE {where_clause} \
             ORDER BY i.created_at DESC LIMIT ${idx}",
        );

        let mut q =
            sqlx::query_as::<_, IssueRow>(db::sqlx::AssertSqlSafe(query));
        q = q.bind(wk_id);
        for i in 1..params.len() {
            q = q.bind(&params[i]);
        }
        q = q.bind(limit);

        let rows = q
            .fetch_all(git.db.reader())
            .await
            .map_err(AiError::Database)?;

        let issues: Vec<Value> = rows.iter().map(|r| json!({
            "number": r.number,
            "title": r.title,
            "state": r.state,
            "priority": r.priority,
            "body": r.body.as_ref().map(|b| if b.len() > 500 { format!("{}...", &b[..500]) } else { b.clone() }),
            "created_at": r.created_at.to_rfc3339(),
        })).collect();

        Ok(json!({ "issues": issues, "count": issues.len() }))
    }
}

#[derive(sqlx::FromRow)]
struct IssueRow {
    number: i64,
    title: String,
    body: Option<String>,
    state: String,
    priority: String,
    due_at: Option<chrono::DateTime<chrono::Utc>>,
    closed_at: Option<chrono::DateTime<chrono::Utc>>,
    created_at: chrono::DateTime<chrono::Utc>,
}

pub struct IssueGetTool;

impl IssueGetTool {
    pub fn new() -> Self {
        Self
    }
}

impl Default for IssueGetTool {
    fn default() -> Self {
        Self::new()
    }
}

#[async_trait]
impl FunctionCall for IssueGetTool {
    type Context = AppAgentContext;

    fn name(&self) -> &'static str {
        "issue_get"
    }

    fn description(&self) -> &'static str {
        "Get full details of a single issue by its number."
    }

    fn schema(&self) -> Value {
        json!({
            "type": "object",
            "properties": {
                "workspace": { "type": "string", "description": "Workspace name" },
                "number": { "type": "integer", "description": "Issue number" }
            },
            "required": ["workspace", "number"]
        })
    }

    async fn call(
        &self,
        ctx: &mut AppAgentContext,
        args: Value,
    ) -> AiResult<Value> {
        let git = git_ctx(ctx)?;
        let workspace = arg_str(&args, "workspace")?;
        let number =
            args.get("number").and_then(|v| v.as_i64()).ok_or_else(|| {
                AiError::Config("'number' parameter is required".to_string())
            })?;
        let wk_id =
            require_workspace_member(git, ctx.user_id, workspace).await?;

        let row = sqlx::query_as::<_, IssueRow>(
            "SELECT number, title, body, state, priority, \
             closed_at, due_at, created_at \
             FROM issue WHERE wk = $1 AND number = $2 AND deleted_at IS NULL",
        )
        .bind(wk_id)
        .bind(number)
        .fetch_optional(git.db.reader())
        .await
        .map_err(AiError::Database)?
        .ok_or_else(|| AiError::Config(format!("issue #{number} not found")))?;

        // Load labels
        #[derive(sqlx::FromRow)]
        struct LabelRow {
            name: String,
        }

        let labels: Vec<String> = sqlx::query_as::<_, LabelRow>(
            "SELECT l.name FROM label l \
             INNER JOIN issue_label il ON il.label = l.id \
             WHERE il.issue = (SELECT id FROM issue WHERE wk = $1 AND number = $2)",
        )
        .bind(wk_id).bind(number)
        .fetch_all(git.db.reader()).await.map_err(AiError::Database)?
        .iter().map(|r| r.name.clone()).collect();

        // Load assignees
        #[derive(sqlx::FromRow)]
        struct AssigneeRow {
            username: String,
        }

        let assignees: Vec<String> = sqlx::query_as::<_, AssigneeRow>(
            "SELECT u.username FROM \"user\" u \
             INNER JOIN issue_assignee ia ON ia.\"user\" = u.id \
             WHERE ia.issue = (SELECT id FROM issue WHERE wk = $1 AND number = $2)",
        )
        .bind(wk_id).bind(number)
        .fetch_all(git.db.reader()).await.map_err(AiError::Database)?
        .iter().map(|r| r.username.clone()).collect();

        Ok(json!({
            "number": row.number,
            "title": row.title,
            "body": row.body,
            "state": row.state,
            "priority": row.priority,
            "labels": labels,
            "assignees": assignees,
            "created_at": row.created_at.to_rfc3339(),
            "due_at": row.due_at.map(|d| d.to_rfc3339()),
            "closed_at": row.closed_at.map(|d| d.to_rfc3339()),
        }))
    }
}

pub struct IssueCommentsTool;

impl IssueCommentsTool {
    pub fn new() -> Self {
        Self
    }
}

impl Default for IssueCommentsTool {
    fn default() -> Self {
        Self::new()
    }
}

#[async_trait]
impl FunctionCall for IssueCommentsTool {
    type Context = AppAgentContext;

    fn name(&self) -> &'static str {
        "issue_comments"
    }

    fn description(&self) -> &'static str {
        "List comments on an issue, ordered by time."
    }

    fn schema(&self) -> Value {
        json!({
            "type": "object",
            "properties": {
                "workspace": { "type": "string", "description": "Workspace name" },
                "number": { "type": "integer", "description": "Issue number" },
                "limit": { "type": "integer", "description": "Max results (default 50)" }
            },
            "required": ["workspace", "number"]
        })
    }

    async fn call(
        &self,
        ctx: &mut AppAgentContext,
        args: Value,
    ) -> AiResult<Value> {
        let git = git_ctx(ctx)?;
        let workspace = arg_str(&args, "workspace")?;
        let number =
            args.get("number").and_then(|v| v.as_i64()).ok_or_else(|| {
                AiError::Config("'number' parameter is required".to_string())
            })?;
        let wk_id =
            require_workspace_member(git, ctx.user_id, workspace).await?;
        let limit = args
            .get("limit")
            .and_then(|v| v.as_i64())
            .unwrap_or(50)
            .min(200);

        #[derive(sqlx::FromRow)]
        struct CommentRow {
            body: String,
            username: String,
            created_at: chrono::DateTime<chrono::Utc>,
        }

        let rows: Vec<CommentRow> = sqlx::query_as(
            "SELECT ic.body, u.username, ic.created_at \
             FROM issue_comment ic \
             INNER JOIN \"user\" u ON u.id = ic.author \
             WHERE ic.issue = (SELECT id FROM issue WHERE wk = $1 AND number = $2) \
               AND ic.deleted_at IS NULL \
             ORDER BY ic.created_at ASC LIMIT $3",
        )
        .bind(wk_id).bind(number).bind(limit)
        .fetch_all(git.db.reader()).await.map_err(AiError::Database)?;

        let comments: Vec<Value> = rows
            .iter()
            .map(|r| {
                json!({
                    "author": r.username,
                    "body": r.body,
                    "created_at": r.created_at.to_rfc3339(),
                })
            })
            .collect();

        Ok(
            json!({ "issue_number": number, "comments": comments, "count": comments.len() }),
        )
    }
}

pub struct IssueEventsTool;

impl IssueEventsTool {
    pub fn new() -> Self {
        Self
    }
}

impl Default for IssueEventsTool {
    fn default() -> Self {
        Self::new()
    }
}

#[async_trait]
impl FunctionCall for IssueEventsTool {
    type Context = AppAgentContext;

    fn name(&self) -> &'static str {
        "issue_events"
    }

    fn description(&self) -> &'static str {
        "List the timeline of events for an issue (created, commented, closed, labeled, etc)."
    }

    fn schema(&self) -> Value {
        json!({
            "type": "object",
            "properties": {
                "workspace": { "type": "string", "description": "Workspace name" },
                "number": { "type": "integer", "description": "Issue number" }
            },
            "required": ["workspace", "number"]
        })
    }

    async fn call(
        &self,
        ctx: &mut AppAgentContext,
        args: Value,
    ) -> AiResult<Value> {
        let git = git_ctx(ctx)?;
        let workspace = arg_str(&args, "workspace")?;
        let number =
            args.get("number").and_then(|v| v.as_i64()).ok_or_else(|| {
                AiError::Config("'number' parameter is required".to_string())
            })?;
        let wk_id =
            require_workspace_member(git, ctx.user_id, workspace).await?;

        #[derive(sqlx::FromRow)]
        struct EventRow {
            event: String,
            from_value: Option<String>,
            to_value: Option<String>,
            username: Option<String>,
            created_at: chrono::DateTime<chrono::Utc>,
        }

        let rows: Vec<EventRow> = sqlx::query_as(
            "SELECT e.event, e.from_value, e.to_value, u.username, e.created_at \
             FROM issue_event e \
             LEFT JOIN \"user\" u ON u.id = e.actor \
             WHERE e.issue = (SELECT id FROM issue WHERE wk = $1 AND number = $2) \
             ORDER BY e.created_at ASC",
        )
        .bind(wk_id).bind(number)
        .fetch_all(git.db.reader()).await.map_err(AiError::Database)?;

        let events: Vec<Value> = rows
            .iter()
            .map(|r| {
                json!({
                    "event": r.event,
                    "actor": r.username,
                    "from": r.from_value,
                    "to": r.to_value,
                    "created_at": r.created_at.to_rfc3339(),
                })
            })
            .collect();

        Ok(
            json!({ "issue_number": number, "events": events, "count": events.len() }),
        )
    }
}