import 'package:drift/drift.dart';
import 'package:drift_flutter/drift_flutter.dart';
import 'package:uto_manager/infrastructure/database/daos/personnel_dao.dart';
import 'package:uto_manager/infrastructure/database/daos/position_slot_dao.dart';
import 'package:uto_manager/infrastructure/database/daos/soldier_positions_dao.dart';
import 'package:uto_manager/infrastructure/database/daos/template_dao.dart';
import 'package:uto_manager/infrastructure/database/daos/unit_dao.dart';

part 'app_database.g.dart';

// ---------------------------------------------------------------------------
// Table Definitions
// ---------------------------------------------------------------------------

/// Drift table for organizational unit nodes.
class Units extends Table {
  TextColumn get unitId => text().named('unit_id')();
  TextColumn get echelon => text()();
  TextColumn get branch => text()();
  TextColumn get parentUnitId => text().named('parent_unit_id').nullable()();
  TextColumn get name => text()();
  BoolColumn get isKeyLeader => boolean().named('is_key_leader')();

  @override
  Set<Column> get primaryKey => {unitId};
}

/// Drift table for authorized duty position slots.
class PositionSlots extends Table {
  TextColumn get slotId => text().named('slot_id')();
  TextColumn get unitId => text().named('unit_id').references(Units, #unitId)();
  TextColumn get positionTitle => text().named('position_title')();
  TextColumn get dutyMos => text().named('duty_mos')();
  TextColumn get authorizedRank => text().named('authorized_rank')();
  BoolColumn get isKeyLeader => boolean().named('is_key_leader')();
  TextColumn get assignedPersonnelId => text().named('assigned_personnel_id').nullable()();

  @override
  Set<Column> get primaryKey => {slotId};
}

/// Drift table for soldier personnel records.
class Personnel extends Table {
  TextColumn get soldierId => text().named('soldier_id')();
  TextColumn get name => text().nullable()();
  TextColumn get positionTitle => text().named('position_title').nullable()();
  TextColumn get primaryMos => text().named('primary_mos')();
  TextColumn get payGrade => text().named('pay_grade')();
  TextColumn get qualifications => text()(); // JSON-encoded list
  TextColumn get readinessState => text().named('readiness_state')();
  TextColumn get assignedSlotId => text().named('assigned_slot_id').nullable()();

  @override
  Set<Column> get primaryKey => {soldierId};
}

/// Drift table for user-managed soldier duty positions.
class SoldierPositions extends Table {
  TextColumn get positionId => text().named('position_id')();
  TextColumn get title => text()();
  BoolColumn get isBuiltIn => boolean().named('is_built_in').withDefault(const Constant(false))();

  @override
  Set<Column> get primaryKey => {positionId};
}

/// Drift table for serialized unit tree templates.
class Templates extends Table {
  TextColumn get templateId => text().named('template_id')();
  TextColumn get name => text()();
  TextColumn get bctType => text().named('bct_type')();
  TextColumn get jsonData => text().named('json_data')();
  DateTimeColumn get createdAt => dateTime().named('created_at')();
  DateTimeColumn get updatedAt => dateTime().named('updated_at')();

  @override
  Set<Column> get primaryKey => {templateId};
}

// ---------------------------------------------------------------------------
// Database
// ---------------------------------------------------------------------------

/// The main Drift database for the UTO Manager application.
///
/// Manages all core tables: units, position_slots, personnel, soldier_positions, templates.
/// Supports SQLite on Windows/Android and IndexedDB on Web via drift_web.
@DriftDatabase(
  tables: [Units, PositionSlots, Personnel, SoldierPositions, Templates],
  daos: [UnitDao, PersonnelDao, PositionSlotDao, SoldierPositionsDao, TemplateDao],
)
class AppDatabase extends _$AppDatabase {
  /// Creates the production database backed by SQLite on disk.
  AppDatabase() : super(_openConnection());

  /// Constructor for in-memory database (used in tests).
  AppDatabase.forTesting(super.executor);

  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onCreate: (m) async {
          await m.createAll();
        },
        onUpgrade: (m, from, to) async {
          if (from < 2) {
            // v1 → v2: make Personnel.name nullable, add position_title column,
            // and create the new soldier_positions table.
            await m.addColumn(personnel, personnel.positionTitle);
            await m.createTable(soldierPositions);
          }
        },
      );
}

/// Opens a platform-adaptive database connection.
///
/// Uses SQLite via FFI on Windows/Android and WASM/IndexedDB on Web.
QueryExecutor _openConnection() {
  return driftDatabase(
    name: 'uto_manager',
    web: DriftWebOptions(
      sqlite3Wasm: Uri.parse('sqlite3.wasm'),
      driftWorker: Uri.parse('drift_worker.js'),
    ),
  );
}