use std::str::FromStr;

use crate::db;

use super::records::{EquityChangeEvent, EquitySymbol, FiduciaryAccount};

use super::{ContextError, Store};
use bigdecimal::ToPrimitive;
use chrono::Local;
use sqlite::{Connection, Row};

type DateTime = chrono::DateTime<chrono::Utc>;
type Result<T> = miette::Result<T, ContextError>;
type RowResult = miette::Result<Row, sqlite::Error>;

pub struct SQLiteMigration {
    migration_id: usize,
    migration_filename: String,
    migration_timestamp: DateTime,
}

pub struct SQLiteStore {
    location: String,
    connection: Option<Connection>,
}

/// Define data conversion from sqlite::Row to SQLiteMigration
impl TryFrom<RowResult> for SQLiteMigration {
    type Error = ContextError;
    fn try_from(value: RowResult) -> Result<Self> {
        let result = value?;

        let id: i64 = result.try_read("migration_id")?;
        let filename: &str = result.try_read("migration_filename")?;
        let timestamp: &str = result.try_read("migration_timestamp")?;

        Ok(SQLiteMigration {
            migration_id: usize::try_from(id)?,
            migration_filename: filename.into(),
            migration_timestamp: DateTime::from_str(timestamp)?,
        })
    }
}

/// Define data conversion from sqlite::Row to FiduciaryAccount
impl TryFrom<RowResult> for FiduciaryAccount {
    type Error = ContextError;
    fn try_from(value: RowResult) -> Result<Self> {
        let result = value?;

        let id: i64 = result.try_read("fiduciary_account_id")?;
        let number: i64 = result.try_read("fiduciary_account_number")?;
        let name: &str = result.try_read("fiduciary_account_name")?;
        let description: &str = result.try_read("fiduciary_account_description")?;
        let timestamp: &str = result.try_read("fiduciary_account_created_timestamp")?;

        Ok(FiduciaryAccount {
            fiduciary_account_id: u8::try_from(id)?,
            fiduciary_account_number: usize::try_from(number)?,
            fiduciary_account_name: name.into(),
            fiduciary_account_description: description.into(),
            fiduciary_account_created_timestamp: DateTime::from_str(timestamp)?,
        })
    }
}

/// Define data conversion from sqlite::Row to EquitySymbol
impl TryFrom<RowResult> for EquitySymbol {
    type Error = ContextError;
    fn try_from(value: RowResult) -> Result<Self> {
        let result = value?;

        let id: i64 = result.try_read("equity_symbol_id")?;
        let name: &str = result.try_read("equity_symbol_name")?;
        let symbol_type: &str = result.try_read("equity_symbol_type")?;
        let company: &str = result.try_read("equity_symbol_managing_company")?;
        let timestamp: &str = result.try_read("equity_symbol_created_timestamp")?;

        Ok(EquitySymbol {
            equity_symbol_id: usize::try_from(id)?,
            equity_symbol_name: name.into(),
            equity_symbol_type: symbol_type.try_into()?,
            equity_symbol_managing_company: company.into(),
            equity_symbol_created_timestamp: DateTime::from_str(&timestamp)?,
        })
    }
}

/// Define data conversion from sqlite::Row to EquityChangeEvent
impl TryFrom<RowResult> for EquityChangeEvent {
    type Error = ContextError;
    fn try_from(value: RowResult) -> Result<Self> {
        let result = value?;

        let event_id: i64 = result.try_read("equity_change_event_id")?;
        let account_id: i64 = result.try_read("fiduciary_account_id")?;
        let symbol_id: i64 = result.try_read("equity_symbol_id")?;

        // FP is not to be trusted !!
        let quantity: &str = result.try_read("equity_change_quantity")?;
        let cost_basis: &str = result.try_read("equity_change_cost_basis_usd")?;

        let change_type: &str = result.try_read("equity_change_type")?;
        let timestamp: &str = result.try_read("equity_change_timestamp")?;

        Ok(EquityChangeEvent {
            equity_change_event_id: usize::try_from(event_id)?,
            fiduciary_account_id: u8::try_from(account_id)?,
            equity_symbol_id: usize::try_from(symbol_id)?,
            equity_change_quantity: bigdecimal::BigDecimal::from_str(quantity)?,
            equity_change_cost_basis: bigdecimal::BigDecimal::from_str(cost_basis)?,
            equity_change_type: change_type.try_into()?,
            equity_change_timestamp: DateTime::from_str(&timestamp)?,
        })
    }
}

impl SQLiteStore {
    const FIDUCIARY_ACCOUNT_INSERT_QUERY: &str = "
        INSERT INTO fiduciary_account VALUES (
            :fiduciary_account_id,
            :fiduciary_account_number,
            :fiduciary_account_name,
            :fiduciary_account_description,
            :fiduciary_account_created_timestamp
        )";
    const FIDUCIARY_ACCOUNT_SELECT_QUERY: &str = "
        SELECT
            fiduciary_account_id,
            fiduciary_account_number,
            fiduciary_account_name,
            fiduciary_account_description,
            fiduciary_account_created_timestamp
        FROM fiduciary_account";
    const EQUITY_SYMBOL_INSERT_QUERY: &str = "
        INSERT INTO equity_symbol VALUES (
            :equity_symbol_id,
            :equity_symbol_name,
            :equity_symbol_type,
            :equity_symbol_managing_company,
            :equity_symbol_created_timestamp
        )";
    const EQUITY_SYMBOL_SELECT_QUERY: &str = "
        SELECT
            equity_symbol_id,
            equity_symbol_name,
            equity_symbol_type,
            equity_symbol_managing_company,
            equity_symbol_created_timestamp
        FROM equity_symbol";
    const EQUITY_CHANGE_EVENT_INSERT_QUERY: &str = "
        INSERT INTO equity_change_event VALUES (
            :equity_change_event_id,
            :fiduciary_account_id,
            :equity_symbol_id,
            :equity_change_quantity,
            :equity_change_cost_basis_usd,
            :equity_change_type,
            :equity_change_timestamp
        )";
    const EQUITY_CHANGE_EVENT_SELECT_QUERY: &str = "
        SELECT
            equity_change_event_id,
            fiduciary_account_id,
            equity_symbol_id,
            equity_change_quantity,
            equity_change_cost_basis_usd,
            equity_change_type,
            equity_change_timestamp
        FROM equity_change_event";

    const MIGRATION_CONTROL_CREATE_QUERY: &str = "
        CREATE TABLE migration_control (
            migration_id        UNSIGNED INT PRIMARY KEY,
            migration_filename  NVARCHAR(512) NOT NULL,
            migration_timestamp TIMESTAMP NOT NULL
        )";
    const MIGRATION_CONTROL_SELECT_QUERY: &str = "
        SELECT
            migration_id,
            migration_filename,
            migration_timestamp
        FROM migration_control";
    const MIGRATION_CONTROL_INSERT_QUERY: &str = "
        INSERT INTO migration_control VALUES (
            :migration_id,
            :migration_filename,
            :migration_timestamp
        )";

    /// Wrap connection in a Result to make it easier to unwrap later
    fn get_connection(&self) -> Result<&Connection> {
        match &self.connection {
            Some(conn) => Ok(conn),
            None => Err(sqlite::Error {
                code: None,
                message: Some("Uninitialized".into()),
            }
            .into()),
        }
    }

    /// Attempts to get the migration list from the database; if that fails, creates the migration table and returns an empty vec
    fn exec_migration_list(&mut self) -> Result<Vec<SQLiteMigration>> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            "Preparing statement to select from migration_control table."
        );

        let statement = conn.prepare(Self::MIGRATION_CONTROL_SELECT_QUERY);

        if statement.is_err() {
            tracing::event!(
                tracing::Level::TRACE,
                "Executing create table migration_control."
            );
            conn.execute(Self::MIGRATION_CONTROL_CREATE_QUERY)?;
            Ok(vec![])
        } else {
            statement?
                .into_iter()
                .map(SQLiteMigration::try_from)
                .collect()
        }
    }

    /// Executes a migration against the database, and inserts a migration record into the migration_control table.
    fn run_and_insert_migration(&mut self, id: usize, migration: &db::Migration) -> Result<()> {
        let conn = self.get_connection()?;
        let timestamp = Local::now().to_string();
        tracing::event!(
            tracing::Level::INFO,
            id = id,
            directory = migration.directory,
            filename = migration.filename,
            timestamp = timestamp,
            "Performing migration."
        );

        conn.execute(migration.script_contents.clone())?;
        tracing::event!(
            tracing::Level::TRACE,
            migration_id = id,
            "Preparing statement to insert into migration_control table."
        );
        let mut statement = conn.prepare(Self::MIGRATION_CONTROL_INSERT_QUERY)?;
        statement.bind::<&[(_, sqlite::Value)]>(&[
            (":migration_id", i64::try_from(id)?.into()),
            (":migration_filename", (&*migration.filename).into()),
            (":migration_timestamp", (&*timestamp).into()),
        ])?;

        statement.next()?;
        Ok(())
    }

    /// Gets list of available migrations, asserts whether they need to be run, and executes them against the sqlite db.
    fn run_migrations(&mut self) -> Result<()> {
        // - get list of sql files
        let migrations = db::get_migration_scripts()?;

        let run_migrations = self.exec_migration_list()?;
        let mut seed_id = run_migrations.len();
        tracing::event!(
            tracing::Level::DEBUG,
            "Found {} already run migrations.",
            seed_id
        );

        // Against list of migration records, run any scripts absent
        for migration in &migrations {
            let prev_migration = run_migrations
                .iter()
                .find(|m| m.migration_filename == migration.filename);
            if prev_migration.is_none() {
                self.run_and_insert_migration(seed_id, migration)?;

                seed_id += 1;
            } else {
                let actual_migration = prev_migration.unwrap();
                tracing::event!(
                    tracing::Level::DEBUG,
                    migration_id = actual_migration.migration_id,
                    migration_filename = actual_migration.migration_filename,
                    migration_timestamp = actual_migration.migration_timestamp.to_string(),
                    "Migration already occurred."
                )
            }
        }

        Ok(())
    }
}

impl Default for SQLiteStore {
    fn default() -> Self {
        SQLiteStore {
            location: ":memory:".into(),
            connection: Option::None,
        }
    }
}

impl Store for SQLiteStore {
    /// Initialize SQLite database connection and execute any pending migrations
    fn init(&mut self) -> Result<()> {
        let conn_path = self.location.clone();
        let conn = sqlite::open(conn_path)?;
        self.connection = Option::Some(conn);

        self.run_migrations()?;

        Ok(())
    }

    /// Selects accounts from the fiduciary_account table
    fn get_accounts(&self) -> Result<Vec<FiduciaryAccount>> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            "Preparing statement to select from fiduciary_account table."
        );
        let statement = conn.prepare(Self::FIDUCIARY_ACCOUNT_SELECT_QUERY)?;

        statement
            .into_iter()
            .map(FiduciaryAccount::try_from)
            .collect()
    }

    /// Inserts an account into the fiduciary_account table
    fn add_account(&mut self, account: &FiduciaryAccount) -> Result<()> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            fiduciary_account_id = account.fiduciary_account_id,
            "Preparing statement to insert into fiduciary_account table."
        );
        let mut statement = conn.prepare(Self::FIDUCIARY_ACCOUNT_INSERT_QUERY)?;

        statement.bind::<&[(_, sqlite::Value)]>(&[
            (
                ":fiduciary_account_id",
                i64::from(account.fiduciary_account_id).into(),
            ),
            (
                ":fiduciary_account_name",
                account.fiduciary_account_name.clone().into(),
            ),
            (
                ":fiduciary_account_number",
                i64::try_from(account.fiduciary_account_number)?.into(),
            ),
            (
                ":fiduciary_account_description",
                account.fiduciary_account_description.clone().into(),
            ),
            (
                ":fiduciary_account_created_timestamp",
                account
                    .fiduciary_account_created_timestamp
                    .to_string()
                    .into(),
            ),
        ])?;
        Ok(())
    }

    /// Selects symbols from the equity_symbol table
    fn get_symbols(&self) -> Result<Vec<EquitySymbol>> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            "Preparing statement to select from equity_symbol table."
        );
        let statement = conn.prepare(Self::EQUITY_SYMBOL_SELECT_QUERY)?;

        statement.into_iter().map(EquitySymbol::try_from).collect()
    }

    /// Inserts a symbol into the equity_symbol table
    fn add_symbol(&mut self, symbol: &EquitySymbol) -> Result<()> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            equity_symbol_id = symbol.equity_symbol_id,
            "Preparing statement to insert into equity_symbol table."
        );
        let mut statement = conn.prepare(Self::EQUITY_SYMBOL_INSERT_QUERY)?;

        statement.bind::<&[(_, sqlite::Value)]>(&[
            (
                ":equity_symbol_id",
                i64::try_from(symbol.equity_symbol_id)?.into(),
            ),
            (
                ":equity_symbol_name",
                symbol.equity_symbol_name.clone().into(),
            ),
            (
                ":equity_symbol_type",
                symbol.equity_symbol_type.to_string().into(),
            ),
            (
                ":equity_symbol_managing_company",
                symbol.equity_symbol_managing_company.clone().into(),
            ),
            (
                ":equity_symbol_created_timestamp",
                symbol.equity_symbol_created_timestamp.to_string().into(),
            ),
        ])?;
        Ok(())
    }

    /// Selects events from the equity_change_event table
    fn get_events(&self) -> Result<Vec<EquityChangeEvent>> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            "Preparing statement to select from equity_change_event table."
        );
        let statement = conn.prepare(Self::EQUITY_CHANGE_EVENT_SELECT_QUERY)?;

        statement
            .into_iter()
            .map(EquityChangeEvent::try_from)
            .collect()
    }

    /// Inserts an event into the equity_change_event table
    fn add_event(&mut self, event: &EquityChangeEvent) -> Result<()> {
        let conn = self.get_connection()?;

        tracing::event!(
            tracing::Level::TRACE,
            equity_change_event_id = event.equity_change_event_id,
            "Preparing statement to insert into equity_change_event table."
        );
        let mut statement = conn.prepare(Self::EQUITY_SYMBOL_INSERT_QUERY)?;

        statement.bind::<&[(_, sqlite::Value)]>(&[
            (
                ":equity_change_event_id",
                i64::try_from(event.equity_change_event_id)?.into(),
            ),
            (
                ":fiduciary_account_id",
                i64::from(event.fiduciary_account_id).into(),
            ),
            (
                ":equity_symbol_id",
                i64::try_from(event.equity_symbol_id)?.into(),
            ),
            (
                ":equity_change_quantity",
                event.equity_change_quantity.to_f64().into(),
            ),
            (
                ":equity_change_cost_basis",
                event.equity_change_cost_basis.to_f64().into(),
            ),
            (
                ":equity_change_type",
                event.equity_change_type.to_string().into(),
            ),
            (
                ":equity_change_timestamp",
                event.equity_change_timestamp.to_string().into(),
            ),
        ])?;
        Ok(())
    }
}

#[cfg(test)]
mod tests {

    use super::*;

    // Practically, the `Store` trait is what we interface with externally, and
    // provides the more general `Result` interface we want to work with so
    // that's what we test here

    #[test]
    fn default() {
        let context = SQLiteStore::default();

        assert_eq!(context.location, ":memory:");
        assert!(context.connection.is_none());
    }

    #[test]
    fn init() {
        let mut context = SQLiteStore::default();

        let res = context.init();

        assert!(res.is_ok());
        assert!(context.connection.is_some());
    }

    #[test]
    fn exec_migration_list_init() {
        let mut context = SQLiteStore::default();
        let conn_path = context.location.clone();
        let conn = sqlite::open(conn_path).unwrap();
        context.connection = Option::Some(conn);

        let res = context.exec_migration_list();
        assert!(res.is_ok());

        let migration_list = res.unwrap();
        assert!(migration_list.is_empty());
    }

    #[test]
    fn exec_migration_list_post_migration() {
        let mut context = SQLiteStore::default();
        let conn_path = context.location.clone();
        let conn = sqlite::open(conn_path).unwrap();
        context.connection = Option::Some(conn);

        let migrations = context.run_migrations();
        assert!(migrations.is_ok());

        let res = context.exec_migration_list();
        assert!(res.is_ok());

        let migration_list = res.unwrap();
        assert!(!migration_list.is_empty());
    }

    #[test]
    fn run_migrations_all() {
        let mut context = SQLiteStore::default();
        let conn_path = context.location.clone();
        let conn = sqlite::open(conn_path).unwrap();
        context.connection = Option::Some(conn);

        {
            let mig_list_before = context
                .get_connection()
                .unwrap()
                .prepare(SQLiteStore::MIGRATION_CONTROL_SELECT_QUERY);

            // select query fails; migration_control table hasn't been added yet
            assert!(mig_list_before.is_err());
        };

        let res = context.run_migrations();
        assert!(res.is_ok());

        let mig_list_after = context.exec_migration_list();
        assert!(mig_list_after.is_ok());
        let a = mig_list_after.unwrap();

        let file_list = db::get_migration_scripts();
        assert!(file_list.is_ok());

        // assert there are migrations, and the end result of migrations is the same
        let b = file_list.unwrap();
        assert!(!b.is_empty());
        assert_eq!(b.len(), a.len());

        // All migrations are in the file list
        assert!(a.iter().all(|migration| {
            b.iter()
                .any(|file| file.filename == migration.migration_filename)
        }));
        // All files are in the migration list
        assert!(b.iter().all(|file| {
            a.iter()
                .any(|migration| file.filename == migration.migration_filename)
        }));
    }

    #[test]
    fn run_migrations_partial() {
        let mut context = SQLiteStore::default();
        let conn_path = context.location.clone();
        let conn = sqlite::open(conn_path).unwrap();
        context.connection = Option::Some(conn);

        // Init migration table
        let migration_control = context.exec_migration_list();
        assert!(migration_control.is_ok());

        // Run first migration
        let file_list = db::get_migration_scripts();
        assert!(file_list.is_ok());
        let mig_list = file_list.unwrap();
        assert!(mig_list.len() > 1);
        let first_mig = &mig_list[0];
        let res = context.run_and_insert_migration(0_usize, first_mig);
        assert!(res.is_ok());

        // Get list of run migrations; assert first one is in there
        let mig_list_before = context.exec_migration_list();
        assert!(mig_list_before.is_ok());
        let a = mig_list_before.unwrap();
        assert_eq!(a.len(), 1);
        assert_eq!(a[0].migration_id, 0_usize);
        assert_eq!(a[0].migration_filename, first_mig.filename);

        // Run the rest
        let res = context.run_migrations();
        assert!(res.is_ok());

        let mig_list_after = context.exec_migration_list();
        assert!(mig_list_after.is_ok());
        let b = mig_list_after.unwrap();
        assert!(b.len() > a.len());
        assert_eq!(mig_list.len(), b.len() - a.len() + 1);

        // All final migrations are in the file list
        assert!(b.iter().all(|migration| {
            mig_list
                .iter()
                .any(|file| file.filename == migration.migration_filename)
        }));
        // All files are in the final migration list
        assert!(mig_list.iter().all(|file| {
            b.iter()
                .any(|migration| file.filename == migration.migration_filename)
        }));
    }

    #[test]
    fn get_accounts_empty() {
        let mut context = SQLiteStore::default();

        let init = context.init();
        assert!(init.is_ok());

        let res = context.get_accounts();
        assert!(res.is_ok());

        let accounts = res.unwrap();
        assert!(accounts.is_empty());
    }

    #[test]
    fn get_symbols_empty() {
        let mut context = SQLiteStore::default();

        let init = context.init();
        assert!(init.is_ok());

        let res = context.get_symbols();
        assert!(res.is_ok());

        let symbols = res.unwrap();
        assert!(symbols.is_empty());
    }

    #[test]
    fn get_events_empty() {
        let mut context = SQLiteStore::default();

        let init = context.init();
        assert!(init.is_ok());

        let res = context.get_events();
        assert!(res.is_ok());

        let events = res.unwrap();
        assert!(events.is_empty());
    }
}