src/customer_intelligence/etl/warehouse_ddl.sql

-- Warehouse layer: dimensional model
-- Idempotent: all CREATE TABLE IF NOT EXISTS

CREATE SCHEMA IF NOT EXISTS warehouse;

-- ── Dimension: Dates ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS warehouse.dim_dates (
    date_id         DATE PRIMARY KEY,
    year            SMALLINT    NOT NULL,
    quarter         SMALLINT    NOT NULL,
    month           SMALLINT    NOT NULL,
    month_name      TEXT        NOT NULL,
    week            SMALLINT    NOT NULL,
    day_of_week     SMALLINT    NOT NULL,   -- 0=Mon, 6=Sun
    day_name        TEXT        NOT NULL,
    is_weekend      BOOLEAN     NOT NULL,
    is_month_end    BOOLEAN     NOT NULL
);

-- ── Dimension: Customers ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS warehouse.dim_customers (
    customer_key        SERIAL PRIMARY KEY,
    customer_id         TEXT UNIQUE NOT NULL,   -- from stg_customers (order-level id)
    customer_unique_id  TEXT NOT NULL,
    zip_code_prefix     TEXT,
    city                TEXT,
    state               TEXT,
    lat                 DOUBLE PRECISION,
    lng                 DOUBLE PRECISION,
    first_order_date    DATE,
    last_order_date     DATE,
    total_orders        INTEGER         DEFAULT 0,
    total_revenue       NUMERIC(12, 2)  DEFAULT 0,
    clv_score           NUMERIC(10, 4),
    rfm_segment         TEXT,           -- e.g. "Champions", "At Risk"
    churn_probability   NUMERIC(5, 4),
    segment_label       INTEGER,        -- KMeans cluster id
    _updated_at         TIMESTAMPTZ     DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_dim_customers_unique_id ON warehouse.dim_customers(customer_unique_id);
CREATE INDEX IF NOT EXISTS idx_dim_customers_state ON warehouse.dim_customers(state);
CREATE INDEX IF NOT EXISTS idx_dim_customers_segment ON warehouse.dim_customers(rfm_segment);

-- ── Dimension: Products ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS warehouse.dim_products (
    product_key         SERIAL PRIMARY KEY,
    product_id          TEXT UNIQUE NOT NULL,
    category_name_pt    TEXT,
    category_name_en    TEXT,
    weight_g            INTEGER,
    length_cm           INTEGER,
    height_cm           INTEGER,
    width_cm            INTEGER,
    _updated_at         TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_dim_products_category ON warehouse.dim_products(category_name_en);

-- ── Dimension: Sellers ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS warehouse.dim_sellers (
    seller_key          SERIAL PRIMARY KEY,
    seller_id           TEXT UNIQUE NOT NULL,
    zip_code_prefix     TEXT,
    city                TEXT,
    state               TEXT,
    lat                 DOUBLE PRECISION,
    lng                 DOUBLE PRECISION,
    total_orders        INTEGER         DEFAULT 0,
    avg_review_score    NUMERIC(3, 2),
    on_time_rate        NUMERIC(5, 4),
    _updated_at         TIMESTAMPTZ     DEFAULT now()
);

-- ── Fact Table: Orders (grain = 1 row per order_item) ──────────────────────
CREATE TABLE IF NOT EXISTS warehouse.fact_orders (
    fact_key                BIGSERIAL PRIMARY KEY,
    order_id                TEXT        NOT NULL,
    order_item_id           SMALLINT    NOT NULL,
    customer_key            INTEGER     REFERENCES warehouse.dim_customers(customer_key),
    product_key             INTEGER     REFERENCES warehouse.dim_products(product_key),
    seller_key              INTEGER     REFERENCES warehouse.dim_sellers(seller_key),
    order_date_id           DATE        REFERENCES warehouse.dim_dates(date_id),
    approved_date_id        DATE,
    delivered_date_id       DATE,
    estimated_delivery_id   DATE,
    order_status            TEXT,
    price                   NUMERIC(10, 2),
    freight_value           NUMERIC(10, 2),
    payment_value           NUMERIC(10, 2),
    payment_type            TEXT,
    payment_installments    SMALLINT,
    review_score            SMALLINT,
    days_to_delivery        INTEGER,        -- delivered_date - approved_date
    delivery_delay_days     INTEGER,        -- delivered_date - estimated_date (neg = early)
    is_late                 BOOLEAN,
    UNIQUE(order_id, order_item_id)
);

CREATE INDEX IF NOT EXISTS idx_fact_orders_customer ON warehouse.fact_orders(customer_key);
CREATE INDEX IF NOT EXISTS idx_fact_orders_date ON warehouse.fact_orders(order_date_id);
CREATE INDEX IF NOT EXISTS idx_fact_orders_seller ON warehouse.fact_orders(seller_key);
CREATE INDEX IF NOT EXISTS idx_fact_orders_product ON warehouse.fact_orders(product_key);
CREATE INDEX IF NOT EXISTS idx_fact_orders_status ON warehouse.fact_orders(order_status);