namespace SqlDynamite.Common

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

type SybaseMetadataFinder() =
    inherit MsSqlMetadataFinder()

    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.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let definition (record:DataRow) =
            let column = "\n\t" + record.Item("name").ToString() + " "
            let rtn = record.Item("type_name").ToString()
            let col_len = record.Item("length").ToString().Trim()
            let typedef =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "nvarchar" ->
                    "(" + (if col_len <> "-1" && col_len <> "0" then col_len else "max") + ") "
                | "decimal" | "numeric" ->
                    "(" + record.Item("prec").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + ") "
                | _ -> ""
            let nullable = record.Item("nullable").ToString().Trim()
            let defaultstr =
                if record.Item("deftext") :? DBNull then
                    ""
                else
                    record.Item("deftext").ToString().Trim()
            let compstr =
                if record.Item("text") :? DBNull then
                    ""
                else
                    record.Item("text").ToString().Trim()
            let nulltype =
                if nullable = "1" then
                    "NULL"
                else
                    "NOT NULL"
            if record.Item("text") :? DBNull then String.Join("",[column;rtn;" ";typedef;defaultstr;" ";nulltype;","]) else String.Join("",[column;" ";compstr;","])
        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 tableName = parts.[1]
        let query = String.Format("select c.name, c.length, c.prec, c.scale, m.text, p.text as deftext, t.name as type_name, convert(smallint, convert(bit, c.status&8)) as nullable
        from syscolumns as c
        inner join sysobjects as o on c.id = o.id
        inner join systypes as t on c.usertype = t.usertype
        inner join sysusers as u on o.uid = t.uid
        left outer join syscomments as m on c.computedcol = m.id
        left outer join syscomments as p on c.cdefault = p.id
        where u.name = '{0}' and o.name = '{1}' and o.type = 'U'
        order by c.colid", schema, tableName)
        let columns = this.CreateDataSet(query).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 = "select text from syscomments c, sysobjects o, sysusers u where o.uid = u.uid and u.name = '" + schema + "' and o.name = '" + objName + "' and o.id = c.id order by colid"
        let lst = this.CreateDataSet(query.ToString()).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 query =
            if caseSensitive then
                "select u.name + '.' + o.name as name, case when o.type = 'F' then 'FUNCTION' else o.type end as type from syscomments c, sysobjects o, sysusers u where o.uid = u.uid and text like '%" + searchStr
            else
                "select u.name + '.' + o.name as name, case when o.type = 'F' then 'FUNCTION' else o.type end as type from syscomments c, sysobjects o, sysusers u where o.uid = u.uid and lower(text) like '%" + searchStr.ToLower()
        let cond = "%' and o.id = c.id and o.type in (null"
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) (",'P','XP'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (",'TR'" :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) (",'V'" :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (",'F', 'SF'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let ccExists = List.exists(fun x -> x = ObjectType.COMPUTED_COLUMN) types
        let part2 =
            if ccExists then
                String.Format(" union all 
                select u.name +'.' + o2.name as name, 'TABLE' 
                from sysusers u, sysobjects o1, sysobjects o2, syscolumns t left outer join syscomments c on t.computedcol = c.id 
                where o2.uid = u.uid and t.id = o2.id and o1.type = 'C' and computedcol is not null and (lower(text) like '%{0}%' or lower(t.name) like '%{0}%')", searchStr)
            else
                ""
        String.Join("",[query;cond;String.Join("",loop types.Length []);")";part2])

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select u.name + '.' + o.name as name, case when o.type = 'F' then 'FUNCTION' else o.type end as type from sysobjects o, sysusers u where o.uid = u.uid and o.name like '%" + search
            else
                "select u.name + '.' + o.name as name, case when o.type = 'F' then 'FUNCTION' else o.type end as type from sysobjects o, sysusers u where o.uid = u.uid and lower(o.name) like '%" + search.ToLower()
        let cond = "%' and (type is NULL"
        let pkeyExists = List.exists (fun x -> x = ObjectType.PRIMARY_KEY) types
        let indexExists = List.exists(fun x -> x = ObjectType.INDEX) types
        let typExists = List.exists(fun x -> x = ObjectType.TYPE) types
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) (" or type = 'P' or type = 'XP'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (" or type = 'TR'" :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) (" or type = 'V'" :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (" or type = 'F' or type = 'SF'" :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) (" or type = 'U'" :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) (" or type = 'RI'" :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) (" or type = 'PK'" :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) (" or type = 'UQ'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let pkeys =
            if pkeyExists then
                if caseSensitive then
                    String.Format(" union all select u.name + '.' + i.name as name, 'PRIMARY_KEY' as type from sysindexes i, sysobjects o, sysusers u where o.uid = u.uid and i.id = o.id and status > 2000 and o.type <> 'S' and i.name like '%{0}%'", search)
                else
                    String.Format(" union all select u.name + '.' + i.name as name, 'PRIMARY_KEY' as type from sysindexes i, sysobjects o, sysusers u where o.uid = u.uid and i.id = o.id and status > 2000 and o.type <> 'S' and lower(i.name) like '%{0}%'", search.ToLower())
            else ""
        let indices =
            if indexExists then
                if caseSensitive then
                    String.Format(" union all select u.name + '.' + i.name as name, 'INDEX' as type from sysindexes i, sysobjects o, sysusers u where o.uid = u.uid and i.id = o.id and status < 2000 and keys1 is not null and o.type <> 'S' and i.name like '%{0}%'", search)
                else
                    String.Format(" union all select u.name + '.' + i.name as name, 'INDEX' as type from sysindexes i, sysobjects o, sysusers u where o.uid = u.uid and i.id = o.id and status < 2000 and keys1 is not null and o.type <> 'S' and lower(i.name) like '%{0}%'", search.ToLower())
            else ""
        let tps =
            if typExists then
                if caseSensitive then
                    String.Format(" union all select u.name + '.' + t.name as name, 'TYPE' as type from systypes t, sysusers u where t.uid = u.uid and t.name like '%{0}%' and accessrule = 0", search)
                else
                    String.Format(" union all select u.name + '.' + t.name as name, 'TYPE' as type from systypes t, sysusers u where t.uid = u.uid and lower(t.name) like '%{0}%' and accessrule = 0", search.ToLower())
            else ""
        let sorting = " order by type, name"
        String.Join("",[query;cond;String.Join("",loop types.Length []);")";pkeys;indices;tps;sorting])

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct u.name +'.' + OBJECT_NAME(c.id) as name, o.type from syscolumns c, sysobjects o, sysusers u where c.id = o.id and o.uid = u.uid and (o.type = 'U' or o.type = 'PK' or o.type = 'RI' or o.type = 'UQ')"
        let cond =  if caseSensitive then " and c.name like '%" + search else " and lower(c.name) like '%" + search.ToLower()
        let sorting = "%' order by o.type, OBJECT_NAME(c.id)"
        String.Join("", [query;cond;sorting])

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let dataSet = this.CreateDataSet(String.Format("select o2.name from sysobjects as o1, sysconstraints as c, sysobjects as o2, sysusers as u where u.uid = o1.uid and o1.id = c.constrid and c.tableid = o2.id and o1.name = '{0}'", name))
        let tableName = dataSet.Tables.Item(0).Rows.Item(0).Item(0).ToString().Trim()
        let constraints = this.CreateDataSet(String.Format("exec sp_helpconstraint '{0}.{1}'", schema, tableName)).Tables.Item(0).Rows
        let definition = constraints.Item(0).Item("definition").ToString().Trim().Split(' ')
        definition.SetValue(name, 0)
        "ALTER TABLE " + schema + "." + tableName + " ADD CONSTRAINT " + String.Join(" ", definition)

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select o.name from sysobjects o, sysusers u where u.uid = o.uid and id in (select id from sysindexes where name = '{0}' and status > 2000 and keys1 is not null)", name)
        let tableName = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0).Item(0).ToString().Trim()
        let constraints = this.CreateDataSet(String.Format("exec sp_helpconstraint '{0}.{1}'", schema, tableName)).Tables.Item(0).Rows
        let definition:String = constraints.Item(0).Item("definition").ToString().Trim()
        let start = definition.IndexOf('(')
        let length = definition.IndexOf(')') - start + 1
        let columns = definition.Substring(start, length)
        "ALTER TABLE " + schema + "." + tableName + " ADD CONSTRAINT " + name + " PRIMARY KEY " + columns

    override this.GetIndexDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select o.name from sysobjects o, sysusers u where u.uid = o.uid and id in (select id from sysindexes where name = '{0}')", name)
        let tableName = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0).Item(0).ToString().Trim()
        let constraints = this.CreateDataSet(String.Format("exec sp_helpindex '{0}.{1}'", schema, tableName)).Tables.Item(0).Rows
        let count = constraints.Count
        let rec loop n =
            if n > 0 then
                let index_name = constraints.Item(n - 1).Item("index_name").ToString().Trim()
                let index_desc = constraints.Item(n - 1).Item("index_description").ToString().Replace(",", "").Trim()
                if index_name = name then
                    "CREATE " + index_desc + " INDEX " + name + " ON " + schema + "." + tableName + " (" + constraints.Item(n - 1).Item("index_keys").ToString().Trim() + ")"
                else
                    loop (n - 1)
            else
                null
        loop count

    override this.GetTypeDefinition(typeName:string) : string =
        let parts = typeName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("SELECT st.name AS col_name,
            CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND st.length <> -1 THEN st.length/2 ELSE st.length END AS int) AS col_len,
            CAST(st.prec AS int) AS col_prec, CAST(st.scale AS int) AS col_scale, st.allownulls AS col_null, st.ident, baset.name AS col_typename
            FROM systypes AS st
            INNER JOIN sysusers AS sst ON sst.uid = st.uid
            LEFT OUTER JOIN systypes AS baset ON baset.type = st.type and baset.name <> st.name
            WHERE st.accessrule = 0 and sst.name = '{0}' and st.name = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let rtn = row.Item("col_typename").ToString()
        let typedef =
            match rtn.ToLower() with
            | "char" | "varchar"| "nchar" | "nvarchar" ->
                "(" + row.Item("col_len").ToString().Trim() + ")"
            | "decimal" | "numeric" ->
                "(" + row.Item("col_prec").ToString().Trim() + ", " + row.Item("col_scale").ToString().Trim() + ")"
            | _ -> ""
        let nulls = if row.Item("ident").ToString() = "1" then "identity" elif row.Item("col_null") :?> bool then "null" else "not null"
        "exec sp_addtype '" + name + "', '" + row.Item("col_typename").ToString() + typedef.Replace("-1", "max") + "', '" + nulls + "'"

    override this.GetExtendedProcedureDefinition(procName:string) : string =
        String.Join("", this.GetTextColumns(procName, ObjectType.PROCEDURE))
