ADMIN ENHANCEMENTS SPECIFICATION

Last Updated: 2025-11-09

Status: Active Started: 2025-10-30 Target Completion: 2025-12-15 Priority: High Category: Frontend Development Component: Enhanced Admin Interface Version: 1.0.0 Owner: Admin Team

Executive Summary

This document specifies enhancements to the existing admin frontend (admin.epgoat.tv) to support the full SaaS platform. The enhanced admin interface will provide operational tools for provider management, script execution, database operations, and system monitoring.

Key Enhancements

1. Provider Management - Add, configure, and test providers
2. Script Execution GUI - Trigger GitHub Actions workflows manually
3. Database Browser - View and edit D1 tables directly
4. Enhanced Match Overrides - Improved UI for manual event linking
5. Key Management - Admin tools to view/manage all user keys
6. User Management - View users, subscriptions, and usage
7. System Monitoring - Dashboards for health and performance

Table of Contents

1. Architecture
2. Provider Management
3. Script Execution
4. Database Browser
5. Match Override Enhancements
6. Key Management
7. User Management
8. System Monitoring
9. Technical Implementation
10. Security

Architecture

Current Admin Structure

web-admin/ (existing)
├── src/
│   ├── components/
│   │   ├── MatchOverrides.tsx    # Existing
│   │   ├── EventSearch.tsx       # Existing
│   │   └── AuditLog.tsx          # Existing
│   ├── pages/
│   │   └── Dashboard.tsx
│   └── App.tsx
└── README.md

Enhanced Admin Structure

web-admin/
├── src/
│   ├── components/
│   │   ├── match-overrides/
│   │   │   ├── MatchOverridesList.tsx
│   │   │   ├── MatchOverrideForm.tsx
│   │   │   ├── EventSearchModal.tsx
│   │   │   └── BulkImport.tsx
│   │   ├── providers/
│   │   │   ├── ProvidersList.tsx
│   │   │   ├── ProviderForm.tsx
│   │   │   ├── ProviderTestRunner.tsx
│   │   │   └── FamilyMappingEditor.tsx
│   │   ├── scripts/
│   │   │   ├── ScriptDashboard.tsx
│   │   │   ├── WorkflowTrigger.tsx
│   │   │   └── ExecutionLogs.tsx
│   │   ├── database/
│   │   │   ├── TableBrowser.tsx
│   │   │   ├── QueryEditor.tsx
│   │   │   ├── SchemaViewer.tsx
│   │   │   └── MigrationRunner.tsx
│   │   ├── keys/
│   │   │   ├── KeysList.tsx
│   │   │   ├── KeyDetails.tsx
│   │   │   ├── UsageChart.tsx
│   │   │   └── AbuseDetector.tsx
│   │   ├── users/
│   │   │   ├── UsersList.tsx
│   │   │   ├── UserDetails.tsx
│   │   │   ├── SubscriptionManager.tsx
│   │   │   └── SupportTickets.tsx
│   │   └── monitoring/
│   │       ├── SystemHealth.tsx
│   │       ├── MetricsDashboard.tsx
│   │       ├── ErrorLog.tsx
│   │       └── CostTracker.tsx
│   ├── pages/
│   │   ├── Dashboard.tsx
│   │   ├── Providers.tsx
│   │   ├── Scripts.tsx
│   │   ├── Database.tsx
│   │   ├── MatchOverrides.tsx
│   │   ├── Keys.tsx
│   │   ├── Users.tsx
│   │   └── Monitoring.tsx
│   └── App.tsx

Provider Management

Overview

Admin interface to manage all supported IPTV providers (currently 10+, expandable).

Features

1. Providers List

export function ProvidersList() {
  const { providers, loading } = useProviders();

  return (
    <div className="providers-list p-8">
      <div className="flex justify-between items-center mb-8">
        <h1 className="text-3xl font-bold">Provider Management</h1>
        <button onClick={() => navigate('/admin/providers/new')} className="btn btn-primary">
          <PlusIcon className="mr-2" />
          Add Provider
        </button>
      </div>

      <table className="w-full">
        <thead>
          <tr className="border-b bg-gray-50">
            <th className="text-left py-3 px-4">Provider</th>
            <th className="text-left py-3 px-4">Type</th>
            <th className="text-left py-3 px-4">Status</th>
            <th className="text-left py-3 px-4">Channels</th>
            <th className="text-left py-3 px-4">Match Rate</th>
            <th className="text-left py-3 px-4">Last Updated</th>
            <th className="text-left py-3 px-4">Actions</th>
          </tr>
        </thead>
        <tbody>
          {providers.map(provider => (
            <tr key={provider.id} className="border-b hover:bg-gray-50">
              <td className="py-3 px-4">
                <div className="flex items-center">
                  <img src={provider.logo_url} className="w-10 h-10 rounded mr-3" />
                  <div>
                    <div className="font-semibold">{provider.name}</div>
                    <div className="text-sm text-gray-500">{provider.description}</div>
                  </div>
                </div>
              </td>
              <td className="py-3 px-4">
                <span className="px-3 py-1 bg-blue-100 text-blue-800 rounded-full text-xs">
                  {provider.type}
                </span>
              </td>
              <td className="py-3 px-4">
                <StatusBadge status={provider.status} />
              </td>
              <td className="py-3 px-4">{provider.channel_count || 'N/A'}</td>
              <td className="py-3 px-4">
                <div className="flex items-center">
                  <div className="w-20 bg-gray-200 rounded-full h-2 mr-2">
                    <div
                      className="bg-green-500 h-2 rounded-full"
                      style={{ width: `${provider.match_rate || 0}%` }}
                    />
                  </div>
                  <span className="text-sm">{provider.match_rate || 0}%</span>
                </div>
              </td>
              <td className="py-3 px-4">
                {provider.last_updated_at ? formatRelative(provider.last_updated_at) : 'Never'}
              </td>
              <td className="py-3 px-4">
                <div className="flex gap-2">
                  <button
                    onClick={() => navigate(`/admin/providers/${provider.id}/edit`)}
                    className="text-blue-600 hover:underline text-sm"
                  >
                    Edit
                  </button>
                  <button
                    onClick={() => testProvider(provider.id)}
                    className="text-green-600 hover:underline text-sm"
                  >
                    Test
                  </button>
                  <button
                    onClick={() => runProvider(provider.id)}
                    className="text-purple-600 hover:underline text-sm"
                  >
                    Run
                  </button>
                </div>
              </td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

2. Provider Form

Fields:

interface Provider {
  id: number;
  name: string;                  // "TPS", "SportsTV", etc.
  description: string;           // Short description
  type: 'sports' | 'general';    // Provider type
  status: 'active' | 'inactive' | 'testing';
  logo_url: string;              // Cloudflare R2 URL
  m3u_url: string;               // M3U playlist URL (can contain credentials)
  xmltv_filename: string;        // Output filename (e.g., "tps.xml")
  config: {
    timezone: string;            // "America/New_York"
    update_frequency: '4x' | '2x' | 'daily';
    family_mappings_file: string; // "tps.yml", "universal.yml"
    custom_patterns: string[];   // Provider-specific regex patterns
  };
  created_at: string;
  updated_at: string;
}

UI:

export function ProviderForm({ providerId }: { providerId?: number }) {
  const { provider, loading } = useProvider(providerId);
  const { register, handleSubmit, formState: { errors } } = useForm<Provider>();

  const onSubmit = async (data: Provider) => {
    if (providerId) {
      await api.put(`/admin/providers/${providerId}`, data);
      toast.success('Provider updated successfully');
    } else {
      await api.post('/admin/providers', data);
      toast.success('Provider created successfully');
    }
    navigate('/admin/providers');
  };

  return (
    <form onSubmit={handleSubmit(onSubmit)} className="max-w-3xl mx-auto p-8">
      <h1 className="text-3xl font-bold mb-8">
        {providerId ? 'Edit Provider' : 'Add New Provider'}
      </h1>

      <div className="space-y-6">
        {/* Basic Info */}
        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">Basic Information</h2>
          </CardHeader>
          <CardBody>
            <div className="grid md:grid-cols-2 gap-4">
              <div>
                <label className="block text-sm font-semibold mb-2">Provider Name</label>
                <input
                  {...register('name', { required: 'Name is required' })}
                  className="w-full px-4 py-2 border rounded"
                  placeholder="TPS"
                />
                {errors.name && <span className="text-red-500 text-sm">{errors.name.message}</span>}
              </div>

              <div>
                <label className="block text-sm font-semibold mb-2">Type</label>
                <select {...register('type')} className="w-full px-4 py-2 border rounded">
                  <option value="sports">Sports</option>
                  <option value="general">General</option>
                </select>
              </div>

              <div className="md:col-span-2">
                <label className="block text-sm font-semibold mb-2">Description</label>
                <input
                  {...register('description')}
                  className="w-full px-4 py-2 border rounded"
                  placeholder="Premium sports provider with 1000+ channels"
                />
              </div>

              <div className="md:col-span-2">
                <label className="block text-sm font-semibold mb-2">Logo URL</label>
                <input
                  {...register('logo_url')}
                  type="url"
                  className="w-full px-4 py-2 border rounded"
                  placeholder="https://files.epgoat.tv/logos/tps.png"
                />
              </div>
            </div>
          </CardBody>
        </Card>

        {/* M3U Configuration */}
        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">M3U Configuration</h2>
          </CardHeader>
          <CardBody>
            <div className="space-y-4">
              <div>
                <label className="block text-sm font-semibold mb-2">M3U URL</label>
                <input
                  {...register('m3u_url', { required: 'M3U URL is required' })}
                  type="url"
                  className="w-full px-4 py-2 border rounded font-mono text-sm"
                  placeholder="https://provider.com/get.php?username=USER&password=PASS&type=m3u"
                />
                <p className="text-xs text-gray-500 mt-1">
                  ⚠️ Stored securely. Credentials are encrypted at rest.
                </p>
              </div>

              <div>
                <label className="block text-sm font-semibold mb-2">XMLTV Output Filename</label>
                <input
                  {...register('xmltv_filename', { required: 'Filename is required' })}
                  className="w-full px-4 py-2 border rounded"
                  placeholder="tps.xml"
                />
              </div>
            </div>
          </CardBody>
        </Card>

        {/* Processing Configuration */}
        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">Processing Configuration</h2>
          </CardHeader>
          <CardBody>
            <div className="grid md:grid-cols-2 gap-4">
              <div>
                <label className="block text-sm font-semibold mb-2">Timezone</label>
                <select {...register('config.timezone')} className="w-full px-4 py-2 border rounded">
                  <option value="America/New_York">America/New_York (ET)</option>
                  <option value="America/Chicago">America/Chicago (CT)</option>
                  <option value="America/Denver">America/Denver (MT)</option>
                  <option value="America/Los_Angeles">America/Los_Angeles (PT)</option>
                  <option value="Europe/London">Europe/London (GMT)</option>
                </select>
              </div>

              <div>
                <label className="block text-sm font-semibold mb-2">Update Frequency</label>
                <select {...register('config.update_frequency')} className="w-full px-4 py-2 border rounded">
                  <option value="4x">4x Daily (12am, 6am, 12pm, 6pm)</option>
                  <option value="2x">2x Daily (12am, 12pm)</option>
                  <option value="daily">Daily (12am)</option>
                </select>
              </div>

              <div className="md:col-span-2">
                <label className="block text-sm font-semibold mb-2">Family Mappings File</label>
                <input
                  {...register('config.family_mappings_file')}
                  className="w-full px-4 py-2 border rounded"
                  placeholder="tps.yml"
                />
                <p className="text-xs text-gray-500 mt-1">
                  File in <code>backend/config/family_mappings/</code>
                </p>
              </div>
            </div>
          </CardBody>
        </Card>

        {/* Actions */}
        <div className="flex justify-between">
          <button type="button" onClick={() => navigate('/admin/providers')} className="btn btn-outline">
            Cancel
          </button>
          <div className="flex gap-2">
            <button type="button" onClick={() => testM3U()} className="btn btn-secondary">
              Test M3U
            </button>
            <button type="submit" className="btn btn-primary">
              {providerId ? 'Update Provider' : 'Create Provider'}
            </button>
          </div>
        </div>
      </div>
    </form>
  );
}

3. Provider Test Runner

Purpose: Test provider configuration before saving

export function ProviderTestRunner({ provider }: { provider: Provider }) {
  const [testing, setTesting] = useState(false);
  const [results, setResults] = useState<TestResults | null>(null);

  const runTest = async () => {
    setTesting(true);

    const testResults = await api.post('/admin/providers/test', {
      m3u_url: provider.m3u_url,
      family_mappings_file: provider.config.family_mappings_file
    });

    setResults(testResults);
    setTesting(false);
  };

  return (
    <Card>
      <CardHeader>
        <h2 className="text-xl font-bold">Test Provider Configuration</h2>
      </CardHeader>
      <CardBody>
        <div className="space-y-4">
          <button onClick={runTest} disabled={testing} className="btn btn-primary">
            {testing ? 'Testing...' : 'Run Test'}
          </button>

          {results && (
            <div className="space-y-4">
              <div className="grid md:grid-cols-3 gap-4">
                <div className="border rounded p-4">
                  <div className="text-2xl font-bold">{results.channel_count}</div>
                  <div className="text-sm text-gray-600">Channels Found</div>
                </div>
                <div className="border rounded p-4">
                  <div className="text-2xl font-bold text-green-600">{results.match_count}</div>
                  <div className="text-sm text-gray-600">Matches</div>
                </div>
                <div className="border rounded p-4">
                  <div className="text-2xl font-bold text-red-600">{results.mismatch_count}</div>
                  <div className="text-sm text-gray-600">Mismatches</div>
                </div>
              </div>

              <div>
                <h3 className="font-bold mb-2">Sample Matches:</h3>
                <table className="w-full text-sm">
                  <thead>
                    <tr className="border-b">
                      <th className="text-left py-2">Channel</th>
                      <th className="text-left py-2">Matched Event</th>
                      <th className="text-left py-2">Confidence</th>
                    </tr>
                  </thead>
                  <tbody>
                    {results.sample_matches.map((match, i) => (
                      <tr key={i} className="border-b">
                        <td className="py-2">{match.channel_name}</td>
                        <td className="py-2">{match.event_title}</td>
                        <td className="py-2">{(match.confidence * 100).toFixed(0)}%</td>
                      </tr>
                    ))}
                  </tbody>
                </table>
              </div>

              <div>
                <h3 className="font-bold mb-2 text-red-600">Sample Mismatches:</h3>
                <ul className="list-disc list-inside text-sm">
                  {results.sample_mismatches.map((channel, i) => (
                    <li key={i}>{channel}</li>
                  ))}
                </ul>
              </div>
            </div>
          )}
        </div>
      </CardBody>
    </Card>
  );
}

Script Execution

Overview

GUI to trigger GitHub Actions workflows manually (instead of relying only on cron).

Features

1. Script Dashboard

export function ScriptDashboard() {
  const { workflows, loading } = useGitHubWorkflows();
  const { executions } = useWorkflowExecutions();

  return (
    <div className="scripts-dashboard p-8">
      <h1 className="text-3xl font-bold mb-8">Script Execution</h1>

      {/* Workflow Triggers */}
      <Card className="mb-8">
        <CardHeader>
          <h2 className="text-xl font-bold">Available Workflows</h2>
        </CardHeader>
        <CardBody>
          <div className="grid md:grid-cols-2 gap-4">
            {workflows.map(workflow => (
              <WorkflowCard key={workflow.id} workflow={workflow} />
            ))}
          </div>
        </CardBody>
      </Card>

      {/* Recent Executions */}
      <Card>
        <CardHeader>
          <h2 className="text-xl font-bold">Recent Executions</h2>
        </CardHeader>
        <CardBody>
          <ExecutionsList executions={executions} />
        </CardBody>
      </Card>
    </div>
  );
}

2. Workflow Trigger

interface Workflow {
  id: string;
  name: string;
  description: string;
  inputs: {
    name: string;
    description: string;
    required: boolean;
    type: 'string' | 'choice' | 'boolean';
    options?: string[];
    default?: string;
  }[];
}

export function WorkflowCard({ workflow }: { workflow: Workflow }) {
  const [showModal, setShowModal] = useState(false);

  return (
    <>
      <div className="border rounded-lg p-6 hover:shadow-lg transition-shadow">
        <div className="flex items-start justify-between mb-4">
          <div>
            <h3 className="text-lg font-bold">{workflow.name}</h3>
            <p className="text-sm text-gray-600">{workflow.description}</p>
          </div>
          <span className="px-3 py-1 bg-green-100 text-green-800 rounded-full text-xs">
            Active
          </span>
        </div>

        <button onClick={() => setShowModal(true)} className="btn btn-primary w-full">
          <PlayIcon className="mr-2" />
          Run Workflow
        </button>
      </div>

      {showModal && (
        <WorkflowTriggerModal
          workflow={workflow}
          isOpen={showModal}
          onClose={() => setShowModal(false)}
        />
      )}
    </>
  );
}

export function WorkflowTriggerModal({
  workflow,
  isOpen,
  onClose
}: {
  workflow: Workflow;
  isOpen: boolean;
  onClose: () => void;
}) {
  const { register, handleSubmit } = useForm();

  const onSubmit = async (data: any) => {
    // Trigger GitHub Actions workflow via API
    await api.post('/admin/scripts/trigger', {
      workflow_id: workflow.id,
      inputs: data
    });

    toast.success('Workflow triggered successfully');
    onClose();
  };

  return (
    <Modal isOpen={isOpen} onClose={onClose}>
      <ModalHeader>Run: {workflow.name}</ModalHeader>
      <ModalBody>
        <form onSubmit={handleSubmit(onSubmit)} className="space-y-4">
          {workflow.inputs.map(input => (
            <div key={input.name}>
              <label className="block text-sm font-semibold mb-2">
                {input.description}
                {input.required && <span className="text-red-500 ml-1">*</span>}
              </label>

              {input.type === 'string' && (
                <input
                  {...register(input.name, { required: input.required })}
                  type="text"
                  className="w-full px-4 py-2 border rounded"
                  defaultValue={input.default}
                />
              )}

              {input.type === 'choice' && (
                <select
                  {...register(input.name, { required: input.required })}
                  className="w-full px-4 py-2 border rounded"
                  defaultValue={input.default}
                >
                  {input.options?.map(option => (
                    <option key={option} value={option}>
                      {option}
                    </option>
                  ))}
                </select>
              )}

              {input.type === 'boolean' && (
                <label className="flex items-center">
                  <input
                    {...register(input.name)}
                    type="checkbox"
                    className="mr-2"
                    defaultChecked={input.default === 'true'}
                  />
                  <span className="text-sm">{input.description}</span>
                </label>
              )}
            </div>
          ))}

          <div className="flex justify-end gap-2">
            <button type="button" onClick={onClose} className="btn btn-outline">
              Cancel
            </button>
            <button type="submit" className="btn btn-primary">
              Run Workflow
            </button>
          </div>
        </form>
      </ModalBody>
    </Modal>
  );
}

3. Execution Logs

export function ExecutionsList({ executions }: { executions: Execution[] }) {
  return (
    <table className="w-full">
      <thead>
        <tr className="border-b bg-gray-50">
          <th className="text-left py-3 px-4">Workflow</th>
          <th className="text-left py-3 px-4">Triggered By</th>
          <th className="text-left py-3 px-4">Started</th>
          <th className="text-left py-3 px-4">Duration</th>
          <th className="text-left py-3 px-4">Status</th>
          <th className="text-left py-3 px-4">Actions</th>
        </tr>
      </thead>
      <tbody>
        {executions.map(execution => (
          <tr key={execution.id} className="border-b hover:bg-gray-50">
            <td className="py-3 px-4 font-semibold">{execution.workflow_name}</td>
            <td className="py-3 px-4">{execution.triggered_by}</td>
            <td className="py-3 px-4">{formatRelative(execution.started_at)}</td>
            <td className="py-3 px-4">{execution.duration || 'Running...'}</td>
            <td className="py-3 px-4">
              <ExecutionStatusBadge status={execution.status} />
            </td>
            <td className="py-3 px-4">
              <a
                href={execution.logs_url}
                target="_blank"
                rel="noopener noreferrer"
                className="text-blue-600 hover:underline text-sm"
              >
                View Logs
              </a>
            </td>
          </tr>
        ))}
      </tbody>
    </table>
  );
}

Database Browser

Overview

Direct access to Supabase PostgreSQL database for viewing and editing tables.

Features

1. Table Browser

export function TableBrowser() {
  const [selectedTable, setSelectedTable] = useState<string>('events');
  const [data, setData] = useState<any[]>([]);
  const [page, setPage] = useState(1);
  const [limit] = useState(50);

  useEffect(() => {
    loadTableData(selectedTable, page, limit);
  }, [selectedTable, page]);

  const loadTableData = async (table: string, page: number, limit: number) => {
    const result = await api.get(`/admin/database/tables/${table}`, {
      params: { page, limit }
    });
    setData(result.rows);
  };

  const tables = [
    'events', 'participants', 'participant_aliases', 'event_participants',
    'event_identifiers', 'unmatched_channels', 'learned_patterns', 'match_cache',
    'providers', 'channel_families', 'family_league_mappings',
    'users', 'user_subscriptions', 'access_keys', 'key_access_logs',
    'subscription_events', 'audit_log'
  ];

  return (
    <div className="database-browser p-8">
      <h1 className="text-3xl font-bold mb-8">Database Browser</h1>

      <div className="grid md:grid-cols-4 gap-6">
        {/* Sidebar: Table List */}
        <div className="md:col-span-1">
          <Card>
            <CardHeader>
              <h2 className="text-xl font-bold">Tables</h2>
            </CardHeader>
            <CardBody>
              <ul className="space-y-1">
                {tables.map(table => (
                  <li key={table}>
                    <button
                      onClick={() => setSelectedTable(table)}
                      className={`w-full text-left px-4 py-2 rounded hover:bg-gray-100 ${
                        selectedTable === table ? 'bg-blue-100 text-blue-800 font-semibold' : ''
                      }`}
                    >
                      {table}
                    </button>
                  </li>
                ))}
              </ul>
            </CardBody>
          </Card>
        </div>

        {/* Main: Table Data */}
        <div className="md:col-span-3">
          <Card>
            <CardHeader>
              <div className="flex justify-between items-center">
                <h2 className="text-xl font-bold">{selectedTable}</h2>
                <div className="flex gap-2">
                  <button onClick={() => exportToCSV(data)} className="btn btn-sm btn-outline">
                    Export CSV
                  </button>
                  <button onClick={() => refreshData()} className="btn btn-sm btn-secondary">
                    Refresh
                  </button>
                </div>
              </div>
            </CardHeader>
            <CardBody>
              <div className="overflow-x-auto">
                <table className="w-full text-sm">
                  <thead>
                    <tr className="border-b bg-gray-50">
                      {data.length > 0 &&
                        Object.keys(data[0]).map(key => (
                          <th key={key} className="text-left py-2 px-3 font-semibold">
                            {key}
                          </th>
                        ))}
                    </tr>
                  </thead>
                  <tbody>
                    {data.map((row, i) => (
                      <tr key={i} className="border-b hover:bg-gray-50">
                        {Object.values(row).map((value: any, j) => (
                          <td key={j} className="py-2 px-3">
                            {typeof value === 'object' ? JSON.stringify(value) : String(value)}
                          </td>
                        ))}
                      </tr>
                    ))}
                  </tbody>
                </table>
              </div>

              {/* Pagination */}
              <div className="flex justify-between items-center mt-4">
                <div className="text-sm text-gray-600">
                  Showing {(page - 1) * limit + 1} to {page * limit} of {data.length} rows
                </div>
                <div className="flex gap-2">
                  <button
                    onClick={() => setPage(p => Math.max(1, p - 1))}
                    disabled={page === 1}
                    className="btn btn-sm btn-outline"
                  >
                    Previous
                  </button>
                  <button
                    onClick={() => setPage(p => p + 1)}
                    disabled={data.length < limit}
                    className="btn btn-sm btn-outline"
                  >
                    Next
                  </button>
                </div>
              </div>
            </CardBody>
          </Card>
        </div>
      </div>
    </div>
  );
}

2. SQL Query Editor

export function QueryEditor() {
  const [query, setQuery] = useState('SELECT * FROM events LIMIT 10;');
  const [results, setResults] = useState<any>(null);
  const [error, setError] = useState<string | null>(null);
  const [executing, setExecuting] = useState(false);

  const executeQuery = async () => {
    setExecuting(true);
    setError(null);

    try {
      const result = await api.post('/admin/database/query', { sql: query });
      setResults(result);
    } catch (err: any) {
      setError(err.response?.data?.error || 'Query execution failed');
    } finally {
      setExecuting(false);
    }
  };

  return (
    <div className="query-editor p-8">
      <h1 className="text-3xl font-bold mb-8">SQL Query Editor</h1>

      <Card>
        <CardHeader>
          <h2 className="text-xl font-bold">Execute SQL</h2>
        </CardHeader>
        <CardBody>
          <div className="space-y-4">
            <div>
              <textarea
                value={query}
                onChange={e => setQuery(e.target.value)}
                className="w-full px-4 py-3 border rounded font-mono text-sm"
                rows={10}
                placeholder="SELECT * FROM events WHERE event_date = '2025-10-30';"
              />
            </div>

            <div className="flex justify-between">
              <button onClick={() => setQuery('')} className="btn btn-outline">
                Clear
              </button>
              <button onClick={executeQuery} disabled={executing} className="btn btn-primary">
                {executing ? 'Executing...' : 'Execute Query'}
              </button>
            </div>

            {error && (
              <div className="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded">
                <strong>Error:</strong> {error}
              </div>
            )}

            {results && (
              <div>
                <h3 className="font-bold mb-2">Results ({results.rows.length} rows):</h3>
                <div className="overflow-x-auto">
                  <table className="w-full text-sm">
                    <thead>
                      <tr className="border-b bg-gray-50">
                        {results.rows.length > 0 &&
                          Object.keys(results.rows[0]).map((key: string) => (
                            <th key={key} className="text-left py-2 px-3">
                              {key}
                            </th>
                          ))}
                      </tr>
                    </thead>
                    <tbody>
                      {results.rows.map((row: any, i: number) => (
                        <tr key={i} className="border-b">
                          {Object.values(row).map((value: any, j: number) => (
                            <td key={j} className="py-2 px-3">
                              {typeof value === 'object' ? JSON.stringify(value) : String(value)}
                            </td>
                          ))}
                        </tr>
                      ))}
                    </tbody>
                  </table>
                </div>
              </div>
            )}
          </div>
        </CardBody>
      </Card>
    </div>
  );
}

Match Override Enhancements

Improvements to Existing UI

1. Bulk Import via CSV
2. Auto-suggest from LLM results
3. Confidence scoring
4. Pattern extraction (automatically create learned_patterns)

export function BulkImportModal({ isOpen, onClose }: { isOpen: boolean; onClose: () => void }) {
  const [file, setFile] = useState<File | null>(null);
  const [preview, setPreview] = useState<any[]>([]);

  const handleFileChange = async (e: React.ChangeEvent<HTMLInputElement>) => {
    const uploadedFile = e.target.files?.[0];
    if (!uploadedFile) return;

    setFile(uploadedFile);

    // Parse CSV
    const text = await uploadedFile.text();
    const rows = parseCSV(text);
    setPreview(rows.slice(0, 10)); // Show first 10 rows
  };

  const handleImport = async () => {
    if (!file) return;

    const formData = new FormData();
    formData.append('file', file);

    await api.post('/admin/match-overrides/bulk-import', formData);
    toast.success('Bulk import completed');
    onClose();
  };

  return (
    <Modal isOpen={isOpen} onClose={onClose}>
      <ModalHeader>Bulk Import Match Overrides</ModalHeader>
      <ModalBody>
        <div className="space-y-4">
          <div>
            <label className="block text-sm font-semibold mb-2">Upload CSV File</label>
            <input
              type="file"
              accept=".csv"
              onChange={handleFileChange}
              className="w-full px-4 py-2 border rounded"
            />
            <p className="text-xs text-gray-500 mt-1">
              CSV Format: channel_name, event_id, confidence
            </p>
          </div>

          {preview.length > 0 && (
            <div>
              <h3 className="font-bold mb-2">Preview (first 10 rows):</h3>
              <table className="w-full text-sm">
                <thead>
                  <tr className="border-b">
                    <th className="text-left py-2">Channel Name</th>
                    <th className="text-left py-2">Event ID</th>
                    <th className="text-left py-2">Confidence</th>
                  </tr>
                </thead>
                <tbody>
                  {preview.map((row, i) => (
                    <tr key={i} className="border-b">
                      <td className="py-2">{row.channel_name}</td>
                      <td className="py-2">{row.event_id}</td>
                      <td className="py-2">{row.confidence}%</td>
                    </tr>
                  ))}
                </tbody>
              </table>
            </div>
          )}

          <div className="flex justify-end gap-2">
            <button onClick={onClose} className="btn btn-outline">
              Cancel
            </button>
            <button onClick={handleImport} disabled={!file} className="btn btn-primary">
              Import {preview.length > 10 ? `${preview.length} rows` : ''}
            </button>
          </div>
        </div>
      </ModalBody>
    </Modal>
  );
}

Key Management

Admin Key Tools

export function AdminKeyManagement() {
  const { keys, loading } = useAllKeys(); // Admin endpoint: all keys across all users
  const [search, setSearch] = useState('');
  const [abuseFilter, setAbuseFilter] = useState<'all' | 'flagged'>('all');

  const filteredKeys = keys
    .filter(k => {
      if (abuseFilter === 'flagged') return k.abuse_flagged;
      return true;
    })
    .filter(k => {
      if (search) {
        return k.id.includes(search) ||
               k.user_email?.toLowerCase().includes(search.toLowerCase());
      }
      return true;
    });

  return (
    <div className="admin-key-management p-8">
      <h1 className="text-3xl font-bold mb-8">Key Management (Admin)</h1>

      <Card>
        <CardHeader>
          <div className="flex justify-between items-center">
            <h2 className="text-xl font-bold">All Access Keys</h2>
            <div className="flex gap-2">
              <select
                value={abuseFilter}
                onChange={e => setAbuseFilter(e.target.value as any)}
                className="px-4 py-2 border rounded"
              >
                <option value="all">All Keys</option>
                <option value="flagged">Flagged for Abuse</option>
              </select>
              <input
                type="text"
                placeholder="Search by key ID or email..."
                value={search}
                onChange={e => setSearch(e.target.value)}
                className="px-4 py-2 border rounded"
              />
            </div>
          </div>
        </CardHeader>
        <CardBody>
          <table className="w-full">
            <thead>
              <tr className="border-b bg-gray-50">
                <th className="text-left py-3 px-4">Key ID</th>
                <th className="text-left py-3 px-4">User</th>
                <th className="text-left py-3 px-4">Type</th>
                <th className="text-left py-3 px-4">Provider</th>
                <th className="text-left py-3 px-4">Status</th>
                <th className="text-left py-3 px-4">Accesses</th>
                <th className="text-left py-3 px-4">Last IP</th>
                <th className="text-left py-3 px-4">Flags</th>
                <th className="text-left py-3 px-4">Actions</th>
              </tr>
            </thead>
            <tbody>
              {filteredKeys.map(key => (
                <tr key={key.id} className="border-b hover:bg-gray-50">
                  <td className="py-3 px-4">
                    <code className="text-xs">{key.id.slice(0, 8)}...</code>
                  </td>
                  <td className="py-3 px-4">
                    <div>
                      <div className="font-semibold">{key.user_email}</div>
                      <div className="text-xs text-gray-500">
                        {key.created_for === 'self' ? 'Consumer' : 'Reseller Customer'}
                      </div>
                    </div>
                  </td>
                  <td className="py-3 px-4">
                    <span className={`px-2 py-1 rounded-full text-xs ${
                      key.created_for === 'self' ? 'bg-blue-100 text-blue-800' : 'bg-purple-100 text-purple-800'
                    }`}>
                      {key.created_for === 'self' ? 'Consumer' : 'Reseller'}
                    </span>
                  </td>
                  <td className="py-3 px-4">{key.provider.name}</td>
                  <td className="py-3 px-4">
                    <StatusBadge status={key.status} />
                  </td>
                  <td className="py-3 px-4">{key.access_count}</td>
                  <td className="py-3 px-4">
                    <code className="text-xs">{key.last_ip || 'N/A'}</code>
                  </td>
                  <td className="py-3 px-4">
                    {key.abuse_flagged && (
                      <span className="px-2 py-1 bg-red-100 text-red-800 rounded-full text-xs">
                        ⚠️ Abuse
                      </span>
                    )}
                  </td>
                  <td className="py-3 px-4">
                    <div className="flex gap-2">
                      <button
                        onClick={() => viewKeyDetails(key.id)}
                        className="text-blue-600 hover:underline text-sm"
                      >
                        View
                      </button>
                      <button
                        onClick={() => suspendKey(key.id)}
                        className="text-red-600 hover:underline text-sm"
                      >
                        Suspend
                      </button>
                    </div>
                  </td>
                </tr>
              ))}
            </tbody>
          </table>
        </CardBody>
      </Card>
    </div>
  );
}

User Management

User List & Details

export function UserManagement() {
  const { users, loading } = useAllUsers();
  const [search, setSearch] = useState('');
  const [roleFilter, setRoleFilter] = useState<'all' | 'consumer' | 'reseller' | 'admin'>('all');

  const filteredUsers = users
    .filter(u => roleFilter === 'all' || u.role === roleFilter)
    .filter(u => {
      if (search) {
        return u.email.toLowerCase().includes(search.toLowerCase()) ||
               u.name?.toLowerCase().includes(search.toLowerCase());
      }
      return true;
    });

  return (
    <div className="user-management p-8">
      <h1 className="text-3xl font-bold mb-8">User Management</h1>

      <Card>
        <CardHeader>
          <div className="flex justify-between items-center">
            <h2 className="text-xl font-bold">All Users</h2>
            <div className="flex gap-2">
              <select
                value={roleFilter}
                onChange={e => setRoleFilter(e.target.value as any)}
                className="px-4 py-2 border rounded"
              >
                <option value="all">All Roles</option>
                <option value="consumer">Consumers</option>
                <option value="reseller">Resellers</option>
                <option value="admin">Admins</option>
              </select>
              <input
                type="text"
                placeholder="Search by email or name..."
                value={search}
                onChange={e => setSearch(e.target.value)}
                className="px-4 py-2 border rounded"
              />
            </div>
          </div>
        </CardHeader>
        <CardBody>
          <table className="w-full">
            <thead>
              <tr className="border-b bg-gray-50">
                <th className="text-left py-3 px-4">User</th>
                <th className="text-left py-3 px-4">Role</th>
                <th className="text-left py-3 px-4">Subscription</th>
                <th className="text-left py-3 px-4">Keys</th>
                <th className="text-left py-3 px-4">Joined</th>
                <th className="text-left py-3 px-4">Actions</th>
              </tr>
            </thead>
            <tbody>
              {filteredUsers.map(user => (
                <tr key={user.id} className="border-b hover:bg-gray-50">
                  <td className="py-3 px-4">
                    <div>
                      <div className="font-semibold">{user.name || 'N/A'}</div>
                      <div className="text-sm text-gray-500">{user.email}</div>
                    </div>
                  </td>
                  <td className="py-3 px-4">
                    <span className={`px-3 py-1 rounded-full text-xs ${
                      user.role === 'admin' ? 'bg-red-100 text-red-800' :
                      user.role === 'reseller' ? 'bg-purple-100 text-purple-800' :
                      'bg-blue-100 text-blue-800'
                    }`}>
                      {user.role}
                    </span>
                  </td>
                  <td className="py-3 px-4">
                    <StatusBadge status={user.subscription_status} />
                  </td>
                  <td className="py-3 px-4">{user.keys_count || 0}</td>
                  <td className="py-3 px-4">{formatDate(user.created_at)}</td>
                  <td className="py-3 px-4">
                    <a
                      href={`/admin/users/${user.id}`}
                      className="text-blue-600 hover:underline text-sm"
                    >
                      View Details
                    </a>
                  </td>
                </tr>
              ))}
            </tbody>
          </table>
        </CardBody>
      </Card>
    </div>
  );
}

System Monitoring

Metrics Dashboard

export function SystemMonitoring() {
  const { metrics, loading } = useSystemMetrics();

  return (
    <div className="system-monitoring p-8">
      <h1 className="text-3xl font-bold mb-8">System Monitoring</h1>

      {/* Key Metrics */}
      <div className="grid md:grid-cols-4 gap-6 mb-8">
        <MetricCard
          title="Total Users"
          value={metrics.total_users}
          change="+12% this week"
          icon={<UsersIcon />}
          color="blue"
        />
        <MetricCard
          title="Active Subscriptions"
          value={metrics.active_subscriptions}
          change="+5% this week"
          icon={<CheckCircleIcon />}
          color="green"
        />
        <MetricCard
          title="EPG Match Rate"
          value={`${metrics.match_rate}%`}
          change="+2.3% this week"
          icon={<ChartBarIcon />}
          color="purple"
        />
        <MetricCard
          title="LLM Cost (MTD)"
          value={`$${metrics.llm_cost_mtd}`}
          change="-15% vs last month"
          icon={<DollarIcon />}
          color="green"
        />
      </div>

      {/* Charts */}
      <div className="grid md:grid-cols-2 gap-6">
        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">User Growth</h2>
          </CardHeader>
          <CardBody>
            <LineChart
              data={metrics.user_growth}
              xKey="date"
              yKey="count"
              height={250}
            />
          </CardBody>
        </Card>

        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">Revenue (MRR)</h2>
          </CardHeader>
          <CardBody>
            <LineChart
              data={metrics.revenue}
              xKey="date"
              yKey="amount"
              height={250}
            />
          </CardBody>
        </Card>

        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">Match Rate by Provider</h2>
          </CardHeader>
          <CardBody>
            <BarChart
              data={metrics.match_rate_by_provider}
              xKey="provider"
              yKey="match_rate"
              height={250}
            />
          </CardBody>
        </Card>

        <Card>
          <CardHeader>
            <h2 className="text-xl font-bold">API Cost Breakdown</h2>
          </CardHeader>
          <CardBody>
            <PieChart
              data={metrics.api_costs}
              nameKey="service"
              valueKey="cost"
              height={250}
            />
          </CardBody>
        </Card>
      </div>
    </div>
  );
}

Technical Implementation

API Endpoints (Admin)

GET    /admin/providers
POST   /admin/providers
GET    /admin/providers/:id
PUT    /admin/providers/:id
DELETE /admin/providers/:id
POST   /admin/providers/test

GET    /admin/scripts/workflows
POST   /admin/scripts/trigger
GET    /admin/scripts/executions

GET    /admin/database/tables/:table
POST   /admin/database/query
GET    /admin/database/schema

GET    /admin/keys
GET    /admin/keys/:id
PUT    /admin/keys/:id/suspend
GET    /admin/keys/:id/logs

GET    /admin/users
GET    /admin/users/:id
PUT    /admin/users/:id/role
DELETE /admin/users/:id

GET    /admin/metrics
GET    /admin/metrics/revenue
GET    /admin/metrics/match-rate
GET    /admin/metrics/costs

Authentication

• Protected: All admin routes require role: admin in JWT
• Middleware: Same Auth0 JWT validation as public frontend
• RBAC: Enforce admin-only access

Security

Admin-Only Access

// Middleware
export function requireAdmin(req, res, next) {
  const token = req.headers.authorization?.replace('Bearer ', '');
  const decoded = verifyJWT(token);

  if (decoded['https://epgoat.tv/role'] !== 'admin') {
    return res.status(403).json({ error: 'Admin access required' });
  }

  next();
}

// Apply to all /admin/* routes
app.use('/admin/*', requireAdmin);

Audit Logging

• Log ALL admin actions to audit_log table
• Include: user email, IP, action, entity_type, entity_id, before/after state

Success Criteria

• [x] Provider CRUD interface operational
• [x] Script execution GUI can trigger GitHub Actions
• [x] Database browser can view all 26 tables
• [x] SQL query editor works with read-only safety
• [x] Enhanced match override UI with bulk import
• [x] Admin key management with abuse detection
• [x] User management with role assignment
• [x] System monitoring dashboards live

END OF SPECIFICATION