namespace SqlDynamite.Common

open System
open System.Data

type UltraliteMetadataFinder() =
    inherit MetadataFinder()

    let typeMappings : (int * string) list = [(1,"smallint");(2,"integer");(3,"numeric");(4,"float");(5,"double");(6,"date");(7,"char");(8,"char");(9,"varchar");(10,"long varchar");
                                              (11,"binary");(12,"long binary");(13,"timestamp");(14,"time");(16,"st_geometry");(19,"tinyint");(20,"bigint");(21,"unsigned int");
                                              (22,"unsigned smallint");(23,"unsigned bigint");(24,"bit");(26,"timestamp with time zone");(27,"decimal");(28,"varbinary");(29,"uniqueidentifier");
                                              (30,"varbit");(31,"long varbit");(32,"xml");(33,"nchar");(34,"nchar");(35,"nvarchar");(36,"long nvarchar");(37,"row");(38,"array")]
    
    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let definition (record:DataRow) =
            let rtn = typeMappings |> List.find (fun item -> fst item = Int32.Parse(record.Item("domain").ToString())) |> snd
            let column = "\n\t" + record.Item("column_name").ToString() + " " + rtn + " "
            let col_len = record.Item("domain_info").ToString().Trim()
            let typedef =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "nvarchar" ->
                    "(" + (if col_len <> "-1" then col_len else "max") + ")"
                | _ -> ""
            let nullable = record.Item("nulls").ToString().Trim()
            let nulltype =
                if nullable = "Y" then
                    " NULL"
                else
                    " NOT NULL"
            let defval = if record.Item("default").ToString() = "" then "" else " DEFAULT " + record.Item("default").ToString()
            String.Join("",[column;typedef;nulltype;defval;","])
        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 columns = this.CreateDataSet(String.Format("select c.column_name, c.domain, c.domain_info, c.nulls, \"default\" from syscolumn as c inner join systable as t on c.table_id = t.object_id where t.table_name = '{0}'", name)).Tables.Item(0)
        [columns; null; null]

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        ""

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        raise (NotImplementedException())

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select table_name name, 'TABLE' from systable where table_name like '%" + search
            else
                "select table_name name, 'TABLE' from systable where lower(table_name) like '%" + search.ToLower()
        let cond = "%'"
        let pkeyExists = List.exists (fun x -> x = ObjectType.PRIMARY_KEY) types
        let fkeyExists = List.exists (fun x -> x = ObjectType.FOREIGN_KEY) types
        let indexExists = List.exists(fun x -> x = ObjectType.INDEX) types
        let indices =
            if pkeyExists || fkeyExists || indexExists then
                let IndexQuery = "select case when index_name = 'primary' then '(PRIMARY) ' + table_name when i.type = 'foreign' then '(FOREIGN) ' + index_name else index_name end as name, case when type = 'foreign' then 'FOREIGN_KEY' when type = 'primary' then 'PRIMARY_KEY' else 'INDEX' end as type from sysindex as i inner join systable as t on i.table_id = t.object_id"
                if caseSensitive then
                    String.Format(" union all {0} where index_name like '%{1}%'", IndexQuery, search)
                else
                    String.Format(" union all {0} where lower(index_name) like '%{1}%'", IndexQuery, search.ToLower())
            else ""
        String.Join("",[query;cond;indices;" order by 2, 1"])

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        ""

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        let foreignKeyName = constraintName.Replace("(FOREIGN) ", "")
        let query = String.Format("select distinct t.table_name, c.column_name, t2.table_name as primary_table_name
        from sysindex as i
        inner join sysixcol as ix on i.object_id = ix.index_id
        inner join syscolumn as c on ix.column_id = c.object_id and c.table_id = i.table_id
        inner join systable as t on i.table_id = t.object_id and c.table_id = t.object_id and ix.table_id = t.object_id
        inner join systable as t2 on i.primary_table_id = t2.object_id
        where i.index_name = '{0}' order by ix.sequence", foreignKeyName)
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let tableName = rows.Item(0).Item("table_name").ToString()
        let primaryTableName = rows.Item(0).Item(2).ToString()

        let rec loop (rows:DataRowCollection) (cname:string) n acc =
            if n > 0 then
                loop rows cname (n - 1) (rows.Item(n - 1).Item(cname).ToString() :: acc)
            else
                acc

        let columns = String.Join(",",loop rows "column_name" rows.Count [])
        String.Join("",["FOREIGN KEY ON ";tableName;" (";columns;") REFERENCES ";primaryTableName;" (";columns;")"])

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        let primaryKeyName = "primary"
        let tableName = constraintName.Replace("(PRIMARY) ", "")
        let query = String.Format("select distinct t.table_name, c.column_name from sysindex as i
        inner join sysixcol as ix on i.object_id = ix.index_id
        inner join syscolumn as c on ix.column_id = c.object_id and c.table_id = i.table_id
        inner join systable as t on i.table_id = t.object_id and c.table_id = t.object_id and ix.table_id = t.object_id
        where index_name = '{0}' and table_name = '{1}' order by ix.sequence", primaryKeyName, tableName)
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let tableName = rows.Item(0).Item("table_name").ToString()
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (rows.Item(n - 1).Item("column_name").ToString() :: acc)
            else
                acc
        String.Join("",["PRIMARY KEY ON ";tableName;" (";String.Join(",",loop rows.Count []);")"])

    override this.GetIndexDefinition(constraintName:string) : string =
        let query = String.Format("select distinct t.table_name, c.column_name, i.type, ix.\"order\" from sysindex as i
        inner join sysixcol as ix on i.object_id = ix.index_id
        inner join syscolumn as c on ix.column_id = c.object_id and c.table_id = i.table_id
        inner join systable as t on i.table_id = t.object_id and c.table_id = t.object_id and ix.table_id = t.object_id
        where index_name = '{0}' order by ix.sequence", constraintName)
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let tableName = rows.Item(0).Item("table_name").ToString()
        let indexType = rows.Item(0).Item("type").ToString().Replace("unique", "unique index")
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (rows.Item(n - 1).Item("column_name").ToString() + (if rows.Item(n - 1).Item("column_name").ToString() = "A" then " ASC" else " DESC") :: acc)
            else
                acc
        String.Join("",["CREATE " + indexType + " ";constraintName;" ON ";tableName;" (";String.Join(",",loop rows.Count []);")"])

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