package com.mybank.db;

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

import com.mybank.domain.Account;
import com.mybank.domain.Bank;
import com.mybank.domain.Customer;

public class DBService {

	private static Connection conn;

	public static void loadData() {
		try {
			conn = ATMDB.connect();

			// query database to get all customers records
			// add these records to Bank
			// customer id, first name, last name, account balance
			String query = "SELECT customer.id AS id, " 
					+ "customer.firstname AS firstname, "
					+ "customer.lastname AS lastname, " 
					+ "account.balance AS balance" 
					+ " FROM customer JOIN account"
					+ " ON customer.id = account.custid";

			PreparedStatement preparedStatement = conn.prepareStatement(query);
			ResultSet result = preparedStatement.executeQuery();

			while (result.next()) {
				// get customer details
				int id = result.getInt("id");
				String firstname = result.getString("firstname");
				String lastname = result.getString("lastname");
				double balance = result.getDouble("balance");

				// add customers to bank
				Bank.addCustomer(firstname, lastname);
				// get customer from bank
				Customer customer = Bank.getCustomer(id - 1);
				// add account to customer
				customer.addAccount(new Account(balance));

				System.out.println(customer.getFirstName() + " " + customer.getLastName());
			}
			System.out.println("There are " + Bank.getNumOfCustomers() + " customers.");
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}

}