namespace SqlDynamite.Common

open System

type InterbaseMetadataFinder() =
    inherit FirebirdMetadataFinder()

    override this.GetSequenceDefinition(sequenceName:string) : string =
        let query = String.Format("select gen_id( {0} , 0) from rdb$database;", sequenceName)
        let value = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0).Item(0).ToString()
        let part1 = "CREATE GENERATOR " + sequenceName.Trim() + ";" + Environment.NewLine
        let part2 = "SET GENERATOR " + sequenceName.Trim() + " TO " + value + ";"
        part1 + part2

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        base.GetNameByTextColumns(searchStr, types |> List.filter (fun x -> x <> ObjectType.PACKAGE), caseSensitive)

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        base.GenerateNameScript(search, types |> List.filter (fun x -> x <> ObjectType.PACKAGE), caseSensitive)

    override this.GetParameters(name:string, objtype:ObjectType) : string list =
        let query =
            if objtype = ObjectType.FUNCTION then
                "select " + this.TypeClause + "from rdb$function_arguments as f
                     where f.rdb$function_name = '" + name + "' order by f.rdb$argument_position"
            elif objtype = ObjectType.PROCEDURE then
                "select p.rdb$parameter_name as name, " + this.TypeClause + ", p.rdb$parameter_type as ptype, f.rdb$field_length as \"length\", f.rdb$field_precision as \"precision\", f.rdb$field_scale as scale from rdb$procedure_parameters as p left join rdb$fields f on p.rdb$field_source = f.rdb$field_name
                     where p.rdb$procedure_name = '" + name + "' order by p.rdb$parameter_number"
            else
                ""
        let lst = this.CreateDataSet(query).Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                let pname = if objtype = ObjectType.PROCEDURE then lst.Rows.Item(n - 1).Item("name").ToString().Trim() else ""
                let ptype =
                    if objtype = ObjectType.PROCEDURE then
                        let pt = lst.Rows.Item(n - 1).Item("ptype") :?> int16
                        if pt = 1s then "RETURNS " else ""
                    else ""
                let ptn = lst.Rows.Item(n - 1).Item("type_name").ToString().Trim()
                let stn = lst.Rows.Item(n - 1).Item("sub_type_name")
                let length = if objtype = ObjectType.PROCEDURE then lst.Rows.Item(n - 1).Item("length").ToString().Trim() else ""
                let precision = if objtype = ObjectType.PROCEDURE then lst.Rows.Item(n - 1).Item("precision").ToString().Trim() else ""
                let scale = if objtype = ObjectType.PROCEDURE then lst.Rows.Item(n - 1).Item("scale").ToString().Trim().Replace("-", "") else ""
        
                let typeName =
                    match stn with
                    | :? DBNull -> ptn
                    | _ -> if stn.ToString().Trim() = "unspecified" then ptn else stn.ToString().Trim()
                
                let typeDef =
                    match typeName.ToLower() with
                    | "char" | "varchar" ->
                        "(" + length + ")"
                    | "decimal" | "numeric" ->
                        "(" + precision + ", " + scale + ")"
                    | _ -> ""

                loop (n - 1) ("\n\t" + ptype + pname + " " + typeName + " " + typeDef :: acc)
            else
                acc

        loop lst.Rows.Count []

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        if objtype = ObjectType.FUNCTION then
            let query = String.Format("select rdb$entrypoint from rdb$functions where rdb$function_name = '{0}';", name)
            let body = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0).Item(0).ToString()
            let pms = this.GetParameters(name, objtype)
            let header = "CREATE OR ALTER " + objtype.ToString() + " " + name + "(" + String.Join("", pms.Tail) + ")\n\tRETURNS " + pms.Head.Trim() + "\n\t"
            [header;body]
        else
            base.GetTextColumns(name, objtype)
