namespace SqlDynamite.Common

open System
open System.Data

type InformixMetadataFinder() =
    inherit MetadataFinder()

    static let system_schemas = "' '"(*"'informix','sysibm','sysproc','sqlj'"*)

    override this.GetDatabaseList (scname:string) : string list =
        let dbs = this.CreateDataSet("select name from sysmaster:sysdatabases").Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                let startIndex = 
                    if this._connection.ConnectionString.IndexOf("Server=") = -1 then 
                        this._connection.ConnectionString.IndexOf("@") + 1 
                    else 
                        this._connection.ConnectionString.IndexOf("Server=") + 7
                let endIndex = this._connection.ConnectionString.Substring(startIndex).IndexOf(";");
                let srv = this._connection.ConnectionString.Substring(startIndex, endIndex)
                let item = (srv + "/" + dbs.Rows.Item(n - 1).Item(0).ToString()).Trim()
                loop (n - 1) (item :: acc)
            else
                acc
        loop dbs.Rows.Count []

    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 tn = record.Item("type_name") :?> int
            let eid = record.Item("extended_id") :?> int
            let rtn = if tn < 40  || tn > 41 then this.NumberToType(tn) else this.CreateDataSet("select name from sysxtdtypes where extended_id = " + eid.ToString()).Tables.Item(0).Rows.Item(0).Item(0).ToString().Trim()
            let part1 = "\n\t" + record.Item("column_name").ToString() + " " + rtn + " "
            let part2 =
                match rtn.ToLower() with
                | "char" | "nchar" | "varchar" | "nvarchar" | "lvarchar" ->
                    "(" + record.Item("length").ToString().Trim() + ") "
                | _ -> ""
            let nullable = record.Item("nullable") :?> bool
            let nulldef = if nullable then "NULL" else "NOT NULL"
            part1 + part2 + nulldef + ","
        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 + ")"

    member this.NumberToType(coltype) : string =
        match coltype with
        | 0 -> "char"
        | 1 -> "smallint"
        | 2 -> "integer"
        | 3 -> "float"
        | 4 -> "smallfloat"
        | 5 -> "decimal"
        | 6 -> "serial"
        | 7 -> "date"
        | 8 -> "money"
        | 9 -> "null"
        | 10 -> "datetime"
        | 11 -> "byte"
        | 12 -> "text"
        | 13 -> "varchar"
        | 14 -> "interval"
        | 15 -> "nchar"
        | 16 -> "nvarchar"
        | 17 -> "int8"
        | 18 -> "serial8"
        | 19 -> "set"
        | 20 -> "multiset"
        | 21 -> "list"
        | 52 -> "bigint"
        | 53 -> "bigserial"
        | _ -> "unknown"

    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, coltype < 256 as nullable, collength as length, mod(coltype, 256) as type_name, extended_id from syscolumns as a inner join systables as b on a.tabid = b.tabid where owner = '{0}' and tabname = '{1}' order by colno;", schema, tblName)
        let columns = this.CreateDataSet(script).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 data from systriggers as a inner join systrigbody as b on a.trigid = b.trigid where a.owner = '" + schema + "' and a.trigname = '"
            elif objtype = ObjectType.VIEW then
                "select viewtext from systables a, sysviews v where a.tabid = v.tabid and a.owner = '" + schema + "' and a.tabtype='V' and a.tabname = '"
            elif objtype = ObjectType.PROCEDURE then
                "select data from sysprocedures as a inner join sysprocbody as b on a.procid = b.procid where a.owner = '" + schema + "' and a.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(owner) || '.' || procname as name, 'PROCEDURE' as type from sysprocedures as a inner join sysprocbody as b on a.procid = b.procid where owner not in (" + system_schemas + ") and data 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 systriggers as a inner join systrigbody as b on a.trigid = b.trigid where owner not in (" + system_schemas + ") and data like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("SELECT trim(a.owner) || '.' || a.tabname as name, 'VIEW' as type from systables a, sysviews v where a.owner not in (" + system_schemas + ") and a.tabid = v.tabid and a.tabtype = 'V' and viewtext 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(owner) || '.' || procname as name, 'PROCEDURE' as type from sysprocedures as a inner join sysprocbody as b on a.procid = b.procid where owner not in (" + system_schemas + ") and lower(data) 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 systriggers as a inner join systrigbody as b on a.trigid = b.trigid where owner not in (" + system_schemas + ") and lower(data) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("SELECT trim(a.owner) || '.' || a.tabname as name, 'VIEW' as type from systables a, sysviews v WHERE a.owner not in (" + system_schemas + ") and a.tabid = v.tabid and a.tabtype = 'V' and lower(viewtext) 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(owner) || '.' || procname as name, 'PROCEDURE' as type from sysprocedures where owner 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 systriggers where owner not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("SELECT trim(a.owner) || '.' || a.tabname as name, 'VIEW' as type FROM systables a, sysviews v WHERE a.owner not in (" + system_schemas + ") and a.tabtype='V' and a.tabid = v.tabid" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select trim(owner) || '.' || tabname as name, 'TABLE' as type from systables a WHERE owner not in (" + system_schemas + ") and tabtype in ('T', 'E')" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select trim(owner) || '.' || constrname as name, 'FOREIGN_KEY' as type from sysconstraints a where owner not in (" + system_schemas + ") and constrtype = 'R'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select trim(owner) || '.' || constrname as name, 'PRIMARY_KEY' as type from sysconstraints a where owner not in (" + system_schemas + ") and constrtype = 'P'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select trim(owner) || '.' || idxname as name, 'INDEX' as type from sysindexes where owner not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select trim(owner) || '.' || tabname as name, 'SEQUENCE' as type from systables a WHERE owner not in (" + system_schemas + ") and tabtype = 'Q'" + 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 trim(t.owner) || '.' || t.tabname as name, 'TABLE' from systables as t inner join syscolumns as c on t.tabid = c.tabid WHERE t.owner not in (" + system_schemas + ") and t.tabtype in ('T', 'E')"
        let cond =
            if caseSensitive then
                " and colname like '%" + search
            else
                " 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 s.start_val,s.inc_val,s.min_val,s.max_val,s.cycle,s.cache,s.order from syssequences s inner join systables as t on s.tabid = t.tabid where t.owner = '{0}' and t.tabname='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName + " INCREMENT BY " + row.Item("inc_val").ToString() + " WITH START " + row.Item("start_val").ToString()
        let part2 = " MAXVALUE " + row.Item("max_val").ToString() + " MINVALUE " + row.Item("min_val").ToString()
        let part3 = if row.Item("cycle").ToString() = "0" then " NOCYCLE" else " CYCLE"
        let part4 = if row.Item("cache").ToString() = "0" then " NOCACHE" else " CACHE " + row.Item("cache").ToString()
        let part5 = if row.Item("order").ToString() = "0" then " NOORDER" 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())
