<?php

namespace App\Service\Cron\Job;

use App\Service\Cron\BaseCronJob;
use App\Service\Cron\CronjobInterface;
use App\Service\Utils\DatesUtils;
use DateTime;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DBALException;
use Exception;
use JetBrains\PhpStorm\Pure;

/**
 * Cronjob that delete records older than N days in DB tables like Audit_ or Message
 *
 * >>> ot:cron clean-db
 */
// TODO: voir si ajouter :
// * Information (282.000 lignes)? (avec NotificationUser, 169.000 lignes)
// * MigrationLog (972.000 lignes)?
// * ReportMessage? (4.500.000 lignes)

// TODO: table potentiellement à supprimer dans la foulée : AccessTmp, Person_ori, Person_save, MigrationLog

class CleanDb extends BaseCronJob implements CronjobInterface
{
    /**
     * Delay before deleting the record of the temporary file from the DB
     */
    private const PURGE_RECORDS_OLDER_THAN = 365;

    /**
     * @param Connection $connection
     */
    #[Pure]
    public function __construct(
        private Connection $connection
    ) {
        parent::__construct();
    }

    /**
     * Preview the result of the execution, without actually deleting anything
     * @throws Exception
     */
    public function preview(): void
    {
        $this->purgeDb(false);
    }

    /**
     * Proceed to the deletion of the files and the purge of the DB
     * @throws Exception
     */
    public function execute(): void
    {
        $this->purgeDb();
    }

    /**
     * Purge the DB from temporary file records older than N days
     *
     * @param bool $commit
     * @throws Exception
     * @throws \Doctrine\DBAL\Driver\Exception
     */
    protected function purgeDb(bool $commit = true): void
    {
        $maxDate = DatesUtils::new();
        $maxDate->sub(new \DateInterval('P' . self::PURGE_RECORDS_OLDER_THAN . 'D'));

        $this->ui->print('Purge DB from temporary records modified before ' . $maxDate->format('c'));
        $this->connection->beginTransaction();

        $purged = 0;

        $purged += $this->purgeAuditTables($maxDate);
//        $purged += $this->purgeMessages($maxDate);
        $purged += $this->purgeLoginLog($maxDate);

        if ($commit) {
            $this->connection->commit();
            $this->ui->print('DB purged - ' . $purged . ' records permanently deleted');
            return;
        }

        $this->connection->rollback();
        $this->ui->print('DB purged - ' . $purged . ' records would be permanently deleted');
    }

    /**
     * Purge Access tables and returns the number of deleted records
     *
     * @param DateTime $maxDate
     * @return int
     * @throws DBALException
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    protected function purgeAuditTables(DateTime $maxDate): int
    {
        $this->ui->print('Purge Audit tables');

        $tableNames = $this->connection->getSchemaManager()->listTableNames();

        $total = 0;

        foreach (array_values($tableNames) as $tableName) {
            if (preg_match("/Audit_\w+/", $tableName)) {
                /** @noinspection SqlWithoutWhere */
                $sql = "DELETE a, r 
                     FROM opentalent.{$tableName} a
                     INNER JOIN opentalent.revisions r ON r.id = a.rev
                     WHERE r.timestamp < :maxDate;";

                $stmt = $this->connection->prepare($sql);
                $purged = $stmt->executeStatement(['maxDate' => $maxDate->format('Y-m-d')]);

                $this->ui->print('* ' . $tableName . ' : ' . $purged . ' lines to delete');

                $total += $purged;
            }
        }

        return $total;
    }

    /**
     * Purge Message table and returns the number of deleted records
     * TODO: à confirmer
     *
     * @param DateTime $maxDate
     * @return int
     * @throws \Doctrine\DBAL\Exception
     */
    protected function purgeMessages(DateTime $maxDate): int
    {
        $q = $this->connection->createQueryBuilder();
        $q->delete('Message')->where($q->expr()->lt('dateSent', $maxDate->format('Y-m-d')));

        // TODO: tester si les tables message_documents et message_files sont bien nettoyées en cascade ou s'il faut
        //       le faire manuellement
        return $q->execute();
    }

    /**
     * Purge LoginLog table and returns the number of deleted records
     *
     * @param DateTime $maxDate
     * @return int
     * @throws \Doctrine\DBAL\Exception
     */
    protected function purgeLoginLog(DateTime $maxDate): int
    {
        $q = $this->connection->createQueryBuilder();
        $q->delete('LoginLog')->where($q->expr()->lt('date', $maxDate->format('Y-m-d')));

        return $q->execute();
    }

}
