use std::{
    convert::{
        AsRef,
        From,
    },
    env,
    str::FromStr,
};

use anyhow::{
    Error,
    Result,
    anyhow,
};
use chrono::{
    DateTime,
    NaiveDate,
    TimeZone,
    Utc,
};
use deadpool_postgres::{
    GenericClient,
    Pool as RawPgConnMgr,
    PoolConfig,
};
use diesel::{
    BoolExpressionMethods,
    ExpressionMethods,
    NotFound,
    PgTextExpressionMethods,
    QueryDsl,
    prelude::*,
};
use diesel_async::{
    AsyncConnection,
    AsyncPgConnection,
    RunQueryDsl,
    pooled_connection::{
        AsyncDieselConnectionManager,
        deadpool::Pool,
    },
    scoped_futures::ScopedFutureExt,
};
use grate::tracing;
use rand::prelude::IndexedRandom;
use serenity::FutureExt;
use tokio_postgres::types::FromSql;

use self::schema::*;

pub mod manual_migrate;

mod models;
mod schema;

pub use self::models::*;

static MIGRATIONS: diesel_async_migrations::EmbeddedMigrations =
    diesel_async_migrations::embed_migrations!();

lazy_static::lazy_static! {
    static ref MIGRATE: tokio::sync::OnceCell<()> = tokio::sync::OnceCell::new();

    static ref DB_URL: String =
        env::var("DATABASE_URL").expect("no database url in environment");

    static ref POOL: Pool<AsyncPgConnection> = {
        let cfg = AsyncDieselConnectionManager::new(DB_URL.clone());

        Pool::builder(cfg).build().unwrap()
    };

    static ref RAW_CONN_MGR: RawPgConnMgr = {
        let pg_conf = tokio_postgres::Config::from_str(&DB_URL).expect("parsing db url");
        let mgr_conf = deadpool_postgres::ManagerConfig {
            recycling_method: deadpool_postgres::RecyclingMethod::Fast,
        };

        let mgr = deadpool_postgres::Manager::from_config(pg_conf, tokio_postgres::NoTls, mgr_conf);

        RawPgConnMgr::builder(mgr).config(PoolConfig::new(8)).build().expect("failed to build pool")
    };
}

#[inline]
pub async fn connection()
-> Result<diesel_async::pooled_connection::deadpool::Object<AsyncPgConnection>> {
    POOL.get()
        .then(|mut conn| async move {
            if let Ok(ref mut conn) = conn {
                do_migrate(conn).await;
            }

            conn
        })
        .await
        .map_err(Error::from)
}

async fn do_migrate(conn: &mut AsyncPgConnection) {
    MIGRATE
        .get_or_init(|| async move {
            tracing::info!("running migrations");
            MIGRATIONS.run_pending_migrations(conn).await.expect("failed running migrations");
            tracing::info!("migrations complete");
        })
        .await;
}

#[inline]
async fn raw_connection() -> Result<deadpool_postgres::Object> {
    // HACK
    if !MIGRATE.initialized() {
        let _ = connection().await?;
    }

    RAW_CONN_MGR.get().await.map_err(Error::from)
}

pub async fn find_meme<T: AsRef<str>>(
    conn: &mut AsyncPgConnection,
    search: T,
    guild_id: u64,
) -> Result<Meme> {
    let search = search.as_ref();
    let guild_match = memes::guild.eq(guild_id as i64);

    let mut meme = memes::table
        .filter(memes::title.eq(search).and(guild_match))
        .limit(1)
        .first::<Meme>(conn)
        .await;

    if let Err(NotFound) = meme {
        let format_search = format!("%{}%", search);

        meme = memes::table
            .filter(
                memes::title
                    .ilike(&format_search)
                    .or(memes::content.ilike(&format_search))
                    .and(guild_match),
            )
            .limit(1)
            .first::<Meme>(conn)
            .await;
    }

    meme.map_err(Error::from)
}

pub async fn query_meme<T: AsRef<str>>(
    search: T,
    user_id: Option<u64>,
    age_desc: bool,
    guild: u64,
) -> Result<Vec<(Meme, Metadata)>> {
    let raw_conn = raw_connection().await?;

    let guild = guild as i64;
    let search = format!("%{}%", search.as_ref());

    let rows = raw_conn
        .query(
            &format!(
                r#"
    SELECT memes.id, title, content, image_id, audio_id, metadata_id, created, created_by
    FROM memes
    INNER JOIN metadata ON memes.metadata_id = metadata.id
    WHERE (memes.title ILIKE $1 OR memes.content ILIKE $1)
              AND (metadata.created_by = $2 OR $3) AND memes.guild = $4
    ORDER BY metadata.created {}
    LIMIT 100
    "#,
                if age_desc {
                    "DESC"
                } else {
                    "ASC"
                },
            ),
            &[&search, &(user_id.unwrap_or(0) as i64), &user_id.is_none(), &guild],
        )
        .await?;

    let result = rows
        .iter()
        .map(|row| {
            let meme = Meme {
                id: row.get(0),
                guild,
                title: row.get(1),
                content: row.get(2),
                image_id: row.get(3),
                audio_id: row.get(4),
                metadata_id: row.get(5),
            };

            let metadata = Metadata {
                id:         row.get(5),
                created:    row.get(6),
                created_by: row.get(7),
            };

            (meme, metadata)
        })
        .collect();

    Ok(result)
}

pub async fn del_memes_by_userid(
    _conn: &mut AsyncPgConnection,
    user_id: u64,
    deleted_by: u64,
) -> Result<Vec<u64>> {
    let user_id = user_id as i64;
    let deleted_by = deleted_by as i64;

    let raw_conn = raw_connection().await?;

    let result = raw_conn
        .query(
            r#"
        WITH
            meme_deletes AS (
                DELETE FROM memes
                    WHERE metadata_id IN (SELECT id FROM metadata WHERE created_by = $1)
                    RETURNING id, guild, metadata_id
            )
            , image_deletes AS (
                DELETE FROM images
                    WHERE (SELECT COUNT(*) FROM memes WHERE memes.image_id = images.id) = 0
                    RETURNING id, metadata_id
            )
            , audio_deletes AS (
                DELETE FROM audio
                    WHERE (SELECT COUNT(*) FROM memes WHERE memes.audio_id = audio.id) = 0
                    RETURNING id, metadata_id
            )
            , tombstone_inserts AS (
                INSERT INTO tombstones (meme_id, guild, metadata_id, deleted_at, deleted_by)
                    SELECT id, guild, metadata_id, NOW(), $2 FROM meme_deletes
                    RETURNING meme_id, metadata_id
            )

        SELECT meme_id
        FROM tombstone_inserts
        ;
    "#,
            &[&user_id, &deleted_by],
        )
        .await?;

    Ok(result.into_iter().map(|x| x.get::<_, i32>(0) as u64).collect())
}

pub async fn delete_meme<T: AsRef<str>>(
    conn: &mut AsyncPgConnection,
    search: T,
    deleted_by: u64,
    guild: u64,
) -> Result<()> {
    let search = search.as_ref();

    conn.transaction::<(), Error, _>(|tx| {
        async move {
            let deleted = memes::table
                .filter(memes::title.eq(search).and(memes::guild.eq(guild as i64)))
                .first::<Meme>(tx)
                .await?;

            diesel::delete(memes::table).filter(memes::id.eq(deleted.id)).execute(tx).await?;

            if let Some(image_id) = deleted.image_id {
                let count =
                    memes::table.filter(memes::image_id.eq(image_id)).count().execute(tx).await?;

                if count == 0 {
                    diesel::delete(images::table)
                        .filter(images::id.eq(image_id))
                        .execute(tx)
                        .await?;
                }
            }

            if let Some(audio_id) = deleted.audio_id {
                let count = memes::table
                    .select(::diesel::dsl::count_star())
                    .filter(memes::audio_id.eq(audio_id))
                    .execute(tx)
                    .await?;

                if count == 0 {
                    diesel::delete(audio::table).filter(audio::id.eq(audio_id)).execute(tx).await?;
                }
            }

            let tombstone = NewTombstone {
                guild:       guild as i64,
                deleted_by:  deleted_by as i64,
                metadata_id: deleted.metadata_id,
                meme_id:     deleted.id,
            };

            let _ = diesel::insert_into(tombstones::table).values(&tombstone).execute(tx).await?;

            Ok(())
        }
        .scope_boxed()
    })
    .await
}

pub async fn rare_meme(
    conn: &mut AsyncPgConnection,
    audio: bool,
    guild: u64,
) -> Result<Option<Meme>> {
    use rand::prelude::*;

    let guild = guild as i64;
    let raw_conn = raw_connection().await?;

    let rows = raw_conn
        .query(
            r#"
    WITH
    meme_count AS (
        SELECT
               meme_id,
               COUNT(*) AS ct
        FROM invocation_records
        WHERE guild = $3
        GROUP BY meme_id
    ),
    aggregate AS (
        SELECT
               memes.id AS meme_id,
               COALESCE(meme_count.ct, 0) AS ct,
               EXTRACT(EPOCH FROM (now() - metadata.created)) AS time_diff
        FROM meme_count
            RIGHT JOIN memes ON memes.id = meme_count.meme_id
            INNER JOIN metadata ON metadata.id = memes.metadata_id
        WHERE (memes.audio_id IS NULL) = $1 OR $2
    ),
    least_used AS (
        SELECT
               meme_id,
               (TRUNC(time_diff / (ct + 1))::double precision) as play_prop
        FROM aggregate
    )
    SELECT
           meme_id,
           sum(play_prop) OVER (ORDER BY play_prop DESC) as play_prop
    FROM least_used
    LIMIT 100;
    "#,
            &[&!audio, &audio, &guild],
        )
        .await?;

    let elems = rows
        .iter()
        .map(|row| (row.get::<_, i32>(0), row.get::<_, f64>(1) as i64))
        .collect::<Vec<_>>();

    if elems.is_empty() {
        return Ok(None);
    }

    let target_prob = {
        let mut rng = rand::rng();
        rng.random_range(0..elems.last().unwrap().1)
    };

    let meme_id = elems
        .into_iter()
        .find(|(_, x)| target_prob < *x)
        .ok_or_else(|| anyhow!("couldn't locate meme satisfying target probability"))?
        .0;

    Meme::find(conn, meme_id).await.map(Some)
}

pub async fn rand_meme(
    conn: &mut AsyncPgConnection,
    audio: bool,
    guild: u64,
) -> Result<Option<Meme>> {
    let ids: Vec<i32> = if audio {
        memes::table
            .select(memes::id)
            .filter(
                memes::guild.eq(guild as i64).and(
                    memes::content
                        .is_not_null()
                        .or(memes::image_id.is_not_null())
                        .or(memes::audio_id.is_not_null()),
                ),
            )
            .load(conn)
            .await
            .map_err(Error::from)?
    } else {
        memes::table
            .select(memes::id)
            .filter(
                memes::guild
                    .eq(guild as i64)
                    .and(memes::content.is_not_null().or(memes::image_id.is_not_null())),
            )
            .load(conn)
            .await
            .map_err(Error::from)?
    };

    if ids.is_empty() {
        return Ok(None);
    }

    let id = ids.choose(&mut rand::rng()).ok_or_else(|| anyhow!("couldn't load meme"))?;

    memes::table.find(id).first::<Meme>(conn).await.map_err(Error::from).map(Some)
}

pub async fn rand_audio_meme(conn: &mut AsyncPgConnection, guild: u64) -> Result<Option<Meme>> {
    let ids: Vec<i32> = memes::table
        .select(memes::id)
        .filter(memes::audio_id.is_not_null().and(memes::guild.eq(guild as i64)))
        .load(conn)
        .await
        .map_err(Error::from)?;

    if ids.is_empty() {
        return Ok(None);
    }

    let id = ids.choose(&mut rand::rng()).ok_or_else(|| anyhow!("couldn't load audio meme"))?;

    memes::table.find(id).first::<Meme>(conn).await.map_err(Error::from).map(Some)
}

pub async fn rand_silent_meme(conn: &mut AsyncPgConnection, guild: u64) -> Result<Option<Meme>> {
    let ids: Vec<i32> = memes::table
        .select(memes::id)
        .filter(memes::audio_id.is_null().and(memes::guild.eq(guild as i64)))
        .load(conn)
        .await
        .map_err(Error::from)?;

    if ids.is_empty() {
        return Ok(None);
    }

    let id = ids.choose(&mut rand::rng()).ok_or_else(|| anyhow!("couldn't load audio meme"))?;

    memes::table.find(id).first::<Meme>(conn).await.map_err(Error::from).map(Some)
}

#[derive(Debug, Clone)]
pub struct Stats {
    pub memes_overall:           usize,
    pub audio_memes:             usize,
    pub image_memes:             usize,
    pub started_recording:       Option<DateTime<Utc>>,
    pub total_meme_invocations:  usize,
    pub audio_meme_invocations:  usize,
    pub random_meme_invocations: usize,

    pub most_active_day:       Option<NaiveDate>,
    pub most_active_day_count: usize,

    pub most_audio_active_day:   Option<NaiveDate>,
    pub most_audio_active_count: usize,

    pub most_random_meme_user:          Option<u64>,
    pub most_random_meme_user_count:    usize,
    pub most_directly_named_meme_user:  Option<u64>,
    pub most_directly_named_meme_count: usize,

    pub most_popular_named_meme:       Option<String>,
    pub most_popular_named_meme_count: usize,

    pub most_popular_random_meme:       Option<String>,
    pub most_popular_random_meme_count: usize,

    pub most_popular_meme_overall:       Option<String>,
    pub most_popular_meme_overall_count: usize,
}

pub async fn stats(conn: &mut AsyncPgConnection, guild: u64) -> Result<Stats> {
    use chrono::NaiveDateTime;
    use diesel::dsl::{
        count,
        count_star,
    };

    let guild = guild as i64;

    let guild_match = memes::guild.eq(guild);
    let invoke_guild_match = invocation_records::guild.eq(guild);

    #[inline]
    fn to_utc(ndt: NaiveDateTime) -> DateTime<Utc> {
        Utc.from_utc_datetime(&ndt)
    }

    let total_count: i64 = memes::table
        .select(count_star())
        .filter(guild_match)
        .first(conn)
        .await
        .map_err(Error::from)?;

    let image_count: i64 = memes::table
        .select(count(memes::image_id))
        .filter(memes::image_id.is_not_null().and(guild_match))
        .first(conn)
        .await
        .map_err(Error::from)?;

    let audio_count: i64 = memes::table
        .select(count(memes::audio_id))
        .filter(memes::audio_id.is_not_null().and(guild_match))
        .first(conn)
        .await
        .map_err(Error::from)?;

    let started_recording = invocation_records::table
        .select(invocation_records::time)
        .filter(invoke_guild_match)
        .order(invocation_records::time)
        .first(conn)
        .await
        .optional();

    tracing::debug!(?started_recording);

    let started_recording: Option<NaiveDateTime> = started_recording.map_err(Error::from)?;
    let started_recording = started_recording.map(to_utc);

    let total_meme_invocations: i64 = invocation_records::table
        .select(count_star())
        .filter(invoke_guild_match)
        .first(conn)
        .await
        .map_err(Error::from)?;

    let audio_meme_invocations: i64 = invocation_records::table
        .inner_join(memes::table)
        .select(count_star())
        .filter(memes::audio_id.is_not_null().and(guild_match))
        .first(conn)
        .await
        .map_err(Error::from)?;

    let random_meme_invocations: i64 = invocation_records::table
        .select(count_star())
        .filter(invocation_records::random.eq(true).and(invoke_guild_match))
        .first(conn)
        .await
        .map_err(Error::from)?;

    let raw_conn = raw_connection().await?;

    #[inline]
    fn option_first_count<T>(rows: Vec<tokio_postgres::Row>) -> (Option<T>, i64)
    where
        for<'a> T: FromSql<'a>,
    {
        let Some(row) = <[_]>::first(&rows) else {
            return (None, 0);
        };

        (row.get(0), row.get(1))
    }

    let row = raw_conn
        .query(
            r#"
    SELECT DATE(time) as dt, COUNT(*) FROM invocation_records
    WHERE guild = $1
    GROUP BY dt
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_active_day, most_active_day_count) = option_first_count(row);

    let row = raw_conn
        .query(
            r#"
    SELECT DATE(time) as dt, COUNT(*) FROM invocation_records
    INNER JOIN memes ON (invocation_records.meme_id = memes.id AND invocation_records.guild = memes.guild)
    WHERE memes.audio_id IS NOT NULL AND memes.guild = $1
    GROUP BY dt
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_active_audio_day, most_active_audio_day_count) = option_first_count(row);

    let row = raw_conn
        .query(
            r#"
    SELECT user_id, COUNT(*) FROM invocation_records
    WHERE random IS TRUE AND guild = $1
    GROUP BY user_id
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_random_invoker, most_random_invoker_count) = option_first_count::<i64>(row);

    let row = raw_conn
        .query(
            r#"
    SELECT user_id, COUNT(*) FROM invocation_records
    WHERE random IS FALSE AND guild = $1
    GROUP BY user_id
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_specific_invoker, most_specific_invoker_count) = option_first_count::<i64>(row);

    let row = raw_conn
        .query(
            r#"
    SELECT memes.title, COUNT(*) FROM invocation_records
    INNER JOIN memes ON (meme_id = memes.id AND invocation_records.guild = memes.guild)
    WHERE random IS FALSE AND memes.guild = $1
    GROUP BY memes.title
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_requested_meme, most_requested_meme_count) = option_first_count(row);

    let row = raw_conn
        .query(
            r#"
    SELECT memes.title, COUNT(*) FROM invocation_records
    INNER JOIN memes ON (meme_id = memes.id AND invocation_records.guild = memes.guild)
    WHERE random IS TRUE AND memes.guild = $1
    GROUP BY memes.title
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_random_meme, most_random_meme_count) = option_first_count(row);

    let row = raw_conn
        .query(
            r#"
    SELECT memes.title, COUNT(*) FROM invocation_records
    INNER JOIN memes ON (meme_id = memes.id AND invocation_records.guild = memes.guild)
    WHERE memes.guild = $1
    GROUP BY memes.title
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    "#,
            &[&guild],
        )
        .await?;

    let (most_invoked_meme, most_invoked_meme_count) = option_first_count(row);

    Ok(Stats {
        memes_overall: total_count as usize,
        image_memes: image_count as usize,
        audio_memes: audio_count as usize,
        started_recording,
        total_meme_invocations: total_meme_invocations as usize,
        audio_meme_invocations: audio_meme_invocations as usize,
        random_meme_invocations: random_meme_invocations as usize,

        most_active_day,
        most_active_day_count: most_active_day_count as usize,

        most_audio_active_day: most_active_audio_day,
        most_audio_active_count: most_active_audio_day_count as usize,

        most_random_meme_user: most_random_invoker.map(|x| x as u64),
        most_random_meme_user_count: most_random_invoker_count as usize,
        most_directly_named_meme_user: most_specific_invoker.map(|x| x as u64),
        most_directly_named_meme_count: most_specific_invoker_count as usize,

        most_popular_named_meme: most_requested_meme,
        most_popular_named_meme_count: most_requested_meme_count as usize,

        most_popular_random_meme: most_random_meme,
        most_popular_random_meme_count: most_random_meme_count as usize,

        most_popular_meme_overall: most_invoked_meme,
        most_popular_meme_overall_count: most_invoked_meme_count as usize,
    })
}

#[derive(Clone, Debug, Hash, PartialEq, Eq, Default)]
pub struct MemerInfo {
    pub user_id:              u64,
    pub random_memes:         usize,
    pub specific_memes:       usize,
    pub most_used_meme:       String,
    pub most_used_meme_count: usize,
}

pub async fn memers(guild: u64) -> Result<Vec<MemerInfo>> {
    let guild = guild as i64;
    let raw_conn = raw_connection().await?;

    let rows = raw_conn.query(r#"
    WITH random_count AS (
        SELECT user_id, COUNT(*) as count
        FROM invocation_records
        WHERE random = TRUE AND guild = $1
        GROUP BY user_id
    ),
         specific_count AS (
             SELECT user_id, COUNT(*) as count
             FROM invocation_records
             WHERE random = FALSE AND guild = $1
             GROUP BY user_id
         ),
         user_meme_counts AS (
             SELECT user_id, meme_id, COUNT(meme_id) as meme_count
             FROM invocation_records
             WHERE guild = $1 AND EXISTS (SELECT id FROM memes WHERE id = invocation_records.meme_id)
             GROUP BY user_id, meme_id
             ORDER BY user_id, meme_count DESC
         ),
         most_memed_per_user AS (
             SELECT user_id, MAX(meme_count) as max_count
             FROM user_meme_counts
             GROUP BY user_id
         ),
         most_memed AS (
             SELECT DISTINCT ON (user_meme_counts.user_id) user_meme_counts.user_id, user_meme_counts.meme_id, user_meme_counts.meme_count
             FROM user_meme_counts
             INNER JOIN most_memed_per_user ON user_meme_counts.user_id = most_memed_per_user.user_id
             WHERE user_meme_counts.meme_count = most_memed_per_user.max_count
         )
    SELECT random_count.user_id, random_count.count, specific_count.count, memes.title, most_memed.meme_count
    FROM random_count
    INNER JOIN most_memed ON most_memed.user_id = random_count.user_id
    INNER JOIN specific_count ON specific_count.user_id = random_count.user_id
    INNER JOIN memes ON memes.id = most_memed.meme_id
    ORDER BY (random_count.count + specific_count.count) DESC
    "#, &[&guild]).await?;

    let result = rows
        .iter()
        .map(|row| {
            let user_id: i64 = row.get(0);
            let random_count: i64 = row.get(1);
            let specific_count: i64 = row.get(2);
            let most_memed_meme: String = row.get(3);
            let most_memed_count: i64 = row.get(4);

            MemerInfo {
                user_id:              user_id as u64,
                random_memes:         random_count as usize,
                specific_memes:       specific_count as usize,
                most_used_meme:       most_memed_meme,
                most_used_meme_count: most_memed_count as usize,
            }
        })
        .collect();

    Ok(result)
}