namespace SqlDynamite.Common

open System
open System.Data

type SqlAzureMetadataFinder() =
    inherit MsSqlMetadataFinder()

    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("type_name").ToString() + " "
            let rtn = record.Item("type_name").ToString()
            let part2 =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "nvarchar" ->
                    "(" + record.Item("length").ToString().Trim() + " ) "
                | "decimal" ->
                    "(" + record.Item("precision").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + " ) "
                | _ -> ""
            let nullable = record.Item("nullable").ToString().Trim()
            let nulldef = if nullable = "1" 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 + ")"

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let parts = name.Split('.')
        let tableName = parts.[1]
        let script = String.Format("select column_name, is_nullable nullable, character_maximum_length as length, numeric_precision as precision, numeric_scale as scale, data_type as type_name from information_schema.columns where table_name = '{0}' order by ordinal_position;", tableName)
        let columns = this.CreateDataSet(script.ToString()).Tables.Item(0)
        [columns; null; null]

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select s.name + '.' + o.name as name, o.type from sys.sql_modules c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and definition like '%" + searchStr
            else
                "select s.name + '.' + o.name as name, o.type from sys.sql_modules c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and lower(definition) like '%" + searchStr.ToLower()
        let cond = "%' and o.object_id = c.object_id and o.type in (null"
        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 then
                    loop (n - 1) (",'P','X','PC'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (",'TR','TA'" :: 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) (",'AF','FS','FT','FN','TF','IF'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let cccond = 
            if ccExists then 
                if caseSensitive then
                    " union all select s.name + '.' + o.name as name, 'TABLE' as type from sys.computed_columns c, sys.objects o, sys.schemas s where c.object_id = o.object_id and o.schema_id = s.schema_id and (definition like '%" + searchStr + "%' or c.name like '%" + searchStr + "%')" 
                else
                    " union all select s.name + '.' + o.name as name, 'TABLE' as type from sys.computed_columns c, sys.objects o, sys.schemas s where c.object_id = o.object_id and o.schema_id = s.schema_id and (lower(definition) like '%" + searchStr.ToLower() + "%' or lower(c.name) like '%" + searchStr.ToLower() + "%')" 
            else 
                ""
        query + cond + String.Join("",loop types.Length []) + ")" + cccond + " order by type, name"

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select s.name + '.' + o.name as name, type from sys.objects o, sys.schemas s where o.schema_id = s.schema_id and o.name like '%" + search
            else
                "select s.name + '.' + o.name as name, type from sys.objects o, sys.schemas s where o.schema_id = s.schema_id and lower(o.name) like '%" + search.ToLower()
        let cond = "%' and (type is NULL"
        let indexExists = List.exists(fun x -> x = ObjectType.INDEX) types
        let typeExists = List.exists(fun x -> x = ObjectType.TYPE) types
        let sqExists = List.exists(fun x -> x = ObjectType.SERVICE_QUEUE) 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 = 'X' or type = 'PC'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (" or type = 'TR' or type = 'TA'" :: 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 = 'AF' or type = 'FS' or type = 'FT' or type = 'FN' or type = 'TF' or type = 'IF'" :: 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 = 'FK' or type = 'F'" :: 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)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) (" or type = 'SO'" :: acc)
                elif types.Item(n - 1) = ObjectType.SYNONYM then
                    loop (n - 1) (" or type = 'SN'" :: acc)
                elif types.Item(n - 1) = ObjectType.SERVICE_QUEUE then
                    loop (n - 1) (" or type = 'SQ'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let indices =
            if indexExists then
                if caseSensitive then
                    String.Format(" union all select s.name + '.' + i.name as name, 'INDEX' as type from sys.indexes as i, sys.objects as o, sys.schemas as s where o.object_id = i.object_id and o.schema_id = s.schema_id and o.type <> 'S' and i.name like '%{0}%' and is_primary_key = 0 and not exists (select null from sys.objects where name = i.name and (type = 'FK' or type = 'F'))", search)
                else
                    String.Format(" union all select s.name + '.' + i.name as name, 'INDEX' as type from sys.indexes as i, sys.objects as o, sys.schemas as s where o.object_id = i.object_id and o.schema_id = s.schema_id and o.type <> 'S' and lower(i.name) like '%{0}%' and is_primary_key = 0 and not exists (select null from sys.objects where name = i.name and (type = 'FK' or type = 'F'))", search.ToLower())
            else ""
        let tps =
            if typeExists then
                if caseSensitive then
                    String.Format(" union all select s.name + '.' + t.name as name, 'TYPE' as type from sys.types t, sys.schemas s where t.schema_id = s.schema_id and is_user_defined = 1 and t.name like '%{0}%'", search)
                else
                    String.Format(" union all select s.name + '.' + t.name as name, 'TYPE' as type from sys.types t, sys.schemas s where t.schema_id = s.schema_id and is_user_defined = 1 and lower(t.name) like '%{0}%'", search.ToLower())
            else ""
        let serviceQueues =
            if sqExists then
                if caseSensitive then
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE' as type from sys.services where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_MESSAGE_TYPE' as type from sys.service_message_types where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_CONTRACT' as type from sys.service_contracts where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'ROUTE' as type from sys.routes where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'REMOTE_SERVICE_BINDING' as type from sys.remote_service_bindings where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_BROKER_PRIORITY' as type from sys.conversation_priorities where name like '%{0}%'", search)
                else
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE' as type from sys.services where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_MESSAGE_TYPE' as type from sys.service_message_types where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_CONTRACT' as type from sys.service_contracts where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'ROUTE' as type from sys.routes where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'REMOTE_SERVICE_BINDING' as type from sys.remote_service_bindings where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_BROKER_PRIORITY' as type from sys.conversation_priorities where lower(name) like '%{0}%'", search.ToLower())
            else ""
        query + cond + String.Join("",loop types.Length []) + ")" + indices + tps + serviceQueues + " order by type, name"

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct s.name + '.' + o.name as name, o.type from sys.columns c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and c.object_id = o.object_id and (o.type = 'U' or o.type = 'PK' or o.type = 'F' or o.type = 'FK' or o.type = 'UQ')"
        let cond =
            if caseSensitive then
                " and c.name like '%" + search
            else
                " and lower(c.name) like '%" + search.ToLower()
        query + cond + "%' order by 2, 1"

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let name = parts.[1]
        let part1 = "select fk.name, pobj.name, robj.name, pcol.name, rcol.name from sys.foreign_keys as fk"
        let part2 = "inner join sys.foreign_key_columns as fkc on fkc.constraint_object_id = fk.object_id"
        let part3 = "inner join sysobjects as pobj on pobj.id = fkc.parent_object_id"
        let part4 = "inner join sysobjects as robj on robj.id = fkc.referenced_object_id"
        let part5 = "inner join syscolumns as pcol on pcol.id = pobj.id and pcol.colid = fkc.parent_column_id"
        let part6 = "inner join syscolumns as rcol on rcol.id = robj.id and rcol.colid = fkc.referenced_column_id"
        let query = String.Join(" ", [part1;part2;part3;part4;part5;part6;String.Format("where fk.name = '{0}'", name)])
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let tableName = rows.Item(0).Item(1).ToString().Trim()
        let refTableName = rows.Item(0).Item(2).ToString().Trim()
        let count = rows.Count
        let rec loop (colnum:int) n acc =
            if n > 0 then
                let item = rows.Item(n - 1).Item(colnum).ToString().Trim() + if n < count then "," else ""
                loop colnum (n - 1) (item :: acc)
            else
                acc
        let keyCols = loop 3 count []
        let refCols = loop 4 count []
        "ALTER TABLE " + tableName + " ADD CONSTRAINT " + constraintName + " FOREIGN KEY (" + keyCols.ToString() + ") REFERENCES " + refTableName + " (" + refCols.ToString() + ")"
