<?php

namespace FQP;

use PDO;

class DB {
    public static $prefix = 'fasoon_';

    public static function init_database() {
        global $wpdb;

        $tables = [
            'legal_entity_type' => [
                'sql'      => /** @lang text */'CREATE TABLE `%table%` (
                                `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                                `hasBacking` ENUM("y", "n") NOT NULL DEFAULT "n",
                                `sorter` INT(11) NOT NULL,
                                PRIMARY KEY (`ID`)
                          ) %charset%;',
                'defaults' => [
                    ['ID' => 1, 'hasBacking' => 'y', 'sorter' => 1],
                    ['ID' => 2, 'hasBacking' => 'y', 'sorter' => 2],
                    ['ID' => 3, 'hasBacking' => 'n', 'sorter' => 3],
                    ['ID' => 4, 'hasBacking' => 'n', 'sorter' => 4],
                ],
            ],

            'legal_entity_type_data' => [
                'sql'      => /** @lang text */'CREATE TABLE `%table%` (
                            `ID` INT(10) UNSIGNED NOT NULL,
                            `lang` CHAR(2) NOT NULL,
                            `title` VARCHAR(255) NOT NULL,
                            PRIMARY KEY (`ID`, `lang`)
                        ) %charset%;',
                'defaults' => [
                    ['ID' => 1, 'lang' => 'en', 'title' => 'AG'],
                    ['ID' => 2, 'lang' => 'en', 'title' => 'Ltd.'],
                    ['ID' => 3, 'lang' => 'en', 'title' => 'Single Company'],
                    ['ID' => 4, 'lang' => 'en', 'title' => 'Collective Community'],

                    ['ID' => 1, 'lang' => 'de', 'title' => 'AG'],
                    ['ID' => 2, 'lang' => 'de', 'title' => 'GmbH'],
                    ['ID' => 3, 'lang' => 'de', 'title' => 'Einzelfirma'],
                    ['ID' => 4, 'lang' => 'de', 'title' => 'Kollektivgemeinschaft'],
                ],
            ],

            'financial_backing_type' => [
                'sql'      => /** @lang text */'CREATE TABLE `%table%` (
                                `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                                `sorter` INT(11) NOT NULL,
                                PRIMARY KEY (`ID`) 
                          ) %charset%;',
                'defaults' => [
                    ['ID' => 1, 'sorter' => 1],
                    ['ID' => 2, 'sorter' => 2],
                ],
            ],

            'financial_backing_type_data' => [
                'sql'      => /** @lang text */'CREATE TABLE `%table%` (
                            `ID` INT(10) UNSIGNED NOT NULL,
                            `lang` CHAR(2) NOT NULL,
                            `title` VARCHAR(255) NOT NULL,
                            PRIMARY KEY (`ID`, `lang`)
                        ) %charset%;',
                'defaults' => [
                    ['ID' => 1, 'lang' => 'en', 'title' => 'Cash'],
                    ['ID' => 2, 'lang' => 'en', 'title' => 'Goods'],

                    ['ID' => 1, 'lang' => 'de', 'title' => 'Bareinlage'],
                    ['ID' => 2, 'lang' => 'de', 'title' => 'Sacheinlage'],
                ],
            ],

            'submission' => [
                'sql'      => /** @lang text */'CREATE TABLE `%table%` (
                              `ID` int(10) UNSIGNED NOT NULL,
                              `zoho_account_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT \'\',
                              `zoho_contact_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT \'\',
                              `zoho_deal_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT \'\',
                              `stripe_checkout_session_id` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
                              `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                              `price` decimal(10,2) NOT NULL,
                              `tax` decimal(10,2) NOT NULL DEFAULT \'0.00\',
                              `payment_fee` decimal(10,2) NOT NULL DEFAULT \'0.00\',
                              `payment_fee_tax` decimal(10,2) NOT NULL DEFAULT \'0.00\',
                              `entry_type_ID` int(10) UNSIGNED NOT NULL,
                              `entry_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `backing_type_ID` int(10) UNSIGNED DEFAULT NULL,
                              `backing_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                              `prefix` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
                              `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `street` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `phone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `company_street` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `company_zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `company_city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                              `company_purpose` text COLLATE utf8_unicode_ci NOT NULL,
                              `_shares_number` int(10) UNSIGNED NOT NULL,
                              `equity_capital` decimal(10,2) NOT NULL,
                              `equity_capital_paid` decimal(10,2) NOT NULL,
                              `in_kind` decimal(10,2) NOT NULL,
                              `in_cash` decimal(10,2) NOT NULL,
                              `share_price` decimal(10,2) NOT NULL,
                              `bank_details` text COLLATE utf8_unicode_ci NOT NULL,
                              `comment` text COLLATE utf8_unicode_ci NOT NULL,
                              `payment_method` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT \'\',
                              `payment_status` enum(\'pending\',\'paid\') COLLATE utf8_unicode_ci DEFAULT NULL,
                              `email_subscription` enum(\'y\',\'n\') COLLATE utf8_unicode_ci NOT NULL DEFAULT \'n\',
                              `agree` enum(\'y\',\'n\') COLLATE utf8_unicode_ci NOT NULL DEFAULT \'n\',
                              `language` varchar(255) COLLATE utf8_unicode_ci NOT NULL
                          ) %charset%;
                          ALTER TABLE `%table%`
                              ADD PRIMARY KEY (`ID`),
                              ADD UNIQUE KEY `stripe_checkout_session_id` (`stripe_checkout_session_id`);
                            ALTER TABLE `%table%`
                                MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;',
                'defaults' => [],
            ],

            'submission_partner' => [
                'sql' => 'CREATE TABLE `%table%` (
                          `ID` int(10) UNSIGNED NOT NULL,
                          `submission_ID` int(10) UNSIGNED NOT NULL,
                          `partner_ID` bigint(20) UNSIGNED DEFAULT NULL,
                          `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `discount` decimal(10,2) NOT NULL
                        ) %charset%;
                        ALTER TABLE `%table%`
                            ADD PRIMARY KEY (`ID`),
                            ADD KEY `submission_ID` (`submission_ID`),
                            ADD KEY `partner_ID` (`partner_ID`);
                        ALTER TABLE `%table%`
                            MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;',
                'defaults' => [],
            ],

            'submission_person' => [
                'sql' => 'CREATE TABLE `%table%` (
                          `ID` int(10) UNSIGNED NOT NULL,
                          `submission_ID` int(10) UNSIGNED NOT NULL,
                          `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
                          `shares_number` int(10) UNSIGNED NOT NULL,
                          `prefix` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
                          `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `street` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `nationality_ID` int(10) UNSIGNED DEFAULT NULL,
                          `nationality` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `hometown` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `birth_date` date NOT NULL,
                          `vr_function_in_company_ID` int(10) UNSIGNED DEFAULT NULL,
                          `vr_function_in_company` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `function_in_company_ID` int(10) UNSIGNED DEFAULT NULL,
                          `function_in_company` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `signature_authorization_ID` int(10) UNSIGNED DEFAULT NULL,
                          `signature_authorization` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `company_address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `company_zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `company_city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `company_country_ID` int(10) UNSIGNED DEFAULT NULL,
                          `company_country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `company_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL
                        )  %charset%;
                        ALTER TABLE `%table%`
                            ADD PRIMARY KEY (`ID`),
                            ADD KEY `submission_ID` (`submission_ID`),
                            ADD KEY `nationality_ID` (`nationality_ID`),
                            ADD KEY `vr_function_in_company_ID` (`vr_function_in_company_ID`),
                            ADD KEY `function_in_company_ID` (`function_in_company_ID`),
                            ADD KEY `signature_authorization_ID` (`signature_authorization_ID`),
                            ADD KEY `company_country_ID` (`company_country_ID`);
                        ALTER TABLE `%table%`
                            MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;',
                'defaults' => [],
            ],

            'submission_product' => [
                'sql' => 'CREATE TABLE `%table%` (
                          `ID` int(10) UNSIGNED NOT NULL,
                          `submission_ID` int(10) UNSIGNED NOT NULL,
                          `product_ID` bigint(20) UNSIGNED DEFAULT NULL,
                          `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
                          `price` decimal(10,2) NOT NULL,
                          `tax` decimal(10,2) NOT NULL
                        )  %charset%;
                        ALTER TABLE `%table%`
                            ADD PRIMARY KEY (`ID`),
                            ADD KEY `submission_ID` (`submission_ID`),
                            ADD KEY `product_ID` (`product_ID`);
                        ALTER TABLE `%table%`
                            MODIFY `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;',
                'defaults' => [],
            ],
        ];

        foreach ( $tables as $table => $specs ) {
            if ( !self::is_table_exists( $table ) ) {
                $table_name = self::get_table_name( $table );
                $charset_collate = $wpdb->get_charset_collate();
                $sql = str_replace( ['%table%', '%charset%'], [$table_name, $charset_collate], $specs['sql'] );

                $wpdb->show_errors();
                $wpdb->query( $sql );
                foreach ( $specs['defaults'] as $row ) {
                    $wpdb->insert( $table_name, $row );
                }
                $wpdb->hide_errors();
            }
        }
    }

    /**
     * @param string $table
     * @return bool
     */
    public static function is_table_exists( $table ) {
        global $wpdb;
        $table = self::get_table_name( $table );
        return (bool) $wpdb->query( 'SHOW TABLES LIKE "' . $wpdb->esc_like( $table ) . '"' );
    }

    /**
     * @param string $table
     * @return string
     */
    public static function get_table_name( $table ) {
        global $wpdb;
        return $wpdb->prefix . self::$prefix . $table;
    }

    public static function get_entry_types( $lang = null, $force = false ) {
        global $wpdb;
        static  $types;

        if ( $force ) {
            $types = null;
        }

        if ( null === $types ) {
            if (null === $lang) {
                $lang = FQP::get_curr_lang();
            }
            $lang = esc_sql($lang);

            $table_entity_type = self::get_table_name( 'legal_entity_type' );

            $sql = /** @lang text */"
                SELECT `et`.`ID`, `et`.`hasBacking`, `etd`.`title`, `et`.`gtm_title`
                FROM `{$table_entity_type}` as `et`
                JOIN `{$table_entity_type}_data` as `etd` ON `et`.`ID` = `etd`.`ID` AND `etd`.`lang` = '{$lang}'
                WHERE 1
                ORDER BY `et`.`sorter`
            ";
            $types = $wpdb->get_results( $sql, ARRAY_A );
            $types = array_combine( array_column( $types, 'ID' ), $types );
        }

        return $types;
    }

    public static function get_backing_types( $lang = null, $force = false ) {
        global $wpdb;
        static $types;

        if ( $force ) {
            $types = null;
        }

        if ( null === $types ) {
            if (null === $lang) {
                $lang = FQP::get_curr_lang();
            }
            $lang = esc_sql($lang);

            $table_backing_type = self::get_table_name( 'financial_backing_type' );

            $sql = /** @lang text */"
                SELECT `bt`.`ID`, `btd`.`title`, `bt`.`gtm_title`
                FROM `{$table_backing_type}` as `bt`
                JOIN `{$table_backing_type}_data` as `btd` ON `bt`.`ID` = `btd`.`ID` AND `btd`.`lang` = '{$lang}'
                WHERE 1
                ORDER BY `bt`.`sorter`
            ";
            $types = $wpdb->get_results($sql, ARRAY_A);
            $types = array_combine(array_column($types, 'ID'), $types);
        }

        return $types;
    }

    public static function get_partners_query()
    {
        static $query;
        if (null === $query) {
            $query = new \WP_Query( [
                'post_type' => FQP::PARTNER_POST_TYPE,
                'order'     => 'ASC',
                'orderby'   => 'menu_order'
            ] );
        }
        return $query;
    }

    public static function get_partners( $forceArrays = false ) {
        static $partners;
        if (null === $partners) {
            $partners = self::get_partners_query()->get_posts();
            /** @var \WP_Post $partner */
            foreach ( $partners as &$partner ) {
                $partner->acf = get_fields($partner);
                if ( ! empty( $partner->acf['partner_discount'] ) ) {
                    if (@$partner->acf['hide_discount']) {
                        array_walk($partner->acf['partner_discount'], function (&$val) { $val = 0; });
                    }
                    $discount  = [];
                    $_discount = $partner->acf['partner_discount'];
                    foreach ( $_discount as $key => $val ) {
                        $val = floatval( $val );
                        eval( "\${$key} = {$val};" );
                    }
                    $partner->acf['partner_discount'] = $discount;
                }
            }
            unset( $partner );
        }

        if ( $forceArrays ) {
            $arrays = $partners;
            array_walk( $arrays, function ( &$val ) { $val = (array) $val; } );
            return array_combine( array_column( $arrays, 'ID' ), $arrays );
        }
        return $partners;
    }

    public static function delete_row( $table, $ID ) {
        global $wpdb;
        return $wpdb->delete( self::get_table_name( $table ), [ 'ID' => $ID] );
    }

    public static function delete_submission( $ID ) {
        global $wpdb;
        $wpdb->delete( self::get_table_name( 'submission' ), [ 'ID' => $ID] );
        $wpdb->delete( self::get_table_name( 'submission_partner' ), [ 'submission_ID' => $ID] );
        $wpdb->delete( self::get_table_name( 'submission_person' ), [ 'submission_ID' => $ID] );
        $wpdb->delete( self::get_table_name( 'submission_product' ), [ 'submission_ID' => $ID] );
    }

    protected static function get_sql_for_select( $table, $lang = null ) {
        if ( null === $lang ) {
            $lang = FQP::get_curr_lang();
        }
        $lang = esc_sql($lang);

        $table = self::get_table_name( $table );

        $sql = /** @lang text */"
                SELECT `n`.`id`, `nd`.`title`
                FROM `{$table}` as `n`
                JOIN `{$table}_data` as `nd` ON `n`.`id` = `nd`.`id` AND `nd`.`lang` = '{$lang}'
                WHERE 1
                ORDER BY `n`.`sorter`
            ";

        return $sql;
    }

    public static function get_nationality( $lang = null ) {
        global $wpdb;
        static $nationality;

        if ( null === $nationality ) {
            $sql = static::get_sql_for_select( 'nationality', $lang );
            $nationality = $wpdb->get_results( $sql, ARRAY_A );
            $nationality = array_combine( array_column( $nationality, 'id' ), array_column( $nationality, 'title' ) );
        }

        return $nationality;
    }

    public static function get_vr_function_in_company( $lang = null, $force = false ) {
        global $wpdb;
        static $vr_function;

        if ( $force ) {
            $vr_function = null;
        }

        if ( null === $vr_function ) {
            $sql = static::get_sql_for_select( 'vr_function_in_company', $lang );
            $vr_function = $wpdb->get_results( $sql, ARRAY_A );
            $vr_function = array_combine( array_column( $vr_function, 'id' ), array_column( $vr_function, 'title' ) );
        }

        return $vr_function;
    }

    public static function get_function_in_company( $lang = null, $force = false ) {
        global $wpdb;
        static $function;

        if ( $force ) {
            $function = null;
        }

        if ( null === $function ) {
            $sql = static::get_sql_for_select( 'function_in_company', $lang );
            $function = $wpdb->get_results( $sql, ARRAY_A );
            $function = array_combine( array_column( $function, 'id' ), array_column( $function, 'title' ) );
        }

        return $function;
    }

    public static function get_signature_authorization( $lang = null, $force = false ) {
        global $wpdb;
        static $signature;

        if ( $force ) {
            $signature = null;
        }

        if ( null === $signature ) {
            $sql = static::get_sql_for_select( 'signature_authorization', $lang );
            $signature = $wpdb->get_results( $sql, ARRAY_A );
            $signature = array_combine( array_column( $signature, 'id' ), array_column( $signature, 'title' ) );
        }

        return $signature;
    }

    public static function get_pdo() {
        static $pdo;

        if ( null === $pdo ) {
            $db_name = DB_NAME;
            $db_user = DB_USER;
            $db_pass = DB_PASSWORD;
            $db_host = DB_HOST;
            $db_charset = DB_CHARSET;
            $dsn = "mysql:dbname={$db_name};host={$db_host};charset={$db_charset}";
            $pdo = new PDO( $dsn, $db_user, $db_pass );
        }

        return $pdo;
    }

    public static function get_submission( $submission_ID ) {
        global $wpdb;

        $table = DB::get_table_name( 'submission' );
        $submission_ID = esc_sql( $submission_ID );
        $query = /** @lang sql */ "SELECT * FROM `$table` WHERE `ID` = $submission_ID";
        $submission = $wpdb->get_row( $query, ARRAY_A );
        if ( empty( $submission ) ) {
            return false;
        }

        if ( ! empty( $submission['referrer'] ) ) {
            $submission['referrer'] = unserialize( $submission['referrer'] );
        } else {
            $submission['referrer'] = [];
        }

        $query = /** @lang sql */ "SELECT * FROM `{$table}_partner` WHERE `submission_ID` = $submission_ID ORDER BY `ID`";
        $submission['partners'] = $wpdb->get_results( $query, ARRAY_A );

        $query = /** @lang sql */ "SELECT * FROM `{$table}_person` WHERE `submission_ID` = $submission_ID ORDER BY `ID`";
        $submission['persons'] = $wpdb->get_results( $query, ARRAY_A );

        $query = /** @lang sql */ "SELECT * FROM `{$table}_product` WHERE `submission_ID` = $submission_ID ORDER BY `ID`";
        $submission['products'] = $wpdb->get_results( $query, ARRAY_A );

        return $submission;
    }

    /**
     * Return
     * array(
     *     'canotn title' => array(
     *         'zip_code' => 'zip_code + city',
     *         ...
     *     ),
     *     ...
     * )
     * or (if $for_autocomplete == true)
     * array(
     *     array(
     *         'value' => 'zip_code',
     *         'label' => 'zip_code + city name',
     *         'city'  => 'city name',
     *     ),
     *     ...
     * )
     *
     * @return array
     */
    public static function get_zip_codes( $lang = null, $for_autocomplete = false )
    {
        global $wpdb;

        if ( null === $lang ) {
            $lang = FQP::get_curr_lang();;
        }
        $lang = esc_sql($lang);

        if ( !$for_autocomplete ) {
            $sql_tpl = /** @lang text */"
                SELECT `c`.`ID`, `cd`.`title`
                FROM `{$wpdb->prefix}swiss_canton` as `c`
                JOIN `{$wpdb->prefix}swiss_canton_data` as `cd` ON `c`.`ID` = `cd`.`canton_ID` AND `cd`.`lang` = '{lang}'
                WHERE 1
                ORDER BY `c`.`sorter`
            ";

            $cantons =  $wpdb->get_results( str_replace( '{lang}', $lang, $sql_tpl ), ARRAY_A );
            if ( empty( $cantons ) ) {
                $cantons =  $wpdb->get_results( str_replace( '{lang}', 'de', $sql_tpl ), ARRAY_A );
            }

            if (empty( $cantons ) ) {
                return  [];
            }
        }

        $sql_tpl = /** @lang text */"
                SELECT `z`.`zip_code`, `z`.`canton_ID`, `zd`.`city`
                FROM `{$wpdb->prefix}swiss_zip` as `z`
                JOIN `{$wpdb->prefix}swiss_zip_data` as `zd` ON `z`.`ID` = `zd`.`zip_ID` AND `zd`.`lang` = '{lang}'
                WHERE 1
                ORDER BY `zd`.`city`
            ";

        $zip_codes =  $wpdb->get_results( str_replace( '{lang}', $lang, $sql_tpl ), ARRAY_A );
        if ( empty( $zip_codes ) ) {
            $zip_codes =  $wpdb->get_results( str_replace( '{lang}', 'de', $sql_tpl ), ARRAY_A );
        }

        if (empty( $zip_codes ) ) {
            return  [];
        }

        $result = [];

        if ( !$for_autocomplete ) {
            $_zip_codes = [];
            foreach ( $zip_codes as $zip ) {
                $_zip_codes[$zip['canton_ID']][$zip['zip_code']] = "{$zip['zip_code']} - {$zip['city']}";
            }
            $zip_codes = $_zip_codes;
            unset($_zip_codes);

            foreach ($cantons as $canton) {
                $result[$canton['title']] = $zip_codes[$canton['ID']];
            }
        } else {
            foreach ( $zip_codes as $zip ) {
                $result[] = [
                    'value'  => $zip['zip_code'],
                    'label'  => "{$zip['zip_code']} - {$zip['city']}",
                    'city'   => $zip['city'],
                    'canton' => $zip['canton_ID'],
                ];
            }
        }

        return $result;
    }


    /**
     * Return array( 'ID' => 'string canton code', 'title' => 'string canton title' )
     * @param $zip
     * @param null $lang
     * @return array | false
     */
    public static function get_canton_by_zip( $zip, $lang = null )
    {
        global $wpdb;

        if ( null === $lang ) {
            $lang = FQP::get_curr_lang();;
        }
        $lang = esc_sql( $lang );

        $zip = esc_sql( $zip );

        $sql_tpl = /** @lang text */"
                SELECT `cd`.`canton_ID` as `ID`, `cd`.`title`
                FROM `{$wpdb->prefix}swiss_zip` as `z`
                JOIN `{$wpdb->prefix}swiss_canton_data` as `cd` ON `z`.`canton_ID` = `cd`.`canton_ID` AND `cd`.`lang` = '{lang}'
                WHERE `z`.`zip_code` = '{$zip}'
                LIMIT 1
            ";

        $canton =  $wpdb->get_row( str_replace( '{lang}', $lang, $sql_tpl ), ARRAY_A );
        if ( empty( $canton ) ) {
            $canton =  $wpdb->get_row( str_replace( '{lang}', 'de', $sql_tpl ), ARRAY_A );
        }

        return (is_array($canton)) ? $canton : false;
    }

    /**
     * Return array( canton_ID => canton_title, ...)
     * @param null $lang
     * @return WP_Post[]|false
     */
    public static function get_cantons( $lang = null )
    {
        global $wpdb;

        if ( null === $lang ) {
            $lang = FQP::get_curr_lang();;
        }
        $lang = esc_sql( $lang );

        $sql_tpl = /** @lang text */"
                SELECT `cd`.`canton_ID`, `cd`.`title`
                FROM `{$wpdb->prefix}swiss_canton_data` as `cd`
                JOIN `{$wpdb->prefix}swiss_canton` as `c` ON `c`.`ID` = `cd`.`canton_ID`
                WHERE `cd`.`lang` = '{lang}'
                ORDER BY `c`.`sorter` ASC
            ";

        $cantons =  $wpdb->get_results( str_replace( '{lang}', $lang, $sql_tpl ), ARRAY_A );
        if ( empty( $cantons ) ) {
            $cantons =  $wpdb->get_results( str_replace( '{lang}', 'de', $sql_tpl ), ARRAY_A );
        }

        if ( ! is_array( $cantons ) || empty( $cantons ) ) {
            return false;
        }

        $cantons = array_combine(
            array_column( $cantons, 'canton_ID' ),
            array_column( $cantons, 'title' )
        );

        return $cantons;
    }
}