use std::{str::FromStr, collections::HashMap};

use anyhow::bail;
use log::{warn, debug};
use postgres::{Config, NoTls, Client, GenericClient, Transaction};
use::anyhow::anyhow;
use semver::Version;
use crate::{config::ConnectionInfo, migration_data::{migrations::Migration, committed::CommittedFile}};

use super::{DatabaseBackend, MigrationWithStatus, MigrationStatus};


#[derive(Debug)]
pub struct PgBackend {
  config: postgres::Config,
  meta_tables_schema: String
}

struct TryRecord {
  // migration_id: String,
  hash: String
}

impl PgBackend {
    pub fn new(connection_info: &ConnectionInfo) -> anyhow::Result<Self> {
      let config = match connection_info {
        ConnectionInfo::Url(u) => Config::from_str(u.as_ref().ok_or_else(|| anyhow!("connection string url is blank"))?)?,
        ConnectionInfo::Params {
          user,
          password,
          dbname,
          options,
          host,
          port
        } => {
          let mut c = postgres::Config::new();
          if let Some(u) = user { c.user(u); } else { c.user(&whoami::username()); }
          if let Some(pw) = password { c.password(pw); }
          if let Some(db) = dbname { c.dbname(db); }
          if let Some(opt) = options { c.options(opt); }
          if let Some(h) = host { c.host(h); } else { c.host("localhost"); }
          if let Some(p) = port { c.port(p.parse()?); }

          c
        },
      };
      let instance = Self { config, meta_tables_schema: "public".into() };
      instance.setup()?;
      Ok(instance)
    }

    fn connect(&self) -> anyhow::Result<Client> {
      Ok(self.config.connect(NoTls)?)
    }

    fn config_table(&self) -> String {
      format!("{}.salmo_meta", self.meta_tables_schema)
    }

    fn tries_table(&self) -> String {
      format!("{}.salmo_tried_migrations", self.meta_tables_schema)
    }

    fn executions_table(&self) -> String {
      format!("{}.salmo_executed_migrations", self.meta_tables_schema)
    }

    fn setup(&self) -> anyhow::Result<()> {
      let mut client = self.connect()?;
      let mut t = client.transaction()?;
      let config_table_name = self.config_table();
      let try_table_name = self.tries_table();
      let exe_table_name = self.executions_table();

      t.execute(&format!("
      CREATE TABLE IF NOT EXISTS {config_table_name} (
        id int PRIMARY KEY,
        version text
      )"), &[])?;
      let v: Option<String> = t.query_opt(&format!("SELECT version FROM {config_table_name} WHERE id = 0"), &[])?.map(|r| r.get(0));
      let app_version = META_MIGRATIONS[META_MIGRATIONS.len() - 1].0;
      if let Some(db_version) = &v {
        if Version::parse(db_version)? > Version::parse(app_version)? {
          bail!("database is managed using a later version of salmo. This schema version: {}, db schema version: {}", app_version, db_version)
        }
      }

      let meta_migration_index = v.and_then(|version|{
        META_MIGRATIONS.iter().position(|m| m.0 == version).map(|p| p + 1)
      }).unwrap_or(0);
      for (m_version, m_text) in META_MIGRATIONS[meta_migration_index..].iter() {
        let processed_m = m_text
          .replace("__meta_table__", &config_table_name)
          .replace("__tries_table__", &try_table_name)
          .replace("__executions_table__", &exe_table_name);
        debug!("Executing meta migration: {m_version}");
        t.batch_execute(&processed_m)?;
      }
      t.execute(
        &format!("INSERT INTO {config_table_name} (id, version) VALUES (0, $1) ON CONFLICT (id) DO UPDATE SET version = EXCLUDED.version"),
        &[&app_version])?;

      t.commit()?;
      Ok(())
    }


    fn get_status(&self, client: &mut impl GenericClient, commits_file: &CommittedFile, migrations: &[Migration]) -> anyhow::Result<Vec<MigrationWithStatus>> {
      let migration_ids = migrations.iter().map(|m| m.id.as_str()).collect::<Vec<_>>();
      let tries = get_tries(client, &self.tries_table(), &migration_ids)?;
      let executions = get_executions(client, &self.executions_table(), &migration_ids)?;
      let commits = commits_file.commits_hash();

      migrations.iter().map(|m| {
        let status = match (tries.get(&m.id), commits.get(&m.id), executions.get(&m.id)) {
            (None, None, None) => MigrationStatus::Untried,
            (Some(t), None, None) => MigrationStatus::Tried { up_to_date: m.migrate_hash()? == t.hash },
            (None, Some(_), None) => MigrationStatus::Committed {tried: false},
            (Some(_), Some(_), None) => MigrationStatus::Committed {tried: true},
            (None, Some(_), Some(_)) => MigrationStatus::Executed,
            (t, c, e) => panic!("invalid migration: {}; tried={}, committed={}, executed={}", m.id, t.is_some(), c.is_some(), e.is_some()),
        };

        Ok(MigrationWithStatus {
            migration: m.clone(),
            status,
        })
      }).collect::<anyhow::Result<_>>()
    }

    fn try_m(&self, client: &mut Transaction, m: &Migration) -> anyhow::Result<()> {
      self.run_migration_script(client, &m.migrate_sql()?)?;
      let q = format!("INSERT INTO {} (id, hash) VALUES ($1, $2)", self.tries_table());
      client.execute(&q, &[&m.id, &m.migrate_hash()?])?;
      Ok(())
    }

    fn untry_m(&self, client: &mut Transaction, m: &Migration) -> anyhow::Result<()> {
      self.run_migration_script(client, &m.revert_sql()?)?;
      let q = format!("DELETE FROM {} WHERE id = $1", self.tries_table());
      client.execute(&q, &[&m.id])?;
      Ok(())
    }

    fn execute_m(&self, client: &mut Transaction, m: &Migration, run_migration: bool) -> anyhow::Result<()> {
      if run_migration {
        self.run_migration_script(client, &m.migrate_sql()?)?;
      }

      let q = format!("INSERT INTO {} (id) VALUES ($1)", self.executions_table());
      client.execute(&q, &[&m.id])?;
      Ok(())
    }

    fn run_migration_script(&self, client: &mut Transaction, script: &str) -> anyhow::Result<()> {
      client.batch_execute(script)?;
      Ok(())
    }

}

impl DatabaseBackend for PgBackend {
    fn try_migrations(&mut self, migrations: &[MigrationWithStatus]) -> anyhow::Result<()> {
      let mut client = self.connect()?;
      let mut t = client.transaction()?;
      for m in migrations {
        match m.status {
            MigrationStatus::Untried => {
              self.try_m(&mut t, &m.migration)?
            },
            MigrationStatus::Tried { up_to_date } => {
              warn!("migration {} has already been tried, skipping (it is{} up to date)", m.migration.id, if up_to_date { "" } else { " not" })
            },
            MigrationStatus::Committed {tried: _} => bail!("Cannot try {} it is already committed", m.migration.id),
            MigrationStatus::Executed => bail!("Cannot try {} it is already committed and executed", m.migration.id),
        }
      }
      t.commit()?;
      Ok(())
    }

    fn untry_migrations(&mut self, migrations: &[MigrationWithStatus]) -> anyhow::Result<()> {
      let mut client = self.connect()?;
      let mut t = client.transaction()?;
      for m in migrations {
        match m.status {
            MigrationStatus::Untried => {
              warn!("migration {} has not been tried, skipping", m.migration.id)
            },
            MigrationStatus::Tried { up_to_date: _  } => {
              self.untry_m(&mut t, &m.migration)?
            },
            MigrationStatus::Committed {tried: _} => bail!("Cannot untry {} it is already committed", m.migration.id),
            MigrationStatus::Executed => bail!("Cannot untry {} it is already committed and executed", m.migration.id),
        }
      }
      t.commit()?;
      Ok(())
    }

    fn migration_status(&mut self, commits_file: &CommittedFile, migrations: &[Migration]) -> anyhow::Result<Vec<MigrationWithStatus>> {
      let mut client = self.connect()?;
      self.get_status(&mut client, commits_file, migrations)
    }

    fn execute_migrations(&mut self, migrations: &[MigrationWithStatus]) -> anyhow::Result<()> {
      let mut client = self.connect()?;
      let mut t = client.transaction()?;
      for m in migrations {
        match m.status {
            MigrationStatus::Untried => {
              warn!("migration {} has not been tried, skipping", m.migration.id)
            },
            MigrationStatus::Tried { up_to_date: _ } => {
              warn!("migration {} has not been committed, skipping", m.migration.id)
            },
            MigrationStatus::Committed {tried} => {
              debug!("executing {}; tried={:?}", m.migration.id, tried);
              self.execute_m(&mut t, &m.migration, !tried)?
            },
            MigrationStatus::Executed => {}, // do nothing for these
        }
      }
      t.commit()?;
      Ok(())
    }
}


fn get_tries(client: &mut impl GenericClient, table_name: &str, migration_ids: &[&str]) -> anyhow::Result<HashMap<String, TryRecord>> {
  let tries_query = format!("SELECT id, hash FROM {} WHERE id = ANY ($1)", table_name);
  let tries = client.query(&tries_query, &[&migration_ids])?
        .into_iter()
        .map(|r| {
          let migration_id: String = r.get(0);
          (migration_id, TryRecord { hash: r.get(1) })
        })
        .collect();
  Ok(tries)
}

fn get_executions(client: &mut impl GenericClient, table_name: &str, migration_ids: &[&str]) -> anyhow::Result<HashMap<String, String>> {
  let tries_query = format!("SELECT id FROM {} WHERE id = ANY ($1)", table_name);
  let tries = client.query(&tries_query, &[&migration_ids])?
        .into_iter()
        .map(|r| {
          let migration_id: String = r.get(0);
          (migration_id.clone(), migration_id)
        })
        .collect();
  Ok(tries)
}

const META_MIGRATIONS: &[(&str, &str)] = &[
  ("0.1.0", "
    CREATE TABLE IF NOT EXISTS __tries_table__ (
      id TEXT PRIMARY KEY,
      hash TEXT,
      tried_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );
    CREATE TABLE IF NOT EXISTS __executions_table__ (
      id TEXT PRIMARY KEY,
      committed_index integer
    );
  "), // placeholder for initial
  ("0.2.0", "ALTER TABLE __executions_table__ DROP COLUMN IF EXISTS committed_index")
];