import { Router, Request, Response } from 'express';
import pool from '../db';

const router = Router();

// GET /api/rainmaker/forecasts
router.get('/forecasts', async (_req: Request, res: Response) => {
  try {
    // Accuracy buckets
    const { rows: buckets } = await pool.query(
      `SELECT accuracy_bucket, COUNT(*)::int as count, ROUND(AVG(accuracy_pct)::numeric, 1) as avg_accuracy
       FROM rm_forecast_accuracy
       GROUP BY accuracy_bucket
       ORDER BY accuracy_bucket`
    );

    // Recent resolved
    const { rows: recent } = await pool.query(
      `SELECT a.*, c.home_team, c.away_team, c.league
       FROM rm_forecast_accuracy a
       LEFT JOIN rm_forecast_cache c ON a.forecast_id = c.id
       ORDER BY a.resolved_at DESC
       LIMIT 100`
    );

    res.json({ buckets, recent });
  } catch (err) {
    console.error('Rainmaker forecasts error:', err);
    res.status(500).json({ error: 'Failed to load forecasts' });
  }
});

// GET /api/rainmaker/weathermen
router.get('/weathermen', async (_req: Request, res: Response) => {
  try {
    const { rows: weathermen } = await pool.query(
      'SELECT id, email, is_weatherman, created_at FROM rm_users WHERE is_weatherman = TRUE ORDER BY email'
    );

    const { rows: allUsers } = await pool.query(
      'SELECT id, email, is_weatherman, created_at FROM rm_users ORDER BY email LIMIT 500'
    );

    res.json({ weathermen, allUsers });
  } catch (err) {
    console.error('Rainmaker weathermen error:', err);
    res.status(500).json({ error: 'Failed to load weathermen' });
  }
});

// POST /api/rainmaker/weathermen/toggle
router.post('/weathermen/toggle', async (req: Request, res: Response) => {
  try {
    const { userId, isWeatherman } = req.body;
    if (!userId) {
      res.status(400).json({ error: 'userId required' });
      return;
    }

    await pool.query(
      'UPDATE rm_users SET is_weatherman = $1, updated_at = NOW() WHERE id = $2',
      [!!isWeatherman, userId]
    );

    res.json({ success: true });
  } catch (err) {
    console.error('Rainmaker toggle error:', err);
    res.status(500).json({ error: 'Failed to toggle weatherman' });
  }
});

// GET /api/rainmaker/purchases
router.get('/purchases', async (_req: Request, res: Response) => {
  try {
    // Stats (last 30 days)
    const { rows: statsRows } = await pool.query(
      `SELECT
         COUNT(*)::int as total_purchases,
         COALESCE(SUM(amount_cents), 0)::int as total_revenue_cents,
         COALESCE(SUM(picks_granted), 0)::int as total_picks_sold,
         COUNT(DISTINCT user_id)::int as unique_buyers
       FROM rm_purchases
       WHERE created_at >= NOW() - interval '30 days'`
    );

    // Recent purchases
    const { rows: recent } = await pool.query(
      `SELECT p.*, u.email
       FROM rm_purchases p
       JOIN rm_users u ON p.user_id = u.id
       ORDER BY p.created_at DESC
       LIMIT 100`
    );

    res.json({ stats: statsRows[0] || {}, recent });
  } catch (err) {
    console.error('Rainmaker purchases error:', err);
    res.status(500).json({ error: 'Failed to load purchases' });
  }
});

// ── User Hub Routes ──

// GET /api/rainmaker/users — paginated, searchable, sortable user list
router.get('/users', async (req: Request, res: Response) => {
  try {
    const search = (req.query.search as string || '').trim();
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(100, Math.max(1, parseInt(req.query.limit as string) || 25));
    const offset = (page - 1) * limit;
    const order = (req.query.order as string || 'desc').toLowerCase() === 'asc' ? 'ASC' : 'DESC';

    const sortMap: Record<string, string> = {
      email: 'u.email',
      picks: 'picks_unlocked',
      surveys: 'survey_count',
      purchases: 'purchase_count',
      created_at: 'u.created_at',
    };
    const sortCol = sortMap[req.query.sort as string] || 'u.created_at';

    const whereClause = search ? `WHERE u.email ILIKE $1` : '';
    const params: any[] = search ? [`%${search}%`] : [];
    const limitIdx = params.length + 1;
    const offsetIdx = params.length + 2;

    // Count
    const { rows: countRows } = await pool.query(
      `SELECT COUNT(*)::int as total FROM rm_users u ${whereClause}`,
      params
    );

    // Users with aggregated counts
    const { rows: users } = await pool.query(
      `SELECT
         u.id, u.email, u.email_verified, u.is_weatherman,
         u.daily_free_forecasts, u.single_picks, u.daily_pass_picks,
         u.country_code, u.affiliate_code, u.marketing_consent,
         u.preferences, u.created_at, u.updated_at,
         COALESCE(p.picks_unlocked, 0)::int as picks_unlocked,
         p.last_pick_at,
         COALESCE(s.survey_count, 0)::int as survey_count,
         COALESCE(pu.purchase_count, 0)::int as purchase_count,
         COALESCE(pu.total_spent_cents, 0)::int as total_spent_cents,
         l.last_activity
       FROM rm_users u
       LEFT JOIN (
         SELECT user_id, COUNT(*)::int as picks_unlocked, MAX(created_at) as last_pick_at
         FROM rm_user_picks GROUP BY user_id
       ) p ON p.user_id = u.id
       LEFT JOIN (
         SELECT user_id, COUNT(*)::int as survey_count
         FROM rm_survey_responses GROUP BY user_id
       ) s ON s.user_id = u.id
       LEFT JOIN (
         SELECT user_id, COUNT(*)::int as purchase_count, SUM(amount_cents)::int as total_spent_cents
         FROM rm_purchases GROUP BY user_id
       ) pu ON pu.user_id = u.id
       LEFT JOIN (
         SELECT user_id, MAX(created_at) as last_activity
         FROM rm_forecast_ledger GROUP BY user_id
       ) l ON l.user_id = u.id
       ${whereClause}
       ORDER BY ${sortCol} ${order}
       LIMIT $${limitIdx} OFFSET $${offsetIdx}`,
      [...params, limit, offset]
    );

    // Summary stats
    const { rows: summaryRows } = await pool.query(
      `SELECT
         COUNT(*)::int as total_users,
         COUNT(*) FILTER (WHERE email_verified = TRUE)::int as verified_users,
         COUNT(*) FILTER (WHERE is_weatherman = TRUE)::int as weathermen,
         (SELECT COALESCE(COUNT(*), 0)::int FROM rm_user_picks) as total_picks,
         (SELECT COALESCE(COUNT(*), 0)::int FROM rm_survey_responses) as total_surveys
       FROM rm_users`
    );

    res.json({
      users,
      total: countRows[0]?.total || 0,
      page,
      limit,
      summary: summaryRows[0] || {},
    });
  } catch (err) {
    console.error('Rainmaker users error:', err);
    res.status(500).json({ error: 'Failed to load users' });
  }
});

// GET /api/rainmaker/users/:userId — full user detail
router.get('/users/:userId', async (req: Request, res: Response) => {
  try {
    const { userId } = req.params;

    const [userRes, picksRes, surveysRes, purchasesRes, ledgerRes] = await Promise.all([
      pool.query('SELECT * FROM rm_users WHERE id = $1', [userId]),
      pool.query(
        `SELECT up.*, fc.home_team, fc.away_team, fc.league, fc.starts_at
         FROM rm_user_picks up
         LEFT JOIN rm_forecast_cache fc ON up.forecast_id = fc.id
         WHERE up.user_id = $1
         ORDER BY up.created_at DESC
         LIMIT 50`,
        [userId]
      ),
      pool.query(
        `SELECT sr.*, s.title as survey_title, s.questions as survey_questions
         FROM rm_survey_responses sr
         LEFT JOIN rm_surveys s ON sr.survey_id = s.id
         WHERE sr.user_id = $1
         ORDER BY sr.created_at DESC`,
        [userId]
      ),
      pool.query(
        `SELECT * FROM rm_purchases WHERE user_id = $1 ORDER BY created_at DESC`,
        [userId]
      ),
      pool.query(
        `SELECT * FROM rm_forecast_ledger WHERE user_id = $1 ORDER BY created_at DESC LIMIT 50`,
        [userId]
      ),
    ]);

    if (userRes.rows.length === 0) {
      res.status(404).json({ error: 'User not found' });
      return;
    }

    const user = userRes.rows[0];
    // Remove sensitive fields
    delete user.password_hash;
    delete user.verification_token;
    delete user.verification_expires;

    res.json({
      user,
      picks: picksRes.rows,
      surveys: surveysRes.rows,
      purchases: purchasesRes.rows,
      ledger: ledgerRes.rows,
    });
  } catch (err) {
    console.error('Rainmaker user detail error:', err);
    res.status(500).json({ error: 'Failed to load user detail' });
  }
});

// ── Logo Admin Routes (direct DB queries on shared sportsdb) ──

// GET /api/rainmaker/logos/coverage — per-league breakdown
router.get('/logos/coverage', async (_req: Request, res: Response) => {
  try {
    const { rows: byLeague } = await pool.query(
      `SELECT league,
              COUNT(*)::int as total,
              COUNT(*) FILTER (WHERE resolution_status = 'resolved')::int as resolved,
              COUNT(*) FILTER (WHERE resolution_status = 'fallback')::int as fallback,
              COUNT(*) FILTER (WHERE resolution_status IN ('pending', 'failed'))::int as failed
       FROM rm_team_logos
       GROUP BY league
       ORDER BY league`
    );
    const totals = byLeague.reduce(
      (acc: any, c: any) => ({
        total: acc.total + c.total,
        resolved: acc.resolved + c.resolved,
        fallback: acc.fallback + c.fallback,
        failed: acc.failed + c.failed,
      }),
      { total: 0, resolved: 0, fallback: 0, failed: 0 }
    );
    res.json({ totals, byLeague });
  } catch (err) {
    console.error('Logo coverage error:', err);
    res.status(500).json({ error: 'Failed to load logo coverage' });
  }
});

// GET /api/rainmaker/logos/recent — last 20 updated logos
router.get('/logos/recent', async (_req: Request, res: Response) => {
  try {
    const { rows: logos } = await pool.query(
      'SELECT * FROM rm_team_logos ORDER BY updated_at DESC LIMIT 20'
    );
    res.json({ logos });
  } catch (err) {
    console.error('Logo recent error:', err);
    res.status(500).json({ error: 'Failed to load recent logos' });
  }
});

// GET /api/rainmaker/logos/needs-review — fallback/failed teams
router.get('/logos/needs-review', async (_req: Request, res: Response) => {
  try {
    const { rows: teams } = await pool.query(
      `SELECT * FROM rm_team_logos
       WHERE resolution_status IN ('fallback', 'failed', 'pending')
         AND admin_override = FALSE
       ORDER BY league, team_abbr`
    );
    res.json({ count: teams.length, teams });
  } catch (err) {
    console.error('Logo needs-review error:', err);
    res.status(500).json({ error: 'Failed to load needs-review logos' });
  }
});

// POST /api/rainmaker/logos/retry/:league/:abbr — trigger re-resolution via Rainmaker API
router.post('/logos/retry/:league/:abbr', async (req: Request, res: Response) => {
  try {
    const { league, abbr } = req.params;
    // Call Rainmaker's sync endpoint (no auth needed for internal call)
    const rmRes = await fetch(`http://localhost:3021/api/logos/sync`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ league, abbr }),
    });
    // If Rainmaker API rejects (needs auth), fall back to direct DB update
    if (!rmRes.ok) {
      // Mark as pending so next sync picks it up
      await pool.query(
        `UPDATE rm_team_logos SET resolution_status = 'pending', updated_at = NOW()
         WHERE league = $1 AND team_abbr = $2 AND admin_override = FALSE`,
        [league, abbr]
      );
      res.json({ status: 'queued', message: 'Marked for retry on next sync cycle' });
      return;
    }
    const data = await rmRes.json();
    res.json(data);
  } catch (err) {
    console.error('Logo retry error:', err);
    res.status(500).json({ error: 'Failed to retry logo' });
  }
});

// ── Orb Agent Routes ──

// Helper: convert snake_case DB row to camelCase agent object
function formatAgent(row: any): any {
  return {
    id: row.id,
    name: row.name,
    category: row.category,
    department: row.department,
    level: row.level,
    parentId: row.parent_id,
    status: row.status,
    primaryMission: row.primary_mission,
    strategicGoals: row.strategic_goals,
    primaryResponsibilities: row.primary_responsibilities,
    secondaryResponsibilities: row.secondary_responsibilities,
    function: row.function_desc,
    role: row.role_desc,
    frequency: row.frequency,
    inputs: row.inputs,
    outputs: row.outputs,
    dependencies: row.dependencies,
    triggerConditions: row.trigger_conditions,
    executionWorkflow: row.execution_workflow,
    escalationPath: row.escalation_path,
    pm2Process: row.pm2_process,
    version: row.version,
    owner: row.owner,
    creatorName: row.creator_name,
    originalCreator: row.original_creator,
    projectId: row.project_id,
    projectName: row.project_name,
    projectType: row.project_type,
    projectTag: row.project_tag,
    businessUnit: row.business_unit,
    bornDate: row.born_date,
    fullLifeSummary: row.full_life_summary,
    roleDefinition: row.role_definition,
    nonScopeDefinition: row.non_scope_definition,
    limitations: row.limitations,
    ecosystemContribution: row.ecosystem_contribution,
    functionScope: row.function_scope,
    learningLog: row.learning_log,
    currentObservations: row.current_observations,
    selfIdentifiedImprovements: row.self_identified_improvements,
    recurringIssuesDetected: row.recurring_issues_detected,
    legalComplianceStatus: row.legal_compliance_status,
    legalRiskLevel: row.legal_risk_level,
    legalNotes: row.legal_notes,
    legalLastReviewDate: row.legal_last_review_date,
    conceptData: row.concept_data,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    runtimeMode: row.runtime_mode,
    operatingScope: row.operating_scope,
    deploymentStatus: row.deployment_status,
    monitoringTargets: row.monitoring_targets,
    riskMonitoring: row.risk_monitoring,
    securityConfig: row.security_config,
    futureLogicHooks: row.future_logic_hooks,
    auditRequirements: row.audit_requirements,
    departmentScope: row.department_scope,
    manages: row.manages,
    adminIdentity: row.admin_identity,
    originatingAdmin: row.originating_admin,
    contextType: row.context_type,
    researchCategory: row.research_category,
    level1Category: row.level_1_category,
    departmentBranch: row.department_branch,
    hierarchyBucket: row.hierarchy_bucket,
    collaborationRequired: row.collaboration_required,
    taskInstructions: row.task_instructions,
    mobilityType: row.mobility_type,
    dataTargets: row.data_targets,
    collaborationAgents: row.collaboration_agents,
    loggingRequirements: row.logging_requirements,
    legalReviewedBy: row.legal_reviewed_by,
    legalCreationReview: row.legal_creation_review,
    legalEventsLog: row.legal_events_log,
    legalReviewHistory: row.legal_review_history,
    metrics: row.metrics || null,
  };
}

// GET /api/rainmaker/orb-agents — all agents with latest metrics
router.get('/orb-agents', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT a.*, json_build_object(
        'completionRate', COALESCE(m.completion_rate, 0),
        'efficiency', COALESCE(m.efficiency, 0),
        'resourceUsage', COALESCE(m.resource_usage, 0),
        'qualityScore', COALESCE(m.quality_score, 0),
        'tasksCompleted', COALESCE(m.tasks_completed, 0),
        'tasksFailed', COALESCE(m.tasks_failed, 0),
        'costUsd', COALESCE(m.cost_usd, 0),
        'tokensUsed', COALESCE(m.tokens_used, 0)
      ) AS metrics
      FROM orb.agents a
      LEFT JOIN LATERAL (
        SELECT * FROM orb.metrics WHERE agent_id = a.id ORDER BY snapshot_at DESC LIMIT 1
      ) m ON TRUE
      ORDER BY a.level, a.department, a.name`
    );

    const agents = rows.map(formatAgent);
    res.json({ agents });
  } catch (err) {
    console.error('Orb agents list error:', err);
    res.status(500).json({ error: 'Failed to load orb agents' });
  }
});

// GET /api/rainmaker/orb-agents/projects — projects with agent counts
router.get('/orb-agents/projects', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT p.*,
        COUNT(a.id)::int as agent_count,
        COUNT(DISTINCT a.department) FILTER (WHERE a.department IS NOT NULL)::int as dept_count
      FROM orb.projects p
      LEFT JOIN orb.agents a ON a.project_id = p.id
      WHERE p.entity_type = 'project'
      GROUP BY p.id
      ORDER BY p.name`
    );

    const projects = rows.map((r: any) => ({
      id: r.id,
      name: r.name,
      summary: r.summary,
      icon: r.icon,
      creator: r.creator,
      status: r.status,
      priorityLevel: r.priority_level,
      entityType: r.entity_type,
      createdAt: r.created_at,
      updatedAt: r.updated_at,
      conceptData: r.concept_data,
      agentCount: r.agent_count,
      deptCount: r.dept_count,
    }));

    res.json({ projects });
  } catch (err) {
    console.error('Orb projects error:', err);
    res.status(500).json({ error: 'Failed to load orb projects' });
  }
});

// GET /api/rainmaker/orb-agents/:agentId — single agent detail with children
router.get('/orb-agents/:agentId', async (req: Request, res: Response) => {
  try {
    const { agentId } = req.params;

    const [agentRes, childrenRes] = await Promise.all([
      pool.query(
        `SELECT a.*, json_build_object(
          'completionRate', COALESCE(m.completion_rate, 0),
          'efficiency', COALESCE(m.efficiency, 0),
          'resourceUsage', COALESCE(m.resource_usage, 0),
          'qualityScore', COALESCE(m.quality_score, 0),
          'tasksCompleted', COALESCE(m.tasks_completed, 0),
          'tasksFailed', COALESCE(m.tasks_failed, 0),
          'costUsd', COALESCE(m.cost_usd, 0),
          'tokensUsed', COALESCE(m.tokens_used, 0)
        ) AS metrics
        FROM orb.agents a
        LEFT JOIN LATERAL (
          SELECT * FROM orb.metrics WHERE agent_id = a.id ORDER BY snapshot_at DESC LIMIT 1
        ) m ON TRUE
        WHERE a.id = $1`,
        [agentId]
      ),
      pool.query(
        `SELECT a.*, json_build_object(
          'completionRate', COALESCE(m.completion_rate, 0),
          'efficiency', COALESCE(m.efficiency, 0),
          'resourceUsage', COALESCE(m.resource_usage, 0),
          'qualityScore', COALESCE(m.quality_score, 0),
          'tasksCompleted', COALESCE(m.tasks_completed, 0),
          'tasksFailed', COALESCE(m.tasks_failed, 0),
          'costUsd', COALESCE(m.cost_usd, 0),
          'tokensUsed', COALESCE(m.tokens_used, 0)
        ) AS metrics
        FROM orb.agents a
        LEFT JOIN LATERAL (
          SELECT * FROM orb.metrics WHERE agent_id = a.id ORDER BY snapshot_at DESC LIMIT 1
        ) m ON TRUE
        WHERE a.parent_id = $1
        ORDER BY a.level, a.department, a.name`,
        [agentId]
      ),
    ]);

    if (agentRes.rows.length === 0) {
      res.status(404).json({ error: 'Agent not found' });
      return;
    }

    const agent = formatAgent(agentRes.rows[0]);
    const children = childrenRes.rows.map(formatAgent);

    res.json({ agent, children });
  } catch (err) {
    console.error('Orb agent detail error:', err);
    res.status(500).json({ error: 'Failed to load orb agent detail' });
  }
});

export default router;