package lapr.project.utils;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import lapr.project.model.Junction;
import lapr.project.model.Project;
import lapr.project.model.Road;
import oracle.jdbc.pool.OracleDataSource;

public class DatabaseHelper {

    private static final String databaseUrl = "";
    private static final String username = "LAPR_G05";
    private static final String password = "ytrewq";
    private static Connection connection;

    public static void openConnection() throws SQLException {
        OracleDataSource ds = new OracleDataSource();
        ds.setURL(databaseUrl);
        connection = ds.getConnection(username, password);
    }

    public static String closeAll() {

        StringBuilder message = new StringBuilder("");

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
                message.append(ex.getMessage());
                message.append("\n");
            }
            connection = null;
        }
        return message.toString();
    }

    public static void createProject(String name) throws SQLException {
        CallableStatement call = connection.prepareCall("{ CALL proc_create_project(?) }");

        call.setString(1, name);

        call.execute();
        call.close();
    }

    public static void createRoadNetwork(int projectId, String name, String description) throws SQLException {
        CallableStatement call = connection.prepareCall("{ CALL proc_create_roadnetwork(?, ?, ?)");

        call.setInt(1, projectId);
        call.setString(2, name);
        call.setString(3, description);

        call.execute();
        call.close();
    }

    public static void createRoad(Road road, int roadNetworkId) throws SQLException {
        CallableStatement call = connection.prepareCall("{ CALL proc_create_road(?, ?, ?)");

        call.setInt(1, roadNetworkId);
        call.setString(2, road.getName());
        call.setString(3, road.getTypology());

        call.execute();
        call.close();
    }

    public static void createJunction(Junction junction, int roadNetworkId) throws SQLException {
        CallableStatement call = connection.prepareCall("{ CALL proc_create_junction(?) }");

        call.setInt(1, roadNetworkId);

        call.execute();
        call.close();
    }

    public static int getProjectId(String nameProject) throws SQLException {
        int id = -1;

        String query = "SELECT p.id FROM projeto p WHERE p.name = '" + nameProject + "'";

        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(query);

        if (result.next())
            id = result.getInt("id");
        statement.close();

        return id;
    }

    public static int getRoadNetworkId(int projectId) throws SQLException {
        int id = -1;

        String query = "SELECT r.id FROM roadnetwork r WHERE r.project_id = '" + projectId + "'";

        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(query);

        if (result.next())
            id = result.getInt("id");
        statement.close();

        return id;
    }
}