import 'dart:io';

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

final _log = Logger('Migrate');

/// Database migration runner.
///
/// Run with: dart run bin/migrate.dart
///
/// This applies all migrations in order. Each migration is idempotent
/// (uses IF NOT EXISTS) so it is safe to re-run.
void main(List<String> args) async {
  Logger.root.level = Level.ALL;
  Logger.root.onRecord.listen((record) {
    stderr.writeln(
        '${record.time} [${record.level.name}] ${record.loggerName}: ${record.message}');
  });

  _log.info('Starting migrations (env=${Env.environment})...');

  await Database.initialize();

  try {
    for (var i = 0; i < _migrations.length; i++) {
      _log.info('Running migration ${i + 1}/${_migrations.length}: ${_migrationNames[i]}');
      await Database.execute(_migrations[i]);
    }

    _log.info('All migrations applied successfully');
  } catch (e, st) {
    _log.severe('Migration failed', e, st);
    exit(1);
  } finally {
    await Database.close();
  }
}

const _migrationNames = [
  'Create users table',
  'Create refresh_tokens table',
  'Create recipes table',
  'Create ingredients table',
  'Create steps table',
  'Create saved_recipes table',
  'Create reviews table',
];

const _migrations = [
  // 1. Users
  '''
  CREATE TABLE IF NOT EXISTS users (
    id          TEXT PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    username    TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    bio         TEXT,
    avatar_url  TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
  )
  ''',

  // 2. Refresh tokens
  '''
  CREATE TABLE IF NOT EXISTS refresh_tokens (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  TEXT NOT NULL,
    expires_at  TIMESTAMPTZ NOT NULL,
    revoked     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
  )
  ''',

  // 3. Recipes
  '''
  CREATE TABLE IF NOT EXISTS recipes (
    id              TEXT PRIMARY KEY,
    user_id         TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    description     TEXT,
    prep_time       INTEGER,
    cook_time       INTEGER,
    servings        INTEGER,
    difficulty      TEXT,
    cuisine         TEXT,
    tags            TEXT[] DEFAULT '{}',
    image_url       TEXT,
    is_ai_generated BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ
  )
  ''',

  // 4. Ingredients
  '''
  CREATE TABLE IF NOT EXISTS ingredients (
    id          TEXT PRIMARY KEY,
    recipe_id   TEXT NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    quantity    TEXT,
    unit        TEXT,
    sort_order  INTEGER NOT NULL DEFAULT 0
  )
  ''',

  // 5. Steps
  '''
  CREATE TABLE IF NOT EXISTS steps (
    id                TEXT PRIMARY KEY,
    recipe_id         TEXT NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
    step_number       INTEGER NOT NULL,
    instruction       TEXT NOT NULL,
    duration_minutes  INTEGER
  )
  ''',

  // 6. Saved recipes (bookmarks)
  '''
  CREATE TABLE IF NOT EXISTS saved_recipes (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    recipe_id   TEXT NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, recipe_id)
  )
  ''',

  // 7. Reviews
  '''
  CREATE TABLE IF NOT EXISTS reviews (
    id          TEXT PRIMARY KEY,
    recipe_id   TEXT NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating      INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment     TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
  )
  ''',
];