package nl.sbdeveloper.themeparkplus.managers;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import nl.sbdeveloper.themeparkplus.api.PlusAPI;
import nl.sbdeveloper.themeparkplus.api.objects.Gate;
import nl.sbdeveloper.themeparkplus.api.objects.MalfunctionReport;
import nl.sbdeveloper.themeparkplus.api.objects.WaitingRow;
import nl.sbdeveloper.themeparkplus.sbutils.LocationSerializer;
import nl.sbdeveloper.themeparkplus.sbutils.SQLiteDB;
import nl.sbdeveloper.themeparkplus.sbutils.Verify;
import nl.sbdeveloper.themeparkplus.util.LocationGsonAdapter;
import org.bukkit.Location;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

public class DBManager {
    private static final Gson gson = new GsonBuilder().registerTypeAdapter(Location.class, new LocationGsonAdapter()).create();

    private static SQLiteDB data;
    private static Connection con;

    public DBManager(String name) {
        data = new SQLiteDB(name);

        try {
            con = data.getConnection();

            String query = "CREATE TABLE IF NOT EXISTS gates (gateLocation varchar(255) NOT NULL, gateData blob NOT NULL, UNIQUE (gateLocation))";
            PreparedStatement statement = con.prepareStatement(query);
            statement.execute();

            query = "CREATE TABLE IF NOT EXISTS rows (rideID varchar(255) NOT NULL, rowData blob NOT NULL, UNIQUE (rideID))";
            statement = con.prepareStatement(query);
            statement.execute();

            query = "CREATE TABLE IF NOT EXISTS malfunction_reports (rideID varchar(255) NOT NULL, reportData blob NOT NULL, UNIQUE (rideID))";
            statement = con.prepareStatement(query);
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Gson getGson() {
        return gson;
    }

    public void load() throws SQLException {
        /* Load gates */
        String query = "SELECT * FROM gates";
        PreparedStatement statement = con.prepareStatement(query);
        ResultSet gateSet = statement.executeQuery();
        while (gateSet.next()) {
            //Loading a gates...
            byte[] blob = gateSet.getBytes("gateData");
            String json = new String(blob);

            Gson gson = getGson();
            Gate gate = gson.fromJson(json, Gate.class);
            PlusAPI.addGate(gate);
        }

        /* Load rows */
        query = "SELECT * FROM rows";
        statement = con.prepareStatement(query);
        ResultSet rowSet = statement.executeQuery();
        while (rowSet.next()) {
            //Loading a gates...
            byte[] blob = rowSet.getBytes("rowData");
            String json = new String(blob);

            Gson gson = getGson();
            WaitingRow row = gson.fromJson(json, WaitingRow.class);
            PlusAPI.addRow(row);
        }

        /* Load reports */
        query = "SELECT * FROM malfunction_reports";
        statement = con.prepareStatement(query);
        ResultSet reportSet = statement.executeQuery();
        while (reportSet.next()) {
            //Loading a gates...
            byte[] blob = reportSet.getBytes("reportData");
            String json = new String(blob);

            Gson gson = getGson();
            MalfunctionReport report = gson.fromJson(json, MalfunctionReport.class);
            PlusAPI.addReport(report);
        }
    }

    public void save() {
        if (Verify.isValid() == null || !Verify.isValid()) return;

        for (Map.Entry<Location, Gate> entry : PlusAPI.getGates().entrySet()) {
            Gson gson = getGson();
            byte[] blob = gson.toJson(entry.getValue()).getBytes();

            try {
                String query = "INSERT INTO gates (gateLocation, gateData) VALUES (?, ?)";
                PreparedStatement statement = con.prepareStatement(query);
                statement.setString(1, LocationSerializer.serialize(entry.getKey()));
                statement.setBytes(2, blob);
                statement.executeUpdate();
            } catch (SQLException ignored) {}

            try {
                String query2 = "UPDATE gates SET gateData = ? WHERE gateLocation = ?";
                PreparedStatement statement2 = con.prepareStatement(query2);
                statement2.setBytes(1, blob);
                statement2.setString(2, LocationSerializer.serialize(entry.getKey()));
                statement2.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        for (Map.Entry<String, WaitingRow> entry : PlusAPI.getRows().entrySet()) {
            Gson gson = getGson();
            byte[] blob = gson.toJson(entry.getValue()).getBytes();

            try {
                String query = "INSERT INTO rows (rideID, rowData) VALUES (?, ?)";
                PreparedStatement statement = con.prepareStatement(query);
                statement.setString(1, entry.getKey());
                statement.setBytes(2, blob);
                statement.executeUpdate();
            } catch (SQLException ignored) {}

            try {
                String query2 = "UPDATE rows SET rowData = ? WHERE rideID = ?";
                PreparedStatement statement2 = con.prepareStatement(query2);
                statement2.setBytes(1, blob);
                statement2.setString(2, entry.getKey());
                statement2.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        for (Map.Entry<String, MalfunctionReport> entry : PlusAPI.getReports().entrySet()) {
            Gson gson = getGson();
            byte[] blob = gson.toJson(entry.getValue()).getBytes();

            try {
                String query = "INSERT INTO malfunction_reports (rideID, reportData) VALUES (?, ?)";
                PreparedStatement statement = con.prepareStatement(query);
                statement.setString(1, entry.getKey());
                statement.setBytes(2, blob);
                statement.executeUpdate();
            } catch (SQLException ignored) {}

            try {
                String query2 = "UPDATE malfunction_reports SET reportData = ? WHERE rideID = ?";
                PreparedStatement statement2 = con.prepareStatement(query2);
                statement2.setBytes(1, blob);
                statement2.setString(2, entry.getKey());
                statement2.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void closeConnection() {
        data.closeSource();
    }
}
