<%@ WebHandler Language="C#" Class="Api" %>

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Net;
using System.Globalization;
using getepay_sdk;


public class Api : IHttpHandler {
    private string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    public void ProcessRequest (HttpContext context) {
        // 1. Enable CORS for Flutter app integration (mobile/web)
        context.Response.Headers.Add("Access-Control-Allow-Origin", "*");
        context.Response.Headers.Add("Access-Control-Allow-Headers", "Content-Type, Accept");
        context.Response.Headers.Add("Access-Control-Allow-Methods", "GET, POST, OPTIONS");
        context.Response.ContentType = "application/json";

        // Handle CORS Preflight OPTIONS requests
        if (context.Request.HttpMethod == "OPTIONS") {
            context.Response.StatusCode = 200;
            context.Response.End();
            return;
        }

        string action = context.Request.QueryString["action"];
        if (string.IsNullOrEmpty(action)) {
            SendError(context, 400, "Action parameter is missing. Please use ?action=members, etc.");
            return;
        }

        try {
            switch (action.ToLower()) {
                case "members":
                    HandleMembers(context);
                    break;
                case "member_directory":
                    HandleMemberDirectory(context);
                    break;
                case "member_details":
                    HandleMemberDetails(context);
                    break;
                case "notices":
                    HandleNotices(context);
                    break;
                case "announcement":
                case "announcements":
                    HandleAnnouncement(context);
                    break;
                case "communications":
                    HandleCommunications(context);
                    break;
                case "communicationdetails":
                case "communication_details":
                    HandleCommunicationDetails(context);
                    break;
                case "forms":
                case "ndbaforms":
                case "ndba_forms":
                    HandleForms(context);
                    break;
                case "albums":
                    HandleAlbums(context);
                    break;
                case "photos":
                    HandlePhotos(context);
                    break;
                case "videos":
                    HandleVideos(context);
                    break;
                case "video_details":
                    HandleVideoDetails(context);
                    break;
                case "office_bearers":
                    HandleOfficeBearers(context);
                    break;
                case "committee_members":
                case "committeemembers":
                    HandleCommitteeMembers(context);
                    break;
                case "banners":
                    HandleBanners(context);
                    break;
                case "calculate_dues":
                    HandleCalculateDues(context);
                    break;
                case "member_ledger":
                    HandleMemberLedger(context);
                    break;
                case "send_otp":
                    HandleSendOtp(context);
                    break;
                case "apply_membership":
                    if (context.Request.HttpMethod == "POST") {
                        HandleApplyMembership(context);
                    } else {
                        SendError(context, 405, "HTTP Method not allowed. Use POST.");
                    }
                    break;
                case "initiate_payment":
                    if (context.Request.HttpMethod == "POST") {
                        HandleInitiatePayment(context);
                    } else {
                        SendError(context, 405, "HTTP Method not allowed. Use POST.");
                    }
                    break;
                case "verify_payment":
                    HandleVerifyPayment(context);
                    break;
                case "request_idcard":
                    if (context.Request.HttpMethod == "POST") {
                        HandleRequestIdCard(context);
                    } else {
                        SendError(context, 405, "HTTP Method not allowed. Use POST.");
                    }
                    break;
                case "member_login":
                    HandleMemberLogin(context);
                    break;
                case "member_login_password":
                    HandleMemberLoginPassword(context);
                    break;
                case "change_password":
                case "member_change_password":
                    HandleChangePassword(context);
                    break;
                case "member_register":
                    HandleMemberRegister(context);
                    break;
                case "court_hearings":
                    HandleVirtualCourtHearings(context);
                    break;
                case "case_create":
                    HandleCaseCreate(context);
                    break;
                case "case_update":
                    HandleCaseUpdate(context);
                    break;
                case "case_list":
                    HandleCaseList(context);
                    break;
                case "case_details":
                    HandleCaseDetails(context);
                    break;
                case "case_comment_add":
                    HandleCaseCommentAdd(context);
                    break;
                case "case_file_upload":
                    HandleCaseFileUpload(context);
                    break;
                case "search_lawyers":
                    HandleSearchLawyers(context);
                    break;
                default:
                    SendError(context, 404, "Invalid API endpoint/action: " + action);
                    break;
            }
        }
        catch (Exception ex) {
            SendError(context, 500, ex.Message);
        }
    }

    // --- 1. GET MEMBERS WITH SEARCH AND PAGING ---
    private void HandleMembers(HttpContext context) {
        string search = context.Request.QueryString["search"];
        string pageStr = context.Request.QueryString["page"];
        string limitStr = context.Request.QueryString["limit"];

        int page = 1;
        int limit = 20;

        if (!string.IsNullOrEmpty(pageStr)) int.TryParse(pageStr, out page);
        if (!string.IsNullOrEmpty(limitStr)) int.TryParse(limitStr, out limit);

        int offset = (page - 1) * limit;

        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            if (string.IsNullOrEmpty(search)) {
                // Return paginated list
                query = @"SELECT F_name, M_name, L_name, Ndbano, Bcdenroll, Mobile_1, Photo, sstatus 
                          FROM Memberslist 
                          ORDER BY id DESC 
                          OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY";
                cmd.Parameters.AddWithValue("@offset", offset);
                cmd.Parameters.AddWithValue("@limit", limit);
            }
            else {
                // Return searched paginated list
                query = @"SELECT F_name, M_name, L_name, Ndbano, Bcdenroll, Mobile_1, Photo, sstatus 
                          FROM Memberslist 
                          WHERE F_name LIKE @search 
                             OR L_name LIKE @search 
                             OR Mobile_1 LIKE @search 
                             OR Bcdenroll LIKE @search 
                             OR Ndbano LIKE @search 
                          ORDER BY id DESC
                          OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY";
                cmd.Parameters.AddWithValue("@search", "%" + search + "%");
                cmd.Parameters.AddWithValue("@offset", offset);
                cmd.Parameters.AddWithValue("@limit", limit);
            }

            cmd.CommandText = query;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                page = page,
                limit = limit,
                data = dt
            }));
        }
     }

    // --- GET MEMBER DIRECTORY (SEARCH BY SPECIFIC FIELD TYPE) ---
    private void HandleMemberDirectory(HttpContext context) {
        string searchType = context.Request.QueryString["search_type"];
        string searchValue = context.Request.QueryString["search_value"];

        if (string.IsNullOrEmpty(searchType)) {
            SendError(context, 400, "Parameter 'search_type' is required (options: 'S.No.', 'First Name', 'Mobile No.', 'BCD Enrollment No.').");
            return;
        }

        if (searchValue == null) {
            searchValue = "";
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "";
            string typeLower = searchType.ToLower().Replace(" ", "").Replace(".", "");

            if (typeLower == "sno") {
                query = "SELECT * FROM Memberslist WHERE Ndbano LIKE @searchValue";
            }
            else if (typeLower == "firstname" || typeLower == "name") {
                query = "SELECT * FROM Memberslist WHERE F_name LIKE @searchValue";
            }
            else if (typeLower == "mobileno" || typeLower == "mobile") {
                query = "SELECT * FROM Memberslist WHERE Mobile_1 LIKE @searchValue";
            }
            else if (typeLower == "bcdenrollmentno" || typeLower == "enrollment") {
                query = "SELECT * FROM Memberslist WHERE Bcdenroll LIKE @searchValue";
            }
            else {
                SendError(context, 400, "Invalid 'search_type'. Allowed: 'S.No.', 'First Name', 'Mobile No.', 'BCD Enrollment No.'");
                return;
            }

            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@searchValue", "%" + searchValue + "%");
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                count = dt.Rows.Count,
                data = dt
            }));
        }
    }

    // --- 2. GET SINGLE MEMBER PROFILE, DUES, RECEIPTS AND BILLS ---
    private void HandleMemberDetails(HttpContext context) {
        string id = context.Request.QueryString["id"];
        if (string.IsNullOrEmpty(id)) {
            SendError(context, 400, "Parameter 'id' is required.");
            return;
        }

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

            // 1. Fetch Profile info
            string profileQuery = "SELECT * FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id";
            SqlCommand profileCmd = new SqlCommand(profileQuery, con);
            profileCmd.Parameters.AddWithValue("@id", id);
            SqlDataAdapter profileDa = new SqlDataAdapter(profileCmd);
            DataTable profileDt = new DataTable();
            profileDa.Fill(profileDt);

            if (profileDt.Rows.Count == 0) {
                SendError(context, 404, "Member not found with enrollment or NDBA ID: " + id);
                return;
            }

            DataRow profileRow = profileDt.Rows[0];
            string bcdEnroll = profileRow["Bcdenroll"].ToString();

            // 2. Fetch Dues info from Membersduelist
            string duesQuery = "SELECT * FROM Membersduelist WHERE Bcdenroll = @bcd";
            SqlCommand duesCmd = new SqlCommand(duesQuery, con);
            duesCmd.Parameters.AddWithValue("@bcd", bcdEnroll);
            SqlDataAdapter duesDa = new SqlDataAdapter(duesCmd);
            DataTable duesDt = new DataTable();
            duesDa.Fill(duesDt);

            // 3. Fetch Receipts from Membersallrecpit
            string receiptsQuery = "SELECT Recp_no, Recp_dt, Recp_md, Dd_no, Amount, Dd_bnk FROM Membersallrecpit WHERE Bcdenroll = @bcd ORDER BY Recp_dt DESC";
            SqlCommand receiptsCmd = new SqlCommand(receiptsQuery, con);
            receiptsCmd.Parameters.AddWithValue("@bcd", bcdEnroll);
            SqlDataAdapter receiptsDa = new SqlDataAdapter(receiptsCmd);
            DataTable receiptsDt = new DataTable();
            receiptsDa.Fill(receiptsDt);

            // 4. Fetch Bills from newdelhibar_bill
            string billsQuery = "SELECT Billno, Daten, Paymentmode, totalamount, btype, Status FROM newdelhibar_bill WHERE BCDno = @bcd ORDER BY Daten DESC";
            SqlCommand billsCmd = new SqlCommand(billsQuery, con);
            billsCmd.Parameters.AddWithValue("@bcd", bcdEnroll);
            SqlDataAdapter billsDa = new SqlDataAdapter(billsCmd);
            DataTable billsDt = new DataTable();
            billsDa.Fill(billsDt);

            var result = new {
                success = true,
                profile = DataRowToDictionary(profileRow),
                dues = duesDt.Rows.Count > 0 ? DataRowToDictionary(duesDt.Rows[0]) : null,
                receipts = receiptsDt,
                bills = billsDt
            };

            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    // --- 3. GET NOTICE BOARD LIST WITH THEIR IMAGES INCLUDED ---
    private void HandleNotices(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();

            // Fetch all notices
            string query = "SELECT pkid, title, [Type], pdfPath FROM newdelhibar_NoticeBoard ORDER BY pkid DESC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable noticesDt = new DataTable();
            da.Fill(noticesDt);

            List<Dictionary<string, object>> noticesList = new List<Dictionary<string, object>>();

            foreach (DataRow row in noticesDt.Rows) {
                var notice = new Dictionary<string, object>();
                notice["pkid"] = row["pkid"];
                notice["title"] = row["title"];
                notice["type"] = row["Type"];
                notice["pdfPath"] = row["pdfPath"];

                // Fetch attached images for each notice (if it is an Images type notice)
                string imgQuery = "SELECT attch FROM newdelhibar_NoticeBoardImg WHERE number = @noticeId";
                SqlCommand imgCmd = new SqlCommand(imgQuery, con);
                imgCmd.Parameters.AddWithValue("@noticeId", row["pkid"].ToString());
                SqlDataAdapter imgDa = new SqlDataAdapter(imgCmd);
                DataTable imgDt = new DataTable();
                imgDa.Fill(imgDt);

                List<string> images = new List<string>();
                foreach (DataRow imgRow in imgDt.Rows) {
                    images.Add(imgRow["attch"].ToString());
                }

                notice["images"] = images;
                noticesList.Add(notice);
            }

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = noticesList
            }));
        }
    }

    // --- 4. GET PHOTO ALBUMS ---
    private void HandleAlbums(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT pkid, AlbumName, CoverPhotoPath, AlbumRank FROM newdelhibar_PhotoAlbumList ORDER BY CAST(AlbumRank AS INT) ASC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- 5. GET PHOTOS BY ALBUM ID ---
    private void HandlePhotos(HttpContext context) {
        string albumId = context.Request.QueryString["album_id"];
        if (string.IsNullOrEmpty(albumId)) {
            SendError(context, 400, "Parameter 'album_id' is required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT pkid, Name, ImagePath, ImageRank, About FROM newdelhibar_imageGalleryList WHERE Album = @albumId ORDER BY CAST(ImageRank AS INT) ASC";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@albumId", albumId);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                albumId = albumId,
                data = dt
            }));
        }
    }

    // --- 6. POST: SUBMIT MEMBERSHIP APPLICATION ---
    private void HandleApplyMembership(HttpContext context) {
        string jsonInput = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) {
            jsonInput = reader.ReadToEnd();
        }

        if (string.IsNullOrEmpty(jsonInput)) {
            SendError(context, 400, "Request body is empty.");
            return;
        }

        dynamic data = JsonConvert.DeserializeObject(jsonInput);
        if (data == null) {
            SendError(context, 400, "Invalid JSON body provided.");
            return;
        }

        // Required parameters validation
        if (data.Name == null || data.Mobile == null || data.BarEnrollment == null) {
            SendError(context, 400, "Required parameters missing. 'Name', 'Mobile', and 'BarEnrollment' must be provided.");
            return;
        }

        string name = (string)data.Name;
        string fatherHusbandName = (string)data.FatherHusbandName ?? "";
        string dob = (string)data.DOB ?? "";
        string barEnrollment = (string)data.BarEnrollment;
        string otherStateEnrollment = (string)data.OtherStateEnrollment ?? "";
        string placePractice = (string)data.PlacePractice ?? "";
        string graduationDetails = (string)data.GraduationDetails ?? "";
        string chamberNo = (string)data.ChamberNo ?? "";
        string resAddress = (string)data.ResAddress ?? "";
        string mobile = (string)data.Mobile;
        string email = (string)data.Email ?? "";
        string aadhar = (string)data.Aadhar ?? "";
        string voterId = (string)data.VoterId ?? "";
        string bloodGroup = (string)data.BloodGroup ?? "";
        string pendingCase = (string)data.PendingCase ?? "";
        string retired = (string)data.Retired ?? "";
        string applicantName = (string)data.ApplicantName ?? name;
        string recommendations = (string)data.Recommendations ?? "";
        
        // base64 strings or URLs for signature and photos
        string signaturePath = (string)data.SignaturePath ?? "";
        string photoPath = (string)data.PhotoPath ?? "";

        string billNo = "ND" + DateTime.Now.Ticks.ToString().Substring(12);

        using (SqlConnection conn = new SqlConnection(connectionString)) {
            conn.Open();
            SqlTransaction transaction = conn.BeginTransaction();

            try {
                // 1. Insert into NDBAMembers
                string memberQuery = @"INSERT INTO NDBAMembers (
                    Name, FatherHusbandName, DOB, BarEnrollment, OtherStateEnrollment, PlacePractice, 
                    GraduationDetails, ChamberNo, ResAddress, Mobile, Email, Aadhar, VoterId, 
                    BloodGroup, PendingCase, Retired, ApplicantName, Recommendations,
                    SignaturePath, PhotoPath, Paymentsatats, daten
                ) VALUES (
                    @Name, @FatherHusbandName, @DOB, @BarEnrollment, @OtherStateEnrollment, @PlacePractice,
                    @GraduationDetails, @ChamberNo, @ResAddress, @Mobile, @Email, @Aadhar, @VoterId,
                    @BloodGroup, @PendingCase, @Retired, @ApplicantName, @Recommendations,
                    @SignaturePath, @PhotoPath, 'Pending', @Daten
                )";

                SqlCommand mCmd = new SqlCommand(memberQuery, conn, transaction);
                mCmd.Parameters.AddWithValue("@Name", name);
                mCmd.Parameters.AddWithValue("@FatherHusbandName", fatherHusbandName);
                mCmd.Parameters.AddWithValue("@DOB", dob);
                mCmd.Parameters.AddWithValue("@BarEnrollment", barEnrollment);
                mCmd.Parameters.AddWithValue("@OtherStateEnrollment", otherStateEnrollment);
                mCmd.Parameters.AddWithValue("@PlacePractice", placePractice);
                mCmd.Parameters.AddWithValue("@GraduationDetails", graduationDetails);
                mCmd.Parameters.AddWithValue("@ChamberNo", chamberNo);
                mCmd.Parameters.AddWithValue("@ResAddress", resAddress);
                mCmd.Parameters.AddWithValue("@Mobile", mobile);
                mCmd.Parameters.AddWithValue("@Email", email);
                mCmd.Parameters.AddWithValue("@Aadhar", aadhar);
                mCmd.Parameters.AddWithValue("@VoterId", voterId);
                mCmd.Parameters.AddWithValue("@BloodGroup", bloodGroup);
                mCmd.Parameters.AddWithValue("@PendingCase", pendingCase);
                mCmd.Parameters.AddWithValue("@Retired", retired);
                mCmd.Parameters.AddWithValue("@ApplicantName", applicantName);
                mCmd.Parameters.AddWithValue("@Recommendations", recommendations);
                mCmd.Parameters.AddWithValue("@SignaturePath", signaturePath);
                mCmd.Parameters.AddWithValue("@PhotoPath", photoPath);
                mCmd.Parameters.AddWithValue("@Daten", DateTime.Now);
                mCmd.ExecuteNonQuery();

                // 2. Insert into newdelhibar_bill (Pending Membership Bill)
                string billQuery = @"INSERT INTO newdelhibar_bill (
                    Billno, Name, Mobile, NDBAno, BCDno, Daten, Address, 
                    Headermessage, Paymentmode, Footermessage, totalamount, btype, Status
                ) VALUES (
                    @Billno, @Name, @Mobile, '', @BCDno, @Daten, @Address, 
                    'New Member Fee', 'Online', 'Online Fee', '1700', 'Member ship', 'Pending'
                )";

                SqlCommand bCmd = new SqlCommand(billQuery, conn, transaction);
                bCmd.Parameters.AddWithValue("@Billno", billNo);
                bCmd.Parameters.AddWithValue("@Name", name);
                bCmd.Parameters.AddWithValue("@Mobile", mobile);
                bCmd.Parameters.AddWithValue("@BCDno", barEnrollment);
                bCmd.Parameters.AddWithValue("@Daten", DateTime.Now);
                bCmd.Parameters.AddWithValue("@Address", resAddress);
                bCmd.ExecuteNonQuery();

                // 3. Insert into newdelhibar_rowbill (Row item details)
                string rowBillQuery = "INSERT INTO newdelhibar_rowbill (Billno, Particulars, Amount) VALUES (@Billno, 'New Membership Fee', '1700')";
                SqlCommand rCmd = new SqlCommand(rowBillQuery, conn, transaction);
                rCmd.Parameters.AddWithValue("@Billno", billNo);
                rCmd.ExecuteNonQuery();

                transaction.Commit();

                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    message = "Membership application submitted successfully.",
                    billNo = billNo,
                    amount = 1700,
                    status = "Pending"
                }));
            }
            catch (Exception ex) {
                transaction.Rollback();
                throw ex;
            }
        }
    }

    // --- 7. GET COMMUNICATIONS BOARD NOTICES ---
    private void HandleCommunications(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = "SELECT pkid, title, [Type] as [type], ISNULL(pdfPath, '') as pdfPath FROM newdelhibar_CommunicationBoard ORDER BY pkid DESC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- GET ANNOUNCEMENT (SINGLE OR ALL) ---
    private void HandleAnnouncement(HttpContext context) {
        string id = context.Request.QueryString["id"];
        if (string.IsNullOrEmpty(id)) {
            id = context.Request.QueryString["pkid"];
        }

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

            if (!string.IsNullOrEmpty(id)) {
                // Fetch single announcement
                string query = "SELECT pkid, title, [Type], pdfPath FROM newdelhibar_NoticeBoard WHERE pkid = @id";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@id", id);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count == 0) {
                    SendError(context, 404, "Announcement not found with ID: " + id);
                    return;
                }

                DataRow row = dt.Rows[0];
                var notice = new Dictionary<string, object>();
                notice["pkid"] = row["pkid"];
                notice["title"] = row["title"];
                notice["type"] = row["Type"];
                notice["pdfPath"] = row["pdfPath"];

                // Fetch attached images
                string imgQuery = "SELECT attch FROM newdelhibar_NoticeBoardImg WHERE number = @noticeId";
                SqlCommand imgCmd = new SqlCommand(imgQuery, con);
                imgCmd.Parameters.AddWithValue("@noticeId", id);
                SqlDataAdapter imgDa = new SqlDataAdapter(imgCmd);
                DataTable imgDt = new DataTable();
                imgDa.Fill(imgDt);

                List<string> images = new List<string>();
                foreach (DataRow imgRow in imgDt.Rows) {
                    images.Add(imgRow["attch"].ToString());
                }

                notice["images"] = images;

                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    data = notice
                }));
            }
            else {
                // Fetch all announcements
                string query = "SELECT pkid, title, [Type] as [type], ISNULL(pdfPath, '') as pdfPath FROM newdelhibar_NoticeBoard ORDER BY pkid DESC";
                SqlCommand cmd = new SqlCommand(query, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable noticesDt = new DataTable();
                da.Fill(noticesDt);

                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    data = noticesDt
                }));
            }
        }
    }

    // --- GET COMMUNICATION DETAILS (SINGLE OR ALL) ---
    private void HandleCommunicationDetails(HttpContext context) {
        string id = context.Request.QueryString["id"];
        if (string.IsNullOrEmpty(id)) {
            id = context.Request.QueryString["pkid"];
        }

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

            if (!string.IsNullOrEmpty(id)) {
                // Fetch single communication
                string query = "SELECT pkid, title, [Type], pdfPath FROM newdelhibar_CommunicationBoard WHERE pkid = @id";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@id", id);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count == 0) {
                    SendError(context, 404, "Communication not found with ID: " + id);
                    return;
                }

                DataRow row = dt.Rows[0];
                var comm = new Dictionary<string, object>();
                comm["pkid"] = row["pkid"];
                comm["title"] = row["title"];
                comm["type"] = row["Type"];
                comm["pdfPath"] = row["pdfPath"] != DBNull.Value ? row["pdfPath"].ToString() : "";

                // Fetch attached images
                string imgQuery = "SELECT attch FROM newdelhibar_CommunicationBoardImg WHERE number = @commId";
                SqlCommand imgCmd = new SqlCommand(imgQuery, con);
                imgCmd.Parameters.AddWithValue("@commId", id);
                SqlDataAdapter imgDa = new SqlDataAdapter(imgCmd);
                DataTable imgDt = new DataTable();
                imgDa.Fill(imgDt);

                List<string> images = new List<string>();
                foreach (DataRow imgRow in imgDt.Rows) {
                    images.Add(imgRow["attch"].ToString());
                }

                comm["images"] = images;

                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    data = comm
                }));
            }
            else {
                // Fetch all communications
                string query = "SELECT pkid, title, [Type] as [type], ISNULL(pdfPath, '') as pdfPath FROM newdelhibar_CommunicationBoard ORDER BY pkid DESC";
                SqlCommand cmd = new SqlCommand(query, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    data = dt
                }));
            }
        }
    }

    // --- 8. GET NDBA FORMS ---
    private void HandleForms(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT Title, pdfPath FROM newdelhibar_NDBAForm ORDER BY id DESC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            List<Dictionary<string, object>> formsList = new List<Dictionary<string, object>>();
            foreach (DataRow row in dt.Rows) {
                var form = new Dictionary<string, object>();
                form["title"] = row["Title"];
                form["pdfPath"] = row["pdfPath"] != DBNull.Value ? row["pdfPath"].ToString().Replace("~/", "") : "";
                formsList.Add(form);
            }

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = formsList
            }));
        }
    }

    // --- 9. GET VIDEO ALBUMS ---
    private void HandleVideos(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT id, AlbumName, CoverPhotoPath, AlbumRank FROM newdelhibar_VideoAlbumList ORDER BY CAST(AlbumRank AS INT) ASC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- 10. GET VIDEOS BY ALBUM ID ---
    private void HandleVideoDetails(HttpContext context) {
        string albumId = context.Request.QueryString["album_id"];
        if (string.IsNullOrEmpty(albumId)) {
            SendError(context, 400, "Parameter 'album_id' is required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT id, video_rnk, video_title, vdo, vdo_album FROM newdelhibar_Vedio WHERE vdo_album = @albumId ORDER BY CAST(video_rnk AS INT) ASC";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@albumId", albumId);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                albumId = albumId,
                data = dt
            }));
        }
    }

    // --- 11. GET OFFICE BEARERS ---
    private void HandleOfficeBearers(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT id, Name, Post, Mobile, Photo FROM Officelist ORDER BY id DESC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- GET COMMITTEE MEMBERS ---
    private void HandleCommitteeMembers(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT Id, Designation, MemberName, PhoneNumber, PhotoPath, CreatedDate FROM tb_committee_members ORDER BY Id ASC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- 12. GET BANNERS ---
    private void HandleBanners(HttpContext context) {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            string query = "SELECT id, Imagep, titlename, BannerSet, link FROM Advertise_Data ORDER BY id DESC";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- 13. CALCULATE MEMBER DUES AND LATE FEES ---
    private void HandleCalculateDues(HttpContext context) {
        string memberId = context.Request.QueryString["member_id"];
        if (string.IsNullOrEmpty(memberId)) {
            SendError(context, 400, "Parameter 'member_id' is required.");
            return;
        }

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

            // Fetch profile info
            string profileQuery = "SELECT * FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id";
            SqlCommand profileCmd = new SqlCommand(profileQuery, con);
            profileCmd.Parameters.AddWithValue("@id", memberId);
            SqlDataAdapter profileDa = new SqlDataAdapter(profileCmd);
            DataTable profileDt = new DataTable();
            profileDa.Fill(profileDt);

            if (profileDt.Rows.Count == 0) {
                SendError(context, 404, "Member not found with enrollment or NDBA ID: " + memberId);
                return;
            }

            DataRow profileRow = profileDt.Rows[0];
            string bcdEnroll = profileRow["Bcdenroll"].ToString();
            string ndbaNo = profileRow["Ndbano"].ToString();

            // Fetch dues info
            string duesQuery = "SELECT * FROM Membersduelist WHERE Bcdenroll = @bcd OR Ndbano = @ndba ORDER BY id DESC";
            SqlCommand duesCmd = new SqlCommand(duesQuery, con);
            duesCmd.Parameters.AddWithValue("@bcd", bcdEnroll);
            duesCmd.Parameters.AddWithValue("@ndba", ndbaNo);
            SqlDataAdapter duesDa = new SqlDataAdapter(duesCmd);
            DataTable duesDt = new DataTable();
            duesDa.Fill(duesDt);

            string dues = "0";
            string dueDateStr = "";
            int lateFee = 0;
            int totalDues = 0;

            if (duesDt.Rows.Count > 0) {
                DataRow duesRow = duesDt.Rows[0];
                dues = duesRow["dues"].ToString();
                dueDateStr = duesRow["Ndbadate"].ToString();

                if (dues != "0" && !string.IsNullOrEmpty(dueDateStr)) {
                    DateTime dt1 = DateTime.Now.AddDays(-1);
                    DateTime dt2 = Convert.ToDateTime(dueDateStr);
                    // Replicating month count logic exactly
                    int monthCount = dt1.Month - dt2.Month;
                    if (dt1.Year != dt2.Year) {
                        monthCount = ((dt1.Year - dt2.Year - 1) * 12) + (13 - dt2.Month + dt1.Month);
                    }
                    if (monthCount > 0) {
                        lateFee = 100 * monthCount;
                    }
                }
                
                int baseDuesVal = 0;
                int.TryParse(dues, out baseDuesVal);
                totalDues = baseDuesVal + lateFee;
            }

            var result = new {
                success = true,
                profile = DataRowToDictionary(profileRow),
                baseDues = dues,
                dueDate = dueDateStr,
                lateFee = lateFee,
                totalDues = totalDues
            };

            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    // --- 14. GET RUNNING SUBSCRIPTION & PAYMENTS LEDGER ---
    private void HandleMemberLedger(HttpContext context) {
        string memberId = context.Request.QueryString["member_id"];
        if (string.IsNullOrEmpty(memberId)) {
            SendError(context, 400, "Parameter 'member_id' is required.");
            return;
        }

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

            // Find Ndbadate for membership start date
            string profileQuery = "SELECT Ndbadate FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id";
            SqlCommand profileCmd = new SqlCommand(profileQuery, con);
            profileCmd.Parameters.AddWithValue("@id", memberId);
            object ndbaDateObj = profileCmd.ExecuteScalar();

            if (ndbaDateObj == null || ndbaDateObj == DBNull.Value) {
                SendError(context, 404, "Member not found or has no membership date.");
                return;
            }

            DateTime membershipDate = Convert.ToDateTime(ndbaDateObj);
            DateTime minDate = new DateTime(2016, 1, 1);
            if (membershipDate <= minDate) {
                membershipDate = minDate;
            }

            // Create DataTable for ledger items
            DataTable dt = new DataTable();
            dt.Columns.Add("Date", typeof(DateTime));
            dt.Columns.Add("RefNo", typeof(string));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Credit", typeof(decimal));
            dt.Columns.Add("Debit", typeof(decimal));

            // Load from Membersallrecpit
            string queryReceipts = @"
                SELECT Recp_dt AS Date, Recp_no AS RefNo,
                    CASE WHEN CAST(Recp_dt AS DATE) = '2021-09-30' THEN 'COVID WAVE OFF' ELSE 'RECEIPT' END AS Type,
                    Amount AS Credit, 0 AS Debit
                FROM Membersallrecpit
                WHERE Bcdenroll = @id";
            SqlCommand cmdRec = new SqlCommand(queryReceipts, con);
            cmdRec.Parameters.AddWithValue("@id", memberId);
            SqlDataAdapter daRec = new SqlDataAdapter(cmdRec);
            daRec.Fill(dt);

            // Load from newdelhibar_bill
            string queryBills = @"
                SELECT Daten AS Date, Billno AS RefNo, btype AS Type,
                    CASE WHEN btype = 'SUBSCRIPTION FEE' THEN TRY_CAST(totalamount AS DECIMAL(18,2)) ELSE 0 END AS Credit,
                    CASE WHEN btype <> 'SUBSCRIPTION FEE' THEN TRY_CAST(totalamount AS DECIMAL(18,2)) ELSE 0 END AS Debit
                FROM newdelhibar_bill
                WHERE BCDno = @id AND btype = @btype";
            SqlCommand cmdBill = new SqlCommand(queryBills, con);
            cmdBill.Parameters.AddWithValue("@id", memberId);
            cmdBill.Parameters.AddWithValue("@btype", "SUBSCRIPTION FEE");
            SqlDataAdapter daBill = new SqlDataAdapter(cmdBill);
            daBill.Fill(dt);

            // Add membership monthly subscription fees
            DateTime today = DateTime.Today;
            DateTime loopDate = new DateTime(membershipDate.Year, membershipDate.Month, 1);
            while (loopDate <= today) {
                decimal amount = 100;
                dt.Rows.Add(loopDate, "SUB", "SUBSCRIPTION", 0, amount);
                loopDate = loopDate.AddMonths(1);
            }

            // Sort by Date ASC
            DataView dv = dt.DefaultView;
            dv.Sort = "Date ASC";
            DataTable sortedDt = dv.ToTable();

            // Calculate running balance
            sortedDt.Columns.Add("Balance", typeof(decimal));
            decimal balance = 0;
            foreach (DataRow row in sortedDt.Rows) {
                decimal cr = Convert.ToDecimal(row["Credit"]);
                decimal dr = Convert.ToDecimal(row["Debit"]);
                balance += cr - dr;
                row["Balance"] = balance;
            }

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = sortedDt
            }));
        }
    }

    // --- 15. SEND OTP SMS ---
    private void HandleSendOtp(HttpContext context) {
        string memberId = context.Request.QueryString["member_id"];
        string mobile = context.Request.QueryString["mobile"];

        if (string.IsNullOrEmpty(mobile) && !string.IsNullOrEmpty(memberId)) {
            using (SqlConnection con = new SqlConnection(connectionString)) {
                con.Open();
                string query = "SELECT top 1 Mobile_1 FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@id", memberId);
                object mobObj = cmd.ExecuteScalar();
                if (mobObj != null && mobObj != DBNull.Value) {
                    mobile = mobObj.ToString();
                }
            }
        }

        if (string.IsNullOrEmpty(mobile) || mobile == "0") {
            SendError(context, 400, "Mobile number not found or invalid.");
            return;
        }

        // Generate 4-digit OTP
        string[] saAllowedCharacters = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" };
        string sOTP = "";
        Random rand = new Random();
        for (int i = 0; i < 4; i++) {
            sOTP += saAllowedCharacters[rand.Next(0, saAllowedCharacters.Length)];
        }

        string messag = " NDBA User, Your OTP is " + sOTP + ". Do Not share this with anyone. TEAM NDBA";
        string smsStatusMsg = "";
        try {
            using (WebClient client = new WebClient()) {
                string url = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + mobile + "&text=" + messag + "&route=2";
                smsStatusMsg = client.DownloadString(url);
            }
        }
        catch (Exception ex) {
            SendError(context, 500, "Failed to send SMS: " + ex.Message);
            return;
        }

        context.Response.Write(JsonConvert.SerializeObject(new {
            success = true,
            mobile = mobile,
            otp = sOTP,
            smsResponse = smsStatusMsg
        }));
    }

    // --- 16. POST: INITIATE PAYMENT (CREATE BILL & ORDER) ---
    private void HandleInitiatePayment(HttpContext context) {
        string jsonInput = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) {
            jsonInput = reader.ReadToEnd();
        }

        if (string.IsNullOrEmpty(jsonInput)) {
            SendError(context, 400, "Request body is empty.");
            return;
        }

        dynamic data = JsonConvert.DeserializeObject(jsonInput);
        if (data == null || data.MemberId == null || data.Amount == null) {
            SendError(context, 400, "Required parameters missing. 'MemberId' and 'Amount' must be provided.");
            return;
        }

        string memberId = (string)data.MemberId;
        string amount = (string)data.Amount;
        string particulars = (string)data.Particulars ?? "Member Fee";
        string btype = (string)data.BillType ?? "Member ship";

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

            // Fetch member details
            string query = "SELECT top 1 F_name, M_name, L_name, Mobile_1, Ndbano, Bcdenroll, Resid_1 FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@id", memberId);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count == 0) {
                SendError(context, 404, "Member not found.");
                return;
            }

            DataRow row = dt.Rows[0];
            string name = row["F_name"].ToString() + " " + row["M_name"].ToString() + " " + row["L_name"].ToString();
            string mobile = row["Mobile_1"].ToString();
            string ndbaNo = row["Ndbano"].ToString();
            string bcdNo = row["Bcdenroll"].ToString();
            string address = row["Resid_1"].ToString();

            // Generate unique bill number
            int nextId = 1;
            string idQuery = "SELECT top 1 id FROM newdelhibar_bill ORDER BY id DESC";
            SqlCommand idCmd = new SqlCommand(idQuery, con);
            object lastIdObj = idCmd.ExecuteScalar();
            if (lastIdObj != null && lastIdObj != DBNull.Value) {
                nextId = Convert.ToInt32(lastIdObj) + 1;
            }
            string billNo = nextId.ToString();

            SqlTransaction transaction = con.BeginTransaction();
            try {
                // Insert bill
                string billQuery = @"INSERT INTO newdelhibar_bill (
                    Billno, Name, Mobile, NDBAno, BCDno, Daten, Address, 
                    Headermessage, Paymentmode, Footermessage, totalamount, btype, Status
                ) VALUES (
                    @Billno, @Name, @Mobile, @NDBAno, @BCDno, @Daten, @Address, 
                    'Online Member Fee', 'Online', 'Online Fee', @totalamount, @btype, 'Pending'
                )";
                SqlCommand bCmd = new SqlCommand(billQuery, con, transaction);
                bCmd.Parameters.AddWithValue("@Billno", billNo);
                bCmd.Parameters.AddWithValue("@Name", name);
                bCmd.Parameters.AddWithValue("@Mobile", mobile);
                bCmd.Parameters.AddWithValue("@NDBAno", ndbaNo);
                bCmd.Parameters.AddWithValue("@BCDno", bcdNo);
                bCmd.Parameters.AddWithValue("@Daten", DateTime.Now);
                bCmd.Parameters.AddWithValue("@Address", address);
                bCmd.Parameters.AddWithValue("@totalamount", amount);
                bCmd.Parameters.AddWithValue("@btype", btype);
                bCmd.ExecuteNonQuery();

                // Insert rowbill
                string rowBillQuery = "INSERT INTO newdelhibar_rowbill (Billno, Particulars, Amount) VALUES (@Billno, @Particulars, @Amount)";
                SqlCommand rCmd = new SqlCommand(rowBillQuery, con, transaction);
                rCmd.Parameters.AddWithValue("@Billno", billNo);
                rCmd.Parameters.AddWithValue("@Particulars", particulars);
                rCmd.Parameters.AddWithValue("@Amount", amount);
                rCmd.ExecuteNonQuery();

                // Call Getepay SDK
                string mid = ConfigurationManager.AppSettings["Mid"];
                string terminalId = ConfigurationManager.AppSettings["terminalId"];
                string key = ConfigurationManager.AppSettings["key"];
                string iv = ConfigurationManager.AppSettings["iv"];
                string url = ConfigurationManager.AppSettings["url"];

                GetepayConfig config = new GetepayConfig();
                config.mid = mid;
                config.terminalId = terminalId;
                config.key = key;
                config.iv = iv;
                config.url = url;

                System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;

                GetepayRequest request = new GetepayRequest();
                request.mid = mid;
                request.amount = amount;
                request.merchantTransactionId = billNo;
                request.transactionDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                request.terminalId = terminalId;
                request.udf1 = mobile;
                request.udf2 = "ndba.patialahousecourt@gmail.com";
                request.udf3 = name;
                request.udf4 = "";
                request.udf5 = "";
                request.udf6 = "";
                request.udf7 = "";
                request.udf8 = "";
                request.udf9 = "";
                request.udf10 = url;
                request.ru = "https://newdelhibarassociation.in/Paymentstatus.aspx";
                request.callbackUrl = "";
                request.currency = "INR";
                request.paymentMode = "ALL";
                request.bankId = "";
                request.txnType = "";
                request.productType = "";
                request.txnNote = "";
                request.vpa = "";

                GetepayOrderResponse orderResponse = Getepay.generateRequest(config, request);

                // Update payment ID
                string updateQuery = "UPDATE newdelhibar_bill SET payment_id = @paymentId WHERE Billno = @billNo";
                SqlCommand uCmd = new SqlCommand(updateQuery, con, transaction);
                uCmd.Parameters.AddWithValue("@paymentId", orderResponse.paymentId.ToString());
                uCmd.Parameters.AddWithValue("@billNo", billNo);
                uCmd.ExecuteNonQuery();

                transaction.Commit();

                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    billNo = billNo,
                    paymentId = orderResponse.paymentId,
                    paymentUrl = orderResponse.paymentUrl,
                    amount = amount,
                    status = "Pending"
                }));
            }
            catch (Exception ex) {
                transaction.Rollback();
                throw ex;
            }
        }
    }

    // --- 17. VERIFY PAYMENT (REQUERY & PROCESS DUES UPDATE) ---
    private void HandleVerifyPayment(HttpContext context) {
        string billNo = context.Request.QueryString["bill_no"];
        if (string.IsNullOrEmpty(billNo)) {
            SendError(context, 400, "Parameter 'bill_no' is required.");
            return;
        }

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

            string qrajneeti = "SELECT * FROM newdelhibar_bill WHERE Billno = @billNo";
            SqlCommand cmd = new SqlCommand(qrajneeti, con);
            cmd.Parameters.AddWithValue("@billNo", billNo);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count == 0) {
                SendError(context, 404, "Bill not found.");
                return;
            }

            DataRow billRow = dt.Rows[0];
            string paymentId = billRow["payment_id"].ToString();
            string mobile = billRow["Mobile"].ToString();
            string ndbaNo = billRow["NDBAno"].ToString();
            string amount = billRow["totalamount"].ToString();
            string status = billRow["Status"].ToString();

            if (status == "SUCCESS") {
                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    status = "SUCCESS",
                    billNo = billNo,
                    message = "Payment is already marked as SUCCESS."
                }));
                return;
            }

            // Perform Getepay requery
            string mid = ConfigurationManager.AppSettings["Mid"];
            string terminalId = ConfigurationManager.AppSettings["terminalId"];
            string key = ConfigurationManager.AppSettings["key"];
            string iv = ConfigurationManager.AppSettings["iv"];
            string url = "https://portal.getepay.in:8443/getepayPortal/pg/invoiceStatus";

            GetepayConfig config = new GetepayConfig();
            config.mid = mid;
            config.terminalId = terminalId;
            config.key = key;
            config.iv = iv;
            config.url = url;

            GetepayRequery getepayRequery = new GetepayRequery();
            getepayRequery.paymentId = paymentId;
            getepayRequery.terminalId = terminalId;
            getepayRequery.mid = mid;

            System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
            GetepayRequeryResponse requeryResponse = Getepay.requeryRequest(config, getepayRequery);

            string txnStatus = requeryResponse.txnStatus;

            if (txnStatus == "SUCCESS") {
                SqlTransaction transaction = con.BeginTransaction();
                try {
                    // Update status in bill
                    string updateBill = "UPDATE newdelhibar_bill SET Status = 'SUCCESS' WHERE Billno = @billNo";
                    SqlCommand ubCmd = new SqlCommand(updateBill, con, transaction);
                    ubCmd.Parameters.AddWithValue("@billNo", billNo);
                    ubCmd.ExecuteNonQuery();

                    // Send SMS
                    string messag = "We confirm receipt of online payment made via NDBA for " + amount + " against Membership Renew Bill No: " + billNo + ". - NDBA";
                    try {
                        using (WebClient client = new WebClient()) {
                            string smsUrl = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + mobile + "&text=" + HttpUtility.UrlEncode(messag) + "&route=2";
                            client.DownloadString(smsUrl);
                        }
                    }
                    catch { }

                    // Update Membersduelist
                    string duesQuery = "SELECT * FROM Membersduelist WHERE Ndbano = @ndba ORDER BY id DESC";
                    SqlCommand duesCmd = new SqlCommand(duesQuery, con, transaction);
                    duesCmd.Parameters.AddWithValue("@ndba", ndbaNo);
                    SqlDataAdapter duesDa = new SqlDataAdapter(duesCmd);
                    DataTable duesDt = new DataTable();
                    duesDa.Fill(duesDt);

                    if (duesDt.Rows.Count > 0) {
                        string baseDues = duesDt.Rows[0]["dues"].ToString();
                        DateTime dt2 = Convert.ToDateTime(duesDt.Rows[0]["Ndbadate"].ToString());
                        DateTime dt1 = DateTime.Now.AddDays(-1);

                        int monthCount = dt1.Month - dt2.Month;
                        if (dt1.Year != dt2.Year) {
                            monthCount = ((dt1.Year - dt2.Year - 1) * 12) + (13 - dt2.Month + dt1.Month);
                        }
                        int lateFee = 0;
                        if (monthCount > 0) {
                            lateFee = 100 * monthCount;
                        }

                        int dueson = Convert.ToInt32(baseDues) + lateFee;
                        int paidAmount = Convert.ToInt32(amount);
                        int remainingDues = dueson - paidAmount;

                        if (remainingDues >= 0) {
                            string updue = "UPDATE Membersduelist SET dues = @remainingDues WHERE Ndbano = @ndba";
                            SqlCommand updueCmd = new SqlCommand(updue, con, transaction);
                            updueCmd.Parameters.AddWithValue("@remainingDues", remainingDues);
                            updueCmd.Parameters.AddWithValue("@ndba", ndbaNo);
                            updueCmd.ExecuteNonQuery();
                        }
                        else {
                            int result = remainingDues / 100;
                            int kj = Math.Abs(result);
                            DateTime nextDueDate = dt2.AddMonths(kj + (monthCount > 0 ? monthCount : 0));
                            string updue = "UPDATE Membersduelist SET dues = '0', Ndbadate = @nextDueDate WHERE Ndbano = @ndba";
                            SqlCommand updueCmd = new SqlCommand(updue, con, transaction);
                            updueCmd.Parameters.AddWithValue("@nextDueDate", nextDueDate);
                            updueCmd.Parameters.AddWithValue("@ndba", ndbaNo);
                            updueCmd.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
                catch (Exception ex) {
                    transaction.Rollback();
                    throw ex;
                }
            }
            else {
                string updateBill = "UPDATE newdelhibar_bill SET Status = @status WHERE Billno = @billNo";
                SqlCommand ubCmd = new SqlCommand(updateBill, con);
                ubCmd.Parameters.AddWithValue("@status", txnStatus);
                ubCmd.Parameters.AddWithValue("@billNo", billNo);
                ubCmd.ExecuteNonQuery();
            }

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = (txnStatus == "SUCCESS"),
                status = txnStatus,
                billNo = billNo
            }));
        }
    }

    // --- 18. POST: REQUEST NEW ID CARD / KYC ---
    private void HandleRequestIdCard(HttpContext context) {
        string jsonInput = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) {
            jsonInput = reader.ReadToEnd();
        }

        if (string.IsNullOrEmpty(jsonInput)) {
            SendError(context, 400, "Request body is empty.");
            return;
        }

        dynamic data = JsonConvert.DeserializeObject(jsonInput);
        if (data == null || data.ndba_no == null || data.mobile == null || data.address == null) {
            SendError(context, 400, "Required parameters missing. 'ndba_no', 'mobile', and 'address' are required.");
            return;
        }

        string ndbaNo = (string)data.ndba_no;
        string mobile = (string)data.mobile;
        string address = (string)data.address;
        string photoBase64 = (string)data.photo_base64 ?? "";
        string signatureBase64 = (string)data.signature_base64 ?? ""; // optional

        string photoPath = "";
        if (!string.IsNullOrEmpty(photoBase64)) {
            try {
                // Strip metadata prefix if exists (e.g. "data:image/jpeg;base64,")
                if (photoBase64.Contains(",")) {
                    photoBase64 = photoBase64.Substring(photoBase64.IndexOf(",") + 1);
                }
                byte[] imageBytes = Convert.FromBase64String(photoBase64);
                string fileName = DateTime.Now.Ticks.ToString() + ".jpg";
                string relativePath = "dataimp1/" + fileName;
                string physicalPath = context.Server.MapPath("~/" + relativePath);
                
                // Ensure directory exists
                string dir = Path.GetDirectoryName(physicalPath);
                if (!Directory.Exists(dir)) {
                    Directory.CreateDirectory(dir);
                }

                File.WriteAllBytes(physicalPath, imageBytes);
                photoPath = "../" + relativePath; // Same relative path structure as newidcard.aspx.cs
            }
            catch (Exception ex) {
                SendError(context, 500, "Failed to upload photo: " + ex.Message);
                return;
            }
        }

        using (SqlConnection conn = new SqlConnection(connectionString)) {
            conn.Open();
            
            // First check if member exists with matching ndbaNo and mobile
            string checkQuery = "SELECT count(*) FROM Memberslist WHERE Ndbano = @ndbaNo AND Mobile_1 = @mobile";
            SqlCommand checkCmd = new SqlCommand(checkQuery, conn);
            checkCmd.Parameters.AddWithValue("@ndbaNo", ndbaNo);
            checkCmd.Parameters.AddWithValue("@mobile", mobile);
            int count = (int)checkCmd.ExecuteScalar();

            if (count == 0) {
                SendError(context, 404, "Member not found with matching NDBA No and Mobile number.");
                return;
            }

            string updateQuery = "";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            if (!string.IsNullOrEmpty(photoPath)) {
                updateQuery = "UPDATE Memberslist SET I_card_no = 'New', Photo = @Photo, Resid_1 = @Resid_1 WHERE Ndbano = @ndbaNo AND Mobile_1 = @mobile";
                cmd.Parameters.AddWithValue("@Photo", photoPath);
            } else {
                updateQuery = "UPDATE Memberslist SET I_card_no = 'New', Resid_1 = @Resid_1 WHERE Ndbano = @ndbaNo AND Mobile_1 = @mobile";
            }

            cmd.CommandText = updateQuery;
            cmd.Parameters.AddWithValue("@Resid_1", address);
            cmd.Parameters.AddWithValue("@ndbaNo", ndbaNo);
            cmd.Parameters.AddWithValue("@mobile", mobile);
            cmd.ExecuteNonQuery();
        }

        context.Response.Write(JsonConvert.SerializeObject(new {
            success = true,
            message = "ID Card / KYC Request updated successfully."
        }));
    }

    // --- MEMBER LOGIN ---
    private void HandleMemberLogin(HttpContext context) {
        string id = context.Request.QueryString["id"];
        string mobile = context.Request.QueryString["mobile"];

        if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(mobile)) {
            SendError(context, 400, "Parameters 'id' (NDBA/BCD) and 'mobile' are required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            // Fetch profile info matching either Bcdenroll or Ndbano, and Mobile_1
            string query = "SELECT * FROM Memberslist WHERE (Bcdenroll = @id OR Ndbano = @id) AND Mobile_1 = @mobile";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@mobile", mobile);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count == 0) {
                SendError(context, 404, "Invalid Member ID or Mobile Number.");
                return;
            }

            DataRow memberRow = dt.Rows[0];
            
            // Format profile data
            var profile = new Dictionary<string, object>();
            profile["F_name"] = memberRow["F_name"];
            profile["M_name"] = memberRow["M_name"];
            profile["L_name"] = memberRow["L_name"];
            profile["Bcdenroll"] = memberRow["Bcdenroll"];
            profile["Ndbano"] = memberRow["Ndbano"];
            profile["Mobile_1"] = memberRow["Mobile_1"];
            profile["Photo"] = memberRow["Photo"];
            profile["sstatus"] = memberRow["sstatus"] != DBNull.Value && !string.IsNullOrEmpty(memberRow["sstatus"].ToString()) ? memberRow["sstatus"] : "Active";
            
            // Determine Validity (check Ndbadate, default to "Valid - Lifetime" or "Dec 31, 2030")
            string validity = "Valid - Lifetime";
            if (memberRow.Table.Columns.Contains("Ndbadate") && memberRow["Ndbadate"] != DBNull.Value) {
                DateTime ndbaDate;
                if (DateTime.TryParse(memberRow["Ndbadate"].ToString(), out ndbaDate)) {
                    validity = "Valid until " + ndbaDate.AddYears(10).ToString("dd-MMM-yyyy");
                }
            }
            profile["validity"] = validity;

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                member = profile
            }));
        }
    }

    // --- VIRTUAL COURT HEARINGS ---
    private void HandleVirtualCourtHearings(HttpContext context) {
        string memberId = context.Request.QueryString["member_id"];
        
        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            // Fetch court hearings where Bcdenroll matches member's enrollment, OR Bcdenroll is NULL/empty
            string query = "";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            if (string.IsNullOrEmpty(memberId)) {
                // Return only public hearings if no member_id provided
                query = "SELECT pkid, CourtNumber, JudgeName, ScheduledTime, WebexLink, Bcdenroll FROM newdelhibar_VirtualCourtHearings WHERE Bcdenroll IS NULL OR Bcdenroll = '' ORDER BY ScheduledTime ASC";
            } else {
                // Return public hearings + hearings mapped to this member's Bcdenroll
                query = "SELECT pkid, CourtNumber, JudgeName, ScheduledTime, WebexLink, Bcdenroll FROM newdelhibar_VirtualCourtHearings WHERE Bcdenroll IS NULL OR Bcdenroll = '' OR Bcdenroll = @memberId ORDER BY ScheduledTime ASC";
                cmd.Parameters.AddWithValue("@memberId", memberId);
            }

            cmd.CommandText = query;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- DYNAMIC ERROR SENDER ---
    private void SendError(HttpContext context, int statusCode, string message) {
        context.Response.StatusCode = statusCode;
        context.Response.Write(JsonConvert.SerializeObject(new { 
            success = false, 
            error = message 
        }));
    }

    // --- MEMBER LOGIN WITH PASSWORD ---
    private void HandleMemberLoginPassword(HttpContext context) {
        string id = context.Request.QueryString["id"];
        string password = context.Request.QueryString["password"];

        if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(password)) {
            SendError(context, 400, "Parameters 'id' and 'password' are required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = "SELECT * FROM Memberslist WHERE (Bcdenroll = @id OR Ndbano = @id OR Mobile_1 = @id OR Email_id = @id) AND password = @password";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@password", password);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count == 0) {
                SendError(context, 401, "Invalid credentials.");
                return;
            }

            DataRow memberRow = dt.Rows[0];
            var profile = new Dictionary<string, object>();
            profile["F_name"] = memberRow["F_name"];
            profile["M_name"] = memberRow["M_name"];
            profile["L_name"] = memberRow["L_name"];
            profile["Bcdenroll"] = memberRow["Bcdenroll"];
            profile["Ndbano"] = memberRow["Ndbano"];
            profile["Mobile_1"] = memberRow["Mobile_1"];
            profile["Photo"] = memberRow["Photo"];
            profile["sstatus"] = memberRow["sstatus"] != DBNull.Value && !string.IsNullOrEmpty(memberRow["sstatus"].ToString()) ? memberRow["sstatus"] : "Active";
            
            string validity = "Valid - Lifetime";
            if (memberRow.Table.Columns.Contains("Ndbadate") && memberRow["Ndbadate"] != DBNull.Value) {
                DateTime ndbaDate;
                if (DateTime.TryParse(memberRow["Ndbadate"].ToString(), out ndbaDate)) {
                    validity = "Valid until " + ndbaDate.AddYears(10).ToString("dd-MMM-yyyy");
                }
            }
            profile["validity"] = validity;

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                member = profile
            }));
        }
    }

    // --- MEMBER CHANGE PASSWORD ---
    private void HandleChangePassword(HttpContext context) {
        string id = context.Request.QueryString["id"] ?? context.Request.Form["id"];
        string newPassword = context.Request.QueryString["new_password"] ?? context.Request.Form["new_password"];

        if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(newPassword)) {
            SendError(context, 400, "Parameters 'id' and 'new_password' are required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            // First verify if the member exists
            string checkQuery = "SELECT COUNT(*) FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id";
            SqlCommand checkCmd = new SqlCommand(checkQuery, con);
            checkCmd.Parameters.AddWithValue("@id", id);
            int count = (int)checkCmd.ExecuteScalar();

            if (count == 0) {
                SendError(context, 404, "Member not found.");
                return;
            }

            // Update password
            string updateQuery = "UPDATE Memberslist SET password = @new_password WHERE Bcdenroll = @id OR Ndbano = @id";
            SqlCommand updateCmd = new SqlCommand(updateQuery, con);
            updateCmd.Parameters.AddWithValue("@id", id);
            updateCmd.Parameters.AddWithValue("@new_password", newPassword);
            
            int rows = updateCmd.ExecuteNonQuery();
            if (rows > 0) {
                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    message = "Password changed successfully."
                }));
            } else {
                SendError(context, 500, "Failed to update password.");
            }
        }
    }

    // --- MEMBER REGISTER ---
    private void HandleMemberRegister(HttpContext context) {
        string ndbaNo = context.Request.QueryString["ndba_no"];
        string bcdEnroll = context.Request.QueryString["bcd_enroll"];
        string prefix = context.Request.QueryString["prefix"] ?? "Mr.";
        string fName = context.Request.QueryString["f_name"];
        string mName = context.Request.QueryString["m_name"] ?? "";
        string lName = context.Request.QueryString["l_name"];
        string fatherName = context.Request.QueryString["father_name"];
        string mobile = context.Request.QueryString["mobile"];
        string email = context.Request.QueryString["email"];
        string password = context.Request.QueryString["password"];

        if (string.IsNullOrEmpty(ndbaNo) || string.IsNullOrEmpty(bcdEnroll) || 
            string.IsNullOrEmpty(fName) || string.IsNullOrEmpty(lName) || 
            string.IsNullOrEmpty(fatherName) || string.IsNullOrEmpty(mobile) || 
            string.IsNullOrEmpty(email) || string.IsNullOrEmpty(password)) {
            SendError(context, 400, "All required fields must be supplied.");
            return;
        }

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

            // Check if already exists
            string checkQuery = "SELECT COUNT(*) FROM Memberslist WHERE Bcdenroll = @bcd OR Ndbano = @ndba";
            SqlCommand checkCmd = new SqlCommand(checkQuery, con);
            checkCmd.Parameters.AddWithValue("@bcd", bcdEnroll);
            checkCmd.Parameters.AddWithValue("@ndba", ndbaNo);
            int count = (int)checkCmd.ExecuteScalar();

            if (count > 0) {
                SendError(context, 409, "A member with this BCD Enrollment Number or NDBA Number is already registered.");
                return;
            }

            string insertQuery = @"INSERT INTO Memberslist (
                Ndbano, Bcdenroll, Prefix, F_name, M_name, L_name, Father_name, Mobile_1, Email_id, password
            ) VALUES (
                @Ndbano, @Bcdenroll, @Prefix, @F_name, @M_name, @L_name, @Father_name, @Mobile_1, @Email_id, @password
            )";

            SqlCommand insertCmd = new SqlCommand(insertQuery, con);
            insertCmd.Parameters.AddWithValue("@Ndbano", ndbaNo);
            insertCmd.Parameters.AddWithValue("@Bcdenroll", bcdEnroll);
            insertCmd.Parameters.AddWithValue("@Prefix", prefix);
            insertCmd.Parameters.AddWithValue("@F_name", fName);
            insertCmd.Parameters.AddWithValue("@M_name", mName);
            insertCmd.Parameters.AddWithValue("@L_name", lName);
            insertCmd.Parameters.AddWithValue("@Father_name", fatherName);
            insertCmd.Parameters.AddWithValue("@Mobile_1", mobile);
            insertCmd.Parameters.AddWithValue("@Email_id", email);
            insertCmd.Parameters.AddWithValue("@password", password);

            int rows = insertCmd.ExecuteNonQuery();
            if (rows > 0) {
                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    message = "Member registered successfully."
                }));
            } else {
                SendError(context, 500, "Failed to register member.");
            }
        }
    }

    // --- CASE MODULE HELPER: self-healing table creation ---
    private void EnsureTablesCreated() {
        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string sql = @"
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND type in (N'U'))
            BEGIN
                CREATE TABLE [dbo].[Cases] (
                    [Id] INT IDENTITY(1,1) PRIMARY KEY,
                    [CaseSubject] NVARCHAR(255) NOT NULL,
                    [CaseDescription] NVARCHAR(MAX) NULL,
                    [ClientName] NVARCHAR(255) NOT NULL,
                    [ClientAddress] NVARCHAR(MAX) NULL,
                    [ClientPhone] NVARCHAR(50) NULL,
                    [ClientEmail] NVARCHAR(255) NULL,
                    [CaseCategory] NVARCHAR(100) NULL,
                    [LawyerId] NVARCHAR(MAX) NOT NULL,
                    [LawyerName] NVARCHAR(MAX) NOT NULL,
                    [CaseStatus] NVARCHAR(50) NOT NULL DEFAULT 'Open',
                    [CaseStartDate] DATETIME NULL,
                    [CaseCloserDate] DATETIME NULL,
                    [NextFollowUpDate] DATETIME NULL,
                    [CreatedBy] NVARCHAR(100) NOT NULL,
                    [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(),
                    [UpdatedAt] DATETIME NOT NULL DEFAULT GETDATE()
                );
            END
            
            IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'LawyerId')
            BEGIN
                IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cases' AND COLUMN_NAME = 'LawyerId') != -1
                BEGIN
                    ALTER TABLE [dbo].[Cases] ALTER COLUMN [LawyerId] NVARCHAR(MAX) NOT NULL;
                END
            END
            
            IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'LawyerName')
            BEGIN
                IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cases' AND COLUMN_NAME = 'LawyerName') != -1
                BEGIN
                    ALTER TABLE [dbo].[Cases] ALTER COLUMN [LawyerName] NVARCHAR(MAX) NOT NULL;
                END
            END

            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CaseComments]') AND type in (N'U'))
            BEGIN
                CREATE TABLE [dbo].[CaseComments] (
                    [Id] INT IDENTITY(1,1) PRIMARY KEY,
                    [CaseId] INT NOT NULL,
                    [CommentText] NVARCHAR(MAX) NOT NULL,
                    [AuthorId] NVARCHAR(100) NOT NULL,
                    [AuthorName] NVARCHAR(255) NOT NULL,
                    [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(),
                    CONSTRAINT [FK_CaseComments_Cases] FOREIGN KEY ([CaseId]) REFERENCES [dbo].[Cases]([Id]) ON DELETE CASCADE
                );
            END

            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CaseFiles]') AND type in (N'U'))
            BEGIN
                CREATE TABLE [dbo].[CaseFiles] (
                    [Id] INT IDENTITY(1,1) PRIMARY KEY,
                    [CaseId] INT NOT NULL,
                    [FileName] NVARCHAR(255) NOT NULL,
                    [FilePath] NVARCHAR(MAX) NOT NULL,
                    [UploadedBy] NVARCHAR(100) NOT NULL,
                    [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(),
                    CONSTRAINT [FK_CaseFiles_Cases] FOREIGN KEY ([CaseId]) REFERENCES [dbo].[Cases]([Id]) ON DELETE CASCADE
                );
            END";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.ExecuteNonQuery();
        }
    }

    // --- SEARCH LAWYERS ---
    private void HandleSearchLawyers(HttpContext context) {
        string search = context.Request.QueryString["search"];
        if (string.IsNullOrEmpty(search)) {
            search = "";
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = @"SELECT TOP 20 Bcdenroll, F_name, M_name, L_name, Mobile_1, Ndbano 
                             FROM Memberslist 
                             WHERE F_name LIKE @search 
                                OR L_name LIKE @search 
                                OR Bcdenroll LIKE @search 
                             ORDER BY F_name ASC";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@search", "%" + search + "%");
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- CREATE CASE ---
    private void HandleCaseCreate(HttpContext context) {
        EnsureTablesCreated();

        string json = "";
        using (var reader = new StreamReader(context.Request.InputStream)) {
            json = reader.ReadToEnd();
        }

        dynamic data = JsonConvert.DeserializeObject(json);
        if (data == null) {
            SendError(context, 400, "Invalid or empty request body.");
            return;
        }

        string caseSubject = data.CaseSubject;
        string caseDescription = data.CaseDescription;
        string clientName = data.ClientName;
        string clientAddress = data.ClientAddress;
        string clientPhone = data.ClientPhone;
        string clientEmail = data.ClientEmail;
        string caseCategory = data.CaseCategory;
        string lawyerId = data.LawyerId;
        string lawyerName = data.LawyerName;
        string caseStatus = data.CaseStatus ?? "Open";
        string caseStartDateStr = data.CaseStartDate;
        string caseCloserDateStr = data.CaseCloserDate;
        string nextFollowUpDateStr = data.NextFollowUpDate;
        string createdBy = data.CreatedBy;

        if (string.IsNullOrEmpty(caseSubject) || string.IsNullOrEmpty(clientName) || string.IsNullOrEmpty(lawyerId) || string.IsNullOrEmpty(createdBy)) {
            SendError(context, 400, "Required fields: CaseSubject, ClientName, LawyerId, and CreatedBy.");
            return;
        }

        DateTime? caseStartDate = null;
        DateTime? caseCloserDate = null;
        DateTime? nextFollowUpDate = null;

        DateTime tempDate;
        if (!string.IsNullOrEmpty(caseStartDateStr) && DateTime.TryParse(caseStartDateStr, out tempDate)) caseStartDate = tempDate;
        if (!string.IsNullOrEmpty(caseCloserDateStr) && DateTime.TryParse(caseCloserDateStr, out tempDate)) caseCloserDate = tempDate;
        if (!string.IsNullOrEmpty(nextFollowUpDateStr) && DateTime.TryParse(nextFollowUpDateStr, out tempDate)) nextFollowUpDate = tempDate;

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = @"INSERT INTO Cases (CaseSubject, CaseDescription, ClientName, ClientAddress, ClientPhone, ClientEmail, 
                                               CaseCategory, LawyerId, LawyerName, CaseStatus, CaseStartDate, CaseCloserDate, NextFollowUpDate, CreatedBy)
                             VALUES (@CaseSubject, @CaseDescription, @ClientName, @ClientAddress, @ClientPhone, @ClientEmail, 
                                     @CaseCategory, @LawyerId, @LawyerName, @CaseStatus, @CaseStartDate, @CaseCloserDate, @NextFollowUpDate, @CreatedBy);
                             SELECT SCOPE_IDENTITY();";

            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@CaseSubject", caseSubject);
            cmd.Parameters.AddWithValue("@CaseDescription", (object)caseDescription ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ClientName", clientName);
            cmd.Parameters.AddWithValue("@ClientAddress", (object)clientAddress ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ClientPhone", (object)clientPhone ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ClientEmail", (object)clientEmail ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@CaseCategory", (object)caseCategory ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@LawyerId", lawyerId);
            cmd.Parameters.AddWithValue("@LawyerName", lawyerName);
            cmd.Parameters.AddWithValue("@CaseStatus", caseStatus);
            cmd.Parameters.AddWithValue("@CaseStartDate", (object)caseStartDate ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@CaseCloserDate", (object)caseCloserDate ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@NextFollowUpDate", (object)nextFollowUpDate ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@CreatedBy", createdBy);

            object newId = cmd.ExecuteScalar();

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                message = "Case created successfully.",
                case_id = Convert.ToInt32(newId)
            }));
        }
    }

    // --- UPDATE CASE ---
    private void HandleCaseUpdate(HttpContext context) {
        EnsureTablesCreated();

        string json = "";
        using (var reader = new StreamReader(context.Request.InputStream)) {
            json = reader.ReadToEnd();
        }

        dynamic data = JsonConvert.DeserializeObject(json);
        if (data == null) {
            SendError(context, 400, "Invalid or empty request body.");
            return;
        }

        int caseId = 0;
        if (data.CaseId == null || !int.TryParse(data.CaseId.ToString(), out caseId)) {
            SendError(context, 400, "CaseId is required and must be an integer.");
            return;
        }

        string caseSubject = data.CaseSubject;
        string caseDescription = data.CaseDescription;
        string clientName = data.ClientName;
        string clientAddress = data.ClientAddress;
        string clientPhone = data.ClientPhone;
        string clientEmail = data.ClientEmail;
        string caseCategory = data.CaseCategory;
        string lawyerId = data.LawyerId;
        string lawyerName = data.LawyerName;
        string caseStatus = data.CaseStatus;
        string caseStartDateStr = data.CaseStartDate;
        string caseCloserDateStr = data.CaseCloserDate;
        string nextFollowUpDateStr = data.NextFollowUpDate;

        DateTime? caseStartDate = null;
        DateTime? caseCloserDate = null;
        DateTime? nextFollowUpDate = null;

        DateTime tempDate;
        if (!string.IsNullOrEmpty(caseStartDateStr) && DateTime.TryParse(caseStartDateStr, out tempDate)) caseStartDate = tempDate;
        if (!string.IsNullOrEmpty(caseCloserDateStr) && DateTime.TryParse(caseCloserDateStr, out tempDate)) caseCloserDate = tempDate;
        if (!string.IsNullOrEmpty(nextFollowUpDateStr) && DateTime.TryParse(nextFollowUpDateStr, out tempDate)) nextFollowUpDate = tempDate;

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = @"UPDATE Cases 
                             SET CaseSubject = @CaseSubject, CaseDescription = @CaseDescription, ClientName = @ClientName, 
                                 ClientAddress = @ClientAddress, ClientPhone = @ClientPhone, ClientEmail = @ClientEmail, 
                                 CaseCategory = @CaseCategory, LawyerId = @LawyerId, LawyerName = @LawyerName, 
                                 CaseStatus = @CaseStatus, CaseStartDate = @CaseStartDate, CaseCloserDate = @CaseCloserDate, 
                                 NextFollowUpDate = @NextFollowUpDate, UpdatedAt = GETDATE()
                             WHERE Id = @CaseId";

            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@CaseId", caseId);
            cmd.Parameters.AddWithValue("@CaseSubject", caseSubject);
            cmd.Parameters.AddWithValue("@CaseDescription", (object)caseDescription ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ClientName", clientName);
            cmd.Parameters.AddWithValue("@ClientAddress", (object)clientAddress ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ClientPhone", (object)clientPhone ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ClientEmail", (object)clientEmail ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@CaseCategory", (object)caseCategory ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@LawyerId", lawyerId);
            cmd.Parameters.AddWithValue("@LawyerName", lawyerName);
            cmd.Parameters.AddWithValue("@CaseStatus", caseStatus);
            cmd.Parameters.AddWithValue("@CaseStartDate", (object)caseStartDate ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@CaseCloserDate", (object)caseCloserDate ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@NextFollowUpDate", (object)nextFollowUpDate ?? DBNull.Value);

            int rows = cmd.ExecuteNonQuery();
            if (rows > 0) {
                context.Response.Write(JsonConvert.SerializeObject(new {
                    success = true,
                    message = "Case updated successfully."
                }));
            } else {
                SendError(context, 404, "Case not found.");
            }
        }
    }

    // --- CASE LIST ---
    private void HandleCaseList(HttpContext context) {
        EnsureTablesCreated();

        string lawyerId = context.Request.QueryString["lawyer_id"];
        string search = context.Request.QueryString["search"];

        if (string.IsNullOrEmpty(lawyerId)) {
            SendError(context, 400, "Parameter 'lawyer_id' is required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = "";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            if (string.IsNullOrEmpty(search)) {
                query = @"SELECT * FROM Cases WHERE (LawyerId = @lawyerId OR ',' + LawyerId + ',' LIKE '%,' + @lawyerId + ',%') ORDER BY UpdatedAt DESC";
                cmd.Parameters.AddWithValue("@lawyerId", lawyerId);
            } else {
                query = @"SELECT * FROM Cases 
                          WHERE (LawyerId = @lawyerId OR ',' + LawyerId + ',' LIKE '%,' + @lawyerId + ',%')
                            AND (CaseSubject LIKE @search 
                              OR ClientName LIKE @search 
                              OR ClientPhone LIKE @search 
                              OR CaseCategory LIKE @search 
                              OR CaseStatus LIKE @search)
                          ORDER BY UpdatedAt DESC";
                cmd.Parameters.AddWithValue("@lawyerId", lawyerId);
                cmd.Parameters.AddWithValue("@search", "%" + search + "%");
            }

            cmd.CommandText = query;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                data = dt
            }));
        }
    }

    // --- CASE DETAILS ---
    private void HandleCaseDetails(HttpContext context) {
        EnsureTablesCreated();

        string caseIdStr = context.Request.QueryString["case_id"];
        int caseId = 0;
        if (string.IsNullOrEmpty(caseIdStr) || !int.TryParse(caseIdStr, out caseId)) {
            SendError(context, 400, "Parameter 'case_id' is required and must be an integer.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string caseQuery = "SELECT * FROM Cases WHERE Id = @CaseId";
            SqlCommand caseCmd = new SqlCommand(caseQuery, con);
            caseCmd.Parameters.AddWithValue("@CaseId", caseId);
            SqlDataAdapter caseDa = new SqlDataAdapter(caseCmd);
            DataTable caseDt = new DataTable();
            caseDa.Fill(caseDt);

            if (caseDt.Rows.Count == 0) {
                SendError(context, 404, "Case not found.");
                return;
            }

            DataRow caseRow = caseDt.Rows[0];

            string commentsQuery = "SELECT * FROM CaseComments WHERE CaseId = @CaseId ORDER BY CreatedAt DESC";
            SqlCommand commentsCmd = new SqlCommand(commentsQuery, con);
            commentsCmd.Parameters.AddWithValue("@CaseId", caseId);
            SqlDataAdapter commentsDa = new SqlDataAdapter(commentsCmd);
            DataTable commentsDt = new DataTable();
            commentsDa.Fill(commentsDt);

            string filesQuery = "SELECT * FROM CaseFiles WHERE CaseId = @CaseId ORDER BY CreatedAt DESC";
            SqlCommand filesCmd = new SqlCommand(filesQuery, con);
            filesCmd.Parameters.AddWithValue("@CaseId", caseId);
            SqlDataAdapter filesDa = new SqlDataAdapter(filesCmd);
            DataTable filesDt = new DataTable();
            filesDa.Fill(filesDt);

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                @case = DataRowToDictionary(caseRow),
                comments = commentsDt,
                files = filesDt
            }));
        }
    }

    // --- ADD COMMENT ---
    private void HandleCaseCommentAdd(HttpContext context) {
        EnsureTablesCreated();

        string json = "";
        using (var reader = new StreamReader(context.Request.InputStream)) {
            json = reader.ReadToEnd();
        }

        dynamic data = JsonConvert.DeserializeObject(json);
        if (data == null) {
            SendError(context, 400, "Invalid or empty request body.");
            return;
        }

        int caseId = 0;
        if (data.CaseId == null || !int.TryParse(data.CaseId.ToString(), out caseId)) {
            SendError(context, 400, "CaseId is required and must be an integer.");
            return;
        }

        string commentText = data.CommentText;
        string authorId = data.AuthorId;
        string authorName = data.AuthorName;

        if (string.IsNullOrEmpty(commentText) || string.IsNullOrEmpty(authorId) || string.IsNullOrEmpty(authorName)) {
            SendError(context, 400, "CommentText, AuthorId, and AuthorName are required.");
            return;
        }

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = @"INSERT INTO CaseComments (CaseId, CommentText, AuthorId, AuthorName)
                             VALUES (@CaseId, @CommentText, @AuthorId, @AuthorName);
                             UPDATE Cases SET UpdatedAt = GETDATE() WHERE Id = @CaseId;";

            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@CaseId", caseId);
            cmd.Parameters.AddWithValue("@CommentText", commentText);
            cmd.Parameters.AddWithValue("@AuthorId", authorId);
            cmd.Parameters.AddWithValue("@AuthorName", authorName);

            cmd.ExecuteNonQuery();

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                message = "Comment added successfully."
            }));
        }
    }

    // --- UPLOAD FILE ---
    private void HandleCaseFileUpload(HttpContext context) {
        EnsureTablesCreated();

        if (context.Request.Files.Count == 0) {
            SendError(context, 400, "No file uploaded.");
            return;
        }

        string caseIdStr = context.Request.Form["case_id"];
        string uploadedBy = context.Request.Form["uploaded_by"];

        int caseId = 0;
        if (string.IsNullOrEmpty(caseIdStr) || !int.TryParse(caseIdStr, out caseId)) {
            SendError(context, 400, "Parameter 'case_id' is required and must be an integer.");
            return;
        }

        if (string.IsNullOrEmpty(uploadedBy)) {
            SendError(context, 400, "Parameter 'uploaded_by' is required.");
            return;
        }

        HttpPostedFile file = context.Request.Files[0];
        if (file.ContentLength == 0) {
            SendError(context, 400, "Uploaded file is empty.");
            return;
        }

        string fileName = Path.GetFileName(file.FileName);
        string folderPath = context.Server.MapPath("~/Uploads/Cases/");
        if (!Directory.Exists(folderPath)) {
            Directory.CreateDirectory(folderPath);
        }

        string uniqueFileName = Guid.NewGuid().ToString() + "_" + fileName;
        string relativePath = "Uploads/Cases/" + uniqueFileName;
        string fullPath = Path.Combine(folderPath, uniqueFileName);

        file.SaveAs(fullPath);

        using (SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            string query = @"INSERT INTO CaseFiles (CaseId, FileName, FilePath, UploadedBy)
                             VALUES (@CaseId, @FileName, @FilePath, @UploadedBy);
                             UPDATE Cases SET UpdatedAt = GETDATE() WHERE Id = @CaseId;";

            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@CaseId", caseId);
            cmd.Parameters.AddWithValue("@FileName", fileName);
            cmd.Parameters.AddWithValue("@FilePath", relativePath);
            cmd.Parameters.AddWithValue("@UploadedBy", uploadedBy);

            cmd.ExecuteNonQuery();

            context.Response.Write(JsonConvert.SerializeObject(new {
                success = true,
                message = "File uploaded successfully.",
                file_name = fileName,
                file_path = relativePath
            }));
        }
    }

    private Dictionary<string, object> DataRowToDictionary(DataRow row) {
        if (row == null) return null;
        var dict = new Dictionary<string, object>();
        foreach (DataColumn col in row.Table.Columns) {
            dict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
        }
        return dict;
    }

    public bool IsReusable {
        get { return false; }
    }
}