-- Migration: Add created_at column to listening_history table
-- This column is required by analytics and tracking queries

-- Add created_at column if it doesn't exist
ALTER TABLE listening_history
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Backfill created_at from played_at for existing records
UPDATE listening_history
SET created_at = played_at
WHERE created_at IS NULL;

-- Make created_at NOT NULL after backfilling
ALTER TABLE listening_history
ALTER COLUMN created_at SET NOT NULL;

-- Add index on created_at for analytics queries
CREATE INDEX IF NOT EXISTS idx_listening_history_created_at 
ON listening_history(created_at DESC);