using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;

namespace UpdateImageRoles
{
    class Program
    {
        public class Result {
            public string EntityId { get; set; }
            public string Value { get; set; }
        }

        public static bool UpdateImageRole(MySqlConnection connection, string enittyId, string value)
        {
            try
            {
                var sql = $@"UPDATE catalog_product_entity_varchar
                             SET value = '{value}'
                             WHERE row_id = '{enittyId}' AND attribute_id in (174,176,178);";

                var command = new MySqlCommand(sql, connection);
                var r = command.ExecuteNonQuery();

                return true;
            }
            catch(Exception e) {
                return false;
            }
        }

        static void Main(string[] args)
        {
            //var connection = new MySqlConnection("Server=192.168.65.215;Database=magento;Uid=magento;Pwd=magento;CharSet=utf8");

            var connection = new MySqlConnection("Server=192.168.0.8;Database=magento_stage;Uid=magento;Pwd=MXf2tZka;CharSet=utf8");

            //Console.WriteLine("Server ip Address :");

            //var ip = Console.ReadLine().Trim();

            //if (String.IsNullOrEmpty(ip))
            //{
            //    throw new Exception("Server ip address cannot be NULL");
            //}

            //Console.WriteLine("Database Name : ");

            //var database = Console.ReadLine().Trim();

            //if (String.IsNullOrEmpty(database))
            //{
            //    throw new Exception("Data cannot be NULL");
            //}

            //Console.WriteLine("Username : ");

            //var username = Console.ReadLine().Trim();

            //if (String.IsNullOrEmpty(username))
            //{
            //    throw new Exception("Username cannot be NULL");
            //}

            //Console.WriteLine("Password : ");

            //var password = Console.ReadLine().Trim();

            //if (String.IsNullOrEmpty(password))
            //{
            //    throw new Exception("Password cannot be NULL");
            //}

            Console.WriteLine("Starting Executting ------------- ");

            //var connection = new MySqlConnection($"Server={ip};Database={database};Uid={username};Pwd={password};CharSet=utf8");
            System.IO.StreamWriter file = new System.IO.StreamWriter(@"log.txt", true);

            try
            {
                var sql = @"SELECT media.value_id, product.row_id, position, images.value FROM catalog_product_entity as product
                        join catalog_product_entity_media_gallery_value as media on product.row_id = media.row_id
                        join catalog_product_entity_media_gallery as images on media.value_id = images.value_id
                        WHERE product.type_id = 'simple' AND product.sku = '3517200004'
                        GROUP BY media.value_id, product.row_id, position, images.value ORDER by position";

                connection.Open();

                var getAll = new MySqlCommand(sql, connection);

                var reader = getAll.ExecuteReader();

                var result = new List<Result>();

                while (reader.Read())
                {
                    result.Add(new Result()
                    {
                        EntityId = reader[1].ToString(),
                        Value = reader[3].ToString()

                    });
                }

                reader.Close();

                var updatedEntities = new List<string>();

                foreach (var r in result)
                {
                    if (!updatedEntities.Contains(r.EntityId))
                    {
                        UpdateImageRole(connection, r.EntityId, r.Value);
                        updatedEntities.Add(r.EntityId);

                        Console.WriteLine($"Data : {DateTime.Now}, Entity Updated {r.EntityId} ");
                        file.WriteLine($"Data : {DateTime.Now}, Entity Updated {r.EntityId} ");
                    }
                }

                connection.Close();
                file.Close();

                Console.WriteLine("--------- Done");
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                file.WriteLine(ex.Message.ToString());
                file.Close();
                connection.Close();
            }

        }
    }
}