{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_application = \"\"\"\n",
    "    CREATE TABLE application (\n",
    "        application_id int,\n",
    "        application_number  varchar(10),\n",
    "        amount int,\n",
    "        amount_paid int,\n",
    "        state  varchar(10),\n",
    "        office_code varchar(10), \n",
    "        service_code varchar(10), \n",
    "        date_created datetime,\n",
    "        date_paid datetime,\n",
    "        date_processed datetime,\n",
    "        PRIMARY KEY (application_id)\n",
    "    );\n",
    "\"\"\"\n",
    "\n",
    "table_office =\"\"\"\n",
    "    CREATE TABLE Office (\n",
    "        office_code varchar(10),\n",
    "        office_name varchar(20),\n",
    "        location_code varchar(10),\n",
    "        PRIMARY KEY (office_code)\n",
    "        );\n",
    "\"\"\"\n",
    "\n",
    "table_location =\"\"\"\n",
    "    CREATE TABLE location (\n",
    "        location_code varchar(10),\n",
    "        location_name varchar(20),\n",
    "        PRIMARY KEY (location_code)\n",
    "        );\n",
    "\"\"\"\n",
    "\n",
    "table_service =\"\"\"\n",
    "    CREATE TABLE service (\n",
    "        service_code varchar(10),\n",
    "        service_name varchar(20),\n",
    "        PRIMARY KEY (service_code)\n",
    "        );\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x143b448c0>"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the data base\n",
    "import sqlite3\n",
    "\n",
    "DB_FILENAME = 'irembo_application_4.db'\n",
    "\n",
    "conn = sqlite3.connect(DB_FILENAME)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(table_application)\n",
    "cursor.execute(table_office)\n",
    "cursor.execute(table_location)\n",
    "cursor.execute(table_service)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "# --\n",
    "# Define Office, Location, Application and Service information\n",
    "# --\n",
    "\n",
    "office_data = [\n",
    "    ('O1', 'Office 1', 'L1'),\n",
    "    ('O2', 'Office 2', 'L2'),\n",
    "    ('O3', 'Office 3', 'L3'),\n",
    "    ('O4', 'Office 4', 'L4'),\n",
    "    ('O5', 'Office 5', 'L5'),\n",
    "    ('O6', 'Office 6', 'L6'),\n",
    "    ('O7', 'Office 7', 'L7'),\n",
    "    ('O8', 'Office 8', 'L8'),\n",
    "    ('O9', 'Office 9', 'L9'),\n",
    "    ('O10', 'Office 10', 'L10'),\n",
    "    ('O11', 'Office 11', 'L11'),\n",
    "    ('O12', 'Office 12', 'L12'),\n",
    "    ('O13', 'Office 13', 'L13'),\n",
    "    ('O14', 'Office 14', 'L14'),\n",
    "    ('O15', 'Office 15', 'L15'),\n",
    "    ('O16', 'Office 16', 'L16'),\n",
    "    ('O17', 'Office 17', 'L17'),\n",
    "]\n",
    "\n",
    "location_data = [\n",
    "    ('L1', 'Location 1'),\n",
    "    ('L2', 'Location 2'),\n",
    "    ('L3', 'Location 3'),\n",
    "    ('L4', 'Location 4'),\n",
    "    ('L5', 'Location 5'),\n",
    "    ('L6', 'Location 6'),\n",
    "    ('L7', 'Location 7'),\n",
    "    ('L8', 'Location 8'),\n",
    "]\n",
    "\n",
    "service_data = [\n",
    "    ('S1', 'Service 1'),\n",
    "    ('S2', 'Service 2'),\n",
    "    ('S3', 'Service 3'),\n",
    "    ('S4', 'Service 4'),\n",
    "    ('S5', 'Service 5'),\n",
    "    ('S6', 'Service 6'),\n",
    "    ('S7', 'Service 7'),\n",
    "    ('S8', 'Service 8'),\n",
    "]\n",
    "\n",
    "conn = sqlite3.connect(DB_FILENAME)\n",
    "cursor = conn.cursor()\n",
    "cursor.executemany('INSERT INTO Office VALUES (?,?,?)', office_data)\n",
    "cursor.executemany('INSERT INTO Location VALUES (?,?)', location_data)\n",
    "cursor.executemany('INSERT INTO Service VALUES (?,?)', service_data)\n",
    "conn.commit()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "import string\n",
    "import random\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "states = ['APPROVED','REJECTED','PENDING_PAYMENT', 'PAID']\n",
    "prices = [1000, 10000,25000, 20000, 0]\n",
    "\n",
    "# or a function\n",
    "def gen_datetime(min_year=2021, max_year=datetime.now().year):\n",
    "    # generate a datetime in format yyyy-mm-dd hh:mm:ss.000000\n",
    "\n",
    "    today_datetime = datetime.now()\n",
    "    return today_datetime - timedelta(days=random.randint(1,1100), hours=random.randint(1,23), minutes=random.randint(1,60), seconds=random.randint(1,60))\n",
    "    # start = datetime(min_year, 1, 1, 00, 00, 00)\n",
    "    # years = max_year - min_year + 1\n",
    "    # end = start + timedelta(days=365 * years)\n",
    "    # return start + (end - start) * random.random()\n",
    "\n",
    "def generate_random_application(states=states):\n",
    "    N = 8\n",
    "    application_data = []\n",
    "    strformat = '%Y-%m-%d %H:%M:%S'\n",
    "    for i in range(100000,150000):\n",
    "        creationdate = gen_datetime()\n",
    "        price = prices[random.randint(0,4)]\n",
    "        application = (\n",
    "            i+1,\n",
    "            'A0' + ''.join(random.choices(string.ascii_uppercase + string.digits, k=8)), \n",
    "            price, \n",
    "            price, \n",
    "            states[random.randint(0,3)],\n",
    "            'O' +''.join(str(random.randint(1,8))),\n",
    "            'S' +''.join(str(random.randint(1,8))),\n",
    "            creationdate.strftime(strformat),\n",
    "            (creationdate + timedelta(hours=9)).strftime(strformat),\n",
    "            (creationdate + timedelta(hours=24)).strftime(strformat),\n",
    "        )\n",
    "        \n",
    "        application_data.append(application)\n",
    "    return application_data    \n",
    "\n",
    "application_data = generate_random_application()\n",
    "\n",
    "conn = sqlite3.connect(DB_FILENAME)\n",
    "cursor = conn.cursor()\n",
    "cursor.executemany('INSERT INTO Application VALUES (?,?,?,?,?,?,?,?,?,?)', application_data)\n",
    "conn.commit()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2024-04-20', 1), ('2024-04-21', 1), ('2024-04-25', 1)]\n"
     ]
    }
   ],
   "source": [
    "def run_query(query=''):\n",
    "    conn = sqlite3.connect(DB_FILENAME)\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(query)      \n",
    "    data = cursor.fetchall()\n",
    "    print(data)\n",
    "    conn.close\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "query_trend = \"\"\"\n",
    "SELECT \n",
    "    strftime('%Y-%m-%d', date_created) AS application_date,\n",
    "    COUNT(*) AS approved_applications\n",
    "FROM \n",
    "    application\n",
    "JOIN \n",
    "    Office ON application.office_code = Office.office_code\n",
    "JOIN \n",
    "    location ON Office.location_code = location.location_code\n",
    "WHERE \n",
    "    application.state = 'APPROVED'\n",
    "    AND location.location_name = 'Location 1'\n",
    "    AND date_created >= date('now', '-9 days') -- last 10 days including today\n",
    "    AND date_created <= date('now') -- up to today\n",
    "GROUP BY \n",
    "    strftime('%Y-%m-%d', date_created)\n",
    "ORDER BY \n",
    "    strftime('%Y-%m-%d', date_created) ASC;\n",
    "\"\"\"\n",
    "\n",
    "run_query(query=query_trend)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2021-01', 27), ('2021-02', 31), ('2021-03', 30), ('2021-04', 33), ('2021-05', 40), ('2021-06', 28), ('2021-07', 27), ('2021-08', 30), ('2021-09', 31), ('2021-10', 35), ('2021-11', 26), ('2021-12', 33), ('2022-01', 32), ('2022-02', 34), ('2022-03', 39), ('2022-04', 39), ('2022-05', 30), ('2022-06', 29), ('2022-07', 35), ('2022-08', 31), ('2022-09', 37), ('2022-10', 31), ('2022-11', 31), ('2022-12', 22), ('2023-01', 33), ('2023-02', 35), ('2023-03', 34), ('2023-04', 35), ('2023-05', 28), ('2023-06', 32), ('2023-07', 26), ('2023-08', 30), ('2023-09', 26), ('2023-10', 36), ('2023-11', 37), ('2023-12', 39), ('2024-01', 39), ('2024-02', 33), ('2024-03', 29), ('2024-04', 28), ('2024-05', 37), ('2024-06', 33), ('2024-07', 33), ('2024-08', 35), ('2024-09', 34), ('2024-10', 36), ('2024-11', 26), ('2024-12', 41)]\n"
     ]
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "    strftime('%Y-%m', date_paid) AS month,\n",
    "    COUNT(*) AS approved_applications\n",
    "FROM \n",
    "    application\n",
    "WHERE \n",
    "    amount_paid = amount AND state='APPROVED' AND office_code IN (\n",
    "        SELECT \n",
    "            o.office_code\n",
    "        FROM \n",
    "            Office o, location l\n",
    "        WHERE \n",
    "            o.location_code=l.location_code AND l.location_name='Location 2'\n",
    "    )\n",
    "GROUP BY \n",
    "    month\n",
    "ORDER BY \n",
    "    month;\n",
    "\"\"\"\n",
    "\n",
    "run_query(query=query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2023-04', 34), ('2023-05', 33), ('2023-06', 32), ('2023-07', 34), ('2023-08', 32), ('2023-09', 35), ('2023-10', 37), ('2023-11', 31), ('2023-12', 32), ('2024-01', 36), ('2024-02', 28), ('2024-03', 22)]\n"
     ]
    }
   ],
   "source": [
    "query=\"\"\"\n",
    "WITH monthly_trend AS (\n",
    "    SELECT strftime('%Y-%m', date_created) AS month,\n",
    "           COUNT(*) AS approved_applications\n",
    "    FROM application\n",
    "    WHERE state = 'APPROVED'\n",
    "      AND strftime('%Y-%m', date_created) >= strftime('%Y-%m', 'now', '-12 months')\n",
    "      AND office_code IN (SELECT office_code FROM Office WHERE location_code = 'L1')\n",
    "    GROUP BY month\n",
    ")\n",
    "SELECT all_months.month, COALESCE(approved_applications, 0) AS approved_applications\n",
    "FROM (\n",
    "    SELECT strftime('%Y-%m', 'now', '-12 months') AS month\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-11 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-10 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-9 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-8 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-7 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-6 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-5 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-4 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-3 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-2 months')\n",
    "    UNION ALL\n",
    "    SELECT strftime('%Y-%m', 'now', '-1 months')\n",
    ") AS all_months\n",
    "LEFT JOIN monthly_trend ON all_months.month = monthly_trend.month;\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "run_query(query=query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2023-04', 25), ('2023-05', 170), ('2023-06', 157), ('2023-07', 153), ('2023-08', 160), ('2023-09', 158), ('2023-10', 165), ('2023-11', 159), ('2023-12', 175), ('2024-01', 186), ('2024-02', 158), ('2024-03', 148), ('2024-04', 148), ('2024-05', 154), ('2024-06', 166), ('2024-07', 158), ('2024-08', 166), ('2024-09', 156), ('2024-10', 164), ('2024-11', 149), ('2024-12', 170)]\n"
     ]
    }
   ],
   "source": [
    "query =\"\"\" \n",
    "SELECT \n",
    "    strftime('%Y-%m', date_processed) AS month, \n",
    "    COUNT(*) as approved_applications\n",
    "FROM application\n",
    "WHERE state = 'APPROVED' AND office_code IN (\n",
    "    SELECT office_code FROM Office WHERE location_code in ('L1','L2','L3','L4','L5')\n",
    ") AND date_processed >= DATE('now', '-12 months')\n",
    "GROUP BY strftime('%Y-%m', date_processed)\n",
    "ORDER BY month;\n",
    "\"\"\"\n",
    "\n",
    "run_query(query=query)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sample-projects",
   "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}