using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using instavibe.Models;
using InstaVibe.Models;

namespace InstaVibe.Controllers
{
    public class AlumniProfileController : Controller
    {
        public string connectionString = ConfigurationManager.ConnectionStrings["InstaVibe"].ConnectionString.ToString();

        // ====================== ALUMNI SELF PROFILE ======================
        public ActionResult Index()
        {
            if (Session["UserId"] == null)
                return RedirectToAction("Login", "Account");


            int userId = Convert.ToInt32(Session["UserId"]);
            AlumniProfileModel model = LoadAlumniProfile(userId, userId);
            model.IsCurrentUser = true;
            return View("Index", model);
        }

        // ====================== ALUMNI PUBLIC PROFILE ======================
        public ActionResult Public(int id)
        {
            if (Session["UserId"] == null)
                return RedirectToAction("Login", "Account");

            int loggedInUserId = Convert.ToInt32(Session["UserId"]);
            AlumniProfileModel model = LoadAlumniProfile(id, loggedInUserId);
            model.IsCurrentUser = (id == loggedInUserId);
            return View("PublicAlumniProfile", model);
        }

        // ====================== SHARED PROFILE LOADER ======================
        private AlumniProfileModel LoadAlumniProfile(int profileUserId, int viewerUserId)
        {
            AlumniProfileModel model = new AlumniProfileModel();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                // User basic info
                string userQuery = @"
                    SELECT u.user_id, u.fullname, u.profile_picture, u.department, u.email,
                           ap.job_title, ap.company_name, ap.achievements, ap.cv_url,
                           ap.graduation_year, ap.linkedin_url
                    FROM users u
                    LEFT JOIN alumni_profiles ap ON ap.user_id = u.user_id
                    WHERE u.user_id = @UserId";

                using (SqlCommand cmd = new SqlCommand(userQuery, con))
                {
                    cmd.Parameters.AddWithValue("@UserId", profileUserId);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            model.UserId = profileUserId;
                            model.FullName = reader["fullname"].ToString();
                            model.ProfileImage = reader["profile_picture"].ToString();
                            model.Department = reader["department"].ToString();
                            model.Email = reader["email"].ToString();
                            model.JobTitle = reader["job_title"]?.ToString();
                            model.CompanyName = reader["company_name"]?.ToString();
                            model.Achievements = reader["achievements"]?.ToString();
                            model.CvUrl = reader["cv_url"]?.ToString();
                            model.GraduationYear = reader["graduation_year"]?.ToString();
                            model.LinkedInUrl = reader["linkedin_url"]?.ToString();
                        }
                    }
                }

                // Posts
                model.Posts = new List<PostModel>();
                string postsQuery = @"
                    SELECT p.post_id, p.media_url, p.caption, p.created_at,
                           u.username, u.profile_picture
                    FROM Posts p
                    INNER JOIN users u ON p.user_id = u.user_id
                    WHERE p.user_id = @UserId
                    ORDER BY p.created_at DESC";

                using (SqlCommand postCmd = new SqlCommand(postsQuery, con))
                {
                    postCmd.Parameters.AddWithValue("@UserId", profileUserId);
                    using (SqlDataReader reader = postCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            model.Posts.Add(new PostModel()
                            {
                                PostId = Convert.ToInt32(reader["post_id"]),
                                MediaUrl = reader["media_url"].ToString(),
                                Caption = reader["caption"].ToString(),
                                PostDate = Convert.ToDateTime(reader["created_at"]),
                                Username = reader["username"].ToString(),
                                ProfileImage = reader["profile_picture"].ToString()
                            });
                        }
                    }
                }

                model.PostsCount = model.Posts.Count;

                // Follower / Following counts
                model.FollowersCount = GetCount(con, "SELECT COUNT(*) FROM followers WHERE following_user_id = @Id", profileUserId);
                model.FollowingCount = GetCount(con, "SELECT COUNT(*) FROM followers WHERE follower_user_id = @Id", profileUserId);

                // Relationship (only needed for public view)
                if (viewerUserId != profileUserId)
                {
                    model.IsFollowing = DatabaseHelper.IsFollowing(viewerUserId, profileUserId);
                    model.HasSentRequest = GetCount(con,
                        "SELECT COUNT(*) FROM FriendRequests WHERE from_user_id = @Id AND to_user_id = " + profileUserId + " AND status = 'Pending'",
                        viewerUserId) > 0;
                }
            }

            return model;
        }

        private int GetCount(SqlConnection con, string query, int id)
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Id", id);
                return (int)cmd.ExecuteScalar();
            }
        }

        // ====================== CV UPLOAD ======================
        [HttpPost]
        public JsonResult UploadCV(HttpPostedFileBase cvFile)
        {
            if (Session["UserId"] == null)
                return Json(new { success = false, message = "Not logged in" });

            if (cvFile == null || cvFile.ContentLength == 0)
                return Json(new { success = false, message = "No file selected" });

            // Sirf PDF allow karo
            string extension = Path.GetExtension(cvFile.FileName).ToLower();
            if (extension != ".pdf")
                return Json(new { success = false, message = "Only PDF allowed" });

            int userId = Convert.ToInt32(Session["UserId"]);
            string fileName = "cv_" + userId + "_" + DateTime.Now.Ticks + ".pdf";
            string savePath = Server.MapPath("~/Uploads/CVs/" + fileName);

            // Folder exist nahi karta toh banao
            Directory.CreateDirectory(Server.MapPath("~/Uploads/CVs/"));
            cvFile.SaveAs(savePath);

            string cvUrl = "/Uploads/CVs/" + fileName;

            // DB mein update karo
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                // Pehle check karo alumni_profile row exist karta hai ya nahi
                string checkQuery = "SELECT COUNT(*) FROM alumni_profiles WHERE user_id = @UserId";
                using (SqlCommand checkCmd = new SqlCommand(checkQuery, con))
                {
                    checkCmd.Parameters.AddWithValue("@UserId", userId);
                    int exists = (int)checkCmd.ExecuteScalar();

                    if (exists > 0)
                    {
                        string updateQuery = "UPDATE alumni_profiles SET cv_url = @CvUrl WHERE user_id = @UserId";
                        using (SqlCommand updateCmd = new SqlCommand(updateQuery, con))
                        {
                            updateCmd.Parameters.AddWithValue("@CvUrl", cvUrl);
                            updateCmd.Parameters.AddWithValue("@UserId", userId);
                            updateCmd.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        string insertQuery = "INSERT INTO alumni_profiles (user_id, cv_url) VALUES (@UserId, @CvUrl)";
                        using (SqlCommand insertCmd = new SqlCommand(insertQuery, con))
                        {
                            insertCmd.Parameters.AddWithValue("@UserId", userId);
                            insertCmd.Parameters.AddWithValue("@CvUrl", cvUrl);
                            insertCmd.ExecuteNonQuery();
                        }
                    }
                }
            }

            return Json(new { success = true, cvUrl = cvUrl });
        }

        // ====================== UPDATE PROFESSIONAL INFO ======================
        [HttpPost]
        public JsonResult UpdateProfessionalInfo(string jobTitle, string companyName,
            string achievements, string graduationYear, string linkedInUrl)
        {
            if (Session["UserId"] == null)
                return Json(new { success = false, message = "Not logged in" });

            int userId = Convert.ToInt32(Session["UserId"]);

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                string checkQuery = "SELECT COUNT(*) FROM alumni_profiles WHERE user_id = @UserId";
                using (SqlCommand checkCmd = new SqlCommand(checkQuery, con))
                {
                    checkCmd.Parameters.AddWithValue("@UserId", userId);
                    int exists = (int)checkCmd.ExecuteScalar();

                    string query = exists > 0
                        ? @"UPDATE alumni_profiles SET job_title=@JobTitle, company_name=@CompanyName,
                            achievements=@Achievements, graduation_year=@GradYear, linkedin_url=@LinkedIn
                            WHERE user_id=@UserId"
                        : @"INSERT INTO alumni_profiles (user_id, job_title, company_name, achievements, graduation_year, linkedin_url)
                            VALUES (@UserId, @JobTitle, @CompanyName, @Achievements, @GradYear, @LinkedIn)";

                    using (SqlCommand cmd = new SqlCommand(query, con))
                    {
                        cmd.Parameters.AddWithValue("@UserId", userId);
                        cmd.Parameters.AddWithValue("@JobTitle", jobTitle ?? "");
                        cmd.Parameters.AddWithValue("@CompanyName", companyName ?? "");
                        cmd.Parameters.AddWithValue("@Achievements", achievements ?? "");
                        cmd.Parameters.AddWithValue("@GradYear", graduationYear ?? "");
                        cmd.Parameters.AddWithValue("@LinkedIn", linkedInUrl ?? "");
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            return Json(new { success = true });
        }

        // ====================== FOLLOWERS / FOLLOWING PARTIALS ======================
        public ActionResult GetFollowers(int userId)
        {
            var followers = DatabaseHelper.GetFollowers(userId);
            return PartialView("~/Views/Shared/_FollowListPartial.cshtml", followers);
        }

        public ActionResult GetFollowing(int userId)
        {
            var following = DatabaseHelper.GetFollowing(userId);
            return PartialView("~/Views/Shared/_FollowListPartial.cshtml", following);
        }
    }
}