namespace SqlDynamite.Common

open System
open System.Data

type SqliteMetadataFinder() =
    inherit MetadataFinder()

    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 query = String.Format("SELECT sql FROM sqlite_master WHERE name = '{0}' AND type = '{1}'", name, objtype.ToString().ToLower())
        let columns = this.CreateDataSet(query).Tables.Item(0)
        [columns; null; null]

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let query = String.Format("select sql from sqlite_master where name = '{0}'", name)
        [this.CreateDataSet(query).Tables.Item(0).Rows.Item(0).Item(0).ToString()]

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let query = "select distinct s.name, upper(s.type) from sqlite_master s where s.type in (null"
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (",'view'" :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) (",'trigger'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let objkind = String.Join("",loop types.Length [])
        let cond =
            if caseSensitive then
                ") and s.sql like '%" + searchStr
            else
                ") and lower(s.sql) like '%" + searchStr.ToLower()
        query + objkind + cond + "%'"

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select name, upper(type) from sqlite_master where name like '%" + search
            else
                "select name, upper(type) from sqlite_master where lower(name) like '%" + search.ToLower()
        let cond = "%' and (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().ToLower()
                let str = " or type = '" + ot + "'"
                loop (n - 1) (str :: acc)
            else
                acc

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

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select name, upper(type) from sqlite_master"
        let cond =
            if caseSensitive then
                " where sql like '%" + search
            else
                " where lower(sql) like '%" + search.ToLower()
        query + cond + "%' order by 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 =
        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())
