Imports System.Text

Namespace Database.Base

    Public MustInherit Class CommandHelpers
        Public Overridable Function Search(param As SearchParams) As String
            Dim searchString As New StringBuilder

            '基础查询
            searchString.Append("Select")
            searchString.Append(" ")
            searchString.Append($"{String.Join(",", param.SearchColNames)}")
            searchString.Append(" ")
            searchString.Append("From")
            searchString.Append(" ")
            searchString.Append($"`{param.TableName}`")

            '筛选条件
            If param.SearchCondition IsNot Nothing Then
                If param.SearchCondition.Count > 0 Then
                    searchString.Append(" ")
                    searchString.Append("Where")
                    For i As Integer = 0 To param.SearchCondition.Count - 1
                        If i > 0 Then
                            searchString.Append(" ")
                            searchString.Append(param.SearchCondition(i).LogicPrevious.ToString())
                        End If
                        searchString.Append(param.SearchCondition(i).ToString())
                    Next
                End If
            End If

            '排序与排序方式
            If param.OrderType <> SearchParams.OrderTypeEnum.None Then
                searchString.Append($" Order By {param.OrderColName} {param.OrderType}")
            End If

            '返回结果行数
            If param.Limit > -1 Then
                searchString.Append($" Limit {param.Limit}")
            End If

            searchString.Append(";")
            Return searchString.ToString()
        End Function


        Public Overridable Function SearchAll(tableName As String) As String
            Return $"Select * FROM `{tableName}`;"
        End Function

        Public Overridable Function SearchAll(tableName As String, condition As String) As String
            Return $"Select * FROM `{tableName}` WHERE {condition};"
        End Function

        Public Overridable Function Search(columnName As List(Of String), tableName As String) As String
            Dim colNameString As New StringBuilder
            For i As Integer = 0 To columnName.Count - 1
                If i = 0 Then
                    colNameString.Append($"`{columnName(i)}`")
                Else
                    colNameString.Append($",`{columnName(i)}`")
                End If
            Next

            Return $"Select {colNameString} FROM `{tableName}`;"
        End Function

        Public Overridable Function Search(columnName As String, tableName As String) As String
            Return $"Select {columnName} FROM `{tableName}`;"
        End Function

        Public Overridable Function Search(columnName As String, tableName As String, condition As String) As String
            Return $"Select {columnName} FROM `{tableName}` WHERE {condition};"
        End Function

        Public Overridable Function Search(columnName As String, tableName As String, condition As String, limit As Integer) As String
            Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Limit {limit};"
        End Function

        Public Overridable Function SearchDistinct(columnName As String, tableName As String) As String
            Return $"Select Distinct {columnName} FROM `{tableName}`;"
        End Function

        Public Overridable Function SearchDistinct(columnName As String, tableName As String, condition As String) As String
            Return $"Select Distinct {columnName} FROM `{tableName}` WHERE {condition};"
        End Function

        Public Overridable Function SearchDescOrder(columnName As String, tableName As String, orderCol As String) As String
            Return $"Select {columnName} FROM `{tableName}` Order By {orderCol} Desc;"
        End Function

        Public Overridable Function SearchDescOrder(columnName As String, tableName As String, orderCol As String, limit As Integer) As String
            Return $"Select {columnName} FROM `{tableName}` Order By {orderCol} Desc Limit {limit};"
        End Function

        Public Overridable Function SearchDescOrder(columnName As String, ByVal tableName As String, ByVal condition As String, ByVal orderCol As String) As String
            Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Order By `{orderCol}` Desc;"
        End Function

        Public Overridable Function SearchDescOrder(columnName As String, ByVal tableName As String, ByVal condition As String, ByVal orderCol As String, limit As Integer) As String
            Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Order By `{orderCol}` Desc Limit {limit};"
        End Function

        Public Overridable Function SearchAscOrder(ByVal columnName As String, ByVal tableName As String, ByVal orderCol As String) As String
            Return $"Select {columnName} FROM `{tableName}` Order By {orderCol} Asc;"
        End Function


        Public Overridable Function SearchAscOrder(ByVal columnName As String, ByVal tableName As String, ByVal condition As String, ByVal orderCol As String) As String
            Return $"Select {columnName} FROM `{tableName}` WHERE {condition} Order By {orderCol} Asc;"
        End Function

        Public Overridable Function SearchNullTable(tableName As String) As String
            Return $"Select * FROM `{tableName}` Where Limit 0;"
        End Function


        Public Overridable Function Insert(ByVal tableName As String, ByVal values As String) As String
            Return $"Insert into `{tableName}` Values ( {values} );"
        End Function

        Public Overridable Function Insert(ByVal tableName As String, ByVal colNames As String, ByVal values As String) As String
            Return $"Insert into `{tableName}` ({colNames}) Values ( {values} );"
        End Function

        Public Overridable Function Insert(tableName As String, dicNameValues As Dictionary(Of String, String)) As String
            Dim colNames As New StringBuilder
            Dim values As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If colNames.Length = 0 Then
                    colNames.Append($"`{keyValuePair.Key}`")
                    values.Append($"'{keyValuePair.Value}'")
                Else
                    colNames.Append($",`{keyValuePair.Key}`")
                    values.Append($",'{keyValuePair.Value}'")
                End If
            Next
            Return Insert(tableName, colNames.ToString(), values.ToString())
        End Function

        Public Overridable Function InsertByParameters(tableName As String, dicNameValues As Dictionary(Of String, String)) As String
            Dim colNames As New StringBuilder
            Dim values As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If colNames.Length = 0 Then
                    colNames.Append($"`{keyValuePair.Key}`")
                    values.Append($"{keyValuePair.Value}")
                Else
                    colNames.Append($",`{keyValuePair.Key}`")
                    values.Append($",{keyValuePair.Value}")
                End If
            Next
            Return Insert(tableName, colNames.ToString(), values.ToString())
        End Function


        Public Overridable Function AddCol(ByVal tableName As String, ByVal colName As String, ByVal colType As String, Optional isNull As Boolean = True) As String
            Return $"Alter Table `{tableName}` Add `{colName}` {colType} {IIf(isNull, "Default Null", "Not Null")};"
        End Function

        Public Overridable Function AddCol(ByVal tableName As String, colParam As DatabaseData) As String
            Dim sb As New StringBuilder
            sb.Append($"Alter Table `{tableName}` ")
            sb.Append("Add ")
            sb.Append(colParam.ToAddColString())
            Return sb.ToString()
        End Function


        Public Overridable Function AddCols(tableName As String, colList As List(Of DatabaseData)) As String
            Dim sb As New StringBuilder
            sb.Append($"Alter Table `{tableName}` ")
            sb.Append("Add ")
            sb.Append("( ")

            sb.Append(colList(0).ToAddColString())
            For i As Integer = 1 To colList.Count - 1
                sb.Append($",{colList(i).ToAddColString()}")
            Next

            sb.Append(");")

            Return sb.ToString()
        End Function


        Public Overridable Function Update(ByVal tableName As String, ByVal destStr As String, ByVal condition As String) As String
            Return $"Update `{tableName}` Set {destStr} Where {condition};"
        End Function

        Public Overridable Function Update(ByVal tableName As String, dicNameValues As Dictionary(Of String, String), ByVal condition As String) As String
            Dim destStr As New StringBuilder
            For Each keyValuePair As KeyValuePair(Of String, String) In dicNameValues
                If destStr.Length = 0 Then
                    destStr.Append($"`{keyValuePair.Key}` = '{keyValuePair.Value}'")
                Else
                    destStr.Append($",`{keyValuePair.Key}` = '{keyValuePair.Value}'")
                End If
            Next
            Return Update(tableName, destStr.ToString(), condition)
        End Function

        Public Overridable Function Update(ByVal tableName As String, names() As String, values() As String, condition As String) As String
            Dim destStr As New StringBuilder
            If names.Length <> values.Length Then
                Throw New Exception("DBHelpers_Update:names.Length <> values.Length")
            End If

            For i As Integer = 0 To names.Length - 1
                If i = 0 Then
                    destStr.Append($"{names(i)} = '{values(i)}'")
                Else
                    destStr.Append($",{names(i)} = '{values(i)}'")
                End If
            Next

            Return Update(tableName, destStr.ToString(), condition)
        End Function

        Public Overridable Function DeleteRows(ByVal tableName As String, ByVal condition As String) As String
            Return $"Delete From `{tableName}` Where {condition};"
        End Function

        ''' <summary>
        ''' 清空数据表
        ''' </summary>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DeleteTable(ByVal tableName As String) As String
            Return $"Delete From `{tableName}`;"
        End Function

        Public Overridable Function DropCol(ByVal tableName As String, ByVal colName As String) As String
            Return $"Alter Table `{tableName}` Drop Column `{colName}`;"
        End Function

        ''' <summary>
        ''' 删除数据表
        ''' </summary>
        ''' <param name="tableName">数据表名</param>
        ''' <returns></returns>
        Public Overridable Function DropTable(ByVal tableName As String) As String
            Return $"Drop Table `{tableName}`;"
        End Function

        Public Overridable Function CreateTable(ByVal tableName As String, ByVal createStr As String) As String
            Return $"Create Table `{tableName}` ( {createStr} );"
        End Function

        Public Overridable Function CreateTableWhenNotExists(tableName As String, createStr As String) As String
            Return $"Create Table if not exists `{tableName}` ( {createStr} );"
        End Function


        Public Overridable Function CreateLikeTable(tableName As String, baseTableName As String) As String
            Return $"create table `{tableName}` like `{baseTableName}`;"
        End Function

        Public Overridable Function CreateLikeTableNotExists(tableName As String, baseTableName As String) As String
            Return $"create table if not exists `{tableName}` like `{baseTableName}`;"
        End Function

        ''' <summary>
        ''' 创建表,同时复制基础表数据(不包含原表索引与主键)
        ''' 若想复制表结构加数据,则先复制表结构创建表，再拷贝数据
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="baseTableName">基础表名</param>
        ''' <returns></returns>
        Public Overridable Function CreateCopyTable(tableName As String, baseTableName As String) As String
            Return $"create table `{tableName}` as select * from `{baseTableName}`;"
        End Function

        ''' <summary>
        ''' 不存在表时即创建表,同时复制基础表数据(不包含原表索引与主键)
        ''' 若想复制表结构加数据,则先复制表结构创建表，再拷贝数据
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="baseTableName">基础表名</param>
        ''' <returns></returns>
        Public Overridable Function CreateCopyTableNotExists(tableName As String, baseTableName As String) As String
            Return $"create table if not exists `{tableName}` as select * from `{baseTableName}`;"
        End Function


        ''' <summary>
        ''' 复制基础表数据到新表中
        ''' </summary>
        ''' <param name="tableName">表名</param>
        ''' <param name="baseTableName">基础表名</param>
        ''' <returns></returns>
        Public Overridable Function InsertCopyTable(tableName As String, baseTableName As String) As String
            Return $"insert into `{tableName}` select * from `{baseTableName}`;"
        End Function
    End Class
End Namespace