//! The ticket store: the rows behind a repository's tickets, and the
//! lookups the webui makes of them.
//!
//! Split from [`super`], which holds the rules that do not need a database.

use std::collections::{HashMap, HashSet};

use sea_orm::ActiveValue::Set;
use sea_orm::sea_query::OnConflict;
use sea_orm::{
    ActiveModelTrait, ColumnTrait, ConnectionTrait, DatabaseConnection, DbBackend, DbErr,
    EntityTrait, PaginatorTrait, QueryFilter, QueryOrder, Statement,
};
use uuid::Uuid;

use super::{DEFAULT_LABELS, search_expression};
use crate::entities::{
    ticket_attachments, ticket_label_assignments, ticket_labels, tickets, users,
};

/// A repository's labels.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn labels(
    db: &DatabaseConnection,
    repository_id: i64,
) -> Result<Vec<ticket_labels::Model>, DbErr> {
    list_labels(db, repository_id).await
}

/// The labels a repository is missing of [`DEFAULT_LABELS`], and no others.
///
/// A repository that already has all of them - because it was listed, or
/// because its owner renamed one and made it again - is left exactly as it
/// stands: the defaults are what a repository starts with, not what it is
/// corrected back to.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn ensure_default_labels(
    db: &DatabaseConnection,
    repository_id: i64,
) -> Result<(), DbErr> {
    let present: HashSet<String> = list_labels(db, repository_id)
        .await?
        .into_iter()
        .map(|label| label.name)
        .collect();

    let missing: Vec<ticket_labels::ActiveModel> = DEFAULT_LABELS
        .iter()
        .filter(|name| !present.contains(**name))
        .map(|name| ticket_labels::ActiveModel {
            repository_id: Set(repository_id),
            name: Set((*name).to_string()),
            ..Default::default()
        })
        .collect();

    if missing.is_empty() {
        return Ok(());
    }

    // The pair of repository and name is what makes a filter able to name
    // its label, so two requests making the same label at once is one label
    // rather than one page answering with a constraint violation.
    ticket_labels::Entity::insert_many(missing)
        .on_conflict(
            OnConflict::columns([
                ticket_labels::Column::RepositoryId,
                ticket_labels::Column::Name,
            ])
            .do_nothing()
            .to_owned(),
        )
        .exec(db)
        .await?;

    Ok(())
}

/// The labels of a repository, in the order the repository made them.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn list_labels(
    db: &DatabaseConnection,
    repository_id: i64,
) -> Result<Vec<ticket_labels::Model>, DbErr> {
    ticket_labels::Entity::find()
        .filter(ticket_labels::Column::RepositoryId.eq(repository_id))
        .order_by_asc(ticket_labels::Column::Id)
        .all(db)
        .await
}

/// One label of a repository, looked up the way a filter names it: by name.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_label(
    db: &DatabaseConnection,
    repository_id: i64,
    name: &str,
) -> Result<Option<ticket_labels::Model>, DbErr> {
    ticket_labels::Entity::find()
        .filter(ticket_labels::Column::RepositoryId.eq(repository_id))
        .filter(ticket_labels::Column::Name.eq(name))
        .one(db)
        .await
}

/// One label by its id, regardless of repository.
///
/// Used to check whether a label exists before deletion; the handler then
/// scopes it to the repository.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_label_by_id(
    db: &DatabaseConnection,
    label_id: i64,
) -> Result<Option<ticket_labels::Model>, DbErr> {
    ticket_labels::Entity::find_by_id(label_id).one(db).await
}

/// A new label for `repository_id`, refused by the repository's own unique
/// pair of repository and name if the same name is being made at the same
/// moment.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn create_label(
    db: &DatabaseConnection,
    repository_id: i64,
    name: &str,
) -> Result<ticket_labels::Model, DbErr> {
    ticket_labels::ActiveModel {
        repository_id: Set(repository_id),
        name: Set(name.to_string()),
        ..Default::default()
    }
    .insert(db)
    .await
}

/// Removes a label from `repository_id`.
///
/// Returns true if the label existed and was deleted, false otherwise.
/// The label must belong to the repository (scoped by repository_id).
#[tracing::instrument(level = "debug", skip(db))]
pub async fn delete_label(
    db: &DatabaseConnection,
    repository_id: i64,
    label_id: i64,
) -> Result<bool, DbErr> {
    let result = ticket_labels::Entity::delete_by_id(label_id)
        .filter(ticket_labels::Column::RepositoryId.eq(repository_id))
        .exec(db)
        .await?;
    Ok(result.rows_affected > 0)
}

/// The repository's tickets as the list page shows them: newest first, every
/// one carrying every label the filter asks for, and - when the search box
/// holds something - among the ones it found.
///
/// The two are one view rather than two: a ticket the filter leaves out is
/// not found by a search either.
#[tracing::instrument(level = "debug", skip(db, state, selected), fields(words = query.len()))]
pub async fn list_tickets(
    db: &DatabaseConnection,
    repository_id: i64,
    state: Option<tickets::State>,
    selected: &[String],
    query: &str,
) -> Result<Vec<tickets::Model>, DbErr> {
    let found = match search_expression(query) {
        Some(expression) => {
            let found = search_ticket_ids(db, &expression).await?;
            if found.is_empty() {
                return Ok(Vec::new());
            }
            Some(found)
        }
        // A search box holding nothing worth searching for has not searched
        // for anything; one holding only things the engine has opinions of
        // its own about - a stray quote, a `*` - is a search for those
        // characters, which matches nothing rather than being refused.
        None if !query.trim().is_empty() => return Ok(Vec::new()),
        None => None,
    };

    let mut select =
        tickets::Entity::find().filter(tickets::Column::RepositoryId.eq(repository_id));

    // Only filter by state if a specific state was requested
    if let Some(state) = state {
        select = select.filter(tickets::Column::State.eq(state));
    }

    if let Some(found) = found {
        select = select.filter(tickets::Column::Id.is_in(found));
    }

    let mut listed = select
        .order_by_desc(tickets::Column::Number)
        .all(db)
        .await?;

    // An empty filter shows everything, and an empty list has nothing left
    // to filter - either way the assignments below would say no more than
    // this does.
    if listed.is_empty() || selected.is_empty() {
        return Ok(listed);
    }

    // The labels to keep by, looked up in this repository's own. A name this
    // repository does not have matches nothing instead of being let through:
    // a ticket cannot carry a label it could never be filtered by, so a
    // filter naming one has no tickets to show.
    let mut wanted: Vec<i64> = Vec::with_capacity(selected.len());
    for name in selected {
        match find_label(db, repository_id, name).await? {
            Some(label) => wanted.push(label.id),
            None => return Ok(Vec::new()),
        }
    }

    let ids: Vec<i64> = listed.iter().map(|ticket| ticket.id).collect();
    let mut held: HashMap<i64, HashSet<i64>> = HashMap::new();
    for assignment in ticket_label_assignments::Entity::find()
        .filter(ticket_label_assignments::Column::TicketId.is_in(ids))
        .all(db)
        .await?
    {
        held.entry(assignment.ticket_id)
            .or_default()
            .insert(assignment.label_id);
    }

    // Every one of the labels, not any of them: a filter of `bug,improvement` is a
    // ticket that is both a bug and an improvement.
    listed.retain(|ticket| {
        wanted.iter().all(|label| {
            held.get(&ticket.id)
                .is_some_and(|held| held.contains(label))
        })
    });

    Ok(listed)
}

/// The rows a search finds across every ticket, as their ids.
///
/// FTS5 answers a `MATCH`, which is not something the query builder writes,
/// so the search itself is raw SQL. The expression is passed as a bound
/// value rather than pasted into the statement - though
/// [`super::search_expression`] has already reduced it to quoted words and
/// `*` for all that.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn search_ticket_ids(
    db: &DatabaseConnection,
    expression: &str,
) -> Result<Vec<i64>, DbErr> {
    let statement = Statement::from_sql_and_values(
        DbBackend::Sqlite,
        "SELECT rowid AS id FROM ticket_search WHERE ticket_search MATCH ?",
        vec![expression.to_string().into()],
    );

    let mut found = Vec::new();
    for row in db.query_all_raw(statement).await? {
        found.push(row.try_get::<i64>("", "id")?);
    }

    Ok(found)
}

/// One ticket of a repository, addressed by the number it is known by.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_ticket(
    db: &DatabaseConnection,
    repository_id: i64,
    number: i64,
) -> Result<Option<tickets::Model>, DbErr> {
    tickets::Entity::find()
        .filter(tickets::Column::RepositoryId.eq(repository_id))
        .filter(tickets::Column::Number.eq(number))
        .one(db)
        .await
}

/// The labels of each of `ticket_ids`, as their names, in the order the
/// repository made them.
///
/// One lookup for the whole list rather than one per ticket: a page showing
/// thirty tickets should not make thirty-one more queries to say what each
/// one is filed under.
#[tracing::instrument(level = "debug", skip(db, ticket_ids))]
pub async fn labels_by_ticket(
    db: &DatabaseConnection,
    ticket_ids: &[i64],
) -> Result<HashMap<i64, Vec<String>>, DbErr> {
    let mut named: HashMap<i64, Vec<String>> = HashMap::new();
    if ticket_ids.is_empty() {
        return Ok(named);
    }

    let assignments = ticket_label_assignments::Entity::find()
        .filter(ticket_label_assignments::Column::TicketId.is_in(ticket_ids.to_vec()))
        .all(db)
        .await?;
    if assignments.is_empty() {
        return Ok(named);
    }

    let label_ids: HashSet<i64> = assignments
        .iter()
        .map(|assignment| assignment.label_id)
        .collect();
    let mut name_of: HashMap<i64, String> = HashMap::new();
    for label in ticket_labels::Entity::find()
        .filter(ticket_labels::Column::Id.is_in(label_ids))
        .all(db)
        .await?
    {
        name_of.insert(label.id, label.name);
    }

    let mut held: HashMap<i64, Vec<i64>> = HashMap::new();
    for assignment in assignments {
        held.entry(assignment.ticket_id)
            .or_default()
            .push(assignment.label_id);
    }

    for (ticket_id, mut label_ids) in held {
        // Label ids were handed out in creation order, which is the order
        // the repository's own labels are offered in.
        label_ids.sort_unstable();
        let names = label_ids
            .into_iter()
            .filter_map(|label_id| name_of.get(&label_id).cloned())
            .collect();
        named.insert(ticket_id, names);
    }

    Ok(named)
}

/// The names of the users who opened `creator_ids`, for saying on a ticket
/// who opened it.
#[tracing::instrument(level = "debug", skip(db, creator_ids))]
pub async fn creator_names(
    db: &DatabaseConnection,
    creator_ids: &[i64],
) -> Result<HashMap<i64, String>, DbErr> {
    let mut names = HashMap::new();
    if creator_ids.is_empty() {
        return Ok(names);
    }

    for user in users::Entity::find()
        .filter(users::Column::Id.is_in(creator_ids.to_vec()))
        .all(db)
        .await?
    {
        names.insert(user.id, user.name);
    }

    Ok(names)
}

/// The authors who created tickets, looking up both local users and remote actors.
#[tracing::instrument(level = "debug", skip(db, creator_ids, remote_actor_ids))]
pub async fn creator_authors(
    db: &DatabaseConnection,
    creator_ids: &[i64],
    remote_actor_ids: &[i64],
) -> Result<HashMap<i64, crate::comments::CommentAuthor>, DbErr> {
    let mut authors = HashMap::new();

    // Look up local users
    if !creator_ids.is_empty() {
        for user in users::Entity::find()
            .filter(users::Column::Id.is_in(creator_ids.to_vec()))
            .all(db)
            .await?
        {
            authors.insert(
                user.id,
                crate::comments::CommentAuthor {
                    name: user.name,
                    actor_url: None,
                    is_remote: false,
                },
            );
        }
    }

    // Look up remote actors
    if !remote_actor_ids.is_empty() {
        use crate::entities::ap_remote_actors;
        for actor in ap_remote_actors::Entity::find()
            .filter(ap_remote_actors::Column::Id.is_in(remote_actor_ids.to_vec()))
            .all(db)
            .await?
        {
            let name = actor
                .preferred_username
                .or(actor.name)
                .unwrap_or_else(|| "unknown".to_string());

            authors.insert(
                actor.id,
                crate::comments::CommentAuthor {
                    name,
                    actor_url: Some(actor.actor_url),
                    is_remote: true,
                },
            );
        }
    }

    Ok(authors)
}

/// The number for the next ticket of `repository_id`, which is never given
/// out twice.
///
/// The count lives on the repository and is moved on in the same statement
/// that reads it, so two tickets opened at once are two numbers rather than
/// a race for one. What comes back is the number the repository was at:
/// counting what it has already handed out rather than what still exists is
/// the whole point, because a deleted ticket is still talked about by its
/// number and the number therefore cannot come to mean another ticket.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn take_ticket_number(db: &DatabaseConnection, repository_id: i64) -> Result<i64, DbErr> {
    // `RETURNING` is SQLite's own way of answering with the row a statement
    // changed.
    let statement = Statement::from_sql_and_values(
        DbBackend::Sqlite,
        "UPDATE repositories SET next_ticket_number = next_ticket_number + 1 \
         WHERE id = ? RETURNING next_ticket_number",
        vec![repository_id.into()],
    );

    let row = db
        .query_one_raw(statement)
        .await?
        .ok_or_else(|| DbErr::RecordNotFound(format!("repository {repository_id} is not there")))?;
    let counted: i64 = row.try_get::<i64>("", "next_ticket_number")?;

    Ok(counted - 1)
}

/// A new ticket on `repository_id`, numbered as the repository's next.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn create_ticket(
    db: &DatabaseConnection,
    repository_id: i64,
    creator_id: i64,
    number: i64,
    summary: &str,
    description: Option<&str>,
    state: tickets::State,
) -> Result<tickets::Model, DbErr> {
    tickets::ActiveModel {
        uuid: Set(Uuid::new_v4()),
        repository_id: Set(repository_id),
        creator_id: Set(Some(creator_id)),
        remote_actor_id: Set(None),
        remote_offer_id: Set(None),
        resolve_activity_url: Set(None),
        number: Set(number),
        summary: Set(summary.to_string()),
        description: Set(description.map(str::to_string)),
        state: Set(state),
        ..Default::default()
    }
    .insert(db)
    .await
}

/// Creates a ticket from a remote Offer (inbound federation).
///
/// The `remote_offer_id` is the Offer activity's id, used for idempotency:
/// duplicate Offers return the existing ticket rather than creating a new one.
#[allow(clippy::too_many_arguments)]
pub async fn create_ticket_from_remote(
    db: &DatabaseConnection,
    repository_id: i64,
    remote_actor_id: i64,
    number: i64,
    summary: &str,
    description: Option<&str>,
    state: tickets::State,
    remote_offer_id: &str,
) -> Result<tickets::Model, DbErr> {
    tickets::ActiveModel {
        uuid: Set(Uuid::new_v4()),
        repository_id: Set(repository_id),
        creator_id: Set(None),
        remote_actor_id: Set(Some(remote_actor_id)),
        remote_offer_id: Set(Some(remote_offer_id.to_string())),
        resolve_activity_url: Set(None),
        number: Set(number),
        summary: Set(summary.to_string()),
        description: Set(description.map(str::to_string)),
        state: Set(state),
        ..Default::default()
    }
    .insert(db)
    .await
}

/// A ticket as it was edited to be, said in the words of the columns rather
/// than in SQLite's.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn update_ticket(
    db: &DatabaseConnection,
    ticket_id: i64,
    summary: &str,
    description: Option<&str>,
    state: tickets::State,
    resolve_activity_url: Option<String>,
) -> Result<(), DbErr> {
    tickets::Entity::update_many()
        .filter(tickets::Column::Id.eq(ticket_id))
        .set(tickets::ActiveModel {
            summary: Set(summary.to_string()),
            description: Set(description.map(str::to_string)),
            state: Set(state),
            resolve_activity_url: Set(resolve_activity_url),
            updated_at: Set(chrono::Utc::now().fixed_offset()),
            ..Default::default()
        })
        .exec(db)
        .await?;

    Ok(())
}

/// Gives `ticket_id` exactly `label_ids`, dropping whatever it carried
/// before: an edit is the ticket's labels as they are now, not as they were
/// with the touched ones swapped out.
#[tracing::instrument(level = "debug", skip(db, label_ids))]
pub async fn set_ticket_labels(
    db: &DatabaseConnection,
    ticket_id: i64,
    label_ids: &[i64],
) -> Result<(), DbErr> {
    ticket_label_assignments::Entity::delete_many()
        .filter(ticket_label_assignments::Column::TicketId.eq(ticket_id))
        .exec(db)
        .await?;

    if label_ids.is_empty() {
        return Ok(());
    }

    let assigned: Vec<ticket_label_assignments::ActiveModel> = label_ids
        .iter()
        .map(|label_id| ticket_label_assignments::ActiveModel {
            ticket_id: Set(ticket_id),
            label_id: Set(*label_id),
            ..Default::default()
        })
        .collect();

    // The same label checked twice in one form is still one label on the
    // ticket, and the unique pair is what says so.
    ticket_label_assignments::Entity::insert_many(assigned)
        .on_conflict(
            OnConflict::columns([
                ticket_label_assignments::Column::TicketId,
                ticket_label_assignments::Column::LabelId,
            ])
            .do_nothing()
            .to_owned(),
        )
        .exec(db)
        .await?;

    Ok(())
}

/// A ticket taken down, along with what it was labelled with - the foreign
/// key between them is a cascade, so the assignments go with their ticket
/// rather than being left as labels of nothing.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn delete_ticket(db: &DatabaseConnection, ticket_id: i64) -> Result<(), DbErr> {
    tickets::Entity::delete_many()
        .filter(tickets::Column::Id.eq(ticket_id))
        .exec(db)
        .await?;

    Ok(())
}

/// Lists all attachments for a given ticket, ordered by creation time.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn list_attachments(
    db: &DatabaseConnection,
    ticket_id: i64,
) -> Result<Vec<ticket_attachments::Model>, DbErr> {
    ticket_attachments::Entity::find()
        .filter(ticket_attachments::Column::TicketId.eq(ticket_id))
        .order_by_asc(ticket_attachments::Column::CreatedAt)
        .all(db)
        .await
}

/// Gets a single attachment by its UUID.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn find_attachment_by_uuid(
    db: &DatabaseConnection,
    uuid: Uuid,
) -> Result<Option<ticket_attachments::Model>, DbErr> {
    ticket_attachments::Entity::find()
        .filter(ticket_attachments::Column::Uuid.eq(uuid))
        .one(db)
        .await
}

/// Creates a new attachment for a ticket.
#[tracing::instrument(level = "debug", skip(db, data))]
pub async fn create_attachment(
    db: &DatabaseConnection,
    ticket_id: i64,
    filename: &str,
    content_type: &str,
    data: Vec<u8>,
) -> Result<ticket_attachments::Model, DbErr> {
    ticket_attachments::ActiveModel {
        uuid: Set(Uuid::new_v4()),
        ticket_id: Set(ticket_id),
        filename: Set(filename.to_string()),
        content_type: Set(content_type.to_string()),
        size: Set(data.len() as i64),
        data: Set(data),
        ..Default::default()
    }
    .insert(db)
    .await
}

/// Counts how many attachments a ticket currently has.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn count_attachments(db: &DatabaseConnection, ticket_id: i64) -> Result<u64, DbErr> {
    ticket_attachments::Entity::find()
        .filter(ticket_attachments::Column::TicketId.eq(ticket_id))
        .count(db)
        .await
}

/// Deletes an attachment by its ID.
#[tracing::instrument(level = "debug", skip(db))]
pub async fn delete_attachment(db: &DatabaseConnection, attachment_id: i64) -> Result<(), DbErr> {
    ticket_attachments::Entity::delete_many()
        .filter(ticket_attachments::Column::Id.eq(attachment_id))
        .exec(db)
        .await?;

    Ok(())
}