encounter_stats (id) {
        id -> Int4,
        team -> Int4,
        encounter_id -> Int4,
        difficulty -> Int4,
        kill_log -> Nullable<Int4>,
        kill_week -> Nullable<Int4>,
        ilvl_min -> Float4,
        ilvl_max -> Float4,
        pull_count -> Int4,
        prog_time -> Int4,
    }
}
table! {

joinable!(encounter_stats -> logs (kill_log));

    encounter_stats,

}
#[derive(Debug, Insertable)]
#[table_name = "encounter_stats"]
pub struct EncounterStats {
    pub team: i32,
    pub encounter_id: i32,
    pub difficulty: i32,
    pub kill_log: Option<i32>,
    pub kill_week: Option<i32>,
    pub ilvl_min: f32,
    pub ilvl_max: f32,
    pub pull_count: i32,
    pub prog_time: i32,

mod analysis;

use crate::analysis::{generate_analysis, logs_for_encounter};

        use crate::schema::fights::dsl as fights;
        use crate::schema::logs::{dsl as logs, table as logs_t};
        use crate::schema::teams::{dsl as team_dsl, table as teams};

        Ok(fights::fights
            .inner_join(logs_t.inner_join(teams))
            .filter(
                team_dsl::id
                    .eq(team_id)
                    .and(fights::difficulty.eq(difficulty))
                    .and(fights::encounter_id.eq(encounter_id)),
            )
            // annoying that i have to list these out, but oh well
            .select((
                logs::iid,
                logs::code,
                logs::team,
                logs::start_time,
                logs::end_time,
                logs::title,
                logs::zone_id,
            ))
            .distinct_on(logs::iid)
            .load::<Log>(&*conpool.get()?)?)

        let logs = logs_for_encounter(&*conpool.get()?, team_id, encounter_id, difficulty)?;
        Ok(logs)

    generate_analysis(&*con, guild_id.id() as i32)?;

use crate::model::{EncounterStats, Fight, Log};
use anyhow::Result;
use chrono::prelude::*;
use chrono::Duration;
use diesel::pg::upsert::excluded;
use diesel::prelude::*;
use log::debug;
use std::collections::HashMap;
use std::convert::TryFrom;
pub fn logs_for_encounter(
    con: &PgConnection,
    team_id: i32,
    encounter_id: i32,
    difficulty: i32,
) -> Result<Vec<Log>> {
    use crate::schema::fights::dsl as fights;
    use crate::schema::logs::{dsl as logs, table as logs_t};
    use crate::schema::teams::{dsl as team_dsl, table as teams};
    Ok(fights::fights
        .inner_join(logs_t.inner_join(teams))
        .filter(
            team_dsl::id
                .eq(team_id)
                .and(fights::difficulty.eq(difficulty))
                .and(fights::encounter_id.eq(encounter_id)),
        )
        // annoying that i have to list these out, but oh well
        .select((
            logs::iid,
            logs::code,
            logs::team,
            logs::start_time,
            logs::end_time,
            logs::title,
            logs::zone_id,
        ))
        .distinct_on((logs::iid, logs::start_time))
        .order(logs::start_time.asc())
        .load::<Log>(con)?)
}
fn overlapping_logs(a: &Log, b: &Log) -> bool {
    (a.start_time <= b.start_time && b.start_time <= a.end_time)
        || (b.start_time <= a.start_time && a.start_time <= b.end_time)
}
fn longer_log(
    (fight_a, log_a): (&Vec<Fight>, Log),
    (fight_b, log_b): (&Vec<Fight>, Log),
) -> (Log, Log) {
    if fight_a.len() >= fight_b.len() {
        (log_a, log_b)
    } else {
        (log_b, log_a)
    }
}
#[derive(Debug, Copy, Clone)]
enum Region {
    NA,
    EU,
    CN,
}
impl TryFrom<String> for Region {
    type Error = anyhow::Error;
    fn try_from(value: String) -> Result<Self> {
        match value.as_str() {
            "US" | "us" => Ok(Region::NA),
            "EU" | "eu" => Ok(Region::EU),
            "CN" | "cn" => Ok(Region::CN),
            _ => Err(anyhow::anyhow!("invalid region code")),
        }
    }
}
fn region_offset(region: Region) -> Duration {
    use Region::*;
    match region {
        NA => Duration::days(0),
        EU => Duration::days(1),
        CN => Duration::days(2),
    }
}
fn tier_week(log: &Log, region: Region) -> i32 {
    // TODO: lookup tier start date
    let tier_start = Utc.ymd(2020, 12, 8).and_hms(0, 0, 0);
    let diff = log.start_time - (tier_start + region_offset(region));
    diff.num_weeks() as i32
}
fn team_region(con: &PgConnection, team_id: i32) -> Result<Region> {
    use crate::schema::teams::dsl as teams;
    let region = teams::teams
        .filter(teams::id.eq(team_id))
        .select(teams::region_slug)
        .first::<String>(con)?;
    Region::try_from(region)
}
fn upsert_stats(con: &PgConnection, stat: EncounterStats) -> Result<()> {
    use crate::schema::encounter_stats::dsl as enc;
    diesel::insert_into(enc::encounter_stats)
        .values(&stat)
        .on_conflict((enc::team, enc::encounter_id, enc::difficulty))
        .do_update()
        .set((
            enc::kill_log.eq(excluded(enc::kill_log)),
            enc::kill_week.eq(excluded(enc::kill_week)),
            enc::ilvl_min.eq(excluded(enc::ilvl_min)),
            enc::ilvl_max.eq(excluded(enc::ilvl_max)),
            enc::pull_count.eq(excluded(enc::pull_count)),
            enc::prog_time.eq(excluded(enc::prog_time)),
        ))
        .execute(con)?;
    Ok(())
}
// Generate the analysis entries for a team on the given encounter and difficulty. This overwites
// any existing analysis for that 3-tuple.
pub fn generate_encounter_analysis(
    con: &PgConnection,
    team_id: i32,
    encounter_id: i32,
    difficulty: i32,
) -> Result<()> {
    let raw_logs = logs_for_encounter(con, team_id, encounter_id, difficulty)?;
    let region = team_region(con, team_id)?;
    let mut fightmap = HashMap::new();
    let mut logs = Vec::with_capacity(raw_logs.len());
    let mut kill_log = None;
    debug!(
        "generating analysis for {} {} {}",
        team_id, encounter_id, difficulty
    );
    for log in raw_logs {
        use crate::schema::fights::dsl as fights;
        let f = fights::fights
            .filter(
                fights::log
                    .eq(log.iid)
                    .and(fights::encounter_id.eq(encounter_id))
                    .and(fights::difficulty.eq(difficulty)),
            )
            .order(fights::id)
            .load::<Fight>(con)?;
        let code = log.code.clone();
        debug!("processing log {}", code);
        let kill = f.iter().any(|fight| fight.kill);
        fightmap.insert(log.iid, f);
        if logs.is_empty() {
            logs.push(log);
        } else {
            let overlap = {
                let prev = logs.last().expect("the log vec to be non-empty");
                overlapping_logs(prev, &log)
            };
            // merging close logs happens on the frontend. it isn't super relevant for the backend
            // analysis, only for charting
            if overlap {
                let prev = logs.pop().unwrap();
                let prev_code = prev.code.clone();
                let (keep, discard) =
                    longer_log((&fightmap[&prev.iid], prev), (&fightmap[&log.iid], log));
                debug!(
                    "log {} overlaps with {}. using {}",
                    code, prev_code, &keep.code
                );
                fightmap.remove(&discard.iid);
                logs.push(keep);
            } else {
                logs.push(log);
            }
        }
        if kill {
            kill_log = logs.last();
            break;
        }
    }
    let mut ilvl_min = std::f32::INFINITY;
    let mut ilvl_max = std::f32::NEG_INFINITY;
    let mut pull_count: i32 = 0;
    let mut prog_time: i32 = 0;
    for log in &logs {
        let fights = &fightmap[&log.iid];
        if fights.is_empty() {
            // this shouldn't happen, but weirder shit has occurred
            continue;
        }
        pull_count += fights.len() as i32;
        prog_time += fights.last().expect("there to be a fight").end_time - fights[0].start_time;
        for fight in fights {
            ilvl_min = fight.avg_ilvl.unwrap_or(ilvl_min).min(ilvl_min);
            ilvl_max = fight.avg_ilvl.unwrap_or(ilvl_max).max(ilvl_max);
        }
    }
    let stats = EncounterStats {
        team: team_id,
        encounter_id,
        difficulty,
        kill_log: kill_log.map(|log| log.iid),
        kill_week: kill_log.as_ref().map(|log| tier_week(log, region)),
        ilvl_min,
        ilvl_max,
        pull_count,
        prog_time,
    };
    debug!(
        "calculated stats for {} {} {}: {:?}",
        team_id, encounter_id, difficulty, &stats
    );
    upsert_stats(con, stats)
}
pub fn generate_analysis(con: &PgConnection, team_id: i32) -> Result<()> {
    use crate::schema::fights::dsl as fights;
    use crate::schema::logs::table as logs_t;
    use crate::schema::teams::{dsl as team_dsl, table as teams};
    let encounters = fights::fights
        .inner_join(logs_t.inner_join(teams))
        .filter(team_dsl::id.eq(team_id))
        // annoying that i have to list these out, but oh well
        .select((fights::encounter_id, fights::difficulty))
        .distinct()
        .load::<(i32, Option<i32>)>(con)?;
    for (encounter_id, difficulty) in encounters {
        if let Some(difficulty) = difficulty {
            generate_encounter_analysis(con, team_id, encounter_id, difficulty)?;
        }
    }
    Ok(())
}

alter table teams add unique(id);
create table encounter_stats (
  id serial primary key,
  team integer not null references teams(id),
  encounter_id integer not null,
  difficulty integer not null,
  kill_log integer references logs(iid),
  kill_week integer,
  ilvl_min real not null,
  ilvl_max real not null,
  pull_count integer not null,
  prog_time integer not null,
  unique(team, encounter_id, difficulty)
);

-- This file should undo anything in `up.sql`