namespace SqlDynamite.Common

open System
open System.Data
open System.Collections
open System.IO

type OracleMetadataFinder() =
    inherit MetadataFinder()

    static let system_schemas = "' '"(*"'APEX_040000','APPQOSSYS','AUDSYS','CTXSYS','DBSFWUSER','DBSNMP','DVF','DVSYS','FLOWS_FILES','GSMADMIN_INTERNAL','LBACSYS','MDSYS',
                                 'OJVMSYS','OLAPSYS','OUTLN','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','PUBLIC','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA',
                                 'SYS','SYSTEM','WMSYS','XDB'"*)
    
    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        if fields.Rows.Count = 1 then fields.Rows.Item(0).Item(0).ToString() else ""

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let objName = parts.[1]
        let query = String.Format("select DBMS_METADATA.GET_DDL('{0}','{1}','{2}') from DUAL", (if objtype = ObjectType.JOB then "PROCOBJ" else objtype.ToString()), objName, schema)
        let columns = this.CreateDataSet(query).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 = String.Format("select DBMS_METADATA.GET_DDL('{0}','{1}','{2}') from DUAL", objtype, objName, schema)
        let columns = this.CreateDataSet(query).Tables.Item(0)
        [columns.Rows.Item(0).Item(0).ToString()]

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let query = "select distinct p.OWNER || '.' || s.NAME as NAME, p.OBJECT_TYPE from ALL_OBJECTS p inner join ALL_SOURCE s on s.NAME = p.OBJECT_NAME where p.OBJECT_TYPE in (NULL"
        let jobExists = List.exists(fun x -> x = ObjectType.JOB) types
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) (",'PROCEDURE'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (",'TRIGGER'" :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (",'FUNCTION'" :: acc)
                elif types.Item(n - 1) = ObjectType.PACKAGE then
                    loop (n - 1) (",'PACKAGE'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let objkind = String.Join("",loop types.Length [])
        let cond =
            if caseSensitive then
                ") and p.OWNER not in (" + system_schemas + ") and s.TEXT like '%" + searchStr
            else
                ") and p.OWNER not in (" + system_schemas + ") and lower(s.TEXT) like '%" + searchStr.ToLower()
        let part1 = query + objkind + cond + "%'"
        let part2 =
            if jobExists then
                if caseSensitive then
                    " union all select OWNER || '.' || JOB_NAME name, 'JOB' from ALL_SCHEDULER_JOBS where OWNER not in (" + system_schemas + ") and JOB_ACTION like '%" + searchStr + "%'"
                else
                    " union all select OWNER || '.' || JOB_NAME name, 'JOB' from ALL_SCHEDULER_JOBS where OWNER not in (" + system_schemas + ") and lower(JOB_ACTION) like '%" + searchStr.ToLower() + "%'"
            else
                ""
        part1 + part2 + " order by 2, 1"

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select OWNER || '.' || OBJECT_NAME as OBJECT_NAME, OBJECT_TYPE from ALL_OBJECTS where OWNER not in (" + system_schemas + ") and OBJECT_NAME like '%" + search
            else
                "select OWNER || '.' || OBJECT_NAME as OBJECT_NAME, OBJECT_TYPE from ALL_OBJECTS where OWNER not in (" + system_schemas + ") and lower(OBJECT_NAME) like '%" + search.ToLower()
        let cond = "%' and (OBJECT_TYPE IS NULL"

        let rec loop n acc =
            if n > 0 then
                let objectType = types.Item(n - 1)
                let ot = if objectType = ObjectType.PRIMARY_KEY || objectType = ObjectType.FOREIGN_KEY then "INDEX" else objectType.ToString().ToUpper()
                let str = " or OBJECT_TYPE = '" + ot + "'"
                loop (n - 1) (str :: acc)
            else
                acc

        let objkind = String.Join("",loop types.Length [])
        query + cond + objkind + ") order by OBJECT_TYPE, OBJECT_NAME" 

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select o.OWNER || '.' || c.TABLE_NAME as TABLE_NAME, o.OBJECT_TYPE from ALL_TAB_COLUMNS c inner join ALL_OBJECTS o on o.OBJECT_NAME = c.TABLE_NAME"
        let cond =
            if caseSensitive then
                " where o.OWNER not in (" + system_schemas + ") and COLUMN_NAME like '%" + search
            else
                " where o.OWNER not in (" + system_schemas + ") and lower(COLUMN_NAME) like '%" + search.ToLower()
        query + cond + "%' and o.OBJECT_TYPE in ('TABLE', 'VIEW', 'INDEX') order by c.TABLE_NAME"

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        this.GetDefColumns(constraintName, ObjectType.INDEX).Item(0).Rows.Item(0).Item(0).ToString()

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        this.GetDefColumns(constraintName, ObjectType.INDEX).Item(0).Rows.Item(0).Item(0).ToString()

    override this.GetIndexDefinition(constraintName:string) : string =
        this.GetDefColumns(constraintName, ObjectType.INDEX).Item(0).Rows.Item(0).Item(0).ToString()

    override this.GetSequenceDefinition(sequenceName:string) : string =
        this.GetDefColumns(sequenceName, ObjectType.SEQUENCE).Item(0).Rows.Item(0).Item(0).ToString()

    override this.GetSynonymDefinition(synonymName:string) : string =
        this.GetDefColumns(synonymName, ObjectType.SYNONYM).Item(0).Rows.Item(0).Item(0).ToString()

    override this.GetTypeDefinition(typeName:string) : string =
        this.GetDefColumns(typeName, ObjectType.TYPE).Item(0).Rows.Item(0).Item(0).ToString()

    static member GetDatabases(tnsFile:string) : string list =
        let mutable result: string list = []
        let mutable output = ""
        let mutable service:string = null
        let parens = Stack()
        let sr = new StreamReader(tnsFile)
        let mutable fileLine = sr.ReadLine()
        while fileLine <> null do
            fileLine <- fileLine.Trim()
            if fileLine.Length > 0 && fileLine.Substring(0, 1) <> "#" then
                if fileLine.ToLower().Replace(" ", "").Contains("(service_name=") then
                    let substr = fileLine.Substring(fileLine.ToLower().IndexOf("service_name") + "service_name".Length)
                    service <- substr.Substring(0, substr.IndexOf(')')).Replace("=", "").Trim()
                for lineChar in fileLine do
                    if lineChar = '(' then
                        parens.Push(lineChar)
                    else if lineChar = ')' then
                        let _ = parens.Pop()
                        if parens.Count = 0 && service <> null then
                            let services = output.Split('=')
                            if (services.Length > 1) then
                                result <- services.[services.Length - 2] :: result
                                output <- ""
                                service <- null
                    else if parens.Count = 0 then
                        output <- output + lineChar.ToString()
            fileLine <- sr.ReadLine()
        sr.Close()
        result

    override this.GetJobDefinition(jobName:string) : string =
        this.GetDefColumns(jobName, ObjectType.JOB).Item(0).Rows.Item(0).Item(0).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())
