"""Station module"""
import time
from db import get_service_client
import engine_user
from engineHelper import parse_db_timestamp, upload_media_to_cloudinary

def _is_member(station_name, username):
    sb = get_service_client()
    res = sb.table("station_members").select("username").eq("station_name", station_name).eq("username", username).execute()
    return len(res.data) > 0

def create_station(station_name, admin_username, bio=""):
    sb = get_service_client()
    existing = sb.table("stations").select("station_name").eq("station_name", station_name).execute()
    if existing.data: return False
    # Create station
    sb.table("stations").insert({
        "station_name": station_name, "admin": admin_username,
        "user_count": 0, "bio": bio, "hero_banner": "", "profile_pic": ""
    }).execute()
    # Add admin to members
    sb.table("station_members").insert({"station_name": station_name, "username": admin_username}).execute()
    return True

def delete_station(station_name, username):
    sb = get_service_client()
    rows = sb.table("stations").select("admin").eq("station_name", station_name).eq("admin", username).execute()
    if not rows.data: return False
    
    # Manually handle cascading deletes for posts, members, and requests
    sb.table("posts").delete().eq("station_name", station_name).execute()
    sb.table("station_members").delete().eq("station_name", station_name).execute()
    sb.table("station_requests").delete().eq("station_name", station_name).execute()

    # Delete the station itself
    sb.table("stations").delete().eq("station_name", station_name).execute()
    return True

def force_delete_station(station_name):
    sb = get_service_client()
    # Manually handle cascading deletes for posts, members, and requests
    sb.table("posts").delete().eq("station_name", station_name).execute()
    sb.table("station_members").delete().eq("station_name", station_name).execute()
    sb.table("station_requests").delete().eq("station_name", station_name).execute()
    
    # Delete the station itself
    sb.table("stations").delete().eq("station_name", station_name).execute()
    return True

def update_station_name(old_name, new_name):
    sb = get_service_client()
    existing = sb.table("stations").select("station_name").eq("station_name", new_name).execute()
    if existing.data: return False
    sb.table("stations").update({"station_name": new_name}).eq("station_name", old_name).execute()
    sb.table("posts").update({"station_name": new_name}).eq("station_name", old_name).execute()
    sb.table("station_requests").update({"station_name": new_name}).eq("station_name", old_name).execute()
    return True

def add_user_to_station(station_name, admin_username, user_to_add):
    # Check if admin and user have blocked each other
    if engine_user.is_blocked_either_way(admin_username, user_to_add):
        return False

    sb = get_service_client()
    rows = sb.table("stations").select("admin").eq("station_name", station_name).execute()
    if not rows.data or rows.data[0]["admin"] != admin_username: return False
    
    if _is_member(station_name, user_to_add): return True  # already a member, treat as success
    
    try:
        sb.table("station_members").insert({"station_name": station_name, "username": user_to_add}).execute()
    except Exception as e:
        if "23505" in str(e):  # duplicate key
            return True
        raise
    return True

def remove_user_from_station(station_name, admin_username, user_to_remove):
    sb = get_service_client()
    rows = sb.table("stations").select("admin").eq("station_name", station_name).execute()
    if not rows.data: return False
    admin = rows.data[0]["admin"]
    if admin != admin_username and user_to_remove != admin: return False
    
    sb.table("station_members").delete().eq("station_name", station_name).eq("username", user_to_remove).execute()
    return True

def request_to_join(station_name, username):
    sb = get_service_client()
    rows = sb.table("stations").select("admin").eq("station_name", station_name).execute()
    if not rows.data: return False
    admin = rows.data[0]["admin"]
    
    # Block check
    if engine_user.is_blocked_either_way(admin, username):
        return False

    if admin == username or _is_member(station_name, username): return False
    
    pending = sb.table("station_requests").select("id").eq("station_name", station_name).eq("username", username).eq("status", "pending").execute()
    if pending.data: return False
    
    sb.table("station_requests").insert({"station_name": station_name, "username": username, "status": "pending"}).execute()
    return True

def check_pending_request(station_name, username):
    sb = get_service_client()
    rows = sb.table("station_requests").select("id").eq("station_name", station_name).eq("username", username).eq("status", "pending").execute()
    return len(rows.data) > 0

def approve_request(station_name, admin_username, user_to_approve):
    sb = get_service_client()
    station_rows = sb.table("stations").select("admin").eq("station_name", station_name).eq("admin", admin_username).execute()
    if not station_rows.data: return False
    
    sb.table("station_requests").update({"status": "approved"}).eq("station_name", station_name).eq("username", user_to_approve).execute()
    # Add to members
    add_user_to_station(station_name, admin_username, user_to_approve)
    return True

def reject_request(station_name, admin_username, user_to_reject):
    sb = get_service_client()
    station_rows = sb.table("stations").select("admin").eq("station_name", station_name).eq("admin", admin_username).execute()
    if not station_rows.data: return False
    sb.table("station_requests").update({"status": "rejected"}).eq("station_name", station_name).eq("username", user_to_reject).execute()
    return True

def get_pending_requests(station_name, admin_username):
    sb = get_service_client()
    station_rows = sb.table("stations").select("admin").eq("station_name", station_name).eq("admin", admin_username).execute()
    if not station_rows.data: return []
    rows = sb.table("station_requests").select("id, station_name, username, timestamp").eq("station_name", station_name).eq("status", "pending").execute()
    
    usernames = [r["username"] for r in rows.data]
    avatar_map = {}
    if usernames:
        try:
            profiles = sb.table("users").select("username, avatar").in_("username", usernames).execute()
            for p in profiles.data:
                avatar_map[p["username"]] = p["avatar"]
        except Exception:
            pass
            
    return [{"id": str(r["id"]), "station_name": r["station_name"], "username": r["username"], "avatar": avatar_map.get(r["username"]), "timestamp": str(parse_db_timestamp(r["timestamp"]))} for r in rows.data]

def create_station_post(station_name, username, content, media_path):
    sb = get_service_client()
    rows = sb.table("stations").select("admin").eq("station_name", station_name).execute()
    if not rows.data: return {}
    admin = rows.data[0]["admin"]
    
    # Block check
    if engine_user.is_blocked_either_way(admin, username):
        return {}

    if admin != username and not _is_member(station_name, username): return {}
    
    if media_path and media_path.startswith("data:"):
        media_path = upload_media_to_cloudinary(media_path)
    result = sb.table("posts").insert({
        "username": username, "station_name": station_name, "content": content,
        "media_path": media_path, "comments_count": 0, "likes_count": 0
    }).execute()
    row = result.data[0]

    # Fetch station profile pic for branding
    station_avatar = ""
    try:
        s_rows = sb.table("stations").select("profile_pic").eq("station_name", station_name).execute()
        if s_rows.data:
            station_avatar = s_rows.data[0].get("profile_pic") or ""
    except Exception:
        pass

    return {
        "id": str(row["id"]), "station_name": station_name, "username": username,
        "content": content, "media_path": media_path, "timestamp": str(row["timestamp"]),
        "comments_count": "0", "likes_count": "0",
        "avatar": station_avatar
    }

def delete_station_post(post_id, station_name, username):
    sb = get_service_client()
    station_rows = sb.table("stations").select("admin").eq("station_name", station_name).execute()
    if not station_rows.data: return False
    admin = station_rows.data[0]["admin"]
    is_admin = admin == username
    post_rows = sb.table("posts").select("username").eq("id", post_id).eq("station_name", station_name).execute()
    if not post_rows.data: return False
    is_author = post_rows.data[0]["username"] == username
    is_member = is_admin or _is_member(station_name, username)
    if not is_admin and not is_author: return False
    if not is_member and not is_author: return False
    sb.table("posts").delete().eq("id", post_id).eq("station_name", station_name).execute()
    return True

def get_station_posts(station_name, viewer=""):
    sb = get_service_client()
    
    # Get station admin to check blocks
    rows_admin = sb.table("stations").select("admin, profile_pic").eq("station_name", station_name).execute()
    if not rows_admin.data: return []
    admin = rows_admin.data[0]["admin"]
    station_avatar = rows_admin.data[0].get("profile_pic") or ""
    
    if viewer and engine_user.is_blocked_either_way(admin, viewer):
        return []

    rows = sb.table("posts").select("id, station_name, username, content, media_path, timestamp, comments_count, likes_count").eq("station_name", station_name).order("timestamp", desc=True).execute()
    
    restricted_users = {u.lower() for u in engine_user.get_blocking_relationship_usernames(viewer)} if viewer else set()

    # Pre-fetch likes if viewer is provided
    liked_post_ids = []
    if viewer:
        try:
            likes_res = sb.table("likes").select("post_id").eq("username", viewer).eq("isLike", 1).execute()
            liked_post_ids = [str(l["post_id"]) for l in likes_res.data]
        except Exception:
            liked_post_ids = []

    results = []
    for r in rows.data:
        # Filter posts from blocked users within the station
        if r["username"].lower() in restricted_users:
            continue
            
        p = {k: str(v) for k, v in r.items()}
        p["avatar"] = station_avatar
        p["is_liked"] = str(r["id"]) in liked_post_ids
        results.append(p)
    return results

def get_station(station_name, viewer=""):
    sb = get_service_client()
    rows = sb.table("stations").select("station_name, admin, user_count, bio, hero_banner, profile_pic").eq("station_name", station_name).execute()
    if not rows.data: return []
    
    admin = rows.data[0]["admin"]
    if viewer and engine_user.is_blocked_either_way(admin, viewer):
        return []

    station = {k: str(v) if v is not None else "" for k, v in rows.data[0].items()}
    
    # Fetch members with avatars
    members_res = sb.table("station_members").select("username").eq("station_name", station_name).execute()
    
    restricted_users = {u.lower() for u in engine_user.get_blocking_relationship_usernames(viewer)} if viewer else set()
    
    usernames = [r["username"] for r in members_res.data if r["username"].lower() not in restricted_users]
    
    avatar_map = {}
    if usernames:
        try:
            profiles_res = sb.table("users").select("username, avatar").in_("username", usernames).execute()
            for prof in profiles_res.data:
                avatar_map[prof["username"]] = prof["avatar"]
        except Exception:
            pass
            
    station["users"] = ",".join(usernames)
    station["members_info"] = [{"username": u, "avatar": avatar_map.get(u)} for u in usernames]
    return [station]

def get_station_members(station_name, viewer=""):
    sb = get_service_client()
    rows = sb.table("station_members").select("username").eq("station_name", station_name).execute()
    
    restricted_users = {u.lower() for u in engine_user.get_blocking_relationship_usernames(viewer)} if viewer else set()
    
    return [r["username"] for r in rows.data if r["username"].lower() not in restricted_users]

def get_all_stations(viewer=""):
    sb = get_service_client()
    rows = sb.table("stations").select("station_name, admin, user_count, bio, hero_banner, profile_pic").execute()
    if not rows.data: return []
    
    restricted_users = {u.lower() for u in engine_user.get_blocking_relationship_usernames(viewer)} if viewer else set()
    
    # Filter stations where admin is blocked
    filtered_rows = [r for r in rows.data if r["admin"].lower() not in restricted_users]
    
    # Fetch all members in one query
    station_names = [r["station_name"] for r in filtered_rows]
    if not station_names: return []
    
    all_members = sb.table("station_members").select("station_name, username").in_("station_name", station_names).execute()
    
    members_map = {}
    for m in all_members.data:
        if m["username"].lower() not in restricted_users:
            members_map.setdefault(m["station_name"], []).append(m["username"])
            
    result = []
    for r in filtered_rows:
        s = {k: str(v) if v is not None else "" for k, v in r.items()}
        s["users"] = ",".join(members_map.get(r["station_name"], []))
        result.append(s)
    return result

def get_station_bio(station_name):
    sb = get_service_client()
    rows = sb.table("stations").select("bio").eq("station_name", station_name).execute()
    return rows.data[0]["bio"] if rows.data else ""

def get_station_hero_banner(station_name):
    sb = get_service_client()
    rows = sb.table("stations").select("hero_banner").eq("station_name", station_name).execute()
    return rows.data[0]["hero_banner"] if rows.data else ""

def get_station_profile_pic(station_name):
    sb = get_service_client()
    rows = sb.table("stations").select("profile_pic").eq("station_name", station_name).execute()
    return rows.data[0]["profile_pic"] if rows.data else ""

def get_station_admin(station_name):
    sb = get_service_client()
    rows = sb.table("stations").select("admin").eq("station_name", station_name).execute()
    return rows.data[0]["admin"] if rows.data else ""

def update_station_bio(station_name, bio):
    sb = get_service_client()
    sb.table("stations").update({"bio": bio}).eq("station_name", station_name).execute()
    return True

def update_station_hero_banner(station_name, hero_banner_base64):
    sb = get_service_client()
    if hero_banner_base64 and hero_banner_base64.startswith("data:"):
        hero_banner_base64 = upload_media_to_cloudinary(hero_banner_base64)
    sb.table("stations").update({"hero_banner": hero_banner_base64}).eq("station_name", station_name).execute()
    return True

def update_station_profile_pic(station_name, profile_pic_base64):
    sb = get_service_client()
    if profile_pic_base64 and profile_pic_base64.startswith("data:"):
        profile_pic_base64 = upload_media_to_cloudinary(profile_pic_base64)
    sb.table("stations").update({"profile_pic": profile_pic_base64}).eq("station_name", station_name).execute()
    return True

def get_bulk_station_posts(station_names, viewer=""):
    if not station_names: return []
    sb = get_service_client()
    
    restricted_users = {u.lower() for u in engine_user.get_blocking_relationship_usernames(viewer)} if viewer else set()
    
    # Filter station names where admin is blocked
    stations_info = sb.table("stations").select("station_name, admin, profile_pic").in_("station_name", station_names).execute()
    
    allowed_stations = []
    station_avatar_map = {}
    for si in stations_info.data:
        if si["admin"].lower() not in restricted_users:
            allowed_stations.append(si["station_name"])
            station_avatar_map[si["station_name"]] = si["profile_pic"] or ""
            
    if not allowed_stations: return []

    rows = sb.table("posts").select("id, station_name, username, content, media_path, timestamp, comments_count, likes_count").in_("station_name", allowed_stations).order("timestamp", desc=True).execute()
    
    liked_post_ids = []
    if viewer:
        try:
            likes_res = sb.table("likes").select("post_id").eq("username", viewer).eq("isLike", 1).execute()
            liked_post_ids = [str(l["post_id"]) for l in likes_res.data]
        except Exception:
            pass

    results = []
    for r in rows.data:
        if r["username"].lower() in restricted_users:
            continue
        p = {k: str(v) for k, v in r.items()}
        p["avatar"] = station_avatar_map.get(r["station_name"], "")
        p["is_liked"] = str(r["id"]) in liked_post_ids
        results.append(p)
    return results

def get_bulk_pending_requests(station_names, username):
    if not station_names: return {}
    sb = get_service_client()
    res = sb.table("station_requests").select("station_name").in_("station_name", station_names).eq("username", username).eq("status", "pending").execute()
    
    pending_map = {name: False for name in station_names}
    for r in res.data:
        pending_map[r["station_name"]] = True
    return pending_map

def cleanup_station_memberships_on_block(user1, user2):
    """Remove cross-memberships when a block occurs"""
    sb = get_service_client()
    
    # 1. Handle stations where user1 is admin (remove user2)
    stations1 = sb.table("stations").select("station_name").eq("admin", user1).execute()
    s_names1 = [s["station_name"] for s in stations1.data]
    if s_names1:
        sb.table("station_members").delete().in_("station_name", s_names1).eq("username", user2).execute()
        sb.table("station_requests").update({"status": "rejected"}).in_("station_name", s_names1).eq("username", user2).eq("status", "pending").execute()
        # Delete blocked user's posts from these stations
        sb.table("posts").delete().in_("station_name", s_names1).eq("username", user2).execute()

    # 2. Handle stations where user2 is admin (remove user1)
    stations2 = sb.table("stations").select("station_name").eq("admin", user2).execute()
    s_names2 = [s["station_name"] for s in stations2.data]
    if s_names2:
        sb.table("station_members").delete().in_("station_name", s_names2).eq("username", user1).execute()
        sb.table("station_requests").update({"status": "rejected"}).in_("station_name", s_names2).eq("username", user1).eq("status", "pending").execute()
        # Delete blocker's posts from these stations
        sb.table("posts").delete().in_("station_name", s_names2).eq("username", user1).execute()