use models::space::space_extras::{self, Entity as SpaceExtras};
use models::space::space_members::{self, Entity as Members};
use models::space::spaces::{self, Entity as Spaces};
use models::space::{SpaceJoinPolicy, SpaceStatus};
use sea_orm::{ActiveModelTrait, ColumnTrait, EntityTrait, QueryFilter, QuerySelect, Set};
use time::OffsetDateTime;
use uuid::Uuid;

use crate::errors::SpaceError;
use crate::services::service::SpaceService;

pub fn to_time(value: chrono::DateTime<chrono::Utc>) -> OffsetDateTime {
    OffsetDateTime::from_unix_timestamp(value.timestamp())
        .map(|row| {
            row.replace_nanosecond(value.timestamp_subsec_nanos())
                .unwrap_or(row)
        })
        .unwrap_or_else(|_| OffsetDateTime::now_utc())
}

pub fn to_time_opt(value: Option<chrono::DateTime<chrono::Utc>>) -> Option<OffsetDateTime> {
    value.map(to_time)
}

pub fn space_is_active(space: &spaces::Model) -> bool {
    space.status == SpaceStatus::Active && space.archived_at.is_none()
}

pub async fn extras(
    db: &impl sea_orm::ConnectionTrait,
    space_id: Uuid,
) -> Result<Option<space_extras::Model>, SpaceError> {
    Ok(SpaceExtras::find_by_id(space_id).one(db).await?)
}

pub async fn member_count(state: &SpaceService, space_id: Uuid) -> Result<i64, SpaceError> {
    use sea_orm::PaginatorTrait;

    let count = Members::find()
        .filter(space_members::Column::OrganizationId.eq(space_id))
        .filter(space_members::Column::LeftAt.is_null())
        .filter(space_members::Column::BannedAt.is_null())
        .count(state.access.database.read())
        .await?;
    Ok(count as i64)
}

pub async fn space_by_slug(state: &SpaceService, slug: &str) -> Result<spaces::Model, SpaceError> {
    Spaces::find()
        .filter(spaces::Column::Slug.eq(slug.trim().to_lowercase()))
        .filter(spaces::Column::DeletedAt.is_null())
        .one(state.access.database.read())
        .await?
        .ok_or(SpaceError::NotFound)
}

pub async fn member(
    state: &SpaceService,
    space_id: Uuid,
    user_id: i64,
) -> Result<Option<space_members::Model>, SpaceError> {
    Ok(Members::find()
        .filter(space_members::Column::OrganizationId.eq(space_id))
        .filter(space_members::Column::UserId.eq(user_id))
        .one(state.access.database.read())
        .await?)
}

pub async fn list_members(
    state: &SpaceService,
    space_id: Uuid,
    include_inactive: bool,
    include_banned: bool,
) -> Result<Vec<space_members::Model>, SpaceError> {
    let mut query = Members::find().filter(space_members::Column::OrganizationId.eq(space_id));
    if !include_inactive {
        query = query.filter(space_members::Column::LeftAt.is_null());
    }
    if !include_banned {
        query = query.filter(space_members::Column::BannedAt.is_null());
    }
    Ok(query.all(state.access.database.read()).await?)
}

pub async fn list_user_spaces(
    state: &SpaceService,
    user_id: i64,
) -> Result<Vec<spaces::Model>, SpaceError> {
    let space_ids: Vec<Uuid> = Members::find()
        .filter(space_members::Column::UserId.eq(user_id))
        .filter(space_members::Column::LeftAt.is_null())
        .filter(space_members::Column::BannedAt.is_null())
        .select_only()
        .column(space_members::Column::OrganizationId)
        .into_tuple()
        .all(state.access.database.read())
        .await?;
    spaces_by_ids(state, space_ids).await
}

/// Public directory (ACTIVE, joinable). Presentation fields come from the extras row.
pub async fn list_public_spaces(
    state: &SpaceService,
    query: &str,
) -> Result<Vec<spaces::Model>, SpaceError> {
    use sea_orm::Condition;

    let mut select = Spaces::find()
        .filter(spaces::Column::Status.eq(SpaceStatus::Active))
        .filter(spaces::Column::DeletedAt.is_null())
        .filter(
            spaces::Column::JoinPolicy.is_in([SpaceJoinPolicy::Open, SpaceJoinPolicy::Approval]),
        );
    let needle = query.trim();
    if !needle.is_empty() {
        select = select.filter(
            Condition::any()
                .add(spaces::Column::Name.contains(needle))
                .add(spaces::Column::Slug.contains(needle)),
        );
    }
    Ok(select
        .limit(1_000)
        .all(state.access.database.read())
        .await?)
}

async fn spaces_by_ids(
    state: &SpaceService,
    ids: Vec<Uuid>,
) -> Result<Vec<spaces::Model>, SpaceError> {
    if ids.is_empty() {
        return Ok(Vec::new());
    }
    Ok(Spaces::find()
        .filter(spaces::Column::Id.is_in(ids))
        .filter(spaces::Column::DeletedAt.is_null())
        .all(state.access.database.read())
        .await?)
}

pub async fn account_created_at(
    accounts: &auth::Accounts,
    user_id: i64,
) -> Result<Option<OffsetDateTime>, SpaceError> {
    let user = accounts.get_authority_user(user_id).await?;
    let Some(created) = user.created_at else {
        return Ok(None);
    };
    Ok(OffsetDateTime::from_unix_timestamp(created.seconds)
        .ok()
        .map(|row| {
            row.replace_nanosecond(created.nanos.clamp(0, 999_999_999) as u32)
                .unwrap_or(row)
        }))
}

pub async fn mfa_enabled(accounts: &auth::Accounts, user_id: i64) -> Result<bool, SpaceError> {
    let status = accounts.get_user_status(user_id).await?;
    Ok(status.active_mfa_factors > 0)
}

pub async fn has_verified_email(
    accounts: &auth::Accounts,
    user_id: i64,
) -> Result<bool, SpaceError> {
    let status = accounts.get_user_status(user_id).await?;
    Ok(status.email_verified_at.is_some())
}

pub async fn upsert_extras(
    db: &db::DbConn,
    space_id: Uuid,
    avatar_url: Option<Option<String>>,
    welcome_object: Option<Option<Uuid>>,
    banner_url: Option<Option<String>>,
    banner_width: Option<Option<i32>>,
    banner_height: Option<Option<i32>>,
    invite_banner_url: Option<Option<String>>,
    invite_banner_width: Option<Option<i32>>,
    invite_banner_height: Option<Option<i32>>,
    embed_splash_url: Option<Option<String>>,
    embed_splash_width: Option<Option<i32>>,
    embed_splash_height: Option<Option<i32>>,
    invite_sort_url_hash: Option<Option<String>>,
) -> Result<space_extras::Model, SpaceError> {
    let now = OffsetDateTime::now_utc();
    let existing = extras(db.read(), space_id).await?;
    let row = match existing {
        Some(row) => {
            let mut active: space_extras::ActiveModel = row.into();
            if let Some(value) = avatar_url {
                active.avatar_url = Set(value);
            }
            if let Some(value) = welcome_object {
                active.welcome_object = Set(value);
            }
            if let Some(value) = banner_url {
                active.banner_url = Set(value);
            }
            if let Some(value) = banner_width {
                active.banner_width = Set(value);
            }
            if let Some(value) = banner_height {
                active.banner_height = Set(value);
            }
            if let Some(value) = invite_banner_url {
                active.invite_banner_url = Set(value);
            }
            if let Some(value) = invite_banner_width {
                active.invite_banner_width = Set(value);
            }
            if let Some(value) = invite_banner_height {
                active.invite_banner_height = Set(value);
            }
            if let Some(value) = embed_splash_url {
                active.embed_splash_url = Set(value);
            }
            if let Some(value) = embed_splash_width {
                active.embed_splash_width = Set(value);
            }
            if let Some(value) = embed_splash_height {
                active.embed_splash_height = Set(value);
            }
            if let Some(value) = invite_sort_url_hash {
                active.invite_sort_url_hash = Set(value);
            }
            active.updated_at = Set(now);
            active.update(db.write()).await?
        }
        None => {
            space_extras::ActiveModel {
                space_id: Set(space_id),
                avatar_url: Set(avatar_url.unwrap_or(None)),
                invite_sort_url_hash: Set(invite_sort_url_hash.unwrap_or(None)),
                banner_url: Set(banner_url.unwrap_or(None)),
                banner_width: Set(banner_width.unwrap_or(None)),
                banner_height: Set(banner_height.unwrap_or(None)),
                invite_banner_url: Set(invite_banner_url.unwrap_or(None)),
                invite_banner_width: Set(invite_banner_width.unwrap_or(None)),
                invite_banner_height: Set(invite_banner_height.unwrap_or(None)),
                embed_splash_url: Set(embed_splash_url.unwrap_or(None)),
                embed_splash_width: Set(embed_splash_width.unwrap_or(None)),
                embed_splash_height: Set(embed_splash_height.unwrap_or(None)),
                welcome_object: Set(welcome_object.unwrap_or(None)),
                created_at: Set(now),
                updated_at: Set(now),
            }
            .insert(db.write())
            .await?
        }
    };
    Ok(row)
}