sam-api/app/Console/Commands/ManageAuditPartitions.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class ManageAuditPartitions extends Command
{
    protected $signature = 'audit:partitions
        {--add-months=3 : 미래 파티션 추가 개월 수}
        {--retention-months=13 : 보관 기간 (개월)}
        {--drop : 보관 기간 초과 파티션 삭제 실행}
        {--dry-run : 변경 없이 계획만 출력}';

    protected $description = '트리거 감사 로그 파티션 자동 관리 (추가/삭제)';

    public function handle(): int
    {
        $addMonths = (int) $this->option('add-months');
        $retentionMonths = (int) $this->option('retention-months');
        $doDrop = $this->option('drop');
        $dryRun = $this->option('dry-run');

        $this->info('=== 트리거 감사 로그 파티션 관리 ===');
        $this->newLine();

        // 현재 파티션 목록 조회
        $partitions = $this->getPartitions();
        $this->info('현재 파티션: '.count($partitions).'개');
        $this->table(
            ['파티션명', '상한값 (UNIX_TIMESTAMP)', '행 수'],
            collect($partitions)->map(fn ($p) => [
                $p->PARTITION_NAME,
                $p->PARTITION_DESCRIPTION === 'MAXVALUE' ? 'MAXVALUE' : Carbon::createFromTimestamp($p->PARTITION_DESCRIPTION)->format('Y-m-d'),
                number_format($p->TABLE_ROWS),
            ])->toArray()
        );

        $this->newLine();

        // 1. 미래 파티션 추가
        $added = $this->addFuturePartitions($partitions, $addMonths, $dryRun);

        // 2. 오래된 파티션 삭제
        $dropped = 0;
        if ($doDrop) {
            $dropped = $this->dropOldPartitions($partitions, $retentionMonths, $dryRun);
        } else {
            $this->warn('파티션 삭제는 --drop 옵션 필요');
        }

        $this->newLine();
        $this->info("결과: 추가 {$added}개, 삭제 {$dropped}개".($dryRun ? ' (dry-run)' : ''));

        return self::SUCCESS;
    }

    private function getPartitions(): array
    {
        return DB::select("
            SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS
            FROM INFORMATION_SCHEMA.PARTITIONS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'trigger_audit_logs'
            AND PARTITION_NAME IS NOT NULL
            ORDER BY PARTITION_ORDINAL_POSITION
        ", [config('database.connections.mysql.database')]);
    }

    private function addFuturePartitions(array $partitions, int $addMonths, bool $dryRun): int
    {
        $existingBounds = [];
        foreach ($partitions as $p) {
            if ($p->PARTITION_DESCRIPTION !== 'MAXVALUE') {
                $existingBounds[] = (int) $p->PARTITION_DESCRIPTION;
            }
        }

        $added = 0;
        $now = Carbon::now();

        for ($i = 0; $i <= $addMonths; $i++) {
            $target = $now->copy()->addMonths($i)->startOfMonth()->addMonth();
            $ts = $target->timestamp;
            $name = 'p'.$target->copy()->subMonth()->format('Ym');

            if (in_array($ts, $existingBounds)) {
                continue;
            }

            $sql = "ALTER TABLE trigger_audit_logs REORGANIZE PARTITION p_future INTO (
                PARTITION {$name} VALUES LESS THAN ({$ts}),
                PARTITION p_future VALUES LESS THAN MAXVALUE
            )";

            if ($dryRun) {
                $this->line("  [DRY-RUN] 추가: {$name} (< {$target->format('Y-m-d')})");
            } else {
                DB::statement($sql);
                $this->info("  추가: {$name} (< {$target->format('Y-m-d')})");
            }
            $added++;
        }

        if ($added === 0) {
            $this->info('  추가할 파티션 없음');
        }

        return $added;
    }

    private function dropOldPartitions(array $partitions, int $retentionMonths, bool $dryRun): int
    {
        $cutoff = Carbon::now()->subMonths($retentionMonths)->startOfMonth()->timestamp;
        $dropped = 0;

        foreach ($partitions as $p) {
            if ($p->PARTITION_DESCRIPTION === 'MAXVALUE') {
                continue;
            }

            $bound = (int) $p->PARTITION_DESCRIPTION;
            if ($bound <= $cutoff) {
                if ($dryRun) {
                    $this->line("  [DRY-RUN] 삭제: {$p->PARTITION_NAME} ({$p->TABLE_ROWS}행)");
                } else {
                    DB::statement("ALTER TABLE trigger_audit_logs DROP PARTITION {$p->PARTITION_NAME}");
                    $this->warn("  삭제: {$p->PARTITION_NAME} ({$p->TABLE_ROWS}행)");
                }
                $dropped++;
            }
        }

        if ($dropped === 0) {
            $this->info('  삭제할 파티션 없음');
        }

        return $dropped;
    }
}