etl2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Generate Fake Data and Insert rows into OLTP function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#def generate_and_insert_data(num_rows)\n",
    "import sqlite3\n",
    "from faker import Faker\n",
    "import random\n",
    "from datetime import timedelta\n",
    "\n",
    "def generate_and_insert_data(num_rows):\n",
    "    conn = sqlite3.connect('newhotel.db')\n",
    "    c = conn.cursor()\n",
    "\n",
    "    # Get the maximum IDs from the tables\n",
    "    max_guest_id = c.execute('SELECT MAX(GuestID) FROM guests').fetchone()[0] or 0\n",
    "    max_room_id = c.execute('SELECT MAX(RoomID) FROM rooms').fetchone()[0] or 0\n",
    "    max_booking_id = c.execute('SELECT MAX(BookingID) FROM bookings').fetchone()[0] or 0\n",
    "    max_employee_id = c.execute('SELECT MAX(EmployeeID) FROM employees').fetchone()[0] or 0\n",
    "    max_service_id = c.execute('SELECT MAX(ServiceID) FROM services').fetchone()[0] or 0\n",
    "    max_service_booking_id = c.execute('SELECT MAX(ServiceBookingID) FROM servicebookings').fetchone()[0] or 0\n",
    "    max_payment_id = c.execute('SELECT MAX(PaymentID) FROM payments').fetchone()[0] or 0\n",
    "    max_review_id = c.execute('SELECT MAX(ReviewID) FROM reviews').fetchone()[0] or 0\n",
    "    max_maintenance_id = c.execute('SELECT MAX(MaintenanceID) FROM roommaintenance').fetchone()[0] or 0\n",
    "\n",
    "    fake = Faker()\n",
    "\n",
    "    # Data Lists\n",
    "    guests = []\n",
    "    rooms = []\n",
    "    bookings = []\n",
    "    employees = []\n",
    "    services = []\n",
    "    service_bookings = []\n",
    "    payments = []\n",
    "    reviews = []\n",
    "    room_maintenance = []\n",
    "\n",
    "        # Fill data lists\n",
    "    for i in range(num_rows):\n",
    "        guests.append((max_guest_id + i + 1, fake.first_name(), fake.last_name(), fake.email(), fake.phone_number(),\n",
    "                    str(fake.date_of_birth(minimum_age=18, maximum_age=85)), fake.country(), random.choice([True, False])))\n",
    "        rooms.append((max_room_id + i + 1, random.choice(['Single', 'Double', 'Suite']), round(random.uniform(100, 400), 2),\n",
    "                    random.choice(['Available', 'Occupied', 'Maintenance']), random.randint(1, 10)))\n",
    "\n",
    "        check_in = fake.date_this_year()\n",
    "        check_out = check_in + timedelta(days=random.randint(1, 5))\n",
    "        bookings.append((max_booking_id + i + 1, random.randint(1, num_rows), random.randint(1, num_rows), str(check_in), str(check_out),\n",
    "                        random.randint(1, 4), round(random.uniform(100.0, 1000.0), 2), round(random.uniform(5.0, 100.0), 2)))\n",
    "\n",
    "        employees.append((max_employee_id + i + 1, fake.first_name(), fake.last_name(), random.choice(['Manager', 'Receptionist', 'Housekeeper', 'Chef']),\n",
    "                        str(fake.date_this_decade()), round(random.uniform(30000, 60000), 2), random.choice(['Administration', 'Housekeeping', 'Kitchen', 'Front Desk']),\n",
    "                        random.randint(1, 10)))\n",
    "\n",
    "        services.append((max_service_id + i + 1, fake.word(), fake.word(), round(random.uniform(20, 100), 2), round(random.uniform(10, 50), 2), fake.time()))\n",
    "\n",
    "        service_bookings.append((max_service_booking_id + i + 1, random.randint(1, num_rows), random.randint(1, num_rows), str(fake.date_this_year()), fake.time(), random.randint(1, 5),\n",
    "                                round(random.uniform(20.0, 200.0), 2)))\n",
    "\n",
    "        payments.append((max_payment_id + i + 1, random.randint(1, num_rows), str(fake.date_this_year()), random.choice(['Cash', 'Credit', 'Debit']),\n",
    "                    round(random.uniform(100.0, 1000.0), 2), round(random.uniform(5.0, 100.0), 2), round(random.uniform(100.0, 150.0), 2), round(random.uniform(0.0, 50.0), 2)))\n",
    "\n",
    "        reviews.append((max_review_id + i + 1, random.randint(1, num_rows), random.randint(1, 5), fake.text(), fake.text(), random.randint(1, 5)))\n",
    "\n",
    "        room_maintenance.append((max_maintenance_id + i + 1, random.randint(1, num_rows), random.randint(1, num_rows), str(fake.date_this_year()), str(fake.date_this_year()),\n",
    "                                fake.word(), random.choice(['Pending', 'In Progress', 'Completed']), round(random.uniform(50, 500), 2), random.randint(1, 24)))\n",
    "\n",
    "        \n",
    "        # Connect to the SQLite database\n",
    "    with sqlite3.connect('newhotel.db') as conn:\n",
    "        c = conn.cursor()\n",
    "\n",
    "        # Insert data into the database\n",
    "        for guest in guests:\n",
    "            c.execute(\"INSERT INTO guests VALUES (?, ?, ?, ?, ?, ?, ?, ?)\", guest)\n",
    "        for room in rooms:\n",
    "            c.execute(\"INSERT INTO rooms VALUES (?, ?, ?, ?, ?)\", room)\n",
    "        for booking in bookings:\n",
    "            c.execute(\"INSERT INTO bookings VALUES (?, ?, ?, ?, ?, ?, ?, ?)\", booking)\n",
    "        for employee in employees:\n",
    "            c.execute(\"INSERT INTO employees VALUES (?, ?, ?, ?, ?, ?, ?, ?)\", employee)\n",
    "        for service in services:\n",
    "            c.execute(\"INSERT INTO services VALUES (?, ?, ?, ?, ?, ?)\", service)\n",
    "        for service_booking in service_bookings:\n",
    "            c.execute(\"INSERT INTO servicebookings VALUES (?, ?, ?, ?, ?, ?, ?)\", service_booking)\n",
    "        for payment in payments:\n",
    "            c.execute(\"INSERT INTO payments VALUES (?, ?, ?, ?, ?, ?, ?, ?)\", payment)\n",
    "        for review in reviews:\n",
    "            c.execute(\"INSERT INTO reviews VALUES (?, ?, ?, ?, ?, ?)\", review)\n",
    "        for maintenance in room_maintenance:\n",
    "            c.execute(\"INSERT INTO roommaintenance VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\", maintenance)\n",
    "\n",
    "        # Commit changes\n",
    "        conn.commit()\n",
    "\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The table guests has 4650 rows.\n",
      "The table rooms has 4650 rows.\n",
      "The table bookings has 4650 rows.\n",
      "The table employees has 4650 rows.\n",
      "The table services has 4650 rows.\n",
      "The table servicebookings has 4650 rows.\n",
      "The table payments has 4650 rows.\n",
      "The table reviews has 4650 rows.\n",
      "The table roommaintenance has 4649 rows.\n"
     ]
    }
   ],
   "source": [
    "#check if rows got inserted \n",
    "def count_rows():\n",
    "    conn = sqlite3.connect('newhotel.db')\n",
    "    c = conn.cursor()\n",
    "\n",
    "    tables = ['guests', 'rooms', 'bookings', 'employees', 'services', 'servicebookings', 'payments', 'reviews', 'roommaintenance']\n",
    "\n",
    "    for table in tables:\n",
    "        count = c.execute(f'SELECT COUNT(*) FROM {table}').fetchone()[0]\n",
    "        print(f'The table {table} has {count} rows.')\n",
    "\n",
    "    conn.close()\n",
    "\n",
    "# Call the function\n",
    "count_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Staging Area Ingestion function (data frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#def create_staging_area():\n",
    "\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "def create_staging_area():\n",
    "    conn = sqlite3.connect('newhotel.db')\n",
    "\n",
    "    # Read data from guests table into a DataFrame\n",
    "    df_guests = pd.read_sql_query(\"SELECT guestid, firstname, lastname, country, vipstatus, DateOfBirth FROM guests\", conn)\n",
    "\n",
    "   # Read data from bookings and rooms tables into a DataFrame\n",
    "    df_dimbooking = pd.read_sql_query(\"\"\"\n",
    "        SELECT bookings.bookingid, bookings.guestid, bookings.roomid, rooms.roomtype, rooms.pricepernight, bookings.numberofguests, bookings.discounts, bookings.totalbill, bookings.checkindate, bookings.checkoutdate\n",
    "        FROM bookings \n",
    "        JOIN rooms ON bookings.roomid = rooms.roomid\n",
    "    \"\"\", conn)\n",
    "\n",
    "    # Read data from services and service bookings tables into a DataFrame\n",
    "    df_servicesbooking = pd.read_sql_query(\"\"\"\n",
    "        SELECT ServiceBookings.ServiceBookingID, ServiceBookings.BookingID, ServiceBookings.ServiceID, ServiceBookings.Date, Services.ServiceType, Services.ServiceName, Services.Price, Services.Cost, ServiceBookings.TotalServiceCost\n",
    "        FROM ServiceBookings\n",
    "        JOIN Services ON ServiceBookings.ServiceID = Services.ServiceID\n",
    "    \"\"\", conn)\n",
    "\n",
    "    # Read data from payments table into a DataFrame\n",
    "    df_payments = pd.read_sql_query(\"\"\"\n",
    "    SELECT PaymentID, BookingID, PaymentMethod, PaymentDate, AmountPaid, AmountDue, Revenue\n",
    "    FROM Payments\n",
    "    \"\"\", conn)\n",
    "\n",
    "    # Create a new DataFrame for the fact table\n",
    "    df_fact = pd.DataFrame()\n",
    "\n",
    "    # Copy guestid from df_guests into df_fact\n",
    "    df_fact['GuestID'] = df_guests['GuestID'].copy()\n",
    "\n",
    "    # Copy required columns from df_dimbooking into df_fact\n",
    "    df_fact['BookingID'] = df_dimbooking['BookingID'].copy()\n",
    "    df_fact['PricePerNight'] = df_dimbooking['PricePerNight'].copy()\n",
    "    df_fact['NumberOfGuests'] = df_dimbooking['NumberOfGuests'].copy()\n",
    "    df_fact['Discounts'] = df_dimbooking['Discounts'].copy()\n",
    "    df_fact['TotalBill'] = df_dimbooking['TotalBill'].copy()\n",
    "\n",
    "    # Drop specified columns from df_dimbooking\n",
    "    df_dimbooking = df_dimbooking.drop(['PricePerNight', 'NumberOfGuests', 'Discounts', 'TotalBill'], axis=1)\n",
    "\n",
    "    # Copy required columns from df_servicesbooking into df_fact\n",
    "    df_fact['ServiceBookingID'] = df_servicesbooking['ServiceBookingID'].copy()\n",
    "    df_fact['Price'] = df_servicesbooking['Price'].copy()\n",
    "    df_fact['Cost'] = df_servicesbooking['Cost'].copy()\n",
    "    df_fact['TotalServiceCost'] = df_servicesbooking['TotalServiceCost'].copy()\n",
    "\n",
    "    # Drop specified columns from df_servicesbooking\n",
    "    df_servicesbooking = df_servicesbooking.drop(['Price', 'Cost', 'TotalServiceCost'], axis=1)\n",
    "\n",
    "    # Copy required columns from df_payments into df_fact\n",
    "    df_fact['PaymentID'] = df_payments['PaymentID'].copy()\n",
    "    df_fact['AmountDue'] = df_payments['AmountDue'].copy()\n",
    "    df_fact['AmountPaid'] = df_payments['AmountPaid'].copy()\n",
    "    df_fact['Revenue'] = df_payments['Revenue'].copy()\n",
    "\n",
    "    # Drop specified columns from df_payments\n",
    "    df_payments = df_payments.drop(['AmountDue', 'AmountPaid', 'Revenue'], axis=1)\n",
    "\n",
    "    # Close connection\n",
    "    conn.close()\n",
    "\n",
    "    return df_guests, df_dimbooking, df_servicesbooking, df_payments, df_fact\n",
    "\n",
    "# Call the function and print the DataFrame\n",
    "df_guests, df_dimbooking, df_servicesbooking, df_payments, df_fact = create_staging_area()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cleaning Function (Imputes missing values using mean and mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_df(df):\n",
    "    for col in df.columns:\n",
    "        if pd.api.types.is_numeric_dtype(df[col]):\n",
    "            df[col] = df[col].fillna(df[col].mean())\n",
    "        elif pd.api.types.is_string_dtype(df[col]):\n",
    "            df[col] = df[col].fillna(df[col].mode()[0])\n",
    "        elif pd.api.types.is_datetime64_any_dtype(df[col]):\n",
    "            df[col] = pd.to_datetime(df[col], errors='coerce')\n",
    "            if df[col].isnull().any():\n",
    "                print(f\"Column {col} could not be converted to datetime.\")\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creation of New Attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_stay_duration(df):\n",
    "    # Convert CheckInDate and CheckOutDate to datetime\n",
    "    df['CheckInDate'] = pd.to_datetime(df['CheckInDate'], format='%Y-%m-%d', errors='coerce')\n",
    "    df['CheckOutDate'] = pd.to_datetime(df['CheckOutDate'], format='%Y-%m-%d', errors='coerce')\n",
    "\n",
    "    # Check if conversion was successful\n",
    "    if df['CheckInDate'].isnull().any() or df['CheckOutDate'].isnull().any():\n",
    "        print(\"CheckInDate or CheckOutDate could not be converted to datetime.\")\n",
    "        return df\n",
    "\n",
    "    # Create StayDuration attribute\n",
    "    df['StayDuration'] = (df['CheckOutDate'] - df['CheckInDate']).dt.days\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mapping Data From Staging Area to Data Mart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mapping function for each dimension\n",
    "import sqlite3\n",
    "\n",
    "def map_to_dimGuests(df, conn):\n",
    "    cur = conn.cursor()\n",
    "    for _, row in df.iterrows():\n",
    "        cur.execute(\"SELECT * FROM dimGuests WHERE GuestID = ?\", (row['GuestID'],))\n",
    "        data = cur.fetchone()\n",
    "        if data is None:\n",
    "            cur.execute(\"INSERT INTO dimGuests VALUES (?, ?, ?, ?, ?, ?)\", \n",
    "                        (row['GuestID'], row['FirstName'], row['LastName'], row['Country'], row['VIPStatus'], row['DateOfBirth']))\n",
    "    conn.commit()\n",
    "\n",
    "def map_to_dimBooking(df, conn):\n",
    "    cur = conn.cursor()\n",
    "    for _, row in df.iterrows():\n",
    "        cur.execute(\"SELECT * FROM dimBooking WHERE BookingID = ?\", (row['BookingID'],))\n",
    "        data = cur.fetchone()\n",
    "        if data is None:\n",
    "            # Convert 'CheckInDate' and 'CheckOutDate' to string format\n",
    "            check_in_date = row['CheckInDate'].strftime('%Y-%m-%d %H:%M:%S')\n",
    "            check_out_date = row['CheckOutDate'].strftime('%Y-%m-%d %H:%M:%S')\n",
    "            cur.execute(\"INSERT INTO dimBooking VALUES (?, ?, ?, ?, ?, ?, ?)\", \n",
    "                        (row['BookingID'], row['GuestID'], row['RoomID'], row['RoomType'], check_in_date, check_out_date, row['StayDuration']))\n",
    "    conn.commit()\n",
    "\n",
    "def map_to_dimServicesBooking(df, conn):\n",
    "    cur = conn.cursor()\n",
    "    for _, row in df.iterrows():\n",
    "        cur.execute(\"SELECT * FROM dimServicesBooking WHERE ServiceBookingID = ?\", (row['ServiceBookingID'],))\n",
    "        data = cur.fetchone()\n",
    "        if data is None:\n",
    "            cur.execute(\"INSERT INTO dimServicesBooking VALUES (?, ?, ?, ?, ?, ?)\", \n",
    "                        (row['ServiceBookingID'], row['BookingID'], row['ServiceID'], row['Date'], row['ServiceType'], row['ServiceName']))\n",
    "    conn.commit()\n",
    "\n",
    "def map_to_dimPayments(df, conn):\n",
    "    cur = conn.cursor()\n",
    "    for _, row in df.iterrows():\n",
    "        cur.execute(\"SELECT * FROM dimPayments WHERE PaymentID = ?\", (row['PaymentID'],))\n",
    "        data = cur.fetchone()\n",
    "        if data is None:\n",
    "            cur.execute(\"INSERT INTO dimPayments VALUES (?, ?, ?, ?)\", \n",
    "                        (row['PaymentID'], row['BookingID'], row['PaymentMethod'], row['PaymentDate']))\n",
    "    conn.commit()\n",
    "\n",
    "def map_to_factTable(df, conn):\n",
    "    cur = conn.cursor()\n",
    "    for _, row in df.iterrows():\n",
    "        cur.execute(\"SELECT * FROM factTable WHERE BookingID = ? AND PaymentID = ?\", (row['BookingID'], row['PaymentID']))\n",
    "        data = cur.fetchone()\n",
    "        if data is None:\n",
    "            cur.execute(\"INSERT INTO factTable VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\", \n",
    "                        (row['BookingID'], row['PaymentID'], row['GuestID'], row['ServiceBookingID'], row['PricePerNight'], row['NumberOfGuests'], row['TotalBill'], row['Discounts'], row['Price'], row['Cost'], row['TotalServiceCost'], row['AmountPaid'], row['AmountDue'], row['Revenue']))\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Generate Snapshot Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_fact_table_snapshot(conn):\n",
    "    # Query to join the fact table with the four dimension tables\n",
    "    query = \"\"\"\n",
    "    SELECT *\n",
    "    FROM factTable\n",
    "    LEFT JOIN dimGuests ON factTable.GuestID = dimGuests.GuestID\n",
    "    LEFT JOIN dimBooking ON factTable.BookingID = dimBooking.BookingID\n",
    "    LEFT JOIN dimServicesBooking ON factTable.ServiceBookingID = dimServicesBooking.ServiceBookingID\n",
    "    LEFT JOIN dimPayments ON factTable.PaymentID = dimPayments.PaymentID\n",
    "    \"\"\"\n",
    "    \n",
    "    # Execute the query and store the result in a DataFrame\n",
    "    df_fact_table_snapshot = pd.read_sql_query(query, conn)\n",
    "    \n",
    "    return df_fact_table_snapshot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create Dashboard Function and % Change Dashboard Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import dash\n",
    "import os\n",
    "import dash_core_components as dcc\n",
    "import dash_html_components as html\n",
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "def create_dashboard(df_snapshot, old_snapshot_filename='old_snapshot.csv'):\n",
    "    # Load the old snapshot\n",
    "    try:\n",
    "        df_old_snapshot = pd.read_csv(old_snapshot_filename)\n",
    "    except FileNotFoundError:\n",
    "        df_old_snapshot = pd.DataFrame()\n",
    "\n",
    "    # Create a Dash app\n",
    "    app = dash.Dash(__name__)\n",
    "\n",
    "    # Calculate total revenue for each country and get the top 10\n",
    "    top_countries = df_snapshot.groupby('Country')['Revenue'].sum().nlargest(10).reset_index()\n",
    "\n",
    "    # If old snapshot exists, calculate the percentage change for each country\n",
    "    if not df_old_snapshot.empty:\n",
    "        old_top_countries = df_old_snapshot.groupby('Country')['Revenue'].sum().nlargest(10).reset_index()\n",
    "        top_countries = top_countries.merge(old_top_countries, on='Country', how='left', suffixes=('_new', '_old'))\n",
    "        top_countries['Revenue_change'] = ((top_countries['Revenue_new'] - top_countries['Revenue_old']) / top_countries['Revenue_old']) * 100\n",
    "\n",
    "    # Calculate total revenue\n",
    "    total_revenue = df_snapshot['Revenue'].sum()\n",
    "\n",
    "    # Calculate the change in total revenue\n",
    "    if not df_old_snapshot.empty:\n",
    "        old_total_revenue = df_old_snapshot['Revenue'].sum()\n",
    "        revenue_change = ((total_revenue - old_total_revenue) / old_total_revenue) * 100\n",
    "    else:\n",
    "        revenue_change = 0\n",
    "\n",
    "    # Create a bar chart for the total revenue for the top 10 countries\n",
    "    fig1 = px.bar(top_countries, x='Country', y='Revenue_new', title='Total Revenue for Top 10 Countries', \n",
    "                  labels={'Country': 'Country', 'Revenue_new': 'Total Revenue'}, \n",
    "                  color='Country', barmode='group')\n",
    "\n",
    "    # Create a bar chart for the revenue change for the top 10 countries\n",
    "    fig2 = px.bar(top_countries, x='Country', y='Revenue_change', title='Revenue Change for Top 10 Countries', \n",
    "                  labels={'Country': 'Country', 'Revenue_change': 'Revenue Change (%)'}, \n",
    "                  color='Country', barmode='group')\n",
    "\n",
    "    # Convert 'PaymentDate' to datetime\n",
    "    df_snapshot['PaymentDate'] = pd.to_datetime(df_snapshot['PaymentDate'])\n",
    "\n",
    "    # Group by month and sum the revenue\n",
    "    df_snapshot['Month'] = df_snapshot['PaymentDate'].dt.to_period('M')\n",
    "    monthly_revenue = df_snapshot.groupby('Month')['Revenue'].sum().reset_index()\n",
    "    monthly_revenue['Month'] = monthly_revenue['Month'].astype(str)\n",
    "\n",
    "    # Create a line chart for the monthly revenue\n",
    "    fig3 = px.line(monthly_revenue, x='Month', y='Revenue', title='Monthly Revenue',\n",
    "                   labels={'Month': 'Month', 'Revenue': 'Revenue'})\n",
    "\n",
    "    # Define the layout of the app\n",
    "    app.layout = html.Div(children=[\n",
    "        html.H1(children='Hotel Management Dashboard'),\n",
    "\n",
    "        html.Div(children='''Dash: A web application framework for Python.'''),\n",
    "\n",
    "        html.H2(children=f'Total Revenue: ${total_revenue:,.2f}'),\n",
    "        html.H2(children=f'Revenue Change: {revenue_change:.2f}%'),\n",
    "\n",
    "        dcc.Graph(id='example-graph-1', figure=fig1),\n",
    "\n",
    "        dcc.Graph(id='example-graph-2', figure=fig2),\n",
    "\n",
    "        dcc.Graph(id='example-graph-3', figure=fig3)\n",
    "    ])\n",
    "\n",
    "    # Run the app\n",
    "    app.run_server(port=8050)\n",
    "\n",
    "    # Save the current snapshot for the next execution\n",
    "    df_snapshot.to_csv(old_snapshot_filename, index=False)\n",
    "\n",
    "# Example usage\n",
    "# df_snapshot = pd.read_csv('your_data.csv')\n",
    "# create_dashboard(df_snapshot)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def master_etl_function(): \n",
    "    # Generate and insert data \n",
    "    generate_and_insert_data(50) \n",
    "\n",
    "    # Staging area\n",
    "    df_guests, df_dimbooking, df_servicesbooking, df_payments, df_fact = create_staging_area()\n",
    "\n",
    "    # Call clean_data() \n",
    "    df_guests = clean_df(df_guests)\n",
    "    df_dimbooking = clean_df(df_dimbooking)\n",
    "    df_servicesbooking = clean_df(df_servicesbooking)\n",
    "    df_payments = clean_df(df_payments)\n",
    "    df_fact = clean_df(df_fact)\n",
    "\n",
    "    # Create new attributes in Booking dimension\n",
    "    df_dimbooking = add_stay_duration(df_dimbooking)\n",
    "\n",
    "    import sqlite3\n",
    "\n",
    "    try:\n",
    "        # Establish a connection to the SQLite database\n",
    "        with sqlite3.connect('hmart.db') as conn:\n",
    "            # Assuming your cleaned DataFrames are named 'df_guests', 'df_booking', 'df_services_booking', 'df_payments', and 'df_fact_table'\n",
    "            map_to_dimGuests(df_guests, conn)\n",
    "            map_to_dimBooking(df_dimbooking, conn)\n",
    "            map_to_dimServicesBooking(df_servicesbooking, conn)\n",
    "            map_to_dimPayments(df_payments, conn)\n",
    "            map_to_factTable(df_fact, conn)\n",
    "\n",
    "        # Create a snapshot of the fact table\n",
    "        with sqlite3.connect('hmart.db') as conn:\n",
    "            # Generate the fact table snapshot\n",
    "            df_fact_table_snapshot = generate_fact_table_snapshot(conn)\n",
    "\n",
    "        # Call the dashboard creation functions\n",
    "        create_dashboard(df_fact_table_snapshot)\n",
    "\n",
    "    except Exception as e:\n",
    "        print(f\"An error occurred: {e}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8050/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x2bb2d413190>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "master_etl_function()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database unlocked successfully.\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "def unlock_database(db_path):\n",
    "    try:\n",
    "        conn = sqlite3.connect(db_path)\n",
    "        conn.execute(\"BEGIN EXCLUSIVE\")\n",
    "        conn.commit()\n",
    "        print(\"Database unlocked successfully.\")\n",
    "    except sqlite3.OperationalError:\n",
    "        print(\"Database is locked.\")\n",
    "    finally:\n",
    "        if conn:\n",
    "            conn.close()\n",
    "\n",
    "# Use the function\n",
    "unlock_database('hmart.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table: dimGuests\n",
      "DDL:\n",
      "CREATE TABLE dimGuests (\n",
      "            GuestID INT PRIMARY KEY,\n",
      "            FirstName TEXT,\n",
      "            LastName TEXT,\n",
      "            Country TEXT,\n",
      "            VIPStatus TEXT,\n",
      "            DateOfBirth TEXT\n",
      "        )\n",
      "\n",
      "Table: dimBooking\n",
      "DDL:\n",
      "CREATE TABLE dimBooking (\n",
      "            BookingID INT PRIMARY KEY,\n",
      "            GuestID INT,\n",
      "            RoomID INT,\n",
      "            RoomType TEXT,\n",
      "            CheckInDate TEXT,\n",
      "            CheckOutDate TEXT,\n",
      "            StayDuration INT\n",
      "        )\n",
      "\n",
      "Table: dimServicesBooking\n",
      "DDL:\n",
      "CREATE TABLE dimServicesBooking (\n",
      "            ServiceBookingID INT PRIMARY KEY,\n",
      "            BookingID INT,\n",
      "            ServiceID INT,\n",
      "            Date TEXT,\n",
      "            ServiceType TEXT,\n",
      "            ServiceName TEXT\n",
      "        )\n",
      "\n",
      "Table: dimPayments\n",
      "DDL:\n",
      "CREATE TABLE dimPayments (\n",
      "            PaymentID INT PRIMARY KEY,\n",
      "            BookingID INT,\n",
      "            PaymentMethod TEXT,\n",
      "            PaymentDate TEXT\n",
      "        )\n",
      "\n",
      "Table: factTable\n",
      "DDL:\n",
      "CREATE TABLE factTable (\n",
      "            BookingID INT PRIMARY KEY,\n",
      "            PaymentID INT,\n",
      "            GuestID INT,\n",
      "            ServiceBookingID INT,   \n",
      "            PricePerNight REAL,\n",
      "            NumberOfGuests INT,\n",
      "            TotalBill REAL,\n",
      "            Discounts REAL,\n",
      "            Price REAL,\n",
      "            Cost REAL,\n",
      "            TotalServiceCost REAL,\n",
      "            AmountPaid REAL,\n",
      "            AmountDue REAL,\n",
      "            Revenue REAL,\n",
      "            FOREIGN KEY (BookingID) REFERENCES dimBooking(BookingID),\n",
      "            FOREIGN KEY (PaymentID) REFERENCES dimPayments(PaymentID),\n",
      "            FOREIGN KEY (GuestID) REFERENCES dimGuests(GuestID),\n",
      "            FOREIGN KEY (ServiceBookingID) REFERENCES dimServicesBooking(ServiceBookingID)\n",
      "        )\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect('hmart.db')\n",
    "\n",
    "# Create a cursor object\n",
    "c = conn.cursor()\n",
    "\n",
    "# Query the sqlite_master table to get the DDL scripts\n",
    "c.execute(\"SELECT name, sql FROM sqlite_master WHERE type='table';\")\n",
    "\n",
    "# Fetch all results\n",
    "tables = c.fetchall()\n",
    "\n",
    "# Print the DDL scripts\n",
    "for table in tables:\n",
    "    print(f\"Table: {table[0]}\\nDDL:\\n{table[1]}\\n\")\n",
    "\n",
    "# Close the connection\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}