<?php
defined('ABSPATH') || exit;

class Termin_Kalender_Page_Overview {

    public static function get_page_locations($blocks, $shortcodes) {
        global $wpdb;
        $query_parts = [];
        
        foreach ($blocks as $block) {
            $query_parts[] = "post_content LIKE '%wp:" . esc_sql($wpdb->esc_like($block)) . "%'";
            $query_parts[] = "post_content LIKE '%" . esc_sql($wpdb->esc_like($block)) . "%'";
        }
        foreach ($shortcodes as $shortcode) {
            $query_parts[] = "post_content LIKE '%[" . esc_sql($wpdb->esc_like($shortcode)) . "%'";
        }

        if (empty($query_parts)) {
            return [];
        }

        $where = implode(' OR ', $query_parts);
        $query = "SELECT ID, post_title, post_content FROM {$wpdb->posts} WHERE ($where) AND post_status = 'publish'";
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- each part is escaped via esc_sql($wpdb->esc_like())
        return $wpdb->get_results($query);
    }

    public static function render($blocks, $shortcodes, $title, $description, $legacy_shortcode_check = null, $legacy_warning_msg = '') {
        $pages = self::get_page_locations($blocks, $shortcodes);
        ?>
        <div class="tk-modern-card termin-kalender-admin-card termin-kalender-overview-card">
            <div class="tk-modern-card-body termin-kalender-admin-card-body">
                <h3 class="termin-kalender-admin-h2 termin-kalender-overview-h3">
                    <?php echo esc_html($title); ?>
                </h3>
                <p class="termin-kalender-admin-text-lead termin-kalender-overview-p">
                    <?php echo esc_html($description); ?>
                </p>
                
                <?php if (empty($pages)): ?>
                    <p class="termin-kalender-overview-empty"><?php esc_html_e('No published pages found containing the respective blocks or shortcodes yet.', 'termin-kalender'); ?></p>
                <?php else: ?>
                    <ul class="termin-kalender-overview-list">
                        <?php foreach ($pages as $p): 
                            $is_legacy = false;
                            if ($legacy_shortcode_check && strpos($p->post_content, $legacy_shortcode_check) !== false) {
                                $is_legacy = true;
                            }
                            
                            $type_label = __('UNKNOWN', 'termin-kalender');
                            foreach ($blocks as $b) {
                                if (strpos($p->post_content, 'wp:' . $b) !== false || strpos($p->post_content, '<!-- wp:' . $b) !== false || strpos($p->post_content, 'wp-block-' . str_replace('/', '-', $b)) !== false) {
                                    $type_label = __('BLOCK', 'termin-kalender');
                                    break;
                                }
                            }
                            if ($type_label === __('UNKNOWN', 'termin-kalender')) {
                                foreach ($shortcodes as $sc) {
                                    if (strpos($p->post_content, '[' . $sc) !== false) {
                                        $type_label = __('SHORTCODE', 'termin-kalender');
                                        break;
                                    }
                                }
                            }
                            
                            $edit_link = get_edit_post_link($p->ID);
                            $view_link = get_permalink($p->ID);
                        ?>
                        <li class="termin-kalender-overview-list-item">
                            <strong class="termin-kalender-overview-list-title"><?php echo esc_html($p->post_title ?: __('Untitled', 'termin-kalender')); ?></strong>
                            
                            <span class="termin-kalender-overview-badge-type"><?php echo esc_html($type_label); ?></span>
                            
                            <?php if ($is_legacy): ?>
                                <span class="termin-kalender-overview-badge-legacy"><?php esc_html_e('LEGACY SHORTCODE', 'termin-kalender'); ?></span>
                            <?php else: ?>
                                <span class="termin-kalender-overview-badge-active"><?php esc_html_e('ACTIVE', 'termin-kalender'); ?></span>
                            <?php endif; ?>
                            
                            <div class="termin-kalender-overview-actions">
                                <a href="<?php echo esc_url($view_link); ?>" target="_blank"><?php esc_html_e('View Page', 'termin-kalender'); ?></a>
                                <span class="termin-kalender-overview-actions-separator">|</span>
                                <a href="<?php echo esc_url($edit_link); ?>"><?php esc_html_e('Edit Page', 'termin-kalender'); ?></a>
                            </div>
                            
                            <?php if ($is_legacy && !empty($legacy_warning_msg)): ?>
                                <div class="termin-kalender-overview-warning">
                                    ⚠️ <?php echo esc_html($legacy_warning_msg); ?>
                                </div>
                            <?php endif; ?>
                        </li>
                        <?php endforeach; ?>
                    </ul>
                <?php endif; ?>
            </div>
        </div>
        <?php
    }
}
