using MySql.Data.MySqlClient;
using System.Reflection;

namespace ServiceShared.Database.Migrations
{
    public class Version_1 : Migration
    {
        public Version_1()
        {
            this.Version = 1;
        }

        /**
         * Run current migration
         **/
        public override bool Run(DbContext dbContext)
        {
            bool result = false;

            try
            {
                Log.Info("Check if database exists...");

                /** necessary to run first for refreshing mysql connection context **/
                if (!dbContext.DatabaseExists())
                {
                    Log.Info("Run Migration - " + this.Version + "");

                    if (dbContext.DatabaseCreate())
                    {
                        using (MySqlConnection client = dbContext.CreateConnection())
                        {
                            client.Open();

                            MySqlCommand cmd = client.CreateCommand();
                            cmd.CommandText = "set foreign_key_checks = 0";
                            cmd.ExecuteNonQuery();

                            /* devices table */
                            cmd.CommandText = @"create table if not exists device (
                                                    udid varchar(64) not null,
                                                    device_token varchar(255) not null,
                                                    pat_hash varchar(255) null,
                                                    public_key varchar(64) null,      
                                                    verificator_hash varchar(128) not null,
                                                    pin varchar(5) not null,
                                                    device_type enum ('IOS','ANDROID') not null,
                                                    created timestamp not null default current_timestamp(),
                                                    modified datetime null,
                                                    primary key(udid) using btree,
                                                    index device_token(device_token) using btree,
                                                    index verificator_hash(verificator_hash) using btree
                                                )";

                            cmd.ExecuteNonQuery();

                            /* results table */
                            cmd.CommandText = @"create table if not exists results (
                                                    pgs varchar(128) not null,
                                                    pgs_hash varchar(128) not null,
                                                    udid varchar(64) not null,
                                                    client_public_key varchar(64) not null,           
                                                    server_public_key varchar(64) null,      
                                                    available bit(1) not null default 0,
                                                    available_ts datetime null,
                                                    picked_up bit(1) not null default 0,
                                                    picked_up_ts datetime null,
                                                    file_checksum varchar(128) null,
                                                    notified bit(1) not null default 0,
                                                    notified_ts datetime null,
                                                    modified datetime null,
                                                    status varchar(50) null,
                                                    created timestamp not null default current_timestamp(),
                                                    primary key(pgs, udid) using btree,
                                                    index pgs(pgs) using btree,
                                                    index udid(udid) using btree,                                                    
                                                    index client_public_key(client_public_key) using btree,
                                                    constraint fk_results_device foreign key(udid) references device(udid) on update cascade on delete cascade
                                                )";

                            cmd.ExecuteNonQuery();

                            /* trace table */
                            cmd.CommandText = @"create table if not exists traces (
                                                    id bigint primary key auto_increment,
                                                    pgs varchar(128) not null,
                                                    udid varchar(64) null,
                                                    message varchar(256) not null,                         
                                                    trace_type enum('CRITICAL', 'ERROR', 'INFO', 'LOG', 'TRACE', 'DEBUG', 'WARNING') not null default 'TRACE',      
                                                    created timestamp not null default current_timestamp(),
                                                    index pgs(pgs) using btree,
                                                    index udid(udid) using btree,
                                                    index pgs_udid(pgs, udid) using btree)";

                            cmd.ExecuteNonQuery();

                            /* config table */
                            cmd.CommandText = @"create table if not exists config (
                                                    id int primary key auto_increment,
                                                    maintenance bit(1) not null default 0,
                                                    modified datetime null,
                                                    created timestamp not null default current_timestamp())";

                            cmd.ExecuteNonQuery();

                            /* insert into config table */
                            cmd.CommandText = @"insert into config (maintenance) values(0)";

                            cmd.ExecuteNonQuery();

                            /* Enable FK check */
                            cmd.CommandText = "set foreign_key_checks = 1";
                            cmd.ExecuteNonQuery();
                            client.Close();

                            result = true;
                        }
                    }
                }
                else
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, "ServiceOutside.Database.Migrations.Version_1", "Run");
            }

            return result;
        }

        /**
         * Run previous migrations
         **/ 
        public override bool RunPrevious(DbContext dbContext)
        {
            return true;
        }
    }
}