use oiseau::{PostgresRow, cache::Cache, execute, get, params, query_row, query_rows};
use crate::{
    DataManager, auto_method,
    model::{
        groups::{GroupRole, GroupMembership},
        id::Id,
        Error, Result,
    },
};

impl DataManager {
    /// Get a [`GroupMembership`] from an SQL row.
    pub(crate) fn get_group_membership_from_row(x: &PostgresRow) -> GroupMembership {
        GroupMembership {
            id: Id::deserialize(&get!(x->0(String))),
            created: get!(x->1(i64)) as u128,
            owner: Id::deserialize(&get!(x->2(String))),
            group: Id::deserialize(&get!(x->3(String))),
            role: serde_json::from_str(&get!(x->4(String))).unwrap(),
        }
    }

    auto_method!(get_group_membership_by_id()@get_group_membership_from_row -> "SELECT * FROM group_memberships WHERE id = $1" --name="membership" --returns=GroupMembership --cache-key-tmpl="juic.group_membership:{}");

    /// Get a membership by `owner` and `group` (in that order).
    pub async fn get_group_membership_by_owner_group(
        &self,
        owner: &Id,
        group: &Id,
    ) -> Result<GroupMembership> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_row!(
            &conn,
            "SELECT * FROM group_memberships WHERE owner = $1 AND group_id = $2",
            &[&owner.printable(), &group.printable()],
            |x| { Ok(Self::get_group_membership_from_row(x)) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("membership".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get group memberships by the given user.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    /// * `batch` - the limit of group memberships in each page
    /// * `page` - the page number
    pub async fn get_group_memberships_by_owner(
        &self,
        id: &Id,
        batch: usize,
        page: usize,
    ) -> Result<Vec<GroupMembership>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM group_memberships WHERE owner = $1 AND role != '\"Banned\"' ORDER BY created DESC LIMIT $2 OFFSET $3",
            &[&id.printable(), &(batch as i64), &((page * batch) as i64)],
            |x| { Self::get_group_membership_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("group membership".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get group memberships by the given user.
    ///
    /// # Arguments
    /// * `id` - the ID of the user
    pub async fn get_group_memberships_group_by_owner_all(&self, id: &Id) -> Result<Vec<Id>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT group_id FROM group_memberships WHERE owner = $1 AND role != '\"Banned\"'",
            &[&id.printable()],
            |x| { Id::deserialize(&get!(x->0(String))) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("group membership".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Get all memberships of a group by role.
    pub async fn get_group_memberships_by_group_role(
        &self,
        group: &Id,
        role: &GroupRole,
        batch: usize,
        page: usize,
    ) -> Result<Vec<GroupMembership>> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = query_rows!(
            &conn,
            "SELECT * FROM group_memberships WHERE role = $1 AND group_id = $2 ORDER BY created DESC LIMIT $3 OFFSET $4",
            &[
                &serde_json::to_string(&role).unwrap(),
                &group.printable(),
                &(batch as i64),
                &((page * batch) as i64)
            ],
            |x| { Self::get_group_membership_from_row(x) }
        );

        if res.is_err() {
            return Err(Error::GeneralNotFound("membership".to_string()));
        }

        Ok(res.unwrap())
    }

    /// Create a new membership in the database.
    ///
    /// # Arguments
    /// * `data` - a mock [`GroupMembership`] object to insert
    pub async fn create_group_membership(&self, data: GroupMembership) -> Result<()> {
        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "INSERT INTO group_memberships VALUES ($1, $2, $3, $4, $5)",
            params![
                &data.id.printable(),
                &(data.created as i64),
                &data.owner.printable(),
                &data.group.printable(),
                &serde_json::to_string(&data.role).unwrap(),
            ]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        // ...
        if data.role == GroupRole::Owner || data.role == GroupRole::Member {
            self.incr_group_members(&data.group).await?;
        }

        // return
        Ok(())
    }

    pub async fn delete_group_membership(&self, id: &Id) -> Result<()> {
        let y = self.get_group_membership_by_id(id).await?;

        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "DELETE FROM group_memberships WHERE id = $1",
            &[&id.printable()]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.0
            .1
            .remove(format!("juic.group_membership:{}", id))
            .await;

        // ...
        if y.role == GroupRole::Owner || y.role == GroupRole::Member {
            self.decr_group_members(&y.group).await?;
        }

        // return
        Ok(())
    }

    pub async fn update_group_membership_role(&self, id: &Id, role: GroupRole) -> Result<()> {
        let y = self.get_group_membership_by_id(id).await?;

        let conn = match self.0.connect().await {
            Ok(c) => c,
            Err(e) => return Err(Error::DatabaseConnection(e.to_string())),
        };

        let res = execute!(
            &conn,
            "UPDATE group_memberships SET role = $1 WHERE id = $2",
            &[&serde_json::to_string(&role).unwrap(), &id.printable()]
        );

        if let Err(e) = res {
            return Err(Error::DatabaseError(e.to_string()));
        }

        self.0
            .1
            .remove(format!("juic.group_membership:{}", id))
            .await;

        // ...
        if (y.role == GroupRole::Owner || y.role == GroupRole::Member)
            && (role == GroupRole::Banned || role == GroupRole::Pending)
        {
            // owner/member -> banned/pending
            self.decr_group_members(&y.group).await?;
        } else if (y.role == GroupRole::Banned || y.role == GroupRole::Pending)
            && (role == GroupRole::Owner || role == GroupRole::Member)
        {
            // banned/pending -> owner/member
            self.incr_group_members(&y.group).await?;
        }

        // return
        Ok(())
    }
}
