<?php

namespace Core\App\Models;

use PDO;

class AiLandingPage extends BaseModel
{
    protected string $table = 'ai_landing_pages';
    private static ?array $knownColumns = null;

    public function __construct()
    {
        parent::__construct();
    }

    /* =====================================================
       Get all landing pages (admin list)
    ===================================================== */
    public function getAll(): array
    {
        return $this->fetchAll("
            SELECT lp.*, p.title AS product_title
            FROM {$this->table} lp
            LEFT JOIN products p ON p.id = lp.product_id
            ORDER BY lp.updated_at DESC
        ");
    }

    /* =====================================================
       Get all landing pages for a product
    ===================================================== */
    public function getByProduct(int $productId): array
    {
        return $this->fetchAll(
            "SELECT * FROM {$this->table} WHERE product_id = ? ORDER BY updated_at DESC",
            [$productId]
        );
    }

    /* =====================================================
       Get single page by ID
    ===================================================== */
    public function getById(int $id): ?array
    {
        $row = $this->fetchOne(
            "SELECT lp.*, p.title AS product_title
             FROM {$this->table} lp
             LEFT JOIN products p ON p.id = lp.product_id
             WHERE lp.id = ? LIMIT 1",
            [$id]
        );

        if (!$row) {
            return null;
        }

        return $this->hydrateJsonFields($row);
    }

    /* =====================================================
       Get by slug (frontend display)
    ===================================================== */
    public function getBySlug(string $slug): ?array
    {
        return $this->fetchOne(
            "SELECT * FROM {$this->table} WHERE slug = ? AND status = 'published' LIMIT 1",
            [$slug]
        );
    }

    /* =====================================================
       Get all published pages for sitemap (minimal fields only)
    ===================================================== */
    public function getPublishedForSitemap(): array
    {
        return $this->fetchAll(
            "SELECT slug, updated_at FROM {$this->table} WHERE status = 'published' AND slug != '' ORDER BY updated_at DESC"
        );
    }

    /* =====================================================
       Create
    ===================================================== */
    public function create(array $data): int
    {
        $slug = $this->ensureUniqueSlug($data['slug'] ?? '');
        $columns = $this->getAvailableColumns();

        $insert = [
            'product_id' => (int)$data['product_id'],
            'name' => trim($data['name']),
            'slug' => $slug,
            'html_content' => $data['html_content'] ?? '',
            'conversation' => is_array($data['conversation'] ?? null)
                ? json_encode($data['conversation'], JSON_UNESCAPED_UNICODE)
                : ($data['conversation'] ?? '[]'),
            'status' => in_array($data['status'] ?? '', ['published', 'draft'], true)
                ? $data['status']
                : 'draft',
        ];

        if (in_array('source_mode', $columns, true)) {
            $insert['source_mode'] = in_array($data['source_mode'] ?? '', ['manual', 'image'], true) ? $data['source_mode'] : 'manual';
        }
        if (in_array('source_image', $columns, true)) {
            $insert['source_image'] = $data['source_image'] ?? null;
        }
        $analysisColumn = $this->resolveStorageColumn('analysis_json');
        if ($analysisColumn !== null && $analysisColumn !== 'page_spec') {
            $insert[$analysisColumn] = $this->encodeJsonValue($data['analysis_json'] ?? []);
        }
        $structureColumn = $this->resolveStorageColumn('structure_json');
        if ($structureColumn !== null && $structureColumn !== 'page_spec') {
            $insert[$structureColumn] = $this->encodeJsonValue($data['structure_json'] ?? []);
        }
        $agentTraceColumn = $this->resolveStorageColumn('agent_trace');
        if ($agentTraceColumn !== null) {
            $insert[$agentTraceColumn] = $this->encodeJsonValue($data['agent_trace'] ?? []);
        }
        $legacyPageSpec = $this->buildLegacyPageSpecPayload([], $data);
        if ($legacyPageSpec !== null) {
            $insert['page_spec'] = $legacyPageSpec;
        }

        $fieldNames = array_keys($insert);
        $placeholders = array_fill(0, count($fieldNames), '?');

        $this->execute(
            "INSERT INTO {$this->table} (" . implode(', ', $fieldNames) . ")
             VALUES (" . implode(', ', $placeholders) . ")",
            array_values($insert)
        );

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

    /* =====================================================
       Update
    ===================================================== */
    public function update(int $id, array $data): bool
    {
        $fields = [];
        $params = [];
        $columns = $this->getAvailableColumns();

        if (isset($data['name'])) {
            $fields[] = 'name = ?';
            $params[] = trim($data['name']);
        }

        if (isset($data['slug'])) {
            $fields[] = 'slug = ?';
            $params[] = $this->ensureUniqueSlug($data['slug'], $id);
        }

        if (array_key_exists('html_content', $data)) {
            $fields[] = 'html_content = ?';
            $params[] = $data['html_content'];
        }

        if (array_key_exists('conversation', $data)) {
            $fields[] = 'conversation = ?';
            $params[] = is_array($data['conversation'])
                ? json_encode($data['conversation'], JSON_UNESCAPED_UNICODE)
                : ($data['conversation'] ?? '[]');
        }

        if (isset($data['status'])) {
            $fields[] = 'status = ?';
            $params[] = in_array($data['status'], ['published', 'draft'], true)
                ? $data['status']
                : 'draft';
        }

        if (isset($data['source_mode']) && in_array('source_mode', $columns, true)) {
            $fields[] = 'source_mode = ?';
            $params[] = in_array($data['source_mode'], ['manual', 'image'], true) ? $data['source_mode'] : 'manual';
        }

        if (array_key_exists('source_image', $data) && in_array('source_image', $columns, true)) {
            $fields[] = 'source_image = ?';
            $params[] = $data['source_image'];
        }

        $analysisColumn = $this->resolveStorageColumn('analysis_json');
        if (array_key_exists('analysis_json', $data) && $analysisColumn !== null && $analysisColumn !== 'page_spec') {
            $fields[] = $analysisColumn . ' = ?';
            $params[] = $this->encodeJsonValue($data['analysis_json']);
        }

        $structureColumn = $this->resolveStorageColumn('structure_json');
        if (array_key_exists('structure_json', $data) && $structureColumn !== null && $structureColumn !== 'page_spec') {
            $fields[] = $structureColumn . ' = ?';
            $params[] = $this->encodeJsonValue($data['structure_json']);
        }

        $agentTraceColumn = $this->resolveStorageColumn('agent_trace');
        if (array_key_exists('agent_trace', $data) && $agentTraceColumn !== null) {
            $fields[] = $agentTraceColumn . ' = ?';
            $params[] = $this->encodeJsonValue($data['agent_trace']);
        }

        $legacyPageSpec = $this->buildLegacyPageSpecPayload($this->getById($id) ?: [], $data);
        if ($legacyPageSpec !== null) {
            $fields[] = 'page_spec = ?';
            $params[] = $legacyPageSpec;
        }

        if (empty($fields)) {
            return false;
        }

        $params[] = $id;

        return $this->execute(
            "UPDATE {$this->table} SET " . implode(', ', $fields) . " WHERE id = ?",
            $params
        );
    }

    /* =====================================================
       Delete
    ===================================================== */
    public function delete(int $id): bool
    {
        return $this->execute(
            "DELETE FROM {$this->table} WHERE id = ?",
            [$id]
        );
    }

    /* =====================================================
       Slug uniqueness helper
    ===================================================== */
    private function ensureUniqueSlug(string $slug, int $excludeId = 0): string
    {
        $slug = $this->makeSlug($slug);
        if ($slug === '') {
            $slug = 'landing-' . bin2hex(random_bytes(4));
        }

        $base   = $slug;
        $suffix = 1;

        while (true) {
            $existing = $this->fetchOne(
                "SELECT id FROM {$this->table} WHERE slug = ? LIMIT 1",
                [$slug]
            );

            if (!$existing || (int)$existing['id'] === $excludeId) {
                break;
            }

            $slug = $base . '-' . $suffix;
            $suffix++;
        }

        return $slug;
    }

    private function makeSlug(string $text): string
    {
        $text = mb_strtolower(trim($text), 'UTF-8');
        $text = preg_replace('/[^\p{L}\p{N}\-]+/u', '-', $text);
        $text = preg_replace('/-+/', '-', $text);
        return trim($text, '-');
    }

    private function encodeJsonValue($value): string
    {
        if (is_string($value)) {
            $decoded = json_decode($value, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                return $value;
            }
        }

        return json_encode(is_array($value) ? $value : [], JSON_UNESCAPED_UNICODE);
    }

    private function hydrateJsonFields(array $row): array
    {
        $row = $this->inflateLegacyFields($row);

        foreach (['conversation', 'analysis_json', 'structure_json', 'agent_trace'] as $field) {
            if (isset($row[$field]) && !empty($row[$field]) && is_string($row[$field])) {
                $decoded = json_decode($row[$field], true);
                $row[$field] = is_array($decoded) ? $decoded : [];
            } else {
                $row[$field] = [];
            }
        }

        $row['source_mode'] = (isset($row['source_mode']) && in_array($row['source_mode'], ['manual', 'image'], true))
            ? (string)$row['source_mode']
            : 'manual';

        return $row;
    }

    private function inflateLegacyFields(array $row): array
    {
        if (array_key_exists('page_spec', $row)) {
            $decodedSpec = $this->decodeJsonValue($row['page_spec']);
            if (is_array($decodedSpec) && $this->isLegacyPageSpecEnvelope($decodedSpec)) {
                if (!array_key_exists('structure_json', $row) && isset($decodedSpec['structure_json'])) {
                    $row['structure_json'] = $this->encodeJsonValue($decodedSpec['structure_json']);
                }
                if (!array_key_exists('analysis_json', $row) && isset($decodedSpec['analysis_json'])) {
                    $row['analysis_json'] = $this->encodeJsonValue($decodedSpec['analysis_json']);
                }
                if (!array_key_exists('source_mode', $row) && isset($decodedSpec['source_mode'])) {
                    $row['source_mode'] = $decodedSpec['source_mode'];
                }
                if (!array_key_exists('source_image', $row) && isset($decodedSpec['source_image'])) {
                    $row['source_image'] = $decodedSpec['source_image'];
                }
            } else {
                if (!array_key_exists('structure_json', $row)) {
                    $row['structure_json'] = $row['page_spec'];
                }
                if (!array_key_exists('analysis_json', $row)) {
                    $row['analysis_json'] = $row['page_spec'];
                }
            }
        }

        if (!array_key_exists('agent_trace', $row)) {
            if (array_key_exists('quality_data', $row)) {
                $row['agent_trace'] = $row['quality_data'];
            } elseif (array_key_exists('command_history', $row)) {
                $row['agent_trace'] = $row['command_history'];
            }
        }

        return $row;
    }

    private function getAvailableColumns(): array
    {
        if (is_array(self::$knownColumns)) {
            return self::$knownColumns;
        }

        self::$knownColumns = [];
        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM {$this->table}");
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                if (!empty($row['Field'])) {
                    self::$knownColumns[] = (string)$row['Field'];
                }
            }
        } catch (\Throwable $e) {
            self::$knownColumns = [
                'id',
                'product_id',
                'name',
                'slug',
                'html_content',
                'conversation',
                'status',
                'created_at',
                'updated_at',
            ];
        }

        return self::$knownColumns;
    }

    private function resolveStorageColumn(string $logical): ?string
    {
        $columns = $this->getAvailableColumns();

        if (in_array($logical, $columns, true)) {
            return $logical;
        }

        $legacyMap = [
            'structure_json' => 'page_spec',
            'analysis_json' => 'page_spec',
            'agent_trace' => 'quality_data',
        ];

        if (isset($legacyMap[$logical]) && in_array($legacyMap[$logical], $columns, true)) {
            return $legacyMap[$logical];
        }

        return null;
    }

    private function buildLegacyPageSpecPayload(array $existingRow, array $data): ?string
    {
        $columns = $this->getAvailableColumns();
        if (!in_array('page_spec', $columns, true)) {
            return null;
        }

        $needsLegacyEnvelope =
            !in_array('analysis_json', $columns, true) ||
            !in_array('structure_json', $columns, true) ||
            !in_array('source_mode', $columns, true) ||
            !in_array('source_image', $columns, true);

        if (!$needsLegacyEnvelope) {
            return null;
        }

        $hasRelevantIncomingData =
            array_key_exists('analysis_json', $data) ||
            array_key_exists('structure_json', $data) ||
            array_key_exists('source_mode', $data) ||
            array_key_exists('source_image', $data);

        if (!$hasRelevantIncomingData && empty($existingRow)) {
            return null;
        }

        $payload = [];
        if (!empty($existingRow['page_spec'])) {
            $decoded = $this->decodeJsonValue($existingRow['page_spec']);
            if (is_array($decoded)) {
                if ($this->isLegacyPageSpecEnvelope($decoded)) {
                    $payload = $decoded;
                } else {
                    $payload['structure_json'] = $decoded;
                    $payload['analysis_json'] = $decoded;
                }
            }
        }

        if (array_key_exists('analysis_json', $data)) {
            $payload['analysis_json'] = $data['analysis_json'];
        }
        if (array_key_exists('structure_json', $data)) {
            $payload['structure_json'] = $data['structure_json'];
        }
        if (array_key_exists('source_mode', $data)) {
            $payload['source_mode'] = in_array($data['source_mode'], ['manual', 'image'], true) ? $data['source_mode'] : 'manual';
        } elseif (!isset($payload['source_mode'])) {
            $payload['source_mode'] = 'manual';
        }
        if (array_key_exists('source_image', $data)) {
            $payload['source_image'] = $data['source_image'];
        }

        return $this->encodeJsonValue($payload);
    }

    private function isLegacyPageSpecEnvelope(array $payload): bool
    {
        return array_key_exists('analysis_json', $payload)
            || array_key_exists('structure_json', $payload)
            || array_key_exists('source_mode', $payload)
            || array_key_exists('source_image', $payload);
    }

    private function decodeJsonValue($value): array
    {
        if (!is_string($value) || trim($value) === '') {
            return [];
        }

        $decoded = json_decode($value, true);
        return is_array($decoded) ? $decoded : [];
    }
}
