namespace SqlDynamite.Common

open System
open System.Data

type IngresMetadataFinder() =
    inherit MetadataFinder()

    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().Trim() + " " + record.Item("type_name").ToString().Trim() + " "
            let rtn = record.Item("type_name").ToString().Trim()
            let part2 =
                match rtn.ToLower() with
                "char" | "varchar"| "nchar" | "nvarchar" | "byte" | "byte varying" ->
                    "(" + record.Item("length").ToString().Trim() + " ) "
                | _ -> ""
            let nullable = record.Item("nullable").ToString().Trim()
            let nulldef = if nullable = "Y" 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 schema = parts.[0]
        let tblName = parts.[1]
        let script = String.Format("select column_name, column_nulls nullable, column_internal_length as length, column_internal_datatype as type_name from iicolumns where table_owner = '{0}' and table_name = '{1}' order by column_sequence", schema, tblName)
        let columns = this.CreateDataSet(script.ToString()).Tables.Item(0)
        [columns; null; null]

    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.TRIGGER then
                "select text_segment from iirules s where rule_owner = '" + schema + "' and rule_name = '"
            elif objtype = ObjectType.VIEW then
                "select text_segment from iiviews s where table_owner = '" + schema + "' and table_name = '"
            elif objtype = ObjectType.PROCEDURE then
                "select text_segment from iiprocedures s where procedure_owner = '" + schema + "' and procedure_name = '"
            else ""
        let lst = this.CreateDataSet(query + objName + "' order by text_sequence").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 trim(procedure_owner) + '.' + procedure_name as name, 'PROCEDURE' as type from iiprocedures where procedure_owner <> '$ingres' and text_segment like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select trim(rule_owner) + '.' + rule_name as name, 'TRIGGER' as type from iirules where rule_owner <> '$ingres' and text_segment like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select trim(table_owner) + '.' + table_name as name, 'VIEW' as type from iiviews where table_owner <> '$ingres' and text_segment like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.JOB then
                        loop (n - 1) (String.Format("select trim(event_owner) + '.' + event_name as name, 'JOB' as type from iievents where event_owner <> '$ingres' and text_segment 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 trim(procedure_owner) + '.' + procedure_name as name, 'PROCEDURE' as type from iiprocedures where procedure_owner <> '$ingres' and lower(text_segment) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select trim(rule_owner) + '.' + rule_name as name, 'TRIGGER' as type from iirules where rule_owner <> '$ingres' and lower(text_segment) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select trim(table_owner) + '.' + table_name as name, 'VIEW' as type from iiviews where table_owner <> '$ingres' and lower(text_segment) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.JOB then
                        loop (n - 1) (String.Format("select trim(event_owner) + '.' + event_name as name, 'JOB' as type from iievents where event_owner <> '$ingres' and lower(text_segment) 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 trim(procedure_owner) + '.' + procedure_name as name, 'PROCEDURE' as type from iiprocedures where procedure_owner <> '$ingres' and text_sequence = 1" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select trim(rule_owner) + '.' + rule_name as name, 'TRIGGER' as type from iirules where rule_owner <> '$ingres' and text_sequence = 1" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select trim(table_owner) + '.' + table_name as name, 'VIEW' as type from iiviews where table_owner <> '$ingres' and text_sequence = 1" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select trim(table_owner) + '.' + table_name as name, 'TABLE' as type from iitables where table_owner <> '$ingres' and table_type in ('T','V')" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select trim(table_owner) + '.' + constraint_name as name, 'FOREIGN_KEY' as type from iiconstraints i, iitables t where t.table_name = i.table_name and table_owner <> '$ingres' and constraint_type = 'R'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select trim(table_owner) + '.' + constraint_name as name, 'PRIMARY_KEY' as type from iiconstraints i, iitables t where t.table_name = i.table_name and table_owner <> '$ingres' and constraint_type = 'P'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select trim(index_owner) + '.' + index_name as name, 'INDEX' as type from iiindex_columns where index_owner <> '$ingres' and key_sequence = 1" + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select trim(seq_owner) + '.' + seq_name as name, 'SEQUENCE' as type from iisequences where seq_owner <> '$ingres'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.SYNONYM then
                    loop (n - 1) ("select trim(synonym_owner) + '.' + synonym_name as name, 'SYNONYM' as type from iisynonyms where synonym_owner <> '$ingres'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.JOB then
                    loop (n - 1) ("select trim(event_owner) + '.' + event_name as name, 'JOB' as type from iievents where event_owner <> '$ingres'" + 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 trim(table_owner) + '.' + table_name as name, 'TABLE' as type from iicolumns where table_owner <> '$ingres'"
        let cond =  if caseSensitive then " and column_name like '%" + search else " and lower(column_name) like '%" + search.ToLower()
        let sorting = "%' order by column_sequence"
        String.Join("", [query;cond;sorting])

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select constraint_name, t.table_name, text_segment from iiconstraints i, iitables t where t.table_name = i.table_name and table_owner = '{0}' and constraint_name = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        "ALTER TABLE " + row.Item("table_name").ToString().Trim() + " ADD " + row.Item("text_segment").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 constraint_name, t.table_name, text_segment from iiconstraints i, iitables t where t.table_name = i.table_name and table_owner = '{0}' and constraint_name = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        "ALTER TABLE " + row.Item("table_name").ToString().Trim() + " ADD " + row.Item("text_segment").ToString().Trim()

    override this.GetIndexDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select c.table_name, i.index_name, i.column_name from iiindex_columns as i, iicolumns as c where c.column_name = i.column_name and c.table_name <> i.index_name and i.index_owner = '{0}' and i.index_name = '{1}' order by i.key_sequence", schema, name)
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let row = rows.Item(0)
        let tableName = row.Item("table_name").ToString().Trim()
        let rec loop n (acc:string list) =
            if n > 0 then
                if rows.Item(n - 1).Item("table_name").ToString().Trim() = tableName then
                    loop (n - 1) (rows.Item(n - 1).Item("column_name").ToString().Trim() :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let columns = String.Join(",",loop rows.Count [])
        "CREATE 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_value as start,increment_value as increment,min_value,max_value,cycle_flag as cycle,cache_flag as cache,cache_size,order_flag as order from iisequences where seq_owner = '{0}' and seq_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").ToString() = "N" then " NOCYCLE" else " CYCLE"
        let part4 = if row.Item("cache").ToString() = "N" then " NOCACHE" else " CACHE " + row.Item("cache_size").ToString()
        let part5 = if row.Item("order").ToString() = "N" then " NOORDER" else " ORDER"
        part1 + part2 + part3 + part4 + part5

    override this.GetSynonymDefinition(synonymName:string) : string =
        let parts = synonymName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select synonym_name, table_name from iisynonyms where synonym_owner = '{0}' and synonym_name='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        "CREATE SYNONYM " + synonymName + " FOR " + row.Item("table_name").ToString()

    override this.GetTypeDefinition(typeName:string) : string =
        raise (NotImplementedException())

    override this.GetJobDefinition(jobName:string) : string =
        let parts = jobName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select text_segment from iievents where event_owner = '{0}' and event_name='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        row.Item("text_segment").ToString()

    override this.GetReportDefinition(reportName:string) : string =
        raise (NotImplementedException())

    override this.GetExtendedProcedureDefinition(procName:string) : string =
        raise (NotImplementedException())

    override this.GetExtendedTriggerDefinition(triggerName:string) : string =
        raise (NotImplementedException())
