-- Add support for DRM and adaptive streaming
-- Version 1.0

-- Add columns for multiple audio quality URLs
ALTER TABLE tracks
ADD COLUMN IF NOT EXISTS audio_url_low TEXT,
ADD COLUMN IF NOT EXISTS audio_url_normal TEXT,
ADD COLUMN IF NOT EXISTS audio_url_high TEXT,
ADD COLUMN IF NOT EXISTS audio_url_very_high TEXT,
ADD COLUMN IF NOT EXISTS drm_protected BOOLEAN DEFAULT TRUE,
ADD COLUMN IF NOT EXISTS stream_url_expires_hours INTEGER DEFAULT 24;

-- Update existing tracks to use audio_url as high quality
UPDATE tracks
SET audio_url_high = audio_url
WHERE audio_url_high IS NULL AND audio_url IS NOT NULL;

-- Create table for signed URL tokens
CREATE TABLE IF NOT EXISTS stream_tokens (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    track_id UUID NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    token VARCHAR(255) UNIQUE NOT NULL,
    quality VARCHAR(50) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    device_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_stream_tokens_token ON stream_tokens(token);
CREATE INDEX IF NOT EXISTS idx_stream_tokens_expires ON stream_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_stream_tokens_user_track ON stream_tokens(user_id, track_id);

-- Create table for network quality detection logs
CREATE TABLE IF NOT EXISTS network_quality_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    connection_type VARCHAR(50), -- 4g, 3g, wifi, etc
    effective_bandwidth_mbps DECIMAL(10, 2),
    quality_selected VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add index
CREATE INDEX IF NOT EXISTS idx_network_quality_user ON network_quality_logs(user_id, created_at DESC);

COMMENT ON TABLE stream_tokens IS 'Stores temporary tokens for DRM-protected audio streaming';
COMMENT ON TABLE network_quality_logs IS 'Logs network quality for adaptive streaming analytics';