Calibrate Dashboard Data Architecture

flowchart TD
    %% -- STYLES --
    classDef source fill:#e0f2fe,stroke:#0284c7,stroke-width:2px,color:#0c4a6e,font-weight:bold
    classDef etl fill:#ffedd5,stroke:#ea580c,stroke-width:2px,color:#7c2d12,stroke-dasharray: 4 4
    classDef bqDataset fill:#dcfce7,stroke:#16a34a,stroke-width:2px,color:#14532d
    classDef bqTable fill:#ffffff,stroke:#86efac,stroke-width:1px,color:#166534
    classDef bqView fill:#fef08a,stroke:#ca8a04,stroke-width:2px,color:#713f12,stroke-dasharray: 3 3
    classDef auth fill:#f1f5f9,stroke:#64748b,stroke-width:1px,color:#334155,stroke-dasharray: 2 2
    classDef api fill:#f3e8ff,stroke:#9333ea,stroke-width:2px,color:#4c1d95,font-weight:bold
    classDef dashboard fill:#ccfbf1,stroke:#0d9488,stroke-width:2px,color:#115e59,font-weight:bold

    %% -- LAYER 1: SOURCE SYSTEMS --
    subgraph Sources ["1️⃣ SOURCE SYSTEMS (External & Internal)"]
        direction LR
        L["📦 Logiwa WMS<br/>(15 min / API Polling)"]:::source
        TS["🚢 TechShip<br/>(Daily / Batch Import)"]:::source
        TT["📍 TechTrack<br/>(Real-time / Webhooks)"]:::source
        G["👥 Gusto HR<br/>(Weekly / API Sync)"]:::source
        B["💰 Billing System<br/>(Daily / DB Export)"]:::source
    end

    %% -- LAYER 2: ETL --
    subgraph Pipeline ["2️⃣ DATA INTEGRATION PIPELINE"]
        direction LR
        CS["⏱️ Cloud Scheduler<br/>(Triggers Jobs)"]:::etl
        CR["🐍 Python ETL Scripts<br/>(Cloud Run)"]:::etl
        CS -.-> CR
    end

    L -->|"Orders, Inv, Tasks"| CR
    TS -->|"Shipping Costs, Margins"| CR
    TT -->|"Delivery SLAs, Transit"| CR
    G -->|"Payrolls, Time Entries"| CR
    B -->|"Invoices, Catalog"| CR

    %% -- LAYER 3: BIGQUERY --
    subgraph BigQuery ["3️⃣ GOOGLE CLOUD BIGQUERY (Project: yc-partners)"]
        direction TB
        
        subgraph Datasets ["🗄️ Raw Datasets (~816k Total Records)"]
            direction LR
            
            subgraph DS_L ["yc-partners.logiwa (101 tables)"]
                direction TB
                T_SO["shipment_orders"]:::bqTable
                T_SOD["shipment_order_details"]:::bqTable
                T_SOP["shipment_order_packages"]:::bqTable
                T_WT["warehouse_tasks"]:::bqTable
                T_IL["inventory_locations"]:::bqTable
            end
            
            subgraph DS_TS ["yc-partners.techship (12 tables)"]
                direction TB
                T_TSS["techship_shipments"]:::bqTable
                T_CR["carrier_rates"]:::bqTable
                T_SA["shipment_audits"]:::bqTable
            end

            subgraph DS_TT ["yc-partners.techtrack (8 tables)"]
                direction TB
                T_TE["tracking_events"]:::bqTable
                T_DP["delivery_performance"]:::bqTable
            end

            subgraph DS_G ["yc-partners.gusto (15 tables)"]
                direction TB
                T_P["payrolls"]:::bqTable
                T_TE2["time_entries"]:::bqTable
            end

            subgraph DS_B ["yc-partners.billing (14 tables)"]
                direction TB
                T_I["invoices"]:::bqTable
                T_ILI["invoice_line_items"]:::bqTable
            end
        end

        subgraph Views ["📊 Aggregation & Views Layer"]
            direction LR
            V1["v_daily_income_statement<br/>(Revenue - Freight - Labor)"]:::bqView
            V2["v_client_rankings<br/>(Rev, Vol, Profit)"]:::bqView
            V3["v_carrier_performance<br/>(Cost, SLA, Margin)"]:::bqView
            V4["v_warehouse_kpi_daily_summary<br/>(Productivity, Orders)"]:::bqView
        end

        DS_L & DS_TS & DS_TT & DS_G & DS_B -->|"SQL Transformation"| Views
    end

    CR -->|"Load Raw Data"| Datasets

    %% -- LAYER 4: API BACKEND --
    subgraph Backend ["4️⃣ API & APPLICATION LAYER"]
        direction TB
        SA["🔐 Service Account Auth<br/>(sergio@yc-partners)"]:::auth
        API["⚙️ Flask Backend API<br/>(enhanced_api.py / demo_api.py)"]:::api
        
        subgraph Endpoints ["Active Endpoints"]
            direction LR
            EP1["/api/dashboard/summary"]:::bqTable
            EP2["/api/dashboard/clients"]:::bqTable
            EP3["/api/dashboard/income-statement"]:::bqTable
            EP4["/api/analytics/orders"]:::bqTable
        end
        
        API --- Endpoints
    end

    Views -->|"SQL Query Execution"| SA
    SA --> API

    %% -- LAYER 5: FRONTEND DASHBOARD --
    subgraph Frontend ["5️⃣ CALIBRATE DASHBOARD (Next.js - calibrate.sgarza.com)"]
        direction LR
        UI1["📦 Orders Dashboard<br/>(Volume, AOV, Weight)"]:::dashboard
        UI2["👥 Clients Dashboard<br/>(Profitability, Frequency)"]:::dashboard
        UI3["🚚 Freight Dashboard<br/>(Margins, Costs by Zone)"]:::dashboard
        UI4["🏭 Warehouse Dashboard<br/>(Labor/Order, Productivity)"]:::dashboard
        UI5["💵 Income Statement<br/>(Gross/Net Margin, COGS)"]:::dashboard
    end

    API -->|"JSON Payload over HTTPS<br/>(< 1s Response Time)"| Frontend

    %% Highlight specific complex calculations
    UI5 -.->|"Calculated via: Billing Rev<br/>- TechShip Costs<br/>- Gusto Labor"| V1