use std::{path::Path, str::FromStr, time::Duration};

use fs_err::{self as fs};
use sql_builder::{SqlBuilder, SqlName};
use sqlx::{
    Result, Row,
    sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePool, SqliteRow, SqliteSynchronous},
};
use time::OffsetDateTime;
use tracing::debug;
use turtle_api::history::{History, HistoryId};
use turtle_common::utils;

use crate::aclient::utils::setup_db;

// Intended for use on a developer machine and not a sync server.
// TODO: implement IntoIterator
#[derive(Debug, Clone)]
pub(crate) struct ClientSqlite {
    pool: SqlitePool,
}

impl ClientSqlite {
    pub(crate) async fn new(path: impl AsRef<Path>, timeout: f64) -> Result<Self> {
        fn mk_opts(path: &str) -> Result<SqliteConnectOptions> {
            let opts = SqliteConnectOptions::from_str(path)?
                .journal_mode(SqliteJournalMode::Wal)
                .optimize_on_close(true, None)
                .synchronous(SqliteSynchronous::Normal)
                .with_regexp()
                .create_if_missing(true);

            Ok(opts)
        }

        let path = path.as_ref();
        debug!("opening sqlite database at {path:?}");

        if utils::broken_symlink(path) {
            eprintln!(
                "Atuin: Sqlite db path ({}) is a broken symlink. Unable to read or create replacement.",
                path.display()
            );
            std::process::exit(1);
        }

        if !path.exists()
            && let Some(dir) = path.parent()
        {
            fs::create_dir_all(dir)?;
        }

        let pool = setup_db!(path, timeout, mk_opts, "./db/client-migrations").await?;
        Ok(Self { pool })
    }

    async fn save_raw(tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>, h: &History) -> Result<()> {
        sqlx::query(
            "insert or ignore into history(id, timestamp, duration, exit, command, cwd, session, hostname, author, intent, deleted_at)
                values(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
        )
        .bind(h.id.to_string().as_str())
        .bind(h.timestamp.unix_timestamp_nanos() as i64)
        .bind(h.duration.as_nanos() as i64)
        .bind(h.exit)
        .bind(h.command.as_str())
        .bind(h.cwd.as_str())
        .bind(h.session.as_str())
        .bind(h.hostname.as_str())
        .bind(h.author.as_str())
        .bind(h.intent.as_deref())
        .bind(h.deleted_at.map(|t|t.unix_timestamp_nanos() as i64))
        .execute(&mut **tx)
        .await?;

        Ok(())
    }

    async fn delete_row_raw(
        tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
        id: HistoryId,
    ) -> Result<()> {
        sqlx::query("delete from history where id = ?1")
            .bind(id.to_string().as_str())
            .execute(&mut **tx)
            .await?;

        Ok(())
    }

    #[expect(clippy::needless_pass_by_value)]
    fn query_history_inner(row: SqliteRow) -> History {
        let deleted_at: Option<i64> = row.get("deleted_at");
        let hostname: String = row.get("hostname");
        let author: Option<String> = row.try_get("author").ok().flatten();
        let author = author
            .filter(|author| !author.trim().is_empty())
            .unwrap_or_else(|| History::author_from_hostname(hostname.as_str()));
        let intent: Option<String> = row.try_get("intent").ok().flatten();
        let intent = intent.filter(|intent| !intent.trim().is_empty());

        History::from_db()
            .id(row.get("id"))
            .timestamp(
                OffsetDateTime::from_unix_timestamp_nanos(i128::from(
                    row.get::<i64, _>("timestamp"),
                ))
                .unwrap(),
            )
            .duration(Duration::from_nanos(
                u64::try_from(row.get::<i64, _>("duration")).expect("to be small enough"),
            ))
            .exit(row.get("exit"))
            .command(row.get("command"))
            .cwd(row.get("cwd"))
            .session(row.get("session"))
            .hostname(hostname)
            .author(author)
            .intent(intent)
            .deleted_at(
                deleted_at
                    .and_then(|t| OffsetDateTime::from_unix_timestamp_nanos(i128::from(t)).ok()),
            )
            .build()
            .into()
    }
}

impl ClientSqlite {
    pub(crate) async fn save(&self, h: &History) -> Result<()> {
        debug!("saving history to sqlite");
        let mut tx = self.pool.begin().await?;
        Self::save_raw(&mut tx, h).await?;
        tx.commit().await?;

        Ok(())
    }

    /// make a unique list, that only shows the *newest* version of things
    pub(crate) async fn list(
        &self,
        max: Option<usize>,
        unique: bool,
        include_deleted: bool,
    ) -> Result<Vec<History>> {
        debug!("listing history");

        let mut query = SqlBuilder::select_from(SqlName::new("history").alias("h").baquoted());
        query.field("*").order_desc("timestamp");
        if !include_deleted {
            query.and_where_is_null("deleted_at");
        }

        if unique {
            query.group_by("command").having("max(timestamp)");
        }

        if let Some(max) = max {
            query.limit(max);
        }

        let query = query.sql().expect("bug in list query. please report");

        let res = sqlx::query(&query)
            .map(Self::query_history_inner)
            .fetch_all(&self.pool)
            .await?;

        Ok(res)
    }

    pub(crate) async fn range(
        &self,
        from: OffsetDateTime,
        to: OffsetDateTime,
    ) -> Result<Vec<History>> {
        debug!("listing history from {:?} to {:?}", from, to);

        let res = sqlx::query(
            "select * from history where timestamp >= ?1 and timestamp <= ?2 order by timestamp asc",
        )
        .bind(from.unix_timestamp_nanos() as i64)
        .bind(to.unix_timestamp_nanos() as i64)
            .map(Self::query_history_inner)
        .fetch_all(&self.pool)
        .await?;

        Ok(res)
    }

    pub(crate) async fn delete_rows(&self, ids: &[HistoryId]) -> Result<()> {
        let mut tx = self.pool.begin().await?;

        for id in ids {
            Self::delete_row_raw(&mut tx, id.clone()).await?;
        }

        tx.commit().await?;

        Ok(())
    }
}