namespace SqlDynamite.Common

open System
open System.Data

type SybaseAnywhereMetadataFinder() =
    inherit SybaseMetadataFinder()

    static let system_schemas = "' '"(*"'sys'"*)
    
    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 column_name, ss_type_name, width, scale, nulls, column_type, \"default\"
        from SYSTABCOL as c, SYSTAB as t, SYS.SYSSQLSERVERTYPE as sst, SYS.SYSTYPEMAP as map, SYSUSER as u
        where u.user_id = t.creator and c.table_id = t.table_id and u.user_name = '{0}' and t.table_name = '{1}' and map.sa_domain_id = c.domain_id
        and(map.sa_user_type = c.user_type or(select count() from SYS.SYSTYPEMAP where sa_user_type = c.user_type) = 0 and(map.sa_user_type is null))
        and sst.ss_user_type = map.ss_user_type and(map.nullable is null or map.nullable = 'N')
        order by c.column_id", schema, tableName)
        let columns = this.CreateDataSet(String.Format(query, name)).Tables.Item(0)
        [columns; null; null]

    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("ss_type_name").ToString() + " "
            let rtn = record.Item("ss_type_name").ToString()
            let col_len = record.Item("width").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("width").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + ") "
                | _ -> ""
            let nullable = record.Item("nulls").ToString().Trim()
            let nulltype =
                if nullable = "Y" then
                    "NULL "
                else
                    "NOT NULL "
            let columnType = record.Item("column_type").ToString().Trim()
            let defaultval = record.Item("default")
            let defaultstr = 
                if defaultval :? DBNull then
                    ""
                else
                    if columnType = "C" then 
                        String.Format("COMPUTE ({0})", defaultval.ToString().Trim())
                    else 
                        String.Format("DEFAULT {0} ", defaultval.ToString().Trim())
            String.Join("",[column;typedef;nulltype;defaultstr;","])
        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.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 u.name = '" + schema + "' and o.name = '" + objName + "' and o.uid = u.uid and o.id = c.id and texttype <> 1 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, o.type from syscomments c, sysobjects o, sysusers u where u.name not in (" + system_schemas + ") and text like '%" + searchStr
            else
                "select u.name + '.' + o.name as name, o.type from syscomments c, sysobjects o, sysusers u where u.name not in (" + system_schemas + ") and lower(text) like '%" + searchStr.ToLower()
        let cond = "%' and o.uid = u.uid and o.id = c.id and o.type in (null"
        let jobExists = List.exists(fun x -> x = ObjectType.JOB) types
        let ccExists = List.exists(fun x -> x = ObjectType.COMPUTED_COLUMN) types
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE || types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (",'P'" :: 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)
                else
                    loop (n - 1) acc
            else
                acc
        let part1 = String.Join("",[query;cond;String.Join("",loop types.Length []);")"])
        let part2 =
            if jobExists then
                let EventQuery = "select u.name + '.' + event_name name, 'JOB' as type from sys.sysevent e inner join sysusers u on u.uid = e.creator"
                if caseSensitive then
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and action like '%{1}%'", EventQuery, searchStr)
                else
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and lower(action) like '%{1}%'", EventQuery, searchStr.ToLower())
            else ""
        let part3 =
            if ccExists then
                let ComputedColumnQuery = "select creator + '.' + tname name, 'TABLE' as type from sys.syscolumns"
                if caseSensitive then
                    String.Format(" union all {0} where creator not in (" + system_schemas + ") and column_kind = 'C' and (default_value like '%{1}%' or cname like '%{1}%')", ComputedColumnQuery, searchStr)
                else
                    String.Format(" union all {0} where creator not in (" + system_schemas + ") and column_kind = 'C' and (lower(default_value) like '%{1}%' or lower(cname) like '%{1}%')", ComputedColumnQuery, searchStr.ToLower())
            else ""
        part1 + part2 + part3

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select u.name + '.' + o.name as name, type from sysobjects o, sysusers u where u.uid = o.uid and u.name not in (" + system_schemas + ") and name like '%" + search
            else
                "select u.name + '.' + o.name as name, type from sysobjects o, sysusers u where u.uid = o.uid and u.name not in (" + system_schemas + ") and lower(name) like '%" + search.ToLower()
        let cond = "%' and (type is NULL"
        let pkeyExists = List.exists (fun x -> x = ObjectType.PRIMARY_KEY) types
        let fkeyExists = List.exists (fun x -> x = ObjectType.FOREIGN_KEY) types
        let indexExists = List.exists(fun x -> x = ObjectType.INDEX) types
        let seqExists = List.exists(fun x -> x = ObjectType.SEQUENCE) types
        let typExists = List.exists(fun x -> x = ObjectType.TYPE) types
        let jobExists = List.exists(fun x -> x = ObjectType.JOB) types
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE || types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (" or type = 'P'" :: 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.TABLE then
                    loop (n - 1) (" or type = 'S' 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 indices =
            if pkeyExists || fkeyExists || indexExists then
                let IndexQuery = "select icreator + '.' + iname as name, case when indextype = 'Foreign Key' then 'FOREIGN_KEY' when indextype = 'Primary Key' then 'PRIMARY_KEY' else 'INDEX' end as type from sys.sysindexes"
                if caseSensitive then
                    String.Format(" union all {0} where icreator not in (" + system_schemas + ") and iname like '%{1}%'", IndexQuery, search)
                else
                    String.Format(" union all {0} where icreator not in (" + system_schemas + ") and lower(iname) like '%{1}%'", IndexQuery, search.ToLower())
            else ""
        let sequences =
            if seqExists then
                let IndexQuery = "select u.name + '.' + sequence_name as name, 'SEQUENCE' as type from syssequence s inner join sysusers u on s.owner = u.uid"
                if caseSensitive then
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and sequence_name like '%{1}%'", IndexQuery, search)
                else
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and lower(sequence_name) like '%{1}%'", IndexQuery, search.ToLower())
            else ""
        let tps =
            if typExists then
                let TypeQuery = "select u.name + '.' + type_name name, 'TYPE' as type from sysusertype t inner join sysusers u on u.uid = t.creator"
                if caseSensitive then
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and type_name like '%{1}%'", TypeQuery, search)
                else
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and lower(type_name) like '%{1}%'", TypeQuery, search.ToLower())
            else ""
        let jobs =
            if jobExists then
                let TypeQuery = "select u.name + '.' + event_name name, 'JOB' as type from sys.sysevent e inner join sysusers u on u.uid = e.creator"
                if caseSensitive then
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and event_name like '%{1}%'", TypeQuery, search)
                else
                    String.Format(" union all {0} where u.name not in (" + system_schemas + ") and lower(event_name) like '%{1}%'", TypeQuery, search.ToLower())
            else ""
        String.Join("",[query;cond;String.Join("",loop types.Length []);")";indices;sequences;tps;jobs;" order by 2, 1"])

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct u.name + '.' + tname as name, 'U' from sys.syscolumns c, sysobjects o, sysusers u"
        let cond =
            if caseSensitive then
                " where o.uid = u.uid and u.name not in (" + system_schemas + ") and o.name = c.tname and o.type <> 'S' and c.cname like '%" + search
            else
                " where o.uid = u.uid and u.name not in (" + system_schemas + ") and o.name = c.tname and o.type <> 'S' and lower(c.cname) like '%" + search.ToLower()
        String.Join("",[query;cond;"%' order by name"])

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select role, foreign_tname, foreign_creator, primary_tname, primary_creator, columns from sys.sysforeignkeys where foreign_creator = '{0}' and role = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let columns = row.Item("columns").ToString().Split([|" IS "|], StringSplitOptions.None)
        let tableName = row.Item("foreign_creator").ToString().Trim() + "." + row.Item("foreign_tname").ToString().Trim()
        let refTableName = row.Item("primary_creator").ToString().Trim() + "." + row.Item("primary_tname").ToString().Trim()
        "ALTER TABLE " + tableName + " ADD CONSTRAINT " + name + " FOREIGN KEY (" + columns.GetValue(0).ToString().Trim() + ") REFERENCES " + refTableName + " (" + columns.GetValue(1).ToString().Trim() + ")"

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select iname, tname, indextype, colnames, creator from sys.sysindexes where icreator = '{0}' and iname = '{1}'", schema, name);
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let columns = row.Item("colnames").ToString().Trim()
        let tableName = row.Item("creator").ToString().Trim() + "." + row.Item("tname").ToString().Trim()
        "ALTER TABLE " + 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 iname, tname, indextype, colnames from sys.sysindexes where icreator = '{0}' and iname = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let columns = row.Item("colnames").ToString().Trim()
        let tableName = row.Item("tname").ToString().Trim()
        let description = row.Item("indextype").ToString().Trim()
        let index = if description.ToUpper().Contains("INDEX") || description.ToUpper().Contains("KEY") then " " else " INDEX "
        String.Join("",["CREATE ";description;index;constraintName;" ON ";tableName;" (";columns;")"])

    override this.GetSequenceDefinition(sequenceName:string) : string =
        let parts = sequenceName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select start_with,increment_by,min_value,max_value,cache,cycle from syssequence s inner join sysusers u on u.uid = s.owner where u.name = '{0}' and sequence_name='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName + " INCREMENT BY " + row.Item("increment_by").ToString() + " START WITH " + row.Item("start_with").ToString()
        let part2 = " MINVALUE " + row.Item("min_value").ToString() + " MAXVALUE " + row.Item("max_value").ToString()
        let part3 = if row.Item("cache").ToString() = "0" then " NO CACHE" else " CACHE " + row.Item("cache").ToString()
        let part4 = if row.Item("cycle").ToString() = "0" then " NO CYCLE" else " CYCLE"
        part1 + part2 + part3 + part4

    override this.GetTypeDefinition(typeName:string) : string =
        let parts = typeName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("SELECT st.type_name, st.base_type_str, st.nulls FROM sysusertype st INNER JOIN sysusers AS u ON u.uid = st.creator WHERE u.name = '{0}' AND st.type_name = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let nulls =
            match row.Item("nulls").ToString() with
            | "Y" -> " NULL"
            | "N" -> " NOT NULL"
            | _ -> " NOT NULL"
        "CREATE DOMAIN " + typeName + " " + row.Item("base_type_str").ToString() + nulls

    override this.GetJobDefinition(jobName:string) : string =
        let parts = jobName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select action from sys.sysevent e inner join sysusers u on u.uid = e.creator where u.name = '{0}' and event_name='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        row.Item("action").ToString()
