namespace SqlDynamite.Common

open System
open System.Data
open System.Data.Odbc
open System.Data.OleDb

type MySqlMetadataFinder() =
    inherit MetadataFinder()

    override this.EstablishConnection(connectionString:string, connectionType:ConnectionType, database:string) =
        if connectionType = ConnectionType.ODBC then
            this._connection <- new OdbcConnection(connectionString)
            this._connection.Open()
            if String.IsNullOrWhiteSpace(database) = false then
                let cmd = new OdbcCommand("use `" + database + "`", this._connection :?> OdbcConnection)
                ignore(cmd.ExecuteNonQuery())
        else if connectionType = ConnectionType.OLEDB then
            this._connection <- new OleDbConnection(connectionString)
            this._connection.Open()
            if String.IsNullOrWhiteSpace(database) = false then
                let cmd = new OleDbCommand("use `" + database + "`", this._connection :?> OleDbConnection)
                ignore(cmd.ExecuteNonQuery())
        else
            this._connection <- MetadataFinderHelper.CreateConnection(this, connectionString)
            this._connection.Open()
            if String.IsNullOrWhiteSpace(database) = false then
                MetadataFinderHelper.UseDatabase(this._connection, database)

    override this.GetDatabaseList (scname:string) : string list =
        let schema = if String.IsNullOrWhiteSpace(scname) then this._connection.GetSchema() else this._connection.GetSchema(scname)
        let rec loop n acc =
            if n > 0 then
                let item = schema.Rows.Item(n - 1).Item(1).ToString()
                loop (n - 1) (item :: acc)
            else
                acc
        loop schema.Rows.Count []

    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        if fields.Rows.Count = 1 then fields.Rows.Item(0).Item("Create Table").ToString() else ""

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let script = String.Format("SHOW CREATE {0} {1}", (if objtype = ObjectType.JOB then "EVENT" else objtype.ToString()), name)
        if objtype = ObjectType.TRIGGER then
            [this.CreateDataSet(script).Tables.Item(0).Rows.Item(0).Item("SQL Original Statement").ToString()]
        elif objtype = ObjectType.VIEW then
            [this.CreateDataSet(script).Tables.Item(0).Rows.Item(0).Item("Create View").ToString()]
        elif objtype = ObjectType.FUNCTION then
            [this.CreateDataSet(script).Tables.Item(0).Rows.Item(0).Item("Create Function").ToString()]
        elif objtype = ObjectType.PROCEDURE then
            [this.CreateDataSet(script).Tables.Item(0).Rows.Item(0).Item("Create Procedure").ToString()]
        elif objtype = ObjectType.JOB then
            [this.CreateDataSet(script).Tables.Item(0).Rows.Item(0).Item("Create Event").ToString()]
        else [""]

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let rec loop n (acc:string list) =
            if n > 0 then
                let un = if String.Join("",acc).Length > 0 && n < types.Length then " union all " else ""
                if caseSensitive then
                    if types.Item(n - 1) = ObjectType.PROCEDURE then
                        loop (n - 1) (String.Format("select ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '" + this._connection.Database + "' and ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select TRIGGER_NAME as name, 'TRIGGER' as type from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = '" + this._connection.Database + "' and ACTION_STATEMENT like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TABLE_NAME as name, 'VIEW' as type from INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = '" + this._connection.Database + "' and VIEW_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '" + this._connection.Database + "' and ROUTINE_TYPE = 'FUNCTION' and ROUTINE_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.JOB then
                        loop (n - 1) (String.Format("select EVENT_NAME as name, 'JOB' as type from INFORMATION_SCHEMA.EVENTS where EVENT_SCHEMA = '" + this._connection.Database + "' and EVENT_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select TABLE_NAME as name, 'TABLE' as type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '" + this._connection.Database + "' and GENERATION_EXPRESSION is not null and GENERATION_EXPRESSION != '' and (GENERATION_EXPRESSION like '%{0}%' or COLUMN_NAME like '%{0}%')" + un, searchStr) :: acc)
                    else
                        loop (n - 1) acc
                else
                    if types.Item(n - 1) = ObjectType.PROCEDURE then
                        loop (n - 1) (String.Format("select ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '" + this._connection.Database + "' and ROUTINE_TYPE = 'PROCEDURE' and lower(ROUTINE_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select TRIGGER_NAME as name, 'TRIGGER' as type from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = '" + this._connection.Database + "' and lower(ACTION_STATEMENT) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TABLE_NAME as name, 'VIEW' as type from INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = '" + this._connection.Database + "' and lower(VIEW_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '" + this._connection.Database + "' and ROUTINE_TYPE = 'FUNCTION' and lower(ROUTINE_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.JOB then
                        loop (n - 1) (String.Format("select EVENT_NAME as name, 'JOB' as type from INFORMATION_SCHEMA.EVENTS where EVENT_SCHEMA = '" + this._connection.Database + "' and lower(EVENT_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select TABLE_NAME as name, 'TABLE' as type from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '" + this._connection.Database + "' and GENERATION_EXPRESSION is not null and GENERATION_EXPRESSION != '' and (lower(GENERATION_EXPRESSION) like '%{0}%' or lower(COLUMN_NAME) like '%{0}%')" + un, searchStr.ToLower()) :: acc)
                    else
                        loop (n - 1) acc
            else acc
        String.Join("",loop types.Length [])

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let startQuery = "select name, type from ("
        let rec loop n (acc:string list) =
            if n > 0 then
                let un = if String.Join("",acc).Length <> startQuery.Length && n < types.Length then " union all " else ""
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) ("select ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '" + this._connection.Database + "' and ROUTINE_TYPE = 'PROCEDURE'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select TRIGGER_NAME as name, 'TRIGGER' as type from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = '" + this._connection.Database + "'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select TABLE_NAME as name, 'VIEW' as type from INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = '" + this._connection.Database + "'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '" + this._connection.Database + "' and ROUTINE_TYPE = 'FUNCTION'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select TABLE_NAME as name, TABLE_TYPE as type from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '" + this._connection.Database + "' and TABLE_TYPE = 'BASE TABLE'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select CONSTRAINT_NAME as name, CONSTRAINT_TYPE as type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = '" + this._connection.Database + "' and CONSTRAINT_TYPE = 'FOREIGN KEY'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select CONSTRAINT_NAME as name, CONSTRAINT_TYPE as type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = '" + this._connection.Database + "' and CONSTRAINT_TYPE = 'PRIMARY KEY'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select CONSTRAINT_NAME as name, CONSTRAINT_TYPE as type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_SCHEMA = '" + this._connection.Database + "' and CONSTRAINT_TYPE = 'UNIQUE'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.JOB then
                    loop (n - 1) ("select EVENT_NAME as name, 'JOB' as type from INFORMATION_SCHEMA.EVENTS where EVENT_SCHEMA = '" + this._connection.Database + "'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                    loop (n - 1) ("select null as name, null as type" + un :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let query = String.Join("",loop types.Length [])
        let cond =
            if caseSensitive then
                ") as tbl where name like '%" + search + "%' order by type, name;"
            else
                ") as tbl where lower(name) like '%" + search.ToLower() + "%' order by type, name;"
        startQuery + query + cond

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct t.TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES as t inner join INFORMATION_SCHEMA.COLUMNS as c on t.TABLE_NAME = c.TABLE_NAME"
        let cond =
            if caseSensitive then
                " where t.TABLE_SCHEMA = '" + this._connection.Database + "' and TABLE_TYPE = 'BASE TABLE' and COLUMN_NAME like '%" + search
            else
                " where t.TABLE_SCHEMA = '" + this._connection.Database + "' and TABLE_TYPE = 'BASE TABLE' and lower(COLUMN_NAME) like '%" + search.ToLower()
        query + cond + "%' order by t.TABLE_NAME"

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        if objtype = ObjectType.FOREIGN_KEY || objtype = ObjectType.PRIMARY_KEY || objtype = ObjectType.INDEX then
            [new DataTable(); null; null]
        else
            let script = String.Format("SHOW CREATE {0} {1}", (if objtype = ObjectType.JOB then "EVENT" else objtype.ToString()), name)
            let columns = this.CreateDataSet(script).Tables.Item(0)
            [columns; null; null]

    override this.GetSequenceDefinition(sequenceName:string) : string =
        raise (NotImplementedException())

    override this.GetJobDefinition(jobName:string) : string =
        this.GetDefColumns(jobName, ObjectType.JOB).Item(0).Rows.Item(0).Item("Create Event").ToString()
        
    override this.GetForeignKeyDefinition(synonymName:string) : string =
        raise (NotImplementedException())

    override this.GetPrimaryKeyDefinition(synonymName:string) : string =
        raise (NotImplementedException())

    override this.GetIndexDefinition(synonymName:string) : string =
        raise (NotImplementedException())

    override this.GetSynonymDefinition(synonymName:string) : string =
        raise (NotImplementedException())

    override this.GetTypeDefinition(typeName:string) : string =
        raise (NotImplementedException())

    override this.GetReportDefinition(reportName:string) : string =
        raise (NotImplementedException())

    override this.GetExtendedProcedureDefinition(procName:string) : string =
        raise (NotImplementedException())

    override this.GetExtendedTriggerDefinition(triggerName:string) : string =
        raise (NotImplementedException())
