namespace SqlDynamite.Common

open System
open System.Data

type SapHanaMetadataFinder() =
    inherit MetadataFinder()

    static let system_schemas = "' '"(*"'HANA_XS_BASE','SAP_REST_API','SAP_XS_LM','SAP_XS_LM_PE','SAP_XS_USAGE','SYS','UIS',
    '_SYS_AFL','_SYS_BI','_SYS_BIC','_SYS_DATA_ANONYMIZATION','_SYS_EPM','_SYS_LDB','_SYS_PLAN_STABILITY','_SYS_REPO',
    '_SYS_RT','_SYS_SECURITY','_SYS_SQL_ANALYZER','_SYS_STATISTICS','_SYS_TASK','_SYS_TELEMETRY','_SYS_XS','_SYS_ADVISOR',
    'SAP_XS_LM_PE_TMP','SAPDBCTRL','_SYS_AUDIT','_SYS_EPM_DATA','_SYS_WORKLOAD_REPLAY','PUBLIC','SYSTEM'"*)
    
    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let result = "CREATE TABLE " + tablename + " ("
        let rowdef index =
            let record = fields.Rows.Item(index)
            let part1 = "\n\t" + record.Item("column_name").ToString() + " " + record.Item("data_type_name").ToString() + " "
            let rtn = record.Item("data_type_name").ToString()
            let part2 =
                match rtn.ToLower() with
                | "char" | "varchar" | "binary" | "varbinary" | "alphanum" | "shorttext" ->
                    "(" + record.Item("length").ToString().Trim() + ") "
                | "decimal" ->
                    "(" + record.Item("length").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + ") "
                | _ -> ""
            let nullable = record.Item("is_nullable").ToString().Trim()
            let nulldef = if nullable = "1" then "NULL" else "NOT NULL"
            let defaultstr =
                if record.Item("default_value") :? DBNull then
                    ""
                else
                    " DEFAULT " + record.Item("default_value").ToString().Trim()
            let genstr =
                if record.Item("generation_type") :? DBNull then
                    ""
                else
                    " GENERATED " + record.Item("generation_type").ToString().Trim() + " " + record.Item("generated_always_as").ToString().Trim()
            part1 + part2 + nulldef + defaultstr + genstr + ","
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (rowdef(n - 1) :: acc)
            else
                acc
        result + String.Join("", loop fields.Rows.Count []) + Environment.NewLine + ")"

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let tblName = parts.[1]
        let script = String.Format("select column_name, is_nullable, length, scale, data_type_name, default_value, generation_type, generated_always_as from sys.table_columns where schema_name = '{0}' and table_name = '{1}' order by position", schema, tblName)
        let columns = this.CreateDataSet(script.ToString()).Tables.Item(0)
        [columns; null; null]

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let objName = parts.[1]
        let query =
            if objtype = ObjectType.TRIGGER then
                "select s.DEFINITION from SYS.TRIGGERS s where s.SCHEMA_NAME = '" + schema + "' and s.TRIGGER_NAME = '"
            elif objtype = ObjectType.VIEW then
                "select s.DEFINITION from SYS.VIEWS s where s.SCHEMA_NAME = '" + schema + "' and s.VIEW_NAME = '"
            elif objtype = ObjectType.FUNCTION then
                "select s.DEFINITION from SYS.FUNCTIONS s where s.SCHEMA_NAME = '" + schema + "' and s.FUNCTION_NAME = '"
            elif objtype = ObjectType.PROCEDURE then
                "select s.DEFINITION from SYS.PROCEDURES s where s.SCHEMA_NAME = '" + schema + "' and s.PROCEDURE_NAME = '"
            else ""
        let lst = this.CreateDataSet(query + objName + "'").Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (lst.Rows.Item(n - 1).Item(0).ToString() :: acc)
            else
                acc
        loop lst.Rows.Count []

    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 SCHEMA_NAME || '.' || PROCEDURE_NAME as name, 'PROCEDURE' as type from SYS.PROCEDURES where SCHEMA_NAME not in (" + system_schemas + ") and DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || FUNCTION_NAME as name, 'FUNCTION' as type from SYS.FUNCTIONS where SCHEMA_NAME not in (" + system_schemas + ") and DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || TRIGGER_NAME as name, 'TRIGGER' as type from SYS.TRIGGERS where SCHEMA_NAME not in (" + system_schemas + ") and DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || VIEW_NAME as name, 'VIEW' as type from SYS.VIEWS where SCHEMA_NAME not in (" + system_schemas + ") and DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || TABLE_NAME as name, 'TABLE' as type from SYS.TABLE_COLUMNS where SCHEMA_NAME not in (" + system_schemas + ") and GENERATED_ALWAYS_AS 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 SCHEMA_NAME || '.' || PROCEDURE_NAME as name, 'PROCEDURE' as type from SYS.PROCEDURES where SCHEMA_NAME not in (" + system_schemas + ") and lower(DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || FUNCTION_NAME as name, 'FUNCTION' as type from SYS.FUNCTIONS where SCHEMA_NAME not in (" + system_schemas + ") and lower(DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || TRIGGER_NAME as name, 'TRIGGER' as type from SYS.TRIGGERS where SCHEMA_NAME not in (" + system_schemas + ") and lower(DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || VIEW_NAME as name, 'VIEW' as type from SYS.VIEWS where SCHEMA_NAME not in (" + system_schemas + ") and lower(DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || TABLE_NAME as name, 'TABLE' as type from SYS.TABLE_COLUMNS where SCHEMA_NAME not in (" + system_schemas + ") and lower(GENERATED_ALWAYS_AS) like '%{0}%'" + un, searchStr) :: 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 query =
            if caseSensitive then
                "select SCHEMA_NAME || '.' || OBJECT_NAME as OBJECT_NAME, OBJECT_TYPE from SYS.OBJECTS where SCHEMA_NAME not in (" + system_schemas + ") and OBJECT_NAME like '%" + search
            else
                "select SCHEMA_NAME || '.' || OBJECT_NAME as OBJECT_NAME, OBJECT_TYPE from SYS.OBJECTS where SCHEMA_NAME not in (" + system_schemas + ") and lower(OBJECT_NAME) like '%" + search.ToLower()
        let cond = "%' and (OBJECT_TYPE IS NULL"

        let rec loop n acc =
            if n > 0 then
                let objectType = types.Item(n - 1).ToString().ToUpper()
                let str = " or OBJECT_TYPE = '" + objectType + "'"
                loop (n - 1) (str :: acc)
            else
                acc

        let objkind = String.Join("",loop types.Length [])

        let pstr = if List.exists(fun x -> x = ObjectType.PRIMARY_KEY) types then " union all select SCHEMA_NAME || '.' || CONSTRAINT_NAME as OBJECT_NAME, 'PRIMARY_KEY' from SYS.CONSTRAINTS where SCHEMA_NAME not in (" + system_schemas + ") and CONSTRAINT_NAME like '%" + search + "%'" else ""
        let fstr = if List.exists(fun x -> x = ObjectType.FOREIGN_KEY) types then " union all select SCHEMA_NAME || '.' || CONSTRAINT_NAME as OBJECT_NAME, 'FOREIGN_KEY' from SYS.REFERENTIAL_CONSTRAINTS where SCHEMA_NAME not in (" + system_schemas + ") and CONSTRAINT_NAME like '%" + search + "%'" else ""
        query + cond + objkind + ")" + pstr + fstr + " order by OBJECT_TYPE, OBJECT_NAME" 

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct t.SCHEMA_NAME || '.' || t.TABLE_NAME as name, 'TABLE' from SYS.TABLES as t inner join SYS.TABLE_COLUMNS as c on t.TABLE_OID = c.TABLE_OID"
        let cond =
            if caseSensitive then
                " where t.SCHEMA_NAME not in (" + system_schemas + ") and COLUMN_NAME like '%" + search
            else
                " where t.SCHEMA_NAME not in (" + system_schemas + ") and lower(COLUMN_NAME) like '%" + search.ToLower()
        query + cond + "%' order by name"

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        raise (NotImplementedException())

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

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

    override this.GetSequenceDefinition(sequenceName:string) : string =
        let parts = sequenceName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select start_number,increment_by,min_value,max_value,is_cycled,cache_size from SYS.SEQUENCES where SCHEMA_NAME = '{0}' and SEQUENCE_NAME='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName + " START WITH " + row.Item("start_number").ToString() + " INCREMENT BY " + row.Item("increment_by").ToString()
        let part2 = " MINVALUE " + row.Item("min_value").ToString() + " MAXVALUE " + row.Item("max_value").ToString()
        let part3 = if row.Item("is_cycled").ToString() = "YES" then " CYCLE" else " NO CYCLE"
        let part4 = if row.Item("cache_size").ToString() = "1" then " NO CACHE" else " CACHE " + row.Item("cache_size").ToString()
        part1 + part2 + part3 + part4

    override this.GetSynonymDefinition(synonymName:string) : string =
        let parts = synonymName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select synonym_name, object_schema || '.' || object_name as name from sys.synonyms s where SCHEMA_NAME = '" + schema + "' and SYNONYM_NAME='{0}'", name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        "CREATE SYNONYM " + synonymName + " FOR " + row.Item("name").ToString()

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

    override this.GetJobDefinition(jobName: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())
