-- Mount a SQLite database (simple example)
ATTACH 'path/to/file.db' AS my_db (TYPE SQLITE);

-- Now cross-database query is possible
SELECT *
FROM my_db.some_table AS a
JOIN main.public.another_table AS b ON a.id = b.id;

#!/usr/bin/env python3
"""
DuckDB Cross-Database JOIN Demo
Scenario: E-commerce data across three sources, one SQL to unite them all

Prerequisites: pip install duckdb openpyxl
"""

import duckdb
import os

# ====== Step 1: Create Mock Data ======

# Mock MySQL orders table (CSV file)
orders_csv = """order_id,customer_id,product_id,amount,order_date
1001,201,5001,299.00,2026-05-01
1002,202,5002,159.00,2026-05-01
1003,201,5003,899.00,2026-05-02
1004,203,5001,299.00,2026-05-02
1005,204,5004,459.00,2026-05-03
1006,202,5002,159.00,2026-05-03
1007,205,5005,1299.00,2026-05-04
1008,203,5003,899.00,2026-05-04
1009,206,5001,299.00,2026-05-05
1010,201,5004,459.00,2026-05-05
"""

# Mock PostgreSQL users table (CSV file)
users_csv = """customer_id,name,city,member_level,register_date
201,Alice,Beijing,Gold,2025-01-15
202,Bob,Shanghai,Silver,2025-03-20
203,Carol,Guangzhou,Gold,2025-02-01
204,Dave,Shenzhen,Regular,2025-06-10
205,Eve,Hangzhou,Silver,2025-04-05
206,Frank,Chengdu,Regular,2025-08-15
"""

# Mock product catalog
products_csv = """product_id,product_name,category,unit_price,cost_price
5001,Wireless Earbuds,Electronics,299.00,180.00
5002,Insulated Mug,Home,159.00,80.00
5003,Smart Watch,Electronics,899.00,550.00
5004,Running Shoes,Apparel,459.00,280.00
5005,Tablet Stand,Electronics,1299.00,800.00
"""

# Write temp files
os.makedirs("day07_data", exist_ok=True)
with open("day07_data/orders.csv", "w") as f:
    f.write(orders_csv)
with open("day07_data/users.csv", "w") as f:
    f.write(users_csv)
with open("day07_data/products.csv", "w") as f:
    f.write(products_csv)

# ====== Step 2: Cross-Source JOIN with DuckDB ======

con = duckdb.connect()

# Mount CSV files as views — simulating "different data sources"
con.execute("""
    CREATE VIEW orders AS 
    SELECT * FROM read_csv_auto('day07_data/orders.csv')
""")
con.execute("""
    CREATE VIEW users AS 
    SELECT * FROM read_csv_auto('day07_data/users.csv')
""")
con.execute("""
    CREATE VIEW products AS 
    SELECT * FROM read_csv_auto('day07_data/products.csv')
""")

print("=" * 60)
print("📊 Cross-Source: What did Gold members buy?")
print("=" * 60)

# One SQL across three "data sources"
result = con.execute("""
    SELECT 
        u.name AS customer_name,
        u.city,
        u.member_level,
        p.product_name,
        p.category,
        o.amount AS unit_price,
        (o.amount - p.cost_price) AS gross_margin
    FROM orders o
    JOIN users u ON o.customer_id = u.customer_id
    JOIN products p ON o.product_id = p.product_id
    WHERE u.member_level IN ('Gold', 'Silver')
    ORDER BY o.amount DESC
""").fetchdf()

print(result.to_string(index=False))

print("\n" + "=" * 60)
print("💰 Daily Sales Summary (with Margins)")
print("=" * 60)

result2 = con.execute("""
    SELECT 
        o.order_date AS date,
        COUNT(DISTINCT o.order_id) AS orders,
        SUM(o.amount) AS revenue,
        SUM(o.amount - p.cost_price) AS gross_profit,
        ROUND(AVG(o.amount - p.cost_price), 2) AS avg_profit_per_order
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY o.order_date
    ORDER BY o.order_date
""").fetchdf()

print(result2.to_string(index=False))

print("\n" + "=" * 60)
print("🏆 Category Profitability Analysis")
print("=" * 60)

result3 = con.execute("""
    SELECT 
        p.category,
        COUNT(*) AS units_sold,
        SUM(o.amount) AS revenue,
        SUM(o.amount - p.cost_price) AS gross_profit,
        ROUND(AVG(o.amount - p.cost_price), 2) AS avg_profit,
        ROUND(SUM(o.amount - p.cost_price) / SUM(o.amount) * 100, 1) AS margin_pct
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
    ORDER BY gross_profit DESC
""").fetchdf()

print(result3.to_string(index=False))

# ====== Step 3: Export to Excel ======

try:
    con.execute("INSTALL spatial; LOAD spatial;")
    con.execute("""
        COPY (
            SELECT 
                o.order_id,
                u.name,
                u.city,
                u.member_level,
                p.product_name,
                p.category,
                o.amount,
                (o.amount - p.cost_price) AS profit
            FROM orders o
            JOIN users u ON o.customer_id = u.customer_id
            JOIN products p ON o.product_id = p.product_id
        ) TO 'day07_data/cross_source_report.xlsx' 
        WITH (FORMER XLSX);
    """)
    print("\n✅ Report exported: day07_data/cross_source_report.xlsx")
except Exception as e:
    print(f"\n⚠️ XLSX export needs spatial extension: {e}")
    print("Falling back to CSV:")
    con.execute("""
        COPY (
            SELECT 
                o.order_id,
                u.name,
                u.city,
                u.member_level,
                p.product_name,
                p.category,
                o.amount,
                (o.amount - p.cost_price) AS profit
            FROM orders o
            JOIN users u ON o.customer_id = u.customer_id
            JOIN products p ON o.product_id = p.product_id
        ) TO 'day07_data/cross_source_report.csv' (HEADER, DELIMITER ',');
    """)
    print("✅ Report exported: day07_data/cross_source_report.csv")

# ====== Step 4: Validation ======
print("\n" + "=" * 60)
print("📈 Validation: Are all orders matched to users and products?")
print("=" * 60)

validation = con.execute("""
    SELECT 
        'Total orders' AS metric, CAST(COUNT(*) AS VARCHAR) AS value FROM orders
    UNION ALL
    SELECT 'Matched to users', CAST(COUNT(*) AS VARCHAR) FROM orders o 
        JOIN users u ON o.customer_id = u.customer_id
    UNION ALL
    SELECT 'Matched to products', CAST(COUNT(*) AS VARCHAR) FROM orders o 
        JOIN products p ON o.product_id = p.product_id
    UNION ALL
    SELECT 'Fully matched', CAST(COUNT(*) AS VARCHAR) FROM orders o 
        JOIN users u ON o.customer_id = u.customer_id
        JOIN products p ON o.product_id = p.product_id
""").fetchdf()

print(validation.to_string(index=False))

con.close()
print("\n🎉 Cross-database JOIN demo complete!")

-- Install extensions (one-time)
INSTALL mysql_scanner;
LOAD mysql_scanner;
INSTALL postgres_scanner;
LOAD postgres_scanner;

-- Mount MySQL orders database
ATTACH 'host=localhost port=3306 dbname=orders_db user=analyst password=xxx' 
    AS mysql_db (TYPE MYSQL);

-- Mount PostgreSQL user database
ATTACH 'host=localhost port=5432 dbname=users_db user=analyst password=xxx' 
    AS pg_db (TYPE POSTGRES);

-- Mount local product CSV
CREATE VIEW products AS 
SELECT * FROM read_csv_auto('products.csv');

-- One SQL across all three
SELECT 
    u.name,
    u.city,
    p.product_name,
    SUM(o.amount) AS total_spent
FROM mysql_db.orders AS o
JOIN pg_db.public.customers AS u ON o.customer_id = u.customer_id
JOIN products AS p ON o.product_id = p.product_id
WHERE o.order_date >= '2026-04-01'
GROUP BY u.name, u.city, p.product_name
ORDER BY total_spent DESC;

-- DuckDB executes this GROUP BY on MySQL
-- Only the aggregated results come back, not a full table scan
SELECT customer_id, COUNT(*) 
FROM mysql_db.orders 
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

-- ❌ BAD: Pull everything, filter later
SELECT * FROM mysql_db.orders;  -- Could be millions of rows

-- ✅ GOOD: Push down filter + limit
SELECT * FROM mysql_db.orders 
WHERE order_date >= '2026-05-01' 
LIMIT 1000;

-- ✅ BEST: Aggregate first, then JOIN
WITH daily_stats AS (
    -- This aggregation runs on MySQL
    SELECT customer_id, DATE(order_date) AS day, SUM(amount) AS daily_total
    FROM mysql_db.orders
    WHERE order_date >= '2026-04-01'
    GROUP BY customer_id, DATE(order_date)
)
-- Then JOIN with local data
SELECT u.name, d.day, d.daily_total
FROM daily_stats d
JOIN pg_db.public.customers u ON d.customer_id = u.customer_id
ORDER BY d.daily_total DESC
LIMIT 20;

-- MySQL → DuckDB local table (one-time snapshot)
CREATE TABLE local_orders AS 
SELECT * FROM mysql_db.orders 
WHERE order_date >= '2026-01-01';

-- DuckDB → PostgreSQL (write back)
CREATE TABLE pg_db.public.report AS 
SELECT * FROM local_analytics;

-- Mount production and staging simultaneously
ATTACH 'prod_conn' AS prod (TYPE POSTGRES);
ATTACH 'staging_conn' AS staging (TYPE POSTGRES);

-- Compare data differences
SELECT 
    COALESCE(p.order_id, s.order_id) AS order_id,
    p.amount AS prod_amount,
    s.amount AS staging_amount,
    (p.amount - s.amount) AS diff
FROM prod.public.orders p
FULL OUTER JOIN staging.public.orders s 
    ON p.order_id = s.order_id
WHERE p.amount IS DISTINCT FROM s.amount;

# Cron-based daily report generation
import duckdb

con = duckdb.connect()

# ATTACH data sources
con.execute("ATTACH '...' AS mysql_db (TYPE MYSQL)")
con.execute("ATTACH '...' AS pg_db (TYPE POSTGRES)")

# Generate daily report
con.execute("""
    COPY (
        -- cross-source query...
    ) TO '/tmp/daily_report.csv' (HEADER, DELIMITER ',');
""")

# Send email via SMTP (pseudo-code)
# send_email(to='boss@company.com', attachment='/tmp/daily_report.csv')
print("✅ Daily report generated")

# Ensure MySQL client libraries are installed
apt-get install -y default-libmysqlclient-dev  # Ubuntu/Debian
# or
brew install mysql-client  # macOS

INSTALL mysql_scanner;
LOAD mysql_scanner;

rm -rf day07_data/