thoughtspot_deployer.py

#!/usr/bin/env python3
"""
ThoughtSpot Deployment Module

A comprehensive tool for deploying data models to ThoughtSpot:
- Creates Snowflake connections
- Parses DDL and creates tables
- Generates and deploys models

Usage:
    from thoughtspot_deployer import ThoughtSpotDeployer
    
    deployer = ThoughtSpotDeployer()
    results = deployer.deploy_all(ddl, database, schema)
"""

import os
import time
import subprocess
from supabase_client import get_admin_setting
import re
import yaml
import json
import requests
import snowflake.connector
from datetime import datetime
from typing import Dict, List, Optional, Tuple
from dotenv import load_dotenv
from snowflake_auth import get_snowflake_connection_params
from thoughtspot_errors import friendly_mcp_liveboard_error

# Load environment variables
load_dotenv()


def _get_code_version() -> str:
    """Return the deployed code version for run lineage/debugging."""
    for env_name in ("DEMO_PREP_COMMIT", "SPACE_COMMIT_SHA", "GIT_COMMIT", "COMMIT_SHA"):
        value = os.getenv(env_name)
        if value:
            return value[:12]
    try:
        result = subprocess.run(
            ["git", "rev-parse", "--short", "HEAD"],
            cwd=os.path.dirname(os.path.abspath(__file__)),
            capture_output=True,
            text=True,
            timeout=2,
        )
        if result.returncode == 0 and result.stdout.strip():
            return result.stdout.strip()
    except Exception:
        pass
    return "unknown"


def _safe_print(*args, **kwargs):
    """Print that ignores BrokenPipeError - prevents crashes when output is closed."""
    try:
        print(*args, **kwargs)
    except BrokenPipeError:
        pass


def _apply_naming_style(name: str, style: str = "snake_case") -> str:
    """
    Convert column name to specified naming style for ThoughtSpot display.
    
    Args:
        name: Original column name
        style: Naming style - one of: Regular Case, snake_case, camelCase, PascalCase, UPPER_CASE, original
    
    Examples (for "SHIPPING_MODE"):
        Regular Case → Shipping Mode
        snake_case   → shipping_mode
        camelCase    → shippingMode
        PascalCase   → ShippingMode
        UPPER_CASE   → SHIPPING_MODE
        original     → SHIPPING_MODE (unchanged)
    """
    name = name.strip()
    
    if style == "original":
        return name
    
    if style == "UPPER_CASE":
        return name.upper().replace(" ", "_")
    
    # Split into words (handle underscores, spaces, and camelCase)
    import re
    # Split on underscores, spaces, or camelCase boundaries
    words = re.split(r'[_\s]+', name)
    # Further split camelCase words
    expanded_words = []
    for word in words:
        # Split on camelCase boundaries (e.g., "firstName" -> ["first", "Name"])
        parts = re.findall(r'[A-Z]?[a-z]+|[A-Z]+(?=[A-Z][a-z]|\d|\W|$)|\d+', word)
        if parts:
            expanded_words.extend(parts)
        else:
            expanded_words.append(word)
    
    words = [w.lower() for w in expanded_words if w]
    
    if not words:
        return name.lower()
    
    if style == "Regular Case":
        # Title case each word, join with spaces: STATE_ID -> State Id
        return " ".join(w.capitalize() for w in words)
    
    if style == "snake_case":
        return "_".join(words)
    
    elif style == "camelCase":
        # First word lowercase, rest capitalized
        return words[0] + "".join(w.capitalize() for w in words[1:])
    
    elif style == "PascalCase":
        # All words capitalized
        return "".join(w.capitalize() for w in words)
    
    else:
        # Default to snake_case
        return "_".join(words)


def _to_snake_case(name: str) -> str:
    """
    Legacy function - converts to snake_case.
    Use _apply_naming_style() for more options.
    """
    return _apply_naming_style(name, "snake_case")


def _infer_liveboard_context_from_custom_request(text: str) -> Tuple[str, str]:
    """Infer a coarse vertical/function label for custom liveboard question generation."""
    normalized = (text or "").lower()
    rules = [
        (("ad yield", "arpu", "ctv", "smartcast", "fast channel", "ad monetization"), ("Media & Entertainment", "Marketing")),
        (("retail sales", "ecommerce", "product performance", "store", "sales"), ("Retail & Consumer Goods", "Sales")),
        (("saas", "subscription", "arr", "mrr", "churn"), ("Technology", "Finance")),
        (("banking", "deposit", "loan", "wealth"), ("Financial Services", "Finance")),
        (("hotel", "hospitality", "occupancy", "adr", "revpar"), ("Travel & Hospitality", "Finance")),
        (("trucking", "shipping", "logistics", "carrier", "shipment"), ("Transportation & Logistics", "Finance")),
        (("healthcare", "patient", "clinical", "pharma"), ("Healthcare & Life Sciences", "Operations")),
    ]
    for terms, labels in rules:
        if any(term in normalized for term in terms):
            return labels
    return None, None


def prepare_liveboard_creation_context(
    *,
    ts_client,
    model_guid: str,
    tables: Dict,
    company_name: str = None,
    use_case: str = None,
    additional_context: str = None,
    vertical: str = None,
    line: str = None,
    function: str = None,
    log_callback=None,
) -> Dict:
    """Build MCP liveboard inputs after model creation and before liveboard creation."""
    warnings = []

    def _log(message: str) -> None:
        if log_callback:
            log_callback(message)

    clean_company = (
        company_name.split('.')[0].title()
        if company_name and '.' in company_name
        else (company_name or 'Demo Company')
    )
    company_data = {
        'name': clean_company,
        'use_case': use_case or 'General Analytics',
        'additional_context': additional_context or '',
        # Pass the raw company input through as a URL/domain hint so the overview
        # note tile can derive a brand logo (e.g. "nike.com"). Non-domain names
        # fall back to a monogram badge downstream.
        'url': company_name,
    }

    # Use ThoughtSpot model columns first because TS can rename imported columns.
    model_columns = ts_client.get_model_columns(model_guid) if ts_client and model_guid else []
    if not model_columns:
        warning = "Could not get model columns, using DDL columns"
        warnings.append(warning)
        _log(f"   [WARN] {warning}")
        model_columns = []
        for columns_list in (tables or {}).values():
            model_columns.extend(columns_list)
    company_data['model_columns'] = model_columns

    matrix_config = None
    matrix_label = None
    resolved_vertical = None
    resolved_function = None
    inferred_custom_context = False
    try:
        from demo_personas import parse_use_case, get_use_case_config
        parsed_vertical, parsed_function = parse_use_case(use_case or '')
        resolved_vertical = line or vertical or parsed_vertical
        resolved_function = function or parsed_function
        if not resolved_vertical or not resolved_function:
            inferred_vertical, inferred_function = _infer_liveboard_context_from_custom_request(
                "\n".join(
                    part
                    for part in [
                        use_case or "",
                        additional_context or "",
                        company_name or "",
                    ]
                    if part
                )
            )
            resolved_vertical = resolved_vertical or inferred_vertical
            resolved_function = resolved_function or inferred_function
            inferred_custom_context = bool(inferred_vertical or inferred_function)
        uc_config = get_use_case_config(
            resolved_vertical or "Generic",
            resolved_function or "Generic",
            vertical_fallback=vertical if line else None,
        )
        if uc_config.get("liveboard_questions"):
            matrix_config = uc_config
            matrix_label = (
                f"{vertical}/{line}×{resolved_function}"
                if vertical and line
                else f"{resolved_vertical}×{resolved_function}"
            )
            _log(
                f"   [MCP] Matrix config loaded: {matrix_label} "
                f"({len(uc_config['liveboard_questions'])} story questions)"
            )
        else:
            _log(
                f"   [MCP] No matrix coverage for {resolved_vertical}×{resolved_function} "
                "— using custom AI generation"
            )
    except Exception as matrix_err:
        warnings.append(f"Matrix config load skipped: {matrix_err}")
        _log(f"   [MCP] Matrix config load skipped: {matrix_err}")

    if inferred_custom_context:
        company_data['resolved_vertical'] = resolved_vertical
        company_data['resolved_function'] = resolved_function

    return {
        'company_data': company_data,
        'model_columns': model_columns,
        'matrix_config': matrix_config,
        'matrix_label': matrix_label,
        'resolved_vertical': resolved_vertical,
        'resolved_function': resolved_function,
        'warnings': warnings,
        'ready': bool(model_guid and model_columns),
    }


class ThoughtSpotDeployer:
    """ThoughtSpot deployment automation"""
    
    def __init__(self, base_url: str = None, username: str = None, secret_key: str = None):
        """
        Initialize ThoughtSpot deployer (trusted auth only)
        
        Reads from environment variables if not passed directly.
        Env vars are populated from Supabase admin settings at login time.
        Raises ValueError if any required setting is missing.
        """
        self.base_url = base_url if base_url else ''
        if not username:
            raise ValueError("ThoughtSpotDeployer requires username — pass the logged-in user's email")
        self.username = username
        if not secret_key:
            raise ValueError("ThoughtSpotDeployer requires secret_key — pass the trusted auth key for the selected environment")
        self.secret_key = secret_key
        
        # Snowflake connection details from environment (key pair auth)
        self.sf_account = get_admin_setting('SNOWFLAKE_ACCOUNT')
        self.sf_user = get_admin_setting('SNOWFLAKE_KP_USER')
        self.sf_role = get_admin_setting('SNOWFLAKE_ROLE')
        self.sf_warehouse = get_admin_setting('SNOWFLAKE_WAREHOUSE')
        
        self.headers = {
            'Content-Type': 'application/json',
            'X-Requested-By': 'ThoughtSpot'
        }
        
        # Use session to maintain cookies between requests
        self.session = requests.Session()
        self.session.headers.update(self.headers)
        self.last_auth_status_code = None
        self.last_auth_error = ""
        
        # Column naming style for ThoughtSpot model columns
        # Options: Regular Case, snake_case, camelCase, PascalCase, UPPER_CASE, original
        self.column_naming_style = "Regular Case"

        # Per-session prompt logger — set by the chat controller after construction
        self.prompt_logger = None
        
        # Validate credentials for trusted auth
        if not all([self.base_url, self.username, self.secret_key]):
            raise ValueError("Missing ThoughtSpot URL, username, or trusted auth key")
        
        if not all([self.sf_account, self.sf_user, self.sf_role, self.sf_warehouse]):
            raise ValueError("Missing required Snowflake credentials in environment variables")
    
    def _get_private_key_for_thoughtspot(self) -> str:
        """Get private key in format suitable for ThoughtSpot TML"""
        private_key_raw = get_admin_setting('SNOWFLAKE_KP_PK')
        
        if not private_key_raw:
            raise ValueError("SNOWFLAKE_KP_PK environment variable not set")
        
        # ThoughtSpot expects the private key as raw PEM format string
        if not private_key_raw.startswith('-----BEGIN'):
            # If it's base64 encoded, decode it
            import base64
            try:
                private_key_raw = base64.b64decode(private_key_raw).decode('utf-8')
            except Exception:
                pass
        
        return private_key_raw

    def authenticate(self) -> bool:
        """Authenticate with ThoughtSpot using trusted authentication"""
        return self.authenticate_trusted()

    def _is_transient_auth_error(self, status_code: int = None, message: str = "") -> bool:
        text = str(message or "").lower()
        if status_code in {429, 500, 502, 503, 504}:
            return True
        return any(
            term in text
            for term in (
                "bad gateway",
                "gateway time-out",
                "gateway timeout",
                "temporarily unavailable",
                "timeout",
                "timed out",
                "connection aborted",
                "connection reset",
                "remote end closed connection",
                "too many requests",
            )
        )
    
    def authenticate_trusted(self) -> bool:
        """Authenticate with ThoughtSpot using trusted authentication (secret key)"""
        self.last_auth_status_code = None
        self.last_auth_error = ""
        auth_url = f"{self.base_url}/api/rest/2.0/auth/token/full"
        max_attempts = max(1, int(os.getenv("TS_AUTH_MAX_ATTEMPTS", "3")))
        base_wait_seconds = max(1, int(os.getenv("TS_AUTH_RETRY_WAIT_SECONDS", "5")))

        for attempt in range(1, max_attempts + 1):
            try:
                print(f"   🔐 Attempting trusted authentication to: {auth_url}")
                print(f"   👤 Username: {self.username}")
                print(f"   🔁 Auth attempt: {attempt}/{max_attempts}")
                print(f"   🔑 Using secret key: {self.secret_key[:8]}...{self.secret_key[-4:]}" if self.secret_key and len(self.secret_key) > 12 else "   🔑 Using secret key")

                response = self.session.post(
                    auth_url,
                    json={
                        "username": self.username,
                        "secret_key": self.secret_key,
                        "validity_time_in_sec": 3600  # 1 hour token
                    },
                    timeout=60,
                )

                print(f"   📡 HTTP Status: {response.status_code}")
                self.last_auth_status_code = response.status_code

                if response.status_code == 200:
                    result = response.json()
                    if 'token' in result:
                        # Use the token as bearer auth
                        self.session.headers['Authorization'] = f'Bearer {result["token"]}'
                        print("   ✅ Trusted authentication successful (bearer token)")
                        return True
                    print(f"   ❌ No token in response: {result}")
                    self.last_auth_error = f"No token in response: {result}"
                    return False
                if response.status_code == 204:
                    # Session cookie auth
                    print("   ✅ Trusted authentication successful (session cookies)")
                    return True

                print(f"   ❌ HTTP Error {response.status_code}: {response.text}")
                self.last_auth_error = response.text[:500]
                if not self._is_transient_auth_error(response.status_code, self.last_auth_error):
                    return False

            except Exception as e:
                print(f"   💥 Trusted authentication exception: {e}")
                self.last_auth_error = str(e)
                self.last_auth_status_code = None
                if not self._is_transient_auth_error(None, self.last_auth_error):
                    return False

            if attempt < max_attempts:
                wait_seconds = base_wait_seconds * attempt
                print(f"   ⏳ Auth failed with transient error; retrying in {wait_seconds}s")
                time.sleep(wait_seconds)

        return False

    def authenticate_oauth(self, timeout: int = 120) -> bool:
        """
        Authenticate with ThoughtSpot using browser-based SSO (Okta, SAML, etc.)
        
        Opens browser to ThoughtSpot login, user authenticates via SSO,
        and cookies are captured via a local callback server.
        
        Args:
            timeout: Seconds to wait for authentication (default 120)
            
        Returns:
            True if authentication successful, False otherwise
        """
        import webbrowser
        import http.server
        import socketserver
        import threading
        import urllib.parse
        
        print(f"   🔐 Starting OAuth/SSO authentication for: {self.base_url}")
        print(f"   👤 User: {self.username or 'SSO user'}")
        
        # Find an available port for the callback server
        callback_port = 8765
        for port in range(8765, 8800):
            try:
                with socketserver.TCPServer(("", port), None) as test:
                    callback_port = port
                    break
            except OSError:
                continue
        
        callback_url = f"http://localhost:{callback_port}/callback"
        auth_complete = threading.Event()
        auth_success = [False]  # Use list to allow modification in nested function
        
        class OAuthCallbackHandler(http.server.BaseHTTPRequestHandler):
            def log_message(self, format, *args):
                pass  # Suppress logging
                
            def do_GET(self):
                if self.path.startswith('/callback'):
                    # Authentication completed - show success page
                    self.send_response(200)
                    self.send_header('Content-type', 'text/html')
                    self.end_headers()
                    
                    # Page that extracts cookies and displays success
                    html = """
                    <!DOCTYPE html>
                    <html>
                    <head>
                        <title>ThoughtSpot Authentication</title>
                        <style>
                            body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif; 
                                   display: flex; justify-content: center; align-items: center; 
                                   height: 100vh; margin: 0; background: #f5f5f5; }
                            .container { text-align: center; background: white; padding: 40px; 
                                        border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
                            .success { color: #28a745; font-size: 48px; }
                            h1 { color: #333; }
                            p { color: #666; }
                        </style>
                    </head>
                    <body>
                        <div class="container">
                            <div class="success">✓</div>
                            <h1>Authentication Successful!</h1>
                            <p>You can close this window and return to the application.</p>
                        </div>
                    </body>
                    </html>
                    """
                    self.wfile.write(html.encode())
                    auth_success[0] = True
                    auth_complete.set()
                    
                elif self.path == '/check':
                    # Health check endpoint
                    self.send_response(200)
                    self.send_header('Content-type', 'text/plain')
                    self.end_headers()
                    self.wfile.write(b'OK')
                else:
                    self.send_response(404)
                    self.end_headers()
        
        # Start callback server in background thread
        server = socketserver.TCPServer(("", callback_port), OAuthCallbackHandler)
        server_thread = threading.Thread(target=server.handle_request)
        server_thread.daemon = True
        server_thread.start()
        
        # Build the SSO login URL
        # ThoughtSpot redirects to SSO provider, then back to ThoughtSpot, then to our callback
        ts_login_url = f"{self.base_url}/?redirectURL={urllib.parse.quote(callback_url)}"
        
        print(f"   🌐 Opening browser for SSO login...")
        print(f"   📍 Callback URL: {callback_url}")
        print(f"   ⏳ Waiting up to {timeout} seconds for authentication...")
        
        # Open browser to ThoughtSpot login
        webbrowser.open(ts_login_url)
        
        # Wait for authentication to complete
        if auth_complete.wait(timeout=timeout):
            if auth_success[0]:
                print("   ✅ Browser authentication completed!")
                
                # Now we need to get the session from ThoughtSpot
                # The user authenticated in the browser, so we need to get a session token
                # We'll use the session/token endpoint to get a token for API calls
                
                # Try to get a session token using the trusted auth flow
                # Since user is now logged in via browser, we attempt to get session info
                try:
                    # Check if session is valid by calling a simple API endpoint
                    # First, let's try to get current user info
                    user_response = self.session.get(
                        f"{self.base_url}/api/rest/2.0/auth/session/user",
                        timeout=10
                    )
                    
                    if user_response.status_code == 200:
                        user_info = user_response.json()
                        print(f"   ✅ Session active for: {user_info.get('name', 'unknown')}")
                        return True
                    else:
                        # Browser auth completed but we don't have cookies in our session
                        # This is expected - browser and Python have separate cookie jars
                        print("   ⚠️  Browser authenticated but Python session needs cookies")
                        print("   💡 For full OAuth support, please use the browser-based workflow")
                        print("   💡 Or configure trusted authentication on ThoughtSpot")
                        return False
                        
                except Exception as e:
                    print(f"   ⚠️  Could not verify session: {e}")
                    return False
            else:
                print("   ❌ Authentication callback received but marked as failed")
                return False
        else:
            print("   ❌ Authentication timed out")
            server.shutdown()
            return False

    def get_model_columns(self, model_guid: str) -> List[Dict]:
        """
        Get actual column names from a ThoughtSpot model.
        
        This is important because ThoughtSpot may rename columns to make them unique
        (e.g., PROCESSING_FEE becomes gift_processing_fee and tran_processing_fee).
        
        Args:
            model_guid: GUID of the ThoughtSpot model
            
        Returns:
            List of column dicts with 'name' and 'type' keys
        """
        try:
            # Export the model TML to get actual column names
            export_response = self.session.post(
                f"{self.base_url}/api/rest/2.0/metadata/tml/export",
                json={
                    'metadata': [{'identifier': model_guid}],
                    'export_associated': False
                }
            )
            
            if export_response.status_code != 200:
                print(f"   ⚠️ Could not export model TML: HTTP {export_response.status_code}")
                return []
            
            tml_data = export_response.json()
            if not tml_data or len(tml_data) == 0:
                print(f"   ⚠️ Empty TML export response")
                return []
            
            # Parse YAML TML
            tml_str = tml_data[0].get('edoc', '')
            model_tml = yaml.safe_load(tml_str)
            
            if not model_tml or 'model' not in model_tml:
                print(f"   ⚠️ Invalid model TML structure")
                return []
            
            # Extract columns with their actual names from the model
            columns = []
            for col in model_tml.get('model', {}).get('columns', []):
                col_name = col.get('name', '')
                col_props = col.get('properties', {})
                col_type = col_props.get('column_type', 'ATTRIBUTE')
                
                # Map ThoughtSpot column types to SQL-like types for AI understanding
                if col_type == 'MEASURE':
                    sql_type = 'NUMBER'  # Measures are numeric
                elif col_props.get('calendar'):
                    sql_type = 'DATE'    # Calendar attribute = date column
                else:
                    sql_type = 'VARCHAR'  # Other attributes are typically strings

                columns.append({
                    'name': col_name,
                    'type': sql_type,
                    'ts_type': col_type  # Keep original for reference
                })
            
            print(f"   📊 Got {len(columns)} columns from ThoughtSpot model")
            return columns
            
        except Exception as e:
            print(f"   ⚠️ Error getting model columns: {e}")
            return []

    def wait_for_model_answer_ready(
        self,
        model_guid: str,
        model_columns: List[Dict],
        log_callback=None,
        session_logger=None,
        timeout_seconds: int = None,
        poll_interval_seconds: int = None,
    ) -> bool:
        """Wait until ThoughtSpot's answer service can query the newly-created model."""
        timeout_seconds = timeout_seconds if timeout_seconds is not None else int(
            os.getenv("TS_MODEL_READY_TIMEOUT_SECONDS", "300")
        )
        poll_interval_seconds = poll_interval_seconds if poll_interval_seconds is not None else int(
            os.getenv("TS_MODEL_READY_POLL_INTERVAL_SECONDS", "30")
        )
        poll_interval_seconds = max(1, poll_interval_seconds)
        timeout_seconds = max(poll_interval_seconds, timeout_seconds)

        def _log(message):
            if log_callback:
                log_callback(message)
            else:
                print(message, flush=True)

        def _answer_has_liveboard_tokens(data: Dict) -> bool:
            if not isinstance(data, dict):
                return False
            if not data.get("session_identifier"):
                return False
            tokens = data.get("tokens")
            display_tokens = data.get("display_tokens")
            has_tokens = False
            for value in (tokens, display_tokens):
                if isinstance(value, str) and value.strip():
                    has_tokens = True
                elif isinstance(value, list) and value:
                    has_tokens = True
            if not has_tokens:
                return False
            if data.get("generation_number") == -1:
                return False
            return True

        def _is_business_measure(col: Dict) -> bool:
            name = str(col.get("name") or "").lower()
            if col.get("type") != "NUMBER":
                return False
            non_business_terms = (" key", "_key", " id", "_id", "month num", "year num", "quarter num")
            return not any(term in name for term in non_business_terms)

        measure = next((col for col in model_columns if _is_business_measure(col)), None)
        if not measure:
            measure = next((col for col in model_columns if col.get("type") == "NUMBER"), None)
        if measure:
            probe_query = f"sum [{measure.get('name')}]"
        elif model_columns:
            probe_query = f"[{model_columns[0].get('name')}]"
        else:
            probe_query = None

        if not probe_query:
            _log("   ⚠️  Model readiness check skipped: no columns available")
            return False

        deadline = time.time() + timeout_seconds
        attempt = 0
        last_error = ""

        while True:
            attempt += 1
            try:
                response = self.session.post(
                    f"{self.base_url}/api/rest/2.0/ai/answer/create",
                    json={
                        "query": probe_query,
                        "metadata_identifier": model_guid,
                    },
                    timeout=60,
                )
                if response.status_code == 200:
                    data = response.json() or {}
                    if _answer_has_liveboard_tokens(data):
                        _log(f"   [OK] Model answer-ready after {attempt} probe(s)")
                        if session_logger:
                            session_logger.log(
                                "thoughtspot",
                                "model answer-ready",
                                model_guid=model_guid,
                                probe_query=probe_query,
                                attempts=attempt,
                        )
                        return True
                    last_error = (
                        "answer response missing liveboard-compatible tokens "
                        f"(keys={list(data.keys())}, "
                        f"visualization_type={data.get('visualization_type')}, "
                        f"generation_number={data.get('generation_number')}, "
                        f"tokens_type={type(data.get('tokens')).__name__}, "
                        f"display_tokens_type={type(data.get('display_tokens')).__name__})"
                    )
                else:
                    last_error = f"HTTP {response.status_code}: {response.text[:300]}"
            except Exception as exc:
                last_error = f"{type(exc).__name__}: {exc}"

            _log(
                f"   ⏳ Model answer-ready poll {attempt}: not ready "
                f"({last_error[:180]})"
            )
            if session_logger:
                session_logger.log(
                    "thoughtspot",
                    "model answer-ready poll",
                    model_guid=model_guid,
                    probe_query=probe_query,
                    attempt=attempt,
                    ready=False,
                    error=last_error[:1000],
                    timeout_seconds=timeout_seconds,
                    poll_interval_seconds=poll_interval_seconds,
                )

            if time.time() >= deadline:
                _log("   ⚠️  Model answer-ready check timed out; continuing to liveboard creation")
                return False
            time.sleep(min(poll_interval_seconds, max(0, deadline - time.time())))

    def parse_ddl(self, ddl: str) -> Tuple[Dict, List]:
        """
        Parse DDL to extract table definitions and foreign key relationships
        
        Returns:
            Tuple of (tables_dict, foreign_keys_list)
        """
        tables = {}
        foreign_keys = []
        
        # Find all CREATE TABLE statements
        table_pattern = (
            r'CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?'
            r'(?:"?([A-Za-z0-9_]+)"?)\s*\((.*?)\)\s*;'
        )
        
        for match in re.finditer(table_pattern, ddl, re.IGNORECASE | re.DOTALL):
            table_name = match.group(1).upper()
            columns_text = match.group(2)
            
            columns = []
            # Parse each column definition - PROPERLY FIXED parsing
            # Split by comma but be careful of commas inside parentheses
            column_lines = []
            current_line = ""
            paren_count = 0
            
            for char in columns_text:
                if char == '(':
                    paren_count += 1
                elif char == ')':
                    paren_count -= 1
                elif char == ',' and paren_count == 0:
                    column_lines.append(current_line.strip())
                    current_line = ""
                    continue
                current_line += char
            
            # Add the last line
            if current_line.strip():
                column_lines.append(current_line.strip())
            
            for line in column_lines:
                line = line.strip()
                line_upper = line.upper()

                def _normalize_table_name(raw_name: str) -> str:
                    # Handle optional quoting and optional DB/SCHEMA qualifiers.
                    normalized = raw_name.replace('"', '').strip()
                    if '.' in normalized:
                        normalized = normalized.split('.')[-1]
                    return normalized.upper()

                # Parse FK constraints in any common table-level form:
                # 1) FOREIGN KEY (COL) REFERENCES TBL(COL)
                # 2) CONSTRAINT FK_NAME FOREIGN KEY (COL) REFERENCES TBL(COL)
                fk_match = re.search(
                    r'FOREIGN\s+KEY\s*\((\w+)\)\s*REFERENCES\s+([A-Za-z0-9_".]+)\s*\((\w+)\)',
                    line,
                    re.IGNORECASE
                )
                if fk_match:
                    from_col = fk_match.group(1).upper()
                    to_table = _normalize_table_name(fk_match.group(2))
                    to_col = fk_match.group(3).upper()
                    foreign_keys.append({
                        'from_table': table_name,
                        'from_column': from_col,
                        'to_table': to_table,
                        'to_column': to_col
                    })
                    print(f"   🔗 Found FK: {table_name}.{from_col} -> {to_table}.{to_col}")
                    continue

                # Parse inline FK form in column definitions:
                # COL_NAME <TYPE...> REFERENCES TARGET_TABLE(TARGET_COL)
                inline_fk_match = re.search(
                    r'^(\w+)\s+.+?\s+REFERENCES\s+([A-Za-z0-9_".]+)\s*\((\w+)\)',
                    line,
                    re.IGNORECASE
                )
                if inline_fk_match:
                    from_col = inline_fk_match.group(1).upper()
                    to_table = _normalize_table_name(inline_fk_match.group(2))
                    to_col = inline_fk_match.group(3).upper()
                    foreign_keys.append({
                        'from_table': table_name,
                        'from_column': from_col,
                        'to_table': to_table,
                        'to_column': to_col
                    })
                    print(f"   🔗 Found inline FK: {table_name}.{from_col} -> {to_table}.{to_col}")

                if not line_upper.startswith(('PRIMARY KEY', 'CONSTRAINT', 'FOREIGN KEY', 'UNIQUE', 'CHECK', 'INDEX')):
                    # Parse: COLUMNNAME DATATYPE(params) [IDENTITY] [NOT NULL]
                    parts = line.split()
                    if len(parts) >= 2:
                        col_name_original = parts[0]  # Preserve original casing for display name
                        col_name = parts[0].upper()   # Uppercase for DB reference
                        # Get the FULL data type including parameters - HANDLE IDENTITY!
                        col_type_match = re.match(r'(\w+(?:\([^)]+\))?)', parts[1])
                        col_type = col_type_match.group(1).upper() if col_type_match else parts[1].upper()
                        
                        columns.append({
                            'name': col_name,
                            'original_name': col_name_original,  # Keep original for naming style
                            'type': col_type,
                            'nullable': 'NOT NULL' not in line.upper()
                        })
            
            tables[table_name] = columns
        
        print(f"📊 Found {len(tables)} tables and {len(foreign_keys)} foreign keys in DDL")
        return tables, foreign_keys

    def _model_connected_components(self, table_names, foreign_keys):
        """Union-find over table_names; undirected edge = each FK's from_table<->to_table
        (when both tables are present). Returns a list of components (lists of UPPER names)."""
        parent = {t.upper(): t.upper() for t in table_names}
        def find(x):
            root = x
            while parent[root] != root:
                root = parent[root]
            while parent[x] != root:  # path compression
                parent[x], x = root, parent[x]
            return root
        for fk in foreign_keys:
            a, b = fk['from_table'].upper(), fk['to_table'].upper()
            if a in parent and b in parent and a != b:
                parent[find(a)] = find(b)
        comps = {}
        for t in parent:
            comps.setdefault(find(t), []).append(t)
        return list(comps.values())

    def _select_model_component(self, tables, foreign_keys):
        """A ThoughtSpot model must form ONE connected join graph; a disconnected graph
        — an orphan dimension nothing joins to, or two unrelated fact stars — is rejected
        on import with schema-validation error 13122. Pick the primary connected component
        (the star carrying the most fact tables) for the model and report the rest.

        Returns (keep:set[str], dropped_orphans:list[str], secondary:list[list[str]]):
          keep            - table names (UPPER) to include in the model
          dropped_orphans - tables in components carrying NO fact (orphan / stranded dims)
          secondary       - other fact-bearing components set aside from THIS model
        """
        names = [t.upper() for t in tables.keys()]
        comps = self._model_connected_components(names, foreign_keys)
        # A "fact" is any table with an outgoing FK (fact -> dimension). Dimensions and
        # true orphans have no outgoing FK, so a component with zero facts is unbuildable.
        fact_tables = {fk['from_table'].upper() for fk in foreign_keys}
        def n_facts(c):
            return sum(1 for t in c if t in fact_tables)
        joinable = [c for c in comps if n_facts(c) > 0]
        no_fact = [c for c in comps if n_facts(c) == 0]
        dropped_orphans = sorted(t for c in no_fact for t in c)
        if not joinable:
            # Nothing has a fact/join — leave the set intact and let it fail loud downstream
            # rather than silently emptying the model.
            return set(names), [], []
        joinable.sort(key=lambda c: (n_facts(c), len(c)), reverse=True)
        primary, secondary = joinable[0], joinable[1:]
        return set(primary), dropped_orphans, secondary

    def create_relationships_separately(self, table_relationships: Dict, table_guids: Dict):
        """Create relationships as separate TML objects after tables exist"""
        for table_name, relationships in table_relationships.items():
            for relationship in relationships:
                # Create relationship TML
                relationship_tml = {
                    'guid': None,
                    'relationship': {
                        'name': relationship['name'],
                        'destination_table': table_guids.get(relationship['to_table']),
                        'source_table': table_guids.get(table_name),
                        'type': relationship['type'],
                        'join_columns': [
                            {
                                'source_column': rel_on['from_column'],
                                'destination_column': rel_on['to_column']
                            }
                            for rel_on in relationship['on']
                        ]
                    }
                }
                
                relationship_yaml = yaml.dump(relationship_tml, default_flow_style=False, sort_keys=False)
                print(f"   🔗 Creating relationship: {relationship['name']}")
                print(f"   📄 Relationship TML:\n{relationship_yaml}")
                
                response = self.session.post(
                    f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                    json={
                        "metadata_tmls": [relationship_yaml],
                        "import_policy": "ALL_OR_NONE",
                        "create_new": True
                    }
                )
                
                if response.status_code == 200:
                    result = response.json()
                    print(f"   📋 Relationship response: {result}")
                    if result[0].get('response', {}).get('status', {}).get('status_code') == 'OK':
                        print(f"   ✅ Relationship created: {relationship['name']}")
                    else:
                        error_msg = result[0].get('response', {}).get('status', {}).get('error_message', 'Unknown error')
                        print(f"   ❌ Relationship failed: {error_msg}")
                else:
                    print(f"   ❌ Relationship API call failed: {response.status_code}")
                    print(f"   📋 Response: {response.text}")

    def create_table_tml(self, table_name: str, columns: List, connection_name: str,
                        database: str, schema: str, all_tables: Dict = None,
                        table_guid: str = None, foreign_keys: List = None,
                        connection_fqn: str = None) -> str:
        """Generate table TML matching working example structure

        Args:
            table_guid: If provided, use this GUID (for updating existing tables with joins)
            foreign_keys: List of foreign key relationships parsed from DDL
            connection_fqn: Optional connection GUID to disambiguate same-name connections
        """
        tml_columns = []
        
        # Generate columns with proper typing
        for col in columns:
            ts_type = self._map_data_type(col['type'])
            col_name = col['name'].upper()
            
            # Determine column type - IDs are measures in table TML but not model TML
            if ts_type in ['INT64'] and col_name.endswith('ID'):
                col_type = 'MEASURE'
                properties = {
                    'column_type': col_type,
                    'aggregation': 'SUM',
                    'index_type': 'DONT_INDEX'
                }
            elif ts_type in ['DOUBLE', 'INT64'] and not col_name.endswith('ID'):
                col_type = 'MEASURE'
                properties = {
                    'column_type': col_type,
                    'aggregation': 'SUM',
                    'index_type': 'DONT_INDEX'
                }
            else:
                col_type = 'ATTRIBUTE'
                properties = {
                    'column_type': col_type,
                    'index_type': 'DONT_INDEX'
                }
            
            column_def = {
                'name': col['name'].upper(),
                'db_column_name': col['name'].upper(),
                'properties': properties,
                'db_column_properties': {
                    'data_type': ts_type
                }
            }
                
            tml_columns.append(column_def)
        
        connection_ref = {'name': connection_name}
        if connection_fqn:
            connection_ref['fqn'] = connection_fqn

        table_tml = {
            'guid': table_guid,  # Use provided GUID or None for new tables
            'table': {
                'name': table_name.upper(),
                'db': database,
                'schema': schema,
                'db_table': table_name.upper(),
                'connection': connection_ref,
                'columns': tml_columns,
                'properties': {
                    'sage_config': {
                        'is_sage_enabled': False
                    }
                }
            }
        }
        
        # Add joins_with relationships (matching working example)
        if all_tables:
            joins_with = self._generate_table_joins(table_name, columns, all_tables, foreign_keys)
            if joins_with:
                table_tml['table']['joins_with'] = joins_with
        
        # Generate YAML with proper formatting
        yaml_output = yaml.dump(table_tml, default_flow_style=False, sort_keys=False)
        # Keep quotes around 'on' key as shown in working example
        return yaml_output
    
    def _generate_table_joins(self, table_name: str, columns: List, all_tables: Dict, foreign_keys: List = None) -> List:
        """Generate joins_with structure based on parsed foreign keys from DDL"""
        joins = []
        table_name_upper = table_name.upper()
        
        if not foreign_keys:
            print(f"   ⚠️  No foreign keys provided for {table_name_upper}")
            return joins
        
        # Use actual foreign keys from DDL
        for fk in foreign_keys:
            if fk['from_table'] == table_name_upper:
                to_table = fk['to_table']
                from_col = fk['from_column']
                to_col = fk['to_column']
                
                # Skip self-joins (e.g., EMPLOYEES.MANAGER_ID -> EMPLOYEES.EMPLOYEE_ID)
                # ThoughtSpot models don't handle self-referential joins well (causes cycles)
                if to_table == table_name_upper:
                    print(f"   ⏭️  Skipping self-join: {table_name_upper}.{from_col} -> {to_table}.{to_col} (self-referential)")
                    continue

                # Check if target table exists in THIS deployment
                available_tables_upper = [t.upper() for t in all_tables.keys()]
                if to_table in available_tables_upper:
                    constraint_id = f"SYS_CONSTRAINT_{self._generate_constraint_id()}"
                    join_def = {
                        'name': constraint_id,
                        'destination': {
                            'name': to_table
                        },
                        'on': f"[{table_name_upper}::{from_col}] = [{to_table}::{to_col}]",
                        'type': 'INNER'
                    }
                    joins.append(join_def)
                    print(f"   🔗 Generated join: {table_name_upper}.{from_col} -> {to_table}.{to_col}")
                else:
                    print(f"   ⏭️  Skipping join: {table_name_upper}.{from_col} -> {to_table} (table not in this deployment)")
        
        return joins

    def create_connection_tml(self, connection_name: str, database: str) -> str:
        """Generate connection TML matching working example.

        The connection MUST be scoped to the demo database: ThoughtSpot table
        imports scan all external metadata visible to the connection, and an
        unscoped connection (role sees ~507 DBs) costs ~250s per import vs
        <1s scoped to a small database (measured 2026-08-10 on sebe+secloud).
        """
        if not database:
            raise ValueError("create_connection_tml requires a database to scope the connection")
        connection_tml = {
            'guid': None,  # Will be generated by ThoughtSpot
            'connection': {
                'name': connection_name,
                'type': 'RDBMS_SNOWFLAKE',
                'authentication_type': 'KEY_PAIR',
                'properties': [
                    {'key': 'accountName', 'value': self.sf_account},
                    {'key': 'user', 'value': self.sf_user},
                    {'key': 'private_key', 'value': self._get_private_key_for_thoughtspot()},
                    {'key': 'passphrase', 'value': get_admin_setting('SNOWFLAKE_KP_PASSPHRASE', required=False)},
                    {'key': 'role', 'value': self.sf_role},
                    {'key': 'warehouse', 'value': self.sf_warehouse},
                    {'key': 'database', 'value': database}
                ],
                'description': f'Auto-generated Snowflake connection for {connection_name}'
            }
        }
        
        yaml_output = yaml.dump(connection_tml, default_flow_style=False, sort_keys=False)
        return yaml_output

    def create_actual_model_tml(self, tables: Dict, foreign_keys: List, table_guids: Dict = None, 
                               model_name: str = None, connection_name: str = None) -> str:
        """Generate proper model TML matching boone_test5 working example"""
        if not model_name:
            model_name = f"demo_model_{datetime.now().strftime('%Y%m%d')}"
        
        if not connection_name:
            connection_name = model_name
        
        # Create model structure matching working example exactly
        model = {
            'guid': None,  # Will be generated by ThoughtSpot
            'model': {
                'name': model_name,
                'model_tables': [],
                'columns': [],
                'properties': {
                    'is_bypass_rls': False,
                    'join_progressive': True,
                    'spotter_config': {
                        'is_spotter_enabled': True
                    }
                }
            }
        }
        
        # Build column name conflict resolution
        column_name_counts = {}
        for table_name, columns in tables.items():
            for col in columns:
                col_name = col['name'].upper()
                if col_name not in column_name_counts:
                    column_name_counts[col_name] = []
                column_name_counts[col_name].append(table_name.upper())
        
        # Add model_tables - START WITH NO JOINS for now (we can add them later)
        print("   📋 Creating model without explicit joins (ThoughtSpot can auto-detect)")
        
        for table_name in tables.keys():
            table_name_upper = table_name.upper()
            table_guid = table_guids.get(table_name_upper) if table_guids else None
            
            # Use FQN to resolve "multiple data sources with same name" issue
            # ThoughtSpot explicitly requires this when there are duplicate table names
            table_entry = {
                'name': table_name_upper,
                'fqn': table_guid  # Required to uniquely identify which table to use
            }
            
            # For now, don't add explicit joins - let ThoughtSpot auto-detect
            # This matches the pattern where some tables in your working example don't have joins
                    
            model['model']['model_tables'].append(table_entry)
        
        # Remove diamond join paths - ThoughtSpot rejects models where
        # table A joins to B, A joins to C, and C also joins to B
        self._remove_diamond_joins(model['model']['model_tables'])
        
        # Add columns with proper global conflict resolution
        used_display_names = set()  # Track used names globally across all columns
        
        for table_name, columns in tables.items():
            table_name_upper = table_name.upper()
            for col in columns:
                col_name = col['name'].upper()
                original_col_name = col.get('original_name', col['name'])  # Use original casing for display
                
                # TODO: Later we can exclude ID columns for cleaner model
                # For now, include all columns to get the basic model working
                
                # Start with basic conflict resolution
                display_name = self._resolve_column_name_conflict(
                    col_name, table_name_upper, column_name_counts,
                    original_name=original_col_name
                )
                
                # If the display name is still used, make it unique with table prefixes
                original_display_name = display_name
                counter = 1
                while display_name.lower() in used_display_names:
                    # Use table prefix for all tables consistently
                    if counter == 1:
                        # Generate consistent short prefix from table name
                        if len(table_name_upper) <= 4:
                            prefix = table_name_upper.lower()
                        else:
                            prefix = table_name_upper[:4].lower()
                        # Use snake_case: prefix_name
                        display_name = f"{prefix}_{original_display_name}"
                    else:
                        # Fallback: add number
                        display_name = f"{original_display_name}_{counter}"
                    counter += 1
                
                used_display_names.add(display_name.lower())
                
                # Determine column type based on data type
                col_type, aggregation = self._determine_column_type(col['type'], col_name)
                
                column_def = {
                    'name': display_name,
                    'column_id': f"{table_name_upper}::{col_name}",
                    'properties': {
                        'column_type': col_type,
                        'index_type': 'DONT_INDEX'
                    }
                }
                
                # Add aggregation for measures
                if aggregation:
                    column_def['properties']['aggregation'] = aggregation

                # Add calendar property for DATE columns so ThoughtSpot enables
                # time bucketing (.weekly, .monthly, etc.) on them
                if self._map_data_type(col['type']) == 'DATE':
                    column_def['properties']['calendar'] = 'calendar'

                model['model']['columns'].append(column_def)

        # Generate YAML output with proper formatting
        yaml_output = yaml.dump(model, default_flow_style=False, sort_keys=False,
                               default_style=None, indent=2, width=120)
        
        # Validate the generated YAML
        try:
            # Test if the YAML can be parsed back
            yaml.safe_load(yaml_output)
            print("   ✅ Generated YAML is valid")
        except yaml.YAMLError as e:
            print(f"   ❌ Generated YAML is invalid: {e}")
            print("   📄 Invalid YAML:")
            print(yaml_output)
            raise ValueError(f"Generated invalid YAML: {e}")
        
        return yaml_output

    def _is_foreign_key_column(self, col_name: str, table_name: str, foreign_keys: List) -> bool:
        """Check if column is a foreign key (used only for joins, not analytics)"""
        for fk in foreign_keys:
            if (fk.get('source_table', '').upper() == table_name and
                fk.get('source_column', '').upper() == col_name):
                return True
        return False

    def _is_surrogate_primary_key(self, col: Dict, col_name: str) -> bool:
        """Check if column is a meaningless surrogate key (numeric ID)"""
        # Common patterns: ID, _ID, ID_, ends with 'id'
        if col_name.upper().endswith('ID'):
            # Check if it's numeric (INT, BIGINT, NUMBER)
            col_type = col.get('type', '').upper()
            if any(t in col_type for t in ['INT', 'NUMBER', 'NUMERIC', 'BIGINT']):
                return True
        return False

    def _create_model_with_constraints(self, tables: Dict, foreign_keys: List, table_guids: Dict,
                                     table_constraints: Dict, model_name: str, connection_name: str) -> str:
        """Generate model TML with constraint references like our successful test"""
        print("   📋 Creating model with constraint references")
        
        # Build column name conflict tracking
        column_name_counts = {}
        for table_name, columns in tables.items():
            for col in columns:
                col_name = col['name'].upper()
                if col_name not in column_name_counts:
                    column_name_counts[col_name] = []
                column_name_counts[col_name].append(table_name.upper())
        
        model = {
            'guid': None,
            'model': {
                'name': model_name,
                'model_tables': [],
                'columns': [],
                'properties': {
                    'is_bypass_rls': False,
                    'join_progressive': True,
                    'spotter_config': {
                        'is_spotter_enabled': True
                    }
                }
            }
        }

        # Add model_tables with FQNs and constraint-based joins
        for table_name in tables.keys():
            table_name_upper = table_name.upper()
            table_guid = table_guids.get(table_name_upper)
            
            table_entry = {
                'name': table_name_upper,
                'fqn': table_guid
            }
            
            # Build joins from foreign_keys list (more reliable than constraint extraction)
            table_joins = []
            for fk in foreign_keys:
                if fk['from_table'].upper() == table_name_upper:
                    to_table = fk['to_table'].upper()
                    
                    # Skip self-joins (e.g., EMPLOYEES.MANAGER_ID -> EMPLOYEES.EMPLOYEE_ID)
                    # ThoughtSpot models don't handle self-referential joins well (causes cycles)
                    if to_table == table_name_upper:
                        print(f"   ⏭️  Skipping self-join in model: {table_name_upper}.{fk['from_column']} -> {to_table}")
                        continue
                    
                    # Check if target table exists in this deployment
                    if to_table in [t.upper() for t in tables.keys()]:
                        # ThoughtSpot on clause format: [SOURCE::COL] = [DEST::COL]
                        from_col = fk['from_column'].upper()
                        to_col = fk['to_column'].upper()
                        on_clause = f"[{table_name_upper}::{from_col}] = [{to_table}::{to_col}]"
                        
                        join_entry = {
                            'with': to_table,
                            'on': on_clause,
                            'type': 'LEFT_OUTER',
                            'cardinality': 'MANY_TO_ONE'  # Fact to dimension is many-to-one
                        }
                        table_joins.append(join_entry)
                        print(f"   🔗 Added join: {table_name_upper}.{from_col} -> {to_table}.{to_col}")
            
            if table_joins:
                table_entry['joins'] = table_joins
            
            model['model']['model_tables'].append(table_entry)
        
        # Remove diamond join paths - ThoughtSpot rejects models where
        # table A joins to B, A joins to C, and C also joins to B
        self._remove_diamond_joins(model['model']['model_tables'])
        
        # Add columns with proper global conflict resolution (same as working version)
        used_display_names = set()
        
        for table_name, columns in tables.items():
            table_name_upper = table_name.upper()
            for col in columns:
                col_name = col['name'].upper()
                original_col_name = col.get('original_name', col['name'])  # Use original casing for display

                # NOTE: We used to skip FK/PK columns, but ThoughtSpot requires them for joins
                # Even though users don't search "customer 23455", the join columns must be present
                # in the model's columns section for the joins to work properly.
                #
                # SKIP foreign key columns - they're join keys, not analytics columns
                # if self._is_foreign_key_column(col_name, table_name_upper, foreign_keys):
                #     print(f"   ⏭️  Skipping FK column: {table_name_upper}.{col_name}")
                #     continue
                #
                # SKIP surrogate primary keys (numeric IDs) - nobody searches "customer 23455"
                # if self._is_surrogate_primary_key(col, col_name):
                #     print(f"   ⏭️  Skipping surrogate PK: {table_name_upper}.{col_name}")
                #     continue

                # Start with basic conflict resolution
                display_name = self._resolve_column_name_conflict(
                    col_name, table_name_upper, column_name_counts,
                    original_name=original_col_name
                )
                
                # If the display name is still used, make it unique with table prefixes
                original_display_name = display_name
                counter = 1
                while display_name.lower() in used_display_names:
                    # Use snake_case: prefix_name
                    if counter == 1:
                        if table_name_upper == 'CUSTOMERS':
                            display_name = f"cust_{original_display_name}"
                        elif table_name_upper == 'PRODUCTS':
                            display_name = f"prod_{original_display_name}"
                        elif table_name_upper == 'ORDERS':
                            display_name = f"order_{original_display_name}"
                        elif table_name_upper == 'ORDERITEMS':
                            display_name = f"item_{original_display_name}"
                        elif table_name_upper == 'SALES':
                            display_name = f"sale_{original_display_name}"
                        elif table_name_upper == 'SALESREPS':
                            display_name = f"rep_{original_display_name}"
                        else:
                            display_name = f"{table_name_upper[:4].lower()}_{original_display_name}"
                    else:
                        display_name = f"{original_display_name}_{counter}"
                    counter += 1
                
                used_display_names.add(display_name.lower())
                
                # Determine column type based on data type
                col_type, aggregation = self._determine_column_type(col['type'], col_name)
                
                column_def = {
                    'name': display_name,
                    'column_id': f"{table_name_upper}::{col_name}",
                    'properties': {
                        'column_type': col_type,
                        'index_type': 'DONT_INDEX'
                    }
                }

                if aggregation:
                    column_def['properties']['aggregation'] = aggregation

                # Add calendar property for DATE columns so ThoughtSpot enables
                # time bucketing (.weekly, .monthly, etc.) on them
                if self._map_data_type(col['type']) == 'DATE':
                    column_def['properties']['calendar'] = 'calendar'

                model['model']['columns'].append(column_def)

        # Generate YAML output with validation
        yaml_output = yaml.dump(model, default_flow_style=False, sort_keys=False,
                               default_style=None, indent=2, width=120)

        # Fix YAML reserved word quoting - 'on' gets quoted because it's a YAML boolean
        # ThoughtSpot needs it unquoted
        yaml_output = yaml_output.replace("'on':", "on:")
        
        # Validate the generated YAML
        try:
            yaml.safe_load(yaml_output)
            print("   ✅ Generated YAML is valid")
        except yaml.YAMLError as e:
            print(f"   ❌ Generated YAML is invalid: {e}")
            raise ValueError(f"Generated invalid YAML: {e}")
        
        return yaml_output
    
    def _remove_diamond_joins(self, model_tables: list):
        """Remove join cycles that ThoughtSpot rejects using a spanning tree.
        
        ThoughtSpot requires exactly ONE path between any two tables in a model.
        Uses Kruskal's algorithm: add edges from highest-priority (fact) tables first,
        skip any edge that would create a cycle. Priority order ensures fact table
        joins are preserved and dimension-to-dimension "snowflake" joins are pruned.
        """
        def edge_key(src_name: str, join_def: dict):
            return (
                src_name,
                join_def.get('with'),
                join_def.get('on', ''),
                join_def.get('type', ''),
                join_def.get('cardinality', ''),
            )

        all_edges = []
        for t in model_tables:
            src_name = t['name']
            for j in t.get('joins', []):
                all_edges.append((src_name, j.get('with'), j, edge_key(src_name, j)))
        
        if not all_edges:
            print(f"   ✅ No joins to check for cycles")
            return
        
        out_degree = {}
        for t in model_tables:
            out_degree[t['name']] = len(t.get('joins', []))
        
        in_degree = {t['name']: 0 for t in model_tables}
        for src, dst, _, _ in all_edges:
            in_degree[dst] = in_degree.get(dst, 0) + 1
        
        all_edges.sort(key=lambda e: (-out_degree.get(e[0], 0), -in_degree.get(e[1], 0), e[0], e[1]))
        
        parent = {t['name']: t['name'] for t in model_tables}
        def find(x):
            while parent[x] != x:
                parent[x] = parent[parent[x]]
                x = parent[x]
            return x
        def union(a, b):
            ra, rb = find(a), find(b)
            if ra == rb:
                return False
            parent[ra] = rb
            return True
        
        kept_edge_keys = set()
        removed = []
        for src, dst, join_def, e_key in all_edges:
            if union(src, dst):
                kept_edge_keys.add(e_key)
            else:
                removed.append(f"{src}->{dst} ({join_def.get('on', '')})")
        
        for t in model_tables:
            if 'joins' not in t:
                continue
            src_name = t['name']
            t['joins'] = [j for j in t['joins'] if edge_key(src_name, j) in kept_edge_keys]
        
        for t in model_tables:
            if 'joins' in t and not t['joins']:
                del t['joins']
        
        if removed:
            print(f"   🔶 Removed {len(removed)} redundant joins (cycle prevention):")
            for r in removed:
                print(f"      - {r}")
        else:
            print(f"   ✅ No join cycles detected")
    
    def _generate_constraint_id(self) -> str:
        """Generate a constraint ID similar to ThoughtSpot's system constraints"""
        import uuid
        return str(uuid.uuid4())

    def validate_foreign_key_references(self, tables: Dict, foreign_keys: List = None) -> List[str]:
        """
        Validate that foreign key columns reference tables that exist in the schema.
        Uses explicit FK constraints from DDL - not heuristics.

        Args:
            tables: Dictionary of table definitions
            foreign_keys: List of FK relationships parsed from DDL
                         Each FK is: {'from_table': str, 'from_column': str, 
                                      'to_table': str, 'to_column': str}

        Returns:
            List of warning messages about missing referenced tables
        """
        warnings = []
        
        if not foreign_keys:
            return warnings  # No explicit FKs defined, nothing to validate
        
        table_names_upper = [t.upper() for t in tables.keys()]

        for fk in foreign_keys:
            target_table = fk.get('to_table', '').upper()
            from_table = fk.get('from_table', '')
            from_column = fk.get('from_column', '')
            
            # Check if the target table exists in this schema
            if target_table and target_table not in table_names_upper:
                warnings.append(
                    f"⚠️  {from_table}.{from_column} references {fk.get('to_table')}, "
                    f"but {fk.get('to_table')} is not in this schema. "
                    f"The join will be skipped during deployment."
                )

        return warnings
    
    def _resolve_column_name_conflict(self, col_name: str, table_name: str, 
                                     column_name_counts: Dict,
                                     original_name: str = None) -> str:
        """
        Resolve column name conflicts using configured naming style and prefixes.
        
        Examples (snake_case):
            SHIPPING_MODE → shipping_mode
            DAYS_TO_SHIP → days_to_ship  
            ORDER_DATE (conflict) → order_order_date, cust_order_date, etc.
        
        Args:
            col_name: Uppercase column name (for conflict detection)
            table_name: Table name for prefix generation
            column_name_counts: Dict tracking column name occurrences
            original_name: Original casing of column name (for proper camelCase detection)
        """
        # Use original name if provided (preserves camelCase boundaries)
        name_for_styling = original_name if original_name else col_name
        
        # Apply configured naming style
        styled_name = _apply_naming_style(name_for_styling, self.column_naming_style)
        
        if len(column_name_counts.get(col_name, [])) <= 1:
            # No conflict - use styled name directly
            return styled_name
        
        # For conflicts, generate prefix dynamically from table name
        if len(table_name) <= 4:
            prefix = table_name.lower()
        elif 'sales' in table_name.lower() and 'rep' in table_name.lower():
            prefix = 'rep'  # Special case for readability
        elif 'customer' in table_name.lower():
            prefix = 'cust'  # Common abbreviation
        elif 'product' in table_name.lower():
            prefix = 'prod'  # Common abbreviation
        elif 'order' in table_name.lower():
            prefix = 'order'  # Common abbreviation
        else:
            prefix = table_name[:4].lower()  # First 4 characters
        
        # Apply naming style to prefix + name combination
        prefixed_name = f"{prefix}_{styled_name}" if styled_name else prefix
        return _apply_naming_style(prefixed_name, self.column_naming_style)
    
    def _get_table_prefix(self, table_name: str) -> str:
        """Get appropriate prefix for table to avoid column conflicts"""
        # Generate prefix dynamically based on table name patterns
        table_lower = table_name.lower()

        if 'customer' in table_lower:
            return ''  # Primary table gets no prefix for readability
        elif 'sales' in table_lower and 'rep' in table_lower:
            return 'Rep'
        elif 'sales' in table_lower:
            return 'Sale'
        elif 'order' in table_lower and 'item' in table_lower:
            return 'Item'
        elif 'order' in table_lower:
            return 'Order'
        elif 'product' in table_lower:
            return 'Product'
        else:
            # Use first 3-4 characters as prefix, capitalize first letter
            prefix = table_name[:4] if len(table_name) > 3 else table_name
            return prefix.capitalize()
    
    def _determine_column_type(self, data_type: str, col_name: str) -> tuple:
        """Determine if column should be ATTRIBUTE or MEASURE"""
        base_type = data_type.upper().split('(')[0]
        col_upper = col_name.upper()
        
        # SALEID is special - it's treated as a measure in the working example
        if col_upper == 'SALEID':
            return 'MEASURE', 'SUM'
        
        # Numeric types should be measures (unless they're IDs or keys)
        if base_type in ['NUMBER', 'DECIMAL', 'FLOAT', 'DOUBLE', 'INT', 'INTEGER', 'BIGINT']:
            # Skip ID/KEY columns - they're join keys, not analytics columns
            if col_upper.endswith('ID') or col_upper.endswith('KEY') or col_upper.endswith('_CODE'):
                return 'ATTRIBUTE', None
            
            # All other numeric columns are measures
            # Determine aggregation based on column name patterns
            if any(word in col_upper for word in ['QUANTITY', 'QTY', 'COUNT', 'SOLD']):
                return 'MEASURE', 'SUM'
            elif any(word in col_upper for word in ['PRICE', 'COST', 'REVENUE', 'AMOUNT', 'TOTAL', 'PROFIT', 'DISCOUNT', 'SHIPPING', 'TAX']):
                return 'MEASURE', 'SUM'
            elif any(word in col_upper for word in ['RATING', 'SCORE', 'MARGIN', 'PERCENT', 'RATE']):
                return 'MEASURE', 'AVERAGE'
            else:
                # Default: numeric = measure with SUM
                return 'MEASURE', 'SUM'
        
        # Everything else is an attribute (strings, dates, booleans, etc.)
        return 'ATTRIBUTE', None
    
    def _build_table_relationships(self, tables: Dict, foreign_keys: List) -> Dict:
        """Build table relationships for joins"""
        relationships = {}
        
        # Auto-detect relationships based on common ID patterns
        table_names = list(tables.keys())
        for table_name in table_names:
            table_name_upper = table_name.upper()
            table_cols = [col['name'].upper() for col in tables[table_name]]
            
            # Find foreign key relationships
            for col_name in table_cols:
                if col_name.endswith('ID') and col_name != f"{table_name_upper}ID":
                    # This looks like a foreign key
                    target_table = col_name[:-2] + 'S'  # CUSTOMERID -> CUSTOMERS
                    if target_table in [t.upper() for t in table_names]:
                        if table_name_upper not in relationships:
                            relationships[table_name_upper] = []
                        relationships[table_name_upper].append({
                            'to_table': target_table,
                            'on_column': col_name
                        })
        
        return relationships
    
    def _create_model_level_joins(self, tables, foreign_keys):
        """Create joins at model level using the format from working example"""
        joins = []
        
        # Auto-detect joins if no explicit foreign keys  
        if len(tables) > 1:
            table_names = list(tables.keys())
            for i, table1 in enumerate(table_names):
                table1_upper = table1.upper()
                table1_cols = [col['name'].upper() for col in tables[table1]]
                
                for j, table2 in enumerate(table_names):
                    if i >= j:  # Avoid duplicates and self-joins
                        continue
                    table2_upper = table2.upper()
                    table2_cols = [col['name'].upper() for col in tables[table2]]
                    
                    # Look for matching ID columns
                    for col1 in table1_cols:
                        if col1.endswith('ID') and col1 in table2_cols:
                            join_entry = {
                                'name': f"{table1_upper.lower()}_{table2_upper.lower()}",
                                'source': table1_upper,
                                'destination': table2_upper,
                                'type': 'INNER',
                                'on': f"{table1_upper}.{col1} = {table2_upper}.{col1}"
                            }
                            joins.append(join_entry)
                            print(f"   🔗 Model-level join: {table1_upper} -> {table2_upper} on {col1}")
                            break
        
        return joins
    
    def _add_joins_to_tables(self, model_tables, tables, foreign_keys):
        """Add joins to individual tables (not as separate section)"""
        # Build join relationships
        table_joins = {}
        
        # Skip joins for now - test basic model creation first
        if False and foreign_keys:
            for fk in foreign_keys:
                from_table = fk['from_table'].upper()
                to_table = fk['to_table'].upper()
                
                if from_table not in table_joins:
                    table_joins[from_table] = []
                
                join_entry = {
                    'with': to_table,
                    'on': f"[{from_table}].[{fk['from_column'].upper()}] = [{to_table}].[{fk['to_column'].upper()}]",
                    'type': 'INNER',
                    'cardinality': 'MANY_TO_ONE'
                }
                table_joins[from_table].append(join_entry)
                print(f"   🔗 Adding join: {from_table} -> {to_table}")
        
        # Skip joins for now - test basic model creation first
        elif False and len(tables) > 1:
            table_names = list(tables.keys())
            for i, table1 in enumerate(table_names):
                table1_upper = table1.upper()
                table1_cols = [col['name'].upper() for col in tables[table1]]
                
                for j, table2 in enumerate(table_names[i+1:], i+1):
                    table2_upper = table2.upper()
                    table2_cols = [col['name'].upper() for col in tables[table2]]
                    
                    # Look for matching ID columns
                    for col1 in table1_cols:
                        if col1.endswith('ID') and col1 in table2_cols:
                            if table1_upper not in table_joins:
                                table_joins[table1_upper] = []
                            
                            join_entry = {
                                'with': table2_upper,
                                'on': f"[{table1_upper}].[{col1}] = [{table2_upper}].[{col1}]",
                                'type': 'INNER',
                                'cardinality': 'MANY_TO_ONE'
                            }
                            table_joins[table1_upper].append(join_entry)
                            print(f"   🔗 Auto-detected join: {table1_upper} -> {table2_upper} on {col1}")
                            break
        
        # Apply joins to model_tables
        for table_entry in model_tables:
            table_name = table_entry['name']
            if table_name in table_joins:
                table_entry['joins'] = table_joins[table_name]
    
    def _build_table_relationships(self, tables: Dict, foreign_keys: List) -> Dict:
        """Build relationships for each table based on foreign keys"""
        table_relationships = {}
        
        if foreign_keys:
            for fk in foreign_keys:
                from_table = fk['from_table'].upper()
                to_table = fk['to_table'].upper()
                from_column = fk['from_column'].upper()
                to_column = fk['to_column'].upper()
                
                # Add relationship to the from_table
                if from_table not in table_relationships:
                    table_relationships[from_table] = []
                
                relationship = {
                    'name': f"{from_table}_{to_table}_{from_column}",
                    'to_table': to_table,
                    'type': 'many_to_one',  # Assuming FK relationships are many-to-one
                    'on': [
                        {
                            'from_column': from_column,
                            'to_column': to_column
                        }
                    ]
                }
                table_relationships[from_table].append(relationship)
                print(f"   🔗 Relationship: {from_table}.{from_column} -> {to_table}.{to_column}")
        
        # Auto-detect relationships if no explicit foreign keys
        elif len(tables) > 1:
            table_names = list(tables.keys())
            for i, table1 in enumerate(table_names):
                table1_upper = table1.upper()
                table1_cols = [col['name'].upper() for col in tables[table1]]
                
                for j, table2 in enumerate(table_names[i+1:], i+1):
                    table2_upper = table2.upper()
                    table2_cols = [col['name'].upper() for col in tables[table2]]
                    
                    # Look for matching ID columns
                    for col1 in table1_cols:
                        if col1.endswith('ID') and col1 in table2_cols:
                            if table1_upper not in table_relationships:
                                table_relationships[table1_upper] = []
                            
                            relationship = {
                                'name': f"{table1_upper}_{table2_upper}_{col1}",
                                'to_table': table2_upper,
                                'type': 'many_to_one',
                                'on': [
                                    {
                                        'from_column': col1,
                                        'to_column': col1
                                    }
                                ]
                            }
                            table_relationships[table1_upper].append(relationship)
                            print(f"   🔗 Auto-detected relationship: {table1_upper}.{col1} -> {table2_upper}.{col1}")
                            break
        
        return table_relationships

    def create_model_tml(self, tables: Dict, foreign_keys: List, table_guids: Dict = None, 
                        model_name: str = None) -> str:
        """Generate worksheet TML (ORIGINAL APPROACH - keeping for comparison)"""
        if not model_name:
            model_name = f"demo_worksheet_{datetime.now().strftime('%Y%m%d')}"
        
        worksheet = {
            'guid': None,
            'worksheet': {
                'name': model_name,
                'description': 'Auto-generated worksheet from DDL',
                'tables': [],  
                'worksheet_columns': [],  # Adding back - but with GUID references
                'properties': {
                    'is_bypass_rls': False,
                    'join_progressive': True,
                    'spotter_config': {
                        'is_spotter_enabled': True
                    }
                }
            }
        }
        
        # Add tables with joins
        for table_name in tables.keys():
            table_entry = {'name': table_name.upper()}
            
            # Add FQN (GUID) if available to resolve multiple tables with same name
            if table_guids and table_name.upper() in table_guids:
                table_entry['fqn'] = table_guids[table_name.upper()]
            
            joins = []
            for fk in foreign_keys:
                if fk['source_table'] == table_name:
                    joins.append({
                        'with': fk['target_table'].upper(),
                        'referencing_join': f"FK_{table_name.upper()}_{fk['target_table'].upper()}"
                    })
            
            if joins:
                table_entry['joins'] = joins
            
            # Just populate the required 'tables' field with GUID reference
            worksheet['worksheet']['tables'].append({
                'name': table_name.upper(),
                'fqn': table_guids.get(table_name.upper()) if table_guids else f"table_{table_name.lower()}"
            })
        
        # Add columns using table GUIDs in expressions
        for table_name, columns in tables.items():
            table_guid = table_guids.get(table_name.upper()) if table_guids else None
            
            for col in columns:
                col_type = 'MEASURE' if 'DECIMAL' in col['type'] else 'ATTRIBUTE'
                
                # Use GUID in expression if available
                if table_guid:
                    expr = f"[{table_guid}].[{col['name']}]"
                else:
                    expr = f"[{table_name.upper()}].[{col['name']}]"
                
                column_def = {
                    'name': col['name'].upper(),
                    'data_type': col_type,
                    'expr': expr
                }
                
                worksheet['worksheet']['worksheet_columns'].append(column_def)
        
        return yaml.dump(worksheet, default_flow_style=False, sort_keys=False)

    def _map_data_type(self, sql_type: str) -> str:
        """Map SQL data types to ThoughtSpot types"""
        sql_type = sql_type.upper()
        
        # DEBUG: Print what we're mapping (commented out for cleaner output)
        # print(f"   🔍 Mapping data type: '{sql_type}'")
        
        # Handle NUMBER with precision/scale intelligently
        if sql_type.startswith('NUMBER'):
            # Extract precision and scale from NUMBER(precision,scale)
            if '(' in sql_type and ')' in sql_type:
                params = sql_type[sql_type.find('(')+1:sql_type.find(')')].split(',')
                if len(params) >= 2:
                    scale = int(params[1].strip())
                    result = 'INT64' if scale == 0 else 'DOUBLE'
                    # print(f"      → NUMBER({params[0].strip()},{scale}) → {result}")
                    return result
                else:
                    # print(f"      → NUMBER({params[0].strip()}) → INT64")
                    return 'INT64'  # NUMBER(x) defaults to integer
            else:
                # print(f"      → Plain NUMBER → DOUBLE")
                return 'DOUBLE'  # Plain NUMBER defaults to double
        
        type_mapping = {
            'INT64': 'INT64',
            'INT': 'INT64',      # FIXED: INT should map to INT64
            'INTEGER': 'INT64', 
            'BIGINT': 'INT64',
            'VARCHAR': 'VARCHAR',
            'TEXT': 'VARCHAR',
            'STRING': 'VARCHAR',
            'DATE': 'DATE',
            'TIMESTAMP': 'DATE',  # Try DATE for TIMESTAMP - DATE fields worked fine
            'TIMESTAMP_NTZ': 'DATE',  # Try DATE for TIMESTAMP_NTZ - we know DATE works
            'DECIMAL': 'DOUBLE',
            'FLOAT': 'DOUBLE',
            'BOOLEAN': 'BOOL'
        }
        
        for sql_key, ts_type in type_mapping.items():
            if sql_key in sql_type:
                return ts_type
                
        return 'VARCHAR'  # Default fallback

    def get_connection_by_name(self, connection_name: str) -> Dict:
        """Check if a connection with this exact name already exists."""
        try:
            response = self.session.post(
                f"{self.base_url}/api/rest/2.0/metadata/search",
                headers=self.headers,
                json={
                    "metadata": [{"type": "CONNECTION", "name_pattern": connection_name}],
                    "record_size": 50,
                    "record_offset": 0,
                },
                timeout=60,
            )

            if response.status_code == 200:
                exact_name = str(connection_name or "").upper()
                for row in response.json() or []:
                    header = self._metadata_header(row)
                    row_name = (
                        row.get("metadata_name")
                        or row.get("name")
                        or header.get("name")
                        or header.get("display_name")
                        or header.get("displayName")
                        or ""
                    )
                    row_guid = (
                        row.get("metadata_id")
                        or row.get("id_guid")
                        or header.get("id_guid")
                        or header.get("id")
                    )
                    if row_guid and row_name.upper() == exact_name:
                        return {"header": {"id_guid": row_guid, "name": row_name}}
            return None
        except Exception as e:
            print(f"   ⚠️  Could not check existing connections: {e}")
            return None

    def _normalize_tml_import_response_objects(self, result) -> Optional[List[Dict]]:
        """Normalize ThoughtSpot TML import response shapes into response objects."""
        def _normalize(obj):
            if not isinstance(obj, dict):
                return obj
            if "response" in obj:
                return obj
            if "status" in obj or "header" in obj:
                return {"response": obj}
            return obj

        if isinstance(result, list):
            return [_normalize(obj) for obj in result]
        if isinstance(result, dict) and "object" in result:
            return [_normalize(obj) for obj in result.get("object") or []]
        return None

    def _tml_import_object_status(self, obj: Dict) -> Tuple[str, str, Dict]:
        response = obj.get("response", {}) if isinstance(obj, dict) else {}
        status = response.get("status", {}) if isinstance(response, dict) else {}
        header = response.get("header", {}) if isinstance(response, dict) else {}
        return (
            str(status.get("status_code") or ""),
            str(status.get("error_message") or ""),
            header,
        )

    def _is_transient_connection_error(self, status_code: int = None, message: str = "") -> bool:
        text = str(message or "").lower()
        if status_code in {502, 503, 504}:
            return True
        return any(
            term in text
            for term in (
                "bad gateway",
                "gateway time-out",
                "gateway timeout",
                "secure_store_error",
                "secure store",
                "temporarily unavailable",
                "timeout",
                "timed out",
            )
        )

    def create_connection_with_reconcile(self, connection_name: str, database: str, log_progress=None, slog=None) -> Tuple[str, str]:
        """Create or find a ThoughtSpot connection, reconciling transient secure-store failures."""
        def _log(message: str) -> None:
            if log_progress:
                log_progress(message)
            else:
                print(message, flush=True)

        existing = self.get_connection_by_name(connection_name)
        if existing:
            header = existing.get("header", {})
            connection_guid = header.get("id_guid") or header.get("id")
            if connection_guid:
                _log("[OK] Connection ready")
                if slog:
                    slog.log_verbose(
                        "thoughtspot",
                        "connection reconciled before create",
                        connection_name=connection_name,
                        connection_guid=connection_guid,
                    )
                return connection_guid, connection_guid

        connection_tml_yaml = self.create_connection_tml(connection_name, database)
        max_attempts = max(1, int(os.getenv("TS_CONNECTION_CREATE_MAX_ATTEMPTS", "3")))
        base_wait_seconds = max(1, int(os.getenv("TS_CONNECTION_CREATE_RETRY_WAIT_SECONDS", "20")))
        last_error = ""

        for attempt in range(1, max_attempts + 1):
            _log(f"Creating new connection: {connection_name}" + (f" (attempt {attempt}/{max_attempts})" if max_attempts > 1 else ""))
            if slog:
                slog.log_verbose(
                    "thoughtspot",
                    "connection create attempt",
                    connection_name=connection_name,
                    attempt=attempt,
                    max_attempts=max_attempts,
                )

            response = self.session.post(
                f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                json={
                    "metadata_tmls": [connection_tml_yaml],
                    "import_policy": "PARTIAL",
                },
                timeout=300,
            )
            print(f"   Response status: {response.status_code}")

            result = None
            if response.status_code == 200:
                try:
                    result = response.json()
                except Exception:
                    result = None
                print(f"📋 Connection response: {result}")
                objects = self._normalize_tml_import_response_objects(result)
                if objects:
                    obj_status, error_message, header = self._tml_import_object_status(objects[0])
                    if obj_status == "OK":
                        connection_guid = header.get("id_guid") or header.get("id")
                        if connection_guid:
                            print(f"✅ Connection created: {connection_name} (GUID: {connection_guid})")
                            return connection_guid, connection_guid
                    last_error = error_message or "Connection import returned non-OK status"
                else:
                    last_error = "Connection creation failed: No object in response"
            else:
                try:
                    error_response = response.json()
                    print(f"❌ Error response: {error_response}")
                    last_error = json.dumps(error_response)[:2000]
                except Exception:
                    print(f"❌ Error response (raw): {response.text}")
                    last_error = response.text[:2000]

            if not self._is_transient_connection_error(response.status_code, last_error):
                raise Exception(f"Connection creation failed: {last_error}")

            _log(
                f"   ⚠️  Connection create returned a transient ThoughtSpot error; "
                f"checking whether {connection_name} exists before retry"
            )
            if slog:
                slog.log(
                    "thoughtspot",
                    "connection create transient",
                    connection_name=connection_name,
                    attempt=attempt,
                    status_code=response.status_code,
                    error=last_error[:1000],
                )

            reconciled = self.get_connection_by_name(connection_name)
            if reconciled:
                header = reconciled.get("header", {})
                connection_guid = header.get("id_guid") or header.get("id")
                if connection_guid:
                    _log(f"   ℹ️  Connection found after transient create error: {connection_guid}")
                    return connection_guid, connection_guid

            if attempt < max_attempts:
                wait_seconds = base_wait_seconds * attempt
                _log(f"   ⏳ Waiting {wait_seconds}s before retrying connection create")
                time.sleep(wait_seconds)

                reconciled = self.get_connection_by_name(connection_name)
                if reconciled:
                    header = reconciled.get("header", {})
                    connection_guid = header.get("id_guid") or header.get("id")
                    if connection_guid:
                        _log(f"   ℹ️  Connection found during retry wait: {connection_guid}")
                        return connection_guid, connection_guid

        reconciled = self.get_connection_by_name(connection_name)
        if reconciled:
            header = reconciled.get("header", {})
            connection_guid = header.get("id_guid") or header.get("id")
            if connection_guid:
                _log(f"   ℹ️  Connection found after final reconciliation: {connection_guid}")
                return connection_guid, connection_guid

        raise Exception(f"Connection creation failed after reconciliation: {last_error}")

    def _metadata_header(self, metadata_object: Dict) -> Dict:
        """Return the metadata header regardless of ThoughtSpot API response shape."""
        if not isinstance(metadata_object, dict):
            return {}
        return (
            metadata_object.get("metadata_header")
            or metadata_object.get("header")
            or metadata_object.get("response", {}).get("header")
            or {}
        )

    def _parse_tml_edoc(self, edoc):
        """Parse ThoughtSpot TML export content whether the API returns YAML, JSON, or a dict."""
        if isinstance(edoc, dict):
            return edoc
        if not isinstance(edoc, str):
            return {}
        try:
            return json.loads(edoc)
        except Exception:
            try:
                return yaml.safe_load(edoc) or {}
            except Exception:
                return {}

    def get_logical_table_by_name(self, table_name: str, database: str = None,
                                  schema: str = None, connection_name: str = None,
                                  connection_fqn: str = None) -> Dict:
        """Find an existing ThoughtSpot logical table by name and optional backing table context."""
        try:
            response = self.session.post(
                f"{self.base_url}/api/rest/2.0/metadata/search",
                headers=self.headers,
                json={
                    "metadata": [{"type": "LOGICAL_TABLE", "identifier": table_name}],
                    "record_size": 100,
                },
            )
            if response.status_code != 200:
                return None

            candidates = response.json() or []
            exact_name = table_name.upper()
            for candidate in candidates:
                header = self._metadata_header(candidate)
                candidate_name = (
                    candidate.get("metadata_name")
                    or candidate.get("name")
                    or header.get("name")
                    or header.get("display_name")
                    or header.get("displayName")
                    or ""
                )
                candidate_guid = (
                    candidate.get("metadata_id")
                    or candidate.get("id_guid")
                    or header.get("id_guid")
                    or header.get("id")
                )
                if candidate_name.upper() != exact_name or not candidate_guid:
                    continue

                if not (database or schema or connection_name or connection_fqn):
                    return {"header": {"id_guid": candidate_guid, "name": candidate_name}}

                try:
                    export_response = self.session.post(
                        f"{self.base_url}/api/rest/2.0/metadata/tml/export",
                        json={
                            "metadata": [{"identifier": candidate_guid, "type": "LOGICAL_TABLE"}],
                            "export_associated": False,
                            "format_type": "YAML",
                        },
                    )
                    if export_response.status_code != 200:
                        continue
                    tml_data = export_response.json() or []
                    if not tml_data or "edoc" not in tml_data[0]:
                        continue
                    tml_json = self._parse_tml_edoc(tml_data[0].get("edoc"))
                    table = tml_json.get("table", {})
                    connection = table.get("connection", {}) or {}
                    table_connection = connection.get("name")
                    table_connection_fqn = connection.get("fqn")
                    if database and str(table.get("db", "")).upper() != str(database).upper():
                        continue
                    if schema and str(table.get("schema", "")).upper() != str(schema).upper():
                        continue
                    if connection_name and table_connection != connection_name and table_connection_fqn != connection_name:
                        continue
                    if connection_fqn and table_connection_fqn != connection_fqn:
                        continue
                    return {"header": {"id_guid": candidate_guid, "name": candidate_name}}
                except Exception:
                    continue

            return None
        except Exception as e:
            print(f"   ⚠️  Could not check existing logical table {table_name}: {e}")
            return None

    def search_logical_tables_for_connection(
        self,
        connection_guid: str,
        connection_name: str = None,
        expected_table_names: List[str] = None,
        record_size: int = 200,
    ) -> Dict[str, Dict]:
        """Return logical tables listed directly under a ThoughtSpot connection.

        Source of truth is the CONNECTION object's metadata_detail.logicalTableList.
        Do not search logical tables by name here; common names such as MONTHS
        or REGIONS are not unique across a busy ThoughtSpot instance.
        """
        if not connection_guid:
            return {}

        expected = {name.upper() for name in (expected_table_names or [])}
        response = self.session.post(
            f"{self.base_url}/api/rest/2.0/metadata/search",
            headers=self.headers,
            json={
                "metadata": [{"type": "CONNECTION", "identifier": connection_guid}],
                "record_size": -1,
                "include_details": True,
            },
            timeout=60,
        )
        if response.status_code != 200:
            return {}

        connection_row = None
        for row in response.json() or []:
            row_guid = (
                row.get("metadata_id")
                or row.get("id_guid")
                or self._metadata_header(row).get("id_guid")
                or self._metadata_header(row).get("id")
            )
            row_name = (
                row.get("metadata_name")
                or row.get("name")
                or self._metadata_header(row).get("name")
            )
            if row_guid == connection_guid or (connection_name and row_name == connection_name):
                connection_row = row
                break
        if not connection_row:
            return {}

        detail = connection_row.get("metadata_detail") or {}
        logical_tables = detail.get("logicalTableList") or detail.get("tables") or []
        resolved = {}
        for table in logical_tables:
            header = table.get("header") or {}
            table_name = (
                table.get("name")
                or header.get("name")
                or ""
            ).upper()
            table_guid = (
                table.get("id")
                or table.get("guid")
                or header.get("id")
                or header.get("id_guid")
            )
            if table_name and table_guid and (not expected or table_name in expected):
                resolved[table_name] = {
                    "response": {
                        "status": {"status_code": "OK"},
                        "header": {"id_guid": table_guid, "name": table_name},
                    }
                }
        return resolved

    def create_snowflake_schema(self, database: str, schema: str):
        """Create schema in Snowflake via ThoughtSpot connection"""
        try:
            print(f"   🏗️  Creating schema {database}.{schema}...")
            
            # Use ThoughtSpot's SQL execution API to create schema
            create_schema_sql = f"CREATE SCHEMA IF NOT EXISTS {database}.{schema}"
            
            response = self.session.post(
                f"{self.base_url}/api/rest/2.0/database/executeQuery",
                json={
                    "sql_query": create_schema_sql,
                    "connection_guid": self.sf_connection_guid if hasattr(self, 'sf_connection_guid') else None
                }
            )
            
            if response.status_code == 200:
                print(f"   ✅ Schema {database}.{schema} created/verified")
            else:
                print(f"   ⚠️  Schema creation response: {response.status_code} - {response.text}")
                print(f"   📝 Will proceed assuming schema exists or will be created by table operations")
                
        except Exception as e:
            print(f"   ⚠️  Could not create schema: {e}")
            print(f"   📝 Will proceed assuming schema exists or will be created by table operations")

    def ensure_tag_exists(self, tag_name: str) -> bool:
        """
        Check if a tag exists, create it if it doesn't.
        
        Args:
            tag_name: Name of the tag
            
        Returns:
            True if tag exists or was created, False on error
        """
        if not tag_name:
            # No tag name provided - skip silently
            return True
            
        try:
            # First, try to get the tag to see if it exists
            print(f"[ThoughtSpot]    🔍 Checking if tag '{tag_name}' exists...", flush=True)
            search_response = self.session.post(
                f"{self.base_url}/api/rest/2.0/tags/search",
                json={"tag_identifier": tag_name}
            )
            
            print(f"[ThoughtSpot]    🔍 Tag search response: {search_response.status_code}", flush=True)
            
            if search_response.status_code == 200:
                tags = search_response.json()
                print(f"[ThoughtSpot]    🔍 Tags found: {len(tags) if tags else 0}", flush=True)
                if tags and len(tags) > 0:
                    # Tag exists
                    tag_id = tags[0].get('id', 'unknown')
                    print(f"[ThoughtSpot]    ✅ Tag '{tag_name}' exists (ID: {tag_id})", flush=True)
                    return True
            elif search_response.status_code == 400:
                # 400 might mean tag not found in some ThoughtSpot versions
                print(f"[ThoughtSpot]    🔍 Tag search returned 400 - tag likely doesn't exist", flush=True)
            else:
                print(f"[ThoughtSpot]    ⚠️  Tag search error: {search_response.status_code}", flush=True)
                try:
                    print(f"[ThoughtSpot]    ⚠️  Response: {search_response.text[:200]}", flush=True)
                except:
                    pass
            
            # Tag doesn't exist - create it
            print(f"[ThoughtSpot]    📎 Creating tag '{tag_name}'...", flush=True)
            create_response = self.session.post(
                f"{self.base_url}/api/rest/2.0/tags/create",
                json={"name": tag_name}
            )
            
            print(f"[ThoughtSpot]    📎 Create tag response: {create_response.status_code}", flush=True)
            
            if create_response.status_code in [200, 201]:
                try:
                    result = create_response.json()
                    tag_id = result.get('id', 'unknown')
                    print(f"[ThoughtSpot]    ✅ Tag '{tag_name}' created (ID: {tag_id})", flush=True)
                except:
                    print(f"[ThoughtSpot]    ✅ Tag '{tag_name}' created", flush=True)
                return True
            else:
                print(f"[ThoughtSpot]    ⚠️  Could not create tag: {create_response.status_code}", flush=True)
                try:
                    print(f"[ThoughtSpot]    ⚠️  Response: {create_response.text[:200]}", flush=True)
                except:
                    pass
                # Return False - don't silently proceed
                return False
                
        except Exception as e:
            import traceback
            print(f"[ThoughtSpot]    ⚠️  Tag check/create error: {str(e)}", flush=True)
            print(f"[ThoughtSpot]    ⚠️  Traceback: {traceback.format_exc()}", flush=True)
            return False

    def assign_tags_to_objects(self, object_guids: List[str], object_type: str, tag_name: str) -> bool:
        """
        Assign tags to ThoughtSpot objects using REST API v1.
        Auto-creates the tag if it doesn't exist.
        
        Args:
            object_guids: List of object GUIDs to tag
            object_type: Type of objects (LOGICAL_TABLE for tables/models, PINBOARD_ANSWER_BOOK for liveboards)
            tag_name: Tag name to assign
            
        Returns:
            True if successful, False otherwise
        """
        if not tag_name:
            # No tag name provided - skip silently (this is expected behavior)
            return True
        if not object_guids:
            return False
        
        try:
            import json as json_module

            # Ensure tag exists (create if needed)
            tag_ready = self.ensure_tag_exists(tag_name)
            if not tag_ready:
                print(f"[ThoughtSpot]    ⚠️  Could not ensure tag exists, skipping assignment", flush=True)
                return False

            # v1 type names differ from v2 — map them
            _v2_type_map = {'PINBOARD_ANSWER_BOOK': 'LIVEBOARD', 'DATA_SOURCE': 'CONNECTION'}
            v2_type = _v2_type_map.get(object_type, object_type)

            # Try v2 first (bearer-token sessions work cleanly with v2)
            try:
                v2_response = self.session.post(
                    f"{self.base_url}/api/rest/2.0/tags/assign",
                    json={
                        "tag_identifiers": [tag_name],
                        "metadata": [{"identifier": guid, "type": v2_type} for guid in object_guids]
                    }
                )
                if v2_response.status_code in [200, 204]:
                    print(f"[ThoughtSpot]    ✅ Tagged {len(object_guids)} {v2_type} objects with '{tag_name}'", flush=True)
                    return True
                else:
                    print(f"[ThoughtSpot]    ⚠️  v2 tag assignment failed ({v2_response.status_code}): {v2_response.text[:300]}", flush=True)
            except Exception as v2_err:
                print(f"[ThoughtSpot]    ⚠️  v2 tag assignment error: {v2_err}", flush=True)

            # Fall back to v1
            print(f"[ThoughtSpot]    🔁 Falling back to v1 tag assignment...", flush=True)
            assign_response = self.session.post(
                f"{self.base_url}/tspublic/v1/metadata/assigntag",
                data={
                    'id': json_module.dumps(object_guids),
                    'type': object_type,
                    'tagname': json_module.dumps([tag_name])
                },
                headers={
                    'X-Requested-By': 'ThoughtSpot',
                    'Content-Type': 'application/x-www-form-urlencoded'
                }
            )
            if assign_response.status_code in [200, 204]:
                print(f"[ThoughtSpot]    ✅ Tagged {len(object_guids)} {object_type} objects with '{tag_name}' (v1)", flush=True)
                return True
            else:
                print(f"[ThoughtSpot]    ⚠️  v1 tag assignment also failed: {assign_response.status_code} — {assign_response.text[:300]}", flush=True)
                return False

        except Exception as e:
            print(f"[ThoughtSpot]    ⚠️  Tag assignment error: {str(e)}", flush=True)
            return False

    def share_objects(self, object_guids: List[str], object_type: str, share_with: str) -> bool:
        """
        Share ThoughtSpot objects with a user or group (can_edit / MODIFY).

        Args:
            object_guids: GUIDs to share
            object_type: 'LOGICAL_TABLE' for models/tables, 'LIVEBOARD' for liveboards
            share_with: user email (contains '@') or group name
        """
        if not share_with or not object_guids:
            return True

        principal_type = "USER" if '@' in share_with else "USER_GROUP"

        try:
            response = self.session.post(
                f"{self.base_url}/api/rest/2.0/security/metadata/share",
                json={
                    "permissions": [
                        {
                            "principal": {
                                "identifier": share_with,
                                "type": principal_type
                            },
                            "share_mode": "MODIFY"
                        }
                    ],
                    "metadata": [
                        {"identifier": guid, "type": object_type}
                        for guid in object_guids
                    ],
                    # The REST endpoint proxies to a GraphQL mutation that declares
                    # $message as a non-null String! — omitting it makes the backend
                    # reject the request ("Variable \"$message\" ... was not provided").
                    # An empty string satisfies the contract; notify is off so nothing
                    # is emailed to the recipient.
                    "notify_on_share": False,
                    "message": ""
                }
            )
            if response.status_code in [200, 204]:
                print(f"[ThoughtSpot]    ✅ Shared {len(object_guids)} {object_type} with {principal_type} '{share_with}'", flush=True)
                return True
            else:
                print(f"[ThoughtSpot]    ⚠️  Share failed: {response.status_code} - {response.text[:200]}", flush=True)
                return False
        except Exception as e:
            print(f"[ThoughtSpot]    ⚠️  Share error: {str(e)}", flush=True)
            return False

    def _generate_demo_names(self, company_name: str = None, use_case: str = None):
        """Generate standardized demo names using DM convention"""
        from datetime import datetime
        import re
        
        # Get timestamp components
        now = datetime.now()
        yymmdd = now.strftime('%y%m%d')
        hhmmss = now.strftime('%H%M%S')
        
        # Clean and truncate company name (5 chars)
        if company_name:
            company_clean = re.sub(r'[^a-zA-Z0-9]', '', company_name.upper())[:5]
        else:
            company_clean = 'DEMO'[:5]
        
        # Clean and truncate use case (3 chars) 
        if use_case:
            usecase_clean = re.sub(r'[^a-zA-Z0-9]', '', use_case.upper())[:3]
        else:
            usecase_clean = 'GEN'[:3]
        
        # Generate names
        base_name = f"DM{yymmdd}_{hhmmss}_{company_clean}_{usecase_clean}"
        
        return {
            'schema': base_name,
            'connection': f"{base_name}_conn",
            'model': f"{base_name}_model",
            'base': base_name
        }

    def import_tmls_async(self, expected_names, tmls, create_new,
                          connection_guid, connection_name,
                          poll_interval_s=None, timeout_s=None,
                          log_progress=None, slog=None):
        """Async TML import: submit once, poll the light status endpoint to a
        definitive terminal state, then resolve name->guid from the connection.

        Returns the same shape as the sync _import_tmls_chunked closure
        ({NAME_UPPER: {"response": {"status": {...}, "header": {...}}}}), so all
        downstream processing in deploy_all is unchanged. Submit returns in ~0.2s
        and the status poll is light, so there is no gateway 504 to recover from.
        """
        def _lp(msg):
            if log_progress:
                log_progress(msg)
        base = self.base_url
        poll_interval_s = poll_interval_s or int(os.getenv("TS_TML_ASYNC_POLL_INTERVAL_SECONDS", "5"))
        timeout_s = timeout_s or int(os.getenv("TS_TML_ASYNC_TIMEOUT_SECONDS", "900"))
        payload = {"metadata_tmls": tmls, "import_policy": "PARTIAL", "create_new": create_new}
        submit_url = f"{base}/api/rest/2.0/metadata/tml/async/import"
        r = self.session.post(submit_url, json=payload, timeout=60)
        if r.status_code == 401 and self.authenticate():
            r = self.session.post(submit_url, json=payload, timeout=60)
        r.raise_for_status()
        task_id = (r.json() or {}).get("task_id")
        _lp(f"   [async] submitted import task {task_id} for {len(tmls)} object(s); polling...")
        if slog:
            try:
                slog.log("thoughtspot", "async import submitted",
                         task_id=task_id, object_count=len(tmls), create_new=create_new)
            except Exception:
                pass
        status_url = f"{base}/api/rest/2.0/metadata/tml/async/status"
        deadline = time.time() + timeout_s
        started = time.time()
        final = None
        last_status = None
        while time.time() < deadline:
            s = self.session.post(status_url,
                                  json={"task_ids": [task_id], "include_import_response": True},
                                  timeout=60)
            if s.status_code == 401 and self.authenticate():
                continue
            if s.status_code == 200:
                final = ((s.json() or {}).get("status_list") or [{}])[0]
                st = final.get("task_status")
                if st != last_status:
                    _lp(f"   [async] task {task_id}: {st}")
                    last_status = st
                if final.get("completed_at") or st in ("COMPLETED", "SUCCESS", "FAILED", "ERROR", "PARTIAL_SUCCESS"):
                    break
            time.sleep(poll_interval_s)
        elapsed = time.time() - started
        imp = (final or {}).get("import_response") or {}
        st = (final or {}).get("task_status")
        if st in ("FAILED", "ERROR") or (imp.get("status") or {}).get("status_code") == "ERROR":
            err = (imp.get("status") or {}).get("error_message") or f"task_status={st}"
            if slog:
                try:
                    slog.log("thoughtspot", "async import failed", task_id=task_id, error=str(err)[:500])
                except Exception:
                    pass
            raise RuntimeError(f"async import failed: {err}")
        _lp(f"   [async] task {task_id} {st or 'no-terminal-status'} in {elapsed:.1f}s; resolving tables on connection...")
        if slog:
            try:
                slog.log("thoughtspot", "async import complete",
                         task_id=task_id, task_status=st, elapsed_s=round(elapsed, 1))
            except Exception:
                pass
        # import_response carries no per-object headers; resolve name->guid from the connection.
        return self.search_logical_tables_for_connection(
            connection_guid, connection_name,
            expected_table_names=expected_names,
            record_size=max(50, len(expected_names) * 2),
        )

    def deploy_all(self, ddl: str, database: str, schema: str, base_name: str,
                  connection_name: str = None, company_name: str = None,
                  use_case: str = None, liveboard_name: str = None,
                  llm_model: str = None, tag_name: str = None,
                  share_with: str = None,
                  company_research: str = None, additional_context: str = None,
                  vertical: str = None, line: str = None, function: str = None,
                  progress_callback=None, session_logger=None) -> Dict:
        """
        Deploy complete data model to ThoughtSpot
        
        Args:
            ddl: Data Definition Language statements
            database: Target database name
            schema: Target schema name  
            connection_name: Optional connection name (auto-generated if not provided)
        
        Returns:
            Dict with deployment results and names of created objects
        """
        code_version = _get_code_version()
        results = {
            'success': False,
            'code_version': code_version,
            'ts_environment': self.base_url,
            'ts_username': self.username,
            'connection': None,
            'connection_guid': None,
            'tables': [],
            'model': None,
            'model_guid': None,
            'liveboard': None,
            'liveboard_guid': None,
            'liveboard_url': None,
            'liveboard_creation_path': 'none',
            'backup_liveboard': False,
            'fallback_reason': None,
            'errors': [],
            'warnings': []
        }
        
        table_guids = {}  # Store table GUIDs for model creation
        
        def log_progress(message):
            """Helper to log progress both to console and callback"""
            # ALWAYS print to console FIRST
            import sys
            print(f"[ThoughtSpot] {message}", flush=True)
            sys.stdout.flush()  # Force flush
            
            # Then call callback if provided
            if progress_callback:
                try:
                    progress_callback(message)
                except Exception as e:
                    print(f"[Warning] Callback error: {e}", flush=True)
        
        _slog = session_logger
        _ts_error = None

        try:
            import time
            start_time = time.time()

            # STEP 0: Authenticate first!
            log_progress("Authenticating...")
            log_progress(f"Run context: code={code_version}; ThoughtSpot environment={self.base_url}; user={self.username}")
            if _slog:
                _slog.log_verbose(
                    "thoughtspot",
                    "authenticating",
                    code_version=code_version,
                    ts_environment=self.base_url,
                    ts_username=self.username,
                )
            if not self.authenticate():
                if _slog:
                    _slog.log(
                        "thoughtspot",
                        "auth failed",
                        error=self.last_auth_error or "ThoughtSpot authentication failed",
                        status_code=self.last_auth_status_code,
                        ts_url=self.base_url,
                        username=self.username,
                    )
                raise Exception("ThoughtSpot authentication failed")
            auth_time = time.time() - start_time
            log_progress(f"[OK] Auth complete ({auth_time:.1f}s)")
            if _slog:
                _slog.log("thoughtspot", "auth complete", elapsed_s=round(auth_time, 1))
            
            # Parse DDL
            tables, foreign_keys = self.parse_ddl(ddl)
            if not tables:
                raise Exception("No tables found in DDL")

            # Validate foreign key references before deployment (uses explicit FKs from DDL)
            fk_warnings = self.validate_foreign_key_references(tables, foreign_keys)
            if fk_warnings:
                log_progress(f"[WARN] {len(fk_warnings)} FK warning(s) - joins to missing tables will be skipped")
                for warning in fk_warnings:
                    log_progress(f"   {warning}")

            # Step 1: Create connection using base name
            # base_name is like "DEMO_AMA_12111207_X4R"
            # schema is like "DEMO_
            # 2111207_X4R_sch"
            
            demo_names = {
                'schema': schema,
                'connection': f"{base_name}_conn",
                'model': f"{base_name}_mdl",
                'base': base_name
            }
            
            if not connection_name:
                connection_name = demo_names['connection']
            
            log_progress(f"Creating connection: {connection_name}...")
            if _slog:
                _slog.log_verbose("thoughtspot", f"creating connection: {connection_name}")

            print(f"🔗 Creating connection: {connection_name}")
            print(f"   Account: '{self.sf_account}' (length: {len(self.sf_account)})")
            print(f"   User: '{self.sf_user}'")
            print(f"   Database: '{database}'")
            connection_guid, connection_fqn = self.create_connection_with_reconcile(
                connection_name,
                database,
                log_progress=log_progress,
                slog=_slog,
            )
            results['connection'] = connection_name
            results['connection_guid'] = connection_guid
            
            if _slog:
                _slog.log_verbose(
                    "thoughtspot",
                    "connection created",
                    connection_name=connection_name,
                    connection_guid=connection_guid,
                )
            # Assign tag to connection
            if tag_name and connection_guid:
                log_progress(f"Assigning tag '{tag_name}' to connection...")
                self.assign_tags_to_objects([connection_guid], 'DATA_SOURCE', tag_name)

            # Step 1.5: Schema should already exist (created by demo_prep tool)
            print("\n1️⃣.5 Using existing schema in Snowflake...")
            
            # Step 2: Build relationships for tables
            print("\n1️⃣.5 Building relationships...")
            table_relationships = self._build_table_relationships(tables, foreign_keys)

            # Step 2: TWO-PHASE TABLE CREATION (to avoid dependency order issues)
            table_count = len(tables)
            batch1_start = time.time()
            log_progress(f"Batch 1/2: Creating {table_count} tables...")

            def _normalize_tml_import_object(obj):
                if not isinstance(obj, dict):
                    return obj
                if "response" in obj:
                    return obj
                if "status" in obj or "header" in obj:
                    return {"response": obj}
                return obj

            def _normalize_tml_import_objects(result):
                if isinstance(result, list):
                    return [_normalize_tml_import_object(obj) for obj in result]
                if isinstance(result, dict) and 'object' in result:
                    return [_normalize_tml_import_object(obj) for obj in result['object']]
                return None

            def _tml_objects_by_name(expected_names, objects):
                named = {}
                for idx, obj in enumerate(objects or []):
                    obj_response = obj.get('response', {}) if isinstance(obj, dict) else {}
                    status = obj_response.get('status', {})
                    header = obj_response.get('header', {})
                    raw_name = (
                        header.get('name')
                        or header.get('display_name')
                        or header.get('displayName')
                    )
                    table_name = (raw_name or (expected_names[idx] if idx < len(expected_names) else f"TABLE_{idx}")).upper()
                    error_message = str(status.get('error_message') or '')
                    existing_guid_match = re.search(
                        r'Existing Table GUID:\s*([0-9a-fA-F-]{36})',
                        error_message,
                    )
                    if (
                        status.get('status_code') == 'ERROR'
                        and 'already exists' in error_message.lower()
                        and existing_guid_match
                    ):
                        existing_guid = existing_guid_match.group(1)
                        if _table_guid_matches_current_context(existing_guid, table_name):
                            log_progress(
                                f"   ℹ️  {table_name} already exists after create timeout; "
                                f"using verified Existing Table GUID {existing_guid}"
                            )
                            if _slog:
                                _slog.log(
                                    "thoughtspot",
                                    "table resolved from verified existing-guid error",
                                    table_name=table_name,
                                    table_guid=existing_guid,
                                    connection_guid=connection_guid,
                                    error=error_message[:1000],
                                )
                            obj = _synthetic_ok_object(table_name, existing_guid)
                        else:
                            log_progress(
                                f"   ⚠️  {table_name} existing GUID {existing_guid} did not "
                                "match the current connection/schema; refusing it"
                            )
                            if _slog:
                                _slog.log(
                                    "thoughtspot",
                                    "table existing-guid rejected",
                                    table_name=table_name,
                                    table_guid=existing_guid,
                                    connection_guid=connection_guid,
                                    error=error_message[:1000],
                                )
                    named[table_name] = obj
                return named

            def _import_tml_chunk(phase_label, names, tmls, create_new):
                payload = {
                    "metadata_tmls": tmls,
                    "import_policy": "PARTIAL",
                    "create_new": create_new,
                }
                body_bytes = len(json.dumps(payload, default=str))
                start = time.time()
                log_progress(f"   {phase_label}: importing {len(tmls)} table(s): {', '.join(names)}")
                import_meta = {
                    "phase": phase_label,
                    "table_names": names,
                    "table_count": len(tmls),
                    "payload_bytes": body_bytes,
                    "create_new": create_new,
                    "ts_environment": self.base_url,
                }
                if _slog:
                    _slog.log(
                        "thoughtspot",
                        "table tml import request started",
                        **import_meta,
                    )

                try:
                    response = self.session.post(
                        f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                        json=payload,
                        timeout=360,
                    )
                except Exception as exc:
                    elapsed = time.time() - start
                    error = f"{phase_label} request exception: {exc}"
                    log_progress(f"   ❌ {error}")
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "table tml import exception",
                            error=error[:1000],
                            **import_meta,
                            elapsed_s=round(elapsed, 1),
                            exception_type=type(exc).__name__,
                        )
                    return None
                elapsed = time.time() - start

                if response.status_code == 401:
                    log_progress(f"   ⚠️  {phase_label} auth expired; re-authenticating and retrying once")
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "tml import auth expired",
                            phase=phase_label,
                            table_names=names,
                            elapsed_s=round(elapsed, 1),
                        )
                    if self.authenticate():
                        retry_start = time.time()
                        try:
                            response = self.session.post(
                                f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                                json=payload,
                                timeout=360,
                            )
                        except Exception as exc:
                            elapsed += time.time() - retry_start
                            error = f"{phase_label} request exception after re-auth: {exc}"
                            log_progress(f"   ❌ {error}")
                            if _slog:
                                _slog.log(
                                    "thoughtspot",
                                    "table tml import exception",
                                    error=error[:1000],
                                    **import_meta,
                                    elapsed_s=round(elapsed, 1),
                                    exception_type=type(exc).__name__,
                                    after_reauth=True,
                                )
                            return None
                        elapsed += time.time() - retry_start

                if response.status_code == 200:
                    response_json = response.json()
                    objects = _normalize_tml_import_objects(response_json)
                    object_count = len(objects) if isinstance(objects, list) else 0
                    object_statuses = []
                    object_names = []
                    object_errors = []
                    for obj in objects or []:
                        obj_response = obj.get('response', {}) if isinstance(obj, dict) else {}
                        status = obj_response.get('status', {})
                        header = obj_response.get('header', {})
                        object_statuses.append(status.get('status_code'))
                        object_names.append(header.get('name') or header.get('display_name') or header.get('displayName'))
                        if status.get('error_message'):
                            object_errors.append(str(status.get('error_message'))[:300])
                    status_summary = ", ".join(str(status) for status in object_statuses if status)
                    if status_summary:
                        log_progress(
                            f"   {phase_label}: response objects={object_count}; "
                            f"statuses={status_summary}"
                        )
                    if object_errors:
                        log_progress(f"   {phase_label}: first object error: {object_errors[0]}")
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "table tml import response received",
                            **import_meta,
                            elapsed_s=round(elapsed, 1),
                            status_code=response.status_code,
                            object_count=object_count,
                            object_statuses=object_statuses,
                            object_names=[name for name in object_names if name],
                            object_errors=object_errors[:5],
                        )
                    if objects is None:
                        error = f"{phase_label} failed: Unexpected response format: {type(response_json)}"
                        log_progress(f"   ❌ {error}")
                        results['errors'].append(error)
                        if _slog:
                            _slog.log(
                                "thoughtspot",
                                "tml import invalid response",
                                error=error,
                                phase=phase_label,
                                table_names=names,
                                elapsed_s=round(elapsed, 1),
                            )
                        return None
                    return objects

                error = f"{phase_label} HTTP error: {response.status_code} - {response.text}"
                log_progress(f"   ❌ {error}")
                if _slog:
                    _slog.log(
                        "thoughtspot",
                        "tml import HTTP error",
                        error=error[:1000],
                        phase=phase_label,
                        table_names=names,
                        table_count=len(tmls),
                        payload_bytes=body_bytes,
                        elapsed_s=round(elapsed, 1),
                        status_code=response.status_code,
                        response_text=response.text[:1000],
                    )
                    _slog.log(
                        "thoughtspot",
                        "table tml import response received",
                        error=error[:1000],
                        **import_meta,
                        elapsed_s=round(elapsed, 1),
                        status_code=response.status_code,
                        response_text=response.text[:1000],
                    )
                return response

            def _existing_table_import_object(table_name):
                existing = self.get_logical_table_by_name(
                    table_name,
                    database=database,
                    schema=schema,
                    connection_name=connection_name,
                    connection_fqn=connection_fqn,
                )
                if not existing:
                    return None
                table_guid = existing.get("header", {}).get("id_guid")
                if not table_guid:
                    return None
                log_progress(f"   ℹ️  {table_name} already exists in ThoughtSpot; using existing GUID {table_guid}")
                if _slog:
                    _slog.log(
                        "thoughtspot",
                        "table resolved after import retry",
                        table_name=table_name,
                        table_guid=table_guid,
                        schema=schema,
                    )
                return {
                    "response": {
                        "status": {"status_code": "OK"},
                        "header": {"id_guid": table_guid, "name": table_name},
                    }
                }

            def _tables_for_connection_import_objects(expected_table_names):
                try:
                    resolved = self.search_logical_tables_for_connection(
                        connection_guid,
                        connection_name,
                        expected_table_names,
                        record_size=max(50, len(expected_table_names) * 2),
                    )
                except Exception as exc:
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "connection-scoped table poll exception",
                            connection_guid=connection_guid,
                            error=str(exc)[:1000],
                        )
                    return {}

                if resolved:
                    log_progress(
                        f"   ℹ️  Connection-scoped poll found "
                        f"{len(resolved)}/{len(expected_table_names)} table(s)"
                    )
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "connection-scoped table poll resolved",
                            connection_guid=connection_guid,
                            resolved_count=len(resolved),
                            table_count=len(expected_table_names),
                            table_names=sorted(resolved.keys()),
                        )
                return resolved

            def _tml_dict_from_text(tml_text):
                try:
                    return yaml.safe_load(tml_text) or {}
                except Exception:
                    return {}

            def _synthetic_ok_object(table_name, table_guid):
                return {
                    "response": {
                        "status": {"status_code": "OK"},
                        "header": {"id_guid": table_guid, "name": table_name},
                    }
                }

            def _export_logical_table_tml(table_guid):
                try:
                    export_response = self.session.post(
                        f"{self.base_url}/api/rest/2.0/metadata/tml/export",
                        json={
                            "metadata": [{"identifier": table_guid, "type": "LOGICAL_TABLE"}],
                            "export_associated": False,
                            "format_type": "YAML",
                        },
                    )
                    if export_response.status_code != 200:
                        return None
                    tml_data = export_response.json() or []
                    if not tml_data or "edoc" not in tml_data[0]:
                        return None
                    return self._parse_tml_edoc(tml_data[0].get("edoc"))
                except Exception:
                    return None

            def _table_guid_matches_current_context(table_guid, expected_table_name=None):
                if not table_guid or not expected_table_name:
                    return False
                # Source of truth: the table must be discoverable under the
                # connection GUID created for this run. Same-name tables
                # elsewhere in the instance are not acceptable.
                connection_tables = _tables_for_connection_import_objects([expected_table_name])
                expected = connection_tables.get(str(expected_table_name).upper())
                expected_guid = (
                    expected
                    and expected.get("response", {})
                    .get("header", {})
                    .get("id_guid")
                )
                return bool(expected_guid and expected_guid == table_guid)

            def _join_signature(join):
                destination = join.get("destination", {}) if isinstance(join, dict) else {}
                return (
                    str(join.get("name", "")).upper(),
                    str(destination.get("name", "")).upper(),
                    str(join.get("on", "")).strip(),
                )

            def _expected_joins_present(expected_tml, exported_tml):
                expected_table = (_tml_dict_from_text(expected_tml).get("table") or {})
                expected_joins = expected_table.get("joins_with") or []
                if not expected_joins:
                    return True

                exported_table = (exported_tml or {}).get("table") or {}
                exported_joins = exported_table.get("joins_with") or []
                exported_signatures = {_join_signature(join) for join in exported_joins}
                return all(_join_signature(join) in exported_signatures for join in expected_joins)

            def _verify_table_updates_after_timeout(table_names, tmls, phase_label):
                timeout_seconds = _env_int("TS_TML_504_POLL_TIMEOUT_SECONDS", 900)
                poll_interval_seconds = max(1, _env_int("TS_TML_504_POLL_INTERVAL_SECONDS", 30))
                timeout_seconds = max(poll_interval_seconds, timeout_seconds)
                pending = {}

                for table_name, tml in zip(table_names, tmls):
                    tml_dict = _tml_dict_from_text(tml)
                    table_guid = tml_dict.get("guid")
                    if table_guid:
                        pending[table_name] = {"guid": table_guid, "tml": tml}

                verified = {}
                deadline = time.time() + timeout_seconds
                attempt = 0

                while pending:
                    attempt += 1
                    for table_name, info in list(pending.items()):
                        exported_tml = _export_logical_table_tml(info["guid"])
                        if exported_tml and _expected_joins_present(info["tml"], exported_tml):
                            verified[table_name] = _synthetic_ok_object(table_name, info["guid"])
                            pending.pop(table_name, None)

                    missing = list(pending.keys())
                    missing_preview = ", ".join(missing[:5])
                    missing_suffix = f"; pending: {missing_preview}" if missing_preview else ""
                    if len(missing) > 5:
                        missing_suffix += f", +{len(missing) - 5} more"
                    log_progress(
                        f"   ⏳ 504 update poll {attempt}: "
                        f"{len(verified)}/{len(table_names)} table update(s) verified"
                        f"{missing_suffix}"
                    )
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "tml 504 update poll",
                            phase=phase_label,
                            verified_count=len(verified),
                            table_count=len(table_names),
                            missing_table_names=missing[:10],
                            poll_attempt=attempt,
                            timeout_seconds=timeout_seconds,
                            poll_interval_seconds=poll_interval_seconds,
                        )

                    if not pending or time.time() >= deadline:
                        break
                    time.sleep(min(poll_interval_seconds, max(0, deadline - time.time())))

                return verified

            def _env_int(name, default_value):
                raw_value = os.getenv(name)
                if raw_value in (None, ""):
                    return default_value
                try:
                    return int(raw_value)
                except ValueError:
                    return default_value

            def _resolve_existing_tables_after_timeout(
                table_names,
                timeout_seconds=None,
                poll_interval_seconds=None,
                phase_label="",
            ):
                timeout_seconds = timeout_seconds if timeout_seconds is not None else _env_int(
                    "TS_TML_504_POLL_TIMEOUT_SECONDS",
                    900,
                )
                poll_interval_seconds = poll_interval_seconds if poll_interval_seconds is not None else _env_int(
                    "TS_TML_504_POLL_INTERVAL_SECONDS",
                    30,
                )
                poll_interval_seconds = max(1, poll_interval_seconds)
                timeout_seconds = max(poll_interval_seconds, timeout_seconds)
                resolved = {}
                top_table_name = table_names[0] if table_names else None
                deadline = time.time() + timeout_seconds
                attempt = 0

                while True:
                    attempt += 1
                    connection_resolved = _tables_for_connection_import_objects(table_names)
                    resolved.update(connection_resolved)

                    top_found = bool(top_table_name and top_table_name in resolved)
                    remaining = [name for name in table_names if name not in resolved]
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "tml 504 poll",
                            phase=phase_label,
                            top_table_name=top_table_name,
                            top_table_found=top_found,
                            resolved_count=len(resolved),
                            table_count=len(table_names),
                            missing_table_names=remaining[:10],
                            poll_attempt=attempt,
                            timeout_seconds=timeout_seconds,
                            poll_interval_seconds=poll_interval_seconds,
                        )
                    log_progress(
                        f"   ⏳ 504 poll {attempt}: top table "
                        f"{top_table_name or 'N/A'}={'found' if top_found else 'missing'}; "
                        f"{len(resolved)}/{len(table_names)} table(s) visible"
                    )

                    if len(resolved) == len(table_names):
                        break
                    if time.time() >= deadline:
                        break
                    time.sleep(min(poll_interval_seconds, max(0, deadline - time.time())))

                if _slog:
                    _slog.log(
                        "thoughtspot",
                        "tml 504 poll completed",
                        phase=phase_label,
                        top_table_name=top_table_name,
                        top_table_found=bool(top_table_name and top_table_name in resolved),
                        resolved_count=len(resolved),
                        table_count=len(table_names),
                        missing_table_names=[name for name in table_names if name not in resolved][:10],
                        timeout_seconds=timeout_seconds,
                    )
                return resolved

            def _wait_for_create_after_gateway_timeout(table_names):
                # A 504 from ThoughtSpot often means the gateway stopped waiting
                # while the import continued. Poll long enough to avoid issuing
                # duplicate create_new imports for the same logical table.
                return _resolve_existing_tables_after_timeout(
                    table_names,
                    timeout_seconds=_env_int("TS_TML_504_POLL_TIMEOUT_SECONDS", 900),
                    poll_interval_seconds=_env_int("TS_TML_504_POLL_INTERVAL_SECONDS", 30),
                    phase_label="create_new 504 recovery",
                )

            def _record_import_problem(message, fatal_errors=True):
                if fatal_errors:
                    results['errors'].append(message)
                else:
                    results['warnings'].append(message)

            def _import_tmls_chunked(phase_label, names, tmls, create_new, chunk_size=3, fatal_errors=True):
                all_objects = {}
                retriable_statuses = {502, 503, 504}

                for start_idx in range(0, len(tmls), chunk_size):
                    chunk_names = names[start_idx:start_idx + chunk_size]
                    chunk_tmls = tmls[start_idx:start_idx + chunk_size]
                    chunk_label = f"{phase_label} chunk {start_idx // chunk_size + 1}"
                    result = _import_tml_chunk(chunk_label, chunk_names, chunk_tmls, create_new)

                    if isinstance(result, requests.Response) and result.status_code in retriable_statuses:
                        if create_new:
                            resolved = _resolve_existing_tables_after_timeout(
                                chunk_names,
                                phase_label=chunk_label,
                            )
                            all_objects.update(resolved)
                            missing = [
                                (name, tml)
                                for name, tml in zip(chunk_names, chunk_tmls)
                                if name not in resolved
                            ]
                            if not missing:
                                continue
                            chunk_names = [name for name, _ in missing]
                            chunk_tmls = [tml for _, tml in missing]
                        else:
                            verified = _verify_table_updates_after_timeout(chunk_names, chunk_tmls, chunk_label)
                            all_objects.update(verified)
                            missing = [
                                (name, tml)
                                for name, tml in zip(chunk_names, chunk_tmls)
                                if name not in verified
                            ]
                            if not missing:
                                continue
                            chunk_names = [name for name, _ in missing]
                            chunk_tmls = [tml for _, tml in missing]

                        retry_kind = "create" if create_new else "update"
                        log_progress(f"   ⚠️  {chunk_label} timed out; retrying full {retry_kind} payload once")
                        result = _import_tml_chunk(
                            f"{chunk_label} retry",
                            chunk_names,
                            chunk_tmls,
                            create_new,
                        )
                        if isinstance(result, requests.Response):
                            if result.status_code in retriable_statuses:
                                if create_new:
                                    resolved = _resolve_existing_tables_after_timeout(
                                        chunk_names,
                                        phase_label=f"{chunk_label} retry",
                                    )
                                else:
                                    resolved = _verify_table_updates_after_timeout(
                                        chunk_names,
                                        chunk_tmls,
                                        f"{chunk_label} retry",
                                    )
                                all_objects.update(resolved)
                                missing_names = [name for name in chunk_names if name not in resolved]
                                if missing_names:
                                    error = (
                                        f"{chunk_label} timed out and {len(missing_names)} table(s) "
                                        f"could not be verified after full-payload retry: {', '.join(missing_names)}"
                                    )
                                    _record_import_problem(error, fatal_errors=fatal_errors)
                                    if fatal_errors:
                                        return None
                                continue
                        elif result is None and create_new:
                            resolved = _resolve_existing_tables_after_timeout(
                                chunk_names,
                                phase_label=f"{chunk_label} retry empty response",
                            )
                            all_objects.update(resolved)
                            if all(name in resolved for name in chunk_names):
                                continue

                    if isinstance(result, requests.Response):
                        if create_new:
                            resolved = _resolve_existing_tables_after_timeout(
                                chunk_names,
                                phase_label=chunk_label,
                            )
                            all_objects.update(resolved)
                            if all(name in resolved for name in chunk_names):
                                continue
                        error = (
                            f"{phase_label} failed: HTTP {result.status_code} - {result.text}"
                        )
                        _record_import_problem(error, fatal_errors=fatal_errors)
                        if fatal_errors:
                            return None
                        continue
                    if result is None:
                        error = f"{phase_label} failed: no import response"
                        _record_import_problem(error, fatal_errors=fatal_errors)
                        if fatal_errors:
                            return None
                        continue
                    all_objects.update(_tml_objects_by_name(chunk_names, result))

                return all_objects

            # PHASE 1: Create all tables WITHOUT joins in ONE batch API call
            # Build array of all table TMLs
            table_tmls_batch1 = []
            table_names_order = []  # Track order for matching response
            
            for table_name, columns in tables.items():
                print(f"[ThoughtSpot]    Preparing {table_name.upper()}...", flush=True)
                table_tml = self.create_table_tml(
                    table_name,
                    columns,
                    connection_name,
                    database,
                    schema,
                    all_tables=None,
                    foreign_keys=foreign_keys,
                    connection_fqn=connection_fqn,
                )
                table_tmls_batch1.append(table_tml)
                table_names_order.append(table_name.upper())

            create_chunk_size = _env_int("TS_TABLE_CREATE_CHUNK_SIZE", 0)
            if create_chunk_size <= 0:
                create_chunk_size = len(table_tmls_batch1)

            log_progress(
                f"   Sending table creation requests for {len(table_tmls_batch1)} tables "
                f"(chunk size {create_chunk_size})..."
            )
            objects = self.import_tmls_async(
                table_names_order, table_tmls_batch1, True,
                connection_guid, connection_name,
                log_progress=log_progress, slog=_slog,
            )

            if objects is None:
                return results

            # Process each table result by table name. Gateway-timeout recovery can
            # return a partial set, so positional matching is unsafe here.
            for table_name in table_names_order:
                obj = objects.get(table_name)
                if obj is None:
                    error = f"Table {table_name} failed: no import response after retry"
                    print(f"[ThoughtSpot]    ❌ {error}", flush=True)
                    results['errors'].append(error)
                    continue
                
                if obj.get('response', {}).get('status', {}).get('status_code') == 'OK':
                    table_guid = obj.get('response', {}).get('header', {}).get('id_guid')
                    print(f"[ThoughtSpot]    ✅ {table_name} created", flush=True)
                    results['tables'].append(table_name)
                    table_guids[table_name] = table_guid
                else:
                    error_msg = obj.get('response', {}).get('status', {}).get('error_message', 'Unknown error')
                    if "already exists" in str(error_msg).lower():
                        existing_object = _tables_for_connection_import_objects([table_name]).get(table_name)
                        existing_guid = existing_object and existing_object.get("response", {}).get("header", {}).get("id_guid")
                        if existing_guid:
                            print(f"[ThoughtSpot]    ✅ {table_name} resolved under current connection", flush=True)
                            results['tables'].append(table_name)
                            table_guids[table_name] = existing_guid
                            continue
                    error = f"Table {table_name} failed: {error_msg}"
                    print(f"[ThoughtSpot]    ❌ {table_name} failed: {error_msg}", flush=True)
                    results['errors'].append(error)

            # Check if we created any tables successfully
            if not table_guids:
                log_progress("   ❌ No tables were created successfully in Batch 1")
                return results

            missing_tables = [name for name in table_names_order if name not in table_guids]
            if missing_tables:
                error = (
                    f"Batch 1 incomplete: {len(table_guids)}/{len(table_names_order)} "
                    f"tables created; missing {', '.join(missing_tables)}"
                )
                log_progress(f"   ❌ {error}")
                results['errors'].append(error)
                if _slog:
                    _slog.log(
                        "thoughtspot",
                        "table creation incomplete",
                        error=error,
                        table_count=len(table_names_order),
                        created_count=len(table_guids),
                        missing_tables=missing_tables,
                    )
                return results

            # Assign tags to tables
            table_guid_list = list(table_guids.values())
            print(f"🔍 DEBUG BEFORE TAG CALL: tag_name='{tag_name}', table_guid_list={table_guid_list}")
            log_progress(f"Assigning tag '{tag_name}' to {len(table_guid_list)} tables...")
            self.assign_tags_to_objects(table_guid_list, 'LOGICAL_TABLE', tag_name)

            batch1_time = time.time() - batch1_start
            log_progress(f"[OK] Batch 1 complete: {len(table_guids)} tables created ({batch1_time:.1f}s)")
            if _slog:
                _slog.log("thoughtspot", f"tables created: {len(table_guids)}", elapsed_s=round(batch1_time, 1))

            # PHASE 2: Update tables WITH joins in ONE batch API call
            batch2_start = time.time()
            log_progress(f"Batch 2/2: Adding joins to {len(table_guids)} tables...")
            
            # Build array of all table update TMLs (with joins)
            table_tmls_batch2 = []
            table_names_order_batch2 = []
            
            for table_name, columns in tables.items():
                table_name_upper = table_name.upper()
                
                # Only add joins if the table was created successfully in Phase 1
                if table_name_upper not in table_guids:
                    print(f"[ThoughtSpot]    Skipping {table_name_upper} (not created)", flush=True)
                    continue

                # Get the GUID for this table
                table_guid = table_guids[table_name_upper]

                print(f"[ThoughtSpot]    Preparing joins for {table_name_upper}...", flush=True)
                # Create table TML WITH joins_with section AND the table GUID
                table_tml = self.create_table_tml(
                    table_name, columns, connection_name, database, schema,
                    all_tables=tables,
                    table_guid=table_guid,
                    foreign_keys=foreign_keys,
                    connection_fqn=connection_fqn,
                )
                table_tmls_batch2.append(table_tml)
                table_names_order_batch2.append(table_name_upper)

            if table_tmls_batch2:
                update_chunk_size = _env_int("TS_TABLE_UPDATE_CHUNK_SIZE", 0)
                if update_chunk_size <= 0:
                    update_chunk_size = len(table_tmls_batch2)

                log_progress(
                    f"   Sending join update requests for {len(table_tmls_batch2)} tables "
                    f"(chunk size {update_chunk_size})..."
                )
                objects = self.import_tmls_async(
                    table_names_order_batch2, table_tmls_batch2, False,
                    connection_guid, connection_name,
                    log_progress=log_progress, slog=_slog,
                )

                if objects is not None:
                    # Process each result by table name. Partial retry recovery can
                    # return fewer objects than requested.
                    for table_name in table_names_order_batch2:
                        obj = objects.get(table_name)
                        if obj is None:
                            warning = f"Joins for {table_name} failed: no import response after retry"
                            print(f"[ThoughtSpot]    ⚠️  {warning}", flush=True)
                            results['warnings'].append(warning)
                            continue
                        
                        if obj.get('response', {}).get('status', {}).get('status_code') == 'OK':
                            print(f"[ThoughtSpot]    ✅ {table_name} joins added", flush=True)
                        else:
                            error_msg = obj.get('response', {}).get('status', {}).get('error_message', 'Unknown error')
                            print(f"[ThoughtSpot]    ⚠️  {table_name} joins failed: {error_msg}", flush=True)
                            results['errors'].append(f"Joins for {table_name} failed: {error_msg}")

            batch2_time = time.time() - batch2_start
            log_progress(f"[OK] Batch 2 complete: Joins added ({batch2_time:.1f}s)")
            actual_constraint_ids = {}  # We'll generate these for the model
            
            # Skip separate relationship creation for now
            # print("\n2️⃣.5 Creating relationships separately...")
            # self.create_relationships_separately(table_relationships, table_guids)
            
            # Step 3: Extract constraint IDs from created tables
            table_constraints = {}
            
            for table_name, table_guid in table_guids.items():
                print(f"[ThoughtSpot]    Extracting joins from {table_name}...", flush=True)
                
                # Export table TML to get constraint IDs
                export_response = self.session.post(
                    f"{self.base_url}/api/rest/2.0/metadata/tml/export",
                    json={
                        "metadata": [{"identifier": table_guid, "type": "LOGICAL_TABLE"}],
                        "export_associated": False,
                        "format_type": "YAML"
                    }
                )
                
                if export_response.status_code == 200:
                    tml_data = export_response.json()
                    if tml_data and 'edoc' in tml_data[0]:
                        tml_json = self._parse_tml_edoc(tml_data[0].get('edoc'))
                        
                        # Extract joins_with constraint IDs
                        joins_with = tml_json.get('table', {}).get('joins_with', [])
                        if joins_with:
                            table_constraints[table_name] = []
                            for join in joins_with:
                                constraint_id = join.get('name')
                                destination = join.get('destination', {}).get('name')
                                if constraint_id and destination:
                                    table_constraints[table_name].append({
                                        'constraint_id': constraint_id,
                                        'destination': destination
                                    })
            
            # Step 4: Create model (semantic layer) with constraint references
            model_start = time.time()
            model_name = demo_names['model']
            log_progress(f"Creating model: {model_name}...")
            
            # Connectivity guard: a TS model must be ONE connected join graph or the
            # import fails with schema-validation error 13122. Restrict the model to the
            # primary connected component; drop orphan tables (no joins) and set aside any
            # secondary fact stars. All tables were still created above (Batch 1/2); this
            # only scopes what goes INTO the model.
            _keep, _dropped_orphans, _secondary = self._select_model_component(tables, foreign_keys)
            if len(_keep) < len(tables):
                model_tables = {t: c for t, c in tables.items() if t.upper() in _keep}
                model_foreign_keys = [fk for fk in foreign_keys
                                      if fk['from_table'].upper() in _keep and fk['to_table'].upper() in _keep]
                model_table_guids = {t: g for t, g in table_guids.items() if t.upper() in _keep}
                model_table_constraints = {t: c for t, c in (table_constraints or {}).items() if t.upper() in _keep}
                if _dropped_orphans:
                    _msg = ("Connectivity guard: dropped orphan table(s) with no joins from the model "
                            f"(would fail TS schema validation 13122): {', '.join(_dropped_orphans)}")
                    log_progress(f"   ⚠️  {_msg}")
                    results['warnings'].append(_msg)
                    results.setdefault('dropped_orphan_tables', []).extend(_dropped_orphans)
                for _comp in _secondary:
                    _msg = ("Connectivity guard: set aside a separate subject area not joined to the "
                            f"primary model: {', '.join(sorted(_comp))}")
                    log_progress(f"   ⚠️  {_msg}")
                    results['warnings'].append(_msg)
                    results.setdefault('set_aside_components', []).append(sorted(_comp))
                log_progress(f"   ✅ Model scoped to {len(model_tables)} connected table(s): "
                             f"{', '.join(sorted(model_tables.keys()))}")
            else:
                model_tables, model_foreign_keys = tables, foreign_keys
                model_table_guids, model_table_constraints = table_guids, table_constraints

            # Use the enhanced model creation that includes constraint references
            model_tml = self._create_model_with_constraints(model_tables, model_foreign_keys, model_table_guids, model_table_constraints, model_name, connection_name)
            print(f"\n📄 Model TML being sent:\n{model_tml}")

            response = self.session.post(
                f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                json={
                    "metadata_tmls": [model_tml],
                    "import_policy": "ALL_OR_NONE",
                    "create_new": True
                }
            )

            # Some complex multi-fact models are rejected by ThoughtSpot when
            # the Model TML repeats explicit model-table joins even though the
            # table objects already have joins from Batch 2. Retry once with the
            # same tables/columns but no model-table joins.
            if response.status_code == 200:
                try:
                    _model_import_preview = response.json()
                    _preview_objects = self._normalize_tml_import_response_objects(_model_import_preview) or []
                    _first_status = (
                        _preview_objects[0].get('response', {}).get('status', {})
                        if _preview_objects else {}
                    )
                    _first_status_code = str(_first_status.get('status_code') or '')
                    _first_error = str(_first_status.get('error_message') or '')
                    _first_error_code = str(_first_status.get('error_code') or '')
                except Exception:
                    _first_status_code = ''
                    _first_error = ''
                    _first_error_code = ''

                if (
                    _first_status_code == 'ERROR'
                    and (
                        _first_error_code == '13122'
                        or 'schema validation failed' in _first_error.lower()
                    )
                ):
                    warning = (
                        "Model import with explicit joins failed schema validation; "
                        "retrying model import without model-table joins."
                    )
                    log_progress(f"   ⚠️  {warning}")
                    results['warnings'].append(warning)
                    if _slog:
                        _slog.log(
                            "thoughtspot",
                            "model import retrying without joins",
                            error_code=_first_error_code,
                            error=_first_error[:500],
                        )
                    model_tml = self.create_actual_model_tml(
                        model_tables,
                        model_foreign_keys,
                        table_guids=model_table_guids,
                        model_name=model_name,
                        connection_name=connection_name,
                    )
                    response = self.session.post(
                        f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                        json={
                            "metadata_tmls": [model_tml],
                            "import_policy": "ALL_OR_NONE",
                            "create_new": True
                        }
                    )
            
            if response.status_code == 200:
                result = response.json()

                # Handle both response formats (list or dict with 'object' key)
                if isinstance(result, list):
                    objects = result
                elif isinstance(result, dict) and 'object' in result:
                    objects = result['object']
                else:
                    error = f"Model failed: Unexpected response format: {type(result)}"
                    log_progress(f"   ❌ {error}")
                    results['errors'].append(error)
                    objects = []

                if objects and len(objects) > 0:
                    if objects[0].get('response', {}).get('status', {}).get('status_code') == 'OK':
                        model_guid = objects[0].get('response', {}).get('header', {}).get('id_guid')
                        # Fallback: search by name when TML import response omits id_guid (e.g. on update)
                        if not model_guid:
                            try:
                                sr = self.session.get(
                                    f"{self.base_url}/api/rest/2.0/metadata/search",
                                    params={"metadata": [{"type": "LOGICAL_TABLE", "identifier": model_name}], "record_size": 5},
                                )
                                if sr.status_code == 200:
                                    for item in (sr.json() or []):
                                        if item.get('metadata_name') == model_name:
                                            model_guid = item.get('metadata_id')
                                            break
                                    if model_guid:
                                        log_progress(f"   ℹ️  GUID resolved via name search: {model_guid}")
                            except Exception:
                                pass
                        model_time = time.time() - model_start
                        log_progress(f"[OK] Model created ({model_time:.1f}s)")
                        if _slog:
                            _slog.log("thoughtspot", "model created", model_guid=model_guid, ts_url=self.base_url, elapsed_s=round(model_time, 1))
                        results['model'] = model_name
                        results['model_guid'] = model_guid
                        
                        # Assign tag to model
                        if tag_name and model_guid:
                            log_progress(f"Assigning tag '{tag_name}' to model...")
                            self.assign_tags_to_objects([model_guid], 'LOGICAL_TABLE', tag_name)
                            if _slog: _slog.log_verbose("thoughtspot", "model tagged", tag=tag_name)

                        # Share model
                        _effective_share = share_with or get_admin_setting('SHARE_WITH', required=False)
                        if _effective_share:
                            log_progress(f"Sharing model with '{_effective_share}'...")
                            self.share_objects([model_guid], 'LOGICAL_TABLE', _effective_share)
                            if _slog: _slog.log_verbose("thoughtspot", "model shared", share_with=_effective_share)

                        # Step 3.5: Enable Spotter + enrich model semantics in a single export→update→reimport
                        # (create_new=True import ignores spotter_config, so we always re-import here)
                        try:
                            export_resp = self.session.post(
                                f"{self.base_url}/api/rest/2.0/metadata/tml/export",
                                json={
                                    "metadata": [{"identifier": model_guid, "type": "LOGICAL_TABLE"}],
                                    "export_associated": False,
                                    "format_type": "YAML"
                                }
                            )
                            if export_resp.status_code == 200:
                                export_data = export_resp.json()
                                if export_data and 'edoc' in export_data[0]:
                                    edoc = export_data[0]['edoc']
                                    try:
                                        model_tml_dict = json.loads(edoc)
                                    except Exception:
                                        model_tml_dict = yaml.safe_load(edoc)
                                    if not model_tml_dict.get('model', {}).get('columns'):
                                        # Some exports return a sparse object immediately after
                                        # create_new import. The just-created TML has the full
                                        # column list; copy those columns while preserving the
                                        # exported object's GUID and other server-owned fields.
                                        fallback_tml = yaml.safe_load(model_tml)
                                        if fallback_tml.get('model', {}).get('columns'):
                                            exported_model = model_tml_dict.setdefault('model', {})
                                            fallback_model = fallback_tml.get('model', {})
                                            exported_model.setdefault('name', fallback_model.get('name'))
                                            exported_model.setdefault('model_tables', fallback_model.get('model_tables', []))
                                            exported_model['columns'] = fallback_model['columns']

                                    # Enable Spotter
                                    model_tml_dict.setdefault('model', {}).setdefault('properties', {})['spotter_config'] = {'is_spotter_enabled': True}

                                    # Enrich with description, synonyms, and AI context
                                    semantics_applied = False
                                    if company_research:
                                        try:
                                            from model_semantic_updater import ModelSemanticUpdater
                                            sem_start = time.time()
                                            updater = ModelSemanticUpdater(self, llm_model=llm_model)
                                            log_progress(f"Generating model description, synonyms, and AI context with {updater.llm_model}...")

                                            model_description = updater.generate_model_description(
                                                company_research=company_research,
                                                use_case=use_case or '',
                                                company_name=company_name or '',
                                                model_name=model_name,
                                            )
                                            column_semantics = updater.generate_column_semantics(
                                                company_research=company_research,
                                                model_tml=model_tml_dict,
                                                use_case=use_case or '',
                                                company_name=company_name or '',
                                            )
                                            # Apply to TML dict in-place (returns YAML string)
                                            enriched_yaml = updater.apply_to_model_tml(
                                                model_tml_dict, column_semantics, model_description
                                            )
                                            # Parse back so we can still dump consistently below
                                            model_tml_dict = yaml.safe_load(enriched_yaml)
                                            sem_time = time.time() - sem_start
                                            if column_semantics:
                                                semantics_applied = True
                                                log_progress(f"[OK] Semantics generated: {len(column_semantics)} columns enriched ({sem_time:.1f}s)")
                                                if _slog: _slog.log("thoughtspot", "semantics applied", columns_enriched=len(column_semantics), elapsed_s=round(sem_time, 1), llm_model=llm_model)
                                            else:
                                                log_progress(f"[WARN] Semantics generation returned 0 columns — LLM call may have failed ({sem_time:.1f}s)")
                                                if _slog: _slog.log("thoughtspot", "semantics empty", elapsed_s=round(sem_time, 1), llm_model=llm_model)
                                        except Exception as sem_err:
                                            if _slog: _slog.log("thoughtspot", "semantics failed", error=str(sem_err), llm_model=llm_model)
                                            log_progress(f"[WARN] Semantic enrichment failed (non-fatal): {sem_err}")
                                    else:
                                        log_progress("[WARN] Semantic enrichment skipped: no company research context available")
                                        if _slog: _slog.log("thoughtspot", "semantics skipped", reason="missing company_research")

                                    updated_tml = yaml.dump(model_tml_dict, allow_unicode=True, sort_keys=False)
                                    update_resp = self.session.post(
                                        f"{self.base_url}/api/rest/2.0/metadata/tml/import",
                                        json={
                                            "metadata_tmls": [updated_tml],
                                            "import_policy": "ALL_OR_NONE",
                                            "create_new": False
                                        }
                                    )
                                    if update_resp.status_code == 200:
                                        if semantics_applied:
                                            log_progress("🤖 Spotter enabled + model semantics applied")
                                        else:
                                            log_progress("🤖 Spotter enabled; model semantics were not enriched")
                                        if _slog: _slog.log("thoughtspot", "spotter enabled", semantics_applied=semantics_applied)
                                    else:
                                        log_progress(f"🤖 Model update failed: HTTP {update_resp.status_code} — {update_resp.text[:200]}")
                                        if _slog: _slog.log("thoughtspot", "spotter enable failed", error=f"HTTP {update_resp.status_code}")
                                else:
                                    log_progress("🤖 Spotter enable: export returned no edoc")
                                    if _slog: _slog.log("thoughtspot", "spotter enable failed", error="export returned no edoc")
                            else:
                                log_progress(f"🤖 Spotter enable: export failed HTTP {export_resp.status_code}")
                                if _slog: _slog.log("thoughtspot", "spotter enable failed", error=f"HTTP {export_resp.status_code}")
                        except Exception as spotter_error:
                            if _slog: _slog.log("thoughtspot", "spotter/semantics failed", error=str(spotter_error))
                            log_progress(f"🤖 Spotter/semantics exception: {spotter_error}")

                        # Step 4: Auto-create Liveboard from model
                        lb_start = time.time()

                        log_progress("Creating liveboard...")
                        
                        try:
                            # MCP creates the liveboard. TML is only used afterward to enhance
                            # the liveboard MCP already created.
                            from liveboard_creator import (
                                create_liveboard_from_model_mcp,
                                create_spotter_backup_liveboard,
                                enhance_mcp_liveboard,
                            )
                            used_backup_liveboard = False
                            backup_attempted = False

                            liveboard_context = prepare_liveboard_creation_context(
                                ts_client=self,
                                model_guid=model_guid,
                                tables=tables,
                                company_name=company_name,
                                use_case=use_case,
                                additional_context=additional_context,
                                vertical=vertical,
                                line=line,
                                function=function,
                                log_callback=log_progress,
                            )
                            company_data = liveboard_context['company_data']
                            model_columns = liveboard_context['model_columns']
                            matrix_config = liveboard_context['matrix_config']

                            log_progress("   Checking model answer readiness before liveboard creation...")
                            model_answer_ready = self.wait_for_model_answer_ready(
                                model_guid=model_guid,
                                model_columns=model_columns,
                                log_callback=log_progress,
                                session_logger=_slog,
                            )

                            if not model_answer_ready:
                                warning = (
                                    "Model answer API is not returning liveboard-compatible tokens; "
                                    "creating Spotter/TML backup liveboard without attempting MCP."
                                )
                                results['liveboard_creation_path'] = 'spotter_tml_backup'
                                results['backup_liveboard'] = True
                                results['fallback_reason'] = 'model_answer_not_ready'
                                log_progress(f"   [WARN] {warning}")
                                log_progress("   ⚠️  FALLBACK PATH: using Spotter/TML backup liveboard because model answers are not ready")
                                results['warnings'].append(warning)
                                if _slog:
                                    _slog.log(
                                        "thoughtspot",
                                        "backup liveboard starting",
                                        reason="model_answer_not_ready",
                                        model_guid=model_guid,
                                    )
                                liveboard_result = create_spotter_backup_liveboard(
                                    ts_client=self,
                                    model_id=model_guid,
                                    model_name=model_name,
                                    company_data=company_data,
                                    use_case=use_case or 'General Analytics',
                                    num_visualizations=10,
                                    liveboard_name=liveboard_name,
                                    llm_model=llm_model,
                                    model_columns=model_columns,
                                    prompt_logger=self.prompt_logger,
                                )
                                backup_attempted = True
                                if liveboard_result.get('success'):
                                    used_backup_liveboard = True
                                    backup_warning = liveboard_result.get('warning') or warning
                                    results['warnings'].append(backup_warning)
                                    log_progress(f"   [OK] Backup liveboard created: {liveboard_result.get('liveboard_guid')}")
                                    if _slog:
                                        _slog.log(
                                            "thoughtspot",
                                            "backup liveboard created",
                                            method="Spotter/TML",
                                            liveboard_guid=liveboard_result.get('liveboard_guid'),
                                            warning=backup_warning,
                                        )
                            else:
                                log_progress("   Step 1/2: MCP creating liveboard...")
                                log_progress(f"   [MCP] Model: {model_name}, GUID: {model_guid}")
                                log_progress(f"   [MCP] Use case: {use_case or 'General Analytics'}")
                                log_progress(f"   [MCP] Using {len(model_columns)} columns from ThoughtSpot model")

                                if _slog:
                                    _slog.log_verbose("thoughtspot", "liveboard creation starting", method="MCP")
                                try:
                                    liveboard_result = create_liveboard_from_model_mcp(
                                        ts_client=self,
                                        model_id=model_guid,
                                        model_name=model_name,
                                        company_data=company_data,
                                        use_case=use_case or 'General Analytics',
                                        num_visualizations=10,
                                        liveboard_name=liveboard_name,
                                        matrix_config=matrix_config,
                                        llm_model=llm_model,
                                        model_columns=model_columns,
                                        prompt_logger=self.prompt_logger,
                                        session_logger=_slog,
                                    )
                                except Exception as mcp_error:
                                    import traceback
                                    error_trace = traceback.format_exc()
                                    log_progress(f"   [MCP ERROR] {type(mcp_error).__name__}: {str(mcp_error)}")
                                    liveboard_result = {'success': False, 'error': str(mcp_error), 'traceback': error_trace}

                            if liveboard_result.get('success'):
                                if not used_backup_liveboard:
                                    log_progress(f"   [MCP] Liveboard created: {liveboard_result.get('liveboard_guid')}")
                            else:
                                if backup_attempted:
                                    backup_error = f"Spotter/TML backup liveboard failed: {liveboard_result.get('error', 'Unknown error')}"
                                    log_progress(f"   [ERROR] {backup_error}")
                                    if _slog:
                                        _slog.log(
                                            "thoughtspot",
                                            "backup liveboard failed",
                                            method="Spotter/TML",
                                            error=backup_error,
                                        )
                                    raise Exception(backup_error)

                                raw_mcp_error = liveboard_result.get('error', 'Unknown error')
                                error, error_category = friendly_mcp_liveboard_error(raw_mcp_error)
                                log_progress(f"   [ERROR] {error}")
                                if _slog:
                                    _slog.log(
                                        "thoughtspot",
                                        "liveboard creation failed",
                                        method="MCP",
                                        error_category=error_category,
                                        error=error,
                                        raw_error=str(raw_mcp_error)[:1000],
                                    )

                                if error_category in {"mcp_service", "mcp_answer_tokens"}:
                                    if error_category == "mcp_answer_tokens":
                                        warning = "MCP answer tokens unavailable; creating a clearly marked Spotter/TML backup liveboard."
                                    else:
                                        warning = "MCP service unavailable; creating a clearly marked Spotter/TML backup liveboard."
                                    results['liveboard_creation_path'] = 'spotter_tml_backup'
                                    results['backup_liveboard'] = True
                                    results['fallback_reason'] = error_category
                                    log_progress(f"   [WARN] {warning}")
                                    log_progress(f"   ⚠️  FALLBACK PATH: using Spotter/TML backup liveboard because {error_category}")
                                    results['warnings'].append(warning)
                                    if _slog:
                                        _slog.log(
                                            "thoughtspot",
                                            "backup liveboard starting",
                                            reason=error_category,
                                            raw_error=str(raw_mcp_error)[:1000],
                                        )
                                    backup_result = create_spotter_backup_liveboard(
                                        ts_client=self,
                                        model_id=model_guid,
                                        model_name=model_name,
                                        company_data=company_data,
                                        use_case=use_case or 'General Analytics',
                                        num_visualizations=10,
                                        liveboard_name=liveboard_name,
                                        llm_model=llm_model,
                                        model_columns=model_columns,
                                        prompt_logger=self.prompt_logger,
                                    )
                                    if backup_result.get('success'):
                                        used_backup_liveboard = True
                                        liveboard_result = backup_result
                                        backup_warning = backup_result.get('warning') or warning
                                        results['warnings'].append(backup_warning)
                                        log_progress(f"   [OK] Backup liveboard created: {backup_result.get('liveboard_guid')}")
                                        if _slog:
                                            _slog.log(
                                                "thoughtspot",
                                                "backup liveboard created",
                                                method="Spotter/TML",
                                                liveboard_guid=backup_result.get('liveboard_guid'),
                                                warning=backup_warning,
                                            )
                                    else:
                                        backup_error = f"Spotter/TML backup liveboard failed: {backup_result.get('error', 'Unknown error')}"
                                        log_progress(f"   [ERROR] {backup_error}")
                                        if _slog:
                                            _slog.log("thoughtspot", "backup liveboard failed", method="Spotter/TML", error=backup_error)
                                        raise Exception(f"{error} Backup attempt also failed. {backup_error}")
                                else:
                                    raise Exception(error)

                            if liveboard_result.get('liveboard_guid'):
                                enhance_label = "backup liveboard" if used_backup_liveboard else "MCP liveboard"
                                log_progress(f"   Step 2/2: Enhancing {enhance_label} with TML post-processing...")
                                if _slog:
                                    _slog.log_verbose(
                                        "thoughtspot",
                                        "liveboard enhance started",
                                        liveboard_creation_path="spotter_tml_backup" if used_backup_liveboard else "mcp",
                                    )
                                enhance_result = enhance_mcp_liveboard(
                                    liveboard_guid=liveboard_result['liveboard_guid'],
                                    company_data=company_data,
                                    ts_client=self,
                                    add_groups=True,
                                    fix_kpis=True,
                                    apply_brand_colors=True,
                                    llm_model=llm_model,
                                    layout_strategy="golden_two_tab",
                                )
                                if enhance_result.get('success'):
                                    log_progress("   [OK] Enhancement applied")
                                    if _slog:
                                        _slog.log_verbose(
                                            "thoughtspot",
                                            "liveboard enhance completed",
                                            liveboard_creation_path="spotter_tml_backup" if used_backup_liveboard else "mcp",
                                            enhancements=enhance_result.get('enhancements', []),
                                        )
                                else:
                                    enhance_err = f"TML enhancement failed: {enhance_result.get('message', 'unknown')[:100]}"
                                    if used_backup_liveboard:
                                        log_progress(f"   [WARN] {enhance_err}; keeping backup liveboard")
                                        results['warnings'].append(enhance_err)
                                    else:
                                        log_progress(f"   [ERROR] {enhance_err}")
                                        results['errors'].append(enhance_err)
                                    if _slog:
                                        _slog.log(
                                            "thoughtspot",
                                            "liveboard enhance failed",
                                            liveboard_creation_path="spotter_tml_backup" if used_backup_liveboard else "mcp",
                                            error=enhance_err,
                                        )

                            # Check result
                            print(f"🔍 DEBUG: Liveboard result received: {liveboard_result}")
                            print(f"🔍 DEBUG: Success flag: {liveboard_result.get('success')}")

                            if liveboard_result.get('success'):
                                lb_time = time.time() - lb_start
                                log_progress(f"[OK] Liveboard created ({lb_time:.1f}s)")
                                created_method = "Spotter/TML backup" if used_backup_liveboard else "MCP"
                                if _slog: _slog.log("thoughtspot", "liveboard created",
                                                    method=created_method,
                                                    liveboard_guid=liveboard_result.get('liveboard_guid'),
                                                    elapsed_s=round(lb_time, 1))
                                results['liveboard'] = liveboard_result.get('liveboard_name')
                                results['liveboard_guid'] = liveboard_result.get('liveboard_guid')
                                if liveboard_result.get('backup_liveboard'):
                                    results['backup_liveboard'] = True
                                    results['liveboard_creation_path'] = liveboard_result.get('liveboard_creation_path', 'spotter_tml_backup')
                                else:
                                    results['backup_liveboard'] = False
                                    results['liveboard_creation_path'] = 'mcp'
                                if liveboard_result.get('liveboard_url'):
                                    results['liveboard_url'] = liveboard_result.get('liveboard_url')

                                # Assign tag to liveboard
                                if tag_name and liveboard_result.get('liveboard_guid'):
                                    log_progress(f"Assigning tag '{tag_name}' to liveboard...")
                                    self.assign_tags_to_objects([liveboard_result['liveboard_guid']], 'PINBOARD_ANSWER_BOOK', tag_name)
                                    if _slog: _slog.log_verbose("thoughtspot", "liveboard tagged", tag=tag_name)

                                # Share liveboard
                                _effective_share = share_with or get_admin_setting('SHARE_WITH', required=False)
                                if _effective_share and liveboard_result.get('liveboard_guid'):
                                    log_progress(f"Sharing liveboard with '{_effective_share}'...")
                                    self.share_objects([liveboard_result['liveboard_guid']], 'LIVEBOARD', _effective_share)
                                    if _slog: _slog.log_verbose("thoughtspot", "liveboard shared", share_with=_effective_share)
                            else:
                                error = f"Liveboard creation failed: {liveboard_result.get('error', 'Unknown error')}"
                                print(f"❌ DEBUG: Liveboard creation failed! Error: {error}")
                                if _slog: _slog.log("thoughtspot", "liveboard failed", error=error[:200])
                                results['errors'].append(error)
                                log_progress(f"[ERROR] {error}")
                        except Exception as lb_error:
                            error = f"Liveboard creation exception: {str(lb_error)}"
                            if _slog: _slog.log("thoughtspot", "liveboard failed", error=str(lb_error)[:200])
                            results['errors'].append(error)
                            log_progress(f"[ERROR] {error}")
                    else:
                        # Extract detailed error information
                        obj_response = objects[0].get('response', {})
                        status = obj_response.get('status', {})
                        error_message = status.get('error_message', 'Unknown error')
                        
                        # Clean HTML tags from error message (ThoughtSpot sometimes returns HTML)
                        error_message = re.sub(r'<[^>]+>', '', error_message).strip()
                        if not error_message:
                            error_message = 'Schema validation failed (no details provided)'
                        
                        error_code = status.get('error_code', 'N/A')
                        
                        # Try to extract additional error details from various response fields
                        error_details = []
                        
                        # Check for detailed error messages in different response structures
                        if 'error_details' in status:
                            error_details.append(f"Error details: {status.get('error_details')}")
                        
                        if 'validation_errors' in obj_response:
                            error_details.append(f"Validation errors: {obj_response.get('validation_errors')}")
                        
                        if 'warnings' in obj_response:
                            error_details.append(f"Warnings: {obj_response.get('warnings')}")
                        
                        # Check header for additional info
                        header = obj_response.get('header', {})
                        if 'error' in header:
                            error_details.append(f"Header error: {header.get('error')}")
                        
                        # Get any additional error details
                        full_response = json.dumps(objects[0], indent=2)
                        
                        # Save the TML that failed for debugging
                        import tempfile
                        # os is already imported at module level
                        try:
                            debug_dir = os.path.join(tempfile.gettempdir(), 'thoughtspot_debug')
                            os.makedirs(debug_dir, exist_ok=True)
                            failed_tml_path = os.path.join(debug_dir, f'failed_model_{datetime.now().strftime("%Y%m%d_%H%M%S")}.tml')
                            with open(failed_tml_path, 'w') as f:
                                f.write(model_tml)
                            log_progress(f"💾 Failed TML saved to: {failed_tml_path}")
                            print(f"💾 Failed TML saved to: {failed_tml_path}")
                        except Exception as save_error:
                            log_progress(f"[WARN] Could not save failed TML: {save_error}")
                        
                        # Build comprehensive error message
                        error = f"Model validation failed: {error_message}"
                        if error_code != 'N/A':
                            error += f" (Error code: {error_code})"
                        
                        if error_details:
                            error += f"\n\nAdditional details:\n" + "\n".join(error_details)
                        
                        print(f"📋 Full model response: {full_response}")  # DEBUG: Show full response
                        print(f"   ❌ {error}")
                        log_progress(f"   ❌ {error}")
                        log_progress(f"   📋 Full response details:")
                        log_progress(f"{full_response}")
                        
                        # Log full TML for debugging
                        log_progress(f"\n📄 TML that was sent:\n{model_tml}")
                        
                        results['errors'].append(error)
                        results['errors'].append(f"Full API response: {full_response}")
                        results['errors'].append(f"Failed TML saved to: {failed_tml_path if 'failed_tml_path' in locals() else 'N/A'}")
                else:
                    error = "Model failed: No objects in response"
                    log_progress(f"   ❌ {error}")
                    results['errors'].append(error)

            # Mark as successful if we got this far
            results['success'] = len(results['errors']) == 0

            # Log summary with clickable links before returning
            ts_base = self.base_url.rstrip('/')
            model_guid = results.get('model_guid', '')
            liveboard_guid = results.get('liveboard_guid', '')
            lb_url = results.get('liveboard_url', '')
            if not lb_url and liveboard_guid:
                lb_url = f"{ts_base}/#/pinboard/{liveboard_guid}"
            model_url = f"{ts_base}/#/data/tables/{model_guid}" if model_guid else ''

            log_progress("─" * 40)
            if results['success']:
                if results.get('warnings'):
                    log_progress(f"⚠️ Pipeline complete with {len(results['warnings'])} warning(s)")
                    for warning in results['warnings'][:3]:
                        log_progress(f"Warning: {warning}")
                else:
                    log_progress("✅ Pipeline complete")
            else:
                log_progress(f"⚠️ Pipeline finished with {len(results['errors'])} error(s)")
            if model_url:
                log_progress(f"Model: {model_url}")
            if lb_url:
                log_progress(f"Liveboard: {lb_url}")
            log_progress("─" * 40)

        except Exception as e:
            import traceback
            error_msg = str(e)
            full_trace = traceback.format_exc()
            
            # Log to console with full details
            print(f"\n{'='*60}")
            print(f"❌ DEPLOYMENT EXCEPTION")
            print(f"{'='*60}")
            print(f"Error: {error_msg}")
            print(f"\nFull traceback:")
            print(full_trace)
            print(f"{'='*60}\n")
            
            # Log through callback too
            log_progress(f"[ERROR] Deployment failed: {error_msg}")
            log_progress(f"Traceback: {full_trace}")

            if _slog:
                _slog.log("thoughtspot", "deployment exception", error=error_msg)

            results['errors'].append(error_msg)
            results['errors'].append(f"Traceback: {full_trace}")

        return results


def deploy_to_thoughtspot(ddl: str, database: str, schema: str, 
                         connection_name: str = None, company_name: str = None, 
                         use_case: str = None, progress_callback=None) -> Dict:
    """
    Convenience function for deploying to ThoughtSpot
    
    Args:
        ddl: Data Definition Language statements
        database: Target database name
        schema: Target schema name
        connection_name: Optional connection name
        progress_callback: Optional callback for progress updates
        
    Returns:
        Dict with deployment results
    """
    deployer = ThoughtSpotDeployer()
    return deployer.deploy_all(
        ddl=ddl,
        database=database,
        schema=schema,
        base_name=schema,
        connection_name=connection_name,
        company_name=company_name,
        use_case=use_case,
        progress_callback=progress_callback,
    )


if __name__ == "__main__":
    # Example usage
    test_ddl = """
    CREATE TABLE CUSTOMERS (
    CUSTOMERID INT64 PRIMARY KEY,
    NAME VARCHAR(255)
);
    """
    
    # Test deployment - using a schema that exists
    results = deploy_to_thoughtspot(
        ddl=test_ddl,
        database="DEMOBUILD",  # Use the actual Snowflake database
        schema="THOUGHTSPO_SALESA_20250915_193303"  # Use the working schema from your working table
    )
    
    print("\n" + "=" * 60)
    print("📊 DEPLOYMENT RESULTS:")
    print("=" * 60)
    print(json.dumps(results, indent=2))