using System;
using System.Diagnostics;
using System.IO;

namespace RickRoll
{
public class Db
{
    public string DataSource { get; }
    public string Database { get; }
    public string Username { get; }
    public string Password { get; }
    public string ConnectionString { get; private set; }
    public string ExtendedProperties { get; }

    private WrapColumn wrapcol;
    private WrapObject wrapobj;

    public DataProvider Provider { get; set; }
    public DataFileType FileType { get; private set; }
    public DataEngine Engine { get; private set; }
    public WrapColumn WrapCol { get => wrapcol; private set => wrapcol = value; }
    public WrapObject WrapObj { get => wrapobj; private set => wrapobj = value; }

    public Db()
    {
        // only used for public enum access
    }

    // For file databases without password set
    public Db(string filepath)
    {
        FileType = GetDbFileType (filepath);
        Engine = GetDbEngine (FileType);
        Provider = GetDbProvider (FileType);
        DataSource = filepath;
        Database = Path.GetFileName (filepath);
        Username = null;
        Password = null;
        ConnectionString = GetConnectionString (Provider);
        GetWrapCol (Engine);
        GetWrapObj (Engine);
    }

    // For file databases with password set
    public Db(string filepath, string password)
    {
        ... (same as first constructor)
    }

    // For server connections using windows auth
    public Db(string path, string server , string database)
    {
        ... (same as first constructor)
    }

    // For all connection types
    public Db(string path, string server, string database , string username , string password)
    {
        ... (same as first constructor)
    }

    public Db(int filetype_value, string server, string database)
    {
        ... (same as first constructor)
    }

    public Db(int filetype_value, string server, string database, string username , string password)
    {
        ... (same as first constructor)
    }

    /// <summary>
    /// This changes the database connection string to use a different Provider.
    /// </summary>
    /// <param name="p"></param>
    public void ReSetConnectionString(DataProvider p)
    {
        Provider = p;
        ConnectionString = GetConnectionString ( Provider );
    }

    private DataFileType GetDbFileType(string path)
    {
        DataFileType ft;
        string ext;
        if (path.Contains ("."))
        {
            string fpath = path.Replace ("|DataDirectory|" , AppDomain.CurrentDomain.BaseDirectory);
            ext = Path.GetExtension (fpath).Replace ("." , "").ToUpper ( );

            if (Enum.TryParse<DataFileType> (ext , out ft) == true) return ft;
        }
        else
        {
            ft = (DataFileType) Enum.Parse (typeof (DataFileType) , path.ToUpper());            /// throw exception if extention is not in DataFileType enum or is null
        }
        return ft;
    }

    private string GetDataSource(DataEngine e, string path, string server)
    {
        if (DataSourceIsFile (e)) return path;
        return server;
    }

    private DataFileType GetDbFileType(int enumvalue)
    {
        DataFileType ft = (DataFileType) Enum.Parse (typeof (DataFileType) , enumvalue.ToString ( ));        // throws and exception if int isn't in the DataFileType enum.
        return ft;
    }

    public enum DataEngine
    {
        None,
        ACCESS = 1,              // 0 = not applicable or not determined
        MSSQL,
        EXCEL,
        ORACLE,
        MYSQL,
        TEXT
    }

    // <summary>
    /// These are the int enum values that get stored in the database for each type of data file
    /// </summary>
    public enum DataFileType
    {
        None,
        MDB = 1,       // 0 = not a configured data file
        ACCDB,
        MDF,           // Primary Data FIle
        NDF,           // File Group (secondary data files)
        XLS,           // Excel 97-2003 worksheet
        XLSX,          // Excel 2007 workbook
        XLSXM,         // Macro enabled workbook
        XLTM,          // Binary worksheet (BIFF12)
        XLW,           // Excel works space, previously known as workbook
        CSV,           // Comma separated values
        TAB,           // Tab separated values
        TSV,           // Tab separated values
        TXT            // Delimited Text file
    }

    /// <summary>
    /// These are the int values that get stored in the database for each db connection
    /// </summary>
    public enum DataProvider
    {
        None,
        Microsoft_ACE_OLEDB_12_0 = 1,          // Microsoft.ACE.OLEDB.12.0 - MS OLEDB DataProvider for MDB or ACCDB or EXCEL
        Microsoft_ACE_OLEDB,               // Microsoft.ACE.OLEDB VersionIndependentProgID
        Microsoft_Jet_OLEDB_4_0,           // MS Access - Does not work with ACCDB or any SQL Server version
        Microsoft_Jet_OLEDB,               // Version Independent ProgID
        SQLNCLI11,                         // SQL Server Native Client for OleDb
        SQLNCLI,                           // Version Independent ProgID
        SQLOLEDB_1,                        // SQL Server OleDb - Does not work with SQL Server Express
        SQLOLEDB,                          // VersionIndependentProgID
        SQL__Server__Native__Client__11_0, // SQL Server Native Client using ODbC
        SQL__Server__Native__Client,       // Version Independent ProgID
        MSDASQL_1,                         // Microsoft OleDb Data Access Components using ODbC
        MSDASQL                            // Version Independent ProgID
    }

    private DataEngine GetDbEngine(int enumvalue)
    {
        DataEngine result = (DataEngine) Enum.Parse (typeof (DataEngine) , enumvalue.ToString ( ));        // throws and exception if int isn't in the DataEngine enum.
        return result;
    }

    private DataEngine GetDbEngine(DataFileType ft)
    {
        switch (ft)
        {
            case DataFileType.MDB:
                return DataEngine.ACCESS;
            case DataFileType.ACCDB:
                return DataEngine.ACCESS;
            case DataFileType.MDF:
                return DataEngine.MSSQL;
            case DataFileType.NDF:
                return DataEngine.MSSQL;
            case DataFileType.XLS:
                return DataEngine.EXCEL;
            case DataFileType.XLSX:
                return DataEngine.EXCEL;
            case DataFileType.CSV:
                return DataEngine.TEXT;
            case DataFileType.TAB:
                return DataEngine.TEXT;
            case DataFileType.TSV:
                return DataEngine.TEXT;
            case DataFileType.TXT:
                return DataEngine.TEXT;
            default:
                throw new ArgumentException ($"* * * DataFileType is not a supported data file format.  Database DataEngine could not be determined.");
        }
    }

    private DataProvider GetDbProvider(DataFileType ft)
    {
        switch (ft)
        {
            case DataFileType.MDB:
            case DataFileType.ACCDB:
                return DataProvider.Microsoft_ACE_OLEDB_12_0;
            case DataFileType.MDF:
                return DataProvider.SQLNCLI11;                 // SQLOLEDB_1 and SQLOLEDB did not work with SQL Server Express
            case DataFileType.NDF:
                return DataProvider.SQLNCLI11;
            case DataFileType.XLS:
            case DataFileType.XLSX:
            case DataFileType.CSV:
            case DataFileType.TAB:
            case DataFileType.TSV:
            case DataFileType.TXT:
                return DataProvider.Microsoft_ACE_OLEDB_12_0;
            default:
                throw new ArgumentException ($"* * * DataFileType is not a supported data file format.  Database DataProvider could not be determined.");
        }
    }

    private void GetWrapCol(DataEngine e)
    {
        switch (e)
        {
            case DataEngine.ACCESS:
                WrapCol = new WrapColumn  { left = "`" , middle = "`,`" , right = "`" };
                break;
            case DataEngine.MSSQL:
                WrapCol = new WrapColumn  { left = "[" , middle = "],[" , right = "]" };
                break;
            case DataEngine.EXCEL:
                WrapCol = new WrapColumn  { left = "`" , middle = "`,`" , right = "`" };
                break;
            case DataEngine.ORACLE:
                WrapCol = new WrapColumn  { left = @"""" , middle = @""",""" , right = @"""" };
                break;
            case DataEngine.MYSQL:
                WrapCol = new WrapColumn  { left = "`" , middle = "`,`" , right = "`" };        // might be brackets for sysnames and ` for columns
                break;
            case DataEngine.TEXT:
                WrapCol = new WrapColumn  { left = @"""" , middle = @""",""" , right = @"""" };        // not sure how to handle this yet
                break;
            default:
                throw new ArgumentException ($"* * * DataEngine was not valid. Could not determine a COLUMN escape character.");
        }
    }

    private void GetWrapObj(DataEngine e)
    {
        switch (e)
        {
            case DataEngine.ACCESS:
                WrapObj = new WrapObject { left = "`" , middle = "`,`" , right = "`" };
                break;
            case DataEngine.MSSQL:
                WrapObj = new WrapObject { left = "[" , middle = "],[" , right = "]" };
                break;
            case DataEngine.EXCEL:
                WrapObj = new WrapObject { left = "`" , middle = "`,`" , right = "`" };
                break;
            case DataEngine.ORACLE:
                WrapObj = new WrapObject { left = @"""" , middle = @""",""" , right = @"""" };
                break;
            case DataEngine.MYSQL:
                WrapObj = new WrapObject { left = "[" , middle = "],[" , right = "]" };        // might be brackets for sysnames and ` for columns
                break;
            case DataEngine.TEXT:
                WrapObj = new WrapObject { left = @"""" , middle = @""",""" , right = @"""" };        // not sure how to handle this yet
                break;
            default:
                throw new ArgumentException ($"* * * DataEngine was not valid. Could not determine a OBJECT escape character.");
        }
    }

    private bool DataSourceIsFile(DataEngine e)
    {
        switch (e)
        {
            case DataEngine.ACCESS:
                return true;
            case DataEngine.MSSQL:
                return false;
            case DataEngine.EXCEL:
                return true;
            case DataEngine.ORACLE:
                return false;
            case DataEngine.MYSQL:
                return false;
            case DataEngine.TEXT:
                return true;
            default:
                throw new ArgumentException ($"* * * DataEngine was not valid. Could not determine if this is a file or server DataSource.");
        }
    }



    //https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax?view=netframework-4.7.1
    private string GetConnectionString(DataProvider Provider)
    {
        string ProgId = Provider.ToString ( ).Replace ("_" , ".");
        string result = "";
        switch (Provider)
        {
            case DataProvider.Microsoft_ACE_OLEDB_12_0:                                                           // Microsoft MS OLEDB for MDB or ACCDB or EXCEL
            case DataProvider.Microsoft_ACE_OLEDB:                                                                // VersionIndependentProgID
                return $@"Provider={ProgId};Data Source={DataSource};Persist Security Info=False;Jet OLEDB:Database Password={Password.EmptyIfNull ( )};";

            case DataProvider.Microsoft_Jet_OLEDB_4_0:                                                            // MS Access Jet OLEDB - Does not work with ACCDB or any SQL Server version
            case DataProvider.Microsoft_Jet_OLEDB:                                                             // VersionIndependentProgID
                // Jet db with user-lvel security requires a Workgroup information file designation:
                // Jet OLEDB:System Database=|DataDirectory|System.mdw;"   <--- that could be stored in the Server field
                // Jet user-level security uses the User ID and Password setting.  A new constructer that includes the Username field will need to be added to support this
                if (Engine == DataEngine.ACCESS && Password.NullIfEmpty() == null)
                    return $@"Provider={ProgId};Data Source={DataSource};User ID=Admin;Password=;";
                else
                    return $@"Provider={ProgId};Data Source={DataSource};Persist Security Info=False;Jet OLEDB:Database Password={Password.EmptyIfNull ( )};";


            case DataProvider.SQLNCLI11:                                                                          // SQL Server OleDb Native Client
            case DataProvider.SQLNCLI:                                                                            // VersionIndependentProgID
                result = $@"Provider={ProgId};Server={DataSource};Database={Database};";
                if (Password != null) result += $"Uid={Username.EmptyIfNull ( )};Pwd={Password.EmptyIfNull ( )};";
                if (Password == null) result += "Integrated Security=SSPI;";
                return result;

            case DataProvider.SQLOLEDB_1:                                                                         // Microsoft OLE DB DataProvider for SQL Server (I've seen this work for ACCESS also) - DID NOT work for SQL Server 2016 Express, but DID work for SQL Server 2016 Developer Edition
            case DataProvider.SQLOLEDB:                                                                           // VersionIndependentProgID
                result = $@"Provider={ProgId};Data Source={DataSource};Initial Catalog={Database};";
                if (Password != null) result += $"User Id={Username.EmptyIfNull()};Password={Password.EmptyIfNull ( )};";
                if (Password == null) result += "Integrated Security=SSPI;";
                return result;

            case DataProvider.MSDASQL_1:                                                                          // Microsoft Data Access OleDb using ODbC
            case DataProvider.MSDASQL:                                                                            // VersionIndependentProgID
                if (Engine == DataEngine.ACCESS)
                    return $@"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};DbQ={DataSource}";
                if (Engine == DataEngine.EXCEL)
                    return $@"Driver={{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}};DbQ={DataSource}";
                if (Engine == DataEngine.MSSQL)
                    if (string.IsNullOrEmpty (Username))
                        return $@"[DataProvider = MSDASQL;] DRIVER={{SQL Server}}; SERVER={DataSource}; DATABASE={Database};UID=;Integrated Security=SSPI";
                    else
                        return $@"[DataProvider = MSDASQL;] DRIVER={{SQL Server}}; SERVER={DataSource}; DATABASE={Database};UID={Username};PWD={Password}";
                else
                    throw new ArgumentException ($" * * * The MSDASQL Provider has only been set up for MS Access or Excel or MSSQL connections.  Could not create Connection String");


            case DataProvider.SQL__Server__Native__Client__11_0:                                        // SQl Server OleDb using ODbC
                if (Engine == DataEngine.MSSQL && (Username.NullIfEmpty ( ) != null))
                    return $@"Driver={{{ProgId}}};Server={DataSource};Database={Database};Uid={Username};Pwd={Password};";
                else
                    return $@"Driver={{{ProgId}}};Server={DataSource};Database={Database};Trusted_Connection=yes;";

            default:
                throw new ArgumentException ($" * * * DataProvider is not valid.  Could not create Connection String");
        }
    }

    public string Columns(string value)
    {
        string[ ] c = value.Split (',');                                                        // if col names have "," in them, maybe replace it with another char and modify this method to replace it back.
        return $"{WrapCol.left}{string.Join(WrapCol.middle , c)}{WrapCol.right}";
    }



}


public static class StringExt
{
    public static string NullIfEmpty(this string value)
    {
        return string.IsNullOrEmpty (value) ? null : value;
    }

    public static string EmptyIfNull(this string value)
    {
        if (string.IsNullOrEmpty (value))
            return string.Empty;
        else
            return value;
    }

}
}