<?php

/**
 * Matomo - free/libre analytics platform
 *
 * @link    https://matomo.org
 * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
 */
namespace Piwik\Plugins\GeoIp2\Commands;

use Piwik\Common;
use Piwik\Db;
use Piwik\DbHelper;
use Piwik\Option;
use Piwik\Plugin\ConsoleCommand;
use Piwik\Plugins\UserCountry\LocationProvider;
use Piwik\Plugins\GeoIp2\LocationProvider\GeoIp2;
class ConvertRegionCodesToIso extends ConsoleCommand
{
    public const OPTION_NAME = 'regioncodes_converted';
    public const MAPPING_TABLE_NAME = 'fips2iso';
    protected function configure()
    {
        $this->setName('usercountry:convert-region-codes');
        $this->setDescription("Convert FIPS region codes saved by GeoIP legacy provider to ISO.");
    }
    public function isEnabled()
    {
        return LocationProvider::getCurrentProvider() instanceof GeoIp2;
    }
    protected function doExecute() : int
    {
        $output = $this->getOutput();
        // chick if option is set to disable second run
        if (Option::get(self::OPTION_NAME)) {
            $output->writeln('Converting region codes already done.');
            return self::SUCCESS;
        }
        $output->setDecorated(\true);
        $output->write('Creating mapping table in database');
        Db::query('DROP table if exists ' . self::MAPPING_TABLE_NAME);
        DbHelper::createTable(self::MAPPING_TABLE_NAME, "`country_code` VARCHAR(2) NOT NULL,\n                           `fips_code` VARCHAR(2) NOT NULL,\n                           `iso_code` VARCHAR(4) NULL DEFAULT NULL,\n                           PRIMARY KEY (`country_code`, `fips_code`)");
        $output->writeln(' <fg=green>✓</>');
        $mappings = (include __DIR__ . '/../data/regionMapping.php');
        $output->write('Inserting mapping data ');
        $counter = 0;
        foreach ($mappings as $country => $regionMapping) {
            foreach ($regionMapping as $fips => $iso) {
                if ($fips == $iso) {
                    continue;
                    // nothing needs to be changed, so ignore the mapping
                }
                Db::query('INSERT INTO `' . Common::prefixTable(self::MAPPING_TABLE_NAME) . '` VALUES (?, ?, ?)', [$country, $fips, $iso]);
                $counter++;
                if ($counter % 50 == 0) {
                    $output->write('.');
                }
            }
        }
        $output->writeln(' <fg=green>✓</>');
        $output->writeln('Updating Matomo log tables:');
        $activationTime = Option::get(GeoIp2::SWITCH_TO_ISO_REGIONS_OPTION_NAME);
        $activationDateTime = date('Y-m-d H:i:s', $activationTime);
        // fix country and region of tibet so it will be updated correctly afterwards
        $tibetFixQuery = 'UPDATE `%s` SET location_country = "cn", location_region = "14" WHERE location_country = "ti"';
        // replace invalid country codes used by GeoIP Legacy
        $fixInvalidCountriesQuery = 'UPDATE `%s` SET location_country = "" WHERE location_country IN("AP", "EU", "A1", "A2")';
        $query = "UPDATE `%s` INNER JOIN %s ON location_country = country_code AND location_region = fips_code SET location_region = iso_code\n                  WHERE `%s` < ?";
        $logTables = ['log_visit' => 'visit_first_action_time', 'log_conversion' => 'server_time'];
        foreach ($logTables as $logTable => $dateField) {
            $output->write('- Updating ' . $logTable);
            Db::query(sprintf($tibetFixQuery, Common::prefixTable($logTable)));
            Db::query(sprintf($fixInvalidCountriesQuery, Common::prefixTable($logTable)));
            $sql = sprintf($query, Common::prefixTable($logTable), Common::prefixTable(self::MAPPING_TABLE_NAME), $dateField);
            Db::query($sql, $activationDateTime);
            $output->writeln(' <fg=green>✓</>');
        }
        $output->write('Removing mapping table from database ');
        Db::dropTables(Common::prefixTable(self::MAPPING_TABLE_NAME));
        $output->writeln(' <fg=green>✓</>');
        // save option to prevent a second run
        Option::set(self::OPTION_NAME, \true);
        $output->writeln('All region codes converted.');
        return self::SUCCESS;
    }
}
