focusflow_api/bin/migrate.dart

import 'dart:io';

import 'package:logging/logging.dart';

import 'package:focusflow_api/src/config/database.dart';
import 'package:focusflow_api/src/config/env.dart';

final _log = Logger('Migrate');

/// Run database migrations.
///
/// Usage: dart run bin/migrate.dart
Future<void> main() async {
  Logger.root.level = Level.ALL;
  Logger.root.onRecord.listen((record) {
    // ignore: avoid_print
    print(
      '${record.time} [${record.level.name}] ${record.loggerName}: '
      '${record.message}',
    );
  });

  Env.init();
  await Database.init();

  _log.info('Running migrations...');

  try {
    // ── Users ──────────────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS users (
        id           TEXT PRIMARY KEY,
        email        TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        display_name TEXT NOT NULL,
        deleted_at   TIMESTAMPTZ,
        created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    ''');
    _log.info('  users: OK');

    // ── Refresh tokens ────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS refresh_tokens (
        id         TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL REFERENCES users(id),
        token      TEXT NOT NULL UNIQUE,
        expires_at TIMESTAMPTZ NOT NULL,
        revoked    BOOLEAN NOT NULL DEFAULT FALSE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    ''');
    _log.info('  refresh_tokens: OK');

    // ── Tasks ─────────────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS tasks (
        id                TEXT PRIMARY KEY,
        user_id           TEXT NOT NULL REFERENCES users(id),
        title             TEXT NOT NULL,
        description       TEXT,
        status            TEXT NOT NULL DEFAULT 'pending',
        priority          TEXT NOT NULL DEFAULT 'medium',
        energy_level      INT NOT NULL DEFAULT 3,
        estimated_minutes INT DEFAULT 25,
        actual_minutes    INT,
        due_date          TIMESTAMPTZ,
        tags              TEXT[] DEFAULT '{}',
        times_postponed   INT NOT NULL DEFAULT 0,
        last_interacted_at TIMESTAMPTZ,
        completed_at      TIMESTAMPTZ,
        deleted_at        TIMESTAMPTZ,
        created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    ''');
    _log.info('  tasks: OK');

    // ── Streaks ───────────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS streaks (
        id             TEXT PRIMARY KEY,
        user_id        TEXT NOT NULL REFERENCES users(id),
        name           TEXT NOT NULL,
        description    TEXT,
        frequency      TEXT NOT NULL DEFAULT 'daily',
        grace_days     INT NOT NULL DEFAULT 1,
        current_count  INT NOT NULL DEFAULT 0,
        longest_count  INT NOT NULL DEFAULT 0,
        frozen_until   TIMESTAMPTZ,
        created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    ''');
    _log.info('  streaks: OK');

    // ── Streak entries ────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS streak_entries (
        id          TEXT PRIMARY KEY,
        streak_id   TEXT NOT NULL REFERENCES streaks(id),
        entry_date  DATE NOT NULL,
        entry_type  TEXT NOT NULL DEFAULT 'completion',
        note        TEXT,
        created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        UNIQUE(streak_id, entry_date)
      );
    ''');
    _log.info('  streak_entries: OK');

    // ── Rewards ───────────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS rewards (
        id          TEXT PRIMARY KEY,
        user_id     TEXT NOT NULL REFERENCES users(id),
        task_id     TEXT REFERENCES tasks(id),
        points      INT NOT NULL,
        magnitude   DOUBLE PRECISION NOT NULL,
        visual_type TEXT NOT NULL,
        is_surprise BOOLEAN NOT NULL DEFAULT FALSE,
        breakdown   TEXT,
        created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
    ''');
    _log.info('  rewards: OK');

    // ── Time entries ──────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS time_entries (
        id                TEXT PRIMARY KEY,
        user_id           TEXT NOT NULL REFERENCES users(id),
        task_id           TEXT NOT NULL REFERENCES tasks(id),
        estimated_minutes INT,
        actual_minutes    INT,
        created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        UNIQUE(task_id)
      );
    ''');
    _log.info('  time_entries: OK');

    // ── Sync log ──────────────────────────────────────────────────────
    await Database.query('''
      CREATE TABLE IF NOT EXISTS sync_log (
        id          TEXT PRIMARY KEY,
        user_id     TEXT NOT NULL REFERENCES users(id),
        entity_type TEXT NOT NULL,
        entity_id   TEXT NOT NULL,
        operation   TEXT NOT NULL,
        data        TEXT,
        version     INT NOT NULL DEFAULT 0,
        synced_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        UNIQUE(entity_type, entity_id, user_id)
      );
    ''');
    _log.info('  sync_log: OK');

    // ── Indexes ───────────────────────────────────────────────────────
    await Database.query('''
      CREATE INDEX IF NOT EXISTS idx_tasks_user_status
        ON tasks(user_id, status) WHERE deleted_at IS NULL;
    ''');
    await Database.query('''
      CREATE INDEX IF NOT EXISTS idx_tasks_user_due
        ON tasks(user_id, due_date) WHERE deleted_at IS NULL;
    ''');
    await Database.query('''
      CREATE INDEX IF NOT EXISTS idx_streaks_user
        ON streaks(user_id);
    ''');
    await Database.query('''
      CREATE INDEX IF NOT EXISTS idx_rewards_user
        ON rewards(user_id, created_at);
    ''');
    await Database.query('''
      CREATE INDEX IF NOT EXISTS idx_sync_log_user_time
        ON sync_log(user_id, synced_at);
    ''');
    _log.info('  indexes: OK');

    _log.info('All migrations complete!');
  } catch (e, st) {
    _log.severe('Migration failed', e, st);
  } finally {
    await Database.close();
    exit(0);
  }
}