package eg;

import static org.seamframework.sql.Expressions.and;
import static org.seamframework.sql.Expressions.eq;
import static org.seamframework.sql.Expressions.parameter;
import static org.seamframework.sql.Type.SMALLINT;
import static org.seamframework.sql.Type.VARCHAR;

import javax.enterprise.event.Observes;
import javax.enterprise.inject.Default;
import javax.enterprise.util.AnnotationLiteral;
import javax.inject.Inject;

import org.jboss.weld.environment.se.events.ContainerInitialized;
import org.seamframework.sql.ConnectionFactory;
import org.seamframework.sql.Create;
import org.seamframework.sql.DefaultPreparedStatementFactory;
import org.seamframework.sql.ExecutableInsert;
import org.seamframework.sql.ExecutableStatement;
import org.seamframework.sql.ExecutableUpdate;
import org.seamframework.sql.From;
import org.seamframework.sql.Insert;
import org.seamframework.sql.Parameter;
import org.seamframework.sql.PreparedStatementFactory;
import org.seamframework.sql.Query;
import org.seamframework.sql.Selection;
import org.seamframework.sql.Session;
import org.seamframework.sql.Tuple;
import org.seamframework.sql.Update;

import eg.Schema.permissions;
import eg.Schema.users;
import eg.Schema.customers;
import eg.Schema.orders;

public class Main {
	
	void start(@Observes ContainerInitialized ci, Session session) {
		createTable(session);
		testInsert(session);
		testUpdate(session);
		testLoginQuery(session);
		testQuery2(session);
		testQuery3(session);
	}
	
	public static void main(String[] args) throws Exception {
		ConnectionFactory cf = new TestConnectionFactory();
		PreparedStatementFactory psf = new DefaultPreparedStatementFactory();
		Session session = new Session( new AnnotationLiteral<Default>() {}, cf, psf );
		
		createTable(session);
		testInsert(session);
		testUpdate(session);
		testQuery2(session);
		testQuery3(session);
	}

	public static void createTable(Session session) {
		Create<users> create = new Create<users>(users.table);
		create.add(users.name, "varchar(40) not null");
		create.add(users.username, "varchar(10) not null primary key");
		create.add(users.password, "varchar(10) not null");
		create.add(users.age, "smallint not null");
		System.out.println( create.toSql() );
		
		session.prepare(create).execute();

		Create<permissions> create2 = new Create<permissions>(permissions.table);
		create2.add(permissions.username, "varchar(10) not null");
		create2.add(permissions.id, "varchar(20) not null primary key");
		System.out.println( create2.toSql() );
		
		session.prepare(create2).execute();
		
		Create<customers> create3 = new Create<customers>(customers.table);
		create3.add(customers.id, "varchar(40) not null primary key");
		System.out.println( create3.toSql() );
		
		session.prepare(create3).execute();
		
		Create<orders> create4 = new Create<orders>(orders.table);
		create4.add(orders.id, "varchar(40) not null primary key");
		create4.add(orders.customersid, "varchar(40) not null foreign key(customersid) references customers(id)");
		System.out.println( create4.toSql() );
		
		session.prepare(create4).execute();
}
	
	

	public static void testInsert(Session session) {
		Insert<users> insert = new Insert<users>(users.table);
		Parameter<String> nameParameter = insert.set(users.name);
		Parameter<String> usernameParameter = insert.set(users.username);
		Parameter<String> passwordParameter = insert.set(users.password);
		Parameter<Short> ageParameter = insert.set(users.age);
		System.out.println( insert.toSql() );
		
		ExecutableInsert es = session.prepare(insert);
		es.set(nameParameter, "Gavin King");
		es.set(usernameParameter, "gavin");
		es.set(passwordParameter, "foobar");
		es.set(ageParameter, (short)21);
		es.execute();

		Insert<permissions> insert2 = new Insert<permissions>(permissions.table);
		Parameter<String> userParameter = insert2.set(permissions.username);
		Parameter<String> idParameter = insert2.set(permissions.id);
		System.out.println( insert.toSql() );
		
		ExecutableStatement es2 = session.prepare(insert2);
		es2.set(userParameter, "gavin");
		es2.set(idParameter, "create");
		es2.execute();
		es2.set(idParameter, "update");
		es2.execute();
		es2.set(idParameter, "delete");
		es2.execute();
}

	public static void testUpdate(Session session) {
		Update<users> update = new Update<users>(users.table);
		Parameter<String> nameParameter = update.set(users.name);
		Parameter<String> passwordParameter = update.set(users.password);
		Parameter<String> usernameParam = parameter(VARCHAR);
		update.where( eq( update.get(users.username), usernameParam ) );
		System.out.println( update.toSql() );
		
		ExecutableUpdate es = session.prepare(update);
		es.set(nameParameter, "Gavin King");
		es.set(passwordParameter, "foobax");
		es.set(usernameParam, "gavin");
		int rows = es.executeUpdate();
	}
	
	
	@Inject LoginQuery loginQuery;

	public void testLoginQuery(Session session) {
		
		System.out.println( loginQuery.toSql() );
		
		Iterable<Tuple> result = session.prepare(loginQuery)
			.set(loginQuery.username, "gavin")
			.set(loginQuery.password, "foobax")
			.result();
		
		for (Tuple tuple: result) {
			System.out.println( tuple.get(loginQuery.name) + ' ' + 
						tuple.get(loginQuery.age) + ' ' + 
						tuple.get(loginQuery.permissionId) );
		}
	}

	public static void testQuery2(Session session) {
		System.out.println("begin testQuery2()");
		Query query = new Query();
		From<users> user = query.from(users.table, "u");

		Parameter<String> username = parameter(VARCHAR);
		Parameter<String> password = parameter(VARCHAR);
		
		query.where( and( eq( user.get(users.username), username ), 
				          eq( user.get(users.password), password ) ) );

		Selection<String> name = query.select( user.get(users.name), "n" );
		Selection<Short> age = query.select( user.get(users.age), "a" );

		query.orderBy(name, age);
		
		System.out.println( query.toSql() );
		
		Iterable<Tuple> result = session.prepare(query)
			.set(username, "gavin")
			.set(password, "foobax")
			.result();
		
		for (Tuple tuple: result) {
			System.out.println( tuple.get(name) + ' ' + tuple.get(age) );
		}
	}
	
	public static void testQuery3(Session session) {
		System.out.println("begin testQuery3()");
		Iterable<Tuple> result = session.prepareQuery("select username, name, password, age from users where username=?")
			.set(0, "gavin", VARCHAR)
			.result();
		/*for (Tuple tuple: result) {
			System.out.println( tuple.get(0, VARCHAR) );
			System.out.println( tuple.get(1, VARCHAR) );
			System.out.println( tuple.get(2, VARCHAR) );
			System.out.println( tuple.get(3, SMALLINT) );
		}*/
		
		for (Tuple tuple: result) {
			System.out.println( tuple.testGet(0));
			System.out.println( tuple.testGet(1));
			System.out.println( tuple.testGet(2));
			System.out.println( tuple.testGet(3));
		}
	}

}
