using System;
using System.Linq;
using System.Collections.Generic;
using ChemAxon.NET.Base.Data.Sql;
using ChemAxon.NET.Base.Search.Options;
using ChemAxon.NET.IKVM.DB;
using ChemAxon.NET.IKVM.Chemistry;
using ChemAxon.NET.IKVM.DB.Entities;
using ChemAxon.NET.API;

namespace ChemAxon.NET.UnitTest.API.Examples.Database
{

    public class DatabaseExample
    {
        public IJChemDatabaseHandler ConnectoDatabase_Example(string host, int port, string database, string user, string pwd, DBType databaseType)
        {
            IConnectionInfo connection = MainFactory.Database.CreateConnection(host, port, database, user, pwd, databaseType);
            return MainFactory.Database.OpenJChem(connection);
        }

        public IJChemTable GetTableFromDatabase_Example(IJChemDatabaseHandler database, string tableName)
        {
            IEnumerable<IJChemTable> tables = database.GetTables();
            return tables.First(table => table.Name == tableName);
        }

        public IEnumerable<JChemDBMolecule> SelectMoleculeFromDatabase_Example(IJChemTable table)
        {
            IJChemMolecule queryMolecule = MainFactory.Chemistry.CreateMolecule("Benzene");
            IJChemMoleculeFilter moleculeFilter = MainFactory.Database.CreateJChemMoleculeFilter(queryMolecule);
            moleculeFilter.SearchOption.InvertResults = false;
            moleculeFilter.SearchOption.AbsoluteStereo = JChemAbsoluteStereoMatchingModes.AlwaysOn;

            IDBConditions dbConditions = MainFactory.Database.CreateDBConditions(ComparisonSqlOperatorType.Greater, new DateTime(1993, 12, 11), table["DATE_COLUMN"]);
            dbConditions.And(ComparisonSqlOperatorType.Less, DateTime.Now, table["DATE_COLUMN"]);

            IEnumerable<JChemDBMolecule> molecules = table.GetMolecules(moleculeFilter, dbConditions);
            return molecules;
        }

        public JChemDBMolecule InsertMolecule_Into_Table_Example(IJChemTable table)
        {
            IJChemMolecule molecule = MainFactory.Chemistry.CreateMolecule("benzene");

            //Set datacolumn value
            molecule.Properties.Add("DATE_COLUMN", DateTime.Now);
            molecule.Properties.Add("VARCHAR_255_COLUMN", "Corporate ID");

            JChemDBMolecule insertedMolecule;
            table.Insert(molecule, out insertedMolecule);
            return insertedMolecule;
        }

        public void UpdateMolecule_Example(IJChemTable table, JChemDBMolecule moleculeToModify)
        {
            //Update the molecule       
            IJChemMolecule newMolecule = MainFactory.Chemistry.CreateMolecule("methylpropane");
            moleculeToModify.ModifyWith(newMolecule);

            //Update datacolumn value
            moleculeToModify.Properties.Add("DATE_COLUMN", new DateTime(1992, 12, 11));
            moleculeToModify.Properties.Add("VARCHAR_255_COLUMN", "Modified corporate ID");

            table.Update(moleculeToModify);
        }

        public void DeleteMolecule_Example(IJChemTable table, JChemDBMolecule moleculeToDelete)
        {
            table.Delete(moleculeToDelete);
        }
    }

}