namespace SqlDynamite.Common

open System
open System.Data

type Db2MetadataFinder() =
    inherit MetadataFinder()

    static let system_schemas = "'SYSIBM'"(*"'SYSCAT','SYSIBM','SYSIBMADM','SYSPUBLIC','SYSSTAT','SYSTOOLS','SYSFUN','SYSPROC','SQLJ'"*)

    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let definition (record:DataRow) =
            let column = "\n\t" + record.Item("column_name").ToString() + " " + record.Item("type_name").ToString() + " "
            let rtn = record.Item("type_name").ToString()
            let typedef =
                match rtn.ToUpper() with
                | "CHARACTER" | "VARCHAR" ->
                    "(" + record.Item("length").ToString().Trim() + ")"
                | "DECIMAL" | "NUMERIC" ->
                    "(" + record.Item("length").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + ")"
                | _ -> ""
            let nullable = record.Item("nullable").ToString().Trim()
            let nulltype =
                if nullable = "Y" then
                    " NULL"
                else
                    " NOT NULL"
            String.Join("",[column;typedef;nulltype;","])
        let result = "CREATE TABLE " + tablename + " ("
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (definition(fields.Rows.Item(n - 1)) :: acc)
            else
                acc
        String.Join("",[result;String.Join("",loop fields.Rows.Count []).TrimEnd(',');")"])

    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 COLNAME as column_name, NULLS nullable, LENGTH as length, SCALE as scale, TYPENAME as type_name  from SYSCAT.COLUMNS where TABSCHEMA = '{0}' and TABNAME = '{1}' order by COLNO;", 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 TEXT from SYSCAT.TRIGGERS where OWNER = '" + schema + "' and TRIGNAME = '"
            elif objtype = ObjectType.VIEW then
                "select TEXT from SYSCAT.VIEWS where OWNER = '" + schema + "' and VIEWNAME = '"
            elif objtype = ObjectType.FUNCTION then
                "select IMPLEMENTATION from SYSCAT.FUNCTIONS where FUNCSCHEMA = '" + schema + "' and FUNCNAME = '"
            elif objtype = ObjectType.PROCEDURE then
                "select IMPLEMENTATION from SYSCAT.PROCEDURES where PROCSCHEMA = '" + schema + "' and PROCNAME = '"
            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 TRIM(PROCSCHEMA) || '.' || PROCNAME as name, 'PROCEDURE' as type from SYSCAT.PROCEDURES where PROCSCHEMA not in (" + system_schemas + ") and IMPLEMENTATION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select TRIM(OWNER) || '.' || TRIGNAME as name, 'TRIGGER' as type from SYSCAT.TRIGGERS where OWNER not in (" + system_schemas + ") and TEXT like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TRIM(OWNER) || '.' || VIEWNAME, 'VIEW' as type from SYSCAT.VIEWS where OWNER not in (" + system_schemas + ") and TEXT like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select TRIM(FUNCSCHEMA) || '.' || FUNCNAME as name, 'FUNCTION' as type from SYSCAT.FUNCTIONS where PROCSCHEMA not in (" + system_schemas + ") and IMPLEMENTATION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select TRIM(TABSCHEMA) || '.' || TABNAME as name, 'TABLE' as type from SYSCAT.COLUMNS where TABSCHEMA not in (" + system_schemas + ") and GENERATED = 'A' and (TEXT like '%{0}%' or COLNAME 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 TRIM(PROCSCHEMA) || '.' || PROCNAME as name, 'PROCEDURE' as type from SYSCAT.PROCEDURES where PROCSCHEMA not in (" + system_schemas + ") and lower(IMPLEMENTATION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select TRIM(OWNER) || '.' || TRIGNAME as name, 'TRIGGER' as type from SYSCAT.TRIGGERS where OWNER not in (" + system_schemas + ") and lower(TEXT) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TRIM(OWNER) || '.' || VIEWNAME, 'VIEW' as type from SYSCAT.VIEWS where OWNER not in (" + system_schemas + ") and lower(TEXT) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select TRIM(FUNCSCHEMA) || '.' || FUNCNAME as name, 'FUNCTION' as type from SYSCAT.FUNCTIONS where FUNCSCHEMA not in (" + system_schemas + ") and lower(IMPLEMENTATION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select TRIM(TABSCHEMA) || '.' || TABNAME as name, 'TABLE' as type from SYSCAT.COLUMNS where TABSCHEMA not in (" + system_schemas + ") and GENERATED = 'A' and (lower(TEXT) like '%{0}%' or lower(COLNAME) 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 TRIM(PROCSCHEMA) || '.' || PROCNAME as name, 'PROCEDURE' as type from SYSCAT.PROCEDURES where PROCSCHEMA not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select TRIM(OWNER) || '.' || TRIGNAME as name, 'TRIGGER' as type from SYSCAT.TRIGGERS where OWNER not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select TRIM(OWNER) || '.' || VIEWNAME as name, 'VIEW' as type from SYSCAT.VIEWS where OWNER not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select TRIM(FUNCSCHEMA) || '.' || FUNCNAME as name, 'FUNCTION' as type from SYSCAT.FUNCTIONS where FUNCSCHEMA not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select TRIM(TABSCHEMA) || '.' || TABNAME as name, 'TABLE' as type from SYSCAT.TABLES where TABSCHEMA not in (" + system_schemas + ") and TYPE != 'V'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select TRIM(TABSCHEMA) || '.' || CONSTNAME as name, 'FOREIGN KEY' as type from SYSCAT.TABCONST where TABSCHEMA not in (" + system_schemas + ") and TYPE = 'F'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select TRIM(TABSCHEMA) || '.' || CONSTNAME as name, 'PRIMARY KEY' as type from SYSCAT.TABCONST where TABSCHEMA not in (" + system_schemas + ") and TYPE = 'P'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select TRIM(TABSCHEMA) || '.' || CONSTNAME as name, 'INDEX' as type from SYSCAT.TABCONST where TABSCHEMA not in (" + system_schemas + ") and TYPE = 'U'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select TRIM(OWNER) || '.' || SEQNAME as name, 'SEQUENCE' as type from SYSCAT.SEQUENCES where OWNER not in (" + system_schemas + ")" + 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 TRIM(t.TABSCHEMA) || '.' || t.TABNAME as name, 'TABLE' from SYSCAT.TABLES as t inner join SYSCAT.COLUMNS as c on t.TABNAME = c.TABNAME"
        let cond =
            if caseSensitive then
                " where t.TABSCHEMA not in (" + system_schemas + ") and COLNAME like '%" + search
            else
                " where t.TABSCHEMA not in (" + system_schemas + ") and lower(COLNAME) 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,increment,minvalue,maxvalue,precision,cycle,cache,order from SYSCAT.SEQUENCES where owner='{0}' and seqname='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let typename = match row.Item("precision").ToString() with
                        | "5" -> "SMALLINT"
                        | "10" -> "INTEGER"
                        | "19" -> "BIGINT"
                        | _ -> "DECIMAL (" + row.Item("precision").ToString() + ")"
        let part1 = "CREATE SEQUENCE " + sequenceName + " AS " + typename + " START WITH " + row.Item("start").ToString() + " INCREMENT BY " + row.Item("increment").ToString()
        let part2 = " MINVALUE " + row.Item("minvalue").ToString() + " MAXVALUE " + row.Item("maxvalue").ToString()
        let part3 = if row.Item("cycle").ToString() = "N" then " NO CYCLE" else " CYCLE"
        let part4 = if row.Item("cache").ToString() = "0" then " NO CACHE" else " CACHE " + row.Item("cache").ToString()
        let part5 = if row.Item("order").ToString() = "N" then " NO ORDER" else " ORDER"
        part1 + part2 + part3 + part4 + part5

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

    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())
