-- Enhanced Ringtone API System for Telecom Operators
-- Adds operator-specific pricing, comprehensive logging, and statistics

-- Add operator-specific pricing table
CREATE TABLE IF NOT EXISTS operator_pricing (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  api_key_id UUID REFERENCES external_api_keys(id) ON DELETE CASCADE,
  track_id UUID REFERENCES tracks(id) ON DELETE CASCADE,
  custom_price DECIMAL(10, 2),
  currency VARCHAR(10) DEFAULT 'USD',
  commission_rate DECIMAL(5, 2) DEFAULT 30.00,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(api_key_id, track_id)
);

-- Enhance API usage logs with more details
ALTER TABLE api_usage_logs
ADD COLUMN IF NOT EXISTS error_message TEXT,
ADD COLUMN IF NOT EXISTS request_size INTEGER,
ADD COLUMN IF NOT EXISTS response_size INTEGER,
ADD COLUMN IF NOT EXISTS country_code VARCHAR(5),
ADD COLUMN IF NOT EXISTS operator_name VARCHAR(255);

-- Add indexes for analytics queries
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_endpoint ON api_usage_logs(endpoint);
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_status_code ON api_usage_logs(status_code);
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_date ON api_usage_logs(DATE(created_at));
CREATE INDEX IF NOT EXISTS idx_operator_pricing_api_key ON operator_pricing(api_key_id);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_created_at ON ringtone_downloads(DATE(created_at));

-- Create materialized view for operator statistics (faster queries)
CREATE MATERIALIZED VIEW IF NOT EXISTS operator_stats AS
SELECT 
  eak.id as api_key_id,
  eak.name as api_name,
  eak.company,
  COUNT(DISTINCT rd.id) as total_downloads,
  COUNT(DISTINCT rd.track_id) as unique_tracks,
  SUM(rd.price) as total_revenue,
  COUNT(DISTINCT DATE(rd.created_at)) as active_days,
  MAX(rd.created_at) as last_download,
  AVG(rd.price) as avg_price
FROM external_api_keys eak
LEFT JOIN ringtone_downloads rd ON rd.api_key_id = eak.id
GROUP BY eak.id, eak.name, eak.company;

-- Create index on materialized view
CREATE UNIQUE INDEX IF NOT EXISTS idx_operator_stats_api_key ON operator_stats(api_key_id);

-- Function to refresh operator stats
CREATE OR REPLACE FUNCTION refresh_operator_stats()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY operator_stats;
END;
$$ LANGUAGE plpgsql;

-- Add trigger to update operator pricing timestamp
CREATE OR REPLACE FUNCTION update_operator_pricing_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_operator_pricing_timestamp
BEFORE UPDATE ON operator_pricing
FOR EACH ROW
EXECUTE FUNCTION update_operator_pricing_timestamp();

-- Sample operator-specific pricing
INSERT INTO operator_pricing (api_key_id, track_id, custom_price, currency, commission_rate)
SELECT 
  eak.id,
  t.id,
  1.49,
  'USD',
  25.00
FROM external_api_keys eak
CROSS JOIN (SELECT id FROM tracks WHERE ringtone_enabled = true LIMIT 10) t
ON CONFLICT (api_key_id, track_id) DO NOTHING;