package com.atomikos.examples.tomcat.jdbc;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import jakarta.transaction.Status;
import jakarta.transaction.UserTransaction;


public class DbPopulator {

	public DbPopulator() throws Exception {
		checkTables();
	}

	private DataSource findDataSource() throws NamingException {
		Context initCtx = new InitialContext();
		Context envCtx = (Context) initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource) envCtx.lookup("jdbc/demodb");
		return ds;
	}

	private UserTransaction findUserTransaction() throws NamingException {
		Context initCtx = new InitialContext();
		Context ctx = (Context) initCtx.lookup("java:comp");
		return (UserTransaction) ctx.lookup("UserTransaction");
	}

	public long getBalance(int account) throws Exception {
		long res = -1;
		boolean error = false;
		Connection conn = null;

		try {
			conn = 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();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);
		}
		return res;
	}

	private void checkTables() throws Exception {
		boolean error = false;
		Connection conn = null;
		try {
			conn = 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) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

		// That concludes setup

	}

	public void withdraw(int account, int amount) throws Exception {
		boolean error = false;
		Connection conn = null;

		try {
			conn = getConnection();
			Statement s = conn.createStatement();

			String sql = "update Accounts set balance = balance - " + amount
					+ " where account ='account" + account + "'";
			s.executeUpdate(sql);
			s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(conn, error);

		}

	}

	private Connection getConnection() throws Exception {
		DataSource ds = findDataSource();
		Connection conn = null;

		// Retrieve of construct the UserTransaction
		// (can be bound in JNDI where available)
		UserTransaction utx = findUserTransaction();
		utx.setTransactionTimeout(60);

		// First, create a transaction
		utx.begin();
		conn = ds.getConnection();

		return conn;

	}

	private void closeConnection(Connection conn, boolean error)
			throws Exception {
		if (conn != null)
			conn.close();

		UserTransaction utx = findUserTransaction();
		if (utx.getStatus() != Status.STATUS_NO_TRANSACTION) {
			if (error)
				utx.rollback();
			else
				utx.commit();
		} else
			System.out.println("WARNING: closeConnection called outside a tx");

	}
}
