from fastapi import FastAPI, File, UploadFile, HTTPException, Request
from fastapi.responses import JSONResponse
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import FileResponse 
from pydantic import BaseModel
from operations.upload_data import upload_data 
from operations.upload_data import upload_lawyer_data
from operations.upload_data import update_lawyer_data
from models.connect import connect_db
from typing import List, Dict
from datetime import datetime
import aiofiles
import bcrypt
import hashlib
import hmac
import os
import zipfile
import mysql.connector
from pathlib import Path
import logging
# import jsonify
# import paramiko
import pandas as pd
from passlib.context import CryptContext

# Create a CryptContext for password hashing
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
app = FastAPI()

origins = ["*"]

app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Pydantic model for request body
class LoginRequest(BaseModel):
    email: str
    password: str

@app.get("/")
async def read_root():
    return {"message": "Welcome to the Legal Notice Generator API"}

def hash_password(password: str) -> str:
    """Hashes the password using SHA-256."""
    return hashlib.sha256(password.encode('utf-8')).hexdigest()

def verify_password(provided_password: str, stored_password_hash: str) -> bool:
    """Verifies the provided password against the stored hash."""
    return hmac.compare_digest(hash_password(provided_password), stored_password_hash)

@app.post("/login")
async def login(request: LoginRequest):
    try:
        # Connect to the database
        connection = await connect_db()
        cursor = connection.cursor(dictionary=True)
        cursor.execute("SELECT password FROM users WHERE email = %s", (request.email,))
        result = cursor.fetchone()
        # return result['password']
        if result is None:
            return {"message": "Invalid email or password", "status_code": 401}

        stored_hashed_password = result['password']

        # Check if the provided password matches the stored hashed password
        if not bcrypt.checkpw(request.password.encode('utf-8'), stored_hashed_password.encode('utf-8')):
            # raise HTTPException(status_code=400, detail="Invalid email or password")
            return {"message": "Invalid email or password", "status_code": 401}

        # If password matches, return a success message or token
        return {"message": "Login successful", "status_code": 200}
    
    except mysql.connector.Error as err:
        raise HTTPException(status_code=500, detail=f"Database error: {err}")
    
    finally:
        # Close the connection
        if connection.is_connected():
            cursor.close()
            connection.close()

# Function to convert DataFrame to serializable format
# def convert_dates_to_string(data):
#     for record in data:
#         for key, value in record.items():
#             if isinstance(value, datetime):
#                 record[key] = value.isoformat()  # Convert datetime to ISO 8601 string
#     return data

def convert_dates_to_string(data):
    if isinstance(data, list):
        for record in data:
            if isinstance(record, dict):
                for key, value in record.items():
                    if isinstance(value, datetime):
                        record[key] = value.isoformat()
    elif isinstance(data, dict):
        for key, value in data.items():
            if isinstance(value, datetime):
                data[key] = value.isoformat()
    return data

# Upload File API
@app.post("/upload_file")
async def upload_file(request: Request, file: UploadFile = File(...)):
    # return "fff"
    try:
        # Read file content
        file_content = await file.read()

        # Define the object name 
        object_name = file.filename 

        # Parse form-data
        form_data = await request.form()
        file_name = form_data.get('file_name')
        company_id = form_data.get('company_id')
        notice_type = form_data.get('notice_type')
        lawyer_id = form_data.get('lawyer_id')
        # return directory_path
        
        # Upload the file to the local directory and database
        success = await upload_data(file_content, object_name, file_name, notice_type, company_id, lawyer_id)  # Await the asynchronous function
       
        if success:
            return JSONResponse(content={"filename": file.filename, "message": "File uploaded successfully"})
        else:
            raise HTTPException(status_code=500, detail="Failed to upload file")

    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error processing file: {e}")

# File List API    
@app.get("/get_file_list")
async def getFileList():
    conn = await connect_db()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT fl.*, c.name, count(n.id) as notice_count, l.name as lawyer_name    
        FROM file_list fl
        LEFT JOIN companies c ON fl.company_id = c.id
        LEFT JOIN notice_list n on fl.id = n.file_list_id
        LEFT JOIN lawyers l on fl.lawyer_id = l.id
        WHERE fl.is_deleted != TRUE
        # AND n.is_deleted != TRUE
        GROUP BY 
        fl.id
        ORDER BY fl.id DESC
    """)    
    result = cursor.fetchall()

    conn.close()  # Ensure to close the connection
    # return result 
    # Convert result to a list of dictionaries with proper JSON serialization
    file_list = []
    for row in result:
        
        file_dict = {
            "id": row[0],
            "file_path":row[1],
            "company": row[9],
            "lawyer_name": row[11],
            "file_name": row[3],
            "notice_type": row[4],
            "total_records": row[10],
            "created_at": row[5].isoformat() if isinstance(row[5], datetime) else row[5],
            "updated_at": row[6].isoformat() if isinstance(row[6], datetime) else row[6]
        }
        file_list.append(file_dict)
    
    return JSONResponse(content={"file_data": file_list})

# Notice List API
@app.post("/get_notice_list")
async def getNoticeList(request: Request):
   
    conn = await connect_db()
    cursor = conn.cursor()
    body = await request.json()
    file_list_id = body.get('file_list_id')
    query = """
            SELECT n.*, f.file_name, c.name as company_name
            FROM notice_list n
            LEFT JOIN file_list f ON n.file_list_id = f.id
            LEFT JOIN companies c ON c.id = f.company_id
            WHERE n.is_deleted != TRUE
            AND f.id = %s
            
            """
    cursor.execute(query, (file_list_id,))
    result = cursor.fetchall()
    # return result
    # Get column names from cursor.description
    column_names = [desc[0] for desc in cursor.description]
    
    # Create a DataFrame from the result
    df = pd.DataFrame(result, columns=column_names)
        
    # Convert the DataFrame to a JSON-compatible format
    data = df.to_dict(orient='records')
    return {'notice_list':data}

# Notice List Data API
@app.post("/get_notice_list_data")
async def getNoticeListData(request: Request):
    conn = await connect_db()
    cursor = conn.cursor()
    body = await request.json()
    notice_list_id = body.get('notice_list_id')
    if not notice_list_id:
        raise HTTPException(status_code=400, detail="Notice list ID is required")
    query = """
            SELECT nld.*, fl.file_name
            FROM notice_list_data nld
            LEFT JOIN file_list fl ON nld.file_list_id = fl.id
            WHERE nld.is_deleted != TRUE AND nld.notice_list_id = %s;
            """
    cursor.execute(query, (notice_list_id,))
    # Correct SQL statement using %s as placeholder
    # cursor.execute("SELECT * FROM notice_list_data WHERE  is_deleted != TRUE and notice_list_id = %s", (notice_list_id,))  # Notice the tuple for parameters
    result = cursor.fetchall()
   
    # Get column names from cursor.description
    column_names = [desc[0] for desc in cursor.description]
    
    # Create a DataFrame from the result
    df = pd.DataFrame(result, columns=column_names)
    
    # Convert the DataFrame to a JSON-compatible format
    data = df.to_dict(orient='records')
    return {'notice_list_data':data}

# Delete File API
@app.delete("/delete_file")
async def deleteFile(request: Request):
    body = await request.json()
    file_id = body.get('file_id')
    # return file_id
    try:
        conn = await connect_db()
        cursor = conn.cursor()
        body = await request.json()
        file_id = body.get('file_id')
        
        # Update the is_deleted flag to True (soft delete)
        cursor.execute("UPDATE file_list SET is_deleted = TRUE WHERE is_deleted != TRUE and id = %s", (file_id,))
        conn.commit()

        if cursor.rowcount > 0:
            return {"message": "File deleted successfully."}
        else:
            raise HTTPException(status_code=404, detail="File not found.")

    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

    finally:
        cursor.close()
        conn.close()

# Delete File Data API
@app.delete("/delete_file_data")
async def deleteFileData(request: Request):
   
    try:
        conn = await connect_db()
        cursor = conn.cursor()
        body = await request.json()
        file_id = body.get('file_id')
        
        # Update the is_deleted flag to True (soft delete)
        cursor.execute("UPDATE notice_list SET is_deleted = TRUE WHERE file_list_id = %s", (file_id,))
        # cursor.execute("UPDATE notice_list_data SET is_deleted = TRUE WHERE file_list_id = %s", (file_id,))
        conn.commit()

        if cursor.rowcount > 0:
            return {"message": "File deleted successfully."}
        else:
            return {"message": "File not found."}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

    finally:
        cursor.close()
        conn.close()

# Add company API
@app.post('/add_company')
async def add_company(request: Request):
    data = await request.json()
    company_name = data.get('company_name')
    company_status = data.get('company_status')
   
    if company_name is None or company_status is None:
        return JSONResponse(content={"error": "All fields are required"}, status_code=400)

    conn = await connect_db()
    cursor = conn.cursor()
    
    try:
        # Insert company details into the database
        sql = "INSERT INTO companies (name, status, created_at, updated_at) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (company_name, company_status, datetime.now(), datetime.now()))
        conn.commit()

        return JSONResponse(content={"message": "Company added successfully!"}, status_code=201)
    except Exception as e:
        conn.rollback()
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# API to update company details
@app.post('/update_company')
async def update_company(request: Request):
    data = await request.json()
    company_id = data.get('company_id')
    company_name = data.get('company_name')
    company_status = data.get('company_status')

    if company_id is None or company_name is None or company_status is None:
        return JSONResponse(content={"error": "All fields are required"}, status_code=400)

    conn = await connect_db()
    cursor = conn.cursor()

    try:
        # Update company details in the database
        sql = """
            UPDATE companies
            SET name = %s, status = %s, updated_at = %s
            WHERE id = %s
        """
        cursor.execute(sql, (company_name, company_status, datetime.now(), company_id))
        conn.commit()

        # Check if any row was affected
        if cursor.rowcount == 0:
            return JSONResponse(content={"error": "Company not found"}, status_code=404)

        return JSONResponse(content={"message": "Company updated successfully!"}, status_code=200)
    except Exception as e:
        conn.rollback()
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# API to list all companies
@app.get("/companies")
async def list_companies():
    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names

    try:
        # Query all companies from the table

        query = """
        SELECT 
            c.id, 
            c.name, 
            c.status, 
            c.created_at, 
            c.updated_at, 
            COUNT(f.id) AS filecount
        FROM 
            companies c
        LEFT JOIN 
            file_list f 
        ON 
            c.id = f.company_id
        GROUP BY 
            c.id, c.name, c.status, c.created_at, c.updated_at
        """
        cursor.execute(query)
        company = cursor.fetchall()
        # Convert datetime fields to strings
        company = convert_dates_to_string(company)
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)
        
        # Wrap the result in the "List" key
        response = {"List": company}
        
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)

        return JSONResponse(content=response, status_code=200)

    except Exception as e:
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# change company status API
@app.post('/change_company_status')
async def changeCompanyStatus(request: Request):
    data = await request.json()
    company_id = data.get('company_id')
   
    if company_id is None :
        return JSONResponse(content={"error": "All fields are required"}, status_code=400)

    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)
    

    try:
        # Check the current status of the company
        cursor.execute("SELECT status FROM companies WHERE id = %s", (company_id,))
        result = cursor.fetchone()
        if result is None:
            return JSONResponse(content={"error": "Company not found"}, status_code=404)

        current_status = result['status']
        new_status = 1 if current_status == 0 else 0  # Toggle status
        
        # Update the company status
        update_query = """
            UPDATE companies
            SET status = %s, updated_at = %s
            WHERE id = %s
        """
        cursor.execute(update_query, (new_status, datetime.now(), company_id))
        conn.commit()

        # Check if any row was affected
        if cursor.rowcount == 0:
            return JSONResponse(content={"error": "Company not found"}, status_code=404)

        # Return success message
        return JSONResponse(content={"message": "Company status updated successfully"}, status_code=200)

    except Exception as e:
        conn.rollback()
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# API to list company by id
@app.post("/company_by_id")
async def companyByID(request:Request):
    data = await request.json()
    company_id = data.get('company_id')

    if company_id is None:
        return JSONResponse(content={"error": "company_id is required"}, status_code=400)

    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names

    try:
        cursor.execute("SELECT * FROM companies WHERE id = %s", (company_id,))
        company = cursor.fetchone()  # Use fetchone() for a single record
        
        if company is None:
            return JSONResponse(content={"error": "Company not found"}, status_code=404)

        # Convert datetime fields to strings
        company = convert_dates_to_string(company)
        
        # Wrap the result in the "List" key
        response = company
        return JSONResponse(content=response, status_code=200)

    except mysql.connector.Error as db_error:
        logging.error(f"Database error: {db_error}")
        return JSONResponse(content={"error": "Database error"}, status_code=500)
    except ValueError as json_error:
        logging.error(f"JSON error: {json_error}")
        return JSONResponse(content={"error": "Invalid JSON format"}, status_code=400)
    except Exception as e:
        logging.error(f"Unexpected error: {e}")
        return JSONResponse(content={"error": "Internal server error"}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# All active company API
@app.get("/all_companies")
async def all_companies(request : Request):
    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names

    try:
        # Query all companies from the table

        query = """
        SELECT * FROM  companies where status = 'True'
        """
        cursor.execute(query)
        company = cursor.fetchall()
        # Convert datetime fields to strings
        company = convert_dates_to_string(company)
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)
        
        # Wrap the result in the "List" key
        response = {"List": company}
        
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)

        return JSONResponse(content=response, status_code=200)

    except Exception as e:
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# Files by company API
@app.post("/files_by_company")
async def all_companies(request : Request):
    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names
    body = await request.json()
    company_id = body.get('company_id')
    # return company_id
    try:
        # Query all companies from the table

        query = """
        SELECT f.*, c.name as company
        FROM file_list f 
        LEFT JOIN companies c ON c.id = f.company_id
        WHERE f.company_id = %s
        """
        cursor.execute(query, (company_id,))
        company = cursor.fetchall()
        # Convert datetime fields to strings
        company = convert_dates_to_string(company)
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)
        
        # Wrap the result in the "List" key
        response = {"List": company}
        
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)

        return JSONResponse(content=response, status_code=200)

    except Exception as e:
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# Filter Data
@app.post("/filter_data")
async def filter_data(request : Request):
    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names
    body = await request.json()
    company_id = body.get('company_id')
    file_list_id = body.get('file_list_id')

    # return company_id
    try:
        
        # Check if both company_id and file_list_id are provided
        if company_id is not None and file_list_id not in [None, ""]:
            query = """
            SELECT  f.*, c.name as company_name, count(n.id) as notice_count   
            FROM file_list f
            LEFT JOIN companies c ON c.id = f.company_id
            LEFT JOIN notice_list n on f.id = n.file_list_id
            WHERE f.is_deleted != TRUE
            AND f.id = %s AND f.company_id = %s
            AND n.is_deleted != TRUE
            GROUP BY 
            f.id, c.name
            """
            cursor.execute(query, (file_list_id,company_id))
        elif file_list_id is None or file_list_id == "":
            query = """
            SELECT  f.*, c.name as company_name, count(n.id) as notice_count 
            FROM file_list f
            LEFT JOIN companies c ON c.id = f.company_id
            LEFT JOIN notice_list n on f.id = n.file_list_id
            WHERE f.is_deleted != TRUE
            AND f.company_id = %s 
            AND n.is_deleted != TRUE
            GROUP BY 
            f.id, c.name
            """ 
            cursor.execute(query, (company_id,))
        else:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)
        
        company = cursor.fetchall()
        
        # Convert datetime fields to strings
        # company = convert_dates_to_string(company)
       
        # Wrap the result in the "List" key
        response = {"List": company}
       
        # return response
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No companies found"}, status_code=404)

        return {"List": company}

    except Exception as e:
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# Add Lawyer API
@app.post('/add_lawyer')
async def add_lawyer(request: Request, signature: UploadFile = File(...)):
    try:
        # Parse form-data
        form_data = await request.form()
        lawyer_name = form_data.get('lawyer_name')
        lawyer_email = form_data.get('lawyer_email')
        lawyer_address = form_data.get('lawyer_address')
        
        if not lawyer_name or not lawyer_email or not lawyer_address:
            raise HTTPException(status_code=422, detail="Missing required fields")

        # Upload the file to the local directory and database
        success = await upload_lawyer_data(signature, lawyer_name, lawyer_email, lawyer_address)
       
        if success:
            return JSONResponse(content={"message": "Data Inserted successfully"})
        else:
            raise HTTPException(status_code=500, detail="Failed to upload file")

    except Exception as e:
        print(f"Exception occurred: {e}")
        raise HTTPException(status_code=400, detail=f"Error processing file: {e}")
 
# Update Lawyer API 
@app.post('/update_lawyer')   
async def update_lawyer(request: Request, signature: UploadFile = File(None)):
    try:
        # Parse form-data
        form_data = await request.form()
        lawyer_name = form_data.get('lawyer_name')
        lawyer_email = form_data.get('lawyer_email')
        lawyer_address = form_data.get('lawyer_address')
        lawyer_id = form_data.get('lawyer_id')

        if not lawyer_id:
            raise HTTPException(status_code=400, detail="Lawyer ID is required")

        # Update lawyer data
        success = await update_lawyer_data(
            signature=signature,
            lawyer_name=lawyer_name,
            lawyer_email=lawyer_email,
            lawyer_address=lawyer_address,
            lawyer_id=int(lawyer_id)
        )
       
        if success:
            return JSONResponse(content={"message": "Data Updated Successfully"})
        else:
            raise HTTPException(status_code=500, detail="Failed to update lawyer data")

    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error processing update: {e}")
    
# API to list all lawyers
@app.get("/lawyer_list")
async def list_lawyers():
    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names

    try:
        # Query all companies from the table

        query = "SELECT * FROM lawyers"
        cursor.execute(query)
        company = cursor.fetchall()
        # Convert datetime fields to strings
        company = convert_dates_to_string(company)
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No lawyer found"}, status_code=404)
        
        # Wrap the result in the "List" key
        response = {"List": company}
        
        # If no companies are found
        if not company:
            return JSONResponse(content={"message": "No lawyer found"}, status_code=404)

        return JSONResponse(content=response, status_code=200)

    except Exception as e:
        return JSONResponse(content={"error": str(e)}, status_code=500)
    finally:
        cursor.close()
        conn.close()

# API to download all pdfs
@app.post("/download-folder-zip/")
async def download_folder_zip(request: Request):
    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names
    try:
        data = await request.json()
        file_list_id = data.get('file_list_id')
       
        # Validate file_list_id
        if not file_list_id:
            raise HTTPException(status_code=400, detail="file_list_id is required")

        # Connect to the database
        conn = await connect_db()
        cursor = conn.cursor(dictionary=True)

        # Fetch file_name using file_list_id
        query = "SELECT file_name FROM file_list WHERE id = %s"
        cursor.execute(query, (file_list_id,))
        record = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        file_name = record['file_name']
        
        # Assuming file_name corresponds to a folder path
        folder_path = os.path.join("/var/www/NoticeGen/uploads", file_name)
        

        # Check if the folder exists
        if not os.path.isdir(folder_path):
            raise HTTPException(status_code=404, detail="Folder not found")

        # Create a temporary ZIP file
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        zip_filename = f"{file_name}_{timestamp}.zip"
        zip_filepath = os.path.join("/tmp", zip_filename)

        with zipfile.ZipFile(zip_filepath, 'w', zipfile.ZIP_DEFLATED) as zipf:
            # Iterate over all files in the directory
            for root, _, files in os.walk(folder_path):
                for file in files:
                    file_path = os.path.join(root, file)
                    arcname = os.path.relpath(file_path, folder_path)  # Keep the folder structure in the ZIP
                    zipf.write(file_path, arcname)

        # Check if ZIP file was created
        if not os.path.exists(zip_filepath):
            raise HTTPException(status_code=500, detail="Failed to create ZIP file")

        # Serve the ZIP file
        return FileResponse(zip_filepath, filename=zip_filename, media_type="application/zip")

    except HTTPException as e:
        raise e
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error processing request: {e}")


# API to list lawyer by id
@app.post("/lawyer_by_id")
async def companyByID(request:Request):
    data = await request.json()
    lawyer_id = data.get('lawyer_id')

    if lawyer_id is None:
        return JSONResponse(content={"error": "lawyer_id is required"}, status_code=400)

    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names

    try:
        cursor.execute("SELECT * FROM lawyers WHERE id = %s", (lawyer_id,))
        lawyer = cursor.fetchone()  # Use fetchone() for a single record
        
        if lawyer is None:
            return JSONResponse(content={"error": "Lawyer not found"}, status_code=404)

        # Convert datetime fields to strings
        lawyer = convert_dates_to_string(lawyer)
        
        # Wrap the result in the "List" key
        response = lawyer
        return JSONResponse(content=response, status_code=200)

    except mysql.connector.Error as db_error:
        logging.error(f"Database error: {db_error}")
        return JSONResponse(content={"error": "Database error"}, status_code=500)
    except ValueError as json_error:
        logging.error(f"JSON error: {json_error}")
        return JSONResponse(content={"error": "Invalid JSON format"}, status_code=400)
    except Exception as e:
        logging.error(f"Unexpected error: {e}")
        return JSONResponse(content={"error": "Internal server error"}, status_code=500)
    finally:
        cursor.close()
        conn.close()


# API to list lawyer by id
@app.post("/link_by_id")
async def linkByID(request:Request):
    data = await request.json()
    notice_list_id = data.get('notice_list_id')

    if notice_list_id is None:
        return JSONResponse(content={"error": "notice_list_id is required"}, status_code=400)

    conn = await connect_db()
    cursor = conn.cursor(dictionary=True)  # Ensure dictionary=True to get column names

    try:
        cursor.execute("SELECT link FROM notice_list WHERE id = %s", (notice_list_id,))
        lawyer = cursor.fetchone()  # Use fetchone() for a single record
        
        if lawyer is None:
            return JSONResponse(content={"error": "notice  not found"}, status_code=404)

        # Convert datetime fields to strings
        lawyer = convert_dates_to_string(lawyer)
        
        # Wrap the result in the "List" key
        response = lawyer
        return JSONResponse(content=response, status_code=200)

    except mysql.connector.Error as db_error:
        logging.error(f"Database error: {db_error}")
        return JSONResponse(content={"error": "Database error"}, status_code=500)
    except ValueError as json_error:
        logging.error(f"JSON error: {json_error}")
        return JSONResponse(content={"error": "Invalid JSON format"}, status_code=400)
    except Exception as e:
        logging.error(f"Unexpected error: {e}")
        return JSONResponse(content={"error": "Internal server error"}, status_code=500)
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    import uvicorn
    uvicorn.run("main:app", host="0.0.0.0", port=9001, log_level="info", reload=True)