-- Create Ringtone API System for Mobile Operators

-- Add ringtone-specific fields to tracks table
ALTER TABLE tracks
ADD COLUMN IF NOT EXISTS ringtone_enabled BOOLEAN DEFAULT false,
ADD COLUMN IF NOT EXISTS ringtone_price DECIMAL(10, 2) DEFAULT 0.99,
ADD COLUMN IF NOT EXISTS ringtone_duration INTEGER DEFAULT 30;

-- Create external API keys table for mobile operators
CREATE TABLE IF NOT EXISTS external_api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  company VARCHAR(255) NOT NULL,
  api_key VARCHAR(255) UNIQUE NOT NULL,
  api_secret VARCHAR(255) NOT NULL,
  status VARCHAR(50) DEFAULT 'active',
  permissions JSONB DEFAULT '["ringtone:read", "track:search"]'::jsonb,
  rate_limit INTEGER DEFAULT 1000,
  requests_count INTEGER DEFAULT 0,
  last_used_at TIMESTAMP,
  expires_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES users(id),
  notes TEXT
);

-- Create API usage logs table
CREATE TABLE IF NOT EXISTS api_usage_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  api_key_id UUID REFERENCES external_api_keys(id) ON DELETE CASCADE,
  endpoint VARCHAR(255) NOT NULL,
  method VARCHAR(10) NOT NULL,
  status_code INTEGER,
  response_time INTEGER,
  ip_address VARCHAR(45),
  user_agent TEXT,
  request_data JSONB,
  response_data JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create ringtone downloads table for tracking
CREATE TABLE IF NOT EXISTS ringtone_downloads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  track_id UUID REFERENCES tracks(id) ON DELETE CASCADE,
  api_key_id UUID REFERENCES external_api_keys(id) ON DELETE SET NULL,
  operator_name VARCHAR(255),
  customer_phone VARCHAR(50),
  price DECIMAL(10, 2),
  currency VARCHAR(10) DEFAULT 'USD',
  status VARCHAR(50) DEFAULT 'completed',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_external_api_keys_status ON external_api_keys(status);
CREATE INDEX IF NOT EXISTS idx_external_api_keys_api_key ON external_api_keys(api_key);
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_api_key_id ON api_usage_logs(api_key_id);
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_created_at ON api_usage_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_track_id ON ringtone_downloads(track_id);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_api_key_id ON ringtone_downloads(api_key_id);
CREATE INDEX IF NOT EXISTS idx_tracks_ringtone_enabled ON tracks(ringtone_enabled) WHERE ringtone_enabled = true;

-- Fixed: Use total_streams instead of non-existent play_count column
-- Enable some tracks for ringtones based on popularity
UPDATE tracks
SET ringtone_enabled = true,
    ringtone_price = 0.99,
    ringtone_duration = 30
WHERE id IN (
  SELECT id FROM tracks ORDER BY total_streams DESC LIMIT 50
);

-- Create sample external API key for testing
INSERT INTO external_api_keys (
  name,
  company,
  api_key,
  api_secret,
  status,
  permissions,
  rate_limit,
  expires_at,
  notes
) VALUES (
  'Test Operator API',
  'Demo Mobile Operator',
  'test_' || substring(md5(random()::text) from 1 for 32),
  'secret_' || substring(md5(random()::text) from 1 for 32),
  'active',
  '["ringtone:read", "ringtone:download", "track:search"]'::jsonb,
  1000,
  CURRENT_TIMESTAMP + INTERVAL '1 year',
  'Demo API key for testing ringtone integration'
);