import 'package:sqflite/sqflite.dart' show Database, openDatabase, getDatabasesPath, ConflictAlgorithm;
import 'package:path/path.dart' as path_helper;
import '../models/record.dart';

class DatabaseService {
  static Database? _database;
  
  /// 获取数据库实例
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  /// 初始化数据库
  Future<Database> _initDatabase() async {
    final path = await getDatabasesPath();
    final dbPath = path_helper.join(path, 'records.db');

    return await openDatabase(
      dbPath,
      version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE records(
            id TEXT PRIMARY KEY,
            type INTEGER NOT NULL,
            categoryId TEXT NOT NULL,
            note TEXT,
            amount REAL NOT NULL,
            createTime TEXT NOT NULL,
            accountId TEXT,
            imageUrls TEXT
          )
        ''');
      },
    );
  }

  /// 插入记录
  Future<void> insertRecord(Record record) async {
    final db = await database;
    await db.insert(
      'records',
      record.toJson()..addAll({
        'imageUrls': record.imageUrls?.join(','),
      }),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 更新记录
  Future<void> updateRecord(Record record) async {
    final db = await database;
    await db.update(
      'records',
      record.toJson()..addAll({
        'imageUrls': record.imageUrls?.join(','),
      }),
      where: 'id = ?',
      whereArgs: [record.id],
    );
  }

  /// 删除记录
  Future<void> deleteRecord(String id) async {
    final db = await database;
    await db.delete(
      'records',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 获取所有记录
  Future<List<Record>> getAllRecords() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('records');
    
    return List.generate(maps.length, (i) {
      final map = maps[i];
      return Record(
        id: map['id'],
        type: RecordType.values[map['type']],
        categoryId: map['categoryId'],
        note: map['note'],
        amount: map['amount'],
        createTime: DateTime.parse(map['createTime']),
        accountId: map['accountId'],
        imageUrls: map['imageUrls']?.split(','),
      );
    });
  }

  /// 添加按日���范围获取记录的方法
  Future<List<Record>> getRecordsByDateRange(DateTime startDate, DateTime endDate) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'records',
      where: 'createTime BETWEEN ? AND ?',
      whereArgs: [
        startDate.toIso8601String(),
        endDate.toIso8601String(),
      ],
      orderBy: 'createTime DESC',
    );

    return List.generate(maps.length, (i) {
      final map = maps[i];
      return Record(
        id: map['id'],
        type: RecordType.values[map['type']],
        categoryId: map['categoryId'],
        note: map['note'],
        amount: map['amount'],
        createTime: DateTime.parse(map['createTime']),
        accountId: map['accountId'],
        imageUrls: map['imageUrls']?.split(','),
      );
    });
  }
}