rog_app/lib/utils/db_helper.dart

import 'package:flutter_map/flutter_map.dart';
import 'package:rogapp/model/location_response.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'dart:io';
import 'dart:convert'; // for jsonEncode

class DatabaseHelper {
  static const _databaseName = "FeatureDatabase.db";
  static const _databaseVersion = 1;

  // Singleton class
  DatabaseHelper._();
  static final DatabaseHelper instance = DatabaseHelper._();

  Database? _database;
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE features (
        id INTEGER PRIMARY KEY,
        geometry TEXT NOT NULL,
        properties TEXT NOT NULL,
        type TEXT NOT NULL,
        location_id,
        latitude REAL NOT NULL,
        longitude REAL NOT NULL
      )
      ''');
  }
  
  // Database insert operation
  Future<void> insertFeature(Map<String, dynamic> feature) async {
    // Get a reference to the database.
    final db = await database;

    // Insert the feature into the correct table.
    await db.insert(
      'features',
      feature,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }
  
  // Database get operation
  Future<List<Feature>> getFeatures() async {
    Database db = await database;
    final List<Map<String, dynamic>> maps = await db.query('features');

    return List.generate(maps.length, (i) {
      return Feature(
        id: maps[i]['id'],
        geometry: Geometry.fromMap(jsonDecode(maps[i]['geometry'])),
        properties: Properties.fromMap(jsonDecode(maps[i]['properties'])),
        type: maps[i]['type'],
        location_id: Properties.fromMap(jsonDecode(maps[i]['properties'])).location_id,
        latitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][1],
        longitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][0]
      );
    });
  }

  // Database delete operation
  Future<void> deleteFeature(int id) async {
    Database db = await database;
    await db.delete(
      'features',
      where: "id = ?",
      whereArgs: [id],
    );
  }

  Future<void> deleteAll() async {
    // Get a reference to the database.
    final db = await database;

    // Delete all rows.
    await db.rawDelete('DELETE FROM features');
  }

  // Database search by location_id
  Future<List<Feature>> getFeatureByLocationId(int locationId) async {
    Database db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'features',
      where: "location_id = ?",
      whereArgs: [locationId],
    );

    return List.generate(maps.length, (i) {
      return Feature(
        id: maps[i]['id'],
        geometry: Geometry.fromMap(jsonDecode(maps[i]['geometry'])),
        properties: Properties.fromMap(jsonDecode(maps[i]['properties'])),
        type: maps[i]['type'],
        location_id: Properties.fromMap(jsonDecode(maps[i]['properties'])).location_id,
        latitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][1],
        longitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][0]
      );
    });
  }

  // Database search by latitude and longitude
  Future<List<Feature>> getFeatureByLatLon(double latitude, double longitude) async {
    Database db = await database;
        final List<Map<String, dynamic>> maps = await db.query(
      'features',
      where: "latitude = ? AND longitude = ?",
      whereArgs: [latitude, longitude],
    );

    return List.generate(maps.length, (i) {
      return Feature(
        id: maps[i]['id'],
        geometry: Geometry.fromMap(jsonDecode(maps[i]['geometry'])),
        properties: Properties.fromMap(jsonDecode(maps[i]['properties'])),
        type: maps[i]['type'],
        location_id: Properties.fromMap(jsonDecode(maps[i]['properties'])).location_id,
        latitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][1],
        longitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][0]
      );
    });
  }

  Future<List<Feature>> getFeaturesWithinBounds(LatLngBounds bounds) async {
    final db = await database;

    final List<Map<String, dynamic>> maps = await db.query(
      'features',
      where: 'latitude BETWEEN ? AND ? AND longitude BETWEEN ? AND ?',
      whereArgs: [
        bounds.southWest!.latitude,
        bounds.northEast!.latitude,
        bounds.southWest!.longitude,
        bounds.northEast!.longitude,
      ],
    );

    return List.generate(maps.length, (i) {
      return Feature(
        id: maps[i]['id'],
        geometry: Geometry.fromMap(jsonDecode(maps[i]['geometry'])),
        properties: Properties.fromMap(jsonDecode(maps[i]['properties'])),
        type: maps[i]['type'],
        location_id: Properties.fromMap(jsonDecode(maps[i]['properties'])).location_id,
        latitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][1],
        longitude: Geometry.fromMap(jsonDecode(maps[i]['geometry'])).coordinates![0][0]
      );
    });
  }



}