use models::issue::comment;
use models::issue::issue_attachment;
use models::issue::issue_content_history;
use models::issue::issue_dependency;
use models::issue::issue_event_line;
use models::issue::issue_event_line::IssueEventType;
use models::issue::issue_item;
use models::issue::issue_item::IssuePriority;
use models::issue::issue_label;
use models::issue::issue_member;
use models::issue::issue_pin;
use models::issue::issue_reactions;
use models::issue::issue_relation_label;
use models::issue::issue_relation_pull;
use models::issue::issue_relation_repo;
use models::issue::issue_user;
use models::issue::issue_watch;
use models::issue::issues;
use models::issue::milestone;
use models::issue::stopwatch;
use models::issue::tracked_time;
use models::space::space_members::{self, MemberStatus};
use models::users::user_notifications::NotificationType;
use sea_orm::{
    ActiveModelTrait, ColumnTrait, Condition, ConnectionTrait, EntityTrait, PaginatorTrait,
    QueryFilter, QueryOrder, QuerySelect, Set, TransactionTrait,
};
use serde::{Deserialize, Serialize};
use space::services::spaces::UserDto;
use time::OffsetDateTime;
use utoipa::{IntoParams, ToSchema};
use uuid::Uuid;

use crate::errors::IssueError;
use crate::services::labels::LabelDto;
use crate::services::service::IssueEventDetail;
use crate::services::service::IssueService;
use crate::share::authz::is_board_manager;
use crate::share::lookup::{board_context, issue_context, lookup_issue_by_ref};

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize, ToSchema)]
#[serde(rename_all = "snake_case")]
pub enum IssueState {
    Open,
    Closed,
}

#[derive(Debug, Clone, Serialize, Deserialize, ToSchema)]
pub struct IssueDto {
    pub id: Uuid,
    pub board_id: Uuid,
    pub number: i32,
    pub author: Option<UserDto>,
    pub title: String,
    pub description: Option<String>,
    pub content: String,
    pub milestone_id: Option<Uuid>,
    pub priority: IssuePriority,
    pub due_at: Option<OffsetDateTime>,
    pub assignees: Vec<UserDto>,
    pub viewer_watch: Option<models::common::watch_mode::WatchMode>,
    pub state: IssueState,
    pub created_at: OffsetDateTime,
    pub updated_at: Option<OffsetDateTime>,
    pub closed_at: Option<OffsetDateTime>,
}

#[derive(Debug, Clone, Serialize, ToSchema)]
pub struct IssueListItemDto {
    pub id: Uuid,
    pub board_id: Uuid,
    pub number: i32,
    pub author: Option<UserDto>,
    pub title: String,
    /// Chips the list rows render; the detail view fetches these separately.
    pub labels: Vec<LabelDto>,
    pub milestone_id: Option<Uuid>,
    pub priority: IssuePriority,
    pub due_at: Option<OffsetDateTime>,
    pub assignees: Vec<UserDto>,
    pub state: IssueState,
    pub created_at: OffsetDateTime,
    pub updated_at: Option<OffsetDateTime>,
    pub closed_at: Option<OffsetDateTime>,
}

pub type IssuePage = export::pagination::Page<IssueListItemDto>;

#[derive(Debug, Clone, Deserialize, ToSchema)]
pub struct CreateIssueCmd {
    pub title: String,
    #[serde(default)]
    pub description: Option<String>,
    #[serde(default)]
    pub content: String,
    #[serde(default)]
    pub milestone_id: Option<Uuid>,
    #[serde(default)]
    pub priority: IssuePriority,
    #[serde(
        default,
        deserialize_with = "export::serde_datetime::deserialize_opt_datetime"
    )]
    pub due_at: Option<OffsetDateTime>,
    #[serde(default)]
    pub assignee_ids: Vec<i64>,
    #[serde(default)]
    pub label_ids: Vec<Uuid>,
    /// Draft uploads (`POST /issues/attachments`) to link to the new issue.
    #[serde(default)]
    pub attachment_ids: Vec<Uuid>,
}

#[derive(Debug, Clone, Deserialize, ToSchema)]
pub struct UpdateIssueCmd {
    #[serde(default)]
    pub title: Option<String>,
    #[serde(default, deserialize_with = "export::serde_patch::deserialize_opt_opt")]
    pub description: Option<Option<String>>,
    #[serde(default)]
    pub content: Option<String>,
    #[serde(default, deserialize_with = "export::serde_patch::deserialize_opt_opt")]
    pub milestone_id: Option<Option<Uuid>>,
    #[serde(default)]
    pub priority: Option<IssuePriority>,
    #[serde(
        default,
        deserialize_with = "export::serde_datetime::deserialize_opt_opt_datetime"
    )]
    pub due_at: Option<Option<OffsetDateTime>>,
    #[serde(default)]
    pub assignee_ids: Option<Vec<i64>>,
    #[serde(default)]
    pub label_ids: Option<Vec<Uuid>>,
}

#[derive(Debug, Clone, Deserialize, IntoParams, ToSchema)]
pub struct ListIssueQuery {
    pub state: Option<IssueState>,
    pub author_id: Option<i64>,
    /// One id or a comma-separated id list (`a,b`); `none` matches issues
    /// without a milestone. Any of the listed values matches.
    pub milestone_id: Option<String>,
    /// One id or a comma-separated id list (`a,b`); matches any of them.
    pub label_id: Option<String>,
    /// One id or a comma-separated id list (`a,b`); matches any of them.
    pub assignee_id: Option<String>,
    /// One priority or a comma-separated list (`high,urgent`).
    pub priority: Option<String>,
    pub sort: Option<String>,
    pub q: Option<String>,
    pub limit: Option<u64>,
    pub offset: Option<u64>,
}

/// `a,b` / `a` / absent → parsed ids, rejecting anything malformed so a bad
/// filter link answers 400 instead of silently listing everything.
fn parse_filter_ids(raw: Option<&str>) -> Result<Vec<Uuid>, IssueError> {
    let Some(raw) = raw else {
        return Ok(Vec::new());
    };
    raw.split(',')
        .map(str::trim)
        .filter(|part| !part.is_empty())
        .map(|part| {
            Uuid::parse_str(part)
                .map_err(|_| IssueError::BadRequest("invalid id in filter".to_string()))
        })
        .collect()
}

fn parse_filter_priorities(raw: Option<&str>) -> Result<Vec<IssuePriority>, IssueError> {
    let Some(raw) = raw else {
        return Ok(Vec::new());
    };
    raw.split(',')
        .map(str::trim)
        .filter(|part| !part.is_empty())
        .map(|part| {
            PRIORITY_KEYS
                .iter()
                .copied()
                .find(|(key, _)| *key == part)
                .map(|(_, priority)| priority)
                .ok_or_else(|| IssueError::BadRequest("invalid priority in filter".to_string()))
        })
        .collect()
}

/// Milestones accept the extra `none` token, so "未设里程碑" is a server-side
/// filter instead of a per-page client-side hack.
fn parse_milestone_filter(raw: Option<&str>) -> Result<(Vec<Uuid>, bool), IssueError> {
    let Some(raw) = raw else {
        return Ok((Vec::new(), false));
    };
    let parts: Vec<&str> = raw
        .split(',')
        .map(str::trim)
        .filter(|part| !part.is_empty())
        .collect();
    let include_none = parts.contains(&"none");
    let ids = parts
        .into_iter()
        .filter(|part| *part != "none")
        .map(|part| {
            Uuid::parse_str(part)
                .map_err(|_| IssueError::BadRequest("invalid id in filter".to_string()))
        })
        .collect::<Result<Vec<Uuid>, IssueError>>()?;
    Ok((ids, include_none))
}

/// snake_case query keys, in the enum's own order — the one place the wire
/// names for `?priority=` live.
const PRIORITY_KEYS: [(&str, IssuePriority); 5] = [
    ("none", IssuePriority::None),
    ("low", IssuePriority::Low),
    ("medium", IssuePriority::Medium),
    ("high", IssuePriority::High),
    ("urgent", IssuePriority::Urgent),
];

/// Inverse of {@link PRIORITY_KEYS}: the query string a caller should send.
pub fn priority_query_key(priority: IssuePriority) -> &'static str {
    PRIORITY_KEYS
        .iter()
        .find(|(_, value)| *value == priority)
        .map(|(key, _)| *key)
        .unwrap_or("none")
}

#[derive(Debug, Clone, Deserialize, IntoParams, ToSchema)]
pub struct IssuePath {
    pub space_name: String,
    pub board_id: Uuid,
    /// issue uuid or board-scoped number (e.g. "123")
    pub issue_id: String,
}

pub async fn delete_issue_cascade_in_txn(
    txn: &impl ConnectionTrait,
    issue_id: Uuid,
) -> Result<(), IssueError> {
    issue_pin::Entity::delete_many()
        .filter(issue_pin::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    let comment_rows = comment::Entity::find()
        .filter(comment::Column::IssueId.eq(issue_id))
        .all(txn)
        .await?;
    let comment_ids: Vec<Uuid> = comment_rows.into_iter().map(|c| c.id).collect();

    if !comment_ids.is_empty() {
        issue_reactions::Entity::delete_many()
            .filter(issue_reactions::Column::CommentId.is_in(comment_ids))
            .exec(txn)
            .await?;
    }

    issue_reactions::Entity::delete_many()
        .filter(issue_reactions::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_attachment::Entity::delete_many()
        .filter(issue_attachment::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    comment::Entity::delete_many()
        .filter(comment::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_event_line::Entity::delete_many()
        .filter(issue_event_line::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_member::Entity::delete_many()
        .filter(issue_member::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_relation_label::Entity::delete_many()
        .filter(issue_relation_label::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_relation_pull::Entity::delete_many()
        .filter(issue_relation_pull::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_relation_repo::Entity::delete_many()
        .filter(issue_relation_repo::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_user::Entity::delete_many()
        .filter(issue_user::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_watch::Entity::delete_many()
        .filter(issue_watch::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_dependency::Entity::delete_many()
        .filter(
            Condition::any()
                .add(issue_dependency::Column::IssueId.eq(issue_id))
                .add(issue_dependency::Column::DependencyId.eq(issue_id)),
        )
        .exec(txn)
        .await?;

    stopwatch::Entity::delete_many()
        .filter(stopwatch::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    tracked_time::Entity::delete_many()
        .filter(tracked_time::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_content_history::Entity::delete_many()
        .filter(issue_content_history::Column::IssueId.eq(issue_id))
        .exec(txn)
        .await?;

    issue_item::Entity::delete_by_id(issue_id).exec(txn).await?;

    Ok(())
}

impl IssueService {
    pub(crate) async fn load_assignee_map(
        &self,
        issue_ids: &[Uuid],
    ) -> Result<std::collections::HashMap<Uuid, Vec<UserDto>>, IssueError> {
        let mut map = std::collections::HashMap::new();
        if issue_ids.is_empty() {
            return Ok(map);
        }
        let rows = issue_member::Entity::find()
            .filter(issue_member::Column::IssueId.is_in(issue_ids.to_vec()))
            .filter(issue_member::Column::Kind.eq(issue_member::IssueMemberKind::Assignee))
            .all(self.db().read())
            .await?;
        let uids: Vec<i64> = rows.iter().map(|r| r.user_id).collect();
        let user_map = self.load_user_briefs(&uids).await?;
        for r in rows {
            if let Some(brief) = user_map.get(&r.user_id) {
                map.entry(r.issue_id)
                    .or_insert_with(Vec::new)
                    .push(brief.clone());
            }
        }
        Ok(map)
    }

    /// Labels per issue for one page of rows, in the board's own label order.
    pub(crate) async fn load_label_map(
        &self,
        issue_ids: &[Uuid],
    ) -> Result<std::collections::HashMap<Uuid, Vec<LabelDto>>, IssueError> {
        let mut map: std::collections::HashMap<Uuid, Vec<LabelDto>> =
            std::collections::HashMap::new();
        if issue_ids.is_empty() {
            return Ok(map);
        }
        let rels = issue_relation_label::Entity::find()
            .filter(issue_relation_label::Column::IssueId.is_in(issue_ids.to_vec()))
            .all(self.db().read())
            .await?;
        if rels.is_empty() {
            return Ok(map);
        }
        let label_ids: Vec<Uuid> = rels.iter().map(|r| r.label_id).collect();
        let labels = issue_label::Entity::find()
            .filter(issue_label::Column::Id.is_in(label_ids))
            .all(self.db().read())
            .await?;
        let by_id: std::collections::HashMap<Uuid, LabelDto> = labels
            .into_iter()
            .map(|label| (label.id, LabelDto::from(label)))
            .collect();
        for rel in rels {
            if let Some(label) = by_id.get(&rel.label_id) {
                map.entry(rel.issue_id).or_default().push(label.clone());
            }
        }
        for rows in map.values_mut() {
            rows.sort_by(|a, b| a.name.cmp(&b.name));
        }
        Ok(map)
    }

    pub(crate) async fn build_issue_dto(
        &self,
        caller: i64,
        item: issue_item::Model,
    ) -> Result<IssueDto, IssueError> {
        let user_map = self.load_user_briefs(&[item.author_id]).await?;
        let author = user_map.get(&item.author_id).cloned();
        let assignees = self
            .load_assignee_map(&[item.id])
            .await?
            .remove(&item.id)
            .unwrap_or_default();
        let state = if item.closed_at.is_some() {
            IssueState::Closed
        } else {
            IssueState::Open
        };
        let viewer_watch = issue_watch::Entity::find()
            .filter(issue_watch::Column::IssueId.eq(item.id))
            .filter(issue_watch::Column::UserId.eq(caller))
            .one(self.db().read())
            .await?
            .map(|w| w.mode);
        Ok(IssueDto {
            viewer_watch,
            id: item.id,
            board_id: item.collection_id,
            number: item.autoincrement_id,
            author,
            title: item.title,
            description: item.description,
            content: item.body,
            milestone_id: item.milestone_id,
            priority: item.priority,
            due_at: item.due_at,
            assignees,
            state,
            created_at: item.created_at,
            updated_at: item.updated_at,
            closed_at: item.closed_at,
        })
    }

    #[tracing::instrument(name = "issues.create_issue", skip_all, fields(caller = %caller, space = %space_name, board_id = %board_id))]
    pub async fn create_issue(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        cmd: CreateIssueCmd,
    ) -> Result<IssueDto, IssueError> {
        if !self
            .state
            .redis
            .quota_allow(
                &format!("quota:issue_create:{caller}"),
                self.config().quota.issue_create_per_day,
                86_400,
            )
            .await
        {
            return Err(IssueError::RateLimited(
                "issue creation quota exceeded, retry tomorrow".to_string(),
            ));
        }
        let (space, board) = board_context(self, caller, space_name, board_id).await?;

        let title = cmd.title.trim();
        if title.is_empty() || title.chars().count() > self.config().issues.max_title_len {
            return Err(IssueError::BadRequest(
                "issue title is empty or too long".to_string(),
            ));
        }
        if cmd.content.chars().count() > self.config().issues.max_body_len {
            return Err(IssueError::BadRequest("issue body is too long".to_string()));
        }

        if let Some(mid) = cmd.milestone_id {
            milestone::Entity::find_by_id(mid)
                .filter(milestone::Column::CollectionId.eq(board.id))
                .one(self.db().read())
                .await?
                .ok_or_else(|| {
                    IssueError::BadRequest("milestone does not belong to this board".to_string())
                })?;
        }

        for a_uid in &cmd.assignee_ids {
            let is_member = *a_uid == space.owner_id
                || space_members::Entity::find_by_id((space.id, *a_uid))
                    .one(self.db().read())
                    .await?
                    .is_some_and(|m| m.status == MemberStatus::Active);
            if !is_member {
                return Err(IssueError::BadRequest(format!(
                    "assignee {a_uid} is not a member of this space"
                )));
            }
        }
        if !cmd.label_ids.is_empty() {
            let count = issue_label::Entity::find()
                .filter(issue_label::Column::Id.is_in(cmd.label_ids.clone()))
                .filter(issue_label::Column::DirId.eq(board.dir_id))
                .count(self.db().read())
                .await?;
            if count != cmd.label_ids.len() as u64 {
                return Err(IssueError::BadRequest(
                    "one or more labels do not belong to this board".to_string(),
                ));
            }
        }

        let full_text = format!("{} {}", title, cmd.content);
        let mentioned_uids = self
            .collect_mentioned_uids(&space, &full_text, caller)
            .await?;

        let now = OffsetDateTime::now_utc();
        let issue_id = Uuid::now_v7();
        let title_str = title.to_string();
        let desc_opt = cmd.description.clone();
        let body_str = cmd.content.clone();
        let milestone_opt = cmd.milestone_id;
        let priority_val = cmd.priority;
        let due_val = cmd.due_at;
        let assignee_ids = cmd.assignee_ids.clone();
        let label_ids = cmd.label_ids.clone();
        let attachment_ids = cmd.attachment_ids.clone();
        let space_lower_for_txn = space.lower_name.clone();
        let mentioned_for_txn = mentioned_uids.clone();
        let this = self.clone();
        let pending = export::notify::PendingNotifications::default();
        let pending_txn = pending.clone();

        let inserted = self
            .db()
            .primary()
            .transaction::<_, issue_item::Model, IssueError>(move |txn| {
                let title_clone = title_str.clone();
                let mentioned_clone = mentioned_for_txn;
                let space_name_clone = space_lower_for_txn;
                Box::pin(async move {
                    let _ = issues::Entity::find_by_id(board.id)
                        .lock_exclusive()
                        .one(txn)
                        .await?;

                    let max_row = issue_item::Entity::find()
                        .filter(issue_item::Column::CollectionId.eq(board.id))
                        .order_by_desc(issue_item::Column::AutoincrementId)
                        .one(txn)
                        .await?;
                    let next_num = max_row.map(|m| m.autoincrement_id + 1).unwrap_or(1);

                    let row = issue_item::ActiveModel {
                        id: Set(issue_id),
                        collection_id: Set(board.id),
                        autoincrement_id: Set(next_num),
                        author_id: Set(caller),
                        title: Set(title_clone.clone()),
                        description: Set(desc_opt),
                        body: Set(body_str),
                        milestone_id: Set(milestone_opt),
                        priority: Set(priority_val),
                        due_at: Set(due_val),
                        created_at: Set(now),
                        updated_at: Set(None),
                        closed_at: Set(None),
                    };
                    let ins = row.insert(txn).await?;

                    for a_uid in assignee_ids.clone() {
                        if a_uid == caller {
                            continue;
                        }
                        let member_row = issue_member::ActiveModel {
                            id: Set(Uuid::now_v7()),
                            issue_id: Set(issue_id),
                            user_id: Set(a_uid),
                            kind: Set(issue_member::IssueMemberKind::Assignee),
                            created_at: Set(now),
                            updated_at: Set(Some(now)),
                        };
                        member_row.insert(txn).await?;
                    }
                    for l_id in label_ids.clone() {
                        let link = issue_relation_label::ActiveModel {
                            id: Set(Uuid::now_v7()),
                            issue_id: Set(issue_id),
                            label_id: Set(l_id),
                            created_at: Set(now),
                        };
                        link.insert(txn).await?;
                    }

                    IssueService::link_issue_attachments(
                        txn,
                        caller,
                        issue_id,
                        &attachment_ids,
                        now,
                    )
                    .await?;

                    this.write_event(
                        txn,
                        issue_id,
                        caller,
                        IssueEventType::Created,
                        IssueEventDetail {
                            comment_id: None,
                            old_title: None,
                            new_title: Some(title_clone.clone()),
                            content: None,
                        },
                    )
                    .await?;

                    for m_uid in mentioned_clone {
                        let existing_member = issue_member::Entity::find()
                            .filter(issue_member::Column::IssueId.eq(issue_id))
                            .filter(issue_member::Column::UserId.eq(m_uid))
                            .filter(
                                issue_member::Column::Kind
                                    .eq(models::issue::issue_member::IssueMemberKind::Mentioned),
                            )
                            .one(txn)
                            .await?;
                        if existing_member.is_none() {
                            let m_row = issue_member::ActiveModel {
                                id: Set(Uuid::now_v7()),
                                issue_id: Set(issue_id),
                                user_id: Set(m_uid),
                                kind: Set(models::issue::issue_member::IssueMemberKind::Mentioned),
                                created_at: Set(now),
                                updated_at: Set(None),
                            };
                            m_row.insert(txn).await?;
                        }

                        let data = serde_json::json!({
                            "space_name": space_name_clone,
                            "board_id": board.id,
                            "issue_id": issue_id,
                            "autoincrement_id": next_num,
                        });
                        this.notify_user(
                            txn,
                            &pending_txn,
                            m_uid,
                            NotificationType::Mention,
                            format!("You were mentioned in issue #{next_num}: {title_clone}"),
                            Some(title_clone.clone()),
                            Some(data),
                            Some(caller),
                        )
                        .await?;
                    }

                    Ok(ins)
                })
            })
            .await?;
        pending.flush(&self.state.redis).await;

        if !mentioned_uids.is_empty() {
            let emails = self.primary_emails(&mentioned_uids).await?;
            for (uid, addr) in emails {
                let locale = self.recipient_locale(uid).await;
                let subject = i18n::catalog::issue_mention_subject(
                    locale,
                    inserted.autoincrement_id,
                    &inserted.title,
                );
                let link = format!(
                    "{}/issues/{}/{}/{}",
                    self.config().web.web_base_url,
                    space.lower_name,
                    board.id,
                    inserted.id
                );
                let text = format!("{subject}\n\n{}: {link}", i18n::catalog::view_issue(locale));
                let html = format!(
                    r#"<p>{subject}</p><p><a href="{link}">{}</a></p>"#,
                    i18n::catalog::view_issue(locale)
                );
                if let Err(err) = self.send_email(&addr, &subject, &text, &html).await {
                    tracing::warn!(error = %err, "failed to send issue mention email");
                }
            }
        }

        self.build_issue_dto(caller, inserted).await
    }

    pub async fn list_issues(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        q: ListIssueQuery,
    ) -> Result<IssuePage, IssueError> {
        let (_, board) = board_context(self, caller, space_name, board_id).await?;

        let (limit, offset) = self.paginate(q.limit, q.offset);

        let mut select =
            issue_item::Entity::find().filter(issue_item::Column::CollectionId.eq(board.id));

        if let Some(state) = q.state {
            match state {
                IssueState::Open => {
                    select = select.filter(issue_item::Column::ClosedAt.is_null());
                }
                IssueState::Closed => {
                    select = select.filter(issue_item::Column::ClosedAt.is_not_null());
                }
            }
        }

        if let Some(author) = q.author_id {
            select = select.filter(issue_item::Column::AuthorId.eq(author));
        }

        let (milestone_ids, include_no_milestone) =
            parse_milestone_filter(q.milestone_id.as_deref())?;
        if include_no_milestone && !milestone_ids.is_empty() {
            select = select.filter(
                Condition::any()
                    .add(issue_item::Column::MilestoneId.is_null())
                    .add(issue_item::Column::MilestoneId.is_in(milestone_ids)),
            );
        } else if include_no_milestone {
            select = select.filter(issue_item::Column::MilestoneId.is_null());
        } else if !milestone_ids.is_empty() {
            select = select.filter(issue_item::Column::MilestoneId.is_in(milestone_ids));
        }

        if let Some(ref search) = q.q {
            let kw = search.trim();
            if !kw.is_empty() {
                select = select.filter(issue_item::Column::Title.contains(kw));
            }
        }

        let label_ids = parse_filter_ids(q.label_id.as_deref())?;
        if !label_ids.is_empty() {
            // Any-of: picking two labels widens the list, matching the panel's
            // checkbox semantics rather than intersecting them.
            let issue_ids: Vec<Uuid> = issue_relation_label::Entity::find()
                .filter(issue_relation_label::Column::LabelId.is_in(label_ids))
                .all(self.db().read())
                .await?
                .into_iter()
                .map(|r| r.issue_id)
                .collect();
            select = select.filter(issue_item::Column::Id.is_in(issue_ids));
        }
        let assignee_ids = parse_filter_ids(q.assignee_id.as_deref())?;
        if !assignee_ids.is_empty() {
            let issue_ids: Vec<Uuid> = issue_member::Entity::find()
                .filter(issue_member::Column::UserId.is_in(assignee_ids))
                .filter(issue_member::Column::Kind.eq(issue_member::IssueMemberKind::Assignee))
                .all(self.db().read())
                .await?
                .into_iter()
                .map(|r| r.issue_id)
                .collect();
            select = select.filter(issue_item::Column::Id.is_in(issue_ids));
        }
        let priorities = parse_filter_priorities(q.priority.as_deref())?;
        if !priorities.is_empty() {
            select = select.filter(issue_item::Column::Priority.is_in(priorities));
        }

        match q.sort.as_deref() {
            Some("updated_at") => select = select.order_by_desc(issue_item::Column::UpdatedAt),
            Some("due_at") => select = select.order_by_asc(issue_item::Column::DueAt),
            Some("priority") => select = select.order_by_desc(issue_item::Column::Priority),
            Some("number") => select = select.order_by_asc(issue_item::Column::AutoincrementId),
            _ => select = select.order_by_desc(issue_item::Column::CreatedAt),
        }

        let total = select.clone().count(self.db().read()).await? as i64;
        let rows = select
            .limit(limit)
            .offset(offset)
            .all(self.db().read())
            .await?;

        let author_ids: Vec<i64> = rows.iter().map(|i| i.author_id).collect();
        let user_map = self.load_user_briefs(&author_ids).await?;
        let issue_ids: Vec<Uuid> = rows.iter().map(|i| i.id).collect();
        let assignee_map = self.load_assignee_map(&issue_ids).await?;
        let label_map = self.load_label_map(&issue_ids).await?;

        let items = rows
            .into_iter()
            .map(|i| {
                let author = user_map.get(&i.author_id).cloned();
                let assignees = assignee_map.get(&i.id).cloned().unwrap_or_default();
                let labels = label_map.get(&i.id).cloned().unwrap_or_default();
                let state = if i.closed_at.is_some() {
                    IssueState::Closed
                } else {
                    IssueState::Open
                };
                IssueListItemDto {
                    id: i.id,
                    board_id: i.collection_id,
                    number: i.autoincrement_id,
                    author,
                    title: i.title,
                    labels,
                    milestone_id: i.milestone_id,
                    priority: i.priority,
                    due_at: i.due_at,
                    assignees,
                    state,
                    created_at: i.created_at,
                    updated_at: i.updated_at,
                    closed_at: i.closed_at,
                }
            })
            .collect();

        Ok(IssuePage {
            items,
            total,
            limit,
            offset,
        })
    }

    pub async fn get_issue(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        issue_id: Uuid,
    ) -> Result<IssueDto, IssueError> {
        let (_, _, item) = issue_context(self, caller, space_name, board_id, issue_id).await?;

        self.build_issue_dto(caller, item).await
    }

    #[tracing::instrument(name = "issues.update_issue", skip_all, fields(caller = %caller, space = %space_name, board_id = %board_id, issue_id = %issue_id))]
    pub async fn update_issue(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        issue_id: Uuid,
        cmd: UpdateIssueCmd,
    ) -> Result<IssueDto, IssueError> {
        let (space, board, item) =
            issue_context(self, caller, space_name, board_id, issue_id).await?;

        if item.author_id != caller && !is_board_manager(self, &space, caller).await? {
            return Err(IssueError::Forbidden);
        }

        let old_title = item.title.clone();
        let old_description = item.description.clone();
        let old_body = item.body.clone();
        let old_milestone = item.milestone_id;

        let mut title_changed = None;
        if let Some(new_title) = cmd.title {
            let t = new_title.trim();
            if t.is_empty() || t.chars().count() > self.config().issues.max_title_len {
                return Err(IssueError::BadRequest(
                    "issue title is empty or too long".to_string(),
                ));
            }
            if t != old_title {
                title_changed = Some(t.to_string());
            }
        }

        if cmd
            .content
            .as_ref()
            .is_some_and(|b| b.chars().count() > self.config().issues.max_body_len)
        {
            return Err(IssueError::BadRequest("issue body is too long".to_string()));
        }

        let mut milestone_changed = false;
        let mut new_milestone_val = old_milestone;
        if let Some(mid_opt) = cmd.milestone_id {
            if let Some(mid) = mid_opt {
                milestone::Entity::find_by_id(mid)
                    .filter(milestone::Column::CollectionId.eq(board.id))
                    .one(self.db().read())
                    .await?
                    .ok_or_else(|| {
                        IssueError::BadRequest(
                            "milestone does not belong to this board".to_string(),
                        )
                    })?;
            }
            if mid_opt != old_milestone {
                milestone_changed = true;
                new_milestone_val = mid_opt;
            }
        }

        for a_uid in cmd.assignee_ids.iter().flatten() {
            let is_member = *a_uid == space.owner_id
                || space_members::Entity::find_by_id((space.id, *a_uid))
                    .one(self.db().read())
                    .await?
                    .is_some_and(|m| m.status == MemberStatus::Active);
            if !is_member {
                return Err(IssueError::BadRequest(format!(
                    "assignee {a_uid} is not a member of this space"
                )));
            }
        }
        if let Some(label_ids) = &cmd.label_ids
            && !label_ids.is_empty()
        {
            let count = issue_label::Entity::find()
                .filter(issue_label::Column::Id.is_in(label_ids.clone()))
                .filter(issue_label::Column::DirId.eq(board.dir_id))
                .count(self.db().read())
                .await?;
            if count != label_ids.len() as u64 {
                return Err(IssueError::BadRequest(
                    "one or more labels do not belong to this board".to_string(),
                ));
            }
        }

        let now = OffsetDateTime::now_utc();
        let mut description_changed: Option<Option<String>> = None;
        let mut body_changed: Option<String> = None;
        let mut am: issue_item::ActiveModel = item.into();
        if let Some(priority) = cmd.priority {
            am.priority = Set(priority);
        }
        if let Some(due) = cmd.due_at {
            am.due_at = Set(due);
        }
        if let Some(ref t) = title_changed {
            am.title = Set(t.clone());
        }
        if let Some(desc) = cmd.description {
            if desc != old_description {
                description_changed = Some(desc.clone());
            }
            am.description = Set(desc);
        }
        if let Some(b) = cmd.content {
            if b != old_body {
                body_changed = Some(b.clone());
            }
            am.body = Set(b);
        }
        if milestone_changed {
            am.milestone_id = Set(new_milestone_val);
        }
        am.updated_at = Set(Some(now));
        let assignee_ids_val = cmd.assignee_ids.clone();
        let label_ids_val = cmd.label_ids.clone();
        let this = self.clone();

        let updated = self
            .db()
            .primary()
            .transaction::<_, issue_item::Model, IssueError>(move |txn| {
                let title_change_clone = title_changed.clone();
                let desc_change_clone = description_changed.clone();
                let body_change_clone = body_changed.clone();
                let assignee_replace = assignee_ids_val.clone();
                let label_replace = label_ids_val.clone();
                Box::pin(async move {
                    let u = am.update(txn).await?;

                    if let Some(new_assignees) = assignee_replace {
                        issue_member::Entity::delete_many()
                            .filter(issue_member::Column::IssueId.eq(u.id))
                            .filter(
                                issue_member::Column::Kind
                                    .eq(issue_member::IssueMemberKind::Assignee),
                            )
                            .exec(txn)
                            .await?;
                        for a_uid in new_assignees {
                            if a_uid == u.author_id {
                                continue;
                            }
                            let member_row = issue_member::ActiveModel {
                                id: Set(Uuid::now_v7()),
                                issue_id: Set(u.id),
                                user_id: Set(a_uid),
                                kind: Set(issue_member::IssueMemberKind::Assignee),
                                created_at: Set(OffsetDateTime::now_utc()),
                                updated_at: Set(Some(OffsetDateTime::now_utc())),
                            };
                            member_row.insert(txn).await?;
                        }
                    }

                    if let Some(new_labels) = label_replace {
                        let old_links = issue_relation_label::Entity::find()
                            .filter(issue_relation_label::Column::IssueId.eq(u.id))
                            .all(txn)
                            .await?;
                        let old_set: std::collections::HashSet<Uuid> =
                            old_links.iter().map(|l| l.label_id).collect();
                        let new_set: std::collections::HashSet<Uuid> =
                            new_labels.iter().cloned().collect();
                        for l in old_links {
                            if !new_set.contains(&l.label_id) {
                                issue_relation_label::Entity::delete_by_id(l.id)
                                    .exec(txn)
                                    .await?;
                            }
                        }
                        for lid in &new_labels {
                            if old_set.contains(lid) {
                                continue;
                            }
                            let link = issue_relation_label::ActiveModel {
                                id: Set(Uuid::now_v7()),
                                issue_id: Set(u.id),
                                label_id: Set(*lid),
                                created_at: Set(OffsetDateTime::now_utc()),
                            };
                            link.insert(txn).await?;
                        }
                    }

                    if let Some(new_t) = title_change_clone {
                        this.write_event(
                            txn,
                            issue_id,
                            caller,
                            IssueEventType::TitleUpdated,
                            IssueEventDetail {
                                comment_id: None,
                                old_title: Some(old_title.clone()),
                                new_title: Some(new_t.clone()),
                                content: None,
                            },
                        )
                        .await?;

                        issue_content_history::ActiveModel {
                            id: Set(Uuid::now_v7()),
                            issue_id: Set(issue_id),
                            poster_id: Set(caller),
                            comment_id: Set(None),
                            content_before: Set(Some(old_title)),
                            content_after: Set(Some(new_t)),
                            edited_at: Set(now),
                            created_at: Set(now),
                        }
                        .insert(txn)
                        .await?;
                    }

                    if let Some(new_desc) = desc_change_clone {
                        issue_content_history::ActiveModel {
                            id: Set(Uuid::now_v7()),
                            issue_id: Set(issue_id),
                            poster_id: Set(caller),
                            comment_id: Set(None),
                            content_before: Set(old_description),
                            content_after: Set(new_desc),
                            edited_at: Set(now),
                            created_at: Set(now),
                        }
                        .insert(txn)
                        .await?;
                    }

                    if let Some(new_body) = body_change_clone {
                        issue_content_history::ActiveModel {
                            id: Set(Uuid::now_v7()),
                            issue_id: Set(issue_id),
                            poster_id: Set(caller),
                            comment_id: Set(None),
                            content_before: Set(Some(old_body)),
                            content_after: Set(Some(new_body)),
                            edited_at: Set(now),
                            created_at: Set(now),
                        }
                        .insert(txn)
                        .await?;
                    }

                    if milestone_changed {
                        let old_name = if let Some(omid) = old_milestone {
                            let m = milestone::Entity::find_by_id(omid).one(txn).await?;
                            m.map(|m| m.name).unwrap_or_else(|| omid.to_string())
                        } else {
                            "none".to_string()
                        };
                        let new_name = if let Some(nmid) = new_milestone_val {
                            let m = milestone::Entity::find_by_id(nmid).one(txn).await?;
                            m.map(|m| m.name).unwrap_or_else(|| nmid.to_string())
                        } else {
                            "none".to_string()
                        };
                        let content = format!("{old_name} -> {new_name}");
                        this.write_event(
                            txn,
                            issue_id,
                            caller,
                            IssueEventType::MilestoneUpdated,
                            IssueEventDetail {
                                comment_id: None,
                                old_title: None,
                                new_title: None,
                                content: Some(content),
                            },
                        )
                        .await?;
                    }

                    Ok(u)
                })
            })
            .await?;

        self.build_issue_dto(caller, updated).await
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize, ToSchema)]
#[serde(rename_all = "snake_case")]
pub enum BulkAction {
    Close,
    Reopen,
}

#[derive(Debug, Clone, Deserialize, ToSchema)]
pub struct BulkIssuesCmd {
    pub action: BulkAction,
    pub issue_refs: Vec<String>,
}

#[derive(Debug, Clone, Serialize, ToSchema)]
pub struct BulkResultDto {
    pub updated: u64,
}

impl IssueService {
    #[tracing::instrument(name = "issues.bulk_update_state", skip_all, fields(caller = %caller, space = %space_name, board_id = %board_id))]
    pub async fn bulk_update_state(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        cmd: BulkIssuesCmd,
    ) -> Result<BulkResultDto, IssueError> {
        if cmd.issue_refs.is_empty() || cmd.issue_refs.len() > 100 {
            return Err(IssueError::BadRequest(
                "issue_refs must contain 1..=100 entries".to_string(),
            ));
        }
        let (space, board) = board_context(self, caller, space_name, board_id).await?;
        let is_manager = is_board_manager(self, &space, caller).await?;

        let mut updated = 0u64;
        for issue_ref in cmd.issue_refs {
            let Ok(item) = lookup_issue_by_ref(self, &board, &issue_ref).await else {
                continue;
            };
            let is_closed = item.closed_at.is_some();
            let should_close = matches!(cmd.action, BulkAction::Close);
            if is_closed == should_close {
                continue;
            }
            if item.author_id != caller && !is_manager {
                continue;
            }
            let now = OffsetDateTime::now_utc();
            issue_item::Entity::update_many()
                .col_expr(
                    issue_item::Column::ClosedAt,
                    sea_orm::sea_query::Expr::value(should_close.then_some(now)),
                )
                .col_expr(
                    issue_item::Column::UpdatedAt,
                    sea_orm::sea_query::Expr::value(now),
                )
                .filter(issue_item::Column::Id.eq(item.id))
                .exec(self.db().write())
                .await?;
            self.write_event(
                self.db().write(),
                item.id,
                caller,
                if should_close {
                    IssueEventType::Closed
                } else {
                    IssueEventType::Reopened
                },
                IssueEventDetail::default(),
            )
            .await?;
            updated += 1;
        }
        Ok(BulkResultDto { updated })
    }
}

impl IssueService {
    #[tracing::instrument(name = "issues.delete_issue", skip_all, fields(caller = %caller, space = %space_name, board_id = %board_id, issue_id = %issue_id))]
    pub async fn delete_issue(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        issue_id: Uuid,
    ) -> Result<(), IssueError> {
        let (space, _, item) = issue_context(self, caller, space_name, board_id, issue_id).await?;

        if item.author_id != caller && !is_board_manager(self, &space, caller).await? {
            return Err(IssueError::Forbidden);
        }

        // Objects live outside the transaction: collect the keys first, drop
        // the rows in the cascade, then clean storage best effort.
        let attachment_keys =
            IssueService::issue_attachment_keys(self.db().read(), issue_id).await?;
        self.db()
            .primary()
            .transaction::<_, (), IssueError>(|txn| {
                Box::pin(async move {
                    delete_issue_cascade_in_txn(txn, issue_id).await?;
                    Ok(())
                })
            })
            .await?;

        for key in attachment_keys {
            if let Err(err) = self.state.storage.client().delete(&key).await {
                tracing::warn!(error = %err, key = %key, "failed to delete issue attachment object");
            }
        }

        Ok(())
    }

    #[tracing::instrument(name = "issues.close_issue", skip_all, fields(caller = %caller, space = %space_name, board_id = %board_id, issue_id = %issue_id))]
    pub async fn close_issue(
        &self,
        caller: i64,
        space_name: &str,
        board_id: Uuid,
        issue_id: Uuid,
        closed: bool,
    ) -> Result<IssueDto, IssueError> {
        let (space, _, item) = issue_context(self, caller, space_name, board_id, issue_id).await?;

        if item.author_id != caller && !is_board_manager(self, &space, caller).await? {
            return Err(IssueError::Forbidden);
        }

        let is_currently_closed = item.closed_at.is_some();
        if is_currently_closed == closed {
            return self.build_issue_dto(caller, item).await;
        }

        let now = OffsetDateTime::now_utc();
        let mut am: issue_item::ActiveModel = item.into();
        am.closed_at = Set(closed.then_some(now));
        am.updated_at = Set(Some(now));

        let event_type = if closed {
            IssueEventType::Closed
        } else {
            IssueEventType::Reopened
        };
        let this = self.clone();

        let updated = self
            .db()
            .primary()
            .transaction::<_, issue_item::Model, IssueError>(move |txn| {
                Box::pin(async move {
                    let u = am.update(txn).await?;
                    this.write_event(
                        txn,
                        issue_id,
                        caller,
                        event_type,
                        IssueEventDetail::default(),
                    )
                    .await?;
                    Ok(u)
                })
            })
            .await?;

        self.build_issue_dto(caller, updated).await
    }
}