use serde::{Deserialize, Serialize};
use sqlx::mysql::{MySqlPool, MySqlPoolOptions, MySqlQueryResult};
use std::env;

#[derive(Serialize)]
pub struct Song {
    name: Option<String>,
    id: Option<i32>,
    lyrics: Option<String>,
    album_name: Option<String>,
    album_id: Option<i32>,
    artist_name: Option<String>,
    artist_id: Option<i32>,
}

#[derive(Serialize)]
pub struct Album {
    name: Option<String>,
    id: Option<i32>,
    cover: Option<String>,
    artist_name: Option<String>,
    artist_id: Option<i32>,
}

#[derive(Serialize)]
pub struct Artist {
    name: Option<String>,
    id: Option<i32>,
}

#[derive(Serialize, Deserialize)]
pub struct SongPost {
    name: Option<String>,
    lyrics: Option<String>,
    album_id: Option<String>,
}

#[derive(Serialize, Deserialize)]
pub struct AlbumPost {
    name: Option<String>,
    cover: Option<String>,
    artist_id: Option<String>,
}

#[derive(Serialize, Deserialize)]
pub struct ArtistPost {
    name: Option<String>,
}

#[derive(Serialize, Deserialize)]
pub struct SongPut {
    id: Option<String>,
    name: Option<String>,
    lyrics: Option<String>,
    album_id: Option<String>,
}

#[derive(Serialize, Deserialize)]
pub struct AlbumPut {
    id: Option<String>,
    name: Option<String>,
    cover: Option<String>,
    artist_id: Option<String>,
}

#[derive(Serialize, Deserialize)]
pub struct ArtistPut {
    id: Option<String>,
    name: Option<String>,
}

#[derive(Serialize, Deserialize)]
pub struct Delete {
    pub id: Option<String>,
}

pub struct DatabaseWrapper {
    db_pool: MySqlPool,
}

impl DatabaseWrapper {
    pub async fn new() -> Result<DatabaseWrapper, sqlx::Error> {
        let pool: MySqlPool = MySqlPoolOptions::new()
            .max_connections(10)
            .connect(
                env::var("DATABASE_URL")
                    .expect("environment variables are *probably not setted up!!")
                    .as_str(),
            )
            .await
            .unwrap(); /* This will break in case of error. It's intended. */

        Ok(DatabaseWrapper { db_pool: pool })
    }

    pub async fn select_songs(&self) -> Result<Vec<Song>, sqlx::Error> {
        sqlx::query_as!(
            Song,
            "SELECT song.name, song.lyrics, song.id,
            album.name as album_name, album.id as album_id,
            artist.name as artist_name, artist.id as artist_id
            FROM song
            INNER JOIN album ON song.album_id = album.id
            INNER JOIN artist ON album.artist_id = artist.id
            ",
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_song_by_id(&self, id: &str) -> Result<Option<Song>, sqlx::Error> {
        sqlx::query_as!(
            Song,
            "SELECT song.name, song.lyrics, song.id,
            album.name as album_name, album.id as album_id,
            artist.name as artist_name, artist.id as artist_id
            FROM song
            INNER JOIN album ON song.album_id = album.id
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE song.id = ?
            ",
            id,
        )
        .fetch_optional(&self.db_pool)
        .await
    }

    pub async fn select_songs_by_album(&self, album_id: &str) -> Result<Vec<Song>, sqlx::Error> {
        sqlx::query_as!(
            Song,
            "SELECT song.name, song.lyrics, song.id,
            album.name as album_name, album.id as album_id,
            artist.name as artist_name, artist.id as artist_id
            FROM song
            INNER JOIN album ON song.album_id = album.id
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE album.id = ?
            ",
            album_id,
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_songs_by_artist(&self, artist_id: &str) -> Result<Vec<Song>, sqlx::Error> {
        sqlx::query_as!(
            Song,
            "SELECT song.name, song.lyrics, song.id,
            album.name as album_name, album.id as album_id,
            artist.name as artist_name, artist.id as artist_id
            FROM song
            INNER JOIN album ON song.album_id = album.id
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE artist.id = ?
            ",
            artist_id,
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_songs_by_name(&self, name_raw: &str) -> Result<Vec<Song>, sqlx::Error> {
        let name: String = format!("{}{}{}", "%", name_raw, "%");
        sqlx::query_as!(
            Song,
            "SELECT song.name, song.lyrics, song.id,
            album.name as album_name, album.id as album_id,
            artist.name as artist_name, artist.id as artist_id
            FROM song
            INNER JOIN album ON song.album_id = album.id
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE LOWER(song.name) LIKE LOWER(?)",
            name,
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_albums(&self) -> Result<Vec<Album>, sqlx::Error> {
        sqlx::query_as!(
            Album,
            "SELECT album.name, album.id, album.cover,
            artist.name as artist_name, artist.id as artist_id
            FROM album
            INNER JOIN artist ON album.artist_id = artist.id
            ",
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_album_by_id(&self, id: &str) -> Result<Option<Album>, sqlx::Error> {
        sqlx::query_as!(
            Album,
            "SELECT album.name, album.id, album.cover,
            artist.name as artist_name, artist.id as artist_id
            FROM album
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE album.id=?",
            id,
        )
        .fetch_optional(&self.db_pool)
        .await
    }

    pub async fn select_albums_by_name(&self, name_raw: &str) -> Result<Vec<Album>, sqlx::Error> {
        let name: String = format!("{}{}{}", "%", name_raw, "%");
        sqlx::query_as!(
            Album,
            "SELECT album.name, album.id, album.cover,
            artist.name as artist_name, artist.id as artist_id
            FROM album
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE LOWER(album.name) LIKE LOWER(?)
            ",
            name,
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_albums_by_artist(
        &self,
        artist_id: &str,
    ) -> Result<Vec<Album>, sqlx::Error> {
        sqlx::query_as!(
            Album,
            "SELECT album.name, album.id, album.cover,
            artist.name as artist_name, artist.id as artist_id
            FROM album
            INNER JOIN artist ON album.artist_id = artist.id
            WHERE artist.id=?",
            artist_id,
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_artists(&self) -> Result<Vec<Artist>, sqlx::Error> {
        sqlx::query_as!(
            Artist,
            "SELECT name, id
            FROM artist",
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn select_artist_by_id(&self, id: &str) -> Result<Option<Artist>, sqlx::Error> {
        sqlx::query_as!(
            Artist,
            "SELECT name, id
            FROM artist
            WHERE id = ?",
            id,
        )
        .fetch_optional(&self.db_pool)
        .await
    }

    pub async fn select_artists_by_name(&self, name_raw: &str) -> Result<Vec<Artist>, sqlx::Error> {
        let name: String = format!("{}{}{}", "%", name_raw, "%");
        sqlx::query_as!(
            Artist,
            "SELECT name, id
            FROM artist
            WHERE name LIKE ?",
            name,
        )
        .fetch_all(&self.db_pool)
        .await
    }

    pub async fn search_results(
        &self,
    ) -> (
        Result<Vec<Artist>, sqlx::Error>,
        Result<Vec<Album>, sqlx::Error>,
        Result<Vec<Song>, sqlx::Error>,
    ) {
        (
            self.select_artists().await,
            self.select_albums().await,
            self.select_songs().await,
        )
    }

    pub async fn search_results_by_id(
        &self,
        id: &str,
    ) -> (
        Result<Option<Artist>, sqlx::Error>,
        Result<Option<Album>, sqlx::Error>,
        Result<Option<Song>, sqlx::Error>,
    ) {
        (
            self.select_artist_by_id(id).await,
            self.select_album_by_id(id).await,
            self.select_song_by_id(id).await,
        )
    }

    pub async fn search_results_by_name(
        &self,
        name: &str,
    ) -> (
        Result<Vec<Artist>, sqlx::Error>,
        Result<Vec<Album>, sqlx::Error>,
        Result<Vec<Song>, sqlx::Error>,
    ) {
        return (
            self.select_artists_by_name(name).await,
            self.select_albums_by_name(name).await,
            self.select_songs_by_name(name).await,
        );
    }

    pub async fn create_song(&self, data: SongPost) -> Result<MySqlQueryResult, sqlx::Error> {
        if data.name.is_none() || data.album_id.is_none() {
            return Err(sqlx::Error::RowNotFound);
        }

        if match self
            .select_album_by_id(data.album_id.as_ref().unwrap())
            .await
        {
            Ok(res) => res.is_none(),
            Err(_) => true,
        } {
            return Err(sqlx::Error::RowNotFound);
        }

        sqlx::query!(
            "INSERT INTO song (name, lyrics, album_id)
            VALUE (?, ?, ?)",
            data.name,
            data.lyrics.unwrap_or(String::default()),
            data.album_id,
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn create_album(&self, data: AlbumPost) -> Result<MySqlQueryResult, sqlx::Error> {
        if data.name.is_none() || data.artist_id.is_none() {
            return Err(sqlx::Error::RowNotFound);
        }

        if match self
            .select_artist_by_id(data.artist_id.as_ref().unwrap())
            .await
        {
            Ok(res) => res.is_none(),
            Err(_) => true,
        } {
            return Err(sqlx::Error::RowNotFound);
        }

        sqlx::query!(
            "INSERT INTO album (name, cover, artist_id)
            VALUE (?, ?, ?)",
            data.name,
            data.cover.unwrap_or(String::default()),
            data.artist_id,
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn create_artist(&self, data: ArtistPost) -> Result<MySqlQueryResult, sqlx::Error> {
        if data.name.is_none() {
            return Err(sqlx::Error::RowNotFound);
        }
        sqlx::query!(
            "INSERT INTO artist (name)
            VALUE (?)",
            data.name
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn edit_song(&self, data: SongPut) -> Result<MySqlQueryResult, sqlx::Error> {
        if data.id.is_none() { return Err(sqlx::Error::RowNotFound); }
        let og_song: Song = match self.select_song_by_id(data.id.as_ref().unwrap()).await {
            Ok(res) => match res.is_some() {
                true => res.unwrap(),
                false => return Err(sqlx::Error::RowNotFound),
            }
            Err(_) => return Err(sqlx::Error::RowNotFound),
        };
        sqlx::query!(
            "UPDATE song SET name=?, lyrics=? WHERE id=?",
            data.name.unwrap_or(og_song.name.unwrap_or(String::default())),
            data.lyrics.unwrap_or(og_song.lyrics.unwrap_or(String::default())),
            data.id,
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn edit_album(&self, data: AlbumPut) -> Result<MySqlQueryResult, sqlx::Error> {
        if data.id.is_none() { return Err(sqlx::Error::RowNotFound); }
        let og_album: Album = match self.select_album_by_id(data.id.as_ref().unwrap()).await {
            Ok(res) => match res.is_some() {
                true => res.unwrap(),
                false => return Err(sqlx::Error::RowNotFound),
            }
            Err(_) => return Err(sqlx::Error::RowNotFound),
        };
        sqlx::query!(
            "UPDATE album SET name=?, cover=? WHERE id=?",
            data.name.unwrap_or(og_album.name.unwrap_or(String::default())),
            data.cover.unwrap_or(og_album.cover.unwrap_or(String::default())),
            data.id,
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn edit_artist(&self, data: ArtistPut) -> Result<MySqlQueryResult, sqlx::Error> {
        if data.id.is_none() { return Err(sqlx::Error::RowNotFound); }
        let og_artist: Artist = match self.select_artist_by_id(data.id.as_ref().unwrap()).await {
            Ok(res) => match res.is_some() {
                true => res.unwrap(),
                false => return Err(sqlx::Error::RowNotFound),
            }
            Err(_) => return Err(sqlx::Error::RowNotFound),
        };
        sqlx::query!(
            "UPDATE artist SET name=? WHERE id=?",
            data.name.unwrap_or(og_artist.name.unwrap_or(String::default())),
            data.id,
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn delete_song(&self, id: i32) -> Result<MySqlQueryResult, sqlx::Error> {
        sqlx::query!(
            "DELETE FROM song
            WHERE id = ?",
            id
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn delete_album(&self, id: i32) -> Result<MySqlQueryResult, sqlx::Error> {
        let _ = sqlx::query!(
            "DELETE FROM song
            WHERE album_id = ?",
            id
        )
        .execute(&self.db_pool)
        .await;

        sqlx::query!(
            "DELETE FROM album
            WHERE id = ?",
            id
        )
        .execute(&self.db_pool)
        .await
    }

    pub async fn delete_artist(&self, id: i32) -> Result<MySqlQueryResult, sqlx::Error> {
        let _ = sqlx::query!(
            "DELETE song FROM song
            INNER JOIN album ON song.album_id = album.id
            WHERE album.artist_id = ?",
            id
        )
        .execute(&self.db_pool)
        .await;

        let _ = sqlx::query!(
            "DELETE FROM album
            WHERE artist_id = ?",
            id
        )
        .execute(&self.db_pool)
        .await;

        sqlx::query!(
            "DELETE FROM artist
            WHERE id = ?",
            id
        )
        .execute(&self.db_pool)
        .await
    }
}