package org.drools.template.jdbc;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import static org.junit.Assert.*;
import org.drools.RuleBase;
import org.drools.WorkingMemory;
import org.drools.RuleBaseFactory;
import org.drools.compiler.PackageBuilder;

import java.sql.*;
import java.io.InputStream;
import java.io.StringReader;
import java.util.List;
import java.util.ArrayList;


/**
 * /**
 * <p>A simple example of using the ResultSetGenerator.
 * The template used is "Cheese.drt" the same used by SimpleRuleTemplateExample.
 * Rather than use the spreadsheet ExampleCheese.xls, this example reads the data
 * from an HSQL database (which is created in this example).</p>
 */
public class ResultSetGeneratorTest {

    @Test
    public void testResultSet() throws Exception {

        // setup the HSQL database with our rules.
        Class.forName("org.hsqldb.jdbcDriver");
        Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:drools-templates", "sa", "");

        try {
            update("CREATE TABLE cheese_rules ( id INTEGER IDENTITY, persons_age INTEGER, birth_date DATE, cheese_type VARCHAR(256), log VARCHAR(256) )", conn);

            update("INSERT INTO cheese_rules(persons_age,birth_date,cheese_type,log) VALUES(42, '1950-01-01', 'stilton', 'Old man stilton')", conn);
            update("INSERT INTO cheese_rules(persons_age,birth_date,cheese_type,log) VALUES(10, '2009-01-01', 'cheddar', 'Young man cheddar')", conn);
        } catch (SQLException e) {
            throw new IllegalStateException("Could not initialize in memory database", e);
        }

        // query the DB for the rule rows, convert them using the template.

        Statement sta = conn.createStatement();
        ResultSet rs = sta.executeQuery("SELECT persons_age, cheese_type, log " +
                " FROM cheese_rules");

        final ResultSetGenerator converter = new ResultSetGenerator();
        final String drl = converter.compile(rs, getRulesStream());

        System.out.println(drl);

        sta.close();

        // test that our rules can execute.
        final RuleBase rb = buildRuleBase(drl);

        WorkingMemory wm = rb.newStatefulSession();

        //now create some test data
        wm.insert(new Cheese("stilton",
                42));
        wm.insert(new Person("michael",
                "stilton",
                42));
        final List<String> list = new ArrayList<String>();
        wm.setGlobal("list",
                list);

        wm.fireAllRules();

        assertEquals(1, list.size());

    }

    /**
     * Build the rule base from the generated DRL.
     * Same method from SimpleRuleTemplateExample.
     *
     * @param drls variable length of Drools rules as strings
     * @return RuleBase built from the rules
     * @throws Exception Add Exception handling to a real implementation.
     */
    private RuleBase buildRuleBase(String... drls) throws Exception {
        //now we build the rule package and rulebase, as if they are normal rules
        PackageBuilder builder = new PackageBuilder();
        for (String drl : drls) {
            builder.addPackageFromDrl(new StringReader(drl));
        }

        //add the package to a rulebase (deploy the rule package).
        RuleBase ruleBase = RuleBaseFactory.newRuleBase();
        ruleBase.addPackage(builder.getPackage());
        return ruleBase;
    }

    /**
     * simple getter method looks up our template as a Resource
     *
     * @return the template as an InputStream
     */
    private InputStream getRulesStream() {
        return this.getClass().getResourceAsStream("/templates/Cheese.drt");
    }

    /**
     * An HSQL update wrapper from http://hsqldb.org/doc/guide/apb.html
     *
     * @param expression SQL expression
     * @throws SQLException just rethrowing all the errors for the example
     */
    private void update(String expression, Connection conn) throws SQLException {
        Statement st;
        st = conn.createStatement();
        int i = st.executeUpdate(expression);
        if (i == -1) {
            System.out.println("db error : " + expression);
        }

        st.close();
    }

}
