TLW Database Tool

CLI tool for Tippliga Würzburg football prediction league administration. Syncs match data from API-Football, manages a MySQL database backing a phpBB forum, pushes Google Calendar events, sends WhatsApp reminders with AI-generated messages via OpenAI, and uploads fixture data to S3.

Architecture

App.java (CLI entry with argparse4j)
├── apifootball/        — API-Football HTTP client, match model, S3 caching
│   ├── APIFootballUpdater       Fetch raw fixtures/rounds → S3
│   ├── APIFootballConnector     Singleton, reads cached S3 data, resolves matchdays
│   ├── APIFootballMatch         API-Football match model (extends BaseMatch)
│   └── APIFootballMatchesProvider  Resolves config entries to match lists
├── googlecalendar/     — Google Calendar API CRUD
│   ├── GoogleCalendarConnector     OAuth2 + service setup
│   ├── CalendarConfigProvider      Reads calendar ID/URL from JSON resource
│   └── TippligaGoogleEventManager  Create/update/delete matchday events
├── teamidmatcher/      — Bi-directional ID mapping
│   ├── TeamIDMatcher              HashBiMap: Tippliga team ID ↔ API-Football ID
│   └── TeamIDMatcherTemplateCreator  Generates config template from API teams
├── tippliga/           — Domain models & operations
│   ├── TLWMatch / TLWMatchday / TLWTeam / TLWLeague  (DB-mapped models)
│   ├── TLWMatchesCreatorFootball     Build match schedule from API data
│   ├── TLWMatchesCreatorTipperLeague  Build tipper-vs-tipper league matches
│   ├── TLWMatchesCreatorTipperPokal   Build knockout tournament matches
│   ├── TLWMatchesUpdaterFootball      Update teams/datetimes from API
│   ├── TLWMatchesResultsUpdater       Push finished match results to website
│   ├── TLWMatchdaysCreator            Compute delivery dates from match times
│   ├── TLWMatchdaysUpdater            Write delivery date updates to DB + Calendar
│   ├── TLWTeamsCreator / TLWTeamsUpdater  Team management
│   └── TLWMatchesManagerBase         Shared helpers (date math, match matching)
├── tippligaforum/      — MySQL + phpBB integration
│   ├── TippligaSQLConnector          Singleton JDBC connector (all queries)
│   ├── TippligaConfigProvider         Reads JSON config from forum posts
│   ├── TippligaWebsiteConnector       HTTP client for admin result updates
│   └── MatchesListCreator / MatchesListForumUpdater  Fixture list posts
└── whatsapp/           — WhatsApp reminder system
    ├── WhatsAppNotifier              Sends messages via WhatsApp API
    ├── OpenAIConnector               Generates reminder text via GPT-3.5
    └── WhatsAppReminder              Record: user, matchday, missing bets

BaseMatch              — Abstract match with status/comparison/shared fields
S3Provider             — AWS S3 read/write for cached API-Football JSON
ResourceProvider       — Reads JSON config files from classpath resources
StatusHolder           — Global error flag

Setup

Prerequisites

Java 17+
MySQL database (phpBB with Tippliga extension)
API-Football API key (free tier)
Google Calendar API service account + credentials file
AWS S3 bucket for fixture caching
WhatsApp API endpoint

Environment Variables

Variable	Description
`TLW_DATABASE_USERNAME`	MySQL username
`TLW_DATABASE_PASSWORD`	MySQL password
`FORUM_USERNAME`	phpBB admin username (for website connector)
`FORUM_PASSWORD`	phpBB admin password
`OPENAI_TOKEN`	OpenAI API token (GPT reminders)
`TLW_WHATSAPP_HOST`	WhatsApp API host
`TLW_WHATSAPP_PORT`	WhatsApp API port
`TLW_WHATSAPP_API_KEY`	WhatsApp API key
`AWS_ACCESS_KEY_ID`	AWS credentials (S3)
`AWS_SECRET_ACCESS_KEY`	AWS credentials (S3)

Resources

Place these in src/main/resources/:

Tippliga/Team_ID_Matcher_Config.json — team ID mapping
Tippliga/Ligen_v3.json — league definitions
Google_Calendar_Config.json — calendar IDs
Tippliga_Configs/<config>.json — per-season match configs

Configs are stored as phpBB forum posts (JSON wrapped in [code] BBCode).

Usage

# Build fat JAR
./gradlew jar

# Run modes (all require -s <season> -l <league> -c <configFile>)
java -jar build/libs/tlw-database-tool-1.0.jar -m MatchdaysUpdater -s 2025 -l 1 -c tl_2025

CLI Modes

Mode	Function
`MatchdaysUpdater`	Update delivery dates from match times, sync Google Calendar
`MatchesCreatorFootball`	Create match schedule from API-Football
`MatchesUpdaterFootball`	Update match teams/datetimes from API-Football
`MatchesResultsUpdater`	Push finished results to website
`TeamsUpdater`	Add missing teams to DB
`APIFootballUpdater`	Fetch raw fixtures/rounds → S3
`MatchesListGistUpdater`	Update forum posts with fixture lists
`PostChecksum`	Print MD5 checksum of a config post
`WhatsAppNotifier`	Send WhatsApp reminders for missing bets

Build Commands

./gradlew build          # compile + test + jar
./gradlew test           # run all tests
./gradlew clean test     # clean build + test
./gradlew jar            # build fat JAR
./gradlew dependencies   # list dependency tree

Code Conventions

Package: de.jeyp91.*
SQL columns referenced by ordinal position in ResultSet (constants like final int SEASON = 1)
Logger via SLF4J: LoggerFactory.getLogger(Class.class)
Match status constants inherited from BaseMatch
Config JSON stored in phpBB forum posts, loaded via TippligaConfigProvider
Secrets in environment variables only

Testing

JUnit 4 + TestNG
Tests under src/test/java/de/jeyp91/
Run single test: ./gradlew test --tests "de.jeyp91.apifootball.APIFootballMatchTest"

How It Works (Data Flow)

APIFootballUpdater fetches fixtures/rounds from API-Football → caches in S3
APIFootballConnector reads cached S3 data, resolves API matchdays to Tippliga matchdays
TLWMatchesCreatorFootball config on which API matches map to which Tippliga matchdays → SQL INSERT
TLWMatchdaysCreator computes delivery deadlines from match times
TLWMatchdaysUpdater writes deadlines to DB + creates/updates Google Calendar events
TLWMatchesUpdaterFootball updates team IDs, datetimes from latest API data
TLWMatchesResultsUpdater pushes finished match results to the website via HTTP
WhatsAppNotifier queries DB for users with missing bets, generates GPT reminders, sends via WhatsApp API

6.6 KiB Raw Permalink Blame History