<?php
/**
 * Cast Conductor Proprietary License v5
 * SPDX-License-Identifier: LicenseRef-CastConductor-Proprietary-v5
 * 
 * Copyright (c) 2025 CastConductor.com. All Rights Reserved.
 * 
 * This file is part of Cast Conductor ("Software"). The Software and its source
 * code constitute proprietary, confidential, and trade secret information of
 * CastConductor.com ("Company"). Any access or use is governed strictly by the
 * Cast Conductor Proprietary License v5 ("License"). By installing, copying,
 * accessing, compiling, or otherwise using the Software you agree to be bound by
 * all terms of the License. If you do not agree, you must cease use immediately.
 * 
 * Key Terms (Summary – see full License for binding terms):
 *  1. No Redistribution: You may not publish, distribute, sublicense, rent,
 *     lease, transfer, sell, or otherwise make the Software (or any derivative)
 *     available to any third party without prior written consent of Company.
 *  2. No Modification: Modification, reverse engineering, decompilation, or
 *     disassembly is prohibited except to the limited extent expressly permitted
 *     by applicable law that cannot be contractually waived.
 *  3. Confidentiality: Treat all source code and related artifacts as Company
 *     Confidential Information. Maintain at least the same degree of care as for
 *     your own confidential materials, and not less than reasonable care.
 *  4. No Patent License: No express or implied patent rights are granted. Future
 *     patents (if any) are fully reserved.
 *  5. No Trademark License: Company names, marks, and logos may not be used
 *     without prior written permission.
 *  6. Limited Internal Use: Use is limited solely to internal evaluation and
 *     operation of licensed Cast Conductor deployments. Commercial hosting or
 *     resale as a service requires a separate written agreement.
 *  7. Telemetry & License Validation: The Software may periodically transmit a
 *     hashed installation identifier, domain (or site ID), plugin/app version,
 *     and a truncated (non-reversible) fragment of the license key solely to
 *     validate activation status and enforce licensing. This minimal "phone home"
 *     check contains no personal or content data. If optional telemetry is later
 *     introduced it will be limited to aggregate operational metrics (no PII),
 *     fully documented, and optionally disableable per published instructions.
 *  8. Third-Party Components: The Software may include open source components
 *     covered by their own licenses. See THIRD-PARTY-NOTICES.md. Those licenses
 *     govern their respective components; this License governs all remaining code.
 *  9. Export Compliance: You are responsible for compliance with all applicable
 *     export control and sanctions laws.
 * 10. Warranty Disclaimer: THE SOFTWARE IS PROVIDED "AS IS" WITHOUT WARRANTY OF
 *     ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO MERCHANTABILITY,
 *     FITNESS FOR A PARTICULAR PURPOSE, TITLE, AND NON-INFRINGEMENT.
 * 11. Limitation of Liability: IN NO EVENT WILL COMPANY OR AUTHORS BE LIABLE FOR
 *     ANY INDIRECT, SPECIAL, INCIDENTAL, CONSEQUENTIAL, EXEMPLARY, OR PUNITIVE
 *     DAMAGES, OR LOST PROFITS, EVEN IF ADVISED OF THE POSSIBILITY.
 * 12. Acceptance: Use of the Software constitutes acceptance of the License.
 * 13. Enforcement: Unauthorized reproduction or distribution may result in civil
 *     and criminal penalties and will be prosecuted to the maximum extent allowed
 *     by law.
 * 
 * Authoritative EULA: EULA-v5.1.md (repository root – private) and https://castconductor.com/eula
 * Precedence: If this summary conflicts with the EULA, the EULA governs.
 * Revision: Current EULA revision v5.1 (subject to update; check EULA for current enterprise thresholds).
 * 
 * Full License text available from: licensing@castconductor.com
 * Security reports: security@castconductor.com
 * Commercial inquiries: licensing@castconductor.com
 * 
 * END OF HEADER
 */

/**
 * CastConductor Database Management
 * 
 * Handles database schema creation and management with WordPress-compliant naming
 * Implements the 3-table architecture: containers, content_blocks, container_blocks
 */

if (!defined('ABSPATH')) {
    exit;
}

class CastConductor_Database {
    
    /**
     * Database version for upgrades
     */
    const DB_VERSION = '5.1.0';
    
    /**
     * Constructor
     */
    public function __construct() {
        add_action('init', array($this, 'check_database_version'));
    }
    
    /**
     * Create all database tables
     */
    public function create_tables() {
        global $wpdb;
        
        // Enable error reporting for debugging
        $wpdb->show_errors();
        
        // Get charset and collation
        $charset_collate = $wpdb->get_charset_collate();
        
        // Create containers table
        $this->create_containers_table($charset_collate);
        
        // Create content blocks table
        $this->create_content_blocks_table($charset_collate);
        
        // Create container-blocks assignment table
        $this->create_container_blocks_table($charset_collate);
        
        // Create backgrounds table
        $this->create_backgrounds_table($charset_collate);

        // Create scenes tables (Phase 4)
        $this->create_scenes_table($charset_collate);
        $this->create_scene_containers_table($charset_collate);
        
        // Create menu navigation tables (Phase 5 - Menu Builder)
        $this->create_menus_table($charset_collate);
        $this->create_menu_items_table($charset_collate);
        $this->create_button_bindings_table($charset_collate);
        
        // Update database version
        update_option('castconductor_db_version', self::DB_VERSION);
        
        // Log successful creation
        error_log('CastConductor V5: Database tables created successfully');

        // Ensure at least one default scene exists
        try { $this->ensure_default_scene(); } catch (Exception $e) { /* non-fatal */ }
    }
    
    /**
     * Create containers table
     * Stores layout container definitions (lower third, sidebar, etc.)
     */
    private function create_containers_table($charset_collate) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'castconductor_containers';
        
        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(200) NOT NULL,
            position VARCHAR(50) NOT NULL DEFAULT 'custom',
            width INT(11) DEFAULT 1280,
            height INT(11) DEFAULT 200,
            x_position INT(11) DEFAULT 0,
            y_position INT(11) DEFAULT 0,
            z_index INT(11) DEFAULT 100,
            rotation_enabled TINYINT(1) DEFAULT 1,
            rotation_interval INT(11) DEFAULT 30,
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY position_enabled (position, enabled),
            KEY z_index (z_index),
            KEY created_at (created_at)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Verify table creation
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }
    
    

    /**
     * Create content blocks table
     * Stores content block definitions and visual configurations
     */
    private function create_content_blocks_table($charset_collate) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'castconductor_content_blocks';
        
        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(200) NOT NULL,
            type VARCHAR(50) NOT NULL,
            visual_config LONGTEXT,
            data_config LONGTEXT,
            artwork_cache LONGTEXT,
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY type_enabled (type, enabled),
            KEY created_at (created_at)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Verify table creation
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }
    
    /**
     * Create container-blocks assignment table
     * Many-to-many relationship between containers and content blocks
     */
    private function create_container_blocks_table($charset_collate) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'castconductor_container_blocks';
        
        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            container_id BIGINT(20) UNSIGNED NOT NULL,
            content_block_id BIGINT(20) UNSIGNED NOT NULL,
            rotation_order INT DEFAULT 1,
            rotation_percentage FLOAT DEFAULT 10.0,
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY container_id (container_id),
            KEY content_block_id (content_block_id),
            KEY rotation_order (rotation_order),
            KEY enabled (enabled),
            UNIQUE KEY unique_assignment (container_id, content_block_id)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Verify table creation
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }

    /**
     * Create scenes table
     * Stores scene metadata, background/branding overrides, and activation
     */
    private function create_scenes_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'castconductor_scenes';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(200) NOT NULL,
            description TEXT NULL,
            is_active TINYINT(1) DEFAULT 0,
            rotation_enabled TINYINT(1) DEFAULT 0,
            rotation_interval INT(11) DEFAULT 60,
            rotation_percentage FLOAT DEFAULT 0.0,
            schedule_enabled TINYINT(1) DEFAULT 0,
            schedule_start DATETIME NULL,
            schedule_end DATETIME NULL,
            schedule_timezone VARCHAR(50) DEFAULT 'America/Los_Angeles',
            schedule_days TEXT NULL,
            schedule_time_start TIME NULL,
            schedule_time_end TIME NULL,
            schedule_priority INT(11) DEFAULT 0,
            branding LONGTEXT NULL,
            background LONGTEXT NULL,
            metadata LONGTEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY is_active (is_active),
            KEY schedule_enabled (schedule_enabled),
            KEY schedule_priority (schedule_priority),
            KEY created_at (created_at)
        ) {$charset_collate};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }

    /**
     * Create scene-container overrides table
     * Stores per-scene container geometry and per-zone assignments snapshot
     */
    private function create_scene_containers_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'castconductor_scene_containers';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            scene_id BIGINT(20) UNSIGNED NOT NULL,
            container_id BIGINT(20) UNSIGNED NOT NULL,
            overrides LONGTEXT NULL,
            zones LONGTEXT NULL,
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_scene_container (scene_id, container_id),
            KEY scene_id (scene_id),
            KEY container_id (container_id),
            KEY enabled (enabled)
        ) {$charset_collate};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }

    /**
     * Create menus table
     * Stores menu definitions for hierarchical navigation
     */
    private function create_menus_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'castconductor_menus';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name VARCHAR(100) NOT NULL,
            trigger_button VARCHAR(20) DEFAULT NULL,
            position VARCHAR(20) DEFAULT 'left',
            animation VARCHAR(20) DEFAULT 'slide',
            width INT(11) DEFAULT 400,
            height INT(11) DEFAULT 720,
            background_color VARCHAR(20) DEFAULT '#1a1a1aEE',
            background_image VARCHAR(500) DEFAULT NULL,
            overlay_opacity FLOAT DEFAULT 0.9,
            overlay_enabled TINYINT(1) DEFAULT 1,
            text_color VARCHAR(20) DEFAULT '#FFFFFF',
            accent_color VARCHAR(20) DEFAULT '#8b5cf6',
            font_family VARCHAR(50) DEFAULT 'Lexend',
            font_size INT(11) DEFAULT 18,
            item_padding INT(11) DEFAULT 16,
            show_live_stream_button TINYINT(1) DEFAULT 1,
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_trigger (trigger_button),
            KEY enabled (enabled)
        ) {$charset_collate};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }

    /**
     * Create menu items table
     * Stores ordered items within menus (content blocks, submenus, or actions)
     */
    private function create_menu_items_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'castconductor_menu_items';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            menu_id BIGINT(20) UNSIGNED NOT NULL,
            display_label VARCHAR(100) NOT NULL,
            icon VARCHAR(50) DEFAULT NULL,
            item_type VARCHAR(20) DEFAULT 'content_block',
            content_block_id BIGINT(20) UNSIGNED DEFAULT NULL,
            submenu_id BIGINT(20) UNSIGNED DEFAULT NULL,
            action_type VARCHAR(50) DEFAULT NULL,
            action_data TEXT DEFAULT NULL,
            sort_order INT(11) DEFAULT 0,
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY menu_id (menu_id),
            KEY sort_order (sort_order),
            KEY item_type (item_type),
            KEY enabled (enabled)
        ) {$charset_collate};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }

    /**
     * Create button bindings table
     * Maps remote buttons to menus or direct content block overlays
     */
    private function create_button_bindings_table($charset_collate) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'castconductor_button_bindings';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            trigger_button VARCHAR(20) NOT NULL,
            binding_type VARCHAR(20) DEFAULT 'disabled',
            menu_id BIGINT(20) UNSIGNED DEFAULT NULL,
            content_block_id BIGINT(20) UNSIGNED DEFAULT NULL,
            overlay_position VARCHAR(20) DEFAULT 'bottom',
            overlay_animation VARCHAR(20) DEFAULT 'slide',
            overlay_width INT(11) DEFAULT 1280,
            overlay_height INT(11) DEFAULT 400,
            overlay_background VARCHAR(20) DEFAULT '#000000CC',
            enabled TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_button (trigger_button),
            KEY binding_type (binding_type),
            KEY enabled (enabled)
        ) {$charset_collate};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }
    
    /**
     * Check if database needs updating
     */
    public function check_database_version() {
        $current_version = get_option('castconductor_db_version', '0.0.0');
        
        if (version_compare($current_version, self::DB_VERSION, '<')) {
            $this->upgrade_database($current_version);
        }

    // Always ensure critical schema integrity for seamless upgrades
    $this->ensure_schema_integrity();
    }
    
    /**
     * Upgrade database schema
     */
    private function upgrade_database($current_version) {
        // Future database upgrades will be handled here
        error_log("CastConductor V5: Upgrading database from {$current_version} to " . self::DB_VERSION);
        
        // Re-create tables to ensure they exist
        $this->create_tables();
    }

    /**
     * Ensure required schema elements exist even if activation didn't run
     * Self-heals common upgrade paths without manual deactivate/activate.
     */
    private function ensure_schema_integrity() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        // Ensure backgrounds table exists and has container_id column
        $bg_table = $wpdb->prefix . 'castconductor_backgrounds';
        $table_exists = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $bg_table)) === $bg_table);
        if (!$table_exists) {
            $this->create_backgrounds_table($charset_collate);
            return;
        }

        // Check for container_id column; if missing, dbDelta with the latest schema
        $has_container_id = (bool) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'container_id'", $bg_table));
        if (!$has_container_id) {
            $this->create_backgrounds_table($charset_collate);
        }

        // Ensure scenes tables exist
        $scenes_table = $wpdb->prefix . 'castconductor_scenes';
        $scene_containers_table = $wpdb->prefix . 'castconductor_scene_containers';

        $scenes_exists = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $scenes_table)) === $scenes_table);
        if (!$scenes_exists) {
            $this->create_scenes_table($charset_collate);
        } else {
            // Ensure rotation_percentage column exists (added for scene rotation feature)
            $has_rotation_pct = (bool) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'rotation_percentage'",
                $scenes_table
            ));
            if (!$has_rotation_pct) {
                $wpdb->query("ALTER TABLE {$scenes_table} ADD COLUMN rotation_percentage FLOAT DEFAULT 0.0 AFTER rotation_interval");
                error_log("CastConductor V5: Added rotation_percentage column to scenes table");
            }
        }

        $scene_containers_exists = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $scene_containers_table)) === $scene_containers_table);
        if (!$scene_containers_exists) {
            $this->create_scene_containers_table($charset_collate);
        }

        // Ensure menus table has background_image column (added in v5.6.9m)
        $menus_table = $wpdb->prefix . 'castconductor_menus';
        $menus_exists = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $menus_table)) === $menus_table);
        if ($menus_exists) {
            $has_bg_image = (bool) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'background_image'",
                $menus_table
            ));
            if (!$has_bg_image) {
                $wpdb->query("ALTER TABLE {$menus_table} ADD COLUMN background_image VARCHAR(500) DEFAULT NULL AFTER background_color");
                error_log("CastConductor V5: Added background_image column to menus table");
            }
            
            // Add overlay_opacity column (v5.6.9o)
            $has_overlay_opacity = (bool) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'overlay_opacity'",
                $menus_table
            ));
            if (!$has_overlay_opacity) {
                $wpdb->query("ALTER TABLE {$menus_table} ADD COLUMN overlay_opacity FLOAT DEFAULT 0.9 AFTER background_image");
                $wpdb->query("ALTER TABLE {$menus_table} ADD COLUMN overlay_enabled TINYINT(1) DEFAULT 1 AFTER overlay_opacity");
                error_log("CastConductor V5: Added overlay_opacity and overlay_enabled columns to menus table");
            }
            
            // Add font_size column (v5.6.9o)
            $has_font_size = (bool) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'font_size'",
                $menus_table
            ));
            if (!$has_font_size) {
                $wpdb->query("ALTER TABLE {$menus_table} ADD COLUMN font_size INT(11) DEFAULT 18 AFTER font_family");
                error_log("CastConductor V5: Added font_size column to menus table");
            }
            
            // Add show_live_stream_button column (v5.6.9s)
            $has_live_stream_btn = (bool) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'show_live_stream_button'",
                $menus_table
            ));
            if (!$has_live_stream_btn) {
                $wpdb->query("ALTER TABLE {$menus_table} ADD COLUMN show_live_stream_button TINYINT(1) DEFAULT 1 AFTER item_padding");
                error_log("CastConductor V5: Added show_live_stream_button column to menus table");
            }
        }

        // Ensure we have at least one scene
        try { $this->ensure_default_scene(); } catch (Exception $e) { /* non-fatal */ }

        // Self-heal: canonicalize default block IDs in assignments to the latest redesigned blocks by type
        try { $this->canonicalize_default_blocks_assignments(); } catch (Exception $e) { /* non-fatal */ }
    }
    
    /**
     * Get table name with prefix
     */
    public function get_table_name($table) {
        global $wpdb;
        return $wpdb->prefix . 'castconductor_' . $table;
    }
    
    /**
     * Create default containers (called during activation wizard)
     * V5 GEOMETRY UPDATE: Native 1280x720 seeding.
     */
    public function create_default_containers() {
        global $wpdb;
        
        $table_name = $this->get_table_name('containers');
        // Idempotency guard: reuse by position if already created
        $existing_lower = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table_name} WHERE position = %s ORDER BY id ASC LIMIT 1",
            'lower_third'
        ));
        $existing_upper = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table_name} WHERE position = %s ORDER BY id ASC LIMIT 1",
            'upper_third'
        ));
        $existing_full = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table_name} WHERE position = %s ORDER BY id ASC LIMIT 1",
            'full_screen'
        ));

        // Default lower third container - Updated for V5 1280x720
        $lower_third_config = array(
            'name' => 'Lower Third',
            'position' => 'lower_third',
            'width' => 1280,
            'height' => 240, // 1/3 of 720
            'x_position' => 0,
            'y_position' => 480, // 720 - 240
            'z_index' => 100,
            'rotation_enabled' => 1,
            // V5 default: 15s rotation for lower third (Roku parity)
            'rotation_interval' => 15,
            'enabled' => 1
        );
        if ($existing_lower) {
            $lower_third_id = (int) $existing_lower;
        } else {
            $wpdb->insert($table_name, $lower_third_config);
            $lower_third_id = (int) $wpdb->insert_id;
        }
        
        // Create upper third container
        $upper_third_config = array(
            'name' => 'Upper Third',
            'position' => 'upper_third',
            'width' => 1280,
            'height' => 240, // 1/3 of 720
            'x_position' => 0,
            'y_position' => 0,
            'z_index' => 110,
            'rotation_enabled' => 1,
            // Phase 3a: align OOTB interval to 15s and enable Upper Third
            'rotation_interval' => 15,
            'enabled' => 1
        );
        if ($existing_upper) {
            $upper_third_id = (int) $existing_upper;
        } else {
            $wpdb->insert($table_name, $upper_third_config);
            $upper_third_id = (int) $wpdb->insert_id;
        }

        // Phase 3a: Seed reference left/right zones for Upper Third (stored as layout metadata)
        try {
            // Authoring is natively 1280×720. Seed Upper Third halves directly in 1280-space.
            $layout = [
                'zones' => [
                    [
                        'id' => 'upper_left',
                        'name' => 'Upper Left',
                        'rect' => ['x' => 0, 'y' => 0, 'w' => 640, 'h' => 240],
                        'grid' => ['cols' => 12, 'gutter' => 24, 'snap' => 8],
                        'allow' => ['location_time']
                    ],
                    [
                        'id' => 'upper_right',
                        'name' => 'Upper Right',
                        'rect' => ['x' => 640, 'y' => 0, 'w' => 640, 'h' => 240],
                        'grid' => ['cols' => 12, 'gutter' => 24, 'snap' => 8],
                        'allow' => ['weather']
                    ]
                ],
                'activeZoneIds' => ['upper_left', 'upper_right'],
                'authored_space' => 'roku_1280x720'
            ];
            // Only set if not already present to avoid clobbering user edits
            $k = 'castconductor_container_layout_' . intval($upper_third_id);
            $existing_layout = get_option($k, null);
            if ($existing_layout === null || $existing_layout === '' ) {
                update_option($k, wp_json_encode($layout));
            } else if (is_string($existing_layout)) {
                // V5: Ensure authored_space is set to roku_1280x720 if missing
                $decoded = json_decode($existing_layout, true);
                if (is_array($decoded) && !isset($decoded['authored_space'])) {
                    $decoded['authored_space'] = 'roku_1280x720';
                    update_option($k, wp_json_encode($decoded));
                }
            }
        } catch (Exception $e) {
            // Non-fatal
        }
        
        // Create full screen container
        $full_screen_config = array(
            'name' => 'Full Screen',
            'position' => 'full_screen',
            'width' => 1280,
            'height' => 720,
            'x_position' => 0,
            'y_position' => 0,
            'z_index' => 90,
            'rotation_enabled' => 1,
            'rotation_interval' => 60,
            'enabled' => 0  // Disabled by default
        );
        if ($existing_full) {
            $full_screen_id = (int) $existing_full;
        } else {
            $wpdb->insert($table_name, $full_screen_config);
            $full_screen_id = (int) $wpdb->insert_id;
        }
        
        // Create Featured Hero container (1280x480 top portion for Track Info Hero)
        $existing_featured_hero = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table_name} WHERE position = %s ORDER BY id ASC LIMIT 1",
            'featured_grid_hero'
        ));
        $featured_hero_config = array(
            'name' => 'Featured Hero',
            'position' => 'featured_grid_hero',
            'width' => 1280,
            'height' => 480,  // Top 2/3 of 720
            'x_position' => 0,
            'y_position' => 0,
            'z_index' => 95,
            'rotation_enabled' => 0,
            'rotation_interval' => 60,
            'enabled' => 1  // Enabled by default for default scene
        );
        if ($existing_featured_hero) {
            $featured_hero_id = (int) $existing_featured_hero;
        } else {
            $wpdb->insert($table_name, $featured_hero_config);
            $featured_hero_id = (int) $wpdb->insert_id;
        }
        
        // Store default container IDs for reference
        update_option('castconductor_default_lower_third_id', $lower_third_id);
        update_option('castconductor_default_upper_third_id', $upper_third_id);
        update_option('castconductor_default_full_screen_id', $full_screen_id);
        update_option('castconductor_default_featured_hero_id', $featured_hero_id);
        
        error_log("CastConductor V5: Created default containers - Lower Third (ID: {$lower_third_id}), Upper Third (ID: {$upper_third_id}), Full Screen (ID: {$full_screen_id}), Featured Hero (ID: {$featured_hero_id})");
        
        return $lower_third_id;
    }
    
    /**
     * Create default backgrounds (called during activation wizard)
     */
    public function create_default_backgrounds() {
        global $wpdb;
        
        $table_name = $this->get_table_name('backgrounds');
        
        // Default gradient background (active by default)
        $gradient_config = array(
            'name' => 'Station Gradient',
            'type' => 'color',
            'config' => wp_json_encode([
                'color' => '#1e3c72',
                'gradient' => true,
                'gradient_colors' => ['#1e3c72', '#2a5298'],
                'gradient_direction' => '135deg'
            ]),
            'z_index' => 0,
            'enabled' => 1,
            'is_active' => 1
        );
        
        $wpdb->insert($table_name, $gradient_config);
        $gradient_id = $wpdb->insert_id;
        
        // Solid color background
        $solid_config = array(
            'name' => 'Dark Background',
            'type' => 'color',
            'config' => wp_json_encode([
                'color' => '#1e1e1e',
                'gradient' => false
            ]),
            'z_index' => 0,
            'enabled' => 1,
            'is_active' => 0
        );
        
        $wpdb->insert($table_name, $solid_config);
        $solid_id = $wpdb->insert_id;
        
        // Placeholder for station branding background
        $branding_config = array(
            'name' => 'Station Branding',
            'type' => 'static',
            'config' => wp_json_encode([
                'image_url' => '',
                'fit' => 'cover'
            ]),
            'z_index' => 0,
            'enabled' => 0,
            'is_active' => 0
        );
        
        $wpdb->insert($table_name, $branding_config);
        $branding_id = $wpdb->insert_id;
        
        // Store default background IDs for reference
        update_option('castconductor_default_gradient_background_id', $gradient_id);
        update_option('castconductor_default_solid_background_id', $solid_id);
        update_option('castconductor_default_branding_background_id', $branding_id);
        
        error_log("CastConductor V5: Created default backgrounds - Gradient (ID: {$gradient_id}, Active), Solid (ID: {$solid_id}), Branding (ID: {$branding_id})");
        
        return $gradient_id;
    }
    
    /**
     * Create default content blocks (called during activation wizard)
     * Creates default content blocks on activation
     */
    public function create_default_content_blocks() {
        global $wpdb;
        
        $table_name = $this->get_table_name('content_blocks');
        $created_blocks = array();

        // Phase: load file-based seeds if available
        // Primary location: includes/seeds/content-block-seeds.php (written by reseed/generate-seeds.php)
        $seeds = [];
        $base_dir = plugin_dir_path(__FILE__);
        $candidates = [
            // Correct path (includes/seeds)
            $base_dir . 'seeds/content-block-seeds.php',
            // Backward-compat fallback: plugin root /seeds (in case some builds placed it there)
            realpath($base_dir . '..') . '/seeds/content-block-seeds.php',
        ];
        $seed_file = '';
        foreach ($candidates as $cand) {
            if ($cand && file_exists($cand)) { $seed_file = $cand; break; }
        }
        if ($seed_file) {
            $loaded = include $seed_file;
            if (is_array($loaded)) {
                $seeds = $loaded;
            }
            if (!empty($seeds)) {
                error_log('CastConductor V5: Loaded file-based content block seeds from ' . $seed_file . ' (' . count($seeds) . ' types)');
            } else {
                error_log('CastConductor V5: Seed file found but empty: ' . $seed_file);
            }
        } else {
            error_log('CastConductor V5: No file-based content block seeds found (looked in includes/seeds and /seeds)');
        }

        // Helper: fetch existing block ID by unique type (V5 defaults are unique per type)
        $get_existing_id = function($type) use ($wpdb, $table_name) {
            $sql = $wpdb->prepare("SELECT id FROM {$table_name} WHERE type = %s ORDER BY id ASC LIMIT 1", $type);
            $id = $wpdb->get_var($sql);
            return $id ? (int)$id : 0;
        };
        
        // Helper to upsert one block from seed
        $upsert_from_seed = function($seed) use ($wpdb, $table_name) {
            if (!is_array($seed)) return 0;
            $name = isset($seed['name']) ? (string)$seed['name'] : 'Unnamed';
            $type = isset($seed['type']) ? (string)$seed['type'] : '';
            if ($type === '') return 0;
            $visual_arr = isset($seed['visual_config']) && is_array($seed['visual_config']) ? $seed['visual_config'] : array();
            $data_arr = isset($seed['data_config']) && is_array($seed['data_config']) ? $seed['data_config'] : array();

            // Safety: if seed omitted data_config, merge minimal defaults per type so live-data works OOTB
            if ($type === 'track_info') {
                if (empty($data_arr) || empty($data_arr['data_source'])) {
                    $data_arr = array(
                        'data_source' => 'metadata_api',
                        'endpoint_override' => '', // will fallback to global WordPress Metadata URL if set
                        'fallback_mode' => 'global_default',
                        'refresh_interval' => 15,
                    );
                }
            }
            // Location/Time block: uses Roku viewer IP geolocation (with server fallback for admin preview)
            if ($type === 'location_time') {
                if (empty($data_arr) || empty($data_arr['data_source']) || $data_arr['data_source'] === 'manual_entry') {
                    $data_arr = array(
                        'data_source' => 'roku_ip_geolocation_api',
                        'refresh_interval' => 60,
                    );
                }
            }
            // Weather block: uses OpenWeatherMap with Roku viewer IP (with server fallback for admin preview)
            if ($type === 'weather') {
                if (empty($data_arr) || empty($data_arr['data_source']) || $data_arr['data_source'] === 'manual_entry') {
                    $data_arr = array(
                        'data_source' => 'openweathermap_api_roku_viewer_ip',
                        'refresh_interval' => 300,
                    );
                }
            }

            $visual = wp_json_encode($visual_arr);
            $data = wp_json_encode($data_arr);
            $enabled = isset($seed['enabled']) ? (int)$seed['enabled'] : 1;

            // One default per type
            $existing_id = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table_name} WHERE type = %s ORDER BY id ASC LIMIT 1", $type));
            if ($existing_id) {
                $wpdb->update($table_name, [
                    'name' => $name,
                    'visual_config' => $visual,
                    'data_config' => $data,
                    'enabled' => $enabled
                ], ['id' => (int)$existing_id]);
                return (int)$existing_id;
            } else {
                $wpdb->insert($table_name, [
                    'name' => $name,
                    'type' => $type,
                    'visual_config' => $visual,
                    'data_config' => $data,
                    'enabled' => $enabled
                ]);
                return (int)$wpdb->insert_id;
            }
        };

        // If file-based seeds exist, apply them but continue to seed remaining inline defaults
        if (!empty($seeds)) {
            foreach ($seeds as $type => $seed) {
                $id = $upsert_from_seed($seed);
                if ($id) { $created_blocks[$type] = $id; }
            }
            // Do not return here; continue with inline defaults for any types not provided by file seeds
            if (!empty($created_blocks)) {
                error_log('CastConductor V5: Applied file-based content block seeds (' . count($created_blocks) . ' types) and continuing with inline defaults for remaining types');
            }
        }

        // 1. Track Info Block - REAL METADATA (legacy inline defaults)
        $track_info_config = array(
            'name' => 'Track Info',
            'type' => 'track_info',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 24,
                    'font_weight' => 'bold',
                    'color' => '#ffffff',
                    'text_shadow' => '2px 2px 4px rgba(0,0,0,0.8)'
                ),
                'background' => array(
                    'type' => 'gradient',
                    'color_start' => '#1e1e1e',
                    'color_end' => '#2a2a2a',
                    'opacity' => 0.9
                ),
                'layout' => array(
                    'show_album_art' => true,
                    'album_art_size' => 160,
                    'album_art_position' => array('x' => 60, 'y' => 740)
                ),
                'album_artwork' => array(
                    'enabled' => true,
                    'size' => '600x600',
                    'max_resolution' => '1280x720',
                    'resize_method' => 'smart_crop',
                    'api_sources' => array('itunes', 'musicbrainz', 'deezer'),
                    'search_timeout' => 10,
                    'cache_duration' => 3600
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'metadata_api',
                'endpoint_override' => '', // Will be configured via Toaster or settings
                'fallback_mode' => 'global_default',
                'refresh_interval' => 15
            )),
            'enabled' => 1
        );
        
        $existing = $get_existing_id('track_info');
        if ($existing) {
            $created_blocks['track_info'] = $existing;
        } else {
            $wpdb->insert($table_name, $track_info_config);
            $created_blocks['track_info'] = (int)$wpdb->insert_id;
        }
        
        // 2. Weather Block - REAL IP GEOLOCATION DATA
        $weather_config = array(
            'name' => 'Weather',
            'type' => 'weather',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 20,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.8
                ),
                'layout' => array(
                    'show_icon' => true,
                    'show_temperature' => true,
                    'show_condition' => true,
                    'show_location' => true
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'openweathermap_api_roku_viewer_ip',
                'api_key_option' => 'castconductor_openweather_api_key',
                'geolocation_source' => 'roku_ip',
                'refresh_interval' => 600 // 10 minutes
            )),
            'enabled' => 1
        );
        
        $existing = $get_existing_id('weather');
        if ($existing) {
            $created_blocks['weather'] = $existing;
        } else {
            $wpdb->insert($table_name, $weather_config);
            $created_blocks['weather'] = (int)$wpdb->insert_id;
        }
        
        // 3. Location/Time Block - REAL ROKU VIEWER GEOLOCATION
        $location_time_config = array(
            'name' => 'Location & Time',
            'type' => 'location_time',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 18,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.8
                ),
                'layout' => array(
                    'show_city' => true,
                    'show_state' => true,
                    'show_time' => true,
                    'time_format' => '12h'
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'roku_ip_geolocation_api',
                'geolocation_service' => 'ipinfo',
                'timezone_auto' => true,
                'refresh_interval' => 60
            )),
            'enabled' => 1
        );
        
        $existing = $get_existing_id('location_time');
        if ($existing) {
            $created_blocks['location_time'] = $existing;
        } else {
            $wpdb->insert($table_name, $location_time_config);
            $created_blocks['location_time'] = (int)$wpdb->insert_id;
        }
        
        // 4. Shoutout Block - REAL ADMIN TEST DATA
        $shoutout_config = array(
            'name' => 'Shoutouts',
            'type' => 'shoutout',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 22,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.9
                ),
                'layout' => array(
                    'show_name' => true,
                    'show_location' => true,
                    'show_timestamp' => false
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'wp_posts_cc_shoutout',
                'post_status' => 'publish',
                'order_by' => 'date',
                'order' => 'DESC',
                'limit' => 10
            )),
            'enabled' => 1
        );
        
        $existing = $get_existing_id('shoutout');
        if ($existing) {
            $created_blocks['shoutout'] = $existing;
        } else {
            $wpdb->insert($table_name, $shoutout_config);
            $created_blocks['shoutout'] = (int)$wpdb->insert_id;
        }
        
        // 5. Sponsor Block - REAL ADMIN TEST CAMPAIGNS
        $sponsor_config = array(
            'name' => 'Sponsors',
            'type' => 'sponsor',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 20,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.9
                ),
                'layout' => array(
                    'show_logo' => true,
                    'show_message' => true,
                    'logo_size' => 160
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'wp_posts_cc_sponsor',
                'post_status' => 'publish',
                'scheduling_enabled' => true,
                'timezone' => wp_timezone_string()
            )),
            'enabled' => 1
        );
        
        $existing = $get_existing_id('sponsor');
        if ($existing) {
            $created_blocks['sponsor'] = $existing;
        } else {
            $wpdb->insert($table_name, $sponsor_config);
            $created_blocks['sponsor'] = (int)$wpdb->insert_id;
        }
        
        // 6. Promo Block - REAL ADMIN TEST CONTENT
        $promo_config = array(
            'name' => 'Promos',
            'type' => 'promo',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 20,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.9
                ),
                'layout' => array(
                    'show_image' => true,
                    'show_title' => true,
                    'show_message' => true,
                    'image_size' => 160
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'wp_posts_cc_promo',
                'post_status' => 'publish',
                'scheduling_enabled' => true,
                'timezone' => wp_timezone_string()
            )),
            'enabled' => 1
        );
        
        $existing = $get_existing_id('promo');
        if ($existing) {
            $created_blocks['promo'] = $existing;
        } else {
            $wpdb->insert($table_name, $promo_config);
            $created_blocks['promo'] = (int)$wpdb->insert_id;
        }
        
        // 7. Custom API Block - CONFIGURED AS NEEDED
        $custom_api_config = array(
            'name' => 'Custom API',
            'type' => 'custom_api',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 18,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.8
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'external_api',
                'api_url' => '',
                'auth_method' => 'none',
                'refresh_interval' => 300
            )),
            'enabled' => 0 // Disabled by default until configured
        );
        
        $existing = $get_existing_id('custom_api');
        if ($existing) {
            $created_blocks['custom_api'] = $existing;
        } else {
            $wpdb->insert($table_name, $custom_api_config);
            $created_blocks['custom_api'] = (int)$wpdb->insert_id;
        }
        
        // 8. Custom Block - MANUAL ENTRY
        $custom_config = array(
            'name' => 'Custom Content',
            'type' => 'custom',
            'visual_config' => json_encode(array(
                'typography' => array(
                    'font_family' => 'Roboto',
                    'font_size' => 20,
                    'color' => '#ffffff'
                ),
                'background' => array(
                    'type' => 'solid',
                    'color' => '#1e1e1e',
                    'opacity' => 0.9
                )
            )),
            'data_config' => json_encode(array(
                'data_source' => 'manual_entry',
                'content' => '',
                'refresh_interval' => 0 // Static content
            )),
            'enabled' => 0 // Disabled by default until content added
        );
        
        $existing = $get_existing_id('custom');
        if ($existing) {
            $created_blocks['custom'] = $existing;
        } else {
            $wpdb->insert($table_name, $custom_config);
            $created_blocks['custom'] = (int)$wpdb->insert_id;
        }
        
        // Store created block IDs for reference
        update_option('castconductor_default_content_blocks', $created_blocks);
        
        error_log('CastConductor V5: Ensured ' . count($created_blocks) . ' default content blocks');
        
        return $created_blocks;
    }

    /**
     * One-time cleanup: de-duplicate content blocks by (type, name), keep oldest ID,
     * update container assignments to point to kept IDs, and remove extras.
     */
    public function maybe_deduplicate_content_blocks() {
        if (get_option('castconductor_dedup_blocks_done')) {
            return;
        }
        $did_work = $this->deduplicate_content_blocks();
        if ($did_work) {
            update_option('castconductor_dedup_blocks_done', current_time('mysql'));
        }
    }

    /**
     * Ensure at least one default scene exists
     */
    public function ensure_default_scene() {
        global $wpdb;
        $table = $this->get_table_name('scenes');

        // If table doesn't exist yet, create it
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) !== $table) {
            $this->create_scenes_table($wpdb->get_charset_collate());
        }

        $existing = $wpdb->get_var("SELECT id FROM {$table} ORDER BY id ASC LIMIT 1");
        if ($existing) {
            return (int) $existing;
        }

        $wpdb->insert($table, array(
            'name' => 'Default Scene',
            'description' => 'Initial scene created automatically',
            'is_active' => 1,
            'rotation_enabled' => 0,
            'rotation_interval' => 60,
            'branding' => wp_json_encode(array()),
            'background' => wp_json_encode(array()),
            'metadata' => wp_json_encode(array())
        ));
        $scene_id = (int) $wpdb->insert_id;

        // Mark as active and ensure only one active
        $wpdb->query($wpdb->prepare("UPDATE {$table} SET is_active = CASE WHEN id = %d THEN 1 ELSE 0 END", $scene_id));

        return $scene_id;
    }

    /**
     * Get the active scene row (or null)
     */
    public function get_active_scene() {
        global $wpdb;
        $table = $this->get_table_name('scenes');
        return $wpdb->get_row("SELECT * FROM {$table} WHERE is_active = 1 ORDER BY id ASC LIMIT 1");
    }

    /**
     * Get a scene using weighted random selection based on rotation_percentage.
     * 
     * Rotation Logic:
     * - If multiple scenes have rotation_percentage > 0, select randomly based on weights
     * - rotation_percentage values are relative weights (don't need to sum to 100)
     * - Scenes with rotation_percentage = 0 are excluded from rotation
     * - If no scenes have rotation enabled, falls back to get_active_scene()
     * 
     * @return object|null Scene row or null
     */
    public function get_weighted_scene() {
        global $wpdb;
        $table = $this->get_table_name('scenes');
        
        // Get all scenes with rotation_percentage > 0
        $scenes = $wpdb->get_results("SELECT * FROM {$table} WHERE rotation_percentage > 0 ORDER BY id ASC");
        
        if (empty($scenes)) {
            // No scenes with rotation enabled, fall back to active scene
            return $this->get_active_scene();
        }
        
        // Calculate total weight
        $total_weight = 0.0;
        foreach ($scenes as $scene) {
            $total_weight += floatval($scene->rotation_percentage);
        }
        
        if ($total_weight <= 0) {
            return $this->get_active_scene();
        }
        
        // Generate random number and select scene
        $random = mt_rand() / mt_getrandmax() * $total_weight;
        $cumulative = 0.0;
        
        foreach ($scenes as $scene) {
            $cumulative += floatval($scene->rotation_percentage);
            if ($random <= $cumulative) {
                error_log("CastConductor V5: Weighted selection chose scene {$scene->id} ({$scene->name}) - {$scene->rotation_percentage}%");
                return $scene;
            }
        }
        
        // Fallback to last scene (shouldn't happen)
        return end($scenes);
    }

    /**
     * Get per-scene container overrides map: container_id => {overrides, zones}
     */
    public function get_scene_container_overrides($scene_id) {
        global $wpdb;
        $table = $this->get_table_name('scene_containers');
        $rows = $wpdb->get_results($wpdb->prepare("SELECT container_id, overrides, zones, enabled FROM {$table} WHERE scene_id = %d", $scene_id));
        $map = array();
        foreach ($rows as $r) {
            if (isset($r->enabled) && (int)$r->enabled !== 1) { continue; }
            $map[(int)$r->container_id] = array(
                'overrides' => $this->maybe_decode_json($r->overrides),
                'zones' => $this->maybe_decode_json($r->zones)
            );
        }
        return $map;
    }

    /**
     * Utility: decode JSON string to array if needed
     */
    private function maybe_decode_json($value) {
        if (is_array($value) || is_object($value)) { return json_decode(wp_json_encode($value), true); }
        if (is_string($value) && $value !== '') {
            $decoded = json_decode($value, true);
            return (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) ? $decoded : array();
        }
        return array();
    }

    /**
     * Perform de-duplication. Returns true if any changes were made.
     */
    public function deduplicate_content_blocks() {
        global $wpdb;
        $tbl = $this->get_table_name('content_blocks');
        $assign_tbl = $this->get_table_name('container_blocks');

        // Find duplicates by (type, name)
        $dups = $wpdb->get_results(
            "SELECT type, name, COUNT(*) as cnt FROM {$tbl} GROUP BY type, name HAVING cnt > 1",
            ARRAY_A
        );
        if (empty($dups)) {
            return false;
        }

        $wpdb->query('START TRANSACTION');
        $changed = false;
        foreach ($dups as $row) {
            $type = $row['type'];
            $name = $row['name'];
            // Get all IDs ordered by oldest first, keep the first
            $ids = $wpdb->get_col($wpdb->prepare(
                "SELECT id FROM {$tbl} WHERE type = %s AND name = %s ORDER BY id ASC",
                $type, $name
            ));
            if (!$ids || count($ids) < 2) continue;
            $keep = (int) array_shift($ids);
            $extras = array_map('intval', $ids);
            if (!empty($extras)) {
                // Re-point any container assignments to kept ID
                $in = implode(',', array_fill(0, count($extras), '%d'));
                $sql = $wpdb->prepare(
                    "UPDATE {$assign_tbl} SET content_block_id = %d WHERE content_block_id IN ($in)",
                    array_merge([$keep], $extras)
                );
                $wpdb->query($sql);
                // Delete extras
                $in2 = implode(',', array_fill(0, count($extras), '%d'));
                $wpdb->query($wpdb->prepare("DELETE FROM {$tbl} WHERE id IN ($in2)", $extras));
                $changed = true;
            }
        }
        if ($changed) {
            $wpdb->query('COMMIT');
            // Refresh default content blocks option to point to kept IDs
            $map = [];
            $rows = $wpdb->get_results("SELECT id, type FROM {$tbl}", ARRAY_A);
            foreach ($rows as $r) {
                $map[$r['type']] = (int)$r['id'];
            }
            // Preserve only known defaults
            $defaults = [
                'track_info','weather','location_time','shoutout','sponsor','promo','custom_api','custom'
            ];
            $final = [];
            foreach ($defaults as $t) { if (isset($map[$t])) { $final[$t] = $map[$t]; } }
            if (!empty($final)) {
                update_option('castconductor_default_content_blocks', $final);
            }
        } else {
            $wpdb->query('ROLLBACK');
        }
        return $changed;
    }

    /**
     * Canonicalize default seeded content block IDs across assignments by TYPE (ignores name differences).
     * For each known default type, pick the most recently updated row as canonical and repoint all
     * container assignments to that ID. This ensures Scenes preview renders the redesigned JSON
     * after exports/updates, even if older rows with the same type/name still exist.
     */
    public function canonicalize_default_blocks_assignments() {
        global $wpdb;
        $tbl = $this->get_table_name('content_blocks');
        $assign_tbl = $this->get_table_name('container_blocks');
        $scenes_tbl = $this->get_table_name('scene_containers');

        // Only enforce for default seed types (unique by design)
        $defaultTypes = ['track_info','weather','location_time','shoutout','sponsor','promo'];
        $mapTypeToId = [];
        
        foreach ($defaultTypes as $type) {
            // Pick the most recently updated block of this type as canonical
            $row = $wpdb->get_row($wpdb->prepare(
                "SELECT id FROM {$tbl} WHERE type = %s ORDER BY updated_at DESC, id DESC LIMIT 1",
                $type
            ));
            if (!$row || empty($row->id)) { continue; }
            $canonicalId = (int) $row->id;
            $mapTypeToId[$type] = $canonicalId;
            
            // Repoint any assignments that reference a different ID of the same type
            // First, find all other ids for this type
            $otherIds = $wpdb->get_col($wpdb->prepare(
                "SELECT id FROM {$tbl} WHERE type = %s AND id <> %d",
                $type, $canonicalId
            ));
            
            if (!empty($otherIds)) {
                // 1. Update global container assignments
                $placeholders = implode(',', array_fill(0, count($otherIds), '%d'));
                $sql = $wpdb->prepare(
                    "UPDATE {$assign_tbl} SET content_block_id = %d WHERE content_block_id IN ($placeholders)",
                    array_merge([$canonicalId], array_map('intval', $otherIds))
                );
                $wpdb->query($sql);
                
                // 2. Update per-scene container zone assignments (JSON blobs)
                // Fetch all scene containers that might have zones
                $scene_rows = $wpdb->get_results("SELECT id, zones FROM {$scenes_tbl} WHERE zones IS NOT NULL AND zones != '' AND zones != '[]'");
                
                foreach ($scene_rows as $s_row) {
                    $zones = json_decode($s_row->zones, true);
                    if (!is_array($zones)) continue;
                    
                    $changed = false;
                    foreach ($zones as $z_key => $z_val) {
                        if (isset($z_val['content_block_id']) && in_array((int)$z_val['content_block_id'], $otherIds)) {
                            $zones[$z_key]['content_block_id'] = $canonicalId;
                            $changed = true;
                        }
                    }
                    
                    if ($changed) {
                        $wpdb->update(
                            $scenes_tbl,
                            ['zones' => wp_json_encode($zones)],
                            ['id' => $s_row->id],
                            ['%s'],
                            ['%d']
                        );
                    }
                }
            }
        }

        // Update defaults option to reflect canonical IDs
        if (!empty($mapTypeToId)) {
            $existing = get_option('castconductor_default_content_blocks', []);
            if (!is_array($existing)) { $existing = []; }
            $merged = array_merge($existing, $mapTypeToId);
            update_option('castconductor_default_content_blocks', $merged);
        }
    }
    
    /**
     * Get container by ID
     */
    public function get_container($id) {
        global $wpdb;
        
        $table_name = $this->get_table_name('containers');
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table_name} WHERE id = %d", $id));
    }
    
    /**
     * Get content block by ID
     */
    public function get_content_block($id) {
        global $wpdb;
        
        $table_name = $this->get_table_name('content_blocks');
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table_name} WHERE id = %d", $id));
    }
    
    /**
     * Get container assignments
     */
    public function get_container_assignments($container_id) {
        global $wpdb;
        
        $table_name = $this->get_table_name('container_blocks');
        $blocks_table = $this->get_table_name('content_blocks');
        
        return $wpdb->get_results($wpdb->prepare("
            SELECT cb.*, bl.name, bl.type, bl.visual_config, bl.data_config 
            FROM {$table_name} cb 
            JOIN {$blocks_table} bl ON cb.content_block_id = bl.id 
            WHERE cb.container_id = %d AND cb.enabled = 1 AND bl.enabled = 1
            ORDER BY cb.rotation_order ASC
        ", $container_id));
    }
    
    /**
     * Create backgrounds table
     * Stores global background configurations for the 1920x1080 display layer system
     */
    private function create_backgrounds_table($charset_collate) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'castconductor_backgrounds';
        
        $sql = "CREATE TABLE {$table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            container_id BIGINT(20) UNSIGNED DEFAULT NULL,
            name VARCHAR(200) NOT NULL,
            type VARCHAR(20) NOT NULL DEFAULT 'static',
            config TEXT NOT NULL,
            z_index INT(11) DEFAULT 0,
            enabled TINYINT(1) DEFAULT 1,
            is_active TINYINT(1) DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY container_id (container_id),
            KEY type_enabled (type, enabled),
            KEY is_active (is_active),
            KEY z_index (z_index),
            KEY created_at (created_at)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Verify table creation
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name) {
            error_log("CastConductor V5: Created table {$table_name}");
        } else {
            error_log("CastConductor V5: Failed to create table {$table_name}");
        }
    }
}
