Fix: Implement Accurate Hackathon Results Page with Time-Based Filtering

[IgorShadurin]

Overview

Currently, the hackathon results page may show incorrect winners if applications are created after the hackathon end date. We need to implement proper time-based filtering and create a dedicated results page that shows the accurate final standings.

Current Issues

• Results can be affected by apps created after hackathon end
• No clear distinction between hackathon and post-hackathon submissions
• Potential for manipulation of results
• Lack of historical record of final standings

Required Changes

1. Database Updates

-- Add timestamps to track submission time
ALTER TABLE hackathon_submissions
ADD COLUMN submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add hackathon time constraints
CREATE TABLE hackathon_periods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,
    voting_end_date TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

2. Backend Implementation

API Endpoints

interface HackathonResults {
  submissions: {
    id: number;
    appName: string;
    creatorAddress: string;
    submittedAt: Date;
    score: number;
    rank: number;
    // other relevant fields
  }[];
  hackathonInfo: {
    name: string;
    startDate: Date;
    endDate: Date;
    votingEndDate: Date;
    totalParticipants: number;
    totalSubmissions: number;
  };
}

// New API endpoints
GET /api/hackathon/:id/results
GET /api/hackathon/:id/submissions
GET /api/hackathon/active

Results Logic

const getHackathonResults = async (hackathonId: number): Promise<HackathonResults> => {
  const hackathon = await getHackathonPeriod(hackathonId);
  
  // Only include submissions within the hackathon period
  const submissions = await prisma.submissions.findMany({
    where: {
      submittedAt: {
        gte: hackathon.startDate,
        lte: hackathon.endDate
      },
      // Additional filters
    },
    orderBy: {
      score: 'desc'
    }
  });

  // Calculate rankings
  const rankedSubmissions = submissions.map((sub, index) => ({
    ...sub,
    rank: index + 1
  }));

  return {
    submissions: rankedSubmissions,
    hackathonInfo: {
      ...hackathon,
      totalParticipants: await getUniqueParticipants(hackathonId),
      totalSubmissions: submissions.length
    }
  };
};

3. Frontend Implementation

Results Page Layout

┌─────────────────────────────────┐
│ Hackathon Results               │
├─────────────────────────────────┤
│ Status: Completed              │
│ Period: Mar 1 - Mar 15, 2024   │
│                               │
│ Final Standings:               │
├─────────────────────────────────┤
│ 🥇 1. App Name                 │
│    Score: 95                   │
│    Creator: 0x1234...5678      │
├─────────────────────────────────┤
│ 🥈 2. App Name                 │
│    Score: 90                   │
│    Creator: 0x2345...6789      │
└─────────────────────────────────┘

4. Implementation Tasks

• Update database schema
• Create new API endpoints
• Implement time-based filtering
• Create dedicated results page
• Add results caching mechanism
• Implement vote counting logic
• Add admin controls for managing results
• Create historical results view

5. Required Features

1. Time-based Filtering

   • Filter submissions by hackathon period
   • Consider submission timestamps
   • Handle timezone differences

2. Results Calculation

   • Score calculation based on votes
   • Proper ranking algorithm
   • Tiebreaker handling

3. Results Display

   • Clear winner presentation
   • Submission details
   • Voting statistics
   • Time period information

4. Admin Controls

   • Ability to lock results
   • Manual override if needed
   • Results verification tools

6. Data Model

interface HackathonPeriod {
  id: number;
  name: string;
  startDate: Date;
  endDate: Date;
  votingEndDate: Date;
  isActive: boolean;
}

interface Submission {
  id: number;
  appId: number;
  hackathonId: number;
  creatorAddress: string;
  submittedAt: Date;
  lastUpdatedAt: Date;
  score: number;
  votes: Vote[];
}

interface Vote {
  id: number;
  submissionId: number;
  voterAddress: string;
  votedAt: Date;
  value: number;
}

7. Security Considerations

• Prevent post-hackathon submission manipulation
• Secure vote counting
• Admin access controls
• Data integrity checks

8. Performance Optimization

• Cache final results
• Optimize database queries
• Implement pagination for large result sets
• Background job for results calculation

9. Testing Requirements

• Test time-based filtering
• Test vote counting accuracy
• Test ranking algorithm
• Test edge cases (ties, no submissions)
• Test admin controls
• Performance testing with large datasets

10. Success Criteria

• Results show only submissions from hackathon period
• Rankings are calculated correctly
• Results page loads quickly
• Admin controls work properly
• Historical results are preserved
• All tests pass

Timeline

• Database updates: 0.5 day
• Backend implementation: 1 day
• Frontend implementation: 1 day
• Testing and refinement: 0.5 day

Notes

• Consider implementing result snapshots
• Add export functionality for results
• Consider adding detailed statistics
• Plan for future hackathons
• Document ranking algorithm
• Add audit trail for changes