Arquitectura de Datos

erDiagram
    products {
        int id PK
        string name
        string line
        string category
        int size_ml
        string ean UK
        string brand
        string image_url
        bool is_active
        text ingredients
        datetime created_at
    }

    stores {
        int id PK
        string name
        string chain
        string region
        string city
        string format
        string address
    }

    tc_executions {
        int id PK
        int product_id FK
        int store_id FK
        date date
        float osa_score
        float sos_percentage
        float shelf_compliance
        bool price_tag_present
        int facing_count
        string photo_url
    }

    nl_market_share {
        int id PK
        string brand
        string category
        string channel
        date period_start
        date period_end
        float value_share
        float volume_share
        float numeric_distribution
        float weighted_distribution
    }

    nl_category_trends {
        int id PK
        string category
        date period
        float total_value
        float total_volume
        float avg_price
        float growth_yoy
    }

    sap_inventory {
        int id PK
        int product_id FK
        string warehouse
        int quantity
        int min_stock
        int max_stock
        datetime last_updated
    }

    sap_sales_orders {
        int id PK
        int product_id FK
        string customer
        int quantity
        float unit_price
        float total
        string status
        date order_date
        date delivery_date
    }

    sap_production {
        int id PK
        int product_id FK
        int planned_qty
        int produced_qty
        string batch
        date start_date
        date end_date
        string status
    }

    price_observations {
        int id PK
        int product_id FK
        int store_id FK
        float price
        float promo_price
        datetime observed_at
        string source
    }

    kpi_snapshots {
        int id PK
        string metric_name
        float metric_value
        date period
        string dimension
        string dimension_value
        datetime computed_at
    }

    price_alerts {
        int id PK
        int product_id FK
        string alert_type
        string chain
        float old_price
        float new_price
        float change_pct
        string severity
        datetime created_at
        bool acknowledged
    }

    saved_filters {
        int id PK
        string name
        text filters_json
        datetime created_at
        bool is_default
    }

    custom_workspaces {
        int id PK
        string name
        text description
        text layout
        datetime created_at
        datetime updated_at
        bool is_active
    }

    products ||--o{ tc_executions : "product_id"
    stores ||--o{ tc_executions : "store_id"
    products ||--o{ sap_inventory : "product_id"
    products ||--o{ sap_sales_orders : "product_id"
    products ||--o{ sap_production : "product_id"
    products ||--o{ price_observations : "product_id"
    stores ||--o{ price_observations : "store_id"
    products ||--o{ price_alerts : "product_id"
                    

flowchart LR
    subgraph Sources["Fuentes Externas"]
        direction TB
        TC["Teamcore API"]
        NL["Nielsen Files"]
        SAP["SAP ERP"]
        WS["Web Scrapers"]
    end

    subgraph DB["Base de Datos SQLite"]
        direction TB
        tc_exec["tc_executions"]
        nl_ms["nl_market_share"]
        nl_ct["nl_category_trends"]
        sap_inv["sap_inventory"]
        sap_so["sap_sales_orders"]
        sap_prod["sap_production"]
        prices["price_observations"]
        kpis["kpi_snapshots"]
        alerts["price_alerts"]
        prods["products"]
        stores["stores"]
    end

    subgraph Analytics["Dashboards & Analytics"]
        direction TB
        D1["Dashboard Principal"]
        D2["Retail Execution"]
        D3["Market Intelligence"]
        D4["SAP Analytics"]
        D5["Price Tracking"]
        D6["Comparador Precios"]
        D7["Modelos Econometricos"]
        D8["Reportes"]
    end

    subgraph AI["IA & Avanzado"]
        direction TB
        A1["KPI Engine"]
        A2["Alertas Automaticas"]
        A3["AI Chatbot"]
    end

    TC -->|"OSA, SOS, compliance"| tc_exec
    NL -->|"market share"| nl_ms
    NL -->|"category trends"| nl_ct
    SAP -->|"stock levels"| sap_inv
    SAP -->|"sales orders"| sap_so
    SAP -->|"production data"| sap_prod
    WS -->|"retail prices"| prices

    tc_exec --> D2
    tc_exec --> D1
    nl_ms --> D3
    nl_ms --> D1
    nl_ct --> D3
    sap_inv --> D4
    sap_so --> D4
    sap_so --> D1
    sap_prod --> D4
    prices --> D5
    prices --> D6
    prices --> D1

    tc_exec --> D7
    nl_ms --> D7
    sap_so --> D7
    prices --> D7

    D1 --> A1
    A1 --> kpis
    prices --> A2
    A2 --> alerts

    prods -.->|"FK refs"| tc_exec
    prods -.->|"FK refs"| sap_inv
    prods -.->|"FK refs"| sap_so
    prods -.->|"FK refs"| sap_prod
    prods -.->|"FK refs"| prices
    stores -.->|"FK refs"| tc_exec
    stores -.->|"FK refs"| prices

    tc_exec --> D8
    nl_ms --> D8
    sap_so --> D8
    prices --> D8
    kpis --> A3

    classDef source fill:#fdf2fa,stroke:#A1249A,stroke-width:2px,color:#7d1347
    classDef database fill:#f3e8ff,stroke:#0C369C,stroke-width:2px,color:#581c87
    classDef dashboard fill:#eff6ff,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    classDef ai fill:#ecfdf5,stroke:#10b981,stroke-width:2px,color:#065f46

    class TC,NL,SAP,WS source
    class tc_exec,nl_ms,nl_ct,sap_inv,sap_so,sap_prod,prices,kpis,alerts,prods,stores database
    class D1,D2,D3,D4,D5,D6,D7,D8 dashboard
    class A1,A2,A3 ai