{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL work sheet for running queries on export shopify data.\n",
    "\n",
    "Requires local postgres database and CSV export of shopify data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import psycopg2\n",
    "\n",
    "conn_params = {\n",
    "    \"dbname\": \"localv1db\",\n",
    "    \"user\": \"postgres\",\n",
    "    \"password\": \"postgres_admin_password\",\n",
    "    \"host\": \"127.0.0.1\",\n",
    "    \"port\": \"5432\"\n",
    "}\n",
    "\n",
    "csv_file_path = '../ShopifyDataExportNov2023/orders_export_2.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "conn = psycopg2.connect(**conn_params)\n",
    "cursor = conn.cursor()\n",
    "\n",
    "table_name = \"shopify_export_2\"\n",
    "\n",
    "with open(csv_file_path, newline='', encoding='utf-8') as csvfile:\n",
    "    reader = csv.reader(csvfile)\n",
    "    headers = next(reader)\n",
    "    formatted_headers = [header.lower().replace(' ', '_') for header in headers]\n",
    "    create_table_statement = f\"CREATE TABLE {table_name} (\\n\"\n",
    "    create_table_statement += \",\\n\".join([f\"{header} varchar\" for header in formatted_headers])\n",
    "    create_table_statement += \"\\n);\"\n",
    "    cursor.execute(create_table_statement)\n",
    "    conn.commit()\n",
    "\n",
    "with open(csv_file_path, 'r', encoding='utf-8') as f:\n",
    "    # Skip header\n",
    "    next(f)\n",
    "    cursor.copy_expert(f\"COPY {table_name} FROM STDIN WITH CSV\", f)\n",
    "    conn.commit()\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "# Connect to PostgreSQL\n",
    "conn = psycopg2.connect(**conn_params)\n",
    "cursor = conn.cursor()\n",
    "\n",
    "table_name = \"shopify_export\"\n",
    "\n",
    "query = f\"\"\"\n",
    "    WITH UNFULFILLED_PAIRS AS (\n",
    "        SELECT UNFULFILLED_BEYONDS.name as beyond_order_name, UNFULFILLED_LENS.name as lens_order_name FROM\n",
    "        (\n",
    "            select name, email from shopify_export_2 where name in \n",
    "            (\n",
    "                select distinct(LINE_ITEM_COUNTS.name) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export_2 where fulfillment_status = 'unfulfilled' and financial_status IN ('paid')\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                ) \n",
    "                as LINE_ITEM_COUNTS where beyond_count = 1 and lens_count = 0\n",
    "            ) order by name, email\n",
    "        ) as UNFULFILLED_BEYONDS\n",
    "        INNER JOIN\n",
    "        (\n",
    "            select name, email from shopify_export_2 where name in\n",
    "            (\n",
    "                select distinct(LINE_ITEM_COUNTS.name) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export_2 where fulfillment_status = 'unfulfilled' and financial_status = 'partially_refunded'\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                )\n",
    "                as LINE_ITEM_COUNTS where beyond_count = 0 and cushion_count = 0 and lens_count = 1\n",
    "            ) group by name, email\n",
    "        ) as UNFULFILLED_LENS\n",
    "        ON UNFULFILLED_BEYONDS.email = UNFULFILLED_LENS.email\n",
    "    ) SELECT distinct beyond_order_name, lens_order_name FROM UNFULFILLED_PAIRS;\n",
    "\"\"\"\n",
    "cursor.execute(query)\n",
    "rows = cursor.fetchall()\n",
    "with open('./pairs.json', 'w') as file:\n",
    "    json.dump(rows, file, indent=4)\n",
    "#for row in rows:\n",
    "#    print(row)  # sku is a tuple, where the first element is the lineitem_sku\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT count(*) FROM shopify_export where lineitem_sku = 'BS1RL';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT count(*) from shopify_export where CAST(NULLIF(regexp_replace(subtotal, '[^\\d.]', '', 'g'), '') AS NUMERIC) < 200;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT count(distinct(name)) FROM shopify_export where financial_status = 'paid';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select distinct(bbb.name) from (\n",
    "SELECT\n",
    "  name,\n",
    "  COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "  COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "  COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "FROM shopify_export\n",
    "WHERE name IN (SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid')\n",
    "GROUP BY name) as bbb where beyond_count = 0 and cushion_count = 0 and lens_count = 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select distinct(bbb.name) from (\n",
    "SELECT\n",
    "  name,\n",
    "  COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "  COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "  COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "FROM shopify_export\n",
    "WHERE name IN (SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'fulfilled' and financial_status = 'paid')\n",
    "GROUP BY name) as bbb where beyond_count = 1 and lens_count = 0;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select distinct(bbb.name) from (\n",
    "    SELECT\n",
    "        name,\n",
    "        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "    FROM shopify_export\n",
    "    WHERE name IN (SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid')\n",
    "    AND email IN (\n",
    "        SELECT FULFILLED_BEYONDS.* FROM\n",
    "        (\n",
    "            select distinct(email) as email from shopify_export where name in \n",
    "            (\n",
    "                select distinct(LINE_ITEM_COUNTS.name) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid'\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                ) \n",
    "                as LINE_ITEM_COUNTS where beyond_count = 1 and lens_count = 0\n",
    "            ) group by email\n",
    "        ) as FULFILLED_BEYONDS\n",
    "        INNER JOIN\n",
    "        (\n",
    "            select distinct(email) as email from shopify_export where name in\n",
    "            (\n",
    "                select distinct(LINE_ITEM_COUNTS.name) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid'\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                )\n",
    "                as LINE_ITEM_COUNTS where beyond_count = 0 and cushion_count = 0 and lens_count = 1\n",
    "            ) group by email\n",
    "        ) as UNFULFILLED_LENS\n",
    "        ON FULFILLED_BEYONDS.email = UNFULFILLED_LENS.email\n",
    "    ) GROUP BY name\n",
    ") as bbb where beyond_count = 0 and cushion_count = 0 and lens_count = 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT SUM(LINE_ITEM_COUNTS.beyond_count) as beyond_count, SUM(LINE_ITEM_COUNTS.cushion_count) as cushion_count, SUM(LINE_ITEM_COUNTS.lens_count) as lens_count, SUM(LINE_ITEM_COUNTS.prescription_lens_count) as prescription_lens_count FROM\n",
    "            (SELECT\n",
    "                name,\n",
    "                COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count,\n",
    "                COUNT(CASE WHEN lineitem_sku = 'BS1RL' THEN 1 END) AS prescription_lens_count\n",
    "            FROM shopify_export\n",
    "            WHERE name IN (\n",
    "                SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid'\n",
    "            )\n",
    "            GROUP BY name) as LINE_ITEM_COUNTS;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "WITH PaginatedItems AS (\n",
    "    SELECT *, COUNT(*) OVER() As TotalCount FROM shopify_export where name in (select distinct(name) from shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid') order by name limit 50 offset 0\n",
    ") select * from PaginatedItems;\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "    WITH UNFULFILLED_PAIRS AS (\n",
    "        SELECT UNFULFILLED_BEYONDS.name as beyond_order_name, UNFULFILLED_LENS.name as lens_order_name FROM\n",
    "        (\n",
    "            select name, email from shopify_export_2 where name in \n",
    "            (\n",
    "                select distinct(LINE_ITEM_COUNTS.name) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export_2 where fulfillment_status = 'unfulfilled' and financial_status = 'paid'\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                ) \n",
    "                as LINE_ITEM_COUNTS where beyond_count = 1 and lens_count = 0\n",
    "            ) order by name, email\n",
    "        ) as UNFULFILLED_BEYONDS\n",
    "        INNER JOIN\n",
    "        (\n",
    "            select name, email from shopify_export_2 where name in\n",
    "            (\n",
    "                select distinct(LINE_ITEM_COUNTS.name) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export_2 where fulfillment_status = 'unfulfilled' and financial_status = 'paid'\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                )\n",
    "                as LINE_ITEM_COUNTS where beyond_count = 0 and cushion_count = 0 and lens_count = 1\n",
    "            ) group by name, email\n",
    "        ) as UNFULFILLED_LENS\n",
    "        ON UNFULFILLED_BEYONDS.email = UNFULFILLED_LENS.email\n",
    "    ) SELECT distinct beyond_order_name, lens_order_name FROM UNFULFILLED_PAIRS;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "                select count(*) from (\n",
    "                    SELECT\n",
    "                        name,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1B00' THEN 1 END) AS beyond_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1F' THEN 1 END) AS cushion_count,\n",
    "                        COUNT(CASE WHEN lineitem_sku = 'BS1R' THEN 1 END) AS lens_count\n",
    "                    FROM shopify_export\n",
    "                    WHERE name IN (\n",
    "                        SELECT DISTINCT name FROM shopify_export where fulfillment_status = 'unfulfilled' and financial_status = 'paid'\n",
    "                    )\n",
    "                    GROUP BY name\n",
    "                )\n",
    "                as LINE_ITEM_COUNTS where beyond_count = 1 and cushion_count = 0 and lens_count = 1"
   ]
  }
 ],
 "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}