using System;
using System.Data;
using System.Linq;
using ICSharpCode.Data.Core.DatabaseObjects;
using ICSharpCode.Data.Core.Interfaces;
using MySql.Data.MySqlClient;

namespace ICSharpCode.Data.Core.DatabaseDrivers.MySQL
{
    public class MySQLDatabaseDriver : DatabaseDriver<MySQLDatasource>
    {
        #region Consts

        private const string _getTables = @"SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='{0}' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME";

        private const string _getColumnsScript = @"SELECT
                 ORDINAL_POSITION
                ,COLUMN_NAME
                ,DATA_TYPE
                ,CHARACTER_MAXIMUM_LENGTH
                ,COLUMN_TYPE
                ,CASE WHEN DATA_TYPE IN ('nchar', 'nvarchar') AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) THEN CHARACTER_MAXIMUM_LENGTH/2 ELSE 0 END AS LENGTH
                ,CASE WHEN NUMERIC_PRECISION IS NULL THEN 0 ELSE NUMERIC_PRECISION END AS NUMERIC_PRECISION
                ,COLUMN_DEFAULT
                ,CASE WHEN EXTRA='auto_increment' THEN 1 ELSE 0 END AS IS_IDENTITY
                ,CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 0 END AS IS_NULLABLE
                ,CASE WHEN NUMERIC_SCALE IS NULL THEN 0 ELSE NUMERIC_SCALE END AS NUMERIC_SCALE
                ,CASE WHEN EXISTS(SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
                                  WHERE K.CONSTRAINT_NAME='PRIMARY'
                                    AND K.TABLE_SCHEMA=C.TABLE_SCHEMA
                                    AND K.TABLE_NAME=C.TABLE_NAME
                                    AND K.COLUMN_NAME=C.COLUMN_NAME)
                      THEN 1 ELSE 0 END IS_PRIMARYKEY
            FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'
            ORDER BY ORDINAL_POSITION";

        private const string _getConstraintsScript = 
           @"SELECT C.TABLE_NAME AS FKTable
                   ,K.COLUMN_NAME AS FKColumn
                   ,K.REFERENCED_TABLE_NAME AS PKTable
                   ,K.REFERENCED_COLUMN_NAME AS PKColumn
                   ,C.CONSTRAINT_NAME
            FROM        INFORMATION_SCHEMA.TABLE_CONSTRAINTS    C
            INNER JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE     K
                ON C.CONSTRAINT_NAME=K.CONSTRAINT_NAME
            WHERE C.CONSTRAINT_TYPE='FOREIGN KEY'
            ORDER BY 1,2,3,4";

        private const string _getViews = @"SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='{0}' ORDER BY TABLE_NAME";
        private const string _getViewDefiningQuery = @"SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='{0}'";
        private const string _getProcedures = "SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_BODY FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'";
        private const string _getProcedureParameters = @"#没有发现与存储过程参数相关的元数据";

        #endregion
        
		public MySQLDatabaseDriver()
		{
			Datasources = new DatabaseObjectsCollection<MySQLDatasource>(null);
		}
		
        public override string Name
        {
            get { return "MySQL"; }
        }

        public override string ProviderName
        {
            get
            {
                return "MySql.Data.MySqlClient";
            }
        }

        public override string ODBCProviderName
        {
            get
            {
                return string.Empty;//需要安装mysql-connector-net-6.2.5.msi后支持MySQL的ODBC访问
            }
        }

        public override void PopulateDatasources()
        {
            //没发现MySql.Data.dll包含相关信息
        }


        bool IsVersionSupported( Version version )
        {
            if ( version == null )
                return false;
            else if ( version.Major >= 5 && version.Major <= 6 )
            {
                return true;
            }

            return false;
        }

        public override void PopulateDatabases( IDatasource datasource )
        {
            DatabaseObjectsCollection<IDatabase> databases = new DatabaseObjectsCollection<IDatabase>( datasource );

            MySqlConnection sqlConnection = null;
            sqlConnection = new MySqlConnection();
            sqlConnection.ConnectionString = datasource.ConnectionString;
            try
            {
                sqlConnection.Open();
            }
            catch ( MySqlException ex )
            {
                throw ex;
            }

            Version version = new Version( sqlConnection.ServerVersion.Split('-')[0] );
            if ( !IsVersionSupported( version ) )
                throw new NotSupportedException( string.Format( "Version '{0}' is not supported!" , version == null ? "unknown" : version.ToString() ) );
            datasource.ProviderManifestToken = version.ToString();

            string sql = "use INFORMATION_SCHEMA;SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY SCHEMA_NAME";

            MySqlCommand sqlCommand = new MySqlCommand( sql , sqlConnection );
            sqlCommand.CommandTimeout = 20;

            MySqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

            while ( sqlDataReader.Read() )
            {
                databases.Add( new Database( datasource )
                {
                    Name = sqlDataReader[ "SCHEMA_NAME" ].ToString()
                } );
            }

            sqlDataReader.Close();

            datasource.Databases = databases;

            if ( sqlConnection != null && sqlConnection.State == ConnectionState.Open )
                sqlConnection.Close();
        }

        private void LoadColumns( MySqlConnection sqlConnection , ITable table , IDatabase database )
        {
            using ( MySqlDataAdapter dataAdapter =
                new MySqlDataAdapter( string.Format( _getColumnsScript , database.Name , table.TableName ) , sqlConnection ) )
            {
                DataTable dtColumns = new DataTable( "Columns" );
                dataAdapter.Fill( dtColumns );

                for ( int j = 0 ; j < dtColumns.Rows.Count ; j++ )
                {
                    Column column = new Column( table );

                    column.ColumnId = Convert.ToInt32( dtColumns.Rows[ j ][ "ORDINAL_POSITION" ] );
                    column.Name = dtColumns.Rows[ j ][ "COLUMN_NAME" ] as string;
                    column.DataType = dtColumns.Rows[ j ][ "DATA_TYPE" ] as string;
                    column.SystemType = dtColumns.Rows[ j ][ "DATA_TYPE" ] as string;
                    column.Length = Convert.ToInt32( dtColumns.Rows[ j ][ "LENGTH" ] );
                    column.Precision = Convert.ToInt32( dtColumns.Rows[ j ][ "NUMERIC_PRECISION" ] );
                    column.Scale = Convert.ToInt32( dtColumns.Rows[ j ][ "NUMERIC_SCALE" ] );
                    column.IsIdentity = Convert.ToBoolean( dtColumns.Rows[ j ][ "IS_IDENTITY" ] );
                    column.IsNullable = Convert.ToBoolean( dtColumns.Rows[ j ][ "IS_NULLABLE" ] );
                    column.IsPrimaryKey = Convert.ToBoolean( dtColumns.Rows[ j ][ "IS_PRIMARYKEY" ] );

                    table.Items.Add( column );
                }
            }
        }

        public override DatabaseObjectsCollection<ITable> LoadTables( IDatabase database )
        {
            DatabaseObjectsCollection<ITable> tables = new DatabaseObjectsCollection<ITable>( database );

            MySqlConnection sqlConnection = new MySqlConnection( database.ConnectionString );

            using ( MySqlDataAdapter da = new MySqlDataAdapter( _getConstraintsScript , sqlConnection ) )
            {
                DataTable dtConstraints = new DataTable( "Constraints" );
                da.Fill( dtConstraints );

                for ( int i = 0 ; i < dtConstraints.Rows.Count ; i++ )
                {
                    string constraintName = (string)dtConstraints.Rows[ i ][ "CONSTRAINT_NAME" ];

                    if ( database.Constraints.Count > 0 )
                    {
                        IConstraint constraint = database.Constraints.FirstOrDefault( c => c.Name == constraintName );

                        if ( constraint == null )
                        {
                            constraint = new ICSharpCode.Data.Core.DatabaseObjects.Constraint();
                            constraint.Name = constraintName;
                            constraint.FKTableName = (string)dtConstraints.Rows[ i ][ "FKTable" ];
                            constraint.PKTableName = (string)dtConstraints.Rows[ i ][ "PKTable" ];

                            database.Constraints.Add( constraint );
                        }

                        constraint.FKColumnNames.Add( (string)dtConstraints.Rows[ i ][ "FKColumn" ] );
                        constraint.PKColumnNames.Add( (string)dtConstraints.Rows[ i ][ "PKColumn" ] );
                    }
                }
            }

            using ( MySqlDataAdapter da = new MySqlDataAdapter( string.Format( _getTables , database.Name ) , sqlConnection ) )
            {
                DataTable dtTables = new DataTable( "Tables" );
                da.Fill( dtTables );

                for ( int i = 0 ; i < dtTables.Rows.Count ; i++ )
                {
                    string tableName = (string)dtTables.Rows[ i ][ "TABLE_NAME" ];

                    Table table = new Table()
                    {
                        TableName = tableName
                    };
                    LoadColumns( sqlConnection , table , database );

                    table.Constraints = database.Constraints.Where( constraint => constraint.FKTableName == tableName ).ToDatabaseObjectsCollection( table );
                    tables.Add( table );
                }
            }

            return tables;
        }

        public override DatabaseObjectsCollection<IView> LoadViews( IDatabase database )
        {
            DatabaseObjectsCollection<IView> views = new DatabaseObjectsCollection<IView>( database );

            MySqlConnection sqlConnection = new MySqlConnection( database.ConnectionString );

            using ( MySqlDataAdapter da = new MySqlDataAdapter( string.Format(_getViews,database.Name) , sqlConnection ) )
            {
                DataTable dtViews = new DataTable( "Views" );
                da.Fill( dtViews );

                for ( int i = 0 ; i < dtViews.Rows.Count ; i++ )
                {
                    string viewName = (string)dtViews.Rows[ i ][ "TABLE_NAME" ];
                    string schemaName = (string)dtViews.Rows[ i ][ "TABLE_SCHEMA" ];
                    string viewDefinition = (string)dtViews.Rows[ i ][ "VIEW_DEFINITION" ];

                    View view = new View()
                    {
                        TableName = viewName ,
                        SchemaName = schemaName ,
                        Query = viewDefinition
                    };
                    LoadColumns( sqlConnection , view , database );
                    views.Add( view );
                }
            }

            return views;
        }

        public override DatabaseObjectsCollection<IProcedure> LoadProcedures( IDatabase database )
        {
            DatabaseObjectsCollection<IProcedure> procedures = new DatabaseObjectsCollection<IProcedure>( database );

            MySqlConnection sqlConnection = new MySqlConnection( database.ConnectionString );

            using ( MySqlDataAdapter da = new MySqlDataAdapter( _getProcedures , sqlConnection ) )
            {
                DataTable dtProcedures = new DataTable( "Procedures" );
                da.Fill( dtProcedures );

                for ( int i = 0 ; i < dtProcedures.Rows.Count ; i++ )
                {
                    Procedure procedure = new Procedure();
                    procedure.Name = (string)dtProcedures.Rows[ i ][ "ROUTINE_NAME" ];

                    #region 没有发现与存储过程参数相关的元数据
                    procedure.Items = new DatabaseObjectsCollection<IProcedureParameter>( procedure );

                    //DatabaseObjectsCollection<IProcedureParameter> procedureParameters =  new DatabaseObjectsCollection<IProcedureParameter>( procedure );

                    //da.SelectCommand = new MySqlCommand( _getProcedureParameters , sqlConnection );
                    //DataTable dtProcedureParameters = new DataTable( "ProcedureParameters" );
                    //da.Fill( dtProcedureParameters );

                    //for ( int j = 0 ; j < dtProcedureParameters.Rows.Count ; j++ )
                    //{
                    //    ProcedureParameter procedureParameter = new ProcedureParameter();
                    //    procedureParameter.Name = (string)dtProcedureParameters.Rows[ j ][ "PARAMETER_NAME" ];
                    //    if ( procedureParameter.Name.StartsWith( "@" ) )
                    //        procedureParameter.Name = procedureParameter.Name.Substring( 1 );

                    //    if ( dtProcedureParameters.Rows[ j ][ "DATA_TYPE" ] != DBNull.Value )
                    //        procedureParameter.DataType = (string)dtProcedureParameters.Rows[ j ][ "DATA_TYPE" ];

                    //    if ( dtProcedureParameters.Rows[ j ][ "CHARACTER_MAXIMUM_LENGTH" ] != DBNull.Value )
                    //        procedureParameter.Length = Convert.ToInt32( dtProcedureParameters.Rows[ j ][ "CHARACTER_MAXIMUM_LENGTH" ] );

                    //    string parameterMode = (string)dtProcedureParameters.Rows[ j ][ "PARAMETER_MODE" ];
                    //    if ( parameterMode == "IN" )
                    //        procedureParameter.ParameterMode = ParameterMode.In;
                    //    else if ( parameterMode == "OUT" )
                    //        procedureParameter.ParameterMode = ParameterMode.Out;
                    //    else
                    //        procedureParameter.ParameterMode = ParameterMode.InOut;

                    //    procedure.Items.Add( procedureParameter );
                    //}
                    #endregion
                    procedures.Add( procedure );
                }
            }

            return procedures;
        }

        public override string ToString()
        {
            return this.Name;
        }

        public override IDatasource CreateNewIDatasource( string server , string userid , string password )
        {
            MySQLDatasource ds = new MySQLDatasource( this );
            ds.Name = server;
            ds.Server = server;
            ds.UserId = userid;
            ds.Password = password;

            return ds;
        }
    }
}