package engine;

import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerFactoryConfigurationError;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpression;
import javax.xml.xpath.XPathFactory;

import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.CLOB;
import oracle.xdb.XMLType;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class OracleConnector {
	private Connection Connect() throws ClassNotFoundException, SQLException {
		Connection con = null;
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Properties props = new Properties();
		props.put("user", "conia6am");
		props.put("password", "heslo");
		props.put("SetBigStringTryClob", "true");
		con = DriverManager.getConnection(
				"jdbc:oracle:thin:@tirpitz.ms.mff.cuni.cz:1521:xml", props);
		return con;

	}

	public int UpdateItem(int invoiceId, Element invoice) throws Exception {
		Connection conn = Connect();
		try {

			String str = TransformToString(invoice);
			OraclePreparedStatement stmt = (OraclePreparedStatement) conn
					.prepareStatement("UPDATE TB_Invoices SET InvoiceXML=? WHERE InvoiceId=?");
			stmt.setObject(2, invoiceId);
			stmt.setObject(1, str); // bind the XMLType instance
			stmt.execute();
			conn.commit();
			conn.close();
		} catch (Exception ex) {
			conn.rollback();
			throw ex;
		}
		return 1;
	}

	public static String TransformToString(Element invoice)
			throws TransformerFactoryConfigurationError,
			TransformerConfigurationException, TransformerException {
		TransformerFactory transFactory = TransformerFactory.newInstance();
		Transformer transformer = transFactory.newTransformer();
		StringWriter buffer = new StringWriter();
		transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
		DOMSource domSource = new DOMSource(invoice);

		transformer.transform(domSource, new StreamResult(buffer));
		String str = buffer.toString();
		return str;
	}

	/**
	 * @param invoice
	 * @return
	 * @throws Exception
	 */
	public int InsertItem(Element invoice) throws Exception {
		int maxId = 0;
		Connection conn = Connect();
		try {
			System.out
					.println("---------Processing InvoiceLine and Party------");
			XPathFactory factory = XPathFactory.newInstance();
			XPath xpath = factory.newXPath();
			XPathExpression expr = xpath.compile("InvoiceLines");
			Object result = expr.evaluate(invoice, XPathConstants.NODESET);
			NodeList nodes = (NodeList) result;

			for (int i = 0; i < nodes.getLength(); i++) {
				System.out.println("---------Inserting InvoiceLines------");
				Node node = nodes.item(i);
				InsertItemInvoiceLine((Element) node);
				System.out.println("---------InvoiceLines inserted------");
			}

			// expr = xpath.compile("SellerSupplierParty");
			expr = xpath.compile("Party");
			result = expr.evaluate(invoice, XPathConstants.NODESET);
			nodes = (NodeList) result;
			for (int i = 0; i < nodes.getLength(); i++) {
				System.out.println("---------Inserting Party------");
				Node node = nodes.item(i);
				InsertItemParty((Element) node);
				System.out.println("---------Party inserted------");
			}
			String str = "";
			str = TransformToString(invoice);
			maxId = GetMaxId() + 1;
			PreparedStatement stmt = conn
					.prepareStatement("insert into TB_Invoices (InvoiceId, InvoiceXML) values(?,XMLTYPE(TO_CLOB(?)))");

			CLOB clobObj = getCLOB(str, conn);
			stmt.setObject(1, maxId);
			stmt.setObject(2, clobObj); // bind the XMLType instance

			stmt.executeUpdate();
			conn.commit();
			conn.close();
		} catch (Exception ex) {
			conn.rollback();
			throw ex;
		}
		return maxId;
	}

	public void InsertItemInvoiceLine(Element invoiceLine) throws Exception {
		Connection conn = Connect();
		try {
			String str = "";
			str = TransformToString(invoiceLine);
			//System.out.println(str);
			PreparedStatement stmt = conn
					.prepareStatement("INSERT INTO invoicebody VALUES (xmltype(TO_CLOB(?)).createSchemaBasedXML('http://xmlns.oracle.com/xml/invoice/invoiceline.xsd'))");
			CLOB clobObj = getCLOB(str, conn);
			stmt.setObject(1, clobObj); // bind the XMLType instance

			stmt.executeUpdate();
			conn.commit();
			conn.close();
		} catch (Exception ex) {
			conn.rollback();
			throw ex;
		}
	}

	public int InsertItemParty(Element party) throws Exception {
		int maxId = 0;
		Connection conn = Connect();
		try {

			String str = "";
			str = TransformToString(party);
			maxId = GetMaxIdParty() + 1;
			PreparedStatement stmt = conn
					.prepareStatement("insert into TB_PARTIES (Id, Party) values(?,XMLTYPE(TO_CLOB(?)))");

			CLOB clobObj = getCLOB(str, conn);
			stmt.setObject(1, maxId);
			stmt.setObject(2, clobObj); // bind the XMLType instance

			stmt.executeUpdate();
			conn.commit();
			conn.close();
		} catch (Exception ex) {
			conn.rollback();
			throw ex;
		}
		return maxId;
	}

	public int GetMaxId() throws ClassNotFoundException, SQLException {
		int result = 0;
		Connection con = Connect();
		Statement s = con.createStatement();
		ResultSet rset = s
				.executeQuery("Select Max(invoiceId) FROM TB_INVOICES");
		OracleResultSet orset = (OracleResultSet) rset;
		while (orset.next()) {
			result = orset.getInt(1);
		}
		return result;
	}

	public int GetMaxIdParty() throws ClassNotFoundException, SQLException {
		int result = 0;
		Connection con = Connect();
		Statement s = con.createStatement();
		ResultSet rset = s.executeQuery("Select Max(Id) FROM TB_PARTIES");
		OracleResultSet orset = (OracleResultSet) rset;
		while (orset.next()) {
			result = orset.getInt(1);
		}
		return result;
	}

	public Document SelectItem(int invoiceId) throws Exception {
		Document podoc = null;
		try {
			Connection con = Connect();
			Statement s = con.createStatement();
			PreparedStatement stmt = con
					.prepareStatement("SELECT INVOICEXML FROM TB_INVOICES WHERE InvoiceId=?");
			stmt.setObject(1, invoiceId);
			ResultSet rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			while (orset.next()) {
				// get the XMLType
				XMLType poxml = (XMLType) orset.getObject(1);
				// get the XMLDocument as a string...
				podoc = (Document) poxml.getDOM();
			}

			s.close();
			con.close();

		} catch (Exception ex) {
			throw ex;
		}
		return podoc;
	}

	public Document SelectItemParty(int partyId) throws Exception {
		Document podoc = null;
		try {
			Connection con = Connect();
			Statement s = con.createStatement();
			PreparedStatement stmt = con
					.prepareStatement("SELECT SYS_XMLGEN(e.party) FROM TB_PARTIES e WHERE e.id=?");
			stmt.setObject(1, partyId);
			ResultSet rset = stmt.executeQuery();
			OracleResultSet orset = (OracleResultSet) rset;

			while (orset.next()) {
				// get the XMLType
				XMLType poxml = (XMLType) orset.getObject(1);
				// get the XMLDocument as a string...
				podoc = (Document) poxml.getDOM();
			}

			s.close();
			con.close();

		} catch (Exception ex) {
			throw ex;
		}
		return podoc;
	}

	public int DeleteItem(String invoiceId) {
		return DeleteItem(Integer.parseInt(invoiceId));
	}

	public int DeleteItem(int invoiceId) {
		int result = 0;
		try {
			Connection conn = Connect();
			OraclePreparedStatement stmt = (OraclePreparedStatement) conn
					.prepareStatement("DELETE FROM TB_Invoices WHERE invoiceId=?");
			stmt.setObject(1, invoiceId);
			stmt.execute();
			conn.commit();
			result = 1;
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		return result;
	}

	public int UpdateXML(int invoiceId, String xPath, String value) {
		int result = 0;
		try {
			// /PO/CUSTNAME/text()
			// (SELECT INVOICEXML FROM TB_INVOICES WHERE InvoiceId=? )
			// UPDATE TB_Invoices SET InvoiceXML =
			// UPDATEXML(InvoiceXML,'/aaa/text()', 'John MARY') WHERE
			// invoiceId=?
			Connection conn = Connect();
			OraclePreparedStatement stmt = (OraclePreparedStatement) conn
					.prepareStatement("UPDATE TB_Invoices SET InvoiceXML = UPDATEXML(InvoiceXML,?, ?) WHERE invoiceId=?");
			stmt.setString(1, xPath);
			stmt.setString(2, value);
			stmt.setInt(3, invoiceId);
			stmt.execute();
			conn.commit();
			result = 1;
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		return result;
	}

	private CLOB getCLOB(String xmlData, Connection conn) throws SQLException {
		CLOB tempClob = null;
		// try{
		// if(tempClob!=null) {
		// ((oracle.sql.CLOB)tempClob).putString(1, xmlData);
		// }
		// } catch (Exception e){
		// }
		try {
			System.out
					.println("CREATE CLOB-------------------------------------");
			// If the temporary CLOB has not yet been created, create new
			tempClob = CLOB.createTemporary(conn, false,
					oracle.sql.CLOB.DURATION_CALL);
			System.out
					.println("SETTING MODE-------------------------------------");
			// Open the temporary CLOB in readwrite mode to enable writing
			tempClob.open(CLOB.MODE_READWRITE);
			System.out.println("MODE SET-------------------------------------");
			// Get the output stream to write
			Writer tempClobWriter = tempClob.getCharacterOutputStream();
			System.out.println("WRITE-------------------------------------");
			// Write the data into the temporary CLOB
			tempClobWriter.write(xmlData);

			// Flush and close the stream
			tempClobWriter.flush();
			tempClobWriter.close();

			// Close the temporary CLOB
			tempClob.close();
		} catch (SQLException sqlexp) {
			tempClob.freeTemporary();
			sqlexp.printStackTrace();
		} catch (Exception exp) {
			tempClob.freeTemporary();
			exp.printStackTrace();
		}
		return tempClob;
	}
}
