using System;
using System.Text;
using System.Data;
using System.Configuration;
using java.sql;
using chemaxon.util;
using chemaxon.struc;
using chemaxon.jchem.db;
using chemaxon.sss.search;

namespace JChemBaseSearch
{

    /// 

    /// DataAccess class using JDBC driver 
    /// 
    public static class DataAccess
    {
        private static DataTable GetStructures(int[] ids)
        {
            string structureTableName = ConfigurationManager.AppSettings["username"] + "." + ConfigurationManager.AppSettings["tablename"]; //read configuration settings from app.config
            String sql = "SELECT * " + "FROM " + structureTableName;
            if (ids.Length > 0)
            {
                sql += " WHERE cd_id in (";

                for (int i = 0; i < Math.Min(ids.Length, 10); i++)
                {
                    sql += ids[i] + ",";
                }

                sql = sql.Remove(sql.Length - 1);
                sql += ")";
            }

            Connection con = getConnectionHandler().getConnection();
            PreparedStatement preparedStatement = con.prepareStatement(sql, ResultSet.__Fields.TYPE_SCROLL_INSENSITIVE, ResultSet.__Fields.CONCUR_READ_ONLY);
            //preparedStatement.setInt(1, 1);
            ResultSet results = preparedStatement.executeQuery();

            DataTable dt = new DataTable();
            dt.Columns.Add("cd_id");
            dt.Columns.Add("cd_structure");
            bool a = results.first();

            while (results.next())
            {
                Blob blobStructure = results.getBlob("cd_structure");
                byte[] barr = new byte[blobStructure.length()];
                blobStructure.getBinaryStream().read(barr);
                string molFile = Encoding.ASCII.GetString(barr);
                string cdId = results.getString("cd_id");
                dt.Rows.Add(cdId, molFile);
            }

            return dt;
        }

        private static ConnectionHandler getConnectionHandler()
        {
            string url = ConfigurationManager.AppSettings["url"]; //read configuration settings from App.config
            string username = ConfigurationManager.AppSettings["username"];
            string password = ConfigurationManager.AppSettings["password"];

            try
            {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                Connection conn = DriverManager.getConnection(url, username, password);

                ConnectionHandler ch = new ConnectionHandler();
                ch.setConnection(conn);

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

        public static DataTable SearchStructure(object molecule, JChemSearchOptions searchOptions)
        {
            JChemSearch searcher = new JChemSearch();

            string molStr = molecule as string;
            if (molStr == null)
            {
                Molecule mol = molecule as Molecule;
                searcher.setQueryStructure(mol);
            }
            else
            {
                searcher.setQueryStructure(molStr);
            }

            searcher.setConnectionHandler(getConnectionHandler());
            searcher.setStructureTable(ConfigurationManager.AppSettings["tablename"]);

            searcher.setSearchOptions(searchOptions);
            searcher.setRunMode(JChemSearch.RUN_MODE_SYNCH_COMPLETE);
            searcher.run();

            int[] resultedIds = searcher.getResults();
            return GetStructures(resultedIds);
        }
    }

}