package com.jatak.webtools.web.gui;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;

import org.apache.log4j.Category;

import com.jatak.bitfield.BitfieldTypeContainer;
import com.jatak.bitfield.BitfieldValue;
import com.jatak.webtools.utils.ConnectionFactory;

import wicket.IFeedback;
import wicket.PageParameters;
import wicket.markup.html.basic.Label;
import wicket.markup.html.form.DropDownChoice;
import wicket.markup.html.form.Form;
import wicket.markup.html.form.RequiredTextField;
import wicket.markup.html.form.TextField;
import wicket.markup.html.panel.FeedbackPanel;
import wicket.model.CompoundPropertyModel;

/**
 * Home page.
 * 
 * @author jan
 */
public class MemberPage extends SimpleBorderedPage {

	private static Category log = Category.getInstance(MemberPage.class
			.getName());

	private Connection conn = null;

	private BitfieldTypeContainer container;

	/**
	 * Constructor that is invoked when page is invoked without a session.
	 * 
	 * @param parameters
	 *            Page parameters
	 */
	public MemberPage(final PageParameters parameters) {
		super("Members");

		conn = null;
		try {
			conn = ConnectionFactory.getDRBSConnection();
			container = BitfieldTypeContainer.getInstance(conn, "demographics");
		} catch (SQLException sqle) {
			log.error(sqle.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException sqlEx) {
					// ignore
				}
			}
		}

		// New Member form and feedbackpanel
		final FeedbackPanel newMemberFormFeedback = new FeedbackPanel("newMemberFormFeedback");
		add(new NewMemberForm("newMemberForm", newMemberFormFeedback));
		add(newMemberFormFeedback);
		
		// Query Member form and feedbackpanel
		final FeedbackPanel queryMemberFormFeedback = new FeedbackPanel("queryMemberFormFeedback");
		add(new QueryMemberForm("queryMemberForm", queryMemberFormFeedback));
		add(queryMemberFormFeedback);
		
	}

	/**
	 * New member form
	 */
	private class NewMemberForm extends Form {
		/**
		 * Construct.
		 * 
		 * @param name
		 *            Component name
		 * @param feedback
		 *            Feedback display for form
		 */
		public NewMemberForm(String name, IFeedback feedback) {
			super(name, new CompoundPropertyModel(new NewMemberFormModel()), feedback);

			add(new RequiredTextField("email"));
			add(new RequiredTextField("firstName"));
			add(new TextField("lastName"));
			add(new TextField("gender"));
			add(new TextField("birthYear"));
			add(new TextField("birthMonth"));
			add(new TextField("birthDay"));
			add(new RequiredTextField("categories"));
			
			Label resultLabel = new Label("result");
			resultLabel.setEscapeModelStrings(false);
			add(resultLabel);
		}

		/**
		 * @see wicket.markup.html.form.Form#onSubmit()
		 */
		public void onSubmit() {
			BitfieldFormModel model = (BitfieldFormModel) getModelObject();
			BitfieldValue value = null;

			BitfieldValue tmpValue;
			String s;
			int i;
			StringTokenizer st;
			String status = "";
			
			if (!("".equals(model.getCountries().trim()))) {
				tmpValue = null;
				st = new StringTokenizer(model.getCountries(), ",");
				try {
					while (st.hasMoreTokens()) {
						s = st.nextToken().trim();
						i = Integer.parseInt(s);
						BitfieldValue v = container.getBitfieldValue("country", i);
						if (v == null) {
							model.setStatus("Unknown value in list of countries");
						} else {
							if (tmpValue == null) {
								tmpValue = v;
							} else {
								tmpValue.or(v);
							}
						}
					}
					if (tmpValue != null) {
						if (value == null) {
							value = tmpValue;
						} else {
							value.and(tmpValue);
						}
						status += "Countries: " + tmpValue.getCount() + "<br>";
					}
				} catch (SQLException e) {
					e.printStackTrace();
					model.setStatus("Error in list of countries");
					return;
				} catch (NumberFormatException e) {
					e.printStackTrace();
					model.setStatus("Error in list of countries");
					return;
				}
			}

			if (!("".equals(model.getPartners().trim()))) {
				tmpValue = null;
				st = new StringTokenizer(model.getPartners(), ",");
				try {
					while (st.hasMoreTokens()) {
						s = st.nextToken().trim();
						i = Integer.parseInt(s);
						BitfieldValue v = container.getBitfieldValue("currentpl", i);
						if (v == null) {
							model.setStatus("Unknown value in list of partners");
						} else {
							if (tmpValue == null) {
								tmpValue = v;
							} else {
								tmpValue.or(v);
							}
						}
					}
					if (tmpValue != null) {
						if (value == null) {
							value = tmpValue;
						} else {
							value.and(tmpValue);
						}
						status += "Partners: " + tmpValue.getCount() + "<br>";
					}
				} catch (SQLException e) {
					e.printStackTrace();
					model.setStatus("Error in list of partners");
					return;
				} catch (NumberFormatException e) {
					e.printStackTrace();
					model.setStatus("Error in list of partners");
					return;
				}
			}

			if (!("".equals(model.getCategories().trim()))) {
				tmpValue = null;
				st = new StringTokenizer(model.getCategories(), ",");
				try {
					while (st.hasMoreTokens()) {
						s = st.nextToken().trim();
						i = Integer.parseInt(s);
						BitfieldValue v = container.getBitfieldValue("catid", i);
						if (v == null) {
							model.setStatus("Unknown value in list of categories");
						} else {
							if (tmpValue == null) {
								tmpValue = v;
							} else {
								tmpValue.or(v);
							}
						}
					}
					if (tmpValue != null) {
						if (value == null) {
							value = tmpValue;
						} else {
							value.and(tmpValue);
						}
						status += "Categories: " + tmpValue.getCount() + "<br>";
					}
				} catch (SQLException e) {
					e.printStackTrace();
					model.setStatus("Error in list of categories");
					return;
				} catch (NumberFormatException e) {
					e.printStackTrace();
					model.setStatus("Error in list of categories");
					return;
				}
			}

			if (!("".equals(model.getGenders().trim()))) {
				tmpValue = null;
				st = new StringTokenizer(model.getGenders(), ",");
				try {
					while (st.hasMoreTokens()) {
						s = st.nextToken().trim();
						i = Integer.parseInt(s);
						BitfieldValue v = container.getBitfieldValue("gender", i);
						if (v == null) {
							model.setStatus("Unknown value in list of genders");
						} else {
							if (tmpValue == null) {
								tmpValue = v;
							} else {
								tmpValue.or(v);
							}
						}
					}
					if (tmpValue != null) {
						if (value == null) {
							value = tmpValue;
						} else {
							value.and(tmpValue);
						}
						status += "Genders: " + tmpValue.getCount() + "<br>";
					}
				} catch (SQLException e) {
					e.printStackTrace();
					model.setStatus("Error in list of genders");
					return;
				} catch (NumberFormatException e) {
					e.printStackTrace();
					model.setStatus("Error in list of genders");
					return;
				}
			}

			if (!("".equals(model.getAges().trim()))) {
				tmpValue = null;
				st = new StringTokenizer(model.getAges(), ",");
				try {
					while (st.hasMoreTokens()) {
						s = st.nextToken().trim();
						i = Integer.parseInt(s);
						BitfieldValue v = container.getBitfieldValue("age", i);
						if (v == null) {
							model.setStatus("Unknown value in list of ages");
						} else {
							if (tmpValue == null) {
								tmpValue = v;
							} else {
								tmpValue.or(v);
							}
						}
					}
					if (tmpValue != null) {
						if (value == null) {
							value = tmpValue;
						} else {
							value.and(tmpValue);
						}
						status += "Ages: " + tmpValue.getCount() + "<br>";
					}
				} catch (SQLException e) {
					e.printStackTrace();
					model.setStatus("Error in list of ages");
					return;
				} catch (NumberFormatException e) {
					e.printStackTrace();
					model.setStatus("Error in list of ages");
					return;
				}
			}

			if (!("".equals(model.getStatusIds().trim()))) {
				tmpValue = null;
				st = new StringTokenizer(model.getStatusIds(), ",");
				try {
					while (st.hasMoreTokens()) {
						s = st.nextToken().trim();
						i = Integer.parseInt(s);
						BitfieldValue v = container.getBitfieldValue("statusid", i);
						if (v == null) {
							model.setStatus("Unknown value in list of status Ids");
						} else {
							if (tmpValue == null) {
								tmpValue = v;
							} else {
								tmpValue.or(v);
							}
						}
					}
					if (tmpValue != null) {
						if (value == null) {
							value = tmpValue;
						} else {
							value.and(tmpValue);
						}
						status += "Status Ids: " + tmpValue.getCount() + "<br>";
					}
				} catch (SQLException e) {
					e.printStackTrace();
					model.setStatus("Error in list of status IDs");
					return;
				} catch (NumberFormatException e) {
					e.printStackTrace();
					model.setStatus("Error in list of status IDs");
					return;
				}
			}

			try {
				model.setStatus(status + "Total : " + value.getCount() + " matching members");
			} catch (SQLException e) {
				e.printStackTrace();
				model.setStatus("Could not get total. Value: " + value);
				return;
			}
			// Form validation successful. Display message showing edited model.
			// log.info("Categories: " + ((BitfieldFormModel)
			// getModelObject()).getCategories());
		}
	}
	
	/**
	 * Query member form
	 */
	private class QueryMemberForm extends Form {
		/**
		 * Construct.
		 * 
		 * @param name
		 *            Component name
		 * @param feedback
		 *            Feedback display for form
		 */
		public QueryMemberForm(String name, IFeedback feedback) {
			super(name, new CompoundPropertyModel(new QueryMemberFormModel()), feedback);

			List platformList = new ArrayList();
			platformList.add("DRBS");
			platformList.add("CBS");
			platformList.add("DRBS and CBS");
			
			add(new DropDownChoice("services", platformList));
 			
			add(new TextField("emailExact"));
			add(new TextField("emailPartial"));
			
			Label resultLabel = new Label("result");
			resultLabel.setEscapeModelStrings(false);
			add(resultLabel);
		}

		/**
		 * @see wicket.markup.html.form.Form#onSubmit()
		 */
		public void onSubmit() {
			QueryMemberFormModel model = (QueryMemberFormModel) getModelObject();
			try {
				ResultSet rs = null;
				PreparedStatement ps = null;
				String result = "";
				
				String where = null;
				if (notEmpty(model.getEmailExact())) {
					if (where == null) {
						where = "where ";
					} else {
						where += "and ";
					}
					where += "m.email='" + model.getEmailExact() + "' ";
				}
				if (notEmpty(model.getEmailPartial())) {
					if (where == null) {
						where = "where ";
					} else {
						where += "and ";
					}
					where += "m.email like '%" + model.getEmailPartial() + "%' ";
				}
				
				if (where != null) {
					log.info("where: " + where);
					log.info("DRBS...");
					// DRBS
					Connection connDRBS = ConnectionFactory.getDRBSConnection();
					String sql = "select m.firstname, m.lastname, m.email, p.name " +
							"from member m, privatelabelpartners p " + where + " and m.statusid=30 and m.currentpl=p.plid";
					log.info("sql: " + sql);
					ps = connDRBS.prepareStatement(sql);
					rs = ps.executeQuery();
					while (rs.next()) {
log.info(rs.getString("name"));
						result += "DRBS: " + rs.getString("email") + "; " + rs.getString("firstname") + 
							"; " + rs.getString("lastname") + "; " + rs.getString("name") + "<br>"; 
					}
					rs.close();
					ps.close();
					
					// CBS
					List cbsConnections = ConnectionFactory.getCBSConnections();
					for (int i = 0; i < cbsConnections.size(); i++) {
						log.info("CBS" + i + "...");
						Connection conn = (Connection) cbsConnections.get(i);
						ps = conn.prepareStatement("select m.firstname, m.lastname, m.email, p.name " +
								"from tbl_member m, tbl_product p " + where + " and m.product=p.product_id");
						rs = ps.executeQuery();
						while (rs.next()) {
							result += "CBS : " + rs.getString("email") + "; " + rs.getString("firstname") + 
								"; " + rs.getString("lastname") + "; " + rs.getString("name") + "<br>"; 
						}
						rs.close();
						ps.close();
					}
					if ("".equals(result)) {
						result = "No memberships found";
					}
					model.setResult(result);
				} else {
					model.setResult("Exact or partial email address must be specified");
				}
			} catch (SQLException e) {
				e.printStackTrace();
				model.setResult("Technical problem!");
				return;
			}
		}
	}
	
	private boolean notEmpty (String s) {
		if (s == null) return false;
		return (!s.equals(""));
	}
}