// gemi_invoice/lib/data/database.dart
// version: 1.4.0 (Fix: Added missing types and methods for List Screen)

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;
import 'dart:io';

part 'database.g.dart';

class Customers extends Table {
  TextColumn get id => text()();
  TextColumn get displayName => text()();
  TextColumn get formalName => text()();
  TextColumn get address => text().nullable()();
  TextColumn get zipCode => text().nullable()();
  TextColumn get department => text().nullable()();
  RealColumn get latitude => real().nullable()();
  RealColumn get longitude => real().nullable()();
  DateTimeColumn get lastUpdatedAt => dateTime().nullable()();
  @override
  Set<Column> get primaryKey => {id};
}

class Products extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  IntColumn get defaultPrice => integer()();
  TextColumn get category => text().nullable()();
}

class Invoices extends Table {
  TextColumn get id => text()();
  TextColumn get customerId => text().references(Customers, #id)();
  DateTimeColumn get date => dateTime()();
  TextColumn get type => text()();
  TextColumn get filePath => text().nullable()();
  TextColumn get notes => text().nullable()();
  IntColumn get totalAmount => integer()();
  @override
  Set<Column> get primaryKey => {id};
}

class InvoiceItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get invoiceId => text().references(Invoices, #id)();
  TextColumn get description => text()();
  IntColumn get quantity => integer()();
  IntColumn get unitPrice => integer()();
}

// ★ここが重要：一覧画面で使うための「セット」の定義
class InvoiceWithCustomer {
  final Invoices invoice;
  final Customers customer;
  InvoiceWithCustomer(this.invoice, this.customer);
}

@DriftDatabase(tables: [Customers, Products, Invoices, InvoiceItems])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  // --- DAOメソッド ---

  // ★ここが重要：履歴一覧画面で使う「監視用」メソッド
  Stream<List<InvoiceWithCustomer>> watchAllInvoices() {
    final query = select(
      invoices,
    ).join([innerJoin(customers, customers.id.equalsExp(invoices.customerId))]);
    query.orderBy([OrderingTerm.desc(invoices.date)]);
    return query.watch().map((rows) {
      return rows.map((row) {
        return InvoiceWithCustomer(
          row.readTable(invoices),
          row.readTable(customers),
        );
      }).toList();
    });
  }

  Future<void> saveFullInvoice(
    InvoicesCompanion invoice,
    List<InvoiceItemsCompanion> items,
  ) async {
    await transaction(() async {
      await into(invoices).insertOnConflictUpdate(invoice);
      await (delete(
        invoiceItems,
      )..where((t) => t.invoiceId.equals(invoice.id.value))).go();
      for (var item in items) {
        await into(invoiceItems).insert(item);
      }
    });
  }

  Future<List<Customer>> getNearbyCustomers(double lat, double lon) {
    return select(customers).get();
  }
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return NativeDatabase(file);
  });
}