<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class AddDeLanguageContent extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
        // Benefit Languages
        DB::statement("INSERT INTO benefit_languages (benefit_id, language_id, title, `text`, created_at)
                             SELECT benefit_id, 'de', CONCAT(title, ' (DE)'), CONCAT(`text`, ' (DE)'), NOW() FROM benefit_languages WHERE language_id = 'en';");

        // Block Languages
        DB::statement("INSERT INTO block_languages (block_id, language_id, title, block_title, wysiwyg,
                             block_title_text_box, block_title_text_area, block_primary_text_area, block_secondary_text_area,
                             block_project_keywords, block_deliverables_text_box)
                             SELECT block_id, 'de', CONCAT(title, ' (DE)'), CONCAT(block_title, ' (DE)'), wysiwyg,
                             block_title_text_box, block_title_text_area, block_primary_text_area, block_secondary_text_area,
                             block_project_keywords, block_deliverables_text_box FROM block_languages WHERE language_id = 'en';");
        // Blog Article Languages
        DB::statement("INSERT INTO blog_article_languages (blog_article_id, language_id, article_title, title,
                                    meta_title, meta_description, meta_keywords, created_at)
                             SELECT blog_article_id, 'de', CONCAT(article_title, ' (DE)'), CONCAT(title, ' (DE)'), meta_title, meta_description, meta_keywords,
                                    NOW() FROM blog_article_languages WHERE language_id = 'en';");

        // Client Logo Languages
        DB::statement("INSERT INTO client_logo_languages (client_logo_id, language_id, title, created_at)
                             SELECT client_logo_id, 'de', CONCAT(title, ' (DE)'), NOW() FROM client_logo_languages WHERE language_id = 'en';");

        // Entries (pages) Languages
        DB::statement("INSERT INTO entries_lang (entry_id, language_id, title, content, meta_title, meta_description,
                               meta_keywords)
                             SELECT entry_id, 'de', CONCAT(title, ' (DE)'), CONCAT(content, ' (DE)'), meta_title, meta_description, meta_keywords
                                FROM entries_lang WHERE language_id = 'en';");

        // Footer Languages
        DB::statement("INSERT INTO footer_languages (footer_id, language_id, title, address, copyright_text, funny_phrase,
                              created_at)
                             SELECT footer_id, 'de', CONCAT(title, ' (DE)'), address, copyright_text, funny_phrase, NOW() FROM footer_languages
                                WHERE language_id = 'en';");

        // Key Message Languages
        DB::statement("INSERT INTO key_message_languages (key_message_id, language_id, title, message_title, `text`,
                              created_at)
                             SELECT key_message_id, 'de', CONCAT(title, ' (DE)'), CONCAT(message_title, ' (DE)'), `text`, NOW() FROM key_message_languages
                                WHERE language_id = 'en';");

        // Loader Collection Languages
        DB::statement("INSERT INTO loader_collection_languages (loader_collection_id, language_id, title, created_at)
                             SELECT loader_collection_id, 'de', CONCAT(title, ' (DE)'), NOW() FROM loader_collection_languages
                                WHERE language_id = 'en';");

        // Menu Items Languages
        DB::statement("INSERT INTO menu_items_lang (menu_item_id, language_id, title, url)
                             SELECT menu_item_id, 'de', CONCAT(title, ' (DE)'), CONCAT('/de', url) FROM menu_items_lang
                                WHERE language_id = 'en';");

        // Open Position Detail Languages
        DB::statement("INSERT INTO open_position_detail_languages (open_position_detail_id, language_id, title,
                                detail_title, `text`, created_at)
                             SELECT open_position_detail_id, 'de', CONCAT(title, ' (DE)'), CONCAT(detail_title, ' (DE)'), `text`, NOW() FROM open_position_detail_languages
                                WHERE language_id = 'en';");

        // Open Position Languages
        DB::statement("INSERT INTO open_position_languages (open_position_id, language_id, title, position_title,
                                link_description, meta_title, meta_description, meta_keywords, created_at)
                             SELECT open_position_id, 'de', CONCAT(title, ' (DE)'), CONCAT(position_title, ' (DE)'), link_description, meta_title,
                                meta_description, meta_keywords, NOW() FROM open_position_languages
                                WHERE language_id = 'en';");

        // Open Position Spec Languages
        DB::statement("INSERT INTO open_position_spec_languages (open_position_spec_id, language_id, title,
                                spec_title, text_wysiwyg, created_at)
                             SELECT open_position_spec_id, 'de', CONCAT(title, ' (DE)'), CONCAT(spec_title, ' (DE)'), text_wysiwyg, NOW() FROM open_position_spec_languages
                                WHERE language_id = 'en';");

        // Phrase Languages
        DB::statement("INSERT INTO phrase_languages (phrase_id, language_id, title, phrase, created_at)
                             SELECT phrase_id, 'de', CONCAT(title, ' (DE)'), CONCAT(phrase, ' (DE)'), NOW() FROM phrase_languages
                                WHERE language_id = 'en';");

        // Project Detail Languages
        DB::statement("INSERT INTO project_detail_languages (project_detail_id, language_id, title, label, info, created_at)
                             SELECT project_detail_id, 'de', CONCAT(title, ' (DE)'), CONCAT(label, ' (DE)'), info, NOW() FROM project_detail_languages
                                WHERE language_id = 'en';");

        // Project Languages
        DB::statement("INSERT INTO project_languages (project_id, language_id, project_title, title, project_keywords,
                               link_label, meta_title, meta_description, meta_keywords, created_at)
                             SELECT project_id, 'de', project_title, CONCAT(title, ' (DE)'), CONCAT(project_keywords, ' (DE)'), link_label, meta_title,
                                meta_description, meta_keywords, NOW() FROM project_languages
                                WHERE language_id = 'en';");

        // Service Solution Languages
        DB::statement("INSERT INTO service_solution_languages (service_solution_id, language_id, title, created_at)
                             SELECT service_solution_id, 'de', CONCAT(title, ' (DE)'), NOW() FROM service_solution_languages
                                WHERE language_id = 'en';");

        // Simple Link Languages
        DB::statement("INSERT INTO simple_link_languages (simple_link_id, language_id, title, created_at)
                             SELECT simple_link_id, 'de', CONCAT(title, ' (DE)'), NOW() FROM simple_link_languages
                                WHERE language_id = 'en';");

        // Social Media Languages
        DB::statement("INSERT INTO social_media_languages (social_media_id, language_id, title, created_at)
                             SELECT social_media_id, 'de', CONCAT(title, ' (DE)'), NOW() FROM social_media_languages
                                WHERE language_id = 'en';");

        // Team Member Languages
        DB::statement("INSERT INTO team_member_languages (team_member_id, language_id, title, role, question, answer, created_at)
                             SELECT team_member_id, 'de', CONCAT(title, ' (DE)'), CONCAT(role, ' (DE)'), question, answer, NOW() FROM team_member_languages
                                WHERE language_id = 'en';");


        DB::statement("INSERT INTO website_objects_lang (website_object_id, wo_language_id, lang_slug, lang_uri)
                             SELECT website_object_id, 'de', CONCAT(lang_slug, '-de'), IF (lang_uri IS NOT NULL, CONCAT(lang_uri, '-de'), NULL) FROM website_objects_lang WHERE wo_language_id = 'en';");

        DB::statement("UPDATE `website_objects_lang` SET lang_uri = REPLACE(lang_uri, '/work', '/arbeiten') where lang_uri LIKE '/work%' and wo_language_id =  'de';");
        DB::statement("UPDATE `website_objects_lang` SET lang_uri = REPLACE(lang_uri, '/blog', '/bloggen') where lang_uri LIKE '/blog%' and wo_language_id =  'de';");
        DB::statement("UPDATE `website_objects_lang` SET lang_uri = REPLACE(lang_uri, '/careers', '/karriere') where lang_uri LIKE '/careers%' and wo_language_id =  'de';");
        DB::statement("UPDATE `website_objects_lang` SET lang_uri = '/' where lang_uri = '/-de';");

    }

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
        DB::statement("DELETE FROM benefit_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM block_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM blog_article_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM client_logo_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM entries_lang WHERE language_id = 'de';");
        DB::statement("DELETE FROM footer_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM key_message_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM loader_collection_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM menu_items_lang WHERE language_id = 'de';");
        DB::statement("DELETE FROM open_position_detail_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM open_position_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM open_position_spec_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM phrase_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM project_detail_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM project_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM service_solution_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM simple_link_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM social_media_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM team_member_languages WHERE language_id = 'de';");
        DB::statement("DELETE FROM website_objects_lang WHERE wo_language_id = 'de';");
	}

}