import pandas as pd
import requests
import os

# CSV ingestion
def ingest_csv(file_path):
    df = pd.read_csv(file_path)
    print(f"Ingested {len(df)} rows from {file_path}")
    return df

# Multiple CSV files
def ingest_multiple_csvs(folder_path):
    all_dfs = []
    for file in os.listdir(folder_path):
        if file.endswith('.csv'):
            df = pd.read_csv(os.path.join(folder_path, file))
            all_dfs.append(df)
    return pd.concat(all_dfs, ignore_index=True)

# Example
df = ingest_csv('sales_data.csv')

# API ingestion
def ingest_api(url, params=None):
    response = requests.get(url, params=params)
    if response.status_code == 200:
        data = response.json()
        df = pd.DataFrame(data)
        print(f"Ingested {len(df)} records from API")
        return df
    else:
        print(f"Error: {response.status_code}")
        return None

# Web scraping (simple)
def scrape_website(url, selector):
    from bs4 import BeautifulSoup
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    elements = soup.select(selector)
    data = [e.text.strip() for e in elements]
    df = pd.DataFrame({'data': data})
    return df

def clean_data(df):
    # Remove duplicates
    df = df.drop_duplicates()

    # Handle missing values
    df = df.dropna(subset=['date', 'amount'])  # Drop rows with critical missing values

    # Fill missing values in other columns
    if 'category' in df.columns:
        df['category'] = df['category'].fillna('Unknown')

    # Standardize date format
    df['date'] = pd.to_datetime(df['date'], errors='coerce')

    # Remove outliers
    if 'amount' in df.columns:
        q1 = df['amount'].quantile(0.25)
        q3 = df['amount'].quantile(0.75)
        iqr = q3 - q1
        lower = q1 - 1.5 * iqr
        upper = q3 + 1.5 * iqr
        df = df[(df['amount'] >= lower) & (df['amount'] <= upper)]

    return df

# Apply transformation
df_cleaned = clean_data(df)

def feature_engineering(df):
    # Extract date features
    df['year'] = df['date'].dt.year
    df['month'] = df['date'].dt.month
    df['quarter'] = df['date'].dt.quarter
    df['day_of_week'] = df['date'].dt.dayofweek

    # Create time-based features
    df['is_weekend'] = df['day_of_week'].isin([5, 6]).astype(int)

    # Aggregations
    if 'customer_id' in df.columns and 'amount' in df.columns:
        # Customer-level aggregates
        customer_stats = df.groupby('customer_id').agg({
            'amount': ['sum', 'mean', 'count']
        }).reset_index()
        customer_stats.columns = ['customer_id', 'total_spend', 'avg_spend', 'purchase_count']

        # Merge back
        df = df.merge(customer_stats, on='customer_id', how='left')

    return df

df_transformed = feature_engineering(df_cleaned)

import sqlite3

def create_database(df, table_name, db_path='analytics.db'):
    conn = sqlite3.connect(db_path)

    # Create table
    df.to_sql(table_name, conn, if_exists='replace', index=False)

    # Verify
    cursor = conn.cursor()
    cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
    count = cursor.fetchone()[0]
    print(f"Stored {count} rows in {table_name}")

    conn.close()
    return db_path

def query_database(query, db_path='analytics.db'):
    conn = sqlite3.connect(db_path)
    result = pd.read_sql_query(query, conn)
    conn.close()
    return result

# Example
create_database(df_transformed, 'sales_data')
result = query_database("SELECT * FROM sales_data LIMIT 5")

# PostgreSQL connection
import psycopg2
from sqlalchemy import create_engine

def create_postgres_connection():
    engine = create_engine('postgresql://user:password@localhost:5432/analytics')
    return engine

def store_in_postgres(df, table_name):
    engine = create_postgres_connection()
    df.to_sql(table_name, engine, if_exists='replace', index=False)
    print(f"Stored {len(df)} rows in {table_name}")

# Query using SQLAlchemy
def query_postgres(query):
    engine = create_postgres_connection()
    with engine.connect() as conn:
        result = pd.read_sql_query(query, conn)
    return result

# SQL Analysis Queries
def run_analysis(db_path='analytics.db'):
    conn = sqlite3.connect(db_path)

    # 1. Total sales by month
    query1 = """
    SELECT 
        strftime('%Y-%m', date) as month,
        SUM(amount) as total_sales
    FROM sales_data
    GROUP BY month
    ORDER BY month
    """
    monthly_sales = pd.read_sql_query(query1, conn)

    # 2. Top products by revenue
    query2 = """
    SELECT 
        product_name,
        SUM(amount) as revenue,
        COUNT(*) as orders
    FROM sales_data
    GROUP BY product_name
    ORDER BY revenue DESC
    LIMIT 10
    """
    top_products = pd.read_sql_query(query2, conn)

    # 3. Customer segmentation
    query3 = """
    SELECT 
        CASE 
            WHEN total_spend < 1000 THEN 'Low'
            WHEN total_spend < 5000 THEN 'Medium'
            ELSE 'High'
        END as segment,
        COUNT(*) as customers,
        AVG(total_spend) as avg_spend
    FROM (
        SELECT 
            customer_id,
            SUM(amount) as total_spend
        FROM sales_data
        GROUP BY customer_id
    )
    GROUP BY segment
    """
    segmentation = pd.read_sql_query(query3, conn)

    conn.close()
    return monthly_sales, top_products, segmentation

# Python analysis
def analyze_data(df):
    # Summary statistics
    summary = df.describe()

    # Revenue trends
    monthly_trend = df.groupby(df['date'].dt.to_period('M'))['amount'].sum()

    # Product performance
    product_performance = df.groupby('product_name')['amount'].agg(['sum', 'mean', 'count']).sort_values('sum', ascending=False)

    # Customer insights
    customer_insights = df.groupby('customer_id').agg({
        'amount': ['sum', 'mean', 'count']
    })
    customer_insights.columns = ['total_spend', 'avg_order_value', 'order_count']

    return {
        'summary': summary,
        'monthly_trend': monthly_trend,
        'product_performance': product_performance,
        'customer_insights': customer_insights
    }

import streamlit as st
import plotly.express as px

st.set_page_config(page_title="Analytics Dashboard", layout="wide")

st.title("End-to-End Analytics Dashboard")

# Load data
df = pd.read_csv('sales_data_transformed.csv')

# Metrics
col1, col2, col3, col4 = st.columns(4)
col1.metric("Total Revenue", f"₹{df['amount'].sum():,.0f}")
col2.metric("Total Orders", f"{len(df):,}")
col3.metric("Avg Order Value", f"₹{df['amount'].mean():,.0f}")
col4.metric("Unique Customers", f"{df['customer_id'].nunique():,}")

# Charts
st.subheader("Revenue Trends")
fig1 = px.line(df.groupby(df['date'].dt.to_period('M'))['amount'].sum().reset_index(),
               x='date', y='amount', title='Monthly Revenue')
st.plotly_chart(fig1, use_container_width=True)

col1, col2 = st.columns(2)
with col1:
    st.subheader("Top Products")
    top_products = df.groupby('product_name')['amount'].sum().sort_values(ascending=False).head(10).reset_index()
    fig2 = px.bar(top_products, x='product_name', y='amount', title='Top Products by Revenue')
    st.plotly_chart(fig2, use_container_width=True)

with col2:
    st.subheader("Customer Segmentation")
    df['segment'] = pd.cut(df['customer_id'].groupby(df['customer_id'])['amount'].transform('sum'),
                           bins=[0, 1000, 5000, float('inf')], labels=['Low', 'Medium', 'High'])
    segments = df['segment'].value_counts().reset_index()
    fig3 = px.pie(segments, values='count', names='segment', title='Customer Segments')
    st.plotly_chart(fig3, use_container_width=True)

# Filters
st.subheader("Filters")
date_range = st.date_input("Select Date Range", [])
if date_range:
    filtered_df = df[(df['date'] >= pd.to_datetime(date_range[0])) &
                     (df['date'] <= pd.to_datetime(date_range[1]))]
    st.dataframe(filtered_df)

# Deploy on Streamlit Cloud
# Requirements:
# streamlit, pandas, plotly, sqlite3

# Run:
# streamlit run dashboard.py

# Or export to Tableau/Power BI:
# 1. Save transformed data as CSV
# 2. Import into Tableau/Power BI
# 3. Create dashboard