#[derive(Debug, Clone, Serialize)]
pub struct ClosingMrRef {
    pub iid: i64,
    pub title: String,
    pub state: String,
    pub web_url: Option<String>,
}

#[derive(Debug, Serialize)]
pub struct IssueDetail {
    pub id: i64,
    pub iid: i64,
    pub title: String,
    pub description: Option<String>,
    pub state: String,
    pub author_username: String,
    pub created_at: i64,
    pub updated_at: i64,
    pub closed_at: Option<String>,
    pub confidential: bool,
    pub web_url: Option<String>,
    pub project_path: String,
    pub references_full: String,
    pub labels: Vec<String>,
    pub assignees: Vec<String>,
    pub due_date: Option<String>,
    pub milestone: Option<String>,
    pub user_notes_count: i64,
    pub merge_requests_count: usize,
    pub closing_merge_requests: Vec<ClosingMrRef>,
    pub discussions: Vec<DiscussionDetail>,
    pub status_name: Option<String>,
    pub status_category: Option<String>,
    pub status_color: Option<String>,
    pub status_icon_name: Option<String>,
    pub status_synced_at: Option<i64>,
}

#[derive(Debug, Serialize)]
pub struct DiscussionDetail {
    pub notes: Vec<NoteDetail>,
    pub individual_note: bool,
}

#[derive(Debug, Serialize)]
pub struct NoteDetail {
    pub gitlab_id: i64,
    pub author_username: String,
    pub body: String,
    pub created_at: i64,
    pub is_system: bool,
}

pub fn run_show_issue(
    config: &Config,
    iid: i64,
    project_filter: Option<&str>,
) -> Result<IssueDetail> {
    let db_path = get_db_path(config.storage.db_path.as_deref());
    let conn = create_connection(&db_path)?;

    let issue = find_issue(&conn, iid, project_filter)?;

    let labels = get_issue_labels(&conn, issue.id)?;

    let assignees = get_issue_assignees(&conn, issue.id)?;

    let closing_mrs = get_closing_mrs(&conn, issue.id)?;

    let discussions = get_issue_discussions(&conn, issue.id)?;

    let references_full = format!("{}#{}", issue.project_path, issue.iid);
    let merge_requests_count = closing_mrs.len();

    Ok(IssueDetail {
        id: issue.id,
        iid: issue.iid,
        title: issue.title,
        description: issue.description,
        state: issue.state,
        author_username: issue.author_username,
        created_at: issue.created_at,
        updated_at: issue.updated_at,
        closed_at: issue.closed_at,
        confidential: issue.confidential,
        web_url: issue.web_url,
        project_path: issue.project_path,
        references_full,
        labels,
        assignees,
        due_date: issue.due_date,
        milestone: issue.milestone_title,
        user_notes_count: issue.user_notes_count,
        merge_requests_count,
        closing_merge_requests: closing_mrs,
        discussions,
        status_name: issue.status_name,
        status_category: issue.status_category,
        status_color: issue.status_color,
        status_icon_name: issue.status_icon_name,
        status_synced_at: issue.status_synced_at,
    })
}

#[derive(Debug)]
struct IssueRow {
    id: i64,
    iid: i64,
    title: String,
    description: Option<String>,
    state: String,
    author_username: String,
    created_at: i64,
    updated_at: i64,
    closed_at: Option<String>,
    confidential: bool,
    web_url: Option<String>,
    project_path: String,
    due_date: Option<String>,
    milestone_title: Option<String>,
    user_notes_count: i64,
    status_name: Option<String>,
    status_category: Option<String>,
    status_color: Option<String>,
    status_icon_name: Option<String>,
    status_synced_at: Option<i64>,
}

fn find_issue(conn: &Connection, iid: i64, project_filter: Option<&str>) -> Result<IssueRow> {
    let (sql, params): (&str, Vec<Box<dyn rusqlite::ToSql>>) = match project_filter {
        Some(project) => {
            let project_id = resolve_project(conn, project)?;
            (
                "SELECT i.id, i.iid, i.title, i.description, i.state, i.author_username,
                        i.created_at, i.updated_at, i.closed_at, i.confidential,
                        i.web_url, p.path_with_namespace,
                        i.due_date, i.milestone_title,
                        (SELECT COUNT(*) FROM notes n
                         JOIN discussions d ON n.discussion_id = d.id
                         WHERE d.noteable_type = 'Issue' AND d.issue_id = i.id AND n.is_system = 0) AS user_notes_count,
                        i.status_name, i.status_category, i.status_color,
                        i.status_icon_name, i.status_synced_at
                 FROM issues i
                 JOIN projects p ON i.project_id = p.id
                 WHERE i.iid = ? AND i.project_id = ?",
                vec![Box::new(iid), Box::new(project_id)],
            )
        }
        None => (
            "SELECT i.id, i.iid, i.title, i.description, i.state, i.author_username,
                    i.created_at, i.updated_at, i.closed_at, i.confidential,
                    i.web_url, p.path_with_namespace,
                    i.due_date, i.milestone_title,
                    (SELECT COUNT(*) FROM notes n
                     JOIN discussions d ON n.discussion_id = d.id
                     WHERE d.noteable_type = 'Issue' AND d.issue_id = i.id AND n.is_system = 0) AS user_notes_count,
                    i.status_name, i.status_category, i.status_color,
                    i.status_icon_name, i.status_synced_at
             FROM issues i
             JOIN projects p ON i.project_id = p.id
             WHERE i.iid = ?",
            vec![Box::new(iid)],
        ),
    };

    let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();

    let mut stmt = conn.prepare(sql)?;
    let issues: Vec<IssueRow> = stmt
        .query_map(param_refs.as_slice(), |row| {
            let confidential_val: i64 = row.get(9)?;
            Ok(IssueRow {
                id: row.get(0)?,
                iid: row.get(1)?,
                title: row.get(2)?,
                description: row.get(3)?,
                state: row.get(4)?,
                author_username: row.get(5)?,
                created_at: row.get(6)?,
                updated_at: row.get(7)?,
                closed_at: row.get(8)?,
                confidential: confidential_val != 0,
                web_url: row.get(10)?,
                project_path: row.get(11)?,
                due_date: row.get(12)?,
                milestone_title: row.get(13)?,
                user_notes_count: row.get(14)?,
                status_name: row.get(15)?,
                status_category: row.get(16)?,
                status_color: row.get(17)?,
                status_icon_name: row.get(18)?,
                status_synced_at: row.get(19)?,
            })
        })?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    match issues.len() {
        0 => Err(LoreError::NotFound(format!("Issue #{} not found", iid))),
        1 => Ok(issues.into_iter().next().unwrap()),
        _ => {
            let projects: Vec<String> = issues.iter().map(|i| i.project_path.clone()).collect();
            Err(LoreError::Ambiguous(format!(
                "Issue #{} exists in multiple projects: {}. Use --project to specify.",
                iid,
                projects.join(", ")
            )))
        }
    }
}

fn get_issue_labels(conn: &Connection, issue_id: i64) -> Result<Vec<String>> {
    let mut stmt = conn.prepare(
        "SELECT l.name FROM labels l
         JOIN issue_labels il ON l.id = il.label_id
         WHERE il.issue_id = ?
         ORDER BY l.name",
    )?;

    let labels: Vec<String> = stmt
        .query_map([issue_id], |row| row.get(0))?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    Ok(labels)
}

fn get_issue_assignees(conn: &Connection, issue_id: i64) -> Result<Vec<String>> {
    let mut stmt = conn.prepare(
        "SELECT username FROM issue_assignees
         WHERE issue_id = ?
         ORDER BY username",
    )?;

    let assignees: Vec<String> = stmt
        .query_map([issue_id], |row| row.get(0))?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    Ok(assignees)
}

fn get_closing_mrs(conn: &Connection, issue_id: i64) -> Result<Vec<ClosingMrRef>> {
    let mut stmt = conn.prepare(
        "SELECT mr.iid, mr.title, mr.state, mr.web_url
         FROM entity_references er
         JOIN merge_requests mr ON mr.id = er.source_entity_id
         WHERE er.target_entity_type = 'issue'
           AND er.target_entity_id = ?
           AND er.source_entity_type = 'merge_request'
           AND er.reference_type = 'closes'
         ORDER BY mr.iid",
    )?;

    let mrs: Vec<ClosingMrRef> = stmt
        .query_map([issue_id], |row| {
            Ok(ClosingMrRef {
                iid: row.get(0)?,
                title: row.get(1)?,
                state: row.get(2)?,
                web_url: row.get(3)?,
            })
        })?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    Ok(mrs)
}

fn get_issue_discussions(conn: &Connection, issue_id: i64) -> Result<Vec<DiscussionDetail>> {
    let mut disc_stmt = conn.prepare(
        "SELECT id, individual_note FROM discussions
         WHERE issue_id = ?
         ORDER BY first_note_at",
    )?;

    let disc_rows: Vec<(i64, bool)> = disc_stmt
        .query_map([issue_id], |row| {
            let individual: i64 = row.get(1)?;
            Ok((row.get(0)?, individual == 1))
        })?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    let mut note_stmt = conn.prepare(
        "SELECT gitlab_id, author_username, body, created_at, is_system
         FROM notes
         WHERE discussion_id = ?
         ORDER BY position",
    )?;

    let mut discussions = Vec::new();
    for (disc_id, individual_note) in disc_rows {
        let notes: Vec<NoteDetail> = note_stmt
            .query_map([disc_id], |row| {
                let is_system: i64 = row.get(4)?;
                Ok(NoteDetail {
                    gitlab_id: row.get(0)?,
                    author_username: row.get(1)?,
                    body: row.get(2)?,
                    created_at: row.get(3)?,
                    is_system: is_system == 1,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        let has_user_notes = notes.iter().any(|n| !n.is_system);
        if has_user_notes || notes.is_empty() {
            discussions.push(DiscussionDetail {
                notes,
                individual_note,
            });
        }
    }

    Ok(discussions)
}
