Database

-- articles
CREATE INDEX idx_articles_category    ON articles(category_id);
CREATE INDEX idx_articles_published   ON articles(published);
CREATE INDEX idx_articles_slug        ON articles(slug);
CREATE INDEX idx_articles_created_at  ON articles(created_at DESC);
CREATE INDEX idx_articles_featured    ON articles(featured) WHERE featured = true;
CREATE INDEX idx_articles_deleted_at  ON articles(deleted_at);

-- categories
CREATE INDEX idx_categories_slug ON categories(slug);

-- tags
CREATE INDEX idx_tags_slug            ON tags(slug);
CREATE INDEX idx_article_tags_article ON article_tags(article_id);
CREATE INDEX idx_article_tags_tag     ON article_tags(tag_id);

-- comments
CREATE INDEX idx_comments_article_id ON comments(article_id);

-- subscribers
CREATE INDEX subscribers_email_idx  ON subscribers(email);
CREATE INDEX subscribers_active_idx ON subscribers(active);

-- page_views
CREATE INDEX idx_page_views_article_id ON page_views(article_id);
CREATE INDEX idx_page_views_viewed_at  ON page_views(viewed_at);
CREATE INDEX idx_page_views_source     ON page_views(referrer_source);

-- ad_events
CREATE INDEX idx_ad_events_ad_id      ON ad_events(ad_id);
CREATE INDEX idx_ad_events_created_at ON ad_events(created_at);
CREATE INDEX idx_ad_events_type       ON ad_events(event_type);

CREATE OR REPLACE FUNCTION increment_views(
  article_id UUID,
  p_referrer TEXT DEFAULT NULL,
  p_referrer_source TEXT DEFAULT NULL
)
RETURNS void AS $$
BEGIN
  UPDATE articles SET views = views + 1
  WHERE id = article_id AND deleted_at IS NULL;
  INSERT INTO page_views (article_id, referrer, referrer_source)
  VALUES (article_id, p_referrer, p_referrer_source);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION popular_articles_this_week(lim INT DEFAULT 5)
RETURNS TABLE (article_id UUID, view_count BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT pv.article_id, COUNT(*) AS view_count
  FROM page_views pv
  INNER JOIN articles a ON a.id = pv.article_id
  WHERE pv.viewed_at >= now() - interval '7 days'
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY pv.article_id
  ORDER BY view_count DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION trending_articles_24h(lim INT DEFAULT 5)
RETURNS TABLE (article_id UUID, view_count BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT pv.article_id, COUNT(*) AS view_count
  FROM page_views pv
  INNER JOIN articles a ON a.id = pv.article_id
  WHERE pv.viewed_at >= now() - interval '24 hours'
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY pv.article_id
  ORDER BY view_count DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION related_articles_by_tags(
  p_article_id UUID,
  p_category_id UUID,
  lim INT DEFAULT 4
)
RETURNS TABLE (
  id UUID, title TEXT, slug TEXT, excerpt TEXT,
  image_url TEXT, created_at TIMESTAMPTZ,
  category_name TEXT, category_color TEXT, shared_tags BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    a.id, a.title, a.slug, a.excerpt, a.image_url, a.created_at,
    c.name AS category_name, c.color AS category_color,
    COUNT(at2.tag_id) AS shared_tags
  FROM article_tags at1
  INNER JOIN article_tags at2
    ON at1.tag_id = at2.tag_id AND at2.article_id != p_article_id
  INNER JOIN articles a ON a.id = at2.article_id
  LEFT JOIN categories c ON c.id = a.category_id
  WHERE at1.article_id = p_article_id
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY a.id, a.title, a.slug, a.excerpt, a.image_url, a.created_at,
           c.name, c.color
  ORDER BY shared_tags DESC, a.created_at DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION ad_metrics(days INT DEFAULT 30)
RETURNS TABLE (ad_id UUID, impressions BIGINT, clicks BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.ad_id,
    COUNT(*) FILTER (WHERE e.event_type = 'impression') AS impressions,
    COUNT(*) FILTER (WHERE e.event_type = 'click') AS clicks
  FROM ad_events e
  WHERE e.created_at >= now() - (days || ' days')::interval
  GROUP BY e.ad_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

supabase db push