import { beforeEach, describe, expect, it, vi } from 'vitest';

const poolQuery = vi.fn();

vi.mock('../../../db', () => ({ default: { query: poolQuery } }));

describe('social-engine data queries', () => {
  beforeEach(() => {
    vi.clearAllMocks();
    vi.resetModules();
  });

  it('casts the stats window parameter so persona stats aggregation does not compare date > integer', async () => {
    poolQuery.mockResolvedValueOnce({ rows: [] });
    const { getPersonaStatsAgg } = await import('../data-queries');

    await getPersonaStatsAgg(7);

    expect(String(poolQuery.mock.calls[0]?.[0])).toContain('s.stat_date > CURRENT_DATE - ($1::int)');
    expect(poolQuery.mock.calls[0]?.[1]).toEqual([7]);
  });

  it('includes approved content in preview feed mode and falls back to created_at for ordering', async () => {
    poolQuery.mockResolvedValueOnce({ rows: [] });
    const { getPostedContent } = await import('../data-queries');

    await getPostedContent(20, 0, undefined, undefined, { includePreview: true });

    const sql = String(poolQuery.mock.calls[0]?.[0]);
    expect(sql).toContain(`c.status IN ('posted', 'preview', 'approved')`);
    expect(sql).toContain('COALESCE(c.posted_at, c.created_at) as posted_at');
    expect(sql).toContain('ORDER BY COALESCE(c.posted_at, c.created_at) DESC');
  });

  it('prioritizes approved content by breaking status, heat, and engagement instead of raw recency', async () => {
    poolQuery.mockResolvedValueOnce({ rows: [] });
    const { getApprovedContent } = await import('../data-queries');

    await getApprovedContent(20, 12);

    const sql = String(poolQuery.mock.calls[0]?.[0]);
    expect(sql).toContain('LEFT JOIN rm_social_trends t ON c.trend_id = t.id');
    expect(sql).toContain(`COALESCE((c.source_data->>'is_breaking')::boolean, (t.data->>'is_breaking')::boolean, false) DESC`);
    expect(sql).toContain('COALESCE(t.heat_score, 0) DESC');
    expect(sql).toContain(`COALESCE((c.source_data->>'engagement_score')::numeric, (t.data->>'engagement_score')::numeric, 0) DESC`);
  });

  it('counts social posts in the last rolling hour for hourly rate limiting', async () => {
    poolQuery.mockResolvedValueOnce({ rows: [{ cnt: '1' }] });
    const { getSocialPostCountLastHours } = await import('../data-queries');

    const count = await getSocialPostCountLastHours(1);

    expect(count).toBe(1);
    expect(String(poolQuery.mock.calls[0]?.[0])).toContain(`posted_at >= NOW() - INTERVAL '1 hour' * $1`);
    expect(String(poolQuery.mock.calls[0]?.[0])).not.toContain(`format != 'thread_reply'`);
    expect(poolQuery.mock.calls[0]?.[1]).toEqual([1]);
  });
});