"""
Database utilities for Spark POC testing
Simulates database operations and Snowflake connectivity
"""

import pandas as pd
import os


def get_config():
    """Get configuration values (simulated)"""
    return {
        'database_host': 'snowflake-account.snowflakecomputing.com',
        'database_name': 'SPARK',
        'schema_name': 'MUSIC_DATA',
        'warehouse': 'DEV_OWS_WH',
        'user': os.getenv('SNOWFLAKE_USER', 'not_configured'),
        'password': '***',  # Would be from environment
        'chartmetric_table': 'DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK'
    }


def test_connection():
    """Test database connection (simulated)"""
    try:
        # In real implementation, this would test actual Snowflake connection
        # For now, simulate the test
        config = get_config()
        
        if config['user'] == 'not_configured':
            return "❌ Connection not configured - set SNOWFLAKE_USER environment variable"
        
        # Simulate connection test
        return "✅ Connection test successful (simulated)"
        
    except Exception as e:
        return f"❌ Connection test failed: {str(e)}"


def get_sample_songs():
    """Get sample song data (simulated Chartmetric data)"""
    sample_data = {
        'artist': ['Bahadır Özdemir', 'Thomas3191', 'Max Thomson', 'CHRISTOS BOUGAS'],
        'track': ['Ya Hızır', 'Originalton', 'Cosplay Girl', 'πρωτότυπος ήχος'],
        'tiktok_track_id': [
            '6741496814790248449',
            '7148348203996367622', 
            '6960652200922056706',
            '7169534149685545733'
        ],
        'posts_latest': [24, 2, 2, 1],
        'active': [True, True, True, True],
        'isrc': ['DEXD61800207', '', 'QZHN62125706', ''],
        'created_at': ['2021-04-23', '2023-07-24', '2021-06-26', '2023-07-07']
    }
    
    return pd.DataFrame(sample_data)


def format_tiktok_url(track_title, track_id):
    """Format TikTok music URL from track ID"""
    # Clean track title for URL (basic implementation)
    clean_title = track_title.replace(' ', '-').replace('ı', 'i')
    url = f"https://www.tiktok.com/music/{clean_title}-{track_id}"
    return url


def search_songs(artist=None, track=None, limit=10):
    """Search songs in database (simulated)"""
    df = get_sample_songs()
    
    # Apply filters
    if artist:
        mask = df['artist'].str.contains(artist, case=False, na=False)
        df = df[mask]
    
    if track:
        mask = df['track'].str.contains(track, case=False, na=False)
        df = df[mask]
    
    return df.head(limit)


def get_song_analytics(track_id):
    """Get analytics for a specific song (simulated)"""
    # In real implementation, this would query video data for the song
    return {
        'track_id': track_id,
        'total_videos': 150,
        'total_views': 2500000,
        'total_likes': 350000,
        'total_shares': 25000,
        'top_video_views': 500000,
        'top_creator': 'popular_tiktoker',
        'last_updated': '2024-01-15'
    }


def create_tables_sql():
    """Return SQL for creating Spark POC tables"""
    return """
-- Spark POC Database Schema
CREATE DATABASE IF NOT EXISTS SPARK;
USE SPARK;

CREATE SCHEMA IF NOT EXISTS MUSIC_DATA;
USE SCHEMA MUSIC_DATA;

-- Track information with Chartmetric integration
CREATE OR REPLACE TABLE TRACKED_SONGS (
    song_id VARCHAR(50) PRIMARY KEY,
    title VARCHAR(500),
    artist VARCHAR(500), 
    tiktok_track_id VARCHAR(50),
    tiktok_music_url VARCHAR(1000),
    chartmetric_id INTEGER,
    created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    last_updated TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Video data from TikTok scraping
CREATE OR REPLACE TABLE SONG_VIDEOS (
    video_id VARCHAR(50) PRIMARY KEY,
    song_id VARCHAR(50),
    creator VARCHAR(200),
    creator_profile_url VARCHAR(1000),
    views INTEGER,
    likes INTEGER, 
    shares INTEGER,
    hearts INTEGER,
    url VARCHAR(1000),
    scraped_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    FOREIGN KEY (song_id) REFERENCES TRACKED_SONGS(song_id)
);

-- Aggregated analytics
CREATE OR REPLACE TABLE SONG_ANALYTICS (
    song_id VARCHAR(50) PRIMARY KEY,
    total_videos INTEGER,
    total_views BIGINT,
    total_likes BIGINT,
    total_shares BIGINT,
    top_video_id VARCHAR(50),
    top_creator VARCHAR(200),
    date DATE DEFAULT CURRENT_DATE(),
    FOREIGN KEY (song_id) REFERENCES TRACKED_SONGS(song_id)
);
"""