import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DBProvider {
  DBProvider._();

  static final DBProvider db = DBProvider._();
  static Database _database;

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

    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }

  static const tableProject = """
  CREATE TABLE IF NOT EXISTS Project (
        id TEXT PRIMARY KEY,
        managerId TEXT,
        consultantID TEXT
        name TEXT,
        description TEXT,
        created TEXT,
        deadline TEXT
      );""";
  static const tableAudit = """
  CREATE TABLE IF NOT EXISTS Audit (
        id TEXT PRIMARY key,
        projectId TEXT,
        timeTrackId TEXT,
        jsonChanges TEXT,
        date TEXT,
        employeeId TEXT
      );""";
  static const tableEmployee = """
  CREATE TABLE IF NOT EXISTS Employee (
        id TEXT PRIMARY key,
        fullName TEXT,
        managementLogonAccess INTEGER
      );""";
  static const tableJobPosition = """
  CREATE TABLE IF NOT EXISTS JobPosition (
        id TEXT PRIMARY KEY,
        name TEXT
      );""";
  static const tableWorkType = """
  CREATE TABLE IF NOT EXISTS WorkType (
        id TEXT PRIMARY key,
        name TEXT
      );""";
  static const tableAssignedJobPosition = """
  CREATE TABLE IF NOT EXISTS AssignedJobPosition (
        employeeId TEXT,
        positionId TEXT
      );""";
  static const tableTimeTrack = """
  CREATE TABLE IF NOT EXISTS TimeTrack (
        id TEXT PRIMARY key,
        timeSpan INTEGER,
        employeeId TEXT,
        projectId TEXT,
        workType TEXT,
        note TEXT,
        date TEXT
      );""";
  static const tableAllowedWorkType = """
  CREATE TABLE IF NOT EXISTS AllowedWorkType (
        projectId TEXT,
        workTypeId TEXT
      );""";

  Future<Database> initDB() async {
    print("initDB executed");
    //Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(await getDatabasesPath(), "core1.db");
    await deleteDatabase(path);
    return await openDatabase(path, version: 2,
        onCreate: (Database db, int version) async {
          await db.execute(tableEmployee);
          await db.execute(tableAudit);
          await db.execute(tableProject);
          await db.execute(tableJobPosition);
          await db.execute(tableWorkType);
          await db.execute(tableAssignedJobPosition);
          await db.execute(tableTimeTrack);
          await db.execute(tableAllowedWorkType);
      /*await db.execute(tableEmployee +
          tableAudit +
          tableProject +
          tableJobPosition +
          tableWorkType +
          tableAssignedJobPosition +
          tableTimeTrack +
          tableAllowedWorkType);*/
    });
  }

  ///get all Projects
  Future/*<List<Project>>*/ getAllProjects() async {
    final db = await database;
    return await db.query("Project");
    /*var res =
    return res.isNotEmpty ? res.map((c) => Project.fromMap(c, false)).toList() : [];*/
  }
}

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
        visualDensity: VisualDensity.adaptivePlatformDensity,
      ),
      home: MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  int _counter = 0;

  void _incrementCounter() async {
    var res = await DBProvider.db.getAllProjects();
    print(res);
    setState(() {
      _counter++;
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            Text(
              'You have pushed the button this many times:',
            ),
            Text(
              '$_counter',
              style: Theme.of(context).textTheme.headline4,
            ),
          ],
        ),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _incrementCounter,
        tooltip: 'Increment',
        child: Icon(Icons.add),
      ),
    );
  }
}