import java.io.*;
import java.sql.*;
//import SQLite.*;


/*
 * Created on May 13, 2004
 *
 */

/**
 * @author ande
 * Andrew Ebaugh, Saurav Chatterjee
 * CSE477, Spring 2004
 */

public class Service  {

	public static final String mp3_dir = "/mp3/";
	public static final String mp3_sfx = ".mp3";
	public static final String mode_id = "E00781BCC1956C22";
	
	//static clips
	public static final String startup_clip = " "+mp3_dir+"startup-00"+mp3_sfx;
	public static final String err_clip = " "+mp3_dir+"error-00"+mp3_sfx;
	public static final String no_clip = " "+mp3_dir+"error-01"+mp3_sfx;
	public static final String[] modes = {"mode-00", "mode-01", "mode-02"};
	
	public static final int MODE_ALL = 0;
	public static final int MODE_NAME = 1;
	public static final int MODE_PRICE = 2; 
	
		
	public static void main(String[] args) {

		Service OService = new Service(4444, "localhost");
		

	}

	 Connection con;
	Statement statement_product;
	Statement statement_measure;
	Statement statement_brand;
	Statement statement_sale;
	Statement statement_alphanum;
	Statement statement_numeric;
	
	int port;
	String host;
	
	String per_clip;
	String ea_clip;
	String for_clip;
	String dollars_clip;
	String dollar_clip;	
	String cent_clip;
	String and_clip;
	String thousand_clip;
	String hundred_clip;
	
	PortListener p;
	
	int mode_state;
	 
	 
	Service(int port, String host){
		this.port = port;
		this.host = host;
		
		System.out.println("Playing startup file "+startup_clip);
		try{
			Runtime.getRuntime().exec("madplay -Q --no-tty-control"+startup_clip);
		}catch(IOException ioe){
			System.out.println("Exception Starting up:");
			ioe.printStackTrace();
		}

		//set up connection to database
		try{
			Class.forName("SQLite.JDBCDriver");
			//con = DriverManager.getConnection("jdbc:sqlite:/W:/db/product.db");
			con = DriverManager.getConnection("jdbc:sqlite://mnt/local/root/db/product.db");
			//Driver drive = new JDBCDriver();
			//con = drive.connect("jdbc:sqlite://mnt/local/root/db/product.db", null);
			//con = drive.connect("jdbc:sqlite:/C:/db/product.db", null);
			
			mode_state = 0;
			
			if(con != null) {
				statement_product = con.createStatement();
				statement_measure = con.createStatement();
				statement_brand = con.createStatement();
				statement_sale = con.createStatement();
				statement_alphanum = con.createStatement();
				statement_numeric = con.createStatement();

				ResultSet rs;
				
			//Preload frequently used clips	
				//get the "per" clip
				rs = statement_measure.executeQuery("SELECT * FROM measure WHERE id = 7;");
				if(rs.next()){
					per_clip = rs.getString("measure.clip");
					per_clip = " "+mp3_dir+per_clip+mp3_sfx;
				}
				rs.close();
				
				//get the "ea" clip
				rs = statement_measure.executeQuery("SELECT * FROM measure WHERE id = 4;");
				if(rs.next()){
					ea_clip = rs.getString("measure.clip");
					ea_clip = " "+mp3_dir+ea_clip+mp3_sfx;
				}
				rs.close();
				
				//get the "for" clip
				rs = statement_measure.executeQuery("SELECT * FROM measure WHERE id = 8;");
				if(rs.next()){
					for_clip = rs.getString("measure.clip");
					for_clip = " "+mp3_dir+for_clip+mp3_sfx;
				}
				rs.close();
				
				//get the "dollar" clip
				rs = statement_measure.executeQuery("SELECT * FROM measure WHERE id = 9;");
				if(rs.next()){
					dollar_clip = rs.getString("measure.clip");
					dollar_clip = " "+mp3_dir+dollar_clip+mp3_sfx;
				}
				rs.close();					
				
				//get the "dollars" clip
				rs = statement_alphanum.executeQuery("SELECT * FROM alphanum WHERE char = '$';");
				if(rs.next()){
					dollars_clip = rs.getString("alphanum.clip");
					dollars_clip = " "+mp3_dir+dollars_clip+mp3_sfx;
				}
				rs.close();
		
				//get the "cent" clip
				rs = statement_alphanum.executeQuery("SELECT * FROM alphanum WHERE char = '*';");
				if(rs.next()){
					cent_clip = rs.getString("alphanum.clip");
					cent_clip = " "+mp3_dir+cent_clip+mp3_sfx;
				}				
				rs.close();
				
				//get the "and" clip
				rs = statement_alphanum.executeQuery("SELECT * FROM alphanum WHERE char = '&';");
				if(rs.next()){
					and_clip = rs.getString("alphanum.clip");
					and_clip = " "+mp3_dir+and_clip+mp3_sfx;
				}				
				rs.close();
				
				//get the 1K clip
				rs = statement_numeric.executeQuery("SELECT * FROM numeric WHERE num = '1000';");
				if(rs.next()){
					thousand_clip = rs.getString("numeric.clip");
					thousand_clip = " "+mp3_dir+thousand_clip+mp3_sfx;
				}				
				rs.close();
				
				//get the 100 clip
				rs = statement_numeric.executeQuery("SELECT * FROM numeric WHERE num = '100';");
				if(rs.next()){
					hundred_clip = rs.getString("numeric.clip");
					hundred_clip = " "+mp3_dir+hundred_clip+mp3_sfx;
				}				
				rs.close();
			}
			
			else {
				System.out.println("Database connection null");
				System.exit(0);
			}
			
		
		}catch(ClassNotFoundException cnfe){
			System.out.println("Sqlite db driver not found:"+cnfe);
			System.exit(0);	
		}catch(SQLException se){
			System.out.println("Cannot connect to db:"+se);
			System.exit(0);
		}
		
		
		//set up scan connection listening thread
		p = new PortListener(this, host, port);
		Thread t = new Thread(p);
		t.start();
		
	}
	
	public void serveScan(String data, String reader_id) {
		
		System.out.println(reader_id+": "+data);
		String audio_file = null;

		if(checkModeScan(data)){
			audio_file = " "+mp3_dir+toggleMode()+mp3_sfx;
		}
		else{
			try {
	
				ResultSet rs = statement_product.executeQuery("SELECT * FROM product WHERE rfid = '"+data+"';");
				if(rs.next()) {
					audio_file = getAudioClip(rs);
				}
				else {
					System.out.println("No result set found in database for tag:" +data);
					audio_file = no_clip;
				}
				rs.close();
			} catch (SQLException e) {
				System.out.println("SQL Error looking up RFID tag:");
				e.printStackTrace();
				audio_file = err_clip;
			}
		}
		
		try {
			if(audio_file != null && !audio_file.equals("")){
				System.out.println("Playing audio file:"+audio_file);
				Process proc = Runtime.getRuntime().exec("madplay -Q --no-tty-control"+audio_file);
				proc.waitFor();
				
				p = new PortListener(this, host, port);
				Thread t = new Thread(p);
				t.start();
			}
			else{
				System.out.println("Audio clip was not properly constructed");	
			}
		} catch (IOException e1) {
			System.out.println("Error calling external audio player:");
			e1.printStackTrace();
		} catch (InterruptedException e2){
			System.out.println("Interrupted exception waiting for external player");
			e2.printStackTrace();
		}
		
	}
	
	public boolean checkModeScan(String id){
		return id.equals(mode_id);
	}
	
	public String toggleMode(){
		mode_state = (mode_state + 1)%modes.length;
		return modes[mode_state];
	}
	
	public String getAudioClip(ResultSet rs) throws SQLException{
		String clip = "";		

		if(mode_state == MODE_ALL || mode_state == MODE_NAME){
			String brand = getBrandClip(rs);
			if(brand != null)
				clip += brand;
		}


		if(mode_state == MODE_ALL || mode_state == MODE_NAME){				
			String name = getNameClip(rs);
			if(name != null)
				clip += name;
		}

		if(mode_state == MODE_ALL || mode_state == MODE_PRICE){							
			String price = getPriceClip(rs);
			if(price != null)
				clip += price;
		}


		return clip;
	}
	
	public String getPriceClip(ResultSet rs) throws SQLException{

		String price_clip = "";
		
		try{
			String quantity = rs.getString("product.price_quantity");
			String base_price = rs.getString("product.price");
			String price_unit_id = rs.getString("product.price_unit_id");
			String sale_type_str = rs.getString("product.sale_type");
			
			price_clip = parsePriceInfo(quantity, base_price, price_unit_id);
			
						
			int sale_type = 0;
			
			if(sale_type_str == null || sale_type_str.equals("")){
				return price_clip;
			}

			sale_type = Integer.parseInt(sale_type_str);
			ResultSet rs_price = statement_sale.executeQuery("SELECT * FROM sale WHERE id = "+sale_type+";");

			
			//two for one
			if(sale_type == 0){

				if(rs_price.next()){
					String sale_info = rs_price.getString("sale.clip");
					if(sale_info != null && !sale_info.equals("")){
						String sale_clip = " "+mp3_dir+sale_info+mp3_sfx;
						price_clip += sale_clip;
					}
				}
				rs_price.close();
			}
			
			
			//discounted price
			else if(sale_type == 1){

				if(rs_price.next()){
					String sale_info = rs_price.getString("sale.clip");
					if(sale_info != null && !sale_info.equals("")){
						String sale_clip = " "+mp3_dir+sale_info+mp3_sfx;
						price_clip = sale_clip;
					}
				}
				rs_price.close();
				
				String sale_price = rs.getString("product.sale_price");
				if(sale_price != null && !sale_price.equals("")){
					price_clip += parsePrice(sale_price);
				}				
			}
			
			//two for one
			else if(sale_type == 2){

				if(rs_price.next()){
					String sale_info = rs_price.getString("sale.clip");
					if(sale_info != null & !sale_info.equals("")) {
						String sale_clip = " "+mp3_dir+sale_info+mp3_sfx;
						price_clip += sale_clip;
					}
				}
				rs_price.close();				
			}
			
			//quantity discount
			else if(sale_type == 3){
				
				
				if(rs_price.next()){
					String sale_info = rs_price.getString("sale.clip");
					if(sale_info != null && !sale_info.equals("")){
						String sale_clip = " "+mp3_dir+sale_info+mp3_sfx;
						price_clip = sale_clip;
					}
				}
				rs_price.close();

				String sale_quantity = rs.getString("product.sale_quantity");
				String sale_price = rs.getString("product.sale_price");
				price_clip += parsePriceInfo(sale_quantity, sale_price, price_unit_id);					
				
			}
			
			
		}catch(NumberFormatException nfe){
			
		}
		
		return price_clip;

	}
	
	public String getBrandClip(ResultSet rs) throws SQLException{
		String brand_clip = "";
		try {
			String id_str = rs.getString("product.brand_id");
			int id = Integer.parseInt(id_str);
			ResultSet rs_brand = statement_brand.executeQuery("SELECT * FROM brand WHERE id = "+id+";");
			if(rs_brand.next()){
				String brand = rs_brand.getString("brand.clip");
				if(brand != null && !brand.equals(""))
					brand_clip = " "+mp3_dir+brand+mp3_sfx;
			}		
	
			rs_brand.close();
			

		} catch (NumberFormatException nfe){
			System.out.println("Invalid brand ID");
		}
		return brand_clip;
		
	}
	
	public String getNameClip(ResultSet rs) throws SQLException{
		String name_clip = "";
		String name = rs.getString("product.clip");
		if(name != null && !name.equals("")){
			name_clip = " "+mp3_dir+name+mp3_sfx;		
		}

		return name_clip;
				
	}
	
	public String parsePriceInfo(String quantity, String price, String price_unit_id) throws SQLException{
		String clip = "";
		
		String price_clip = "";
		String quantity_clip = "";
		String unit_clip = "";
		
		price_clip = parsePrice(price);
		
		if(quantity != null && !quantity.equals("") ){
			
			quantity_clip += parseQuantity(quantity);
			quantity_clip += for_clip;
		}						
		if(price_unit_id != null && !price_unit_id.equals("")) {
		
			try {
				int unit_id = Integer.parseInt(price_unit_id);
				ResultSet rs_unit = statement_measure.executeQuery("SELECT * FROM measure WHERE id = "+unit_id+";");
				if(rs_unit.next()){
					String unit = rs_unit.getString("measure.clip");
					if(unit != null && !unit.equals("")){
						if(unit_id == 0)
							unit_clip += per_clip;
						unit_clip += " "+mp3_dir+unit+mp3_sfx;
					}
				}
				rs_unit.close();
			}catch (NumberFormatException nfe){
				System.out.println("Number exception getting units: ");
				nfe.printStackTrace();
//				unit_clip = ea_clip;
			}
		}
/*		else{
			unit_clip = ea_clip;
		}*/
		
		return quantity_clip+price_clip+unit_clip;
	}
	public String parsePrice(String price) throws SQLException{
		String clip = "";
		price = fixUpPrice(price);
		
		if(price.indexOf('.') < 0){
			System.out.println("Bad fixed-up price in parsePrice(): "+price);
			return err_clip;
		}
		
		// first check if in database.
		if ( false) {
				//if so play prerecorded sound
		} else {
			// if not generate price sound
			String dollar_string = price.substring(0, price.indexOf('.'));
			String cent_string = price.substring(price.indexOf('.')+1);

			int dollars = 0;
			int cents = 0;
			
			try{
				dollars = Integer.parseInt(dollar_string);
				cents = Integer.parseInt(cent_string);
				
			} catch(NumberFormatException nfe){
				System.out.println("Bad portion of price: dollars: "+dollar_string+", cents: "+cent_string);
				return err_clip;
			}
			
				if ( dollars <= 1000 ) {
					if ( dollars >= 1000) 
						clip += getThousandsClip(dollar_string);
					if ( dollars >= 100)  
						clip += getHundredsClip(dollar_string);
					if ( dollars >= 1)  
						clip += getTensClip(dollar_string );
					if ( dollars > 0){
						if(dollars == 1)
							clip += dollar_clip;
						else
							clip += dollars_clip;					
					

					}

				// now add cents clips
					if ( cents > 0) {
						if ( dollars > 0){
							clip += and_clip;
						}	
						clip += getTensClip(cent_string);
						clip += cent_clip;
					}
				}
				else {
					// if over $10G then use simple method below
					if(price.indexOf('.') > 0 || !price.substring(price.indexOf('.')).equals(".00")){
						for(int x=0; x<price.indexOf('.'); x++){
							clip+=parseAlphanum(price.charAt(x));				
						}
						clip += dollars_clip;
						for(int x=price.indexOf('.')+1; x<price.length(); x++){
							clip+=parseAlphanum(price.charAt(x));				
						}
						clip += cent_clip;
					}
					else{
						for(int x=0; x<price.length(); x++){
							clip+=parseAlphanum(price.charAt(x));				
						}
						clip += dollars_clip;
					}
				}			
			}
		return clip;
	}
	
	private String fixUpPrice(String price) {
		// should return a string in the format DDD.CC
		// eg. 045.0  will return 45.00
		// eg. 0.5   will return 0.50

		String fixed_price = "";
		int x = 0;
		
		//drop preceding zeros
		while(price.charAt(x) == '0'){
			x++;
			if(x == price.length())
				return "0.00";
			if(price.charAt(x) == '.'){
				fixed_price += '0';
				break;
			}
		}
		
		//copy significant numbers until '.' or EOS
		while(price.charAt(x) != '.'){
			fixed_price += price.charAt(x);
			x++;
			if(x == price.length())
				return fixed_price+".00";
		}
		
		//copy '.'
		fixed_price += '.';
		x++;
		
		//copy first two numbers of cents, or pad as necessary
		for(int y = x; y < x+2; y++){
			if(y >= price.length())
				fixed_price += '0';
			else
				fixed_price += price.charAt(y);
		}		
		
		return fixed_price;
	}
	

	private String getThousandsClip(String dollar_string) throws SQLException{
		String clip = "";
		
		char thou_char = dollar_string.charAt( dollar_string.length() - 4);
		
		clip += parseAlphanum(thou_char);
		clip += thousand_clip;
			
		return " "+mp3_dir+clip+mp3_sfx;
	}
	
	private String getHundredsClip(String dollar_string) throws SQLException{
		String clip = "";
		
		char hun_char = dollar_string.charAt( dollar_string.length() - 3);
		
		clip += parseAlphanum(hun_char);
		clip += hundred_clip;
	
		return " "+mp3_dir+clip+mp3_sfx;
	}
	private String getTensClip(String price) throws SQLException{
		return parseNumberUnder100(price);
	}
	
	
	public String parseQuantity(String quantity) throws SQLException{
		String clip = "";
		for(int x=0; x<quantity.length(); x++){
			clip+=parseAlphanum(quantity.charAt(x));
		}
		return clip;
	}
	
	public String parseNumberUnder100(String number) throws SQLException{
		String clip = "";
		int test_int = 0;
		try{
			test_int = Integer.parseInt(number);
			number = Integer.toString(test_int);
		}catch (NumberFormatException nfe){
			System.out.println("Bad number given to parseNumberUnder100: "+number);
			return err_clip;
		}
		if(test_int >= 100){
			System.out.println("Number too big in parseNumberUnder100: "+number);
			return err_clip;
		}
		if(test_int >= 20){
			String query_num = number.charAt(0)+"0";				
			ResultSet rs_an = statement_numeric.executeQuery("SELECT * FROM numeric WHERE num = '"+query_num+"';");
			if(rs_an.next()){
				String t_clip = rs_an.getString("numeric.clip");
				if(t_clip != null && !t_clip.equals(""))
					clip = " "+mp3_dir+t_clip+mp3_sfx;
			}
			rs_an.close();
			
			if(number.charAt(1) != '0'){
				clip += parseAlphanum(number.charAt(1));
			}
		}
		else if(test_int >= 10 && test_int < 20){
			ResultSet rs_an = statement_numeric.executeQuery("SELECT * FROM numeric WHERE num = '"+number+"';");
			if(rs_an.next()){
				String t_clip = rs_an.getString("numeric.clip");
				if(t_clip != null && !t_clip.equals(""))
					clip = " "+mp3_dir+t_clip+mp3_sfx;
			}
			rs_an.close();
		}

		else 
			clip += parseAlphanum(number.charAt(0));

		return clip;
	}
		
	public String parseAlphanum(char alphanum) throws SQLException{
		String clip = "";
		char an = Character.toUpperCase(alphanum);
		ResultSet rs_an = statement_alphanum.executeQuery("SELECT * FROM alphanum WHERE char = '"+an+"';");
		if(rs_an.next()){
			String t_clip = rs_an.getString("alphanum.clip");
			if(t_clip != null && !t_clip.equals(""))
				clip = " "+mp3_dir+t_clip+mp3_sfx;
		}
		rs_an.close();
		return clip;
	}
	
}
