import os
from supabase import create_client, Client
from pandas import json_normalize
import pandas
import io
from PIL import Image

class DatabaseManager:

    def __init__(self,url,key):
        # Supabase connection string format
        # postgresql://postgres:[password]@[host]:[port]/[database]
        self.supabase: Client = create_client(url, key)

    def create_user(self, email, password):
        response = self.supabase.auth.sign_up(
        {
            "email": email,
            "password": password,
        }

)
        if response.user.aud == "authenticated" : # type: ignore
            return True,"Un mail a été envoyé a votre address mail",response

    def fetch_source_table(self,filter) :
        response = (
                self.supabase.table("Source")
                .select("*")
                .eq("user_id", filter)
                .execute()
            )
        return response.data

    def fetch_account_table(self,filter) :
        response = (
                self.supabase.table("Social_network")
                .select("*")
                .eq("id_utilisateur", filter)
                .execute()
            )
        return response.data

    def fetch_schedule_table_acc(self,filter) :
        response = (
                self.supabase
                .table("Scheduling")
                .select("*, Social_network(id_utilisateur, social_network)")
                .execute()
            )
        print(response.data,flush=True)

        df = json_normalize(response.data)
        print(df,flush=True)
        # Renomme les colonnes pour simplifier
        if not df.empty  :
            df.rename(columns={
                "Social_network.id_utilisateur": "user_id",
                "Social_network.social_network": "social_network"
            }, inplace=True)

            # Filtre les lignes pour l'utilisateur donné
            df_user = df[df["user_id"] == filter].reset_index(drop=True)

            return df_user
        return None

    def delete_from_table(self,Source,values) :
        response = (
        self.supabase.table(Source)
        .delete()
        .in_("id", values)
        .execute()
    )

    def authenticate_user(self, email, password):
        try:
            response = self.supabase.auth.sign_in_with_password(
            {
                "email": email,
                "password": password,
            }
            )
            if response.user.aud == "authenticated" and response.user.email_confirmed_at is not None : # type: ignore
                return True,"Logged in  successfully",response
            elif response.user.aud == "authenticated" : # type: ignore
                return False,"Compte non confirmé",response
            else :
                return False,"Compte non existant",response
        except Exception as e:
            # Handle authentication errors
            if "Invalid login credentials" in str(e):
                return False, "Invalid email or password", None
            else:
                return False, f"Authentication error: {str(e)}", None

    def add_token_network(self,token,social_network,account_name,uids,data):
        response = (
        self.supabase.table("Social_network")
        .insert({"social_network": social_network,"account_name" :account_name, "id_utilisateur":uids,"token": token,
                 "sub" : data["sub"],"given_name" : data["given_name"],"family_name" : data["family_name"],"picture" : data["picture"]  })
        .execute()
)
    def add_post(self,id_social,content,ids,img: Image.Image = None,tg : bool =False) :
        if img :
            buffer = io.BytesIO()
            img.save(buffer, format="JPEG")
            img = buffer.getvalue()
        response = (
        self.supabase.table("Post_content")
        .insert({"id_social": id_social,"Text_content" :content ,"is_published" : tg,"sched" : ids,"image_content_url" : img})
        .execute())

    def update_post(self,ids,idd):
        response = (
        self.supabase.table("Post_content")
        .update({"is_published": True})
        .eq("sched", idd)
        .eq("id_social", ids)

        .execute()
    )

    def fetching_post(self,uids,idd,active :bool = False) :
        response = (
        self.supabase.table("Post_content")
        .select("*")
        .eq("id_social", uids)
        .eq("is_published", active)
        .eq("sched", idd)

        .execute()
)
        data = response.data  # liste de dicts, chaque dict contient clé 'Social_network'
        df = json_normalize(data)
        return df

    def fetching_user_identif(self,uids,rs) :
        response = (
        self.supabase.table("Social_network")
        .select("*")
        .eq("id_utilisateur", uids)
        .eq("account_name", rs)

        .execute()
)
        return response

    def get_id_social(self,user_id: str, reseau: str):

        resp = (
            self.supabase
            .table("Social_network")
            .select("id")
            .eq("id_utilisateur", user_id)
            .eq("account_name", reseau)
            .execute()
        )

        return resp.data[0]["id"]

    def create_scheduling_for_user(self,user_id: str, reseau: str, schedule_time: str,adj):

        id_social = self.get_id_social(user_id, reseau)
        resp = (
            self.supabase
            .table("Scheduling")
            .insert({
                "id_social": id_social,
                "schedule_time": schedule_time,
                "adjusted_time": adj,

            })
            .execute()
        )

        print("Scheduling inséré avec succès.")

    def fetch_schedule_table(self) :
        response = (
            self.supabase
                .table("Scheduling")
                .select("*, Social_network(id_utilisateur, account_name)")
                .execute()
        )

        # 2️⃣ On normalise/la platifie la structure JSON en DataFrame
        data = response.data  # liste de dicts, chaque dict contient clé 'Social_network'
        df = json_normalize(data)

        df = df.rename(columns={"Social_network.id_utilisateur": "user_id"})
        df = df.rename(columns={"Social_network.account_name": "social_network"})

        # 4️⃣ On peut réordonner ou filtrer les colonnes si besoin
        # par exemple : id, id_social, user_id, schedule_time, created_at
        cols = ["id", "id_social", "user_id", "schedule_time","social_network","adjusted_time","created_at"]
        df = df[[c for c in cols if c in df.columns]]

        return df