app.py

from flask_migrate import Migrate
from functools import wraps
from flask import Flask, jsonify, request, make_response, send_from_directory
import os
import sqlite3
from sqlalchemy import inspect, text, func
from models import db, User, Property, ListingHistory, Company
from auth_decorators import (
    token_required,
    payment_required,
    admin_required,
    privileged_required,
    company_payment_required,
)
from datetime import datetime, timedelta
from flask_cors import CORS
from property_service import PropertyService
from payment_utils import (
    update_payment_status,
    check_payment_validity,
    update_company_payment_status,
)


app = Flask(__name__, static_folder="client/build", static_url_path="")
app.config[
    "SQLALCHEMY_DATABASE_URI"
] = "postgresql://postgres:SyncLabPASPAS@localhost:5432/real_estate_db"
app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_size": 10,
    "max_overflow": 20,
    "pool_recycle": 1800,
}
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SECRET_KEY"] = os.environ.get(
    "SECRET_KEY", "dev-secret-key-change-in-production"
)
CORS(app, supports_credentials=True, origins=["http://localhost:5173"])
# CORS(app, supports_credentials=True, origins=["http://34.44.116.8:5173"])

# Initialize the database with the app
db.init_app(app)
migrate = Migrate(app, db)


def initialize_database():
    """Initialize database and create default user if needed"""
    with app.app_context():
        # Check if tables exist
        inspector = inspect(db.engine)
        tables_exist = len(inspector.get_table_names()) > 0

        # Create tables if they don't exist
        if not tables_exist:
            app.logger.info("Creating database tables...")
            db.create_all()

        # Check if default user exists, if not create it
        try:
            default_user = User.query.filter_by(id=1).first()
            if not default_user:
                app.logger.info("Creating default user")
                default_user = User(
                    id=1,
                    email="gvantsakhubulia@gmail.com",
                    username="gvantsa",
                    phone="571155949",
                    name_ka="გვანცა",
                    name_en="Gvantsa",
                    name_ru="Гванца",
                    surname="Khubulia",
                    hardcoded_id="20764744",
                    position="CEO",
                    company_name="IDK JET",
                    payment=True,
                    privileged=True,
                )
                # Set the password
                default_user.password = "Baqteria#1"

                # Set payment dates
                current_time = datetime.utcnow()
                default_user.last_payment_date = current_time
                default_user.payment_expiry_date = current_time + timedelta(
                    days=365
                )  # 1 year for admin

                db.session.add(default_user)
                db.session.commit()
                app.logger.info("Created default user")
            else:
                app.logger.info("Default user already exists")

                # Update user fields if needed
                update_needed = False

                if not default_user.username:
                    default_user.username = "gvantsa"
                    update_needed = True

                if default_user.payment is None:
                    default_user.payment = True
                    update_needed = True

                if default_user.privileged is None:
                    default_user.privileged = True
                    update_needed = True

                if not default_user.surname:
                    default_user.surname = "Khubulia"
                    update_needed = True

                if not default_user.payment_expiry_date:
                    default_user.last_payment_date = datetime.utcnow()
                    default_user.payment_expiry_date = datetime.utcnow() + timedelta(
                        days=365
                    )
                    update_needed = True

                if update_needed:
                    db.session.commit()
                    app.logger.info("Updated default user with missing fields")

        except Exception as e:
            app.logger.error(f"Error handling default user: {e}")
            app.logger.exception("Full traceback:")


@app.route("/api/auth/login", methods=["POST"])
def login():
    """Login with username or email and password"""
    data = request.json
    if not data or not data.get("username") or not data.get("password"):
        return (
            jsonify(
                {"result": False, "errors": "Username/email and password required"}
            ),
            400,
        )

    # Convert username/email to lowercase for case-insensitive comparison
    username_or_email = data["username"].lower()

    # Try to find user by username first (case-insensitive)
    user = User.query.filter(func.lower(User.username) == username_or_email).first()

    # If not found, try email (case-insensitive)
    if not user:
        user = User.query.filter(func.lower(User.email) == username_or_email).first()

    # Check if user exists and password is correct
    if user and user.verify_password(data["password"]):
        # Generate a new token for the user
        token = user.generate_auth_token()

        db.session.commit()

        response = make_response(
            jsonify({
                "result": True,
                "data": {
                    "message": "Login successful"
                }
            }),
            200
        )
        
        # Set token expiration
        expiration_date = datetime.utcnow() + timedelta(days=1)
        
        # Set access token cookie
        response.set_cookie(
            "access_token",
            token,
            expires=expiration_date,
            httponly=False,
            secure=False,
            samesite=None,
        )
        
        # Set user ID cookie - not httponly so it can be accessed by JavaScript
        response.set_cookie(
            "user_id",
            str(user.id),
            expires=expiration_date,
            httponly=False,
            secure=False,
            samesite=None,
        )

        return response
    else:
        return (
            jsonify({"result": False, "errors": "Invalid username/email or password"}),
            401,
        )

@app.route("/api/auth/logout", methods=["POST"])
@token_required
def logout():
    """Logout current user by invalidating their token"""
    user = request.current_user
    user.access_token = None
    user.token_expiry = None
    db.session.commit()

    response = make_response(
        jsonify({"result": True, "data": {"message": "Logout successful"}})
    )
    
    # Delete both the access_token and user_id cookies
    response.delete_cookie("access_token")
    response.delete_cookie("user_id")

    return response


@app.route("/api/auth/status", methods=["GET"])
@token_required
def auth_status():
    """Check if user is logged in"""
    user = request.current_user

    # Check payment validity
    payment_valid, payment_message = check_payment_validity(user)

    # Get company info if user belongs to a company
    company_data = None
    if user.company_id:
        company = db.session.get(Company, user.company_id)
        if company:
            company_data = {"id": company.id, "name": company.name}

    return jsonify(
        {
            "result": True,
            "data": {
                "authenticated": True,
                "user_id": user.id,
                "username": user.username,
                "name": user.name_en,
                "surname": user.surname,
                "payment_status": user.payment,
                "payment_valid": payment_valid,
                "payment_expires": user.payment_expiry_date.isoformat()
                if user.payment_expiry_date
                else None,
                "privileged": user.privileged,
                "company": company_data,
            },
        }
    )

# First, let's create a decorator to check if user is CEO
def ceo_required(f):
    """Decorator to require CEO privileges"""
    @wraps(f)
    def decorated_function(*args, **kwargs):
        current_user = request.current_user
        if not current_user.position or current_user.position.lower() != "ceo":
            return jsonify({
                "result": False,
                "errors": "Access denied. CEO privileges required."
            }), 403
        
        if not current_user.company_id:
            return jsonify({
                "result": False,
                "errors": "CEO must be associated with a company."
            }), 400
            
        return f(*args, **kwargs)
    return decorated_function


# First, let's create a decorator to check if user is CEO
def ceo_required(f):
    """Decorator to require CEO privileges"""
    @wraps(f)
    def decorated_function(*args, **kwargs):
        current_user = request.current_user
        if not current_user.position or current_user.position.lower() != "ceo":
            return jsonify({
                "result": False,
                "errors": "Access denied. CEO privileges required."
            }), 403
        
        if not current_user.company_id:
            return jsonify({
                "result": False,
                "errors": "CEO must be associated with a company."
            }), 400
            
        return f(*args, **kwargs)
    return decorated_function

# CEO - Create User within Company
@app.route("/api/ceo/user", methods=["POST"])
@token_required
@ceo_required
def ceo_create_user():
    """Create a new user within CEO's company"""
    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "Invalid user data"}), 400

    current_user = request.current_user

    # Validate required fields
    required_fields = [
        "email",
        "username", 
        "password",
        "phone",
        "name_ka",
        "name_en",
        "name_ru",
        "commission_percentage",
        "surname",
        "position"
    ]
    missing_fields = [field for field in required_fields if field not in data]

    if missing_fields:
        return jsonify({
            "result": False,
            "errors": f"Missing required fields: {', '.join(missing_fields)}",
        }), 400

    # Validate commission percentage if provided
    commission_percentage = data.get("commission_percentage", 10.00)
    try:
        commission_percentage = float(commission_percentage)
        if commission_percentage < 0 or commission_percentage > 100:
            return jsonify({
                "result": False, 
                "errors": "Commission percentage must be between 0 and 100"
            }), 400
    except (ValueError, TypeError):
        return jsonify({
            "result": False, 
            "errors": "Commission percentage must be a valid number"
        }), 400

    try:
        # Make email lowercase for consistency
        data["email"] = data["email"].lower()

        # Check if username or email already exists
        if User.query.filter(func.lower(User.username) == data["username"].lower()).first():
            return jsonify({"result": False, "errors": "Username already exists"}), 400

        if User.query.filter(func.lower(User.email) == data["email"].lower()).first():
            return jsonify({"result": False, "errors": "Email already exists"}), 400

        # CEOs can only create users in their own company
        # and cannot create other CEOs
        if data.get("position", "").lower() == "ceo":
            return jsonify({
                "result": False,
                "errors": "CEOs cannot create other CEO users"
            }), 400

        # Create user in CEO's company
        user = User(
            email=data["email"],
            username=data["username"],
            phone=data["phone"],
            name_ka=data["name_ka"],
            name_en=data["name_en"],
            name_ru=data["name_ru"],
            surname=data["surname"],
            position=data["position"],
            company_id=current_user.company_id,  # Always assign to CEO's company
            hardcoded_id=data.get("hardcoded_id"),
            payment=False,  # CEOs cannot set payment status
            privileged=data.get("privileged", False),
            commission_percentage=commission_percentage,
        )
        
        # Set password
        user.password = data["password"]

        # Initialize payment dates if payment is enabled (always False for CEO creation)
        # Payment status can only be changed by admin

        # Update company employee count
        company = db.session.get(Company, current_user.company_id)
        if company:
            company.number_of_employees = (company.number_of_employees or 0) + 1
            db.session.add(company)

        db.session.add(user)
        db.session.commit()

        return jsonify({
            "result": True,
            "data": {
                "id": user.id,
                "commission_percentage": float(user.commission_percentage),
                "company_id": user.company_id,
                "message": "User created successfully in your company"
            }
        }), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error creating user: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500


# CEO - Update User within Company
@app.route("/api/ceo/user/<int:user_id>", methods=["PUT"])
@token_required
@ceo_required
def ceo_update_user(user_id):
    """Update a user within CEO's company"""
    current_user = request.current_user
    
    # Get the user to update
    user = db.session.get(User, user_id)
    if not user:
        return jsonify({"result": False, "errors": "User not found"}), 404

    # Check if user belongs to CEO's company
    if user.company_id != current_user.company_id:
        return jsonify({
            "result": False, 
            "errors": "You can only update users within your company"
        }), 403

    # CEOs cannot update other CEOs
    if user.position and user.position.lower() == "ceo" and user.id != current_user.id:
        return jsonify({
            "result": False,
            "errors": "CEOs cannot update other CEO users"
        }), 403

    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "Invalid user data"}), 400

    try:
        # Handle password change
        if "password" in data:
            user.password = data["password"]

        # Handle commission percentage update
        if "commission_percentage" in data:
            try:
                commission_percentage = float(data["commission_percentage"])
                if commission_percentage < 0 or commission_percentage > 100:
                    return jsonify({
                        "result": False, 
                        "errors": "Commission percentage must be between 0 and 100"
                    }), 400
                user.commission_percentage = commission_percentage
            except (ValueError, TypeError):
                return jsonify({
                    "result": False, 
                    "errors": "Commission percentage must be a valid number"
                }), 400

        # Update user fields if present in request data
        if "email" in data:
            data["email"] = data["email"].lower()
            existing_user = User.query.filter(
                func.lower(User.email) == data["email"]
            ).first()
            if existing_user and existing_user.id != user_id:
                return jsonify({
                    "result": False,
                    "errors": "Email already in use by another user",
                }), 400
            user.email = data["email"]

        if "username" in data:
            existing_user = User.query.filter(
                func.lower(User.username) == data["username"].lower()
            ).first()
            if existing_user and existing_user.id != user_id:
                return jsonify({
                    "result": False,
                    "errors": "Username already in use by another user",
                }), 400
            user.username = data["username"]

        # Update other fields
        updatable_fields = ["phone", "name_ka", "name_en", "name_ru", "surname", "hardcoded_id"]
        for field in updatable_fields:
            if field in data:
                setattr(user, field, data[field])

        # Handle position update (but prevent creating other CEOs)
        if "position" in data:
            if data["position"].lower() == "ceo" and user.id != current_user.id:
                return jsonify({
                    "result": False,
                    "errors": "Cannot assign CEO position to other users"
                }), 400
            user.position = data["position"]

        # CEOs cannot change payment status - only admin can do this
        # Payment and privileged status are admin-only privileges
        
        db.session.commit()

        return jsonify({
            "result": True,
            "data": {
                "id": user.id, 
                "commission_percentage": float(user.commission_percentage),
                "message": "User updated successfully"
            },
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error updating user: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500

# CEO - Delete User within Company
@app.route("/api/ceo/user/<int:user_id>", methods=["DELETE"])
@token_required
@ceo_required
def ceo_delete_user(user_id):
    """Delete a user within CEO's company and all their properties"""
    current_user = request.current_user
    
    # Get the user to delete
    user = db.session.get(User, user_id)
    if not user:
        return jsonify({"result": False, "errors": "User not found"}), 404

    # Check if user belongs to CEO's company
    if user.company_id != current_user.company_id:
        return jsonify({
            "result": False, 
            "errors": "You can only delete users within your company"
        }), 403

    # CEOs cannot delete themselves or other CEOs
    if user.id == current_user.id:
        return jsonify({
            "result": False,
            "errors": "CEOs cannot delete themselves"
        }), 403

    if user.position and user.position.lower() == "ceo":
        return jsonify({
            "result": False,
            "errors": "CEOs cannot delete other CEO users"
        }), 403

    try:
        # Store user info for response
        deleted_user_info = {
            "id": user.id,
            "username": user.username,
            "email": user.email,
            "name": f"{user.name_en} {user.surname}"
        }

        # Delete all user's properties first
        properties = Property.query.filter_by(user_id=user_id).all()
        deleted_properties = len(properties)
        
        for prop in properties:
            db.session.delete(prop)

        # Delete listing history for this user
        listing_histories = ListingHistory.query.filter_by(user_id=user_id).all()
        deleted_histories = len(listing_histories)
        
        for history in listing_histories:
            db.session.delete(history)

        # Update company employee count
        company = db.session.get(Company, current_user.company_id)
        if company and company.number_of_employees > 0:
            company.number_of_employees -= 1
            db.session.add(company)

        # Delete the user
        db.session.delete(user)
        db.session.commit()

        return jsonify({
            "result": True,
            "data": {
                "deleted_user": deleted_user_info,
                "deleted_properties": deleted_properties,
                "deleted_histories": deleted_histories,
                "message": f"User deleted successfully. {deleted_properties} properties and {deleted_histories} history records removed."
            }
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error deleting user: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500




# User API Routes
@app.route("/api/user/<int:user_id>", methods=["GET"])
@token_required
def get_user(user_id):
    """Get user profile with names in different languages"""
    try:
        # Check if the requested user is the logged in user or admin
        current_user = request.current_user
        if current_user.id != user_id and current_user.id != 1:  # User 1 is admin
            return jsonify({"result": False, "errors": "Unauthorized access"}), 403

        user = db.session.get(User, user_id)
        if user:
            return jsonify({"result": True, "data": user.to_dict()})
        else:
            return jsonify({"result": False, "errors": "User not found"}), 404
    except Exception as e:
        app.logger.error(f"Error retrieving user: {str(e)}")
        return jsonify({"result": False, "errors": "Server error"}), 500


# Replace the create_user function in your main Flask app

@app.route("/api/user", methods=["POST"])
@token_required
@admin_required
def create_user():
    """Create a new user profile (admin only)"""
    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "Invalid user data"}), 400

    # Validate required fields
    required_fields = [
        "email",
        "username",
        "password",
        "phone",
        "name_ka",
        "name_en",
        "name_ru",
    ]
    missing_fields = [field for field in required_fields if field not in data]

    if missing_fields:
        return (
            jsonify(
                {
                    "result": False,
                    "errors": f"Missing required fields: {', '.join(missing_fields)}",
                }
            ),
            400,
        )

    # Validate commission percentage if provided
    commission_percentage = data.get("commission_percentage", 10.00)
    try:
        commission_percentage = float(commission_percentage)
        if commission_percentage < 0 or commission_percentage > 100:
            return jsonify({
                "result": False, 
                "errors": "Commission percentage must be between 0 and 100"
            }), 400
    except (ValueError, TypeError):
        return jsonify({
            "result": False, 
            "errors": "Commission percentage must be a valid number"
        }), 400

    try:
        # Make email lowercase for consistency
        if "email" in data:
            data["email"] = data["email"].lower()

        # Check if username or email already exists
        if User.query.filter(
            func.lower(User.username) == data["username"].lower()
        ).first():
            return jsonify({"result": False, "errors": "Username already exists"}), 400

        if User.query.filter(func.lower(User.email) == data["email"].lower()).first():
            return jsonify({"result": False, "errors": "Email already exists"}), 400

        # Handle company assignment
        company_id = data.get("company_id")
        
        # If company name is provided but no company_id, try to find or create the company
        if not company_id and data.get("company_name"):
            # First, check if a company with this name already exists
            company = Company.query.filter(func.lower(Company.name) == data["company_name"].lower()).first()
            
            if company:
                # Use existing company
                company_id = company.id
            else:
                # Create new company with the provided name
                new_company = Company(
                    name=data["company_name"],
                    number_of_employees=1  # Start with 1 employee
                )
                db.session.add(new_company)
                db.session.flush()  # Get ID without committing yet
                company_id = new_company.id
        
        # If company_id is provided, verify it exists
        elif company_id:
            company = db.session.get(Company, company_id)
            if not company:
                return jsonify({"result": False, "errors": "Company not found"}), 400
            
            # If company found and it has employees, increase employee count
            if company:
                company.number_of_employees = (company.number_of_employees or 0) + 1
                db.session.add(company)

        # Create user
        user = User(
            email=data["email"],
            username=data["username"],
            phone=data["phone"],
            name_ka=data["name_ka"],
            name_en=data["name_en"],
            name_ru=data["name_ru"],
            surname=data.get("surname"),
            position=data.get("position"),
            company_id=company_id,
            hardcoded_id=data.get("hardcoded_id"),
            payment=data.get("payment", False),
            privileged=data.get("privileged", False),
            commission_percentage=commission_percentage,  # Add commission percentage
        )
        
        # Set password
        user.password = data["password"]

        # Initialize payment dates if payment is enabled
        if data.get("payment", False):
            current_time = datetime.utcnow()
            user.last_payment_date = current_time
            user.payment_expiry_date = current_time + timedelta(days=30)

        # If this user should be a CEO and company exists
        if data.get("position", "").lower() == "ceo" and company_id:
            company = db.session.get(Company, company_id)
            if company:
                company.ceo_id = user.id
                db.session.add(company)

        db.session.add(user)
        db.session.commit()

        # Build response with additional info
        response_data = {
            "id": user.id,
            "commission_percentage": float(user.commission_percentage),
            "message": "User created successfully"
        }
        
        # Add company info to response if applicable
        if company_id:
            company = db.session.get(Company, company_id)
            if company:
                response_data["company"] = {
                    "id": company.id,
                    "name": company.name
                }

        return jsonify({"result": True, "data": response_data}), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error creating user: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500


# Replace the update_user function in your main Flask app
@app.route("/api/user/<int:user_id>", methods=["PUT"])
@token_required
def update_user(user_id):
    """Update an existing user profile"""
    # Check if the user is updating their own profile or is an admin
    current_user = request.current_user
    if current_user.id != user_id and current_user.id != 1:
        return (
            jsonify({"result": False, "errors": "Unauthorized to update this user"}),
            403,
        )

    user = db.session.get(User, user_id)
    if not user:
        return jsonify({"result": False, "errors": "User not found"}), 404

    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "Invalid user data"}), 400

    try:
        # Handle password change with old password verification
        if "password" in data:
            # Admin can change password without old password
            if current_user.id != 1:  # Not admin
                # Require old password
                if "old_password" not in data:
                    return (
                        jsonify({"result": False, "errors": "Old password is required to change password"}),
                        400,
                    )
                
                # Verify old password
                if not user.verify_password(data["old_password"]):
                    return (
                        jsonify({"result": False, "errors": "Incorrect old password"}),
                        401,
                    )
            
            # Set the new password
            user.password = data["password"]

        # Handle commission percentage update
        if "commission_percentage" in data:
            # Only admin or the user themselves can update commission percentage
            if current_user.id != 1 and current_user.id != user_id:
                return jsonify({
                    "result": False, 
                    "errors": "Unauthorized to update commission percentage"
                }), 403
            
            try:
                commission_percentage = float(data["commission_percentage"])
                if commission_percentage < 0 or commission_percentage > 100:
                    return jsonify({
                        "result": False, 
                        "errors": "Commission percentage must be between 0 and 100"
                    }), 400
                user.commission_percentage = commission_percentage
            except (ValueError, TypeError):
                return jsonify({
                    "result": False, 
                    "errors": "Commission percentage must be a valid number"
                }), 400

        # Update user fields if present in request data
        if "email" in data:
            # Make email lowercase
            data["email"] = data["email"].lower()

            # Check if email already exists for another user
            existing_user = User.query.filter(
                func.lower(User.email) == data["email"]
            ).first()
            if existing_user and existing_user.id != user_id:
                return (
                    jsonify(
                        {
                            "result": False,
                            "errors": "Email already in use by another user",
                        }
                    ),
                    400,
                )
            user.email = data["email"]

        if "username" in data:
            # Check if username already exists for another user
            existing_user = User.query.filter(
                func.lower(User.username) == data["username"].lower()
            ).first()
            if existing_user and existing_user.id != user_id:
                return (
                    jsonify(
                        {
                            "result": False,
                            "errors": "Username already in use by another user",
                        }
                    ),
                    400,
                )
            user.username = data["username"]

        if "phone" in data:
            user.phone = data["phone"]

        if "name_ka" in data:
            user.name_ka = data["name_ka"]

        if "name_en" in data:
            user.name_en = data["name_en"]

        if "name_ru" in data:
            user.name_ru = data["name_ru"]

        if "surname" in data:
            user.surname = data["surname"]

        if "company_name" in data:
            user.company_name = data["company_name"]

        if "position" in data:
            user.position = data["position"]

        if "company_id" in data and (
            current_user.id == 1 or current_user.position == "CEO"
        ):
            company_id = data["company_id"]
            if company_id:
                company = db.session.get(Company, company_id)
                if not company:
                    return (
                        jsonify({"result": False, "errors": "Company not found"}),
                        400,
                    )
            user.company_id = company_id

        # Only allow admin to update these fields
        if current_user.id == 1:
            if "hardcoded_id" in data:
                user.hardcoded_id = data["hardcoded_id"]

            if "payment" in data:
                payment_status = bool(data["payment"])
                user.payment = payment_status

                # Update payment dates if setting payment to True
                if payment_status:
                    current_time = datetime.utcnow()
                    user.last_payment_date = current_time
                    user.payment_expiry_date = current_time + timedelta(days=30)

            if "privileged" in data:
                user.privileged = bool(data["privileged"])

        db.session.commit()

        return jsonify(
            {
                "result": True,
                "data": {
                    "id": user.id, 
                    "commission_percentage": float(user.commission_percentage),
                    "message": "User updated successfully"
                },
            }
        )
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error updating user: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500


# Add this new endpoint to your main Flask app

@app.route("/api/reports/ceo-monthly", methods=["GET"])
@token_required
def get_ceo_monthly_report():
    """Get monthly report for CEO showing employee statistics"""
    current_user = request.current_user
    
    # Check if user is a CEO
    if not current_user.position or current_user.position.lower() != "ceo":
        return jsonify({
            "result": False, 
            "errors": "Access denied. Only CEOs can access this report."
        }), 403
    
    # Check if user has a company
    if not current_user.company_id:
        return jsonify({
            "result": False, 
            "errors": "CEO must be associated with a company."
        }), 400
    
    try:
        # Get optional month and year parameters
        month = request.args.get("month")
        year = request.args.get("year")
        
        # Default to current month/year if not provided
        if not month or not year:
            current_date = datetime.utcnow()
            month = month or current_date.month
            year = year or current_date.year
        
        # Convert to integers and validate
        try:
            month = int(month)
            year = int(year)
            if month < 1 or month > 12:
                raise ValueError("Invalid month")
            if year < 2000 or year > 2100:
                raise ValueError("Invalid year")
        except ValueError as e:
            return jsonify({
                "result": False, 
                "errors": f"Invalid month or year parameter: {str(e)}"
            }), 400
        
        # Calculate date range for the month
        from calendar import monthrange
        start_date = datetime(year, month, 1)
        last_day = monthrange(year, month)[1]
        end_date = datetime(year, month, last_day, 23, 59, 59)
        
        # Get all employees in the company
        employees = User.query.filter_by(company_id=current_user.company_id).all()
        
        report_data = []
        
        for employee in employees:
            # 1. Statement Quantity (properties created in the month)
            statement_quantity = Property.query.filter(
                Property.user_id == employee.id,
                Property.created_at >= start_date,
                Property.created_at <= end_date
            ).count()
            
            # 2. Closed Deals (properties with negotiation_status = "ჩემი გაყიდვა" in the month)
            closed_deals_monthly = Property.query.filter(
                Property.user_id == employee.id,
                Property.negotiation_status == "ჩემი გაყიდვა",
                Property.updated_at >= start_date,  # Using updated_at as it's when status changes
                Property.updated_at <= end_date
            ).count()
            
            # 3. Monthly Sum (total commission for closed deals in the month)
            monthly_commission_result = db.session.execute(
                text("""
                    SELECT COALESCE(SUM(commission), 0) as total_commission
                    FROM properties 
                    WHERE user_id = :user_id 
                    AND negotiation_status = 'ჩემი გაყიდვა'
                    AND updated_at >= :start_date 
                    AND updated_at <= :end_date
                    AND commission IS NOT NULL
                """),
                {
                    "user_id": employee.id,
                    "start_date": start_date,
                    "end_date": end_date
                }
            ).fetchone()
            
            monthly_sum = float(monthly_commission_result[0]) if monthly_commission_result[0] else 0.0
            
            # 4. Commission Value (monthly sum * user's commission percentage)
            commission_percentage = float(employee.commission_percentage) if employee.commission_percentage else 50.0
            commission_value = monthly_sum * (commission_percentage / 100)
            
            # 5. Total Sum (all-time commission sum for closed deals)
            total_commission_result = db.session.execute(
                text("""
                    SELECT COALESCE(SUM(commission), 0) as total_commission
                    FROM properties 
                    WHERE user_id = :user_id 
                    AND negotiation_status = 'ჩემი გაყიდვა'
                    AND commission IS NOT NULL
                """),
                {"user_id": employee.id}
            ).fetchone()
            
            total_sum = float(total_commission_result[0]) if total_commission_result[0] else 0.0
            
            # Prepare employee data
            employee_name = f"{employee.name_ka or employee.name_en or ''} {employee.surname or ''}".strip()
            if not employee_name:
                employee_name = employee.username
            
            employee_data = {
                "user_id": employee.id,
                "employee_name_surname": employee_name,
                "username": employee.username,
                "position": employee.position,
                "commission_percentage": commission_percentage,
                "statement_quantity": statement_quantity,
                "closed_deals": closed_deals_monthly,
                "commission_value": round(commission_value, 2),
                "monthly_sum": round(monthly_sum, 2),
                "total_sum": round(total_sum, 2)
            }
            
            report_data.append(employee_data)
        
        # Sort by total commission value (highest first)
        report_data.sort(key=lambda x: x["commission_value"], reverse=True)
        
        # Get company information
        company = db.session.get(Company, current_user.company_id)
        company_name = company.name if company else "Unknown Company"
        
        # Calculate totals for the company
        total_statements = sum(emp["statement_quantity"] for emp in report_data)
        total_closed_deals = sum(emp["closed_deals"] for emp in report_data)
        total_commission_value = sum(emp["commission_value"] for emp in report_data)
        total_monthly_sum = sum(emp["monthly_sum"] for emp in report_data)
        total_all_time_sum = sum(emp["total_sum"] for emp in report_data)
        
        response = {
            "result": True,
            "data": {
                "report_info": {
                    "company_id": current_user.company_id,
                    "company_name": company_name,
                    "ceo_name": f"{current_user.name_ka or current_user.name_en} {current_user.surname}",
                    "report_period": f"{year}-{month:02d}",
                    "month": month,
                    "year": year,
                    "start_date": start_date.isoformat(),
                    "end_date": end_date.isoformat(),
                    "total_employees": len(report_data)
                },
                "company_totals": {
                    "total_statements": total_statements,
                    "total_closed_deals": total_closed_deals,
                    "total_commission_value": round(total_commission_value, 2),
                    "total_monthly_sum": round(total_monthly_sum, 2),
                    "total_all_time_sum": round(total_all_time_sum, 2)
                },
                "employees": report_data
            }
        }
        
        return jsonify(response)
        
    except Exception as e:
        app.logger.error(f"Error generating CEO monthly report: {str(e)}")
        return jsonify({"result": False, "errors": f"Server error: {str(e)}"}), 500


# Optional: Add endpoint for CEO to get report for any month/year
@app.route("/api/reports/ceo-monthly/<int:year>/<int:month>", methods=["GET"])
@token_required
def get_ceo_monthly_report_specific(year, month):
    """Get monthly report for CEO for a specific month and year"""
    # Validate parameters
    if month < 1 or month > 12:
        return jsonify({
            "result": False, 
            "errors": "Month must be between 1 and 12"
        }), 400
    
    if year < 2000 or year > 2100:
        return jsonify({
            "result": False, 
            "errors": "Year must be between 2000 and 2100"
        }), 400
    
    # Call the main report function with URL parameters
    from flask import request as flask_request
    # Temporarily modify request args to include the URL parameters
    original_args = flask_request.args
    flask_request.args = flask_request.args.copy()
    flask_request.args = flask_request.args.to_dict()
    flask_request.args['month'] = str(month)
    flask_request.args['year'] = str(year)
    
    return get_ceo_monthly_report()



# Add this simple endpoint to your main Flask app

@app.route("/api/users", methods=["GET"])
@token_required
@admin_required
def list_all_users():
    """Get all users from users table (admin only)"""
    try:
        # Get all users from the database
        users = User.query.all()
        
        # Convert to dictionary format
        users_data = [user.to_dict() for user in users]
        
        return jsonify({
            "result": True,
            "data": users_data
        })
        
    except Exception as e:
        app.logger.error(f"Error retrieving users: {str(e)}")
        return jsonify({
            "result": False, 
            "errors": f"Server error: {str(e)}"
        }), 500

# Property API Routes
@app.route("/api/property/<int:property_id>", methods=["GET"])
@token_required
def get_property(property_id):
    """Get property details by ID"""
    try:
        property = db.session.get(Property, property_id)

        # Check if property exists and belongs to current user or admin
        current_user = request.current_user
        if not property or (
            property.user_id != current_user.id and current_user.id != 1
        ):
            return (
                jsonify(
                    {"result": False, "errors": "Property not found or access denied"}
                ),
                404,
            )

        return jsonify({"result": True, "data": {"statement": property.to_dict()}})
    except Exception as e:
        app.logger.error(f"Error retrieving property: {str(e)}")
        return jsonify({"result": False, "errors": "Server error"}), 500


@app.route("/api/properties", methods=["GET"])
@token_required
def list_properties():
    """List properties for current user (admin can see all)"""
    try:
        current_user = request.current_user

        # Query parameters
        status = request.args.get("status")
        company_id = request.args.get("company_id")

        # Start building the query
        query = Property.query

        # If admin, show all properties, otherwise filter by user_id
        if current_user.id != 1:
            query = query.filter_by(user_id=current_user.id)

        # Apply filters if provided
        if status:
            query = query.filter_by(status=status)

        if company_id and current_user.id == 1:  # Only admin can filter by company
            query = query.filter_by(company_id=company_id)

        # Execute query
        properties = query.all()

        return jsonify(
            {"result": True, "data": [prop.to_dict() for prop in properties]}
        )
    except Exception as e:
        app.logger.error(f"Error listing properties: {str(e)}")
        return jsonify({"result": False, "errors": "Server error"}), 500


@app.route("/api/property", methods=["POST"])
@token_required
@payment_required
def create_property():
    """Create a new property for current user"""
    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "Invalid property data"}), 400

    # Validate required fields
    required_fields = ["address", "price", "area", "rooms", "deal_type_id"]
    missing_fields = [field for field in required_fields if field not in data]

    if missing_fields:
        return (
            jsonify(
                {
                    "result": False,
                    "errors": f"Missing required fields: {', '.join(missing_fields)}",
                }
            ),
            400,
        )

    try:
        # Get current user ID from request
        current_user = request.current_user
        
        # Get user information for name_surname
        user = db.session.get(User, current_user.id)
        if not user:
            return jsonify({"result": False, "errors": "User not found"}), 404
            
        # Calculate commission from prices
        initial_price = float(data["price"])
        new_price = float(data.get("new_price")) if data.get("new_price") else None
        commission = None
        if new_price is not None and initial_price is not None:
            commission = new_price - initial_price
            
        # Create name_surname from user data
        name_surname = None
        if user.name_ka and user.surname:
            name_surname = f"{user.name_ka} {user.surname}"
        else:
            app.logger.warning(f"User {user.id} missing name_ka or surname for name_surname field")
            
        # Create property with all fields explicitly set
        property = Property(
            external_id=data.get("external_id"),
            user_id=current_user.id,
            company_id=current_user.company_id,
            address=data["address"],
            initial_price=initial_price,
            new_price=new_price,
            area=float(data["area"]),
            rooms=int(data["rooms"]),
            deal_type_id=data["deal_type_id"],
            status=data.get("status", "pending"),
            urban_name=data.get("urban_name"),
            owner_name=data.get("owner_name"),
            owner_phone_number=data.get("owner_phone_number"),
            new_property_id=data.get("new_property_id"),
            commission=commission,  # Set commission explicitly
            name_surname=name_surname  # Set name_surname explicitly
        )
        
        # Log field values for debugging
        app.logger.info(
            f"Creating property with commission={commission}, "
            f"name_surname='{name_surname}'"
        )
            
        db.session.add(property)
        db.session.commit()

        return (
            jsonify(
                {
                    "result": True,
                    "data": {
                        "id": property.id,
                        "message": "Property created successfully",
                        "commission": property.commission,
                        "name_surname": property.name_surname
                    },
                }
            ),
            201,
        )
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error creating property: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500

@app.route("/api/property/<int:property_id>", methods=["PUT"])
@token_required
def update_property(property_id):
    """Update an existing property"""
    property = db.session.get(Property, property_id)

    # Check if property exists and belongs to current user or admin
    current_user = request.current_user
    if not property or (property.user_id != current_user.id and current_user.id != 1):
        return (
            jsonify({"result": False, "errors": "Property not found or access denied"}),
            404,
        )

    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "Invalid property data"}), 400

    try:
        # Check if commission is explicitly provided - this takes priority
        if "commission" in data:
            property.commission = float(data["commission"]) if data["commission"] else None
            app.logger.info(f"Explicit commission set: {property.commission}")
        
        # Track if prices are updated
        price_updated = False
        
        # Update property fields if present in request data
        if "external_id" in data:
            property.external_id = data["external_id"]
        if "external_uuid" in data:
            property.external_uuid = data["external_uuid"]
        if "address" in data:
            property.address = data["address"]
        if "price" in data:
            property.initial_price = float(data["price"])
            price_updated = True
        if "new_price" in data:
            property.new_price = float(data["new_price"]) if data["new_price"] else None
            price_updated = True
        # Continue with other fields...
        if "area" in data:
            property.area = float(data["area"])
        if "rooms" in data:
            property.rooms = int(data["rooms"])
        if "deal_type_id" in data:
            property.deal_type_id = data["deal_type_id"]
        if "status" in data:
            property.status = data["status"]
        if "is_posted" in data:
            property.is_posted = bool(data["is_posted"])
        if "negotiation_status" in data:
            property.negotiation_status = data["negotiation_status"]
        if "urban_name" in data:
            property.urban_name = data["urban_name"]
        if "owner_name" in data:
            property.owner_name = data["owner_name"]
        if "owner_phone_number" in data:
            property.owner_phone_number = data["owner_phone_number"]
        if "new_property_id" in data:
            property.new_property_id = data["new_property_id"]
        
        # If commission wasn't explicitly provided but prices were updated, recalculate
        if "commission" not in data and price_updated:
            if property.new_price is not None and property.initial_price is not None:
                property.commission = property.new_price - property.initial_price
                app.logger.info(f"Recalculated commission: {property.commission}")
        
        # Update name_surname from user data
        user = db.session.get(User, property.user_id)
        if user and user.name_ka and user.surname:
            property.name_surname = f"{user.name_ka} {user.surname}"

        db.session.commit()

        return jsonify(
            {
                "result": True,
                "data": {"id": property.id, "message": "Property updated successfully"},
            }
        )
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error updating property: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500


@app.route("/api/company-properties", methods=["GET"])
@token_required
def list_company_properties():
    """List all properties within the current user's company"""
    try:
        current_user = request.current_user
        
        # Check if user belongs to a company
        if not current_user.company_id:
            return jsonify({
                "result": False, 
                "errors": "You are not associated with any company"
            }), 400
        
        # Query parameters for optional filtering
        status = request.args.get("status")
        user_id = request.args.get("user_id")  # Optional: filter by specific user within company
        
        # Start building the query
        query = Property.query.filter_by(company_id=current_user.company_id)
        
        # Apply optional filters
        if status:
            query = query.filter_by(status=status)
        
        if user_id and (current_user.id == 1 or current_user.position == "CEO"):
            # Only admin or CEO can filter by user_id within company
            query = query.filter_by(user_id=user_id)
        
        # Add sorting options
        sort_by = request.args.get("sort_by", "created_at")  # Default sort by creation date
        sort_order = request.args.get("sort_order", "desc")  # Default newest first
        
        # Validate and apply sorting
        valid_sort_fields = ["created_at", "updated_at", "initial_price", "area", "rooms", "address"]
        if sort_by not in valid_sort_fields:
            sort_by = "created_at"  # Default if invalid field
        
        # Get the sort column
        sort_column = getattr(Property, sort_by)
        
        # Apply sorting direction
        if sort_order.lower() == "asc":
            query = query.order_by(sort_column.asc())
        else:
            query = query.order_by(sort_column.desc())
        
        # Execute query to get all results
        properties = query.all()
        
        # Get total count for comparison
        from sqlalchemy import text
        raw_count = db.session.execute(
            text("SELECT COUNT(*) FROM properties WHERE company_id = :company_id"), 
            {"company_id": current_user.company_id}
        ).scalar()
        
        # Format response
        result = {
            "result": True,
            "data": {
                "properties": [prop.to_dict() for prop in properties],
                "count": len(properties),
                "raw_sql_count": raw_count  # For debugging
            }
        }
        
        # Add company information
        company = db.session.get(Company, current_user.company_id)
        if company:
            result["data"]["company"] = {
                "id": company.id,
                "name": company.name,
                "number_of_employees": company.number_of_employees
            }
        
        return jsonify(result)
        
    except Exception as e:
        app.logger.error(f"Error listing company properties: {str(e)}")
        return jsonify({"result": False, "errors": "Server error"}), 500



# API route to perform the property listing operation
@app.route("/api/process-listing", methods=["POST"])
@token_required
@payment_required
def process_listing():
    """Process a property listing using the authenticated user's hardcoded_id"""
    current_user = request.current_user

    if not current_user.hardcoded_id:
        return (
            jsonify({"result": False, "errors": "User has no associated hardcoded_id"}),
            400,
        )

    try:
        # Use the property service to process the listing
        result = PropertyService.process_property_listing(current_user)

        if result.get("success"):
            return jsonify(
                {
                    "result": True,
                    "data": {
                        "message": "Property listing processed successfully",
                        "details": result.get("details", {}),
                        "property_id": result.get("property_id"),
                        "history_id": result.get("history_id"),
                    },
                }
            )
        else:
            return (
                jsonify(
                    {
                        "result": False,
                        "errors": result.get("error", "Unknown error occurred"),
                        "history_id": result.get("history_id"),
                    }
                ),
                500,
            )
    except Exception as e:
        app.logger.error(f"Error processing listing: {str(e)}")
        return jsonify({"result": False, "errors": f"Server error: {str(e)}"}), 500


# Process listing with a specific property ID and optional new price
@app.route("/api/process-listing-with-id", methods=["POST"])
@token_required
@payment_required
def process_listing_with_id():
    """Process a property listing using a provided property ID with optional updates"""
    data = request.json
    # Add detailed logging of the request body
    app.logger.info(f"Request to /api/process-listing-with-id received with body: {data}")
    if not data or not data.get("property_id"):
        return jsonify({"result": False, "errors": "Property ID is required"}), 400

    # Get parameters
    property_id = data.get("property_id")
    new_price_usd = data.get("new_price_usd")
    owner_phone_number = data.get("owner_phone_number")
    custom_comment = data.get("comment")

    # Log parameters
    app.logger.info(f"Process listing requested for property ID: {property_id}")

    current_user = request.current_user

    # Use the property service to process the listing
    result = PropertyService.process_property_listing(
        current_user, property_id, new_price_usd, owner_phone_number, custom_comment
    )

    if result.get("success"):
        return jsonify(
            {
                "result": True,
                "data": {
                    "message": "Property listing processed successfully",
                    "details": result.get("details", {}),
                    "property_id": result.get("property_id"),
                    "history_id": result.get("history_id"),
                },
            }
        )
    else:
        return (
            jsonify(
                {
                    "result": False,
                    "errors": result.get("error", "Unknown error occurred"),
                    "history_id": result.get("history_id"),
                }
            ),
            500,
        )


# Company API Routes
@app.route("/api/companies", methods=["GET"])
@token_required
def list_companies():
    """List all companies"""
    companies = Company.query.all()
    return jsonify(
        {"result": True, "data": [company.to_dict() for company in companies]}
    )


@app.route("/api/company/<int:company_id>", methods=["GET"])
@token_required
def get_company(company_id):
    """Get company details by ID"""
    company = db.session.get(Company, company_id)
    if not company:
        return jsonify({"result": False, "errors": "Company not found"}), 404

    return jsonify({"result": True, "data": company.to_dict()})


@app.route("/api/company", methods=["POST"])
@token_required
@admin_required
def create_company():
    """Create a new company with optional CEO user (admin only)"""
    data = request.json

    if not data or "name" not in data:
        return jsonify({"result": False, "errors": "Company name is required"}), 400

    # Begin transaction
    try:
        company = Company(
            name=data["name"], number_of_employees=data.get("number_of_employees", 1)
        )

        # Set payment dates
        if data.get("with_payment", False):
            current_time = datetime.utcnow()
            company.last_payment_date = current_time
            company.payment_expiry_date = current_time + timedelta(days=30)

        db.session.add(company)
        db.session.flush()  # Flush to get the company ID

        ceo_user = None
        # If CEO details are provided, create a CEO user
        if "ceo_details" in data and isinstance(data["ceo_details"], dict):
            ceo_data = data["ceo_details"]

            # Validate required CEO fields
            required_ceo_fields = [
                "email",
                "username",
                "password",
                "phone",
                "name_ka",
                "name_en",
                "name_ru",
            ]
            missing_fields = [
                field for field in required_ceo_fields if field not in ceo_data
            ]

            if missing_fields:
                return (
                    jsonify(
                        {
                            "result": False,
                            "errors": f"Missing required CEO fields: {', '.join(missing_fields)}",
                        }
                    ),
                    400,
                )

            # Check if username or email already exists
            if User.query.filter(
                func.lower(User.username) == ceo_data["username"].lower()
            ).first():
                return (
                    jsonify({"result": False, "errors": "Username already exists"}),
                    400,
                )

            if User.query.filter(
                func.lower(User.email) == ceo_data["email"].lower()
            ).first():
                return jsonify({"result": False, "errors": "Email already exists"}), 400

            # Create CEO user
            ceo_user = User(
                email=ceo_data["email"].lower(),
                username=ceo_data["username"],
                phone=ceo_data["phone"],
                name_ka=ceo_data["name_ka"],
                name_en=ceo_data["name_en"],
                name_ru=ceo_data["name_ru"],
                surname=ceo_data.get("surname"),
                position="CEO",  # Always set as CEO
                company_id=company.id,
                hardcoded_id=ceo_data.get("hardcoded_id"),
                payment=ceo_data.get("payment", data.get("with_payment", False)),
                privileged=ceo_data.get("privileged", False),
            )

            # Set password
            ceo_user.password = ceo_data["password"]

            # Initialize payment dates if payment is enabled
            if ceo_data.get("payment", False) or data.get("with_payment", False):
                current_time = datetime.utcnow()
                ceo_user.last_payment_date = current_time
                ceo_user.payment_expiry_date = current_time + timedelta(days=30)

            db.session.add(ceo_user)
            db.session.flush()  # Flush to get the CEO's ID

            # IMPORTANT: Set the CEO ID in the company record
            company.ceo_id = ceo_user.id
            db.session.add(company)  # Update the company with CEO ID

        db.session.commit()

        response = {
            "result": True,
            "data": {
                "company": {
                    "id": company.id,
                    "name": company.name,
                    "number_of_employees": company.number_of_employees,
                    "ceo_id": company.ceo_id if hasattr(company, "ceo_id") else None,
                },
                "message": "Company created successfully",
            },
        }

        # Add CEO info to response if created
        if ceo_user:
            response["data"]["ceo"] = {
                "id": ceo_user.id,
                "username": ceo_user.username,
                "email": ceo_user.email,
            }

        return jsonify(response), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error creating company: {str(e)}")
        return jsonify({"result": False, "errors": str(e)}), 500


@app.route("/api/company/<int:company_id>", methods=["PUT"])
@token_required
@admin_required
def update_company(company_id):
    """Update company details (admin only)"""
    company = db.session.get(Company, company_id)
    if not company:
        return jsonify({"result": False, "errors": "Company not found"}), 404

    data = request.json
    if not data:
        return jsonify({"result": False, "errors": "No data provided"}), 400

    # Update fields if provided
    if "name" in data:
        company.name = data["name"]

    if "number_of_employees" in data:
        company.number_of_employees = data["number_of_employees"]

    if "ceo_id" in data:
        ceo = db.session.get(User, data["ceo_id"])
        if not ceo:
            return jsonify({"result": False, "errors": "CEO user not found"}), 400

        # Check if user has CEO position
        if ceo.position and ceo.position.lower() != "ceo":
            return (
                jsonify(
                    {
                        "result": False,
                        "errors": "User must have CEO position to be assigned as company CEO",
                    }
                ),
                400,
            )

        company.ceo_id = ceo.id

    db.session.commit()

    return jsonify(
        {
            "result": True,
            "data": {"id": company.id, "message": "Company updated successfully"},
        }
    )


@app.route("/api/company/<int:company_id>/users", methods=["GET"])
@token_required
def list_company_users(company_id):
    """List all users in a company"""
    company = db.session.get(Company, company_id)
    if not company:
        return jsonify({"result": False, "errors": "Company not found"}), 404

    users = User.query.filter_by(company_id=company_id).all()

    return jsonify({"result": True, "data": [user.to_dict() for user in users]})


@app.route("/api/company/payment", methods=["POST"])
@token_required
@admin_required
def update_company_payment():
    """Update company payment status (admin only)"""
    data = request.json

    if not data or "company_id" not in data or "payment_status" not in data:
        return (
            jsonify(
                {
                    "result": False,
                    "errors": "Company ID and payment status are required",
                }
            ),
            400,
        )

    company = db.session.get(Company, data["company_id"])
    if not company:
        return jsonify({"result": False, "errors": "Company not found"}), 404

    payment_status = bool(data["payment_status"])

    # Call utility function to update payment status
    success, message = update_company_payment_status(company.id, payment_status)

    if success:
        return jsonify(
            {
                "result": True,
                "data": {
                    "id": company.id,
                    "message": "Company payment status updated successfully",
                    "payment_expiry_date": company.payment_expiry_date.isoformat()
                    if company.payment_expiry_date
                    else None,
                },
            }
        )
    else:
        return jsonify({"result": False, "errors": message}), 400


# Admin endpoint to update user payment status
@app.route("/api/user/<int:user_id>/payment", methods=["PUT"])
@token_required
@admin_required
def update_user_payment_status(user_id):
    """Update a user's payment status (admin only)"""
    user = db.session.get(User, user_id)
    if not user:
        return jsonify({"result": False, "errors": "User not found"}), 404

    data = request.json
    if not data or "payment" not in data:
        return jsonify({"result": False, "errors": "Payment status required"}), 400

    try:
        # Update payment status using utility function
        success, message = update_payment_status(user_id, bool(data["payment"]))

        if success:
            return jsonify(
                {
                    "result": True,
                    "data": {
                        "id": user.id,
                        "payment": user.payment,
                        "expiry_date": user.payment_expiry_date.isoformat()
                        if user.payment_expiry_date
                        else None,
                        "message": message,
                    },
                }
            )
        else:
            return jsonify({"result": False, "errors": message}), 400
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error updating payment status: {str(e)}")
        return jsonify({"result": False, "errors": f"Server error: {str(e)}"}), 500


# Admin endpoint to update user privileged status
@app.route("/api/user/<int:user_id>/privileged", methods=["PUT"])
@token_required
@admin_required
def update_privileged_status(user_id):
    """Update a user's privileged status (admin only)"""
    user = db.session.get(User, user_id)
    if not user:
        return jsonify({"result": False, "errors": "User not found"}), 404

    data = request.json
    if not data or "privileged" not in data:
        return jsonify({"result": False, "errors": "Privileged status required"}), 400

    try:
        # Update privileged status
        user.privileged = bool(data["privileged"])
        db.session.commit()

        return jsonify(
            {
                "result": True,
                "data": {
                    "id": user.id,
                    "privileged": user.privileged,
                    "message": f"Privileged status updated to {user.privileged}",
                },
            }
        )
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error updating privileged status: {str(e)}")
        return jsonify({"result": False, "errors": f"Server error: {str(e)}"}), 500


# Get listing history for a user
@app.route("/api/user/<int:user_id>/listing-history", methods=["GET"])
@token_required
def get_user_listing_history(user_id):
    """Get listing history for a specific user"""
    # Check permissions - must be the user or admin
    current_user = request.current_user
    if current_user.id != user_id and current_user.id != 1:
        return jsonify({"result": False, "errors": "Unauthorized access"}), 403

    # Optional query parameters for filtering
    status = request.args.get("status")
    action_type = request.args.get("action_type")

    # Build query
    query = ListingHistory.query.filter_by(user_id=user_id)

    # Apply filters if provided
    if status:
        query = query.filter_by(status=status)

    if action_type:
        query = query.filter_by(action_type=action_type)

    # Order by most recent first
    history_items = query.order_by(ListingHistory.created_at.desc()).all()

    return jsonify({"result": True, "data": [item.to_dict() for item in history_items]})


# Get specific listing history item
@app.route("/api/listing-history/<int:history_id>", methods=["GET"])
@token_required
def get_listing_history_item(history_id):
    """Get details of a specific listing history record"""
    history_item = db.session.get(ListingHistory, history_id)

    if not history_item:
        return (
            jsonify({"result": False, "errors": "Listing history record not found"}),
            404,
        )

    # Check permissions - must be the owner or admin
    current_user = request.current_user
    if current_user.id != history_item.user_id and current_user.id != 1:
        return jsonify({"result": False, "errors": "Unauthorized access"}), 403

    return jsonify({"result": True, "data": history_item.to_dict()})


# Get listing history filtered by source property ID
@app.route("/api/listing-history/by-source/<source_property_id>", methods=["GET"])
@token_required
def get_listing_history_by_source(source_property_id):
    """Get listing history records for a specific source property ID with optional user ID filter"""
    # Get current user for permission checking
    current_user = request.current_user

    # Build query - filter by source_property_id
    query = ListingHistory.query.filter_by(source_property_id=source_property_id)

    # Optional user_id filter from query parameters
    user_id = request.args.get("user_id")

    # Apply user_id filter if provided
    if user_id:
        # Only admins can query other users' records
        if current_user.id != 1 and int(user_id) != current_user.id:
            return (
                jsonify(
                    {
                        "result": False,
                        "errors": "You can only view your own records unless you are an admin",
                    }
                ),
                403,
            )

        query = query.filter_by(user_id=int(user_id))
    # If no user_id specified and not admin, filter to current user's records only
    elif current_user.id != 1:
        query = query.filter_by(user_id=current_user.id)

    # Optional status filter
    status = request.args.get("status")
    if status:
        query = query.filter_by(status=status)

    # Order by most recent first
    history_items = query.order_by(ListingHistory.created_at.desc()).all()

    # Create full response with all fields by using to_dict()
    response_data = {
        "source_property_id": source_property_id,
        "records": [item.to_dict() for item in history_items],
    }

    # Include filter info in response
    if user_id:
        response_data["user_id_filter"] = int(user_id)
    if status:
        response_data["status_filter"] = status

    return jsonify({"result": True, "data": response_data})


# Admin endpoint to get all listing history
@app.route("/api/listing-history", methods=["GET"])
@token_required
@admin_required
def get_all_listing_history():
    """Get all listing history (admin only)"""
    # Optional query parameters
    status = request.args.get("status")
    user_id = request.args.get("user_id")
    action_type = request.args.get("action_type")

    # Build query
    query = ListingHistory.query

    # Apply filters if provided
    if status:
        query = query.filter_by(status=status)
    if user_id:
        query = query.filter_by(user_id=int(user_id))
    if action_type:
        query = query.filter_by(action_type=action_type)

    # Order by most recent first
    history_items = query.order_by(ListingHistory.created_at.desc()).all()

    return jsonify({"result": True, "data": [item.to_dict() for item in history_items]})


# Update the negotiation status of a property
@app.route("/api/listing-history/<int:history_id>/negotiation-status", methods=["PUT"])
@token_required
def update_negotiation_status(history_id):
    """Update the negotiation status of a listing history record"""
    data = request.json
    if not data or "negotiation_status" not in data:
        return (
            jsonify({"result": False, "errors": "Negotiation status is required"}),
            400,
        )

    # Validate status value
    valid_statuses = ["ახალი", "ჩემი გაყიდვა", "სხვისი გაყიდვა"]
    if data["negotiation_status"] not in valid_statuses:
        return (
            jsonify(
                {
                    "result": False,
                    "errors": f"Invalid negotiation status. Must be one of: {', '.join(valid_statuses)}",
                }
            ),
            400,
        )

    # Get the history record
    history = db.session.get(ListingHistory, history_id)
    if not history:
        return (
            jsonify({"result": False, "errors": "Listing history record not found"}),
            404,
        )

    # Check permissions - must be the owner or admin
    current_user = request.current_user
    if current_user.id != history.user_id and current_user.id != 1:
        return jsonify({"result": False, "errors": "Unauthorized access"}), 403

    try:
        # Update the negotiation status
        history.negotiation_status = data["negotiation_status"]

        # Also update the related property if it exists
        if history.property_id:
            property = db.session.get(Property, history.property_id)
            if property:
                property.negotiation_status = data["negotiation_status"]

                # If sold, update property status
                if data["negotiation_status"] in ["ჩემი გაყიდვა", "სხვისი გაყიდვა"]:
                    property.status = "sold"

        db.session.commit()

        return jsonify(
            {
                "result": True,
                "data": {
                    "id": history.id,
                    "negotiation_status": history.negotiation_status,
                    "message": "Negotiation status updated successfully",
                },
            }
        )
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"Error updating negotiation status: {str(e)}")
        return jsonify({"result": False, "errors": f"Server error: {str(e)}"}), 500



# Get reports - Admin only
@app.route("/api/reports/user-activity", methods=["GET"])
@token_required
@admin_required
def get_user_activity_report():
    """Get report on user activity (admin only)"""
    # Query parameters
    start_date = request.args.get("start_date")
    end_date = request.args.get("end_date")

    # Convert string dates to datetime objects
    if start_date:
        try:
            start_date = datetime.strptime(start_date, "%Y-%m-%d")
        except ValueError:
            return (
                jsonify(
                    {
                        "result": False,
                        "errors": "Invalid start_date format. Use YYYY-MM-DD",
                    }
                ),
                400,
            )
    else:
        # Default to 30 days ago
        start_date = datetime.utcnow() - timedelta(days=30)

    if end_date:
        try:
            end_date = datetime.strptime(end_date, "%Y-%m-%d")
            # Add one day to include all activity on the end date
            end_date = end_date + timedelta(days=1)
        except ValueError:
            return (
                jsonify(
                    {
                        "result": False,
                        "errors": "Invalid end_date format. Use YYYY-MM-DD",
                    }
                ),
                400,
            )
    else:
        # Default to now
        end_date = datetime.utcnow()

    # Query to get listing history within date range
    history_query = ListingHistory.query.filter(
        ListingHistory.created_at >= start_date, ListingHistory.created_at < end_date
    )

    # Group by user and status
    # For PostgreSQL, we can use raw SQL for more complex queries
    user_activity = db.session.execute(
        text(
            """
        SELECT 
            user_id,
            COUNT(*) as total_attempts,
            SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful,
            SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
            SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending
        FROM listing_history
        WHERE created_at >= :start_date AND created_at < :end_date
        GROUP BY user_id
        ORDER BY total_attempts DESC
    """
        ),
        {"start_date": start_date, "end_date": end_date},
    ).fetchall()

    # Format the results
    results = []
    for row in user_activity:
        user = db.session.get(User, row[0])
        if user:
            results.append(
                {
                    "user_id": row[0],
                    "username": user.username,
                    "name": f"{user.name_en} {user.surname}",
                    "total_attempts": row[1],
                    "successful": row[2],
                    "failed": row[3],
                    "pending": row[4],
                    "success_rate": round((row[2] / row[1]) * 100, 2)
                    if row[1] > 0
                    else 0,
                }
            )

    return jsonify(
        {
            "result": True,
            "data": {
                "start_date": start_date.strftime("%Y-%m-%d"),
                "end_date": (end_date - timedelta(days=1)).strftime("%Y-%m-%d"),
                "users": results,
            },
        }
    )
@app.route("/debug-static")
def debug_static():
    """Debug endpoint to list files in the static folder"""
    static_files = []
    
    for root, dirs, files in os.walk(app.static_folder):
        rel_path = os.path.relpath(root, app.static_folder)
        if rel_path == '.':
            rel_path = ''
        
        for file in files:
            file_path = os.path.join(rel_path, file)
            static_files.append(file_path)
    
    return jsonify({
        "static_folder": app.static_folder,
        "exists": os.path.exists(app.static_folder),
        "files": sorted(static_files)
    })

# Serve frontend static files
# Serve frontend static files
@app.route("/", defaults={"path": ""})
@app.route("/<path:path>")
def serve_spa(path):
    """
    Serve the SPA frontend - static files if they exist, otherwise index.html
    This is a catch-all route that should be defined AFTER all API routes
    """
    # Log request path for debugging
    app.logger.info(f"Received request for path: '{path}'")
    
    # Skip API routes - they should be handled by their own handlers
    if path.startswith("api/"):
        app.logger.warning(f"API path '{path}' not found")
        return "API route not found", 404
    
    # Check if the requested path is a file that exists
    file_path = os.path.join(app.static_folder, path)
    if os.path.isfile(file_path):
        app.logger.info(f"Serving static file: {path}")
        return send_from_directory(app.static_folder, path)
    
    # Check if it's a request for an asset file
    if path.startswith("assets/"):
        # Normalize the path (handle both forward and backslashes)
        normalized_path = path.replace("/", os.sep)
        file_path = os.path.join(app.static_folder, normalized_path)
        
        if os.path.isfile(file_path):
            app.logger.info(f"Serving asset file: {normalized_path}")
            # Extract the directory and filename
            directory = os.path.dirname(normalized_path)
            filename = os.path.basename(normalized_path)
            return send_from_directory(os.path.join(app.static_folder, directory), filename)
    
    # Handle all other routes - serve index.html for client-side routing
    app.logger.info(f"Path '{path}' not found, serving index.html")
    return send_from_directory(app.static_folder, "index.html")


if __name__ == "__main__":
    # Initialize database before starting the server
    initialize_database()

    # Get port from environment variable or use default 5000
    port = int(os.environ.get("PORT", 5000))

    # Log server startup information
    app.logger.info(f"Starting Flask API server on 0.0.0.0:{port}...")

    # Run the server
    app.run(host="0.0.0.0", port=port, debug=True)