"""Debug why query returns 0 posts when posts exist"""

from src.database.db import get_session
from src.database.models import Post, Source, PostMetrics
from src.utils.time_window import get_time_window
from sqlalchemy.orm import joinedload
from sqlalchemy import func, and_


def debug():
    session = get_session()
    
    try:
        start_date, end_date = get_time_window('yesterday')
        
        print("=" * 60)
        print("DEBUG: Why query returns 0 posts")
        print("=" * 60)
        print(f"Date filter: {start_date} to {end_date}")
        print()
        
        # Step 1: Check total posts
        total_posts = session.query(Post).filter(
            and_(Post.created_at >= start_date, Post.created_at < end_date)
        ).count()
        print(f"1. Total posts in date range: {total_posts}")
        
        # Step 2: Check posts with Source join
        posts_with_source = session.query(Post).join(Source).filter(
            and_(Post.created_at >= start_date, Post.created_at < end_date)
        ).count()
        print(f"2. Posts with Source join: {posts_with_source}")
        
        # Step 3: Check Instagram posts
        instagram_posts = session.query(Post).join(Source).filter(
            and_(Post.created_at >= start_date, Post.created_at < end_date),
            Source.platform == 'instagram'
        ).count()
        print(f"3. Instagram posts: {instagram_posts}")
        
        # Step 4: Check with outerjoin PostMetrics
        posts_with_metrics = session.query(Post).outerjoin(
            PostMetrics, Post.id == PostMetrics.post_id
        ).join(Source).filter(
            and_(Post.created_at >= start_date, Post.created_at < end_date),
            Source.platform == 'instagram'
        ).count()
        print(f"4. Instagram posts with outerjoin PostMetrics: {posts_with_metrics}")
        
        # Step 5: Full query like in handlers.py
        all_instagram_posts = session.query(Post).outerjoin(
            PostMetrics, Post.id == PostMetrics.post_id
        ).join(
            Source, Post.source_id == Source.id
        ).options(
            joinedload(Post.source),
            joinedload(Post.metrics)
        ).filter(
            and_(Post.created_at >= start_date, Post.created_at < end_date),
            Source.platform == 'instagram'
        ).limit(50).all()
        
        print(f"5. Full query result: {len(all_instagram_posts)} posts")
        
        # Show sample posts
        if all_instagram_posts:
            print("\nSample posts:")
            for p in all_instagram_posts[:3]:
                print(f"  - {p.source.username}: {p.external_id[:40]}")
        else:
            print("\nNO POSTS FOUND!")
            print("\nDebugging:")
            
            # Check what platforms exist
            platforms = session.query(Source.platform).distinct().all()
            print(f"Platforms in DB: {[p[0] for p in platforms]}")
            
            # Check posts without platform filter
            sample_posts = session.query(Post).join(Source).filter(
                and_(Post.created_at >= start_date, Post.created_at < end_date)
            ).limit(5).all()
            
            print(f"\nSample posts (all platforms):")
            for p in sample_posts:
                print(f"  - {p.external_id[:40]}: platform={p.source.platform}, username={p.source.username}")
        
    finally:
        session.close()


if __name__ == "__main__":
    debug()