using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using InstaVibe.Models;

namespace InstaVibe.Models
{
    public class DatabaseHelper
    {
        private static readonly string connectionString = ConfigurationManager
    .ConnectionStrings["InstaVibe"].ConnectionString;

        public bool InsertUser(User user)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    con.Open();

                    string checkQuery = "SELECT COUNT(*) FROM users WHERE email = @Email OR username = @Username";
                    using (SqlCommand checkCmd = new SqlCommand(checkQuery, con))
                    {
                        checkCmd.Parameters.AddWithValue("@Email", user.Email);
                        checkCmd.Parameters.AddWithValue("@Username", user.Username);

                        int count = (int)checkCmd.ExecuteScalar();
                        if (count > 0) return false;
                    }

                    string insertQuery = @"INSERT INTO users 
            (username, fullname, email, password, profile_picture, department, 
             registration_no, joining_year, user_type, is_online, birthday, IsApproved) 
            VALUES 
            (@Username, @Fullname, @Email, @Password, @ProfilePicture, @Department, 
             @RegistrationNo, @JoiningYear, @UserType, @IsOnline, @Birthday, @IsApproved)";

                    using (SqlCommand cmd = new SqlCommand(insertQuery, con))
                    {
                        cmd.Parameters.AddWithValue("@Username", user.Username);
                        cmd.Parameters.AddWithValue("@Fullname", user.FirstName + " " + user.LastName);
                        cmd.Parameters.AddWithValue("@Email", user.Email);
                        cmd.Parameters.AddWithValue("@Password", user.Password);
                        cmd.Parameters.AddWithValue("@ProfilePicture", (object)user.ProfilePicture ?? DBNull.Value);
                        cmd.Parameters.AddWithValue("@Department", (object)user.Department ?? DBNull.Value);
                        cmd.Parameters.AddWithValue("@RegistrationNo", (object)user.RegistrationNo ?? DBNull.Value);

                        // 🔥 NEW LINES (IMPORTANT)
                        cmd.Parameters.AddWithValue("@JoiningYear", user.JoiningYear);
                        cmd.Parameters.AddWithValue("@UserType", user.UserType);

                        cmd.Parameters.AddWithValue("@IsOnline", user.IsOnline);
                        cmd.Parameters.AddWithValue("@Birthday", (object)user.Birthday ?? DBNull.Value);
                        cmd.Parameters.AddWithValue("@IsApproved", 0);

                        int rowsAffected = cmd.ExecuteNonQuery();
                        return rowsAffected > 0;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                    return false;
                }
            }
        }


        public List<User> GetAllUsers()
        {
            List<User> userList = new List<User>();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT * FROM users";
                SqlCommand cmd = new SqlCommand(query, con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    User user = new User
                    {
                        UserId = Convert.ToInt32(reader["user_id"]),
                        Username = reader["username"].ToString(),
                        FullName = reader["fullname"].ToString(),
                        Email = reader["email"].ToString(),
                        Password = reader["password"].ToString(),
                        ProfilePicture = reader["profile_picture"] as string,
                        Department = reader["department"] as string,
                        CreatedAt = Convert.ToDateTime(reader["created_at"]),
                        RegistrationNo = reader["registration_no"]?.ToString(),
                        IsOnline = Convert.ToBoolean(reader["is_online"]), // Add this line
                        Birthday = reader["birthday"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["birthday"]) // NEW LINE
                    };
                    userList.Add(user);
                }
                con.Close();
            }
            return userList;
        }

        public User GetUserById(int userId)
        {
            User user = null;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT * FROM users WHERE user_id = @UserId";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    user = new User
                    {
                        UserId = Convert.ToInt32(reader["user_id"]),
                        Username = reader["username"].ToString(),
                        FullName = reader["fullname"].ToString(),
                        Email = reader["email"].ToString(),
                        Password = reader["password"].ToString(),
                        ProfilePicture = reader["profile_picture"] as string,
                        Department = reader["department"] as string,
                        CreatedAt = Convert.ToDateTime(reader["created_at"]),
                        RegistrationNo = reader["registration_no"]?.ToString(),
                        IsOnline = Convert.ToBoolean(reader["is_online"]), // Add this line
                        Birthday = reader["birthday"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["birthday"]) // NEW LINE
                    };
                }
                con.Close();
            }
            return user;
        }

        public User GetUserByEmailOrUsername(string input)
        {
            if (string.IsNullOrEmpty(input))
            {
                throw new ArgumentException("Input cannot be null or empty");
            }

            User user = null;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT * FROM users WHERE email = @input OR username = @input";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@input", input);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    user = new User()
                    {
                        UserId = Convert.ToInt32(reader["user_id"]),
                        Username = reader["username"].ToString(),
                        FullName = reader["fullname"].ToString(),
                        Email = reader["email"].ToString(),
                        Password = reader["password"].ToString(),
                        ProfilePicture = reader["profile_picture"]?.ToString(),
                        Department = reader["department"]?.ToString(),
                        CreatedAt = Convert.ToDateTime(reader["created_at"]),
                        RegistrationNo = reader["registration_no"]?.ToString(),
                        JoiningYear = reader["joining_year"] == DBNull.Value ? 0 : Convert.ToInt32(reader["joining_year"]),
                        UserType = reader["user_type"]?.ToString(),
                        IsOnline = Convert.ToBoolean(reader["is_online"]),
                        Birthday = reader["birthday"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["birthday"]),
                        IsApproved = Convert.ToBoolean(reader["IsApproved"]) // ADD THIS LINE - IMPORTANT!
                    };
                }
                reader.Close();
                con.Close();
            }

            return user;
        }

        public bool UpdateUser(User user)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "UPDATE users SET username = @Username, fullname = @Fullname, email = @Email, " +
               "password = @Password, profile_picture = @ProfilePicture, department = @Department, " +
               "registration_no = @RegistrationNo, is_online = @IsOnline, birthday = @Birthday WHERE user_id = @UserId";

                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", user.UserId);
                cmd.Parameters.AddWithValue("@Username", user.Username);
                cmd.Parameters.AddWithValue("@Fullname", user.FullName);
                cmd.Parameters.AddWithValue("@Email", user.Email);
                cmd.Parameters.AddWithValue("@Password", user.Password);
                cmd.Parameters.AddWithValue("@ProfilePicture", (object)user.ProfilePicture ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@Department", (object)user.Department ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@RegistrationNo", (object)user.RegistrationNo ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@IsOnline", user.IsOnline); // Add this line
                cmd.Parameters.AddWithValue("@Birthday", (object)user.Birthday ?? DBNull.Value);

                try
                {
                    con.Open();
                    int rowsAffected = cmd.ExecuteNonQuery();
                    con.Close();
                    return rowsAffected > 0;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                    return false;
                }
            }
        }

        public bool DeleteUser(int userId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "DELETE FROM users WHERE user_id = @UserId";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);

                try
                {
                    con.Open();
                    int rowsAffected = cmd.ExecuteNonQuery();
                    con.Close();
                    return rowsAffected > 0;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                    return false;
                }
            }
        }

        public List<Post> GetPostsByUserId(int userId)
        {
            var posts = new List<Post>();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT * FROM posts WHERE user_id = @UserId ORDER BY created_at DESC";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var post = new Post
                    {
                        PostId = Convert.ToInt32(reader["post_id"]),
                        UserId = Convert.ToInt32(reader["user_id"]),
                        MediaUrl = reader["media_url"].ToString(),
                        CreatedAt = Convert.ToDateTime(reader["created_at"]),
                        // Add other Post properties as needed
                    };
                    posts.Add(post);
                }
                con.Close();
            }

            return posts;
        }

        public static List<Post> GetExplorePosts()
        {
            List<Post> posts = new List<Post>();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = @"
                  SELECT TOP 20 
                  p.post_id, p.media_url, p.caption, 
                  u.username, u.user_id, u.profile_picture
                  FROM Posts p
                  INNER JOIN users u ON p.user_id = u.user_id
                  ORDER BY p.post_id DESC";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            posts.Add(new Post
                            {
                                PostId = Convert.ToInt32(reader["post_id"]),
                                MediaUrl = reader["media_url"].ToString(),
                                Caption = reader["caption"].ToString(),
                                Username = reader["username"].ToString(),
                                UserId = Convert.ToInt32(reader["user_id"]),
                                UserProfilePic = reader["profile_picture"].ToString()
                            });
                        }
                    }
                }
            }

            return posts;
        }

        public static int GetPostCountByUserId(int userId)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT COUNT(*) FROM Posts WHERE UserId = @UserId";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static object ExecuteScalar(string query, SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddRange(parameters);
                conn.Open();
                return cmd.ExecuteScalar();
            }
        }

        public static int ExecuteNonQuery(string query, SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddRange(parameters);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("ExecuteNonQuery error: " + ex.Message);
                return 0;
            }
        }



        // 🔸 ExecuteReader (SELECT multiple rows)
        public static SqlDataReader ExecuteReader(string query, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(query, conn);
            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            conn.Open();
            // Important: CommandBehavior.CloseConnection ensures connection is closed with reader
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        // Add FriendRequest related methods here:

        // Send a friend request
        public static bool SendRequest(int fromUserId, int toUserId)
        {
            string query = "INSERT INTO FriendRequests (from_user_id, to_user_id, request_date, status) VALUES (@From, @To, @Date, 'Pending')";
            SqlParameter[] parameters = {
    new SqlParameter("@From", fromUserId),
    new SqlParameter("@To", toUserId),
    new SqlParameter("@Date", DateTime.Now)
};
            return ExecuteNonQuery(query, parameters) > 0;
        }

        // Check if a request is already sent
        public static bool AlreadyRequested(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = "SELECT COUNT(*) FROM FriendRequests WHERE from_user_id = @From AND to_user_id = @To AND status = 'Pending'";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@From", fromUserId);
                    cmd.Parameters.AddWithValue("@To", toUserId);

                    int count = (int)cmd.ExecuteScalar();
                    return count > 0;
                }
            }
        }


        public static bool UpdateRequestStatus(int requestId, string status)
        {
            string query = "UPDATE FriendRequests SET status = @Status WHERE request_id = @Id";
            SqlParameter[] parameters = {
    new SqlParameter("@Status", status),
    new SqlParameter("@Id", requestId)
};
            return ExecuteNonQuery(query, parameters) > 0;
        }

        public static List<FriendRequestModel> GetPendingRequests(int userId)
        {
            List<FriendRequestModel> list = new List<FriendRequestModel>();
            string query = @"
    SELECT fr.request_id, fr.from_user_id, fr.to_user_id, fr.request_date,
           u.username, u.profile_picture
    FROM FriendRequests fr
    JOIN Users u ON fr.from_user_id = u.user_id
    WHERE fr.to_user_id = @UserId AND fr.status = 'Pending'";


            SqlParameter[] parameters = { new SqlParameter("@UserId", userId) };
            using (SqlDataReader reader = ExecuteReader(query, parameters))
            {
                while (reader.Read())
                {
                    FriendRequestModel model = new FriendRequestModel
                    {
                        RequestId = Convert.ToInt32(reader["request_id"]),
                        FromUserId = Convert.ToInt32(reader["from_user_id"]),
                        ToUserId = Convert.ToInt32(reader["to_user_id"]),
                        RequestDate = Convert.ToDateTime(reader["request_date"]).ToString("dd MMM yyyy, hh:mm tt"),
                        Username = reader["username"].ToString(),
                        ProfilePicture = reader["profile_picture"].ToString()
                    };

                    list.Add(model);
                }
            }
            return list;
        }

        public static bool HasNewRequests(int userId)
        {
            string query = "SELECT COUNT(*) FROM FriendRequests WHERE to_user_id = @UserId AND status = 'Pending'";
            SqlParameter[] parameters = { new SqlParameter("@UserId", userId) };
            return Convert.ToInt32(ExecuteScalar(query, parameters)) > 0;
        }

        public static bool HasSentFriendRequest(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM FriendRequests WHERE from_user_id = @FromUserId AND to_user_id = @ToUserId AND status = 'Pending'";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@FromUserId", fromUserId);
                cmd.Parameters.AddWithValue("@ToUserId", toUserId);

                con.Open();
                int count = (int)cmd.ExecuteScalar();
                return count > 0;
            }
        }

        public static bool AcceptRequest(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlTransaction transaction = con.BeginTransaction();

                try
                {
                    string updateQuery = "UPDATE FriendRequests SET status = 'Accepted' WHERE from_user_id = @From AND to_user_id = @To";
                    SqlCommand cmd1 = new SqlCommand(updateQuery, con, transaction);
                    cmd1.Parameters.AddWithValue("@From", fromUserId);
                    cmd1.Parameters.AddWithValue("@To", toUserId);
                    cmd1.ExecuteNonQuery();

                    string insertQuery = @"
        IF NOT EXISTS (SELECT 1 FROM followers WHERE follower_user_id = @From AND following_user_id = @To)
        BEGIN
            INSERT INTO followers (follower_user_id, following_user_id) VALUES (@From, @To)
        END;

        IF NOT EXISTS (SELECT 1 FROM followers WHERE follower_user_id = @To AND following_user_id = @From)
        BEGIN
            INSERT INTO followers (follower_user_id, following_user_id) VALUES (@To, @From)
        END;";

                    SqlCommand cmd2 = new SqlCommand(insertQuery, con, transaction);
                    cmd2.Parameters.AddWithValue("@From", fromUserId);
                    cmd2.Parameters.AddWithValue("@To", toUserId);
                    cmd2.ExecuteNonQuery();

                    transaction.Commit();
                    return true;
                }
                catch
                {
                    transaction.Rollback();
                    return false;
                }
            }
        }




        public static bool CancelFriendRequest(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "DELETE FROM FriendRequests WHERE from_user_id = @From AND to_user_id = @To AND status = 'Pending'";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@From", fromUserId);
                cmd.Parameters.AddWithValue("@To", toUserId);
                con.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows > 0;
            }
        }

        // 1. Get Followers (people who follow YOU)
        public static int GetFollowersCount(int userId)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM followers WHERE following_user_id = @UserId"; // ✅ corrected
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@UserId", userId);
                conn.Open();
                count = (int)cmd.ExecuteScalar();
            }
            return count;
        }

        // 2. Get Following (people YOU are following)
        public static int GetFollowingCount(int userId)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM followers WHERE follower_user_id = @UserId"; // ✅ corrected
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@UserId", userId);
                conn.Open();
                count = (int)cmd.ExecuteScalar();
            }
            return count;
        }

        public static bool IsFollowing(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM FriendRequests WHERE from_user_id = @from AND to_user_id = @to AND status = 'Accepted'";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@from", fromUserId);
                cmd.Parameters.AddWithValue("@to", toUserId);
                con.Open();
                int count = (int)cmd.ExecuteScalar();
                return count > 0;
            }
        }

        public static bool AddToFollowers(int followerUserId, int followingUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = @"
        INSERT INTO Followers (follower_user_id, following_user_id, created_at)
        VALUES (@FollowerUserId, @FollowingUserId, GETDATE())";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@FollowerUserId", followerUserId);
                    cmd.Parameters.AddWithValue("@FollowingUserId", followingUserId);
                    con.Open();
                    int rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected > 0;
                }
            }
        }

        public static void IncrementFollowers(int userId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = "UPDATE Users SET Followers = ISNULL(Followers, 0) + 1 WHERE UserId = @UserId";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);
                cmd.ExecuteNonQuery();
            }
        }

        public static void IncrementFollowing(int userId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = "UPDATE Users SET Following = ISNULL(Following, 0) + 1 WHERE UserId = @UserId";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);
                cmd.ExecuteNonQuery();
            }
        }

        public static FriendRequestModel GetFriendRequestById(int requestId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = @"
        SELECT request_id, from_user_id, to_user_id, request_date, status
        FROM FriendRequests
        WHERE request_id = @RequestId";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@RequestId", requestId);
                    con.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new FriendRequestModel
                            {
                                RequestId = Convert.ToInt32(reader["request_id"]),
                                FromUserId = Convert.ToInt32(reader["from_user_id"]),
                                ToUserId = Convert.ToInt32(reader["to_user_id"]),
                                RequestDate = Convert.ToDateTime(reader["request_date"]).ToString("dd MMM, hh:mm tt"),
                                Status = reader["status"].ToString()
                            };
                        }
                    }
                }
            }
            return null;
        }


        public static List<UserDisplayModel> GetFollowersList(int userId)
        {
            var list = new List<UserDisplayModel>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = @"
        SELECT u.user_id, u.fullname, u.username, u.profile_picture
        FROM followers f
        INNER JOIN users u ON f.follower_user_id = u.user_id
        WHERE f.following_user_id = @UserId";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new UserDisplayModel
                            {
                                UserId = Convert.ToInt32(reader["user_id"]),
                                FullName = reader["fullname"].ToString(),
                                Username = reader["username"].ToString(),
                                ProfilePicture = reader["profile_picture"].ToString()
                            });
                        }
                    }
                }
            }
            return list;
        }

        public static List<UserDisplayModel> GetFollowers(int userId)
        {
            List<UserDisplayModel> followers = new List<UserDisplayModel>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = @"SELECT u.user_id, u.fullname, u.username, u.profile_picture
                     FROM followers f
                     INNER JOIN users u ON f.follower_user_id = u.user_id
                     WHERE f.following_user_id = @UserId";

                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    followers.Add(new UserDisplayModel
                    {
                        UserId = Convert.ToInt32(reader["user_id"]),
                        FullName = reader["fullname"].ToString(),
                        Username = reader["username"].ToString(),
                        ProfilePicture = reader["profile_picture"].ToString()
                    });
                }
            }
            return followers;
        }

        public static List<UserDisplayModel> GetFollowing(int userId)
        {
            List<UserDisplayModel> following = new List<UserDisplayModel>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = @"SELECT u.user_id, u.fullname, u.username, u.profile_picture
                     FROM followers f
                     INNER JOIN users u ON f.following_user_id = u.user_id
                     WHERE f.follower_user_id = @UserId";

                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@UserId", userId);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    following.Add(new UserDisplayModel
                    {
                        UserId = Convert.ToInt32(reader["user_id"]),
                        FullName = reader["fullname"].ToString(),
                        Username = reader["username"].ToString(),
                        ProfilePicture = reader["profile_picture"].ToString()
                    });
                }
            }
            return following;
        }

        public static bool Unfollow(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "DELETE FROM followers WHERE follower_user_id = @from AND following_user_id = @to";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@from", fromUserId);
                cmd.Parameters.AddWithValue("@to", toUserId);
                con.Open();
                return cmd.ExecuteNonQuery() > 0;
            }
        }

        public static bool IsAlreadyFollowing(int fromUserId, int toUserId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM followers WHERE follower_user_id = @FromUserId AND following_user_id = @ToUserId";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@FromUserId", fromUserId);
                cmd.Parameters.AddWithValue("@ToUserId", toUserId);
                con.Open();
                int count = (int)cmd.ExecuteScalar();
                return count > 0;
            }
        }

        public static void AddNotification(int senderId, int receiverId, string type, string message)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = @"INSERT INTO Notifications 
                             (sender_user_id, receiver_user_id, type, message, is_read, created_at)
                             VALUES (@sender, @receiver, @type, @message, 0, GETDATE())";

                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@sender", senderId);
                cmd.Parameters.AddWithValue("@receiver", receiverId);
                cmd.Parameters.AddWithValue("@type", type);
                cmd.Parameters.AddWithValue("@message", message);

                con.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public static int GetUnreadNotificationCount(int userId)
        {
            int count = 0;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string query = "SELECT COUNT(*) FROM Notifications WHERE receiver_user_id = @userId AND is_read = 0";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@userId", userId);
                con.Open();
                count = (int)cmd.ExecuteScalar();
            }
            return count;
        }

        public static List<NotificationModel> GetAllNotifications(int userId)
        {
            List<NotificationModel> list = new List<NotificationModel>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                // ✅ Delete notifications older than 30 days
                string cleanupQuery = "DELETE FROM Notifications WHERE DATEDIFF(DAY, created_at, GETDATE()) > 30";
                using (SqlCommand cleanupCmd = new SqlCommand(cleanupQuery, con))
                {
                    cleanupCmd.ExecuteNonQuery();
                }

                // ✅ Now fetch recent notifications
                string query = @"SELECT N.*, U.username, U.profile_picture
                 FROM Notifications N
                 JOIN Users U ON N.sender_user_id = U.user_id
                 WHERE receiver_user_id = @userId
                 ORDER BY created_at DESC";

                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@userId", userId);
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    list.Add(new NotificationModel
                    {
                        NotificationId = Convert.ToInt32(dr["notification_id"]),
                        SenderUserId = Convert.ToInt32(dr["sender_user_id"]),
                        ReceiverUserId = Convert.ToInt32(dr["receiver_user_id"]),
                        Type = dr["type"].ToString(),
                        Message = dr["message"].ToString(),
                        IsRead = Convert.ToBoolean(dr["is_read"]),
                        CreatedAt = Convert.ToDateTime(dr["created_at"]),
                        FromUserName = dr["username"].ToString(),
                        ProfilePicture = dr["profile_picture"].ToString(),
                        TimeAgo = GetTimeAgo(Convert.ToDateTime(dr["created_at"]))
                    });
                }
            }
            return list;
        }

        private static string GetTimeAgo(DateTime dt)
        {
            TimeSpan span = DateTime.Now - dt;
            if (span.TotalMinutes < 1) return "Just now";
            if (span.TotalMinutes < 60) return $"{(int)span.TotalMinutes} mins ago";
            if (span.TotalHours < 24) return $"{(int)span.TotalHours} hours ago";
            if (span.TotalDays < 7) return $"{(int)span.TotalDays} days ago";
            return dt.ToString("dd MMM yyyy");
        }


        public static int GetPostOwnerId(int postId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT user_id FROM Posts WHERE post_id = @pid", con);
                cmd.Parameters.AddWithValue("@pid", postId);
                object result = cmd.ExecuteScalar();
                return result != null ? Convert.ToInt32(result) : 0;
            }
        }


        public static int GetUserIdFromPost(int postId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = "SELECT user_id FROM Posts WHERE post_id = @postId";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@postId", postId);
                    var result = cmd.ExecuteScalar();
                    return result != null ? Convert.ToInt32(result) : 0;
                }
            }
        }

        public static void InsertReport(int postId, int reportedUserId, int reportedByUserId, string reason)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = @"
            INSERT INTO Reports (post_id, reported_user_id, reported_by_user_id, reason, reported_at)
            VALUES (@postId, @reportedUserId, @reportedByUserId, @reason, GETDATE())";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@postId", postId);
                    cmd.Parameters.AddWithValue("@reportedUserId", reportedUserId);
                    cmd.Parameters.AddWithValue("@reportedByUserId", reportedByUserId);
                    cmd.Parameters.AddWithValue("@reason", reason);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static bool HasFiveUniqueReportsOnPost(int postId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string query = @"
                SELECT COUNT(DISTINCT reported_by_user_id)
                FROM Reports
                WHERE post_id = @postId";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@postId", postId);
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    return count >= 5;
                }
            }
        }

        public static void DeletePost(int postId)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                // First delete reports of that post (to prevent FK errors)
                SqlCommand cmd1 = new SqlCommand("DELETE FROM Reports WHERE post_id = @postId", con);
                cmd1.Parameters.AddWithValue("@postId", postId);
                cmd1.ExecuteNonQuery();

                // Then delete the post itself
                SqlCommand cmd2 = new SqlCommand("DELETE FROM Posts WHERE post_id = @postId", con);
                cmd2.Parameters.AddWithValue("@postId", postId);
                cmd2.ExecuteNonQuery();
            }
        }

        public static DataTable GetDataTable(string query, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }

        public static DataTable ExecuteSelectQuery(string query, SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters);

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        return dt;
                    }
                }
            }
        }



        public int InsertScheduledMessage(int senderId, int receiverId, string messageText, DateTime scheduledTime)
        {
            string sql = @"
                INSERT INTO ScheduledMessages 
                    (sender_id, receiver_id, message_text, scheduled_time, is_sent, created_at)
                VALUES 
                    (@sender, @receiver, @text, @scheduled, 0, GETDATE());
                SELECT SCOPE_IDENTITY();";

            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@sender", senderId);
                cmd.Parameters.AddWithValue("@receiver", receiverId);
                cmd.Parameters.AddWithValue("@text", messageText);
                cmd.Parameters.AddWithValue("@scheduled", scheduledTime);

                conn.Open();
                object result = cmd.ExecuteScalar();
                return Convert.ToInt32(result);
            }
        }

    }
}