from ..services.mysql_service import get_db_connection

class Location:
    @staticmethod
    def create_table_if_not_exists():
        """Creates the locations table if it doesn't exist."""
        conn = get_db_connection()
        if not conn:
            return

        try:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS locations (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    name TEXT NOT NULL,
                    UNIQUE (name(191))
                )
            """)
            conn.commit()
            print("[OK] 'locations' table exists or was successfully created.")
        except Exception as e:
            print(f"Error creating 'locations' table: {e}")
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def get_all_locations():
        """Fetch a list of all locations."""
        conn = get_db_connection()
        if not conn:
            return []

        try:
            cursor = conn.cursor(dictionary=True)
            query = "SELECT id, name FROM locations ORDER BY name"
            cursor.execute(query)
            locations = cursor.fetchall()
            return locations
        except Exception as e:
            print(f"Error fetching all locations: {e}")
            return []
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def add_location(name):
        """Adds a new location to the database."""
        conn = get_db_connection()
        if not conn:
            return None

        try:
            cursor = conn.cursor()
            query = "INSERT INTO locations (name) VALUES (%s)"
            cursor.execute(query, (name,))
            conn.commit()
            return cursor.lastrowid
        except Exception as e:
            print(f"Error adding location: {e}")
            conn.rollback()
            return None
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()