<?php

declare(strict_types=1);

final class MicTableRepository
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listAll(int $limit = 100, int $offset = 0): array
    {
        $limit = max(1, min(500, $limit));
        $offset = max(0, $offset);
        $statement = $this->pdo->prepare(
            'SELECT * FROM mic_tables ORDER BY updated_at DESC, name ASC LIMIT :limit OFFSET :offset'
        );
        $statement->bindValue(':limit', $limit, PDO::PARAM_INT);
        $statement->bindValue(':offset', $offset, PDO::PARAM_INT);
        $statement->execute();

        return array_map([$this, 'hydrate'], $statement->fetchAll() ?: []);
    }

    public function findById(string $id): ?array
    {
        $statement = $this->pdo->prepare('SELECT * FROM mic_tables WHERE id = :id LIMIT 1');
        $statement->execute([':id' => trim($id)]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $this->hydrate($row) : null;
    }

    public function create(array $data): array
    {
        $now = myronNow();
        $id = $this->generateTableId();
        $normalized = $this->normalizePayload($data, true);
        $statement = $this->pdo->prepare(
            'INSERT INTO mic_tables (
                id, name, description, caption, columns_json, rows_json, header_style,
                responsive_strategy, created_at, updated_at, created_by, updated_by
             ) VALUES (
                :id, :name, :description, :caption, :columns_json, :rows_json, :header_style,
                :responsive_strategy, :created_at, :updated_at, :created_by, :updated_by
             )'
        );
        $statement->execute([
            ':id' => $id,
            ':name' => $normalized['name'],
            ':description' => $normalized['description'],
            ':caption' => $normalized['caption'],
            ':columns_json' => json_encode($normalized['columns'], JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
            ':rows_json' => json_encode($normalized['rows'], JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
            ':header_style' => $normalized['header_style'],
            ':responsive_strategy' => $normalized['responsive_strategy'],
            ':created_at' => $now,
            ':updated_at' => $now,
            ':created_by' => (string) ($normalized['created_by'] ?? ''),
            ':updated_by' => (string) ($normalized['updated_by'] ?? ''),
        ]);

        return $this->findById($id) ?? [];
    }

    public function update(string $id, array $data): array
    {
        $existing = $this->findById($id);
        if ($existing === null) {
            throw new InvalidArgumentException('Table not found.');
        }

        $merged = array_merge($existing, $data);
        $normalized = $this->normalizePayload($merged, false);
        $statement = $this->pdo->prepare(
            'UPDATE mic_tables
             SET name = :name,
                 description = :description,
                 caption = :caption,
                 columns_json = :columns_json,
                 rows_json = :rows_json,
                 header_style = :header_style,
                 responsive_strategy = :responsive_strategy,
                 updated_at = :updated_at,
                 updated_by = :updated_by
             WHERE id = :id'
        );
        $statement->execute([
            ':id' => trim($id),
            ':name' => $normalized['name'],
            ':description' => $normalized['description'],
            ':caption' => $normalized['caption'],
            ':columns_json' => json_encode($normalized['columns'], JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
            ':rows_json' => json_encode($normalized['rows'], JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
            ':header_style' => $normalized['header_style'],
            ':responsive_strategy' => $normalized['responsive_strategy'],
            ':updated_at' => myronNow(),
            ':updated_by' => (string) ($normalized['updated_by'] ?? ''),
        ]);

        return $this->findById($id) ?? [];
    }

    public function delete(string $id): bool
    {
        $statement = $this->pdo->prepare('DELETE FROM mic_tables WHERE id = :id');
        $statement->execute([':id' => trim($id)]);
        return $statement->rowCount() > 0;
    }

    /**
     * @return list<array{pageId:string,pageTitle:string,fieldPath:string}>
     */
    public function findReferencingPages(string $id): array
    {
        $token = '[mic-table-' . trim($id) . ']';
        $statement = $this->pdo->prepare(
            'SELECT pages.id AS page_id, pages.title AS page_title, page_versions.content_json
             FROM page_versions
             INNER JOIN pages ON pages.id = page_versions.page_id
             WHERE page_versions.content_json LIKE :needle
             ORDER BY pages.title ASC, pages.id ASC'
        );
        $statement->execute([':needle' => '%' . $token . '%']);
        $matches = [];
        foreach ($statement->fetchAll() ?: [] as $row) {
            if (!is_array($row)) {
                continue;
            }
            $content = json_decode((string) ($row['content_json'] ?? '{}'), true);
            $paths = $this->findTokenPaths(is_array($content) ? $content : [], $token);
            foreach ($paths as $path) {
                $matches[] = [
                    'pageId' => (string) ($row['page_id'] ?? ''),
                    'pageTitle' => (string) ($row['page_title'] ?? ''),
                    'fieldPath' => $path,
                ];
            }
        }
        return $matches;
    }

    private function generateTableId(): string
    {
        do {
            $id = 'mic_' . bin2hex(random_bytes(8));
        } while ($this->findById($id) !== null);
        return $id;
    }

    /**
     * @return array<string, mixed>
     */
    private function hydrate(array $row): array
    {
        $columns = json_decode((string) ($row['columns_json'] ?? '[]'), true);
        $rows = json_decode((string) ($row['rows_json'] ?? '[]'), true);
        return [
            'id' => (string) ($row['id'] ?? ''),
            'name' => (string) ($row['name'] ?? ''),
            'description' => (string) ($row['description'] ?? ''),
            'caption' => (string) ($row['caption'] ?? ''),
            'columns' => is_array($columns) ? array_values($columns) : [],
            'rows' => is_array($rows) ? array_values($rows) : [],
            'header_style' => (string) ($row['header_style'] ?? 'default'),
            'responsive_strategy' => (string) ($row['responsive_strategy'] ?? 'scroll'),
            'created_at' => (string) ($row['created_at'] ?? ''),
            'updated_at' => (string) ($row['updated_at'] ?? ''),
            'created_by' => (string) ($row['created_by'] ?? ''),
            'updated_by' => (string) ($row['updated_by'] ?? ''),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function normalizePayload(array $data, bool $creating): array
    {
        $name = trim((string) ($data['name'] ?? ''));
        if ($name === '') {
            throw new InvalidArgumentException('Table name is required.');
        }

        return [
            'name' => $name,
            'description' => trim((string) ($data['description'] ?? '')),
            'caption' => trim((string) ($data['caption'] ?? '')),
            'columns' => $this->normalizeColumns(is_array($data['columns'] ?? null) ? $data['columns'] : []),
            'rows' => $this->normalizeRows(is_array($data['rows'] ?? null) ? $data['rows'] : []),
            'header_style' => $this->enum((string) ($data['header_style'] ?? 'default'), ['default', 'emphatic', 'none'], 'default'),
            'responsive_strategy' => $this->enum((string) ($data['responsive_strategy'] ?? 'scroll'), ['scroll', 'stack', 'auto'], 'scroll'),
            'created_by' => (string) ($data['created_by'] ?? ''),
            'updated_by' => (string) ($data['updated_by'] ?? ''),
        ];
    }

    private function normalizeColumns(array $columns): array
    {
        $normalized = [];
        foreach ($columns as $index => $column) {
            if (!is_array($column)) {
                continue;
            }
            $label = trim((string) ($column['label'] ?? ''));
            if ($label === '') {
                continue;
            }
            $key = trim((string) ($column['key'] ?? ''));
            if ($key === '') {
                $key = myronNormalizeSlug($label);
                $key = str_replace('-', '_', $key);
            }
            $normalized[] = [
                'key' => $key !== '' ? $key : 'column_' . ((int) $index + 1),
                'label' => $label,
                'alignment' => $this->enum((string) ($column['alignment'] ?? 'start'), ['start', 'center', 'end'], 'start'),
            ];
        }
        if ($normalized === []) {
            $normalized[] = ['key' => 'column_1', 'label' => 'Column 1', 'alignment' => 'start'];
        }
        return $normalized;
    }

    private function normalizeRows(array $rows): array
    {
        $normalized = [];
        foreach ($rows as $row) {
            $cells = [];
            foreach (is_array($row) ? $row : [] as $cell) {
                $cells[] = myronRichtextSanitize((string) $cell, ['singleLine' => true]);
            }
            if ($cells !== []) {
                $normalized[] = $cells;
            }
        }
        return $normalized;
    }

    private function enum(string $value, array $allowed, string $default): string
    {
        $value = trim($value);
        return in_array($value, $allowed, true) ? $value : $default;
    }

    /**
     * @return list<string>
     */
    private function findTokenPaths(array $value, string $token, string $path = 'content'): array
    {
        $paths = [];
        foreach ($value as $key => $child) {
            $childPath = $path . '.' . (string) $key;
            if (is_string($child) && str_contains($child, $token)) {
                $paths[] = $childPath;
            } elseif (is_array($child)) {
                array_push($paths, ...$this->findTokenPaths($child, $token, $childPath));
            }
        }
        return $paths;
    }
}

