-- ClickHouse Data Warehouse initialization for FlickNexs Analytics
-- This script creates OLAP tables optimized for analytical queries

-- Create database
CREATE DATABASE IF NOT EXISTS flicknexs_warehouse;
USE flicknexs_warehouse;

-- Content Performance Warehouse Table
CREATE TABLE IF NOT EXISTS content_performance (
    -- Primary dimensions
    content_id String,
    content_type LowCardinality(String),
    title String,
    category_id String,
    category_name String,
    partner_id String,
    
    -- Time dimensions
    timestamp DateTime64(3),
    date Date MATERIALIZED toDate(timestamp),
    hour UInt8 MATERIALIZED toHour(timestamp),
    day_of_week UInt8 MATERIALIZED toDayOfWeek(timestamp),
    
    -- User dimensions
    user_id String,
    user_type LowCardinality(String),
    subscription_type LowCardinality(String),
    
    -- Geographic dimensions
    country LowCardinality(String),
    region String,
    city String,
    
    -- Device dimensions
    device_type LowCardinality(String),
    browser LowCardinality(String),
    platform LowCardinality(String),
    
    -- Metrics
    views UInt64 DEFAULT 1,
    unique_viewers UInt64,
    watch_duration UInt32,
    total_duration UInt32,
    completion_percentage Float32,
    likes UInt32,
    dislikes UInt32,
    comments UInt32,
    shares UInt32,
    bookmarks UInt32,
    revenue Decimal64(4),
    currency LowCardinality(String),
    
    -- Quality metrics
    buffer_events UInt32,
    error_events UInt32,
    quality_changes UInt32
) 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (content_type, content_id, date, timestamp)
TTL date + INTERVAL 2 YEAR;

-- User Engagement Warehouse Table
CREATE TABLE IF NOT EXISTS user_engagement (
    -- User dimensions
    user_id String,
    user_type LowCardinality(String),
    registration_date Date,
    subscription_type LowCardinality(String),
    
    -- Session info
    session_id String,
    session_start DateTime64(3),
    session_end DateTime64(3),
    session_duration UInt32,
    
    -- Time dimensions
    timestamp DateTime64(3),
    date Date MATERIALIZED toDate(timestamp),
    hour UInt8 MATERIALIZED toHour(timestamp),
    
    -- Geographic dimensions
    country LowCardinality(String),
    region String,
    
    -- Device dimensions  
    device_type LowCardinality(String),
    platform LowCardinality(String),
    
    -- Engagement metrics
    page_views UInt32,
    content_views UInt32,
    unique_content UInt32,
    total_watch_time UInt32,
    likes_given UInt32,
    comments_made UInt32,
    shares_made UInt32,
    
    -- Behavioral metrics
    search_queries UInt32,
    playlist_interactions UInt32,
    profile_updates UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (user_id, date, timestamp)
TTL date + INTERVAL 1 YEAR;

-- Revenue Analytics Warehouse Table
CREATE TABLE IF NOT EXISTS revenue_analytics (
    -- Transaction info
    transaction_id String,
    user_id String,
    content_id String,
    partner_id String,
    
    -- Time dimensions
    timestamp DateTime64(3),
    date Date MATERIALIZED toDate(timestamp),
    month String MATERIALIZED formatDateTime(timestamp, '%Y-%m'),
    quarter String MATERIALIZED concat('Q', toString(toQuarter(timestamp)), '-', toString(toYear(timestamp))),
    
    -- Revenue details
    amount Decimal64(4),
    currency LowCardinality(String),
    revenue_type LowCardinality(String), -- subscription, ppv, ad, commission
    payment_method LowCardinality(String),
    
    -- Business metrics
    commission Decimal64(4),
    net_revenue Decimal64(4) MATERIALIZED amount - commission,
    
    -- Geographic dimensions
    country LowCardinality(String),
    
    -- Subscription details (if applicable)
    plan_type String,
    billing_cycle LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (revenue_type, date, user_id, timestamp)
TTL date + INTERVAL 7 YEAR; -- Keep financial data longer

-- Geographic Analytics Table
CREATE TABLE IF NOT EXISTS geographic_analytics (
    -- Geographic dimensions
    country LowCardinality(String),
    region String,
    city String,
    
    -- Time dimensions
    timestamp DateTime64(3),
    date Date MATERIALIZED toDate(timestamp),
    
    -- User metrics
    unique_users UInt64,
    new_users UInt64,
    active_users UInt64,
    
    -- Content metrics
    total_views UInt64,
    unique_content_views UInt64,
    avg_watch_duration Float32,
    
    -- Revenue metrics
    total_revenue Decimal64(4),
    avg_revenue_per_user Decimal64(4),
    
    -- Network metrics
    avg_bandwidth Float32,
    cdn_hits UInt64,
    
    -- Device preferences
    mobile_percentage Float32,
    desktop_percentage Float32,
    tv_percentage Float32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (country, region, date)
TTL date + INTERVAL 2 YEAR;

-- System Performance Metrics Table
CREATE TABLE IF NOT EXISTS system_metrics (
    -- Metric identification
    metric_name LowCardinality(String),
    service_name LowCardinality(String),
    instance_id String,
    
    -- Time dimension
    timestamp DateTime64(3),
    date Date MATERIALIZED toDate(timestamp),
    
    -- Metric value and metadata
    value Float64,
    unit LowCardinality(String),
    tags Map(String, String),
    
    -- Additional context
    environment LowCardinality(String),
    datacenter LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (metric_name, service_name, timestamp)
TTL date + INTERVAL 90 DAY;

-- Create aggregated tables for faster queries

-- Daily Content Summary
CREATE TABLE IF NOT EXISTS content_daily_summary (
    content_id String,
    content_type LowCardinality(String),
    date Date,
    
    -- Aggregated metrics
    total_views UInt64,
    unique_viewers UInt64,
    total_watch_duration UInt64,
    avg_completion_rate Float32,
    total_revenue Decimal64(4),
    
    -- Engagement totals
    total_likes UInt64,
    total_comments UInt64,
    total_shares UInt64,
    
    -- Geographic distribution
    top_country String,
    country_distribution Map(String, UInt64),
    
    -- Device distribution  
    device_distribution Map(String, UInt64)
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (content_type, content_id, date)
TTL date + INTERVAL 2 YEAR;

-- User Daily Summary
CREATE TABLE IF NOT EXISTS user_daily_summary (
    user_id String,
    date Date,
    
    -- Activity metrics
    sessions UInt32,
    total_watch_time UInt32,
    content_views UInt32,
    unique_content UInt32,
    
    -- Engagement metrics
    likes_given UInt32,
    comments_made UInt32,
    shares_made UInt32,
    
    -- Revenue contribution
    amount_spent Decimal64(4),
    
    -- Last activity info
    last_active_timestamp DateTime64(3),
    preferred_device LowCardinality(String)
)
ENGINE = ReplacingMergeTree(last_active_timestamp)
PARTITION BY toYYYYMM(date)
ORDER BY (user_id, date)
TTL date + INTERVAL 1 YEAR;

-- Create materialized views for real-time aggregation

-- Real-time content metrics
CREATE MATERIALIZED VIEW IF NOT EXISTS content_metrics_mv
TO content_daily_summary AS
SELECT
    content_id,
    content_type,
    date,
    sum(views) as total_views,
    uniq(user_id) as unique_viewers,
    sum(watch_duration) as total_watch_duration,
    avg(completion_percentage) as avg_completion_rate,
    sum(revenue) as total_revenue,
    sum(likes) as total_likes,
    sum(comments) as total_comments,
    sum(shares) as total_shares,
    topK(1)(country)[1] as top_country,
    sumMap(map(country, views)) as country_distribution,
    sumMap(map(device_type, views)) as device_distribution
FROM content_performance
GROUP BY content_id, content_type, date;

-- Real-time user metrics  
CREATE MATERIALIZED VIEW IF NOT EXISTS user_metrics_mv
TO user_daily_summary AS
SELECT
    user_id,
    date,
    uniq(session_id) as sessions,
    sum(total_watch_time) as total_watch_time,
    sum(content_views) as content_views,
    sum(unique_content) as unique_content,
    sum(likes_given) as likes_given,
    sum(comments_made) as comments_made,
    sum(shares_made) as shares_made,
    sum(amount_spent) as amount_spent,
    max(timestamp) as last_active_timestamp,
    topK(1)(device_type)[1] as preferred_device
FROM user_engagement
GROUP BY user_id, date;

-- Create functions for common analytics operations

-- Function to calculate content performance score
CREATE FUNCTION contentPerformanceScore AS (views, completion_rate, engagement_rate, revenue) -> 
    (views * 0.3 + completion_rate * 0.25 + engagement_rate * 0.25 + (revenue * 100) * 0.2);

-- Function to calculate user engagement score
CREATE FUNCTION userEngagementScore AS (watch_time, content_diversity, interactions, consistency) ->
    (log10(watch_time + 1) * 0.4 + content_diversity * 0.2 + log10(interactions + 1) * 0.2 + consistency * 0.2);

-- Create dictionary tables for dimension lookups

-- Content metadata dictionary
CREATE TABLE IF NOT EXISTS content_metadata_dict (
    content_id String,
    title String,
    description String,
    duration UInt32,
    release_date Date,
    content_type LowCardinality(String),
    category_id String,
    category_name String,
    tags Array(String),
    language LowCardinality(String),
    age_rating LowCardinality(String),
    partner_id String,
    partner_name String
)
ENGINE = ReplacingMergeTree()
ORDER BY content_id;

-- User metadata dictionary
CREATE TABLE IF NOT EXISTS user_metadata_dict (
    user_id String,
    registration_date Date,
    subscription_type LowCardinality(String),
    preferred_language LowCardinality(String),
    age_group LowCardinality(String),
    country LowCardinality(String),
    lifetime_value Decimal64(4),
    churn_risk_score Float32
)
ENGINE = ReplacingMergeTree()
ORDER BY user_id;

-- Create indexes for faster queries
-- Note: ClickHouse uses different index types

-- Skip indexes for better performance on filtered queries
ALTER TABLE content_performance ADD INDEX idx_content_type content_type TYPE set(10) GRANULARITY 4;
ALTER TABLE content_performance ADD INDEX idx_country country TYPE set(200) GRANULARITY 4;
ALTER TABLE content_performance ADD INDEX idx_device_type device_type TYPE set(10) GRANULARITY 4;

ALTER TABLE user_engagement ADD INDEX idx_user_type user_type TYPE set(10) GRANULARITY 4;
ALTER TABLE user_engagement ADD INDEX idx_subscription subscription_type TYPE set(20) GRANULARITY 4;

ALTER TABLE revenue_analytics ADD INDEX idx_revenue_type revenue_type TYPE set(10) GRANULARITY 4;
ALTER TABLE revenue_analytics ADD INDEX idx_currency currency TYPE set(20) GRANULARITY 4;

-- Create sample data for testing (optional)
-- This would be populated by your ETL processes in production

-- Insert sample content performance data
-- INSERT INTO content_performance (content_id, content_type, title, timestamp, user_id, country, device_type, views, watch_duration, total_duration, completion_percentage, revenue)
-- VALUES 
--     ('video_001', 'video', 'Sample Video 1', now(), 'user_001', 'US', 'mobile', 1, 1800, 3600, 50.0, 2.99),
--     ('video_002', 'video', 'Sample Video 2', now(), 'user_002', 'UK', 'desktop', 1, 2700, 3600, 75.0, 4.99);

-- Create grants for analytics user
GRANT ALL ON flicknexs_warehouse.* TO analytics_user;

-- Add comments for documentation
ALTER TABLE content_performance COMMENT 'Detailed content performance metrics for OLAP queries';
ALTER TABLE user_engagement COMMENT 'User engagement and behavioral analytics';
ALTER TABLE revenue_analytics COMMENT 'Revenue and financial analytics with full transaction details';
ALTER TABLE geographic_analytics COMMENT 'Geographic distribution and performance metrics';
ALTER TABLE system_metrics COMMENT 'System performance and monitoring metrics';

-- Optimize tables (this helps with query performance)
OPTIMIZE TABLE content_performance;
OPTIMIZE TABLE user_engagement;
OPTIMIZE TABLE revenue_analytics;
OPTIMIZE TABLE geographic_analytics;
