-- TimescaleDB initialization script for FlickNexs Analytics
-- This script creates the necessary tables and hypertables for time-series data

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create analytics_events table for raw event data
CREATE TABLE IF NOT EXISTS analytics_events (
    id BIGSERIAL,
    event_id VARCHAR(64) UNIQUE NOT NULL,
    user_id VARCHAR(64),
    session_id VARCHAR(64) NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    content_id VARCHAR(64),
    content_type VARCHAR(20),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ingestion_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB,
    device_info JSONB,
    location JSONB,
    value NUMERIC,
    
    -- Indexes for common queries
    PRIMARY KEY (timestamp, event_id)
);

-- Convert to hypertable (partitioned by time)
SELECT create_hypertable('analytics_events', 'timestamp', 
    chunk_time_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

-- Create indexes for optimal query performance
CREATE INDEX IF NOT EXISTS idx_analytics_events_user_id ON analytics_events (user_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_analytics_events_content_id ON analytics_events (content_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_analytics_events_event_type ON analytics_events (event_type, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_analytics_events_session_id ON analytics_events (session_id, timestamp DESC);

-- Create GIN index for metadata queries
CREATE INDEX IF NOT EXISTS idx_analytics_events_metadata ON analytics_events USING GIN (metadata);
CREATE INDEX IF NOT EXISTS idx_analytics_events_device_info ON analytics_events USING GIN (device_info);
CREATE INDEX IF NOT EXISTS idx_analytics_events_location ON analytics_events USING GIN (location);

-- Create content_analytics table for aggregated content metrics
CREATE TABLE IF NOT EXISTS content_analytics (
    id BIGSERIAL,
    content_id VARCHAR(64) NOT NULL,
    content_type VARCHAR(20) NOT NULL,
    user_id VARCHAR(64),
    session_id VARCHAR(64),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Content metadata
    genre VARCHAR(50),
    subgenre VARCHAR(50),
    language VARCHAR(10),
    age_rating VARCHAR(10), -- G, PG, PG-13, R, etc.
    content_duration INTEGER, -- total content duration
    release_year INTEGER,
    
    -- User demographics
    user_age_group VARCHAR(20), -- 13-17, 18-24, 25-34, 35-44, 45-54, 55-64, 65+
    user_gender VARCHAR(10),
    user_subscription_type VARCHAR(20),
    user_registration_date DATE,
    
    -- Viewing metrics
    view_started BOOLEAN DEFAULT FALSE,
    view_completed BOOLEAN DEFAULT FALSE,
    watch_duration INTEGER DEFAULT 0, -- in seconds
    total_duration INTEGER DEFAULT 0, -- content total duration
    completion_percentage NUMERIC(5,2) DEFAULT 0,
    seek_events INTEGER DEFAULT 0,
    pause_events INTEGER DEFAULT 0,
    replay_count INTEGER DEFAULT 0,
    
    -- Engagement metrics
    likes INTEGER DEFAULT 0,
    dislikes INTEGER DEFAULT 0,
    comments INTEGER DEFAULT 0,
    shares INTEGER DEFAULT 0,
    bookmarks INTEGER DEFAULT 0,
    rating_given NUMERIC(3,1), -- 1.0 to 5.0 rating
    
    -- Technical metrics
    quality_changes INTEGER DEFAULT 0,
    buffer_events INTEGER DEFAULT 0,
    error_events INTEGER DEFAULT 0,
    bandwidth_mbps NUMERIC(8,2),
    
    -- Revenue metrics
    revenue NUMERIC(10,2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Device and location
    device_type VARCHAR(20),
    browser VARCHAR(50),
    platform VARCHAR(20),
    screen_size VARCHAR(20), -- mobile, tablet, desktop, tv
    country VARCHAR(2),
    region VARCHAR(50),
    city VARCHAR(100),
    timezone VARCHAR(50),
    
    -- Temporal context
    time_of_day VARCHAR(20), -- morning, afternoon, evening, night
    day_of_week VARCHAR(10),
    is_weekend BOOLEAN DEFAULT FALSE,
    
    PRIMARY KEY (timestamp, content_id, user_id)
);

-- Convert content_analytics to hypertable
SELECT create_hypertable('content_analytics', 'timestamp',
    chunk_time_interval => INTERVAL '6 hours',
    if_not_exists => TRUE
);

-- Create indexes for content analytics
CREATE INDEX IF NOT EXISTS idx_content_analytics_content_id ON content_analytics (content_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_content_type ON content_analytics (content_type, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_user_id ON content_analytics (user_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_country ON content_analytics (country, timestamp DESC);

-- Age-based analytics indexes
CREATE INDEX IF NOT EXISTS idx_content_analytics_user_age_group ON content_analytics (user_age_group, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_genre ON content_analytics (genre, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_age_rating ON content_analytics (age_rating, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_release_year ON content_analytics (release_year, timestamp DESC);

-- Content preference indexes
CREATE INDEX IF NOT EXISTS idx_content_analytics_genre_age_group ON content_analytics (genre, user_age_group, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_subgenre_age_group ON content_analytics (subgenre, user_age_group, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_language_country ON content_analytics (language, country, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_time_of_day ON content_analytics (time_of_day, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_content_analytics_device_age_group ON content_analytics (device_type, user_age_group, timestamp DESC);

-- Create user_sessions table for session tracking
CREATE TABLE IF NOT EXISTS user_sessions (
    id BIGSERIAL,
    session_id VARCHAR(64) UNIQUE NOT NULL,
    user_id VARCHAR(64),
    start_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    end_time TIMESTAMPTZ,
    duration INTEGER, -- in seconds
    page_views INTEGER DEFAULT 0,
    content_views INTEGER DEFAULT 0,
    unique_content INTEGER DEFAULT 0,
    device_info JSONB,
    location JSONB,
    referrer VARCHAR(500),
    user_agent TEXT,
    
    PRIMARY KEY (start_time, session_id)
);

-- Convert user_sessions to hypertable
SELECT create_hypertable('user_sessions', 'start_time',
    chunk_time_interval => INTERVAL '12 hours',
    if_not_exists => TRUE
);

-- Create revenue_events table for financial tracking
CREATE TABLE IF NOT EXISTS revenue_events (
    id BIGSERIAL,
    transaction_id VARCHAR(64) UNIQUE NOT NULL,
    user_id VARCHAR(64),
    amount NUMERIC(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    revenue_type VARCHAR(50) NOT NULL, -- subscription, ppv, ad, etc.
    content_id VARCHAR(64),
    partner_id VARCHAR(64),
    commission NUMERIC(10,2) DEFAULT 0,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB,
    
    PRIMARY KEY (timestamp, transaction_id)
);

-- Convert revenue_events to hypertable
SELECT create_hypertable('revenue_events', 'timestamp',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

-- Create system_metrics table for monitoring
CREATE TABLE IF NOT EXISTS system_metrics (
    id BIGSERIAL,
    metric_name VARCHAR(100) NOT NULL,
    metric_value NUMERIC NOT NULL,
    metric_unit VARCHAR(20),
    service_name VARCHAR(50),
    instance_id VARCHAR(50),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    tags JSONB,
    
    PRIMARY KEY (timestamp, metric_name, service_name)
);

-- Convert system_metrics to hypertable
SELECT create_hypertable('system_metrics', 'timestamp',
    chunk_time_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

-- Create continuous aggregates for common queries
-- Hourly content metrics
CREATE MATERIALIZED VIEW IF NOT EXISTS content_metrics_hourly
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 hour', timestamp) AS time_bucket,
    content_id,
    content_type,
    COUNT(*) AS total_views,
    COUNT(DISTINCT user_id) AS unique_viewers,
    AVG(watch_duration) AS avg_watch_duration,
    AVG(completion_percentage) AS avg_completion_rate,
    SUM(likes) AS total_likes,
    SUM(comments) AS total_comments,
    SUM(shares) AS total_shares,
    SUM(revenue) AS total_revenue
FROM content_analytics
GROUP BY time_bucket, content_id, content_type
WITH NO DATA;

-- Daily user metrics
CREATE MATERIALIZED VIEW IF NOT EXISTS user_metrics_daily
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', timestamp) AS time_bucket,
    COUNT(DISTINCT user_id) AS active_users,
    COUNT(*) AS total_events,
    COUNT(DISTINCT session_id) AS total_sessions,
    AVG(EXTRACT(EPOCH FROM (end_time - start_time))) AS avg_session_duration
FROM analytics_events ae
LEFT JOIN user_sessions us ON ae.session_id = us.session_id
GROUP BY time_bucket
WITH NO DATA;

-- Revenue aggregates
CREATE MATERIALIZED VIEW IF NOT EXISTS revenue_daily
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', timestamp) AS time_bucket,
    revenue_type,
    currency,
    SUM(amount) AS total_revenue,
    COUNT(*) AS transaction_count,
    AVG(amount) AS avg_transaction_value
FROM revenue_events
GROUP BY time_bucket, revenue_type, currency
WITH NO DATA;

-- Add refresh policies for continuous aggregates
SELECT add_continuous_aggregate_policy('content_metrics_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

SELECT add_continuous_aggregate_policy('user_metrics_daily',
    start_offset => INTERVAL '2 days',
    end_offset => INTERVAL '1 day', 
    schedule_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

SELECT add_continuous_aggregate_policy('revenue_daily',
    start_offset => INTERVAL '2 days',
    end_offset => INTERVAL '1 day',
    schedule_interval => INTERVAL '1 hour', 
    if_not_exists => TRUE
);

-- Create data retention policies (keep raw data for 90 days)
SELECT add_retention_policy('analytics_events', 
    INTERVAL '90 days',
    if_not_exists => TRUE
);

SELECT add_retention_policy('content_analytics',
    INTERVAL '90 days', 
    if_not_exists => TRUE
);

SELECT add_retention_policy('user_sessions',
    INTERVAL '180 days',
    if_not_exists => TRUE
);

-- Keep revenue data longer (1 year)
SELECT add_retention_policy('revenue_events',
    INTERVAL '365 days',
    if_not_exists => TRUE
);

-- Keep system metrics for 30 days
SELECT add_retention_policy('system_metrics',
    INTERVAL '30 days',
    if_not_exists => TRUE
);

-- Create functions for common analytics queries
CREATE OR REPLACE FUNCTION get_content_performance(
    p_content_id VARCHAR(64),
    p_start_time TIMESTAMPTZ DEFAULT NOW() - INTERVAL '7 days',
    p_end_time TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    total_views BIGINT,
    unique_viewers BIGINT,
    avg_watch_time NUMERIC,
    completion_rate NUMERIC,
    total_revenue NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*)::BIGINT,
        COUNT(DISTINCT user_id)::BIGINT,
        AVG(watch_duration),
        AVG(completion_percentage),
        SUM(revenue)
    FROM content_analytics
    WHERE content_id = p_content_id
    AND timestamp BETWEEN p_start_time AND p_end_time;
END;
$$ LANGUAGE plpgsql;

-- Create function for user engagement metrics
CREATE OR REPLACE FUNCTION get_user_engagement(
    p_start_time TIMESTAMPTZ DEFAULT NOW() - INTERVAL '7 days',
    p_end_time TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    total_users BIGINT,
    active_users BIGINT,
    avg_session_duration NUMERIC,
    total_content_views BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(DISTINCT user_id)::BIGINT,
        COUNT(DISTINCT CASE WHEN timestamp >= p_start_time THEN user_id END)::BIGINT,
        AVG(EXTRACT(EPOCH FROM (us.end_time - us.start_time))),
        COUNT(CASE WHEN event_type LIKE '%_view' THEN 1 END)::BIGINT
    FROM analytics_events ae
    LEFT JOIN user_sessions us ON ae.session_id = us.session_id
    WHERE timestamp BETWEEN p_start_time AND p_end_time;
END;
$$ LANGUAGE plpgsql;

-- Create function for age-based content preferences
CREATE OR REPLACE FUNCTION get_age_based_content_preferences(
    p_age_group VARCHAR(20) DEFAULT NULL,
    p_start_time TIMESTAMPTZ DEFAULT NOW() - INTERVAL '30 days',
    p_end_time TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    age_group VARCHAR(20),
    genre VARCHAR(50),
    total_views BIGINT,
    avg_watch_duration NUMERIC,
    avg_completion_rate NUMERIC,
    preference_score NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ca.user_age_group,
        ca.genre,
        COUNT(*)::BIGINT as total_views,
        AVG(ca.watch_duration) as avg_watch_duration,
        AVG(ca.completion_percentage) as avg_completion_rate,
        -- Preference score based on views, completion rate, and engagement
        (COUNT(*) * 0.4 + AVG(ca.completion_percentage) * 0.4 + AVG(ca.likes + ca.shares + ca.comments) * 0.2)::NUMERIC as preference_score
    FROM content_analytics ca
    WHERE ca.timestamp BETWEEN p_start_time AND p_end_time
    AND (p_age_group IS NULL OR ca.user_age_group = p_age_group)
    AND ca.genre IS NOT NULL
    GROUP BY ca.user_age_group, ca.genre
    ORDER BY ca.user_age_group, preference_score DESC;
END;
$$ LANGUAGE plpgsql;

-- Create function for content performance by age rating
CREATE OR REPLACE FUNCTION get_content_performance_by_age_rating(
    p_start_time TIMESTAMPTZ DEFAULT NOW() - INTERVAL '30 days',
    p_end_time TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    age_rating VARCHAR(10),
    user_age_group VARCHAR(20),
    total_views BIGINT,
    unique_viewers BIGINT,
    avg_completion_rate NUMERIC,
    total_revenue NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ca.age_rating,
        ca.user_age_group,
        COUNT(*)::BIGINT,
        COUNT(DISTINCT ca.user_id)::BIGINT,
        AVG(ca.completion_percentage),
        SUM(ca.revenue)
    FROM content_analytics ca
    WHERE ca.timestamp BETWEEN p_start_time AND p_end_time
    AND ca.age_rating IS NOT NULL
    AND ca.user_age_group IS NOT NULL
    GROUP BY ca.age_rating, ca.user_age_group
    ORDER BY ca.age_rating, ca.user_age_group;
END;
$$ LANGUAGE plpgsql;

-- Create function for viewing pattern analysis by age
CREATE OR REPLACE FUNCTION get_viewing_patterns_by_age(
    p_start_time TIMESTAMPTZ DEFAULT NOW() - INTERVAL '30 days',
    p_end_time TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    age_group VARCHAR(20),
    time_of_day VARCHAR(20),
    device_type VARCHAR(20),
    total_views BIGINT,
    avg_session_duration NUMERIC,
    preferred_content_type VARCHAR(20)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ca.user_age_group,
        ca.time_of_day,
        ca.device_type,
        COUNT(*)::BIGINT,
        AVG(ca.watch_duration),
        mode() WITHIN GROUP (ORDER BY ca.content_type) as preferred_content_type
    FROM content_analytics ca
    WHERE ca.timestamp BETWEEN p_start_time AND p_end_time
    AND ca.user_age_group IS NOT NULL
    GROUP BY ca.user_age_group, ca.time_of_day, ca.device_type
    ORDER BY ca.user_age_group, total_views DESC;
END;
$$ LANGUAGE plpgsql;

-- Grant necessary permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO analytics_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO analytics_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO analytics_user;

-- Create initial admin user for Grafana
CREATE DATABASE grafana;
GRANT ALL PRIVILEGES ON DATABASE grafana TO analytics_user;

COMMENT ON DATABASE flicknexs_analytics IS 'FlickNexs Enterprise Analytics Database with TimescaleDB';
COMMENT ON TABLE analytics_events IS 'Raw analytics events from all sources';
COMMENT ON TABLE content_analytics IS 'Aggregated content performance metrics';
COMMENT ON TABLE user_sessions IS 'User session tracking data';
COMMENT ON TABLE revenue_events IS 'Financial transaction tracking';
COMMENT ON TABLE system_metrics IS 'System monitoring and health metrics';
