// Convert from API response like '2022-01-14T13:45:47+00:00' to database native format
// 
// @param string $fieldName Field name to be converted
// @param string|null $date Date string to be converted
// @param string $databaseDatetimeFormat Database datetime format
// @return array [fieldName, convertedValue]
function dateConverter(string $fieldName, ?string $date, string $databaseDatetimeFormat): array
{
    $converted = null;
    if ($date !== null) {
        $tmp = new DateTimeImmutable($date);
        $converted = $tmp->format($databaseDatetimeFormat);
    }
    return [$fieldName, $converted];
}

// Fetch data from Kimai API
//
// @param Client $client Guzzle client
// @param string $endpoint API endpoint to fetch
// @return array|false Array of data or false on error
function doGet(Client $client, string $endpoint): array|false
{
    $response = $client->get($endpoint);
    if ($response->getStatusCode() === 404) {
        return false;
    }
    return json_decode($response->getBody()->getContents(), true);
}

/**
 * Sync data from Kimai API to local database
 * 
 * @param callable $writeLn callable Output one line of text with some status updates
 * @param callable $writeLnSuccess callable Output one line of text with some status updates, indicated successful part
 * @param callable $writeLnError callable Output one line of text with some status updates, indicated error part
 * @param bool $onlyTimesheets Only sync timesheets (for hourly cronjob)
 * @param DateTimeImmutable $modifiedSince Only timesheets that were modified after this date will be synced, by default latest 24 hours
 * 
 * Throws Exception on inescaple errors
 */
function kimaiSync(
    callable $writeLn,
    callable $writeLnSuccess,
    callable $writeLnError,
    bool $onlyTimesheets = false,
    DateTimeImmutable $modifiedSince = new DateTimeImmutable('-24 hours'),
    string $kimaiApiUrl,
    string $kimaiApiToken,
    string $databaseConnection,
    string $databaseUser,
    string $databasePassword,
    string $databaseTableFormat = '`%s`',
    string $databaseColumnFormat = '`%s`',
    string $databaseDatetimeFormat = 'Y-m-d H:i:s',
    string $proxyUrl = ''
) {
    // Setup database connection
    $connection = new PDO($databaseConnection, $databaseUser, $databasePassword);

    // Setup HTTP client
    $clientOptions = [
        'base_uri' => $kimaiApiUrl,
        'verify' => false,
        'headers' => ['Authorization' => 'Bearer ' . $kimaiApiToken],
    ];
    if (defined('PROXY_URL') && !empty($proxyUrl)) {
        $clientOptions['proxy'] = $proxyUrl;
    }
    $client = new Client($clientOptions);

    // Reusable function to sync one endpoint
    $syncEndpoint = function ($title, $settings) use ($connection, $client, $writeLn, $writeLnSuccess, $writeLnError, $databaseTableFormat, $databaseColumnFormat): void {
        $apiEntities = [];
        $existingEntities = []; // mapping local id to kimai id in local database
        $localColumns = []; // column names on local side to prepare SQL statements

        // fetch the API result
        $results = doGet($client, $settings['endpoint']);
        if ($results === false) {
            $writeLnError(sprintf('Failed to sync data for endpoint: %s', $settings['endpoint']));
        }

        // prepare the array of all entities for the local database by mapping columns
        foreach ($results as $entity) {
            $newEntity = [];
            foreach ($settings['mapping'] as $kimaiField => $localField) {
                $key = $localField;
                $value = $entity[$kimaiField];
                // some values need to be converted to local format (eg. datetime)
                if (is_callable($localField)) {
                    $tmp = call_user_func($localField, $entity, $kimaiField);
                    $key = $tmp[0];
                    $value = $tmp[1];
                }
                $newEntity[$key] = $value;
            }
            if (count($localColumns) === 0) {
                $localColumns = array_keys($newEntity);
            }
            $apiEntities[$entity['id']] = $newEntity;
        }

        unset($results);

        if (count($apiEntities) === 0) {
            $writeLnSuccess('No data found to sync: ' . $title);
            return;
        }

        // convert all column names using configured format (e.g. prefix/suffix)
        $localColumns = array_map(function ($columnName) use ($databaseColumnFormat) {
            return sprintf($databaseColumnFormat, $columnName);
        }, $localColumns);

        // convert table name using configured format (e.g. prefix/suffix)
        $tableName = sprintf($databaseTableFormat, $settings['table']);

        // fetch all existing entries to decide if we update or insert
        $sql = sprintf('SELECT id, kimai_id FROM %s WHERE kimai_id IN (%s)', $tableName, implode(',', array_keys($apiEntities)));
        $stmt = $connection->prepare($sql);
        try {
            if ($stmt->execute() === false) {
                $writeLnError($sql);
            }
        } catch (Exception $ex) {
            $writeLnError($sql . PHP_EOL . $ex->getMessage());
        }
        $existing = $stmt->fetchAll();
        foreach ($existing as $existingValues) {
            $existingEntities[$existingValues['kimai_id']] = $existingValues['id'];
        }

        // prepare the insert statement
        $columnsReplacer = [];
        for ($i = 0; $i < count($localColumns); $i++) {
            $columnsReplacer[] = '?';
        }
        $sqlInsert = sprintf('INSERT INTO %s (%s) VALUES (%s)', $tableName, implode(',', $localColumns), implode(',', $columnsReplacer));
        $stmtInsert = $connection->prepare($sqlInsert);

        // prepare the update statement
        $columnsReplacer = [];
        foreach ($localColumns as $localField) {
            $columnsReplacer[] = $localField . ' = ?';
        }
        $sqlUpdate = sprintf('UPDATE %s SET %s WHERE id = ?', $tableName, implode(',', $columnsReplacer));
        $stmtUpdate = $connection->prepare($sqlUpdate);

        foreach ($apiEntities as $kimaiId => $values) {
            if (array_key_exists($kimaiId, $existingEntities)) {
                $values[] = $existingEntities[$kimaiId];
                if ($stmtUpdate->execute(array_values($values)) === false) {
                    $writeLnError(sprintf('Failed updating "%s" for ID "%s" with: %s', $tableName, $existingEntities[$kimaiId], $stmtUpdate->errorInfo()[2]));
                }
            } else {
                if ($stmtInsert->execute(array_values($values)) === false) {
                    $writeLnError(sprintf('Failed inserting into "%s" with: %s', $tableName, $stmtInsert->errorInfo()[2]));
                }
            }
        }
        $writeLnSuccess('Synced ' . $title . ': ' . count($apiEntities));
    };

    $syncConfig = [
        'Customer' => [
            'table' => 'customer',
            'endpoint' => 'customers',
            'mapping' => [
                'id' => 'kimai_id',
                'name' => 'name',
                'number' => 'number',
            ],
        ],
        'Projects' => [
            'table' => 'project',
            'endpoint' => 'projects',
            'mapping' => [
                'id' => 'kimai_id',
                'customer' => 'customer',
                'name' => 'name',
                'start' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                    return dateConverter('start', $entity[$fieldName], $databaseDatetimeFormat);
                },
                'end' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                    return dateConverter('end', $entity[$fieldName], $databaseDatetimeFormat);
                },
            ],
        ],
        'Activities' => [
            'table' => 'activity',
            'endpoint' => 'activities',
            'mapping' => [
                'id' => 'kimai_id',
                'project' => 'project',
                'name' => 'name',
            ],
        ],
        'Users' => [
            'table' => 'user',
            'endpoint' => 'users',
            'mapping' => [
                'id' => 'kimai_id',
                'alias' => 'alias',
                'username' => 'username',
            ],
        ],
        'Teams' => [
            'table' => 'team',
            'endpoint' => 'teams',
            'mapping' => [
                'id' => 'kimai_id',
                'name' => 'name',
            ],
        ],
    ];

    if ($onlyTimesheets) {
        $syncConfig = [];
    }

    $syncConfig['Timesheets'] = [
        'table' => 'timesheet',
        'endpoint' => 'timesheets?user=all&modified_after=' . $modifiedSince->format('Y-m-d\TH:i:s') . '&size=' . PHP_INT_MAX,
        'mapping' => [
            'id' => 'kimai_id',
            'activity' => 'activity',
            'project' => 'project',
            'user' => 'user',
            'begin' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                return dateConverter('begin', $entity[$fieldName], $databaseDatetimeFormat);
            },
            'end' => function ($entity, $fieldName) use ($databaseDatetimeFormat) {
                return dateConverter('end', $entity[$fieldName], $databaseDatetimeFormat);
            },
            'duration' => 'duration',
            'description' => function ($entity, $fieldName) {
                $value = $entity[$fieldName];
                if ($value !== null && mb_strlen($value) > 200) {
                    $value = mb_substr($value, 0, 200);
                }
                return ['description', $value];
            },
            'rate' => 'rate',
            'internalRate' => 'internalRate',
            'billable' => function ($entity, $fieldName) {
                $value = 1;
                if (!$entity[$fieldName]) {
                    $value = 0;
                }
                return ['billable', $value];
            },
        ],
    ];

    foreach ($syncConfig as $title => $settings)
    {
        $syncEndpoint($title, $settings);
    }

    if ($onlyTimesheets) {
        return;
    }

    // SPECIAL HANDLING FOR TEAMS
    $teamTable = sprintf($databaseTableFormat, 'team');
    $stmt = $connection->prepare(sprintf('SELECT id, kimai_id FROM %s', $teamTable));
    $stmt->execute();
    $teams = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $teamProjects = [];
    $teamUsers = [];
    $deleteIds = [];

    $writeLn('Syncing teams, user and project links ...');
    $totalCount = count($teams);
    $doneCount = 0;
    // $progress = new ProgressBar($output, count($teams)); // not available in this context

    foreach ($teams as $team) {
        $kimaiTeamId = $team['kimai_id'];
        $teamId = $team['id'];

        try {
            $team = doGet($client, 'teams/' . $kimaiTeamId);
        } catch (ClientException $ex) {
            if ($ex->getResponse()->getStatusCode() === 404) {
                $deleteIds[] = $teamId;
                continue;
            }
        }

        foreach ($team['members'] as $member) {
            $teamUsers[$kimaiTeamId][] = $member['user']['id'];
        }
        foreach ($team['projects'] as $project) {
            $teamProjects[$kimaiTeamId][] = $project['id'];
        }

        usleep(500); // be polite and do not overstress remote Server/API
        $doneCount++;
        // $progress->advance(); // not available in this context
        $writeLn(sprintf('Syncing teams, user and project links ... %s/%s', $doneCount, $totalCount));
    }
    // $progress->finish(); // not available in this context

    foreach ($deleteIds as $deleteId) {
        // make sure table is always empty before inserting the relations between user and team
        $stmt = $connection->prepare('DELETE FROM team WHERE id = ' . $deleteId);
        $stmt->execute();
    }

    // make sure table is always empty before inserting the relations between user and team
    $teamUserTable = sprintf($databaseTableFormat, 'team_user');
    $stmt = $connection->prepare(sprintf('DELETE FROM %s', $teamUserTable));
    $stmt->execute();

    $stmt = $connection->prepare(sprintf('INSERT INTO %s (team_kimai_id, user_kimai_id) VALUES (?, ?)', $teamUserTable));
    foreach ($teamUsers as $kimaiTeamId => $kimaiUserIds) {
        foreach ($kimaiUserIds as $kimaiUserId) {
            if ($stmt->execute([$kimaiTeamId, $kimaiUserId]) === false) {
                $writeLnError(sprintf('Failed inserting into "team_user" with: %s', $stmt->errorInfo()[2]));
            }
        }
    }

    // make sure table is always empty before inserting the relations between project and team
    $teamProjectTable = sprintf($databaseTableFormat, 'team_project');
    $stmt = $connection->prepare(sprintf('TRUNCATE %s', $teamProjectTable));
    $stmt->execute();

    $stmt = $connection->prepare(sprintf('INSERT INTO %s (team_kimai_id, project_kimai_id) VALUES (?, ?)', $teamProjectTable));
    foreach ($teamProjects as $kimaiTeamId => $kimaiProjectIds) {
        foreach ($kimaiProjectIds as $kimaiProjectId) {
            if ($stmt->execute([$kimaiTeamId, $kimaiProjectId]) === false) {
                $writeLnError(sprintf('Failed inserting into "team_project" with: %s', $stmt->errorInfo()[2]));
            }
        }
    }
}

// Call function connected from pmt-slim configuration
kimaiSync(
    writeLn: function(string $line) {
        echo $line . PHP_EOL;
    },
    writeLnSuccess: function(string $line) {
        echo '✅ ' . $line . PHP_EOL;
    },
    writeLnError: function(string $line) {
        echo '❌ ' . $line . PHP_EOL;
    },
    onlyTimesheets: false,
    modifiedSince: new DateTimeImmutable('-24 hours'),
    kimaiApiUrl: $_ENV['KIMAI_API_URL'],
    kimaiApiToken: $_ENV['KIMAI_API_TOKEN'],
    databaseConnection: $_ENV['DB_DSN'],
    databaseUser: $_ENV['DB_USER'],
    databasePassword: $_ENV['DB_PASS'],
    databaseTableFormat: $_ENV['KIMAI_DB_TABLE_FORMAT']
);

// Command application wrapper for the kimaiSync function using config from configuration.local.php or configuration.php
(new SingleCommandApplication())
    ->setName('Sync Kimai data via API')
    ->setVersion('1.0')
    ->addOption('timesheets', null, InputOption::VALUE_NONE, 'Only sync timesheets (for hourly cronjob)')
    ->addOption('modified', null, InputOption::VALUE_REQUIRED, 'Only timesheets that were modified after this date will be synced, by default latest 24 hours. Format: 2022-01-14 13:45:47')
    ->setCode(function (InputInterface $input, OutputInterface $output): int {

        $io = new SymfonyStyle($input, $output);

        $modifiedSince = $input->getOption('modified');
        if ($modifiedSince === null) {
            $modifiedSince = new DateTimeImmutable('-24 hours');
        } else {
            try {
                $modifiedSince = new DateTimeImmutable($modifiedSince);
            } catch (Exception $ex) {
                $io->error('Invalid "since" date given, please check your format.');
                return 1;
            }
        }

        kimaiSync(
            writeLn: function(string $line) use ($io) {
                $io->writeln($line);
            },
            writeLnSuccess: function(string $line) use ($io) {
                $io->success($line);
            },
            writeLnError: function(string $line) use ($io) {
                $io->error($line);
            },
            onlyTimesheets: $input->getOption('timesheets'),
            modifiedSince: $modifiedSince,
            kimaiApiUrl: KIMAI_API_URL,
            kimaiApiToken: KIMAI_API_TOKEN,
            databaseConnection: DATABASE_CONNECTION,
            databaseUser: DATABASE_USER,
            databasePassword: DATABASE_PASSWORD,
            databaseTableFormat: DATABASE_COLUMN,
            databaseColumnFormat: DATABASE_COLUMN,
            databaseDatetimeFormat: DATABASE_DATETIME_FORMAT
        );
        return 0;
    })
    ->run();