<?php
namespace Core\App\Models;

class Footer
{
    protected string $table = 'footer_sections';
    private static ?string $linksSectionColumn = null;

    private function getLinksSectionColumn(): string
    {
        if (self::$linksSectionColumn !== null) {
            return self::$linksSectionColumn;
        }

        $cols = db()->query("SHOW COLUMNS FROM footer_links")->fetchAll();

        $fields = [];
        foreach ($cols as $col) {
            if (isset($col['Field'])) {
                $fields[] = $col['Field'];
            }
        }

        $candidates = ['footer_section_id', 'section_id', 'footer_id'];

        foreach ($candidates as $candidate) {
            if (in_array($candidate, $fields, true)) {
                self::$linksSectionColumn = $candidate;
                return self::$linksSectionColumn;
            }
        }

        self::$linksSectionColumn = 'footer_section_id';
        return self::$linksSectionColumn;
    }

    public function getAll(): array
    {
        return db()->query("
            SELECT *
            FROM {$this->table}
            ORDER BY position ASC, id ASC
        ")->fetchAll();
    }

    public function getActiveSectionsWithLinks(): array
    {
        $sections = db()->query("
            SELECT *
            FROM {$this->table}
            WHERE status = 1
            ORDER BY position ASC, id ASC
        ")->fetchAll();

        $col = $this->getLinksSectionColumn();

        foreach ($sections as &$section) {
            if (($section['type'] ?? 'links') === 'links' || ($section['type'] ?? '') === 'pages') {
                $stmt = db()->prepare("
                    SELECT *
                    FROM footer_links
                    WHERE {$col} = ?
                      AND is_enabled = 1
                    ORDER BY position ASC, id ASC
                ");
                $stmt->execute([$section['id']]);
                $section['links'] = $stmt->fetchAll();
            } else {
                $section['links'] = [];
            }
        }

        return $sections;
    }

    public function find(int $id): ?array
    {
        $stmt = db()->prepare("
            SELECT *
            FROM {$this->table}
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): bool
    {
        return db()->prepare("
            INSERT INTO {$this->table} (title, type, status, position)
            VALUES (?, ?, ?, ?)
        ")->execute([
            $data['title'],
            $data['type'] ?? 'links',
            $data['status'] ?? 1,
            $data['position'] ?? 0
        ]);
    }

    public function update(int $id, array $data): bool
    {
        $current = $this->find($id);
        if (!$current) {
            return false;
        }

        $type = array_key_exists('type', $data)
            ? $data['type']
            : $current['type'];

        return db()->prepare("
            UPDATE {$this->table}
            SET
                title = ?,
                type = ?,
                status = ?,
                position = ?
            WHERE id = ?
        ")->execute([
            $data['title'] ?? $current['title'],
            $type,
            $data['status'] ?? $current['status'],
            $data['position'] ?? $current['position'],
            $id
        ]);
    }

    /**
     * تحديث ترتيب القسم فقط (خاص بـ Drag & Drop)
     */
    public function updatePosition(int $id, int $position): bool
    {
        return db()->prepare("
            UPDATE {$this->table}
            SET position = ?
            WHERE id = ?
        ")->execute([$position, $id]);
    }

    public function delete(int $id): bool
    {
        $col = $this->getLinksSectionColumn();

        db()->prepare("
            DELETE FROM footer_links
            WHERE {$col} = ?
        ")->execute([$id]);

        return db()->prepare("
            DELETE FROM {$this->table}
            WHERE id = ?
        ")->execute([$id]);
    }

    public function getLinks(int $sectionId): array
    {
        $col = $this->getLinksSectionColumn();

        $stmt = db()->prepare("
            SELECT *
            FROM footer_links
            WHERE {$col} = ?
            ORDER BY position ASC, id ASC
        ");
        $stmt->execute([$sectionId]);
        return $stmt->fetchAll();
    }
}
