package org.capgemini.databank;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Database {
    private Connection connection;
    private final String connectionString = "jdbc:mariadb://localhost:3306/databank?user=root&password=";

    public Database() {
        try {
            setConnection(DriverManager.getConnection(connectionString));
        } catch (Exception e) {
            setConnection(null);
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public void insertNewContact(Contact c) throws SQLException {
            Statement stmt = getConnection().createStatement();
            stmt.executeQuery("INSERT INTO Contact VALUES ('" + c.getFirstName() + "','" + c.getLastName() + "','" + c.getCity() + "','" + c.getStreet() + "','" + c.getDistrict() + "','" + c.getZipCode() + "'," + c.getLandlineNo() + "," + c.getMobileNo() + ")");
    }

    public void deleteContact(int mobileNumber) throws SQLException {
        // we have to verify that contact really exists, because DELETE doesn't throw an exception if it doesn't
        Contact pom = findContact(mobileNumber);
        if (pom == null) throw new SQLException();
        else {
            Statement stmt = getConnection().createStatement();
            stmt.executeQuery("DELETE FROM Contact WHERE MobileNumber=" + mobileNumber);
        }
    }

    public void updateContact (Contact c) throws SQLException {
        Statement stmt = getConnection().createStatement();
        stmt.executeQuery("INSERT INTO Contact VALUES ('" + c.getFirstName() + "','" + c.getLastName() + "','" + c.getCity() + "','" + c.getStreet() + "','" + c.getDistrict() + "','" + c.getZipCode() + "'," + c.getLandlineNo() + "," + c.getMobileNo() + ")");
    }

    public Contact findContact (int mobileNumber) throws SQLException {
            Statement stmt = getConnection().createStatement();
            stmt.executeQuery("SELECT * FROM Contact WHERE MobileNumber=" + mobileNumber + " LIMIT 1");
            ResultSet rs = stmt.getResultSet();
            rs.next(); // current position is before the first row
            return new Contact(rs.getString("FirstName"),
                                         rs.getString("LastName"),
                                         rs.getString("City"),
                                         rs.getString("Street"),
                                         rs.getString("District"),
                                         rs.getString("ZipCode"),
                                         rs.getInt("LandlineNumber"),
                                         rs.getInt("MobileNumber"));
    }

    public List<Contact> getAllContacts () throws SQLException {
        List<Contact> contacts=new ArrayList<>();
        Statement stmt = getConnection().createStatement();
        stmt.executeQuery("SELECT * FROM Contact");
        ResultSet rs = stmt.getResultSet();
        while (rs.next()) {
            contacts.add(new Contact(rs.getString("FirstName"),
                                     rs.getString("LastName"),
                                     rs.getString("City"),
                                     rs.getString("Street"),
                                     rs.getString("District"),
                                     rs.getString("ZipCode"),
                                     rs.getInt("LandlineNumber"),
                                     rs.getInt("MobileNumber")));
        }
        return contacts;
    }


}