package com.atomikos.tomcat.demo;

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

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

@Transactional
public class AccountManager {

	private DataSource dataSource;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void withdraw(int account, int amount) throws Exception {
		Connection conn = dataSource.getConnection();
		Statement s = conn.createStatement();

		String sql = "update Accounts set balance = balance - " + amount
				+ " where account ='account" + account + "'";
		s.executeUpdate(sql);
		s.close();
		conn.close();
	}

	public long balance(int account) throws Exception {
		long res = -1;
		Connection conn = dataSource.getConnection();
		Statement s = conn.createStatement();
		String query = "select balance from Accounts where account='"
				+ "account" + account + "'";
		ResultSet rs = s.executeQuery(query);
		if (rs == null || !rs.next())
			throw new Exception("Account not found: " + account);
		res = rs.getLong(1);
		s.close();
		conn.close();
		return res;
	}

	
	public void checkTable() throws Exception {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (Exception noConnect) {
			noConnect.printStackTrace();
			System.err.println("Failed to connect.");
			System.err
					.println("PLEASE MAKE SURE THAT DERBY IS INSTALLED AND RUNNING");
			throw noConnect;
		}
		try {

			Statement s = conn.createStatement();
			try {
				s.executeQuery("select * from Accounts");
			} catch (SQLException ex) {
				// table not there => create it
				System.err.println("Creating Accounts table...");
				s.executeUpdate("create table Accounts ( "
						+ " account VARCHAR ( 20 ), owner VARCHAR(300), balance DECIMAL (19,0) )");
				for (int i = 0; i < 100; i++) {
					s.executeUpdate("insert into Accounts values ( "
							+ "'account" + i + "' , 'owner" + i + "', 10000 )");
				}
			}
			s.close();
		} catch (Exception e) {
			throw e;
		} finally {
			conn.close();

		}

		// That concludes setup

	}

}
