-- ============================================================
-- INIT SCRIPT: PostgreSQL Data Warehouse
-- Star Schema for Olist E-commerce
-- ============================================================

-- Create additional database for Airflow
SELECT 'CREATE DATABASE airflow_db' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'airflow_db')\gexec

-- Switch to main DW database
\c ecommerce_dw;

-- ============================================================
-- DIMENSION TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS dim_time (
    date_key        INTEGER PRIMARY KEY,
    full_date       DATE NOT NULL,
    day_of_week     SMALLINT NOT NULL,      -- 0=Monday, 6=Sunday
    day_name        VARCHAR(10) NOT NULL,
    day_of_month    SMALLINT NOT NULL,
    week_of_year    SMALLINT NOT NULL,
    month           SMALLINT NOT NULL,
    month_name      VARCHAR(10) NOT NULL,
    quarter         SMALLINT NOT NULL,
    year            SMALLINT NOT NULL,
    is_weekend      BOOLEAN NOT NULL DEFAULT FALSE,
    is_holiday      BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS dim_geography (
    geo_key             SERIAL PRIMARY KEY,
    zip_code_prefix     VARCHAR(10) NOT NULL,
    city                VARCHAR(100),
    state               CHAR(2),
    state_name          VARCHAR(50),
    region              VARCHAR(20),
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    UNIQUE(zip_code_prefix)
);

CREATE TABLE IF NOT EXISTS dim_customer (
    customer_key        SERIAL PRIMARY KEY,
    customer_id         VARCHAR(50) NOT NULL,
    customer_unique_id  VARCHAR(50) NOT NULL,
    zip_code            VARCHAR(10),
    city                VARCHAR(100),
    state               CHAR(2),
    segment             VARCHAR(20) DEFAULT 'standard',
    lifetime_value      DECIMAL(12, 2) DEFAULT 0,
    order_count         INTEGER DEFAULT 0,
    first_order_date    TIMESTAMP,
    last_order_date     TIMESTAMP,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(customer_id)
);

CREATE TABLE IF NOT EXISTS dim_product (
    product_key         SERIAL PRIMARY KEY,
    product_id          VARCHAR(50) NOT NULL,
    category_pt         VARCHAR(100),
    category_en         VARCHAR(100),
    weight_g            INTEGER,
    length_cm           INTEGER,
    height_cm           INTEGER,
    width_cm            INTEGER,
    photos_qty          SMALLINT,
    name_length         INTEGER,
    desc_length         INTEGER,
    UNIQUE(product_id)
);

CREATE TABLE IF NOT EXISTS dim_seller (
    seller_key          SERIAL PRIMARY KEY,
    seller_id           VARCHAR(50) NOT NULL,
    zip_code            VARCHAR(10),
    city                VARCHAR(100),
    state               CHAR(2),
    total_orders        INTEGER DEFAULT 0,
    avg_rating          DECIMAL(3, 2) DEFAULT 0,
    total_revenue       DECIMAL(14, 2) DEFAULT 0,
    category_focus      VARCHAR(100),
    UNIQUE(seller_id)
);

-- ============================================================
-- FACT TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS fact_orders (
    order_key               BIGSERIAL PRIMARY KEY,
    order_id                VARCHAR(50) NOT NULL,
    customer_key            INTEGER REFERENCES dim_customer(customer_key),
    seller_key              INTEGER REFERENCES dim_seller(seller_key),
    product_key             INTEGER REFERENCES dim_product(product_key),
    date_key                INTEGER REFERENCES dim_time(date_key),
    geography_key           INTEGER REFERENCES dim_geography(geo_key),
    
    -- Order attributes
    order_status            VARCHAR(20) NOT NULL,
    item_count              SMALLINT DEFAULT 1,
    
    -- Financial measures
    total_price             DECIMAL(12, 2),
    total_freight           DECIMAL(12, 2),
    total_payment           DECIMAL(12, 2),
    
    -- Payment details
    payment_type            VARCHAR(20),
    payment_installments    SMALLINT,
    
    -- Review measures
    review_score            SMALLINT,
    review_has_text         BOOLEAN DEFAULT FALSE,
    
    -- Delivery measures
    delivery_days           INTEGER,
    estimated_delivery_days INTEGER,
    delivery_delay_days     INTEGER,
    is_late_delivery        BOOLEAN DEFAULT FALSE,
    
    -- Timestamps
    purchase_timestamp      TIMESTAMP,
    approved_timestamp      TIMESTAMP,
    shipped_timestamp       TIMESTAMP,
    delivered_timestamp     TIMESTAMP,
    estimated_delivery_date TIMESTAMP,
    review_timestamp        TIMESTAMP,
    
    -- Metadata
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(order_id)
);

CREATE TABLE IF NOT EXISTS fact_order_items (
    item_key                BIGSERIAL PRIMARY KEY,
    order_id                VARCHAR(50) NOT NULL,
    order_item_id           SMALLINT NOT NULL,
    product_key             INTEGER REFERENCES dim_product(product_key),
    seller_key              INTEGER REFERENCES dim_seller(seller_key),
    price                   DECIMAL(12, 2),
    freight_value           DECIMAL(12, 2),
    shipping_limit_date     TIMESTAMP,
    UNIQUE(order_id, order_item_id)
);

CREATE TABLE IF NOT EXISTS fact_payments (
    payment_key             BIGSERIAL PRIMARY KEY,
    order_id                VARCHAR(50) NOT NULL,
    payment_sequential      SMALLINT NOT NULL,
    payment_type            VARCHAR(20),
    payment_installments    SMALLINT,
    payment_value           DECIMAL(12, 2),
    UNIQUE(order_id, payment_sequential)
);

CREATE TABLE IF NOT EXISTS fact_reviews (
    review_key              BIGSERIAL PRIMARY KEY,
    review_id               VARCHAR(50) NOT NULL,
    order_id                VARCHAR(50) NOT NULL,
    review_score            SMALLINT,
    review_comment_title    TEXT,
    review_comment_message  TEXT,
    review_creation_date    TIMESTAMP,
    review_answer_timestamp TIMESTAMP,
    UNIQUE(review_id)
);

-- ============================================================
-- AGGREGATION TABLES
-- ============================================================

CREATE TABLE IF NOT EXISTS agg_daily_revenue (
    date                DATE PRIMARY KEY,
    total_revenue       DECIMAL(14, 2),
    total_freight       DECIMAL(14, 2),
    gmv                 DECIMAL(14, 2),
    order_count         INTEGER,
    avg_order_value     DECIMAL(10, 2),
    unique_customers    INTEGER,
    item_count          INTEGER
);

CREATE TABLE IF NOT EXISTS agg_category_performance (
    id                  SERIAL PRIMARY KEY,
    category_en         VARCHAR(100),
    year_month          VARCHAR(7),
    revenue             DECIMAL(14, 2),
    order_count         INTEGER,
    avg_review_score    DECIMAL(3, 2),
    avg_price           DECIMAL(10, 2),
    seller_count        INTEGER,
    UNIQUE(category_en, year_month)
);

CREATE TABLE IF NOT EXISTS agg_seller_ranking (
    seller_key          INTEGER PRIMARY KEY REFERENCES dim_seller(seller_key),
    seller_id           VARCHAR(50),
    city                VARCHAR(100),
    state               CHAR(2),
    total_revenue       DECIMAL(14, 2),
    total_freight       DECIMAL(14, 2),
    order_count         INTEGER,
    item_count          INTEGER,
    avg_rating          DECIMAL(3, 2),
    avg_delivery_days   DECIMAL(5, 1),
    late_delivery_pct   DECIMAL(5, 2),
    category_focus      VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS agg_customer_segments (
    segment             VARCHAR(20) PRIMARY KEY,
    customer_count      INTEGER,
    avg_ltv             DECIMAL(10, 2),
    avg_orders          DECIMAL(5, 2),
    avg_review_score    DECIMAL(3, 2),
    total_revenue       DECIMAL(14, 2)
);

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_fact_orders_purchase_ts ON fact_orders(purchase_timestamp);
CREATE INDEX IF NOT EXISTS idx_fact_orders_status ON fact_orders(order_status);
CREATE INDEX IF NOT EXISTS idx_fact_orders_customer ON fact_orders(customer_key);
CREATE INDEX IF NOT EXISTS idx_fact_orders_seller ON fact_orders(seller_key);
CREATE INDEX IF NOT EXISTS idx_fact_orders_product ON fact_orders(product_key);
CREATE INDEX IF NOT EXISTS idx_fact_orders_date ON fact_orders(date_key);
CREATE INDEX IF NOT EXISTS idx_fact_order_items_order ON fact_order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_fact_payments_order ON fact_payments(order_id);
CREATE INDEX IF NOT EXISTS idx_fact_reviews_order ON fact_reviews(order_id);
CREATE INDEX IF NOT EXISTS idx_dim_customer_unique ON dim_customer(customer_unique_id);
CREATE INDEX IF NOT EXISTS idx_dim_customer_state ON dim_customer(state);
CREATE INDEX IF NOT EXISTS idx_agg_daily_revenue_date ON agg_daily_revenue(date);

-- ============================================================
-- POPULATE dim_time (2016-2019)
-- ============================================================

INSERT INTO dim_time (date_key, full_date, day_of_week, day_name, day_of_month, 
                      week_of_year, month, month_name, quarter, year, is_weekend)
SELECT 
    TO_CHAR(d, 'YYYYMMDD')::INTEGER as date_key,
    d as full_date,
    EXTRACT(DOW FROM d)::SMALLINT as day_of_week,
    TO_CHAR(d, 'Day') as day_name,
    EXTRACT(DAY FROM d)::SMALLINT as day_of_month,
    EXTRACT(WEEK FROM d)::SMALLINT as week_of_year,
    EXTRACT(MONTH FROM d)::SMALLINT as month,
    TO_CHAR(d, 'Month') as month_name,
    EXTRACT(QUARTER FROM d)::SMALLINT as quarter,
    EXTRACT(YEAR FROM d)::SMALLINT as year,
    EXTRACT(DOW FROM d) IN (0, 6) as is_weekend
FROM generate_series('2016-01-01'::date, '2019-12-31'::date, '1 day'::interval) d
ON CONFLICT (date_key) DO NOTHING;

-- Brazilian state regions mapping
INSERT INTO dim_geography (zip_code_prefix, city, state, state_name, region) VALUES
('00000', 'Unknown', 'XX', 'Unknown', 'Unknown')
ON CONFLICT (zip_code_prefix) DO NOTHING;

RAISE NOTICE 'PostgreSQL Data Warehouse initialized successfully!';
