from app.db import get_db_connection
from app.schemas import LikeResponse

class LikeService:
    """Handle likes and matches"""
    
    @staticmethod
    def like_user(liker_id: int, liked_id: int) -> LikeResponse:
        """User A likes User B"""
        if liker_id == liked_id:
            raise ValueError("Cannot like yourself")
        
        is_match = False
        with get_db_connection() as conn:
            cur = conn.cursor()
            
            # Check if already liked
            cur.execute("SELECT id FROM likes WHERE liker_id = %s AND liked_id = %s", (liker_id, liked_id))
            if cur.fetchone():
                raise ValueError("Already liked this user")
            
            # Insert like
            cur.execute(
                "INSERT INTO likes (liker_id, liked_id) VALUES (%s, %s) RETURNING id",
                (liker_id, liked_id)
            )
            like_id = cur.fetchone()[0]
            conn.commit()
            
            # Check for mutual like (match)
            cur.execute(
                "SELECT id FROM likes WHERE liker_id = %s AND liked_id = %s",
                (liked_id, liker_id)
            )
            if cur.fetchone():
                is_match = True
                # Create conversation if not exists
                cur.execute(
                    """INSERT INTO conversations (user_id_1, user_id_2) 
                       VALUES (%s, %s) ON CONFLICT DO NOTHING""",
                    (min(liker_id, liked_id), max(liker_id, liked_id))
                )
                conn.commit()
        
        return LikeResponse(id=like_id, liker_id=liker_id, liked_id=liked_id, is_match=is_match)
    
    @staticmethod
    def unlike_user(liker_id: int, liked_id: int) -> dict:
        """User A unlikes User B"""
        with get_db_connection() as conn:
            cur = conn.cursor()
            
            # Delete the like
            cur.execute(
                "DELETE FROM likes WHERE liker_id = %s AND liked_id = %s",
                (liker_id, liked_id)
            )
            conn.commit()
            
            return {"message": "Like removed"}
    
    @staticmethod
    def get_matches(user_id: int) -> list:
        """Get all users that match with this user"""
        with get_db_connection() as conn:
            cur = conn.cursor()
            cur.execute(
                """SELECT DISTINCT CASE 
                   WHEN l1.liker_id = %s THEN l1.liked_id 
                   ELSE l1.liker_id 
                   END as match_user_id
                   FROM likes l1
                   JOIN likes l2 ON (
                       (l1.liker_id = l2.liked_id AND l1.liked_id = l2.liker_id)
                   )
                   WHERE l1.liker_id = %s OR l1.liked_id = %s""",
                (user_id, user_id, user_id)
            )
            
            match_ids = [row[0] for row in cur.fetchall()]
            
            # Fetch profile info for each match
            matches = []
            for match_id in match_ids:
                cur.execute(
                    """SELECT p.id, p.display_name, ph.file_path 
                       FROM profiles p 
                       LEFT JOIN photos ph ON p.id = ph.profile_id AND ph.display_order = 1
                       WHERE p.user_id = %s""",
                    (match_id,)
                )
                row = cur.fetchone()
                if row:
                    matches.append({
                        "user_id": match_id, 
                        "display_name": row[1],
                        "photo": row[2] if row[2] else None
                    })
            
            return matches
    
    @staticmethod
    def get_likes_received(user_id: int) -> list:
        """Get count of likes received from other users (not matched yet and not acknowledged)"""
        with get_db_connection() as conn:
            cur = conn.cursor()
            # Get likes where this user is the liked_id and not yet acknowledged
            cur.execute(
                """SELECT l.liker_id, p.display_name, l.created_at
                   FROM likes l
                   JOIN profiles p ON l.liker_id = p.user_id
                   WHERE l.liked_id = %s
                   AND l.acknowledged_at IS NULL
                   AND NOT EXISTS (
                       SELECT 1 FROM likes l2 
                       WHERE l2.liker_id = %s AND l2.liked_id = l.liker_id
                   )
                   ORDER BY l.created_at DESC""",
                (user_id, user_id)
            )
            
            likes = []
            for row in cur.fetchall():
                likes.append({"user_id": row[0], "display_name": row[1]})
            
            return likes
    
    @staticmethod
    def acknowledge_likes(user_id: int) -> dict:
        """Mark all received likes as acknowledged by this user"""
        with get_db_connection() as conn:
            cur = conn.cursor()
            
            # Update all unacknowledged likes where user is the liked_id
            cur.execute(
                """UPDATE likes SET acknowledged_at = CURRENT_TIMESTAMP
                   WHERE liked_id = %s AND acknowledged_at IS NULL""",
                (user_id,)
            )
            conn.commit()
            
            return {"message": "Likes acknowledged"}
    
    @staticmethod
    def get_liked_profiles(user_id: int) -> list:
        """Get all profiles that this user has liked (sent likes to)"""
        with get_db_connection() as conn:
            cur = conn.cursor()
            cur.execute(
                """SELECT DISTINCT liked_id
                   FROM likes
                   WHERE liker_id = %s""",
                (user_id,)
            )
            
            liked_ids = [row[0] for row in cur.fetchall()]
            return liked_ids