pagodera/lib/db/migrations/012_telemetry_and_ads_enhancement.sql

-- Migration 012: Telemetria e Expansão de Anúncios
-- Criado em: 2025-01-27

-- ============================================
-- 1. Tabela de Eventos de Telemetria
-- ============================================
CREATE TABLE IF NOT EXISTS telemetry_events (
    event_id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    ad_id INTEGER REFERENCES listings(listing_id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    session_id VARCHAR(255),
    metadata JSONB DEFAULT '{}'::jsonb,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_telemetry_ad_id ON telemetry_events(ad_id);
CREATE INDEX IF NOT EXISTS idx_telemetry_user_id ON telemetry_events(user_id);
CREATE INDEX IF NOT EXISTS idx_telemetry_timestamp ON telemetry_events(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_telemetry_event_type ON telemetry_events(event_type);
CREATE INDEX IF NOT EXISTS idx_telemetry_session_id ON telemetry_events(session_id);

-- Índice composto para queries de analytics
CREATE INDEX IF NOT EXISTS idx_telemetry_ad_type_time ON telemetry_events(ad_id, event_type, timestamp DESC);

-- ============================================
-- 2. Expansão da Tabela listings
-- ============================================
ALTER TABLE listings 
    ADD COLUMN IF NOT EXISTS is_featured BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS favorites_count INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS inquiries_count INTEGER DEFAULT 0,
    ADD COLUMN IF NOT EXISTS price_history JSONB DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS expires_at TIMESTAMP;

-- Índices para novos campos
CREATE INDEX IF NOT EXISTS idx_listings_is_featured ON listings(is_featured) WHERE is_featured = TRUE;
CREATE INDEX IF NOT EXISTS idx_listings_expires_at ON listings(expires_at) WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_listings_favorites_count ON listings(favorites_count DESC);

-- ============================================
-- 3. Tabela de Analytics Agregados
-- ============================================
CREATE TABLE IF NOT EXISTS ad_analytics (
    ad_id INTEGER PRIMARY KEY REFERENCES listings(listing_id) ON DELETE CASCADE,
    impressions INTEGER DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    ctr DECIMAL(5, 2) DEFAULT 0.00, -- Click-through rate em porcentagem
    avg_view_duration DECIMAL(10, 2) DEFAULT 0.00, -- em segundos
    bounce_rate DECIMAL(5, 2) DEFAULT 0.00, -- em porcentagem
    inquiries INTEGER DEFAULT 0,
    favorites INTEGER DEFAULT 0,
    conversion_rate DECIMAL(5, 2) DEFAULT 0.00, -- em porcentagem
    time_to_sell INTEGER DEFAULT NULL, -- em horas
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger para atualizar last_updated automaticamente
CREATE OR REPLACE FUNCTION update_ad_analytics_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.last_updated = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_ad_analytics_updated_at
  BEFORE UPDATE ON ad_analytics
  FOR EACH ROW
  EXECUTE FUNCTION update_ad_analytics_updated_at();

-- ============================================
-- 4. Tabela de Inventário do Usuário
-- ============================================
CREATE TABLE IF NOT EXISTS user_inventory (
    inventory_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    item_id INTEGER NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    quantity INTEGER DEFAULT 1 CHECK (quantity > 0),
    acquired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, item_id)
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_user_inventory_user_id ON user_inventory(user_id);
CREATE INDEX IF NOT EXISTS idx_user_inventory_item_id ON user_inventory(item_id);

-- ============================================
-- 5. Tabela de Favoritos
-- ============================================
CREATE TABLE IF NOT EXISTS user_favorites (
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    ad_id INTEGER NOT NULL REFERENCES listings(listing_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, ad_id)
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_user_favorites_user_id ON user_favorites(user_id);
CREATE INDEX IF NOT EXISTS idx_user_favorites_ad_id ON user_favorites(ad_id);

-- ============================================
-- 6. Trigger para Atualizar favorites_count
-- ============================================
CREATE OR REPLACE FUNCTION update_listing_favorites_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE listings 
    SET favorites_count = favorites_count + 1 
    WHERE listing_id = NEW.ad_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE listings 
    SET favorites_count = GREATEST(0, favorites_count - 1) 
    WHERE listing_id = OLD.ad_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_listing_favorites_count
  AFTER INSERT OR DELETE ON user_favorites
  FOR EACH ROW
  EXECUTE FUNCTION update_listing_favorites_count();

-- ============================================
-- 7. Função para Inicializar Analytics ao Criar Anúncio
-- ============================================
CREATE OR REPLACE FUNCTION initialize_ad_analytics()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO ad_analytics (ad_id)
  VALUES (NEW.listing_id)
  ON CONFLICT (ad_id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_initialize_ad_analytics
  AFTER INSERT ON listings
  FOR EACH ROW
  EXECUTE FUNCTION initialize_ad_analytics();

-- ============================================
-- 8. Função para Atualizar Price History
-- ============================================
CREATE OR REPLACE FUNCTION update_price_history()
RETURNS TRIGGER AS $$
DECLARE
  new_price_entry JSONB;
  current_history JSONB;
BEGIN
  -- Verifica se algum preço mudou
  IF (OLD.price_pix IS DISTINCT FROM NEW.price_pix) OR
     (OLD.price_tibia_coins IS DISTINCT FROM NEW.price_tibia_coins) OR
     (OLD.price_gold_coins IS DISTINCT FROM NEW.price_gold_coins) THEN
    
    -- Cria entrada de histórico
    new_price_entry := jsonb_build_object(
      'timestamp', CURRENT_TIMESTAMP,
      'price_pix', NEW.price_pix,
      'price_tibia_coins', NEW.price_tibia_coins,
      'price_gold_coins', NEW.price_gold_coins
    );
    
    -- Obtém histórico atual
    current_history := COALESCE(NEW.price_history, '[]'::jsonb);
    
    -- Adiciona nova entrada no início do array
    NEW.price_history := jsonb_insert(
      current_history,
      '{0}',
      new_price_entry
    );
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_price_history
  BEFORE UPDATE ON listings
  FOR EACH ROW
  EXECUTE FUNCTION update_price_history();