Task3_OLAP_Queries.py

# =============================================================================
# CSE512 — Data Warehousing and Mining | Spring 2023
# Task 3: OLAP Queries — Q1 to Q10
# File:   HDA-Tech-Learning/notebook/analytics/olap.ipynb  (as .py for reference)
# Student: Sazzad Hossain | ID: 2135184050
# Analytics #5: Item and Time Dimensional Inventory Analytics
#   Calculation: (leftmost 7 digits of ID) mod 5 + 1
#              = (2135184 mod 5) + 1 = 4 + 1 = 5
# =============================================================================
# Requirements: pip install pandas psycopg2-binary matplotlib seaborn
# Run after Task2_ETL.py has loaded data into ecomdb
# =============================================================================

# ── Cell 1: Imports ───────────────────────────────────────────────────────────

import pandas as pd
import psycopg2
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import warnings
import os

warnings.filterwarnings("ignore")
os.makedirs("charts", exist_ok=True)

# Colour palette
C = ["#1a3c5e", "#2c6496", "#3d8cc9", "#5aaede", "#8dc4e8", "#b8d9f1", "#e8f4fc"]

DB_CONFIG = {
    "host":     "localhost",
    "port":     5432,
    "dbname":   "ecomdb",
    "user":     "ecomuser",
    "password": "ecom123",
}

conn = psycopg2.connect(**DB_CONFIG)
print("[OK] Connected to ecomdb.")


# ── Helper functions ──────────────────────────────────────────────────────────

def run_query(sql: str) -> pd.DataFrame:
    """Execute a SQL query and return a pandas DataFrame."""
    return pd.read_sql(sql, conn)

def show_and_save(fig: plt.Figure, filename: str) -> None:
    """Display and save a matplotlib figure."""
    fig.savefig(f"charts/{filename}.png", dpi=150, bbox_inches="tight", facecolor="white")
    plt.show()
    print(f"[Saved] charts/{filename}.png\n")

MONTHS = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]


# =============================================================================
# Q1: Division / District / Year / Month wise Total Sale Price
#     Operation: ROLLUP — generates subtotals at each hierarchy level
# =============================================================================

# ── Cell 2: Q1 Query ──────────────────────────────────────────────────────────

q1_sql = """
SELECT
    s.division,
    s.district,
    t.year,
    t.month,
    SUM(f.total_price)  AS total_sales,
    COUNT(*)            AS transaction_count
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
JOIN ecomdb.dim_time  t ON f.time_key  = t.time_key
GROUP BY ROLLUP(s.division, s.district, t.year, t.month)
ORDER BY s.division NULLS LAST,
         s.district NULLS LAST,
         t.year     NULLS LAST,
         t.month    NULLS LAST;
"""

df_q1 = run_query(q1_sql)
print("Q1 — Division/District/Year/Month wise Total Sales (ROLLUP)")
print(f"Total rows returned (including rollup subtotals): {len(df_q1):,}")
print(df_q1.head(12).to_string(index=False))

# ── Cell 3: Q1 Visualization ──────────────────────────────────────────────────

# Aggregate to division-year for a clean grouped bar chart
div_year_sql = """
SELECT s.division, t.year, SUM(f.total_price) AS total_sales
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
JOIN ecomdb.dim_time  t ON f.time_key  = t.time_key
WHERE s.division IS NOT NULL AND t.year IS NOT NULL
GROUP BY s.division, t.year
ORDER BY t.year, total_sales DESC;
"""
df_dy = run_query(div_year_sql)
pivot = df_dy.pivot_table(index="division", columns="year", values="total_sales", aggfunc="sum").fillna(0)

fig, ax = plt.subplots(figsize=(13, 5))
x     = range(len(pivot))
width = 0.12
for i, yr in enumerate(pivot.columns):
    ax.bar([xi + i * width for xi in x], pivot[yr] / 1e6, width,
           label=str(yr), color=C[i % len(C)])

ax.set_xticks([xi + width * len(pivot.columns) / 2 for xi in x])
ax.set_xticklabels(pivot.index, rotation=20, ha="right")
ax.set_ylabel("Total Sales (Million BDT)")
ax.set_title("Q1: Division-wise Total Sales by Year (ROLLUP)", fontweight="bold")
ax.legend(title="Year", loc="upper right")
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:.1f}M"))
ax.grid(axis="y", alpha=0.3)
fig.tight_layout()
show_and_save(fig, "q1_division_year_sales")


# =============================================================================
# Q2: Customer / Bank / Transaction-type wise Total Sale Price
#     Operation: ROLLUP — subtotals per bank, then grand total
# =============================================================================

# ── Cell 4: Q2 Query ──────────────────────────────────────────────────────────

q2_sql = """
SELECT
    tr.bank_name,
    tr.trans_type,
    SUM(f.total_price) AS total_sales,
    COUNT(*)           AS txn_count
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_transaction tr ON f.payment_key = tr.payment_key
GROUP BY ROLLUP(tr.bank_name, tr.trans_type)
ORDER BY tr.bank_name NULLS LAST, tr.trans_type NULLS LAST;
"""

df_q2 = run_query(q2_sql)
print("Q2 — Bank / Transaction-type wise Total Sales (ROLLUP)")
print(df_q2.to_string(index=False))

# ── Cell 5: Q2 Visualization ──────────────────────────────────────────────────

# Pie: by transaction type
type_sql = """
SELECT tr.trans_type, SUM(f.total_price) AS total_sales
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_transaction tr ON f.payment_key = tr.payment_key
WHERE tr.trans_type IS NOT NULL
GROUP BY tr.trans_type;
"""
df_type = run_query(type_sql)

# Bar: top 10 banks by card sales
bank_sql = """
SELECT tr.bank_name, SUM(f.total_price) AS total_sales
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_transaction tr ON f.payment_key = tr.payment_key
WHERE tr.bank_name IS NOT NULL
GROUP BY tr.bank_name
ORDER BY total_sales DESC
LIMIT 10;
"""
df_bank = run_query(bank_sql)

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(13, 5))
ax1.pie(df_type["total_sales"], labels=df_type["trans_type"],
        autopct="%1.1f%%", colors=C[:len(df_type)], startangle=140)
ax1.set_title("Sales Split by Transaction Type", fontweight="bold")

ax2.barh(df_bank["bank_name"][::-1], df_bank["total_sales"][::-1] / 1e6, color=C[1])
ax2.set_xlabel("Total Sales (Million BDT)")
ax2.set_title("Top 10 Banks by Total Card Sales", fontweight="bold")
ax2.xaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:.1f}M"))

fig.suptitle("Q2: Customer / Bank / Transaction-type wise Sales", fontweight="bold")
fig.tight_layout()
show_and_save(fig, "q2_bank_transtype_sales")


# =============================================================================
# Q3: Total Sales in Barisal for item 'Pepsi - 12 oz cans'
#     Operation: SLICE — fix division='BARISAL' and item='Pepsi%'
# =============================================================================

# ── Cell 6: Q3 Query ──────────────────────────────────────────────────────────

q3_sql = """
SELECT
    s.division,
    s.district,
    i.item_name,
    SUM(f.total_price) AS total_sales,
    SUM(f.quantity)    AS total_qty
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
JOIN ecomdb.dim_item  i ON f.item_key  = i.item_key
WHERE s.division ILIKE 'BARISAL'
  AND i.item_name ILIKE '%Pepsi%12 oz%'
GROUP BY s.division, s.district, i.item_name
ORDER BY total_sales DESC;
"""

df_q3 = run_query(q3_sql)
print("Q3 — Total Sales in Barisal for Pepsi 12oz (SLICE)")
print(df_q3.to_string(index=False))
print(f"\nGrand Total (Pepsi 12oz, Barisal): {df_q3['total_sales'].sum():,.2f} BDT")

# ── Cell 7: Q3 Visualization ──────────────────────────────────────────────────

fig, ax = plt.subplots(figsize=(10, 5))
pepsi_only = df_q3[df_q3["item_name"].str.contains("^Pepsi", regex=True)]
diet_only  = df_q3[df_q3["item_name"].str.contains("Diet",   regex=False)]

ax.bar(pepsi_only["district"], pepsi_only["total_sales"], color=C[0], label="Pepsi 12oz")
ax.bar(diet_only["district"],  diet_only["total_sales"],  color=C[2], label="Diet Pepsi 12oz",
       bottom=0, alpha=0.85)
ax.set_ylabel("Total Sales (BDT)")
ax.set_title("Q3: Pepsi 12oz & Diet Pepsi 12oz Sales in Barisal (SLICE)", fontweight="bold")
ax.legend()
plt.xticks(rotation=30, ha="right")
ax.grid(axis="y", alpha=0.3)
fig.tight_layout()
show_and_save(fig, "q3_barisal_pepsi")


# =============================================================================
# Q4: Total Sales in 2015 for Supplier 'BIGSO AB'
#     Operation: DICE — restrict both time (year=2015) and item (supplier=BIGSO AB)
# =============================================================================

# ── Cell 8: Q4 Query ──────────────────────────────────────────────────────────

q4_sql = """
SELECT
    i.supplier,
    t.month,
    SUM(f.total_price) AS total_sales,
    SUM(f.quantity)    AS total_qty
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_item i ON f.item_key = i.item_key
JOIN ecomdb.dim_time t ON f.time_key = t.time_key
WHERE t.year = 2015
  AND i.supplier ILIKE '%BIGSO%'
GROUP BY i.supplier, t.month
ORDER BY t.month;
"""

df_q4 = run_query(q4_sql)
print("Q4 — BIGSO AB Monthly Sales in 2015 (DICE)")
print(df_q4.to_string(index=False))
print(f"\nAnnual Total (BIGSO AB, 2015): {df_q4['total_sales'].sum():,.2f} BDT")

# ── Cell 9: Q4 Visualization ──────────────────────────────────────────────────

fig, ax = plt.subplots(figsize=(11, 5))
x_labels = [MONTHS[m - 1] for m in df_q4["month"]]
bars = ax.bar(x_labels, df_q4["total_sales"] / 1e3, color=C[0])
ax.axhline(df_q4["total_sales"].mean() / 1e3, color="red", linestyle="--",
           linewidth=1.5, label=f"Monthly Avg: {df_q4['total_sales'].mean()/1e3:.1f}K")
ax.set_ylabel("Total Sales (Thousand BDT)")
ax.set_title("Q4: BIGSO AB — Monthly Sales in 2015 (DICE)", fontweight="bold")
ax.legend()
# Annotate bars
for bar, val in zip(bars, df_q4["total_sales"]):
    ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() + 0.5,
            f"{val/1e3:.0f}K", ha="center", va="bottom", fontsize=8)
ax.grid(axis="y", alpha=0.3)
fig.tight_layout()
show_and_save(fig, "q4_bigso_2015")


# =============================================================================
# Q5: Total Sales of Dhaka in 2015
#     Operation: SLICE — fix division='DHAKA' and year=2015
# =============================================================================

# ── Cell 10: Q5 Query ─────────────────────────────────────────────────────────

q5_sql = """
SELECT
    s.district,
    t.month,
    SUM(f.total_price) AS total_sales
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
JOIN ecomdb.dim_time  t ON f.time_key  = t.time_key
WHERE s.division ILIKE 'DHAKA'
  AND t.year = 2015
GROUP BY s.district, t.month
ORDER BY total_sales DESC;
"""

df_q5 = run_query(q5_sql)
print("Q5 — Dhaka Division Total Sales in 2015 (SLICE)")
print(df_q5.head(15).to_string(index=False))
print(f"\nDhaka 2015 Grand Total: {df_q5['total_sales'].sum():,.2f} BDT")

# ── Cell 11: Q5 Visualization ─────────────────────────────────────────────────

# District-level totals
dist_totals = df_q5.groupby("district")["total_sales"].sum().sort_values(ascending=False).head(10)

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 5))
ax1.bar(dist_totals.index, dist_totals / 1e6, color=C[1])
ax1.set_ylabel("Total Sales (Million BDT)")
ax1.set_title("Top Districts in Dhaka (2015)", fontweight="bold")
plt.setp(ax1.get_xticklabels(), rotation=30, ha="right")
ax1.yaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:.1f}M"))
ax1.grid(axis="y", alpha=0.3)

# Monthly trend for Dhaka (Sadar)
dhaka_sadar = df_q5[df_q5["district"] == "DHAKA"].sort_values("month")
ax2.plot(dhaka_sadar["month"], dhaka_sadar["total_sales"] / 1e3,
         marker="o", color=C[0], linewidth=2, markersize=6)
ax2.set_xticks(range(1, 13))
ax2.set_xticklabels(MONTHS, rotation=30, ha="right")
ax2.set_ylabel("Total Sales (Thousand BDT)")
ax2.set_title("Dhaka (Sadar) — Monthly Trend 2015", fontweight="bold")
ax2.grid(alpha=0.3)

fig.suptitle("Q5: Dhaka Division Sales in 2015 (SLICE)", fontweight="bold")
fig.tight_layout()
show_and_save(fig, "q5_dhaka_2015")


# =============================================================================
# Q6: Top 3 Products per Store (Supplier) most often purchased
#     Operation: DRILL-DOWN from supplier → item + RANK window function
# =============================================================================

# ── Cell 12: Q6 Query ─────────────────────────────────────────────────────────

q6_sql = """
WITH ranked AS (
    SELECT
        i.supplier               AS store_supplier,
        i.item_name,
        SUM(f.quantity)          AS qty_sold,
        RANK() OVER (
            PARTITION BY i.supplier
            ORDER BY SUM(f.quantity) DESC
        )                        AS rnk
    FROM ecomdb.fact_sales f
    JOIN ecomdb.dim_item i ON f.item_key = i.item_key
    GROUP BY i.supplier, i.item_name
)
SELECT store_supplier, item_name, qty_sold
FROM   ranked
WHERE  rnk <= 3
ORDER  BY store_supplier, rnk;
"""

df_q6 = run_query(q6_sql)
print("Q6 — Top 3 Products per Supplier (DRILL-DOWN + RANK)")
print(df_q6.head(15).to_string(index=False))
print(f"\nTotal suppliers: {df_q6['store_supplier'].nunique()}")

# ── Cell 13: Q6 Visualization ─────────────────────────────────────────────────

# Show top 5 suppliers by combined quantity
top5 = (df_q6.groupby("store_supplier")["qty_sold"]
               .sum().nlargest(5).index.tolist())
sub   = df_q6[df_q6["store_supplier"].isin(top5)]

fig, axes = plt.subplots(1, 5, figsize=(18, 5), sharey=False)
for ax, sup in zip(axes, top5):
    d      = sub[sub["store_supplier"] == sup].reset_index(drop=True)
    labels = [n[:22] + "…" if len(n) > 22 else n for n in d["item_name"]]
    ax.barh(labels[::-1], d["qty_sold"][::-1] / 1e3, color=C[:3])
    ax.set_title(sup[:22], fontsize=8.5, fontweight="bold")
    ax.set_xlabel("Qty Sold (K)")
    ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:.0f}K"))

fig.suptitle("Q6: Top 3 Products per Supplier — Top 5 Suppliers (DRILL-DOWN)",
             fontweight="bold", fontsize=12)
fig.tight_layout()
show_and_save(fig, "q6_top3_products_per_supplier")


# =============================================================================
# Q7: Products sold via card or mobile in last X days (X = 5)
#     Operation: SLICE on trans_type='card' + rolling time window
# =============================================================================

# ── Cell 14: Q7 Query ─────────────────────────────────────────────────────────

q7_sql = """
WITH max_date AS (
    SELECT MAX(TO_DATE(SPLIT_PART(date_val, ' ', 1), 'DD-MM-YYYY')) AS mxd
    FROM ecomdb.dim_time
)
SELECT DISTINCT i.item_name
FROM ecomdb.fact_sales     f
JOIN ecomdb.dim_transaction tr ON f.payment_key = tr.payment_key
JOIN ecomdb.dim_time        t  ON f.time_key    = t.time_key
JOIN ecomdb.dim_item        i  ON f.item_key    = i.item_key,
     max_date
WHERE tr.trans_type = 'card'
  AND TO_DATE(SPLIT_PART(t.date_val, ' ', 1), 'DD-MM-YYYY')
      >= max_date.mxd - INTERVAL '5 days'
ORDER BY i.item_name;
"""

df_q7 = run_query(q7_sql)
print(f"Q7 — Products sold via card in last 5 days: {len(df_q7)} items")
print(df_q7.head(20).to_string(index=False))

# ── Cell 15: Q7 Visualization ─────────────────────────────────────────────────

fig, ax = plt.subplots(figsize=(10, 6))
ax.axis("off")
display_items = df_q7["item_name"].tolist()[:20]  # show first 20
col_labels = ["#", "Item Name (sold via card — last 5 days)"]
cell_data  = [[str(i + 1), name[:60]] for i, name in enumerate(display_items)]
tbl = ax.table(cellText=cell_data, colLabels=col_labels,
               cellLoc="left", loc="center", bbox=[0, 0, 1, 1])
tbl.auto_set_font_size(False)
tbl.set_fontsize(9)
for (r, c), cell in tbl.get_celld().items():
    if r == 0:
        cell.set_facecolor(C[0])
        cell.set_text_props(color="white", fontweight="bold")
    elif r % 2 == 0:
        cell.set_facecolor("#f0f6fb")
ax.set_title(f"Q7: {len(df_q7)} Unique Products Sold via Card in Last 5 Days (first 20 shown)",
             fontweight="bold", pad=12)
fig.tight_layout()
show_and_save(fig, "q7_card_sales_last5days")


# =============================================================================
# Q8: Worst season (quarter) per product item
#     Operation: DRILL-DOWN to quarter + RANK to find minimum
# =============================================================================

# ── Cell 16: Q8 Query ─────────────────────────────────────────────────────────

q8_sql = """
WITH qtr_sales AS (
    SELECT
        i.item_name,
        t.quarter,
        SUM(f.total_price) AS sales
    FROM ecomdb.fact_sales f
    JOIN ecomdb.dim_item i ON f.item_key = i.item_key
    JOIN ecomdb.dim_time t ON f.time_key = t.time_key
    GROUP BY i.item_name, t.quarter
),
worst AS (
    SELECT
        item_name,
        quarter        AS worst_quarter,
        sales          AS worst_sales,
        RANK() OVER (PARTITION BY item_name ORDER BY sales ASC) AS rnk
    FROM qtr_sales
)
SELECT item_name, worst_quarter, worst_sales
FROM   worst
WHERE  rnk = 1
ORDER  BY worst_sales ASC;
"""

df_q8 = run_query(q8_sql)
print("Q8 — Worst Quarter per Product (DRILL-DOWN + RANK)")
print(df_q8.head(15).to_string(index=False))

worst_dist = df_q8["worst_quarter"].value_counts()
print(f"\nWorst quarter distribution:\n{worst_dist.to_string()}")

# ── Cell 17: Q8 Visualization ─────────────────────────────────────────────────

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(13, 5))

ax1.pie(worst_dist, labels=worst_dist.index, autopct="%1.1f%%",
        colors=C[:4], startangle=140)
ax1.set_title("Distribution of Worst Quarters\nacross All Products", fontweight="bold")

top10_worst = df_q8.nsmallest(10, "worst_sales")
qtr_color   = {"Q1": C[0], "Q2": C[1], "Q3": C[2], "Q4": C[3]}
bar_colors  = [qtr_color.get(q, C[4]) for q in top10_worst["worst_quarter"][::-1]]
ax2.barh([n[:35] for n in top10_worst["item_name"]][::-1],
         top10_worst["worst_sales"][::-1] / 1e3, color=bar_colors)
ax2.set_xlabel("Worst Quarter Sales (Thousand BDT)")
ax2.set_title("10 Items with Lowest Worst-Quarter Sales", fontweight="bold")

# Legend for quarter colors
from matplotlib.patches import Patch
legend_handles = [Patch(facecolor=v, label=k) for k, v in qtr_color.items()]
ax2.legend(handles=legend_handles, title="Worst Quarter", loc="lower right")

fig.suptitle("Q8: Worst Season (Quarter) per Product (DRILL-DOWN)", fontweight="bold")
fig.tight_layout()
show_and_save(fig, "q8_worst_quarter_per_item")


# =============================================================================
# Q9: Break down total sales of items geographically (division-wise)
#     Operation: CUBE on (item_name, division)
# =============================================================================

# ── Cell 18: Q9 Query ─────────────────────────────────────────────────────────

q9_sql = """
SELECT
    i.item_name,
    s.division,
    SUM(f.total_price) AS total_sales
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_item  i ON f.item_key  = i.item_key
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
GROUP BY CUBE(i.item_name, s.division)
ORDER BY i.item_name NULLS LAST, total_sales DESC;
"""

df_q9 = run_query(q9_sql)
print("Q9 — Item × Division Sales (CUBE)")
# Show just a few rows per item for readability
print(df_q9[df_q9["item_name"].notna() & df_q9["division"].notna()].head(16).to_string(index=False))

# ── Cell 19: Q9 Visualization ─────────────────────────────────────────────────

# Heatmap of top 10 items × all divisions
clean  = df_q9.dropna(subset=["item_name", "division"])
top10  = (clean.groupby("item_name")["total_sales"]
                .sum().nlargest(10).index)
sub    = clean[clean["item_name"].isin(top10)]
pivot  = sub.pivot_table(index="item_name", columns="division",
                         values="total_sales", aggfunc="sum").fillna(0)

fig, ax = plt.subplots(figsize=(13, 6))
im = ax.imshow(pivot.values / 1e3, cmap="Blues", aspect="auto")
ax.set_xticks(range(len(pivot.columns)))
ax.set_xticklabels(pivot.columns, rotation=30, ha="right")
ax.set_yticks(range(len(pivot.index)))
ax.set_yticklabels([n[:45] for n in pivot.index])
plt.colorbar(im, ax=ax, label="Sales (Thousand BDT)")
for i in range(len(pivot.index)):
    for j in range(len(pivot.columns)):
        ax.text(j, i, f"{pivot.values[i, j] / 1e3:.0f}",
                ha="center", va="center", fontsize=6.5, color="black")
ax.set_title("Q9: Item × Division Sales Heatmap — Top 10 Items (CUBE)", fontweight="bold")
fig.tight_layout()
show_and_save(fig, "q9_item_division_heatmap")


# =============================================================================
# Q10: Average sales per store monthly
#      Operation: ROLLUP on (store_key, division, month)
# =============================================================================

# ── Cell 20: Q10 Query ────────────────────────────────────────────────────────

q10_sql = """
SELECT
    f.store_key,
    s.division,
    t.month,
    AVG(f.total_price)  AS avg_sale_per_txn,
    SUM(f.total_price)  AS total_sales,
    COUNT(*)            AS txn_count
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
JOIN ecomdb.dim_time  t ON f.time_key  = t.time_key
GROUP BY ROLLUP(f.store_key, s.division, t.month)
ORDER BY f.store_key NULLS LAST, t.month NULLS LAST;
"""

df_q10 = run_query(q10_sql)
print("Q10 — Average Monthly Sales per Store (ROLLUP)")
print(df_q10.head(15).to_string(index=False))

# ── Cell 21: Q10 Visualization ────────────────────────────────────────────────

# Line chart: avg monthly sale per transaction, grouped by division
div_month_sql = """
SELECT s.division, t.month, AVG(f.total_price) AS avg_sale
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_store s ON f.store_key = s.store_key
JOIN ecomdb.dim_time  t ON f.time_key  = t.time_key
WHERE s.division IS NOT NULL AND t.month IS NOT NULL
GROUP BY s.division, t.month
ORDER BY t.month;
"""
df_dm = run_query(div_month_sql)

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(13, 9))

for i, div in enumerate(sorted(df_dm["division"].unique())):
    sub = df_dm[df_dm["division"] == div].set_index("month")
    ax1.plot(sub.index, sub["avg_sale"], marker="o", label=div,
             color=C[i % len(C)], linewidth=2, markersize=5)

ax1.set_xticks(range(1, 13))
ax1.set_xticklabels(MONTHS)
ax1.set_ylabel("Avg Sale per Transaction (BDT)")
ax1.set_title("Average Monthly Sales per Transaction by Division", fontweight="bold")
ax1.legend(loc="upper right")
ax1.grid(alpha=0.3)

# Bar: overall monthly trend (all stores combined)
overall_sql = """
SELECT t.month, AVG(f.total_price) AS avg_sale, SUM(f.total_price) AS total_sales
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_time t ON f.time_key = t.time_key
WHERE t.month IS NOT NULL
GROUP BY t.month ORDER BY t.month;
"""
df_overall = run_query(overall_sql)
ax2.bar(MONTHS, df_overall["total_sales"] / 1e6, color=C[0], alpha=0.8, label="Total Sales")
ax2b = ax2.twinx()
ax2b.plot(MONTHS, df_overall["avg_sale"], color="red", marker="o",
          linewidth=2, markersize=5, label="Avg per Txn")
ax2.set_ylabel("Total Sales (Million BDT)")
ax2b.set_ylabel("Avg Sale per Transaction (BDT)", color="red")
ax2.set_title("Monthly Total Sales vs Avg Transaction Value (All Stores)", fontweight="bold")
lines1, labs1 = ax2.get_legend_handles_labels()
lines2, labs2 = ax2b.get_legend_handles_labels()
ax2.legend(lines1 + lines2, labs1 + labs2, loc="upper right")
ax2.grid(axis="y", alpha=0.3)

fig.suptitle("Q10: Average Monthly Sales per Store (ROLLUP)", fontweight="bold", fontsize=13)
fig.tight_layout()
show_and_save(fig, "q10_avg_monthly_sales")


# =============================================================================
# ANALYTICS #5: Item and Time Dimensional Inventory Analytics (Task 4)
# =============================================================================
# These are additional CUBE queries specifically for Analytics #5
# focusing on item inventory (stock movement via sales quantity) and time.
# =============================================================================

# ── Cell 22: Monthly Stock Turnover per Item Category ────────────────────────

inv1_sql = """
SELECT
    SPLIT_PART(i.description, '.', 1) || '. ' ||
    TRIM(SPLIT_PART(i.description, ' ', 2)) AS item_category,
    t.year,
    t.month,
    SUM(f.quantity)    AS total_qty_sold,
    SUM(f.total_price) AS total_revenue
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_item i ON f.item_key = i.item_key
JOIN ecomdb.dim_time t ON f.time_key = t.time_key
GROUP BY ROLLUP(SPLIT_PART(i.description, '.', 1) || '. ' ||
                TRIM(SPLIT_PART(i.description, ' ', 2)), t.year, t.month)
ORDER BY item_category NULLS LAST, t.year NULLS LAST, t.month NULLS LAST;
"""

df_inv1 = run_query(inv1_sql)
print("Analytics #5 — Monthly Stock Turnover per Item Category (ROLLUP)")
print(df_inv1[df_inv1["item_category"].notna() & df_inv1["year"].notna()].head(15).to_string(index=False))

# ── Cell 23: Quarterly Sales Volume per Item (Seasonal Pattern) ──────────────

inv2_sql = """
SELECT
    i.item_name,
    t.quarter,
    SUM(f.quantity)    AS total_qty_sold,
    SUM(f.total_price) AS total_revenue,
    AVG(f.unit_price)  AS avg_unit_price
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_item i ON f.item_key = i.item_key
JOIN ecomdb.dim_time t ON f.time_key = t.time_key
GROUP BY CUBE(i.item_name, t.quarter)
ORDER BY i.item_name NULLS LAST, t.quarter NULLS LAST;
"""

df_inv2 = run_query(inv2_sql)
print("\nAnalytics #5 — Quarterly Sales Volume per Item (CUBE)")
print(df_inv2[df_inv2["item_name"].notna() & df_inv2["quarter"].notna()].head(12).to_string(index=False))

# ── Cell 24: Top Slow-Moving Items (Lowest Qty Sold) ─────────────────────────

inv3_sql = """
SELECT
    i.item_name,
    i.supplier,
    SUM(f.quantity)    AS total_qty_sold,
    SUM(f.total_price) AS total_revenue,
    COUNT(DISTINCT t.year || '-' || t.month) AS active_months
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_item i ON f.item_key = i.item_key
JOIN ecomdb.dim_time t ON f.time_key = t.time_key
GROUP BY i.item_name, i.supplier
ORDER BY total_qty_sold ASC
LIMIT 15;
"""

df_inv3 = run_query(inv3_sql)
print("\nAnalytics #5 — Slow-Moving Items (Lowest Total Quantity Sold)")
print(df_inv3.to_string(index=False))

# ── Cell 25: Analytics #5 Visualization ──────────────────────────────────────

fig, axes = plt.subplots(1, 3, figsize=(18, 6))

# (a) Quarterly qty sold — top 8 items
cat_qtr = (df_inv2.dropna(subset=["item_name", "quarter"])
           .groupby(["item_name", "quarter"])["total_qty_sold"].sum().reset_index())
top8    = (cat_qtr.groupby("item_name")["total_qty_sold"]
            .sum().nlargest(8).index)
sub8    = cat_qtr[cat_qtr["item_name"].isin(top8)]
pivot8  = sub8.pivot_table(index="item_name", columns="quarter", values="total_qty_sold").fillna(0)
x8      = range(len(pivot8))
w8      = 0.2
for i, qtr in enumerate(["Q1", "Q2", "Q3", "Q4"]):
    if qtr in pivot8.columns:
        axes[0].bar([xi + i * w8 for xi in x8], pivot8[qtr] / 1e3, w8,
                    label=qtr, color=C[i])
axes[0].set_xticks([xi + w8 * 1.5 for xi in x8])
axes[0].set_xticklabels([n[:20] for n in pivot8.index], rotation=45, ha="right", fontsize=7)
axes[0].set_ylabel("Qty Sold (Thousands)")
axes[0].set_title("(a) Quarterly Sales Volume\n— Top 8 Items", fontweight="bold")
axes[0].legend(title="Quarter")
axes[0].yaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:.0f}K"))

# (b) Slow-moving items (bottom 10 by qty)
slow = df_inv3.head(10)
axes[1].barh([n[:30] for n in slow["item_name"]][::-1],
             slow["total_qty_sold"][::-1], color=C[3])
axes[1].set_xlabel("Total Qty Sold")
axes[1].set_title("(b) Slow-Moving Items\n(Lowest Qty Sold)", fontweight="bold")

# (c) Yearly total qty sold trend
year_sql = """
SELECT t.year, SUM(f.quantity) AS total_qty, SUM(f.total_price) AS total_rev
FROM ecomdb.fact_sales f
JOIN ecomdb.dim_time t ON f.time_key = t.time_key
WHERE t.year IS NOT NULL
GROUP BY t.year ORDER BY t.year;
"""
df_yr = run_query(year_sql)
ax_c  = axes[2]
ax_c2 = ax_c.twinx()
ax_c.bar(df_yr["year"].astype(str), df_yr["total_qty"] / 1e6, color=C[0], alpha=0.8, label="Qty Sold")
ax_c2.plot(df_yr["year"].astype(str), df_yr["total_rev"] / 1e6,
           color="red", marker="o", linewidth=2, label="Revenue (M BDT)")
ax_c.set_ylabel("Total Qty Sold (Millions)")
ax_c2.set_ylabel("Total Revenue (Million BDT)", color="red")
ax_c.set_title("(c) Yearly Inventory Turnover\n(Qty & Revenue)", fontweight="bold")
lines1, labs1 = ax_c.get_legend_handles_labels()
lines2, labs2 = ax_c2.get_legend_handles_labels()
ax_c.legend(lines1 + lines2, labs1 + labs2, loc="upper left", fontsize=8)
ax_c.grid(axis="y", alpha=0.3)

fig.suptitle("Analytics #5: Item and Time Dimensional Inventory Analytics",
             fontweight="bold", fontsize=13)
fig.tight_layout()
show_and_save(fig, "analytics5_inventory")


# ── Cell 26: Summary ──────────────────────────────────────────────────────────

print("=" * 60)
print("OLAP Query Summary — CSE512 Task 3")
print("=" * 60)

summary_queries = {
    "dim_transaction": "SELECT COUNT(*) FROM ecomdb.dim_transaction",
    "dim_customer":    "SELECT COUNT(*) FROM ecomdb.dim_customer",
    "dim_item":        "SELECT COUNT(*) FROM ecomdb.dim_item",
    "dim_store":       "SELECT COUNT(*) FROM ecomdb.dim_store",
    "dim_time":        "SELECT COUNT(*) FROM ecomdb.dim_time",
    "fact_sales":      "SELECT COUNT(*) FROM ecomdb.fact_sales",
}
for tbl, sql_str in summary_queries.items():
    cur = conn.cursor()
    cur.execute(sql_str)
    cnt = cur.fetchone()[0]
    cur.close()
    print(f"  {tbl:<20}: {cnt:>10,} rows")

print("\nAnalytics completed:")
print("  Q1  — ROLLUP: Division/District/Year/Month total sales")
print("  Q2  — ROLLUP: Bank/Transaction-type total sales")
print("  Q3  — SLICE:  Barisal × Pepsi 12oz")
print("  Q4  — DICE:   BIGSO AB × Year 2015")
print("  Q5  — SLICE:  Dhaka Division × Year 2015")
print("  Q6  — DRILL-DOWN: Top 3 products per supplier")
print("  Q7  — SLICE:  Card sales last 5 days")
print("  Q8  — DRILL-DOWN: Worst quarter per item")
print("  Q9  — CUBE:   Item × Division sales")
print("  Q10 — ROLLUP: Avg monthly sales per store")
print("  #5  — CUBE/ROLLUP: Item × Time inventory analytics")
print(f"\nAll charts saved to: charts/")

conn.close()
print("[DONE] Connection closed.")