namespace SqlDynamite.Common

open System
open System.Data

type VerticaMetadataFinder() =
    inherit MetadataFinder()

    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("type_name").ToString() + " "
            let rtn = record.Item("type_name").ToString()
            let typedef =
                match rtn.ToUpper() with
                | "CHARACTER" | "VARCHAR" | "LONG VARCHAR" | "BINARY" | "VARBINARY" | "LONG VARBINARY" | "BYTEA" | "RAW" ->
                    "(" + record.Item("length").ToString().Trim() + ")"
                | "DECIMAL" | "NUMERIC" | "NUMBER" | "MONEY" ->
                    "(" + record.Item("precision").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + ")"
                | _ -> ""
            let nullable = record.Item("nullable") :?> bool
            let columnDefault = record.Item("COLUMN_DEFAULT")
            let columnDefaultStr = if columnDefault :? DBNull then "" else " DEFAULT " + record.Item("COLUMN_DEFAULT").ToString().Trim()
            let identity = if record.Item("IS_IDENTITY") :?> bool then " IDENTITY" else ""
            let nulltype =
                if nullable then
                    "NULL"
                else
                    "NOT NULL"
            String.Join("",[column;typedef;nulltype;columnDefaultStr;identity;","])
        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 tblName = parts.[1]
        let script = String.Format("select COLUMN_NAME as column_name, IS_NULLABLE nullable, CHARACTER_MAXIMUM_LENGTH as length, NUMERIC_PRECISION as precision, NUMERIC_SCALE as scale, DATA_TYPE as type_name, COLUMN_DEFAULT, IS_IDENTITY from V_CATALOG.COLUMNS where TABLE_SCHEMA = '{0}' and TABLE_NAME = '{1}' order by ORDINAL_POSITION;", schema, tblName)
        let columns = this.CreateDataSet(script.ToString()).Tables.Item(0)
        [columns; null; null]

    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.VIEW then
                        loop (n - 1) (String.Format("select TABLE_SCHEMA || '.' || TABLE_NAME, 'VIEW' as type from V_CATALOG.VIEWS where VIEW_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || FUNCTION_NAME as name, 'FUNCTION' as type from V_CATALOG.USER_FUNCTIONS where FUNCTION_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    else
                        loop (n - 1) acc
                else
                    if types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TABLE_SCHEMA || '.' || TABLE_NAME, 'VIEW' as type from V_CATALOG.VIEWS where lower(VIEW_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select SCHEMA_NAME || '.' || FUNCTION_NAME as name, 'FUNCTION' as type from V_CATALOG.USER_FUNCTIONS where lower(FUNCTION_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    else
                        loop (n - 1) acc
            else acc
        String.Join("",loop types.Length [])

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let objName = parts.[1]
        let query =
            if objtype = ObjectType.VIEW then
                "select VIEW_DEFINITION from V_CATALOG.VIEWS where TABLE_SCHEMA = '" + schema + "' and TABLE_NAME = '"
            elif objtype = ObjectType.FUNCTION then
                "select FUNCTION_DEFINITION from V_CATALOG.USER_FUNCTIONS where SCHEMA_NAME = '" + schema + "' and FUNCTION_NAME = '"
            elif objtype = ObjectType.PROCEDURE then
                "select PROCEDURE_ARGUMENTS from V_CATALOG.USER_PROCEDURES where SCHEMA_NAME = '" + schema + "' and PROCEDURE_NAME = '"
            else ""
        let lst = this.CreateDataSet(query + objName + "'").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.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 SCHEMA_NAME || '.' || PROCEDURE_NAME as name, 'PROCEDURE' as type from V_CATALOG.USER_PROCEDURES" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || TABLE_NAME as name, 'VIEW' as type from V_CATALOG.VIEWS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select SCHEMA_NAME || '.' || FUNCTION_NAME as name, 'FUNCTION' as type from V_CATALOG.USER_FUNCTIONS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || TABLE_NAME as name, 'TABLE' as type from V_CATALOG.TABLES" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || CONSTRAINT_NAME as name, 'FOREIGN KEY' as type from V_CATALOG.FOREIGN_KEYS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || CONSTRAINT_NAME as name, 'PRIMARY KEY' as type from V_CATALOG.PRIMARY_KEYS" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TYPE then
                    loop (n - 1) ("select TYPE_NAME as name, 'TYPE' as type from V_CATALOG.TYPES" + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select SEQUENCE_SCHEMA || '.' || SEQUENCE_NAME as name, 'SEQUENCE' as type from V_CATALOG.SEQUENCES" + 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 + "%' order by type, name;"
            else
                ") as tbl where lower(name) like '%" + search.ToLower() + "%' order by type, name;"
        startQuery + query + cond

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct t.TABLE_SCHEMA || '.' || t.TABLE_NAME as name, 'TABLE' from V_CATALOG.TABLES as t inner join V_CATALOG.COLUMNS as c on t.TABLE_NAME = c.TABLE_NAME"
        let cond =
            if caseSensitive then
                " where COLUMN_NAME like '%" + search
            else
                " where lower(COLUMN_NAME) like '%" + search.ToLower()
        query + cond + "%' order by name"

    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 =
        let parts = sequenceName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select current_value as start,increment_by as increment,minimum as min_value,maximum as max_value,allow_cycle as cycle,session_cache_count <> 1 as cache,session_cache_count as cache_size from V_CATALOG.SEQUENCES where sequence_schema = '{0}' and sequence_name='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName + " START WITH " + row.Item("start").ToString() + " INCREMENT BY " + row.Item("increment").ToString()
        let part2 = " MAXVALUE " + row.Item("max_value").ToString() + " MINVALUE " + row.Item("min_value").ToString()
        let part3 = if row.Item("cycle") :?> bool = false then " NO CYCLE" else " CYCLE"
        let part4 = if row.Item("cache") :?> bool = false then " NO CACHE" else " CACHE " + row.Item("cache_size").ToString()
        part1 + part2 + part3 + part4

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