import 'package:drift/drift.dart';
import '../database.dart';
import '../tables.dart';

part 'month_dao.g.dart';

/// Holds all entities for a single month, loaded from the database.
class MonthData {
  final MonthPlan plan;
  final List<PaycheckWithItems> paychecks;
  final List<BillDefinition> bills;
  final List<Payout> priorityPayouts;
  final List<Payout> notablePayouts;
  final List<MiscItem> miscItems;

  const MonthData({
    required this.plan,
    required this.paychecks,
    required this.bills,
    required this.priorityPayouts,
    required this.notablePayouts,
    required this.miscItems,
  });
}

class PaycheckWithItems {
  final PaycheckDefinition paycheck;
  final List<PayPeriodItem> items;

  const PaycheckWithItems({required this.paycheck, required this.items});
}

@DriftAccessor(tables: [
  MonthPlans,
  PaycheckDefinitions,
  PayPeriodItems,
  BillDefinitions,
  PaymentRules,
  Payouts,
  MiscItems,
])
class MonthDao extends DatabaseAccessor<AppDatabase> with _$MonthDaoMixin {
  MonthDao(super.db);

  // ── Month CRUD ──

  Stream<List<MonthPlan>> watchAllMonths() {
    return (select(monthPlans)..orderBy([(t) => OrderingTerm.asc(t.year), (t) => OrderingTerm.asc(t.month)])).watch();
  }

  Future<MonthPlan?> getMonth(int year, int month) {
    return (select(monthPlans)..where((t) => t.year.equals(year) & t.month.equals(month))).getSingleOrNull();
  }

  Future<MonthPlan> getOrCreateCurrentMonth() async {
    final now = DateTime.now();
    final existing = await getMonth(now.year, now.month);
    if (existing != null) return existing;
    final id = await into(monthPlans).insert(MonthPlansCompanion.insert(
      year: now.year,
      month: now.month,
    ));
    return (select(monthPlans)..where((t) => t.id.equals(id))).getSingle();
  }

  Future<int> createMonth(int year, int month, {int? bufferAmountCents}) async {
    return into(monthPlans).insert(MonthPlansCompanion.insert(
      year: year,
      month: month,
      bufferAmountCents: bufferAmountCents != null ? Value(bufferAmountCents) : const Value.absent(),
    ));
  }

  Future<void> updateMonthPlan(int id, MonthPlansCompanion companion) {
    return (update(monthPlans)..where((t) => t.id.equals(id))).write(companion);
  }

  Future<void> deleteMonth(int id) async {
    final pcIds = await (select(paycheckDefinitions)..where((t) => t.monthPlanId.equals(id))).map((r) => r.id).get();
    if (pcIds.isNotEmpty) {
      await (delete(payPeriodItems)..where((t) => t.paycheckId.isIn(pcIds))).go();
    }
    await (delete(paycheckDefinitions)..where((t) => t.monthPlanId.equals(id))).go();
    await (delete(billDefinitions)..where((t) => t.monthPlanId.equals(id))).go();
    await (delete(paymentRules)..where((t) => t.monthPlanId.equals(id))).go();
    await (delete(payouts)..where((t) => t.monthPlanId.equals(id))).go();
    await (delete(miscItems)..where((t) => t.monthPlanId.equals(id))).go();
    await (delete(monthPlans)..where((t) => t.id.equals(id))).go();
  }

  // ── Load full month data ──

  Future<MonthData> loadMonthData(int monthPlanId) async {
    final plan = await (select(monthPlans)..where((t) => t.id.equals(monthPlanId))).getSingle();

    final paycheckRows = await (select(paycheckDefinitions)
          ..where((t) => t.monthPlanId.equals(monthPlanId))
          ..orderBy([(t) => OrderingTerm.asc(t.sortOrder)]))
        .get();

    final paychecksWithItems = <PaycheckWithItems>[];
    for (final pc in paycheckRows) {
      final items = await (select(payPeriodItems)
            ..where((t) => t.paycheckId.equals(pc.id))
            ..orderBy([(t) => OrderingTerm.asc(t.sortOrder)]))
          .get();
      paychecksWithItems.add(PaycheckWithItems(paycheck: pc, items: items));
    }

    final bills = await (select(billDefinitions)
          ..where((t) => t.monthPlanId.equals(monthPlanId))
          ..orderBy([(t) => OrderingTerm.asc(t.dueDay), (t) => OrderingTerm.asc(t.sortOrder)]))
        .get();

    final allPayouts = await (select(payouts)
          ..where((t) => t.monthPlanId.equals(monthPlanId))
          ..orderBy([(t) => OrderingTerm.asc(t.sortOrder)]))
        .get();

    final misc = await (select(miscItems)
          ..where((t) => t.monthPlanId.equals(monthPlanId))
          ..orderBy([(t) => OrderingTerm.asc(t.sortOrder)]))
        .get();

    return MonthData(
      plan: plan,
      paychecks: paychecksWithItems,
      bills: bills,
      priorityPayouts: allPayouts.where((p) => p.position == 0).toList(),
      notablePayouts: allPayouts.where((p) => p.position == 1).toList(),
      miscItems: misc,
    );
  }

  // ── Paycheck CRUD ──

  Future<int> addPaycheck(int monthPlanId, {required DateTime date, required int amountCents, required int sequence}) {
    return into(paycheckDefinitions).insert(PaycheckDefinitionsCompanion.insert(
      monthPlanId: monthPlanId,
      sequence: sequence,
      date: date,
      amountCents: amountCents,
      sortOrder: sequence,
    ));
  }

  Future<void> updatePaycheck(int id, PaycheckDefinitionsCompanion companion) {
    return (update(paycheckDefinitions)..where((t) => t.id.equals(id))).write(companion);
  }

  Future<void> deletePaycheck(int id) async {
    await (delete(payPeriodItems)..where((t) => t.paycheckId.equals(id))).go();
    await (delete(paycheckDefinitions)..where((t) => t.id.equals(id))).go();
  }

  // ── PayPeriodItem CRUD ──

  Future<int> addPayPeriodItem(int paycheckId, {required String description, required int amountCents, required DateTime date, int sortOrder = 0}) {
    return into(payPeriodItems).insert(PayPeriodItemsCompanion.insert(
      paycheckId: paycheckId,
      description: description,
      amountCents: amountCents,
      date: date,
      sortOrder: sortOrder,
    ));
  }

  Future<void> updatePayPeriodItem(int id, PayPeriodItemsCompanion companion) {
    return (update(payPeriodItems)..where((t) => t.id.equals(id))).write(companion);
  }

  Future<void> deletePayPeriodItem(int id) {
    return (delete(payPeriodItems)..where((t) => t.id.equals(id))).go();
  }

  // ── Bill CRUD ──

  Future<int> addBill(int monthPlanId, BillDefinitionsCompanion companion) {
    return into(billDefinitions).insert(companion);
  }

  Future<void> updateBill(int id, BillDefinitionsCompanion companion) {
    return (update(billDefinitions)..where((t) => t.id.equals(id))).write(companion);
  }

  Future<void> deleteBill(int id) {
    return (delete(billDefinitions)..where((t) => t.id.equals(id))).go();
  }

  // ── Payout CRUD ──

  Future<int> addPayout(int monthPlanId, {required int position, required String description, required int amountCents, required DateTime date, int sortOrder = 0}) {
    return into(payouts).insert(PayoutsCompanion.insert(
      monthPlanId: monthPlanId,
      position: position,
      description: description,
      amountCents: amountCents,
      date: date,
      sortOrder: sortOrder,
    ));
  }

  Future<void> updatePayout(int id, PayoutsCompanion companion) {
    return (update(payouts)..where((t) => t.id.equals(id))).write(companion);
  }

  Future<void> deletePayout(int id) {
    return (delete(payouts)..where((t) => t.id.equals(id))).go();
  }

  // ── MiscItem CRUD ──

  Future<int> addMiscItem(int monthPlanId, {required String description, required int amountCents, int sortOrder = 0}) {
    return into(miscItems).insert(MiscItemsCompanion.insert(
      monthPlanId: monthPlanId,
      description: description,
      amountCents: amountCents,
      sortOrder: sortOrder,
    ));
  }

  Future<void> updateMiscItem(int id, MiscItemsCompanion companion) {
    return (update(miscItems)..where((t) => t.id.equals(id))).write(companion);
  }

  Future<void> deleteMiscItem(int id) {
    return (delete(miscItems)..where((t) => t.id.equals(id))).go();
  }
}