visualization/data/data_loader.py

"""
Data loader module for Sentiment Analysis Visualization
Handles Snowflake connection and data loading with caching
"""
import sys
import os
import re
import pandas as pd
import numpy as np
import streamlit as st
from pathlib import Path
import json
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta

# Add parent directory to path to import SnowFlakeConnection
parent_dir = Path(__file__).resolve().parent.parent.parent
sys.path.append(str(parent_dir))

from visualization.SnowFlakeConnection import SnowFlakeConn


class SentimentDataLoader:
    """
    Loads sentiment analysis data from Snowflake with caching.

    Three data loading modes:
      - load_dashboard_data()      : lightweight (no text), cached 24h
      - load_sa_data(...)          : top-N content stats + sampled comments, on-demand
      - load_reply_required_data() : reply-queue comments with text, on-demand
    """

    def __init__(self, config_path=None):
        if config_path is None:
            config_path = Path(__file__).parent.parent / "config" / "viz_config.json"

        with open(config_path, 'r') as f:
            self.config = json.load(f)

        self.query = self.config['snowflake']['query']
        self.dashboard_query = self.config['snowflake'].get('dashboard_query', self.query)
        self.demographics_query = self.config['snowflake'].get('demographics_query', None)

    # ─────────────────────────────────────────────────────────────
    # Dashboard data (lightweight, 24-hour cache)
    # ─────────────────────────────────────────────────────────────

    @st.cache_data(ttl=86400)
    def load_dashboard_data(_self):
        """
        Load lightweight dashboard data from Snowflake (no text columns).
        Includes demographics merge if demographics_query is configured.

        Returns:
            pd.DataFrame
        """
        try:
            conn = SnowFlakeConn()
            df = conn.run_read_query(_self.dashboard_query, "dashboard data")
            conn.close_connection()

            if df is None or df.empty:
                st.error("No dashboard data returned from Snowflake")
                return pd.DataFrame()

            df = _self._process_dashboard_dataframe(df)

            if _self.demographics_query:
                demographics_df = _self.load_demographics_data()
                df = _self.merge_demographics_with_comments(df, demographics_df)

            return df

        except Exception as e:
            st.error(f"Error loading dashboard data from Snowflake: {e}")
            return pd.DataFrame()

    def _process_dashboard_dataframe(self, df):
        """Process lightweight dashboard dataframe (no text columns)."""
        df.columns = df.columns.str.lower()

        if 'comment_timestamp' in df.columns:
            df['comment_timestamp'] = pd.to_datetime(df['comment_timestamp'], errors='coerce')

        if 'processed_at' in df.columns:
            df['processed_at'] = pd.to_datetime(df['processed_at'], errors='coerce')

        df['sentiment_polarity'] = df['sentiment_polarity'].fillna('unknown')
        df['intent'] = df['intent'].fillna('unknown')
        df['platform'] = df['platform'].fillna('unknown').str.lower()
        df['brand'] = df['brand'].fillna('unknown').str.lower()

        if 'requires_reply' in df.columns:
            df['requires_reply'] = df['requires_reply'].astype(bool)

        return df

    # ─────────────────────────────────────────────────────────────
    # Full data (legacy / kept for compatibility, 24-hour cache)
    # ─────────────────────────────────────────────────────────────

    @st.cache_data(ttl=86400)
    def load_data(_self, reload=False):
        """
        Load full sentiment data (with text). Kept for compatibility.
        Prefer load_dashboard_data() for dashboard views.
        """
        try:
            conn = SnowFlakeConn()
            df = conn.run_read_query(_self.query, "sentiment features")
            conn.close_connection()

            if df is None or df.empty:
                st.error("No data returned from Snowflake")
                return pd.DataFrame()

            df = _self._process_dataframe(df)

            if _self.demographics_query:
                demographics_df = _self.load_demographics_data()
                df = _self.merge_demographics_with_comments(df, demographics_df)

            return df

        except Exception as e:
            st.error(f"Error loading data from Snowflake: {e}")
            return pd.DataFrame()

    def _process_dataframe(self, df):
        """Process full dataframe including vectorized display_text computation."""
        df.columns = df.columns.str.lower()

        if 'comment_timestamp' in df.columns:
            df['comment_timestamp'] = pd.to_datetime(df['comment_timestamp'], errors='coerce')

        if 'processed_at' in df.columns:
            df['processed_at'] = pd.to_datetime(df['processed_at'], errors='coerce')

        df['sentiment_polarity'] = df['sentiment_polarity'].fillna('unknown')
        df['intent'] = df['intent'].fillna('unknown')
        df['platform'] = df['platform'].fillna('unknown').str.lower()
        df['brand'] = df['brand'].fillna('unknown').str.lower()

        if 'requires_reply' in df.columns:
            df['requires_reply'] = df['requires_reply'].astype(bool)

        # Vectorized display_text: use translated_text when non-English and available
        if 'translated_text' in df.columns and 'is_english' in df.columns:
            mask_translate = (df['is_english'] == False) & df['translated_text'].notna()
            df['display_text'] = df.get('original_text', pd.Series('', index=df.index)).fillna('')
            df.loc[mask_translate, 'display_text'] = df.loc[mask_translate, 'translated_text']
        elif 'original_text' in df.columns:
            df['display_text'] = df['original_text'].fillna('')
        else:
            df['display_text'] = ''

        # Vectorized short version
        text = df['display_text'].astype(str)
        df['display_text_short'] = text.where(text.str.len() <= 100, text.str[:100] + '...')

        return df

    # ─────────────────────────────────────────────────────────────
    # Sentiment Analysis page data (on-demand, 24-hour cache)
    # ─────────────────────────────────────────────────────────────

    def load_sa_data(self, platform, brand, top_n=10, min_comments=10,
                     sort_by='severity_score', sentiments=None, intents=None,
                     date_range=None):
        """
        Load Sentiment Analysis page data:
          1. Content aggregation stats for top-N contents
          2. Sampled comments (up to 50 neg + 50 pos + 50 other per content)

        Args:
            platform: Selected platform string
            brand: Selected brand string
            top_n: Max number of contents to return
            min_comments: Minimum comment threshold for inclusion
            sort_by: 'severity_score' | 'sentiment_percentage' | 'sentiment_count' | 'total_comments'
            sentiments: List of sentiments to filter by (dominant_sentiment)
            intents: List of intents to filter by
            date_range: Tuple (start_date, end_date) or None

        Returns:
            tuple: (contents_df, comments_df)
        """
        sentiments_key = tuple(sorted(sentiments)) if sentiments else ()
        intents_key = tuple(sorted(intents)) if intents else ()
        date_key = (str(date_range[0]), str(date_range[1])) if date_range and len(date_range) == 2 else ()

        return self._fetch_sa_data(
            platform, brand, top_n, min_comments, sort_by,
            sentiments_key, intents_key, date_key
        )

    @st.cache_data(ttl=86400)
    def _fetch_sa_data(_self, platform, brand, top_n, min_comments, sort_by,
                       sentiments, intents, date_range):
        """Cached SA data fetch — returns (contents_df, comments_df)."""
        try:
            conn = SnowFlakeConn()

            # Step 1: content-level aggregation query
            content_query = _self._build_sa_content_query(
                platform, brand, min_comments, sort_by, date_range
            )
            contents_df = conn.run_read_query(content_query, "SA content aggregation")

            if contents_df is None or contents_df.empty:
                conn.close_connection()
                return pd.DataFrame(), pd.DataFrame()

            # columns already lowercased by run_read_query
            contents_df = _self._process_sa_content_stats(contents_df)

            # Python-side sentiment filter (dominant_sentiment)
            if sentiments:
                contents_df = contents_df[contents_df['dominant_sentiment'].isin(sentiments)]

            # Limit to top_n after Python-side filtering
            contents_df = contents_df.head(top_n)

            if contents_df.empty:
                conn.close_connection()
                return pd.DataFrame(), pd.DataFrame()

            # Step 2: sampled comments with text for those content_sks
            content_sk_list = contents_df['content_sk'].tolist()
            comments_query = _self._build_sa_comments_query(
                platform, brand, content_sk_list, date_range
            )
            comments_df = conn.run_read_query(comments_query, "SA sampled comments")
            conn.close_connection()

            if comments_df is not None and not comments_df.empty:
                comments_df = _self._process_sa_comments(comments_df)

                # Python-side intent filter — keep only content_sks that have
                # at least one comment matching any selected intent
                if intents:
                    pattern = '|'.join(re.escape(i) for i in intents)
                    valid_sks = comments_df[
                        comments_df['intent'].str.contains(pattern, na=False, case=False)
                    ]['content_sk'].unique()
                    contents_df = contents_df[contents_df['content_sk'].isin(valid_sks)]
                    comments_df = comments_df[comments_df['content_sk'].isin(valid_sks)]
            else:
                comments_df = pd.DataFrame()

            return contents_df, comments_df

        except Exception as e:
            st.error(f"Error loading SA data: {e}")
            return pd.DataFrame(), pd.DataFrame()

    def _build_sa_content_query(self, platform, brand, min_comments, sort_by, date_range):
        """Build dynamic SQL for content-level aggregation (no text columns)."""
        # Build table-qualified date clauses to avoid ambiguity when a JOIN is present
        social_date_clause = self._build_date_clause(date_range, table_alias='s')
        musora_date_clause = self._build_date_clause(date_range)

        safe_brand = self._sanitize_value(brand.lower())
        safe_platform = self._sanitize_value(platform.lower())

        sort_exprs = {
            'severity_score': (
                "(SUM(CASE WHEN SENTIMENT_POLARITY IN ('negative','very_negative') THEN 1 ELSE 0 END)"
                " * 100.0 / COUNT(*)) * SQRT(COUNT(*))"
            ),
            'sentiment_percentage': (
                "SUM(CASE WHEN SENTIMENT_POLARITY IN ('negative','very_negative') THEN 1 ELSE 0 END)"
                " * 100.0 / COUNT(*)"
            ),
            'sentiment_count': (
                "SUM(CASE WHEN SENTIMENT_POLARITY IN ('negative','very_negative') THEN 1 ELSE 0 END)"
            ),
            'total_comments': "COUNT(*)",
        }
        sort_expr = sort_exprs.get(sort_by, sort_exprs['severity_score'])

        parts = []

        if platform != 'musora_app':
            parts.append(f"""
                SELECT
                    s.COMMENT_SK, s.CONTENT_SK, s.CONTENT_DESCRIPTION,
                    c.PERMALINK_URL, CAST(NULL AS VARCHAR) AS THUMBNAIL_URL,
                    s.SENTIMENT_POLARITY, s.INTENT, s.REQUIRES_REPLY, s.COMMENT_TIMESTAMP
                FROM SOCIAL_MEDIA_DB.ML_FEATURES.COMMENT_SENTIMENT_FEATURES s
                LEFT JOIN SOCIAL_MEDIA_DB.CORE.DIM_CONTENT c ON s.CONTENT_SK = c.CONTENT_SK
                WHERE LOWER(s.CHANNEL_NAME) = '{safe_brand}'
                  AND LOWER(s.PLATFORM) = '{safe_platform}'
                  {social_date_clause}
            """)

        if platform == 'musora_app':
            parts.append(f"""
                SELECT
                    COMMENT_SK, CONTENT_SK, CONTENT_DESCRIPTION,
                    PERMALINK_URL, THUMBNAIL_URL,
                    SENTIMENT_POLARITY, INTENT, REQUIRES_REPLY, COMMENT_TIMESTAMP
                FROM SOCIAL_MEDIA_DB.ML_FEATURES.MUSORA_COMMENT_SENTIMENT_FEATURES
                WHERE LOWER(CHANNEL_NAME) = '{safe_brand}'
                  {musora_date_clause}
            """)

        combined = " UNION ALL ".join(parts)

        return f"""
        WITH combined AS ({combined})
        SELECT
            CONTENT_SK,
            MAX(CONTENT_DESCRIPTION)                                                     AS CONTENT_DESCRIPTION,
            MAX(PERMALINK_URL)                                                           AS PERMALINK_URL,
            MAX(THUMBNAIL_URL)                                                           AS THUMBNAIL_URL,
            COUNT(*)                                                                     AS TOTAL_COMMENTS,
            SUM(CASE WHEN REQUIRES_REPLY THEN 1 ELSE 0 END)                            AS REPLY_REQUIRED_COUNT,
            SUM(CASE WHEN SENTIMENT_POLARITY = 'very_negative' THEN 1 ELSE 0 END)      AS VERY_NEGATIVE_COUNT,
            SUM(CASE WHEN SENTIMENT_POLARITY = 'negative'      THEN 1 ELSE 0 END)      AS NEGATIVE_COUNT_RAW,
            SUM(CASE WHEN SENTIMENT_POLARITY = 'neutral'       THEN 1 ELSE 0 END)      AS NEUTRAL_COUNT,
            SUM(CASE WHEN SENTIMENT_POLARITY = 'positive'      THEN 1 ELSE 0 END)      AS POSITIVE_COUNT_RAW,
            SUM(CASE WHEN SENTIMENT_POLARITY = 'very_positive' THEN 1 ELSE 0 END)      AS VERY_POSITIVE_COUNT
        FROM combined
        GROUP BY CONTENT_SK
        HAVING COUNT(*) >= {int(min_comments)}
        ORDER BY {sort_expr} DESC
        """

    def _process_sa_content_stats(self, df):
        """
        Derive all columns expected by the existing SA page UI from the
        raw content-aggregation result.
        """
        df['negative_count'] = df['very_negative_count'] + df['negative_count_raw']
        df['positive_count'] = df['positive_count_raw'] + df['very_positive_count']

        df['negative_percentage'] = (
            df['negative_count'] / df['total_comments'] * 100
        ).round(2)
        df['positive_percentage'] = (
            df['positive_count'] / df['total_comments'] * 100
        ).round(2)

        df['severity_score'] = (
            df['negative_percentage'] * (df['total_comments'] ** 0.5)
        ).round(2)

        # These mirror the columns produced by get_sentiment_filtered_contents()
        df['dynamic_severity_score'] = df['severity_score']
        df['selected_sentiment_count'] = df['negative_count']
        df['selected_sentiment_percentage'] = df['negative_percentage']

        # Dominant sentiment = sentiment with the highest count
        sentiment_cols = pd.DataFrame({
            'very_negative': df['very_negative_count'],
            'negative':      df['negative_count_raw'],
            'neutral':       df['neutral_count'],
            'positive':      df['positive_count_raw'],
            'very_positive': df['very_positive_count'],
        })
        df['dominant_sentiment'] = sentiment_cols.idxmax(axis=1)

        return df

    def _build_sa_comments_query(self, platform, brand, content_sk_list, date_range):
        """
        Build SQL for sampled comments for a list of content_sks.
        Samples up to 50 per (content_sk, sentiment_group) — neg, pos, other.
        display_text is computed in SQL (no need to fetch both original + translated).
        """
        # Qualified date clauses: social media query has a JOIN so needs s. prefix
        social_date_clause = self._build_date_clause(date_range, table_alias='s')
        musora_date_clause = self._build_date_clause(date_range)
        safe_brand = self._sanitize_value(brand.lower())
        content_sks_str = ", ".join(f"'{self._sanitize_value(str(sk))}'" for sk in content_sk_list)

        parts = []

        if platform != 'musora_app':
            parts.append(f"""
                SELECT
                    s.COMMENT_SK, s.COMMENT_ID, s.CONTENT_SK, s.CONTENT_DESCRIPTION,
                    CASE WHEN s.IS_ENGLISH = FALSE AND s.TRANSLATED_TEXT IS NOT NULL
                         THEN s.TRANSLATED_TEXT ELSE s.ORIGINAL_TEXT END AS DISPLAY_TEXT,
                    s.ORIGINAL_TEXT,
                    LOWER(s.PLATFORM)      AS PLATFORM,
                    LOWER(s.CHANNEL_NAME)  AS BRAND,
                    s.COMMENT_TIMESTAMP, s.AUTHOR_NAME,
                    s.DETECTED_LANGUAGE, s.SENTIMENT_POLARITY, s.INTENT,
                    s.REQUIRES_REPLY, s.SENTIMENT_CONFIDENCE, s.IS_ENGLISH,
                    c.PERMALINK_URL
                FROM SOCIAL_MEDIA_DB.ML_FEATURES.COMMENT_SENTIMENT_FEATURES s
                LEFT JOIN SOCIAL_MEDIA_DB.CORE.DIM_CONTENT c ON s.CONTENT_SK = c.CONTENT_SK
                WHERE s.CONTENT_SK IN ({content_sks_str})
                  AND LOWER(s.CHANNEL_NAME) = '{safe_brand}'
                  {social_date_clause}
            """)

        if platform == 'musora_app':
            parts.append(f"""
                SELECT
                    COMMENT_SK, COMMENT_ID, CONTENT_SK, CONTENT_DESCRIPTION,
                    CASE WHEN IS_ENGLISH = FALSE AND TRANSLATED_TEXT IS NOT NULL
                         THEN TRANSLATED_TEXT ELSE ORIGINAL_TEXT END AS DISPLAY_TEXT,
                    ORIGINAL_TEXT,
                    'musora_app'           AS PLATFORM,
                    LOWER(CHANNEL_NAME)    AS BRAND,
                    COMMENT_TIMESTAMP, AUTHOR_NAME,
                    DETECTED_LANGUAGE, SENTIMENT_POLARITY, INTENT,
                    REQUIRES_REPLY, SENTIMENT_CONFIDENCE, IS_ENGLISH,
                    PERMALINK_URL
                FROM SOCIAL_MEDIA_DB.ML_FEATURES.MUSORA_COMMENT_SENTIMENT_FEATURES
                WHERE CONTENT_SK IN ({content_sks_str})
                  AND LOWER(CHANNEL_NAME) = '{safe_brand}'
                  {musora_date_clause}
            """)

        combined = " UNION ALL ".join(parts)

        return f"""
        WITH combined AS ({combined})
        SELECT *
        FROM combined
        QUALIFY ROW_NUMBER() OVER (
            PARTITION BY CONTENT_SK,
                CASE
                    WHEN SENTIMENT_POLARITY IN ('negative', 'very_negative') THEN 'neg'
                    WHEN SENTIMENT_POLARITY IN ('positive', 'very_positive') THEN 'pos'
                    ELSE 'other'
                END
            ORDER BY
                CASE SENTIMENT_POLARITY
                    WHEN 'very_negative' THEN 1 WHEN 'negative'      THEN 2
                    WHEN 'very_positive' THEN 1 WHEN 'positive'      THEN 2
                    ELSE 3
                END,
                RANDOM()
        ) <= 50
        """

    def _process_sa_comments(self, df):
        """Process sampled comments dataframe for the SA page."""
        if 'comment_timestamp' in df.columns:
            df['comment_timestamp'] = pd.to_datetime(df['comment_timestamp'], errors='coerce')

        df['sentiment_polarity'] = df['sentiment_polarity'].fillna('unknown')
        df['intent'] = df['intent'].fillna('unknown')
        df['platform'] = df['platform'].fillna('unknown').str.lower()

        if 'requires_reply' in df.columns:
            df['requires_reply'] = df['requires_reply'].astype(bool)

        # display_text already computed in SQL; create short version vectorized
        if 'display_text' in df.columns:
            text = df['display_text'].astype(str)
            df['display_text_short'] = text.where(
                text.str.len() <= 100, text.str[:100] + '...'
            )

        return df

    # ─────────────────────────────────────────────────────────────
    # Reply Required page data (on-demand, 24-hour cache)
    # ─────────────────────────────────────────────────────────────

    def load_reply_required_data(self, platforms=None, brands=None, date_range=None):
        """
        Load comments requiring reply, filtered by platform/brand/date.

        Args:
            platforms: List of platform strings (or None for all)
            brands: List of brand strings (or None for all)
            date_range: Tuple (start_date, end_date) or None

        Returns:
            pd.DataFrame
        """
        platforms_key = tuple(sorted(platforms)) if platforms else ()
        brands_key = tuple(sorted(brands)) if brands else ()
        date_key = (str(date_range[0]), str(date_range[1])) if date_range and len(date_range) == 2 else ()

        return self._fetch_rr_data(platforms_key, brands_key, date_key)

    @st.cache_data(ttl=86400)
    def _fetch_rr_data(_self, platforms, brands, date_range):
        """Cached Reply Required data fetch."""
        try:
            query = _self._build_rr_query(platforms, brands, date_range)
            if not query:
                return pd.DataFrame()

            conn = SnowFlakeConn()
            df = conn.run_read_query(query, "reply required comments")
            conn.close_connection()

            if df is None or df.empty:
                return pd.DataFrame()

            # columns already lowercased by run_read_query
            if 'comment_timestamp' in df.columns:
                df['comment_timestamp'] = pd.to_datetime(df['comment_timestamp'], errors='coerce')

            df['sentiment_polarity'] = df['sentiment_polarity'].fillna('unknown')
            df['intent'] = df['intent'].fillna('unknown')
            df['platform'] = df['platform'].fillna('unknown').str.lower()
            df['brand'] = df['brand'].fillna('unknown').str.lower()

            if 'requires_reply' in df.columns:
                df['requires_reply'] = df['requires_reply'].astype(bool)

            if 'comment_timestamp' in df.columns:
                df = df.sort_values('comment_timestamp', ascending=False)

            # display_text already computed in SQL; create short version
            if 'display_text' in df.columns:
                text = df['display_text'].astype(str)
                df['display_text_short'] = text.where(
                    text.str.len() <= 100, text.str[:100] + '...'
                )

            return df

        except Exception as e:
            st.error(f"Error loading reply required data: {e}")
            return pd.DataFrame()

    def _build_rr_query(self, platforms, brands, date_range):
        """Build dynamic SQL for the Reply Required page."""
        # Build separate qualified clauses for the social media table (has a JOIN so needs s. prefix)
        # and unqualified clauses for the musora table (no JOIN, no ambiguity).

        # Date filters
        social_date_clause = ""
        musora_date_clause = ""
        if date_range and len(date_range) == 2:
            social_date_clause = (
                f"AND s.COMMENT_TIMESTAMP >= '{date_range[0]}'"
                f" AND s.COMMENT_TIMESTAMP <= '{date_range[1]}'"
            )
            musora_date_clause = (
                f"AND COMMENT_TIMESTAMP >= '{date_range[0]}'"
                f" AND COMMENT_TIMESTAMP <= '{date_range[1]}'"
            )

        # Brand filters
        social_brand_clause = ""
        musora_brand_clause = ""
        if brands:
            brands_str = "', '".join(self._sanitize_value(b.lower()) for b in brands)
            social_brand_clause = f"AND LOWER(s.CHANNEL_NAME) IN ('{brands_str}')"
            musora_brand_clause = f"AND LOWER(CHANNEL_NAME) IN ('{brands_str}')"

        # Determine which source tables to include
        include_social = True
        include_musora = True
        social_platform_clause = ""

        if platforms:
            non_musora = [p for p in platforms if p != 'musora_app']
            include_musora = 'musora_app' in platforms
            include_social = len(non_musora) > 0
            if non_musora:
                plat_str = "', '".join(self._sanitize_value(p.lower()) for p in non_musora)
                social_platform_clause = f"AND LOWER(s.PLATFORM) IN ('{plat_str}')"

        if not include_social and not include_musora:
            return None

        parts = []

        if include_social:
            parts.append(f"""
                SELECT
                    s.COMMENT_SK, s.COMMENT_ID, s.CONTENT_SK, s.CONTENT_DESCRIPTION,
                    CASE WHEN s.IS_ENGLISH = FALSE AND s.TRANSLATED_TEXT IS NOT NULL
                         THEN s.TRANSLATED_TEXT ELSE s.ORIGINAL_TEXT END AS DISPLAY_TEXT,
                    s.ORIGINAL_TEXT,
                    LOWER(s.PLATFORM)     AS PLATFORM,
                    LOWER(s.CHANNEL_NAME) AS BRAND,
                    s.COMMENT_TIMESTAMP, s.AUTHOR_NAME,
                    s.DETECTED_LANGUAGE, s.SENTIMENT_POLARITY, s.INTENT,
                    s.REQUIRES_REPLY, s.SENTIMENT_CONFIDENCE, s.IS_ENGLISH,
                    c.PERMALINK_URL
                FROM SOCIAL_MEDIA_DB.ML_FEATURES.COMMENT_SENTIMENT_FEATURES s
                LEFT JOIN SOCIAL_MEDIA_DB.CORE.DIM_CONTENT c ON s.CONTENT_SK = c.CONTENT_SK
                WHERE s.REQUIRES_REPLY = TRUE
                  {social_platform_clause}
                  {social_brand_clause}
                  {social_date_clause}
            """)

        if include_musora:
            parts.append(f"""
                SELECT
                    COMMENT_SK, COMMENT_ID, CONTENT_SK, CONTENT_DESCRIPTION,
                    CASE WHEN IS_ENGLISH = FALSE AND TRANSLATED_TEXT IS NOT NULL
                         THEN TRANSLATED_TEXT ELSE ORIGINAL_TEXT END AS DISPLAY_TEXT,
                    ORIGINAL_TEXT,
                    'musora_app'          AS PLATFORM,
                    LOWER(CHANNEL_NAME)   AS BRAND,
                    COMMENT_TIMESTAMP, AUTHOR_NAME,
                    DETECTED_LANGUAGE, SENTIMENT_POLARITY, INTENT,
                    REQUIRES_REPLY, SENTIMENT_CONFIDENCE, IS_ENGLISH,
                    PERMALINK_URL
                FROM SOCIAL_MEDIA_DB.ML_FEATURES.MUSORA_COMMENT_SENTIMENT_FEATURES
                WHERE REQUIRES_REPLY = TRUE
                  {musora_brand_clause}
                  {musora_date_clause}
            """)

        combined = " UNION ALL ".join(parts)
        return f"""
        WITH combined AS ({combined})
        SELECT * FROM combined
        ORDER BY COMMENT_TIMESTAMP DESC
        """

    # ─────────────────────────────────────────────────────────────
    # Demographics (24-hour cache)
    # ─────────────────────────────────────────────────────────────

    @st.cache_data(ttl=86400)
    def load_demographics_data(_self):
        """Load user demographic data from Snowflake."""
        if not _self.demographics_query:
            return pd.DataFrame()

        try:
            conn = SnowFlakeConn()
            query_with_cast = _self.demographics_query.replace(
                "u.birthday as BIRTHDAY",
                "TO_VARCHAR(u.birthday, 'YYYY-MM-DD HH24:MI:SS.FF6 TZHTZM') as BIRTHDAY"
            )
            df = conn.run_read_query(query_with_cast, "user demographics")
            conn.close_connection()

            if df is None or df.empty:
                return pd.DataFrame()

            return _self._process_demographics_dataframe(df)

        except Exception as e:
            st.warning(f"Could not load demographic data: {str(e)}")
            return pd.DataFrame()

    def _process_demographics_dataframe(self, df):
        """Process and enrich demographic dataframe."""
        df.columns = df.columns.str.lower()

        if 'birthday' in df.columns:
            df['birthday'] = df['birthday'].astype(str)
            df['birthday'] = pd.to_datetime(df['birthday'], errors='coerce', utc=True)
            df['birthday'] = df['birthday'].dt.tz_localize(None)
            df['age'] = df['birthday'].apply(self._calculate_age)
            df['age_group'] = df['age'].apply(self._categorize_age)

        if 'timezone' in df.columns:
            df['timezone_region'] = df['timezone'].apply(self._extract_timezone_region)

        if 'experience_level' in df.columns:
            df['experience_group'] = df['experience_level'].apply(self._categorize_experience)

        if 'user_id' in df.columns:
            df = df[df['user_id'].notna()]

        return df

    @staticmethod
    def _calculate_age(birthday):
        if pd.isna(birthday):
            return None
        try:
            age = relativedelta(datetime.now(), birthday).years
            return age if 0 <= age <= 120 else None
        except Exception:
            return None

    def _categorize_age(self, age):
        if pd.isna(age) or age is None:
            return 'Unknown'
        for group_name, (min_age, max_age) in self.config.get('demographics', {}).get('age_groups', {}).items():
            if min_age <= age <= max_age:
                return group_name
        return 'Unknown'

    @staticmethod
    def _extract_timezone_region(timezone):
        if pd.isna(timezone) or not isinstance(timezone, str):
            return 'Unknown'
        parts = timezone.split('/')
        return parts[0] if parts else 'Unknown'

    def _categorize_experience(self, experience_level):
        if pd.isna(experience_level):
            return 'Unknown'
        try:
            exp_level = float(experience_level)
        except Exception:
            return 'Unknown'
        for group_name, (min_exp, max_exp) in self.config.get('demographics', {}).get('experience_groups', {}).items():
            if min_exp <= exp_level <= max_exp:
                return group_name
        return 'Unknown'

    def merge_demographics_with_comments(self, comments_df, demographics_df):
        """Merge demographic data with comment data for musora_app platform only."""
        if demographics_df.empty:
            for col, val in [('age', None), ('age_group', 'Unknown'),
                              ('timezone', None), ('timezone_region', 'Unknown'),
                              ('experience_level', None), ('experience_group', 'Unknown')]:
                comments_df[col] = val
            return comments_df

        if 'author_id' in comments_df.columns and 'user_id' in demographics_df.columns:
            comments_df = comments_df.copy()
            comments_df['author_id_str'] = comments_df['author_id'].astype(str)
            demographics_df['user_id_str'] = demographics_df['user_id'].astype(str)

            merged_df = comments_df.merge(
                demographics_df[['user_id_str', 'age', 'age_group', 'timezone',
                                  'timezone_region', 'experience_level', 'experience_group']],
                left_on='author_id_str',
                right_on='user_id_str',
                how='left'
            )
            merged_df.drop(columns=['author_id_str', 'user_id_str'], errors='ignore', inplace=True)

            for col in ['age_group', 'timezone_region', 'experience_group']:
                if col in merged_df.columns:
                    merged_df[col] = merged_df[col].fillna('Unknown')

            return merged_df

        return comments_df

    # ─────────────────────────────────────────────────────────────
    # Filter helpers
    # ─────────────────────────────────────────────────────────────

    @staticmethod
    def get_filter_options(df):
        """Get unique values for sidebar filters."""
        return {
            'platforms':  sorted(df['platform'].unique().tolist()),
            'brands':     sorted(df['brand'].unique().tolist()),
            'sentiments': sorted(df['sentiment_polarity'].unique().tolist()),
            'languages':  sorted(df['detected_language'].dropna().unique().tolist())
                          if 'detected_language' in df.columns else []
        }

    @staticmethod
    def apply_filters(df, platforms=None, brands=None, sentiments=None,
                      date_range=None, languages=None):
        """Apply sidebar filters to a dataframe (no copy — boolean indexing only)."""
        filtered_df = df

        if platforms:
            filtered_df = filtered_df[filtered_df['platform'].isin(platforms)]

        if brands:
            filtered_df = filtered_df[filtered_df['brand'].isin(brands)]

        if sentiments:
            filtered_df = filtered_df[filtered_df['sentiment_polarity'].isin(sentiments)]

        if languages:
            filtered_df = filtered_df[filtered_df['detected_language'].isin(languages)]

        if date_range and len(date_range) == 2 and 'comment_timestamp' in filtered_df.columns:
            start_date, end_date = date_range
            filtered_df = filtered_df[
                (filtered_df['comment_timestamp'] >= pd.Timestamp(start_date)) &
                (filtered_df['comment_timestamp'] <= pd.Timestamp(end_date))
            ]

        return filtered_df

    @staticmethod
    def get_date_range(df, default_days=30):
        """Get default date range from dataframe."""
        if 'comment_timestamp' in df.columns and not df.empty:
            max_date = df['comment_timestamp'].max()
            min_date = max_date - timedelta(days=default_days)
            return (min_date, max_date)
        return (datetime.now() - timedelta(days=default_days), datetime.now())

    # ─────────────────────────────────────────────────────────────
    # Internal helpers
    # ─────────────────────────────────────────────────────────────

    @staticmethod
    def _sanitize_value(value):
        """Remove characters that could break SQL string literals."""
        return re.sub(r"['\";\\]", '', str(value))

    @staticmethod
    def _build_date_clause(date_range, table_alias=None):
        """
        Build a SQL AND COMMENT_TIMESTAMP ... clause, or empty string.

        Args:
            date_range: tuple of (start, end) or None
            table_alias: optional table alias prefix (e.g. 's') to avoid
                         ambiguous column errors when a JOIN is present
        """
        if date_range and len(date_range) == 2:
            col = f"{table_alias}.COMMENT_TIMESTAMP" if table_alias else "COMMENT_TIMESTAMP"
            return f"AND {col} >= '{date_range[0]}' AND {col} <= '{date_range[1]}'"
        return ""