using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MySqlX.XDevAPI;
using MySqlX.XDevAPI.Common;
using MySqlX.XDevAPI.Relational;

namespace MySQLBug
{
    class Program
    {
        static async Task Main(string[] args)
        {
            //OK, please empty table after any test with successful insert (id can not be insert twice)
            ulong Id1 = await SetNewInformationFound("1", "NoBug", "CURDATE()");
            ulong Id2 = await SetNewInformationFound("2", "NoBug_NoBug", "CURDATE()");
            ulong Id3 = await SetNewInformationFound("3", @"NoBug\NoBug", "CURDATE()");

            //Other special caracters have trouble : -, %, / ==>Exception with not defined field (Bug)
            ulong Id4 = await SetNewInformationFound("4", "ImproperFieldNameBug-Bug", "CURDATE()");
            ulong Id5 = await SetNewInformationFound("5", "ImproperFieldNameBug=Bug", "CURDATE()");
            ulong Id6 = await SetNewInformationFound("6", "ImproperFieldNameBug%Bug", "CURDATE()");
            ulong Id7 = await SetNewInformationFound("7", "ImproperFieldNameBug&Bug", "CURDATE()");
            ulong Id8 = await SetNewInformationFound("8", "ImproperFieldNameBug/Bug", "CURDATE()");

            //Other missuntrepretted for datettime via MySqlExpression : CURDATE is OK Now is OK, but not more complex expression
            ulong Id9 = await SetNewInformationFound("9", @"NoBug\NoBug", "DATE_ADD(NOW(), INTERVAL - 30 DAY)");
        }

        public static async Task<ulong> SetNewInformationFound(string id_, string testString_, string DateTimeComputation_)
        {
            Session dbSession;
            Schema dbSchema;
            Table tblRepliTbAnoncehead;

            dbSession = MySQLX.GetSession("server=w-rec-web-01;userid=debug;password=debug;database=debug;sslmode=none;charset=utf8mb4;port=33090");
            dbSchema = dbSession.GetSchema("debug");
            tblRepliTbAnoncehead = dbSchema.GetTable("repli_tb_anoncehead");

            MySqlExpression mySqlNowExpression = new MySqlExpression("NOW()");
            MySqlExpression mySqlCurDateExpression = new MySqlExpression(DateTimeComputation_);

            List<string> fieldsToInsertList = new List<string>();
            List<object> valuesToInsertList = new List<object>();

            fieldsToInsertList.Add("ID"); valuesToInsertList.Add(id_);
            fieldsToInsertList.Add("SITE_ID"); valuesToInsertList.Add("2");
            fieldsToInsertList.Add("UPDATED_DATE"); valuesToInsertList.Add(mySqlNowExpression);
            fieldsToInsertList.Add("DOC_TYPE"); valuesToInsertList.Add("3");
            fieldsToInsertList.Add("TITLE"); valuesToInsertList.Add("4");
            fieldsToInsertList.Add("TYPE_INDEXATION"); valuesToInsertList.Add("5");
            fieldsToInsertList.Add("ID_PA_DUJOUR"); valuesToInsertList.Add("6");
            fieldsToInsertList.Add("DATE_PUBLI"); valuesToInsertList.Add(mySqlCurDateExpression);
            fieldsToInsertList.Add("USER_ID"); valuesToInsertList.Add("7");
            fieldsToInsertList.Add("COLLECTE_SA"); valuesToInsertList.Add(0);
            fieldsToInsertList.Add("LIEU_EXE"); valuesToInsertList.Add("8");
            fieldsToInsertList.Add("FILE_NAME"); valuesToInsertList.Add("9");
            fieldsToInsertList.Add("SITE_PA_ID"); valuesToInsertList.Add(testString_);
            fieldsToInsertList.Add("STATUS"); valuesToInsertList.Add("new");
            fieldsToInsertList.Add("LastDetect"); valuesToInsertList.Add(mySqlNowExpression);
            fieldsToInsertList.Add("ID_ORIGINE"); valuesToInsertList.Add("12");

            string[] fieldsToInsert = fieldsToInsertList.ToArray();
            Object[] valuesToInsert = valuesToInsertList.ToArray();

            TableInsertStatement insertionRequest = tblRepliTbAnoncehead.Insert(fieldsToInsert).Values(valuesToInsert);

            Task<Result> getFoundDuplicateInformations = insertionRequest.ExecuteAsync();

            Result getFoundDuplicateInformationsResult = await getFoundDuplicateInformations;

            return 1;
        }

}
}