use crate::{JoinedRing, Member};
use serde::Serialize;
use sqlx::{Error, FromRow, Sqlite, SqliteConnection, query, query_as, sqlite::SqliteQueryResult};

///
/// Representation of the join table between a [Site]
/// and a [JoinedRing]
///
#[derive(FromRow, Serialize, Debug)]
pub struct Membership {
    pub id: i64,
    pub ring_id: i64,
    pub site_id: i64,
}

impl Membership {
    ///
    /// Utility function for creating a membership in the
    /// `memberships` join table
    ///
    pub fn new(ring_id: i64, site_id: i64) -> Self {
        Membership {
            id: 0,
            ring_id,
            site_id,
        }
    }

    pub async fn save(self, conn: &mut SqliteConnection) -> Result<SqliteQueryResult, Error> {
        // If id = 0, add new
        if self.id == 0 {
            return query(
                "
                INSERT INTO memberships (ring_id, site_id) VALUES (?, ?)
                ",
            )
            .bind(self.ring_id)
            .bind(self.site_id)
            .execute(&mut *conn)
            .await;
        }

        // Otherwise, update
        query(
            "
            UPDATE memberships SET
                ring_id = ?,
                site_id = ?
            WHERE id = ?
            ",
        )
        .bind(self.ring_id)
        .bind(self.site_id)
        .bind(self.id)
        .execute(&mut *conn)
        .await
    }

    /// Retrieve [Site] of this Membership
    pub async fn member(self, conn: &mut SqliteConnection) -> Result<Member, Error> {
        query_as::<Sqlite, Member>(
            "
            SELECT * FROM members
            WHERE id = ?
            ",
        )
        .bind(self.site_id)
        .fetch_one(&mut *conn)
        .await
    }

    /// Retrieve [JoinedRing] of this Membership
    pub async fn ring(self, conn: &mut SqliteConnection) -> Result<JoinedRing, Error> {
        query_as::<Sqlite, JoinedRing>(
            "
            SELECT * FROM joined_rings
            WHERE id = ?
            ",
        )
        .bind(self.ring_id)
        .fetch_one(&mut *conn)
        .await
    }
}
