from flask import Flask, request, jsonify
import sqlite3
import logging
import os

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

app = Flask(__name__)

# Database connection
DB_PATH = "crime_records.db"

def get_db_connection():
    if not os.path.exists(DB_PATH):
        logger.error(f"Database file {DB_PATH} does not exist")
        raise Exception(f"Database file {DB_PATH} not found")
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

# Add FIR
@app.route("/api/firs", methods=["POST"])
def add_fir():
    try:
        data = request.get_json()
        conn = get_db_connection()
        cursor = conn.cursor()
        query = "INSERT INTO FIRs (crime_id, complainant_name, complainant_contact, filing_date) VALUES (?, ?, ?, ?)"
        cursor.execute(query, (
            data["crime_id"],
            data["complainant_name"].lower(),
            data["complainant_contact"].lower(),
            data["filing_date"]
        ))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "FIR added successfully"}), 200
    except Exception as e:
        logger.error(f"Error adding FIR: {e}")
        return jsonify({"error": str(e)}), 500

# Add Crime
@app.route("/api/crimes", methods=["POST"])
def add_crime():
    try:
        data = request.get_json()
        conn = get_db_connection()
        cursor = conn.cursor()
        query = "INSERT INTO Crimes (crime_type, description, location, date, officer_in_charge, status) VALUES (?, ?, ?, ?, ?, ?)"
        cursor.execute(query, (
            data["crime_type"].lower(),
            data["description"].lower(),
            data["location"].lower(),
            data["date"],
            data["officer_in_charge"].lower(),
            "Open"
        ))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "Crime added successfully"}), 200
    except Exception as e:
        logger.error(f"Error adding crime: {e}")
        return jsonify({"error": str(e)}), 500

# Sign Up
@app.route("/api/users", methods=["POST"])
def signup():
    try:
        data = request.get_json()
        conn = get_db_connection()
        cursor = conn.cursor()
        query = "INSERT INTO Users (username, password, role) VALUES (?, ?, ?)"
        cursor.execute(query, (
            data["username"].lower(),
            data["password"],
            data.get("role", "police").lower()
        ))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "User signed up successfully"}), 200
    except Exception as e:
        logger.error(f"Error signing up user: {e}")
        return jsonify({"error": str(e)}), 500

# Login
@app.route("/api/login", methods=["POST"])
def login():
    try:
        data = request.get_json()
        username = data.get("username")
        password = data.get("password")
        conn = get_db_connection()
        cursor = conn.cursor()
        query = "SELECT * FROM Users WHERE LOWER(username) = ? AND password = ?"
        cursor.execute(query, (username.lower(), password))
        result = cursor.fetchone()
        cursor.close()
        conn.close()
        if result:
            return jsonify({"message": "Login successful", "username": result["username"], "role": result["role"]}), 200
        else:
            return jsonify({"error": "Invalid credentials"}), 401
    except Exception as e:
        logger.error(f"Error during login: {e}")
        return jsonify({"error": str(e)}), 500

# Get Crimes
@app.route("/api/crimes", methods=["GET"])
def get_crimes():
    try:
        search = request.args.get("search", "").lower()
        conn = get_db_connection()
        cursor = conn.cursor()
        if search:
            query = "SELECT * FROM Crimes WHERE LOWER(crime_type) LIKE ? OR LOWER(location) LIKE ?"
            cursor.execute(query, (f"%{search}%", f"%{search}%"))
        else:
            query = "SELECT * FROM Crimes"
            cursor.execute(query)
        crimes = [dict(row) for row in cursor.fetchall()]
        cursor.close()
        conn.close()
        return jsonify(crimes), 200
    except Exception as e:
        logger.error(f"Error fetching crimes: {e}")
        return jsonify({"error": str(e)}), 500

# Get FIRs
@app.route("/api/firs", methods=["GET"])
def get_firs():
    try:
        search = request.args.get("search", "").lower()
        conn = get_db_connection()
        cursor = conn.cursor()
        if search:
            query = "SELECT * FROM FIRs WHERE LOWER(complainant_name) LIKE ? OR LOWER(complainant_contact) LIKE ?"
            cursor.execute(query, (f"%{search}%", f"%{search}%"))
        else:
            query = "SELECT * FROM FIRs"
            cursor.execute(query)
        firs = [dict(row) for row in cursor.fetchall()]
        cursor.close()
        conn.close()
        return jsonify(firs), 200
    except Exception as e:
        logger.error(f"Error fetching FIRs: {e}")
        return jsonify({"error": str(e)}), 500

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=8000, debug=True)