{
  "meta": {
    "version": "1.0.0",
    "created": "2026-01-27",
    "lastUpdated": "2026-01-27",
    "planType": "phase",
    "phaseId": "PHASE_1",
    "phaseName": "Database + Infrastructure",
    "description": "PostgreSQL models, Redis caching, CardService, and development environment setup",
    "totalEstimatedHours": 40,
    "totalTasks": 18,
    "completedTasks": 18,
    "masterPlan": "../PROJECT_PLAN_MASTER.json"
  },

  "goals": [
    "Establish PostgreSQL as durable storage with async SQLAlchemy",
    "Set up Redis for game state caching and future matchmaking",
    "Implement GameStateManager with write-behind persistence",
    "Create CardService to load bundled JSON into CardDefinitions",
    "Configure environment-based settings (dev/staging/prod)",
    "Docker compose for local development"
  ],

  "architectureNotes": {
    "gameStatePersistence": {
      "strategy": "Write-behind cache",
      "primary": "Redis (fast reads/writes during gameplay)",
      "durable": "PostgreSQL (write at turn boundaries + game end)",
      "recovery": "On restart, load active games from Postgres → Redis"
    },
    "asyncEverywhere": "All DB and Redis operations use async/await",
    "sessionManagement": "Scoped async sessions with proper cleanup"
  },

  "directoryStructure": {
    "db": "backend/app/db/",
    "services": "backend/app/services/",
    "config": "backend/app/config.py",
    "docker": "backend/docker-compose.yml"
  },

  "tasks": [
    {
      "id": "DB-001",
      "name": "Create environment config module",
      "description": "Pydantic Settings class for environment-based configuration (database URLs, Redis URL, secrets)",
      "category": "critical",
      "priority": 1,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": [],
      "files": [
        {"path": "app/config.py", "status": "done"}
      ],
      "details": [
        "Use pydantic-settings for env var parsing",
        "Support .env files for local dev",
        "Separate configs: DATABASE_URL, REDIS_URL, SECRET_KEY, OAUTH_*",
        "Environment detection: dev/staging/prod"
      ],
      "estimatedHours": 2
    },
    {
      "id": "DB-002",
      "name": "Create Docker compose for local dev",
      "description": "Docker compose file with PostgreSQL and Redis services",
      "category": "critical",
      "priority": 2,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-001"],
      "files": [
        {"path": "docker-compose.yml", "status": "done"},
        {"path": ".env.example", "status": "done"}
      ],
      "details": [
        "PostgreSQL 15 with health check",
        "Redis 7 with persistence disabled (dev only)",
        "Volume mounts for data persistence",
        "Network for service communication",
        "Uses ports 5433 (Postgres) and 6380 (Redis) to avoid conflicts"
      ],
      "estimatedHours": 1
    },
    {
      "id": "DB-003",
      "name": "Set up SQLAlchemy async engine",
      "description": "Create async engine, session factory, and base model class",
      "category": "critical",
      "priority": 3,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-001"],
      "files": [
        {"path": "app/db/__init__.py", "status": "done"},
        {"path": "app/db/session.py", "status": "done"},
        {"path": "app/db/base.py", "status": "done"}
      ],
      "details": [
        "AsyncEngine with connection pooling",
        "async_sessionmaker for scoped sessions",
        "Base declarative class with common columns (id, created_at, updated_at)",
        "get_session() async context manager"
      ],
      "estimatedHours": 2
    },
    {
      "id": "DB-004",
      "name": "Create User model",
      "description": "SQLAlchemy model for user accounts with OAuth support",
      "category": "high",
      "priority": 4,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-003"],
      "files": [
        {"path": "app/db/models/user.py", "status": "done"}
      ],
      "details": [
        "Fields: id (UUID), email, display_name, avatar_url",
        "OAuth fields: oauth_provider, oauth_id (composite unique)",
        "Premium fields: is_premium, premium_until (nullable datetime)",
        "Timestamps: created_at, last_login",
        "Index on (oauth_provider, oauth_id)"
      ],
      "estimatedHours": 1.5
    },
    {
      "id": "DB-005",
      "name": "Create Collection model",
      "description": "SQLAlchemy model for player card collections",
      "category": "high",
      "priority": 5,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-004"],
      "files": [
        {"path": "app/db/models/collection.py", "status": "done"}
      ],
      "details": [
        "Fields: id, user_id (FK), card_definition_id (str), quantity",
        "Source tracking: source (enum: starter, booster, reward, purchase)",
        "Timestamps: obtained_at",
        "Unique constraint on (user_id, card_definition_id)",
        "Index on user_id for fast collection lookups"
      ],
      "estimatedHours": 1.5
    },
    {
      "id": "DB-006",
      "name": "Create Deck model",
      "description": "SQLAlchemy model for player decks",
      "category": "high",
      "priority": 6,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-004"],
      "files": [
        {"path": "app/db/models/deck.py", "status": "done"}
      ],
      "details": [
        "Fields: id, user_id (FK), name, cards (JSONB), energy_cards (JSONB)",
        "Validation: is_valid (bool), validation_errors (JSONB nullable)",
        "Metadata: is_starter (bool), starter_type (nullable)",
        "Timestamps: created_at, updated_at",
        "Index on user_id"
      ],
      "estimatedHours": 1.5
    },
    {
      "id": "DB-007",
      "name": "Create CampaignProgress model",
      "description": "SQLAlchemy model for single-player campaign state",
      "category": "high",
      "priority": 7,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-004"],
      "files": [
        {"path": "app/db/models/campaign.py", "status": "done"}
      ],
      "details": [
        "Fields: id, user_id (FK unique), current_club, medals (JSONB)",
        "Progress: defeated_npcs (JSONB), total_wins, total_losses",
        "Economy: booster_packs (int), mantibucks (int)",
        "Timestamps: created_at, updated_at"
      ],
      "estimatedHours": 1.5
    },
    {
      "id": "DB-008",
      "name": "Create ActiveGame model",
      "description": "SQLAlchemy model for in-progress games (Postgres backup)",
      "category": "high",
      "priority": 8,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-003"],
      "files": [
        {"path": "app/db/models/game.py", "status": "done"}
      ],
      "details": [
        "Fields: id (UUID), game_type (enum: campaign, freeplay, ranked)",
        "Players: player1_id (FK), player2_id (FK nullable), npc_id (nullable)",
        "State: rules_config (JSONB), game_state (JSONB), turn_number",
        "Timing: started_at, last_action_at, turn_deadline (nullable)",
        "Index on player IDs for reconnection lookup"
      ],
      "estimatedHours": 2
    },
    {
      "id": "DB-009",
      "name": "Create GameHistory model",
      "description": "SQLAlchemy model for completed games",
      "category": "medium",
      "priority": 9,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-003"],
      "files": [
        {"path": "app/db/models/game.py", "status": "done"}
      ],
      "details": [
        "Fields: id, game_type, player1_id, player2_id, npc_id",
        "Result: winner_id, end_reason, turn_count, duration_seconds",
        "Replay: replay_data (JSONB for future replay feature)",
        "Timestamp: played_at",
        "Indexes for leaderboard queries"
      ],
      "estimatedHours": 1.5
    },
    {
      "id": "DB-010",
      "name": "Create models __init__ and export",
      "description": "Consolidate all models in db/models/__init__.py",
      "category": "medium",
      "priority": 10,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-004", "DB-005", "DB-006", "DB-007", "DB-008", "DB-009"],
      "files": [
        {"path": "app/db/models/__init__.py", "status": "done"}
      ],
      "details": [
        "Import and re-export all models",
        "Import Base for Alembic"
      ],
      "estimatedHours": 0.5
    },
    {
      "id": "DB-011",
      "name": "Set up Alembic migrations",
      "description": "Initialize Alembic and create initial migration",
      "category": "high",
      "priority": 11,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-010"],
      "files": [
        {"path": "alembic.ini", "status": "done"},
        {"path": "app/db/migrations/env.py", "status": "done"},
        {"path": "app/db/migrations/versions/7ac994d6f89c_initial_schema.py", "status": "done"},
        {"path": "app/db/migrations/versions/ab8a0039fe55_allow_null_player1_id.py", "status": "done"}
      ],
      "details": [
        "Configure async Alembic",
        "Auto-generate from SQLAlchemy models",
        "Initial migration with all tables",
        "Add to pyproject.toml dependencies",
        "Black formatting enabled for migrations"
      ],
      "estimatedHours": 2
    },
    {
      "id": "DB-012",
      "name": "Create Redis connection utilities",
      "description": "Async Redis client with connection pooling",
      "category": "high",
      "priority": 12,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-001"],
      "files": [
        {"path": "app/db/redis.py", "status": "done"}
      ],
      "details": [
        "Use redis.asyncio (redis-py)",
        "Connection pool configuration",
        "get_redis() async context manager",
        "Helper methods: get_json, set_json, delete"
      ],
      "estimatedHours": 1.5
    },
    {
      "id": "DB-013",
      "name": "Create GameStateManager",
      "description": "Redis-primary, Postgres-backup game state management",
      "category": "critical",
      "priority": 13,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-012", "DB-008"],
      "files": [
        {"path": "app/services/game_state_manager.py", "status": "done"}
      ],
      "details": [
        "load_state(): Try Redis, fallback to Postgres",
        "save_to_cache(): Write to Redis only (fast path)",
        "persist_to_db(): Write to Postgres (turn boundaries)",
        "delete_game(): Clean up Redis + Postgres",
        "recover_active_games(): Load from Postgres → Redis on startup",
        "Key format: game:{game_id}"
      ],
      "estimatedHours": 3
    },
    {
      "id": "DB-014",
      "name": "Create CardService",
      "description": "Load card definitions from bundled JSON files",
      "category": "critical",
      "priority": 14,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": [],
      "files": [
        {"path": "app/services/card_service.py", "status": "done"},
        {"path": "scripts/scrape_pokemon_pocket.py", "status": "done"},
        {"path": "scripts/convert_cards.py", "status": "done"},
        {"path": "data/definitions/", "status": "done"},
        {"path": "data/raw/", "status": "done"}
      ],
      "details": [
        "Load from data/definitions/_index.json on startup",
        "Parse JSON → CardDefinition models",
        "get_card(card_id) → CardDefinition",
        "get_cards_by_ids(card_ids) → dict[str, CardDefinition]",
        "get_set_cards(set_code) → list[CardDefinition]",
        "search(filters) for deck building UI",
        "Includes scraper and converter scripts for card data pipeline",
        "382 cards total (372 scraped + 10 basic energy)"
      ],
      "estimatedHours": 3,
      "notes": "Includes complete card data pipeline with scraper that properly extracts energy types from HTML"
    },
    {
      "id": "DB-015",
      "name": "Create database tests",
      "description": "Tests for models, sessions, and basic CRUD",
      "category": "high",
      "priority": 15,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-011"],
      "files": [
        {"path": "tests/db/__init__.py", "status": "done"},
        {"path": "tests/db/conftest.py", "status": "done"},
        {"path": "tests/db/test_models.py", "status": "done"},
        {"path": "tests/db/test_relationships.py", "status": "done"}
      ],
      "details": [
        "Uses real Postgres via testcontainers pattern (sync psycopg2 for fixtures)",
        "Test each model: create, read, update, delete",
        "Test relationships and constraints",
        "Test JSONB serialization",
        "Test cascade deletes and relationship integrity"
      ],
      "estimatedHours": 4
    },
    {
      "id": "DB-016",
      "name": "Create GameStateManager tests",
      "description": "Tests for Redis + Postgres state management",
      "category": "high",
      "priority": 16,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-013", "DB-015"],
      "files": [
        {"path": "tests/services/test_game_state_manager.py", "status": "done"},
        {"path": "tests/services/conftest.py", "status": "done"}
      ],
      "details": [
        "Uses fakeredis for Redis mocking",
        "Test cache hit (Redis has state)",
        "Test cache miss (fallback to Postgres)",
        "Test persist_to_db writes correctly",
        "Test recovery on startup",
        "Test cleanup on game end"
      ],
      "estimatedHours": 3
    },
    {
      "id": "DB-017",
      "name": "Create CardService tests",
      "description": "Tests for card loading and lookup",
      "category": "high",
      "priority": 17,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": ["DB-014"],
      "files": [
        {"path": "tests/services/test_card_service.py", "status": "done"},
        {"path": "tests/scripts/test_convert_cards.py", "status": "done"}
      ],
      "details": [
        "Test loads all cards from JSON",
        "Test get_card returns correct definition",
        "Test get_cards_by_ids batch lookup",
        "Test search with filters (type, set, rarity, stage, variant)",
        "Test handles missing card gracefully",
        "Test converter script validation and evolution chains"
      ],
      "estimatedHours": 2
    },
    {
      "id": "DB-018",
      "name": "Update dependencies in pyproject.toml",
      "description": "Add all required packages for Phase 1",
      "category": "high",
      "priority": 18,
      "completed": true,
      "completedDate": "2026-01-27",
      "dependencies": [],
      "files": [
        {"path": "pyproject.toml", "status": "done"}
      ],
      "details": [
        "sqlalchemy[asyncio] >= 2.0",
        "asyncpg (Postgres async driver)",
        "alembic",
        "redis >= 5.0 (async support)",
        "pydantic-settings",
        "psycopg2-binary (for test fixtures)",
        "fakeredis[lua] (for Redis mocking in tests)"
      ],
      "estimatedHours": 0.5
    }
  ],

  "testingStrategy": {
    "approach": "Real Postgres via Docker + fakeredis for Redis",
    "fixtures": "Async fixtures in conftest.py with sync psycopg2 for cleanup",
    "coverage": "974 tests passing, high coverage on new code"
  },

  "weeklyRoadmap": {
    "week1": {
      "theme": "Foundation",
      "tasks": ["DB-001", "DB-002", "DB-003", "DB-018"],
      "goals": ["Dev environment running", "SQLAlchemy configured"],
      "status": "complete"
    },
    "week2": {
      "theme": "Models + Migrations",
      "tasks": ["DB-004", "DB-005", "DB-006", "DB-007", "DB-008", "DB-009", "DB-010", "DB-011"],
      "goals": ["All models defined", "Migrations working"],
      "status": "complete"
    },
    "week3": {
      "theme": "Services + Testing",
      "tasks": ["DB-012", "DB-013", "DB-014", "DB-015", "DB-016", "DB-017"],
      "goals": ["GameStateManager working", "CardService working", "Full test coverage"],
      "status": "complete"
    }
  },

  "acceptanceCriteria": [
    {"criterion": "docker-compose up starts Postgres + Redis locally", "met": true},
    {"criterion": "Alembic migrations run successfully", "met": true},
    {"criterion": "All models have CRUD tests passing", "met": true},
    {"criterion": "GameStateManager persists at turn boundaries", "met": true},
    {"criterion": "GameStateManager recovers from Postgres on restart", "met": true},
    {"criterion": "CardService loads all 382 cards from JSON", "met": true, "notes": "372 scraped + 10 basic energy"},
    {"criterion": "All tests pass with testcontainers", "met": true, "notes": "974 tests passing"}
  ],

  "completionSummary": {
    "status": "COMPLETE",
    "completedDate": "2026-01-27",
    "totalTests": 974,
    "keyDeliverables": [
      "Full async SQLAlchemy infrastructure with 6 models",
      "GameStateManager with Redis cache + Postgres persistence",
      "CardService loading 382 card definitions",
      "Complete card data pipeline (scraper + converter)",
      "Comprehensive test suite with real database testing",
      "FastAPI app with lifespan hooks for startup/shutdown"
    ],
    "notableImplementationDetails": [
      "Uses ports 5433/6380 to avoid conflicts with existing services",
      "Scraper properly extracts energy types from HTML spans",
      "pytest-asyncio fixtures use sync psycopg2 for reliable cleanup",
      "fakeredis used for Redis mocking in service tests",
      "FastAPI lifespan wires init_db, init_redis, CardService.load_all",
      "get_session_dependency exported for FastAPI dependency injection",
      "game_cache_ttl_seconds configurable via Settings",
      "datetime.utcnow() replaced with datetime.now(UTC)"
    ],
    "gapsFixedPostReview": [
      "Added FastAPI lifespan hooks (startup/shutdown)",
      "Exported get_session_dependency from app/db/__init__.py",
      "Made game cache TTL configurable via Settings",
      "Fixed datetime.utcnow() deprecation (4 occurrences)",
      "Added /health/ready endpoint for readiness checks",
      "Added CORS middleware"
    ]
  },

  "phase2Prerequisites": {
    "description": "Items identified during Phase 1 review that should be addressed in Phase 2",
    "items": [
      {
        "name": "Auth infrastructure (JWT)",
        "description": "Add JWT encode/decode utilities using secret_key from Settings",
        "priority": "high"
      },
      {
        "name": "python-socketio integration",
        "description": "Install python-socketio and create WebSocket handlers for real-time gameplay",
        "priority": "high"
      }
    ]
  }
}
