setup_tables.sql

-- Run once to create output tables in the recommendations database.

-- Phase 1: materialized user-item interaction matrix (source for ALS training)
CREATE TABLE IF NOT EXISTS recommendations.user_item_interactions
(
    user_id          Int32,
    item_id          String,
    num_views        UInt64,
    num_add_to_cart  UInt64,
    num_wishlist     UInt64,
    num_checkout     UInt64,
    num_purchases    UInt64,
    interaction_score Float64,
    last_event_time  DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, item_id);

-- Personalized feed for logged-in users (ALS output)
CREATE TABLE IF NOT EXISTS recommendations.user_recs
(
    user_id     UInt32,
    item_ids    Array(String),
    scores      Array(Float32),
    computed_at DateTime
)
ENGINE = ReplacingMergeTree(computed_at)
ORDER BY user_id;

-- Item-item similarity via ALS item-factor cosine similarity
CREATE TABLE IF NOT EXISTS recommendations.item_similar
(
    item_id          String,
    similar_item_ids Array(String),
    scores           Array(Float32),
    computed_at      DateTime
)
ENGINE = ReplacingMergeTree(computed_at)
ORDER BY item_id;

-- Session co-occurrence pairs ("frequently viewed/bought together")
CREATE TABLE IF NOT EXISTS recommendations.item_pairs
(
    item_id         String,
    paired_item_ids Array(String),
    pair_scores     Array(Float32),
    pair_type       String,
    computed_at     DateTime
)
ENGINE = ReplacingMergeTree(computed_at)
ORDER BY (item_id, pair_type);

-- Popularity fallback for cold-start / anonymous users
CREATE TABLE IF NOT EXISTS recommendations.item_popularity
(
    item_id     String,
    category    String,
    event_count UInt64,
    computed_at DateTime
)
ENGINE = ReplacingMergeTree(computed_at)
ORDER BY (category, item_id);