<?php
declare(strict_types=1);

namespace App\Models;

use PDO;

class Page extends BaseModel
{
    protected string $table = 'pages';

    public const STATUSES = ['draft', 'published', 'hidden', 'trashed'];

    public function listForAdmin(array $filters = [], int $limit = 120): array
    {
        $limit = min(max($limit, 1), 300);
        $statusFilter = (string) ($filters['status'] ?? '');
        $where = [];
        if ($statusFilter === 'trashed') {
            $where[] = '(p.deleted_at IS NOT NULL OR p.status = "trashed")';
        } else {
            $where[] = 'p.deleted_at IS NULL';
        }
        $params = [];

        if (!empty($filters['q'])) {
            $where[] = '(p.title LIKE :q_title OR p.slug LIKE :q_slug OR p.content LIKE :q_content)';
            $like = '%' . trim((string) $filters['q']) . '%';
            $params['q_title'] = $like;
            $params['q_slug'] = $like;
            $params['q_content'] = $like;
        }

        if (!empty($filters['language'])) {
            $where[] = 'p.language = :language';
            $params['language'] = substr((string) $filters['language'], 0, 2);
        }

        if ($statusFilter !== '' && in_array($statusFilter, self::STATUSES, true)) {
            $where[] = 'p.status = :status';
            $params['status'] = $statusFilter;
        }

        $statement = $this->db()->prepare(
            'SELECT p.*, cu.username AS created_by_username, uu.username AS updated_by_username
             FROM pages p
             LEFT JOIN users cu ON cu.id = p.created_by
             LEFT JOIN users uu ON uu.id = p.updated_by
             WHERE ' . implode(' AND ', $where) . '
             ORDER BY p.updated_at DESC, p.id DESC
             LIMIT :limit'
        );
        foreach ($params as $key => $value) {
            $statement->bindValue($key, $value);
        }
        $statement->bindValue('limit', $limit, PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function findForAdmin(int $id): ?array
    {
        $statement = $this->db()->prepare('SELECT * FROM pages WHERE id = :id LIMIT 1');
        $statement->execute(['id' => $id]);
        $page = $statement->fetch();

        return $page ?: null;
    }

    public function findPublishedBySlug(string $slug, string $language = 'tr'): ?array
    {
        $slug = $this->normalizeSlug($slug);
        $language = substr(preg_replace('/[^a-z]/', '', strtolower($language)) ?: 'tr', 0, 2);

        $statement = $this->db()->prepare(
            'SELECT *
             FROM pages
             WHERE slug = :slug
               AND language = :language
               AND status = "published"
               AND deleted_at IS NULL
             LIMIT 1'
        );
        $statement->execute(['slug' => $slug, 'language' => $language]);
        $page = $statement->fetch();

        if ($page) {
            return $page;
        }

        if ($language !== 'tr') {
            $statement->execute(['slug' => $slug, 'language' => 'tr']);
            $page = $statement->fetch();
        }

        return $page ?: null;
    }

    public function createFromAdmin(array $input, int $adminId): int
    {
        $data = $this->normalizeInput($input);
        $this->assertSlugAvailable($data['slug'], $data['language']);

        $statement = $this->db()->prepare(
            'INSERT INTO pages
                (slug, title, content, language, status, show_in_menu, show_in_header, show_in_footer, sort_order,
                 seo_title, seo_description, created_by, updated_by, created_at, updated_at, deleted_at)
             VALUES
                (:slug, :title, :content, :language, :status, :show_in_menu, :show_in_header, :show_in_footer, :sort_order,
                 :seo_title, :seo_description, :created_by, :updated_by, NOW(), NOW(), NULL)'
        );
        $statement->execute($data + [
            'created_by' => $adminId,
            'updated_by' => $adminId,
        ]);

        return (int) $this->db()->lastInsertId();
    }

    public function updateFromAdmin(int $id, array $input, int $adminId): void
    {
        $data = $this->normalizeInput($input);
        $this->assertSlugAvailable($data['slug'], $data['language'], $id);

        $statement = $this->db()->prepare(
            'UPDATE pages
             SET slug = :slug,
                 title = :title,
                 content = :content,
                 language = :language,
                 status = :status,
                 show_in_menu = :show_in_menu,
                 show_in_header = :show_in_header,
                 show_in_footer = :show_in_footer,
                 sort_order = :sort_order,
                 seo_title = :seo_title,
                 seo_description = :seo_description,
                 updated_by = :updated_by,
                 updated_at = NOW(),
                 deleted_at = CASE WHEN :status_for_deleted = "trashed" THEN COALESCE(deleted_at, NOW()) ELSE NULL END
             WHERE id = :id'
        );
        $statement->execute($data + [
            'updated_by' => $adminId,
            'status_for_deleted' => $data['status'],
            'id' => $id,
        ]);
    }

    public function setStatus(int $id, string $status, int $adminId): void
    {
        $status = in_array($status, self::STATUSES, true) ? $status : 'draft';
        $statement = $this->db()->prepare(
            'UPDATE pages
             SET status = :status,
                 updated_by = :updated_by,
                 updated_at = NOW(),
                 deleted_at = CASE WHEN :status_for_deleted = "trashed" THEN COALESCE(deleted_at, NOW()) ELSE NULL END
             WHERE id = :id'
        );
        $statement->execute([
            'status' => $status,
            'updated_by' => $adminId,
            'status_for_deleted' => $status,
            'id' => $id,
        ]);
    }

    public function deletePermanent(int $id): bool
    {
        $statement = $this->db()->prepare('DELETE FROM pages WHERE id = :id LIMIT 1');
        $statement->execute(['id' => $id]);

        return $statement->rowCount() > 0;
    }

    public function footerLinks(string $language = 'tr', int $limit = 8): array
    {
        return $this->publicLinks($language, 'show_in_footer', $limit);
    }

    public function menuLinks(string $language = 'tr', int $limit = 8): array
    {
        return $this->publicLinks($language, 'show_in_menu', $limit);
    }

    private function publicLinks(string $language, string $column, int $limit): array
    {
        $column = $column === 'show_in_footer' ? 'show_in_footer' : 'show_in_menu';
        $visibilityWhere = $column === 'show_in_footer'
            ? 'show_in_footer = 1'
            : '(show_in_menu = 1 OR show_in_header = 1)';
        $limit = min(max($limit, 1), 20);
        $statement = $this->db()->prepare(
            "SELECT title, slug
             FROM pages
             WHERE language = :language
               AND status = 'published'
               AND deleted_at IS NULL
               AND {$visibilityWhere}
             ORDER BY sort_order ASC, title ASC
             LIMIT :limit"
        );
        $statement->bindValue('language', substr(strtolower($language), 0, 2));
        $statement->bindValue('limit', $limit, PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    private function normalizeInput(array $input): array
    {
        $title = trim((string) ($input['title'] ?? ''));
        $content = trim((string) ($input['content'] ?? ''));
        $language = substr(preg_replace('/[^a-z]/', '', strtolower((string) ($input['language'] ?? 'tr'))) ?: 'tr', 0, 2);
        $slug = $this->normalizeSlug((string) ($input['slug'] ?? $title));
        $status = (string) ($input['status'] ?? 'draft');

        if ($title === '' || $content === '' || $slug === '') {
            throw new \InvalidArgumentException('Sayfa basligi, slug ve icerik zorunludur.');
        }

        if (!in_array($status, self::STATUSES, true)) {
            $status = 'draft';
        }

        return [
            'slug' => $slug,
            'title' => function_exists('mb_substr') ? mb_substr($title, 0, 180) : substr($title, 0, 180),
            'content' => $content,
            'language' => $language,
            'status' => $status,
            'show_in_menu' => $this->truthy($input['show_in_menu'] ?? 0) ? 1 : 0,
            'show_in_header' => $this->truthy($input['show_in_header'] ?? $input['show_in_menu'] ?? 0) ? 1 : 0,
            'show_in_footer' => $this->truthy($input['show_in_footer'] ?? 0) ? 1 : 0,
            'sort_order' => max(0, min(999999, (int) ($input['sort_order'] ?? 0))),
            'seo_title' => trim((string) ($input['seo_title'] ?? '')) ?: null,
            'seo_description' => trim((string) ($input['seo_description'] ?? '')) ?: null,
        ];
    }

    private function assertSlugAvailable(string $slug, string $language, ?int $exceptId = null): void
    {
        $sql = 'SELECT id FROM pages WHERE slug = :slug AND language = :language';
        $params = ['slug' => $slug, 'language' => $language];
        if ($exceptId !== null) {
            $sql .= ' AND id <> :except_id';
            $params['except_id'] = $exceptId;
        }
        $sql .= ' LIMIT 1';

        $statement = $this->db()->prepare($sql);
        $statement->execute($params);

        if ($statement->fetchColumn()) {
            throw new \InvalidArgumentException('Bu slug ve dil icin sayfa zaten var.');
        }
    }

    private function normalizeSlug(string $value): string
    {
        $value = strtolower(trim($value));
        $map = ['ı' => 'i', 'ğ' => 'g', 'ü' => 'u', 'ş' => 's', 'ö' => 'o', 'ç' => 'c'];
        $value = strtr($value, $map);
        $value = preg_replace('/[^a-z0-9]+/', '-', $value) ?: '';

        return trim($value, '-');
    }

    private function truthy(mixed $value): bool
    {
        return in_array(strtolower((string) $value), ['1', 'true', 'on', 'yes'], true);
    }
}
