-- Updated to use UUID schema instead of SERIAL to match main database schema
-- Create listening_history table for real-time analytics
CREATE TABLE IF NOT EXISTS listening_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  track_id UUID NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  -- Adding duration_ms column to match analytics queries
  duration_ms INTEGER DEFAULT 0,
  -- Adding duration_played_ms for backward compatibility with existing code
  duration_played_ms INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Create index for faster queries
CREATE INDEX IF NOT EXISTS idx_listening_history_track_id ON listening_history(track_id);
CREATE INDEX IF NOT EXISTS idx_listening_history_user_id ON listening_history(user_id);
CREATE INDEX IF NOT EXISTS idx_listening_history_created_at ON listening_history(created_at DESC);

-- Create aggregated view for performance
CREATE OR REPLACE VIEW track_analytics AS
SELECT 
  t.id,
  t.title,
  COUNT(lh.id) as total_streams,
  COUNT(DISTINCT lh.user_id) as unique_listeners,
  -- Using COALESCE to support both column names
  AVG(COALESCE(lh.duration_ms, lh.duration_played_ms, 0)) as avg_duration_ms
FROM tracks t
LEFT JOIN listening_history lh ON t.id = lh.track_id
GROUP BY t.id, t.title;