{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f46948ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "from io import BytesIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3a1de802",
   "metadata": {},
   "outputs": [],
   "source": [
    "# --- Configuration ---\n",
    "NEXTCLOUD_URL = \"https://nextcloud.karnelegger.eu\"\n",
    "USERNAME = \"Georg Brantegger\"\n",
    "with open('app_pw.txt','r') as f:\n",
    "    APP_PASSWORD = f.readline()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "1fdfef34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File loaded successfully!\n"
     ]
    }
   ],
   "source": [
    "# read list of boats\n",
    "\n",
    "FILE_PATH = \"Shared/Ruderverein/01_Material/01_Bootsliste.xlsx\"\n",
    "\n",
    "# Build the WebDAV URL\n",
    "# Note: Path should be URL-encoded if it contains spaces or special characters\n",
    "webdav_url = f\"{NEXTCLOUD_URL}/remote.php/dav/files/{USERNAME}/{FILE_PATH}\"\n",
    "\n",
    "# --- The Request ---\n",
    "response = requests.get(webdav_url, auth=(USERNAME, APP_PASSWORD))\n",
    "\n",
    "if response.status_code == 200:\n",
    "    # Use BytesIO to turn the raw binary content into a file-like object\n",
    "    boats_df = pd.read_excel(BytesIO(response.content))\n",
    "    print(\"File loaded successfully!\")\n",
    "else:\n",
    "    print(f\"Failed to fetch file. Status code: {response.status_code}\")\n",
    "    print(response.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "80f97e18",
   "metadata": {},
   "outputs": [],
   "source": [
    "def analyse_class(row):\n",
    "    boot_class = row['Bootsklasse']\n",
    "\n",
    "    number_seats = 0\n",
    "    variable = False\n",
    "\n",
    "    for i in range(8+1):\n",
    "        if str(i) in boot_class:\n",
    "            number_seats = i\n",
    "            break\n",
    "    \n",
    "    if '/' in boot_class:\n",
    "        variable = True\n",
    "\n",
    "    return number_seats, variable\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "70fb2327",
   "metadata": {},
   "outputs": [],
   "source": [
    "boats_df[['number_seats','variable']] = boats_df.apply(analyse_class,axis=1,result_type='expand')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bf18a0fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "boat_names = []\n",
    "variants = []\n",
    "places = []\n",
    "\n",
    "for boat_name in boats_df['Name'].unique():\n",
    "    number_seats = boats_df.loc[boats_df['Name']==boat_name,'number_seats'].values[0]\n",
    "    variable = boats_df.loc[boats_df['Name']==boat_name,'variable'].values[0]\n",
    "\n",
    "    if bool(variable) is False:\n",
    "        for place in range(1,number_seats+1):\n",
    "            boat_names.append(boat_name.strip())\n",
    "            variants.append(f\"{number_seats}x\")\n",
    "            places.append(place)\n",
    "    elif bool(variable) is True:\n",
    "        for place in range(1,number_seats+1):\n",
    "            boat_names.append(boat_name.strip())\n",
    "            variants.append(f\"{number_seats}x\")\n",
    "            places.append(place)\n",
    "        for place in range(1,number_seats+1):\n",
    "            boat_names.append(boat_name.strip())\n",
    "            variants.append(f\"{number_seats}-\")\n",
    "            places.append(place)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "85059518",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {'Bootsname':boat_names,'Trimmung':variants,'Bootsplatz':places,'Ruder-ID Macon':[None for _ in boat_names],'Ruder-ID Hacke':[None for _ in boat_names]}\n",
    "places_df = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "7c4f5c01",
   "metadata": {},
   "outputs": [],
   "source": [
    "places_df['sort_value'] = places_df['Bootsname'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "edd22b3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "places_df.sort_values(by='sort_value',inplace=True,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "3071bf52",
   "metadata": {},
   "outputs": [],
   "source": [
    "places_df[['Bootsname','Trimmung','Bootsplatz','Ruder-ID Macon','Ruder-ID Hacke']].to_excel('temp.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}