/**
 * Social Engine — Database CRUD Operations
 */
import pool from '../../db';
import type { PersonaConfig, TrendSignal, SocialContentPiece, EngineRunRecord } from './types';

// ── Persona Queries ──

export async function getActivePersonas(): Promise<PersonaConfig[]> {
  const result = await pool.query(
    `SELECT id, slug, display_name, voice_style, emoji_prefix, target_audience, content_types, weight, is_active
     FROM rm_social_personas WHERE is_active = true ORDER BY weight DESC`
  );
  return result.rows;
}

export async function getPersonaDailyPostCount(personaId: number): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_social_content
     WHERE persona_id = $1 AND status IN ('approved', 'posted')
     AND created_at >= (NOW() AT TIME ZONE 'America/New_York')::date::timestamptz`,
    [personaId]
  );
  return parseInt(result.rows[0]?.cnt || '0');
}

// ── Trend Queries ──

export async function insertTrend(trend: Omit<TrendSignal, 'id'>): Promise<string> {
  const result = await pool.query(
    `INSERT INTO rm_social_trends (trend_type, signal_source, sport, league, title, data, heat_score, expires_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     RETURNING id`,
    [trend.trend_type, trend.signal_source, trend.sport, trend.league, trend.title, JSON.stringify(trend.data), trend.heat_score, trend.expires_at]
  );
  return result.rows[0].id;
}

export async function getUnprocessedTrends(limit: number = 20): Promise<TrendSignal[]> {
  const result = await pool.query(
    `SELECT id, trend_type, signal_source, sport, league, title, data, heat_score, expires_at
     FROM rm_social_trends
     WHERE NOT content_generated AND expires_at > NOW() AND heat_score >= 20
     ORDER BY heat_score DESC LIMIT $1`,
    [limit]
  );
  return result.rows;
}

export async function markTrendProcessed(trendId: string): Promise<void> {
  await pool.query(
    `UPDATE rm_social_trends SET content_generated = true WHERE id = $1`,
    [trendId]
  );
}

export async function isDuplicateTrend(title: string, hoursBack: number = 12): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1 FROM rm_social_trends
     WHERE title = $1 AND created_at > NOW() - INTERVAL '1 hour' * $2
     LIMIT 1`,
    [title, hoursBack]
  );
  return result.rowCount! > 0;
}

// ── Content Queries ──

export async function insertContent(piece: SocialContentPiece): Promise<string> {
  const result = await pool.query(
    `INSERT INTO rm_social_content
     (persona_id, trend_id, content_type, format, text, image_prompt, image_url, league, sport, game_key, forecast_id, source_data, quality_score, compliance_ok, status, parent_content_id, thread_position)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
     RETURNING id`,
    [
      piece.persona_id, piece.trend_id, piece.content_type, piece.format,
      piece.text, piece.image_prompt || null, piece.image_url || null,
      piece.league || null, piece.sport || null, piece.game_key || null,
      piece.forecast_id || null, JSON.stringify(piece.source_data || {}),
      piece.quality_score || null, piece.compliance_ok || false,
      piece.status || 'draft', piece.parent_content_id || null,
      piece.thread_position || null,
    ]
  );
  return result.rows[0].id;
}

export async function expireStaleApprovedContent(maxAgeHours: number): Promise<number> {
  const result = await pool.query(
    `UPDATE rm_social_content
     SET status = 'failed',
         source_data = COALESCE(source_data, '{}'::jsonb) || jsonb_build_object(
           'distribution_skip_reason', 'stale_content',
           'distribution_stale_after_hours', $1,
           'distribution_stale_marked_at', NOW()
         )
     WHERE status = 'approved'
       AND created_at < NOW() - INTERVAL '1 hour' * $1`,
    [maxAgeHours]
  );
  return result.rowCount || 0;
}

export async function getSocialPostCountLastHours(hours: number = 1): Promise<number> {
  const result = await pool.query(
    `SELECT COUNT(*) as cnt
     FROM rm_social_content
     WHERE status = 'posted'
       AND posted_at >= NOW() - INTERVAL '1 hour' * $1`,
    [hours]
  );
  return parseInt(result.rows[0]?.cnt || '0');
}

export async function getApprovedContent(limit: number = 4, maxAgeHours: number = 12): Promise<any[]> {
  const result = await pool.query(
    `SELECT c.*, p.slug as persona_slug, p.display_name as persona_name, p.emoji_prefix,
            t.heat_score as trend_heat_score,
            COALESCE((c.source_data->>'engagement_score')::numeric, (t.data->>'engagement_score')::numeric, 0) as trend_engagement_score,
            COALESCE((c.source_data->>'is_breaking')::boolean, (t.data->>'is_breaking')::boolean, false) as trend_is_breaking,
            COALESCE((c.source_data->>'is_featured')::boolean, (t.data->>'is_featured')::boolean, false) as trend_is_featured
     FROM rm_social_content c
     JOIN rm_social_personas p ON c.persona_id = p.id
     LEFT JOIN rm_social_trends t ON c.trend_id = t.id
     WHERE c.status = 'approved'
       AND c.format != 'thread_reply'
       AND c.created_at >= NOW() - INTERVAL '1 hour' * $2
       AND COALESCE((c.source_data->>'distribution_hold_until')::timestamptz <= NOW(), true)
     ORDER BY
       COALESCE((c.source_data->>'is_breaking')::boolean, (t.data->>'is_breaking')::boolean, false) DESC,
       COALESCE(t.heat_score, 0) DESC,
       COALESCE((c.source_data->>'engagement_score')::numeric, (t.data->>'engagement_score')::numeric, 0) DESC,
       COALESCE((c.source_data->>'is_featured')::boolean, (t.data->>'is_featured')::boolean, false) DESC,
       c.quality_score DESC NULLS LAST,
       c.created_at DESC
     LIMIT $1`,
    [limit, maxAgeHours]
  );
  return result.rows;
}

export async function updateContentStatus(contentId: string, status: string, extras?: Record<string, any>): Promise<void> {
  const setClauses = ['status = $2'];
  const params: any[] = [contentId, status];
  let idx = 3;

  if (extras?.tweet_id) {
    setClauses.push(`tweet_id = $${idx}`);
    params.push(extras.tweet_id);
    idx++;
  }
  if (extras?.posted_at) {
    setClauses.push(`posted_at = $${idx}`);
    params.push(extras.posted_at);
    idx++;
  }
  if (extras?.image_url) {
    setClauses.push(`image_url = $${idx}`);
    params.push(extras.image_url);
    idx++;
  }
  if (extras?.compliance_ok !== undefined) {
    setClauses.push(`compliance_ok = $${idx}`);
    params.push(extras.compliance_ok);
    idx++;
  }
  if (extras?.source_data !== undefined) {
    setClauses.push(`source_data = $${idx}`);
    params.push(JSON.stringify(extras.source_data || {}));
    idx++;
  }

  await pool.query(
    `UPDATE rm_social_content SET ${setClauses.join(', ')} WHERE id = $1`,
    params
  );
}

interface PostedContentOptions {
  includeReplies?: boolean;
  includePreview?: boolean;
}

export async function getPostedContent(
  limit: number = 20,
  offset: number = 0,
  persona?: string,
  sport?: string,
  options: PostedContentOptions = {}
): Promise<any[]> {
  const includeReplies = options.includeReplies || false;
  const includePreview = options.includePreview || false;
  let where = includePreview ? `c.status IN ('posted', 'preview', 'approved')` : `c.status = 'posted'`;
  const params: any[] = [];
  let idx = 1;

  if (!includeReplies) {
    where += ` AND c.format != 'thread_reply'`;
  }
  if (!includePreview) {
    where += ` AND (c.tweet_id IS NOT NULL AND c.tweet_id NOT LIKE 'dry_%')`;
  }
  if (persona) {
    where += ` AND p.slug = $${idx}`;
    params.push(persona);
    idx++;
  }
  if (sport) {
    where += ` AND c.sport = $${idx}`;
    params.push(sport);
    idx++;
  }

  params.push(limit);
  params.push(offset);

  const result = await pool.query(
    `SELECT c.id, c.content_type, c.format, c.text, c.image_url, c.league, c.sport,
            c.tweet_id, COALESCE(c.posted_at, c.created_at) as posted_at, c.thread_position, c.parent_content_id, c.created_at,
            p.slug as persona_slug, p.display_name as persona_name, p.emoji_prefix,
            perf.impressions, perf.likes, perf.retweets, perf.replies, perf.engagement_rate
     FROM rm_social_content c
     JOIN rm_social_personas p ON c.persona_id = p.id
     LEFT JOIN rm_social_performance perf ON perf.content_id = c.id
     WHERE ${where}
     ORDER BY COALESCE(c.posted_at, c.created_at) DESC
     LIMIT $${idx} OFFSET $${idx + 1}`,
    params
  );
  return result.rows;
}

export async function getPostedContentCount(
  persona?: string,
  sport?: string,
  options: PostedContentOptions = {}
): Promise<number> {
  const includeReplies = options.includeReplies || false;
  const includePreview = options.includePreview || false;
  let where = includePreview ? `c.status IN ('posted', 'preview', 'approved')` : `c.status = 'posted'`;
  const params: any[] = [];
  let idx = 1;

  if (!includeReplies) {
    where += ` AND c.format != 'thread_reply'`;
  }
  if (!includePreview) {
    where += ` AND (c.tweet_id IS NOT NULL AND c.tweet_id NOT LIKE 'dry_%')`;
  }
  if (persona) {
    where += ` AND p.slug = $${idx}`;
    params.push(persona);
    idx++;
  }
  if (sport) {
    where += ` AND c.sport = $${idx}`;
    params.push(sport);
    idx++;
  }

  const result = await pool.query(
    `SELECT COUNT(*) as cnt FROM rm_social_content c
     JOIN rm_social_personas p ON c.persona_id = p.id
     WHERE ${where}`,
    params
  );
  return parseInt(result.rows[0]?.cnt || '0');
}

export async function isDuplicateContent(text: string): Promise<boolean> {
  const prefix = text.substring(0, 100);
  const result = await pool.query(
    `SELECT 1 FROM rm_social_content
     WHERE LEFT(text, 100) = $1 AND created_at > NOW() - INTERVAL '12 hours'
     LIMIT 1`,
    [prefix]
  );
  return result.rowCount! > 0;
}

export async function getContentThreadParts(parentId: string): Promise<any[]> {
  const result = await pool.query(
    `SELECT * FROM rm_social_content
     WHERE parent_content_id = $1 ORDER BY thread_position ASC`,
    [parentId]
  );
  return result.rows;
}

export async function getRecentPersonaMemory(personaId: number, limit: number = 6): Promise<string[]> {
  const result = await pool.query(
    `SELECT text
     FROM rm_social_content
     WHERE persona_id = $1
       AND created_at > NOW() - INTERVAL '72 hours'
     ORDER BY created_at DESC
     LIMIT $2`,
    [personaId, limit]
  );

  return result.rows
    .map((row: any) => String(row.text || '').replace(/\s+/g, ' ').trim())
    .filter(Boolean);
}

export async function getTractionExpansionCandidates(limit: number = 1): Promise<any[]> {
  const result = await pool.query(
    `SELECT c.id, c.trend_id, c.persona_id, c.content_type, c.text, c.tweet_id, c.league, c.sport, c.game_key,
            c.source_data, c.posted_at,
            p.slug as persona_slug, p.display_name as persona_name, p.voice_style, p.emoji_prefix,
            p.target_audience, p.content_types, p.weight, p.is_active,
            perf.impressions, perf.likes, perf.retweets, perf.replies, perf.quotes, perf.engagement_rate
     FROM rm_social_content c
     JOIN rm_social_personas p ON p.id = c.persona_id
     JOIN rm_social_performance perf ON perf.content_id = c.id
     WHERE c.status = 'posted'
       AND c.format = 'tweet'
       AND c.tweet_id IS NOT NULL
       AND c.tweet_id NOT LIKE 'dry_%'
       AND c.posted_at > NOW() - INTERVAL '24 hours'
       AND NOT EXISTS (
         SELECT 1 FROM rm_social_content child
         WHERE child.parent_content_id = c.id
           AND child.status IN ('draft', 'approved', 'posted', 'preview')
       )
       AND (
         COALESCE(perf.impressions, 0) >= 10 OR
         COALESCE(perf.likes, 0) >= 2 OR
         COALESCE(perf.replies, 0) >= 1
       )
     ORDER BY COALESCE(perf.replies, 0) DESC,
              COALESCE(perf.likes, 0) DESC,
              COALESCE(perf.impressions, 0) DESC,
              c.posted_at DESC
     LIMIT $1`,
    [limit]
  );

  return result.rows;
}

// ── Performance Queries ──

export async function upsertPerformance(contentId: string, tweetId: string, personaId: number, metrics: Record<string, number>): Promise<void> {
  await pool.query(
    `INSERT INTO rm_social_performance (content_id, tweet_id, persona_id, impressions, likes, retweets, replies, quotes, engagement_rate, sampled_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
     ON CONFLICT (content_id) DO UPDATE SET
       impressions = $4, likes = $5, retweets = $6, replies = $7, quotes = $8, engagement_rate = $9, sampled_at = NOW()`,
    [contentId, tweetId, personaId, metrics.impressions || 0, metrics.likes || 0, metrics.retweets || 0, metrics.replies || 0, metrics.quotes || 0, metrics.engagement_rate || 0]
  );
}

export async function getRecentPostedForTracking(hours: number = 48): Promise<any[]> {
  const result = await pool.query(
    `SELECT c.id, c.tweet_id, c.persona_id
     FROM rm_social_content c
     WHERE c.status = 'posted' AND c.tweet_id IS NOT NULL
     AND c.tweet_id NOT LIKE 'dry_%'
     AND c.posted_at > NOW() - INTERVAL '1 hour' * $1
     ORDER BY c.posted_at DESC`,
    [hours]
  );
  return result.rows;
}

// ── Persona Stats Queries ──

export async function rollupDailyPersonaStats(): Promise<void> {
  await pool.query(
    `WITH grouped AS (
       SELECT
         c.persona_id,
         (c.posted_at AT TIME ZONE 'America/New_York')::date as stat_date,
         COUNT(*) as tweets_posted,
         COALESCE(SUM(perf.impressions), 0) as total_impressions,
         COALESCE(SUM(perf.likes), 0) as total_likes,
         COALESCE(SUM(perf.retweets), 0) as total_retweets,
         COALESCE(SUM(perf.replies), 0) as total_replies,
         COALESCE(AVG(perf.engagement_rate), 0) as avg_engagement_rate
       FROM rm_social_content c
       LEFT JOIN rm_social_performance perf ON perf.content_id = c.id
       WHERE c.status = 'posted' AND c.posted_at IS NOT NULL
         AND c.posted_at > NOW() - INTERVAL '2 days'
       GROUP BY c.persona_id, (c.posted_at AT TIME ZONE 'America/New_York')::date
     )
     INSERT INTO rm_social_persona_stats (persona_id, stat_date, tweets_posted, total_impressions, total_likes, total_retweets, total_replies, avg_engagement_rate, top_tweet_id)
     SELECT
       g.persona_id,
       g.stat_date,
       g.tweets_posted,
       g.total_impressions,
       g.total_likes,
       g.total_retweets,
       g.total_replies,
       g.avg_engagement_rate,
       top_perf.tweet_id as top_tweet_id
     FROM grouped g
     LEFT JOIN LATERAL (
       SELECT perf2.tweet_id
       FROM rm_social_performance perf2
       JOIN rm_social_content c2 ON perf2.content_id = c2.id
       WHERE c2.persona_id = g.persona_id
         AND (c2.posted_at AT TIME ZONE 'America/New_York')::date = g.stat_date
       ORDER BY perf2.likes DESC NULLS LAST, c2.posted_at DESC
       LIMIT 1
     ) top_perf ON TRUE
     ON CONFLICT (persona_id, stat_date) DO UPDATE SET
       tweets_posted = EXCLUDED.tweets_posted,
       total_impressions = EXCLUDED.total_impressions,
       total_likes = EXCLUDED.total_likes,
       total_retweets = EXCLUDED.total_retweets,
       total_replies = EXCLUDED.total_replies,
       avg_engagement_rate = EXCLUDED.avg_engagement_rate,
       top_tweet_id = EXCLUDED.top_tweet_id`
  );
}

export async function getPersonaStatsAgg(days: number = 7): Promise<any[]> {
  const result = await pool.query(
    `SELECT
       p.slug, p.display_name, p.emoji_prefix,
       COALESCE(SUM(s.tweets_posted), 0) as total_tweets,
       COALESCE(SUM(s.total_impressions), 0) as total_impressions,
       COALESCE(SUM(s.total_likes), 0) as total_likes,
       COALESCE(SUM(s.total_retweets), 0) as total_retweets,
       COALESCE(SUM(s.total_replies), 0) as total_replies,
       COALESCE(AVG(s.avg_engagement_rate), 0) as avg_engagement_rate
     FROM rm_social_personas p
     LEFT JOIN rm_social_persona_stats s ON s.persona_id = p.id AND s.stat_date > CURRENT_DATE - ($1::int)
     WHERE p.is_active = true
     GROUP BY p.id, p.slug, p.display_name, p.emoji_prefix
     ORDER BY total_likes DESC`,
    [days]
  );
  return result.rows;
}

// ── Engine Run Queries ──

export async function createEngineRun(runType: string): Promise<number> {
  const result = await pool.query(
    `INSERT INTO rm_social_engine_runs (run_type) VALUES ($1) RETURNING id`,
    [runType]
  );
  return result.rows[0].id;
}

export async function updateEngineRun(
  runId: number,
  status: string,
  counters: { trends_found: number; content_created: number; content_posted: number },
  durationMs: number,
  errorMessage?: string
): Promise<void> {
  await pool.query(
    `UPDATE rm_social_engine_runs
     SET status = $2, trends_found = $3, content_created = $4, content_posted = $5,
         duration_ms = $6, error_message = $7, finished_at = NOW()
     WHERE id = $1`,
    [runId, status, counters.trends_found, counters.content_created, counters.content_posted, durationMs, errorMessage || null]
  );
}

// ── Trending API ──

export async function getTopTrends(limit: number = 10): Promise<any[]> {
  const result = await pool.query(
    `SELECT id, trend_type, signal_source, sport, league, title, heat_score, content_generated, created_at
     FROM rm_social_trends
     WHERE expires_at > NOW()
     ORDER BY heat_score DESC LIMIT $1`,
    [limit]
  );
  return result.rows;
}