namespace SqlDynamite.Common

open System
open System.Data

type SybaseAdvantageMetadataFinder() =
    inherit MetadataFinder()

    let typeMappings : (int * string) list = [(1,"Logical");(2,"Numeric");(3,"Date");(4,"Char");(5,"Memo");(6,"Blob");(7,"Blob");
                                              (10,"Double");(11,"Integer");(12,"Short");(13,"Time");(14,"TimeStamp");(15,"AutoInc");
                                              (16,"Raw");(17,"CurDouble");(18,"Money");(20,"CIChar");(21,"RowVersion");(22,"ModTime");
                                              (23,"VarChar");(24,"VarBinary");(26,"NChar");(27,"NVarChar");(28,"NMemo")]
    
    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let result = "CREATE TABLE " + tablename + " ("
        let rowdef (record:DataRow) =
            let rtn = typeMappings |> List.find (fun item -> fst item = Int32.Parse(record.Item("Field_Type").ToString())) |> snd
            let colname = "\n\t" + record.Item("Name").ToString().Trim() + " "
            let col_len = record.Item("Field_Length").ToString().Trim()
            let typedef =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "cichar" | "nvarchar" | "raw" | "varbinary" ->
                    "(" + col_len + ") "
                | "numeric" ->
                    "(" + record.Item("Field_Length").ToString().Trim() + ", " + record.Item("Field_Decimal").ToString().Trim() + ") "
                | "double" ->
                    "(" + record.Item("Field_Decimal").ToString().Trim() + ") "
                | _ -> ""
            colname + rtn + typedef
        let rec rowloop n acc =
            if n > 0 then
                let item = rowdef(fields.Rows.Item(n - 1)) + if n < fields.Rows.Count then "," else ""
                rowloop (n - 1) (item :: acc)
            else
                acc
        result + String.Join("",rowloop fields.Rows.Count []) + "\n)\n"

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let query = String.Format("SELECT Name, Field_Type, Field_Length, Field_Decimal FROM SYSTEM.COLUMNS WHERE Parent = '{0}'", name)
        let columns = this.CreateDataSet(query).Tables.Item(0)
        [columns; null; null]

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let query =
            if objtype = ObjectType.TRIGGER then
                "select Trig_Container from SYSTEM.TRIGGERS where Name = '"
            elif objtype = ObjectType.VIEW then
                "select View_Stmt from SYSTEM.VIEWS where Name = '"
            elif objtype = ObjectType.FUNCTION then
                "select Implementation from SYSTEM.FUNCTIONS where Name = '"
            elif objtype = ObjectType.PROCEDURE then
                "select SQL_Script from SYSTEM.STOREDPROCEDURES where Name = '"
            elif objtype = ObjectType.PACKAGE then
                "select Name from SYSTEM.PACKAGES where Name = '"
            else ""
        let lst = this.CreateDataSet(query + name + "'").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 Name as name, 'PROCEDURE' as type from SYSTEM.STOREDPROCEDURES where SQL_Script like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select Name as name, 'TRIGGER' as type from SYSTEM.TRIGGERS where Trig_Container like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select Name, 'VIEW' as type from SYSTEM.VIEWS where View_Stmt like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select Name as name, 'FUNCTION' as type from SYSTEM.FUNCTIONS where Implementation 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 Name as name, 'PROCEDURE' as type from SYSTEM.STOREDPROCEDURES where lower(SQL_Script) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select Name as name, 'TRIGGER' as type from SYSTEM.TRIGGERS where lower(Trig_Container) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select Name, 'VIEW' as type from SYSTEM.VIEWS where lower(View_Stmt) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select Name as name, 'FUNCTION' as type from SYSTEM.FUNCTIONS where lower(Implementation) 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 Name, 'PROCEDURE' as type from SYSTEM.STOREDPROCEDURES" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select Name, 'TRIGGER' as type from SYSTEM.TRIGGERS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select Name, 'VIEW' as type from SYSTEM.VIEWS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select Name, 'FUNCTION' as type from SYSTEM.FUNCTIONS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select Name, 'TABLE' as type from SYSTEM.TABLES" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select Name, 'FOREIGN KEY' as type from SYSTEM.RELATIONS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select Name, 'INDEX' as type from SYSTEM.INDEXES" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PACKAGE then
                    loop (n - 1) ("select Name, 'PACKAGE' as type from SYSTEM.PACKAGES" + 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 + "%'"
            else
                ") as tbl where lower(Name) like '%" + search.ToLower() + "%'"
        startQuery + query + cond

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct t.Name, 'TABLE' from SYSTEM.TABLES as t inner join SYSTEM.COLUMNS as c on t.Name = c.Parent"
        let cond =
            if caseSensitive then
                " where c.Name like '%" + search
            else
                " where lower(c.Name) like '%" + search.ToLower()
        query + cond + "%'"

    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 =
        raise (NotImplementedException())

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