package dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
import model.Company;
import model.Zipcode;
 
public class Dao
{
	static Connection conn = createConnection();
 
	public static Connection createConnection()
	{
		Connection conn = null;
 
		try {
			String userName = "root";
			String password = "test123";
			String url = "jdbc:mysql://localhost/exampledb";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");
 
		} catch (Exception e) {
			System.err.println("Cannot connect to database server: " + e);
		}
		return conn;
 
	}
 
	public static void storeCompany(Company company)
	{
		try {
			PreparedStatement c;
			c = conn.prepareStatement("INSERT INTO company (companynr,companyname,zip) VALUES(?,?,?)");
			c.setInt(1, company.getCompanynr());
			c.setString(2, company.getCompanyname());
			c.setString(3, company.getZip());
			c.executeUpdate();
			c.close();
		} catch (SQLException e) {
			System.out.println(e);
		}
 
	}
 
	public static void updateCompany(Company company, int oldNumber)
	{
		try {
			PreparedStatement c;
			c = conn.prepareStatement("UPDATE company SET companynr=?, companyname=?, zip=? WHERE companynr=?");
			c.setInt(1, company.getCompanynr());
			c.setString(2, company.getCompanyname());
			c.setString(3, company.getZip());
			c.setInt(4, oldNumber);
			c.executeUpdate();
			c.close();
		} catch (SQLException e) {
			if (e.getMessage().contains("Duplicate entry")) {
				System.out
						.println("Cannot have duplicate Primary Key (number)");
			}
			if (e.getMessage()
					.contains(
							"Cannot add or update a child row: a foreign key constraint fails")) {
				System.out.println("Invalid ZIP");
			}
			System.out.println(e);
		}
	}
 
	public static void deleteCompany(Company company)
	{
		try {
			PreparedStatement c;
			c = conn.prepareStatement("DELETE FROM company WHERE companynr=?");
			c.setInt(1, company.getCompanynr());
			c.executeUpdate();
			c.close();
		} catch (SQLException e) {
			System.out.println(e);
		}
	}
 
	public static List<Company> getAllCompanys()
	{
		ArrayList<Company> result = new ArrayList<Company>();
 
		try {
 
			Statement c = conn.createStatement();
			c.executeQuery("SELECT companynr, companyname, zip FROM company");
			ResultSet rs = c.getResultSet();
			while (rs.next()) {
				Company company = new Company();
 
				company.setCompanynr(rs.getInt("companynr"));
 
				company.setCompanyname(rs.getString("companyname"));
				if (rs.wasNull()) {
					company.setCompanyname("(name missing)");
				}
 
				company.setZip(rs.getString("zip"));
				if (rs.wasNull()) {
					company.setZip("????");
				}
				result.add(company);
 
			}
			rs.close();
			c.close();
 
		} catch (SQLException e) {
			System.out.println(e);
		}
		return result;
 
	}
 
	public static ArrayList<Zipcode> getDistricts()
	{
		ArrayList<Zipcode> districts = new ArrayList<Zipcode>();
 
		try {
 
			Statement z = conn.createStatement();
			z.executeQuery("SELECT zip, postaldistrict FROM zipcode");
			ResultSet rs = z.getResultSet();
			while (rs.next()) {
				Zipcode zipcode = new Zipcode();
 
				zipcode.setZip(rs.getString("zip"));
				if (rs.wasNull()) {
					zipcode.setZip("????");
				}
 
				zipcode.setPostaldistrict(rs.getString("postaldistrict"));
				if (rs.wasNull()) {
					zipcode.setPostaldistrict("(district missing)");
				}
 
				districts.add(zipcode);
 
			}
			rs.close();
			z.close();
 
		} catch (SQLException e) {
			System.out.println(e);
		}
		return districts;
 
	}
 
	public static ArrayList<Zipcode> getDistricts(String zip)
	{
		ArrayList<Zipcode> districts = new ArrayList<Zipcode>();
 
		try {
 
			Statement z = conn.createStatement();
			z.executeQuery("SELECT zip, postaldistrict FROM zipcode WHERE zip LIKE '"
					+ zip + "%'");
			System.out.println(zip);
			ResultSet rs = z.getResultSet();
			while (rs.next()) {
				Zipcode zipcode = new Zipcode();
 
				zipcode.setZip(rs.getString("zip"));
				if (rs.wasNull()) {
					zipcode.setZip("????");
				}
 
				zipcode.setPostaldistrict(rs.getString("postaldistrict"));
				if (rs.wasNull()) {
					zipcode.setPostaldistrict("(district missing)");
				}
 
				districts.add(zipcode);
 
			}
			rs.close();
			z.close();
 
		} catch (SQLException e) {
			System.out.println(e);
		}
		return districts;
 
	}
}