{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3228e84e-083c-48f6-9913-9d96e2fd0a2a",
   "metadata": {},
   "source": [
    "# Promotional campaign"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71a559ef-75a7-4aca-86aa-10629e047222",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8fe32f7f-09b3-4d9a-af26-4146c500bf44",
   "metadata": {},
   "outputs": [],
   "source": [
    "from decimal import Decimal\n",
    "from mysql.connector import CMySQLConnection, Error, connect\n",
    "from mysql.connector.cursor_cext import CMySQLCursor"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70da0c3b-ae18-4b60-a7eb-1c0c75c3677a",
   "metadata": {},
   "source": [
    "## Task 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ac95c33d-3ead-4dab-b007-4f20d37f7386",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = connect(\n",
    "    user=\"tobias\",\n",
    "    unix_socket=\"/run/mysqld/mysqld.sock\",\n",
    "    database=\"LittleLemonDB\",\n",
    "    use_pure=False,\n",
    "    autocommit=True,\n",
    ")\n",
    "assert isinstance(connection, CMySQLConnection)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19e74d2d-2b40-4a1b-a57e-38f27106220b",
   "metadata": {},
   "source": [
    "## Task 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1b386dae-78e4-44db-90e5-6da378de16fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables:\n",
      "  Addresses\n",
      "  Bookings\n",
      "  Customers\n",
      "  Employees\n",
      "  MenuItems\n",
      "  OrderItems\n",
      "  Orders\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    with connection.cursor() as cursor:\n",
    "        assert isinstance(cursor, CMySQLCursor)\n",
    "\n",
    "        _ = cursor.execute(\"show tables\")\n",
    "        print(\"Tables:\")\n",
    "        for (table_name,) in cursor:\n",
    "            print(f\"  {table_name}\")\n",
    "except Error as err:\n",
    "    print(err)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62e2485b-1391-4fc6-aa73-35d0ebd8e95d",
   "metadata": {},
   "source": [
    "## Task 3\n",
    "**SQL query in `sql/promotional_campaign.sql`:**\n",
    "```sql\n",
    "select\n",
    "    C.FirstName,\n",
    "    C.LastName,\n",
    "    C.PhoneNumber,\n",
    "    C.EmailAddress,\n",
    "    max(O.BillAmount) as MaxBillAmount\n",
    "from Orders as O\n",
    "inner join Bookings as B on O.BookingID = B.BookingID\n",
    "inner join Customers as C on B.CustomerID = C.CustomerID\n",
    "group by C.CustomerID\n",
    "having MaxBillAmount > 60.00;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "214d0b0e-dd5b-4113-b6d7-aca020655ef3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Customers that placed an order greater then 60.00€:\n",
      "  Li Wei Zhang\n",
      "    Minimum bill amount: 82.50€\n",
      "    Phone number: +86123456789\n",
      "    Email address: li.wei.zhang@example.com\n",
      "  Ivanov Petrov\n",
      "    Minimum bill amount: 150.50€\n",
      "    Phone number: +70987654321\n",
      "    Email address: ivanov.petrov@example.com\n"
     ]
    }
   ],
   "source": [
    "with open(\"./sql/promotional_campaign.sql\") as promo_campaign_file:\n",
    "    promo_campain_request = promo_campaign_file.read()\n",
    "    \n",
    "try:\n",
    "    with connection.cursor() as cursor:\n",
    "        assert isinstance(cursor, CMySQLCursor)\n",
    "        _ = cursor.execute(promo_campain_request)\n",
    "\n",
    "        print()\n",
    "\n",
    "        print(\"Customers that placed an order greater then 60.00€:\")\n",
    "        for (\n",
    "            first_name,\n",
    "            last_name,\n",
    "            phone_number,\n",
    "            email_address,\n",
    "            min_bill_amount,\n",
    "        ) in cursor:\n",
    "            assert isinstance(first_name, str)\n",
    "            assert isinstance(last_name, str)\n",
    "            assert isinstance(phone_number, str)\n",
    "            assert isinstance(email_address, str | None)\n",
    "            assert isinstance(min_bill_amount, Decimal)\n",
    "            print(f\"  {first_name} {last_name}\")\n",
    "            print(f\"    Minimum bill amount: {min_bill_amount}€\")\n",
    "            print(f\"    Phone number: {phone_number}\")\n",
    "            if email_address:\n",
    "                print(f\"    Email address: {email_address}\")\n",
    "except Error as err:\n",
    "    print(err)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be3c702d-5ab6-47a6-a19b-4e26aefdbd6e",
   "metadata": {},
   "source": [
    "## Cleanup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e7f0a6dc-4940-4f8b-a89c-e714e9d375ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}