<?php
/**
 * ITSWEBER CMS – Section Revision Model
 *
 * Stores content snapshots for page builder sections.
 * Limited to MAX_REVISIONS per section (FIFO cleanup).
 *
 * @author    Benny Weber (ITSWEBER)
 * @copyright © ITSWEBER – www.itsweber.de
 * @contact   kontakt@itsweber.de
 */

namespace App\Models;

use App\Core\Model;

class SectionRevision extends Model
{
    protected static string $table = 'iw_section_revisions';

    private const MAX_REVISIONS = 10;

    private static ?bool $tableExistsCache = null;

    /**
     * Check if the revisions table exists (cached per request).
     */
    private static function tableExists(): bool
    {
        if (static::$tableExistsCache !== null) {
            return static::$tableExistsCache;
        }

        try {
            static::db()->query("SELECT 1 FROM " . static::$table . " LIMIT 1");
            static::$tableExistsCache = true;
        } catch (\Throwable $e) {
            static::$tableExistsCache = false;
        }

        return static::$tableExistsCache;
    }

    /**
     * Store a revision before updating a section.
     * Automatically enforces the FIFO limit.
     */
    public static function store(int $sectionId, string $content, ?string $changedBy = null): int
    {
        if (!static::tableExists()) {
            return 0;
        }

        $id = static::create([
            'section_id' => $sectionId,
            'content'    => $content,
            'changed_by' => $changedBy,
        ]);

        static::enforceLimit($sectionId);

        return $id;
    }

    /**
     * Get all revisions for a section, newest first.
     */
    public static function forSection(int $sectionId): array
    {
        if (!static::tableExists()) {
            return [];
        }

        return static::db()->fetchAll(
            "SELECT * FROM " . static::$table
            . " WHERE section_id = :sid ORDER BY created_at DESC",
            ['sid' => $sectionId]
        );
    }

    /**
     * Count revisions for a section.
     */
    public static function countForSection(int $sectionId): int
    {
        if (!static::tableExists()) {
            return 0;
        }

        return (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM " . static::$table . " WHERE section_id = :sid",
            ['sid' => $sectionId]
        );
    }

    /**
     * Delete all revisions for a section (cleanup on section delete).
     */
    public static function deleteForSection(int $sectionId): void
    {
        if (!static::tableExists()) {
            return;
        }

        static::db()->query(
            "DELETE FROM " . static::$table . " WHERE section_id = :sid",
            ['sid' => $sectionId]
        );
    }

    /**
     * Keep only the newest MAX_REVISIONS per section.
     */
    private static function enforceLimit(int $sectionId): void
    {
        $count = static::countForSection($sectionId);

        if ($count <= self::MAX_REVISIONS) {
            return;
        }

        $excess = $count - self::MAX_REVISIONS;

        // Delete oldest revisions
        $oldest = static::db()->fetchAll(
            "SELECT id FROM " . static::$table
            . " WHERE section_id = :sid ORDER BY created_at ASC LIMIT " . (int) $excess,
            ['sid' => $sectionId]
        );

        foreach ($oldest as $row) {
            static::deleteById((int) $row['id']);
        }
    }
}
