test_db_schema.py

#!/usr/bin/env python3
"""
Test module to diagnose database schema issues with market_data table.
This script:
1. Connects to the database
2. Tries to get the schema definition or sample record
3. Attempts various insertions with different field formats
4. Provides detailed error diagnostics
"""
import json
import logging
import sys
from datetime import datetime, timezone
from pathlib import Path

# Add project root to path
current_dir = Path(__file__).parent
sys.path.append(str(current_dir))

from src.database.client import DatabaseClient

# Configure logging
logging.basicConfig(
    level=logging.INFO, format="%(asctime)s - %(name)s - %(levelname)s - %(message)s"
)
logger = logging.getLogger(__name__)


def inspect_db_schema():
    """Inspect the database schema to understand field names and types."""
    db_client = DatabaseClient()

    if not db_client.client:
        logger.error("Failed to initialize database client.")
        return False

    # 1. Try to get one record to inspect schema
    logger.info("Attempting to retrieve a sample market_data record...")
    try:
        # Try to select a single record to examine fields
        response = db_client.client.table("market_data").select("*").limit(1).execute()
        if response.data and len(response.data) > 0:
            sample = response.data[0]
            logger.info(f"Found sample record with fields: {list(sample.keys())}")
            logger.info(f"Sample data: {sample}")
            return sample
        else:
            logger.warning("No existing records found in market_data table.")
    except Exception as e:
        logger.error(f"Error querying market_data table: {str(e)}")

    # 2. If no records, try to get the table schema
    logger.info("Attempting to retrieve table schema information...")
    try:
        # Query schema information
        response = db_client.client.rpc(
            "get_schema_info", {"table_name": "market_data"}
        ).execute()

        if response.data:
            logger.info(f"Schema info: {response.data}")
            return response.data
    except Exception as e:
        logger.error(f"Error querying schema: {str(e)}")

    return None


def test_insertion_variants():
    """Test various field combinations to determine correct schema."""
    db_client = DatabaseClient()
    if not db_client.client:
        logger.error("Failed to initialize database client.")
        return

    # Common fields that should be present in all variants
    base_record = {
        "asset_id": 1,  # BTC
        "timestamp": datetime.now(timezone.utc).isoformat(),
        "interval": "1d",
        "source": "test",
        "additional_data": json.dumps({"test": True}),
    }

    # Test different field variants
    variants = [
        # Variant 1: Using price fields
        {
            **base_record,
            "price": 50000.0,
            "volume": 1000000.0,
            "market_cap": 900000000.0,
        },
        # Variant 2: Using _usd suffix
        {
            **base_record,
            "price_usd": 50000.0,
            "volume_24h_usd": 1000000.0,
            "market_cap_usd": 900000000.0,
        },
        # Variant 3: Using OHLC format
        {
            **base_record,
            "open_price": 49000.0,
            "high_price": 51000.0,
            "low_price": 48000.0,
            "close_price": 50000.0,
            "volume": 1000000.0,
        },
        # Variant 4: Just minimal fields
        {**base_record, "price": 50000.0},
    ]

    # Try each variant
    for i, variant in enumerate(variants):
        logger.info(f"Testing variant {i+1} with fields: {list(variant.keys())}")
        try:
            # Try direct upsert to get more detailed error
            response = (
                db_client.client.table("market_data")
                .upsert(variant, on_conflict="asset_id,timestamp,interval")
                .execute()
            )

            logger.info(
                f"Success! Variant {i+1} worked: {response.data if hasattr(response, 'data') else 'No response data'}"
            )
            return variant  # Return first successful variant
        except Exception as e:
            if hasattr(e, "message"):
                error_msg = e.message
            elif hasattr(e, "details"):
                error_msg = e.details
            else:
                error_msg = str(e)

            logger.error(f"Variant {i+1} failed: {error_msg}")

    return None


if __name__ == "__main__":
    logger.info("Starting database schema inspection...")

    # First try to get schema information
    schema_info = inspect_db_schema()

    # Then try different insertion variants
    logger.info("\nTesting different field variations...")
    successful_variant = test_insertion_variants()

    if successful_variant:
        logger.info(
            f"\nSUCCESS: Found working field structure: {list(successful_variant.keys())}"
        )
        logger.info("Use these field names in your data population script.")
    else:
        logger.error("\nFAILED: Could not determine correct field structure.")
        logger.info("Check database logs or contact your DBA for schema details.")