-- Create external_api_keys table if not exists
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),
  status VARCHAR(50) DEFAULT 'active',
  permissions JSONB DEFAULT '["ringtone:read", "ringtone:download", "track:search"]'::jsonb,
  rate_limit INTEGER DEFAULT 1000,
  requests_count INTEGER DEFAULT 0,
  last_used_at TIMESTAMP WITH TIME ZONE,
  expires_at TIMESTAMP WITH TIME ZONE,
  created_by UUID,
  notes TEXT,
  -- Added operator-specific fields
  country_code VARCHAR(10),
  operator_code VARCHAR(50),
  pricing_tier VARCHAR(50) DEFAULT 'standard',
  callback_url TEXT,
  webhook_secret VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create ringtone_downloads table with enhanced tracking
CREATE TABLE IF NOT EXISTS ringtone_downloads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  track_id UUID NOT NULL,
  api_key_id UUID NOT NULL REFERENCES external_api_keys(id),
  operator_name VARCHAR(255),
  -- Enhanced customer tracking fields
  customer_phone VARCHAR(50),
  customer_msisdn VARCHAR(50),
  customer_country VARCHAR(10),
  subscriber_id VARCHAR(100),
  transaction_id VARCHAR(100) UNIQUE,
  -- Pricing info
  price DECIMAL(10, 2) DEFAULT 0,
  currency VARCHAR(10) DEFAULT 'USD',
  operator_share DECIMAL(10, 2) DEFAULT 0,
  platform_share DECIMAL(10, 2) DEFAULT 0,
  -- Status tracking
  status VARCHAR(50) DEFAULT 'pending',
  delivery_status VARCHAR(50) DEFAULT 'pending',
  delivered_at TIMESTAMP WITH TIME ZONE,
  -- Request metadata
  ip_address VARCHAR(50),
  user_agent TEXT,
  request_source VARCHAR(50),
  -- Timestamps
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 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 NOT NULL REFERENCES external_api_keys(id),
  endpoint VARCHAR(255) NOT NULL,
  method VARCHAR(10) NOT NULL,
  status_code INTEGER,
  response_time INTEGER,
  ip_address VARCHAR(50),
  user_agent TEXT,
  request_body JSONB,
  response_body JSONB,
  error_message TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_customer_phone ON ringtone_downloads(customer_phone);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_customer_msisdn ON ringtone_downloads(customer_msisdn);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_api_key_id ON ringtone_downloads(api_key_id);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_track_id ON ringtone_downloads(track_id);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_created_at ON ringtone_downloads(created_at);
CREATE INDEX IF NOT EXISTS idx_ringtone_downloads_status ON ringtone_downloads(status);
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);

-- Add ringtone_enabled and ringtone_price to tracks if not exists
ALTER TABLE tracks ADD COLUMN IF NOT EXISTS ringtone_enabled BOOLEAN DEFAULT false;
ALTER TABLE tracks ADD COLUMN IF NOT EXISTS ringtone_price DECIMAL(10, 2) DEFAULT 1.00;