use crate::SqliteConnection;
use chrono::prelude::*;
use rusqlite::params;

#[derive(Debug)]
pub struct TopAlbumsResp {
    pub mbid: String,
    pub name: String,
    pub artist_credit: Option<String>,
    pub release_group: i64,
    pub count: i64,
}

pub fn get_top_albums(
    conn: &SqliteConnection,
    did: &str,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
    limit: i64,
) -> rusqlite::Result<Vec<TopAlbumsResp>> {
    let mut stmt = conn.prepare_cached(
        r"WITH albums AS (
            SELECT release_mbid, count(*) as count FROM scrobbles
            WHERE did=$1 AND created_at BETWEEN $2 AND $3
            GROUP BY release_mbid
            ORDER BY count DESC LIMIT $4)
        SELECT release.gid, release.name, artist_credit.name, release_group, count FROM release
        INNER JOIN albums ON release_mbid = release.gid
        INNER JOIN artist_credit ON artist_credit.id = release.artist_credit
        ORDER BY count DESC",
    )?;

    let rows = stmt
        .query_map(params![did, start, end, limit], |row| {
            Ok(TopAlbumsResp {
                mbid: row.get(0)?,
                name: row.get(1)?,
                artist_credit: row.get(2)?,
                release_group: row.get(3)?,
                count: row.get(4)?,
            })
        })?
        .collect::<Result<_, _>>()?;

    Ok(rows)
}

#[derive(Debug)]
pub struct TopArtistResp {
    pub mbid: String,
    pub name: String,
    pub count: i64,
}
pub fn get_top_artists(
    conn: &SqliteConnection,
    did: &str,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
    limit: i64,
) -> rusqlite::Result<Vec<TopArtistResp>> {
    let mut stmt = conn.prepare_cached(
        r"WITH artists AS (
            SELECT artist_credit_name.artist as id, count(*) as count FROM scrobbles
            INNER JOIN track ON track.gid = track_mbid
            INNER JOIN artist_credit_name ON artist_credit_name.artist_credit = track.artist_credit
            WHERE did=$1 AND created_at BETWEEN $2 AND $3
            GROUP BY artist_credit_name.artist
            ORDER BY count DESC limit $4)
        SELECT gid, name, count FROM artist
        INNER JOIN artists ON artists.id = artist.id
        ORDER BY count DESC"
    )?;

    let rows = stmt
        .query_map(params![did, start, end, limit], |row| {
            Ok(TopArtistResp {
                mbid: row.get(0)?,
                name: row.get(1)?,
                count: row.get(2)?,
            })
        })?
        .collect::<Result<_, _>>()?;

    Ok(rows)
}

#[derive(Debug)]
pub struct TopTracksResp {
    pub mbid: String,
    pub name: String,
    pub artist_credit: Option<String>,
    pub count: i64,
}

pub fn get_top_tracks(
    conn: &SqliteConnection,
    did: &str,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
    limit: i64,
) -> rusqlite::Result<Vec<TopTracksResp>> {
    let mut stmt = conn.prepare_cached(
        r"WITH tracks AS (
            SELECT track_mbid, count(*) as count FROM scrobbles
            WHERE did=$1 AND created_at BETWEEN $2 AND $3
            GROUP BY track_mbid
            ORDER BY count DESC LIMIT $4)
        SELECT track.gid, track.name, artist_credit.name, count FROM track
        INNER JOIN tracks on track_mbid = track.gid
        INNER JOIN artist_credit ON artist_credit.id = track.artist_credit
        ORDER BY count DESC",
    )?;

    let rows = stmt
        .query_map(params![did, start, end, limit], |row| {
            Ok(TopTracksResp {
                mbid: row.get(0)?,
                name: row.get(1)?,
                artist_credit: row.get(2)?,
                count: row.get(3)?,
            })
        })?
        .collect::<Result<_, _>>()?;

    Ok(rows)
}