use std::mem;
use crate::database::error::DatabaseError;
use chrono::{DateTime, NaiveDate, TimeZone, Utc};
use proto::touchpad::common::v1::{Gender, SwimMeet, Swimmer, Team};
use sqlx::postgres::{PgPool, PgPoolOptions};
use proto::ProtoTimestamp;

pub struct PostgresClient {
    pool: PgPool,
}

impl PostgresClient {
    pub async fn new<S: AsRef<str>>(url: S) -> Result<PostgresClient, sqlx::Error> {
        Ok(PostgresClient {
            pool: PgPoolOptions::new().connect(url.as_ref()).await?,
        })
    }
}

#[async_trait::async_trait]
impl super::DatabaseClient for PostgresClient {
    async fn get_swimmer(&self, id: u32) -> Result<Swimmer, DatabaseError> {
        let swimmer: SwimmersTableSchema = sqlx::query_as!(
            SwimmersTableSchema,
            "SELECT * FROM swimmers WHERE id = $1;",
            id as i32
        )
            .fetch_one(&self.pool)
            .await?;

        Ok(Swimmer {
            id,
            name: swimmer.name,
            gender: Gender::from_iso_5218(swimmer.gender as u8) as i32,
        })
    }

    async fn add_swimmer(&self, swimmer: &Swimmer) -> Result<(), DatabaseError> {
        let gender = Gender::from_i32(swimmer.gender)
            .ok_or(DatabaseError::BadInput("Invalid gender".into()))?
            .to_iso_5218();

        sqlx::query!(
            "INSERT INTO swimmers VALUES ($1, $2, $3);",
            swimmer.id as i32,
            swimmer.name,
            gender as i16
        )
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    async fn get_team(&self, id: u32) -> crate::database::Result<Team> {
        let team: TeamsTableSchema = sqlx::query_as!(
            TeamsTableSchema,
            "SELECT * FROM teams WHERE id = $1;",
            id as i32
        )
            .fetch_one(&self.pool)
            .await?;

        Ok(Team {
            id,
            name: team.name,
        })
    }

    async fn add_team(&self, team: &Team) -> crate::database::Result<()> {
        sqlx::query!(
            "INSERT INTO teams VALUES ($1, $2);",
            team.id as i32,
            team.name
        )
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    async fn get_meet(&self, id: u32) -> crate::database::Result<SwimMeet> {
        let mut meet: Vec<GetMeetSchema> = sqlx::query_as!(
            GetMeetSchema,
            "
SELECT meets.name as meet_name, start_date, end_date, team_id, t.name as team_name
FROM meets
INNER JOIN meets_teams mt on meets.id = mt.meet_id
INNER JOIN teams t on mt.team_id = t.id
WHERE meets.id = $1;
",
            id as i32
        )
            .fetch_all(&self.pool)
            .await?;

        let meet_name = mem::take(&mut meet.get_mut(0).ok_or(DatabaseError::NotFound)?.meet_name);
        let start = meet.get(0).ok_or(DatabaseError::NotFound)?.start_date.and_hms(0, 0, 0);
        let end = meet.get(0).ok_or(DatabaseError::NotFound)?.end_date.and_hms(0, 0, 0);

        let teams = meet.into_iter()
            .map(|m| Team {
                id: m.team_id as u32,
                name: m.team_name,
            })
            .collect();

        Ok(SwimMeet {
            id,
            meet_name,
            start: Some(Utc.from_utc_datetime(&start).into()),
            end: Some(Utc.from_utc_datetime(&end).into()),
            teams,
            points: Default::default(),
        })
    }

    async fn add_meet(&self, meet: &SwimMeet) -> crate::database::Result<()> {
        let mut tx = self.pool.begin().await?;

        // Convert protobuf timestamp into chrono Datetime
        let start_date: DateTime<Utc> = {
            let proto_timestamp: ProtoTimestamp = meet.start.clone()
                .ok_or(DatabaseError::BadInput("empty start date".into()))?
                .into();
            proto_timestamp.into()
        };

        let end_date: DateTime<Utc> = {
            let proto_timestamp: ProtoTimestamp = meet.end.clone()
                .ok_or(DatabaseError::BadInput("empty end date".into()))?
                .into();
            proto_timestamp.into()
        };

        // Insert into meets table
        sqlx::query!(
            "INSERT INTO meets VALUES ($1, $2, $3, $4)",
            meet.id as i32,
            meet.meet_name,
            start_date.date_naive(),
            end_date.date_naive(),
        )
            .execute(&mut tx)
            .await?;

        for team in &meet.teams {
            sqlx::query!(
                "INSERT INTO teams VALUES ($1, $2) ON CONFLICT DO NOTHING",
                team.id as i32,
                team.name,
            )
                .execute(&mut tx)
                .await?;

            sqlx::query!(
                "INSERT INTO meets_teams VALUES ($1, $2)",
                meet.id as i32,
                team.id as i32,
            )
                .execute(&mut tx)
                .await?;
        }

        tx.commit().await?;

        Ok(())
    }
}

struct SwimmersTableSchema {
    id: i32,
    name: String,
    gender: i16,
}

struct TeamsTableSchema {
    id: i32,
    name: String,
}

struct GetMeetSchema {
    meet_name: String,
    start_date: NaiveDate,
    end_date: NaiveDate,
    team_id: i32,
    team_name: String,
}